EXCEL I POPISI
Kako usporediti dvije tablice u Excelu i sigurno ih povezati preko šifre artikla
Stigao je novi cjenik veletrgovca, više od 3.000 redaka, a nabavne cijene trebaju ući u vaš popis artikala. Dakle tražiti, kopirati, zalijepiti, red po red. Na kraju ostaje neugodan osjećaj da je negdje cijena završila u pogrešnom retku. Zato se isplati znati kako usporediti dvije tablice u Excelu.

To ne košta samo jedno poslijepodne. Pogrešno preuzeta nabavna cijena često se primijeti tek kad je ponuda već kod kupca ili kad marža na kraju godine ne štima.
Ovaj članak pokazuje kako uskladiti Excel popise tako da svaki redak preko šifre artikla dobije pravu vrijednost i da svaka praznina postane vidljiva: s XLOOKUP, s VLOOKUP za starije verzije i s Power Queryjem za usporedbu koja se ponavlja svaki tjedan.
Ukratko: kako usporediti dvije tablice u Excelu?
Usporediti dva Excel popisa znači: svaki redak s popisa A povezujete s odgovarajućim retkom s popisa B. Veza ide preko jedinstvenog ključa, najčešće šifre artikla. Zatim preuzimate željene vrijednosti i označavate sve retke za koje nema pogotka.
- Odredite stupac s ključem
Odaberite stupac koji jednoznačno označava svaki redak i postoji u oba popisa, na primjer šifru artikla dobavljača.
- Pročistite ključ
Uklonite razmake i oba stupca dovedite u isti format, dakle tekst ili broj, kako se 004711 i 4711 ne bi mimoišli.
- Preuzmite vrijednosti
S XLOOKUP, VLOOKUP ili Power Queryjem preuzimate cijenu, rok isporuke ili zalihu s drugog popisa u prvi.
- Učinite praznine vidljivima
Retke bez pogotka neka budu označeni kao „nije pronađeno“, umjesto da ostanu prazni. Prazne ćelije nitko ne primijeti.
- Provjerite rezultat
Prebrojite pogotke, provjerite deset redaka kao uzorak i tek tada preuzmite rezultat u kalkulaciju ili robno poslovanje.
Koji je stupac prikladan kao ključ za usporedbu?
Većina usporedbi ne uspije zbog ključa, rijetko zbog formule. Stupac s ključem je stupac po kojem Excel prepoznaje da dva retka pripadaju zajedno. Svaka formula i svaki upit dobri su samo koliko i taj ključ. Šifra artikla gotovo je uvijek prikladna. Naziv artikla gotovo nikad, jer su „Kabel NYM-J 3x1,5“ i „NYM-J 3 x 1,5 mm² kabel“ za Excel dvije različite stvari.
- Dobri ključevi: šifra artikla dobavljača, EAN odnosno GTIN, broj kupca, broj računa.
- Slabi ključevi: nazivi, imena tvrtki, adrese. Tu se pisanje razlikuje i usporedba pogađa samo dio.
- Vlastite i tuđe šifre: U svom popisu artikala vodite poseban stupac sa šifrom artikla dobavljača. Bez tog mosta ključ nedostaje.
Što ako jedan stupac sam nije jednoznačan?
Ponekad je jednoznačna samo kombinacija, na primjer dobavljač i šifra artikla, kad dva dobavljača dodjeljuju iste brojeve. Tada u oba popisa napravite pomoćni stupac sa složenim ključem: =[@Dobavljač]&"|"&[@Šifra]. Znak razdvajanja je važan. Bez njega „12“ i „345“ daju isto što i „123“ i „45“.
Usporedba dva Excel popisa formulama: XLOOKUP, VLOOKUP ili INDEX
XLOOKUP u Microsoft 365, Excelu 2021 i Excelu 2024
XLOOKUP traži vrijednost u jednom stupcu i vraća vrijednost iz istog retka drugog stupca. Funkcija postoji u Microsoft 365, Excelu 2021 i Excelu 2024. U Excelu 2016 i 2019 je nema.
Najprije oba popisa pretvorite u tablicu s Ctrl+T i tablicu dobavljača nazovite „Dobavljač“. Formula u vašem popisu artikala tada glasi: =XLOOKUP([@Šifra],Dobavljač[Šifra],Dobavljač[Cijena],"nije pronađeno"). Uz hrvatske ili njemačke regionalne postavke između argumenata umjesto zareza stoji točka sa zarezom.
- Točan pogodak je standard. XLOOKUP bez dodatnih postavki traži samo točna podudaranja.
- Četvrti argument je vaš znak upozorenja. Umjesto pogreške #N/A u ćeliji stoji „nije pronađeno“. Po tome zatim možete filtrirati.
- Redoslijed stupaca nije bitan. Šifra artikla ne mora stajati lijevo od cijene.
- Za svaki daljnji stupac posebna formula. Rok isporuke i zalihu preuzimate istom formulom, mijenja se samo stupac povratne vrijednosti.
VLOOKUP i INDEX s MATCH za starije verzije
Ako radite s Excelom 2016 ili 2019, upotrijebite VLOOKUP. Ako je popis dobavljača na listu naziva Dobavljač, formula glasi: =IFNA(VLOOKUP(A2,Dobavljač!$A:$D,3,FALSE),"nije pronađeno"). Broj 3 označava treći stupac raspona, ovdje cijenu u stupcu C.
FALSE na kraju najvažniji je dio. Ako ga izostavite, VLOOKUP prema Microsoftu traži približno podudaranje. Kod nesortiranih šifri artikala formula tada može vratiti cijenu iz tuđeg retka, bez poruke o pogrešci. Još dvije slabosti: šifra artikla mora biti u prvom stupcu raspona. A ako netko umetne stupac, fiksni broj 3 odjednom pokazuje na pogrešan stupac.
Pouzdanija je kombinacija INDEX i MATCH: =INDEX(Dobavljač!$C:$C,MATCH(A2,Dobavljač!$A:$A,0)). MATCH pronalazi redak, INDEX dohvaća vrijednost. Nula nameće točan pogodak, a umetnuti stupci ne smetaju.
Spajanje Excel popisa u Power Queryju kad se ponavlja svaki tjedan
Power Query je alat u Excelu za dohvaćanje i preoblikovanje podataka, a nalazi se na kartici Data u grupi Get & Transform Data. U Excelu za Windows uključen je od verzije 2016, u Excelu za Mac samo uz pretplatu na Microsoft 365. Na Macu provjerite jesu li svi koraci u nastavku dostupni. Prednost: usporedbu postavite jednom i zatim je osvježavate jednim klikom, bez obzira na to tko je u timu pokreće.
- Učitajte oba popisa kao upit
Kliknite u odgovarajuću tablicu i odaberite Data > From Table/Range. U uređivaču postavite tip podataka šifre artikla u oba upita na Text. Prema Microsoftu povezani stupci moraju imati isti tip podataka. Ako Power Query stupac automatski pretvori u broj, vodeće nule se gube.
- Otvorite spajanje
Spremite oba upita s Close & Load To > Only Create Connection. Zatim odaberite Data > Get Data > Combine Queries > Merge. U uređivaču Power Queryja isto ide preko Home > Merge Queries. Gore odaberite svoj popis artikala, ispod popis dobavljača.
- Označite ključ
U obje tablice kliknite šifru artikla. Kod složenog ključa držite tipku Ctrl i označite više stupaca, u obje tablice istim redoslijedom.
- Odaberite vrstu spajanja
Odaberite Left Outer: svi retci iz prve tablice, odgovarajući iz druge. Tako ostaju svi vaši artikli, a dodaju se odgovarajuće vrijednosti dobavljača. Na dnu dijaloga Excel pokazuje koliko se redaka podudara. Taj broj je vaša prva kontrola.
- Proširite stupce i učitajte
Kliknite ikonu za proširenje (dvostruka strelica) novog stupca, odaberite cijenu i rok isporuke i učitajte rezultat s Close & Load na novi list.
Sljedeći tjedan zamijenite samo podatke u tablici dobavljača i kliknite Data > Refresh All. Za razliku od formula, Power Query razlikuje velika i mala slova. A dvostruki ključ na popisu dobavljača nakon proširenja udvostruči pogođeni redak, umjesto da tiho uzme prvi pogodak. Vrsta spajanja „Left Anti“ usput daje upravo artikle bez para. Više o tome u članku pronaći zapise koji nedostaju.
| Kriterij | XLOOKUP | VLOOKUP | Power Query |
|---|---|---|---|
| Verzija Excela | Microsoft 365, 2021, 2024 | sve verzije | Windows od 2016, Mac samo Microsoft 365 |
| Točan pogodak | standard | samo s FALSE | standard |
| Smjer traženja | bilo koji | samo udesno | bilo koji |
| Velika i mala slova | nebitno | nebitno | razlikuju se |
| Dvostruki ključ | prvi pogodak | prvi pogodak | redak se udvostruči |
| Ponavljanje sljedeći tjedan | dopuniti formule | dopuniti formule | jedan klik na Refresh |
| Više stupaca s ključem | preko pomoćnog stupca | preko pomoćnog stupca | izravno moguće |
| Prikladno za | brzu jednokratnu usporedbu | starije verzije Excela | usporedbe koje se ponavljaju |

Primjer iz nabave: preuzeti cijene s popisa dobavljača
Sljedeća tablica pokazuje zbog čega pojedini retci u ovom primjeru ne uspijevaju. S takvim slučajevima računajte kod svake datoteke iz tuđeg programa.
| Vaš popis | Popis dobavljača | Rezultat | Uzrok |
|---|---|---|---|
| 10-4711 | 10-4711 | 12,40 € | odgovara |
| 004711 (tekst) | 4711 (broj) | nije pronađeno | vodeće nule izgubljene, tekst ne pogađa broj |
| AB-120 s razmakom na kraju | AB-120 | nije pronađeno | nevidljivi razmak |
| K-200 | k-200 | 8,90 € | velika i mala slova kod XLOOKUP nisu bitna |
| 55210 | 55210, postoji dvaput | 3,10 € | preuzima se samo prvi pogodak, druga cijena se gubi |
Zašto VLOOKUP ne pronalazi vrijednost? Tipične pogreške i kako ih izbjeći
- Približan pogodak: VLOOKUP bez FALSE može vratiti pogrešne cijene bez poruke o pogrešci. Postojeće datoteke ciljano provjerite na taj četvrti argument.
- Broj protiv teksta: Šifra artikla kao broj ne pronalazi istu šifru kao tekst. Ujednačite oba stupca, na primjer s =TEXT(A2,"000000") za šesteroznamenkaste šifre s vodećim nulama.
- Razmaci: TRIM uklanja suvišne razmake, i na početku i na kraju. Zaštićeni razmak (znak 160) iz web izvoza prema Microsoftu ostaje. Za njega pomaže =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
- Dvostruki ključevi: Formula tiho uzima prvi pogodak. Ključ prije toga provjerite s COUNTIF.
- Fiksni rasponi: Raspon kao $A$2:$D$2500 ne raste. Novi artikli od retka 2501 nikad se ne pronalaze. Tablice napravljene s Ctrl+T rastu automatski.
- Formule kao konačni rezultat: Ako netko obriše popis dobavljača, sve formule pokazuju pogreške. Provjereni rezultat zalijepite kao vrijednosti prije nego što ga proslijedite ili uvezete.
Od kada se ručna usporedba više ne isplati?
Za jednokratnu usporedbu dovoljna je formula. Skupo postaje ponavljanje. Oprezan izračun za samo jednog dobavljača: otvoriti datoteku, prilagoditi stupce, dopuniti formule, razjasniti praznine. To brzo traje 45 minuta tjedno. Uz 46 radnih tjedana to je 34,5 sati godišnje. Uz pretpostavljenih 38 € internog troška po satu, dakle plaća plus dodatni troškovi, rutina košta oko 1.310 € godišnje. Pogreške tu još nisu uračunate, na primjer zastarjela nabavna cijena u ponudi.
Vlastite brojke izračunajte kalkulatorom troškova uredskog rada za dvije minute. Računica se obično preokrene kad vrijedi jedna od ovih točaka:
- Više dobavljača šalje popise u različitim formatima i s drugim nazivima stupaca.
- Rezultat zatim treba uvesti u robno poslovanje ili ERP.
- Samo jedna osoba u poduzeću zna kako datoteka radi. Kad je na godišnjem odmoru, usporedba stoji.
- Popisi stižu kao PDF ili u tekstu e-pošte, a ne kao Excel datoteka.
- Promjene cijena ne treba samo preuzeti, nego i provjeriti i odobriti. Za to postoje posebna pravila provjere, vidi Usporedba cjenika dobavljača.
Sljedeći korak: usporediti popise bez formule ili prepustiti usporedbu
Ako popis stiže od dobavljača, naša besplatna usporedba cjenika najjednostavniji je početak. Povučete stari i novi popis, kao Excel, CSV ili PDF. Alat ih povezuje preko šifre artikla, i kad se stupci drukčije zovu ili su drukčije poredani, te nadoknađuje vodeće nule koje je Excel obrisao. Vidite svaku promjenu cijene u postocima, nove i ukinute artikle i preuzimate promjene kao popis. Dvostruke šifre artikala ne pogađa, nego ih prikazuje pod „Molimo provjerite“. Za druge popise, na primjer kupce ili zalihe, postoji usporedba Excel datoteka. Ona pokazuje retke koji nedostaju, nove i promijenjene retke sa starom i novom vrijednošću. Upisivanje vrijednosti u vaš vlastiti popis ostaje zadatak formule ili Power Queryja. U oba alata datoteke ostaju u vašem pregledniku.
Ako usporedba dolazi svaki tjedan i s više dobavljača, AI pomoćnik preuzima cijeli lanac: dohvaća popis iz poštanskog sandučića, čisti ključ, uspoređuje i izrađuje datoteku za uvoz u vaše robno poslovanje. Nejasni retci idu osobi koja odobrava. Vaš softver ostaje kakav jest, a proces više ne ovisi o jednoj jedinoj kolegici. Što to konkretno znači, pokazuje stranica Automatizacija rada u Excelu.
Kad vam više dobavljača svaki tjedan šalje nove popise, isplati se pogledati cijeli proces. U besplatnoj provjeri procesa opišite koji popisi stižu i kamo rezultat mora ići. U roku od 1–2 radna dana dobit ćete osobnu, besplatnu procjenu odgovara li najbolje formula, Power Query ili pomoćnik. Pomoćnik košta od 1.990 € bez PDV-a po fiksnoj cijeni, a kod malih projekata spreman je za rad 3–7 dana nakon usklađivanja slučaja primjene.
