Методи за споредување на табели во Microsoft Excel

Доста често, корисниците на Excel се соочуваат со задача да споредуваат две табели или списоци за да идентификуваат разлики или елементи што недостасуваат во нив. Секој корисник се справува со оваа задача на свој начин, но најчесто се троши прилично големо време за решавање на ова прашање, бидејќи не сите пристапи кон овој проблем се рационални. Во исто време, постојат неколку докажани алгоритми на дејствување кои ќе ви овозможат да ги споредите списоците или низите на табелите за прилично кратко време со минимален напор. Ајде да ги разгледаме подетално овие опции.

Погледнете исто така: Споредба на два документи во MS Word

Методи за споредување

Постојат неколку начини да се споредат масичките во Excel, но сите тие можат да се поделат во три големи групи:

Врз основа на оваа класификација, пред сè, се избираат методи за споредување, како и специфични активности и алгоритми се утврдени за задачата. На пример, кога се споредувате во различни книги, треба да отворите две датотеки на Ексел во исто време.

Покрај тоа, треба да се каже дека споредувањето на области на табели има смисла само кога тие имаат слична структура.

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

Најлесен начин да се споредат податоците во две табели е да се користи едноставна формула за еднаквост. Ако податоците се совпаѓаат, тогаш тој го дава TRUE индикаторот, и ако не, тогаш FALSE. Може да ги споредите и нумеричките и текстуалните податоци. Недостаток на овој метод е што може да се користат само ако податоците во табелата се нарачани или сортирани на ист начин, се синхронизираат и имаат ист број на линии. Ајде да видиме како да се користи овој метод во пракса со пример на две табели поставени на еден лист.

Значи, имаме две едноставни табели со списоци на вработени и нивните плати. Неопходно е да се споредат списоците на вработените и да се идентификуваат недоследности помеѓу колоните во кои се сместени имињата.

1. За да го направите ова, потребна ни е дополнителна колона на листот. Внесуваме знак таму "=". Потоа кликнуваме на првата ставка што сакате да ја споредите во првиот список. Повторно го ставаме симболот "=" од тастатурата. Следно, кликнете на првата ќелија на колоната што ја споредуваме во втората табела. Резултатот е израз на следниот вид:

   = A2 = D2

   

   Иако, се разбира, во секој случај, координатите ќе бидат различни, но суштината ќе остане иста.

2. Кликнете на копчето Внесетеза да добиете резултати од споредба. Како што можете да видите, при споредување на првите ќелии на обете списоци, програмата покажа индикатор „ВИСТИНА“, што значи совпаѓање на податоците.



3. Сега треба да извршиме слична операција со другите клетки на обете табели во колоните што ги споредуваме. Но, можете едноставно да ја копирате формулата, што значително ќе заштеди време. Овој фактор е особено важен кога се споредуваат списоците со голем број линии.

   Постапката за копирање најлесно се изведува со помош на маркерот за полнење. Ние лебдиме над долниот десен агол на ќелијата, каде што го добивме индикаторот „ВИСТИНА“. Во исто време, треба да се претвори во црн крст. Ова е маркер за полнење. Ние го притискаме левото копче на глувчето и го влечеме курсорот надолу по бројот на линиите во споредените низи на табели.



4. Како што можете да видите, сега во дополнителна колона се прикажани сите резултати од споредбата на податоците во две колони на табелите. Во нашиот случај, податоците на само една линија не се совпаѓаат. Кога ги споредивме, формулата го донесе резултатот ЛЕСНО. За сите други линии, како што гледаме, формулата за споредување даде индикатор „ВИСТИНА“.



5. Покрај тоа, можно е да се пресмета бројот на разлики со помош на специјална формула. За да го направите ова, изберете го елементот на листот каде што ќе се прикаже. Потоа кликнете на иконата "Вметни функција".



6. Во прозорецот Волшебници за функции во група оператори „Математички“ изберете го името СУМПРОДУКТ. Кликнете на копчето „ОК“.



7. Активиран е прозорецот за аргументи за функција. СУМПРОДУКТчија главна задача е да се пресмета збирот на производите од избраниот опсег. Но, оваа функција може да се користи за наши цели. Синтаксата е прилично едноставна:

   = СУМПРОДУКТ (низа 1; низа2; ...)

   Севкупно, адреси до 255 низи може да се користат како аргументи. Но, во нашиот случај, ние ќе користиме само две низи, како додаток, како еден аргумент.

   Ставете го курсорот на полето "Низа1" и одберете го на листот споредениот опсег на податоци во првата област. После тоа, ставете знак на полето не еднаков () и изберете го споредениот опсег на вториот регион. Следно, добиениот израз го завиткаме во загради пред кои ставаме два карактери "-". Во нашиот случај, овој израз се покажа:

   - (A2: A7D2: D7)

   Кликнете на копчето „ОК“.



8. Операторот го пресметува и прикажува резултатот. Како што можете да видите, во нашиот случај, резултатот е еднаков на бројот "1", тоа значи, тоа значи дека е пронајдена една неусогласеност во споредените списоци. Ако списоците беа целосно идентични, тогаш резултатот ќе биде еднаков на бројот "0".

На ист начин, можете да ги споредите податоците во табели што се наоѓаат на различни листови. Но, во овој случај, пожелно е линиите во нив да бидат нумерирани. Инаку, постапката за споредување е скоро иста како што е опишано погоре, освен фактот дека кога ќе ја внесете формулата треба да се префрлате помеѓу листови. Во нашиот случај, изразот ќе изгледа вака:

= B2 = Лист2! Б2

Тоа е, како што гледаме, пред координатите на податоците, кои се наоѓаат на други листови, освен таму каде што се прикажува резултатот од споредбата, се означени бројот на листот и извичник.

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

Споредбата може да се направи со помош на алатката за избор на клеточна група. Може да се користи и за споредување само на синхронизирани и нарачани списоци. Покрај тоа, во овој случај, списоците треба да се наоѓаат едни до други на истиот лист.

1. Ние ги избираме споредените низи. Одете на јазичето „Дома“. Следно, кликнете на иконата Најдете и означетесместена на лентата во алатникот „Уредување“. Се отвора список во кој ќе изберете позиција "Избор на група клетки ...".

   

   Покрај тоа, можеме да дојдеме до посакуваниот прозорец за избор на група клетки на друг начин. Оваа опција ќе биде особено корисна за оние корисници кои инсталирале верзија на програмата порано од Excel 2007, бидејќи методот преку копчето Најдете и означете овие апликации не поддржуваат. Ние ги избираме низите што сакаме да ги споредиме и притиснете го копчето Ф5.

2. Активиран е мал прозорец за транзиција. Кликнете на копчето "Изберете ..." во долниот лев агол.



3. После тоа, која и да е од двете од горенаведените опции што ќе ги изберете, се стартува прозорецот за избор на групи клетки. Поставете го прекинувачот во позиција "Изберете линија по линија". Кликнете на копчето „ОК“.



4. Како што можете да видите, после ова неусогласените вредности на линиите ќе бидат обележани со различна нијанса. Покрај тоа, како што може да се суди од содржината на лентата со формули, програмата ќе направи активна една од клетките лоцирани во наведените неусогласени линии.

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

Можете да споредите користејќи го методот на условно форматирање. Како и во претходниот метод, споредените области треба да бидат на истиот работен лист на Excel и да бидат синхронизирани едни со други.

1. Прво на сите, ние избираме која област на маса ќе ја разгледаме главната, и во која да бараме разлики. Да го направиме последното во втората табела. Затоа, ние го избираме списокот на работници лоцирани во него. Префрлајќи се на јазичето „Дома“кликнете на копчето Условно форматирањекоја се наоѓа на лентата во блокот Стилови. Од паѓачката листа, одете до Управување со правила.



2. Активиран е прозорецот за управување со правила. Кликнете на копчето во него Создадете правило.



3. Во прозорецот што започнува, одберете ја позицијата Користете формула. На полето "Формат на ќелии" напишете формула која ги содржи адресите на првите ќелии од опсегите на споредените колони, одделени со знак „не еднаков“ () Само овој израз ќе се соочи овој пат. "=". Покрај тоа, апсолутно обраќање мора да се примени на сите координати на колоните во оваа формула. За да го направите ова, изберете ја формулата со курсорот и притиснете го копчето три пати Ф4. Како што можете да видите, знак на долар се појавил близу сите адреси на колоните, што значи вртење на врските во апсолутни. За нашиот конкретен случај, формулата ќе ја има следната форма:

   = $ A2 $ D2

   Овој израз го пишуваме во горенаведеното поле. После тоа, кликнете на копчето "Формат ...".



4. Прозорецот е активиран Формат на клетки. Одете на јазичето „Пополнете“. Тука во списокот на бои го запираме изборот за бојата со која сакаме да ги обоиме оние елементи каде податоците нема да одговараат. Кликнете на копчето „ОК“.



5. Враќајќи се на прозорецот за создавање правило за форматирање, кликнете на копчето „ОК“.



6. Откако автоматски се пресели во прозорецот Менаџер за правила кликнете на копчето „ОК“ и во него.



7. Сега во втората табела, елементите што имаат податоци што не се совпаѓаат со соодветните вредности на областа на првата табела ќе бидат обележани во избраната боја.

Постои уште еден начин да се примени форматирање на условно на задачата. Како и претходните опции, таа бара локација и на двете споредбени области на истиот лист, но за разлика од претходно опишаните методи, состојбата за синхронизација или сортирање на податоците нема да биде задолжителна, што ја разликува оваа опција од претходно опишаните.

1. Избираме области што треба да се споредат.



2. Одете на табулаторот наречен „Дома“. Кликнете на копчето Условно форматирање. Во активираната листа, одберете ја позицијата Правила за избор на ќелии. Во следното мени правиме избор на позиција Дупликат вредности.



3. Започнува прозорецот за конфигурирање на изборот на дупликати вредности. Ако направивте сè правилно, тогаш во овој прозорец останува само да кликнете на копчето „ОК“. Иако, ако сакате, во соодветното поле на овој прозорец, можете да изберете различна боја на осветлување.



4. Откако ќе извршиме одредено дејство, сите елементи што повторуваат ќе бидат обележани во избраната боја. Оние елементи што не се совпаѓаат ќе останат насликани во нивната оригинална боја (бело по дифолт). Така, можете веднаш визуелно да видите каква е разликата помеѓу низите.

Ако сакате, можете, напротив, да ги обоите неусогласените елементи и оние индикатори што одговараат, оставете го пополнувањето со иста боја. Во овој случај, алгоритмот на дејства е скоро ист, но во прозорецот за поставки за истакнување на дупликати вредности во првото поле наместо параметарот Дупликат треба да изберете „Единствено“. После тоа, кликнете на копчето „ОК“.

Така, ќе бидат потенцирани токму оние индикатори кои не се совпаѓаат.

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

Метод 4: комплексна формула

Можете исто така да ги споредите податоците користејќи комплексна формула заснована на функцијата СОГЛАСУВАЕ. Користејќи ја оваа алатка, можете да пресметате колку секој елемент од избраната колона од втората табела се повторува во првата.

Оператор СОГЛАСУВАЕ се однесува на статистичка група на функции. Неговата задача е да го брои бројот на клетки чии вредности исполнуваат дадена состојба. Синтаксата на овој оператор е како што следува:

= COUNTIF (опсег; критериум)

Аргумент „Опсег“ ја претставува адресата на низата во која се пресметуваат вредностите за појавување.

Аргумент „Критериум“ поставува состојба на натпревар. Во нашиот случај, тоа ќе бидат координатите на специфичните ќелии во областа на првата табела.

1. Го избираме првиот елемент на дополнителната колона во која ќе се брои бројот на натпревари. Следно, кликнете на иконата "Вметни функција".



2. Започнување Волшебници за функции. Одете во категоријата „Статистика“. Најдете го името во списокот „COUNTIF“. Откако ќе го изберете, кликнете на копчето „ОК“.



3. Се стартува прозорецот за аргументи на операторот СОГЛАСУВАЕ. Како што можете да видите, имињата на полињата во овој прозорец одговараат на имињата на аргументите.

   Поставете го покажувачот на полето „Опсег“. После тоа, држејќи го левото копче на глувчето, изберете ги сите вредности на колоната со имињата на втората табела. Како што можете да видите, координатите веднаш спаѓаат во одредено поле. Но, за наши цели, оваа адреса треба да биде апсолутна. За да го направите ова, изберете ги овие координати во полето и притиснете го копчето Ф4.

   Како што можете да видите, врската доби апсолутна форма, која се карактеризира со присуство на знаци на доларот.

   Потоа одете на поле „Критериум“со поставување на курсорот таму. Ние кликнуваме на првиот елемент со презимиња во опсегот на првата табела. Во овој случај, оставете ја врската релативна. Откако ќе се прикаже во полето, можете да кликнете на копчето „ОК“.



4. Резултатот се прикажува во елементот на листот. Таа е еднаква на бројот "1". Ова значи дека во списокот со имиња на втората табела, презиме „Гринев В.П.“, што е прва во списокот на низата на првата табела, се јавува еднаш.



5. Сега треба да создадеме сличен израз за сите други елементи од првата табела. За да го направите ова, ние ќе копираме со употреба на маркерот за пополнување, како што веќе правевме. Ставете го покажувачот во долниот десен дел од елементот на листот што ја содржи функцијата СОГЛАСУВАЕ, и откако ќе го претворите во маркер за пополнување, држете го левото копче на глувчето и влечете го покажувачот надолу.



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

Се разбира, овој израз, за ​​да се споредат табеларните индикатори, може да се користи во неговата постоечка форма, но постои можност да се подобри.

Уверени сме дека тие вредности што се во втората табела, но не се во првата, се прикажани во посебен список.

1. Како прво, малку ќе ја преработиме нашата формула СОГЛАСУВАЕ, имено, ние го правиме еден од аргументите на операторот Ако. За да го направите ова, изберете ја првата ќелија во која се наоѓа операторот СОГЛАСУВАЕ. Во редот на формулите пред него, додадете го изразот Ако без наводници и отворете ја заградата. Следно, за да ни олесни работата, изберете ја вредноста во лентата со формули Ако и кликнете на иконата "Вметни функција".



2. Се отвора прозорецот за аргументи на функциите Ако. Како што можете да видите, првото поле на прозорецот е веќе исполнето со вредноста на операторот СОГЛАСУВАЕ. Но, треба да додадеме нешто друго на ова поле. Го поставуваме курсорот таму и го додаваме постојниот израз "=0" без понуди

   После тоа, одете на терен "Значење ако е точно". Овде ќе користиме друга вгнездена функција - ЛИНИЈА. Внесете го зборот ЛИНИЈА без наводници, потоа отворете ги држачите и наведете ги координатите на првата ќелија со презиме во втората табела, а потоа затворете ги заградите. Поточно, во нашиот случај, на терен "Значење ако е точно" Следниот израз се покажа:

   ЛИНИЈА (Д2)

   Сега операторот ЛИНИЈА ќе известува за функциите Ако бројот на линијата во која се наоѓа одредено презиме и во случај кога е исполнета состојбата наведена во првото поле, функцијата Ако ќе го прикаже овој број во ќелијата. Кликнете на копчето „ОК“.



3. Како што можете да видите, првиот резултат се прикажува како ЛЕСНО. Ова значи дека вредноста не ги задоволува условите на операторот. Ако. Тоа е, првото презиме е присутно во двата списоци.



4. Користејќи го маркерот за пополнување, го копираме изразот на операторот на вообичаен начин Ако на целата колона. Како што можете да видите, за две позиции што се присутни во втората табела, но не во првата, формулата дава броеви на линиите.



5. Заминуваме од областа на табелата надесно и ја пополнуваме колоната со броеви по ред, почнувајќи од 1. Бројот на броеви мора да одговара на бројот на редови во втората табела што треба да се споредат. За да го забрзате процесот на нумерирање, можете да го користите маркерот за пополнување.



6. После тоа, изберете ја првата ќелија десно од колоната со броеви и кликнете на иконата "Вметни функција".



7. Се отвора Волшебник на карактеристики. Одете во категоријата „Статистика“ и направи избор на името „ЛИСТ“. Кликнете на копчето „ОК“.



8. Функција ЛИСТчиј прозорец за аргументи е отворен, има за цел да ја прикаже најмалата вредност наведена на сметката.

   На полето Низа наведете ги координатите на опсегот на дополнителната колона "Број на натпревари"што претходно ги конвертиравме со помош на функцијата Ако. Сите врски ги правиме апсолутни.

   На полето „К“ означува која сметка треба да ја прикаже најниската вредност. Овде ги наведуваме координатите на првата ќелија на колоната со нумерирање, што неодамна ги додадовме. Ја оставаме адресата релативна. Кликнете на копчето „ОК“.



9. Операторот го прикажува резултатот - број 3. Тоа е најмалото од нумерирањето на неусогласените редови на низите на табели. Користејќи го маркерот за полнење, копирајте ја формулата до дното.



10. Сега, знаејќи ги линиските броеви на несогласените елементи, можеме да ги вметнеме во ќелијата нивните вредности со помош на функцијата Индекс. Изберете го првиот елемент на листот што ја содржи формулата ЛИСТ. После тоа, одете до редот на формулите и пред името „ЛИСТ“ додадете го името Индекс без цитати, веднаш отворете ја заградата и ставете записник (;) Потоа изберете го името во редот на формулите Индекс и кликнете на иконата "Вметни функција".



11. После тоа, се отвора мал прозорец во кој треба да го одредите референтниот преглед треба да има функција Индекс или дизајнирани да работат со низи. Ни треба втората опција. Инсталирано е инсталирано, така што во овој прозорец само кликнете на копчето „ОК“.



12. Започнува прозорецот за аргументи за функција Индекс. Овој оператор е наменет за производство на вредност што се наоѓа во одредена низа во наведената низа.

    Како што можете да видите, полето Реден број веќе исполнети со вредности на функцијата ЛИСТ. Од вредноста што веќе постои таму, треба да се одземе разликата помеѓу нумерирањето на листот Excel и внатрешното броење на табелата. Како што можете да видите, имаме само заглавие над вредностите на табелата. Ова значи дека разликата е една линија. Затоа, додаваме во полето Реден број вредност "-1" без понуди

    На полето Низа наведете ја адресата на опсегот на вредности на втората табела. Во исто време, сите координати ги правиме апсолутни, односно пред нив го ставаме знакот на доларот на начинот на кој претходно го опишавме.

    Кликнете на копчето „ОК“.



13. Откако ќе го прикажеме резултатот на екранот, ја прошируваме функцијата со помош на маркерот за полнење до дното на колоната. Како што можете да видите, и двете презимиња кои се присутни во втората табела, но не се во првата, се прикажани во посебен опсег.

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

Кога споредувате опсези во различни книги, можете да ги користите горенаведените методи, освен оние опции каде што сакате да ги поставите обете области на табелата на еден лист. Главниот услов за постапката за споредба во овој случај е истовремено да ги отворите прозорците на обете датотеки. За верзиите на Excel 2013 и понова верзија, како и за верзиите пред Excel 2007, нема проблеми со оваа состојба. Но, во Excel 2007 и Excel 2010, за да се отворат двата прозорци во исто време, потребни се дополнителни манипулации. Како да го направите ова, е опишано во посебна лекција.

Лекција: Како да го отворите Excel во различни прозорци

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