Metode za uspoređivanje tablica u Microsoft Excelu

Vrlo često korisnici Excela imaju zadatak uspoređivati ​​dvije tablice ili popise da bi identificirali razlike ili elemente koji ih nedostaju u njima. Svaki korisnik rješava ovaj zadatak na svoj način, ali češće troši mnogo vremena na rješavanje ovog problema, jer nisu svi pristuci ovom problemu racionalni. Istodobno, postoji nekoliko validiranih algoritama za akciju koji će vam omogućiti usporedbu popisa ili tabličnih polja u relativno kratkom vremenu uz minimalan napor. Pogledajmo bliže ove opcije.

Pročitajte također: Usporedba dvaju dokumenata u MS Wordu

Metode usporedbe

Postoji nekoliko načina za usporedbu tabličnih područja u Excelu, ali svi se mogu podijeliti u tri velike skupine:

Na temelju ove klasifikacije, prije svega se odabiru metode usporedbe, a određuju se i specifične akcije i algoritmi za obavljanje zadatka. Na primjer, kada se uspoređujete u različitim knjigama, istovremeno morate otvoriti dvije Excel datoteke.

Nadalje, valja reći da usporedba stolnih polja ima smisla samo kada imaju sličnu strukturu.

Metoda 1: Jednostavna formula

Najjednostavniji način usporedbe podataka u dvije tablice je korištenje jednostavne formule jednakosti. Ako se podaci podudaraju, onda ona daje TRUE indikator, a ako ne, onda - FALSE. Možete usporediti i numeričke podatke i tekst. Nedostatak ove metode je da se može koristiti samo ako su podaci u tablici poredani ili sortirani identično, sinkronizirani i imaju jednak broj linija. Pogledajmo kako se ova metoda upotrebljava u praksi na primjeru dvije tablice postavljene na jedan list.

Dakle, imamo dvije jednostavne tablice s popisima zaposlenika poduzeća i njihove plaće. Potrebno je usporediti popise zaposlenika i identificirati nedosljednosti između stupaca u kojima se imena nalaze.

1. Za to nam je potreban dodatni stupac na listu. Ovdje unosimo znak "=" . Zatim kliknite na ime koje želite usporediti na prvom popisu. Opet stavite simbol "=" s tipkovnice. Zatim kliknite prvu ćeliju stupca koju usporedimo u drugoj tablici. Dobivena je ekspresija sljedeće vrste:

   =A2=D2

   Iako, naravno, u svakom pojedinom slučaju, koordinate će biti različite, ali suština će ostati ista.

2. Klikom na tipku Enter dobijemo rezultate usporedbe. Kao što možete vidjeti, kada uspoređujete prve ćelije obaju popisa, program je označio pokazatelj "TRUE" , što znači da se podaci podudaraju.
3. Sada moramo izvršiti sličnu operaciju s preostalim ćelijama obaju tablica u stupcima koje usporedimo. Ali možete jednostavno kopirati formulu koja će uštedjeti puno vremena. Posebno ovaj faktor je važan kada uspoređujete popise s velikim brojem redaka.

   Najlakši način za postizanje kopiranja je korištenje ručke za punjenje. Pokazujemo pokazivač u donji desni kut ćelije, gdje smo dobili indikator "TRUE" . U tom slučaju mora se pretvoriti u crni križ. Ovo je oznaka za popunjavanje. Pritisnite lijevu tipku miša i povucite pokazivač dolje broj redaka u tablicama usporedbi tablice.

4. Kao što možete vidjeti, sada u dodatnom stupcu su prikazani svi rezultati usporedbe podataka u dva stupca tabličnih polja. U našem slučaju podaci nisu odgovarali samo jednoj liniji. Kada ih uspoređujete, formula je rezultirala rezultatom "LIE" . Za sve ostale linije, kao što vidimo, formula za usporedbu je izdala indikator "TRUE" .
5. Osim toga, moguće je izračunati broj neusklađenosti pomoću posebne formule. Da biste to učinili, odaberite element listova na kojem će se izlaziti. Zatim kliknite ikonu "Umetni funkciju" .
6. U prozoru Čarobnjak za funkcije , pod "Matematička" skupina operatora, odaberite naziv SUMPRODUCT . Kliknemo na gumb "OK" .
7. Aktiviran je argumentni prozor funkcije SUMPRODUCT , čiji je glavni zadatak izračunati zbroj proizvoda odabranog raspona. No, ova se funkcija može koristiti za naše potrebe. Sintaksa je vrlo jednostavna:

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

   Ukupno se može upotrijebiti do 255 polja kao argumente. Ali u našem slučaju koristit ćemo samo dva polja, kao jedan argument.

   Stavili smo kursor u polje "Array1" i odaberite na listu usporedbeni raspon podataka u prvom području. Nakon toga stavite znak "nejednak" ( <> ) u polje i odaberite opseg usporedbe drugog područja. Zatim zamotajte rezultirajući izraz s zagradama, prije čega stavljamo dva znaka "-" . U našem slučaju izraz je bio:

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

   Kliknemo na gumb "OK" .

8. Operater izračunava i prikazuje rezultat. Kao što vidite, u našem slučaju rezultat je jednak broju "1" , to znači da je u popisu uspoređenih pronađeno jedno neusklađenost. Ako su popisi bili potpuno identični, rezultat bi bio jednak broju "0" .

Na isti način možete usporediti podatke u tablicama koje se nalaze na različitim listovima. No, u ovom je slučaju poželjno da se redovi u njima numeriraju. Inače, postupak usporedbe je gotovo isti kao što je gore opisano, osim činjenice da kada unesete formulu morate se prebacivati ​​između listova. U našem slučaju izraz će izgledati ovako:

=B2=Лист2!B2

To jest, kao što možete vidjeti, prije nego što se podaci koordinate, koji se nalaze na drugim listovima, različitima od mjesta na kojima je prikazan rezultat usporedbe, označen je broj lista i uskličnik.

Metoda 2: Odabir grupa ćelija

Pomoću alata za odabir stanične skupine možete usporediti. Uz to, možete usporediti samo sinkronizirane i naručene popise. Osim toga, u ovom slučaju, popisi bi se trebali nalaziti jedno uz drugo na jednom listu.

1. Odabrali smo usporedne polja. Idite na karticu "Početna" . Zatim kliknite ikonu "Pronađi i odaberite " , koja se nalazi na vrpci u okviru alata "Uredi" . Otvorit će se popis u kojem odabirete stavku "Odabir grupe ćelija ..." .

   Uz to, na drugi način možete pristupiti prozoru za odabir grupe ćelija. Ova opcija bit će posebno korisna za one korisnike koji imaju verziju programa ranije od programa Excel 2007, jer ove metode ne podržavaju gumb "Pronađi i istaknite" . Odaberite polja koje želimo usporediti i pritisnite tipku F5 .

2. Aktiviran je mali prijelazni prozor. Klikom na gumb "Odaberi ..." u donjem lijevom kutu.
3. Nakon toga, ovisno o dvije gore navedene opcije koju odaberete, počinje prozor za odabir grupa ćelija. Postavite prekidač na položaj "Odaberi po redcima" . Kliknite gumb "OK" .
4. Kao što možete vidjeti, nakon toga će neusklađene vrijednosti linija biti označene različitim nijansama. Osim toga, kao što se može suditi od sadržaja linije formule, program će napraviti jednu od ćelija aktivnih u naznačenim neusklađenim redovima aktivnim.

3. metoda: uvjetno oblikovanje

Možete usporediti primjenom metode uvjetnog oblikovanja. Kao u prethodnoj metodi, usporedna područja moraju biti na istom Excelovom radnom listu i sinkronizirati jedna s drugom.

1. Prije svega, odlučili smo koji stolni prostor uzeti u obzir glavni i kako tražiti razlike. Napravimo drugu tablicu. Stoga odabiremo popis zaposlenika koji su u njemu. Nakon prelaska na karticu "Početna" kliknite na gumb "Uvjetno oblikovanje" , koji ima mjesto na vrpci u bloku "Stilovi" . S padajućeg popisa idite na "Upravljanje pravilima".
2. Aktiviran je prozor upravitelja pravilima. Kliknite gumb "Napravi pravilo" u njemu.
3. U prozoru koji se otvori odabiremo stavku "Koristi formulu" . U polju "Oblikovanje ćelija" zapisujemo formulu koja sadrži adrese prvih ćelija raspona uspoređenih stupaca, odijeljen znakom "nije jednak" ( <> ). Samo prije ovog izraza ovaj put će biti znak "=" . Osim toga, na sve koordinate stupca u ovoj formuli morate primijeniti apsolutno adresiranje. Da biste to učinili, odaberite formulu kursorom i pritisnite F4 tri puta. Kao što vidite, oko svih adresa stupca bilo je znak dolara, što znači pretvoriti veze u apsolutne. Za naš konkretni slučaj, formula ima sljedeći oblik:

   =$A2<>$D2

   Ovaj izraz pišemo u gore navedenom polju. Nakon toga kliknite gumb "Format ..." .

4. Aktiviran je prozor "Format Cells" . Idite na karticu "Ispunite" . Ovdje na popisu boja zaustavljamo izbor boja, koji želimo slikati one elemente u kojima se podaci ne podudaraju. Kliknite gumb "OK" .
5. Vratite se na prozor za izradu pravila za oblikovanje, kliknite na gumb "OK" .
6. Nakon što se automatski prebacite u prozor "Upravitelj pravila", kliknite gumb "OK" iu njemu.
7. Sada u drugoj tablici, elementi koji imaju podatke koji ne odgovaraju odgovarajućim vrijednostima prvog područja tablice bit će označeni odabranom bojom.

Postoji još jedan način primjene uvjetnog oblikovanja na zadatak. Kao i prethodne verzije, ona zahtijeva mjesto oba usporedena područja na jednom listu, ali za razliku od prethodno opisanih metoda, stanje usklađivanja ili razvrstavanja podataka neće biti obavezno, što razlikuje ovu mogućnost od prethodno opisanih.

1. Izbor smo područja koja treba usporediti.
2. Prebacimo se na karticu pod nazivom "Dom" . Kliknemo na gumb "Uvjetno oblikovanje" . Na aktiviranom popisu odaberite stavku "Pravila odabira ćelija" . U sljedećem izborniku odabiremo položaj "Duplicirane vrijednosti" .
3. Pokreće se prozor za podešavanje odabranih dvostrukih vrijednosti. Ako ste sve ispravno radili, onda u ovom prozoru ostaje samo kliknuti na gumb "OK" . Iako, ako želite, možete odabrati drugu boju za označavanje u odgovarajućem polju ovog prozora.
4. Nakon što izvršimo navedenu radnju, svi ponavljajući elementi bit će označeni odabranom bojom. Elementi koji se ne podudaraju ostat će zasićeni u izvornoj boji (zadana je bijela). Dakle, odmah možete vizualno vidjeti koja je razlika između polja.

Ako želite, naprotiv, možete slikati neusklađene elemente, a oni indikatori koji se podudaraju, ostavljaju s istom bojom. U tom je slučaju algoritam radnji gotovo isti, ali u prozoru za postavljanje duplikata vrijednosti u prvom polju, umjesto parametra "Duplikat" , odaberite opciju "Jedinstvena" . Nakon toga kliknite gumb "U redu" .

Stoga će se istaknuti oni indikatori koji se ne podudaraju.

Pouka: Uvjetno oblikovanje u programu Excel

Metoda 4: Složena formula

Također možete usporediti podatke pomoću složene formule temeljene na funkciji COUNTIF . Pomoću ovog alata možete računati koliko se svaki element iz odabranog stupca druge tablice ponavlja u prvom.

Operator COUNTRY odnosi se na statističku skupinu funkcija. Njegova je zadaća brojati broj ćelija čije vrijednosti zadovoljavaju zadano stanje. Sintaksa ovog operatora je:

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

Argument "Raspon" je adresa polja u kojoj se izračunavaju podudarne vrijednosti.

Argument kriterija postavlja uvjet podudaranja. U našem slučaju, ona će predstavljati koordinate specifičnih stanica prvog područja tablice.

1. Odaberite prvi element dodanog stupca u kojem će se broj partija brojati. Zatim kliknite ikonu "Umetni funkciju" .
2. Pokreće se Čarobnjak za funkcije . Idemo u kategoriju "Statistička" . Na popisu se nalazi naziv "VIJEĆENJE" . Nakon odabira kliknite gumb "OK" .
3. Pokreće se prozor argumenata operatora VIJEĆA . Kao što vidite, imena polja u ovom prozoru odgovaraju nazivu argumenata.

   Postavite pokazivač u polje "Raspon" . Nakon toga držite lijevu tipku miša, odaberite sve vrijednosti stupca s imenima druge tablice. Kao što možete vidjeti, koordinate odmah ulaze u određeno polje. Ali za naše potrebe nužno je apsolutno postaviti ovu adresu. Da biste to učinili, odaberite koordinate u polju i pritisnite F4 .

   Kao što možete vidjeti, veza je imala apsolutni oblik, koji karakterizira prisutnost dolarskih znakova.

   Zatim idite na polje "Kriterij" , postavljajući kursor tamo. Kliknemo na prvi element s imenima u prvom rasponu tablica. U ovom slučaju ostavljamo referentni odnos. Nakon što se prikaže u polju, možete kliknuti gumb "OK" .

4. Rezultat se prikazuje u elementu listova. To je jednak broju "1" . To znači da se na popisu imena druge tablice pojavljuje jednom prezime "Grinev VP" , koja je prvi na popisu prve tablice tablice.
5. Sada moramo stvoriti sličan izraz za sve ostale elemente prve tablice. Da biste to učinili, kopirajte pomoću oznake za ispunjavanje, kao što smo već učinili. Postavite pokazivač u donji desni dio elementa listova koji sadrži funkciju COUNTIF i nakon što ga pretvorite u oznaku za ispunjavanje, mi ćemo stisnuti lijevu tipku miša i povući kursor prema dolje.
6. Kao što vidite, program je izračunao slučajnosti usporedbom svake stanice prve tablice s podacima koji se nalaze u drugom rasponu tablica. U četiri slučaja rezultat je bio "1" , au dva slučaja "0" . To jest, program nije mogao naći u drugoj tablici dvije vrijednosti koje su dostupne u prvom stolnom nizu.

Naravno, ovaj izraz za usporedbu tabličnih pokazatelja, možete se prijaviti u postojećem obliku, ali postoji mogućnost da se poboljša.

Napravit ćemo vrijednosti koje su dostupne u drugoj tablici, ali ne u prvoj tablici, biti prikazane na posebnom popisu.

1. Prije svega, preradit ćemo našu formulu za zemlju , malo , neka je to jedan od argumenata IF operatora. Da biste to učinili, odaberite prvu ćeliju u kojoj se nalazi operator COUNTRY . U retku formula prije toga dodamo izraz "IF" bez navodnika i otvorimo nosač. Nadalje, kako bismo lakše radili, odabiremo vrijednost "IF" u liniji formule i kliknite na ikonu "Umetni funkciju" .
2. Otvara se prozor s argumentima IF funkcije. Kao što vidite, prvo polje prozora već je popunjeno vrijednostom operatora VIJEĆA . Ali moramo dovršiti nešto drugo na ovom polju. Postavili smo pokazivač tamo i dodali "= 0" bez navodnika već postojećeg izraza.

   Nakon toga idite na polje "Vrijednost ako je istina". Ovdje ćemo koristiti još jednu ugniježđenu funkciju - STRING . Unesite riječ "LINE" bez navodnika, zatim otvorite zagrade i odredite koordinate prve ćelije s prezimenom u drugoj tablici, a zatim zatvorite zagrade. Naime, u našem slučaju u polju "Vrijednost ako je istina" dobiven je sljedeći izraz:

   СТРОКА(D2)

   Sada LINE izjava će prijaviti funkciju IF broj linije u kojem se nalazi određeno prezime, a u slučaju da se ispunjava uvjet naveden u prvom polju, IF funkcija će izlaziti taj broj u ćeliju. Kliknite gumb "OK" .

3. Kao što možete vidjeti, prvi rezultat se prikazuje kao "FALSE" . To znači da vrijednost ne zadovoljava uvjete IF operatora. To jest, prvi prezime je prisutan u oba popisa.
4. Pomoću oznake za ispunjavanje, izraz operatora IF kopiramo na cijeli stupac na uobičajeni način. Kao što možete vidjeti, za dvije stavke koje su prisutne u drugoj tablici, ali ne u prvom, formula daje brojeve linija.
5. Povratimo unatrag od tablice s desne strane i ispunimo stupac brojevima redom, počevši od 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. Otvara se Čarobnjak za funkcije . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Kliknemo na gumb "OK" .
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

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

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

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

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

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

    Kliknite gumb "OK" .

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

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

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

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

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

• ← SQL upita u Microsoft Excelu
• Rad s povezanim tablicama u programu Microsoft Excel →