Excel. Сравнить списки, найти элементы, которые встречаются во всех списках

Question

Например, у меня есть 3 списка из одного столбца. Каждый список содержит только уникальные значения. Списки не упорядочены. Я хочу определить значения, которые встречаются в каждом из 3 списков.

Ааа ааа ааа
BBB BBBB CCC
CCC CCC BBB

Answer 1

Этот ответ предназначен для пользователей, которые не являются экспертами по всем функциям формул Excel (как я).

Одно простое решение - подсчитать, сколько раз элементы в столбце A были найдены во всех столбцах (дубликаты не допускаются ни в одном столбце).

В ячейке D1 = COUNTIF($ A $ 1:$ C $ 3, A1) Это подсчитывает количество раз, когда «AAA» встречается в 3 столбцах. Ответ 3

В ячейке D2 = COUNTIF($ A $ 1:$ C $ 3, B1)
Это подсчитывает, сколько раз в трех столбцах встречается «BBB». Ответ 2

В ячейке D3 = COUNTIF($ A $ 1:$ C $ 3, C1) = 3 Это подсчитывает количество раз, когда «CCC» встречается в 3 столбцах. Ответ 3

Если у вас длинный список, вы можете применить данные, фильтр и просто выбрать строки с общим количеством 3.

Answer 2

Вот отличная возможность поговорить о функциях массива!

Введите эту формулу в D1 и введите ее, нажав CTRL-Shift Enter. Если введено правильно, формула будет заключена в фигурные скобки {}:

=LARGE((A$1:A$20=B$1:B$20)*(B$1:B$20=C$1:C$20)*ROW(A$1:A$20),ROW())

Затем нажмите на D1 и заполните, пока не увидите нулевой результат. Это дает номера строк, где столбцы A, B и C совпадают.

Вот как это работает: выражение A $ 1: A $ 20 = B $ 1: B $ 20 спрашивает, равен ли диапазон A1: A20 диапазону B1: B20, и возвращает массив значений TRUE/FALSE:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

Массив содержит TRUE в каждой позиции, где столбец A равен столбцу B. Здесь это позиции 10, 17 и 20.

Выражение B $ 1:B $ 20 = C $ 1:C $ 20 выполняет аналогичную операцию для столбцов B и C. Умножение этих двух массивов значений TRUE/FALSE выполняет эквивалент операции AND(), а также преобразует TRUE и FALSE в 1 и 0 соответственно.

Итак, теперь у нас есть массив, который имеет 0 везде, кроме позиций, где столбцы A, B и C равны {0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0} и умножение на ROW(A$1:A$20) (массив номеров строк) дает массив, содержащий номера строк, в которых столбцы A, B и C равны:{0;0;0;0;0;0;0;0;0;10;0;0;0;0;0;0;17;0;0;0}

Теперь осталось только извлечь номера строк. Функция LARGE(array, n) возвращает n-е наибольшее значение в массиве. Формула здесь использует ROW() в качестве n, поэтому, когда она заполнена, она возвращает первое по величине значение в строке 1, второе по величине в строке 2 и т.д.

Надеюсь, вам понравилось. Удачи.