Отбор уникальных значений в 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 , затем ее нужно скопировать вниз, например, с помощью Маркера заполнения .

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

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


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

Аноним, 20 марта 2020 г.
Очень круто! Я в восторге, так как только ваша формула мне помогла!!!❤💋🌷
Аноним, 21 июля 2020 г.
Отличная формула. Только как её адаптировать для таблиц с пустыми ячейками?
Михаил, 21 июля 2020 г.
Здравствуйте, в статье https://excel2.ru/articles/otbor-unikalnyh-znacheniy-ubiraem-povtory-iz-spiska-v-ms-excel есть файл примера, в нем лист "С пропусками" У вас, кстати, хороший вопрос))
(только для авторизованных пользователей)

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