→ → Как в эксель посчитать сумму столбца. Как в эксель посчитать сумму столбца Как в экселе посчитать промежуточные итоги

Как в эксель посчитать сумму столбца. Как в эксель посчитать сумму столбца Как в экселе посчитать промежуточные итоги

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

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами EXCEL: и . См. Файл примера .

Синтаксис функции

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции ;ссылка1 ;ссылка2;...))

Номер_функции - это число от 1 до 11, которое указывает какую функцию использовать при вычислении итогов внутри списка.

Номер_функции
(включая скрытые значения) 		Номер_функции
(за исключением скрытых значений) 		Функция
1 101 СРЗНАЧ
2 102 СЧЁТ
3 103 СЧЁТЗ
4 104 МАКС
5 105 МИН
6 106 ПРОИЗВЕД
7 107 СТАНДОТКЛОН
8 108 СТАНДОТКЛОНП
9 109 СУММ
10 110 ДИСП
11 111 ДИСПР

Например, функция СУММ() имеет код 9. Функция СУММ() также имеет код 109, т.е. можно записать формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10). В чем различие - читайте ниже. Обычно используют коды функций от 1 до 11.

Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;... (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

Пусть имеется исходная таблица.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2 через меню или через контекстное меню.

В этом случае имеется разница между использованием кода функции СУММ() : 9 и 109. Функция с кодом 109 "чувствует" скрыта строка или нет. Другими словами для диапазона кодов номер_функции от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает значения строк скрытых при помощи команды Главная/ Ячейки/ Формат/ Скрыть или отобразить . Эти коды используются для получения промежуточных итогов только для не скрытых чисел списка.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

Пусть имеется также исходная таблица. Создадим структуру с использованием встроенного средства EXCEL - .

Скроем строки с Товар2 , нажав на соответствующую кнопку "минус" в структуре.

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

Другие функции

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

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

Выделив таблицу и нажав на значок «Промежуточные итоги», Excel выдаст окно:

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

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

Ниже располагается 3 чекбокса (флага):

  • Заменить текущие итоги – если установлен, то итоги, подведенные ранее, будут заменены, если нет, то к старым результатам добавятся новые;
  • Конец страницы между группами – если установлен, то при выводе на печать каждая отдельная группа с итогом будет располагаться на новом листе;
  • Итоги под данными – если установлен, то результат будет располагаться в конце группы, если нет, то в начале.

Кнопка «Убрать все» удаляет всю структуру данных.

Так преобразовалась таблица, после применения к ней описываемой функции:

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

Механизм подведения промежуточных итогов имеет общие черты с другим более мощным инструментом приложения Excel –

В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.

Описание

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

Синтаксис

Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

    Номер_функции - обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

Номер_функции
(с включением скрытых значений)

Номер_функции
(с исключением скрытых значений)

Функция

ПРОИЗВЕД

СТАНДОТКЛОН

СТАНДОТКЛОНП

Примечания

    Если уже имеются формулы подведения итогов внутри аргументов "ссылка1;ссылка2;..." (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

    Для констант "номер_функции" от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат , подменю Скрыть или отобразить ) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант "номер_функции" от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки . Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.

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

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы "номер_функции" от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные

Формула

Описание

Результат

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A5)

Значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 9 в качестве первого аргумента.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;A2:A5)

Среднее значение промежуточного итога диапазона ячеек A2:A5, полученное с использованием числа 1 в качестве первого аргумента.

Примечания

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

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

Для примера подведем промежуточные и общие итоги столбца "№ склада" в списке "Список учета товаров на складе".


Рис. 1.

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

  • В первую очередь отсортируйте список по тому столбцу, по которому хотите подвести итоги, в данном случае, отсортируйте "№ склада" по возрастанию
  • Затем выделите любую ячейку в списке и выберите команду "Итоги" в меню "Данные"
  • В появившемся окне диалога "Промежуточные итоги" выберите заголовок столбца "№ столбца" из списка "При каждом изменении в:"
  • В списке "Операция:" выберите требуемую функцию, которая может быть использована для вычисления промежуточных итогов, например функцию СУММ()
  • Далее в списке "Добавить итоги по" установите флажок перед заголовком столбца, по которому необходимо выполнить расчет промежуточного итога (например, стоимость)
  • Нажмите кнопку ОК для того, чтобы выполнить команду промежуточные итоги


Рис. 2.

В результате выполнения команды "Данные" - "Итоги" список отображается на экране в виде структуры. На рисунке представлен список со свернутыми записями в группах 2, 3 и 4.



Рис. 3.

С левой стороны списка расположены кнопки со значками "-" и "+", которые указывают на то, что все записи в первой и пятой группах списка отображены на экране, а в группах 2, 3, 4 - записи свернуты. Если щелкнуть указателем мыши на кнопке "-", записи в группе будут сворачиваться, а если щелкнуть на кнопке "+", то записи в группе будут разворачиваться.

Для завершения работы с командой "Итоги" щелкните указателем мыши на кнопке "Убрать все" в окне "Промежуточные итоги".

При вставке итоговых значений:

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

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

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

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

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

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

    Выделите ячейку в диапазоне.

    Выполните одно из следующих действий.

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

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

    1. при каждом изменении в столбце "Спорт"...

    2.... Промежуточный итог по столбцу Sales.

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

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

    1. при каждом изменении во внешнем столбце Region...

 

 
Это интересно: