СПОРЕДБА НА ТАБЕЛАРНИ ПРЕСМЕТКИ НА EXCEL

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

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

Методи за споредба

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

споредба на листи кои се наоѓаат на истиот лист;

споредба на табели лоцирани на различни листови;

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

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

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

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

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

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

За ова ни е потребна дополнителна колона на листот. Внесете го знакот таму "=". Потоа кликнете на првата ставка што ќе се спореди со првата листа. Повторно го ставаме симболот "=" од тастатурата. Потоа кликнете на првата ќелија на колоната, која ја споредуваме, во втората табела. Изразот е од следниот тип:
= А2 = Д2
Иако, се разбира, во секој случај координатите ќе бидат различни, но суштината ќе остане иста.
Кликнете на копчето Внесетеза да добиете споредбени резултати. Како што можете да видите, кога ги споредувате првите ќелии на двете списоци, програмата покажа индикатор "ИСТИНА"што значи податоци спојување.

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

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

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

Во прозорецот Функција мајстори во група на оператори "Математичка" одберете го името SUMPRODUCT. Кликнете на копчето "Добро".

Прозорецот за аргументите за функции е активиран. SUMPRODUCTчија главна задача е да се пресмета збирот на производите од избраниот опсег. Но, оваа функција може да се користи за нашите цели. Неговата синтакса е прилично едноставна:
= SUMPRODUCT (array1; array2; ...)
Севкупно, можете да користите адреси до 255 низи како аргументи. Но, во нашиот случај ние ќе користиме само две низи, и покрај тоа, како еден аргумент.
Ставете го курсорот во полето "Масив1" и одберете го споредбениот опсег на податоци во првата област на листот. Потоа ставивме белег во полето. "не е еднакво" () и изберете го споредбениот опсег на вториот регион. Потоа, завртете го добиениот израз со загради, пред да ставиме два знака "-". Во нашиот случај, го добиваме следниов израз:
- (A2: A7D2: D7)
Кликнете на копчето "Добро".

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

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

= B2 = Лист2! Б2

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

Метод 2: Изберете Groups of Cells

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

Изберете ги споредените низи. Одете на јазичето "Дома". Следно, кликнете на иконата "Најдете и нагласи"кој се наоѓа на лентата во блокот на алатки Уредување. Отвора листа во која треба да изберете позиција. "Избирање група на клетки ...".
Покрај тоа, во саканиот прозорец за избор на група на клетки може да се пристапи на друг начин. Оваа опција ќе биде особено корисна за оние корисници кои ја инсталирале верзијата на програмата порано од Excel 2007, бидејќи методот преку копчето "Најдете и нагласи" Овие апликации не поддржуваат. Изберете ги низите што сакаме да ги споредуваме, и притиснете го копчето F5.
Се активира мал транзициски прозорец. Кликнете на копчето "Нагласи ..." во долниот лев агол.

После тоа, без оглед на она што го избравте од претходните две опции, прозорецот за избирање на групи на клетки се стартува. Поставете го прекинувачот во положба "Изберете по ред". Кликнете на копчето "Добро".

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

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

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

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

Прозорецот за управување со правила е активиран. Ние притиснуваме на копчето "Креирај правило".

Во прозорецот за стартување, направете избор на позиција "Користете формула". Во полето "Форматирај клетки" напишете ја формулата која ги содржи адресите на првите клетки од опсегот на споредени колони, разделени со знакот "не еднаков" (). Само овој израз ќе има знак овој пат. "=". Покрај тоа, апсолутно адресирање треба да се примени на сите колонски координати во оваа формула. За да го направите ова, изберете ја формулата со курсорот и кликнете три пати на копчето F4. Како што можете да видите, знакот за долар се појавил во близина на сите адреси на колоните, што значи вртење врски во апсолутни. За нашиот конкретен случај, формулата ќе ја има следната форма:
= $ A2 $ D2
Го пишуваме овој израз во горното поле. Потоа кликнете на копчето "Формат ...".

Активиран прозорец "Форматирај клетки". Одете на јазичето "Пополнете". Овде во листата на бои го спречуваме изборот на бојата со која сакаме да ги обоиме оние елементи каде податоците нема да се совпаѓаат. Ние го притискаме копчето "Добро".

Враќање во прозорецот за создавање правило за форматирање, кликнете на копчето. "Добро".

По автоматски се движи кон прозорецот Менаџер на правила кликнете на копчето "Добро" и во него.

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

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

Направете избор на области кои треба да се споредат.

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

Почнува прозорецот за поставување на избор на двојни вредности. Ако сте направиле се што е правилно, тогаш во овој прозорец останува само да кликнете на копчето. "Добро". Иако, ако сакате, можете да изберете друга боја на селекција во соодветното поле на овој прозорец.

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

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

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

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

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

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

Оператор COUNTES се однесува на статистичка група на функции. Неговата задача е да го брои бројот на клетки чии вредности задоволуваат одредена состојба. Синтаксата на овој оператор е како што следува:

= COUNTERS (опсег, критериум)

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

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

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

Стартува се случува Функција мајстори. Оди во категоријата "Статистички". Најдете во списокот името "COUNTES". Откако ќе го изберете, кликнете на копчето. "Добро".

Прозорецот за аргумент на операторот е лансиран COUNTES. Како што можете да видите, имињата на полињата во овој прозорец соодветствуваат со имињата на аргументите.
Поставете го курсорот во полето "Опсег". После тоа, држете го левото копче на глувчето, одберете ги сите вредности на колоната со имињата на втората табела. Како што можете да видите, координатите веднаш паѓаат во наведеното поле. Но, за нашите цели, оваа адреса треба да биде апсолутна. За да го направите ова, одберете ги координатите во полето и кликнете на копчето F4.
Како што можете да видите, врската зеде апсолутна форма, која се карактеризира со присуство на долар знаци.
Потоа оди на терен "Критериум"со поставување на курсорот таму. Ние кликнуваме на првиот елемент со презимиња во првиот табеларен опсег. Во овој случај, оставете ја релативната врска. Откако ќе се прикаже во полето, можете да кликнете на копчето "Добро".

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

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

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

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

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

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

Се отвора прозорецот за аргумент на функцијата. АКО. Како што можете да видите, првото поле на прозорецот е веќе пополнето со вредноста на операторот. COUNTES. Но, ние треба да додадеме нешто друго во ова поле. Го поставивме курсорот и додадовме на веќе постоечкиот израз "=0" без наводници.
Потоа оди на терен "Вредност ако е точно". Тука ќе користиме друга вгнездена функција - ЛИНИЈА. Внесете го зборот "ЛИНИЈА" без наводници, потоа отворете ги заградите и наведете ги координатите на првата ќелија со презиме во втората табела, а потоа затворете ги заградите. Поточно, во нашиот случај на терен "Вредност ако е точно" го добив следниов израз:
ЛИНИЈА (D2)
Сега операторот ЛИНИЈА ќе известуваат за функциите АКО бројот на линијата во која се наоѓа специфичното презиме, а во случај кога е исполнето условот определен во првото поле, функцијата АКО ќе го изведе овој број во ќелијата. Ние го притискаме копчето "Добро".

Како што можете да видите, првиот резултат е прикажан како "FALSE". Ова значи дека вредноста не ги задоволува условите на операторот. АКО. Тоа е, првото презиме е присутно во двете списоци.

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

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

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

Отвора Функционален волшебник. Оди во категоријата "Статистички" и да направат избор на имиња "ИМЕТО". Кликнете на копчето "Добро".

Функција Најлошото, чиј прозорец на аргументите е отворен, е дизајниран да прикажува најниска вредност одредена од сметката.
Во полето "Array" наведете ги координатите на опсегот на дополнителната колона "Број на натпревари"кои претходно ги конвертиравме со користење на функцијата АКО. Сите врски ги правиме апсолутно.
Во полето "К" означете која сметка треба да биде прикажана најниската вредност. Тука ги посочуваме координатите на првата ќелија на колоната со нумерација, која неодамна ја додадовме. Адресата е оставена во однос. Кликнете на копчето "Добро".

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

Сега, знаејќи ги броевите на линиите на елементите кои не се совпаѓаат, можеме да ја вметнеме во ќелијата и нивните вредности користејќи ја функцијата Индекс. Изберете го првиот елемент од листот кој ја содржи формулата Најлошото. Потоа одете на формула и пред името "ИМЕТО" додајте име Индекс без наводници, веднаш отворете го држачот и ставете точка-запирка (;). Потоа одберете го името во формуларот. Индекс и кликнете на иконата "Вметни ја функцијата".

Потоа се отвора мал прозорец во кој треба да се утврди дали референцата треба да има функција Индекс или дизајнирани да работат со низи. Потребна е втора опција. Поставен е стандардно, така што во овој прозорец едноставно кликнете на копчето. "Добро".

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

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

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

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

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

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