Сравнение на таблиците в Excel

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

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

= B2 = list2! B2

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

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

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

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

   

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

2. Активиран е малък преходен прозорец. Кликнете върху бутона "Маркирайте ..." в долния ляв ъгъл.



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



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

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

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

1. На първо място, ние избираме какъв табличен регион ще се счита за основен и какво да търси разликата. Последно да направим във втората маса. Ето защо, ние разпределяме списък на работниците в него. Преместване в раздела "Начало", кликнете върху бутона "Условно форматиране", който има място на лентата в блока "Стилове". От падащия списък преминавате през елемента "Управление на правилата".



2. Прозорецът Dispatcher Dispater на правилата е активиран. Щракнете върху него към бутона "Създаване на правило".



3. В прозореца за бягане, ние избираме позицията "USE Formula". В полето "Формат клетка" напишете формула, съдържаща адресите на първите клетки на диапазоните в сравнение с колони, разделени от знака "не е равен" (). Точно преди този израз този път ще издържи знака "=". В допълнение, към всички координати на колоната в тази формула, трябва да приложите абсолютно адресиране. За да направите това, ние разпределяме формулата с курсора и кликнете три пъти върху клавиша F4. Както виждаме, знакът долар се появи близо до всички адреси на колоните, което означава, че връзките към абсолютно. За нашия конкретен случай формулата ще приеме следната форма:

   = $ A2 $ d2

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



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



5. Връщайки се към прозореца на правилото за форматиране, кликнете върху бутона "OK".



6. След автоматично преминаване към прозореца "Правила Мениджър", кликнете върху бутона "OK" и в него.



7. Сега във втората таблица елементите, които имат данни, които са несъвместими със съответните стойности на първата зона на масата, ще бъдат маркирани в избрания цвят.

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

1. Ние произвеждаме избора на зони, които трябва да сравните.



2. Извършваме прехода към раздела, наречен "Начало". Щракнете върху бутона "Условно форматиране". В активирания списък изберете позицията "Правила за разпределение на клетките". В следващото меню направете позицията "повтарящи се стойности".



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



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

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

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

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

Метод 4: Цялостна формула

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

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

= Графици (обхват; критерий)

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

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

1. Ние разпределяме първия елемент от допълнителната колона, в която ще се изчисли броят на съвпаденията. След това кликнете върху иконата "Вмъкване на функция".



2. Статията на функциите започва. Отидете в категория "Статистически". Намерете в списъка името "Countles". След подбор, кликнете върху бутона "OK".



3. Прозорецът на аргументите на оператора започна да работи. Както виждаме, имената на полетата в този прозорец съответстват на имената на аргументите.

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

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

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



4. Резултатът се извежда в листния елемент. Тя е равна на броя "1". Това означава, че в списъка на имената на втората маса името "Гринев V. P.", който е първият в списъка на първия масив на масата, се случва веднъж.



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



6. Както виждаме, програмата е направила изчисляване на съвпаденията, сравнявайки всяка клетка на първата таблица с данни, които са разположени във втория диапазон на масата. В четири случая резултатът е "1" и в два случая - "0". Това означава, че програмата не може да намери двете стойности във втората таблица, която се предлага в първата масивна маса.

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

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

1. На първо място, ние рециклираме нашата формула, но ще го направим един от аргументите на оператора, ако. За да направите това, ние подчертаваме първата клетка, в която операторът е оператор. В нишката с формула пред нея добавяме израз на "IF" без кавички и отварям скобата. Освен това за нас беше по-лесно да работим, разпределяме формулите във формулата и кликнете върху иконата "Insert Function".



2. Прозорецът на аргументите на функциите се отваря, ако. Както виждате, първият прозорец на прозореца вече е попълнен с оценката на Съвета на оператора. Но трябва да добавим нещо друго в това поле. Ние поставяме там курсора и вече съществуващия израз добави "= 0" без кавички.

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

   Ред (d2)

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



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



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



5. Ние се оттегляме от зоната на масата вдясно и попълнете колоната с цифри в ред, като се започне от 1. Броят на номерата трябва да съответства на броя на редовете във втората компилационна таблица. За да ускорите процедурата за номериране, можете също да използвате маркера за пълнене.



6. След това подчертаваме първата клетка вдясно на високоговорителя с номерата и кликнете върху иконата "Insert Function".



7. Отваря се съветник. Отидете в категорията "Статистически" и създайте името на "най-малкото" име. Кликнете върху бутона "OK".



8. Функцията е най-малка, аргументите на горепосочените аргументи са предназначени да изтеглят определената най-малка стойност.

   В полето "Масива" трябва да посочите координатите на обхвата на допълнителната колона "Броят на съвпаденията", който преди това сме трансформирали с функцията, ако. Ние правим всички връзки абсолют.

   В полето "K" е посочено, което в профила трябва да се покаже най-малката стойност. Тук посочваме координатите на първата клетка на колоната с номерирането, което наскоро добавихме. Адресът остави роднина. Кликнете върху бутона "OK".



9. Операторът показва резултата - номер 3. Именно най-малкото от номерацията на непоследователните линии на масивите на масата. Използвайки маркера за пълнене, копирайте формулата към самия нос.



10. Сега, като знаете броя на редиците на непоследователните елементи, можем да вмъкнем в клетката и техните стойности, използвайки функцията на индекса. Изберете първия елемент на листа, съдържащ формулата, е най-малката. След това отидете на линейните формули и преди името на "най-малко" добавете името "индекс" без кавички, незабавно отворете скобата и поставете точка със запетая (;). След това разпределяме формулата "Индекс" в реда и кликнете върху иконата "Paste Function".



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



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

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

    В полето "Array" задайте адреса на стойността на втората таблица. В същото време всички координати правят абсолютно, т.е. ние поставяме знак за долар преди описания от нас метод.

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



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

Метод 5: Сравнение на масиви в различни книги

Когато сравнявате диапазоните в различни книги, можете да използвате горните методи, с изключение на тези опции, където се изисква поставянето на таблични зони на един лист. Основното условие за процедурата за сравнение в този случай е отворът на прозорците на двата файла едновременно. За версии на Excel 2013 и по-късно, както и за версии до Excel 2007, няма проблеми с прилагането на това състояние. Но в Excel 2007 и Excel 2010, за да отворите двата прозорци едновременно, се изискват допълнителни манипулации. Как да го направите в отделен урок.

Урок: Как да отворите Excel в различни Windows

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