10 funcții esențiale Microsoft Excel pentru analiza datelor
Miscellanea / / November 29, 2021
1. ÎNLĂNŢUI
=CONCATENATE este una dintre cele mai importante funcții pentru analiza datelor, deoarece vă permite să combinați text, numere, date etc. din mai multe celule într-una singură. Funcția este deosebit de utilă pentru combinarea datelor din diferite celule într-o singură celulă. De exemplu, este util pentru a crea parametrii de urmărire pentru campaniile de marketing, pentru a crea interogări API, pentru a adăuga text într-un format numeric și în multe alte lucruri.
![Funcții esențiale Excel Analiza datelor 1](/f/ef4844c9256f6aeeb7180f57b791172d.jpg)
În exemplul de mai sus, am vrut luna și vânzările împreună într-o singură coloană. Pentru asta, am folosit formula =CONCATENATE(A2, B2) în celula C2 pentru a obține Jan$700 ca rezultat.
Formulă: =CONCATENATE(celulele pe care doriți să le combinați)
2. LEN
=LEN este o altă funcție utilă pentru analiza datelor care, în esență, emite numărul de caractere din orice celulă dată. Funcția este utilizabilă în principal în timpul creării etichetelor de titlu sau descrierilor care au o limită de caractere. De asemenea, poate fi util atunci când încercați să aflați diferențele dintre diferiții identificatori unici, care sunt adesea destul de lungi și nu sunt în ordinea corectă.
![Funcții esențiale Excel Analiza datelor 2](/f/d7cadade746a26cd545ab58daa0e9ad7.jpg)
În exemplul de mai sus, am vrut să număr cifrele pentru numărul de vizualizări pe care le primeam în fiecare lună. Pentru aceasta, am folosit formula =LEN(C2) în celula D2 pentru a obține 5 ca rezultat.
Formulă: =LEN(celulă)
3. CĂUTARE V
=VLOOKUP este probabil una dintre cele mai recunoscute funcții pentru oricine familiarizat cu analiza datelor. Îl puteți folosi pentru a potrivi datele dintr-un tabel cu o valoare de intrare. Funcția oferă două moduri de potrivire - exactă și aproximativă, care este controlată de intervalul de căutare. Dacă setați intervalul la FALSE, va căuta o potrivire exactă, dar dacă o setați la TRUE, va căuta o potrivire aproximativă.
![Funcții esențiale Excel Analiza datelor 3](/f/6e053d7c4c477481f07d505cdf6a876f.jpg)
În exemplul de mai sus, am vrut să caut numărul de vizualizări într-o anumită lună. Pentru asta, am folosit formula =VLOOKUP("Jun", A2:C13, 3) în celula G4 și am obținut 74992 ca rezultat. Aici, „Jun” este valoarea de căutare, A2:C13 este tabelul în care caut „Jun” și 3 este numărul coloanei în care formula va găsi vizualizările corespunzătoare pentru iunie.
Singurul dezavantaj al utilizării acestei funcții este că funcționează numai cu date care au fost aranjate în coloane, de unde și numele — căutare verticală. Deci, dacă aveți datele aranjate pe rânduri, mai întâi va trebui transpune rândurile în coloane.
Formulă: =CĂUTARE V(căutare_valoare, table_array, col_index_num, [range_lookup])
4. INDEX/POTRIVIRE
La fel ca și funcția VLOOKUP, funcția INDEX și MATCH sunt utile pentru căutarea unor date specifice pe baza unei valori de intrare. INDEX și MATCH, atunci când sunt utilizate împreună, pot depăși limitările VLOOKUP de a oferi rezultate greșite (dacă nu ești atent). Deci, atunci când combinați aceste două funcții, ele pot identifica referința de date și pot căuta o valoare într-o matrice cu o singură dimensiune. Aceasta returnează coordonatele datelor ca număr.
![Funcții esențiale Excel Analiza datelor 4](/f/b13227460bc7cb0fa84f9d41f380d98d.jpg)
În exemplul de mai sus, am vrut să caut numărul de vizualizări în ianuarie. Pentru asta, am folosit formula =INDEX (A2:C13, MATCH ("Jan", A2:A13,0), 3). Aici, A2:C13 este coloana de date pe care vreau să-l returneze formula, „Jan” este valoarea pe care vreau să o potrivesc, A2:A13 este coloană în care formula va găsi „Jan”, iar 0 înseamnă că vreau ca formula să găsească o potrivire exactă pentru valoare.
Dacă doriți să găsiți o potrivire aproximativă, va trebui să înlocuiți 0 cu 1 sau -1. Astfel încât 1 va găsi cea mai mare valoare mai mică sau egală cu valoarea de căutare și -1 va găsi cea mai mică valoare mai mică sau egală cu valoarea de căutare. Rețineți că, dacă nu utilizați 0, 1 sau -1, formula va folosi 1, până la.
![Funcții esențiale Excel Analiza datelor 5](/f/47d28f577f724d05e8f8ee098e91c6b7.jpg)
Acum, dacă nu doriți să codificați numele lunii, îl puteți înlocui cu numărul de celule. Deci putem înlocui „Jan” în formula menționată mai sus cu F3 sau A2 pentru a obține același rezultat.
Formulă: =INDEX(coloana datelor pe care doriți să le returnați, MATCH (punct de date comun cu care încercați să îl potriviți, coloana celeilalte surse de date care are punctul de date comun, 0))
5. MINIFS/MAXIFS
=MINIFS și =MAXIFS sunt foarte asemănătoare cu funcțiile =MIN și =MAX, cu excepția faptului că vă permit să luați setul minim/maxim de valori și să le potriviți și pe anumite criterii. Deci, în esență, funcția caută valorile minime/maxime și le potrivește cu criteriile de intrare.
![Funcții esențiale Excel Analiza datelor 7](/f/fae987f5603a9782d0f1136c8a385c01.jpg)
În exemplul de mai sus, am vrut să găsesc scorurile minime în funcție de sexul elevului. Pentru asta, am folosit formula =MINIFS (C2:C10, B2:B10, "M") și am obținut rezultatul 27. Aici C2:C10 este coloana în care formula va căuta scorurile, B2:B10 este o coloană în care formula va căuta criteriile (genul), iar „M” este criteriul.
![Funcții esențiale Excel Analiza datelor 6](/f/e122c3fc7d8eafccda0a531139c4dea9.jpg)
În mod similar, pentru scoruri maxime, am folosit formula =MAXIFS(C2:C10, B2:B10, "M") și am obținut rezultatul 100.
Formula pentru MINIFS: =MINIFS(interval_min, interval_criterii1, criteriu1,...)
Formula pentru MAXIFS: =MAXIFS(interval_max, interval_criterii1, criteriu1,...)
6. MEDIEIFS
Funcția =AVERAGEIFS vă permite să găsiți o medie pentru un anumit set de date pe baza unuia sau mai multor criterii. Când utilizați această funcție, ar trebui să rețineți că fiecare criteriu și interval mediu poate fi diferit. Cu toate acestea, în funcția =AVERAGEIF, atât intervalul de criterii, cât și intervalul de sumă trebuie să aibă același interval de dimensiuni. Observați diferența de singular și plural dintre aceste funcții? Ei bine, acolo trebuie să fii atent.
![Funcții esențiale Excel Analiza datelor 8](/f/a2eb06f67342cde2eb6ee86ea6a8d351.jpg)
În acest exemplu, am vrut să găsesc scorul mediu pe baza sexului elevilor. Pentru asta am folosit formula =AVERAGEIFS(C2:C10, B2:B10, "M") și am obținut 56,8 ca rezultat. Aici, C2:C10 este intervalul în care formula va căuta media, B2:B10 este intervalul de criterii și „M” este criteriul.
Formulă: =AVERAGEIFS(interval_medie, interval_criterii1, criteriu1,...)
7. COUNTIFS
Acum, dacă doriți să numărați numărul de cazuri în care un set de date îndeplinește anumite criterii, va trebui să utilizați funcția =COUNTIFS. Această funcție vă permite să adăugați criterii nelimitate la interogarea dvs. și, prin urmare, îl face cel mai simplu mod de a găsi numărătoarea pe baza criteriilor de intrare.
![Funcții esențiale Excel Analiza datelor 9](/f/39635ab1eeffded1042cfd7c6249a7f2.jpg)
În acest exemplu, am vrut să aflu numărul de studenți de sex masculin sau feminin care au obținut note de trecere (adică >=40). Pentru asta am folosit formula =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Aici, B2:B10 este intervalul în care formula va căuta primul criteriu (sex), „M” este primul criteriu, C2:C10 este intervalul în care formula va căuta al doilea criteriu (note), iar „>=40” este al doilea criterii.
Formulă: =COUNTIFS(intervalul_criterii1, criteriul1,...)
8. SUMPRODUS
Funcția =SUMPRODUCT vă ajută să înmulțiți intervale sau matrice împreună și apoi returnează suma produselor. Este o funcție destul de versatilă și poate fi folosită pentru a număra și a însuma matrice precum COUNTIFS sau SUMIFS, dar cu o flexibilitate suplimentară. De asemenea, puteți utiliza alte funcții din SUMPRODUCT pentru a-și extinde și mai mult funcționalitatea.
![Funcții esențiale Excel Analiza datelor 10](/f/f4d91eb407af82ac40d18cb36e6bbd13.jpg)
În acest exemplu, am vrut să aflu suma totală a tuturor produselor vândute. Pentru asta, am folosit formula =SUMPRODUCT(B2:B8, C2:C8). Aici, B2:B8 este prima matrice (cantitatea de produse vândute) și C2:C8 este a doua matrice (prețul fiecărui produs). Formula multiplică apoi cantitatea fiecărui produs vândută cu prețul său și apoi adună toate acestea pentru a livra vânzările totale.
Formulă: =SUMA PRODUS(matrice1, [matrice2], [matrice3],...)
9. TUNDE
Funcția =TRIM este deosebit de utilă atunci când lucrați cu un set de date care are mai multe spații sau caractere nedorite. Funcția vă permite să eliminați aceste spații sau caractere din datele dvs. cu ușurință, permițându-vă să obțineți rezultate precise în timp ce utilizați alte funcții.
![Funcții esențiale Excel Analiza datelor 11](/f/04381c96e973bee237ac85395449540a.jpg)
În acest exemplu, am vrut să elimin toate spațiile suplimentare dintre cuvintele Mouse și pad în A7. Pentru asta am folosit formula =TRIM(A7).
![Funcții esențiale Excel Analiza datelor 12](/f/6d8e3d59bdbc3535366f7e4eeeedb7de.jpg)
Formula a eliminat pur și simplu spațiile suplimentare și a livrat rezultatul Mouse pad cu un singur spațiu.
Formulă: =TIMARE(text)
10. GĂSIRE/CĂUTARE
Lucrurile de rotunjire sunt funcțiile FIND/SEARCH care vă vor ajuta să izolați text specific dintr-un set de date. Ambele funcții sunt destul de similare în ceea ce fac, cu excepția unei diferențe majore - funcția =FIND returnează doar potriviri sensibile la majuscule. Între timp, funcția =SEARCH nu are astfel de limitări. Aceste funcții sunt deosebit de utile atunci când se caută anomalii sau identificatori unici.
![Funcții esențiale Excel Analiza datelor 13](/f/94b07268b0748d7e1ecddc2c94ec57ee.jpg)
În acest exemplu, am vrut să aflu de câte ori a apărut „Gui” în Guiding Tech pentru care am folosit formula =FIND(A2, B2), care a dat rezultatul 1. Acum, dacă aș vrea să aflu de câte ori a apărut „gui” în Guiding Tech, ar trebui să folosesc formula =SEARCH deoarece nu face distincție între majuscule și minuscule.
Formula pentru a găsi: =GĂSĂ(find_text, within_text, [start_num])
Formula pentru căutare: =CAUTARE(find_text, within_text, [start_num])