Сравнить два столбца со списком сотрудников, ответами опроса и заказами

Отдел кадров разослал опрос девяти сотрудникам, ответили семеро, и теперь нужно понять, кому напомнить. Глазами такие списки сверяют с ошибками, а формула делает это за секунды: справка Microsoft описывает сверку двух столбцов формулой с ПОИСКПОЗ, которая возвращает значение, если оно есть во втором столбце^[2]. Та же задача встречается при сверке заказов с оплатами и двух прайсов.

Чтобы сравнить два столбца, найдите ключевой столбец в обоих списках — фамилию, артикул или номер заказа. Выделите первую ячейку рядом с первым списком, введите формулу СЧЁТЕСЛИ, нажмите Enter и протяните её вниз, затем проверьте метки ИСТИНА и ЛОЖЬ. Если метки неожиданные, исправьте ключи и примените ту же формулу к своим данным.

Ключевые факты: сравнить два столбца

• Сверка двух столбцов отвечает на вопрос, есть ли значение из первого списка во втором, и ставит метку ИСТИНА или ЛОЖЬ.
• СЧЁТЕСЛИ даёт неверный результат на строках длиннее 255 символов^[3].
• Справка Microsoft описывает сверку двух столбцов формулой с ПОИСКПОЗ для Excel 2016, 2019, 2021, 2024 и Microsoft 365^[2].
• СЧЁТЕСЛИ и ПОИСКПОЗ не учитывают регистр букв^[1].
• СОВПАД отличается от простого равенства тем, что различает заглавные и строчные буквы^[4].

Как сравнить два столбца формулой СЧЁТЕСЛИ: есть ли значение во втором списке

Сравнение двух списков формулой СЧЁТЕСЛИ отвечает, встречается ли фамилия во втором столбце. Формула =СЧЁТЕСЛИ($B$2:$B$8;A2)>0 возвращает ИСТИНА, если значение из A2 есть во втором списке, и ЛОЖЬ, если его там нет. По справке Microsoft критерии СЧЁТЕСЛИ не учитывают регистр^[3].

1. Выберите ячейку C2 и введите =СЧЁТЕСЛИ($B$2:$B$8;A2)>0.
2. Закрепите диапазон знаками доллара, чтобы он не сдвигался при протягивании.
3. Протяните формулу до C10.
4. Проверьте метки: у Гусейновой, Зуевой и Лебедева стоит ЛОЖЬ^[5].

Если протянули не туда, Ctrl+Z отменит последнее действие^[6].

Чтобы сравнить два столбца в Google Таблицах, подойдёт та же формула: COUNTIF там тоже не учитывает регистр и принимает одно условие^[7]. Сверьте ответы опроса в тренажёре: урок ставит статусы, красит общие фамилии и собирает список, кому напомнить.

Разобравшись с метками, переходим к другим способам — они нужны, когда важна позиция или регистр.

Чем ПОИСКПОЗ, СОВПАД и построчная проверка отличаются друг от друга

Сравнение через ПОИСКПОЗ показывает позицию совпадения, а СОВПАД ещё и учитывает регистр. По справке Microsoft ПОИСКПОЗ с типом сопоставления 0 возвращает позицию первого точного совпадения и не требует сортировки, а при промахе даёт #Н/Д^[1]. Справка о сравнении столбцов строит на ней формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$C$1:$C$5;0));"";A1): найденное значение выводится, ненайденное — пустая ячейка^[2].

Понятный статус даёт =ЕСЛИ(ЕНД(ПОИСКПОЗ(A2;$B$2:$B$8;0));"нет в опросе";"в обоих"): для Гусейновой формула вернёт «нет в опросе»^[5]. СОВПАД учитывает регистр, но игнорирует форматирование: «Слово» и «слово» для неё разные, как и «сл ово» и «слово»^[4]. Сравнить два столбца построчно можно и простым равенством, но «Бета» и «бета» в примере оно считает одинаковыми, а СОВПАД — нет^[5].

Однако метки в каждой строке — ещё не готовый список тех, кому напомнить.

Как вывести список отличий и подсветить общие значения

Сравнение списков выводит перечень отличий формулой ФИЛЬТР с условием СЧЁТЕСЛИ равно 0. ФИЛЬТР с условием СЧЁТЕСЛИ равно 0 выводит тех, кого нет во втором списке: =ФИЛЬТР(A2:A10;СЧЁТЕСЛИ(B2:B8;A2:A10)=0) покажет Гусейнову, Зуеву и Лебедева, а обратная =ФИЛЬТР(B2:B8;СЧЁТЕСЛИ(A2:A10;B2:B8)=0) — Литвинова, которого нет в первом списке^[5].

По справке Microsoft у ФИЛЬТР три аргумента — массив, включить и если_пусто, — и без третьего пустой результат даёт ошибку #CALC!^[8]. Число общих фамилий считает =СУММПРОИЗВ(--(СЧЁТЕСЛИ(B2:B8;A2:A10)>0)): в примере это 6^[5].

Подсветка обходится без формул: выделите оба столбца и выберите «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения»^[9]. Окрасятся фамилии, которые встречаются в выделении больше одного раза, то есть есть в обоих списках, — если внутри каждого списка повторов нет.

Остаётся понять, в каких случаях сравнение формулой даёт неверный ответ.

Когда сравнение формулой не подойдёт

Сравнение формулой не подойдёт, если ключ записан по-разному или регистр важен для результата. Каждое условие ниже проверяется до того, как протягивать формулу.

• Если в одном списке пробел в конце фамилии или непечатаемый знак, СЧЁТЕСЛИ даст неожиданный результат^[3] — сначала очистите ключи.
• Если сравниваемые строки длиннее 255 символов, СЧЁТЕСЛИ вернёт неверный результат^[3].
• При Excel 2019 или Excel 2016 формула ФИЛЬТР не работает: её нет в блоке «Применяется к»^[8] — список отличий собирают отбором по столбцу с ЛОЖЬ.
• Когда важен регистр, СЧЁТЕСЛИ и ПОИСКПОЗ не подойдут: регистр они не учитывают^[1] — нужна СОВПАД.
• Если ключ состоит из двух полей, например фамилии и отдела, одно поле не годится: склейте поля в общий ключ в обоих списках.

Справка Microsoft описывает ПОИСКПОЗ и СОВПАД для Excel 2016, 2019, 2021, 2024 и Microsoft 365, а версии 2021, 2024 и Microsoft 365 — и для Mac^[4].

Пробелы в ключах убирают заранее — способы собраны в теме о пробелах в ячейках и числах. Остальное — в других темах тренажёра о работе с таблицами.