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

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

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

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

Задача

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

Решение

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

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

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

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

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

Тестируем

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

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

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

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

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

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