Загрузите образец книги
В этом руководстве будет показано, как рассчитать промежуточные итоги по группам с помощью функции СУММЕСЛИМН в Excel и Google Таблицах.
Итоговая таблица по категориям или группам
Сначала мы продемонстрируем, как создать динамическую сводную таблицу промежуточных итогов из диапазона данных в Excel 365 и последующих версиях или в Google Таблицах.
Мы используем функции UNIQUE и SUMIFS для автоматического подведения итогов Количество продуктов к Группа товаров:
| 1 | = СУММЕСЛИМН (C3: C11; B3: B11; E3) |
Чтобы создать эту таблицу промежуточных итогов, мы используем стандартное приложение функции СУММЕСЛИМН, чтобы суммировать Количество продуктов которые соответствуют каждому Группа товаров. Однако, прежде чем это станет возможным, нам нужно создать список уникальных Группы товаров. Пользователи Microsoft Excel 365 и Google Sheets имеют доступ к УНИКАЛЬНОЙ функции для создания динамического списка уникальных значений из диапазона ячеек. В этом примере мы добавляем следующую формулу в ячейку E3:
| 1 | = УНИКАЛЬНЫЙ (B3: B11) |
При вводе этой формулы под ячейкой автоматически создается список, в котором отображаются все уникальные значения, найденные в Группа товаров диапазон данных. В этом примере список расширился, чтобы охватить E3: E5, чтобы показать все 3 уникальных Группа товаров ценности.
Это функция динамического массива, в которой размер списка результатов не нужно определять, и он будет автоматически сжиматься и увеличиваться при изменении значений входных данных.
Обратите внимание, что в Excel 365 функция UNIQUE не чувствительна к регистру, но в Google Таблицах это так. Рассмотрим список {«А»; «А»; «Б»; «C»}. Выход UNIQUE Function зависит от программы:
- {«А»; «Б»; «C»} в Excel 365
- {«А»; «А»; «Б»; «C»} в Google Таблицах
Если вы используете версию Excel до Excel 365, вам потребуется другой подход. Это обсуждается в следующем разделе.
Таблица промежуточных итогов по категориям или группам - до Excel 365
Если вы используете версию Excel до Excel 365, функция UNIQUE недоступна для использования. Чтобы воспроизвести то же поведение, вы можете объединить функцию ИНДЕКС и функцию ПОИСКПОЗ с функцией СЧЁТЕСЛИ, чтобы создать формулу массива для создания списка уникальных значений из диапазона ячеек:
| 1 | {= ИНДЕКС ($ B $ 3: $ B $ 11, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Чтобы эта формула работала, ссылки на фиксированные ячейки должны быть написаны осторожно, при этом функция СЧЁТЕСЛИ должна ссылаться на диапазон $ E $ 2: E2, который является диапазоном от E2 до ячейки над ячейкой, содержащей формулу.
Формулу также необходимо ввести как формулу массива, нажав CTRL + SHIFT + ENTER после того, как она будет записана. Эта формула является Формула массива с 1 ячейкой, которые затем можно скопировать и вставить в ячейки E4, E5 и т. д. Не вводите это как формулу массива для всего диапазона E3: E5 за одно действие.
Таким же образом, как и в предыдущем примере, функция СУММЕСЛИМН затем используется для подведения итогов Количество продуктов к Группа товаров:
| 1 | = СУММЕСЛИМН (C3: C11; B3: B11; E3) |
Сумма по категории или группе - промежуточные итоги в таблицах данных
В качестве альтернативы показанному выше методу сводной таблицы мы можем добавлять промежуточные итоги непосредственно в таблицу данных. Мы продемонстрируем это, используя функции ЕСЛИ вместе с функцией СУММЕСЛИМН, чтобы добавить Итого по группам к исходной таблице данных.
| 1 | = ЕСЛИ (B3 = B2; ""; СУММЕСЛИМН (C3: C11; B3: B11; B3)) |
В этом примере используется функция СУММЕСЛИМН, вложенная в функцию ЕСЛИ. Давайте разберем этот пример на шаги:
Чтобы добавить сводную статистику непосредственно в таблицу данных, мы можем использовать функцию СУММЕСЛИМН. Начнем с подсчета Количество продуктов которые соответствуют релевантным Группа товаров:
| 1 | = СУММЕСЛИМН (C3: C11; B3: B11; B3) |
Эта формула дает промежуточное значение для каждой строки данных. Чтобы отображать промежуточные итоги только в первой строке данных каждого Группа товаров, мы используем функцию ЕСЛИ. Обратите внимание, что данные уже должны быть отсортированы по Группа товаров чтобы обеспечить правильное отображение промежуточных итогов.
| 1 | = ЕСЛИ (B3 = B2; ""; СУММЕСЛИМН (C3: C11; B3: B11; B3)) |
Функция ЕСЛИ сравнивает значения каждой строки данных Группа товаров значение со строкой данных над ним, и если они имеют одинаковое значение, выводится пустая ячейка («»).
Если Группа товаров значения разные, отображается сумма. Таким образом, каждый Группа товаров сумма отображается только один раз (в строке своего первого экземпляра).
Сортировка наборов данных по группе
Если данные еще не отсортированы, мы все равно можем использовать ту же формулу для промежуточного итога.
Приведенный выше набор данных не отсортирован по Группа товаров, так что Итого по группам столбец отображает каждый промежуточный итог более одного раза. Чтобы получить данные в желаемом формате, мы можем выбрать таблицу данных и щелкнуть «Сортировать от А до Я».
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали некоторые формулы без заблокированных ссылок на ячейки:
| 1 | = ЕСЛИ (B3 = B2; ""; СУММЕСЛИМН (C3: C11; B3: B11; B3)) |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | = ЕСЛИ (B3 = B2; "", СУММЕСЛИМН ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Использование сводных таблиц для отображения промежуточных итогов
Чтобы убрать требование предварительно отсортировать данные по Группа товаров, мы можем вместо этого использовать возможности сводных таблиц для суммирования данных. Сводные таблицы автоматически вычисляют промежуточные итоги и отображают итоги и промежуточные итоги в нескольких различных форматах.
Сумма по категориям или группам в Google Таблицах
Эти формулы работают в Google Таблицах так же, как и в Excel. Однако функция UNIQUE чувствительна к регистру в Google Таблицах.
