10 osnovnih funkcija programa Microsoft Excel za analizu podataka
Miscelanea / / November 29, 2021
1. SPOJITI
=CONCATENATE je jedna od najvažnijih funkcija za analizu podataka jer vam omogućuje kombiniranje teksta, brojeva, datuma itd. iz više ćelija u jednu. Funkcija je osobito korisna za kombiniranje podataka iz različitih ćelija u jednu ćeliju. Na primjer, zgodan je za stvaranje parametara praćenja za marketinške kampanje, izradu API upita, dodavanje teksta u format brojeva i nekoliko drugih stvari.
U gornjem primjeru želio sam mjesec i prodaju zajedno u jednom stupcu. Za to sam upotrijebio formulu =CONCATENATE(A2, B2) u ćeliji C2 da dobijem Jan $700 kao rezultat.
Formula: =KONKATENIRAJ(stanice koje želite kombinirati)
2. LEN
=LEN je još jedna zgodna funkcija za analizu podataka koja u biti daje broj znakova u bilo kojoj ćeliji. Funkcija je uglavnom upotrebljiva tijekom izrade oznaka naslova ili opisa koji imaju ograničenje znakova. Također može biti korisno kada pokušavate saznati razlike između različitih jedinstvenih identifikatora koji su često prilično dugi i nisu u ispravnom redoslijedu.
U gornjem primjeru htio sam izbrojati brojke za broj pregleda koje sam dobivao svaki mjesec. Za to sam upotrijebio formulu =LEN(C2) u ćeliji D2 da dobijem 5 kao rezultat.
Formula: =LEN(stanica)
3. VLOOKUP
=VLOOKUP je vjerojatno jedna od najprepoznatljivijih funkcija za svakoga tko je upoznat s analizom podataka. Možete ga koristiti za usklađivanje podataka iz tablice s ulaznom vrijednošću. Funkcija nudi dva načina podudaranja — točno i približno, što se kontrolira rasponom traženja. Ako postavite raspon na FALSE, tražit će točno podudaranje, ali ako ga postavite na TRUE, tražit će približno podudaranje.
U gornjem primjeru htio sam potražiti broj pregleda u određenom mjesecu. Za to sam upotrijebio formulu =VLOOKUP("Jun", A2:C13, 3) u ćeliji G4 i dobio sam 74992 kao rezultat. Ovdje je "Jun" vrijednost traženja, A2:C13 je niz tablice u kojem tražim "Jun", a 3 je broj stupca u kojem će formula pronaći odgovarajuće prikaze za lipanj.
Jedina mana korištenja ove funkcije je to što radi samo s podacima koji su raspoređeni u stupce, otuda i naziv — okomito traženje. Dakle, ako imate svoje podatke poredane u redove, prvo ćete to morati transponirati retke u stupce.
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
4. INDEKS/PODRŽAVANJE
Slično kao i funkcija VLOOKUP, funkcije INDEX i MATCH su korisne za pretraživanje određenih podataka na temelju ulazne vrijednosti. INDEX i MATCH, kada se koriste zajedno, mogu prevladati ograničenja VLOOKUP-a u isporuci pogrešnih rezultata (ako niste pažljivi). Dakle, kada kombinirate ove dvije funkcije, one mogu točno odrediti referencu podataka i tražiti vrijednost u nizu jedne dimenzije. To vraća koordinate podataka kao broj.
U gornjem primjeru htio sam pogledati broj pregleda u siječnju. Za to sam koristio formulu =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Ovdje je A2:C13 stupac podataka za koji želim da se formula vrati, "Jan" je vrijednost koju želim uskladiti, A2:A13 je stupac u kojem će formula pronaći "siječanj", a 0 označava da želim da formula pronađe točno podudaranje za vrijednost.
Ako želite pronaći približno podudaranje, morat ćete zamijeniti 0 s 1 ili -1. Tako da će 1 pronaći najveću vrijednost manju ili jednaku traženoj vrijednosti, a -1 pronaći najmanju vrijednost manju ili jednaku vrijednosti pretraživanja. Imajte na umu da ako ne koristite 0, 1 ili -1, formula će koristiti 1, by.
Sada, ako ne želite tvrdo kodirati naziv mjeseca, možete ga zamijeniti brojem ćelije. Dakle, možemo zamijeniti "Jan" u gore spomenutoj formuli s F3 ili A2 kako bismo dobili isti rezultat.
Formula: =INDEX(stupac podataka koje želite vratiti, MATCH(zajednička podatkovna točka koju pokušavate uskladiti, stupac drugog izvora podataka koji ima zajedničku podatkovnu točku, 0))
5. MINI/MAXIFS
=MINIFS i =MAXIFS vrlo su slični funkcijama =MIN i =MAX, osim činjenice da vam omogućuju da uzmete minimalni/maksimalni skup vrijednosti i uskladite ih po određenim kriterijima. Dakle, u biti, funkcija traži minimalne/maksimalne vrijednosti i uparuje ih s kriterijima za unos.
U gornjem primjeru želio sam pronaći minimalne rezultate na temelju spola učenika. Za to sam koristio formulu =MINIFS (C2:C10, B2:B10, "M") i dobio sam rezultat 27. Ovdje je C2:C10 stupac u kojem će formula tražiti rezultate, B2:B10 je stupac u kojem će formula tražiti kriterije (spol), a "M" je kriterij.
Slično, za maksimalne rezultate, koristio sam formulu =MAXIFS(C2:C10, B2:B10, "M") i dobio rezultat 100.
Formula za MINIFS: =MINIFS(min_raspon, kriterij_raspon1, kriterij1,...)
Formula za MAXIFS: =MAXIFS(max_raspon, kriterij_raspon1, kriterij1,...)
6. PROSJEČNI IF
Funkcija =AVERAGEIFS omogućuje vam da pronađete prosjek za određeni skup podataka na temelju jednog ili više kriterija. Prilikom korištenja ove funkcije, morate imati na umu da svaki kriterij i prosječni raspon mogu biti različiti. Međutim, u funkciji =AVERAGEIF, i raspon kriterija i raspon zbroja moraju imati isti raspon veličine. Primjetite razliku u jednini i množini između ovih funkcija? E, tu treba biti oprezan.
U ovom primjeru želio sam pronaći prosječnu ocjenu na temelju spola učenika. Za to sam upotrijebio formulu, =PROSJEČNOIFS(C2:C10, B2:B10, "M") i dobio 56,8 kao rezultat. Ovdje je C2:C10 raspon u kojem će formula tražiti prosjek, B2:B10 je raspon kriterija, a "M" je kriterij.
Formula: =PROSJEČNI IF(prosječni_raspon, kriterij_raspon1, kriterij1,...)
7. COUNTIFS
Sada, ako želite izbrojati broj slučajeva da skup podataka ispunjava određene kriterije, morat ćete koristiti funkciju =COUNTIFS. Ova funkcija vam omogućuje da svom upitu dodate neograničene kriterije i na taj način ga čini najlakšim načinom za pronalaženje broja na temelju kriterija unosa.
U ovom primjeru želio sam pronaći broj učenika ili učenica koji su dobili prolazne ocjene (tj. >=40). Za to sam koristio formulu =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Ovdje je B2:B10 raspon u kojem će formula tražiti prvi kriterij (spol), "M" je prvi kriterij, C2:C10 je raspon u kojem će formula tražiti drugi kriterij (oznake), a ">=40" je drugi kriterijima.
Formula: =COUNTIFS(kriterij_raspon1, kriterij1,...)
8. ZBIRNI PROIZVOD
Funkcija =SUMPRODUCT pomaže vam da pomnožite raspone ili nizove zajedno, a zatim vraća zbroj proizvoda. To je prilično svestrana funkcija i može se koristiti za brojanje i zbrajanje nizova poput COUNTIFS ili SUMIFS, ali uz dodatnu fleksibilnost. Također možete koristiti druge funkcije unutar SUMPRODUCT-a kako biste još više proširili njegovu funkcionalnost.
U ovom primjeru želio sam pronaći zbroj svih prodanih proizvoda. Za to sam koristio formulu =SUMPROIZVOD(B2:B8, C2:C8). Ovdje je B2:B8 prvi niz (količina prodanih proizvoda), a C2:C8 je drugi niz (cijena svakog proizvoda). Formula zatim umnožava količinu svakog prodanog proizvoda s njegovom cijenom, a zatim sve to zbraja kako bi se ostvarila ukupna prodaja.
Formula: =SUMPRODUCT(niz1, [niz2], [niz3],...)
9. TRIM
Funkcija =TRIM posebno je korisna kada radite sa skupom podataka koji ima nekoliko razmaka ili neželjenih znakova. Funkcija vam omogućuje da s lakoćom uklonite te razmake ili znakove iz podataka, omogućujući vam da dobijete točne rezultate dok koristite druge funkcije.
U ovom primjeru htio sam ukloniti sve dodatne razmake između riječi Miš i podloga u A7. Za to sam koristio formulu =TRIM(A7).
Formula je jednostavno uklonila dodatne razmake i isporučila rezultat podloge za miš s jednim razmakom.
Formula: = TRIM(tekst)
10. PRONAĐI/TRAŽI
Zaokružuju funkcije FIND/SEARCH koje će vam pomoći da izolirate određeni tekst unutar skupa podataka. Obje funkcije su prilično slične u onome što rade, osim jedne velike razlike — funkcija =FIND vraća samo podudaranja koja razlikuju velika i mala slova. U međuvremenu, funkcija =SEARCH nema takvih ograničenja. Ove su funkcije osobito korisne kada tražite anomalije ili jedinstvene identifikatore.
U ovom primjeru želio sam pronaći koliko se puta 'Gui' pojavio unutar Guiding Tech-a za koji sam koristio formulu =FIND(A2, B2), što je dalo rezultat 1. Ako bih umjesto toga htio pronaći koliko se puta 'gui' pojavio unutar Guiding Tech-a, morao bih upotrijebiti formulu =SEARCH jer nije osjetljiva na velika i mala slova.
Formula za pronalaženje: =PRONAĐI(pronađi_tekst, unutar_teksta, [početni broj])
Formula za pretraživanje: =TRAŽI(pronađi_tekst, unutar_teksta, [početni broj])