Сравнение строк в экселе

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

Инструкция

  1. Для подсчета совпадений с определенным словом или словосочетанием нужно использовать функцию программы СЧЁТЕСЛИ. Для подсчета совпадений информации в столбце А с искомой фразой установите курсор в ячейку, предназначенную для выведения результата и в строку формул впишите =СЧЁТЕСЛИ($A:$A; «Фраза»). В данном случае программа будет подсчитывать количество слова «Фраза» в столбце А.

Формула для подсчета дат, принадлежащих февралю
Для сравнения данных в целой колонке с одним образцом можно использовать функцию условного форматирования. Для этого впишите нужную фразу в одну из ячеек, например, в ячейку В1. Выделите колонку с информацией, в которой нужно обозначить искомую фразу, перейдите на вкладку «Главная». В разделе «Стили» нажмите иконку «Условное форматирование», укажите пункт «Правила выделения ячеек» и выберите команду «Равно». Команда «Равно»

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

  • Если вам нужно сравнить с образцом одновременно информацию из нескольких ячеек, вам придется использовать опции ЕСЛИ и СЦЕПИТЬ. Для соединения данных из нескольких ячеек используйте опцию СЦЕПИТЬ(А1; «и» ;B1), в данном случае программа использует текст из ячейки А1 и В1, а между ними вставит союз «и». Для сравнения большого текста удобнее будет присвоить ячейке с данным текстом собственное имя, для этого обозначьте данную ячейку и в поле, расположенное левее строки функций, впишите для него имя, например, «Образец». После выполнения подготовки выделите ячейку, в которой должен быть результат сравнения, и впишите в него следующую формулу: ЕСЛИ(СЦЕПИТЬ(A1; «и» ;B1)=образец;1;0). В данном случае, если сравнение даст положительный результат, то в ячейке результата будет вписана единица, если совпадений обнаружено не будет – ноль. Для того, чтобы применить данную формулу к нескольким строкам таблицы, нужно ее выделить, навести на маркер в правом нижнем углу и растянуть ее на нужные строки.
  • Внимание! Для корректной работы программы Excel мы советуем использовать только 100% лицензионную версию, которую вы можете купить со скидкой в нашем интернет-магазине.

    Видео: Функция СЦЕПИТЬ в Excel

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

    Читайте также:  Как перепрошить ноутбук asus

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

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

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

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

    Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:

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

    Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:

    =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

    =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)

    Пример результата вычислений может выглядеть так:

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

    =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

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

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

    • Найти строки с одинаковыми значениями во всех столбцах таблицы;
    • Найти строки с одинаковыми значениями в любых двух столбцах таблицы;

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

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

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

    В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

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

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

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

    =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

    Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2 , вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2 . Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

    Читайте также:  Delphi выйти из процедуры

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

    Представим, что наша таблица состоит из двух столбцов с данными. Нам нужно определить повторяющиеся значения в первом и втором столбцах. Для решения задачи нам помогут функции ЕСЛИ и СЧЁТЕСЛИ .

    =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

    Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

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

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

    Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

    Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

    • Выделить столбцы с данными, в которых нужно вычислить совпадения;
    • На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
    • Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
    • После этого в выделенной колонке будут подсвечены цветом совпадения:

    Поиск и выделение цветом совпадающих строк в Excel

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

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

    Рассмотрим как найти совпадающие строки в таблице:

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

    Во вспомогательной колонке вы увидите объединенные данные таблицы:

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

    • Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
    • На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
    • Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
    • После этого в выделенной колонке будут подсвечены дублирующиеся строки:

    На примере выше, мы выделили строки в созданной вспомогательной колонке.

    Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

    Для этого сделаем следующее:

    • Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

    Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

    • Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
    • Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:
    Читайте также:  Comodo internet security windows 10

    • В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

    • Не забудьте задать формат найденных дублированных строк.

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

    Типовая задача, возникающая периодически перед каждым пользователем 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) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

    Полученный в результате ноль и говорит об отличиях.

    И, наконец, "высший пилотаж" – можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

    Выглядит страшновато, но свою работу выполняет отлично 😉