10 alapvető Microsoft Excel-függvény az adatelemzéshez
Vegyes Cikkek / / November 29, 2021
1. ÖSSZEKAPCSOL
Az =CONCATENATE az adatelemzés egyik legfontosabb funkciója, mivel lehetővé teszi szövegek, számok, dátumok stb. kombinálását. több cellából egy. A funkció különösen hasznos a különböző cellákból származó adatok egyetlen cellává történő kombinálásához. Hasznos lehet például marketingkampányok nyomkövetési paramétereinek létrehozásához, API-lekérdezések készítéséhez, szöveg hozzáadásához számformátumhoz és számos egyéb dologhoz.
![Az alapvető Excel-függvények adatelemzése 1](/f/ef4844c9256f6aeeb7180f57b791172d.jpg)
A fenti példában azt akartam, hogy a hónap és az eladások együtt legyenek egyetlen oszlopban. Ehhez a =CONCATENATE(A2, B2) képletet használtam a C2 cellában, hogy 700 januári dollárt kapjak.
Képlet: =CONCATENATE(egyesíteni kívánt sejteket)
2. LEN
A =LEN egy másik praktikus funkció az adatelemzéshez, amely lényegében az adott cellában lévő karakterek számát adja ki. A funkció túlnyomórészt karakterkorlátozott címcímkék vagy leírások létrehozásakor használható. Akkor is hasznos lehet, ha megpróbálja kideríteni a különböző egyedi azonosítók közötti különbségeket, amelyek gyakran meglehetősen hosszadalmasak és nem a megfelelő sorrendben.
![Az alapvető Excel-függvények adatelemzése 2](/f/d7cadade746a26cd545ab58daa0e9ad7.jpg)
A fenti példában meg akartam számolni a havonta elért megtekintések számát. Ehhez a =LEN(C2) képletet használtam a D2 cellában, hogy 5 legyen az eredmény.
Képlet: =LEN(sejt)
3. VLOOKUP
A =VLOOKUP valószínűleg az egyik legfelismerhetőbb funkció azok számára, akik ismerik az adatelemzést. Használhatja a táblázat adatainak bemeneti értékkel való egyeztetésére. A funkció kétféle illesztési módot kínál – pontos és közelítő, amelyet a keresés tartománya vezérel. Ha a tartományt FALSE-ra állítja, akkor pontos egyezést fog keresni, de ha IGAZ-ra állítja, akkor hozzávetőleges egyezést fog keresni.
![Az alapvető Excel-függvények adatelemzése 3](/f/6e053d7c4c477481f07d505cdf6a876f.jpg)
A fenti példában meg akartam nézni a megtekintések számát egy adott hónapban. Ehhez a =VLOOKUP("Jun", A2:C13, 3) képletet használtam a G4 cellában, és 74992 lett az eredmény. Itt a "Jun" a keresési érték, az A2:C13 a táblázattömb, amelyben a "Jun" kifejezést keresem, a 3 pedig annak az oszlopnak a száma, amelyben a képlet megtalálja a megfelelő júniusi nézeteket.
A funkció használatának egyetlen hátránya, hogy csak oszlopokba rendezett adatokkal működik, innen ered a függőleges keresés elnevezés. Tehát ha az adatokat sorokba rendezte, először ezt kell tennie transzponálja a sorokat oszlopokba.
Képlet: =VLOOKUP(keresési_érték, táblázat_tömb, oszlop_index_száma, [tartomány_keresése])
4. INDEX/MATCH
Hasonlóan a VLOOKUP funkcióhoz, az INDEX és a MATCH függvények jól használhatók adott adatok kereséséhez egy bemeneti érték alapján. Az INDEX és a MATCH együtt használva legyőzheti a VLOOKUP azon korlátait, hogy rossz eredményeket adjon (ha nem vigyáz). Tehát ha ezt a két függvényt kombinálja, akkor pontosan meghatározhatják az adatreferenciát, és egyetlen dimenziós tömbben kereshetnek egy értéket. Ez számként adja vissza az adatok koordinátáit.
![Az alapvető Excel-függvények adatelemzése 4](/f/b13227460bc7cb0fa84f9d41f380d98d.jpg)
A fenti példában meg akartam nézni a januári megtekintések számát. Ehhez az =INDEX (A2:C13, MATCH("január", A2:A13,0), 3) képletet használtam. Itt az A2:C13 az az adatoszlop, amelyet a képletnek vissza akarok adni, a "Jan" az az érték, amelyet egyeztetni akarok, az A2:A13 pedig a oszlopban, amelyben a képlet „jan” értéket talál, a 0 pedig azt jelenti, hogy szeretném, ha a képlet pontosan egyezést találna érték.
Ha hozzávetőleges egyezést szeretne találni, a 0-t 1-gyel vagy -1-gyel kell helyettesítenie. Így az 1 megtalálja a legnagyobb értéket, amely kisebb vagy egyenlő a keresési értékkel, és a -1 a legkisebb értéket, amely kisebb vagy egyenlő, mint a keresési érték. Vegye figyelembe, hogy ha nem 0, 1 vagy -1 értéket használ, a képlet 1, by értéket fog használni.
![Az alapvető Excel-függvények adatelemzése 5](/f/47d28f577f724d05e8f8ee098e91c6b7.jpg)
Most, ha nem akarja keményen kódolni a hónap nevét, lecserélheti a cellaszámra. Tehát a fent említett képletben a "Jan" kifejezést F3-ra vagy A2-re cserélhetjük, hogy ugyanazt az eredményt kapjuk.
Képlet: =INDEX(a visszaküldeni kívánt adatok oszlopa, MATCH(közös adatpont, amelyet egyeztetni próbál, a másik adatforrás oszlopa, amely a közös adatponttal rendelkezik, 0))
5. MINIFS/MAXIFS
A =MINIFS és a =MAXIFS nagyon hasonlóak a =MIN és =MAX függvényekhez, kivéve azt a tényt, hogy lehetővé teszik a minimális/maximális értékkészlet felvételét, és bizonyos kritériumok alapján történő egyeztetését. Lényegében tehát a függvény a minimális/maximális értékeket keresi, és megfelelteti a bemeneti feltételekkel.
![Az alapvető Excel-függvények adatelemzése 7](/f/fae987f5603a9782d0f1136c8a385c01.jpg)
A fenti példában a tanuló neme alapján kívántam megtalálni a minimális pontszámokat. Ehhez a =MINIFS képletet használtam (C2:C10, B2:B10, "M"), és a 27-es eredményt kaptam. Itt a C2:C10 az az oszlop, amelyben a képlet a pontszámokat keresi, a B2:B10 az az oszlop, amelyben a képlet a kritériumokat (a nemet) keresi, és az "M" a kritérium.
![Az alapvető Excel-függvények adatelemzése 6](/f/e122c3fc7d8eafccda0a531139c4dea9.jpg)
Hasonlóképpen a maximális pontszámok eléréséhez a =MAXIFS(C2:C10, B2:B10, "M") képletet használtam, és 100-at kaptam.
A MINIFS képlete: =MINIFS(min_tartomány, kritérium_tartomány1, kritérium1,...)
A MAXIFS képlete: =MAXIFS(max_tartomány, kritérium_tartomány1, kritérium1,...)
6. AVERAGEIFS
Az =AVERAGEIFS függvény lehetővé teszi, hogy egy vagy több feltétel alapján átlagot találjon egy adott adatkészlethez. A funkció használata során ne feledje, hogy minden kritérium és átlagos tartomány eltérő lehet. Az =AVERAGEIF függvényben azonban mind a feltétel-, mind az összegtartománynak azonos mérettartománnyal kell rendelkeznie. Észreveszi az egyes és többes szám különbségét e függvények között? Nos, ott kell vigyázni.
![Az alapvető Excel-függvények adatelemzése 8](/f/a2eb06f67342cde2eb6ee86ea6a8d351.jpg)
Ebben a példában az átlagos pontszámot szerettem volna megtalálni a tanulók neme alapján. Ehhez az =AVERAGEIFS(C2:C10, B2:B10, "M") képletet használtam, és 56,8 lett az eredmény. Itt a C2:C10 az a tartomány, amelyben a képlet az átlagot keresi, a B2:B10 a kritériumtartomány, és az "M" a kritérium.
Képlet: =AVERAGEIFS(átlagos_tartomány, kritérium_tartomány1, kritérium1,...)
7. COUNTIFS
Ha most meg szeretné számolni, hogy egy adatkészlet hány példánynak felel meg bizonyos feltételeknek, akkor a =COUNTIFS függvényt kell használnia. Ez a funkció lehetővé teszi, hogy korlátlan számú feltételt adjon a lekérdezéshez, és ezáltal a legegyszerűbb módja annak, hogy a beviteli feltételek alapján megtalálja a számot.
![Az alapvető Excel-függvények adatelemzése 9](/f/39635ab1eeffded1042cfd7c6249a7f2.jpg)
Ebben a példában azt szerettem volna megkeresni, hogy hány férfi vagy női diák teljesített (azaz >=40). Ehhez a =COUNTIFS(B2:B10, "M", C2:C10, ">=40") képletet használtam. Itt a B2:B10 az a tartomány, amelyben a képlet az első kritériumot keresi (nem), az "M" az első feltétel, C2:C10 az a tartomány, amelyben a képlet a második feltételt (jeleket) keresi, a ">=40" pedig a másodikat kritériumok.
Képlet: =COUNTIFS(kritérium_tartomány1, kritérium1,...)
8. SZUMTERMÉK
Az =SUMPRODUCT függvény segít a tartományok vagy tömbök összeszorozásában, majd a szorzatok összegét adja vissza. Ez egy meglehetősen sokoldalú funkció, és használható tömbök, például COUNTIFS vagy SUMIFS számlálására és összegzésére, de még nagyobb rugalmassággal. A SUMPRODUCT egyéb funkcióit is használhatja, hogy még tovább bővítse a funkcióit.
![Az alapvető Excel-függvények adatelemzése 10](/f/f4d91eb407af82ac40d18cb36e6bbd13.jpg)
Ebben a példában az összes eladott termék összegét akartam megtalálni. Ehhez a =ÖSSZEG(B2:B8, C2:C8) képletet használtam. Itt a B2:B8 az első tömb (az eladott termékek mennyisége), a C2:C8 pedig a második tömb (az egyes termékek ára). A képlet ezután megszorozza az egyes eladott termékek mennyiségét az árával, majd az összeset összeadja, hogy elérje a teljes eladást.
Képlet: =SUMPRODUCT(tömb1, [tömb2], [tömb3],...)
9. TRIM
A =TRIM funkció különösen hasznos, ha több szóközt vagy nem kívánt karaktert tartalmazó adatkészlettel dolgozik. A funkció segítségével könnyedén eltávolíthatja ezeket a szóközöket vagy karaktereket az adatokból, így pontos eredményeket kaphat más funkciók használata közben.
![Az alapvető Excel-függvények adatelemzése 11](/f/04381c96e973bee237ac85395449540a.jpg)
Ebben a példában el akartam távolítani az összes felesleges szóközt az Egér és pad szavak között az A7-ben. Ehhez a =TRIM(A7) képletet használtam.
![Az alapvető Excel-függvények adatelemzése 12](/f/6d8e3d59bdbc3535366f7e4eeeedb7de.jpg)
A képlet egyszerűen eltávolította a felesleges szóközöket, és egyetlen szóközzel megjelenítette az egérpadot.
Képlet: =TRIM(szöveg)
10. KERESÉS/KERESÉS
Lekerekítik a KERESÉS/KERESÉS funkciókat, amelyek segítenek elkülöníteni egy adott szöveget egy adatkészleten belül. Mindkét függvény meglehetősen hasonló abban, amit csinál, kivéve egy fő különbséget – a =FIND függvény csak a kis- és nagybetűkre érzékeny egyezéseket ad vissza. Eközben a =SEARCH függvénynek nincsenek ilyen korlátozásai. Ezek a funkciók különösen hasznosak anomáliák vagy egyedi azonosítók keresésekor.
![Az alapvető Excel-függvények adatelemzése 13](/f/94b07268b0748d7e1ecddc2c94ec57ee.jpg)
Ebben a példában azt szerettem volna megkeresni, hogy hányszor jelent meg a „Gui” a Guiding Tech-en belül, amelyhez a =FIND(A2, B2) képletet használtam, amely az 1-es eredményt adta. Ha szeretném megkeresni, hogy hányszor jelent meg a „gui” a Guiding Tech-en belül, akkor a =SEARCH képletet kell használnom, mert nem különbözteti meg a kis- és nagybetűket.
A keresés képlete: =FIND(szöveg keresése, szövegen belül, [kezdeti_szám])
Keresési képlet: =SEARCH(szöveg keresése, szövegen belül, [kezdeti_szám])