Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL

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

Разовьем идеи, изложенные в статье Отбор уникальных значений (убираем повторы).

Пусть исходная таблица содержит 2 столбца: текстовый – Список регионов и числовой - Объем продаж. Столбец Список регионов содержит повторяющиеся значения (см. файл примера). Уникальные значения выделены цветом с помощью Условного форматирования.

Задача

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

Решение

Создадим Динамические диапазоны: Регионы (названия регионов из столбца А) и Продажи (объемы продаж из столбца B).

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

Для создания списка уникальных значений введем в ячейку D2 формулу массива:
=ЕСЛИОШИБКА(ИНДЕКС(Регионы; ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;Регионы);0));"")

Не забудьте при вводе вышеуказанной формулы нажать CTRL+SHIFT+ENTER. Затем необходимо скопировать формулу вниз (размерность списка уникальных значений должна совпадать с размерностью исходного списка).

Для подсчета суммарных продаж в столбце E используем мощную функцию СУММПРОИЗВ():
=СУММПРОИЗВ((Регионы=D2)*Продажи)

Отображение нулей в строках, в которых нет регионов, уберем пользовательским форматом # ##0;-# ##0; (см. статью Скрытие значений равных 0).

Тестируем

1. Введите в ячейку А11 новый регион - Китай
2. Введите объем продаж - 100
3. Введите в А12 - Китай
4. Введите объем продаж - 500
5. В соседней таблице справа в ячейке D7 будет выведено название региона Китай с суммарным объемом продаж 600

СОВЕТ:
Другим подходом к решению этой задачи является использование Сводных таблиц (см. файл примера).

Связанные статьи

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

Комментарии

Сергей (не проверено)

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

Сергей (не проверено)

Разобрался. это же формула массива CTRL+SHIFT+ENTER :)

Александра (не проверено)

Хороший пример; то, что мне нужно. Делаю все точно по примеру, но формула по выводу уникальных имен по региону не срабатывает. Не могу разобраться в чем дело. Ввожу формулу как надо CTRL+SHIFT+ENTER.

Creator

Александра, пришлите свой пример на creator@excel2.ru Я посмотрю.

Александра (не проверено)

Благодарю)))) я уже разобралась, с диапазоном промахнулась