Microsoft Excel korisnicima nudi stotine različitih funkcija i formula za različite svrhe. Bez obzira na to morate li analizirati svoje osobne financije ili neki veliki skup podataka, funkcije vam olakšavaju posao. Također, štedi puno vremena i truda. Međutim, pronalaženje ispravne funkcije za vaš skup podataka može biti vrlo teško.
Dakle, ako ste se mučili pronaći pravu Excel funkciju za analizu podataka, onda ste došli na pravo mjesto. Ovdje je popis nekih osnovnih Microsoft Excel funkcija koje možete koristiti za analizu podataka i mogu povećati vašu produktivnost u procesu.
Note: Za neupućene, funkcije navedene u ovom postu trebaju biti dodane u traku formule proračunske tablice Excel koja sadrži podatke ili unutar ćelije u kojoj želite rezultat.
1, CONCATENATE
= CONCATENATE jedna je od najvažnijih funkcija za analizu podataka jer omogućuje kombiniranje teksta, brojeva, datuma itd. od nekoliko ćelija u jednoj. Funkcija je posebno korisna za kombiniranje podataka iz različitih stanica u jednu ćeliju. Na primjer, korisno je za stvaranje parametara praćenja za marketinške kampanje, izradu API upita, dodavanje teksta u brojevni format i razne druge stvari.
U gornjem primjeru željeli ste mjesec i prodaju zajedno u jednom stupcu. Za to sam koristio formulu = CONCATENATE (A2, B2) u ćeliji C2 da bih dobio kao rezultat 700 USD.
Formula: = CONCATENATE (ćelije koje želite kombinirati)
2, LEN
= LEN je još jedna korisna funkcija za analizu podataka koja u osnovi generira broj znakova u bilo kojoj ćeliji. Funkcija je uglavnom korisna pri stvaranju naslovnih oznaka ili opisa koji imaju ograničenje broja znakova. To vam može biti korisno i kada pokušavate otkriti razlike između različitih jedinstvenih identifikatora koji su često prilično dugi i nisu u ispravnom redoslijedu.
U gornjem primjeru htio sam brojati brojeve posjeta koje sam primio svaki mjesec. Za to sam koristio formulu = LEN (C2) u ćeliji D2 da bih dobio 5 kao rezultat.
Formula: = LEN (ćelija)
3, TRAŽITI
= VLOOKUP je vjerojatno jedna od najprepoznatljivijih funkcija za svakoga tko je upoznat s analizom podataka. Možete ih koristiti za usporedbu podataka u tablici s ulaznom vrijednošću. Funkcija nudi dva načina podudaranja: precizan i približan, što se kontrolira rasponom pretraživanja. 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, želio sam pronaći broj posjeta u određenom mjesecu. Za to sam koristio formulu = VLOOKUP (“Jun”, A2: C13, 3) u ćeliji G4 i kao rezultat dobio 74992. Ovdje je “Jun” vrijednost pretraživanja, A2: C13 je niz tablica u potrazi za “Jun”, i 3 je broj stupca u kojem će formula naći odgovarajuće prikaze za lipanj.
Jedina mana upotrebe ove značajke je ta da ona radi samo s podacima koji su organizirani u stupce, odatle i naziv: vertikalno pretraživanje. Dakle, ako imate svoje podatke organizirane u redove, prvo morate prenijeti retke u stupce.
Formula: = VLOOKUP (lookup_value, table_array, col_index_num, (range_lookup))
4, INDEX / UTAKMICA
Kao i VLOOKUP funkcija, INDEX i MATCH funkcija korisni su za traženje određenih podataka na temelju ulazne vrijednosti. INDEX i MATCH, ako se koriste zajedno, mogu svladati ograničenja VLOOKUP-a davanja pogrešnih rezultata (ako nisu oprezni). Kada kombinirate ove dvije funkcije, oni mogu prepoznati referencu podataka i potražiti vrijednost u jednodimenzionalnom polju. To vraća koordinate podataka kao broja.
U gornjem primjeru htio sam pronaći broj posjeta u siječnju. Za to sam koristio formulu = INDEX (A2: C13, MATCH (“Jan”, A2: A13,0), 3) Evo, A2: C13 je stupac podataka Želim da se formula vrati, “Jan” je vrijednost s kojom želim uskladiti, A2: A13 je stupac u kojem će formula naći “Jan” i 0 znači da želim da formula pronađe točno podudaranje vrijednosti.
Ako želite pronaći grubu podudarnost, morat ćete je zamijeniti 0 s 1 ili -1, Tako da 1 pronaći će najveću vrijednost manju ili jednaku vrijednosti pretraživanja i -1 pronaći će najmanju vrijednost manju ili jednaku vrijednosti pretraživanja. Imajte na umu ako ne upotrebljavate 0, 1. -1, formula će koristiti 1por.
Ako ne želite kodirati naziv mjeseca, možete ga zamijeniti brojem ćelije. Stoga možemo „Jan“ u gore navedenoj formuli zamijeniti s F3 ili A2 da bismo dobili isti rezultat.
Formula: = INDEX (stupac podataka koji želite vratiti, MATCH (zajednička podatkovna točka koju pokušavate uskladiti, stupac drugog izvora podataka koji ima zajedničku točku podataka, 0))
5, MINIFI / MAXIFS
= MINIFS i = MAXIFS vrlo su slični funkcijama = MIN i = MAX, osim što vam omogućavaju da uzmete minimalni / maksimalni skup vrijednosti i također ih uskladite s određenim kriterijima. U osnovi, funkcija traži minimalne / maksimalne vrijednosti i uspoređuje ih s ulaznim kriterijima.
U gornjem primjeru htio sam pronaći minimalne bodove 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 pretraživati rezultate, B2: B10 je stupac u onaj koji će formula tražiti kriterije (spol), a kriterij “M”.
Slično tome, za maksimalne rezultate, koristio sam formulu = MAXIFS (C2: C10, B2: B10, “M”) i dobio sam rezultat 100.
Formula za MINIFS: = MINIFS (min_range, kriterij_range1, kriterij1, …)
MAXIFS formula: = MAXIFS (maksimalna_razina, kriterij_razina1, kriterij1, …)
6, PROSJEČNI IFS
Funkcija = AVERAGEIFS omogućuje vam da pronađete prosjek za određeni skup podataka na temelju jednog ili više kriterija. Pri korištenju ove funkcije trebate biti svjesni 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čina. Primjećujete li razliku jednine i množine između tih funkcija? Pa tu morate biti oprezni.
U ovom sam primjeru želio pronaći prosječnu ocjenu na temelju spola učenika. Za to sam koristio formulu = AVERAGEIFS (C2: C10, B2: B10, “M”) i dobio sam 56.8 kao rezultat. Ovdje je C2: C10 raspon u kojem će formula pretraživati prosjek, B2: B10 je raspon kriterija, a “M” je kriterij.
Formula: = AVERAGEIFS (prosječna_razina, kriterij_razina1, kriterij1, …)
7, županije
Ako želite računati broj instanci da skup podataka zadovoljava određene kriterije, morat ćete koristiti funkciju = COUNTIFS. Ova značajka omogućuje vam dodavanje neograničenih kriterija na vaš upit te je stoga najlakši način za brojanje na temelju ulaznih kriterija.
U ovom sam primjeru želio pronaći broj učenika koji su zaradili 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 pretraživati prvi kriterij (spol), “M” je prvi kriterij, C2: C10 je raspon u kojem će formula potražiti drugi kriterij (ocjene), i ” > = 40 “je drugi kriterij.
Formula: = COUNTIFS (kriterij_razred1, kriterij1, …)
8, SUMPRODUCT
Funkcija = SUMPRODUCT pomaže vam da množite raspone ili matrice, a zatim vraća zbroj proizvoda. To je prilično svestrana funkcija i može se koristiti za brojanje i dodavanje matrica poput COUNTIFS ili SUMIFS, ali s većom fleksibilnošću. Također možete koristiti druge funkcije unutar SUMPRODUCT-a za daljnje proširenje njegove funkcionalnosti.
U ovom primjeru želio sam pronaći ukupnu svotu svih prodanih proizvoda. Za to sam koristio formulu = SUMPRODUCT (B2: B8, C2: C8). Ovdje je B2: B8 prvi set (broj prodanih proizvoda), a C2: C8 drugi set (cijena svakog proizvoda). Formula zatim množi količinu svakog prodanog proizvoda s njegovom cijenom, a zatim dodaje sve zajedno kako bi se postigla ukupna prodaja.
Formula: = SUMPRODUCT (matrica1, (matrica2), (matrica3), …)
9, TRIM
= TRIM funkcija je osobito korisna u radu s skupa podataka koji ima više razmaka ili neželjenih znakova. Funkcija vam omogućuje jednostavno uklanjanje tih razmaka ili znakova iz podataka, omogućujući vam dobivanje točnih rezultata tijekom upotrebe drugih funkcija.
U ovom sam primjeru želio ukloniti sve dodatne razmake između riječi Miš i jastučića u A7. Za to sam koristio formulu = TRIM (A7).
Formula je jednostavno uklonila dodatne prostore i s jednim razmakom isporučila rezultat Mouse pad.
Formula: = TRIM (tekst)
10. PRETRAŽI / PRETRAŽI
Da biste stvari zaokružili, pronađite funkcije FIND / SEARCH koje će vam pomoći izolirati određeni tekst unutar skupa podataka. Obje su funkcije prilično slične onome što rade, osim jedne važne razlike: funkcija = FIND vraća samo osjetljive na velika i mala slova. U međuvremenu, funkcija = SEARCH nema takva ograničenja. Ove su funkcije osobito korisne kada tražite anomalije ili jedinstvene identifikatore.
U ovom primjeru, želio sam otkriti koliko se puta „Gui“ pojavio unutar Guiding Tech za koji sam koristio formulu = FIND (A2, B2), koji je vratio rezultat 1, Ako biste htjeli otkriti koliko se puta „gui“ pojavio u Guiding Tech, morali biste upotrijebiti formulu = SEARCH jer nije velika i mala slova.
Formula za pronalaženje: = Pronađi (find_text, inside_text, (start_num))
Formula pretraživanja: = SEARCH (find_text, inside_text, (start_num))
Table of Contents
