EXCEL FUNKCIJE i

POWER (EXCEL) QUERY

Suzana Marković, ovlašćeni ECDL trener
 SADRŽAJ

• Tekstualne funkcije
• Validacija podataka
• Logičke funkcije
• Statističke funkcije
• Funkcije za pretragu
• Power Excel – Power Query
 SINTAKSA FORMULA
Sve formule u Excel‐u počinju
znakom “=“ i mogu se izabrati iz
biblioteke formula ili direktno
otkucati unutar formula bar‐a.

Čim se započne za unosom formule, iskočiće vodič koji će prikazati sve stavke formule, a trenutno aktivna biće boldovana

A1 A1:C4 A1;C4
Referenca jedne ćelije opisuje lokaciju Referenca niza (grupe) opisuje Nekontinualna referenca opisuje
ćelije u radnom listu, kao presek odgovarajuće kontinualnu grupu ćelija na osnovu selekciju individualnih ćelija koje
kolone (od A do XFD) i reda (od 1 do lokacije najviše leve ćelije (A1) i nemaju zajedničke granice, odvojene
1.048.576). poslenje desne (C4) ćelije, odvojeno znakom “,” ili “;”.
znakom “:”.

3
 VRSTE REFERENCI
Fiksne, relativne ili mešovite reference; znak $ zaključava određenu ćeliju ili referencu tako da se ona
ne menja ako se formula primeni na ostale ćelije.

Na primer:
$A$1 = fiksna kolona, fiksni red
A$1 = relativna kolona, fiksni red
$A1 = fiksna kolona, relativni red
A1 = relativna kolona, relativni red

Selektuj deo formule i pritisni

taster “F4”. Uoči promenu
reference!
A1
A$1 $A$1
$A1 4
 CTRL TASTER
CTRL taster se može kombinovati sa mnogim tasterima, npr:

1) CTRL‐ STRELICA
Skače na levu, desnu, gornju ili donju, krajnju nepraznu CTRL‐SHIFT‐RIGHT ARROW
ćeliju neprekidnog niza podataka.

2) CTRL‐SHIFT‐STRELICA
Selektuje levu, desnu, gornju ili donju, krajnju CTRL‐SHIFT‐DOWN ARROW

nepraznu ćeliju neprekidnog niza podataka.

3) CTRL‐PAGE UP/DOWN
Skače sa jednog na drugi radni list excel
fajla

8
 EXCEL GREŠKE

Tip greške Značenje Kako je ispraviti?

Povucite ili dvaput kliknite na granicu stuba da biste povećali širinu
###### Kolona nije dovoljno široka da prikaže vrednosti ili kliknite desnim tasterom da biste postavili širinu stupca kolona

Uverite se da su imena funkcija ispravna, da su reference ispravne i

#NAME? Excel ne raspoznaje tekst u formuli
pravilno napisane i da postoje navodnici u kolonama gde treba da ih ima.

Proverite da vaša formula ne pokušava da izvrši aritmetičku operaciju na

#VALUE! Formula ima pogrešan tip argumenta
tekstualnim nizovima ili ćelijama formatiranim kao tekst.

Proverite vrednost delioca; ako je 0 koristite IF izraz za prikaz

#DIV/0! U formuli se pojavljuje deljenje nulom alternativne vrednosti

Uverite se da niste premestili, obrisali ili zamenili ćelije koje su

#REF! Formula ukazuje na ćeliju koja nije validna
navedene u vašoj formuli.

Formula ne može da nađe vrednosti i izračuna Uverite se da vrednosti zaista ne postoje ili koristite obrađivač
#N/A traženo grešaka – funkcija IFERROR.

5
 IFERROR ISKAZ
IFERROR iskaz je alat koji eliminiše dosadne poruke o grešci kao što su:
#N/A, #DIV/0!, #REF!, …

=IFERROR(vrednost, vrednost_ako_je_greška)
Formula ili vrednost (koja može, ali ne mora rezultirati greškom) Povratna vrednost u slučaju greške

U 1. primeru greška koja se pojavi

prilikom deljenja brojeva A1/B1 zamenjena =IFERROR(A1/B1,“Pogrešna formula")
je u formuli sa “Pogrešna formula”, a u 2.
primeru u funkciji VLOOKUP greška je =IFERROR(VLOOKUP(A1,D1:E4,2,0),"‐")
zamenjenja sa “‐”

Prilikom pisanja formule koja može da rezultira greškom, savet je najpre napisati celu formulu a onda je upakovati
u IFERROR iskaz.

23
 TEKSTUALNE FUNKCIJE

Neke funkcije za rad sa tekstom mogu se koristiti za formatiranje teksta

i to funkcije: UPPER, LOWER, PROPER i TRIM.

Ako se dva tekstualna stringa razlikuju samo po tome što jedan ima više razmaka, za korisnika
programa oni mogu izgledati identično, ali će ih Excel tretirati kao potpuno dve različite vrednosti.
Funkcija TRIM će ih izjednačiti.

8
TEKSTUALNE FUNKCIJE

Iz kolone N iskopiramo podatke (Ctrl+C) i pomoću opcije

Paste Values zalepimo ih preko podataka u koloni A.

9
 TEKSTUALNE FUNKCIJE

Funkcije LEFT, MID i RIGHT kao rezultat daju specifičan broj karaktera
tekstualnog stringa, a LEN daje njegov ukupan broj karaktera.
=LEFT(tekst; [br_karak])
=RIGHT(tekst; [br_karak]) =SUBSTITUTE(A14;"‐";"|";2)
Menja određeni karakter na poziciji 2 iz ćelije A14 novim
=MID(tekst; počet_ br; br_karak)

10
 SREĐIVANJE PODATAKA

Analiza podataka često zahteva njihovo prethodno sređivanje:

Neuredni podaci ‐ pogrešno otkucane reči, višestruki razmaci, neželjeni prefiksi,
neodgovarajuća velika ili mala slova, znakovi koji se ne štampaju itd.

Posle uvoza podataka iz spoljnog izvora (tekstualna datoteka, baza

podataka, veb) vrši se:
‐ deljenje jedne kolone u dve ili više njih (TEXT TO COLUMNS) ili
‐ objedinjavanje dve ili više kolona u jednu (upotreba znaka &).

11
 DELJENJE KOLONA

1) Selektuj kolonu za deljenje

2) Pritisni Text to Columns i isprati korake

3) Označi delimitere (npr. tab, razmak,…)
4) Na kraju obavezno upiši destinaciju
izdeljenih podataka.

12
 OBJEDINJAVANJE VIŠE KOLONA U JEDNU

Povezivanje kolone vrši se primenom odgovarajuće formule za

povezivanje ćelija tabele.
Znak za povezivanje je „&“.
Za povezivanje sadržaja ćelija A2 (npr. Ana) i B2 (npr. Mirić) sa
razmakom između, koristi se formula:
A2&" "&B2
Rezultat će biti ispisan u jednoj ćeliji (Ana Mirić)

13
 TEKSTUALNE FUNKCIJE

CONCATENATE dozvoljava kombinovanje teksta, vrednosti ćelija, ili

rezultata formula u jedan tekstualni string.
Umesto korišćenja formule “=CONCATENATE(Text1, Text2…)”,
praktičnije je za svaki segment rezultujućeg stringa koristiti znak
ampersend (“&”).

14
 BRISANJE DUPLIKATA

Uklanjanje duplih podataka u tabelama koje sadrže veliki broj podataka

može se izvršiti primenom opcije Remove Duplicates menija Data.

15
 VALIDACIJA PODATAKA

Sprečava unos neispravnih

Definiše ograničenja u vezi sa podataka (pojavljuje se
podacima koji se unose u ćeliju upozorenje)

Obeležava prethodno unete

Poruka u vidu uputstva podatke koji ne ispunjavaju
za ispravljanje grešaka 16
kriterijum validnosti
 LOGIČKE FUNKCIJE

=IF(logički_test; [vrednost ako je tačno]; [vrednost ako je netačno])

Bilo koji test koji rezultira Vrednost ako je logički Vrednost ako je logički
vrednošću TRUE ili FALSE test TRUE test FALSE

17
 STATISTIČKE FUNKCIJE
COUNTIF, SUMIF, i AVERAGEIF formule računaju sumu, broj ili prosek na osnovu
određenih kriterijuma
=COUNTIF(opseg; kriterijum)
=SUMIF(opseg; kriterijum; suma_opsega)
=AVERAGEIF(opseg; kriterijum; prosek_opsega)

Koje ćelije treba da Pod kojim uslovima želim Gde su vrednosti

odgovaraju vašim da sabiram, brojim ili koje želim da
kriterijumima? nalazim prosek? sabiram ili nalazim
prosek?

COUNTIF(B2:B20;22) = 2
SUMIF(A2:A20,“Ryan”;B2:B20) = 190
SUMIF(A2:A20,“<>Tim”;B2:B20) = 702
AVERAGEIF(A2:A20,“Maria”;B2:B20) = 45.75

29
 STATISTIČKE FUNKCIJE
COUNTIFS, SUMIFS, i AVERAGEIFS koriste se kada želite da izračunate
broj, sumu ili prosek na osnovu višestrukih uslova (kriterijuma).
=COUNTIFS(uslov_opseg1; uslov1; uslov_opseg2; uslov2…)
=SUMIFS(suma_opsega; uslov_opseg1; uslov1; uslov_opseg2; uslov2…)
=AVERAGEIFS(prosek_opsega; uslov_opseg1; uslov1; uslov_opseg2; uslov2…)

COUNTIFS(B2:B13;“Search”; D2:D13;“>200”) = 3
SUMIFS(D2:D13; A2:A13;“Feb”;B2:B13;“Display”) = 734
AVERAGEIFS(D2:D13; A2:A13;“Jan”;C2:C13;“MSN”) = 263

Ako koristite znak manje < ili znak veće >,

morate dodati navodnike (npr. “>200”)

30
 FUNKCIJE ZA PRETRAGU
Najpoznatija funkcija u Excelu za pretragu – VLOOKUP:

=VLOOKUP(vrednost_pretrage, tabela_za pretragu, kol_indeks_br, [opseg_pretrage])

Ovo je vrednost koju Ovo je mesto gde Koja kolona Traži se tačna vrednost
pokušavate da nađete se traži vrednost sadrži podatke koji (0), ili nešto slično (1)?
u tabeli opsega „lookup value" se traže?

D2=VLOOKUP(A2; $G$1:$H$5; 2; 0)

Za izračuvananje cene u koloni

D, tražimo naziv proizvoda u
opsegu od G1:H5 i vraćamo
vrednost iz 2.. kolone.

32
 FUNKCIJE ZA PRETRAGU
Koristiti HLOOKUP ako je tabela transponovana (vrednosti neke promenljive su u jednom
redu, a ne u koloni)

=HLOOKUP(vrednost_pretrage, tabela_za pretragu, red_indeks_br, [opseg_pretrage])

Ovo je vrednost koju Ovo je mesto gde Koji red sadrži Traži se tačna vrednost
pokušavate da nađete se traži vrednost podatke koji se (0), ili nešto slično (1)?
u tabeli opsega „lookup value" traže?

D2=HLOOKUP(A2; $H$1:$L$2; 2; 0)
Pomoću HLOOKUP-a, tražimo naziv proizvoda u
opsegu H1:L2 a povratna vrednost je u 2. redu

33
 FUNKCIJE ZA PRETRAGU
Postoje dva ključna pravila koja ograničavaju VLOOKUP i HLOOKUP funkcije:

1) Vrednost koja se traži mora biti u prvoj koloni VLOOKUP tabele ili u prvom redu HLOOKUP
tabele
2) Excel će uvek vratiti vrednost iz najvišeg reda ili krajnje leve kolone tabele opsega ukoliko
postoje višestruke "lookup“ vrednosti.

34
 KREIRANJE PIVOT
KREIRANJE PIVOT TABELE
TABELE

“Insert” meni --> PivotTable za kreiranje praznog Pivot-a ili

opcija Recommended PivotTables za unapred definisane
početne tabele.

Koje podatke
ćete analizirati?

Gde će Pivot
tabela biti?

(Insert  PivotTable) (Insert  Recommended PivotTables)

 LISTALISTA
POLJA (FIELD
POLJA (FIELDLIST)
LIST)

Layout opcije omogućavaju

podešavanje izgleda liste polja
Field List prikazuje sve
promenljive iz skupa
podataka koje su trenutno
uključene u Pivot tabelu;
kolone početne tabele

Promenljive u delu Columns

Ukoliko želite da filtrirate pojavljuju se kao posebne kolone
tabelu po nekom polju odvucite Pivot tabele
ga u deo Filters

Numeričke vrednosti se uvek

pojavljulju u polju Values
Promenljive u delu Rows
pojaviće se kao posebni
redovi u Pivot tabeli
 POLJA
POLJA(FIELDS) PIVOT
(FIELDS) PIVOT TABELETABELE

• Excel na osnovu formata ćelije dodeljuje polje redu (Row) ili

vrednosti (Value)
• Tekstualni format - redovi
• Numerički podatak - oblast za vrednosti
• Ručno dodeljivanje
• klik pored imena polja
• prevlačenje
KREIRANJE GOOGLE
POLJA (FIELDS) TABELE
PIVOT TABELE

Iskopiramo sadržaj iz radnog lista

Fizička lica u Google tabelu i
preimenujemo radni list.
 KREIRANJE GOOGLE
POLJA (FIELDS) TABELE
PIVOT TABELE

Napravimo novi radni list Podaci o kupcima

i u njemu kolonu sa kupcima (ukloni duplikate).

Primenom funkcije XLOOKUP kreirati kolone JMBG i Grad.

 POWER EXCEL MOGUĆNOSTI

Ovo je Excel‐ov Business Intelligence alat koji ne zahteva dodatni softver.

Omogućite Power Pivot/Power Query preko plug‐ina u Excel‐u:
File > Options > Add‐Ins > Manage: COM Add‐Ins

IZVORI PODATAKA POWER QUERY DATA MODEL POWER PIVOT & DAX
Fajlovi (csv, txt), Excel tabele, (“Get & Transform”) Istražuje i analizira model
Kreiranje relacija između tabela,
baze podataka (SQL, Azure), folderi, Povezivanje sa izvorom podataka, dodavanje izračunatih kolona, podataka i kreira moćne mere
strimovi, veb podaci... uvoz podataka i primena alata za definisanje hijerarhije i perspektive... korišćenjem DAX izraza
uređivanje i transformisanje (Data Analysis Expression).
 UVOZ I ANALIZA MILIONA REDOVA

25.000.000 redova podataka u Excel‐u?

Kada se povežete na podatke pomoću Power Query‐ja

i učitate ih u Excel‐ov Data Model, podaci se
komprimuju i čuvaju u memoriji, a ne u worksheet‐u
(maksimum 1.048.576 redova x 16.384 kolona).
IZRADA MODELA PODATAKA

Primer Data Model‐a u tzv. “Diagram View”

‐u koji omogućava kreiranje veza između
tabela.

Stvaraju se veze pomoću kojih se povezuju

podaci na osnovu zajedničkih polja.
AUTOMATIZACIJA PODATAKA

Pomoću Power Query‐ja, mogu se

filtrirati, uređivati i transformisati
podaci u redovima, pre nego što se
učitaju u model podataka.

Svaki korak se automatski

beleži i čuva u samom upitu
i primenjuje svaki put kada se
izvor podataka osveži!
KREIRANJE MOĆNIH MERA SA DAX‐OM

Mere su fleksibilne i moćne kalkulacije

koje se definišu korišćenjem DAX (Data
Analysis Expressions) izraza.
 KADA KORISTITI POWER QUERY & POWER PIVOT

Koristiti Power Query i Power Pivot u situacijama kada se:

‐ analiziraju podaci koji se ne mogu smestiti unutar jednog radnog

lista;
‐ kreiraju konekcije sa bazama podataka ili drugim eksternim
izvorima;
‐ kombinuju podaci iz više velikih tabela;
‐ želi automatizovati proces učitavanja i uređivanja podataka;
 POWER QUERY

Power Query (opcija “Get & Transform”ranije verzije, sada Data) omogućava:
• Povezivanje podataka iz različitih izvora
• Filtriranje, oblikovanje, dodavanje i transformisanje neobrađenih podataka za
dalju analizu i modeliranje
• Kreiranje procedura koje će automatizovati proces pripreme podataka (slično
makrou)

Power Query alati, Data tab, sekcija

Get & Transform (Excel 2016)
 TIPOVI KONEKCIJA

From File From Database FromAzure From Online Services From Other Sources
 QUERY EDITOR

Alati za
Editovanje
Formula Bar
( “M” kod)

Naziv tabele

Pregled
podataka
Primenjeni
koraci

Pristup Query Editor‐u omogućen je kreiranjem novog query‐ja i izborom opcije “Edit”, ili
Pokretanjem Workbook Queries panela (Data > Show Queries) i desni klik na postojeći query zbog editovanja.
 QUERY EDITOR ALATI
HOME tab: opšta podešavanja i alati transformacije tabela

TRANSFORM tab: modifikacija kolona (deljenje/grupisanje, transponovanje, izvlačenje teksta...)

ADD COLUMN tab: kreiranje nove kolone zasnovano na uslovnim pravilima, operacije sa tekstom, kalkulacije,...)
DATA LOADING OPCIJE

Prilikom učitavanja podataka iz Power Query‐ja postoji nekoliko opcija:

• Table
• Smešta podatke u novi ili postojeći radni list
• Zahteva relativno male skupove podataka

• Only Create Connection

• Čuva sva podešavanja u vezi podataka i korišćenih koraka
• Podaci se ne učitavaju u worksheet

• Add to Data Model

• Komprimuje i učitava podatke u Excel‐ov Data Model
• Omogućava Power Pivot‐u pristup podacima za dalju analizu.
EDITOVANJE POSTOJEĆEG UPITA
 OSNOVNE TRANSFORMACIJE TABELE

Sortiranje Promena tipova podataka Postavljanje 1. reda kao hedera

(A‐Z, Low‐High, etc.) (date, $, %, text, etc.)

Duplira, pomera &

preimenuje kolone
Čuva ili uklanja kolone
Desni‐klik na
Koristiti opciju“Remove Other zaglavlje kolone za pristup
Columns” za kreiranje tačno različitim alatima
određenog skupa podataka.
Čuva ili uklanja redove
Koristiti opciju “Remove Duplicates”
za kreiranje nove tabele za pretragu
 ALATI ZA RAD SA TEKSTOM

Izdvajanje karaktera iz teksta

kolone

Deli tekst kolonu na osnovu Selektovanje dve ili više kolona radi
specifičnog delimitera ili Spajanja (merge, concatenate) polja
na osnovu broja karaktera

Većina ovih alata nalazi se i na kartici

“Transform” i “Add Column”. Formatiranje teksta u koloni (opcije upper, lower,
Razlika postoji jer se u 1. slučaju modifikuje proper) ili dodavanje prefiksa ili sufiksa.
postojeća kolona, a u drugom dodaje nova. Koristi opciju „Trim” da eliminišeš višak praznih mesta
 ALATI ZA RAD SA BROJEVIMA

Informativni alati omogućavaju

definisanje binarnih vrednosti
(TRUE/FALSE ili 1/0) za
Standard Scientific Trigonometry označavanje reda ili kolone
Statističke funkcije omogućavaju u smislu da li je parna, neparna
osnovne statistike za odabranu Standardni, Naučni i Trigonometrijski alati omogućavaju
pozitivna il negativna.
kolonu (sum, min/max, average, korišćenje standardnih operacija (sabiranje, množenje,
count, countdistinct...) deljenje...) i više naprednijih (stepenovanje,
logaritmovanje, trigonometrijske funkcije...)
Ovi alati kao rezultat daju JEDNU vrednost,
I i uglavnom se koriste za ispitivanje tabele. Za razliku od statistističkih opcija, ovi alati se koriste
ponaosob ili za red ili za celu tabelu.
 ALATI ZA RAD SA DATUMIMA

Date & Time alati su relativno jednostavni i uključuju sledeće opcije:

• Age: Razlika između trenutnog vremena i datuma u svakom redu
• Date Only: Uklanja vremensku komponentu (TIME) polja datum/vreme
• Year/Month/Quarter/Week/Day: Izdvaja pojedinačne komponente iz polja datuma
(Opcije za vreme uključuju sat, minut, sekundu itd.)
• Earliest/Latest: Procenjuje najraniji ili najnoviji datum iz kolone kao jednu vrednost
(može se pristupiti samo iz menija „Transform“)

Napomena: Koristite uvek karticu “Add Column” za dodavanje nove kolone; nemojte transformisati postojeću.
 DODAVANJE INDEX KOLONE

Index Column sadrži listu

sekvencijalnih vrednosti koje se mogu
koristiti za identifikaciju bilo kog
jedinstvenog reda u tabeli (obično
počinje od 0 ili 1).
Ovi kolone se često koriste za stvaranje
jedinstvenih ID‐ova koji se mogu koristiti
za formiranje odnosa među tabelama.
 DODAVANJE USLOVNE KOLONE

Conditional Columns* za definisanje novih polja na

osnovu logičkih pravila i uslova (IF/THEN naredbe)

U ovom slučaju kreiramo novu uslovnu

kolonu pod nazivom “Order Size”, koja
zavisi od vrednosti u koloni “quantity”, na
sledeći način:
Ako je quantity >5, Order Size = “Large”
Ako je quantity između 2‐5, Order Size =
“Medium”
Ako je quantity =1, Order Size = “Small”
U ostalim slučajevima Order Size = “Other”
MODIFIKOVANJE WORKBOOK UPITA

Klik na Show Queries da bi se otvorio Workbook

Queries pano.

Desni‐klik na bilo koji upit da bi se pristupilo

zajedničkim opcijama ili alatima:
• Edit (otvara Query Editor)
• Delete
• Rename
• Refresh
• Duplicate
• Merge
• Append
DATA MODEL PROZOR

Data Model se otvara u posebnom Excel prozoru gde se mogu videti tabele,
izračunati nove mere i definisati relacije između tabela.
Zatvaranjem Data Model prozora ne zatvara se Excel workbook!
 DATA POGLED I DIJAGRAM POGLED

DATA VIEW DIAGRAM VIEW

Tabele organizovane kao tabovi Tabele organizovane kao objekti

 TABELE PODATAKA I "LOOKUP" TABELE

Modeli generalno sadrže dva tipa tabela: tabela podataka (“činjenice”) i "lookup" tabele (“dimenzija”).

Tablela podataka sadrže brojeve ili vrednosti,

obično na najgrubljom mogućem nivou, sa ID
ili „ključ“ kolonama koje se mogu koristiti za
povezivanje sa svakom „lookup“ tabelom.

„Lookup" tabele daju opisne, često

tekstualne atribute o svakoj dimenziji
u tabeli.
 PRIMARNI I STRANI (FOREIGN) KLJUČ

Ove kolone su strani ključevi; one mogu da Ove kolone su primarni ključevi; oni jedinstveno identifikuju svaki red
sadrže više instanci za iste vrednosti i koriste se tabele i odgovaraju stranim ključevima u povezanim tabelama podataka.
za spajanje sa primarnim ključevima
u povezanim „lookup" tabelama.
 KREIRANJE RELACIJA IZMEĐU TABELA

Opcija 1: U Dijagram pogledu klikni i povuci relaciju Opcija 2: Koristi “Create Relationship” u Design tabu
 POWER PIVOT

“Power” Pivot je Pivot tabela koja se ne bazira se na jednoj tabeli ili opsegu
podataka, već se nalazi na vrhu celog modela podataka. To omogućava:
• ISPITIVANJE ogromnog skupa podataka, koji se sastoji od više izvora i tabela, korišćenjem
poznatih alata i opcija Pivot tabela, prilagođenih korisnicima.

• Kreiranje moćnih i fleksibilnih kalkulacija korišćenjem DAX izraza (Data Analysis Expressions).

Power Pivot tab uključuje alate za

upravljanje modelom podataka i
definiše nove mere.

Za omogućavanje ovog taba potrebno je selektovati

File > Options > Add‐Ins > Manage COM Add‐Ins