Отбор уникальных значений с суммированием по соседнему столбцу в 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

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


Комментарии
Только для авторизованных пользователей

Аноним, 7 декабря 2015 г.
Ув. Creator, с формулами все понятно, спасибо Вам огромное. Могли бы подсказать маленький нюанс или прислать ссылку с этим уроком - как Вы сделали так, что диапазон A5:A15 и B5:B15 является в формулах Регионы и Продажи соответственно?
Михаил, 19 июля 2016 г.
Это именованный диапазон. см. статью http://excel2.ru/articles/imenovannyy-diapazon-v-ms-excel Точнее, это динамический диапазон, но начать лучше с именованного.
Аноним, 14 ноября 2016 г.
не удается суммировать все значения с одинаковыми ячейками в соседнем столбце. Может ли повлиять положение текстового столбца?
Михаил, 16 ноября 2016 г.
Добрый вечер, Взаимное размещение столбцов с текстом и числами не влияет на результат. Если актуально, то файл примера можете прислать в нашу группу https://vk.com/excel2ru. Посмотрю, что не так.
Аноним, 30 июля 2019 г.
Добрый день, заметил странную особенность, когда создаем в диспетчере имен новое имя и делаем диапазон более 1715 строк (например =СМЕЩ('Таблица'!$J$3;;;СЧЁТЗ('Таблица'!$J$3:$J$2719)) то выберется только до 1715.
Михаил, 2 августа 2019 г.
Значит у Вас заполнено только 1715 ячеек, остальные пустые. Если пустые ячейки встречаются среди заполненных, то это может быть проблемой. Эта формула предназначена только для диапазонов, в которых пустые ячейки только в конце.
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved