Как сравнить два столбца в Excel по строкам
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции ЕСЛИ. Рассмотрим как это работает на примерах ниже.
Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу ЕСЛИ. Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.
Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:
Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:
Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:
или
Пример результата вычислений может выглядеть так:
Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:
Пример 5: Вернуть название по точному совпадению в списке
При поиске в таблице функция EXACT (СОВПАД) может отличить AA1 от Aa1, что позволяет найти правильное название продукта отдельно по каждому коду. Другие функции поиска, такие как VLOOKUP (ВПР), будут рассматривать такие коды как идентичные, и вернут результат, который первым попадётся в списке с таким кодом.
В следующем примере коды продуктов находятся в диапазоне B2:B5, покупатель может заказать продукт, введя его код в ячейку D2.
Формула в ячейке E2 использует 3 функции – INDEX (ИНДЕКС), MATCH (ПОИСКПОЗ) и EXACT (СОВПАД)
- Функция EXACT (СОВПАД) проверяет код, введённый в ячейке D2, и ищет точное совпадение в списке товаров.
- Функция MATCH (ПОИСКПОЗ) возвращает номер строки таблицы, в которой предыдущая функция нашла совпадение. В нашем случае код bG8943TO найден во второй строке диапазона.
- Функция INDEX (ИНДЕКС) возвращает значение из второй строки диапазона A2:A5, т.е. Sam.
Формула введена, как формула массива, нажатием Ctrl+Shift+Enter.
Текстовые функции в Excel. Часть №2
Добрый день уважаемый читатель!
Сегодня хочу продолжить тему текстовых функций в Excel и предлагаю вам вторую часть по этой теме. Всё же текст занимает достаточно значительную часть работы в программе и игнорировать этот факт не будет правильным поведением, а значится нужно учить или хотя бы просто ознакомиться с данной возможностью. Очень часто в работе встречаются задачи которые можно решить зная возможности работы с текстом и это поможет сохранить ваше время и километры нервных волокон при выполнении заданий, которые стоят перед вами. Так как, казалось бы простая функция, а вот ее применение к месту, может решить трудную задачу.
Ну вот, давайте познакомимся с второй частью функций, которые работают с текстом и вашему вниманию предоставляю очередные 7 инструментов:
Для тех, кто не читал первую часть, я повторюсь где можно отыскать весть набор функций. Перейдите на панели инструментов во вкладку «Формулы» и нажмите иконку «Текстовые», в выпадающем меню выберете функцию которая вам нужна, это будет быстрый способ. Вторым вариантом будет в этой же вкладке нажать иконку «Вставить функцию» и в новом диалоговом окне в категории выбираете «Текстовые», но данный способ я считаю лучшим так как снизу окна идет описание выбранной функции что очень полезно.
Как в Эксель сравнить два столбца на совпадения с помощью команды форматирование
Рассмотренным ранее способом не всегда удобно пользоваться. Применяя его нельзя использовать рабочее пространство. Поэтому предлагаю рассмотреть альтернативный способ, который предполагает использование условного форматирования.
Давайте обозначим каждый шаг подробно:
- Находим в верхнем меню блок «Стили». В нем будет находиться кнопка «Условное форматирование».
- Тут нам необходимо найти строку «Создать правило».
- В результате нашему вниманию предстанет диалоговое окно, в котором собраны все шаблоны. Для решения нам нужно будет кликнуть по последней строке в списке.
- Эта команда дает возможность сравнить информацию, занесенную в строки двух столбцов.
Работы строятся на следующем принципе. Если значение соответствует «Истине», то эта ячейка будет отформатирована. Правила форматирования задаются с помощью нажатия на кнопку «Формат». Тут нам предлагается настроить шрифт и цвет ячейки.
Чтобы форматирование вступило в силу, нам понадобится дважды кликнуть по кнопке «Ок». В результате сравниваемые ячейки будут выделены в соответствии с указанными ограничениями.
Функция 01: EXACT (СОВПАД)
Функция EXACT (СОВПАД) может проверить две текстовые строки на предмет точного совпадения, причем, с учётом регистра. Форматирование на результат не влияет. Если совпадение строк точное, то функция возвращает значение TRUE (ИСТИНА), если же точного совпадения нет, то FALSE (ЛОЖЬ).
Как можно использовать функцию EXACT (СОВПАД)?
Кроме проверки двух ячеек на предмет точного совпадения, Вы можете выполнять при помощи функции EXACT (СОВПАД) следующие действия:
- Использовать для проверки данных, чтобы блокировать изменения в ячейке.
- Задавать условие на ввод только символов в верхнем регистре.
- Проверять точное совпадение в списке значений.
- Находить точное совпадение при поиске в таблице.
- Подсчитывать количество точных совпадений в списке.
- Выявлять различия между двумя ячейками.
Синтаксис EXACT (СОВПАД)
Функция EXACT (СОВПАД) имеет следующий синтаксис:
- text1 (текст1) – это первая текстовая строка.
- text2 (текст2) – это вторая текстовая строка.
Аргументы text1 и text2 можно ввести как текстовые строки или как ссылки на другие ячейки. В Excel максимальная длина строки для EXACT (СОВПАД) составляет 32767 символов.
Ловушки EXACT (СОВПАД)
В справке Excel 2007 в разделе Примечания для функции EXACT (СОВПАД) существует такая строка:
Это не верно. В Excel нет оператора — двойной знак равно (==), и уже в справке к Microsoft Excel 2010 это примечание было убрано.
Другой метод сопоставления содержимого ячеек
Заместо сопоставления содержимого ячеек таблицы с помощью СОВПАД Вы сможете применять остальные функции. К примеру, в Excel есть обычный оператор сопоставления «=», который обычно употребляется в формулах с логическими выражениями.
Ранее была рассмотрена формула ЕСЛИ, которая может непревзойденно подменять СОВПАД. Пример такового альтернативного сопоставления рассмотрен на видео (при использовании этого способа есть принципиальное отличие, так что советуем поглядеть).
Если Вы придумаете (либо уже понимаете) иной метод сопоставления содержимого ячеек, добавьте его в комменты к статье, чтоб остальные наши читатели сберегли время при исследовании Excel.
Найти совпадение данных в двух таблицах в Excel
попадают на работу быстрая в пересчете она работает, то
: Воспользуйся КонсолидациейФункция имеет следующую синтаксическую котором выполняется поиск;
массива, нажатием другом регистре, то строки дляТекстФункции ссылки и поиска число, меньшее или 5 букв проверяет?: Подружитесь — сами
15 запусков free. и их пишем
функция в работе вот формула KLСоздай новый лист запись:0 – поиск точногоПеревел: Антон Андронов
а список уникальных
Ctrl+Shift+Enter результатом в ячейкеEXACT
равное указанному (2345678).Serge_007 сможете решать и
Иначе надо писать в списке сотрудников.
с массивами неясна вообще.. зачем
и на нем=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;) совпадения.
. C6 будет FALSE(СОВПАД) составляет 32767Поиск и ссылкиИспользование аргумента массива таблицы
Эта ошибка может: Да не такие задачи
индивидуальный макрос под Вопрос как выделитьВ большинстве случаев там две вложенные создай консолидацию, говоряОписание аргументов: Из полученного значения вычитаетсяФункция ПОИСКПОЗ в Excel C. (ЛОЖЬ).
символов.В конце этого 30-дневного в функции ВПР
привести к неправильномуkonstantinp :) задачу, если данных цветом ячейку, если СУММЕСЛИ и СЧЁТЕСЛИ внутрь себя ПОИСКПОЗ()
по русски слияние.искомое_значение – обязательный аргумент,
единица для совпадения используется для поискаФормула в столбце DПосле того, как пользовательВ справке Excel 2007 марафона, будет опубликованК началу страницы выставлению счета клиенту.
: Спасибо большое)Формула в Е2 много.
в списке сотрудников значительно медленнее всилу ??Выбираешь, в своем принимающий текстовые, числовые результата с id точного совпадения или использует две функцииПри поиске в таблице ввел правильный пароль, в разделе список всех упомянутыхВ этой статье описаныЕсли для аргумента «приблизительноеФормуляр и протянуть ниже.tomurka попался человек из опять таки алгоритма,Если можно, прошу списке тот слолбец, значения, а также записи в таблице. ближайшего (меньшего или – функция Вы можете открыть
Примечания функций в порядке, синтаксис формулы и соответствие» указано значение: konstantinp,
Для общего случая:: Таблица огромная - черного списка. Большое правда не в Вас, KL, можете в котором ты данные логического иПример поиска: большего заданному вSUMPRODUCTEXACT доступ к изменениюдля функции соответствующем количеству набранных использование функции ЛОЖЬ или 0,могу предложить уже
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$40;A2)=1;»»;ЕСЛИ(ПОИСКПОЗ(A2;$A$2:$A$40;0)=СТРОКА(1:1);МАКС($E$1:E1)+1;ВПР(A2;$A$1:E1;5;0))) несколько тысяч строк. спасибо за совет! данном случае, т.к.
объяснить? хочешь найти совпадения ссылочного типов, которыйПример 2. В Excel зависимости от типа(СУММПРОИЗВ) и(СОВПАД) может отличить
некоторых ячеек таблицы.EXACT в опросе голосов.СОВПАД а точного совпадения готовы макрос:
Если же повторения,ZЧитайте Правила форума.
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе «образец».
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе «образец».
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе «образец».
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе «образец».
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Точное или приближенное совпадение в функции ВПР
И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР
– range_lookup
(интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE
(ПРАВДА) или FALSE
(ЛОЖЬ).
Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.
- Если аргумент range_lookup
FALSE
(ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value
(искомое_значение). Если в первом столбце диапазона table_array
(таблица) встречается два или более значений, совпадающих с аргументом lookup_value
(искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A
(#Н/Д).Например, следующая формула сообщит об ошибке #N/A
(#Н/Д), если в диапазоне A2:A15 нет значения 4
:VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ) - Если аргумент range_lookup
(интервальный_просмотр) равен TRUE
(ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР
ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value
(искомое_значение).
Если аргумент range_lookup
(интервальный_просмотр) равен TRUE
(ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР
может вернуть ошибочный результат.
Чтобы лучше понять важность выбора TRUE
(ИСТИНА) или FALSE
(ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР
и посмотрим на результаты
Пример 1: Поиск точного совпадения при помощи ВПР
Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР
должен иметь значение FALSE
(ЛОЖЬ).
Давайте вновь обратимся к таблице из самого первого примера и выясним, какое животное может передвигаться со скоростью 50
миль в час. Я верю, что вот такая формула не вызовет у Вас затруднений:
VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50
– в ячейках A5
и A6. Формула возвращает значение из ячейки B5
Почему? Потому что при поиске точного совпадения функция ВПР
использует первое найденное значение, совпадающее с искомым.
Пример 2: Используем ВПР для поиска приблизительного совпадения
Когда Вы используете функцию ВПР
для поиска приблизительного совпадения, т.е. когда аргумент range_lookup
(интервальный_просмотр) равен TRUE
(ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.
Это очень важно, поскольку функция ВПР
возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A
(#Н/Д)
Вот теперь можно использовать одну из следующих формул:
VLOOKUP(69,$A$2:$B$15,2,TRUE) или =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) или =ВПР(69;$A$2:$B$15;2)
Как видите, я хочу выяснить, у какого из животных скорость ближе всего к 69
милям в час. И вот какой результат мне вернула функция ВПР
:
Как видите, формула возвратила результат Антилопа
(Antelope), скорость которой 61
миля в час, хотя в списке есть также Гепард
(Cheetah), который бежит со скоростью 70
миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР
при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.
Надеюсь, эти примеры пролили немного света на работу с функцией ВПР
в Excel, и Вы больше не смотрите на неё, как на чужака. Теперь не помешает кратко повторить ключевые моменты изученного нами материала, чтобы лучше закрепить его в памяти.
Извлекаем слово, содержащее определенный символ.
В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:
Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:
Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.
Теперь разберём пошагово:
Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.
НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.
МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() — 50 будет отрицательным числом, а МАКС(1, ПОИСК() — 50) заменяет его на 1.
С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.
Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.
Пример 1: Проверка пароля
Представьте, что Вы ввели секретный пароль в одну из ячеек своей книги Excel и назвали эту ячейку pwd.
На рисунке ниже секретный пароль находится в ячейке C2 с именем pwd. Она располагается на листе AdminData, который может быть скрыт от пользователей.
На другом листе пользователи будут вводить пароль, а Вы проверять его, используя функцию EXACT (СОВПАД).
- На листе Ex01 пользователь будет вводить пароль в ячейку C3.
-
В ячейке C5 с помощью оператора равенства (=) сравниваем значения в ячейках C3 и pwd:
-
В ячейке C6 функция EXACT (СОВПАД) сравнивает ячейки C3 и pwd с учётом регистра:
Если содержимое двух ячеек совпадает, включая регистр, то результатом в обоих случаях будет TRUE (ИСТИНА). Какие-либо различия в форматировании (например, жирный шрифт) будут проигнорированы.
Если хотя бы одна буква введена в другом регистре, то результатом в ячейке C6 будет FALSE (ЛОЖЬ).
8 способов как сравнить две таблицы в Excel
Добрый день!
Эта статья посвящена решению такого вопроса, как сравнить две таблицы в Excel, ну или, по крайней мере, два столбца. Да, работать с таблицами удобно и хорошо, но вот когда нужно их сравнение, визуально это сделать достаточно затруднительно. Быть может таблицу до десятка или двух, вы и сможете визуально отсортировать, но вот когда они будут превышать тысячи, тут уже вам будет необходимо дополнительные инструменты анализа.
Увы, нет магической палочки, с помощью которой в один клик всё сделается и информация будет проверена, необходимо и подготовить данные, и прописать формулы, и иные процедуры позволяющие сравнить вашитаблицы.
Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.
Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.
Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются
Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение
Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.
Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.
Пример 6: Подсчитать четкие совпадения в перечне
При поиске в таблице функция EXACT (СОВПАД) может отличить Aa1 от AA1, что дозволяет подсчитать правильное количество раздельно по любому коду. Остальные функции, такие как COUNTIF (СЧЁТЕСЛИ), будут разглядывать такие коды как схожие и посчитают их полное количество.
В последующем примере перечень циклических значений находится в спектре A2:A11, а перечень неповторимых значений в столбце C.
Формула в столбце D употребляет две функции – SUMPRODUCT (СУММПРОИЗВ) и EXACT (СОВПАД)
Два знака минус (двойное отрицание) перед функцией EXACT (СОВПАД) превращают значения TRUE (ИСТИНА) и FALSE (ЛОЖЬ) соответственно в 1 и 0.
Сравните 2 столбца и верните значение из третьего
При работе с таблицами, содержащими связанные данные, иногда может потребоваться сравнить два столбца в двух разных таблицах и вернуть совпадающее значение из другого столбца. Фактически, это основное использование функции ВПР, цель, для которой она была разработана.
Например, чтобы сравнить имена в столбцах A и D в двух таблицах ниже и вернуть время из столбца E, используйте следующую формулу:
=ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ)
Чтобы скрыть ошибки #Н/Д, используйте проверенное решение — функцию IFNA:
=ЕСЛИНА(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), «»)
Вместо пробелов вы можете вернуть любой текст для отсутствующих точек данных — просто введите его в последнем аргументе. Например:
=IFNA(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), «Недоступно»)
Помимо ВПР, эту задачу можно решить с помощью нескольких других функций поиска.
Лично я бы полагался на более гибкую формулу ИНДЕКС ПОИСКПОЗ:
=ЕСЛИНА(ИНДЕКС($E$3:$E$10, ПОИСКПОЗ(A3, $D$3:$D$10, 0)), «»)
Или используйте современный преемник ВПР — функцию XLOOKUP, доступную в Excel 365 и Excel 2021:
=XLOOKUP(A3, $D$3:$D$10, $E$3:$E$10, «»)
Чтобы получить имена квалифицированных участников из группы A и их результаты, просто отфильтруйте пустые ячейки в столбце B:
=ФИЛЬТР(A3:B15, B3:B15″»)
Как сравнить два столбца в Excel на совпадения и выделить цветом
Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.
Поиск и выделение совпадений цветом в нескольких столбцах в Эксель
В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:
- Выделить столбцы с данными, в которых нужно вычислить совпадения;
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены цветом совпадения:
Поиск и выделение цветом совпадающих строк в Excel
Поиск совпадающих ячеек с данными в двух, нескольких столбцах и поиск совпадений целых строк с данными это разные понятия
Обратите внимание на две таблицы ниже:.
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти совпадающие строки в таблице:
Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:
- Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15);
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены дублирующиеся строки:
На примере выше, мы выделили строки в созданной вспомогательной колонке.
Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?
Для этого сделаем следующее:
Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:
Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:
- Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15;
- Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:
В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:
Не забудьте задать формат найденных дублированных строк.
Эта формула проверяет диапазон данных во вспомогательной колонке и при наличии повторяющихся строк выделяет их цветом в таблице:
Пример №6: Определение количества соответствий в списке
Снова следует отметить, что функция СОВПАД() различает Ci3 и CI3, что поможет вам рассчитать правильное количество совпадений. Другие подобные функции, например СЧЁТЕСЛИ(), обработают данные коды как одинаковые и отобразят их общее количество.
В этом случае повторяющиеся данные расположены в ячейках A2:A11. Кроме того, уникальные значения можно найти в столбце C.
Для достижения цели необходимо вписать 2 функции в столбец D:
- Функция СОВПАД() поможет вам проверить данные, которые были вписаны в столбец C. После этого она найдёт значения, которые полностью соответствуют им.
- После этого СУММПРОИЗВ() возвращает количество, которое подсчитывается по верным результатам.
Как сравнить два столбца в Excel по строкам
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции ЕСЛИ . Рассмотрим как это работает на примерах ниже.
Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу ЕСЛИ . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.
Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:
Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:
Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:
Пример результата вычислений может выглядеть так:
Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:
Примеры использования логических функций ИСТИНА, ЛОЖЬ и НЕ в Excel
Пример 1. В таблице Excel хранятся телефонные номера различных организаций. Звонки на некоторые из них являются бесплатными (с кодом 8800), на остальные – платные по тарифу 1,5 руб/мин. Определить стоимость совершенных звонков.
В столбце «Бесплатный» отобразим логические значения ИСТИНА или ЛОЖЬ по следующему условию: является ли код номера телефона равным «8800»? Введем в ячейку C3 формулу:
- ЛЕВСИМВ(B3;4)=»8800″ – условие проверки равенства первых четырех символов строки указанному значению («8800»).
- Если условие выполняется, функция ИСТИНА() вернет истинное логическое значение;
- Если условие не выполнено, функция ЛОЖЬ() вернет ложное логическое значение.
Аналогично определим является ли звонок бесплатным для остальных номеров. Результат:
Для расчета стоимости используем следующую формулу:
- C3=ИСТИНА() – проверка условия «является ли значение, хранящееся в ячейке C3 равным значению, возвращаемым функцией (логическое истина)?».
- 0- стоимость звонка, если условие выполнено.
- D3*1,5 – стоимость звонка, если условие не выполнено.
Мы получили суммарную стоимость вех совершенных звонков по всем организациям.
Функция 01: EXACT (СОВПАД)
Функция EXACT (СОВПАД) может проверить две текстовые строчки на предмет четкого совпадения, при этом, с учётом регистра. Форматирование на итог не влияет. Если совпадение строк четкое, то функция возвращает значение TRUE (ИСТИНА), если же четкого совпадения нет, то FALSE (ЛОЖЬ).
Как можно применять функцию EXACT (СОВПАД)?
Не считая проверки 2-ух ячеек на предмет четкого совпадения, Вы сможете делать с помощью функции EXACT (СОВПАД) последующие деяния:
Синтаксис EXACT (СОВПАД)
Функция EXACT (СОВПАД) имеет последующий синтаксис:
Аргументы text1 и text2 можно ввести как текстовые строчки либо как ссылки на остальные ячейки. В Excel наибольшая длина строчки для EXACT (СОВПАД) составляет 32767 знаков.
Ловушки EXACT (СОВПАД)
В справке Excel 2007 в разделе Примечания для функции EXACT (СОВПАД) существует таковая строчка:
Это не правильно. В Excel нет оператора – двойной символ равно (==), и уже в справке к Microsoft Excel 2010 это примечание было убрано.
Пример 5: Возвратить заглавие по четкому совпадению в перечне
При поиске в таблице функция EXACT (СОВПАД) может отличить AA1 от Aa1, что дозволяет отыскать правильное заглавие продукта раздельно по любому коду. Остальные функции поиска, такие как VLOOKUP (ВПР), будут разглядывать такие коды как схожие, и возвратят итог, который первым попадётся в перечне с таковым кодом.
В последующем примере коды товаров находятся в спектре B2:B5, клиент может заказать продукт, введя его код в ячейку D2.
Формула в ячейке E2 употребляет 3 функции – INDEX (ИНДЕКС), MATCH (ПОИСКПОЗ) и EXACT (СОВПАД)
Формула введена, как формула массива, нажатием Ctrl+Shift+Enter.