Пособие: как найти повторы во многих списках в Excel и Calc / Find duplicates in lists +теория
1. НАГЛЯДНЫЙ СПОСОБ
Достаточно частая задача — сравнение нескольких списков и выявление повторов между ними.
Допустим, у вас четыре списка (напр., email или товаров) и нужно понять, какие значения из каких списков пересекаются друг с другом (не усложняя Сводными таблицами, ВПР)?
Мне удобно это увидеть в виде битовой маски «0110», что будет означать, что конкретное значение есть только в списке 2 и списке 3.
Какими формулами это можно сделать в Excel/Calc?
(файл-пример см. внизу)
а) В колонку «C» вставляете первый список, в «D» второй, в «E» третий, «F» четвёртый.
б) В колонку «B» вставляете все списки один за другим.
в) В первой строке прописываете заголовки.
г) В ячейку «A2» вставляете формулу:
=СЧЁТЕСЛИ(C:C; B2) & СЧЁТЕСЛИ(D:D; B2) & СЧЁТЕСЛИ(E:E; B2) & СЧЁТЕСЛИ(F:F; B2)
или
=COUNTIF(C:C; B2) & COUNTIF(D:D; B2) & COUNTIF(E:E; B2) & COUNTIF(F:F; B2)
д) И, наконец, дважды щелкаете по правому нижнему углу ячейки «A2» (для автозаполнения).
Готово. С помощью битовой маски получаем понимание повторов.
Например, «0111» означает, что конкретный email/фрукт/показатель из колонки «B» повторятся во всех четырёх списках, кроме первого. «1000» означает, что значение только в первом списке.
ДОПОЛНИТЕЛЬНО-1: Если видите «0311», то можно понять, что во втором списке ещё и внутри списка дубликаты.
ДОПОЛНИТЕЛЬНО-2: Если видите «0000», значит явно закралась ошибка: в общем списке есть значение, которого нет ни в одном исходном списке.
2. АЛЬТЕРНАТИВА: ЕДИНЫЙ СПИСОК
Другой интересный способ — через условные веса.
Например, у нас аналогично четыре списка. Берём только одну колонку, куда засовываем значения вниз, допустим, в колонку «A» (или в textarea, если нет офисного пакета, но есть браузер).
Последний список копируем и вставляем 1 раз,
предпоследний список ниже вставляем 2 раза,
второй список копипастим 4 раза,
первый список копируем и вставляем вниз уже 8 раз подряд.
В общем виде подход для весов при N списках у нас идёт через удвоение 2^(N-1) (напр., при четырёх списках =2^(4-1)=2^3=8 "вес самого большого"), а возможных пересечений получается как 2^(N-1)*2-1 (напр., при четырёх списках =2^(4-1)*2-1=8*2-1=15 "непустых пересечений").
Дальше считаем повторы значений.
Полотно данных можно вставить, например, сюда: https://www.somacon.com/p568.php (если беспокоит приватность данных, скачайте страницу через CTRL+S и отключите Интернет, оно сработает локально!) (а можете использовать другой или самописный инструмент).
При четырёх списках каждое значение будет иметь число от «1» до «15».
Допустим, вы видите число «6». Удивительно, но если вы переведёте это в двоичную систему, то получите «0110» (или «110»). И это и будет значить, что это значение есть во втором и третьем списке, но нет ни в первом, ни в четвёртом!
«1» будет означать «0001» (или «1»), то есть значение имеется лишь в последнем списке,
«8» будет означать «1000», то есть лишь в первом списке,
«15» будет означать «1111», то есть значение имеется во всех списках!
СПРАВОЧНО-1: Держите двоичные числа с 0 до 31 (для пяти списков): 0, 1, 10, 11, 100, 101, 110, 111, 1000, 1001, 1010, 1011, 1100, 1101, 1110, 1111, 10000, 10001, 10010, 10011, 10100, 10101, 10110, 10111, 11000, 11001, 11010, 11011, 11100, 11101, 11110, 11111. Дальше логика счёта такая же!
СПРАВОЧНО-2: В Excel/Calc переход туда и обратно осуществляется с помощью формул «ДВ.В.ДЕС» («BIN2DEC») и «ДЕС.В.ДВ» («DEC2BIN»).
3. НАГЛЯДНЫЙ СПОСОБ EXCEL/CALC: СОВЕРШЕНСТВОВАНИЕ
Есть два сюжета.
> Во-первых, вы можете захотеть избежать ненаглядной конкатенации, то есть ненаглядного объединения текстовых строк,
когда у вас в одном списке оказалось десять и больше повторов.
Тогда формулу в ячейке «A2» можно сделать с пробелами, чтобы получить вид вроде «0 12 1 1»:
=СЧЁТЕСЛИ(C:C; B2) &" "& СЧЁТЕСЛИ(D:D; B2) &" "& СЧЁТЕСЛИ(E:E; B2) &" "& СЧЁТЕСЛИ(F:F; B2)
или
=COUNTIF(C:C; B2) &" "& COUNTIF(D:D; B2) &" "& COUNTIF(E:E; B2) &" "& COUNTIF(F:F; B2)
> Во-вторых, вы можете захотеть иметь только нули и единицы (если вам важно сохранять двоичную систему и вы не пытаетесь смотреть на повторы в самих списках):
=--(СЧЁТЕСЛИ(C:C; B2)>0) & --(СЧЁТЕСЛИ(D:D; B2)>0) & --(СЧЁТЕСЛИ(E:E; B2)>0) & --(СЧЁТЕСЛИ(F:F; B2)>0)
или
=--(COUNTIF(C:C; B2)>0) & --(COUNTIF(D:D; B2)>0) & --(COUNTIF(E:E; B2)>0) & --(COUNTIF(F:F; B2)>0)
Тут двойной минус перед скобкой превращает ИСТИНА (TRUE) в 1, а ЛОЖЬ (FALSE) в 0.
Если же оставляете анализ повторов внутри каждого списка, что бывает полезно, то с помощью фильтров/регулярных выражений типа «[2-9]» или вопросительного знака можно отыскивать значения больше единицы, но сюжет фильтров — отдельная крупная тема, без которой
при сравнении повторов любого количества списков
вполне можно обойтись!
Таким образом, здесь мы рассмотрели,
как удобно находить пересечения между несколькими заданными списками
и снизить вероятность ошибок/пропусков при их анализе.
> ПРИЛОЖЕНИЕ: Ссылка на файл (.xlsx)
Делитесь своими полезными советами!

















