Kwert-soft.ru

IT Софт для ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сравнение данных в excel

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

Добрый день!

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

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

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

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

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

Читать еще:  Расчет даты в excel

Как сравнить два столбца в 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” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

Использование условного оператора ЕСЛИ

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

Разместите оба столбца для сравнения в колонках A и B рабочего листа.

В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));»»;A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.

Использование формулы подстановки ВПР

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

Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.

Использование макроса VBA

Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.

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

В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.

Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_Matches и щелкните кнопку выполнить.

После выполнения макроса, результат должен быть следующим:

Использование надстройки Inquire

С запуском версии 2013, Excel обзавелась интересной надстройкой под названием Inquire, которая позволяет сравнивать и анализировать два файла Excel. Если вам необходимо сравнить две версии одного и того же файла, например, после создания книги, ваши коллеги внесли кое-какие изменения, и вам нужно определить что именно они изменили, воспользуйтесь инструментом WorkbookCompare надстройки Inquire. Подробнее о том, как устанавливать, запускать и использовать надстройку читайте в моей предыдущей статье про надстройку Inquire.

Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.

Вам также могут быть интересны следующие статьи

46 комментариев

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

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

Молодца. Читаю Ваши статьи, наглядно и доходчиво, Спасибо.

Огромное спасибо!
Благодаря приведенной Вами формуле =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));»»;A2) я смогла сравнить два списка (9 и 2 тысячи позиций в каждом).

Но выплыла другая проблема. В списках есть одинаковые данные, отличающиеся только значком *. После выполнения формулы были отмечены, как совпадающие, и данные с * и без *. Что нужно поменять в формуле, чтобы она возвращала только точные совпадения?
Спасибо.

Читать еще:  Как сравнить содержимое ячеек в excel

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

Добрый день, Ренат!
Пробовала с помощью вашей формулы сравнить два столбца с датами, затем с договорами. К сожалению, не получается. Ячейки получаются пустыми, хотя большинство значений совпадают (но excel их тотально не видит). Подскажите, в чем может быть ошибка?

Пришлите, пожалуйста, файл с примером, посмотрим

Доброго времени суток!
Спасибо за полезную статью!
Сравнение прошло успешно, но при попытке сохранить результат сравнения «Export Result» выходит ошибка «Unable to save the export file. Error: Exception from HRESULT: 0x800AC472» и ничего не сохраняется. Не знаете в чём может быть дело? Office 2013 Home and Bussiness Windows 8.1 Pro

Забыл добавить! Для сравнения использовал Inquire.

Добрый день, Антон. Честно говоря, не сталкивался с подобной проблемой, поэтому чем-то конкретным помочь не могу. Но официальном сайте данная ошибка описана, если это вам поможет, скидываю ссылку на страницу

Ренат, спасибо большое за статью! Очень пригодилась в сравнении формула!

День добрый, Ренат
статья хорошая, доступно)))
Огромная просьба, рассмотрите мою проблему.
Чаще требуется не просто 2 столбца данных сравнить, а сравнить два прайса. Индентификатором будет код или артикул — а при совпадении значений надо сопоставить цены. НАпример А-артикулы основного массива, В-Цены основного массива, Д-Артикулы сравниваемого массива и Е-цены сравниваемого массива. При совпадении артикула в А и Д в столбик С копировать цену из соответствующего Е.
Обычно по фирмам прайсы составлены по разному, артикулы разбросаны и чтоб сравнить цены полдня (в лучшем случае) убиваешь на рутину((((

Функция СОВПАД для сравнения значений двух таблиц в Excel без ВПР

Функция СОВПАД в Excel предназначена для проверки двух текстовых срок на их полное соответствие друг другу и возвращает одно из двух возможных логических значений: ИСТИНА – если строки полностью совпадают, ЛОЖЬ – если обнаружено хотя бы одно несоответствие в сравниваемых данных. При работе данной функции учитывается регистр букв. Это значит, что результат сравнения двух строк «текст» и «ТеКсТ» будет равен значению ЛОЖЬ. Распространенный вариант применения рассматриваемой функции – определение вхождения некоторого текста в документ.

Сравнение двух таблиц по функции СОВПАД в Excel

Пример 1. Есть две одинаковые (на первый взгляд) таблицы данных, которые содержат наименования продукции. Одну из них предположительно редактировал уволенный работник. Необходимо быстро сравнить имеющиеся данные и выявить несоответствия.

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

Для сравнения двух строк используем следующую формулу массива (CTRL+SHIFT+Enter):

Описание параметров функции СОВПАД:

  • D3 – текущая ячейка с текстом из второй таблицы;
  • $B$3:$B$13 – соответствующая ячейка с текстом из второй таблицы для проверки на совпадение со значением D3.

Функция ИЛИ возвращает логическое значение ИСТИНА из массива если хотя бы одно из них совпадает с исходным значением.

Протянем данную формулу вниз до конца таблицы, чтобы Excel автоматически рассчитал значения для остальных строк:

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

Выборка значений из таблицы по условию в Excel без ВПР

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

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

Поскольку товар имеет фиксированную стоимость, для определения самого продаваемого смартфона можно использовать встроенную функцию МОДА. Чтобы найти наименование наиболее продаваемого товара используем следующую запись:

Функция мода определяет наиболее часто повторяющиеся числовые данные в диапазоне цен. Функция ПОИСКПОЗ находит позицию первой ячейки из диапазона, в которой содержится цена самого популярного товара. Полученное значение выступает в качестве первого аргумента функции адрес, возвращающей ссылку на искомую ячейку (к значению прибавлено число 2, поскольку отсчет начинается с третьей строки сверху). Функция ДВССЫЛ возвращает значение, хранящееся в ячейке по ее адресу.

В результате расчетов получим:

Для определения общей прибыли от продаж iPhone 5s используем следующую запись:

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

Результат расчетов формулы:

Всего было куплено 4 модели iPhone 5s по цене 239 у.е., что в целом составило 956 у.е.

Правила синтаксиса и параметры функции СОВПАД в Excel

Функция СОВПАД имеет следующий вариант синтаксической записи:

  • текст1 – обязательный для заполнения, принимает ссылку на ячейку с текстом или текстовую строку для сравнения с данными, принимаемые вторым аргументом.
  • текст2 – обязательный для заполнения, принимает ссылку на ячейку или текст, с которым сравниваются данные, переданные в виде первого аргумента.
  1. Результат выполнения функции СОВПАД, принимающей на вход два имени, является код ошибки #ИМЯ? (например, СОВПАД(имя;имя)). Для корректной работы функции указываемые текстовые данные необходимо помещать в кавычки (например, («имя»;«имя»)).
  2. Функция выполняет промежуточное преобразование числовых данных в текст. Например, результат выполнения =СОВПАД(111;111) будет логическое значение ИСТИНА. Однако, преобразование логических данных в числа текстового формата не выполняется. Например, результат выполнения =СОВПАД(ИСТИНА;1) будет логическое ЛОЖЬ.
  3. Результат сравнения двух пустых ячеек или пустых текстовых строк с использованием функции СОВПАД — логическое ИСТИНА.
Ссылка на основную публикацию
Adblock
detector