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

Продолжим идеи, изложенные в статье Отбор уникальных значений в MS EXCEL. Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем только уникальные значения из первого столбца. При добавлении новых строк в таблицу, список уникальных значений будет автоматически обновляться.

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

Отберем из таблицы только те строки, которые удовлетворяют заданным условиям, которые приведены в табличке ниже.

Отобранные строки выделим Условным форматированием.

Затем из этих строк выберем только уникальные значения из первого столбца, т.е. только те компании, у которых Стоимость и Дата контракта находится в заданных диапазонах.

Решение приведено в файле примера на листе Уникальные. В его основе лежит формула массива из статьи Отбор уникальных значений (убираем повторы из списка) в MS EXCEL, которая использовалась для игнорирования пропусков в списке. После небольших изменений, формула для отбору уникальных с учетом 4-х условий выглядит так:

=ЕСЛИОШИБКА(ИНДЕКС($A$7:$A$25;ПОИСКПОЗ(0;
ЕСЛИ((($B$7:$B$25>=$F$7)+($B$7:$B$25<=$F$8)+($C$7:$C$25>=$G$7)+($C$7:$C$25<=$G$8))=4;
СЧЁТЕСЛИ($I$6:I6;$A$7:$A$25);"");0));"") 

или так

=ЕСЛИОШИБКА(ИНДЕКС($A$7:$A$25;ПОИСКПОЗ(0;
ЕСЛИ((($B$7:$B$25>=$F$7)*($B$7:$B$25<=$F$8)*($C$7:$C$25>=$G$7)*($C$7:$C$25<=$G$8));
СЧЁТЕСЛИ($I$6:I6;$A$7:$A$25);"");0));"")

Примечание. В формуле использована функция ЕСЛИОШИБКА(), которая работает только начиная с версии MS EXCEL 2007. О том как ее заменить, читайте в статье Функция ЕСЛИОШИБКА() в MS EXCEL.

Если значения Стоимости и Даты контракта соответствуют 4-м условиям, то при отборе уникальных это название компании учитывается. Если хотя бы не выполняется 1 условие, то название компании не учитывается. Если нужно ограничиться, например 2-мя условиями (только Стоимость), то удалите часть формулы +($C$7:$C$25>=$G$7)+($C$7:$C$25<=$G$8), а 4 замените на 2.

Не забудьте, что формулу массива нужно вводить в ячейку EXCEL с помощью одновременного нажатия CTRL+SHIFT+ENTER, затем ее нужно скопировать вниз, например, с помощью Маркера заполнения.

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

Примечание. Как видно из рисунков выше, в файле примера использованы Элементы управления формы для управления выделением строк с помощью Условного форматирования.

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

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

Комментарии

Yura

Я до этого пробовал функцию И() вместо конструкции (условие1 + условие2 = 2) - всегда возвращало мне пустую строку ("").
Волшебство! Спасибо! :)
ЗЫ полученный список не поддаётся сортировке

Creator

Стандартными средствами сортируются значения, непосредственно введенные в ячейку, а не формулы. Вот статья о динамической сортировке - Динамическая сортировка таблицы в MS EXCEL

Antoniy

Подскажите пожалуйста, не могу сообразить, аналогичная задача с двумя условиями, но нет возможности задать интервал по датам, т.е., если на Вашем примере, задаем дата 01/03/2014 и рога и копытца необходимо получить цифру 150 000р, т.е. последнюю стоимость контракта на введенную дату. Пока получилось вот так, но не работает: не могу сообразить где ошибся. (art,price,date соответственно столбцы a,b,c)
=ЕСЛИОШИБКА(ИНДЕКС(Таблица4[price];ПОИСКПОЗ(0;ЕСЛИ(((Таблица4[art]=$F$2+(ИНДЕКС(Таблица4[Date];ПОИСКПОЗ((МАКС(ЕСЛИ(Таблица4[Date]<=$G$2;Таблица4[Date];"")));Таблица4[Date];0);1))))=2;СЧЁТЕСЛИ($I$1:I1;Таблица4[Date]);"");0));"!!")

Creator

Добрый день, я так понял вы задаете наименование компании (art) и дату. Вам необходимо вывести последнюю сумму контракта этой фирмы, которая не ранее, чем заданная дата? 

Если так, то это не та статья, которая Вам нужна. Посмотрите статьи  в разделе Вывод отобранных значений в отдельный диапазон

Автофильтр также справится с Вашей задачей.

Если вопрос остался, то напишите на creator@excel2.ru

Яндекс.Метрика