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

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

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

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

Задача

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

Решение

Для удобства создадим динамические диапазоны : Компании (названия компаний из столбца А ), Тип_контракта (столбец B ) и Продажи (столбец С ). Если в таблицу будет добавлена новая строка, то ее значения будут автоматически включены в Динамические диапазоны и нижеследующие формулы не придется модифицировать.

Также создадим в ячейке E5 Выпадающий (раскрывающийся) список с двумя значениями: Валютный и Рублевый .

Для создания списка уникальных значений введем в ячейку G5 формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(Компании; ПОИСКПОЗ(0;ЕСЛИ(E$5=Тип_контракта;0;1)+СЧЁТЕСЛИ($G$4:G4;Компании);0) );"")

При выборе в ячейке E 5 значения Валютный, все компании, НЕ поставляющие по валютным контрактам, будут проигнорированы,

и список уникальных значений будет сформирован только из тех компаний, которые имеют контракты в валюте. Также будут подсчитаны суммарные продажи и количество продаж по каждой компании (столбцы H и I ).

Тестируем

1. Выберите в желтой ячейке значение Рублевый ; 2. В столбце G будет выведен список названий компаний с типом контракта – Рублевый : - в исходной таблице Условным форматированием будут выделены строки с типом контракта Рублевый; - в столбце H будут выведены суммарные продажи отобранных компаний (по данному типу контракта); - в столбце I будет выведено количество продаж (кол-во контрактов выбранного типа). 3. Теперь в зеленой ячейке Е12 выберите компанию ООО Рога и копытца

4. Установите серый флажок Выделите компанию 5. В исходной таблице будут выделены 2 строки содержащие название компании ООО Рога и копытца с типом контракта Рублевый

Созданная структура является разновидностью Связанного списка .

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