Сравнение ячеек в Excel

Сравнение ячеек в Excel
На чтение
23 мин.
Просмотров
60
Дата обновления
06.11.2024

Вы узнаете, как сравнивать значения в ячейках 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 (диапазон; ячейка модели)

В логическом тесте функции ЕСЛИ вы проверяете равенство двух чисел:

  1. Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов) e
  2. Количество ячеек, содержащих одно и то же значение шаблона (возвращаемое функцией СЧЁТЕСЛИ).

Предполагая, что образец текста находится в 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. Спасибо за прочтение.

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий