Чтобы сравнить два столбца построчно, введите =B2=C2 рядом с первой строкой и протяните вниз: ИСТИНА означает, что две ячейки совпадают, ЛОЖЬ, что различаются. Чтобы найти значения одного столбца, которые встречаются где-нибудь в другом столбце, в любом порядке, используйте =СЧЁТЕСЛИ($B$2:$B$8;A2)>0. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой, как здесь.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Old | New | Same? | Status |
| 2 | Apple | $1.20 | $1.20 | TRUE | Same |
| 3 | Pear | $1.50 | $1.60 | FALSE | Changed |
| 4 | Carrot | $0.80 | $0.80 | TRUE | Same |
| 5 | Bread | $2.40 | $2.20 | FALSE | Changed |
| 6 | Milk | $1.10 | $1.10 | TRUE | Same |
| 7 | Cheese | $4.50 | $4.90 | FALSE | Changed |
=B2=C2D3, D5 и D7 равны ЛОЖЬ, и правило условного форматирования =$B2<>$C2 закрашивает эти три строки. Столбец E показывает ту же проверку словами вместо ИСТИНА и ЛОЖЬ. Замените C3 на 1.5, и строка 3 станет Same.
Сравнить два столбца через ЕСЛИ
=B2=C2 возвращает ИСТИНА или ЛОЖЬ. Оберните её в ЕСЛИ, чтобы выбрать слова: =ЕСЛИ(B2=C2;"Same";"Changed"), как в столбце E выше. Чтобы оставить совпадающие строки пустыми и отмечать только различия, используйте =ЕСЛИ(B2<>C2;"Changed";""). Чтобы показать, насколько изменилось число, вычтите вместо сравнения: =C2-B2.
Чтобы посчитать различия без вспомогательного столбца, сравните два диапазона внутри СУММПРОИЗВ: =СУММПРОИЗВ(--(B2:B7<>C2:C7)) возвращает 3 для таблицы выше.
Без формулы: выделите B2:C7 так, чтобы активной была B2, выберите Главная > Найти и выделить > Выделить группу ячеек, отметьте отличия по строкам и нажмите «ОК» (в Windows то же делает Ctrl+). Excel выделит C3, C5 и C7, ячейки, которые отличаются от столбца B в своей строке; задайте им цвет заливки, чтобы отметить.
Сравнение с учётом регистра через СОВПАД
Сравнение через = не учитывает регистр: ab12 равно AB12. Когда регистр важен (коды товаров, пароли, идентификаторы), используйте СОВПАД(A2;B2) (по-английски EXACT), которая даёт ИСТИНА, только когда два текста одинаковы символ в символ.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Entered | Equal? | EXACT |
| 2 | AB12 | AB12 | TRUE | TRUE |
| 3 | CD34 | cd34 | TRUE | FALSE |
| 4 | EF56 | EF56 | TRUE | TRUE |
| 5 | GH78 | Gh78 | TRUE | FALSE |
=A2=B2Столбец C, сравнение через =, говорит, что все четыре совпадают. СОВПАД говорит, что строки 3 и 5 различаются, потому что в cd34 и Gh78 есть строчные буквы.
Найти значения одного столбца, которых нет в другом
Когда два списка идут в разном порядке, сравнивайте каждое значение со всем другим столбцом. СЧЁТЕСЛИ($B$2:$B$8;A2) считает, сколько раз A2 встречается в B2:B8, поэтому >0 означает «найдено», а =0 «нет». Знаки $ фиксируют диапазон поиска, когда формула протягивается вниз.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | In February? | With MATCH |
| 2 | Ana | Dan | TRUE | TRUE |
| 3 | Ben | Fay | TRUE | TRUE |
| 4 | Cara | Ana | FALSE | FALSE |
| 5 | Dan | Gus | TRUE | TRUE |
| 6 | Eve | Hal | FALSE | FALSE |
| 7 | Fay | Ivy | TRUE | TRUE |
| 8 | Gus | Ben | TRUE | TRUE |
=СЧЁТЕСЛИ($B$2:$B$8;A2)>0У Cara и Eve ЛОЖЬ: они покупали в январе, но не в феврале. ПОИСКПОЗ даёт тот же ответ другим путём: ПОИСКПОЗ(A2;$B$2:$B$8;0) возвращает позицию A2 в столбце B или #Н/Д (по-английски #N/A), если её там нет, а ЕЧИСЛО превращает это в ИСТИНА или ЛОЖЬ. Таблицы на этой странице показывают ошибки под английскими именами. Чтобы проверить в обратную сторону (новые клиенты февраля), поставьте ту же формулу рядом со столбцом B, поменяв диапазоны местами: =СЧЁТЕСЛИ($A$2:$A$8;B2)>0.
Сравнить два списка и вернуть соответствующее значение
Часто вопрос не только «есть ли оно», но и «совпадает ли значение рядом с ним». Здесь счета сравниваются со списком платежей, который идёт в другом порядке: ПРОСМОТРX находит каждый счёт среди платежей и возвращает оплаченную сумму, а столбец D сравнивает её с суммой счёта.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Amount | Paid | Match? | Payment for | Paid | |
| 2 | INV-101 | 120 | 120 | TRUE | INV-103 | 240 | |
| 3 | INV-102 | 85 | Not paid | FALSE | INV-101 | 120 | |
| 4 | INV-103 | 240 | 240 | TRUE | INV-105 | 140 | |
| 5 | INV-104 | 60 | 60 | TRUE | INV-106 | 95 | |
| 6 | INV-105 | 150 | 140 | FALSE | INV-104 | 60 | |
| 7 | INV-106 | 95 | 95 | TRUE |
=ПРОСМОТРX(A2;$F$2:$F$6;$G$2:$G$6;"Not paid")Для INV-102 платежа нет, поэтому C3 пишет Not paid. INV-105 оплачен на 140 вместо 150, поэтому D6 тоже ЛОЖЬ. Последний аргумент ПРОСМОТРX, "Not paid", заменяет #Н/Д, которую дало бы отсутствующее значение. ПРОСМОТРX требует Excel 2021 или Microsoft 365; в Excel 2019 используйте =ЕСЛИОШИБКА(ВПР(A2;$F$2:$G$6;2;ЛОЖЬ);"Not paid"). Остальные аргументы описаны на странице ПРОСМОТРX.
Вывести значения, которых нет в другом столбце
Вместо столбца ИСТИНА/ЛОЖЬ ФИЛЬТР может вернуть недостающие значения списком. СЧЁТЕСЛИ(B2:B8;A2:A8) с диапазоном во втором аргументе считает сразу все значения A, а ФИЛЬТР оставляет те, где количество равно 0.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | Not in February | |
| 2 | Ana | Dan | ||
| 3 | Ben | Fay | ||
| 4 | Cara | Ana | ||
| 5 | Dan | Gus | ||
| 6 | Eve | Hal | ||
| 7 | Fay | Ivy | ||
| 8 | Gus | Ben |
Ваша очередь: В D2 перечислите клиентов января, которых нет в списке февраля.
Ответ разливает Cara и Eve. =ФИЛЬТР(A2:A8;ЕНД(ПОИСКПОЗ(A2:A8;B2:B8;0))) тоже работает. Если вернулись все клиенты, ФИЛЬТР возвращает #ВЫЧИСЛ! (по-английски #CALC!); добавьте третий аргумент для этого случая: =ФИЛЬТР(A2:A8;СЧЁТЕСЛИ(B2:B8;A2:A8)=0;"None"). ФИЛЬТР требует Excel 2021 или Microsoft 365. Другие условия описаны на странице ФИЛЬТР.
Выделить различия между двумя столбцами
Формулы выше работают и как правила условного форматирования. Выделите первый список, выберите Главная > Условное форматирование > Создать правило > Использовать формулу для определения форматируемых ячеек и введите формулу для его первой ячейки. Здесь A2:A8 получает =СЧЁТЕСЛИ($B$2:$B$8;A2)=0, а B2:B8 получает =СЧЁТЕСЛИ($A$2:$A$8;B2)=0: закрашивается каждое имя, которое есть только в одном из списков.
| A | B | |
|---|---|---|
| 1 | January | February |
| 2 | Ana | Dan |
| 3 | Ben | Fay |
| 4 | Cara | Ana |
| 5 | Dan | Gus |
| 6 | Eve | Hal |
| 7 | Fay | Ivy |
| 8 | Gus | Ben |
В январе закрашены Cara и Eve, в феврале Hal и Ivy. Для двух столбцов, которые должны совпадать построчно, правило =$A2<>$B2 на обоих столбцах, как в первой таблице на этой странице. Чтобы закрасить имена, которые есть в обоих списках, используйте >0, как на странице выделить дубликаты.
Почему одинаковые значения показываются как разные
Самая частая причина в невидимом пробеле: Ana с пробелом в конце не равно Ana. Данные, вставленные из другой системы или с веб-страницы, часто приносят такие пробелы. Сравнивайте значения после обрезки.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Other list | Equal? | Trimmed |
| 2 | Ana | Ana | FALSE | TRUE |
| 3 | Ben | Ben | TRUE | TRUE |
| 4 | Cara | Cara | FALSE | TRUE |
| 5 | Dan | Dan | TRUE | TRUE |
=A2=B2Столбец C говорит, что строки 2 и 4 различаются; столбец D, после того как СЖПРОБЕЛЫ удалила пробелы с обоих концов, говорит, что все четыре совпадают. Другая обычная причина в числе, сохранённом как текст в одном столбце, и настоящем числе в другом: 101 и '101 выглядят одинаково, но сравнение через = в Excel даёт ЛОЖЬ, и ПОИСКПОЗ, ВПР и ПРОСМОТРX не находят одно в другом. Исключение составляет СЧЁТЕСЛИ: она читает текст, похожий на число, как это число, поэтому считает их равными. Зелёный треугольник в углу ячейки отмечает текстовый вариант; преобразуйте его через =ЗНАЧЕН(A2) или =A2*1 либо выделите ячейки и выберите Преобразовать в число в значке предупреждения.
Часто задаваемые вопросы
Как сравнить два столбца в Excel на совпадения?
Построчно: введите =A2=B2 в C2 и протяните вниз; ИСТИНА означает, что две ячейки совпадают. Чтобы проверить, встречается ли каждое значение из A где-нибудь в B, используйте =СЧЁТЕСЛИ($B$2:$B$8;A2)>0.
Как сравнить два столбца и вернуть значение из второго?
Найдите значение поиском: =ПРОСМОТРX(A2;$F$2:$F$7;$G$2:$G$7;"Not found") возвращает соответствующее значение из G или Not found. В Excel 2019 и старше используйте =ЕСЛИОШИБКА(ВПР(A2;$F$2:$G$7;2;ЛОЖЬ);"Not found").
Учитывает ли Excel регистр при сравнении двух ячеек?
Нет. =A2=B2 считает abc и ABC равными. Для сравнения с учётом регистра используйте =СОВПАД(A2;B2), которая даёт ИСТИНА, только когда совпадает каждый символ, включая регистр.
Как вывести значения, которые есть в одном столбце, но нет в другом?
В Excel 365 и 2021 =ФИЛЬТР(A2:A8;СЧЁТЕСЛИ(B2:B8;A2:A8)=0) разливает каждое значение A2:A8, которого нет в B2:B8.
Почему Excel считает два одинаковых значения разными?
Обычно в одном из них лишний пробел или это число, сохранённое как текст. Сравните =СЖПРОБЕЛЫ(A2)=СЖПРОБЕЛЫ(B2), чтобы исключить пробелы, а текстовые числа преобразуйте через =ЗНАЧЕН(A2) или =A2*1.
