- Выберите и получите сумму столбца в строке состояния
- Получите сумму столбца с автосуммой (одним щелчком мыши / ярлыком)
- Использование функции СУММ для вычисления суммы вручную
- Суммировать только видимые ячейки в столбце
- Преобразование табличных данных в таблицу Excel, чтобы получить сумму столбца
- Получите сумму столбца на основе критерия
Получить сумму для всего столбца - это то, что большинству из нас приходится делать довольно часто. Например, если у вас есть данные о продажах на сегодняшний день, вы можете быстро узнать общую сумму в столбце до стоимости продаж до настоящего времени.
Возможно, вы захотите быстро увидеть, какова общая сумма, или вы можете сделать это в виде формулы в отдельной ячейке.
Это руководство по Excel расскажет о нескольких быстрых и быстрых методах суммирования столбца в Excel.
Выберите и получите сумму столбца в строке состояния
В Excel есть строка состояния (в правом нижнем углу экрана Excel), которая отображает полезную статистику о выбранных данных, например, Среднее, Счетчик и СУММ.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро узнать сумму продаж за указанные недели.
Для этого выберите весь столбец B (вы можете сделать это, щелкнув букву B в верхней части столбца).
Как только вы выберете весь столбец, вы заметите, что в строке состояния отображается сумма столбца.
Это действительно быстрый и простой способ получить сумму для всего столбца.
Преимущество использования строки состояния для получения суммы столбца заключается в том, что она игнорирует ячейки, содержащие текст, и учитывает только числа. В нашем примере ячейка B1 имеет текстовый заголовок, который игнорируется, а сумма столбца отображается в строке состояния.
Если вы хотите получить сумму нескольких столбцов, вы можете сделать выбор, и он покажет вам общее значение суммы всех выбранных столбцов.
Если вы не хотите выделять весь столбец, вы можете выбрать диапазон, и в строке состояния будет отображаться сумма только выбранных ячеек.
Одним из недостатков использования строки состояния для получения суммы является то, что вы не можете скопировать это значение.При желании вы можете настроить строку состояния и получить больше данных, таких как максимальное или минимальное значение из выбора. Для этого щелкните правой кнопкой мыши строку состояния и внесите изменения.
Получите сумму столбца с автосуммой (одним щелчком мыши / ярлыком)
Автосумма - действительно отличный инструмент, который позволяет быстро получить сумму всего столбца одним щелчком мыши.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите получить сумму значений в столбце B.
Ниже приведены шаги, чтобы получить сумму столбца:
- Выберите ячейку прямо под последней ячейкой в столбце, для которого вы хотите получить сумму.
- Перейдите на вкладку "Формула".
- В группе «Библиотека функций» выберите параметр «Автосумма».
Вышеупомянутые шаги мгновенно дадут вам сумму всего столбца в выбранной ячейке.
Вы также можете использовать автоматическое суммирование, выбрав столбец, в котором есть значение, и нажав опцию автоматического суммирования на вкладке формулы. Как только вы это сделаете, он выдаст вам автоматическую сумму в ячейке под выделенным фрагментом.
Примечание: Автосумма автоматически определяет диапазон и включает все ячейки в формуле СУММ. Если вы выберете ячейку с суммой и посмотрите на формулу в ней, вы заметите, что она относится ко всем ячейкам над ней в столбце.
Один незначительный недостаток при использовании автосуммы заключается в том, что он не определяет правильный диапазон в случае, если в диапазоне есть пустые ячейки или любая ячейка имеет текстовое значение. В случае пустой ячейки (или текстового значения) диапазон автосуммирования начнется ниже этой ячейки.
Совет профессионала: Вы также можете использовать функцию автосуммирования, чтобы получить сумму как по столбцам, так и по строкам. Если ваши данные находятся в строке, просто выберите ячейку после данных (в той же строке) и нажмите кнопку Автосумма.Сочетание клавиш автосуммирования
Хотя использование параметра «Автосумма» на вкладке «Формула» достаточно быстро, вы можете ускорить получение суммы с помощью сочетания клавиш.
Чтобы использовать ярлык, выберите ячейку, в которой вы хотите получить сумму столбца, и используйте ярлык ниже:
ALT = (удерживая клавишу ALT, нажмите клавишу равенства)
Использование функции СУММ для вычисления суммы вручную
Хотя функция автоматического суммирования является быстрой и эффективной, в некоторых случаях вам может потребоваться вычислить сумму столбцов (или строк) вручную.
Одна из причин для этого может заключаться в том, что вам нужна не сумма всего столбца, а только некоторые из ячеек в столбце.
В таком случае вы можете использовать функцию СУММ и вручную указать диапазон, для которого вы хотите получить сумму.
Предположим, у вас есть набор данных, показанный ниже, и вам нужна сумма значений в столбце B:
Ниже приведены шаги по использованию функции СУММ вручную:
- Выберите ячейку, в которой вы хотите получить сумму ячеек / диапазона
- Введите следующее: = СУММ (
- Выделите ячейки, которые вы хотите суммировать. Вы можете использовать мышь или клавишу со стрелкой (с клавишами со стрелками удерживайте клавишу Shift, а затем используйте клавиши со стрелками для выбора диапазона ячеек).
- Нажмите клавишу Enter.
Вышеупомянутые шаги дадут вам сумму выбранных ячеек в столбце.
Вы также можете создавать и использовать именованные диапазоны в функции SUM, чтобы быстро получить значение суммы. Это может быть полезно, если у вас есть данные, разбросанные по большой электронной таблице, и вы хотите быстро получить сумму столбца или диапазона. Сначала вам нужно создать именованный диапазон, а затем вы можете использовать это имя диапазона для получения суммы.
Ниже приведен пример, в котором я назвал диапазон - Продажи. Приведенная ниже формула также дает мне сумму в столбце продаж:
= СУММ (Продажи)
Обратите внимание, что когда вы используете функцию СУММ для получения суммы столбца, она также будет включать отфильтрованные или скрытые ячейки.
Если вы хотите, чтобы скрытые ячейки не включались при суммировании столбца, вам необходимо использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ или АГРЕГАТ (рассматривается далее в этом руководстве).
Суммировать только видимые ячейки в столбце
Если у вас есть набор данных, в котором есть отфильтрованные или скрытые ячейки, вы не можете использовать функцию СУММ.
Ниже приведен пример того, что может пойти не так:
В приведенном выше примере, когда я суммирую видимые ячейки, я получаю результат как 2549, в то время как фактический результат суммы видимых ячеек будет 2190.
Причина, по которой мы получаем неправильный результат, заключается в том, что функция СУММ также принимает отфильтрованные / скрытые ячейки при вычислении суммы столбца.
Если вы хотите получить только сумму видимых ячеек, вы не можете использовать функцию СУММ. В этом случае вам нужно использовать функцию ОБЩИЙ ИТОГ или ПРОМЕЖУТОЧНЫЙ ИТОГ.
Если вы используете Excel 2010 или более позднюю версию, вы можете использовать функцию АГРЕГАТ. Он делает все, что делает функция ПРОМЕЖУТОЧНЫЙ ИТОГ, и даже немного больше. Если вы используете более ранние версии, вы можете использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, чтобы получить СУММУ только видимых ячеек (т. Е. Игнорирует отфильтрованные / скрытые ячейки).Ниже приведена формула, которую можно использовать для получения суммы только видимых ячеек в столбце:
= АГРЕГАТ (9,3; B2: B6)
Функция агрегирования принимает следующие аргументы:
- function_num: Это число, которое сообщает функции СОВМЕСТНОЕ вычисление, которое необходимо выполнить. В этом примере я использовал 9, так как хочу получить сумму.
- опции: В этом аргументе вы можете указать, что вы хотите игнорировать при выполнении вычислений. В этом примере я использовал 3, что «игнорирует скрытую строку, значения ошибок, вложенные функции SUBTOTAL и AGGREGATE». Короче говоря, для вычислений используются только видимые ячейки.
- множество: Это диапазон ячеек, для которого вы хотите получить значение. В нашем примере это B2: B6 (который также имеет несколько скрытых / отфильтрованных строк)
Если вы используете Excel 2007 или более ранние версии, вы можете использовать следующую формулу Excel:
= ПРОМЕЖУТОЧНЫЙ ИТОГ (9; B2: B6)
Ниже приведена формула, в которой я показываю, как суммировать столбец при наличии отфильтрованных ячеек (с использованием функции ПРОМЕЖУТОЧНЫЙ ИТОГ).
Преобразование табличных данных в таблицу Excel, чтобы получить сумму столбца
Когда вы конвертируете табличные данные в таблицу Excel, становится очень легко получить сумму столбцов.
Я всегда рекомендую преобразовывать данные в таблицу Excel, так как это дает много преимуществ. А поскольку новые инструменты, такие как Power Query, Power Pivot и Power BI, так хорошо работают с таблицами, это еще одна причина для их использования.
Чтобы преобразовать данные в таблицу Excel, выполните следующие действия:
- Выберите данные, которые вы хотите преобразовать в таблицу Excel
- Щелкните вкладку Вставка
- Щелкните значок таблицы
- В диалоговом окне «Создание таблицы» убедитесь, что диапазон указан правильно. Также отметьте опцию «Моя таблица имеет заголовки», если у вас есть заголовки в ваших данных.
- Щелкните ОК.
Вышеупомянутые шаги преобразуют ваши табличные данные в таблицу Excel.
Сочетание клавиш для преобразования в таблицу - Control + T (удерживайте клавишу управления и нажмите клавишу T).Когда у вас есть таблица, вы можете легко получить сумму всех столбцов.
Ниже приведены шаги, чтобы получить сумму столбцов в таблице Excel:
- Выберите любую ячейку в таблице Excel
- Щелкните вкладку Дизайн. Это контекстная вкладка, которая появляется только при выборе ячейки в таблице Excel.
- В группе «Параметры стиля таблицы» установите флажок «Строка итогов».
Вышеупомянутые шаги мгновенно добавили бы итоговую строку внизу таблицы и дали бы сумму всех столбцов.
Еще одна вещь, которую нужно знать об использовании таблицы Excel, заключается в том, что вы можете легко изменить значение с СУММ столбца на Среднее, Счетчик, Мин / Макс и т. Д.
Для этого выберите ячейку в итоговых строках и используйте раскрывающееся меню, чтобы выбрать нужное значение.
Получите сумму столбца на основе критерия
Все описанные выше методы дадут вам сумму для всего столбца.
Если вы хотите получить только сумму тех значений, которые удовлетворяют критерию, вы можете легко сделать это с помощью формулы СУММЕСЛИ или СУММЕСЛИМН.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите получить только сумму тех значений, которые больше 500.
Вы можете легко сделать это, используя следующую формулу:
= СУММЕСЛИ (B2: B11; "> 500"; B2: B11)
С помощью формулы СУММЕСЛИ можно использовать как числовое, так и текстовое условие.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите получить СУММУ всех продаж, осуществленных Марком.
В этом случае вы можете использовать столбец A в качестве диапазона критериев и «Отметить» в качестве критерия, и формула даст вам сумму всех значений для Mark.
Следующая формула даст вам результат:
= СУММЕСЛИ (A2: A11; «Отметка»; B2: B10)
