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

Automatizacija Excela: kako birati između formule, Power Queryja, makronaredbe i pomoćnika

Ponedjeljak ujutro, ista datoteka kao svaki tjedan. Kopirati stupce, povući VLOOKUP prema dolje, razlike označiti žutom bojom, rezultat poslati e-poštom. Na kraju ostaje neugodan osjećaj: je li ovaj put obuhvaćen baš svaki redak? A makronaredbu koja je trebala pomoći razumije samo jedna kolegica, i ona je upravo na godišnjem odmoru. Želite automatizirati Excel, ali ne znate čime početi.

Zaposlenica radi na Excel tablici na prijenosnom računalu, automatizacija Excela u svakodnevnom uredskom radu
Fotografija: Rodrigo Rodrigues | WOLF Λ R T na Unsplash

To košta više od sata ponedjeljkom. Previđena cijena ili formula koja seže samo do retka 500 često se primijeti tek tjednima kasnije, kada je ispravak znatno skuplji.

U ovom članku saznat ćete koji od četiri alata odgovara vašem zadatku: formula, Power Query, makronaredba ili Office Script te vlastiti pomoćnik. Za svaki alat navodimo verzije, licence i granice, kako biste odabrali najjednostavnije rješenje koje dugoročno funkcionira.

Ukratko: čime automatizirati Excel

  1. Formula

    Kada vrijednosti unutar jedne datoteke treba potražiti, usporediti ili izračunati. Primjer: staru i novu cijenu staviti jednu uz drugu pomoću funkcije XLOOKUP.

  2. Power Query

    Kada se podaci redovito učitavaju iz datoteka ili mapa, čiste i spajaju. Jedan klik na „Osvježi sve“ ponavlja sve korake.

  3. Makronaredba ili Office Script

    Kada u Excelu treba ponavljati radnje: oblikovati, dodavati listove, spremati datoteke, izrađivati izvještaje.

  4. Pomoćnik

    Kada zadatak izlazi izvan Excela: podaci stižu e-poštom, prijenos u ERP ili računovodstvo, posebni slučajevi koji ciljano stižu do jedne osobe i tijek koji pouzdano radi, a da se nitko ne mora sjetiti pokrenuti ga.

Osnovno pravilo: počnite s najjednostavnijom razinom koja vaš zadatak rješava u potpunosti. Svaka viša razina može više, ali traži i više održavanja. Tko prerano posegne za makronaredbom, često samo zamijeni tjedni ručni rad novom ovisnošću.

Razina 1: kada je dovoljna formula?

Mnoge ponavljajuće zadatke rješava već dobra formula. XLOOKUP traži vrijednost, primjerice šifru artikla, u drugom popisu i vraća odgovarajuću cijenu. Za razliku od funkcije VLOOKUP, XLOOKUP ne treba fiksni broj stupca, prema zadanim postavkama traži točno podudaranje i može vratiti tekst ako ništa ne pronađe. VLOOKUP bez argumenta FALSE na kraju traži približno podudaranje, a to na nesortiranim popisima neprimjetno daje pogrešne rezultate.

Primjer: =XLOOKUP(A2,Cjenik!A:A,Cjenik!C:C,"nema") za šifru artikla u ćeliji A2 dohvaća cijenu iz cjenika i upisuje „nema“ ako te šifre ondje nema. Uz hrvatske regionalne postavke argumente odvajate točkom sa zarezom. Prema Microsoftu, XLOOKUP postoji u sustavu Microsoft 365, u Excelu 2021 i Excelu 2024, za Windows i Mac. Excel 2019 i 2016 nemaju tu funkciju, ondje koristite VLOOKUP s argumentom FALSE ili kombinaciju INDEX i MATCH.

Kako spojiti dva popisa preko ključnog stupca, korak po korak pokazuje članak usporedba popisa u Excelu.

Granice: formule rade samo s podacima koji su već u datoteci. Novi popis dobavljača netko mora kopirati u datoteku, a upravo se pri tom kopiranju događa najviše pogrešaka. Formule ne šalju e-poštu, ne spremaju datoteke i ništa ne prenose u druge programe.

Razina 2: automatizacija Excela bez makronaredbi, uz Power Query

Power Query je alat u Excelu koji učitava i preoblikuje podatke bez programiranja. Nalazi se na kartici Podaci u grupi Dohvaćanje i pretvorba podataka. Svaki vaš korak se sprema: preimenovanje stupca, uklanjanje praznih redaka, određivanje vrste podataka, spajanje dviju tablica. Sljedeći put kliknete „Osvježi sve“ i Excel ponovi sve korake s novim podacima. Umjesto sata kopiranja ostaje jedan klik, a rezultat nastaje svaki put na isti način.

  • Spajanje mape: sve mjesečne datoteke iz jedne mape spojite u jednu tablicu. Ako vam to treba samo povremeno, naš besplatni alat spajanje Excel datoteka slaže više datoteka jednu ispod druge bez upita i povezuje stupce prema naslovu.
  • Čišćenje: šifre artikala tretirati kao tekst kako bi vodeće nule ostale, uklanjati razmake, ujednačiti nazive stupaca.
  • Usporedba: dva popisa spojiti preko ključnog stupca i pronaći retke koji postoje samo u jednom popisu. Više o tome u članku kako pronaći zapise koji nedostaju.

Prema Microsoftu, Power Query postoji u Excelu za Windows od verzije 2016 i u sustavu Microsoft 365, u Excelu za web te u Excelu za Mac uz Microsoft 365. U Excelu 2016 i 2019 za Mac ga nema. Na Macu i u pregledniku osim toga nisu dostupni svi izvori podataka iz verzije za Windows, u pregledniku Power Query učitava datoteke primjerice iz OneDrivea ili SharePointa. Provjerite to prije nego što izradite upit za cijeli tim.

Granice: Power Query čita podatke, ali ih ne zapisuje natrag u druge programe. Osvježavanje radi samo dok je datoteka otvorena u Excelu: na klik, pri otvaranju ili u zadanim razmacima. A kada dobavljač iznenada drukčije nazove stupce, upit se prekida s porukom o pogrešci i treba ga prilagoditi. To je naporno, ali bolje od rezultata koji je neprimjetno pogrešan.

Razina 3: makronaredba ili Office Script, u čemu je razlika?

Makronaredba je mali program u Excelu koji ponavlja radnje. Klasične makronaredbe pisane su u VBA, programskom jeziku sustava Microsoft Office. Snimačem makronaredbi snimite svoje klikove i kasnije ih ponovno pokrenete, bez pisanja koda. Za sve što nadilazi jednostavan niz klikova netko mora prilagoditi kod. To štedi mnogo ručnog rada, dok god je dostupna osoba koja kod razumije.

VBA makronaredbe: moćne, ali vezane uz računalo

VBA radi u Excelu za Windows i Mac, ne u Excelu u pregledniku, i ne treba posebnu licencu. Prema Microsoftu, makronaredbe imaju ista prava kao sam Excel, dakle puni pristup računalu. Upravo se zato često zloupotrebljavaju za zlonamjerni softver. Microsoft od 2022. u aplikacijama Microsoft 365 za Windows prema zadanim postavkama blokira makronaredbe u datotekama s interneta, primjerice u privicima e-pošte ili preuzimanjima. Umjesto gumba „Omogući sadržaj“ tada se pojavljuje upozorenje o sigurnosnom riziku. Datoteke kojima vjerujete vaš IT ciljano odobrava, primjerice preko pouzdanih lokacija ili potpisanih makronaredbi. Radne knjige s makronaredbama spremate u formatu .xlsm.

Office Scripts: zašto se kartica „Automatiziraj“ ne prikazuje?

Office Scripts su novija varijanta. Nalaze se na kartici Automatiziraj. I ovdje se snima klikom, a kod u pozadini je TypeScript. Skripte rade u Excelu za web, Windows i Mac te se preko alata Power Automate mogu pokretati po rasporedu, i kada nitko ne otvori Excel. Imaju pristup samo radnoj knjizi, ne i računalu.

Ako kartica „Automatiziraj“ nedostaje, razlog je obično licenca: prema Microsoftu, Office Scripts traže pretplatu Microsoft 365 za tvrtke ili obrazovne ustanove, OneDrive for Business i, na Windowsima, Excel od verzije 2210. Trajne licence bez pretplate ih nemaju, a osobne i obiteljske pretplate dobivaju ih samo kao pretpregled kroz program Microsoft 365 Insider. Ako je licenca u redu, IT je često isključio tu funkciju. U Excelu u pregledniku karticu ponekad blokiraju postavke kolačića.

Službenica za računalom s dokumentima i kalkulatorom na stolu, ponavljajući uredski posao u Excelu
Fotografija: TECNIC Bioprocess Solutions na Unsplash

Granice: makronaredbe i skripte prestaju raditi kada se promijeni struktura datoteke. Obrada pogrešaka često nedostaje, pa makronaredba jednostavno nastavlja s pogrešnim podacima. I često samo jedna osoba u tvrtki razumije što kod radi. Kada je ona na odmoru ili više nije u tvrtki, posao stoji, a nitko se ne usuđuje ništa promijeniti.

Četiri razine u usporedbi

Automatizacija Excela: formula, Power Query, makronaredba, Office Script i pomoćnik
KriterijFormulaPower QueryMakronaredba (VBA)Office ScriptPomoćnik
Tipičan zadatakTraženje, usporedba, izračunUčitavanje, čišćenje i spajanje datotekaRadnje u Excelu, izrada izvještajaRadnje u Excelu, i po rasporeduTijek kroz više programa s koracima provjere
Znanje programiranjaNije potrebnoNije potrebno ili vrlo maloSnimanje bez, prilagodba uz VBASnimanje bez, prilagodba uz TypeScriptNije potrebno, izrađujemo ga za vas
Dostupno uSvim verzijama, XLOOKUP od Excela 2021 i u sustavu Microsoft 365Windows od Excela 2016, web, Mac uz Microsoft 365Excel za Windows i Mac, ne u preglednikuWeb, Windows, Mac uz poslovnu licencu Microsoft 365Neovisno o verziji Excela
Pokreće se bez klikaPreračunava se pri promjenamaSamo u otvorenoj datoteci, pri otvaranju ili u intervaluNe, netko ga pokreće u ExceluDa, preko alata Power AutomateDa, po rasporedu ili kada podaci stignu
E-pošta, ERP, računovodstvoNeSamo čitanjeMoguće, ali zahtjevnoPreko alata Power AutomateDa, preko sučelja i uvoza
OdržavanjeMaloMalo do srednjeČesto ovisi o jednoj osobiSrednjeDokumentirano i dogovoreno

Primjer: tjedni cjenik dobavljača

Kako pouzdano prepoznati promjene cijena između dviju verzija cjenika, opisuje članak usporedba cjenika dobavljača. Ako razine uopće ne želite graditi: naša besplatna usporedba Excel datoteka prikazuje nove, nedostajuće i promijenjene retke dvaju popisa, a usporedba cjenika prikazuje svaku promjenu cijene u postocima. Oba alata rade u pregledniku, datoteke se ne prenose na poslužitelj.

Koje se pogreške najčešće događaju pri automatizaciji u Excelu?

  • Početi s makronaredbom. Često su dovoljni formula ili Power Query, a oboje je lakše razumjeti i održavati.
  • VLOOKUP bez argumenta FALSE i s fiksnim brojem stupca. Bez FALSE VLOOKUP traži samo približno. Ako netko umetne stupac, formula tiho dohvaća vrijednosti iz pogrešnog stupca. XLOOKUP ili INDEX i MATCH su pouzdaniji.
  • Rasponi umjesto tablica. Formule i upiti ne obuhvaćaju nove retke ako podaci nisu oblikovani kao tablica.
  • Nema testiranja s posebnim slučajevima. Prazni retci, šifre artikala s vodećim nulama, brojevi spremljeni kao tekst: upravo tu automatizacije zakazuju.
  • Nema dokumentacije. U dvije, tri rečenice zapišite što datoteka radi, odakle dolaze podaci i tko ih održava. Tada za vrijeme godišnjeg odmora može preuzeti i netko drugi.
  • Omogućavanje makronaredbi iz privitaka. Makronaredbe omogućite samo u datotekama čije podrijetlo poznajete. U slučaju sumnje pitajte svoj IT.

Kada je pomoćnik bolji izbor od makronaredbe?

Pomoćnik je mali program izrađen po mjeri koji preuzima točno jedan ponavljajući zadatak. Koristi vaše postojeće programe, sučelja i uvoze. Nejasni slučajevi idu vašem timu, koji provjerava i odobrava rezultate. Vi zadržavate kontrolu, otpada samo rutina. To se posebno isplati kada:

  • podaci stižu e-poštom ili kao PDF, a ne nalaze se već u Excelu,
  • rezultat mora ići u drugi program, primjerice u ERP ili u računovodstvo s programom BMD ili RZL (Austrija) ili DATEV (Njemačka),
  • posebni slučajevi trebaju ciljano stići do jedne osobe, umjesto da tiho prođu,
  • tijek treba raditi i kada osoba koja zna kako makronaredba radi nije prisutna,
  • vaš IT iz sigurnosnih razloga ograničava makronaredbe.

Koliko košta ostaviti sve kako jest? Jednostavan izračun s pretpostavljenim vrijednostima: jedan sat svakog ponedjeljka, 46 radnih tjedana godišnje i 38 € internih troškova po satu, uključujući dodatne troškove, daju 46 sati i oko 1.750 € godišnje. Pogreške, dodatna pitanja i zamjena za vrijeme godišnjeg odmora nisu uključeni. Izračunajte vlastite brojke u kalkulatoru troškova uredskog rada. Uz jedan sat tjedno često su dovoljni formula ili Power Query. Od nekoliko sati tjedno ili kod skupih pogrešaka isplati se razmotriti pomoćnika, više o tome u članku o povratu ulaganja u automatizaciju. Što to konkretno znači za popise i usporedbe cijena, opisuje stranica automatizacija rada u Excelu.

Već iz kratkog opisa često se vidi koja razina odgovara vašem zadatku. U Provjeri procesa opišite koje datoteke obrađujete, koliko često i tko o njima ovisi. U roku od 1–2 radna dana dobit ćete osobnu, besplatnu procjenu: formula, Power Query, makronaredba ili pomoćnik. Za pomoćnika navodimo fiksnu cijenu od 1.990 € bez PDV-a, a male projekte nakon dogovora obično provodimo za 3–7 dana. Obvezujuća je tek ponuda koju prihvatite.

Izvori i dodatne poveznice

ČESTA PITANJA

Kratki odgovori.

Može li se Excel automatizirati bez makronaredbi?

Da. Mnoge ponavljajuće zadatke rješavaju formule poput funkcije XLOOKUP ili Power Query, koji učitava podatke iz datoteka i mapa, čisti ih i osvježava na klik. Ni jedno ni drugo ne traži kod ni odobrenje makronaredbi. Uz poslovnu licencu Microsoft 365 postoje i Office Scripts, koji preko alata Power Automate rade čak i po rasporedu.

Zašto se kartica „Automatiziraj“ u Excelu ne prikazuje?

Kartica pripada alatu Office Scripts i prema Microsoftu se prikazuje samo uz pretplatu Microsoft 365 za tvrtke ili obrazovne ustanove s uslugom OneDrive for Business, na Windowsima od verzije 2210. Osobne i obiteljske pretplate dobivaju je samo kao pretpregled kroz Microsoft 365 Insider. Ako je licenca u redu, IT je često isključio tu funkciju. U pregledniku uzrok mogu biti postavke kolačića.

Koja je razlika između makronaredbe i Power Queryja?

Power Query učitava i preoblikuje podatke, primjerice iz datoteka i mapa, i pri svakom osvježavanju ponavlja spremljene korake. Makronaredba izvodi radnje u Excelu, primjerice oblikovanje, kopiranje ili spremanje. Za pripremu podataka Power Query je obično bolji izbor: ne traži kod i blokada makronaredbi na njega ne utječe.

Mogu li Excel automatizirati pomoću Pythona?

Djelomično. Python u Excelu koristite preko funkcije =PY. Prema Microsoftu, dostupan je u sustavu Microsoft 365 za tvrtke, a za osobne i obiteljske pretplate kao pretpregled. Trajne licence bez pretplate ga ne podržavaju. Kod se izvršava u Microsoftovu oblaku bez pristupa mreži i radi samo s ćelijama radne knjige i podacima iz Power Queryja. Za tijekove izvan Excela, primjerice e-poštu ili računovodstvo, potreban je zaseban program.

Može li Copilot automatizirati Excel?

Prema Microsoftu, Copilot pomaže pri izradi formula, grafikona i zaokretnih tablica. No Microsoft savjetuje da svaki rezultat provjerite prije upotrebe. Za tijek koji svaki tjedan radi jednako i provjerljivo, temelj ostaju spremljena formula, upit ili skripta. Koje funkcije Copilota možete koristiti, ovisi o vašoj pretplati.

Jesu li makronaredbe u Excelu sigurne?

Makronaredbe su sigurne onoliko koliko je sigurno njihovo podrijetlo. Imaju puni pristup računalu i zato se koriste i za zlonamjerni softver. Microsoft na Windowsima prema zadanim postavkama blokira makronaredbe u datotekama s interneta, primjerice u privicima e-pošte. Makronaredbe omogućite samo u datotekama čije podrijetlo poznajete, a u slučaju sumnje pitajte svoj IT.

Kada se umjesto makronaredbe isplati pomoćnik?

Kada zadatak izlazi izvan Excela: podaci stižu e-poštom ili kao PDF, prijenos u ERP ili računovodstvo, koraci provjere i posebni slučajevi. I kada tijek ne smije ovisiti o jednoj osobi. Pomoćnik košta od 1.990 € bez PDV-a. Obvezujuću fiksnu cijenu dobit ćete nakon besplatne procjene u Provjeri procesa.

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.