проверить, равны ли две или более ячеек
В этом учебном пособии вы узнаете, как построить формулу «Если совпадает» в Excel, чтобы она возвращала логические значения, пользовательский текст или значение из другой ячейки.
Формула Excel для проверки совпадения двух ячеек может быть такой простой, как A1=B1. Однако могут быть разные обстоятельства, когда это очевидное решение не сработает или даст результаты, отличные от ожидаемых. В этом уроке мы обсудим различные способы сравнения ячеек в Excel, чтобы вы могли найти оптимальное решение для своей задачи.
Как проверить, совпадают ли две ячейки в Excel
Существует множество вариантов формулы Excel If match. Просто просмотрите приведенные ниже примеры и выберите тот, который лучше всего подходит для вашего сценария.
Если две ячейки равны, вернуть TRUE
Простейшая формула Excel «Если одна ячейка равна другой, то это правда»:
ячейка А знак равно ячейка Б
Например, чтобы сравнить ячейки в столбцах A и B в каждой строке, вы вводите эту формулу в C2, а затем копируете ее вниз по столбцу:
=А2=В2
В результате вы получите TRUE, если две ячейки одинаковы, иначе FALSE:
Заметки:
- Эта формула возвращает два логических значения: если две ячейки равны – ИСТИНА; если не равно – ЛОЖЬ. Чтобы вернуть только значения TRUE, используйте оператор IF, как показано в следующем примере.
- Эта формула нечувствительна к регистру, поэтому прописные и строчные буквы рассматриваются как одни и те же символы. Если регистр текста имеет значение, используйте это формула с учетом регистра.
Если две ячейки совпадают, возвращаемое значение
Чтобы вернуть собственное значение, если две ячейки совпадают, создайте оператор IF, используя этот шаблон:
ЕСЛИ(ячейка А знак равно ячейка Бзначение_если_истина, значение_если_ложь)
Например, чтобы сравнить A2 и B2 и вернуть «да», если они содержат одинаковые значения, и «нет» в противном случае, формула будет следующей:
=ЕСЛИ(A2=B2, “да”, “нет”)
Если вы хотите вернуть значение только в том случае, если ячейки равны, укажите пустую строку (“”) для value_if_false.
Если совпадают, то да:
=ЕСЛИ(A2=B2, “да”, “”)
Если совпадают, то ИСТИНА:
=ЕСЛИ(A2=B2, ИСТИНА, “”)
Примечание. Чтобы вернуть логическое значение TRUE, не заключайте его в двойные кавычки. Использование двойных кавычек преобразует логическое значение в обычную текстовую строку.
Если одна ячейка равна другой, верните другую ячейку
А вот вариант формулы Excel при совпадении, которая решает эту конкретную задачу: сравнить значения в двух ячейках и, если данные совпадают, скопировать значение из другой ячейки.
На языке Excel это формулируется так:
ЕСЛИ(ячейка А знак равно ячейка Б, ячейка С“”)
Например, чтобы проверить элементы в столбцах A и B и вернуть значение из столбца C, если текст совпадает, формула в D2, скопированная вниз, выглядит следующим образом:
=ЕСЛИ(А2=В2, С2, “”)
Формула с учетом регистра, чтобы увидеть, совпадают ли две ячейки
В ситуации, когда вы имеете дело с текстовыми значениями, чувствительными к регистру, используйте функцию EXACT для точного сравнения ячеек, включая регистр букв:
ЕСЛИ(ТОЧНО(ячейка А, ячейка Б), значение_если_истина, значение_если_ложь)
Например, чтобы сравнить элементы в A2 и B2 и вернуть «да», если текст точно совпадает, и «нет», если найдено какое-либо различие, вы можете использовать эту формулу:
= ЕСЛИ (ТОЧНО (A2, B2), «Да», «Нет»)
Как проверить, равны ли несколько ячеек
Как и при сравнении двух ячеек, проверку совпадений нескольких ячеек также можно выполнить несколькими способами.
Формула И, чтобы увидеть, совпадают ли несколько ячеек
Чтобы проверить совпадение нескольких значений, вы можете использовать функцию И с двумя или более логическими тестами:
А ТАКЖЕ(ячейка А знак равно ячейка Б, ячейка А знак равно ячейка С…)
Например, чтобы увидеть, равны ли ячейки A2, B2 и C2, используйте следующую формулу:
=И(А2=В2, А2=С2)
В динамическом массиве Excel (365 и 2021) вы также можете использовать приведенный ниже синтаксис. В Excel 2019 и более ранних версиях это будет работать только как традиционная формула массива CSE, которую можно выполнить, нажав одновременно клавиши Ctrl + Shift + Enter.
=И(A2=B2:C2)
Результатом обеих формул И являются логические значения ИСТИНА и ЛОЖЬ.
Чтобы вернуть собственные значения, оберните И в функцию ЕСЛИ следующим образом:
=ЕСЛИ(И(A2=B2:C2), “да”, “”)
Эта формула возвращает «да», если все три ячейки равны, в противном случае — пустую ячейку.
Формула COUNTIF для проверки совпадения нескольких столбцов
Другой способ проверить наличие нескольких совпадений — использовать функцию СЧЁТЕСЛИ в такой форме:
СЧЁТЕСЛИ(диапазон, клетказнак равнон
Где диапазон — это диапазон ячеек, которые нужно сравнить друг с другом, ячейка — это любая отдельная ячейка в диапазоне, а n — это количество ячеек в диапазоне.
Для нашего примера набора данных формула может быть записана в следующем виде:
=СЧЁТЕСЛИ(A2:C2, A2)=3
Если вы сравниваете много столбцов, функция COLUMNS может автоматически получить количество ячеек (n):
=СЧЁТЕСЛИ(A2:C2, A2)=СТОЛБЦЫ(A2:C2)
А функция ЕСЛИ поможет вам вернуть все, что вы хотите, в качестве результата:
=ЕСЛИ(СЧЁТЕСЛИ(A2:C2, A2)=3, “Все совпадают”, “”)
Формула с учетом регистра для нескольких совпадений
Как и при проверке двух ячеек, мы используем функцию EXACT для выполнения точного сравнения, включая регистр букв. Для обработки нескольких ячеек EXACT должен быть вложен в функцию AND следующим образом:
И(ТОЧНО(диапазон, клетка))
В Excel 365 и Excel 2021 из-за поддержки динамических массивов это работает как обычная формула. В Excel 2019 и более ранних версиях не забудьте нажать Ctrl + Shift + Enter, чтобы сделать формулу массива.
Например, чтобы проверить, содержат ли ячейки A2:C2 одинаковые значения, используйте формулу с учетом регистра:
= И (ТОЧНО (A2: C2, A2))
В сочетании с IF он принимает следующий вид:
=ЕСЛИ(И(ТОЧНО(A2:C2, A2)), “Да”, “Нет”)
Проверьте, соответствует ли ячейка какой-либо ячейке в диапазоне
Чтобы узнать, соответствует ли ячейка какой-либо ячейке в заданном диапазоне, используйте одну из следующих формул:
функция ИЛИ
Лучше всего использовать для проверки 2-3 ячеек.
ИЛИ ЖЕ(ячейка А знак равно ячейка Б, ячейка А знак равно ячейка С, ячейка А знак равно ячейка D…)
Excel 365 и Excel 2021 также понимают этот синтаксис:
ИЛИ ЖЕ(клетка знак равно диапазон)
В Excel 2019 и более ранних версиях это следует вводить как формулу массива, нажав сочетание клавиш Ctrl + Shift + Enter.
СЧЁТЕСЛИ функция
СЧЁТЕСЛИ(диапазон, клетка)>0
Например, чтобы проверить, соответствует ли A2 какой-либо ячейке в B2:D2, подойдет любая из этих формул:
=ИЛИ(А2=В2, А2=С2, А2=D2)
=ИЛИ(A2=B2:D2)
=СЧЁТЕСЛИ(B2:D2, A2)>0
Если вы используете Excel 2019 или более раннюю версию, не забудьте нажать Ctrl + Shift + Enter, чтобы вторая формула ИЛИ выдавала правильные результаты.
Чтобы вернуть Да/Нет или любые другие значения, которые вы хотите, вы знаете, что делать – вложить одну из приведенных выше формул в логическую проверку функции ЕСЛИ. Например:
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2, A2)>0, “Да”, “Нет”)
Дополнительные сведения см. в разделе Проверка наличия значения в диапазоне.
Проверьте, равны ли два диапазона
Чтобы сравнить два диапазона по ячейкам и вернуть логическое значение ИСТИНА, если все ячейки в соответствующих позициях совпадают, укажите диапазоны одинакового размера для логической проверки функции И:
А ТАКЖЕ(диапазон А знак равно диапазон Б)
Например, чтобы сравнить матрицу A в B3:F6 и матрицу B в B11:F14, используйте следующую формулу:
=И(B3:F6= B11:F14)
Чтобы в результате получить Да/Нет, используйте следующую комбинацию ЕСЛИ И:
=ЕСЛИ(И(B3:F6=B11:F14), “Да”, “Нет”)
Вот как использовать формулу If match в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь
Если ячейки совпадают в Excel — примеры формул (файл .xlsx)