Промежуточные итоги в Excel: функция и формула

Всем привет! Сегодня мы поговорим про промежуточные итоги в Excel. Очень часто эта функция нужна для подсчета прибыли за определенный период (день, месяц или квартал). В нашем уроке мы рассмотрим два варианта использования подсчета промежуточного итога. В первом способе мы посмотрим на встроенную функцию и кнопку. Второй вариант чуть посложнее, и там уже мы будем использовать полноценную формулу. В качестве примера я взял простую таблицу. Я постарался описать все как можно подробнее, с картинками и пояснениями, но если что-то будет непонятно – пишите в комментариях. Для новичков оставлю урок по созданию таблицы в Excel.

Способ 1: Использование встроенной функции

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

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

ВАЖНО! Перед добавлением итогов отсортируйте таблицу по столбцу, по которому будет группировка. В нашем примере это «Дата», иначе одинаковые даты могут разбиться на несколько кусков. Если нужно, читаем как отсортировать данные в Excel.

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

1. Выделите любую ячейку нашей таблицы и в самом верху перейдите на вкладку «Данные».

2. Теперь уже в панели инструментов в правой части найдите раздел «Структуры» и нажмите по инструменту «Промежуточный итог».

3. А теперь обратите внимание на скриншот ниже. Сначала вспоминаем нашу задачу – нам нужно подсчитать общую прибыль за каждый день. То есть в поле «При каждом изменении в» мы ставим «Дата» (второй столбец). Так как нам нужна «Сумма» всех чисел – ее и указываем, а ниже ставим галочку у столбца сложения – «Прибыль».

4. Галочки я оставил по умолчанию, но давайте я про них коротко расскажу:
   1. Заменить текущие итоги – эта галочка нужна для того, чтобы делать пересчет одних и тех же сумм. В нашем примере ее нужно убрать, ведь прибыль за прошлые дни останется прежней и без изменений. Если же в вашем примере вы будете делать постоянный пересчет одних и тех же строк, то эту галочку лучше оставить.
   2. Конец страницы между группами – в таком случае каждая сумма и итог будут расположены на разных страницах. В некоторых задачах это удобно. В моем же случае я не хочу плодить бесконечное количество страничек по разным дням.
   3. Итоги под данными – после каждого дня в самом низу будет создана новая строка, где будут выведены итоги суммирования.
5. Жмем «ОК» и смотрим результат. Теперь вы можете посмотреть все промежуточные результаты прибыли за каждый день.

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

Способ 2: Использование формулы

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

1. Выделите ячейку, в которой вы будете выводить промежуточный результат.
2. Далее жмем по кнопке «Вставить функцию» – она находится слева от строки значения.

3. Ставим категорию в режим «Полный алфавитный перечень». Далее находим формулу:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ

4. Жмем «ОК».

5. Теперь нам нужно правильно заполнить данные формулы. В первой строчке «Номер_функции» нам нужно указать функцию под номером. Их всего 11:

6. В моем случае нам нужно суммировать строки, а значит мне нужно указать цифру 9. Для ваших целей вы можете использовать другие формулы. Теперь жмем по «Ссылке1» и указываем ячейки промежуточного итога. Я выделил только один день. Жмем «ОК».

7. В этой строке вы увидите конечный результат.

Как правило, формулу используют только в связке с другими функциями или формулами. Аналогично вы можете все прописать вручную в строку значения. Формула будет простая:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер функции;диапазон ячеек)

ПРИМЕР: для суммы можно написать так: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C20). Если строки скрыты фильтром, они не попадут в расчет. Если строки скрыты вручную и их тоже не надо считать, вместо 9 используйте 109.

На этом наш урок подошел к концу. Надеюсь вам все было понятно. Если же у вас еще остались какие-то вопросы, и вы хотите получить помощь от портала WiFiGiD.RU – пишите в комментариях. Всем добра и берегите себя!