Metode za primerjavo tabel v programu Microsoft Excel

Pogosto uporabniki Excela imajo nalogo primerjanja dveh tabel ali seznamov, da bi prepoznali razlike ali manjkajoče elemente v njih. Vsak uporabnik se s to nalogo spoprijema na svoj način, vendar pogosteje porabi precej časa za reševanje tega problema, ker niso vsi pristopi k temu problemu racionalni. Ob istem času obstaja več validiranih algoritmov delovanja, ki vam omogočajo primerjavo seznamov ali nizov tabel v relativno kratkem času z minimalnimi napori. Poglejmo si te možnosti podrobneje.

Preberite tudi: Primerjava dveh dokumentov v MS Wordu

Metode primerjave

Obstaja nekaj načinov za primerjavo področij tabele v Excelu, vendar jih lahko razdelimo na tri velike skupine:

Na podlagi te razvrstitve se najprej izbere metode primerjave, določijo se tudi posebni ukrepi in algoritmi za opravljanje naloge. Če na primer primerjate različne knjige, morate istočasno odpreti dve datoteki Excel.

Poleg tega je treba reči, da je primerjanje tabel polj smiselno le, če imajo podobno strukturo.

Metoda 1: Preprosta formula

Najenostavnejši način za primerjavo podatkov v dveh tabelah je uporaba preproste formule enakosti. Če podatki sovpadajo, potem poda TRUE indikator, in če ne, potem - FALSE. Primerjate lahko tudi numerične podatke in besedilo. Pomanjkljivost te metode je, da jo je mogoče uporabiti le, če so podatki v tabeli naročeni ali so razvrščeni identično, sinhronizirani in imajo enako število vrstic. Poglejmo, kako v tej praksi uporabiti to metodo z dvema tabelama na enem listu.

Torej imamo dve preprosti tabeli s seznami zaposlenih v podjetju in njihove plače. Treba je primerjati sezname zaposlenih in ugotoviti neskladnosti med stolpci, v katerih so imena nameščena.

1. Za to potrebujemo dodaten stolpec na listu. Tukaj vnesemo znak "=" . Nato kliknite na prvo ime, ki ga želite primerjati na prvem seznamu. Znova postavite simbol »=« s tipkovnice. Nato kliknite prvo celico stolpca, ki jo primerjamo v drugi tabeli. Dobljen je bil izraz naslednje vrste:

   =A2=D2

   Čeprav bodo v vsakem konkretnem primeru koordinate drugače, vendar bo bistvo ostalo enako.

2. Kliknemo na tipko Enter, da dobimo rezultate primerjave. Kot lahko vidite, je pri primerjavi prvih celic obeh seznamov program pokazal indikator "TRUE" , kar pomeni, da se podatki ujemajo.
3. Zdaj moramo opraviti podobno operacijo s preostalimi celicami obeh tabel v stolpcih, ki jih primerjamo. Ampak lahko kopirate formulo, ki bo prihranila veliko časa. Še posebej je ta dejavnik pomemben pri primerjavi seznamov z velikim številom vrstic.

   Najlažji način za kopiranje je uporaba polnila. Kazalec premaknemo v spodnji desni kot celice, kjer smo dobili "TRUE" indikator. V tem primeru ga je treba pretvoriti v črni križ. To je oznaka za polnjenje. Kliknite levi gumb miške in povlecite kazalec navzdol števila vrstic v primerjalnih tabelah.

4. Kot lahko vidite, so zdaj v dodatnem stolpcu prikazani vsi rezultati primerjave podatkov v dveh stolpcih tabele. V našem primeru se podatki niso ujemali samo z eno vrstico. Pri primerjavi je formula ustvarila rezultat "LIE" . Za vse druge vrstice, kot smo videli, je primerjalno formulo izdala indikator "TRUE" .
5. Poleg tega je mogoče izračunati število neusklajenosti s pomočjo posebne formule. Če želite to narediti, izberite element lista, na katerem bo prikazan. Nato kliknite ikono »Vstavi funkcijo« .
6. V oknu Čarovnik funkcij pod skupino operaterjev »Matematična« izberite ime SUMPRODUCT . Kliknemo na gumb "OK" .
7. Vključeno je okno argumentov funkcije SUMPRODUCT , katerega glavna naloga je izračunati vsoto izdelkov izbranega območja. Toda ta funkcija se lahko uporabi za naše namene. Sintaksa je precej preprosta:

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

   Kot argument lahko uporabite do 255 nizov. Toda v našem primeru bomo uporabili le dve nizi, poleg tega pa kot en argument.

   Postavili smo kazalko v polje "Array1" in na listu izberemo primerjalno podatkovno območje na prvem področju. Nato v polje postavite znak "ni enak" ( <> ) in izberite primerjalni obseg drugega območja. Nato vnesite nastali izraz z oklepaji, pred katerimi postavimo dva znaka »-« . V našem primeru je bil izraz:

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

   Kliknemo na gumb "OK" .

8. Operater izračuna in prikaže rezultat. Kot lahko vidite, je rezultat v našem primeru enak "1" , to pomeni, da je bila na primerjanih seznamih ugotovljena neusklajenost. Če bi bili seznami povsem enaki, bi bil rezultat enak številu »0« .

Na enak način lahko primerjate podatke v tabelah, ki so na različnih listih. Toda v tem primeru je zaželeno, da so vrstice v njih oštevilčene. V nasprotnem primeru je postopek primerjave skoraj popolnoma enak, kot je opisano zgoraj, razen dejstva, da morate med vnašanjem formule preklopiti med listi. V našem primeru bo izraz videti tako:

=B2=Лист2!B2

To pomeni, kot vidite, pred koordinatami podatkov, ki se nahajajo na drugih listih in se razlikujejo od tistih, kjer je prikazan rezultat primerjave, so prikazane številke liste in klicaj.

2. način: Izbira skupin celic

Primerjavo lahko uporabite z orodjem za izbiro skupine celic. Z njim lahko primerjate tudi samo sinhronizirane in naročene sezname. Poleg tega morajo biti v tem primeru seznami postavljeni drug ob drugem na enem listu.

1. Izberemo primerjane nizove. Pojdite na kartico »Domov «. Nato kliknite ikono »Najdi in izberite « , ki se nahaja na traku v polju »Uredi« . Odpre se seznam, v katerem izberete postavko »Izbira skupine celic ...« .

   Poleg tega lahko dostopate tudi do izbirnega okna za skupino celic na drug način. Ta možnost bo še posebej uporabna za tiste uporabnike, ki imajo različico programa prej kot Excel 2007, ker te metode ne podpirajo z gumbom »Najdi in označite« . Izberite polja, ki jih želimo primerjati, in pritisnite tipko F5 .

2. Aktivirano je majhno prehodno okno. V spodnjem levem kotu kliknite gumb »Izberi ...« .
3. Po tem, kar koli od dveh možnosti, ki jih izberete, se zažene okno za izbiro skupin celic. Stikalo nastavite na položaj »Izberi po vrsticah« . Kliknite gumb "V redu" .
4. Kot lahko vidite, potem bodo neusklajene vrednosti vrstic označene z drugačnim odtenkom. Poleg tega, ker lahko sodimo iz vsebine linije s formulo, bo program aktiviral eno od celic, ki so aktivne v navedenih vrsticah, ki niso v skladu z njimi.

3. način: pogojno oblikovanje

Primerjavo lahko naredite z uporabo pogojnega načina oblikovanja. Kot pri prejšnji metodi morajo biti primerjalna področja na istem delovnem listu Excela in sinhronizirana med seboj.

1. Najprej se odločimo, katero tabelo bomo obravnavali kot glavno, in kako iskati razlike. To naredimo v drugi mizi. Zato izberemo seznam zaposlenih, ki so v njem. Ko se premaknete na kartico »Domov «, kliknemo gumb »Pogojno oblikovanje« , ki ima lokacijo na traku v bloku »Slogi« . Na spustnem seznamu pojdite na »Upravljanje pravil«.
2. Okno upravitelja pravil je aktivirano. V njem kliknite gumb »Ustvari pravilo« .
3. V oknu, ki se odpre, izdelamo izbiro elementa »Uporabi formulo« . V polju »Oblikovanje celic« napišemo formulo, ki vsebuje naslove prvih celic razpona primerjanih stolpcev, ločenih s znakom »ni enako« ( <> ). Šele pred tem izrazom bo tokrat znak "=" . Poleg tega je treba za vse koordinate stolpcev v tej formuli uporabiti absolutno naslavljanje. To naredite tako, da s kazalcem izberete formulo in trikrat pritisnite F4 . Kot vidite, so bili vsi naslovi stolpcev označeni z znakom dolarja, kar pomeni, da so povezave v absolutne. Za naš specifičen primer formula ima naslednjo obliko:

   =$A2<>$D2

   Ta izraz pišemo v zgornjem polju. Po tem kliknite gumb »Format ...« .

4. Vključeno je okno »Oblikovane celice« . Pojdite na kartico »Izpolnite «. Tukaj na seznamu barv ustavimo izbiro na barvi, ki jo želimo obarvati tistih elementov, kjer se podatki ne bodo ujemali. Kliknite gumb "V redu" .
5. Če se vrnete v okno za ustvarjanje pravila za oblikovanje, kliknite gumb »V redu« .
6. Ko se samodejno premaknete v okno "Upravitelj pravil", kliknite gumb "V redu" in v njem.
7. Zdaj v drugi tabeli elementi, ki imajo podatke, ki se ne ujemajo z ustreznimi vrednostmi prvega območja tabele, bodo označene z izbrano barvo.

Obstaja tudi drug način, kako uporabiti pogojno oblikovanje naloge. Tako kot prejšnje različice zahteva tudi lokacijo obeh primerjanih področij na enem listu, vendar za razliko od prej opisanih metod, stanje sinhronizacije ali razvrščanja podatkov ne bo obvezno, kar ločuje to možnost iz predhodno opisanih.

1. Naredimo izbor področij, ki jih je treba primerjati.
2. Premaknemo se na zavihek "Dom" . Kliknemo na gumb "Pogojno oblikovanje" . Na aktiviranem seznamu izberite element »Pravila za izbiro celic« . V naslednjem meniju izbira položaja »Podvojene vrednosti« .
3. Začne se okno za nastavitev izbire podvojenih vrednosti. Če ste storili vse pravilno, potem v tem oknu ostane le kliknite na gumb »V redu« . Čeprav lahko po želji izberete drugo barvo osvetlitve v ustreznem polju tega okna.
4. Ko naredimo določeno dejanje, bodo vsi ponavljajoči se elementi označeni z izbrano barvo. Tisti elementi, ki se ne ujemajo, bodo ostali zatemnjeni v njihovi prvotni barvi (privzeta vrednost je bela). Tako lahko vizualno takoj vidite, kakšna je razlika med nizi.

Če želite, lahko nasprotno barve neusklajenih elementov in tiste indikatorje, ki se ujemajo, pustite z enako barvo. V tem primeru je algoritem akcij skoraj enak, v oknu z nastavitvami pa za izbiro podvojenih vrednosti v prvem polju, namesto parametra "Duplicate" , izberite možnost "Unique" . Nato kliknite gumb "OK" .

Tako bodo poudarjeni kazalniki, ki ne sovpadajo.

Lekcija: Pogojno oblikovanje v Excelu

Metoda 4: Kompleksna formula

Poleg tega lahko podatke primerjate s kompleksno formulo, ki temelji na funkciji COUNTIF . S pomočjo tega orodja lahko računate, koliko se vsak element iz izbranega stolpca druge tabele ponovi v prvem.

Operater DRŽAVA se sklicuje na statistično skupino funkcij. Njegova naloga je šteti število celic, katerih vrednosti ustrezajo določenemu pogoju. Sintaksa tega operaterja je:

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

Argument "Range" je naslov matrike, v katerem se izračunajo ujemajoče vrednosti.

Kriterij argumenta določa pogoj ujemanja. V našem primeru bo predstavljal koordinate specifičnih celic prvega območja tabele.

1. Izberite prvi element dodatnega stolpca, v katerem se šteje število zadetkov. Nato kliknite ikono »Vstavi funkcijo« .
2. Začne se čarovnik za funkcije . Pojdimo v kategorijo "Statistični podatki" . Na seznamu najdemo ime »SVET« . Ko jo izberete, kliknite na gumb »V redu« .
3. Začelo se bo okno argumentov operaterja Sveta . Kot vidite, imena polj v tem oknu ustrezajo imenom argumentov.

   Nastavite kazalko v polju "Range" . Po tem, držite levi gumb miške, izberite vse vrednosti stolpca z imeni druge tabele. Kot lahko vidite, koordinate takoj spadajo v navedeno polje. Toda za naše namene bi moral biti ta naslov absoluten. Če želite to narediti, izberite koordinate na polju in pritisnite F4 .

   Kot lahko vidite, je povezava prevzela absolutno obliko, za katero je značilna prisotnost dolarskih znakov.

   Nato pojdite na polje »Kriterij« in nastavite kazalec. Kliknemo na prvi element z imeni v prvem obsegu tabele. V tem primeru pustimo referenčni relativ. Ko je v polju prikazan, lahko kliknete gumb »V redu« .

4. Rezultat je prikazan v elementu lista. To je enako številu "1" . To pomeni, da se na seznamu imen iz druge tabele pojavlja priimek "Grinev VP" , ki je prvi na seznamu prve razpredelnice matrike.
5. Zdaj moramo ustvariti podoben izraz za vse druge elemente prve tabele. Če želite to narediti, opravite kopiranje z uporabo označevalnika polnila, kot smo že storili. Postavite kazalko v spodnji desni del elementa lista, ki vsebuje funkcijo COUNTIF in jo nato pretvorite v oznako za polnjenje, z levo miškino tipko obrnemo kurzor in ga povlecimo navzdol.
6. Kot lahko vidite, je program izračunal naključnosti s primerjavo vsake celice prve tabele s podatki, ki se nahajajo v drugem obsegu tabele. V štirih primerih je bil rezultat "1" , v dveh primerih pa "0" . To pomeni, da program v drugi tabeli ni mogel najti dveh vrednosti, ki so na voljo v prvi tabeli tabele.

Seveda, ta izraz za primerjavo tabelarnih kazalnikov, lahko uporabite v obstoječi obliki, vendar obstaja možnost, da jo izboljšate.

Vrednosti, ki so na voljo v drugi tabeli, bomo naredili, vendar ne v prvi tabeli, prikazati na posebnem seznamu.

1. Najprej bomo preoblikovali našo formulo za državo , malo , naredimo jo kot enega od argumentov operaterja IF . Če želite to narediti, izberite prvo celico, v kateri se nahaja operator COUNTRY . V vrstici formul pred njo dodamo izraz "IF" brez narekov in odprite nosilec. Nadalje, da bi nam olajšali delo, izberemo vrednost »IF« v vrstici formule in kliknite ikono »Vstavi funkcijo« .
2. Odpre se okno argumentov funkcije IF . Kot vidite, je prvo polje okna že zapolnjeno z vrednostjo operaterja Sveta . Toda na tem področju moramo končati nekaj drugega. Tam smo nastavili kazalko in dodali »0« brez citatov že obstoječemu izrazu.

   Po tem pojdite na polje "Vrednost, če je resnično . " Tukaj bomo uporabili še eno ugnezdeno funkcijo - STRING . Vnesemo besedo "LINE" brez narekov, nato pa odprite oklepaje in v drugi tabeli določite koordinate prve celice s priimkom in nato zaprite oklepaje. Natančneje, v našem primeru v polju "Vrednost, če je resnično" dobljen naslednji izraz:

   СТРОКА(D2)

   Zdaj bo izjava LINE sporočila funkciji, če je številka vrstice, v kateri je določeno priimek, in če je izpolnjen pogoj, določen v prvem polju, bo funkcija IF izdala to številko v celico. Kliknite gumb "V redu" .

3. Kot vidite, se prvi rezultat prikaže kot »FALSE« . To pomeni, da vrednost ne izpolnjuje pogojev operaterja IF . To pomeni, da je prvi priimek na obeh seznamih.
4. Z uporabo označevalnika polnjenja na običajen način kopiramo izraz operatorja IF v celoten stolpec. Kot lahko vidite, za dve elementi, ki sta prisotni v drugi tabeli, vendar ne v prvem, formula daje številke vrstic.
5. Odpravimo se nazaj iz mize na desno in polnimo stolpec s številkami, ki se začnejo z 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. Odpre se čarovnik za funkcije . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Kliknemo na gumb "OK" .
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

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

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

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

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

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

    Kliknite gumb "V redu" .

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

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

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

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

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

• ← SQL poizvedbe v programu Microsoft Excel
• Delo s povezanimi tabelami v programu Microsoft Excel →