Извлечение уникальных значений на основе одного или нескольких критериев в 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» > «Супер ПОИСК» > «Один-ко-многим Поиск (возвращает несколько результатов)», чтобы открыть диалоговое окно. В диалоговом окне укажите операции следующим образом:
- Выберите «Область размещения списка» и «Диапазон значений для поиска» в текстовых полях отдельно;
- Выберите диапазон таблицы, который вы хотите использовать;
- Укажите ключевой столбец и столбец возврата из выпадающих списков «Ключевой столбец» и «Столбец возврата» соответственно;
- Наконец, нажмите кнопку OK.
Результат:
Все уникальные имена на основе критериев извлечены в одну ячейку, см. скриншот:
• С формулой в 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.
- *: Объединяет два условия с логикой AND, то есть оба условия должны быть истинными, чтобы строка была включена.
- UNIQUE(...):
Удаляет дубликаты из фильтрованных результатов, гарантируя, что вывод содержит только уникальные значения.
Извлечение уникальных значений из списка ячеек с помощью Kutools для Excel
Иногда вам может потребоваться извлечь уникальные значения из списка ячеек. Здесь я рекомендую полезный инструмент, Kutools для Excel. Его утилита «Извлечение ячеек с уникальными значениями (включая первый дубликат)» позволяет быстро извлекать уникальные значения.
1. Щелкните ячейку, куда вы хотите вывести результат. (Примечание: Не выбирайте ячейку в первой строке.)
2. Затем нажмите «Kutools» > «Помощник формул» > «Помощник формул», см. скриншот:
3. В диалоговом окне «Помощник формул» выполните следующие операции:
- Выберите опцию «Текст» из выпадающего списка «Тип формулы»;
- Затем выберите «Извлечение ячеек с уникальными значениями (включая первый дубликат)» из списка «Выберите формулу»;
- В правой секции «Ввод аргументов» выберите список ячеек, из которых вы хотите извлечь уникальные значения.
4. Затем нажмите кнопку Ok, первый результат отобразится в ячейке, затем выберите ячейку и протяните маркер заполнения по ячейкам, куда вы хотите перечислить все уникальные значения, пока не появятся пустые ячейки, см. скриншот:
Извлечение уникальных значений на основе критериев в 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% и сократите сотни лишних кликов мышью каждый день!