Сравняване на таблици със стойности 1c 8. Как да сравним две колони в Excel за съвпадения

Четенето на тази статия ще ви отнеме около 10 минути. В следващите 5 минути можете лесно да сравните две колони в Excel и да разберете дали има дубликати в тях, да ги изтриете или да ги маркирате с цвят. И така, времето дойде!

Excel е много мощно и наистина страхотно приложение за създаване и обработка на големи количества данни. Ако имате няколко работни книги с данни (или само една огромна таблица), вероятно ще искате да сравните 2 колони, да намерите дублирани стойности и след това да направите нещо с тях, като изтриване, маркиране или изчистване на съдържанието. Колоните могат да бъдат в една и съща таблица, съседни или несъседни, разположени на 2 различни листа или дори в различни работни книги.

Представете си, че имаме 2 колони с имена на хора - по 5 имена на колона Аи 3 имена в колона б. Трябва да сравните имената в тези две колони и да намерите евентуални дубликати. Както разбирате, това са фиктивни данни, взети само с илюстративна цел. В реални таблици имаме работа с хиляди или дори десетки хиляди записи.

Вариант А:и двете колони са на един лист. Например колона Аи колона б.

Вариант Б:Колоните са разположени на различни листове. Например колона Ана лист Лист2и колона Ана лист Лист3.

Excel 2013, 2010 и 2007 имат вграден инструмент Премахване на дубликат(Премахване на дубликати), но е безсилен в тази ситуация, защото не може да сравни данните в 2 колони. Освен това може да премахва само дубликати. Няма други опции, като подчертаване или промяна на цветовете. И точка!

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

Вариант А: двете колони са на един лист

улика:В големи таблици копирането на формула ще бъде по-бързо, ако използвате клавишни комбинации. Изберете клетка C1и натиснете Ctrl+C(за да копирате формулата в клипборда), след което щракнете Ctrl+Shift+End(за да изберете всички непразни клетки в колона C) и накрая щракнете Ctrl+V(за да поставите формулата във всички избрани клетки).

Вариант Б: две колони са на различни листове (в различни книги)

Обработка на намерени дубликати

Страхотно, намерихме записи в първата колона, които присъстват и във втората колона. Сега трябва да направим нещо с тях. Ръчното преминаване през всички дублиращи се записи в таблица е доста неефективно и отнема твърде много време. Има по-добри начини.

Показване само на дублирани редове в колона A

Ако вашите колони нямат заглавия, тогава трябва да ги добавите. За да направите това, поставете курсора върху числото, показващо първия ред, и той ще се превърне в черна стрелка, както е показано на фигурата по-долу:

Щракнете с десния бутон и изберете от контекстното меню Поставете(Вмъкване):

Дайте имена на колоните, например „ Име" И " дубликат?” След това отворете раздела Данни(Данни) и натиснете Филтър(Филтър):

След това щракнете върху малката сива стрелка до „ дубликат?“ за разширяване на менюто с филтър; премахнете отметката от всички елементи в този списък, с изключение на Дубликати натиснете Добре.

Това е всичко, сега виждате само тези елементи на колоната А, които се дублират в колоната IN. В нашата тренировъчна маса има само две такива клетки, но, както разбирате, на практика ще има много повече от тях.

За да покажете отново всички редове на колона А, щракнете върху символа за филтър в колоната IN, който сега изглежда като фуния с малка стрелка и изберете Избери всички(Избери всички). Или можете да направите същото чрез лентата, като щракнете Данни(Данни) > Изберете и филтрирайте(Сортиране и филтриране) > ясно(Изчистване), както е показано на екранната снимка по-долу:

Променете цвета или маркирайте откритите дубликати

Ако знаците „ Дубликат” не е достатъчно за вашите цели и искате да маркирате дублирани клетки с различен цвят на шрифта, цвят на запълване или по някакъв друг начин...

В този случай филтрирайте дубликати, както е показано по-горе, изберете всички филтрирани клетки и щракнете Ctrl+1за да отворите диалоговия прозорец Форматиране на клетки(Формат на клетка). Като пример, нека променим цвета на запълване на клетките в редовете с дубликати на ярко жълто. Разбира се, можете да промените цвета на запълване с помощта на инструмента Напълнете(Цвят на запълване). У дома(Начало), но предимството на диалоговия прозорец Форматиране на клетки(Форматиране на клетки) е, че можете да конфигурирате всички опции за форматиране наведнъж.

Сега определено няма да пропуснете нито една клетка с дубликати:

Премахване на дублиращи се стойности от първата колона

Филтрирайте таблицата, за да покажете само клетки с дублиращи се стойности, и изберете тези клетки.

Ако 2-те колони, които сравнявате, са на различни листове, тоест в различни таблици щракнете с десния бутон върху избрания диапазон и изберете от контекстното меню Изтриване на ред(Изтриване на ред):

Кликнете Добрекогато Excel ви помоли да потвърдите, че наистина искате да изтриете целия ред на работния лист и след това да изчистите филтъра. Както можете да видите, остават само редове с уникални стойности:

Ако 2 колони са разположени на един лист, близо един до друг (съседни) или не близо един до друг (не съседни), тогава процесът на премахване на дубликати ще бъде малко по-труден. Не можем да изтрием целия ред с дублиращи се стойности, защото това би изтрило и клетки от втората колона. Така че, за да запазите само уникални записи в колона А, направете следното:

Както можете да видите, премахването на дубликати от две колони в Excel с помощта на формули не е толкова трудно.

Понякога има нужда да се сравнят два MS Excel файла. Това може да е откриване на несъответствия в цените за определени артикули или промяна на някакви индикации, няма значение, основното е, че е необходимо да се намерят определени несъответствия.

Няма да е излишно да споменем, че ако има няколко записа във файла на MS Excel, тогава няма смисъл да се прибягва до автоматизация. Ако файлът съдържа няколкостотин или дори хиляди записи, тогава е невъзможно да се направи без помощта на изчислителната мощност на компютъра.

Нека симулираме ситуация, при която два файла имат еднакъв брой редове и несъответствието трябва да се търси в конкретна колона или в няколко колони. Тази ситуация е възможна, например, ако трябва да сравните цената на стоките според две ценови списъци или да сравните измерванията на спортистите преди и след тренировъчния сезон, въпреки че за такава автоматизация трябва да има много от тях.

Като работещ пример, нека вземем файл с представянето на фиктивни участници: бягане на 100 метра, бягане на 3000 метра и набирания. Първият файл е измерване в началото на сезона, а вторият е края на сезона.

Първият начин за решаване на проблема. Решението е само използването на формули на MS Excel.

Тъй като записите са подредени вертикално (най-логичното подреждане), е необходимо да използвате функцията. Ако използвате хоризонтално поставяне на записи, ще трябва да използвате функцията.

За да сравните представянето на бягане на 100 метра, формулата е следната:
=АКО(VLOOKUP($B2,Sheet2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);"Няма разлика")
Ако няма разлика, се показва съобщение, че няма разлика; ако има разлика, тогава стойността в края на сезона се изважда от стойността в началото на сезона.

Формулата за бягане на 3000 метра е следната:
=АКО(VLOOKUP($B2,Sheet2!$B$2:$F$13,4,ВЯРНО)<>E2;"Има разлика";"Няма разлика")
Ако крайната и началната стойност не са равни, се показва съответното съобщение. Формулата за набирания може да бъде подобна на всяка от предишните, няма смисъл да я давате допълнително. Окончателният файл с откритите несъответствия е показан по-долу.

Малко уточнение. За по-лесно четене на формулите данните от двата файла бяха преместени в един (на различни листове), но това нямаше как да стане.

Видео, сравняващо използване и функции на два файла на MS Excel.

Вторият начин за решаване на проблема. Решение с помощта на MS Access.

Този проблем може да бъде разрешен, ако първо импортирате MS Excel файлове в Access. Що се отнася до самия метод за импортиране на външни данни, няма разлика в намирането на различни полета (всяка от представените опции ще свърши работа).

Последният е връзка между файловете на Excel и Access, така че когато промените данни във файлове на Excel, несъответствията ще бъдат намерени автоматично, когато стартирате заявка в MS Access.

Следващата стъпка след импортирането е създаването на релации между таблиците. Като свързващо поле изберете уникалното поле „Номер на артикул“.
Третата стъпка е да създадете проста заявка за избор с помощта на Query Builder.

В първата колона посочваме кои записи трябва да бъдат показани, а във втората - при какви условия ще бъдат показани записите. Естествено, за второто и третото поле действията ще бъдат подобни.

Видео, сравняващо MS файлове с Excel с помощта на MS Access.

В резултат на извършените манипулации се показват всички записи с различни данни в полето: „Бягане на 100 метра“. Файлът на MS Access е представен по-долу (за съжаление, SkyDrive не позволява вграждане като Excel файл)

Съществуват тези два метода за намиране на несъответствия в таблиците на MS Excel. Всеки има както предимства, така и недостатъци. Очевидно това не е изчерпателен списък от сравнения между двата файла на Excel. Очакваме вашите предложения в коментарите.

Имаме две таблици за поръчки, копирани в един работен лист. Необходимо е да се сравнят данните от две таблици в Excel и да се провери кои позиции са в първата таблица, но не и във втората. Няма смисъл ръчно да сравнявате стойността на всяка клетка.

Сравнете две колони за съвпадения в Excel

Как да сравняваме стойности в Excel от две колони? За да разрешите този проблем, препоръчваме да използвате условно форматиране, което бързо подчертава елементи, които са само в една колона. Работен лист с таблици:

Първата стъпка е да наименувате и двете таблици. Това улеснява разбирането кои диапазони от клетки се сравняват:

Изберете инструмента ФОРМУЛИ - Дефинирани имена - Присвояване на име.
В появилия се прозорец в полето „Име:” въведете стойността – Таблица_1.
Използвайте левия бутон на мишката, за да щракнете върху полето за въвеждане "Диапазон:" и изберете диапазона: A2:A15. И щракнете върху OK.

За втория списък изпълнете същите стъпки, само че му дайте име – Table_2. И посочете диапазона C2:C15 - съответно.

Полезен съвет! Имената на диапазони могат да се задават по-бързо с помощта на полето за имена. Намира се вляво от лентата с формули. Просто изберете диапазони от клетки и в полето за име въведете подходящото име за диапазона и натиснете Enter.

Сега нека използваме условно форматиране, за да сравним два списъка в Excel. Трябва да получим следния резултат:

Елементите, които са в Table_1, но не и в Table_2, ще бъдат показани в зелено. В същото време позициите, които са в Table_2, но не и в Table_1, ще бъдат маркирани в синьо.

Принципът на сравняване на данни между две колони в Excel

При дефинирането на условията за форматиране на клетките на колоната използвахме функцията COUNTIF. В този пример тази функция проверява колко пъти стойността на втория аргумент (например A2) се появява в списъка на първия аргумент (например Table_2). Ако брой пъти = 0, тогава формулата връща TRUE. В този случай на клетката се присвоява персонализираният формат, зададен в опциите за условно форматиране.

Връзката във втория аргумент е относителна, което означава, че всички клетки от избрания диапазон ще бъдат проверени една по една (например A2:A15). Например, за да сравните две ценови листи в Excel, дори и на различни листове. Втората формула работи по подобен начин. Същият принцип може да се приложи към различни подобни задачи.

Може би всеки, който работи с данни в Excel, е изправен пред въпроса как да сравни две колони в Excel за прилики и разлики. Има няколко начина да направите това. Нека разгледаме по-подробно всеки от тях.

Как да сравните две колони в Excel ред по ред

Когато сравнявате две колони с данни, често е необходимо да сравнявате данните във всеки отделен ред за съвпадения или разлики. Можем да направим такъв анализ с помощта на функцията. Нека видим как работи това, като използваме примерите по-долу.

Пример 1: Как да сравним две колони за съвпадения и разлики в един и същи ред

За да сравним данните във всеки ред от две колони в Excel, нека напишем проста формула. Формулата трябва да се вмъкне във всеки ред в съседната колона, до таблицата, в която се намират основните данни. След като създадем формула за първия ред на таблицата, можем да я разтеглим/копираме в останалите редове.

За да проверим дали две колони от един и същи ред съдържат едни и същи данни, се нуждаем от формулата:

=АКО(A2=B2; “Съвпадение”; “”)

Формулата, която определя разликите между данните на две колони в един ред, ще изглежда така:

=АКО(A2<>B2; "Не съвпадат"; “”)

Можем да поставим теста за съвпадение и разлика между две колони на един ред в една формула:

=АКО(A2=B2; „Съвпадение“; „Не съвпадение“)

=АКО(A2<>B2; "Не съвпадат"; "Съвпада")

Примерен резултат от изчислението може да изглежда така:

За да сравните данни в две колони на един и същи ред по начин, който различава малките и главните букви, използвайте формулата:

=АКО(МАЧ(A2,B2), “Съвпадение”, “Уникален”)

Как да сравним няколко колони за съвпадения в един ред на Excel

Excel ви позволява да сравнявате данни в множество колони на един и същ ред, като използвате следните критерии:

Намерете редове с еднакви стойности във всички колони на таблицата;
Намерете редове с еднакви стойности във всеки две колони на таблицата;

Пример 1. Как да намерите съвпадения в един ред в множество колони на таблица

Нека си представим, че нашата таблица се състои от няколко колони с данни. Нашата задача е да намерим редове, в които стойностите съвпадат във всички колони. Excel и функциите ще ни помогнат с това. Формулата за определяне на съвпаденията ще бъде следната:

=АКО(И(A2=B2,A2=C2); “Съвпада”; “ “)

Ако нашата таблица има много колони, тогава ще бъде по-лесно да използвате функцията в комбинация с:

=АКО(COUNTIF($A2:$C2,$A2)=3;”Съвпадение”;” “)

Във формулата "5" показва броя на колоните от таблицата, за която сме създали формулата. Ако вашата таблица има повече или по-малко колони, тогава тази стойност трябва да е равна на броя на колоните.

Пример 2: Как да намерите съвпадения на един и същ ред във всеки две колони на таблица

Нека си представим, че нашата задача е да идентифицираме от таблица с данни в няколко колони тези редове, в които данните са еднакви или се повтарят в поне две колони. Функциите и ще ни помогнат с това. Нека напишем формула за таблица, състояща се от три колони с данни:

=АКО(ИЛИ(A2=B2,B2=C2,A2=C2);”Съвпадение”;” “)

В случаите, когато има твърде много колони в нашата таблица, нашата формула с функцията ще бъде много голяма, тъй като в нейните параметри трябва да посочим критериите за съвпадение между всяка колона на таблицата. По-лесен начин в този случай е да използвате .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; „Уникален низ“; „Неуникален низ“)

=IF(COUNTIF($B:$B,$A5)=0; „Няма съвпадения в колона B“; „Има съвпадения в колона B“)

Тази формула проверява стойностите в колона B, за да види дали съвпадат с клетките в колона A.

Ако вашата таблица се състои от фиксиран брой редове, можете да посочите ясен диапазон във формулата (например $B2:$B10). Това ще ускори формулата.

Как да сравните две колони в Excel за съвпадения и да ги маркирате в цвят

Когато търсим съвпадения между две колони в Excel, може да се наложи да визуализираме намерените съвпадения или разлики в данните, например с помощта на цветно подчертаване. Най-лесният начин да маркирате съвпадения и разлики в цвета е да използвате условно форматиране в Excel. Нека да разгледаме как да направите това, като използвате примерите по-долу.

Намиране и маркиране на съвпадения в цвят в множество колони в Excel

В случаите, когато трябва да намерим съвпадения в няколко колони, тогава за това се нуждаем от:

Изберете колоните с данни, в които искате да изчислите съвпаденията;
В раздела „Начало“ на лентата с инструменти щракнете върху елемента от менюто „Условно форматиране“ -> „Правила за маркиране на клетки“ -> „Повтарящи се стойности“;
В изскачащия диалогов прозорец изберете „Повтаряне“ в левия падащ списък, а в десния падащ списък изберете в какъв цвят да бъдат маркирани повтарящи се стойности. Щракнете върху бутона „OK“:
След това съвпаденията ще бъдат маркирани в избраната колона:

Намерете и маркирайте съвпадащи редове в Excel

Намирането на съвпадащи клетки с данни в две или повече колони и търсенето на съвпадения на цели редове с данни са различни концепции. Моля, обърнете внимание на двете таблици по-долу:

Таблиците по-горе съдържат същите данни. Тяхната разлика е, че в примера отляво търсихме съвпадащи клетки, а отдясно намерихме цели повтарящи се редове с данни.

Нека да разгледаме как да намерим съвпадащи редове в таблица:

Вдясно от таблицата с данните ще създадем помощна колона, в която срещу всеки ред с данните ще поставим формула, която комбинира всички стойности на реда на таблицата в една клетка:

=A2&B2&C2&D2

В спомагателната колона ще видите данните от комбинираната таблица:

Сега, за да определите съвпадащите редове в таблицата, направете следните стъпки:

Изберете областта с данни в спомагателната колона (в нашия пример това е диапазон от клетки E2:E15 );
В раздела „Начало“ на лентата с инструменти щракнете върху елемента от менюто „Условно форматиране“ -> „Правила за маркиране на клетки“ -> „Повтарящи се стойности“;
В изскачащия диалогов прозорец изберете „Повтаряне“ в левия падащ списък, а в десния падащ списък изберете в какъв цвят ще бъдат маркирани повтарящите се стойности. Щракнете върху бутона „OK“:
След това дублиращите се редове ще бъдат маркирани в избраната колона:

Често задачата е да се сравнят два списъка с елементи. Правенето на това ръчно е твърде досадно, а също така не може да се изключи възможността за грешки. Excel прави тази операция лесна. Този съвет описва метод, използващ условно форматиране.

На фиг. Фигура 164.1 показва пример на два многоколонни списъка с имена. Използването на условно форматиране може да направи разликите в списъците очевидни. Тези примерни списъци съдържат текст, но въпросният метод работи и с числови данни.

Първият списък е A2:B31, този диапазон се нарича OldList. Вторият списък е D2:E31, диапазонът се извиква Нов списък. Диапазоните са именувани с помощта на командата Формули Дефинирани имена Задайте име. Не е необходимо да наименувате диапазоните, но това улеснява работата с тях.

Нека започнем с добавяне на условно форматиране към стария списък.

Изберете клетки в диапазон OldList.
Изберете.
В прозореца Създайте правило за форматиранеизберете извикания елемент Използвайте формула
Въведете тази формула в полето на прозореца (фиг. 164.2): =COUNTIF(NewList;A2)=0.
Щракнете върху бутона формати укажете форматирането, което ще се приложи, когато условието е вярно. Най-добре е да изберете различни цветове за запълване.
Натиснете OK.

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

Изберете клетки в диапазон Нов списък.
Изберете Начало Условно форматиране Създаване на правилоза да отворите диалогов прозорец Създайте правило за форматиране.
В прозореца Създайте правилоформатиране на избран елемент Използвайте формулаза определяне на клетките за форматиране.
Въведете тази формула в полето на прозореца: =COUNTIF(OldList;D2)=0 .
Щракнете върху бутона формати задайте форматирането, което да се прилага, когато условието е вярно (различен цвят на запълване).
Натиснете OK.

В резултат на това имената, които са в стария списък, но не и в новия, ще бъдат маркирани (фиг. 164.3). Освен това имената в новия списък, които не са в стария списък, също са маркирани, но в различен цвят. Имената, които се появяват и в двата списъка, не са маркирани.

И двете формули за условно форматиране използват функцията COUNTIF. Той изчислява колко пъти дадена стойност се появява в диапазон. Ако формулата върне 0, това означава, че елементът не е в диапазона. По този начин условното форматиране поема и цветът на фона на клетката се променя.