Как проверить равенство ячеек в Excel

Обновлено: 21.11.2024

Многие компании по-прежнему используют Excel, поскольку он позволяет им хранить различные типы данных, например налоговые отчеты и деловые контакты. Поскольку многие действия в Excel часто выполняются вручную, существует потенциальный риск сохранения ложной информации. Возможно, вы сделали опечатку или неправильно прочитали какое-то число.

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

В этой статье показано, как проверить, имеют ли две ячейки Excel одинаковые значения.

Как проверить повторяющиеся ячейки с помощью точной функции

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

Вот как вы можете использовать функцию Exact Excel.

Допустим, вы работаете с листом с картинки. Как видите, не так просто определить, совпадают ли числа из столбца А с числами из столбца Б.

Чтобы убедиться, что ячейки из столбца A не имеют дубликатов в соответствующих ячейках столбца B, используйте функцию Exact. Например, если вы хотите проверить ячейки A1 и B1, вы можете использовать функцию Exact в ячейке C1.

При использовании этой функции Excel вернет значение ИСТИНА, если числа совпадают, и значение ЛОЖЬ, если они не совпадают.

Как проверить повторяющиеся ячейки с помощью функции ЕСЛИ

Еще одна функция, позволяющая сравнивать две ячейки, — это функция ЕСЛИ. Он сравнивает ячейки из столбца построчно. Давайте используем те же два столбца (A1 и B1), что и в предыдущем примере.

Чтобы правильно использовать функцию ЕСЛИ, вам нужно запомнить ее синтаксис.

Проверка на наличие дубликатов и исправление ошибок

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

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

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

Как сравнить 2 столбца в Excel построчно

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

Пример 1. Сравните два столбца на наличие совпадений или различий в одной строке

Чтобы сравнить два столбца в Excel построчно, напишите обычную формулу ЕСЛИ, которая сравнивает первые две ячейки. Введите формулу в другой столбец той же строки, а затем скопируйте ее в другие ячейки, перетащив маркер заполнения (небольшой квадрат в правом нижнем углу выбранной ячейки). При этом курсор изменится на знак плюса:

Формула совпадений

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

Формула различий

Чтобы найти ячейки в одной строке с разными значениями, просто замените знак равенства на знак отсутствия равенства (<>):

Совпадения и различия

И, конечно же, ничто не мешает вам находить как совпадения, так и различия с помощью одной формулы:

Результат может выглядеть примерно так:

Как видите, формула одинаково хорошо обрабатывает числа, даты, время и текстовые строки.

Пример 2.Сравните два списка совпадений с учетом регистра в одной строке

Как вы, наверное, заметили, формулы из предыдущего примера не учитывают регистр при сравнении текстовых значений, как в строке 10 на снимке экрана выше. Если вы хотите найти совпадения с учетом регистра между двумя столбцами в каждой строке, используйте функцию EXACT:

=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "")

Чтобы найти различия в одной и той же строке с учетом регистра, введите соответствующий текст ("Уникальный" в данном примере) в 3-й аргумент функции ЕСЛИ, например:

=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "Уникальный")

Сравнить несколько столбцов на наличие совпадений в одной строке

На листах Excel несколько столбцов можно сравнивать по следующим критериям:

Найти строки с одинаковыми значениями во всех столбцах (пример 1)
Найти строки с одинаковыми значениями в любых двух столбцах (пример 2)

Пример 1. Поиск совпадений во всех ячейках одной строки

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

=ЕСЛИ(И(A2=B2, A2=C2), "Полное совпадение", "")

Если в вашей таблице много столбцов, более элегантным решением будет использование функции СЧЁТЕСЛИ:

=ЕСЛИ(СЧЁТЕСЛИ($A2:$E2, $A2)=5, "Полное совпадение", "")

Где 5 — количество сравниваемых столбцов.

Пример 2. Поиск совпадений в любых двух ячейках в одной строке

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

=ЕСЛИ(ИЛИ(A2=B2, B2=C2, A2=C2), "Соответствие", "")

Если нужно сравнить много столбцов, оператор ИЛИ может стать слишком большим. В этом случае лучшим решением будет добавление нескольких функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько столбцов имеют то же значение, что и в 1-м столбце, второй СЧЁТЕСЛИ подсчитывает, сколько из оставшихся столбцов равны 2-му столбцу, и так далее. Если счетчик равен 0, формула возвращает «Уникальный», в противном случае — «Совпадение». Например:

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2,A2)+СЧЁТЕСЛИ(C2:D2,B2)+(C2=D2)=0,"Уникальный","Совпадение")

Как сравнить два столбца в Excel на совпадения и различия

Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые строки), которые находятся в столбце A, но не в столбце B.

Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B, $A2)=0 в логическую проверку ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (как минимум 1 совпадение). найдено).

Например, следующая формула ЕСЛИ/СЧЕТЕСЛИ ищет по всему столбцу B значение в ячейке A2. Если совпадений не найдено, формула возвращает «Нет совпадений в B», в противном случае — пустую строку:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "")

Совет. Если в вашей таблице фиксированное количество строк, вы можете указать определенный диапазон (например, $B2:$B10), а не весь столбец ($B:$B), чтобы формула работала быстрее с большими наборами данных.

Того же результата можно добиться, используя формулу ЕСЛИ со встроенными функциями ЕОШИБКА и ПОИСКПОЗ:

=IF(ISERROR(MATCH($A2,$B$2:$B$10,0)),"Нет совпадений в B","")

Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):

=IF(SUM(--($B$2:$B$10=$A2))=0, "Нет совпадений в B", "")

Если вы хотите, чтобы одна формула определяла как совпадения (дубликаты), так и различия (уникальные значения), поместите некоторый текст для совпадений в пустые двойные кавычки ("") в любой из приведенных выше формул. Например:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "Соответствует в B")

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

Иногда может потребоваться не только сопоставить два столбца в двух разных таблицах, но и извлечь совпадающие записи из таблицы поиска. В Microsoft Excel для этого предусмотрена специальная функция — функция ВПР. В качестве альтернативы вы можете использовать более мощную и универсальную формулу INDEX MATCH. Пользователи Excel 2021 и Excel 365 могут выполнить эту задачу с помощью функции XLOOKUP.

=ВПР(D2, $A$2:$B$6, 2, ЛОЖЬ)

=ИНДЕКС($B$2:$B$6, ПОИСКПОЗ($D2, $A$2:$A$6, 0))

Если вам не очень удобно работать с формулами, вы можете выполнить эту работу с помощью быстрого и интуитивно понятного решения — Мастера объединения таблиц.

Сравните два списка и выделите совпадения и различия

При сравнении столбцов в Excel может потребоваться «визуализировать» элементы, которые присутствуют в одном столбце, но отсутствуют в другом. Вы можете закрасить такие ячейки любым цветом по вашему выбору, используя функцию условного форматирования Excel, и следующие примеры демонстрируют подробные шаги.

Пример 1. Выделение совпадений и различий в каждой строке

Чтобы сравнить два столбца и Excel и выделить ячейки в столбце A, которые имеют идентичные записи в столбце B в той же строке, выполните следующие действия:

Выберите ячейки, которые хотите выделить (вы можете выбрать ячейки в одном столбце или в нескольких столбцах, если хотите закрасить целые строки).
Нажмите Условное форматирование > Новое правило… > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок столбца). Убедитесь, что вы используете относительную ссылку на строку (без знака $), как в приведенной выше формуле.

Чтобы выделить различия между столбцами A и B, создайте правило со следующей формулой:

=$B2<>$A2

Если вы не знакомы с условным форматированием в Excel, пошаговые инструкции см. в разделе Как создать правило условного форматирования на основе формулы.

Пример 2. Выделение уникальных записей в каждом списке

Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:

Элементы, находящиеся только в первом списке (уникальные)
Элементы, находящиеся только во втором списке (уникальные)
Элементы, которые есть в обоих списках (дубликаты) — показано в следующем примере.

В этом примере показано, как раскрасить элементы только в одном списке.

Предположим, что ваш список 1 находится в столбце A (A2:A6), а список 2 — в столбце C (C2:C5). Вы создаете правила условного форматирования со следующими формулами:

Выделить уникальные значения в списке 1 (столбец A):

Выделить уникальные значения в списке 2 (столбец C):

И получите следующий результат:

Пример 3. Выделение совпадений (дубликатов) между двумя столбцами

Если вы точно следовали предыдущему примеру, у вас не возникнет трудностей с настройкой формул СЧЁТЕСЛИ, чтобы они находили совпадения, а не различия. Все, что вам нужно сделать, это установить счетчик больше нуля:

Выделить совпадения в списке 1 (столбец A):

Выделить совпадения в списке 2 (столбец C):

Выделить различия строк и совпадения в нескольких столбцах

При сравнении значений в нескольких столбцах построчно самый быстрый способ выделить совпадения — создать правило условного форматирования, а самый быстрый способ скрыть различия — воспользоваться функцией Перейти к специальному, как показано в следующих примерах.

Пример 1. Сравните несколько столбцов и выделите совпадения строк

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

Где A2, B2 и C2 — самые верхние ячейки, а 3 — количество столбцов для сравнения.

Конечно, ни формула И, ни СЧЁТЕСЛИ не ограничиваются сравнением только 3 столбцов, вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбцах.

Пример 2. Сравните несколько столбцов и выделите различия в строках

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

По умолчанию самая верхняя ячейка выбранного диапазона является активной ячейкой, и ячейки из других выбранных столбцов в той же строке будут сравниваться с этой ячейкой. Как вы можете видеть на снимке экрана выше, активная ячейка имеет белый цвет, а все остальные ячейки выбранного диапазона выделены. В этом примере активной ячейкой является A2, поэтому столбцом сравнения является столбец A.

Чтобы изменить столбец сравнения, используйте либо клавишу Tab для перемещения по выбранным ячейкам слева направо, либо клавишу Enter для перемещения сверху вниз.

Совет. Чтобы выбрать несмежные столбцы, выберите первый столбец, нажмите и удерживайте клавишу Ctrl , а затем выберите другие столбцы. Активная ячейка будет в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу Tab или Enter, как описано выше.

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

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

Например, для сравнения ячеек A1 и C1 можно использовать следующие формулы.

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

Способ сравнения двух столбцов/списков в Excel без формул

Теперь, когда вы знакомы с предложениями Excel для сравнения и сопоставления столбцов, позвольте мне показать вам наше собственное решение для этой задачи. Этот инструмент называется «Сравнить две таблицы» и входит в состав Ultimate Suite.

Надстройка может сравнивать две таблицы или списки по любому количеству столбцов, а также выявлять совпадения/различия (как мы делали с формулами) и выделять их (как мы делали с условным форматированием).

Для целей этой статьи мы сравним два следующих списка, чтобы найти общие значения, присутствующие в обоих.

Чтобы сравнить два списка, выполните следующие действия:

Повторяющиеся значения (совпадения) — элементы, существующие в обоих списках.
Уникальные значения (различия) — элементы, которые присутствуют в списке 1, но отсутствуют в списке 2.

Поскольку нашей целью является поиск совпадений, мы выбираем первый вариант и нажимаем Далее.

Здесь доступно несколько различных вариантов. Для наших целей эти два наиболее полезны:

Выделение цветом: оттенки совпадают или различаются в выбранном цвете (например, условное форматирование в Excel).
Идентифицировать в столбце "Статус" — вставляет столбец Статус с метками "Дубликат" или "Уникальный" (как в формулах ЕСЛИ).

Для этого примера я решил выделить дубликаты следующим цветом:

И через мгновение получил следующий результат:

Для столбца Статус результат будет выглядеть следующим образом:

Таким образом вы сравниваете столбцы в Excel на совпадения (дубликаты) и различия (уникальные значения). Если вам интересно попробовать этот инструмент, вы можете загрузить ознакомительную версию по ссылке ниже.

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

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

Простой способ сравнения двух ячеек

Самый быстрый способ сравнить две ячейки – использовать формулу, в которой используется знак равенства.

Если содержимое ячеек одинаковое, результатом будет TRUE. (Версии одной и той же буквы в верхнем и нижнем регистре считаются одинаковыми).

Игнорировать лишние пробелы

Если вы просто хотите сравнить две ячейки, но не беспокоитесь о начальных пробелах, конечных пробелах или лишних пробелах, используйте функцию ОБРЕЗ, чтобы удалить их для одной или обеих ячеек.

Это может помочь, если вы пытаетесь сопоставить текстовые строки со значениями в импортированном списке, например в этом примере ВПР.

Точное сравнение двух ячеек

Если вам нужно сравнить содержимое двух ячеек и верхний/нижний регистр, используйте функцию EXACT. В этом видео показано несколько ТОЧНЫХ примеров.

Как следует из названия, функция EXACT может проверять наличие точного совпадения между текстовыми строками, включая верхний и нижний регистр.

Однако форматирование не проверяется, поэтому он не определяет, выделены ли в одной ячейке некоторые или все символы полужирным шрифтом, а в другой — нет.

Дополнительные примеры функций EXACT см. в моей серии "30 функций Excel".

Частично сравнить две ячейки

Иногда полное сравнение двух ячеек не требуется — достаточно проверить первые несколько символов или трехзначный код в конце строки.

Чтобы сравнить символы в начале ячеек, используйте функцию ВЛЕВО. Например, проверьте первые 3 символа:

Чтобы сравнить символы в конце ячеек, используйте функцию ПРАВИЛЬНО. Например, проверьте последние 3 символа:

Вы можете комбинировать LEFT или RIGHT с TRIM, если вас не беспокоят пробелы:

И объедините LEFT или RIGHT с EXACT, чтобы проверить, совпадают ли прописные и строчные буквы. Эта формула игнорирует лишние пробелы, но проверяет регистр:

=EXACT(ПРАВО(ОТРЕЗКА(A2),3),ПРАВО(ОТРЕЗКА(B2),3))

Насколько совпадают ячейки?

Наконец, вот формула от UniMord, которая должна знать, сколько совпадений существует между двумя ячейками. Первые 5 символов одинаковые? Первые 10? Какой процент строки в A2, начиная слева, совпадает с ячейкой B2?

Вот пример списка, в котором адреса в столбцах A и B сравниваются.

Получить длину текста

Первый шаг в вычислении процента совпадения ячеек – это определение длины адреса в столбце A. Эта формула находится в ячейке C2:

Получить длину совпадения

Формула в столбце D выполняет тяжелую работу. Он находит, сколько символов, начиная слева в каждой ячейке, совпадают. Нижний и верхний регистр не сравниваются.

=СУММПРОИЗВ(
–(ЛЕВАЯ(A3,
СТРОКА(ДВССЫЛ(“A1:A” & C3)))
=ЛЕВАЯ(B3,
СТРОКА(ДВССЫЛ ("A1:A" &C3)))))

Как работает формула Match Len

Функция ДВССЫЛ создает ссылку на диапазон ячеек, начиная с ячейки A1. Диапазон заканчивается в столбце A, в строке, которая соответствует длине, рассчитанной в столбце C. Таким образом, в строке 2 этот диапазон равен A1:A9.

Функция СТРОКА возвращает строку для каждой из строк в этом диапазоне. Вот почему мы используем СТРОКА/ДВССЫЛ, а не просто ссылаемся на длину в ячейке C2.

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

Затем функции LEFT возвращают символы, расположенные на 1, 2, 3… символа слева в каждой ячейке. На этом снимке экрана я вычислил обе ЛЕВЫЕ функции, и вы видите совпадение для длин от 1 до 9.

Однако, если я сделаю то же самое в строке 5, совпадет только первый символ. После этого символы в двух ячейках различаются.

Знак равенства сравнивает значения символов с 1 по 5 в этом примере и возвращает ИСТИНА, если они совпадают, и ЛОЖЬ, если они не совпадают.

Двойной знак минус преобразует каждое значение ИСТИНА в 1, а каждое ЛОЖЬ в ноль.

Наконец, функция СУММПРОИЗВ складывает эти числа, чтобы получить количество совпадающих символов слева. В строке 5 эта сумма равна 1

Получить процентное соответствие

После того как длина и длина совпадения будут рассчитаны, легко найти процент совпадений. Эта формула находится в ячейке E2 для сравнения длин:

Совпадение 100 % во второй строке и только 20 %, начиная слева, в строке 5.

Спасибо, UniMord, за то, что поделились своей формулой для сравнения двух ячеек посимвольно.

Дополнительные способы сравнения двух ячеек

Вот еще несколько статей, в которых показаны примеры того, как сравнивать две ячейки — полное или частичное содержимое.

Используйте ИНДЕКС, ПОИСКПОЗ и СЧЁТЕСЛИ, чтобы найти коды в текстовых строках. В комментариях есть и другие формулы, так что ознакомьтесь с ними.
Сравнивайте формулы на разных листах с помощью функций ФОРМУЛАТЕКСТ и ДВССЫЛ. Однако эти функции нестабильны, поэтому они могут замедлить работу книги, если вы используете слишком много из них.
Будьте осторожны при использовании функции "Удалить дубликаты" в Excel: она обрабатывает действительные числа и текстовые числа как одно и то же значение.

24 мысли о том, «Как сравнить две ячейки в Excel»

Я пытаюсь сравнить 2 столбца с комментариями "Да" или "Нет" в них, используя функцию ЕСЛИ, но хочу различать столбцы, в которых комментарии "Да/Да", "Да/Нет" (в любом случае) и "Нет/Нет" (с результатами — одинаковыми (Истина) или разными (Ложь) в третьем столбце. До сих пор мне удалось выяснить, как получить те, которые отличаются (Да/Нет) или одинаковы (Да/Да и Нет). /Нет), но нужно уметь разделять ответы Да/Да и Нет/Нет, так как если у них есть совпадающие ответы, один хороший, а другой плохой!

Идеи с благодарностью приняты!
PQ

Полина, если вы посчитаете комментарии "да", это может помочь вам. Например, если комментарии находятся в B2 и C2:
=СЧЁТЕСЛИ(B2:C2,"Да")

Как сравнить более двух ячеек? Я использовал эту формулу, но она не точна. Мне действительно не нужно, чтобы он был чувствителен к регистру, поэтому я также удаляю «EXACT», но все равно не работает.
=И(ТОЧНО(B23,K23),ТОЧНО(B23,M23),ТОЧНО(B23,Q23),ТОЧНО(B23,V23))

Мне удалось сравнить две ячейки с помощью этой формулы, но добавление дополнительных ячеек привело к ошибке.
=ЕСЛИ(ИЛИ(B15=Q15, ИЛИ(B15=V15)), "Д", "Н")

Мы очень ценим любую рекомендацию!

Хайме, ваша формула сработала, когда я попробовал ее. Если вы получаете неожиданный результат, возможно, в некоторых ячейках есть пробелы или другие скрытые элементы.
Если вам не нужен учет регистра, вы также можете использовать это:
=AND(B23=K23,B23=M23,B23=Q23,B23=V23)

Большое спасибо, Дебра.
С наилучшими пожеланиями
PQ

Я хочу найти общее количество несовпадений между двумя текстами. Ваше решение, приведенное выше, остановится на первом несоответствии, а затем будет считать все символы после этого несоответствием. Но есть ли способ сравнить букву в букву.
В ячейке A1 у меня есть Ричард, а в ячейке B1 у меня есть Рикард. Итак, в ячейке C1 я хотел бы получить 1, так как несоответствие только символов
A1 – 123abcd456 B1 = 14xy456 C1 = 9 . Так как 9 символов из A1 не совпадают с B1. Есть ли способ сделать это. Пожалуйста, помогите

Использовать этот макрос VBA

Public Sub DifferentString()
Dim a() As Byte, b() As Byte, a_$, b_$, i&, j&, d&, u&, l&, x&, y&, f&()
Const GAP = -1
Const PAD = «_»

a = [$a1].Text: b = [$b1].Text
[a3:a6].Clear
[a1:a6].Font.Name = «Courier New»< /p>

ReDim f(0 в UBound(b) \ 2 + 1, 0 в UBound(a) \ 2 + 1)

Для i = 1 To UBound(f, 1)
Для j = 1 To UBound(f, 2)
x = j – 1: y = i – 1
Если a (x * 2) = b(y * 2) Тогда
d = 1 + f(y, x)
u = 0 + f(y, j)
l = 0 + f (i, x)
иначе
d = -1 + f(y, x)
u = GAP + f(y, j)
l = GAP + f(i , x)
Конец Если
f(i, j) = Max(d, u, l)
Следующий
Следующий

i = UBound(f, 1): j = UBound(f, 2)
При ошибке Возобновить Далее
Выполнить
x = j – 1: y = i – 1
d = f(y, x)
u = f(y, j)
l = f(i, x)
Выбрать регистр True
Регистр Err
Если y = u И d >= l Или Mid$(a, j, 1) = Mid$(b, i, 1)
diag:
a_ = Mid$(a, j, 1) ) & a_
b_ = Mid$(b, i, 1) & b_
i = i – 1: j = j – 1
Случай u > l
up: < br />a_ = PAD & a_
b_ = Mid$(b, i, 1) & b_
i = i – 1
Случай l > u
слева:
a_ = Mid$(a, j, 1) & a_
b_ = PAD & b_
j = j – 1
End Select
Цикл до ib Затем Max = c
Завершить функцию

Private Sub DecorateStrings(a$, b$, rOutA As Range, rOutB As Range, PAD$)
Dim i&, j&

FloatArtifacts a, b, PAD
FloatArtifacts b, a, PAD

rOutA = a
rOutB = b

Private Sub FloatArtifacts(s1$, s2$, PAD$)
Dim c&, k&, i&, p&
For i = 1 To Len(s1)
c = InStr(i , s1, PAD)
Если c То
k = 0
Do
k = k + 1
Если Mid$(s1, c + k, 1) PAD Тогда
Если Mid$(s2, c, 1) = Mid$(s1, c + k, 1), то
p = InStr(c + k, s1, PAD)
Если p 0 Тогда
Mid$(s1, c, 1) = Mid$(s1, c + k, 1)
Mid$(s1, c + k, 1) = PAD
i = c
Выход Do
Еще
i = c + k
Выход Do
Конец If
Else
i = c + k
>Exit Do
End If
End If
If c + k > Len(s1) Then Exit Do
Loop
Else
Exit For
>Конец, если
Следующий
Конец подпункта

Здравствуйте!
Если мне нужно сравнить в общей сложности 5 ячеек без учета регистра. Каков наилучший способ? Я попробовал это, но это не сработало, я также вынул 'ТОЧНОЕ'
= И (ТОЧНОЕ (B23, K23), ИСТОЧНОЕ (B23, M23), ИСТОЧНОЕ (B23, Q23), ИСТОЧНОЕ (B23, V23) ))

Мне удалось сравнить две ячейки с помощью этой формулы, но при добавлении остальных появилось сообщение об ошибке.

=ЕСЛИ(ИЛИ(B17=Q17, ИЛИ(B17=V17)), "Д", "Н")

Пожалуйста, помогите сегодня. Заранее спасибо!

Спасибо, Дебра!!
Еще вопрос 🙂
Как написать формулу, если хотя бы одна из ячеек совпадает с B23, а потом вернуть True? Мне не нужно, чтобы все ячейки сравнения совпадали, чтобы это было True, достаточно одного совпадения.

Привет, Дебра, пожалуйста, не обращайте внимания на мой предыдущий пост, так как я использовал приведенную ниже формулу, она работает на большей части моего листа, за исключением трех ячеек, которые возвращаются как True, но это не точно, потому что B23 пусто. Я проверил формат ячеек, и он согласуется с другими, поэтому я не знаю, почему эти три ячейки имеют такой результат. В результате должно быть False.

Есть ли способ сделать это?

Привет, Дебра,
Я пытаюсь получить правильную формулу для условного форматирования (выделения ячеек). Все ячейки числовые.
В определенном столбце я хочу выделить все ячейки, значение которых выше, чем предыдущая/выше ячейка.
Можете ли вы помочь.
PS: я знаю, как сделать условное форматирование, но не могу получить правильную формулу.
Большое спасибо,
Радж

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

Я могу заставить его работать для одного набора, используя эту формулу
=СУММ(ЕСЛИ(И((L12=”1000 мс” ), (E12=”S”)),1,0))

но я бы хотел, чтобы он увеличивался на ряд записей в этих столбцах, таких как
=СУММ(ЕСЛИ( И((L12=”1000 мс”), (E12=”S”)), 1,0))
=СУММ(ЕСЛИ( И((L13=”1000 мс” ), (E13=”S”)),1,0))
=СУММ(ЕСЛИ( И( (L14=”1000 мс” ), (E14=”S”)),1,0))
=СУММ(ЕСЛИ( И((L15=”1000 мс” ), (E15=”S”) ),1,0))

Вместо этого попробуйте эту формулу. $ блокирует ссылку на строку 12, поэтому отсчет всегда будет начинаться с нее.
=COUNTIFS(L$12:L12",1000s",E$12:E12",S")

Привет, Дебра!
Все сработало отлично, спасибо вам огромное! – Майк

Здравствуйте,
Я пытаюсь провести регрессионное тестирование двух файлов Excel. Сравните, если все строки, столбцы равны. Некоторые из них являются текстовыми, некоторые — числовыми значениями.
Я использовал суммы столбцов для сравнения числовых данных и сводную таблицу для агрегирования. это помогло.
Но как мне сравнить два столбца, которые имеют текстовые/строковые значения.
Есть ли быстрый способ сравнения с помощью какой-либо агрегатной функции, которая «суммирует» весь столбец с текстовыми данными? думал о хеш-функции, но не уверен, что это правильно и как мне построить/использовать ее в Excel.
Может быть 20 000 строк и 40 столбцов со смешанными числовыми строковыми данными.
Необходимо использовать его в финансовых данных в инвестиционном банке.
Есть предложения?

Я хочу сравнить два столбца в excle, которые содержат буквенно-цифровые данные.. Я хочу сравнить и выделить конкретную несовпадающую букву или цифру.. Есть ли способ, которым я хочу сравнить столбцы a и b. Бывший. Vinayak27 в колонке A и vinayck27 в томе b … Я что-то не понял с выделенным несоответствием в колонке c…

Например:
Я хочу сравнить ячейку A1 с ячейкой A3. Если они совпадают, ячейка A2 = A1
т.е. A1=3 , A3=3, затем A2=3

Если они одинаковы или МЕНЬШЕ, то число в ячейке A2 такое же, как в ячейке A1.
т.е. A1=3,2 или 1 и A3=3, затем A2=A1

Если число в ячейке A1 БОЛЬШЕ, чем число в ячейке A3, то число в ячейке A2 увеличивается на разницу, но не более чем в 2 раза.
т.е. A1=4 и A3=3, тогда A2=(A3+1)=4
т.е. A1=5 и A3=3, тогда A2=(A3+2)=5
т.е. A1=6 или более и A3=3, тогда A2 по-прежнему увеличивается только x2 =(A3+2)=5

Будем признательны за любую оказанную помощь..

Здравствуйте,
надеемся, что все живы и здоровы.
Я был бы признателен за вашу помощь в сравнении ячеек суммы, и если они совпадают, то да, если нет, то чтобы дать мне сумму отклонения.
т.е. 1) A1=3, B1=3 : если числа совпадают, то C=Да
т.е. 2) A2=3, B2=4: если число не совпадает, то C=-1
Спасибо

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

Как мне сравнить 2 столбца, если они не совпадают из-за последовательности, например, у меня есть 2 адреса электронной почты в столбце A ([email protected], [email protected] ) и в столбце B ([email protected], [email protected] ), так как адреса электронной почты и текст одинаковы, но я получаю результат «False» только потому, что последовательность адресов электронной почты в обоих столбцах различна. Есть ли способ сопоставить этот тип текста в 2 столбцах.

Как сравнить 2 текстовых значения из 2 разных ячеек и показать значения сходства в процентах.

Например:
A1 = Это пример
B1 = Это пример
Результат: 100%

A1 = Для примера
B1 = Это пример
Результат: 50%

мне нужно сравнить число в 1 столбце с числом в другом столбце, и если они одинаковы, то выдать ответ «равно» – если они не совпадают, мне нужно произвести + или – разницу и цветовой код клетка. Как мне написать эту формулу?

Есть ли способ идентифицировать ячейки, в которых (числовое) значение совпадает со значением в ячейке непосредственно над ним в столбце, в идеале путем изменения форматирования?

Оставить ответ Отменить ответ

Этот сайт использует Akismet для уменьшения количества спама. Узнайте, как обрабатываются данные ваших комментариев.

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

Логические операторы Excel — обзор

Логический оператор используется в Excel для сравнения двух значений. Логические операторы иногда называют булевыми операторами, потому что результат сравнения в любом конкретном случае может быть либо ИСТИНА, либо ЛОЖЬ.

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

Однако, если вы попробуете либо =A1=12/1/2014, либо =A1="12/1/2014", в результате вы получите ЛОЖЬ. Немного неожиданно, да?

Дело в том, что Excel хранит даты как числа, начинающиеся с 1 января 1900 года, которые сохраняются как 1. Дата 1/12/2014 хранится как 41974. В приведенных выше формулах Microsoft Excel интерпретирует «12/1». /2014" как обычная текстовая строка, а поскольку "12/1/2014" не равно 41974, возвращается FALSE.

Чтобы получить правильный результат, вы всегда должны заключать дату в функцию ДАТАЗНАЧ, например: =A1=ДАТАЗНАЧ("1/12/2014")

Примечание. Функцию DATEVALUE необходимо использовать и с другим логическим оператором, как показано в следующих примерах.

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

Пример 2.Использование оператора "Равно" с текстовыми значениями

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

Например, если ячейка A1 содержит слово "апельсины", а ячейка B1 содержит слово "апельсины", формула =A1=B1 вернет значение TRUE.

Если вы хотите сравнить текстовые значения с учетом различий в регистре, вам следует использовать функцию EXACT вместо оператора Равно. Синтаксис функции EXACT прост:

Где текст 1 и текст 2 — это значения, которые вы хотите сравнить. Если значения совпадают, включая регистр, Excel возвращает TRUE; в противном случае возвращается ЛОЖЬ. Вы также можете использовать функцию EXACT в формулах ЕСЛИ, когда вам нужно сравнение текстовых значений с учетом регистра, как показано на снимке экрана ниже:

Примечание. Если вы хотите сравнить длину двух текстовых значений, вы можете вместо этого использовать функцию ДЛСТР, например =ДЛСТР(A2)=ДЛСТР(B2) или =ДЛСТР(A2)>=ДЛСТР(B2) .

Пример 3. Сравнение логических значений и чисел

Распространено мнение, что в Microsoft Excel логическое значение TRUE всегда равно 1, а FALSE — 0. Однако это верно лишь отчасти, и ключевое слово здесь — «всегда», а точнее «не всегда». : )

При написании логического выражения "равно" для сравнения логического значения и числа необходимо специально указать для Excel, что нечисловое логическое значение должно рассматриваться как число. Вы можете сделать это, добавив двойной знак минус перед логическим значением или ссылкой на ячейку, например. грамм. =A2=--ИСТИНА или =A2=--B2 .

Первый знак минус, который технически называется унарным оператором, приводит ИСТИНА/ЛОЖЬ к -1/0 соответственно, а второй унарный инвертирует значения, превращая их в +1 и 0. Это, вероятно, будет проще понять, глядя на следующий скриншот:

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

При использовании логических операторов в сложных формулах вам также может понадобиться добавить двойное унарное выражение перед каждым логическим выражением, которое возвращает ИСТИНА или ЛОЖЬ в качестве результата. Вот пример такой формулы: СУММПРОИЗВ и СУММЕСЛИМН в Excel.

Использование логического оператора «Не равно» в Excel

Вы используете оператор Excel Не равно (<>), когда хотите убедиться, что значение ячейки не равно указанному значению. Использование оператора Не равно очень похоже на использование оператора Равно, которое мы только что обсуждали.

Результаты, возвращаемые оператором Не равно, аналогичны результатам, полученным функцией НЕ Excel, которая инвертирует значение своего аргумента. В следующей таблице приведены несколько примеров формул.

< tr> < td>=NOT(A1=DATEVALUE("1/12/2014"))

Не равно оператору	НЕ функция	Описание
=A1<>B1	=NOT(A1=B1)	Возвращает TRUE, если значения в ячейках A1 и B1 не совпадают, в противном случае FALSE.
=A1<>"апельсины"	=NOT(A1="апельсины ")	Возвращает TRUE, если ячейка A1 содержит любое значение, кроме "апельсинов", FALSE, если она содержит "апельсины" или "АПЕЛЬСИНЫ" или "апельсины" и т. д.
=A1<>TRUE	=NOT(A1=TRUE)	Возвращает TRUE, если ячейка A1 содержит любое значение, отличное от TRUE, иначе FALSE.
=A1<>(B1/2)	=NOT(A1=B1/2)	Возвращает TRUE, если число в ячейке A1 не равно частному от деления B1 на 2, в противном случае FALSE.
=A1<>DATEVALUE("1/12/2014")	Возвращает TRUE, если A1 содержит любое значение, кроме даты 1 декабря 2014 г., независимо от даты f ormat, FALSE в противном случае.

Больше, Меньше, Больше или равно, Меньше или равно

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

При сравнении текстовых значений Microsoft Excel игнорирует их регистр и сравнивает значения символ за символом, при этом "a" считается наименьшим текстовым значением, а "z" — самым высоким текстовым значением.

Итак, при сравнении значений слов "яблоки" (A1) и "бананы" (B1) Excel начинает с их первых букв "a" и "b". " соответственно, и поскольку "b" больше, чем "a", формула =A1>B1 возвращает ЛОЖЬ.

Если первые буквы совпадают, то сравниваются вторые буквы, если они тоже совпадают, то Excel получает 3-ю, 4-ю буквы и так далее. Например, если A1 содержит "яблоки", а B1 содержит "агава", формула =A1>B1 вернет значение TRUE, поскольку "p" больше, чем "g".

На первый взгляд кажется, что использование операторов сравнения с текстовыми значениями имеет очень мало практического смысла, но никогда не знаешь, что тебе может понадобиться в будущем, так что, возможно, эти знания кому-то пригодятся.

Распространенное использование логических операторов в Excel

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

1. Использование логических операторов в аргументах функций Excel

Что касается логических операторов, Excel очень либерален и позволяет использовать их в параметрах многих функций. Одно из наиболее распространенных применений находится в функции ЕСЛИ Excel, где операторы сравнения могут помочь построить логический тест, а формула ЕСЛИ вернет соответствующий результат в зависимости от того, оценивается ли тест как ИСТИНА или ЛОЖЬ. Например:

=ЕСЛИ(A1>=B1, "ОК", "НЕ ОК")

Эта простая формула ЕСЛИ возвращает "ОК", если значение в ячейке A1 больше или равно значению в ячейке B1, в противном случае "Не подходит".

А вот еще один пример:

Формула сравнивает значения в ячейках A1 и B1, и если A1 не равно B1, возвращается сумма значений в ячейках A1:C1, в противном случае — пустая строка.

Логические операторы Excel также широко используются в специальных функциях ЕСЛИ, таких как СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ и их аналогах во множественном числе, которые возвращают результат на основе определенного условия или нескольких условий.

Множество примеров формул можно найти в следующих руководствах:

2. Использование логических операторов Excel в математических вычислениях

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

Функция ЕСЛИ: =ЕСЛИ(B2>C2, B2*10, B2*5)

Формула с логическими операторами: =(B2>C2)*(B2*10)+(B2

Думаю, формулу ЕСЛИ интерпретировать легче, верно? Он указывает Excel умножить значение в ячейке B2 на 10, если B2 больше, чем C2, в противном случае значение в ячейке B1 умножается на 5.

Теперь давайте проанализируем, что делает вторая формула с логическими операторами больше чем и меньше или равно. Полезно знать, что в математических вычислениях Excel приравнивает логическое значение ИСТИНА к 1, а ЛОЖЬ к 0. Имея это в виду, давайте посмотрим, что на самом деле возвращает каждое из логических выражений.

Если значение в ячейке B2 больше значения в C2, то выражение B2>C2 равно ИСТИННО и, следовательно, равно 1. С другой стороны, B2 C2 наша формула претерпевает следующее преобразование:

Поскольку любое число, умноженное на ноль, дает ноль, мы можем отбросить вторую часть формулы после знака плюс. И поскольку любое число, умноженное на 1, является этим числом, наша сложная формула превращается в простую =B2*10, которая возвращает произведение умножения B2 на 10, что и делает приведенная выше формула ЕСЛИ : )

Очевидно, что если значение в ячейке B2 меньше, чем в C2, то выражение B2>C2 оценивается как FALSE (0) и B2 =A1

Больше (зеленый): =A1>20

Подробные пошаговые инструкции и примеры правил можно найти в следующих статьях:

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

Читайте также: