Извлечение уникальных значений на основе одного или нескольких критериев в Excel
Извлечение уникальных значений на основе заданных критериев — ключевая задача при анализе данных и подготовке отчётов. Допустим, у вас есть диапазон данных слева, и вы хотите получить только уникальные имена из столбца B, соответствующие определённому условию из столбца A. Независимо от того, работаете ли вы со старыми версиями Excel или используете новейшие функции Excel 365/2021, в этом руководстве показано, как эффективно извлекать уникальные значения.
Извлечение уникальных значений на основе критериев в Excel
- С помощью формул массива
- С помощью Kutools для Excel
- С помощью формулы (Excel 365, Excel 2021 и более поздние версии)
Извлечение уникальных значений на основе нескольких критериев в Excel
Извлечение уникальных значений из списка ячеек с помощью Kutools для Excel
Извлечение уникальных значений на основе критериев в Excel
•С помощью формулы массива для вертикального списка уникальных значений
Для решения этой задачи используйте сложную формулу массива. Выполните следующие действия:
1. Введите приведённую ниже формулу в пустую ячейку, где вы хотите отобразить результат извлечения. В данном примере я поместил её в ячейку E2, а затем нажал Shift + Ctrl + Enter, чтобы получить первое уникальное значение.
=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")2. Затем перетащите маркер заполнения вниз по ячейкам до появления пустых ячеек — и все уникальные значения, соответствующие заданному критерию, автоматически отобразятся. См. скриншот:
•Извлечение и отображение уникальных значений в одной ячейке с помощью Kutools для Excel
Kutools для Excel предлагает простой способ извлекать уникальные значения и отображать их в одной ячейке, экономя ваше время и усилия при работе с большими наборами данных — без необходимости запоминать формулы.
После установки Kutools для Excel выполните следующие действия:
Нажмите «Kutools» → «Супер ПОИСК» → «Поиск один-ко-многим (возвращает несколько результатов)», чтобы открыть диалоговое окно. В нём выполните настройку операций, как указано ниже:
- Выберите «Область размещения списка» и «Диапазон значений поиска» в соответствующих текстовых полях;
- Выберите диапазон таблицы, который вы хотите использовать;
- Укажите Ключевой столбец и Столбец для возврата в раскрывающихся списках «Ключевой столбец» и «Столбец для возврата» соответственно;
- Наконец, нажмите кнопку «ОК».
Результат:
Все уникальные имена, соответствующие заданному критерию, извлечены в одну ячейку — см. скриншот:
•С помощью формулы в Excel 365, Excel 2021 и более поздних версиях для вертикального списка уникальных значений
В Excel 365 и Excel 2021 функции UNIQUE и FILTER значительно упрощают получение уникальных значений.
Введите приведённую ниже формулу в пустую ячейку и нажмите Enter, чтобы мгновенно получить все уникальные имена в вертикальном списке.
=UNIQUE(FILTER(B2:B15, A2:A15=D2))
- FILTER(B2:B15, A2:A15=D2):
- FILTER: фильтрует данные из диапазона B2:B15.
- A2:A15=D2Проверяет, где значения в диапазоне A2:A15 совпадают со значением в ячейке D2, и включает в результат только строки, удовлетворяющие этому условию.
- UNIQUE(...):
гарантирует возврат только уникальных значений из отфильтрованных результатов.
Извлечение уникальных значений на основе нескольких критериев в Excel
•С помощью формулы массива для вертикального списка уникальных значений
Если вам нужно извлечь уникальные значения по двум условиям, воспользуйтесь следующей формулой массива. Выполните эти шаги:
1. Введите приведённую ниже формулу в пустую ячейку, где вы хотите отобразить уникальные значения. В данном примере она помещена в ячейку G2 — после этого нажмите Shift + Ctrl + Enter, чтобы получить первое уникальное значение.
=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<,>,$E$2,1,0)+IF($B$2:$B$15<,>,$F$2,1,0),0)),"")2. Затем перетащите маркер заполнения вниз по ячейкам до появления пустых ячеек — и все уникальные значения, соответствующие двум заданным условиям, будут выведены (см. скриншот):
•С помощью Excel 365, Excel 2021 и более поздних версий для вертикального списка уникальных значений
В новых версиях Excel извлечение уникальных значений по нескольким критериям стало значительно проще.
Введите приведённую ниже формулу в пустую ячейку и нажмите Enter, чтобы мгновенно получить все уникальные имена в вертикальном списке.
=UNIQUE(FILTER(C2:C15, (A2:A15=E2) * (B2:B15=F2)))
- FILTER(C2:C15, (A2:A15=E2) * (B2:B15=F2)):
- FILTERфильтрует данные из диапазона C2:C15.
- (A2:A15=E2)Проверяет, совпадают ли значения в столбце A со значением ячейки E2.
- (B2:B15=F2)Проверяет, совпадают ли значения в столбце B со значением ячейки F2.
- *: объединяет два условия с помощью логического И, то есть строка попадает в результат только если оба условия выполняются одновременно.
- UNIQUE(...):
Удаляет дублирующиеся значения из отфильтрованных результатов, гарантируя, что выходные данные содержат только уникальные значения.
Извлечение уникальных значений из списка ячеек с помощью Kutools для Excel
Иногда возникает необходимость извлечь уникальные значения из списка ячеек — в этом случае рекомендую воспользоваться удобным инструментом Kutools для Excel. Его функция «Извлечь ячейки с уникальными значениями из диапазона (включая первый дубликат)» позволяет быстро и легко получить нужные данные.
1. Щелкните по ячейке, в которую хотите вывести результат. ()Примечание: не выбирайте ячейку в первой строке.)
2. Затем перейдите в меню «Kutools» > «Помощник формул» и выберите «Помощник формул» (см. скриншот):
3. В диалоговом окне «Помощник формул» выполните следующие действия:
- Выберите параметр «Текст» в раскрывающемся списке «Тип формулы»;
- Затем выберите «Извлечь ячейки с уникальными значениями из диапазона (включая первый дубликат)» в списке «Выберите формулу»;
- В правой части раздела «Ввод аргумента» выберите диапазон ячеек, из которого нужно извлечь уникальные значения.
4. Затем нажмите кнопку ОК. Первый результат появится в ячейке. Выделите эту ячейку и перетащите маркер заполнения на нужное количество ячеек — до появления пустых ячеек (см. скриншот):
Извлечение уникальных значений на основе критериев в Excel — важнейшая задача для эффективной работы с анализом данных, и Excel предлагает несколько способов её решения в зависимости от вашей версии и потребностей. Выбрав подходящий метод под вашу версию Excel и конкретные задачи, вы сможете легко и быстро извлекать уникальные значения. Если вы хотите освоить ещё больше полезных приёмов и советов по работе в Excel, на нашем сайте доступны тысячи обучающих материалов.
Другие связанные статьи:
- Подсчет количества уникальных и различных значений из списка
- Предположим, у вас есть длинный список значений, содержащий дубликаты, и вы хотите подсчитать либо количество уникальных значений (тех, что встречаются в списке только один раз), либо количество различных значений (все разные значения в списке, то есть уникальные значения плюс первые вхождения повторяющихся элементов), как показано на скриншоте слева. В этой статье рассказывается, как выполнить такую задачу в Excel.
- Суммирование уникальных значений на основе критериев в Excel
- Например, у вас есть диапазон данных, содержащий столбцы «Имя» и «Заказ». Теперь вы хотите просуммировать только уникальные значения в столбце «Заказ» на основе столбца «Имя», как показано на следующем скриншоте. Как быстро и легко решить эту задачу в Excel?
- Транспонирование ячеек одного столбца на основе уникальных значений другого столбца
- Предположим, у вас есть диапазон данных из двух столбцов, и вы хотите транспонировать ячейки одного столбца в горизонтальные строки на основе уникальных значений другого столбца — как показано в примере ниже. Есть ли у вас эффективные способы решить эту задачу в Excel?
- Объединение уникальных значений в Excel
- Если у меня есть длинный список значений с дубликатами, как быстро и легко найти только уникальные значения и объединить их в одну ячейку в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек