Интеллектуальное сравнение ячеек excel
Часто перед пользователями табличного редактора Эксель стоит такая задачка, как сравнение текста в ячейках таблички. Существует несколько способов реализации этой процедуры. В статье мы детально рассмотрим каждый из способов, позволяющий реализовать сравнение текстовой информации в ячейках таблички.
Сравнение двух ячеек в табличном редакторе
В табличном редакторе имеется два различных метода, позволяющих сравнить текстовую информацию в ячейках. Методы отличаются друг от друга тем, что один учитывает регистр, а второй – нет
Применение специальной формулы без учета регистра
Самый простейший вариант сравнения текста в 2-х ячейках без учета регистра – это применение символа «=» между координатами ячеек. Пример формулы: =А1=В1.
Важно! А1 и В1 являются элементами сравнения. В результате, в ячейке с формулой выведется либо значение ЛОЖЬ, либо значение ИСТИНА.
На нижеприведенной картинке прекрасно видно, как две рассмотренные формулы справляются с процедурой сравнения текстовой информации в ячейках.
Применение специальной формулы с учетом регистра
Часто при работе с документами случаются такие моменты, когда необходимо не просто произвести сравнение текстовой информации, но и учесть регистр. Для реализации этой несложной процедуры применятся специальный оператор «СОВПАД». В английской вариации он имеет наименование «EXACT». Общий вид формулы выглядит так: =СОВПАД(текст1; текст2). Аргументы текст1 и текст2 – это координаты ячеек, в которых будет производиться сравнение текстовой информации.
К примеру, данные для сравнения располагаются в полях, имеющих координаты А1 и В1. В этом случае, применяемая формула будет выглядеть так: =СОВПАД(А1; В1). Значение ИСТИНА будет отображено в том случае, если регистр каждого знака будет совпадать. В иных случаях отобразится значение ЛОЖЬ.
Простой пример такой формулы: =ЕСЛИ(СОВПАД(A1; B1); «Совпадает»; «НЕ совпадает»). На нижеприведенной картинке прекрасно видно, как две рассмотренные формулы справляются с процедурой сравнения текстовой информации в ячейках с учетом регистра.
2
Сравнение нескольких ячеек в табличном редакторе
Теперь поговорим детально о том, как произвести сравнение текстовой информации более, чем в двух ячейках. Эти методы практически идентичны тем, что были рассмотрены выше.
Применение специальной формулы без учета регистра для нескольких ячеек
Здесь, как и в ранее рассмотренных методах, применяется две вариации формулы:
- Первый вариант формулы выглядит так: =И(А1=В1; А1=С1).
- Второй вариант формулы выглядит так: =ЕСЛИ(И(А1=В1; А1=С1); «Совпадает»; «НЕ совпадает»).
Функция «И» реализует возврат значения ИСТИНА в том случае, если все поля таблички имеют совпадающие значения. Возврат значения ЛОЖЬ происходит, если какое-либо из значений не совпадает. Оператор «ЕСЛИ» позволяет отобразить на экране те показатели, которые ввел пользователь в формулу.
На нижеприведенной картинке прекрасно видно, как две рассмотренные формулы справляются с процедурой сравнения не только текстовой информации, но и с числовыми показателями, а также с данными формата «Дата».
3
Применение специальной формулы с учетом регистра для нескольких ячеек
Здесь, как и в ранее рассмотренных методах, применяется две вариации формулы:
- Первый вариант формулы выглядит так: =И(СОВПАД(А1;В1); СОВПАД(А1; С2)).
- Второй вариант формулы выглядит так: =ЕСЛИ(И(СОВПАД(А1;В1);СОВПАД(А1; С1));»Точное совпадение»; «НЕ совпадает»).
Как и в предыдущем примере, функция «И» реализует возврат значения ИСТИНА в том случае, если все поля таблички имеют совпадающие значения. Возврат значения ЛОЖЬ происходит, если какое-либо из значений не совпадает. Оператор «ЕСЛИ» позволяет отобразить на экране те показатели, которые ввел пользователь в формулу. Результат преобразований выглядит так:
4
Если пользователю необходимо, чтобы формула вывела наличие соответствий хотя бы 2-х показателей, то ему необходимо использовать следующую формулу: =ЕСЛИ(ИЛИ(СОВПАД(А1;В1);СОВПАД(А1; С1);СОВПАД(В1;С1)); «Не менее двух точных соответствий»; «Соответствий нет»). Иными словами, в этом случае наличие хотя бы 2-х соответствий в 3-х случаях, выведет положительное значение. Результат преобразований выглядит так:
5
Если же пользователю необходимо произвести сравнение в табличке, в которой присутствует огромное количество ячеек, то вышеописанная формула будет не удобной в использовании, так как она станет слишком длинной. Здесь целесообразнее использовать математический оператор «СЧЕТЕСЛИ». Подсчитать количество соответствий в большом диапазоне ячеек можно при помощи следующей формулы: . Результат преобразований выглядит так:
6
Стоит отметить! Очень важно следить за количеством ненужных пробелов в ячейках. Дело в том, что если в ячейке присутствует пробел, то он будет восприниматься табличным редактором как символ. Поэтому перед использованием формул необходимо убедиться в том, что в ячейках отсутствуют ненужные пробелы.
Заключение
Мы выяснили, что в табличном редакторе Эксель существует огромное количество методов, позволяющих реализовать сравнение текстовой информации в ячейках. Мы узнали, что можно сравнивать информацию с учетом регистра и без учета. Для реализации этой процедуры великолепно подходит такой оператор, как «СОВПАД». Дополнительно для более сложных процедур применяются такие функции, как «И», «ИЛИ», «ЕСЛИ» И «СЧЕТЕСЛИ».
Типовая задача, возникающая периодически перед каждым пользователем Excel - сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :
Число несовпадений можно посчитать формулой:
или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))
Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.
Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)
Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:
- залить цветом или как-то еще визуально отформатировать
- очистить клавишей Delete
- заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
- удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
- и т.д.
Вариант 2. Перемешанные списки
Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.
Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):
Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.
Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.
В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Типовая задача, возникающая периодически перед каждым пользователем Excel - сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :
Число несовпадений можно посчитать формулой:
или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))
Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.
Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)
Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:
- залить цветом или как-то еще визуально отформатировать
- очистить клавишей Delete
- заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
- удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
- и т.д.
Вариант 2. Перемешанные списки
Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.
Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):
Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.
Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.
В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Табличный процессор Эксель – одна из самых популярных программ для работы с электронными таблицами. И нередко у пользователя возникает вопрос – можно ли сравнить в Excel несколько столбцов на наличие совпадений. Особенно это важно для тех, кто работает с огромными объемами информации и, соответственно, большими таблицами.
Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.
1 Сравнение с помощью простого поиска
При наличии небольшой по размеру таблицы заниматься сравнением можно практически вручную. Для этого достаточно выполнить несколько простых действий.
- Перейти на главную вкладку табличного процессора.
- В группе «Редактирование» выбрать пункт поиска.
- Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
- Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.
Если значение обнаружено, результатом станет выделение нужной ячейки. Однако с помощью такого способа можно работать только с небольшими столбцами. И, если это просто цифры, так можно сделать и без поиска — определяя совпадения визуально. Впрочем, если в колонках записаны большие объемы текста, даже такая простая методика позволит упростить поиск точного совпадения.
2 Операторы ЕСЛИ и СЧЕТЕСЛИ
Еще один способ сравнения значений в двух столбцах Excel подходит для таблиц практически неограниченного размера. Он основан на применении условного оператора ЕСЛИ и отличается от других методик тем, что для анализа совпадений берется только указанная в формуле часть, а не все значения массива. Порядок действий при использовании методики тоже не слишком сложный и подойдет даже для начинающего пользователя Excel.
- Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
- В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
- Протянуть формулу до конца столбца.
Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.
Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.
Тот же способ подойдет и для сравнения сразу большого количества колонок с данными на точное совпадение не только значения, но и строки. Для этого применяется уже не оператор ЕСЛИ, а функция СЧЕТЕСЛИ. Принцип написания и размещения формулы похожий.
Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.
Если поставить вместо тройки двойку, результатом будет поиск только тех совпадений с первой колонкой, которые присутствуют в одном из других столбцов. Причем, тройные дубликаты формула проигнорирует. Так же как и совпадения второй и третьей колонки.
3 Формула подстановки ВПР
Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».
Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.
5 Сравнение с выделением совпадений цветом
В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.
У нас имеется две таблицы заказов, скопированных в один рабочий лист. Необходимо выполнить сравнение данных двух таблиц в Excel и проверить, которые позиции есть первой таблице, но нет во второй. Нет смысла вручную сравнивать значение каждой ячейки.
Сравнение двух столбцов на совпадения в Excel
Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:
В первую очередь необходимо присвоить имена обоим таблицам. Благодаря этому легче понять, какие сравниваются диапазоны ячеек:
- Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
- В появившемся окне в поле «Имя:» введите значение – Таблица_1.
- Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.
Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.
Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.
Теперь воспользуемся условным форматированием, чтобы выполнить сравнение двух списков в Excel. Нам нужно получить следующий результат:
Позиции, которые есть в Таблице_1, но нет в Таблцие_2 будут отображаться зеленым цветом. В тоже время позиции, находящиеся в Таблице_2, но отсутствующие в Таблице_1, будут подсвечены синим цветом.
- Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
- В поле ввода введите формулу:
- Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
- Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
- В поле ввода введите формулу:
- Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.
Принцип сравнения данных двух столбцов в Excel
При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.
Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах. Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.
Читайте также: