10 bistvenih funkcij Microsoft Excel za analizo podatkov

Microsoft Excel uporabnikom ponuja na stotine različne funkcije in formule za različne namene. Ne glede na to, ali morate analizirati svoje osebne finance ali velik nabor podatkov, je to delo, ki olajša delo. Prav tako prihrani veliko časa in truda. Vendar je iskanje prave funkcije za vaš nabor podatkov lahko zelo težavno.







Torej, če ste se trudili najti to ustrezna funkcija Excela za analizo podatkov, potem ste prišli na pravo mesto. Tu je seznam nekaterih bistvenih funkcij Microsoft Excel, ki jih lahko uporabite za analizo podatkov, in lahko povečate svojo produktivnost v procesu.

Opomba: Za neuveščene je treba funkcije, omenjene v tej objavi, dodati v vrstico formule preglednice Excel, ki vsebujejo podatke, ali v celico, v kateri želite rezultat.
Tudi na vodilni tehniki



Kako ustvariti brezplačne ankete v programu Microsoft Excel Online
Preberi več




1. CONCATENATE

= CONCATENATE je ena najpomembnejših funkcij za analizo podatkov, saj omogoča kombiniranje besedila, številk, datumov itd. Iz več celic v eno. Funkcija je še posebej uporabna za združevanje podatkov iz različnih celic v eno celico. Na primer, je priročno za ustvarjanje parametrov za sledenje marketinških kampanj, izdelavo poizvedb API-ja, dodajanje besedila v številčno obliko in več drugih stvari.




V zgornjem primeru sem želel mesec in prodajo skupaj v enem stolpcu. Za to sem uporabil formulo = CONCATENATE (A2, B2) v celici C2, da sem kot rezultat dobil 700 USD.

Formula: = CONCATENATE (celice, ki jih želite združiti)





2. LEN

= LEN je še ena priročna funkcija za analizo podatkov, ki v bistvu odda število znakov v kateri koli celici. Funkcija je pretežno uporabna pri ustvarjanju naslovnih oznak ali opisov, ki imajo omejitev znakov. Koristno je tudi, ko poskušate ugotoviti razlike med različnimi enoličnimi identifikatorji, ki so pogosto precej dolgotrajni in niso v pravilnem vrstnem redu.




V zgornjem primeru sem želel prešteti podatke o številu ogledov, ki sem jih dobival vsak mesec. Za to sem uporabil formulo = LEN (C2) v celici D2, da sem kot rezultat dobil 5.

Formula: = LEN (celica)





3. VLOOKUP

= VLOOKUP je verjetno ena najbolj prepoznavnih funkcij za vsakogar, ki je seznanjen z analizo podatkov. Z njimi lahko ujemate podatke iz tabele z vhodno vrednostjo. Funkcija ponuja dva načina ujemanja - natančen in približen, ki ga nadzira obseg iskanja. Če nastavite obseg na FALSE, bo iskal točno ujemanje, če pa ga nastavite na TRUE, bo poiskal približno ujemanje.




V zgornjem primeru sem želel pregledati število ogledov v posameznem mesecu. Za to sem uporabil formulo = VLOOKUP ('Jun', A2: C13, 3) v celici G4 in kot rezultat sem dobil 74992. Tu je 'Jun' vrednost iskanja, A2: C13 je niz tabel, v katerem iščem 'Jun', 3 pa je številka stolpca, v katerem bo formula našla ustrezne poglede za junij.

Edina pomanjkljivost uporabe te funkcije je, da deluje le s podatki, ki so razporejeni v stolpce, od tod tudi ime - vertikalno iskanje. Če imate podatke razporejene po vrstah, morate najprej prenesite vrstice v stolpce.

Formula: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])





4. INDEX / Ujemanje

Funkcije INDEX in MATCH sta podobno kot funkcija VLOOKUP priročna za iskanje določenih podatkov na podlagi vhodne vrednosti. INDEX in MATCH, če se uporabljata skupaj, lahko premagata omejitve VLOOKUP-a pri napačnih rezultatih (če niste previdni). Ko združite ti dve funkciji, lahko natančno določita sklic na podatke in poiščete vrednost v enorazsežnem nizu. Koordinate podatkov vrnejo kot število.




V zgornjem primeru sem želel pregledati število ogledov v januarju. Za to sem uporabil formulo = INDEX (A2: C13, MATCH ('Jan', A2: A13,0), 3). Tu je A2: C13 stolpec podatkov, za katerega želim, da se formula vrne, 'Jan' je vrednost, ki se ji želim ujemati, A2: A13 je stolpec, v katerem bo formula našla 'Jan', 0 pa pomeni, da želim formula za iskanje natančnega ujemanja vrednosti.

Če želite najti približno ujemanje, morate 0 nadomestiti z 1 ali -1. Tako da bo 1 našla največjo vrednost, manjšo ali enako vrednost iskanja, -1 pa najmanjšo vrednost, ki je manjša ali enaka iskalni vrednosti. Če ne uporabljate 0, 1 ali -1, bo formula uporabila 1, s.




Če ne želite trdega imena imena meseca, ga lahko nadomestite s številko celice. Tako lahko zamenjamo „Jan“ v zgoraj navedeni formuli s F3 ali A2, da dobimo enak rezultat.

Formula: = INDEX (stolpec podatkov, ki jih želite vrniti, MATCH (skupna podatkovna točka, s katero se poskušate ujemati, stolpec drugega vira podatkov, ki ima skupno podatkovno točko, 0))





5. MINIFI / MAXIFS

= MINIFS in = MAXIFS sta zelo podobni funkcijama = MIN in = MAX, razen dejstva, da vam omogočata, da vzamete najmanjši / največji nabor vrednosti in jih primerjate tudi po določenih merilih. Tako v bistvu funkcija išče minimalne / največje vrednosti in se ujema z vhodnimi merili.




V zgornjem primeru sem želel najti minimalne ocene glede na spol študenta. Za to sem uporabil formulo = MINIFS (C2: C10, B2: B10, 'M') in dobil sem rezultat 27. Tu je C2: C10 stolpec, v katerem bo formula iskala ocene, B2: B10 je stolpec, v katerem bo formula iskala merila (spol) in 'M'.




Podobno sem za največje ocene uporabil formulo = MAXIFS (C2: C10, B2: B10, 'M') in dobil rezultat 100.

Formula za MINIFS: = MINIFS (min_range, kriteriji_range1, kriteriji1, ...)

Formula za MAXIFS: = MAXIFS (max_range, kriteriji_range1, kriteriji1, ...)

Tudi na vodilni tehniki



Kako uporabljati orodje za filtriranje podatkov v MS Excelu
Preberi več




6. AVERAGEIFS

Funkcija = AVERAGEIFS omogoča iskanje povprečja za določen nabor podatkov na podlagi enega ali več kriterijev. Med uporabo te funkcije ne pozabite, da so lahko vsaka merila in povprečni razpon različni. V funkciji = AVERAGEIF morata biti razpon meril in razpon vsote enak obsegu velikosti. Ali opazite razliko med ednino in množino med temi funkcijami? No, tu morate biti previdni.




V tem primeru sem želel najti povprečno oceno glede na spol študentov. Za to sem uporabil formulo = AVERAGEIFS (C2: C10, B2: B10, 'M') in kot rezultat dobil 56,8. Tu je C2: C10 obseg, v katerem bo formula iskala povprečje, B2: B10 je razpon meril, 'M' pa merila.

Formula: = AVERAGEIFS (povprečna_reda, kriteriji_razred1, merila1, ...)





7. COUNTIFS

Če želite prešteti število primerov, da nabor podatkov ustreza določenim merilom, boste morali uporabiti funkcijo = COUNTIFS. Ta funkcija vam omogoča, da v svojo poizvedbo dodate neomejena merila in s tem olajšate iskanje števila na podlagi vhodnih meril.




V tem primeru sem želel najti število študentov moškega ali ženskega jezika, ki so dobili pozitivne ocene (t.j.> = 40). Za to sem uporabil formulo = COUNTIFS (B2: B10, 'M', C2: C10, '> = 40'). Tukaj je B2: B10 območje, v katerem bo formula iskala prva merila (spol), „M“ prva merila, C2: C10 je območje, v katerem bo formula iskala druga merila (ocene), in '> = 40' je drugo merilo.

Formula: = COUNTIFS (kriteriji_orange1, merila1, ...)





8. POVZETEK

Funkcija = SUMPRODUCT vam pomaga pomnožiti obsege ali matrike skupaj in nato vrne vsoto izdelkov. Je precej vsestranska funkcija in se lahko uporablja za štetje in seštevanje nizov, kot so COUNTIFS ali SUMIFS, vendar z dodatno prilagodljivostjo. Znotraj SUMPRODUCT lahko uporabite tudi druge funkcije, da še bolj razširite njegovo funkcionalnost.




V tem primeru sem želel najti seštevek vseh prodanih izdelkov. Za to sem uporabil formulo = SUMPRODUCT (B2: B8, C2: C8). Tukaj je B2: B8 prvi niz (količina prodanih izdelkov), C2: C8 pa drugi niz (cena vsakega izdelka). Formula nato količino vsakega prodanega izdelka pomnoži s svojo ceno in nato vse skupaj sešteje za skupno prodajo.

Formula: = SUMPRODUCT (array1, [array2], [array3], ...)





9. TRIM

Funkcija = TRIM je še posebej uporabna, če delate z naborom podatkov z več presledki ali neželenimi znaki. Funkcija vam omogoča enostavno odstranjevanje teh presledkov ali znakov iz podatkov, kar vam omogoča natančne rezultate med uporabo drugih funkcij.




V tem primeru sem želel odstraniti vse dodatne presledke med besedami Miška in blazinica v A7. Za to sem uporabil formulo = TRIM (A7).




Formula je preprosto odstranila dodatne presledke in rezultat je dala podlogo za miško z enim presledkom.

Formula: = TRIM (besedilo)





10. NAJDI / ISKANJE

Zaokrožene stvari so funkcije FIND / SEARCH, ki vam bodo pomagale izolirati določeno besedilo znotraj nabora podatkov. Obe funkciji sta si precej podobni v svojem početju, razen ene velike razlike - funkcija = FIND vrne samo velika in velika slovarska ujemanja. Medtem funkcija = SEARCH nima takšnih omejitev. Te funkcije so še posebej uporabne pri iskanju nepravilnosti ali unikatnih identifikatorjev.




V tem primeru sem želel ugotoviti, kolikokrat se je Gui pojavil znotraj Guiding Tech, za kar sem uporabil formulo = FIND (A2, B2), ki je prinesla rezultat 1. Zdaj, če bi želel najti število krat. ' gui 'se je pojavil znotraj Guiding Tech, zato bi moral uporabiti formulo = SEARCH, ker ni velika in majhna.

Formula za iskanje: = NAJDI (find_text, znotraj_text, [start_num])

Formula za iskanje: = ISKANJE (find_text, znotraj_text, [start_num])

Tudi na vodilni tehniki
#office 365
Kliknite tukaj in si oglejte našo pisarniško stran s 365 članki



Obvladajte analizo podatkov

Te bistvene funkcije Microsoft Excel vam bodo zagotovo pomagale pri analizi podatkov, vendar ta seznam opraska le vrh ledene gore. Excel vključuje tudi več drugih naprednih funkcij za doseganje določenih rezultatov. Če vas zanima več o teh funkcijah, nas obvestite v spodnjem razdelku s komentarji.

Naslednje: Če želite Excel bolj učinkovito uporabljati, si v naslednjem članku oglejte nekaj priročnih bližnjic Excel za navigacijo, ki jih morate poznati.