Nastaviti na WhatsApp?

Razgovor se otvara u WhatsAppu, u aplikaciji ili u novoj kartici. Pritom napuštate našu web stranicu, a podaci poput vaše IP adrese prenose se WhatsAppu (Meta).

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.

Ruke tipkaju na prijenosnom računalu, na zaslonu tablica sa stupcima i kontrolnim vrijednostima za usporedbu popisa
Fotografija: Bluestonex na Unsplash

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.

  1. 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.

  2. 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.

  3. Preuzmite vrijednosti

    S XLOOKUP, VLOOKUP ili Power Queryjem preuzimate cijenu, rok isporuke ili zalihu s drugog popisa u prvi.

  4. Učinite praznine vidljivima

    Retke bez pogotka neka budu označeni kao „nije pronađeno“, umjesto da ostanu prazni. Prazne ćelije nitko ne primijeti.

  5. 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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

Tri načina u usporedbi
KriterijXLOOKUPVLOOKUPPower Query
Verzija ExcelaMicrosoft 365, 2021, 2024sve verzijeWindows od 2016, Mac samo Microsoft 365
Točan pogodakstandardsamo s FALSEstandard
Smjer traženjabilo kojisamo udesnobilo koji
Velika i mala slovanebitnonebitnorazlikuju se
Dvostruki ključprvi pogodakprvi pogodakredak se udvostruči
Ponavljanje sljedeći tjedandopuniti formuledopuniti formulejedan klik na Refresh
Više stupaca s ključempreko pomoćnog stupcapreko pomoćnog stupcaizravno moguće
Prikladno zabrzu jednokratnu usporedbustarije verzije Excelausporedbe koje se ponavljaju
Djelatnica u uredu provjerava ispisano izvješće na podlozi s kvačicom pokraj prijenosnog računala
Fotografija: Vitaly Gariev na Unsplash

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.

Tipični razlozi za pogotke koji nedostaju (XLOOKUP)
Vaš popisPopis dobavljačaRezultatUzrok
10-471110-471112,40 €odgovara
004711 (tekst)4711 (broj)nije pronađenovodeće nule izgubljene, tekst ne pogađa broj
AB-120 s razmakom na krajuAB-120nije pronađenonevidljivi razmak
K-200k-2008,90 €velika i mala slova kod XLOOKUP nisu bitna
5521055210, postoji dvaput3,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.

Izvori i dodatne poveznice

ČESTA PITANJA

Kratki odgovori.

Kako usporediti dva Excel popisa i preuzeti vrijednosti?

Oba popisa povezujete preko jednoznačnog stupca s ključem, najčešće šifre artikla. Zatim s XLOOKUP, VLOOKUP s FALSE ili Power Queryjem preuzmite željene vrijednosti s drugog popisa. Retke bez pogotka neka budu označeni kao „nije pronađeno“, prebrojite ih i provjerite pojedinačno prije nego što upotrijebite rezultat.

Kako usporediti Excel popise bez VLOOKUP?

U Microsoft 365, Excelu 2021 i Excelu 2024 upotrijebite XLOOKUP, u starijim verzijama INDEX s MATCH. Za usporedbe koje se ponavljaju prikladan je Power Query s funkcijom Merge Queries. Potpuno bez formule ide s alatom za usporedbu koji oba popisa povezuje preko stupca s ključem kao što je šifra artikla.

Zašto VLOOKUP ne pronalazi vrijednost iako je na popisu?

Najčešće se vrijednosti nevidljivo razlikuju: razmak na kraju, zaštićeni razmak ili šifra koja je na jednom popisu spremljena kao broj, a na drugom kao tekst. Oba stupca pročistite s TRIM i SUBSTITUTE i dovedite ih u isti format. Provjerite i glasi li zadnji argument FALSE.

Mogu li usporediti dvije Excel tablice preko više stupaca?

Da. S formulama u obje tablice napravite pomoćni stupac koji spaja stupce sa znakom razdvajanja, na primjer dobavljača i šifru artikla. U Power Queryju pri spajanju izravno označite više stupaca držeći tipku Ctrl, u obje tablice istim redoslijedom.

Mogu li Excel popise usporediti online bez prijenosa podataka?

Da, ako alat obrađuje datoteke izravno u pregledniku. Naša besplatna usporedba Excel datoteka radi tako: datoteke se ne prenose, a vidite nove retke, one koji nedostaju i promijenjene retke. Podržane su datoteke .xlsx i .csv do 5 MB i 20.000 redaka po datoteci. Dvostruke ključeve alat označava kao nejasne kako biste ih provjerili.

RAZGOVARAJMO O VAŠEM PROCESU

Pišite nam. Javljamo se osobno.

Pitanje, ideja ili zadatak koji svaki tjedan oduzima vrijeme? Dovoljno je nekoliko rečenica. Odgovor dobivate od ljudi koji će vaš pomoćnik i izraditi.

Ivan, Lukas i JanOdgovor u roku od 1–2 radna dana

  • Osobni odgovor
  • Bez obveze
  • Iz Beča, uz poštivanje GDPR-a
Fotografija: Ivan
Radije razgovarate izravno?Ivan, vaša kontakt osoba+43 676 59 40 027office@pebblebyte.comPon do Pet 08:00–18:00
Imate konkretan zadatak? Besplatna provjera procesa

Vaša poruka

Molimo ne unosite pristupne podatke ni povjerljive podatke o klijentima. Vaše podatke koristimo samo za obradu vašeg upita. Bez newslettera, bez prosljeđivanja u promidžbene svrhe. Pojedinosti u obavijesti o zaštiti podataka.