10 tärkeää Microsoft Excel -toimintoa tietojen analysointiin
Sekalaista / / November 29, 2021
1. LIITTÄ
=CONCATENATE on yksi tärkeimmistä data-analyysin toiminnoista, koska sen avulla voit yhdistää tekstiä, numeroita, päivämääriä jne. useista soluista yhdeksi. Toiminto on erityisen hyödyllinen yhdistettäessä tietoja eri soluista yhdeksi soluksi. Se on kätevä esimerkiksi markkinointikampanjoiden seurantaparametrien luomiseen, API-kyselyjen luomiseen, tekstin lisäämiseen numeromuotoon ja moniin muihin asioihin.
![Keskeisten Excel-funktioiden tietojen analyysi 1](/f/ef4844c9256f6aeeb7180f57b791172d.jpg)
Yllä olevassa esimerkissä halusin kuukauden ja myynnin yhteen sarakkeeseen. Tätä varten olen käyttänyt kaavaa =CONCATENATE(A2, B2) solussa C2 saadakseni tulokseksi Jan $700.
Kaava: =CONCATENATE(solut, jotka haluat yhdistää)
2. LEN
=LEN on toinen kätevä data-analyysin toiminto, joka olennaisesti tulostaa merkkien määrän missä tahansa solussa. Toimintoa voidaan käyttää pääasiassa luotaessa otsikkotunnisteita tai kuvauksia, joissa on merkkirajoitus. Se voi olla hyödyllistä myös, kun yrität selvittää eroja eri yksilöllisten tunnisteiden välillä, jotka ovat usein melko pitkiä eivätkä oikeassa järjestyksessä.
![Tärkeimmät Excel-funktiot -tietojen analyysi 2](/f/d7cadade746a26cd545ab58daa0e9ad7.jpg)
Yllä olevassa esimerkissä halusin laskea kuukausittaisten katselukertojen lukumäärän. Tätä varten käytin kaavaa =LEN(C2) solussa D2 saadakseni tulokseksi 5.
Kaava: =pituus(solu)
3. VHAKU
=VLOOKUP on luultavasti yksi tunnistetuimmista toiminnoista kaikille, jotka tuntevat data-analyysin. Voit käyttää sitä yhdistämään taulukon tiedot syötearvoon. Toiminto tarjoaa kaksi tapaa sovittaa - tarkka ja likimääräinen, jota ohjaa haun alue. Jos asetat vaihteluväliksi EPÄTOSI, se etsii tarkan vastaavuuden, mutta jos asetat sen arvoon TOSI, se etsii likimääräistä vastaavuutta.
![Tärkeimmät Excel-funktiot -tietojen analyysi 3](/f/6e053d7c4c477481f07d505cdf6a876f.jpg)
Yllä olevassa esimerkissä halusin tarkastella katselukertojen määrää tietyssä kuukaudessa. Tätä varten käytin kaavaa =VLOOKUP("Jun", A2:C13, 3) solussa G4 ja sain tulokseksi 74992. Tässä "Jun" on hakuarvo, A2:C13 on taulukkotaulukko, josta etsin sanaa "Jun" ja 3 on sen sarakkeen numero, josta kaava löytää vastaavat näkymät kesäkuulle.
Tämän toiminnon käytön ainoa haittapuoli on, että se toimii vain sarakkeisiin järjestettyjen tietojen kanssa, mistä johtuu nimi — vertikaalinen haku. Joten jos tietosi on järjestetty riveihin, sinun on ensin transponoi rivit sarakkeiksi.
Kaava: =VHAKU(lookup_value, table_array, col_index_num, [range_lookup])
4. HAKEMISTO/OSA
Aivan kuten VLOOKUP-toiminto, INDEX- ja MATCH-funktiot ovat hyödyllisiä haettaessa tiettyä dataa syötearvon perusteella. INDEX ja MATCH, kun niitä käytetään yhdessä, voivat voittaa VLOOKUPin rajoitukset väärien tulosten tuottamiseen (jos et ole varovainen). Joten kun yhdistät nämä kaksi funktiota, ne voivat määrittää tietoviitteen ja etsiä arvoa yhden ulottuvuuden taulukosta. Tämä palauttaa datan koordinaatit numeroina.
![Keskeisten Excel-funktioiden tietojen analyysi 4](/f/b13227460bc7cb0fa84f9d41f380d98d.jpg)
Yllä olevassa esimerkissä halusin tarkastella näyttökertojen määrää tammikuussa. Tätä varten käytin kaavaa =INDEKSI (A2:C13, MATCH("Tam", A2:A13,0), 3). Tässä A2:C13 on tietosarake, jonka haluan kaavan palauttavan, "Tam" on arvo, jonka haluan täsmäyttää, A2:A13 on sarake, jossa kaava löytää "tammi" ja 0 tarkoittaa, että haluan kaavan löytävän tarkan vastaavuuden arvo.
Jos haluat löytää likimääräisen vastaavuuden, sinun on korvattava 0 luvulla 1 tai -1. Jotta 1 löytää suurimman arvon, joka on pienempi tai yhtä suuri kuin hakuarvo, ja -1 löytää pienimmän arvon, joka on pienempi tai yhtä suuri kuin hakuarvo. Huomaa, että jos et käytä arvoa 0, 1 tai -1, kaava käyttää arvoa 1, by.
![Keskeisten Excel-funktioiden tietojen analyysi 5](/f/47d28f577f724d05e8f8ee098e91c6b7.jpg)
Nyt jos et halua koodata kuukauden nimeä, voit korvata sen solun numerolla. Joten voimme korvata "Jan" yllä mainitussa kaavassa F3:lla tai A2:lla saadaksesi saman tuloksen.
Kaava: =INDEX(palautettavien tietojen sarake, MATCH(yhteinen tietopiste, jota yrität täsmäyttää, toisen tietolähteen sarake, jolla on yhteinen tietopiste, 0))
5. MINIFS/MAXIFS
=MINIFS ja =MAXIFS ovat hyvin samankaltaisia kuin funktiot =MIN ja =MAX, paitsi että niiden avulla voit ottaa minimi-/maksimiarvojoukon ja sovittaa ne myös tietyillä kriteereillä. Eli pohjimmiltaan funktio etsii minimi-/maksimiarvoja ja sovittaa ne syöttöehtoihin.
![Keskeisten Excel-funktioiden tietojen analyysi 7](/f/fae987f5603a9782d0f1136c8a385c01.jpg)
Yllä olevassa esimerkissä halusin löytää vähimmäispisteet opiskelijan sukupuolen perusteella. Tätä varten käytin kaavaa =MINIFS (C2:C10, B2:B10, "M") ja sain tulokseksi 27. Tässä C2:C10 on sarake, josta kaava etsii pisteitä, B2:B10 on sarake, josta kaava etsii kriteerit (sukupuoli), ja "M" on kriteeri.
![Keskeisten Excel-funktioiden tietojen analyysi 6](/f/e122c3fc7d8eafccda0a531139c4dea9.jpg)
Vastaavasti maksimipisteiden saamiseksi käytin kaavaa =MAXIFS(C2:C10, B2:B10, "M") ja sain tulokseksi 100.
MINIFS: n kaava: =MINIFS(min_alue, ehtojen_väli1, ehdot1,...)
Kaava MAXIFS: lle: =MAXIFS(max_range, kriteerit_väli1, ehdot1,...)
6. AVERAGEIFS
=AVERAGEIFS-funktion avulla voit löytää keskiarvon tietylle tietojoukolle yhden tai useamman kriteerin perusteella. Kun käytät tätä toimintoa, sinun tulee muistaa, että jokainen kriteeri ja keskimääräinen vaihteluväli voivat olla erilaisia. =AVERAGEIF-funktiossa sekä kriteerialueen että summa-alueen on kuitenkin oltava sama kokoalue. Huomaatko yksikön ja monikon eron näiden funktioiden välillä? No, siellä kannattaa olla varovainen.
![Keskeisten Excel-funktioiden tietojen analyysi 8](/f/a2eb06f67342cde2eb6ee86ea6a8d351.jpg)
Tässä esimerkissä halusin löytää keskimääräisen pistemäärän opiskelijoiden sukupuolen perusteella. Tätä varten käytin kaavaa = AVERAGEIFS(C2:C10, B2:B10, "M") ja sain tulokseksi 56,8. Tässä C2:C10 on alue, josta kaava etsii keskiarvoa, B2:B10 on kriteerialue ja "M" on kriteeri.
Kaava: =AVERAGEIFS(keskimääräinen_väli, ehtojen_väli1, ehdot1,...)
7. COUNTIFS
Jos nyt haluat laskea, kuinka monta kertaa tietojoukko täyttää tietyt ehdot, sinun on käytettävä =COUNTIFS-funktiota. Tämän toiminnon avulla voit lisätä kyselyyn rajattomasti ehtoja, mikä tekee siitä helpoimman tavan löytää syöttöehtojen perusteella laskettu määrä.
![Keskeisten Excel-funktioiden tietojen analyysi 9](/f/39635ab1eeffded1042cfd7c6249a7f2.jpg)
Tässä esimerkissä halusin selvittää niiden mies- tai naisopiskelijoiden lukumäärän, jotka saivat läpäisypisteitä (eli >=40). Tätä varten käytin kaavaa =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Tässä B2:B10 on alue, josta kaava etsii ensimmäiset kriteerit (sukupuoli), "M" on ensimmäinen kriteeri, C2:C10 on alue, josta kaava etsii toista kriteeriä (merkit), ja ">=40" on toinen kriteeri.
Kaava: =COUNTIFS(kriteerit_väli1, ehdot1,...)
8. SUMMATUOTE
=SUMPRODUCT-funktio auttaa kertomaan alueet tai taulukot yhteen ja palauttaa sitten tulojen summan. Se on melko monipuolinen toiminto, ja sitä voidaan käyttää laskemaan ja summaamaan taulukoita, kuten COUNTIFS tai SUMIFS, mutta lisää joustavuutta. Voit myös käyttää muita SUMPRODUCTin toimintoja laajentaaksesi sen toimintoja entisestään.
![Keskeisten Excel-funktioiden tietojen analyysi 10](/f/f4d91eb407af82ac40d18cb36e6bbd13.jpg)
Tässä esimerkissä halusin löytää kaikkien myytyjen tuotteiden summan. Tätä varten käytin kaavaa =SUMMATUOTE(B2:B8, C2:C8). Tässä B2:B8 on ensimmäinen matriisi (myytyjen tuotteiden määrä) ja C2:C8 on toinen taulukko (kunkin tuotteen hinta). Kaava moninkertaistaa sitten kunkin myydyn tuotteen määrän sen hinnalla ja laskee sen sitten yhteen kokonaismyynnin saamiseksi.
Kaava: =SUMMATUOTE(jono1, [taulukko2], [taulukko3],...)
9. TRIMMATA
=TRIM-toiminto on erityisen hyödyllinen, kun työskentelet tietojoukon kanssa, jossa on useita välilyöntejä tai ei-toivottuja merkkejä. Toiminnon avulla voit helposti poistaa nämä välilyönnit tai merkit tiedoistasi, jolloin saat tarkkoja tuloksia käyttäessäsi muita toimintoja.
![Keskeisten Excel-funktioiden tietojen analyysi 11](/f/04381c96e973bee237ac85395449540a.jpg)
Tässä esimerkissä halusin poistaa kaikki ylimääräiset välilyönnit sanojen Mouse and pad väliltä A7:ssä. Tätä varten käytin kaavaa =TRIM(A7).
![Keskeisten Excel-funktioiden tietojen analyysi 12](/f/6d8e3d59bdbc3535366f7e4eeeedb7de.jpg)
Kaava yksinkertaisesti poisti ylimääräiset välilyönnit ja toimitti tuloksena Hiirimatto yhdellä välilyönnillä.
Kaava: =TRIM(teksti)
10. ETSI/HAE
Pyöristää asioita ovat FIND/SEARCH-toiminnot, jotka auttavat sinua eristämään tietyn tekstin tietojoukosta. Molemmat funktiot ovat melko samankaltaisia siinä, mitä ne tekevät, lukuun ottamatta yhtä suurta eroa - =FIND-funktio palauttaa vain isot ja pienet kirjaimet. Sillä välin =SEARCH-funktiolla ei ole tällaisia rajoituksia. Nämä toiminnot ovat erityisen hyödyllisiä, kun etsitään poikkeavuuksia tai yksilöllisiä tunnisteita.
![Keskeisten Excel-funktioiden tietojen analyysi 13](/f/94b07268b0748d7e1ecddc2c94ec57ee.jpg)
Tässä esimerkissä halusin selvittää, kuinka monta kertaa 'Gui' esiintyi Guiding Techissä, jolle käytin kaavaa =FIND(A2, B2), joka antoi tuloksen 1. Jos haluaisin sen sijaan selvittää, kuinka monta kertaa "gui" esiintyi Guiding Techissä, minun on käytettävä =SEARCH-kaavaa, koska se ei ole kirjainkoolla.
Etsintäkaava: =FIND(etsi_teksti, tekstin sisällä, [aloitusnumero])
Hakukaava: =SEARCH(etsi_teksti, tekstin sisällä, [aloitusnumero])