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

PODACI I UVOZI

Izvod iz banke u Excel: čisti uvoz, provjera salda i analiza

Mjesec je zatvoren, a knjigovodstvo čeka promete s računa. Preuzmete izvoz iz internetskog bankarstva, otvorite ga dvostrukim klikom, a Excel prikaže „Überweisung Müller GmbH“. Iznad tablice stoji pet redaka s brojem računa i razdobljem, a kod iznosa ne znate je li Excel svaki od njih preuzeo kao broj.

Radni stol s ispisanim popisima brojeva, registratorom, naočalama i kalkulatorom, kao kod mjesečnog usklađivanja prometa s računa
Fotografija: Cht Gsml na Unsplash

Sređivanje svaki mjesec oduzima vrijeme. Skuplji je iznos koji neprimjetno stoji u stupcu kao tekst: Excel ga izostavlja iz svakog zbroja, bez poruke o pogrešci, i usklađivanje s otvorenim računima više se ne slaže. Tko umjesto toga uzme besplatni online pretvarač, prenosi imena i IBAN-ove kupaca i dobavljača nepoznatom pružatelju usluge.

Ove upute pokazuju kako izvod iz banke prebaciti u Excel iz CSV-a ili PDF-a, kako provjerom salda osigurati da nijedan iznos ne nedostaje i kako zatim analizirati transakcije.

Ukratko: kako izvod iz banke prebaciti u Excel

Siguran put vodi preko CSV izvoza vaše banke i uvoza naredbom Data > From Text/CSV umjesto dvostrukog klika. Ondje odredite skup znakova, razdjelnik i format brojeva prije nego što Excel bilo što protumači na svoj način. Na kraju provjera salda dokazuje da je svaka transakcija stigla kao broj. Nazivi izbornika i funkcija u ovom članku navedeni su prema engleskoj verziji Excela.

  1. Izvezite kao CSV

    Preuzmite promete u internetskom bankarstvu kao CSV datoteku. CSV datoteka je čisti tekst s jednom transakcijom po retku i čita se bez zaobilaženja preko izgleda stranice.

  2. Uvezite preko kartice Data

    Otvorite datoteku naredbom Data > From Text/CSV. U pregledu vidite kako Excel čita datoteku prije nego što išta završi u tablici.

  3. Provjerite skup znakova i razdjelnik

    Ako su posebni znakovi pokvareni, postavite File Origin na 65001: Unicode (UTF-8). Kao razdjelnik odaberite znak koji u pregledu ispravno dijeli stupce.

  4. Osigurajte da su iznosi brojevi

    Pretvorite iznos i datum s odgovarajućim regionalnim postavkama i funkcijama COUNTA i COUNT provjerite je li svaki iznos broj.

  5. Napravite provjeru salda

    Početni saldo plus zbroj svih transakcija mora dati završni saldo s izvoda. Tek tada tablica ide knjigovodstvu.

Zašto izvod iz banke u Excelu izgleda pokvareno

Podaci vaše banke su točni. Izvod se pokvari tek pri otvaranju, jer CSV datoteka nema čvrsta pravila za skup znakova, razdjelnik i format brojeva, pa Excel kod dvostrukog klika pogađa. Ove uzroke možete sami provjeriti u svojoj datoteci:

  • Skup znakova: Ako je datoteka spremljena kao UTF-8 bez nevidljive oznake na početku (BOM), Excel je kod dvostrukog klika čita u starom Windows skupu znakova. ü postaje ü, a š postaje Å¡.
  • Razdjelnik: Excel s njemačkim ili austrijskim postavkama u CSV datotekama očekuje točku sa zarezom, jer je zarez decimalni znak. Excel s engleskim postavkama očekuje zarez. Ako datoteka koristi onaj drugi znak, sve završi u stupcu A.
  • Format brojeva: Ako izvoz kao decimalni znak koristi točku, npr. 12.05, Excel s njemačkim postavkama od toga napravi datum, 12. svibnja. Obrnuto, 1.234,56 u Excelu s engleskim postavkama ostaje tekst.
  • Predznak: Ovisno o izvozu, minus stoji iza iznosa, npr. 1.250,00-, ili su duguje i potražuje u zasebnom stupcu. Tada Excelu nedostaje predznak koji mu treba za računanje.
  • Zaglavlje iznad tablice: Ako iznad same tablice stoje broj računa, razdoblje ili saldo, Excel prvi od tih redaka smatra naslovnim retkom.
  • Prijelomi redaka: Opis plaćanja s prijelomom retka može jednu transakciju raspodijeliti na dva retka.

Uvoz izvoda u Excel: korak po korak s Power Queryjem

Dvostruki klik tjera Excel da pogađa. Uvoz preko Power Queryja, alata iza naredbe Data > From Text/CSV, pokazuje vam svaku odluku i pamti vaše postavke za sljedeći mjesec. Postupak je sljedeći:

  1. Odaberite datoteku

    Kliknite Data > From Text/CSV, odaberite izvoz i kliknite Import.

  2. Odredite podrijetlo datoteke

    U pregledu provjerite posebne znakove. Ako nisu ispravni, pod File Origin odaberite 65001: Unicode (UTF-8). Prema Microsoftu, Power Query sam prepoznaje UTF-8 samo ako datoteka počinje BOM oznakom.

  3. Odaberite razdjelnik

    Pod Delimiter odaberite znak uz koji datum, tekst i iznos stoje u zasebnim stupcima, zatim kliknite Transform Data.

  4. Uklonite zaglavlje

    Ako iznad tablice stoji zaglavlje, odaberite Home > Remove Rows > Remove Top Rows i upišite broj tih redaka. Zatim kliknite Use First Row as Headers.

  5. Pretvorite iznos i datum

    Desnom tipkom miša kliknite naslov stupca s iznosom, odaberite Change Type > Using Locale, zatim Decimal Number i German (Austria) ili German (Germany) ako izvoz kao decimalni znak koristi zarez. Ako koristi točku, odaberite English (United States). Datum pretvorite na isti način.

  6. Učitajte

    Kliknite Close & Load. Transakcije se pojavljuju kao tablica na novom listu.

Ako Power Query ne može pretvoriti iznos, u ćeliji stoji pogreška umjesto pogrešne vrijednosti. To je namjerno: naredbom Home > Keep Rows > Keep Errors vidite upravo te retke. Ako se jedna transakcija proteže na dva retka, pod Applied Steps otvorite postavke koraka Source i za Line breaks odaberite zanemarivanje prijeloma redaka unutar navodnika.

Sljedeći mjesec preskačete sve korake: spremite novi izvoz pod istim imenom na isto mjesto i kliknite Data > Refresh All ili pritisnite Ctrl+Alt+F5. Power Query ponavlja uvoz s vašim postavkama.

Brzo rješenje kad je sve u stupcu A

Za jednokratan pregled često je dovoljan čarobnjak Text to Columns. Označite stupac A, odaberite Data > Text to Columns > Delimited, označite razdjelnik datoteke i kliknite Finish. Pokvarene posebne znakove čarobnjak ne popravlja, a iznose Excel tumači prema vlastitim postavkama. Za mjesečno usklađivanje zato je put preko Power Queryja sigurniji.

Provjera salda: kako osigurati da nijedan iznos ne nedostaje

Najopasnije mjesto se ne vidi. Prema Microsoftu, SUM zanemaruje tekstualne vrijednosti i zbraja samo brojeve. Ako i jedan iznos stoji u ćeliji kao tekst, nedostaje u svakom zbroju, a Excel ništa ne javlja. Dvije kontrole to isključuju, u primjeru s iznosima u D2 do D413:

  1. Provjera brojeva: =COUNTA(D2:D413)-COUNT(D2:D413) broji popunjene ćelije koje nisu broj. COUNTA broji svaku ćeliju koja nije prazna, COUNT samo brojeve. Rezultat mora biti 0.
  2. Provjera salda: Početni saldo s izvoda plus =SUM(D2:D413) mora dati završni saldo s izvoda. Svaka razlika je iznos koji nedostaje, koji se ponavlja ili je pogrešno preuzet.

Iznose koji stoje kao tekst pretvorite funkcijom NUMBERVALUE: =NUMBERVALUE(D2,",",".") čita zarez kao decimalni znak, a točku kao razdjelnik tisućica, bez obzira na postavke vašeg Excela. Ako minus stoji iza iznosa, ova formula prvo provjerava zadnji znak: =IF(RIGHT(D2,1)="-",-NUMBERVALUE(LEFT(D2,LEN(D2)-1),",","."),NUMBERVALUE(D2,",",".")). Tako 1.250,00- postaje -1250. Ako izvoz vodi duguje i potražuje u zasebnom stupcu, npr. D i P u stupcu E, predznak postavite formulom =IF(E2="D",-D2,D2).

Osoba za stolom tipka na tipkovnici, pokraj nje ispisano izvješće na podlozi s kopčom, kao kod usporedbe salda i zbroja
Fotografija: Vitaly Gariev na Unsplash

PDF izvod iz banke u Excel

Ponekad postoji samo PDF, npr. za starije mjesece ili za račun koji ne vodite sami. Najprije u internetskom bankarstvu provjerite mogu li se prometi za isto razdoblje preuzeti kao CSV. To je čišći izvor, jer se vrijednosti ne moraju čitati iz izgleda stranice.

Ako ne mogu, Excel za Microsoft 365 na Windowsima čita tablice iz PDF datoteka: Data > Get Data > From File > From PDF. U Navigatoru odaberite prepoznatu tablicu, zatim Transform Data. Slične tablice na uzastopnim stranicama Power Query prema zadanim postavkama spaja u jednu tablicu. Zatim uklonite međuretke poput zaglavlja stranica ili prijenosa, pretvorite vrste podataka kao što je gore opisano i napravite provjeru salda. Skenirani izvod bez prepoznavanja teksta sadrži samo sliku, iz nje Excel ne može pročitati tablicu.

Besplatni online pretvarači prenose datoteku na poslužitelj nepoznatog pružatelja. Na poslovnom računu stoje imena i IBAN-ovi kupaca, dobavljača i zaposlenika, dakle osobni podaci. Ako pružatelj usluge obrađuje takve podatke u vaše ime, čl. 28. GDPR-a zahtijeva ugovor o obradi podataka. Riješite to prije nego što prenesete izvod.

Primjer: mjesečni izvod veletrgovca

Provjera salda u primjeru, iznosi u eurima
ProvjeraFormulaRezultat
Početni saldo s izvoda—18.420,35
Zbroj transakcija=SUM(D2:D413)8.655,68
Izračunani završni saldopočetni saldo plus zbroj27.076,03
Završni saldo s izvoda—25.826,03
Razlikaizračunano minus prema izvodu1.250,00
Provjera brojeva=COUNTA(D2:D413)-COUNT(D2:D413)1
Zbroj nakon ispravka=SUM(D2:D413)7.405,68
Izračunani završni saldo nakon ispravkapočetni saldo plus zbroj25.826,03, razlika 0,00

Analiza i razvrstavanje izvoda u Excelu

Kad je izvod uredno u Excelu, obično želite znati kamo je otišao novac: najam, dobavljači, plaće, uplate kupaca. Ručno razvrstavanje svakog retka traje. Brže ide s malom tablicom ključnih riječi koju jednom postavite i svaki mjesec ponovno koristite.

  1. Postavite tablicu ključnih riječi

    U F2 do F6 upišite ključne riječi koje se pojavljuju u opisu plaćanja, npr. najam, plaća ili ime dobavljača, a u G2 do G6 odgovarajuću kategoriju. Popunite svaki redak, jer prazan redak može odgovarati bilo kojem tekstu.

  2. Pronađite kategoriju formulom

    Ako je opis plaćanja u stupcu C, u E2 upišite: =XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$6,C2)),$G$2:$G$6,"Otvoreno"). SEARCH pronalazi ključnu riječ bilo gdje u tekstu i ne razlikuje velika i mala slova. XLOOKUP vraća kategoriju prve ključne riječi koja odgovara, a ako nema pogotka, ondje stoji Otvoreno.

  3. Provjerite otvorene retke

    Filtrirajte po vrijednosti Otvoreno i te transakcije razvrstajte ručno. Ako se neki primatelj ili platitelj pojavljuje svaki mjesec, dodajte ga u tablicu ključnih riječi.

  4. Izračunajte zbrojeve

    Zbroj po kategoriji daje =SUMIF($E$2:$E$413,"Najam",$D$2:$D$413). Za pregled svih kategorija i mjeseci prikladna je zaokretna tablica (PivotTable).

XLOOKUP postoji u Microsoftu 365 i u Excelu 2021 ili novijem. Budući da pobjeđuje prva ključna riječ koja odgovara, precizne ključne riječi idu na vrh, a općenite na dno. Kategorija je prijedlog za vašu analizu i ne zamjenjuje knjiženje u knjigovodstvu.

Tipične pogreške s izvodom u Excelu

  • Prepisivanje izvoza: Izvornu datoteku banke čuvajte nepromijenjenu, a svoj rad spremite u zasebnu Excel datoteku. Tako u svakom trenutku možete ponovno uvesti podatke.
  • Ručna zamjena posebnih znakova: To traje i uvijek promakne neko mjesto. Ispravno postavite skup znakova pri uvozu ili prethodno popravite datoteku.
  • Slijepo povjerenje u zbroj: Bez provjere brojeva ne primijetite da jedan iznos stoji kao tekst i nedostaje u zbroju.
  • Iznosi postaju datumi: Izvoz s točkom kao decimalnim znakom, otvoren dvostrukim klikom u Excelu s njemačkim postavkama, od 12.05 napravi 12. svibnja. Uvozite s regionalnim postavkama.
  • Prisilno razvrstavanje: Formula bez rezultata Otvoreno gura nejasne transakcije u bilo koju kategoriju. Neka nejasni slučajevi ostanu vidljivi.
  • Prijenos izvoda na online pretvarače: Bez ugovora o obradi podataka osobne podatke predajete trećoj strani.

Trud, granice i put koji dugoročno funkcionira

Jednom postavljen, uvoz preko Power Queryja većinu posla obavi jednim klikom na Refresh All. Posla ipak ostaje: preuzimanje izvoza, provjera kategorija, provjera salda, predaja knjigovodstvu, i to za svaki račun. Oprezan izračun: 2 sata mjesečno su 24 sata godišnje. Uz pretpostavljenih 38 € internog troška po satu, dakle plaća plus dodatni troškovi kao što je zadano u kalkulatoru troškova, to je 912 € godišnje za jedan račun. S vlastitim brojkama računate u kalkulatoru troškova uredskog rada.

Prethodno provjerite može li vaš knjigovodstveni program izravno učitati bankovnu datoteku. Tada knjiženju ne treba zaobilazni put preko Excela, a Excel ostaje za analizu i usklađivanje. Ako datoteka za to mora biti u određenom obliku, Popravak CSV datoteke dovodi skup znakova, razdjelnik i decimalni znak u odgovarajući oblik. Kako podatke općenito uredno prenijeti iz jednog programa u drugi, pokazuje članak Prijenos podataka između programa.

Pomoćnik, odnosno mali program koji postavljamo upravo za ovaj zadatak, preuzima izvoz, čisti ga, razvrstava transakcije prema vašim pravilima, radi provjeru salda i nejasne transakcije označene daje vašem timu. Rezultat je datoteka za uvoz u BMD ili DATEV ili vaša analiza u Excelu. Zadržavate svoje programe i odobravate prije nego što išta ide dalje. Više o tome na stranici Automatizacija prijenosa podataka.

Prolazi li kod vas svaki mjesec više bankovnih računa kroz Excel? Napišite nam u besplatnoj Provjeri procesa koje banke, koje programe i kakvu analizu trebate. U roku od 1–2 radna dana dobivate osobnu, besplatnu procjenu. Male projekte nakon usklađivanja procesa provodimo za 3–7 dana, po fiksnoj cijeni od 1.990 € bez PDV-a.

Izvori i dodatne poveznice

ČESTA PITANJA

Kratki odgovori.

Kako PDF izvod iz banke prebaciti u Excel?

U Excelu za Microsoft 365 na Windowsima naredbom Data > Get Data > From File > From PDF. U Navigatoru odaberite prepoznatu tablicu, uklonite zaglavlja stranica i prijenose te iznose pretvorite u brojeve. Čišći izvor obično je CSV izvoz iz internetskog bankarstva za isto razdoblje. Skenirane izvode bez prepoznavanja teksta Excel ne može pročitati.

Zašto je izvod u Excelu u jednom stupcu?

Zato što datoteka koristi drukčiji razdjelnik od onoga koji vaš Excel očekuje. Excel s njemačkim ili austrijskim postavkama u CSV datotekama očekuje točku sa zarezom, Excel s engleskim postavkama zarez. S pogrešnim znakom sve završi u stupcu A. Uvezite datoteku naredbom Data > From Text/CSV i ondje sami odaberite razdjelnik.

Zašto su posebni znakovi u izvodu pokvareni?

Najčešće je datoteka spremljena kao UTF-8, ali bez oznake na početku po kojoj Excel to prepoznaje. Excel je tada čita u starom Windows skupu znakova, pa ü postaje ü. Pri uvozu naredbom Data > From Text/CSV postavite File Origin na 65001: Unicode (UTF-8) ili prethodno popravite datoteku alatom.

Kako razvrstati transakcije s izvoda u Excelu?

Tablicom ključnih riječi i jednom formulom. Ključne riječi su u F2 do F6, kategorije u G2 do G6, opis plaćanja u stupcu C. Tada =XLOOKUP(TRUE,ISNUMBER(SEARCH($F$2:$F$6,C2)),$G$2:$G$6,"Otvoreno") vraća kategoriju ili Otvoreno, a dostupna je u Microsoftu 365 i Excelu 2021 ili novijem. Transakcije s oznakom Otvoreno razvrstajte ručno.

Kako provjeriti je li pri pretvaranju nestao neki iznos?

S dvije kontrole. =COUNTA(D2:D413)-COUNT(D2:D413) mora dati 0, inače barem jedan iznos stoji kao tekst, a njega SUM zanemaruje. Osim toga, početni saldo plus zbroj svih transakcija mora dati završni saldo s izvoda.

Smijem li izvode prenijeti u besplatni online pretvarač?

Na poslovnim računima stoje imena i IBAN-ovi kupaca, dobavljača i zaposlenika, dakle osobni podaci. Ako ih pružatelj obrađuje u vaše ime, čl. 28. GDPR-a zahtijeva ugovor o obradi podataka. Bez takvog ugovora bolje je koristiti uvoz u Excelu ili alat kod kojeg datoteka ostaje na vašem računalu.

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.