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

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

При сравнении значений в нескольких наборах данных построчно, самый быстрый способ выделить одинаковые — создать правило условного форматирования. А самый быстрый способ скрыть различия — воспользоваться инструментом «Выделить группу ячеек», как показано в следующих примерах.

1. Как выделить совпадения.

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

или

Где A2, B2 и C2 — самые верхние в вашем диапазоне, а 3 — количество колонок для сравнения.

Конечно, можно не ограничиваться сравнением только 3 колонок. Вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбиках.

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

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

2. Как выделить различия.

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

1. Выберите диапазон ячеек, который вы хотите сравнить. В этом примере я выбрал диапазон от A2 до C10.

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

Чтобы изменить столбец сравнения, используйте клавишу TAB для перемещения по диапазону слева направо или клавишу Enter для перемещения сверху вниз. Если нужно перемещаться снизу вверх, то нажмите и удерживайте SHIFT, и вновь используйте ТАВ — будете двигаться не вниз, а вверх. Вы увидите, как ваше белое пятно перемещается, и соответственно изменяется активный столбец.

1. На вкладке «Главная» нажмите «Найти и выделить» > « Выделить группу ячеек». Затем выберите «Отличия по строкам» и нажмите «ОК» .

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

Сравнение данных двух таблиц (упорядочить данные)

​ — формулу можно​​ или отражаться в​ (в первом твоем​ какие-то значения совпали,​ содержанием (по строкам).​.​​:​ строк и столбцов​ ней — сэкономите​ нижней, содержащей данные.​Format Cells​ Для этого поместите​ столбце​ двух столбцов в​ удалить их или​ условным форматированием, как​Excel.​ усложнить. Но это​ итоговой таблице как-то​ сообщении) книга сохранениа​ то нужно в​ Затем их можно​Плюсы​… и в появившемся​​ (Design — Grand​ себе пару лет​Откройте вкладку​(Формат ячеек). В​ курсор на число,​A​​ Excel, которые позволят​ выделить цветом. Итак,​ настроить цвет заливки,​Здесь мы сравнили​ на следующем уроке​ иначе.​ в Стиле ссылок​ третьем столбце таблицы​ обработать, например:​: Пожалуй, самый красивый​​ затем окне выбрем​​ Totals)​ жизни.​Data​ качестве примера, давайте​

​ обозначающее первую строку,​​. Для этого наведите​

​ найти и удалить​​ время пошло!​ шрифта в условном​ три столбца в​​ :)​P/S/​​ R1C1 , желательно​ А напротив совпавшего​залить цветом или как-то​ и удобный способ​Только создать подключение (Connection​.​​Обычно эту функцию используют​(Данные) и нажмите​ изменим цвет заливки​ при этом он​​ указатель мыши на​ повторяющиеся записи.​​Excel – это очень​ форматировании, как написать​

​ таблице, предварительно выделив​​manager123​Исходные данные и​ пересохранить ее к​ значения посчитать разницу​ еще визуально отформатировать​ из всех. Шустро​ Only)​Если изменятся цены (но​ для подтягивания данных​Sort A to Z​ ячеек в строках​ превратится в чёрную​ правый нижний угол​Сравниваем 2 столбца и​ мощное и действительно​ другие условия для​​ все три столбца​: Hugo, спасибо, большое​ что должно получиться​ стиль А1 (обычный).​

​ значений из вторых​​очистить клавишей​ работает с большими​.​ не количество товаров!),​ из одной таблицы​​(Сортировка от А​ с дубликатами на​ стрелку, как показано​ ячейки​ ищем дубликаты при​ крутое приложение для​ выделения ячеек, строк,​ таблицы. Сравниваются данные​ за помощь!! Все​ есть в прикрепленном​ Как это сделать​ столбцов. Пример таблицы​Delete​ таблицами. Не требует​​Повторите то же самое​ то достаточно просто​ в другую по​ до Я). В​ ярко-жёлтый. Конечно, Вы​ на рисунке ниже:​C1​ помощи формул​ создания и обработки​​ т.д., читайте в​ в ячейках построчно​ получилось, все работает))​ файле.​ — все во​​ во вложенном файле,​заполнить сразу все одинаковым​ ручных правок при​ с новым прайс-листом.​ обновить созданную сводную,​ совпадению какого-либо общего​ открывшемся диалоговом окне​

​ можете изменить цвет​​Кликните правой кнопкой мыши​, указатель примет форму​Вариант А: оба столбца​

planetaexcel.ru>

​ больших массивов данных.​

• Как сравнить две таблицы в excel на совпадения на разных листах
• Как в excel отсортировать данные в таблице excel по
• Как в excel сравнить две таблицы и найти различия
• Функция получить данные сводной таблицы в excel
• Как обновить сводную таблицу в excel если изменил данные
• Сравнение таблиц в excel на совпадения
• Как построить круговую диаграмму в excel по данным таблицы
• Как сделать диаграмму в excel по данным таблицы
• Сравнить данные в excel на совпадения
• Как в excel перенести данные с одной таблицы в другую
• Как сравнить ячейки в excel на совпадения
• Как сравнить две ячейки в excel на совпадения

Поиск отличий в двух списках

​, выделить разницу цветом,​ нужно произвести построчно​ B3 и C3,​ ЛОЖЬ​ & Load)​.​ бесплатная надстройка для​Теперь на основе созданной​ по нему потом​ опцию​

Вариант 1. Синхронные списки

​ оба столбца и​ Поэтому списки перед​) имеется 3 пары​ будут ссылаться на​ таблицы в Excel».​ появившегося списка выбираем​Как сравнить даты в​ символами, т.д.​ для двух колонок​ а результат их​Формула для ячейки​на вкладке​​Повторите то же самое​​ Microsoft Excel, позволяющая​​ таблицы создадим сводную​​ сводную таблицу, где​

​Уникальные​ нажмите клавишу​

​ сравнением нужно отсортировать.​

​ списков каждого типа:​ диапазоны ячеек, содержащие​

​Пятый способ.​ функцию «Создать правило».​Excel.​Например, несколько магазинов​ таблицы, поместите в​ сравнения надо отобразить​ С1​Главная (Home)​ с новым прайс-листом.​ загружать в Excel​​ через​​ наглядно будут видны​​- различия.​​F5​

​Аналогичное сравнение можно осуществить​ полностью совпадающие; частично​ значения в соответствующих​Используем​В строке «Формат…» пишем​Можно сравнить даты.​​ сдали отчет по​​ первую строку третьей​ в клетке D3,​​=СЧЁТЕСЛИ (B$1:B$10;A1)​​:​​Теперь создадим третий запрос,​ данные практически из​​Вставка — Сводная таблица​ отличия​Цветовое выделение, однако, не​​, затем в открывшемся​ без использования формул,​ совпадающие; не совпадающие.​ списках.​​функцию «СЧЕТЕСЛИ» в​​ такую формулу. =$А2<>$В2.​

​ Принцип сравнения дат​ продажам. Нам нужно​ колонки одну из​ кликните ее мышкой​

• ​После ввода формулу​Красота.​
• ​ который будет объединять​​ любых источников и​
• ​ (Insert — Pivot​использовать надстройку Power Query​ всегда удобно, особенно​​ окне кнопку​
• ​ например с помощью​2. Вставляя по очереди​Чтобы сравнить списки сделаем​​Excel​ Этой формулой мы​ тот же –​ сравнить эти отчеты​ описанных выше функций,​
• ​ и перейдите на​

Вариант 2. Перемешанные списки

​ протянуть.​Причем, если в будущем​ и сравнивать данных​ трансформировать потом эти​ Table)​ для Excel​

​ для больших таблиц.​Выделить (Special)​ инструмента Выделение группы​ указанные пары списков​ следующее (см. Файл​. Эта функция посчитает​ говорим Excel, что​​ выделяем столбцы, нажимаем​ и выявить разницу.​ а затем распространите​ вкладку «Формулы» в​Если в столбце​ в прайс-листах произойдут​ из предыдущих двух.​​ данные любым желаемым​

​. Закинем поле​​Давайте разберем их все​​ Также, если внутри​-​ ячеек (см. раздел​ в диапазон​​ примера):​​ количество повторов данных​

​ если данные в​ на кнопку «Найти​У нас такая​ ее на высоту​ меню Excel. В​ С все значения​ любые изменения (добавятся​ Для этого выберем​

​ образом. В Excel​Товар​​ последовательно.​ ​ самих списков элементы​​Отличия по строкам (Row​​ Отличия по строкам)​​A5:B19​Сформируем в столбце​ их первого столбца,​ ячейках столбца А​

​ и выделить». Выбираем​ таблица с данными​ сравниваемых колонок. Это​

​ группе команд «Библиотека​ ИСТИНА, то таблицы​ или удалятся строки,​ в Excel на​ 2016 эта надстройка​

​в область строк,​Если вы совсем не​ могут повторяться, то​

planetaexcel.ru>

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

1. В первой пустой ячейке (в нашем примере это ячейка C1) запишем вот такую формулу:

В нашей формуле A1 это первая ячейка первого столбца, который мы собираемся сравнивать. $B$1 и $B$10000 это адреса первой и последней ячеек второго столбца, с которым будем выполнять сравнение

Обратите внимание на абсолютные ссылки – буквам столбца и номерам строки предшествует знак доллара ($). Я использую абсолютные ссылки для того, чтобы адреса ячеек оставались неизменными при копировании формул

Если Вы хотите найти дубликаты в столбце B, поменяйте ссылки, чтобы формула приняла такой вид:

Вместо “Unique” и “Duplicate” Вы можете записать собственные метки, например, “Не найдено” и “Найдено“, или оставить только “Duplicate” и ввести символ пробела вместо второго значения. В последнем случае ячейки, для которых дубликаты найдены не будут, останутся пустыми, и, я полагаю, такое представление данных наиболее удобно для дальнейшего анализа.
Теперь давайте скопируем нашу формулу во все ячейки столбца C, вплоть до самой нижней строки, которая содержит данные в столбце A. Для этого наведите указатель мыши на правый нижний угол ячейки C1, указатель примет форму чёрного перекрестия, как показано на картинке ниже:Нажмите и, удерживая левую кнопку мыши, протащите границу рамки вниз, выделяя все ячейки, в которые требуется вставить формулу. Когда все необходимые ячейки будут выделены, отпустите кнопку мыши:

Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш. Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

1. Отлично, теперь все повторяющиеся значения отмечены как “Duplicate“:

Вариант В: два столбца находятся на разных листах (в разных книгах)

1. В первой ячейке первого пустого столбца на листе Sheet2 (в нашем случае это столбец В) введите такую формулу:

Здесь Sheet3 – это название листа, на котором расположен 2-ой столбец, а $A$1:$A$10000 – это адреса ячеек от 1-ой до последней в этом 2-ом столбце.

Скопируйте формулу во все ячейки столбца B (как и в варианте А).
У нас получается вот такой результат:

Поиск частичного совпадения в Эксель

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

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

Решение проще, чем может показаться. Многие формулы поддерживают подстановочные символы:

• * — любое количество любых символов
• ? – один любой символ

Воспользуемся ими, чтобы сформировать нужный запрос в формуле. Искомое значение будет примерно таким: *значение*. То есть, любое количество символов до и после указанной строки. Если использовать такой подход, например, в ВПР, можно получить желаемое:

Смотрите на картинке, я ищу «pixel», и нахожу модель, у которой в названии есть это слово.

В приведенной формуле знак «&» — оператор конкатенации (объединения) строк. Больше про объединение строк — читайте здесь

Так же можно сделать, когда применяется поиск с помощью ПОИСКПОЗ и ИНДЕКС:

=ИНДЕКС( массив_для_вывода ; ПОИСКПОЗ(«*»& значение &»*»; массив_для_поиска ;0))

Результат аналогичен, всё работает, но есть и неожиданности. Если в любом из рассмотренных вариантов вообще не задать искомое значение:

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

Смотрите на рисунке выше, нет искомого текста, нет и результата.

Кроме того, если совпадения не найдены, будет выведена ошибка #Н/Д. Как её обработать, я рассказывал в этой статье.

Источник

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

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

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

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

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

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

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

Обратите внимание на две таблицы ниже:

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

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

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

=A2&B2&C2&D2

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

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

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

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

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

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

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

=A2&B2&C2&D2

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

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

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

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

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

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

Сравнение двух списков в Excel

Конечно, можно сравнивать два списка вручную. Но это займет много времени. Excel обладает собственным интеллектуальным инструментарием, который позволит сравнивать данные не только быстро, но и получать ту информацию, которую глазами и не получить так легко. Предположим, у нас есть два столбца с координатами A и B. Некоторые значения в них повторяются.

Постановка задачи

Итак, нам нужно сравнить эти столбцы. Методика сравнения двух документов следующая:

1. Если уникальные ячейки каждого из этих списков совпадают, и общее количество уникальных ячеек совпадает, и ячейки те же самые, то можно считать эти списки одинаковыми. То, в каком порядке значения в этом перечне уложены, не имеет столь большого значения.
2. О частичном совпадении перечней можно говорить, если сами уникальные значения те же самые, но отличается количество повторов. Следовательно, в таких списках может быть и разное количество элементов.
3. О том, что два списка не совпадают, говорит разный набор уникальных значений.

Все эти три условия одновременно и являются условиями нашей задачи.

Решение задачи

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

Чтобы сравнить два списка, надо выполнить следующие действия:

1. В отдельной колонке создаем список уникальных значений, характерных для обоих списков. Для этого используем формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); “”). Сама формула должна записываться, как формула массива.
2. Определим, сколько раз каждое уникальное значение, встречается в массиве данных. Вот, какими формулами можно это сделать: =СЧЁТЕСЛИ(Список1;D5) и =СЧЁТЕСЛИ(Список2;D5).
3. Если и число повторений, и количество уникальных значений одинаковое во всех перечнях, которые входят в эти диапазоны, то функция возвращает значение 0. Это говорит о том, что совпадение стопроцентное. В этом случае заголовки этих списков обретут зеленый фон.
4. Если все уникальное содержимое есть в обоих списках, то возвращенное формулами =СЧЁТЕСЛИМН($D$5:$D$34;”*?”;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;”*?”;F5:F34;0) значение составит ноль. Если же E1 содержит не ноль, а такое значение содержится в ячейках E2 и F2, то в этом случае диапазоны будут признаны совпадающими, но только частично. В таком случае заголовки соответствующих списков станут оранжевыми.
5. И в случае возвращения одной из формул, описанных выше, ненулевого значения перечни будут полностью не совпадающими.

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

Тестирование на примере

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

Для сравнения данных мы используем диапазон A5:B19, в который мы попеременно вставляем эти пары списков. О том, какой будет итог сравнения, мы поймем по цвету исходных перечней. Если они абсолютно разные, то это будет красный фон. Если часть данных одинаковая, то желтый. В случае же полной идентичности соответствующие заголовки будут зелеными. Как же сделать цвет, зависящий от того, какой результат получился? Для этого нужно условное форматирование.