Excel-képletek csalólap: Speciális útmutató

Így hát átfutotta az alapokat, megbirkózott a köztes dolgokkal, és most már készen áll a nagyágyúkkal – az Excel fejlett képleteivel – való küzdelemre! Az Excel számos speciális függvényt és képletet tartalmaz a kifinomult számításokhoz, ezért hasznos egy útmutató, amely pontosan meghatározza a szükségeseket.

Ez az Excel képletek csalólapja fejlett előrejelzési képleteket, statisztikai elemzéseket, adatkezelési funkciókat, hibakezelést és még sok mást tartalmaz.

Ez a hivatkozás felvértezi Önt ezen speciális funkciók használatának ismereteivel. Minden képletet világos magyarázatok, szintaxis és gyakorlati példák kísérnek, amelyek segítik a középhaladó Excel-felhasználókat, hogy haladó felhasználókká váljanak.

Kérjük, töltse le és nyomtassa ki a csalólapot, és tartsa kéznél.

Ok, kezdjük.

Először is térjünk be a tömbképletekre.

Tartalomjegyzék

• Tömbképletek
  • 1. EGYEDI funkció
  • 2. RENDEZÉS funkció
  • 3. SZŰRŐ funkció
• Az Excel függvények véletlenszerű rendezése
  • 1. RANDBETWEEN funkció
  • 2. RANDARRAY funkció
• Speciális előrejelzési képletek a Microsoft Excelben
  • 1. ELŐREJELZÉS.ETS funkció
  • 2. TREND funkció
• Speciális statisztikai képletek
  • 1. PERCENTILIS függvény
  • 2. QUARTIL függvény
• Speciális adatelemzési és manipulációs képletek
  • 1. TRANSPOSE funkció
  • 2. FREKVENCIA funkció
  • 3. GETPIVOTDATA funkció
• Speciális hibakezelés
• Speciális keresési képletek
  • 1. XLOOKUP funkció
  • 2. XMATCH funkció
• Végső gondolatok

Tömbképletek

Megmutatjuk, hogyan rendezheti és szűrheti adatait manuálisan. A haladó felhasználók ezt programozottan, tömbképletekkel teszik meg.

A tömbképletek lehetővé teszik, hogy egyidejűleg több cellán végezzen számításokat. Ez a három fő funkció:

1. EGYEDI

2. FAJTA

3. SZŰRŐ

E funkciók némelyike ​​csak a Microsoft Excel legújabb verzióiban érhető el.

1. EGYEDI funkció

Az EGYEDI függvény elfogad egy tartományt, és visszaadja az egyedi értékek listáját.

Tegyük fel, hogy rendelkezik értékesítési adatokkal ruházati cikkekre vonatkozóan. Az A oszlop egyedi tételeinek megkereséséhez használja a következő képletet:

=EGYEDI(A2:A6)

2. RENDEZÉS funkció

A SORT funkció egy tartomány tartalmát rendezi. A szintaxis a következő:

RENDEZÉS(tömb, [rendezési_index], [rendezési_sorrend], [oszlop szerint])

• tömb : a rendezendő értéktartomány.

• sort_index : a rendezendő oszlop (alapértelmezés szerint 1)

• sort_order : 1 növekvő (alapértelmezett) vagy 2 csökkenő).

• by_col : IGAZ oszlop szerinti rendezéshez (alapértelmezett) vagy FALSE a sor szerinti rendezéshez.

Az utolsó három argumentum nem kötelező, és az alapértelmezett értékek általában a kívántak.

A mintaadatok első oszlop szerinti rendezéséhez használja a következő képletet:

=RENDEZÉS(A2:C6)

3. SZŰRŐ funkció

A SZŰRŐ funkció lehetővé teszi egy tartomány szűrését egy adott feltétel alapján. Ez a szintaxis:

=SZŰRŐ(tömb, tartalmazza, [ha_üres])

• array : a szűrni kívánt tartomány.

• include : a feltétel, amely meghatározza, hogy mely értékeket kell szűrni.

• if_empty : megadja, hogy mit adjon vissza, ha egyetlen érték sem felel meg a szűrési feltételeknek (alapértelmezett: "").

Tegyük fel, hogy szűrni szeretné a mintaadatok sorait, hogy csak ott jelenjenek meg, ahol az eladási érték nagyobb, mint 15 USD. Használja ezt a képletet:

=SZŰRŐ(A2:C6, C2:C6>15)

Az Excel függvények véletlenszerű rendezése

A közbenső csalás megmutatja, hogyan kell használni a RAND függvényt, amely 0 és 1 közötti véletlenszámot állít elő.

A haladó Excel-felhasználók tudják, hogyan használhatják a véletlenszerűsítési függvényeket a mintaadatok gyors generálására.

1. RANDBETWEEN funkció

A RANDBETWEEN függvény rugalmasabb, mint a RAND, mert az alsó és felső számokat 0 és 1 helyett másként is megadhatja.

Ha 1 és 100 közötti számokat szeretne előállítani, írja be ezt a képletet az A1 cellába:

=RANDBETWEEN(1; 100)

Ezután másolja a cellát annyi sorba és oszlopba, amennyit csak akar. Másodpercekbe telik egy véletlenszerű számokból álló rács létrehozása:

2. RANDARRAY funkció

Lehet, hogy úgy gondolja, jó lenne elkerülni a RANDBETWEEN funkció kézi másolását. Szuper haladáshoz használhatja az új RANDARRAY funkciót a Microsoft Excel legújabb verziójában.

A szintaxis a következő:

RANDARRAY([sorok], [oszlopok], [perc], [max.], [egész szám])

• sorok : sorok száma

• oszlopok : oszlopok száma

• min : legalacsonyabb szám

• max : legmagasabb szám

• egész szám : alapértelmezés szerint IGAZ, egyébként decimális számokat használ.

Ha 1 és 100 közötti véletlen számokat szeretne generálni hat sorban és kilenc oszlopban, használja a következő képletet:

=RANDARRAY(6; 9; 1; 100; IGAZ)

Speciális előrejelzési képletek a Microsoft Excelben

Az Excel előrejelzési funkciói a jövőbeli értékek előrejelzésére szolgálnak a meglévő adattrendek alapján. Ezek a funkciók segítenek azonosítani a mintákat és előrevetíteni a trendeket az adatok alapján.

1. ELŐREJELZÉS.ETS funkció

A régebbi ELŐREJELZÉS függvényt egy sor újabb függvény váltotta fel az Excel 2016-ban.

A funkciót a kívánt előrejelzési modell alapján választhatja ki. Például a FORECAST.ETS függvény az exponenciális simítási algoritmust használja.

A szintaxis a következő:

ELŐREJELZÉS.ETS(céldátum, értékek, idővonal)

• target_date : az a dátum, amelyre számított értéket szeretne kapni.

• értékek : a történelmi adatok.

• idővonal : dátumok tartománya

Tegyük fel, hogy az A oszlopban január 1. és 5. közötti dátumok, a B oszlopban pedig eladási összegek szerepelnek. Ez a képlet előrejelzi a következő eladási összeget:

=FORECAST.ETS(“2023. január 6.”, B2:B6, A2:A6)

2. TREND funkció

A TREND függvény a legkisebb négyzetek módszerén alapuló értékkészletet vetít ki. Egy tömböt ad vissza. A szintaxis a következő:

TREND(ismert_y, [ismert_x], [új_x], [állandó])

• ismert_y : y értékek tartománya

• ismert_x : x értékek tartománya

• new_x : számított értékek tartománya

Gyakran az ismert_y az adatpontok, míg az ismert_x a dátumok.

Ugyanazokat az adatokat használva, mint az előző példában, beírhatja az alábbi képletet a C2 cellába. A rendszer értékkészletet generál.

=TREND(B2:B6, A2:A6)

Speciális statisztikai képletek

A fejlett statisztikai függvények közé tartozik a percentilisek és kvartilisek kiszámítása. Néhány matematikai függvény rendelkezésre áll a visszamenőleges kompatibilitás érdekében, de ajánlatos a legfrissebb verziókat használni.

1. PERCENTILIS függvény

Ez a függvény kiszámítja azon adatpontok százalékos arányát, amelyek egy adott érték alá esnek. A szintaxis a következő:

PERCENTILE.INC(tömb, k)

• tömb : a cellatartomány

• k : a percentilis 0-tól 1-ig

Tegyük fel, hogy a B oszlopban lévő adatok 70. percentilisét szeretné kiszámítani. Használja ezt a képletet:

=PERCENTIL.INC(B2:B6, 0,7)

2. QUARTIL függvény

Ez a függvény a százalékos függvény egy változata, de negyedeket használ az adatok felosztására. Ez a szintaxis:

QUARTILE.INC(tömb, kvart)

• tömb : az adatok tartománya

• quart : 1 a 25. percentilishez, 2 az 50., 3 a 75. és 4 a maximumhoz.

Az alábbi képlet kiszámítja az A oszlopban lévő adatok első kvartilisét.

=QUARTIL.INC(A2:A5;1)

Speciális adatelemzési és manipulációs képletek

Számos speciális funkció lehetővé teszi az adatok formátumának váltását, a gyakorisági eloszlások elemzését, és adatok kinyerését a pivot táblákból.

1. TRANSPOSE

2. FREKVENCIA

3. GETPIVOTDATA

1. TRANSPOSE funkció

Néha szeretné áthelyezni a sorokban lévő adatokat oszlopokba és fordítva. Ezt megteheti manuálisan, vagy használhatja helyette a TRANSPOSE funkciót.

Tegyük fel, hogy az A2, A3 és A4-es cellákban a „Póló”, „Pucsi” és „Farmer” tételek vannak. Ezeket szeretné oszlopfejlécekké alakítani. Ez a függvény egyetlen sorban adja vissza az értékeket:

=TRANSPOSE(A2:A4)

2. FREKVENCIA funkció

Ez a függvény kiszámítja az értékek gyakorisági eloszlását egy adatkészleten belül. Ez a szintaxis:

FREQUENCY(adattömb, bins_tömb)

• data_array : értéktartomány.

• bins_array : a használandó intervallumok.

Tegyük fel, hogy értékesítési adatai vannak a B oszlopban, és szeretné elemezni az értékek gyakorisági eloszlását aszerint, hogy hány összeg:

• 20 alatt.

• 20-tól 80-ig.

• 80 felett.

Ez három tartályt jelent, és a következő képlettel számítható ki:

=FREKVENCIA(A2:A6; {20,80})

Ha többet szeretne megtudni az Excel frekvenciaelosztásáról, tekintse meg ezt a videót:

3. GETPIVOTDATA funkció

Ezzel a funkcióval összefoglalt információkat nyerhet ki a pivot táblákból. Ez a szintaxis:

GETPIVOTDATA(adatmező, pivot_table, [field1, item1], [field2, item2], …)

• data_field : az adatmező vagy érték, amelyet le szeretne kérni a pivot táblából.

• pivot_table : hivatkozás a pivot táblára.

• field1, item1, stb. : a szűrni kívánt mező/elem párok.

Tegyük fel, hogy van egy pivot táblája az eladott cikkek színe alapján. A piros cikkek eladásainak kivonásához használja ezt a képletet:

=GETPIVOTADATOK ("Értékesítés", A1, "Szín", "Piros")

Speciális hibakezelés

Még a legalapvetőbb Excel-képletek is hibákat okozhatnak. A középhaladó felhasználóknak tudniuk kell, hogyan kell az ISERROR-t használni a hibák kezelésére. A haladó felhasználóknak ismerniük kell az ERROR.TYPE funkciót a hibaazonosításhoz.

Az ERROR.TYPE függvény segít azonosítani a cellán vagy képleten belüli hiba adott típusát.

A különböző hibatípusoknak megfelelő numerikus értéket ad vissza, például #N/A, #VALUE!, #REF! stb.

Tegyük fel, hogy hiba van az A1 cellában, és szeretné azonosítani a hiba típusát. A következő képlet a konkrét hibának megfelelő számot adja vissza:

=HIBA.TÍPUS(A1)

Ezt több funkcióval kombinálhatja, hogy a hiba típusától függően eltérően reagáljon. Ezek a leggyakoribb hibák és értékeik:

1. #NULLA! (egy tartományban nem található közös cella)

2. #DIV/0! ( osztás nullával vagy üres cellával)

3. #ÉRTÉK! (nem megfelelő adattípus vagy argumentum a képletben)

4. #REF! (egy hivatkozott cellát töröltek, vagy van egy )

5. #NÉV? (Az Excel nem ismeri fel a függvényt vagy a tartományt)

6. #SZÁM! (érvénytelen számérték)

7. #N/A (az érték nem található)

Tegyük fel, hogy három konkrét hibatípust szeretne kezelni. Használja ezt a képletet egy adott hibaüzenet megjelenítéséhez a típus alapján:

=HA(ISERROR(C2), IF(HIBA.TÍPUS(C2)=2, “Osztás nulla hibával”, IF(ERROR.TYPE(C2)=3, “Érvénytelen érték hiba”, IF(HIBA.TÍPUS(C2) )=7, "Az érték nem található hiba", "Egyéb hiba"))), C2)

Speciális keresési képletek

Kezdő és középhaladó csalólapjaink egy . Íme néhány speciális beállítás:

1. XLOOKUP

2. XMATCH

1. XLOOKUP funkció

Ez a keresési funkció lehetővé teszi, hogy egy tartományban értéket keressen, és egy másik oszlopból vagy tartományból visszaadja a megfelelő értéket.

Nagyobb sokoldalúságot kínál, mint az egyszerűbb keresési funkciók, mint pl. Ez a szintaxis:

XLOOKUP(keresési_érték, keresési_tömb, visszatérési_tömb, [egyezési_mód], [keresési_mód], [ha_nem található])

• lookup_value : a keresni kívánt érték.

• lookup_array : a keresés tartománya.

• return_array : az a tartomány, amely a megfelelő értéket fogja mutatni.

• match_mode : pontos egyezés (0), következő kisebb (1), következő nagyobb (-1) vagy helyettesítő karakteres egyezés (2).

• search_mode : -1 felülről lefelé, 1 alulról felfelé vagy 2 bináris kereséshez.

• if_not_found : beállítja a visszatérési értéket, ha nem található egyezés.

Tegyük fel, hogy egy ruhadarab első előfordulására szeretne adattartományban keresni, és visszaadni az eladási összegeket. Ez a képlet megkeresi a „Hoodie” szöveget, és visszaadja az értéket a szomszédos cellában, ha megtalálja:

=XKERESÉS("pulcsi, A2:A6, B2:B6)

2. XMATCH funkció

Ezzel a funkcióval megkeresheti egy adott érték pozícióját egy tartományon vagy tömbön belül. Ez a szintaxis:

XMATCH(keresési_érték, keresési_tömb, [egyezési_típus], [keresési_mód])

• lookup_value : a keresni kívánt érték.

• lookup_array : A keresni kívánt tartomány.

• match_type : pontos egyezés (0), következő legkisebb (-1), következő legnagyobb (1).

• search_mode : bináris keresés (1) vagy lineáris keresés (2).

Tegyük fel, hogy meg szeretné keresni egy sárga elem első előfordulását a B oszlopban lévő tartományban. Használja ezt a képletet:

=XMATCH(“sárga”, B2:B6, 0)

Végső gondolatok

Ez a csalólap a funkciók széles skáláját fedi le, a statisztikai elemzéstől kezdve a keresési képleteken, az adatkezelési technikákon és a hibakezelési stratégiákon keresztül.

A bemutatott példák és magyarázatok segítenek tisztázni ezeket a fejlett képleteket, és még a korlátozott tapasztalattal rendelkezők számára is hozzáférhetővé teszik őket.

Ahogy elkezdi beépíteni ezeket az Excel-feladatokba, haladó úton halad Excel-készségeinek haladó szintre emelése felé.

De ne feledje, ez a csalólap csak a jéghegy csúcsa. Az igazán elképesztő dolgok akkor történnek, amikor kreatív leszel, összekevered ezeket a formulákat, és egyedi kihívásaid megoldására szabod őket. Az Excel olyan, mint egy vászon, és ezek a képletek jelentik az Ön palettáját – tehát hajrá, fesse meg remekművét!