Сравните строки в двух ячейках на наличие совпадений (без учета регистра или с точностью)
В этом руководстве показано, как сравнивать текстовые строки в Excel для точного соответствия и без учета регистра. Вы узнаете ряд формул для сравнения двух ячеек по их значениям, длине строки или количеству вхождений определенного символа, а также узнаете, как сравнивать несколько ячеек.
При использовании Excel для анализа данных точность является самой важной задачей. Неверная информация приводит к срыву сроков, неверной оценке тенденций, неправильным решениям и потере доходов.
Хотя формулы Excel всегда абсолютно верны, их результаты могут быть неверными, потому что в систему проникли какие-то ошибочные данные. В этом случае единственным средством является проверка данных на точность. Сравнить две ячейки вручную несложно, но почти невозможно обнаружить различия между сотнями и тысячами текстовых строк.
Из этого туториала вы узнаете, как автоматизировать утомительную и чреватую ошибками задачу сравнения ячеек и какие формулы лучше всего использовать в каждом конкретном случае.
Как сравнить две ячейки в Excel
Существует два разных способа сравнения строк в Excel в зависимости от того, ищете ли вы сравнение с учетом или без учета регистра.
Формула без учета регистра для сравнения 2 ячеек
Чтобы сравнить две ячейки в Excel без учета регистра, используйте простую формулу:
=А1=В1
Где A1 и B1 — это ячейки, которые вы сравниваете. Результатом формулы являются логические значения TRUE и FALSE.
Если вы хотите вывести свои собственные тексты для совпадений и различий, вставьте приведенное выше утверждение в логическую проверку функции ЕСЛИ. Например:
=ЕСЛИ(A1=B1, “Равно”, “Не равно”)
Как вы видите на скриншоте ниже, обе формулы одинаково хорошо сравнивают текстовые строки, даты и числа:
Формула с учетом регистра для сравнения строк в Excel
В некоторых ситуациях может быть важно не только сравнить текстовые значения двух ячеек, но и сравнить регистр символов. Сравнение текста с учетом регистра можно выполнить с помощью функции Excel EXACT:
ТОЧНО (текст1, текст2)
Где текст 1 а также текст2 две ячейки, которые вы сравниваете.
Предполагая, что ваши строки находятся в ячейках A2 и B2, формула выглядит следующим образом:
=ТОЧНО(A2, B2)
В результате вы получаете TRUE для текстовых строк, которые точно совпадают с учетом регистра каждого символа, FALSE в противном случае.
Если вы хотите, чтобы функция EXACT давала какие-то другие результаты, вставьте ее в формулу ЕСЛИ и введите свой собственный текст для значение_если_истина а также значение_если_ложь аргументы:
= ЕСЛИ (ТОЧНО (A2, B2), «Точно равно», «Не равно»)
На следующем снимке экрана показаны результаты сравнения строк с учетом регистра в Excel:
Как сравнить несколько ячеек в Excel
Чтобы сравнить более 2 ячеек подряд, используйте формулы, рассмотренные в приведенных выше примерах, в сочетании с оператором И. Подробная информация приведена ниже.
Формула без учета регистра для сравнения более 2 ячеек
В зависимости от того, как вы хотите отобразить результаты, используйте одну из следующих формул:
=И(А2=В2, А2=С2)
или же
=ЕСЛИ(И(A2=B2, A2=C2), “Равно”, “Не равно”)
Формула И возвращает значение ИСТИНА, если все ячейки содержат одинаковое значение, и ЛОЖЬ, если какое-либо значение отличается. Формула ЕСЛИ выводит метки, которые вы в нее вводите, “Равный” а также “Не равный“в этом примере.
Как показано на скриншоте ниже, формула отлично работает с любыми типами данных — текстовыми, датами и числовыми значениями:
Формула с учетом регистра для сравнения текста в нескольких ячейках
Чтобы сравнить несколько строк друг с другом и убедиться, что они точно совпадают, используйте следующие формулы:
= И (ТОЧНО (A2, B2), ТОЧНО (A2, C2))
Или же
= ЕСЛИ (И (ТОЧНО (A2, B2), ТОЧНО (A2, C2)), «Точно равно», «Не равно»)
Как и в предыдущем примере, первая формула выдает значения ИСТИНА и ЛОЖЬ, тогда как вторая отображает ваши собственные тексты для совпадений и различий:
Сравнение диапазона ячеек с образцом ячейки
В следующих примерах показано, как можно убедиться, что все ячейки заданного диапазона содержат тот же текст, что и в образце ячейки.
Формула без учета регистра для сравнения ячеек с образцом текста
Если регистр символов не имеет большого значения, вы можете использовать следующую формулу для сравнения ячеек с образцом:
РЯДЫ(диапазон)*СТОЛБЦЫ(диапазон)=СЧЁТЕСЛИ(диапазон, ячейка образца)
В логической проверке функции ЕСЛИ вы сравниваете два числа:
- Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов) и
- Количество ячеек, содержащих то же значение, что и в ячейке образца (возвращается функцией СЧЁТЕСЛИ).
Предполагая, что образец текста находится в C2, а строки для сравнения находятся в диапазоне A2: B6, формула выглядит следующим образом:
=СТРОКИ(A2:B6)*СТОЛБЦЫ(A2:B6)=СЧЁТЕСЛИ(A2:B6,C2)
Чтобы сделать результаты более удобными для пользователя, т. е. вывести что-то вроде «Все совпадения» и «Не все совпадения» вместо ИСТИНА и ЛОЖЬ, используйте функцию ЕСЛИ, как мы делали в предыдущих примерах:
=ЕСЛИ(СТРОКИ(A2:B6)*СТОЛБЦЫ(A2:B6)=СЧЁТЕСЛИ(A2:B6,C2),”Все совпадают”, “Не все совпадают”)
Как показано на скриншоте выше, формула отлично справляется с диапазоном текстовых строк, но ее также можно использовать для сравнения чисел и дат.
Формула с учетом регистра для сравнения строк с образцом текста
Если регистр символов имеет значение, вы можете сравнить строки с образцом текста, используя следующие формулы массива.
ЕСЛИ(СТРОКИ(диапазон)*СТОЛБЦЫ(диапазон)=СУММ(–ТОЧНО(sample_cell, диапазон)) “text_if_match“, “text_if_не совпадает“)
С исходным диапазоном, находящимся в A2:B6, и образцом текста в C2, формула принимает следующий вид:
=ЕСЛИ(СТРОКИ(A2:B6)*СТОЛБЦЫ(A2:B6)=СУММ(–EXACT(C2, A2:B6)), “Все совпадают”, “Не все совпадают”)
В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter. При правильном вводе Excel заключает формулу массива в {фигурные скобки}, как показано на снимке экрана:
Как сравнить две ячейки по длине строки
Иногда вам может понадобиться проверить, содержат ли текстовые строки в каждой строке одинаковое количество символов. Формула для этой задачи очень проста. Сначала вы получаете длину строки двух ячеек с помощью функции ДЛСТР, а затем сравниваете числа.
Предположим, что сравниваемые строки находятся в ячейках A2 и B2, используйте одну из следующих формул:
=ДЛСТР(A2)=ДЛСТР(B2)
Или же
=ЕСЛИ(ДЛСТР(A2)=ДЛСТР(B2), “Равно”, “Не равно”)
Как вы уже знаете, первая формула возвращает логические значения ИСТИНА или ЛОЖЬ, тогда как вторая формула выводит ваши собственные результаты:
Как показано на снимке экрана выше, формулы работают как для текстовых строк, так и для чисел.
Кончик. Если две, казалось бы, равные строки возвращают разную длину, скорее всего, проблема заключается в начальных или конечных пробелах в одной или обеих ячейках. В этом случае удалите лишние пробелы с помощью функции TRIM. Подробное объяснение и примеры формул можно найти здесь: Как обрезать пробелы в Excel.
Сравните две ячейки по встречаемости определенного символа
Это последний пример в нашем руководстве по сравнению строк в Excel, и он показывает решение для довольно специфической задачи. Предположим, у вас есть 2 столбца текстовых строк, которые содержат важный для вас символ. Ваша цель — проверить, содержат ли две ячейки в каждой строке одинаковое количество вхождений данного символа.
Чтобы было понятнее, рассмотрим следующий пример. Допустим, у вас есть два списка отправленных (столбец B) и полученных (столбец C) заказов. Каждая строка содержит заказы на определенный товар, уникальный идентификатор которого включен во все идентификаторы заказов и указан в той же строке в столбце A (см. снимок экрана ниже). Вы хотите убедиться, что каждая строка содержит одинаковое количество отправленных и полученных товаров с этим конкретным идентификатором.
Чтобы решить эту задачу, напишите формулу со следующей логикой.
- Во-первых, замените уникальный идентификатор ничем, используя функцию ПОДСТАВИТЬ:
ПОДСТАВИТЬ(A1, число_символов,””)
- Затем подсчитайте, сколько раз уникальный идентификатор появляется в каждой ячейке. Для этого получите длину строки без уникального идентификатора и вычтите ее из общей длины строки. Эта часть должна быть написана для ячейки 1 и ячейки 2 отдельно, например:
ДЛСТР(ячейка 1) – ДЛСТР(ЗАМЕНИТЬ(ячейка 1, количество_символов, “”))
а также
ДЛСТР(ячейка 2) – ДЛСТР(ЗАМЕНИТЬ(ячейка 2, количество_символов, “”)) - Наконец, вы сравниваете эти 2 числа, помещая знак равенства (=) между вышеуказанными частями.
ДЛСТР(ячейка 1) – ДЛСТР(ПОДСТАВИТЬ(ячейка 1, символ_до_счетазнак равно
ДЛСТР(ячейка 2) – ДЛСТР(ПОДСТАВИТЬ(ячейка 2, символ_до_счета“”))
В нашем примере уникальный идентификатор находится в ячейке A2, а строки для сравнения — в ячейках B2 и C2. Итак, полная формула выглядит следующим образом:
=LEN(B2)-LEN(ЗАМЕНИТЬ(B2,$A2,””))=LEN(C2)-LEN(ЗАМЕНИТЬ(C2,$A2,””))
Формула возвращает ИСТИНА, если ячейки B2 и C2 содержат одинаковое количество вхождений символа в ячейке A2, и ЛОЖЬ в противном случае. Чтобы сделать результаты более значимыми для ваших пользователей, вы можете встроить формулу в функцию ЕСЛИ:
=ЕСЛИ(ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2, $A2,””))=ДЛСТР(C2)-ДЛСТР(ПОДСТАВИТЬ(С2, $A2,””)), “Равно”, “Не равно”)
Как вы можете видеть на скриншоте выше, формула работает отлично, несмотря на пару дополнительных сложностей:
- Подсчитываемый символ (уникальный идентификатор) может появиться в любом месте текстовой строки.
- Строки содержат переменное количество символов и различные разделители, такие как точка с запятой, запятая или пробел.
Вот как вы сравниваете строки в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить Рабочий лист Excel для сравнения строк. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.
