Как выполнить суммирование и подсчет данных Excel

Как выполнить суммирование и подсчет данных Excel

Подсчет в сравнении суммированием
Суммирование (сложение значений) является неотъемлемой частью анализа данных при выполнении практически любой задачи: определении промежуточных итогов продаж по региону, суммировании еженедельных поступлений с нарастающим итогом и т. п. В Excel реализовано несколько способов суммирования данных.

Чтобы вам было проще подобрать оптимальный вариант, в этой статье приведен полный обзор методов, включая вспомогательные сведения и ссылки на подробные статьи.

Суммирование не следует путать с подсчетом. Дополнительные сведения о подсчете ячеек, столбцов или строк данных см. в статье Подсчет количества ячеек, строк и столбцов данных.

В этом разделе...

Простое сложение и вычитание

Складывать и вычитать числа можно с помощью простой формулы, кнопки или функции.

Сложение значений в ячейке с помощью простой формулы

Если вам нужен быстрый результат, используйте Excel в качестве мини-калькулятора. Для этого вы можете воспользоваться арифметическим оператором + (знак плюса). Например, если ввести формулу =5+10, в ячейке будет отображен результат 15.

Дополнительные сведения об использовании арифметических операторов в формуле см. в статье Использование Excel в качестве калькулятора.

К началу страницы

Вычитание значений в ячейке с помощью простой формулы

Для этого воспользуйтесь арифметическим оператором - (знак минуса). Например, для формулы =12-9 отображается результат 3.

Дополнительные сведения об использовании арифметических операторов в формуле см. в статье Использование Excel в качестве калькулятора.

К началу страницы

Сложение значений в столбце или строке с помощью кнопки

Для быстрого суммирования диапазона чисел в столбце или строке можно использовать кнопку "Автосумма". Щелкните пустую ячейку под столбцом чисел или справа от строки чисел и нажмите кнопку Автосумма. Excel выделяет то, что определяет как наиболее вероятный диапазон данных. Еще раз нажмите кнопку Автосумма, чтобы принять диапазон, выделенный приложением Excel, или выберите собственный диапазон, а затем нажмите кнопку Автосумма.

Использование функции «Автосумма» для быстрого сложения значений строки данных

Дополнительные сведения об использовании кнопки "Автосумма" см. в статье Использование Excel в качестве калькулятора.

К началу страницы

Сложение значений в диапазоне с помощью функции

Функцию СУММ удобно использовать при сложении или вычитании значений из разных диапазонов или при объединении числовых значений с диапазонами чисел. Функция СУММ складывает все аргументы между открывающей и закрывающей скобками. Каждый аргумент может представлять собой диапазон, ссылку на ячейку либо положительное или отрицательное числовое значение.

Чтобы создать простую формулу, введите =СУММ и открывающую скобку в ячейке. Затем укажите одно или несколько чисел, ссылок на ячейки или диапазонов ячеек через точку с запятой. Теперь поставьте закрывающую скобку и нажмите клавишу ВВОД, чтобы отобразить результат. Ячейки с данными для суммирования также можно выбрать с помощью мыши.

1

2

3

A

Присутствие

4823

12335

Например, для данных в предыдущей таблице все указанные ниже формулы с функцией СУММ возвращают одно и то же значение (17158).

  • =СУММ(4823;12335)

  • =СУММ(A2;A3)

  • =СУММ(A2:A3)

  • =СУММ(A2;12335)

На рисунке ниже показана формула, в которой функция СУММ используется для сложения значения ячейки A2 и числа 12 335. Под формулой появляется всплывающая подсказка с рекомендацией по использованию функции СУММ.

Использование функции СУММ для сложения значения ячейки и числа

Примечания : 

  • В Excel не существует функции ВЫЧЕСТЬ. Чтобы выполнить вычитание с помощью функции, укажите отрицательное значение для функции СУММ. Например, формула =СУММ(30;A3;-15;-B6) складывает 30 со значением ячейки A3, вычитает 15, а затем вычитает значение ячейки B6.

  • В качестве аргументов функции СУММ можно передать до 255 числовых значений, ячеек или диапазонов в любом сочетании.

Дополнительные сведения см. в статье Функция СУММ.

К началу страницы

Вычитание значений в диапазоне с помощью функции

Для вычитания используйте функцию СУММ, указывая в формуле числа, которые нужно вычесть, в виде отрицательных значений.

1

2

3

A

Присутствие

29072

12335

Например, для данных в предыдущей таблице все перечисленные ниже формулы с функцией СУММ возвращают одно и то же значение (16737).

  • =СУММ(29072;-12335)

  • =СУММ(A2;-A3)

  • =СУММ(A2;-12335)

  • =СУММ(A2;(-1*(A3)))

К началу страницы

Суммирование данных путем группировки и сведения

Для группировки и суммирования данных можно использовать структуру или отчет сводной таблицы.

Сложение значений в столбце с помощью структуры

Если данные находятся в списке и их можно логически сгруппировать по значениям столбцов, вы можете создать структуру для их группировки и суммирования. Значения в структуре суммируются путем вставки промежуточных и общих итогов. Предположим, у вас есть приведенные ниже данные о продажах, упорядоченные по регионам и месяцам.

1

2

3

4

5

6

7

A

B

C

Регион

Месяц

Продажи

Восточный

Янв

18 000 ₽

Восточный

Фев

23 000 ₽

Восточный

Мар

19 000 ₽

Западный

Янв

17 000 ₽

Западный

Фев

27 000 ₽

Западный

Мар

21 000 ₽

На рисунке ниже показана структура с промежуточными итогами, сгруппированными по региону, и общим итогом.

Структура с промежуточными и общими итогами

Чтобы создать структуру, промежуточные итоги и общий итог, используйте команду Промежуточный итог (вкладка Данные, группа Структура).

Исходные данные содержат три строки для восточного и три — для западного регионов (строки 2–7). Обратите внимание, что операция промежуточного итога добавляет общую сумму по восточному региону в строке 5 между последней строкой данных по восточному региону и первой строкой по западному.

Если вы щелкнете ячейку A4, а затем выберете команду Промежуточный итог, Excel создаст структуру, добавит строки для значений Итого по восточному, Итого по западному и Общий итог и заполнит ячейки в столбце Продажи соответствующими величинами.

Дополнительные сведения см. в следующих статьях:

Структурирование списка данных на листе

Вставка промежуточных итогов в списке данных на листе

К началу страницы

Сложение значений в списке или столбце таблицы Excel с помощью функции

Вместо команды Промежуточный итог можно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Она обладает более гибкими возможностями для вычисления промежуточных и общих итогов в списках и таблицах Excel, содержащих скрытые строки или столбцы. В функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ вы указываете аргумент, который включает или не включает скрытые значения.

Примечание : Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может определять, нужно ли включать в результаты скрытые строки, и всегда игнорирует строки, которые отфильтрованы.

Предположим, вам нужно вычислить промежуточные итоги и общий итог для данных в ячейках C2–C7, но не учитывать скрытые значения в строках 3 и 6. Соответствующая функция выглядит следующим образом:

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 109;C2:C7)

Первый аргумент (109) указывает, что нужно сложить значения в диапазоне и пропустить скрытые значения. Чтобы включить в расчет скрытые значения, укажите в качестве первого аргумента 9.

Подробные сведения см. в статьях ПРОМЕЖУТОЧНЫЕ.ИТОГИ (функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ) и Подсчет итоговых данных в таблице Excel.

К началу страницы

Суммирование данных и рассмотрение их с разных точек зрения

Если лист содержит сотни или даже тысячи строк похожих данных, из этих чисел сложно сделать какие-то выводы. В Excel на основе этих данных можно создать отчет сводной таблицы, в котором отображаются промежуточные и общие итоги и можно суммировать значения на основе заданных вами категорий.

Чтобы быстро создать сводную таблицу, выделите любую ячейку в диапазоне данных или таблице Excel, а затем на вкладке Вставка в группе Таблицы щелкните Сводная таблица.

Обратите внимание, что в приведенном ниже примере сведения о продажах содержат множество строк (фактически 40 строк данных, но на рисунке показана только часть). Кроме того, значения не суммированы, не содержат промежуточных и общего итогов.

Данные, используемые в отчете сводной таблицы

В отчете сводной таблицы на основе тех же данных показаны промежуточные и общие итоги, а также краткий отчет.

Данные, обобщенные и суммированные в отчете сводной таблицы

Для работы со сводными таблицами может потребоваться подготовить данные, а также ознакомиться с определенными понятиями.

Подробные сведения, которые помогут вам начать работу, см. в следующих статьях:

Вычисление значений в отчете сводной таблицы

Поля промежуточных и общих итогов в отчете сводной таблицы

К началу страницы

Суммирование значений с учетом одного или нескольких условий

При работе с функциями листа можно применять условия (также называемые критериями), которые позволяют складывать только значения, отвечающие заданным признакам.

Сложение значений в диапазоне с учетом одного условия с помощью одной функции или сочетания функций

Используйте функцию СУММЕСЛИ или функцию СУММ, вложенную в функцию ЕСЛИ.

Для сложения чисел с учетом одного условия можно использовать функцию СУММЕСЛИ или сочетание функций СУММ и ЕСЛИ.

Например, формула =СУММЕСЛИ(A2:A6;">20") складывает только числа больше 20 в диапазоне A2–A6.

Примечание : Приведенная далее информация носит технический характер, но, безусловно, стоит того, чтобы с ней ознакомиться.

Даже если бы функции СУММЕСЛИ не существовало, можно было бы получить аналогичный результат, объединив функции ЕСЛИ и СУММ. Такое объединение называется вложением функций, поскольку одна из них используется в другой.

Чтобы создать формулу, имитирующую функцию СУММЕСЛИ, нужно задать формулу, которая рассматривает диапазон A2–A6 как массив. Это означает, что диапазон воспринимается как единый объект, содержащий пять ячеек.

Формула имеет следующий вид: {=СУММ(ЕСЛИ(A2:A6>20;A2:A6))}. Фигурные скобки означают, что это формула массива. У нее есть ряд особенностей. Не вводите фигурные скобки вручную: вместо этого нажмите клавиши CTRL+SHIFT+ВВОД, и Excel заключит формулу =СУММ(ЕСЛИ(A2:A6>20;A2:A6)) в скобки. Если вы введете их вручную, это не приведет к созданию формулы массива.

Попробуйте!    

Скопируйте данные из приведенной ниже таблицы на лист начиная с ячейки A1. Вставив значения, вы увидите, что ячейка A10 содержит ошибку #ЗНАЧ!. Это означает, что ячейку A10 необходимо преобразовать в формулу массива. Для этого нажмите клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД. Excel отобразит результат (65) в ячейках A8 и A10.

Копирование данных листа примера

  • Создайте пустую книгу или лист.

  • Выделите пример в разделе справки.

    Примечание : Не выделяйте заголовки строк или столбцов.

    Выделение примера в справке
    Выделение примера в справке
  • Нажмите клавиши CTRL+C.

  • Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

  • Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих их, нажмите клавиши CTRL +' (знак ударения).

1

2

3

4

5

6

7

8

9

10

A

Вес

18

29

36

11

16

Использование функции СУММЕСЛИ

=СУММЕСЛИ(A2:A6;">20")

Использование функций СУММ и ЕСЛИ

=СУММ(ЕСЛИ(A2:A6>20;A2:A6))

Кроме того, в статье базы знаний XL: использование функции СУММ(ЕСЛИ()) вместо функции СЧИТАТЬПУСТОТЫ() можно найти дополнительные рекомендации по использованию СУММЕСЛИ.

К началу страницы

Сложение значений в столбце с учетом одного или нескольких условий с помощью функции

Функция БДСУММ позволяет складывать числа в поле (столбце) записей списка или базы данных, отвечающие заданным условиям (критериям).

БДСУММ также пригодится в ситуации, когда у вас есть список значений в столбце и вам удобнее задать условия в отдельном диапазоне ячеек, а не использовать вложенную функцию.

Дополнительные сведения см. в статье Функция БДСУММ.

К началу страницы

Сложение значений в диапазоне с учетом нескольких условий с помощью мастера (только для Excel 2007)

Можно использовать мастер суммирования (на вкладке Формулы в группе Решения нажмите кнопку Условная сумма) для сложения значений с учетом нескольких условий.

Важно : Мастер суммирования доступен только в Excel 2007 и более ранних версиях. Дополнительные сведения см. в статье Что произошло с надстройкой "Мастер суммирования"?.

Если эта команда недоступна (группа Решения также может не отображаться), установите и загрузите надстройку "Пакет анализа".

Загрузка пакета анализа

Нажмите кнопку Microsoft Office Изображение кнопки Office , щелкните Параметры Excel и выберите категорию Надстройки.

  1. В списке Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

  2. В списке Доступные надстройки выберите пункт Пакет анализа и нажмите кнопку ОК.

  3. Если мастер указан в списке Неактивные надстройки приложений, выберите его и нажмите кнопку Перейти, чтобы сделать его активным.

  4. Если потребуется, выполните инструкции программы установки.

Для использования мастера суммирования выберите диапазон данных и нажмите кнопку Условная сумма (группа Решения на вкладке Формулы). Выполните действия 1–4 для завершения работы мастера. На следующем рисунке показан шаг 2 мастера, в котором есть одно условие: суммируемые значения должны быть больше 100.

Мастер условной суммы: шаг 2 из 4

К началу страницы

Сложение значений в диапазоне с учетом нескольких условий с помощью одной функции или сочетания функций

Используйте функцию СУММЕСЛИМН. Укажите диапазон, который нужно суммировать, диапазон, содержащий условия, и условия, применяемые к диапазону условий. В качестве альтернативы можно использовать вложенные функции СУММ и ЕСЛИ.

Дополнительные сведения см. в статье Функция СУММЕСЛИМН.

Также см. дополнительные сведения в следующих статьях базы знаний:

К началу страницы

Суммирование квадратов величин и значений массива

С помощью функций листа можно складывать соответствующие друг другу значения в двух или нескольких столбцах. К вычислениям такого типа относятся как простейшие операции определения среднего взвешенного значения (см. ниже пример вычисления среднего балла), так и сложные статистические и технические расчеты.

Суммирование произведений соответствующих значений в одном или нескольких массивах

Используйте функцию СУММПРОИЗВ. Например, можно вычислить средний балл учащегося с помощью функции СУММПРОИЗВ и разделить результат на количество зачетных единиц, как показано на приведенном ниже рисунке.

Умножение соответствующих значений в двух массивах

Формула =СУММПРОИЗВ(A2:A6;B2:B6)/СУММ(B2:B6) умножает оценку, полученную каждым классом, на количество зачетных единиц, складывает эти произведения (61,3), делит сумму на общее количество зачетных единиц (18) и определяет средний балл (3,41). Такое вычисление также называется определением среднего взвешенного значения.

Функцию СУММПРОИЗВ можно использовать и в расширенных математических операциях, умножая с помощью нее несколько массивов.

Функцию СУММПРОИЗВ удобно использовать с массивами, которые имеют одинаковые размерности, однако если этого недостаточно, можно воспользоваться более гибкой функцией СУММ в формула массива.

Дополнительные сведения см. в статье Функция СУММПРОИЗВ.

Следующие четыре функции обычно используются только в расширенных статистических расчетах или сложных математических вычислениях, поэтому здесь приведено лишь их краткое описание. Чтобы получить дополнительные сведения в соответствующих разделах, щелкните название нужной функции.

  • Функция СУММКВ возвращает сумму квадратов указанных чисел или значений в диапазоне. Например, СУММКВ(2;3) возвращает значение 13.

  • Функция СУММСУММКВ складывает суммы квадратов соответствующих значений в двух массивах.

  • Функция СУММРАЗНКВ возвращает сумму разностей квадратов соответствующих значений в двух массивах.

  • Функция СУММКВРАЗН возвращает сумму квадратов разностей соответствующих значений в двух массивах.

К началу страницы

Особые случаи (нарастающий итог, уникальные значения)

С помощью функций листа можно создавать итоговые значения, которые автоматически обновляются по мере добавления новых данных в диапазон или таблицу, или складывать только уникальные значения из определенного диапазона или таблицы.

Создание нарастающего итога с помощью функции

Используйте функцию СУММ. Например, создайте на листе заголовки столбцов Вклады, Выплаты и Баланс.

  1. В первой ячейке столбца Баланс укажите остаток на начало периода.

  2. В ячейке непосредственно под балансом (в данном случае C3) задайте формулу, которая прибавляет значение из столбца Вклады в той же строке и вычитает значение из столбца Выплаты: =СУММ(C2;A3;-B3).

  3. Добавив новую строку, перетащите на нее эту формулу. В приведенном ниже примере формула перенесена на строку 4.

    Чтобы этот пример проще было понять, скопируйте его на пустой лист.

    Копирование примера

    • Создайте пустую книгу или лист.

    • Выделите пример в разделе справки.

      Примечание : Не выделяйте заголовки строк или столбцов.

      Выделение примера в справке

      Выделение примера в справке

    • Нажмите клавиши CTRL+C.

    • Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

    • Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

1

2

3

4

A

B

C

Вклады

Выплаты

Остаток на счете

500

1000

625

=СУММ(C2;A3;-B3)

1000

740

=СУММ(C3;A4;-B4)

Текущий остаток позволяет аналогично контрольному реестру наблюдать за увеличением и уменьшением значений в ячейках по мере ввода новых элементов и значений с течением времени.

Дополнительные сведения см. в статье Вычисление текущего остатка.

К началу страницы

Сложение уникальных значений в диапазоне с помощью составной формулы

Введите столбец значений, содержащий повторы, а затем используйте сочетание функций СУММ, ЕСЛИ и ЧАСТОТА, позволяющее сложить только уникальные величины из этого диапазона.

К началу страницы

Включение в формулу значений из других листов или книг

С помощью ссылок в формулу можно включить значения из ячеек и диапазонов данных листов и книг. Чтобы добавить ссылку на ячейку или диапазон, расположенный на другом листе или в другой книге, следуйте инструкциям в приведенной ниже таблице.

На что нужно сослаться

Что нужно ввести

Примеры

Ячейка или диапазон на другом листе в той же книге

Имя листа, затем восклицательный знак, а затем ссылка на ячейку или имя диапазона.

Лист2!B2:B4
Лист3!ОбъемыПродаж

Ячейка или диапазон в другой открытой книге

Имя файла книги в квадратных скобках ([]) и имя листа, затем восклицательный знак, а затем ссылка на ячейку или имя диапазона.

[МояКнига.xlsx]Лист1!A7

Ячейка или диапазон в другой книге, которая не открыта

Полный путь и имя файла книги в квадратных скобках ([]), имя листа, затем восклицательный знак, а затем ссылка на ячейку или имя диапазона. Если полный путь содержит пробелы, поставьте одинарную кавычку перед его началом, а вторую — после имени листа (см. пример).

['C:\Мои документы\[МояКнига.xlsx]Лист1'!A2:A5

К началу страницы

Сложение и вычитание значений даты и времени

Функции даты и времени и даже простые операции сложения и вычитания можно использовать для расчета затраченного времени, оценки сроков завершения проектов и многого другого. В таблице ниже приведены формулы для вычисления затраченного времени и дат. Обратите внимание, что если при вычитании даты или времени получается отрицательное значение, то Excel отображает символы ### в ячейке, содержащей формулу.

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

  • Создайте пустую книгу или лист.

  • Выделите пример в разделе справки.

    Примечание : Не выделяйте заголовки строк или столбцов.

    Выделение примера в справке

    Выделение примера в справке

  • Нажмите клавиши CTRL+C.

  • Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

  • Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

1

2

3

4

5

A

B

C

Время начала

Время окончания

Формула затраченного времени

Описание (результат)

11:55:24

14:48:13

=B2-A2

Затраченное время в часах, минутах и секундах. Если в системе используется 12-часовой формат времени, Excel выводит результат 02:52:49 в формате "до полудня" (AM), поскольку разность составляет меньше 12 часов. При разности в 12 часов или больше Excel отобразил бы результат в формате "после полудня" (PM).

Дата начала

Дата окончания

Формула затраченных дней

Описание (результат)

28.05.2008

03.06.2008

=B5-A5

Затраченные дни отображаются в виде числа (6).

Операции со значениями даты и времени могут возвращать непредсказуемые результаты, поэтому будьте внимательны.

К началу страницы

Отображение вычислений в строке состояния Excel

В строке состояния в нижней части окна документа Excel можно быстро посмотреть итоговые значения ячеек, выделенных на листе.

При выделении одной или нескольких ячеек информация о данных в них отображается в строке состояния Excel. Например, если на листе выделены четыре ячейки, которые содержат значения 2, 3, текстовую строку (например, "облако") и 4, то в строке состояния могут одновременно отображаться следующие значения: среднее значение, количество выделенных ячеек, количество ячеек с числовыми значениями, минимальное значение, максимальное значение и сумма. Чтобы отобразить или скрыть все или любые из этих значений, щелкните строку состояния правой кнопкой мыши. Эти значения показаны на приведенном ниже рисунке.

Строка состояния, на которой отображаются вычисления и количество выделенных ячеек

К началу страницы

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×