Как сравнить два столбца или списка в Google Таблицах

Сравнение двух столбцов в Google Таблицах – одна из тех задач, которые встречаются регулярно, но решить которую с первого раза без подготовки бывает непросто. Нужно понять, какие значения есть в обоих столбцах одновременно, какие встречаются только в одном из них, а где данные отличаются, хоть и выглядят похоже. Сделать это вручную реально только на совсем маленьких списках, а вот при работе с десятками и сотнями строк лучше доверить проверку формулам или условному форматированию. Именно об этих инструментах и пойдет речь.

Сравнение значений в строке при помощи функции ЕСЛИ

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

С этим справляется функция ЕСЛИ, которая проверяет условие и в зависимости от результата возвращает один из двух заданных ответов. Формула строится очень просто: задается условие равенства двух ячеек, а дальше указывается, что выводить при совпадении и что при различии.

1. Кликните по свободной ячейке в той же строке, что и первые значения сравниваемых столбцов. Удобнее всего разместить формулу в столбце C, чтобы результат был сразу рядом с исходными данными.

2. Введите формулу =ЕСЛИ(A2=B2;"Совпадает";"Не совпадает") и нажмите Enter.

3. Наведите курсор на правый нижний угол ячейки C2 – там появится маленький крестик. Потяните его вниз до ячейки C6, чтобы скопировать формулу на все строки.

Теперь в столбце C напротив каждой пары будет написано, совпадают строки или нет. Текст «Совпадает» и «Не совпадает» произвольный, его можно заменить на любой другой, например, на «+» и «–», если нужен более компактный вывод.

Однако учитывайте, что такой метод сравнивает строго попарно: значение A2 сравнивается только с B2, A3 – только с B3 и так далее. Если в списках одинаковые значения есть, но расположены они в разных строках, этот способ покажет несовпадение. Для сравнения именно содержимого списков без привязки к порядку строк подойдет другой подход, который разобран ниже.

Учет регистра при сравнении: функция СОВПАД

Функция ЕСЛИ считает значения «Яблоко» и «яблоко» одинаковыми, поскольку при сравнении регистр букв не учитывается. Если это важно, например, при работе с кодами, артикулами или паролями, нужно использовать функцию СОВПАД, которая делает именно строгое посимвольное сравнение. Принцип тот же, что и с ЕСЛИ, только вместо простого условия равенства внутрь подставляется СОВПАД с двумя ячейками в качестве аргументов.

Введите в ячейку C2 следующую формулу и протяните ее вниз до C6:

=ЕСЛИ(СОВПАД(A2;B2);"Совпадает";"Не совпадает")

Теперь строки «Яблоко» и «яблоко» будут распознаны как разные значения, хотя визуально они почти идентичны. Для большинства бытовых задач это излишне, но там, где точность критична, такой подход незаменим.

Что делать, если значения выглядят одинаково, но формула говорит иначе

Довольно распространенная ситуация: в обоих столбцах визуально одно и то же слово, а ЕСЛИ упорно возвращает «Не совпадает». Чаще всего причина в лишних пробелах. Они появляются, когда данные копируются из разных источников: в одной ячейке значение «Яблоко», а в другой – «Яблоко » с пробелом в конце или « Яблоко» с пробелом в начале. Глазом такое не заметить, но формула воспринимает это как разные строки.

Исправляется это функцией СЖПРОБЕЛЫ, которая убирает все лишние пробелы по краям строки и лишние пробелы внутри нее, оставляя только одиночные между словами. Проще всего обернуть ею обе сравниваемые ячейки прямо внутри формулы, не меняя исходные данные:

=ЕСЛИ(СЖПРОБЕЛЫ(A2)=СЖПРОБЕЛЫ(B2);"Совпадает";"Не совпадает")

Если после этого результат все равно неожиданный, стоит проверить, нет ли разницы в невидимых символах, например, неразрывных пробелах, которые тоже часто попадают при копировании из браузера или PDF. В таком случае поможет функция ПЕЧСИМВ, которая удаляет непечатаемые символы. Ее можно использовать вместе со СЖПРОБЕЛЫ:

=ЕСЛИ(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2))=СЖПРОБЕЛЫ(ПЕЧСИМВ(B2));"Совпадает";"Не совпадает")

Выделение совпадений и различий через условное форматирование

Формулы в отдельном столбце хорошо работают, когда нужны текстовые пометки. Однако иногда удобнее просто видеть совпадения или отличия визуально – прямо в исходных данных. Для этого в Google Таблицах есть условное форматирование, которое меняет цвет ячеек в зависимости от выполнения заданного условия.

Начнем с самого распространенного случая: когда нужно выделить строки, где значения в обоих столбцах совпадают. Для этого понадобится выделить оба столбца сразу и задать правило с формулой, которая будет проверять каждую строку.

1. Выделите диапазон A2:B6 (оба столбца целиком).

2. В верхнем меню откройте «Формат» – «Условное форматирование».

3. В открывшейся панели убедитесь, что в поле «Применить к диапазону» указано A2:B6.

4. В выпадающем списке «Форматировать ячейки, если...» выберите «Ваша формула».

5. Введите формулу =$A2=$B2 в поле для формулы. Знак доллара перед буквой столбца здесь обязателен, он фиксирует ссылку так, чтобы при проверке каждой строки сравнение всегда шло между A и B, а не сдвигалось вправо.

6. Выберите цвет заливки, например, светло-зеленый, и нажмите «Готово».

Теперь все строки, где значения в обоих столбцах одинаковы, подсветятся выбранным цветом.

Второй сценарий чуть более точечный – нужно найти и выделить только те значения в столбце A, которых нет в столбце B вообще. Это удобно, когда списки перемешаны и нужно быстро увидеть, что именно отсутствует в одном из них.

1. Выделите только диапазон A2:A6.

2. Откройте «Формат» – «Условное форматирование».

3. Выберите «Ваша формула» и введите =СЧЁТЕСЛИ($B$2:$B$6;A2)=0. Формула проверяет, сколько раз текущее значение из столбца A встречается в столбце B, и подсвечивает ячейку, если ответ – ноль, то есть совпадений нет вообще.

4. Задайте цвет заливки, например, светло-красный, и нажмите «Готово».

Выделенными окажутся те ячейки столбца A, значения которых не встречаются в столбце B ни разу. Если нужно сделать то же самое для столбца B, повторите процедуру для диапазона B2:B6, поменяв в формуле местами ссылки на столбцы.

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

Когда порядок значений в столбцах разный, простое построчное сравнение не дает нужного результата. Функция СЧЁТЕСЛИ считает, сколько раз конкретное значение встречается в заданном диапазоне. Если результат больше нуля, значение есть в обоих столбцах, а если ноль – его нет.

=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$6;A2)>0;"Есть в B";"Нет в B").

Обратите внимание, что диапазон B2:B6 зафиксирован знаками доллара с обеих сторон – это необходимо, чтобы при копировании формулы вниз ссылка на столбец B не сдвигалась. Если нужно узнать не просто факт наличия, а сколько именно раз значение из A встречается в B, достаточно использовать саму функцию без обертки в ЕСЛИ: =СЧЁТЕСЛИ($B$2:$B$6;A2).

Важно учитывать, что СЧЁТЕСЛИ, как и обычная ЕСЛИ, не различает регистр букв. Если в столбце A значение «яблоко», а в столбце B – «Яблоко», формула сочтет их совпадением. Для проверки наличия с учетом регистра СЧЁТЕСЛИ не подходит, поэтому в таком случае придется использовать формулу массива на основе СОВПАД, что заметно усложняет конструкцию и выходит за рамки повседневных задач.

Поиск позиции совпадения: функция ПОИСКПОЗ

Бывает, что недостаточно просто знать, есть ли значение в другом столбце, а нужно понять, в какой именно строке оно находится. Это пригодится, когда списки длинные и нужно быстро сопоставить записи между собой.

Функция ПОИСКПОЗ возвращает номер позиции искомого значения в указанном диапазоне. Если значение не найдено, формула возвращает ошибку, поэтому ее стоит обернуть в ЕСЛИОШИБКА, чтобы вместо некрасивого сообщения отображался понятный текст.

Введите в ячейку C2 следующую формулу и протяните ее вниз до C6:

=ЕСЛИОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$6;0);"Не найдено")

Теперь напротив каждого значения из столбца A будет указан порядковый номер его позиции внутри диапазона B2:B6. Например, если значение из A2 совпадает с третьей ячейкой столбца B, в C2 появится цифра 3. Если совпадений нет, появится текст «Не найдено». Ноль в конце формулы означает поиск точного совпадения, что в большинстве случаев именно то, что нужно.

Заключение

Все описанные варианты решают задачу сравнения, но каждый в своем сценарии. Если важно сравнить значения построчно и получить пометки рядом, подойдет функция ЕСЛИ. Когда регистр букв имеет значение, ЕСЛИ заменяется на СОВПАД. Для визуального выделения совпадений или отличий прямо в таблице логичнее воспользоваться условным форматированием – оно не добавляет лишних столбцов и сразу дает наглядную картину. 

СЧЁТЕСЛИ выручает, когда порядок строк в списках разный и нужно просто проверить, есть ли значение в другом столбце. При этом ПОИСКПОЗ пригодится, если важно знать конкретную позицию совпадения для дальнейшей работы с данными.

Изображение на обложке: Flaticon