Вы узнаете, как сравнивать значения в ячейках Excel для точного совпадения или с учетом регистра. Мы предложим вам несколько формул для сравнения двух ячеек по их значениям, длине или количеству вхождений определенного символа, а также вы научитесь сравнивать сразу несколько ячеек.
При использовании Excel для анализа данных точность является самой важной задачей. Неверная информация приводит к срыву сроков, неверной оценке тенденций, неправильным решениям и упущенной выгоде.
Хотя ваши формулы Excel будут абсолютно правильными, их результаты могут быть неверными из-за неверных значений в ваших электронных таблицах. Вручную сравнить две ячейки легко, но практически невозможно определить разницу между сотнями и тысячами текстовых значений и чисел.
- Как сравнить две ячейки между прописными и строчными буквами и между прописными и строчными буквами.
- Сравните несколько ячеек одновременно.
- Сравнение нескольких ячеек с образцом
- В какой ячейке самое длинное содержимое?
- Являются ли ячейки равными с точки зрения количества вхождений определенного символа?
В этом руководстве вы узнаете, как автоматизировать утомительную и подверженную ошибкам задачу сравнения ячеек, а также лучшие формулы для использования в каждом конкретном случае.
Как сравнить две ячейки в Excel.
Существует два разных способа узнать идентичность текстовых данных в Excel, в зависимости от того, хотите ли вы сделать их чувствительными к регистру или нет.
Формула без учета регистра.
Чтобы сопоставить две ячейки Excel без учета регистра, используйте самый простой вариант:
=А1=В1
Где A1 и B1 — объекты для сопоставления. Результатом являются логические значения ИСТИНА и ЛОЖЬ.
Если вы хотите сгенерировать свои собственные сообщения для указания совпадений и различий в результате, вставьте приведенный выше оператор в логическую проверку функции ЕСЛИ. Например:
=ЕСЛИ(A2=B2, «Совпадает», «НЕ совпадает»)
Как вы можете видеть на скриншоте ниже, обе формулы одинаково хорошо сравнивают текст, даты и числа:
Формула с учетом регистра.
В некоторых ситуациях может быть важно не только сравнивать текст, но и учитывать регистр символов. Сопоставление с учетом регистра может быть выполнено с помощью функции Excel EXACT):
ТОЧНО(текст1, текст2)
Где text1 и text2 — две сравниваемые ячейки.
Предполагая, что ваши значения находятся в A2 и B2, расчет выглядит следующим образом:
=ТОЧНО(A2, B2)
Результатом будет TRUE для текстовых данных, точно соответствующих регистру каждого символа, иначе FALSE.
Если вы хотите, чтобы функция EXACT давала другие результаты, вставьте ее в формулу ЕСЛИ и введите собственный текст для вариантов ответа:
=ЕСЛИ(ТОЧНО(A2, B2), «Совпадение», «НЕ совпадение»)
На следующем снимке экрана показаны результаты с учетом регистра:
Как сравнить несколько ячеек в Excel
Чтобы сопоставить более 2 ячеек, используйте выражения, описанные в предыдущих примерах, в сочетании с оператором И.
Формула без учета регистра для более чем двух ячеек
В зависимости от того, как вы хотите отобразить результаты, используйте одну из следующих формул:
=И(А2=В2; А2=С2)
или
=ЕСЛИ(И(A2=B2, A2=C2), «Совпадение», «НЕ совпадение»)
Оператор AND возвращает значение TRUE, если все ячейки таблицы содержат одно и то же значение, и FALSE, если какое-либо значение отличается. Функция ЕСЛИ распечатывает введенные вами метки: «Совпадение» и «Нет совпадения».
Как показано на скриншоте ниже, формула отлично работает с любым типом данных: текстом, датами и числовыми значениями:
Сравниваем несколько ячеек с учетом регистра букв.
Чтобы сопоставить несколько текстовых значений друг с другом и убедиться, что они точно совпадают, используйте:
= И (ТОЧНО (A2, B2), ТОЧНО (A2, C2))
или
=ЕСЛИ(И(ТОЧНОЕ(A2,B2),ТОЧНОЕ(A2,C2)),»Точное совпадение»,»НЕ совпадение»)
Как и в предыдущем примере, первая формула возвращает ИСТИНА и ЛОЖЬ, а вторая возвращает собственные сообщения о совпадениях и различиях:
Если нам достаточно хотя бы двух совпадений значений, то сделаем так:
=ЕСЛИ(ИЛИ(ТОЧНО(A2,B2),ТОЧНО(A2,C2),ТОЧНО(B2,C2)), «По крайней мере 2 точных совпадения», «НЕ совпадает»)
То есть, если два случая из трех имеют одинаковые значения, то это будет для нас положительным результатом.
Но если нам нужно проверить много ячеек в нашей таблице, то приведенное выше выражение с условием ИЛИ станет очень громоздким. Поэтому вы можете использовать функцию СЧЁТЕСЛИ.
Мы можем рассчитать количество совпадений в нашем диапазоне ячеек с помощью этой формулы массива:
{=СЧЁТЕСЛИ(A2:D2,A2:D2)}
Чтобы выбрать группы ячеек с точно такими же значениями, вы можете использовать выражение:
=ЕСЛИ(СЧЁТ.ЕСЛИ(A2:D2,A2:D2)=СЧЁТ(A2:D2),»Точное совпадение»;»»)
Как видите, здесь, к сожалению, не учитывается регистр символов.
Также обратите внимание на последнюю строку. В одной из ячеек есть ведущие пробелы, поэтому полного совпадения нет. Аналогичный результат будет, если после текста случайно будет вставлен так называемый завершающий пробел. И обнаружить его визуально крайне сложно. Поэтому обнаружение и удаление лишних пробелов — очень важная задача. См ссылку на инструкции в конце этой статьи.
Сравните диапазон ячеек с образцом.
В следующих примерах показано, как можно убедиться, что все адреса в заданном диапазоне содержат тот же текст, что и в образце ячейки.
Совпадает ли ячейка с образцом?
Если регистр не важен, вы можете использовать такое выражение для сопоставления с образцом:
СТРОКА(диапазон) * ЧИСЛО(диапазон) = СЧЁТЕСЛИ(диапазон, ячейка шаблона)
В логическом доказательстве функции ЕСЛИ вы проверяете, равны ли два числа:
- Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов) и
- Количество ячеек, содержащих то же значение, что и шаблон (возвращаемый функцией СЧЁТЕСЛИ).
Предполагая, что образец текста находится в C2, а данные находятся в диапазоне A2:B6, формула выглядит следующим образом:
(СТРОКИ(A2:B6) * СТОЛБЦ(A2:B6)) = СЧЁТЕСЛИ(A2:B6,C2)
Чтобы упростить использование результатов, т е отображать осмысленный текст вместо ИСТИНА и ЛОЖЬ, используйте функцию ЕСЛИ, как мы делали в предыдущих примерах:
=ЕСЛИ((СТРОКИ(A2:B6)*СТОЛБЦ(A2:B6))=СЧЁТЕСЛИ(A2:B6,C2),»Все совпадения», «Нет совпадений»)
Как показано на снимке экрана выше, формула отлично работает для диапазона текстовых значений, но ее также можно использовать для сопоставления чисел и дат.
Проверяем совпадение с образцом с учётом регистра букв.
Если регистр важен, вы можете проверить, соответствует ли ячейка образцу текста, используя следующие формулы массива.
ЕСЛИ((СТРОКИ(диапазон)*ЧИСЛО(диапазон))=(СУММ(—EXACT(шаблон, диапазон))); «text_IF_matches»;»text_IF_NOT_matches»)
Поскольку исходный диапазон находится в области A2:B6, а образец текста — в C2, мы получаем следующее:
{=ЕСЛИ((СТРОКИ(B2:C6)*СТОЛБЦ(B2:C6))=(СУММ(—EXACT(C2,B2:C6))), «Все совпадения»;»НЕ совпадения»)}
В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter. При правильном вводе Excel заключает формулу массива в {скобки}, как показано ниже:
Как соотнести две ячейки по длине.
Иногда вам может понадобиться проверить, содержат ли данные в каждой ячейке одинаковое количество символов. Решение этой проблемы очень простое. Сначала вы получаете длину каждой из этих двух ячеек с помощью функции LVL, а затем проверяете, равны ли эти числа.
Предположим, исходные значения находятся в A2 и B2. Используйте любой из вариантов:
=ДЛИНА(A2)=ДЛИНА(B2)
или
=ЕСЛИ(Длина(A2)=Длина(B2), «Одинаковая длина», «Разная длина»)
Как вы понимаете, первый возвращает логические значения TRUE или FALSE, а второй генерирует ваши собственные результаты:
Как показано на скриншоте выше, формулы работают как для текста, так и для чисел.
Примечание. Если две, казалось бы, идентичные ячейки с текстом возвращают разную длину, проблема, скорее всего, связана с начальными или конечными пробелами в одной или обеих из них. В этом случае удалите лишние пробелы с помощью функции TRIM. Вы можете найти подробное объяснение и примеры здесь: Как удалить пробелы в Excel .
Сравниваем две ячейки по вхождению определенного символа
Это последний пример нашего руководства по сравнению ячеек Excel, и он показывает решение очень конкретной проблемы. Допустим, у вас есть 2 столбца текстовых значений, которые содержат важный для вас символ. Его цель — проверить, содержат ли две ячейки в каждой строке одинаковое количество вхождений данного символа.
Чтобы было понятнее, рассмотрим следующий пример. Предположим, у вас есть два списка отправленных заказов (столбец B) и полученных заказов (столбец C). Каждая запись в таблице содержит заказы на определенный продукт, уникальный идентификатор которого включен во все идентификаторы заказов и указан в одной строке столбца A (см снимок экрана ниже). Вы хотите убедиться, что каждая строка содержит одинаковое количество элементов, отправленных и полученных с этим конкретным идентификатором.
Для решения этой задачи будем действовать следующим образом:
- Во-первых, замените код заказа ничем, используя функцию REPLACE:
ПОДСТАВИТЬ(A1, код;»»)
- Затем подсчитайте, сколько раз код появляется в каждой ячейке. Для этого получите длину вашего контента без кода и вычтите ее из общей начальной длины ячейки. Данную операцию необходимо проделать для ячеек 1 и 2 отдельно, например:
DLSTR(B2) — DLSTR(ЗАМЕНИТЬ(B2, $A2;»»))
так же как
DLSTR(C2)-DLSTR(ЗАМЕНИТЬ(C2, $A2;»»))
- Теперь сделайте эти 2 числа равными, поставив знак равенства (=) между предыдущими частями.
ДЛИНА(B2)-ДЛИНА(ЗАМЕНИТЬ(B2, $A2,»»)) = ДЛИНА(C2)-ДЛИНА(ЗАМЕНИТЬ(C2, $A2;»»))
В нашем примере код находится в A2, а значения — в B2 и C2.
Выражение возвращает TRUE, если B2 и C2 содержат одинаковое количество вхождений символа в A2, и FALSE в противном случае. Чтобы сделать результаты более понятными для ваших пользователей, вы можете встроить их в функцию ЕСЛИ:
=ЕСЛИ(ДЛИНА(B2)-ДЛИНА(ПОДСТАВИТЬ(B2, $A2,»»)) = ДЛИНА(C2)-ДЛИНА(ПОДСТАВИТЬ(C2, $A2,»»)), «Соответствует», «НЕ соответствует»)
Как видно из скриншота выше, все работает нормально, несмотря на пару дополнительных сложностей:
- Считаемый символ (уникальный идентификатор) может появиться в любом месте ячейки.
- Записи содержат переменное количество символов и различные разделители, такие как точки с запятой, запятые или пробелы.
Вот как вы можете использовать формулы для сравнения ячеек в Excel. Спасибо за чтение.
Функция MAX в Excel: как найти максимальное значение: в этой статье объясняется функция MAX с множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число на листе. MAX — один из самых простых…
9 способов сравнить две таблицы в Excel и найти разницу. В этом руководстве вы узнаете о различных методах сравнения таблиц Excel и выявления различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для отчета о различиях, выделять…
Как сравнить два столбца на наличие совпадений и различий — чтение этой статьи займет у вас около 10 минут, а в следующие 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные…
Вычисление номера столбца для извлечения данных в ВПР — Домашнее задание: Самый простой способ узнать, как указать столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем менять саму формулу, так как это может привести к случайным ошибкам.…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц — это задача, которую Excel приходится решать довольно часто. Например, у нас есть старый прайс-лист и его новая версия. Вам нужно посмотреть, какие цены изменились и…
Как проверить правильность ввода данных в Excel? — Подтвердите правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода по существующему списку допустимых значений. В случае правильной записи в отдельной графе поставить…
Функция ЕСЛИ: проверка условия с текстом. Рассмотрите возможность использования функции ЕСЛИ в Excel, если в ячейке есть текст. Условие ContentTest для полнотекстового сопоставления.IF + MATCHИспользуя функцию ЕСЛИ с частичным сопоставлением текста.IF + SEARCHIF…
Визуализация данных с помощью функции ЕСЛИ: Функцию ЕСЛИ можно использовать для вставки символов в таблицу, которые визуально отображают изменения в данных. Например, мы хотим показать в отдельной колонке таблицы, есть увеличение или уменьшение продаж.…
3 примера того, как функция ЕСЛИ работает с датами. — На первый взгляд может показаться, что функцию ЕСЛИ можно использовать для работы с датами точно так же, как и с числовыми и текстовыми значениями, которые мы только что обсуждали. К сожалению, это…
Функция ЕСЛИ в Excel — примеры использования — на примерах посмотрим, как можно использовать функцию ЕСЛИ в Excel, а также какие задачи мы можем с ее помощью решить