Как найти общие значения в трёх столбцах Excel?
Работа с данными в Excel часто включает сравнение списков для выявления общих или дублирующихся записей. Хотя поиск совпадений между двумя столбцами — задача довольно распространённая, иногда возникает необходимость определить значения, присутствующие одновременно в трёх отдельных столбцах. Например, при объединении данных опросов, сверке продаж или анализе дубликатов в нескольких списках критически важно точно выявить элементы, встречающиеся во всех трёх столбцах, как показано на рисунке ниже. В этой статье представлены несколько практических методов решения этой задачи в Excel — от формул до VBA, — позволяющих надёжно и эффективно находить общие значения в трёх столбцах независимо от вашего предпочтительного подхода.
Поиск общих значений в 3 столбцах с помощью формул массива
Макрос VBA для извлечения значений, присутствующих во всех трёх столбцах
Поиск общих значений в 3 столбцах с помощью формул массива
Чтобы найти и извлечь общие значения из трёх столбцов, используйте формулы массива, предназначенные для поиска элементов, присутствующих во всех выбранных диапазонах. Это особенно полезно при работе с наборами данных, где вы предпочитаете обойтись без дополнительных надстроек Excel или внешних инструментов.
Введите эту формулу массива в пустую ячейку, где вы хотите отобразить первое общее значение:
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>,0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>,0,1,0)=2,0,1),0))))Как использовать эту формулу массива:
- После ввода формулы в выбранную ячейку нажмите Shift + Ctrl + Enter (а не просто Enter). Excel автоматически заключит формулу в фигурные скобки, чтобы показать, что это формула массива.
- Протяните формулу вниз по столбцу до появления пустых ячеек — так вы получите полный список значений, общих для всех трёх столбцов, а пустые ячейки укажут на отсутствие дальнейших совпадений.
Примечания и пояснения параметров:
- Если вы предпочитаете другую формулу массива, вот ещё одна — она также возвращает все уникальные значения, присутствующие во всех трёх столбцах:
Напоминаем ещё раз: после ввода или вставки формулы нажмите Shift + Ctrl + Enter.=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>,0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>,0,1,0)=2,0,1),0)) - В этих формулах:
- A2:A10, B2:B8, C2:C9 — это диапазоны в каждом из трёх столбцов, которые вы хотите сравнить.
- E1 указывает на ячейку непосредственно над той, где начинается ваша формула (для логики исключения). Настройте ссылки на ячейки в соответствии с вашим фактическим диапазоном и местом, куда вы хотите поместить результаты.
- Эти методы отлично справляются с умеренными объёмами данных, но при работе с очень большими наборами могут замедляться из-за высоких вычислительных затрат, связанных с формулами массива.
- Будьте внимательны: не изменяйте исходный диапазон в процессе работы, чтобы избежать неточных результатов или ошибок в формуле.
- Если результат содержит пустые строки, это означает, что все общие значения уже извлечены, а в остальных ячейках пересечений больше нет.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Макрос VBA для извлечения значений, присутствующих во всех трёх столбцах
Если вы предпочитаете автоматизированный подход без необходимости вручную вводить или копировать сложные формулы, воспользуйтесь макросом VBA: он последовательно обработает ваши данные и выведет только те значения, которые присутствуют во всех трёх столбцах. Этот метод особенно удобен для работы с очень большими наборами данных или динамическими диапазонами — VBA эффективнее справляется с повторяющимися задачами и пользовательскими условиями.
1. Нажмите Разработчик > Visual Basic, чтобы открыть редактор VBA (если вкладка)Разработчик не отображается, её можно включить через Файл > Параметры > Настроить ленту).
2. В редакторе VBA выберите Вставка > Модуль, чтобы создать новый модуль. Затем вставьте приведённый ниже код в окно модуля:
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub3. В окне VBA с выбранным модулем нажмите F5 или кнопку Выполнить (▶), чтобы запустить код. После этого последовательно появятся запросы на выбор каждого из трёх диапазонов столбцов для сравнения. На каждом этапе используйте мышь, чтобы выделить нужные ячейки.
4. Макрос обработает ваш выделенный диапазон и выведет все значения, присутствующие одновременно в трёх столбцах, в первый пустой столбец справа от текущего набора данных — начиная с первой строки.
Этот метод отлично подходит для работы с большими или динамическими наборами данных и легко масштабируется на четыре и более столбцов — достаточно продублировать логику словаря. Обязательно сохраняйте книгу перед запуском макросов: если потребуется вернуться к предыдущему состоянию, несохранённые изменения отменить уже не получится.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек