Как извлечь уникальные значения из нескольких столбцов в Excel?
Предположим, у вас есть несколько столбцов с несколькими значениями, некоторые значения повторяются в одном или другом столбце. И теперь вы хотите найти значения, которые присутствуют в любом столбце только один раз. Есть ли какие-нибудь уловки для извлечения уникальных значений из нескольких столбцов в Excel?
Извлекайте уникальные значения из нескольких столбцов с помощью формул
В этом разделе будут рассмотрены две формулы: одна с использованием формулы массива, подходящей для всех версий Excel, а другая с использованием формулы динамического массива специально для Excel 365.
Извлекайте уникальные значения из нескольких столбцов с помощью формулы массива для всех версий Excel.
Для пользователей любой версии Excel формулы массива могут стать мощным инструментом для извлечения уникальных значений из нескольких столбцов. Вот как вы можете это сделать:
1. Предполагая, что ваши значения находятся в диапазоне A2: C9, введите следующую формулу в ячейку E2:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
2, Затем нажмите Shift + Ctrl + Ввод вместе, а затем перетащите маркер заполнения, чтобы извлечь уникальные значения, пока не появятся пустые ячейки. Смотрите скриншот:
- 2 австралийский доллар: 9 канадских долларов: указывает диапазон данных, который необходимо проверить, то есть ячейки от A2 до C9.
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
- $A$2:$C$9<>"" проверяет, не пусты ли ячейки в диапазоне.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 определяет, не были ли значения этих ячеек еще перечислены в диапазоне ячеек от E1 до E1.
- Если оба условия соблюдены (т. е. значение не пусто и еще не указано в столбце E), функция ЕСЛИ вычисляет уникальное число на основе своей строки и столбца (ROW($2:$9)*100+COLUMN($A: $С)).
- Если условия не выполняются, функция возвращает большое число (7^8), которое служит заполнителем.
- МИН(...): находит наименьшее число, возвращаемое функцией ЕСЛИ, указанной выше, соответствующее местоположению следующего уникального значения.
- ТЕКСТ(...,"R0C00"): Преобразует это минимальное число в адрес в стиле R1C1. Код формата R0C00 указывает на преобразование числа в формат ссылки на ячейку Excel.
- КОСВЕННЫЙ(...): использует функцию ДВССЫЛ для преобразования адреса в стиле R1C1, сгенерированного на предыдущем шаге, обратно в обычную ссылку на ячейку в стиле A1. Функция ДВССЫЛ позволяет ссылаться на ячейку на основе содержимого текстовой строки.
- &"": добавление &"" в конец формулы гарантирует, что окончательный вывод будет обработан как текст, поэтому четные числа будут отображаться как текст.
Извлекайте уникальные значения из нескольких столбцов с помощью формулы для Excel 365
Excel 365 поддерживает динамические массивы, что значительно упрощает извлечение уникальных значений из нескольких столбцов:
Введите или скопируйте следующую формулу в пустую ячейку, в которую вы хотите поместить результат, а затем нажмите кнопку Enter ключ для получения всех уникальных значений одновременно. Смотрите скриншот:
=UNIQUE(TOCOL(A2:C9,1))
Извлекайте уникальные значения из нескольких столбцов с помощью Kutools AI Aide
Познай силу Kutools AI Помощник для беспрепятственного извлечения уникальных значений из нескольких столбцов Excel. Всего за несколько кликов этот интеллектуальный инструмент анализирует ваши данные, определяя и перечисляя уникальные записи в любом выбранном диапазоне. Забудьте о сложных формулах или коде VBA; Оцените эффективность Kutools AI Помощник и превратите рабочий процесс Excel в более продуктивный и безошибочный.
После установки Kutools for Excel, нажмите Кутулс ИИ > AI-помощник для открытия Kutools AI Помощник панель:
- Введите свое требование в окно чата и нажмите Отправить или нажмите Enter ключ для отправки вопроса;
«Извлеките уникальные значения из диапазона A2:C9, игнорируя пустые ячейки, и поместите результаты, начиная с E2:» - После анализа нажмите Выполнить кнопка для запуска. Kutools AI Aide обработает ваш запрос с использованием AI и вернет результаты в указанную ячейку непосредственно в Excel.
Извлекайте уникальные значения из нескольких столбцов с помощью сводной таблицы
Если вы знакомы со сводной таблицей, вы можете легко извлечь уникальные значения из нескольких столбцов, выполнив следующие действия:
1. Сначала вставьте один новый пустой столбец слева от ваших данных, в этом примере я вставлю столбец A рядом с исходными данными.
2. Щелкните одну ячейку в ваших данных и нажмите Alt + D клавиши, затем нажмите P ключ немедленно открыть Мастер сводных таблиц и диаграмм, выберите Несколько диапазонов консолидации в мастере step1 см. снимок экрана:
3. Затем нажмите Следующая кнопку, проверьте Создайте для меня одностраничное поле вариант на шаге 2 мастера, см. снимок экрана:
4. Продолжайте нажимать Следующая нажмите кнопку, щелкните, чтобы выбрать диапазон данных, который включает левый новый столбец ячеек, затем щелкните Добавить кнопку, чтобы добавить диапазон данных в Все диапазоны список, см. снимок экрана:
5. После выбора диапазона данных продолжайте нажимать Следующая, на шаге 3 мастера выберите, где вы хотите разместить отчет сводной таблицы, как хотите.
6. Наконец, нажмите Завершить чтобы завершить работу мастера, и на текущем листе была создана сводная таблица, затем снимите отметки со всех полей в Выберите поля для добавления в отчет раздел, см. снимок экрана:
7. Затем проверьте поле Значение или перетащите Значение в Ряды label, теперь вы получите уникальные значения из нескольких столбцов следующим образом:
Извлечение уникальных значений из нескольких столбцов с кодом VBA
С помощью следующего кода VBA вы также можете извлечь уникальные значения из нескольких столбцов.
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
VBA: извлечение уникальных значений из нескольких столбцов
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3, Затем нажмите F5 для запуска этого кода, и появится диалоговое окно с напоминанием о выборе диапазона данных, который вы хотите использовать. Смотрите скриншот:
4, Затем нажмите OKпоявится другое окно подсказки, в котором вы сможете выбрать место для размещения результата, см. снимок экрана:
5. Нажмите OK чтобы закрыть это диалоговое окно, и все уникальные значения были извлечены сразу.
Больше относительных статей:
- Подсчитайте количество уникальных и отличных значений из списка
- Предположим, у вас есть длинный список значений с некоторыми повторяющимися элементами, теперь вы хотите подсчитать количество уникальных значений (значения, которые появляются в списке только один раз) или отдельных значений (все разные значения в списке, это означает уникальные значения + 1-е повторяющиеся значения) в столбце, как показано на скриншоте слева. В этой статье я расскажу о том, как справиться с этой задачей в Excel.
- Извлечение уникальных значений на основе критериев в Excel
- Предположим, у вас есть следующий диапазон данных, в котором вы хотите перечислить только уникальные имена столбца B на основе определенного критерия столбца A, чтобы получить результат, как показано ниже. Как можно быстро и легко справиться с этой задачей в Excel?
- Разрешить только уникальные значения в Excel
- Если вы хотите сохранить только уникальные значения, вводимые в столбец рабочего листа, и предотвратить дублирование, в этой статье будут представлены некоторые быстрые приемы, которые помогут вам справиться с этой задачей.
- Суммируйте уникальные значения на основе критериев в Excel
- Например, у меня есть диапазон данных, который содержит столбцы Name и Order, теперь для суммирования только уникальных значений в столбце Order на основе столбца Name, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!