Excel képletek csalólap: középhaladó útmutató

Tehát sikerült elsajátítania a – köszönet neked! Most úgy döntött, hogy feldobja a dolgokat, és belemerül a fejlettebb dolgokba, igaz? Nos, szerencséd van, mert összeállítottunk egy praktikus köztes Excel csalólapot, csak neked!

Ez a Microsoft Excel középszintű csalólap a témák széles skáláját fedi le, amelyek szintre emelik a numerikus elemzés, a szövegfeldolgozás, valamint az összetett dátum- és időlogika terén szerzett ismereteit.

Az Excel Intermediate Cheat Sheet asztali referenciaként való felhasználásával mélyen elmerülhet a Microsoft Excel hatékonyabb képleteiben és funkcióiban.

Most mindenképpen olvass tovább, de nyomtasd ki és mentsd is el az alábbi csalólapot!

Kezdjük el!

Tartalomjegyzék

• Középhaladó matematikai képletek
  • Feltételes logika
• Köztes statisztikai képletek
  • 1. MINA és MAXA
  • 2. COUNTA és COUNTIF
• Excel képletek pénzügyi elemzéshez
  • 1. TERMÉK funkció
  • 2. QUOTIENT és LOG funkciók
  • Funkciók befektetési forgatókönyvekhez
• Köztes Excel dátum és idő képletek
  • 1. Köztes hónap funkciók
  • 2. Köztes heti funkciók
  • 3. Köztes idő függvények
• Excel-függvények feltételes és logikai algebrához
  • 1. IF funkció
  • 2. ÉS funkció
  • 3. VAGY funkció
  • 4. NEM funkció
• Referencia cellák
  • 1. KÖZVETETT funkció
  • 2. INDEX funkció
  • 3. OFFSET funkció
• Köztes szöveges képletek
  • 1. TEXTJOIN funkció
  • 2. REPLACE funkció
  • 3. SUBSTITUTE Funkció
• Köztes keresési képletek
• Képletek a Microsoft Excel hibák kezelésére
  • 1. IFERROR funkció
  • 2. IFNA funkció
• Képlet és függvény gyorsbillentyűk
• Köztes cellaformázás
• Végső gondolatok

Középhaladó matematikai képletek

Kezdő csalólapunk a leggyakoribb kezdő matematikai függvényeket fedte le, például a SUM függvényt. A középhaladó felhasználóknak meg kell ismerkedniük ezekkel az összetettebb funkciókkal:

• Az ABS() egy szám abszolút értékét adja vissza.

• Az SQRT() egy szám négyzetgyökét adja vissza

• A RAND() 0 és 1 közötti véletlenszámot ad meg

Feltételes logika

A középhaladó felhasználóknak tudniuk kell, hogyan kell több feltétel alapján matematikai számításokat végezni az alábbi függvények használatával:

• SUMIFS(összeg_tartomány, feltételek_tartománya1, kritérium1, [….])

• COUNTIFS(feltételtartomány1, kritérium1, [feltételtartomány2, kritérium2], …)

• AVERAGEIFS(feltételtartomány1, kritérium1, [feltételtartomány2, kritérium2], …)

A függvények kiértékelik a megadott feltételeket, és IGAZ vagy HAMIS logikai értéket állítanak elő.

Tegyük fel, hogy a következő értékesítési adatok vannak az A, B és C oszlopban:

A piros pólók eladásainak összegének kiszámításához használja a SUMIFS képletet:

=SUMIFS(C2:C6, A2:A6, „póló”, B2:B6, „piros”)

A piros pólós sorok számának megszámlálásához használja a COUNTIFS képletet:

=COUNTIFS(C2:C6, A2:A6, „póló”, B2:B6, „piros”)

A piros pólók átlagos eladásainak kiszámításához használja az AVERAGEIFS képletet:

=ÁTLAGOS (C2:C6, A2:A6, „Póló”, B2:B6, „Piros”)

Köztes statisztikai képletek

Az olyan alapvető funkciókon kívül, mint a MIN és MAX, az Excel statisztikai képletek szélesebb skáláját kínálja a középhaladó felhasználók számára. Íme néhány a leghasznosabbak közül:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA és MAXA

A gyakrabban használt MIN és MAX függvények figyelmen kívül hagyják a szöveges értékeket.

Az alternatív MINA és MAXA függvények a szöveget és a számokat is figyelembe veszik, amikor a legmagasabb vagy legalacsonyabb értékeket keresik. A szöveges értékeket úgy értékeli ki a rendszer, mintha nullák lennének.

A különbségek az alábbi képen láthatók. Az első sor 10-es minimális értéket, míg a második sor 0-t ad ki egy szöveges érték jelenléte miatt.

2. COUNTA és COUNTIF

A COUNTA függvény egy tartományban használatos.

A COUNTIF függvény kevésbé specifikus, mivel egy adott feltételnek vagy kritériumnak megfelelő cellák számának megszámlálására szolgál egy tartományon belül.

Ezekkel a függvényekkel megszámolhatja az oszlopban lévő különálló értékek számát is. Ez a videó bemutatja, hogyan kell ezt megtenni.

Excel képletek pénzügyi elemzéshez

Számos képletet érdemes használni a pénzügyi elemzés, például a befektetések előrejelzése során:

• TERMÉK(szám1, [szám2…])

• QUOTIENT(számláló, nevező)

• NAPLÓ(szám, [alap])

1. TERMÉK funkció

Tegyük fel, hogy egy 1000 dolláros kezdeti befektetés jövőbeli értékét szeretné kiszámítani (B1 cella) 4%-os éves kamatláb mellett (B2 cella) öt év után (B3 cella).

Használja a TERMÉK függvényt a jövőbeli érték kiszámításához a következő képlettel:

=B1 * TERMÉK(1 + B2)^B3

Ez a képlet visszaadja befektetésének jövőbeli értékét.

2. QUOTIENT és LOG funkciók

A következő lépésben ki kell számolni, hogy az adott kamat mellett hány évnek kell eltelnie ahhoz, hogy egy befektetés értéke megduplázódjon.

Használja a QUOTIENT függvényt a NAPLÓ függvénnyel kombinálva ennek kiszámításához az alábbiak szerint:

=Hányados(NAPLÓ(2) / NAPLÓ(1 + B2), 1)

Ez a képlet a mintaadatokkal együtt 17-et ad vissza, ami azt jelzi, hogy 17 évnek kell eltelnie ahhoz, hogy a befektetés értéke megduplázódik 4%-os éves kamat mellett.

Ez a kép :

Funkciók befektetési forgatókönyvekhez

Ezek olyan funkciók, amelyek adott pénzügyi forgatókönyvekhez alkalmasak. Ezek közül az egyik pontosan az lehet, amire szüksége van:

• NPV – Az NPV (nettó jelenérték) függvény kiszámítja a befektetés nettó jelenértékét a jövőbeli pénzáramlások sorozata alapján, diszkontráta alapján.

• ACCRINT – Az ACCRINT függvény kiszámítja egy időszakos kamatot fizető értékpapír felhalmozott kamatát. Ez hasznos az értékpapíron az utolsó fizetési dátumtól egy adott elszámolási dátumig megszerzett kamat meghatározásához.

• INTRATE – Az INTRATE függvény kiszámítja a teljesen befektetett értékpapír kamatlábait.

• PMT – A PMT funkció kiszámítja a hitelviszonyt megtestesítő értékpapír teljes kifizetését.

• IRR – Az IRR függvény a belső megtérülési rátát adja meg.

• HOZAM – A HOZAM függvény egy értékpapír hozamát adja meg kamatláb, névérték és lejárat alapján.

Köztes Excel dátum és idő képletek

Az Excel alapvető dátum- és időfüggvényei közé tartozik a MOST és a TODAY függvény az aktuális dátumhoz. A középhaladó felhasználóknak azt is tudniuk kell, hogyan lehet egy adott dátumból összetevőket kinyerni a következőkkel:

• NAP (dátum)

• HÓNAP(dátum)

• ÉV(dátum)

A =HÓNAP(“2023. április 23.″) képlet 4-es eredményt ad vissza a 4. hónapra. Hasonlóképpen, a DAY és YEAR függvények 23-at és 2023-at adnak vissza.

1. Köztes hónap funkciók

A középhaladó felhasználók néha azzal foglalkoznak, hogy hozzáadják vagy megtalálják a hónap végét.

• EDATE(kezdési_dátum,hónapok_száma)

• EOMONTH(kezdési_dátum,hónapok_száma)

Például az =EDATE(“2023. április 23.”, 2) képlet két hónappal később számítja ki a dátumot.

Az =EOMONTH(2023. április 23.”, 2) képlet két hónappal később számítja ki a hónap végét.

Az eredmény „2023. június 30.”, amely figyelembe veszi, hogy júniusban már csak harminc nap van. Ha 3 hónapot ad meg, az eredmény „2023. július 31.” lesz.

2. Köztes heti funkciók

A SUM és WEEKDAY függvények kombinációjával megszámolhatja a teljes munkanapok számát egy adott dátumtartományon belül.

A WEEKDAY függvény a hét napját adja vissza egy adott dátumhoz. A második paraméter határozza meg a számozási rendszert, az alapértelmezett érték vasárnaptól szombatig.

Ha dátumsorozata van egy B1:B40 cellatartományban, használja ezt a képletet a munkanapok kiszámításához:

=SZUM(–(HÉTNAP(B1:B40,2)>5))

3. Köztes idő függvények

Az YEARFRAC függvény kiszámítja az év két dátum közötti részét.

Az IDŐÉRTÉK függvény arra szolgál, hogy a szövegként ábrázolt időt decimális számmá alakítsa át, amely egy 24 órás nap arányát jelzi.

Az eredmény egy Excel sorozatszám, ahol az 1 a teljes 24 órás napot, a 0,5 a 12 órát, a 0,25 a 6 órát és így tovább.

Tegyük fel, hogy az A1 cellában van egy „17:45” szövegformátumú idő, és decimális számmá szeretné konvertálni . Ez a képlet:

=IDŐÉRTÉK(A1)

A 0,74-es érték a 24 órás nap azon arányát jelenti, amely a hat óra előtti tizenöt perccel telt el.

Az időcella időformátumról szöveges formátumra történő módosításához. Érdemes ezt százalékban formázni, hogy egyértelműbb legyen.

Excel-függvények feltételes és logikai algebrához

A feltételes és logikai függvények elengedhetetlenek az Excelben történő döntéshozatalhoz. A következő funkciók kombinálhatók az alapvetőbb Excel-képletekkel a hatékony logika érdekében:

1. IF(feltétel, érték_ha_igaz, érték_ha_hamis):

2. ÉS(feltétel1, feltétel2, …)

3. VAGY(feltétel1, feltétel2, …)

4. NEM (feltétel1, feltétel2, …)

Az IF függvény kiértékel egy feltételt, és különböző értékeket ad vissza attól függően, hogy a feltétel igaz vagy hamis.

Az ÉS, VAGY és NEM függvények ellenőrzik, hogy mely feltételek igazak, és ezek alapján dönthet.

Íme néhány példa a korábban használt értékesítési adatok alapján.

1. IF funkció

Tegyük fel, hogy a „Low Sales” szöveget szeretné megjeleníteni, ha a cikkek eladásai 15-nél kisebbek. Ha az eladások magasabbak, a „Magas eladások” értéket egy új oszlopban szeretné megjeleníteni.

Használja ezt a képletet, és másolja le a többi sorba:

=IF(C2<15, "alacsony értékesítés", "magas értékesítés")

2. ÉS funkció

Tegyük fel, hogy IGAZ vagy HAMIS értéket szeretne megjeleníteni a legalább 15 eladással rendelkező pólóknál. Használja ezt a képletet:

=ÉS(A3=”póló”, C3>=15)

3. VAGY funkció

Tegyük fel, hogy több feltételt szeretne tesztelni, és IGAZ értéket szeretne visszaadni, ha bármelyik feltétel teljesül, egyébként pedig HAMIS értéket, akkor a VAGY függvényt használja.

Például, ha egy teszt pontszáma van az A1 és B1 cellában, és tudni akarjuk, hogy valamelyik pontszám 80 felett van-e, akkor használhatjuk az =VAGY (A1>80, B1>80) értéket . Ez IGAZ értéket ad vissza, ha bármelyik pontszám (vagy mindkettő) 80 felett van, és HAMIS, ha mindkettő 80 vagy kevesebb.

4. NEM funkció

Tegyük fel, hogy ellenőrizni szeretné, hogy a cikk nem kapucnis pulóver. Használja ezt a képletet:

=NEM(A2="pulcsi"

Referencia cellák

A kezdők gyorsan megismerik az abszolút és relatív hivatkozások használatát , például $A$1 vagy B2. A középhaladó felhasználóknak meg kell ismerniük a közvetett hivatkozások, indexek és eltolások használatát:

1. INDIRECT(ref_text)

2. INDEX(tartomány; sor_száma, oszlop_száma)

3. OFFSET(hivatkozási_cella, sorok, oszlopok):

1. KÖZVETETT funkció

Az INDIRECT függvény a megadott cellahivatkozás szövegként beírt értékét adja vissza. A B3 cellára való hivatkozáshoz használja a következő képletet:

=KÖZVETETT("B3")

Az egyszerű cellahivatkozásokkal szemben az az előnye, hogy ez a függvény dinamikus hivatkozást ad. Ez azt jelenti, hogy az Excel automatikusan frissíti a hivatkozást, ha a táblázat szerkezete megváltozik, pl. egy sor törlődik.

2. INDEX funkció

Az INDEX függvény egy megadott tartományon belüli cellákra való hivatkozásra szolgál sor- és oszlopszám alapján.

Ha egy hatsoros táblázat B3 cellájára szeretne hivatkozni, használja a következő képletet:

=INDEX(B1:B6;3)

3. OFFSET funkció

Az OFFSET függvény egy olyan cellát vagy tartományt ad vissza, amely meghatározott számú sorral és oszloppal távolabb van a referenciacellától.

Ha azt a cellaértéket szeretné megjeleníteni, amely két cellával keresztben van, és egy cellával lejjebb van az A1-től, használja ezt a képletet:

=ELTOLÁS(A1; 2; 1)

Íme a képletek működés közben:

Köztes szöveges képletek

A kezdőknek ismerniük kell az olyan szöveges függvényeket, mint a LEFT, amely egy vagy több karaktert von ki egy karakterlánc bal oldaláról. A hatékony felhasználónak ismernie kell az ehhez hasonló funkciókat:

1. TEXTJOIN(határoló, ignore_empty, text1, [text2, …])

2. REPLACE(régi_szöveg, kezdő_szám, karakterek száma, új_szöveg)

3. SUBSTITUTE(szöveg, régi_szöveg, új_szöveg, [példányszám])

1. TEXTJOIN funkció

A TEXTJOIN függvény összefűzi a cellákat az Ön által megadott határolóval. Azt is megadhatja, hogy hol hagyja figyelmen kívül az üres cellákat.

Az A oszlop egyes elemeinek vesszővel tagolt listájának létrehozásához használja a következő képletet:

=TEXTJOIN(“, „, IGAZ, A2:A4)

2. REPLACE funkció

A helyettesítő funkcióval megadhatja a célba cserélni kívánt karakterlánc kezdő pozícióját és hosszát.

Ha egy karakterlánc első két karakterét „XX”-re szeretné cserélni, használja ezt a képletet:

=TEXTJOIN(A2; 1; 2; "XX")

3. SUBSTITUTE Funkció

A helyettesítő függvény lecseréli egy szöveges karakterlánc meghatározott előfordulásait egy másik szöveges karakterláncon belül egy új szöveges karakterláncra.

A „farmer” szó „leggings” szóra való helyettesítéséhez használja ezt a képletet:

=SUBSTITUTE(A3, "farmer", "leggings")

Íme a képletek működés közben:

Köztes keresési képletek

Az olyan gyakori keresési funkciókat, mint a VLOOKUP és a MATCH, a kezdők csalólapja tárgyalja.

A középhaladó felhasználóknak meg kell ismerkedniük a CHOOSE keresési funkcióval:

• KIVÁLASZT(index_szám, érték1, [érték2, …])

A CHOOSE függvény egy értéket ad vissza egy értéklistából egy megadott indexszám alapján.

Tegyük fel, hogy van egy pólóméretek oszlopa, amely 1, 2 vagy 3 címkével van ellátva. Egy kis, közepes és nagy kategóriát szeretne megjeleníteni ugyanabban a sorban. Használja ezt a képletet:

=VÁLASZTÁS(A1, "Kicsi", "Közepes", "Nagy")

Képletek a Microsoft Excel hibák kezelésére

A kezdő cheatsheet felsorolta azokat a tipikus hibaüzeneteket, amelyeket az Excel használata során láthat. A középhaladó felhasználóknak képesnek kell lenniük képletekkel kezelni a hibákat.

1. IFERROR(érték, ha_hiba)

2. IFNA(érték, érték_ha_na)

1. IFERROR funkció

A nullával való osztás gyakori hibák, de az Excel alapértelmezett megjelenítése a #DIV/0! nem lesz egyértelmű minden felhasználó számára.

Használja az IFERROR függvényt a standard hiba helyére a „Nem osztható nullával” kifejezésre ezzel a képlettel:

=IFIBOR(A1/B1, "Nem osztható nullával")

Ezen a képen a harmadik sorban a nyers hiba és a negyedik sort kezelő IFERROR függvény látható.

2. IFNA funkció

Az Excel IFNA függvénye a #N/A hibák elkapására és kezelésére szolgál.

Ennek a csalólapnak egy másik része a VÁLASZTÁS funkciót mutatta. Ez a függvény N/A hibát ad vissza, ha az indexszám nem egész szám.

Tegyük fel, hogy a póló mérete 1,5, és a felhasználó tévesen azt gondolja, hogy ez valahol a kis és közepes kategóriát fogja mutatni. Ehelyett #N/A hibát fog mutatni .

Hasznosabb hiba megadásához használja ezt a képletet:

=IFNA(VÁLASZTÁS(A2, "Alacsony", "Közepes", "Magas"), "Érvénytelen index"

Képlet és függvény gyorsbillentyűk

Számos billentyűparancs segítségével hatékonyabban dolgozhat a munkalappal.

• F2 : Szerkessze az aktív cellát, és helyezze a beszúrási pontot a cellatartalom végére.

• F9 : Kiszámítja és megjeleníti a képlet kiválasztott részének eredményét.

• Ctrl + Shift + Enter : Adjon meg egy tömbképletet.

• Shift + F3 : Nyissa meg a Funkció beszúrása párbeszédpanelt.

• Esc : Törölje a képlet bevitelét, és térjen vissza az eredeti cellatartalomhoz.

Előfordulhat, hogy ezen Excel-billentyűparancsok némelyike ​​nem érhető el minden nyelven vagy billentyűzet-kiosztáson.

Köztes cellaformázás

A pénzügyi elemzésben kulcsfontosságú a számokat, dátumokat és pénznemeket tartalmazó cellák megfelelő formázása. A cellák formázásához kövesse az alábbi lépéseket:

1. Válassza ki a formázni kívánt cellákat.

2. Kattintson a jobb gombbal a kijelölt cellákra, és válassza a „Cellák formázása” lehetőséget.

3. Válassza ki a megfelelő kategóriát (pl. Szám, Pénznem, Dátum), és alkalmazza a kívánt formátumot.

Néhány általános formázási típus, amelyet figyelembe kell venni a pénzügyekben:

• Pénznem : 1234,56 USD

• százalék : 12,34%

• Könyvelés : (1234,00 USD)

• Időpont : 2023. május 10

Végső gondolatok

Ahogy egyre kényelmesebbé válik az itt kiemelt képletek és funkciók használatában, úgy találja majd, hogy egyre jobban felkészült az egyre összetettebb feladatok és projektek kezelésére.

A gyakorlás kulcsfontosságú bármely készség elsajátításához, ezért ne féljen kísérletezni ezekkel a képletekkel a mindennapi munkája során. A kezdő szinttől a középhaladó szintig elhozzák szakértelmét.

Ez csak egy ízelítő abból, amit az Excel köztes funkciói tehetnek a munka leegyszerűsítésére, az elemzések egyszerűsítésére és az adatokból való betekintések feltárására.

Az igazi varázslat akkor következik be, amikor elkezdi kombinálni a csalólapunkon található funkciókat, és egyedi igényeihez igazítja azokat. Szóval ne állj meg itt. Folytassa a felfedezést, kísérletezést, és rá fog jönni, hogy az Excel nem csak eszköz, hanem játékot is megváltoztat!

Úgy érzed, hogy a következő szintre szeretnéd emelni a dolgokat? Ellenőrizze a mi