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 pronaći unose koji nedostaju u Excelu i vidjeti što fali na kojem popisu

Robno poslovanje za listopad broji 1.184 računa, knjigovodstvo 1.176. Dakle nedostaje osam. Samo koji? Stavite dva izvoza jedan pokraj drugoga, prolazite broj po broj, a na kraju ipak ostaje neugodan osjećaj da ste jedan previdjeli. Trebate pronaći unose koji nedostaju u Excelu, i to pouzdano.

Visoki stogovi registratora i papirnatih dokumenata sa žutim oznakama u uredu, u kojima je teško pronaći pojedine zapise
Fotografija: Wesley Tingey na Unsplash

To košta više od jednog poslijepodneva. Izlazni račun koji nikad nije proknjižen nedostaje u prijavi PDV-a i na popisu otvorenih stavki. Često se primijeti tek tjednima kasnije, kad ispravak donosi znatno više posla.

Ovdje čitate kako u Excelu pronaći unose koji nedostaju u oba smjera, s rezultatom na koji se možete osloniti. Dobivate tri načina za različite verzije Excela, metodu za praznine u brojevima računa i primjer koji pokazuje zašto sam broj redaka zavarava.

Ukratko: kako pronaći zapise koji nedostaju u Excelu?

Zapis nedostaje kad se njegov ključ pojavljuje na popisu A, ali ne i na popisu B. Ključ je stupac koji jednoznačno označava unos, na primjer broj računa, broj kupca ili šifra artikla. Za svaki redak brojite koliko se puta njegov ključ pojavljuje na drugom popisu. Ako je rezultat 0, unos nedostaje.

  1. Odredite smjer

    Razjasnite želite li znati što nedostaje u B, što nedostaje u A ili oboje. Najčešće trebate oba smjera.

  2. Pročistite ključ

    Uklonite suvišne razmake i stupac s ključem na oba popisa dovedite u isti format, inače se isti brojevi smatraju različitima.

  3. Prebrojite pojavljivanja

    Pomoćni stupac s COUNTIF broji koliko se puta svaki ključ nalazi na drugom popisu.

  4. Filtrirajte na 0

    Svi retci s 0 nedostaju na drugom popisu. To je vaš popis za provjeru.

  5. Provjerite drugi smjer

    Ponovite korak obrnuto. Tek oba rezultata zajedno potpuno objašnjavaju razliku.

Što nedostaje i gdje? Najprije pravo pitanje

Tipična pitanja i odgovarajući način
PitanjePrimjerOdgovarajući način
Što je u A, a nije u B?Računi u robnom poslovanju koji nisu proknjiženiCOUNTIF ili Left Anti
Što je u B, a nije u A?Knjiženja bez računa u robnom poslovanjuCOUNTIF obrnuto ili Right Anti
Što nedostaje na obje strane?Inventurni popis prema stanju zalihaoba smjera ili alat za usporedbu
Koji brojevi nedostaju u nizu?Praznine u brojevima računanapraviti niz brojeva i usporediti

Kako pronaći unose koji nedostaju s COUNTIF?

COUNTIF postoji u svakoj verziji Excela, dakle ne trebate ažuriranje. Oba popisa pretvorite u tablice s Ctrl+T i nazovite ih „ERP“ i „Knjigovodstvo“. U novi stupac tablice ERP upišite: =COUNTIF(Knjigovodstvo[Račun],[@Račun])=0

TRUE znači: taj račun nedostaje u knjigovodstvu. Filtrirajte stupac na TRUE i imate popis za provjeru, umjesto da 1.184 retka uspoređujete okom. Za suprotni smjer istu formulu stavite u tablicu Knjigovodstvo i zamijenite nazive tablica.

Kako bojom označiti vrijednosti koje nedostaju?

Ako retke radije vidite obojene, upotrijebite uvjetno oblikovanje. Ono ne prihvaća reference na tablice, zato ovdje radite s referencama na list. Pretpostavimo da su brojevi na oba lista u stupcu A: označite brojeve na popisu A od A2, odaberite Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format i upišite: =COUNTIF(Knjigovodstvo!$A:$A,$A2)=0. Za popis koji obrađujete slučaj po slučaj filtar je ipak praktičniji.

Kako automatski izlistati unose koji nedostaju?

U Microsoft 365, Excelu 2021 i Excelu 2024 FILTER u jednom koraku ispisuje sve unose koji nedostaju, bez pomoćnog stupca: =FILTER(ERP[Račun],COUNTIF(Knjigovodstvo[Račun],ERP[Račun])=0,"ništa ne nedostaje"). Popis se ažurira čim se podaci promijene. Zadnji dio, „ništa ne nedostaje“, važan je: bez njega Excel prikazuje pogrešku #CALC! kad je doista sve potpuno.

Alternativa bez zamjenskih znakova: XLOOKUP s ISNA

XLOOKUP vraća pogrešku #N/A kad ništa ne pronađe. ISNA od toga napravi TRUE ili FALSE: =ISNA(XLOOKUP([@Račun],Knjigovodstvo[Račun],Knjigovodstvo[Račun])). TRUE opet znači: nedostaje. Prednost: XLOOKUP standardno traži točno podudaranje i * i ? tretira kao zamjenske znakove samo u načinu podudaranja 2, pa se posebni znakovi u ključu uzimaju doslovno. XLOOKUP postoji u Microsoft 365, Excelu 2021 i Excelu 2024. U Excelu 2016 i 2019 jednako radi =ISNA(MATCH([@Račun],Knjigovodstvo[Račun],0)), samo MATCH, kao i COUNTIF, čita * i ? kao zamjenske znakove.

Power Query: svaki mjesec pronaći zapise koji nedostaju antijoinom

Ako se provjera ponavlja svaki mjesec, isplati se Power Query. Usporedbu postavite jednom i zatim je osvježavate jednim klikom. Antijoin je spajanje koje vraća samo retke bez para. Upravo to je popis zapisa koji nedostaju.

  1. Učitajte oba popisa

    Kliknite u odgovarajuću tablicu i odaberite Data > From Table/Range. Tip podataka stupca s ključem postavite na Text, razmake uklonite s Transform > Format > Trim i spremite s Close & Load To > Only Create Connection.

  2. Spojite

    Odaberite Data > Get Data > Combine Queries > Merge, a u uređivaču Power Queryja alternativno Home > Merge Queries. Gore popis čije unose koji nedostaju tražite, dolje popis za usporedbu.

  3. Odaberite ključ i vrstu spajanja

    U obje tablice označite stupac s ključem. Kao vrstu spajanja odaberite „Left Anti“, dakle samo retke iz prve tablice bez pogotka u drugoj. Na dnu dijaloga Excel pokazuje na koliko se redaka to odnosi.

  4. Pospremite i učitajte

    Uklonite prazan povezani stupac i učitajte rezultat na zaseban list. Za suprotni smjer napravite drugi upit s „Right Anti“.

Za razliku od COUNTIF, Power Query pri spajanju razlikuje velika i mala slova. „re-2026-1437“ i „RE-2026-1437“ tamo se smatraju različitima. Zato ključeve prije toga ujednačeno pretvorite u velika slova ako vaši programi izvoze različita pisanja.

Sljedeći mjesec zamijenite podatke i kliknite Data > Refresh All. Popis za provjeru gotov je za nekoliko sekundi i svaki put nastaje po istim pravilima, bez obzira na to tko ga u timu osvježava. Kako istom tehnikom preuzeti vrijednosti s drugog popisa, a ne samo tražiti praznine, pokazuje članak Kako usporediti dvije tablice u Excelu.

Prst prelazi preko rukom pisanog popisa s praznim kvadratićima pokraj tipkovnice, unosi se označavaju jedan po jedan
Fotografija: Jakub Żerdzicki na Unsplash

Kako pronaći brojeve računa koji nedostaju u Excelu?

Ponekad nema drugog popisa. Želite samo znati koji brojevi nedostaju u nizu. U Njemačkoj § 14 Abs. 4 Satz 1 Nr. 4 UStG, a u Austriji § 11 Abs. 1 Z 3 lit. h UStG 1994 načelno zahtijevaju uzastopni broj računa koji se za identifikaciju računa dodjeljuje jednom. Niz bez praznina zakonski tekst ne propisuje. Za Njemačku Umsatzsteuer-Anwendungserlass (upute porezne uprave za PDV) u odjeljku 14.5 Abs. 10 izričito pojašnjava da niz bez praznina nije obvezan. Pri nadzoru porezna uprava ipak može pitati za praznine. Na sigurnoj ste strani ako za svaki broj koji nedostaje znate razlog, na primjer odbačeni nacrt. Kako to dokumentirati, najbolje razjasnite sa svojim poreznim savjetnikom.

U Microsoft 365, Excelu 2021 i Excelu 2024 SEQUENCE stvara potpuni niz, a FILTER pokazuje što od toga nedostaje: =LET(n,Brojevi[Br],svi,SEQUENCE(MAX(n)-MIN(n)+1,,MIN(n)),FILTER(svi,COUNTIF(n,svi)=0,"nema praznine")). Formula provjerava niz između najmanjeg i najvećeg postojećeg broja.

  • Starije verzije Excela: Sortirajte brojeve uzlazno i u B3 upišite =A3-A2. Svaka vrijednost veća od 1 označava prazninu neposredno ispred, a 0 dvaput dodijeljen broj.
  • Brojevi s prefiksom: Kod brojeva kao RE-2026-0412 izdvojite brojčani dio, na primjer s =VALUE(RIGHT([@Račun],4)), i svaki niz brojeva provjerite zasebno.
  • Prijelaz godine: Ako niz brojeva svake godine počinje ispočetka, svaku godinu provjerite zasebno. Inače se brojevi obiju godina miješaju, a pravu prazninu u jednoj godini prekrije isti broj iz druge godine.

Primjer iz mjesečnog zaključka: zašto je 8 računa koji nedostaju zapravo 14 slučajeva

Primjer pokazuje najvažniju točku: sam broj redaka malo govori. Unosi koji nedostaju na obje strane međusobno se poništavaju, a razlika od 8 ovdje skriva 14 stvarnih slučajeva. Tko traži samo osam, proknjiži tri računa premalo i ipak misli da je zaključak gotov. Oba smjera u jednom prolazu pokazuje besplatna usporedba Excel datoteka, bez formule i bez prijenosa.

Izvadak iz popisa za provjeru u primjeru
Broj računaRobno poslovanjeKnjigovodstvoNalaz
RE-2026-1412dadau redu
RE-2026-1437daneizvoz prekinut, naknadno proknjižiti
RE-2026-1466dasamo s razmakom na krajunaizgled nedostaje, nakon TRIM u redu
AR-2026-007nedaračun za predujam, izrađen izvan robnog poslovanja

Tipične pogreške pri traženju zapisa koji nedostaju

  • Provjeriti samo jedan smjer: Vidite što nedostaje u B, ali ne i što je u B višak. Uvijek provjerite oba smjera.
  • Brojati samo retke: Isti broj redaka ne dokazuje da oba popisa sadrže iste unose.
  • Neuredni ključevi: Razmaci na kraju i zaštićeni razmaci iz web izvoza stvaraju prividne praznine. TRIM sam ne uklanja zaštićene razmake, zato pomaže =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Brojevi koji su na jednom popisu spremljeni kao tekst stvaraju prividne praznine prije svega kod XLOOKUP, MATCH i Power Queryja.
  • Različita razdoblja: Jedan izvoz do 31.10., drugi do dana izvoza. Unaprijed odredite koje razdoblje vrijedi.
  • Previdjeti dvostruke ključeve: Ako je broj u B dvaput, ne nedostaje, ali je ipak problem. Kako pronaći takve slučajeve, piše u članku Kako pronaći duplikate u Excelu.
  • Popis za provjeru bez statusa: Za svaki redak koji nedostaje zabilježite nalaz i rješenje. Inače sljedeći mjesec ponovno provjeravate iste slučajeve.

Granice i trud: kada se ručni rad više ne isplati

Samu formulu napišete za nekoliko minuta. Vrijeme odnosi sve oko nje: preuzimanje izvoza, usklađivanje formata, izdvajanje prividnih praznina, razjašnjavanje nalaza. Oprezna pretpostavka su 2 sata po mjesečnom zaključku. To je 24 sata godišnje. Uz pretpostavljenih 38 € internog troška po satu, dakle plaća plus dodatni troškovi, provjera košta oko 910 € godišnje. Tome se pribraja ono što košta previđeni račun: ako nedostaje u prijavi PDV-a, ispravak kasnije postaje zahtjevniji od današnje provjere.

Vlastite vrijednosti izračunajte kalkulatorom troškova uredskog rada. Teško postaje kad su uključena više od dva izvora, kad se ključevi razlikuju između programa ili kad se unosi koji nedostaju zatim ručno prenose u drugi program. A ako samo jedna kolegica zna kako usporedba radi, ona stoji čim je kolegica na godišnjem odmoru.

Kada AI pomoćnik ima smisla

Za brzu usporedbu bez formula postoji naša besplatna usporedba Excel datoteka. Odaberite, na primjer, izvoz robnog poslovanja kao dosadašnju, a izvoz knjigovodstva kao novu datoteku. „Nedostaje“ tada znači: samo u robnom poslovanju, „Novo“ znači: samo u knjigovodstvu. Dakle vidite oba smjera odjednom. Stupac za povezivanje mora se u obje datoteke zvati jednako, na primjer broj računa. Alat sam predlaže stupac s jedinstvenim vrijednostima, a vi ga možete promijeniti. Razmake na rubu standardno zanemaruje, velika i mala slova po želji. Dvostruke brojeve označava kao nejasne. Rezultat preuzimate kao popis, a datoteke ostaju u vašem pregledniku.

Ako se provjera između robnog poslovanja i knjigovodstva ponavlja svaki mjesec, pomoćnik može preuzeti izvoze, pročistiti ključeve i izraditi popis za provjeru s nalazom. Vaša djelatnica razjašnjava samo stvarne slučajeve, a usporedba radi i kad ona nije u uredu. Vaši programi ostaju kakvi jesu. Kako takav proces izgleda, pokazuje stranica Automatizacija rada u Excelu.

Ako mjesečna usporedba dolazi iz više programa ili ovisi o jednoj osobi, pomaže pogled izvana. U besplatnoj provjeri procesa opišite koje popise uspoređujete i koliko često. U roku od 1–2 radna dana dobit ćete osobnu, besplatnu procjenu je li dovoljna formula iz ovog članka ili bi provjeru trebao preuzeti pomoćnik, po fiksnoj cijeni od 1.990 € bez PDV-a i kod malih projekata proveden 3–7 dana nakon usklađivanja slučaja primjene.

Izvori i dodatne poveznice

ČESTA PITANJA

Kratki odgovori.

Kako u Excelu pronaći unose koji nedostaju na drugom popisu?

U pomoćni stupac upišite =COUNTIF(PopisB,A2)=0, pri čemu je PopisB raspon drugog popisa, i filtrirajte na TRUE. Ti retci s popisa A nedostaju na popisu B. Zatim provjerite suprotni smjer, jer tek oba rezultata zajedno potpuno objašnjavaju razliku.

Kako u Excelu provjeriti postoji li vrijednost na drugom popisu?

S =COUNTIF(raspon,vrijednost)>0 dobivate TRUE ako se vrijednost pojavljuje. U Microsoft 365, Excelu 2021 i Excelu 2024 ide i s =ISNA(XLOOKUP(vrijednost,raspon,raspon)), što vraća TRUE ako vrijednost nedostaje. U oba slučaja pazite na iste formate i suvišne razmake.

Kako označiti vrijednosti koje nedostaju u Excelu?

Upotrijebite uvjetno oblikovanje s formulom kao =COUNTIF(Sheet2!$A:$A,$A2)=0. Excel tada oboji sve retke čiji se ključ ne pojavljuje na drugom listu. Za popis koji obrađujete slučaj po slučaj pomoćni stupac s filtrom obično je pregledniji.

Kako pronaći brojeve računa koji nedostaju u Excelu?

Sortirajte brojeve uzlazno i izračunajte razliku prema prethodnom retku. Svaka vrijednost veća od 1 označava prazninu. U Microsoft 365, Excelu 2021 i Excelu 2024 sa SEQUENCE napravite potpuni niz, a s FILTER i COUNTIF izdvojite sve brojeve koji nedostaju.

Jesu li praznine u brojevima računa dopuštene?

U Njemačkoj da: zakon o PDV-u (UStG) zahtijeva uzastopni broj koji se dodjeljuje jednom, a porezna uprava u Umsatzsteuer-Anwendungserlass pojašnjava da niz bez praznina nije obvezan. Austrijski zakon koristi istu formulaciju i niz bez praznina također izričito ne propisuje. Praznine ipak trebaju biti objašnjive, na primjer odbačenim nacrtima. U slučaju dvojbe razjasnite to sa svojim poreznim savjetnikom.

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.