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

history

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

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

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

Одно условие отбора

В файле примера добавлено 2 листа с примерами отбора значений по 1 критерию (текстовый и числовой).


Комментарии

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

Аноним, 20 марта 2020 г.
Очень круто! Я в восторге, так как только ваша формула мне помогла!!!❤💋🌷
Аноним, 21 июля 2020 г.
Отличная формула. Только как её адаптировать для таблиц с пустыми ячейками?
Михаил, 21 июля 2020 г.
Здравствуйте, в статье https://excel2.ru/articles/otbor-unikalnyh-znacheniy-ubiraem-povtory-iz-spiska-v-ms-excel есть файл примера, в нем лист "С пропусками" У вас, кстати, хороший вопрос))
Elena, 5 апреля 2021 г.
Добрый день! Подскажите, пожалуйста, как быть, если только одно условие. Скажем, стоимость контракта 400 000. Переписала формулу так, но не получилось: = ЕСЛИОШИБКА(ИНДЕКС($A$7:$A$25;ПОИСКПОЗ(0; ЕСЛИ((($B$7:$B$25=$F$7))=1; СЧЁТЕСЛИ($I$6:I6;$A$7:$A$25);"");0));"")
Elena, 5 апреля 2021 г.
Прошу прощения: если стоимость контракта - 90 000
Михаил, 6 апреля 2021 г.
Здравствуйте, хороший вопрос. Вот формула, добавил в файл примера лист с одним условием. Не забудьте при вводе формулы массива нажимать CTRL+SHIFT+ENTER =ЕСЛИОШИБКА(ИНДЕКС($A$7:$A$25;ПОИСКПОЗ(0; ЕСЛИ((($B$7:$B$25>=$E$7)); СЧЁТЕСЛИ($G$6:G6;$A$7:$A$25);"");0));"")
Elena, 7 апреля 2021 г.
Михаил, добрый вечер! Спасибо огромное. Очень круто работает, нет слов
Юрий, 2 февраля 2022 г.
Михаил, добрый день, пример супер. В Вашем ответе от 06.04.2021 формула работает, если список отбираемых уникальных значений имеет текстовое значение. Но если, условие отбора текстовое значение, список для отбора например трех значное цисло и эти числа в списке идут не в порядке возрастания (например 812,813,810), то формула отбирает список уникальных значений до первого значения, которое меньше предшествующих (т.е 810 в списке не отберет). Помогите доработать формулу под данный вариант задачи
Михаил, 3 февраля 2022 г.
Юрий, я переделал файл примера под текстовый критерий, добавил лист. Структура формулы та же. Таких эффектов, как вы описали, у меня не наблюдается. Все считает корректно. Вопрос хороший )))
Юрий, 4 февраля 2022 г.
Михаил, перепроверил, всё работает корректно,забыл снять фильтр при проверке. Огромное спасибо, Ваша формула действительно очень помогла. Осталось найти макрос добавления новых строк и вставки формул при появлении нового уникального значения
Юрий, 4 февраля 2022 г.
Опробовал формулу на таблице более 1000 строк с уникальными текстовыми значениями порядка 50 знаков -ексель впадает в ступор. При таких условиях похоже нужно другое решение
Юрий, 5 февраля 2022 г.
Михаил, извиняюсь за назойливость. Подскажите как решить задачу: сделал выборку уникальных значений из двух таблиц, в столбец А из табл1, в столбец В из табл2, теперь нужно чтобы в столбец С были выведены значения из столбца В, которые отсутствуют в столбце А. Заранее спасибо за ответ
Михаил, 6 февраля 2022 г.
Юрий, вы правы Нужно использовать сводные таблицы. Этот пример есть в файле примера к статье.
Михаил, 6 февраля 2022 г.
Про сравнение списков я писал в этом разделе https://excel2.ru/gruppy-statey/sravnenie-spiskov
(только для авторизованных пользователей)

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