Сравните строки в двух ячейках на наличие совпадений (без учета регистра или с точностью)

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

При использовании Excel для анализа данных точность является самой важной задачей. Неверная информация приводит к срыву сроков, неверной оценке тенденций, неправильным решениям и потере доходов.

Хотя формулы Excel всегда абсолютно верны, их результаты могут быть неверными, потому что в систему проникли какие-то ошибочные данные. В этом случае единственным средством является проверка данных на точность. Сравнить две ячейки вручную несложно, но почти невозможно обнаружить различия между сотнями и тысячами текстовых строк.

Из этого туториала вы узнаете, как автоматизировать утомительную и чреватую ошибками задачу сравнения ячеек и какие формулы лучше всего использовать в каждом конкретном случае.

Как сравнить две ячейки в Excel

Существует два разных способа сравнения строк в Excel в зависимости от того, ищете ли вы сравнение с учетом или без учета регистра.

Формула без учета регистра для сравнения 2 ячеек

Чтобы сравнить две ячейки в Excel без учета регистра, используйте простую формулу:

=А1=В1

Где A1 и B1 — это ячейки, которые вы сравниваете. Результатом формулы являются логические значения TRUE и FALSE.

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

=ЕСЛИ(A1=B1, «Равно», «Не равно»)

Как вы видите на скриншоте ниже, обе формулы одинаково хорошо сравнивают текстовые строки, даты и числа:
Формула без учета регистра для сравнения 2 ячеек в Excel

Формула с учетом регистра для сравнения строк в Excel

В некоторых ситуациях может быть важно не только сравнить текстовые значения двух ячеек, но и сравнить регистр символов. Сравнение текста с учетом регистра можно выполнить с помощью функции Excel EXACT:

ТОЧНО (текст1, текст2)

Где текст 1 а также текст2 две ячейки, которые вы сравниваете.

Предполагая, что ваши строки находятся в ячейках A2 и B2, формула выглядит следующим образом:

=ТОЧНО(A2, B2)

В результате вы получаете TRUE для текстовых строк, которые точно совпадают с учетом регистра каждого символа, FALSE в противном случае.

Если вы хотите, чтобы функция EXACT давала какие-то другие результаты, вставьте ее в формулу ЕСЛИ и введите свой собственный текст для значение_если_истина а также значение_если_ложь аргументы:

= ЕСЛИ (ТОЧНО (A2, B2), «Точно равно», «Не равно»)

На следующем снимке экрана показаны результаты сравнения строк с учетом регистра в Excel:
Формула без учета регистра для сравнения строк в 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 для сравнения строк. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *