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

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

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

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

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

Сравнение двух столбцов таблицы на совпадение и различия

Хотя есть способы, когда такое сравнение делается довольно быстро. Например, следующий способ. Выделяем столбцы таблицы, строки в которых необходимо сравнить на совпадение или различие. Затем в правом верхнем углу находим иконку бинокля (найти и выделить), нажимаем на нее и в открывшемся меню выбираем «выделение группы ячеек».

Нажимаем на эту строку. Откроется окно, где необходимо поставить галочку в пункте «отличия по строкам».

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

В другом варианте необходимо будет вводить в ячейку рядом со сравниваемыми значениями формулу: =B2=C2. По мере введения формулы. Будет обозначаться и та ячейка, формулу которой вы вводите.

Затем нажимаем клавишу «Enter» и, если значения различаются в этой строке отобразится слово «ЛОЖЬ», если же одинаковы, то появится слово «ИСТИНА».

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

Когда отпустим уголок, в ячейках появятся слова или «ИСТИНА», или «ЛОЖЬ».

Можно сравнить данные ячеек и при помощи условного форматирования. Для этого выделяем таблицу. Затем идем в пункт «Условное форматирование», где в выпадающем меню выбираем пункт «Создать правило».

Откроется новое окно, в котором выбираем строку «Использовать формулу для определения форматируемых ячеек». В нижней части окна в разделе «измените описание правила», вносим необходимые изменения.

В строке «форматировать значения…» вводим обозначения сравниваемых ячеек. Вписываем следующую формулу: =$B2 $C2 Это вариант для моего примера. У вас первые две верхние строчки таблицы могут обозначаться иначе.

После этого нажимаем кнопку «формат» и в появившемся окне задаем стиль отображения результата.

Здесь можно задать цвет заливки, шрифт и пр. варианты. Выбираем любой цвет, которым будут закрашены сравниваемые ячейки. Выбираем, скажем, желтый и жмем ОК. Останется только в основном окне, так же выбрать ОК.

В процессе всех сделанных изменений получили окрашенные ячейки по заданному сравнению.

Таким образом у вас будут выделены все ячейки, в которых значения различаются.

Как удалить дубликаты в excel без сдвига ячеек?

При заполнении таблицы, особенно текстовыми данными, например список людей или что-то подобное, может возникнуть ситуация, когда вы запишите подряд несколько одинаковых значений. Хорошо, если таблица маленькая, то вы сразу обнаружите дубликаты. А если она большая? Сразу и не заметить. Но если вы заметили дубликат и

удалили его простым нажатием на клавишу «Delete». То у вас останется пустая ячейка, а так не должно быть. Удаляя же ячейки в самой программе, они сдвинутся или удалятся соседние данные, так как удалить одну ячейку в excel невозможно. Удаляется или строка целиком, или столбец. Как быть?

В новых версиях excel имеется полезная кнопка удалить дубликаты. Найти ее можно во вкладке «Данные».

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

Они разбросаны по столбцу и удалить их вручную сложно. Ставим курсор в этот столбец и нажимаем на кнопку «удалить дубликаты«.

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

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

Теперь нажимаем ОК и видим, что в столбце удалены дубликаты, а уникальные данные остались.

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

Как остсортировать дубликаты в excel?

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

Итак, имеем список с фамилиями, именами и отчествами, среди которых «закрались» дубликаты. Их то нам надо и отфильтровать. Ставим курсор в любом месте таблицы и идем во вкладку «данные». Здесь находим кнопку «дополнительно» и нажимаем на нее.

Здесь откроется новое окно, одновременно пунктиром выделится вся таблица. Ставим галочку на пункте «только уникальные записи» и жмем ОК.

В результате список уменьшился, а в нумерации строк листа исчезли некоторые цифры. Они теперь идут не по порядку.

Выделяем красным цветом оставшийся список и нажимаем на кнопку фильтр с иконкой воронки.

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

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

Пример функции ПОИСКПОЗ для поиска совпадения значений в Excel

Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.

Примеры использования функции ПОИСКПОЗ в Excel

Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» — «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.

Например, массив <"виноград";"яблоко";"груша";"слива">содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ("яблоко";<"виноград";"яблоко";"груша";"слива">;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.

Формула для поиска неточного совпадения текста в Excel

Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

Вид исходной таблицы данных:

Пример 1.

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

  • D2&"*" – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
  • B:B – ссылка на столбец B:B, в котором выполняется поиск;
  • 0 – поиск точного совпадения.

Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

ПОИСКПОЗ.

Сравнение двух таблиц в Excel на наличие несовпадений значений

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

Вид таблицы данных:

Пример 2.

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:

сравнения значений.

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

Поиск ближайшего большего знания в диапазоне чисел Excel

Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

Вид исходной таблицы данных:

Пример 3.

Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.

поиск ближайшего большего значения.

Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

поиск ближайшего меньшего.

Особенности использования функции ПОИСКПОЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; [тип_сопоставления])

  • искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
  • просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
  • [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:
  1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
  2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
  3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.
  1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
  2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
  3. Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
  4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.
Ссылка на основную публикацию