Функция промежуточные итоги в excel

Просмотр групп по уровням

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

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

1. Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
2. Щелкните следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
3. Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

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

Группировка строк и столбцов в Excel

1. Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.
2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.
3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

Чтобы разгруппировать данные в Excel, выделите сгруппированные строки или столбцы, а затем щелкните команду Разгруппировать.

Как скрыть и показать группы

1. Чтобы скрыть группу в Excel, нажмите иконку Скрыть детали (минус).
2. Группа будет скрыта. Чтобы показать скрытую группу, нажмите иконку Показать детали (плюс).

Расчёты при помощи функции

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

Для этого достаточно совершить несколько простых действий.

1. Перейдите на любую клетку. Нажмите на иконку «Fx». В окне «Вставка функции» выберите категорию «Полный алфавитный перечень». Найдите там нужный пункт. Кликните на кнопку «OK».

1. После этого появится окно, в котором вас попросят указать аргументы функции.

1. В качестве примера поставим цифру 9.

1. Затем нужно кликнуть на второе поле, иначе выделенный диапазон ячеек подставится в первую строчку, а это приведет к ошибке.

1. Затем выделите какие-нибудь значения. В нашем случае посчитаем сумму продаж первого товара. В конце кликаем на «OK».

1. Результат будет следующим.

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

Требования, которые предъявляют к таблицам, чтобы получить промежуточные результаты

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

1. В табличке не должно быть пустых ячеек, каждая из них должна обязательно содержать какую-либо информацию.
2. Шапка должна состоять из одной строчки. Кроме того, ее расположение должно быть корректным: без перескоков и совмещенных ячеек.
3. Оформление шапки должно быть выполнено строго в верхней строке, в противном случае функция не сработает.
4. Сама таблица должна быть представлена обычным количеством ячеек, без дополнительных ветвей. Получается, что конструкция таблицы должна состоять строго из прямоугольника.

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

Использование сводной таблицы Excel

Чтобы воспользоваться данной возможностью, нужно сделать следующее:

1. Укажите нужный диапазон.
2. Перейдите на вкладку «Вставка».
3. Используйте инструмент «Таблица».
4. Выберите нужный пункт.

1. Подберите что-то самое оптимальное для вас. Количество вариантов зависит от выделенной информации.
2. После этого кликните на «OK».

1. По умолчанию редактор Excel попробует сложить все значения в таблице.

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

1. Сделайте двойной клик по заголовку указанного поля.
2. Выберите нужную вам операцию. Например, «Среднее значение».
3. Сохраняем изменения нажатием на «OK».

1. Вследствие этого вы увидите следующее:

Как вставить промежуточные итоги в Excel

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

1. Организуйте исходные данные

Функция «Промежуточные итоги» в Excel требует, чтобы исходные данные располагались в правильном порядке (то есть, однотипные — рядом) и не содержали пустых строк.

Итак, прежде всего обязательно отсортируйте ваши данные по столбцу, по которому вы хотите их сгруппировать. Самый простой способ сделать это – нажать кнопку «Фильтр на вкладке «Данные», затем щелкнуть стрелку фильтра и выбрать сортировку от А до Я или от Я до А:

Чтобы удалить пустые ячейки, не испортив данные, следуйте этим рекомендациям: Как быстро и безопасно удалить пустые строки в Excel.

После этого подготовительную работу можно считать завершенной.

2. Добавьте промежуточные итоги

Выберите любую ячейку в наборе данных, перейдите на вкладку «Данные»> в группу «Структура» и нажмите «Промежуточный итог.

В этом случае Excel будет обрабатывать все данные в вашей таблице, пока не встретит пустые столбец и строку. То есть, до последней заполненной строки.

3. Определите параметры промежуточных итогов.

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

• В поле При каждом изменении в выберите столбец, по которому вы хотите группировать данные. В нашем случае мы выберем колонку с наименованиями покупателей.
• В списке Использовать функцию выберите одну из следующих:
  • Сумма.
  • Количество – подсчет непустых ячеек (это вставит формулы промежуточных итогов с функцией СЧЁТ ).
  • Среднее – расчет среднего значения.
  • Максимум – вернуть наибольшее число.
  • Минимум – получить наименьшее число.
  • Произведение – вычислить произведение по столбцу.
  • Количество чисел – подсчет ячеек, содержащих числа.
  • Стандартное отклонение – вычисление стандартного отклонения генеральной совокупности на основе выборки чисел.
  • Несмещённое отклонение – возвращает стандартное отклонение, основанное на всей совокупности чисел.
  • Дисперсия – оценка дисперсии генеральной совокупности на основе выборки чисел.
  • Несмещённая дисперсия – оценка дисперсии генеральной совокупности на основе всей совокупности чисел.
• В разделе «Добавить итоги по» установите флажок для каждого столбца, по которому вы хотите получить промежуточный итог.

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

Кроме того, вы можете выбрать любую из дополнительных опций:

• Чтобы вставить автоматический разрыв страницы после каждого промежуточного итога, установите флажок Конец страницы между группами. В итоге каждая группа будет распечатана на отдельном листе. Но в большинстве случаев это не нужно, поэтому эта опция обычно не активна.
• Чтобы отобразить итоговую строку сверху над данными, снимите флажок «Итоги под данными. Этот пункт обычно активирован по умолчанию, так как нам все же привычнее, когда сначала идут данные, а под ними — итоги.
• Чтобы перезаписать любые уже существующие промежуточные итоги, активируйте флажок «Заменить текущие итоги. Если вы изменили данные, то старые итоги вам не нужны. А вот если вы работаете не со всей, а только с частью таблицы (о такой возможности мы говорили выше), тогда, возможно, не нужно удалять то, что уже было посчитано. Кроме того, если не ставить этот флажок, то вы добавите еще один уровень итогов. Например, вы нашли сумму продаж по каждой группе, и можете добавить еще количество продаж или средний размер заказа. То есть, по каждой группе можно рассчитать несколько разных итогов.

Наконец, нажмите кнопку ОК. Промежуточные итоги появятся под каждой группой данных, а общая сумма будет добавлена ​​в конец таблицы.

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

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

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

Укажите, какую операцию нужно выполнить. И не забудьте убрать птичку в пункте «Заменить текущие итоги».

В результате получаем вот такую картину:

Как видите, подсчитаны и среднее, и сумма.

Способы подсчета итогов в рабочей таблице

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

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

1. Выделить диапазон клеток, итог сложения которых нужно получить.
2. На вкладке “Главная” в правой стороне найти значок автосуммы, нажать на него.

1. После выполнения данной операции результат появится в клетке под выделенным диапазоном.

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

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

Чтобы рассчитать результаты для отдельных ячеек или столбцов, необходимо воспользоваться функцией “СУММ”. Порядок действий:

1. Отметить нажатием ЛКМ ту ячейку, куда нужно вывести результат расчета.
2. Кликнуть по символу добавления функции.

1. После этого должно открыться окно настройки “Мастер функций”. Из открывшегося списка необходимо выбрать требуемую функцию “СУММ”.
2. Для выхода из окна “Мастер функций” нажать кнопку “ОК”.

Далее необходимо настроить аргументы функции. Для этого в свободном поле нужно ввести координаты ячеек, сумму которых требуется посчитать. Чтобы не вводить данные вручную, можно использовать кнопку справа от свободного поля. Ниже первого свободного поля находится еще одна пустая строчка. Она предназначена для выполнения расчета для второго массива данных. Если нужна информация только по одному диапазону ячеек, ее можно оставить пустой. Для завершения процедуры нужно нажать на кнопку “ОК”.

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

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

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

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

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

Щелкните любое место сводной таблицы, чтобы отобразить вкладку Работа со сводными таблицами.

На вкладке Конструктор нажмите кнопку Итоги.

Выберите подходящий вариант.

Не показывать промежуточные суммы

Показывать все промежуточные итоги в нижней части группы

Показывать все промежуточные итоги в заголовке группы

Совет: Чтобы включить в общие итоги отфильтрованные элементы, выберите пункт Включить отобранные фильтром элементы в итоги. Чтобы отключить эту функцию, выберите тот же пункт еще раз. Дополнительные параметры итогов и отфильтрованных элементов представлены на вкладке Итоги и фильтры диалогового окна Параметры сводной таблицы ( Анализ > Параметры).

Вот как отобразить или скрыть общие итоги.

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

На вкладке Конструктор нажмите кнопку Общие итоги.

Выберите подходящий вариант.

Отключить для строк и столбцов

Включить для строк и столбцов

Включить только для строк

Включить только для столбцов

Совет: Чтобы общие итоги не отображались для строк или столбцов, снимите флажок Показывать общие итоги для строк или Показывать общие итоги для столбцов на вкладке Итоги и фильтры диалогового окна Параметры сводной таблицы ( Анализ > Параметры).

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

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

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

Вот как отобразить или скрыть промежуточные итоги.

Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.

На вкладке Конструктор нажмите кнопку Итоги.

Выберите подходящий вариант.

Не показывать промежуточные итоги

Показывать все промежуточные итоги в нижней части группы

Показывать все промежуточные итоги в заголовке группы

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

Вот как отобразить или скрыть общие итоги.

Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.

На вкладке Конструктор нажмите кнопку Общие итоги.

Выберите подходящий вариант.

Отключить для строк и столбцов

Включить для строк и столбцов

Включить только для строк

Включить только для столбцов

Как использовать промежуточные итоги в Excel

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

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

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

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

В нашем образце рабочего листа щелкните цифру 2, чтобы отобразить первую группировку по регионам :

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

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

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

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

Самый очевидный способ, который приходит на ум – получить желаемые промежуточные итоги, а затем копировать эти строки в другое место – не сработает! 

Excel скопирует и вставит все строки, а не только видимые строки, включенные в указанную вами область.

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

1. Отобразите только те строки промежуточных итогов, которые вы хотите скопировать, используя цифры в структуре или символы «плюс» и «минус».
2. Выберите любую ячейку с промежуточным итогом и нажмите , чтобы выделить все ячейки.
3. Выделив промежуточные итоги, перейдите на вкладку «Главная»> «Редактирование» и нажмите «Найти и выделить» > «Выделить группу ячеек…»
4. В появившемся диалоговом окне выберите «Только видимые ячейки» и нажмите «ОК».
5. На текущем листе нажмите  для копирования выбранных ячеек с промежуточными итогами.
6. Откройте другой лист или книгу и нажмите , чтобы вставить промежуточные итоги.

Готово! В результате у вас есть только сводка данных, скопированная на другой рабочий лист. Обратите внимание, что этот метод копирует только значения, а не формулы:

Копируем только строки с промежуточными итогами

Скопировать только строки с промежуточными итогами в другой диапазон не так просто: если даже таблица сгруппирована на 2-м уровне (см. рисунок выше), то выделив ячейки с итогами (на самом деле выделится диапазон А4:D92) и скопировав его в другой диапазон мы получим всю таблицу. Чтобы скопировать только Итоги используем Расширенный фильтр (будем использовать тот факт, что MS EXCEL при создании структуры Промежуточные итоги вставляет строки итогов с добавлением слова Итог или в английской версии — Total).

создайте в диапазонеD5:D6табличку с критериями: в D5поместите заголовок столбца, в котором содержатся слова Итог, т.е. слово Товар; в D6введите *Итог (будут отобраны все строки, у которых в столбце Товар содержится значения, заканчивающиеся на слово Итог) Звездочка означает подстановочный знак *;

• выделите любую ячейку таблицы;
• вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
• в поле Диапазон условий введите D5:D6;
• установите опцию Скопировать результат в другое место;
• в поле Поместить результат в диапазон укажите пустую ячейку, например А102;

В результате получим табличку содержащую только строки с итогами.

СОВЕТ : Перед добавлением новых данных в таблицу лучше удалить Промежуточные итоги ( Данные/ Структура/ Промежуточные итоги кнопка Убрать все).

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

Промежуточные итоги в виде формулы

Чтобы не искать необходимый инструмент функции во вкладках панели управления, необходимо воспользоваться опцией «Вставить функцию». Рассмотрим этот способ подробнее.

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

4

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

5

1. В следующем окошке «Аргументы функции» выбираем «Номер функции». Прописываем цифру 9, соответствующую нужному нам варианту обработки информации – расчету суммы.

6

1. В следующем поле данных «Ссылка», выбираем количество ячеек, в которых следует найти промежуточные итоги. Чтобы не вводить данные вручную, можно просто с помощью курсора выделить диапазон необходимых ячеек, а следом в окошке нажать кнопку «ОК».

7

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

Промежуточные итоги в MS EXCEL

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

Имеем таблицу продаж товаров (товары повторяются). См. Файл примера .

Подсчитаем стоимость каждого товара с помощью средства MS EXCEL Промежуточные итоги ( Данные/ Структура/ Промежуточные итоги ).

Для этого необходимо:

• убедиться, что названия столбцов имеют заголовки;
• отсортировать данные по столбцу Товары, например с помощью Автофильтра;
• выделив любую ячейку в таблице, вызвать Промежуточные итоги (в меню Данные/ Структура );

• в поле «При каждом изменении в:» выбрать Товар;
• в поле «Операция» выбрать Сумма;
• в поле «Добавить итоги по» поставить галочку напротив значения Стоимость;
• Нажать ОК.

СОВЕТ : Подсчитать промежуточные итоги можно также с помощью Сводных таблиц и формул.

Как видно из рисунка выше, после применения инструмента Промежуточные итоги, MS EXCEL создал три уровня организации данных: слева от таблицы возникли элементы управления структурой. Уровень 1: Общий итог (стоимость всех товаров в таблице); Уровень 2: Стоимость товаров в каждой категории; Уровень 3: Все строки таблицы. Нажимая соответствующие кнопки можно представить таблицу в нужном уровне детализации. На рисунках ниже представлены уровни 1 и 2.

В таблицах в формате EXCEL 2007 Промежуточные итоги работать не будут. Нужно либо преобразовать таблицу в простой диапазон либо использовать Сводные таблицы.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() EXCEL

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами 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; — от 1 до 29 ссылок на диапазон, для которых подводятся итоги (обычно используется один диапазон).

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

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

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

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

Применим Автофильтр и отберем только строки с товаром Товар1 . Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает сумму товаров Товар1 , следовательно будем использовать код функции 9 или 109.

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

Если бы мы записали формулу = ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или = ПРОМЕЖУТОЧНЫЕ.ИТОГИ( 103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5).

Таким образом, эта функция «чувствует» скрыта ли строка автофильтром или нет. Это свойство используется в статье Автоматическая перенумерация строк при применении фильтра .

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

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

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

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

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

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

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

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

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

Воспользуемся той же исходной таблицей.

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

Для подсчета будем использовать формулу массива (см. файл примера , лист2)

С помощью выражения СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1 в качестве второго аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подается не один диапазон, а несколько ( равного числу строк ). Если строка скрыта, то вместо цены выводится значение Пустой текст «» , которое игнорируется функцией СРГЕОМ() . Таким образом, подсчитывается среднее геометрическое цен товара Товар1 .

Применение функции промежуточных итогов

Итак, теперь, когда мы определились с основными критериями “годности” таблиц, приступим к подсчету промежуточных итогов.

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

1. Отмечаем любую ячейку таблицы, переключаемся во вкладку “Данные”, находим раздел “Структура”, щелкаем по нему и в раскрывшемся перечне нажимаем по варианту “Промежуточный итог”.
2. В итоге появится окно, где мы осуществим дальнейшие настройки согласно нашей задаче.
3. Итак, нам требуется произвести расчет ежедневных продаж всех наименований продукции. Информация о дате продажи размещается в одноименном столбце. Исходя из этого, заполняем требуемые поля настроек.

закончив с настройками, подтверждаем действие нажатием на OK.

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

При желании можно убрать лишние данные из поля видимости, оставив только общий итог и промежуточные суммы. Нажатием кнопки “плюс” можно обратно развернуть строки внутри групп.

Примечание: После внесении каких-либо изменений и добавлении новых данных промежуточные итоги будут пересчитаны в автоматическом режиме.

Как быстро посчитать итоги в таблице?

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

Итак, приступим. Есть у нас вот такая таблица:

Мы выделяем все числовые данные плюс одну строку снизу и один столбец справа, чтобы получилось так:

• Переходим во вкладку «Главная» и в разделе «Редактирование» нажимаем кнопку «Автосумма«:
• И MS Excel все сделал за нас:
• Нам осталось только написать недостающие названия строки и столбца.
• А если наша таблица выглядит чуть сложнее и нам недостаточно посчитать общие итоги? Давайте разберем такую таблицу:

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

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

В открывшемся диалоговом окне в списке «Добавить итоги по:» устанавливаем галочки напротив пунктов «Январь«, «Февраль«, «Март» и нажимаем кнопку «ОК«:

После чего наша таблица приобретет следующий вид:

Если вас «напрягают» плюсики и минусики, образовавшиеся слева, во вкладке «Данные» в разделе «Структура» нажмите кнопку «Разгруппировать«, в выпавшем списке нажмите пункт «Удалить структуру«:

1. Структура исчезнет.
2. Что делать дальше вы уже, наверное, догадались? Выделяем все числовые данные и один пустой столбец справа, во вкладке меню «Главная» в разделе «Редактирование» нажимаем «Автосумма«:
3. Таблица приобретет такой вид:
4. Форматируем необходимые строки и столбцы и получаем что хотели:

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

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

1. Создайте таблицу, которая соответствует требованиям, описанным выше.

1. Выделите весь диапазон значений. Используйте иконку сортировки. Выберите инструмент «от А до Я».

1. Благодаря этому все клетки будут отсортированы по наименованию товара.

1. После этого сделайте активной какую-нибудь клетку (необязательно выделять всё целиком). Перейдите на вкладку «Данные». Используйте инструмент «Структура».

1. В появившемся меню выберите указанную кнопку.
2. Далее укажите нужную операцию. Чтобы продолжить, нажмите на «OK».

1. Исход будет следующим. В левой части редактора появятся дополнительные кнопки.

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

Вложенность имеет несколько уровней. Можно свернуть всю информацию целиком.

Как изменить промежуточные итоги

Чтобы быстро изменить существующие промежуточные итоги, просто сделайте следующее:

1. Выберите любую ячейку промежуточного итога.
2. Перейдите на вкладку «Данные» и нажмите «Промежуточный итог .
3. В диалоговом окне внесите необходимые изменения в ключевой столбец, укажите при необходимости другую используемую функцию и значения, для которых требуется вычислить промежуточные итоги.
4. Убедитесь, что установлен флажок Заменить текущие промежуточные итоги.
5. Щелкните ОК.

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