Методи за сравняване на таблици в Microsoft Excel

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

Прочетете още: Сравнение на два документа в MS Word

Методи за сравнение

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

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

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

Метод 1: Една проста формула

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

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

1. За това имаме нужда от допълнителна колона на листа. Въведете знака "=" там. След това кликнете върху първото име, което искате да сравните в първия списък. Отново поставете символа "=" от клавиатурата. След това кликнете върху първата клетка на колоната, която сравняваме във втората таблица. Беше получен израз от следния тип:

   =A2=D2

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

2. Кликваме върху клавиша Enter, за да получим резултатите от сравнението. Както можете да видите, при сравняването на първите клетки и на двата списъка програмата показва индикатора "TRUE" , което означава, че данните съвпадат.
3. Сега трябва да извършим подобна операция с останалите клетки и на двете таблици в колоните, които сравняваме. Но можете просто да копирате формулата, което ще спести много време. Особено този фактор е важен при сравняване на списъци с голям брой линии.

   Най-лесният начин да направите копирането е да използвате дръжката за пълнене. Преместваме курсора до долния десен ъгъл на клетката, където имаме индикатора "TRUE" . В този случай тя трябва да се превърне в черен кръст. Това е маркер за попълване. Кликнете върху левия бутон на мишката и плъзнете курсора надолу по броя линии в масивите от таблицата за сравнение.

4. Както можете да видите, сега в допълнителната колона бяха показани всички резултати от сравнението на данните в две колони на таблични масиви. В нашия случай данните не съответстват само на един ред. При сравняването им формулата произвежда резултата "LIE" . За всички други линии, както виждаме, формулата за сравнение е издадена от индикатора "TRUE" .
5. Освен това е възможно да се изчисли броят на несъответствията, като се използва специална формула. За да направите това, изберете елемента на листа, където ще се изведе. След това кликнете върху иконата "Вмъкване на функция" .
6. В прозореца Wizard of Functions , под групата "Математически" оператори, изберете името SUMPRODUCT . Кликваме върху бутона "OK" .
7. Прозорецът на аргументите на функцията SUMPRODUCT е активиран , основната задача на която е да се изчисли сумата от продуктите от избрания диапазон. Но тази функция може да се използва за нашите цели. Синтаксисът е съвсем прост:

   =СУММПРОИЗВ(массив1;массив2;…)

   Като аргументи могат да се използват до 255 масиви. Но в нашия случай ще използваме само две масиви, като един аргумент.

   Поставете курсора в полето "Array1" и изберете в листа сравнения обхват от данни в първата област. След това поставете знака "не равен" ( <> ) в полето и изберете сравнения обхват на втората област. След това увийте резултантния израз с скоби, пред които поставяме два "-" знака. В нашия случай изразът беше:

   --(A2:A7<>D2:D7)

   Кликваме върху бутона "OK" .

8. Операторът изчислява и показва резултата. Както можете да видите, в нашия случай резултатът е равен на числото "1" , т.е. това означава, че в сравняваните списъци е установено едно несъответствие. Ако списъците бяха напълно идентични, резултатът щеше да е равен на числото "0" .

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

=B2=Лист2!B2

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

Метод 2: Избиране на групи клетки

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

1. Избираме сравняваните масиви. Отидете в раздела "Начало" . След това кликнете върху иконата "Намиране и Избиране " , която се намира на лентата в полето "Редактиране" . Отваря се списък, в който избирате елемента "Избор на група клетки ..." .

   Освен това можете да получите достъп до прозореца за избор на група клетки по друг начин. Тази опция ще бъде особено полезна за тези потребители, които имат версия на програмата по-рано от Excel 2007, защото тези методи не поддържат използването на бутона "Намиране и подчертаване" . Изберете масивите, които искаме да сравняваме, и натиснете клавиша F5 .

2. Активиран е малък прозорец за преход. Кликваме върху бутона "Избери ..." в долния ляв ъгъл.
3. След това, коя от двете опции по-горе изберете, прозорецът за избор на групи от клетки започва. Задайте ключа на позиция "Избиране по редове" . Кликнете върху бутона "OK" .
4. Както можете да видите, след това несъответстващите стойности на линиите ще бъдат откроени с различен оттенък. Освен това, както може да се прецени от съдържанието на формулата, програмата ще направи една от клетките активни в посочените неподредени редове активна.

Метод 3: условно форматиране

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

1. На първо място, ние избираме коя област на таблицата ще разгледаме като основна и как да търсим различия. Нека да го направим във втората таблица. Ето защо ние избираме списъка на служителите, които са в него. След като се преместим в раздела "Начало" , кликваме върху бутона "Условно форматиране" , който има място върху лентата в блока "Стилове" . От падащия списък отидете на "Управление на правилата".
2. Прозорецът на мениджъра на правилата е активиран. Кликнете върху бутона "Създаване на правило" в него.
3. В прозореца, който се отваря, избираме елемента "Използвай формулата" . В полето "Форматиране на клетки" задаваме формула, съдържаща адресите на първите клетки от диапазоните на сравняваните колони, разделени със знака "не е равно на" ( <> ). Само преди този израз този път ще има знак "=" . В допълнение към всички координати на колоните в тази формула трябва да приложите абсолютно адресиране. За да направите това, изберете формулата с курсора и натиснете F4 три пъти. Както можете да видите, около всички адреси на колоните има знак за долар, което означава превръщане на връзките в абсолютни. За нашия конкретен случай формулата има следната форма:

   =$A2<>$D2

   Пишем този израз в горното поле. След това кликнете върху бутона "Форматиране ..." .

4. Прозорецът "Форматиране на клетки" е активиран. Отворете раздела "Попълване" . Тук в списъка с цветове спираме избора на цвета, който искаме да нарисуваме тези елементи, където данните няма да съвпаднат. Кликнете върху бутона "OK" .
5. Връщайки се към прозореца за създаване на правилото за форматиране, кликнете върху бутона "OK" .
6. След автоматично преместване в прозореца "Правила за управление" кликнете върху бутона "OK" и в него.
7. Сега във втората таблица елементите, които имат данни, които не съвпадат със съответните стойности на първата зона на таблицата, ще бъдат маркирани с избрания цвят.

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

1. Ние правим набор от области, които трябва да бъдат сравнени.
2. Преминаваме към раздела "Начало" . Кликваме върху бутона "Условно форматиране" . В активирания списък изберете елемента "Правила за избор на клетки" . В следващото меню избираме позицията "Дублирани стойности" .
3. Появява се прозорецът за избор на дублирани стойности. Ако сте направили всичко правилно, то в този прозорец остава само да кликнете върху бутона "OK" . Въпреки това, ако желаете, можете да изберете различен цвят за маркиране в съответното поле на този прозорец.
4. След като извършим посоченото действие, всички повтарящи се елементи ще бъдат осветени с избрания цвят. Елементите, които не съвпадат, остават оцветени в оригиналния им цвят (по подразбиране е бял). По този начин можете веднага да видите визуално каква е разликата между масивите.

Ако желаете, можете, напротив, да нарисувате несъответстващите елементи и тези, които съвпадат, да се оставят с еднакъв цвят. В този случай алгоритъмът на действията е почти същият, но в прозореца за избор на дублирани стойности в първото поле, вместо "Дублиране" , изберете опцията "Уникален" . След това кликнете върху бутона "OK" .

По този начин тези показатели, които не съвпадат, ще бъдат подчертани.

Урокът: Условно форматиране в Excel

Метод 4: Сложната формула

Също така можете да сравнявате данните чрез сложна формула, основана на функцията COUNTIF . С помощта на този инструмент можете да преброите колко всеки елемент от избраната колона на втората таблица се повтаря в първия.

Операторът COUNTRY се отнася до статистическа група от функции. Неговата задача е да брои броя на клетките, чиито стойности отговарят на дадено условие. Синтаксисът на този оператор е:

=СЧЁТЕСЛИ(диапазон;критерий)

Аргументът "Range" е адреса на масива, в който се изчисляват съвпадащите стойности.

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

1. Изберете първия елемент от допълнителната графа, в който ще се броят броят на мачовете. След това кликнете върху иконата "Вмъкване на функция" .
2. Стартира съветникът за функции . Да отидем в категорията "Статистика" . Намираме името "СЪВЕТ" в списъка. След като го изберете, кликнете върху бутона "OK" .
3. Прозорецът на аргументите на оператора на Съвета стартира . Както можете да видите, имената на полетата в този прозорец съответстват на имената на аргументите.

   Задайте курсора в полето "Обхват" . След това, като задържите левия бутон на мишката, изберете всички стойности на колоната с имената на втората таблица. Както можете да видите, координатите незабавно попадат в определеното поле. Но за нашите цели е необходимо този адрес да бъде абсолютен. За да направите това, изберете координатите в полето и натиснете F4 .

   Както можете да видите, връзката е абсолютна форма, която се характеризира с наличието на знаци за долар.

   След това отидете в полето "Критерий" , като поставите курсора там. Кликваме върху първия елемент с имена в първия обхват на таблицата. В този случай оставяме справката относителна. След като се покаже в полето, можете да кликнете върху бутона "OK" .

4. Резултатът се показва в листовия елемент. То е равно на числото "1" . Това означава, че в списъка с имената на втората таблица се появява еднократно фамилното име "Grinev VP" , което е първото в списъка на първия табличен масив.
5. Сега трябва да създадем подобен израз за всички останали елементи от първата таблица. За да направите това, извършете копирането, като използвате маркера за попълване, както направихме преди. Поставете курсора в долната дясна част на листовия елемент, който съдържа функцията COUNTIF и след като го превърнете в маркер за запълване, затегнете левия бутон на мишката и плъзнете курсора надолу.
6. Както можете да видите, програмата направи изчисление на съвпаденията, като сравнява всяка клетка от първата таблица с данни, които се намират във втория обхват на таблицата. В четири случая резултатът е "1" , а в два случая - "0" . Това означава, че програмата не може да намери във втората таблица двете стойности, които са налични в първия табличен масив.

Разбира се, този израз, за ​​да се сравнят табличните показатели, можете да приложите в съществуващата форма, но има възможност да я подобрите.

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

1. На първо място, ще преработим нашата формула за COUNTRY , малко , нека я направим един от аргументите на оператора на IF . За целта изберете първата клетка, в която се намира операторът COUNTRY . В реда на формулите, преди да добавим израза "IF" без котировки, отворете скобата. Освен това, за да ни улесни работата, ние избираме стойността "IF" във формулата и кликнете върху иконата "Insert function" .
2. Появява се прозорецът на аргументите на функцията IF . Както можете да видите, първото поле на прозореца вече е изпълнено със стойността на оператора на Съвета . Но трябва да завършим нещо друго в тази област. Настройваме курсора там и добавяме "= 0" без кавички към вече съществуващия израз.

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

   СТРОКА(D2)

   Сега изявлението LINE ще отчита функцията, ако е номерът на линията, в който се намира конкретното фамилно име, а ако условието, посочено в първото поле, е изпълнено, функцията IF ще изведе този номер в клетката. Кликнете върху бутона "OK" .

3. Както можете да видите, първият резултат се показва като "FALSE" . Това означава, че стойността не отговаря на условията на оператора на ИФ . Това означава, че първото фамилно име присъства и в двата списъка.
4. Използвайки маркера за попълване, ние копираме израза на оператора IF на цялата колона по обичайния начин. Както можете да видите, за двата елемента, които присъстват във втората таблица, но не и в първата, формулата дава номерата на редовете.
5. Отстъпваме от масата вдясно и попълваме колоната с номера в ред, започвайки от 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. Ще се отвори съветникът за функциите . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Кликваме върху бутона "OK" .
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Кликваме върху бутона "OK" .

9. Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой ( ; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .
11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
12. Запускается окно аргументов функции ИНДЕКС . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

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

    Кликнете върху бутона "OK" .

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

Способ 5: сравнение массивов в разных книгах

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

Урокът: Как открыть Эксель в разных окнах

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

• ← SQL заявки в Microsoft Excel
• Работа с свързани таблици в Microsoft Excel →