Вы узнаете, как сравнивать значения в ячейках Excel для получения точных результатов или результатов без учета регистра. Мы предложим вам разные формулы для сравнения двух ячеек на основе их значений, длины или количества вхождений определенного символа, а также вы узнаете, как одновременно сравнивать несколько ячеек.
При использовании Excel для анализа данных точность является наиболее важной задачей. Неправильная информация приводит к срыву сроков, неверно оцененным тенденциям, неправильным решениям и потере дохода.
Хотя ваши формулы Excel абсолютно верны, их результаты могут быть неверными из-за неправильных значений в ваших таблицах. Легко вручную сравнить две ячейки, но почти невозможно различить сотни и тысячи текстовых и числовых значений.
- Как сравнить две ячейки без учета регистра.
- Сравните несколько ячеек одновременно.
- Сравните несколько ячеек с образцом
- Какая ячейка длиннее?
- Одинаковы ли ячейки по количеству вхождений данного символа?
В этом руководстве вы узнаете, как автоматизировать утомительную и подверженную ошибкам задачу сравнения ячеек и какие формулы лучше всего использовать в каждом случае.
Как сравнить две ячейки в Excel.
Есть два разных способа узнать идентичность текстовых данных в Excel, в зависимости от того, хотите ли вы сделать это с учетом регистра или без учета регистра.
Формула без учета регистра.
Чтобы сопоставить две ячейки в Excel без учета регистра, используйте самый простой вариант:
= A1 = B1
Где A1 и B1 — объединяемые объекты. Результатом являются логические значения ИСТИНА и ЛОЖЬ.
Если вы хотите вывести в результате свои собственные сообщения, чтобы указать совпадения и различия, вставьте предыдущий оператор в логический тест функции ЕСЛИ. Например:
= ЕСЛИ (LA2 = B2; «Соответствует»; «НЕ СООТВЕТСТВУЕТ»)
Как видно на скриншоте ниже, обе формулы одинаково хорошо сравнивают текст, даты и числа:
Формула с учетом регистра.
В некоторых ситуациях может быть важно не только сравнить текст, но и различать верхний и нижний регистр. Сопоставление с учетом регистра может быть выполнено с помощью функции EXACT EXACT):
СОВЕТ (текст1; текст2)
Где text1 и text2 — две сравниваемые ячейки.
Предполагая, что ваши значения находятся в A2 и B2, расчет выглядит следующим образом:
= ТОЧНЫЙ (A2; B2)
Это вернет ИСТИНА для текстовых данных, которые точно соответствуют регистру каждого символа, в противном случае ЛОЖЬ.
Если вы хотите, чтобы функция RUN приводила к другим результатам, вставьте ее в формулу ЕСЛИ и введите свой текст для вариантов ответа:
= ЕСЛИ (ПОИСКПОЗ (A2; B2); «Соответствует»; «НЕ СООТВЕТСТВУЕТ»)
На следующем снимке экрана показаны результаты с учетом регистра:
Как сравнить несколько ячеек в Excel
Чтобы сопоставить друг с другом более 2 ячеек, используйте выражения, описанные в предыдущих примерах, в сочетании с оператором AND.
Формула без учета регистра для более чем двух ячеек
В зависимости от того, как вы хотите отображать результаты, используйте одну из следующих формул:
= И (A2 = B2; A2 = C2)
или
= ЕСЛИ (И (LA2 = B2; A2 = C2), «Соответствует»; «НЕ совпадает»)
Оператор AND возвращает TRUE, если все ячейки в таблице содержат одно и то же значение, FALSE, если одно значение отличается. Функция ЕСЛИ отображает введенные вами метки: «Соответствует» и «Не совпадает».
Как показано на скриншоте ниже, формула отлично подходит для любого типа данных: текста, дат и числовых значений:
Сравниваем несколько ячеек с учетом регистра букв.
Чтобы сопоставить несколько текстовых значений вместе и убедиться, что они совпадают точно, используйте:
= И (ТОЧНЫЙ (A2; B2); ТОЧНЫЙ (A2; C2))
или
= ЕСЛИ (И (ПОИСКПОЗ (A2; B2); ТОЧНОЕ (A2; C2)); «Точное совпадение»; «НЕ совпадает»)
Как и в предыдущем примере, первая формула возвращает значения ИСТИНА и ЛОЖЬ, а вторая отображает собственные сообщения о совпадениях и различиях:
Если нам достаточно хотя бы двух совпадений значений, то сделаем это так:
= ЕСЛИ (OR (TRAVEL (A2; B2); MATCH (A2; C2); MATCH (B2; C2)); «Как минимум 2 точных совпадения»; «НЕ СОВПАДАЕТ»)
То есть, если в двух случаях из трех будут одинаковые значения, то это будет для нас положительным результатом.
Но если в нашей таблице есть много ячеек, которые нужно проверить, приведенное выше выражение с условием ИЛИ станет очень громоздким. Следовательно, вы можете использовать функцию СЧЁТЕСЛИ.
Мы можем вычислить количество совпадений в нашем диапазоне ячеек, используя эту формулу массива:
{= СЧЁТЕСЛИ (A2: D2; A2: D2)}
Чтобы выделить группы ячеек с полностью совпадающими значениями, вы можете использовать выражение:
= ЕСЛИ (СЧЁТЕСЛИ (A2: D2; A2: D2) = СЧЁТ (A2: D2), «Точное совпадение»;»»)
Как видите, к сожалению, здесь не рассматривается случай.
Также обратите внимание на последнюю строчку. В одной из ячеек есть ведущие пробелы, поэтому полное совпадение отсутствует. Аналогичный результат будет, если после текста случайно будет вставлен так называемый конечный пробел. А визуально обнаружить это крайне сложно. Поэтому обнаружение и удаление лишних пробелов — очень важная задача. Ссылку на необходимые инструкции см. В конце этой статьи.
Сравните диапазон ячеек с образцом.
В следующих примерах показано, как проверить, что все адреса в заданном диапазоне содержат тот же текст, что и ячейка примера.
Совпадает ли ячейка с образцом?
Если случайность не имеет значения, вы можете использовать подобное выражение для сопоставления с образцом:
LINES (диапазон) * NUMBERCOLUMN (диапазон) = COUNTIF (диапазон; ячейка модели)
В логическом тесте функции ЕСЛИ вы проверяете равенство двух чисел:
- Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов) e
- Количество ячеек, содержащих одно и то же значение шаблона (возвращаемое функцией СЧЁТЕСЛИ).
Предполагая, что образец текста находится в C2, а данные находятся в диапазоне A2: B6, формула выглядит следующим образом:
(СТРОКИ (A2: B6) * НОМЕР КОЛОНКИ (A2: B6)) = СЧЁТЕСЛИ (A2: B6; C2)
Чтобы сделать результаты более интуитивно понятными, то есть для отображения значимого текста вместо ИСТИНА и ЛОЖЬ, используйте функцию ЕСЛИ, как мы это делали в предыдущих примерах:
= ЕСЛИ ((СТРОКИ (A2: B6) * NUMBERCOLUMNS (A2: B6)) = COUNTIF (A2: B6, C2), «Все совпадает»; «Есть расхождения»)
Как показано на скриншоте выше, формула отлично справляется с диапазоном текстовых значений, но ее также можно использовать для сопоставления чисел и дат.
Проверяем совпадение с образцом с учётом регистра букв.
Если регистр важен, вы можете проверить, соответствует ли ячейка образцу текста, используя следующие формулы массива.
IF ((ROWS (диапазон) * NUMBERCOLUMN (диапазон)) = (SUM (- MATCH (шаблон; диапазон))), «text_if_matches»; «text_if_NOT_matches»)
Поскольку исходный диапазон находится в области A2: B6, а образец текста находится в области C2, мы получаем следующее:
{= IF ((LINES (B2: C6) * NUMBERCOLUMNS (B2: C6)) = (SUM (- MATCH (C2; B2: C6))), «Все совпадения»; «НЕ совпадает»)}
В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter. При правильном вводе Excel заключает формулу массива в {фигурные скобки}, как показано ниже:
Как соотнести две ячейки по длине.
Иногда вам может потребоваться проверить, содержат ли данные в каждой ячейке одинаковое количество символов. Решение этой проблемы очень простое. Сначала получите длину каждой из этих двух ячеек с помощью функции DLSTR, а затем проверьте, совпадают ли числа.
Предположим, что исходные значения находятся в A2 и B2. Воспользуйтесь одним из вариантов:
= DLSTR (A2) = DLSTR (B2)
или
= ЕСЛИ (DLSTR (A2) = DLSTR (B2), «Одинаковая длина», «Различная длина»)
Как вы понимаете, первый возвращает логические значения ИСТИНА или ЛОЖЬ, а второй возвращает ваши результаты:
Как показано на скриншоте выше, формулы работают как для текста, так и для чисел.
Примечание. Если две явно идентичные текстовые ячейки возвращают разную длину, проблема, скорее всего, связана с начальными или конечными пробелами в одной или обоих. В этом случае удалите лишние пробелы с помощью функции ОБРЕЗАТЬ. Подробные объяснения и примеры можно найти здесь: Как удалить пробелы в Excel .
Сравниваем две ячейки по вхождению определенного символа
Это последний пример в нашем Руководстве по сравнению ячеек Excel, и он показывает решение довольно конкретной задачи. Допустим, у вас есть 2 столбца текстовых значений, которые содержат важный для вас символ. Ваша цель — проверить, содержат ли две ячейки в каждой строке одинаковое количество вхождений данного символа.
Чтобы было понятнее, рассмотрим следующий пример. Допустим, у вас есть два списка отправленных заказов (столбец B) и полученных заказов (столбец C). Каждая запись в таблице содержит заказы на определенный продукт, уникальный идентификатор которого включен во все идентификаторы заказа и указан в той же строке в столбце A (см. Снимок экрана ниже). Вы хотите убедиться, что каждая строка содержит равное количество отправленных и полученных элементов с этим конкретным идентификатором.
Чтобы решить эту проблему, поступим следующим образом:
- Сначала замените код заказа ничем с помощью функции ЗАМЕНИТЬ:
ЗАМЕНА (A1; код;»»)
- Таким образом, он считает, сколько раз код появляется в каждой ячейке. Для этого получите длину его содержимого без кода и вычтите ее из общей длины исходной ячейки. Это нужно делать отдельно для ячеек 1 и 2, например:
DLSTR (B2) -LSTR (ПОДСТАВИТЬ (B2; $ A2;»»))
а также
DLSTR (C2) -LSTR (ПОДСТАВИТЬ (C2, $ A2;»»))
- Теперь идентифицируйте эти 2 числа, вставив знак равенства (=) между указанными выше частями.
DLSTR (B2) -LSTR (ПОДСТАВИТЬ (B2, $ A2; «»)) = DLSTR (C2) -LSTR (ПОДСТАВИТЬ (C2, $ A2;»»))
В нашем примере код находится в A2, а значения — в B2 и C2.
Выражение возвращает TRUE, если B2 и C2 содержат такое же количество вхождений символа, что и A2, в противном случае — FALSE. Чтобы сделать результаты более понятными для ваших пользователей, вы можете встроить его в функцию SE:
= IF (DLSTR (B2) -LSTR (SUBSTITUTE (B2, $ A2; «»)) = DLSTR (C2) -LSTR (SUBSTITUTE (C2, $ A2; «»)), «Соответствует»; «НЕ совпадает»)
Как видно на скриншоте выше, все работает нормально, несмотря на пару дополнительных сложностей:
- Подсчитываемый символ (уникальный идентификатор) может находиться в любом месте ячейки.
- Записи содержат переменное количество символов и различные разделители, такие как точки с запятой, запятые или пробелы.
Вот как вы можете использовать формулы для сравнения ячеек в Excel. Спасибо за прочтение.
