Excel speciális képletek (Tartalomjegyzék)
- Bevezetés az Excel speciális képleteibe
- Példák az Excel speciális képletére
Bevezetés az Excel speciális képleteibe
Miután elérte az excel középszintjét, keményen kell dolgoznia a haladó szintre lépéshez. A haladó szintre lépéshez tisztában kell lennie a gyakran használt speciális képletekkel. Ebben a cikkben bemutatom a 10 legfontosabb speciális képletet, amelyeket az összes excel tanulónak tudnia kell. Kövesse ezt a cikket a tanuláshoz és a felfedezéshez.
Példák az Excel speciális képletére
Nézzük meg néhány példával, hogy hogyan használhatjuk az Összetett Képleteket Excelben.
Itt töltheti le az Advanced Formulas Excel sablont - Advanced Formulas Excel Template1. példa - Részleges VLOOKUP funkció
Olyan helyzetbe kellett volna találnia, amikor a VLOOKUP hibát okoz, még akkor is, ha a keresési értékben kissé hiányzott egyeztetés. Például, ha az Abhishek Sinha név fizetését keresik, és ha csak Abhishek van, akkor a VLOOKUP nem tudja behozni az adatokat.
A csillag karakter használatával a keresési érték mindkét végén beolvashatjuk az adatokat a részleges keresési értékhez.
Az 1. táblázatban teljes neve és fizetése van, de a 2. táblázatban csak részleges nevek vannak, és meg kell találnom az egyes alkalmazottak fizetését.
1. lépés: Nyissa meg a VLOOKUP képletet az E3 cellában. A keresési érték kiválasztása előtt helyezze el a csillagot (*) a keresési érték mindkét oldalára.
2. lépés: Mint általában, a VLOOKUP képletet kitöltheti, és megkapjuk az eredményeket.
A fenti képlet használata után a kimenetet az alábbiakban mutatjuk be.
Ugyanezt a képletet használják más cellákban is.
2. példa - COUNTIFS a kezelői szimbólumokkal
A listában szereplő dolgok számításához a COUNTIF és IFS függvényt kell használnia. Számíthatunk olyan operátor szimbólumok alapján is, mint például nagyobb, mint (>) kevesebb, mint (<) és egyenlő jel (=).
Most vessünk egy pillantást az alábbi adatokra. Ha számolni szeretné a DÉL-régió számlájának teljes számát 2018. január 10. után?
3. példa - Ha állapot van ÉS ÉS VAGY feltételekkel
A logikai funkciók a mindennapi tevékenységeink részei. A következő szintre lépéshez el kell sajátítania őket. Ha a bónuszt több feltétel alapján számítja ki, akkor a munka elvégzéséhez fészkelnie kell ÉS vagy VAGY feltétellel IF feltétellel.
Tegyük fel, hogy ki kell számítania a bónusz összegét az egyes osztályokhoz az osztály és a szolgálati év alapján, amelyre szüksége van ezekre a funkciókra. Az alábbi kritériumok alapján ki kell számítanunk a bónuszt.
Ha a szolgálat 4 év feletti, és az osztály értékesítési vagy támogatási bónusz 50000, vagy egyéb bónusz 25000.
Alkalmazza az alábbi képletet a bónusz összeg megszerzéséhez.
A fenti képlet használata után az alábbiakban bemutatott kimenet látható.
Ugyanaz a képlet az E3 – E9 cellákban.
4. példa - SZÖVEG funkció, amely dinamikus irányt mutat
Tegyük fel, hogy napi értékesítési táblát tart fenn, és minden nap frissítenie kell a táblát. A táblázat elején van egy cím, amelyben szerepel: „Összevont értékesítési adatok ÉÉÉÉ-HH-NN-tól HH-HH-NN-ig”. Ahogy a táblázat frissül, módosítani kell a fejléc dátumát. Hát nem bosszantó feladat ugyanazt a dolgot újra és újra megtenni? Ezt a címsort dinamikussá tehetjük a TETX, MIN és Max funkciókkal, a kapcsolt operátor szimbólummal együtt (&).
5. példa - INDEX + MATCH + MAX a legmagasabb eladót keresni
Van egy listája az értékesítési személyekről és az eladásokról, amelyeket a nevük szerint végeztek. Hogyan tudja megmondani, ki a legjobb vagy a legmagasabb eladó a listán? Természetesen számos más módszerrel is megmondhatjuk az eredményt, de ez a funkció visszateheti a legmagasabb eladót a tételből.
Alkalmazza az alábbi funkciót a legmagasabb eladó név megszerzéséhez.
A fenti képlet használata után az alábbiakban bemutatott kimenet látható.
6. példa - Szerezze be az egyedi értékek számát a listából
Ha sok másolatú értéke van, és el kell mondania, hogy hány egyedi érték létezik, hogyan kell mondani? Megmondhatja az ismétlődő érték eltávolításával, de ez nem az egyedi érték számának dinamikus megadása.
E tömb funkció segítségével megtudhatjuk a tétel egyedi értékeit.
Alkalmazza az alábbi képletet az egyedi értékek listájához.
# 7. Példa - Használja a Megnevezett tartományt a legördülő dinamikává
Ha gyakran dolgozik egy legördülő listával, és frissíti a legördülő listát, akkor az értékek törléséhez vagy hozzáadásához vissza kell térnie a forráslista-tartományba. Ezután vissza kell térnie a legördülő cellákhoz, és ismét frissítenie kell a legördülő listát.
Ha azonban létrehozhat nevű tartományt a legördülő listához, akkor a legördülő listát dinamikussá és naprakészé teheti.
Hozzon létre egy névtartományt az alábbi ábra szerint.
Most menjen a legördülő cellára, és nyissa meg a legördülő párbeszédpanelt.
Forrásban nyomja meg az F3 billentyűt, ez megjeleníti az összes meghatározott nevet, válassza ki a legördülő lista nevét.
Ok, egy legördülő lista készen áll, és automatikusan frissíti az értékeket, ha megváltozik a legördülő lista.
8. példa - Megszabaduljon a hibaértékektől az IFERROR függvény használatával
Biztos vagyok abban, hogy hibaértékeket tapasztalt a VLOOKUP, Division számítások használata közben. Ezen hibaértékek kezelése unalmas feladat. Ezeket a hibaértékeket azonban megszabadulhatjuk az IFERROR függvény használatával a képletben.
A fenti képlet használata után az alábbiakban bemutatott kimenet látható.
Ugyanaz a képlet, mint a többi cellában.
9. példa - Használja a PMT funkciót saját EMI diagram készítéséhez
Manapság az EMI lehetőség nem furcsa dolog mindannyiunk számára. Kiválóan becsülhetjük meg saját EMI diagramunkat a PMT funkció használatával. Kövesse az alábbi lépéseket saját diagram készítéséhez.
Vigye az alábbi képletet a B4 cellába az EMI összegének meghatározásához.
10. példa - INDEX + MATCH a VLOOKUP funkció alternatívájaként
Remélem tudatában van a VLOOKUP funkció korlátozásának. Az egyik fő korlátozás a VLOOKUP, hogy az adatokat balról jobbra, nem jobbról balra tudja lekérni. Az adatok nem mindig vannak jól szervezett és felhasználásra készek. A keresett érték oszlop gyakran a keresési érték bal oldalán található, így a VLOOKUP ezekben az esetekben nem segít.
Az INDEX + MATCH funkció kombinációja szolgál a VLOOKUP funkció alternatívájaként.
A fenti képlet használata után a kimenetet az alábbiakban mutatjuk be.
Ugyanezt a képletet használják más cellákban is.
A termékazonosító alapján ki kell vonnunk az értékesítési értékeket. A fő táblázatban a termékazonosító az értékesítési oszlop jobb oldalán található. A VLOOKUP tehát nem tudja letölteni az adatokat. Az adatok beolvasásához használja az alábbi képletet.
Ajánlott cikkek
Ez egy útmutató az Excel speciális képleteire. Itt tárgyaljuk, hogyan lehet az Advanced Formula-t Excelben használni, a gyakorlati példákkal és a letölthető Excel sablonnal együtt. Megnézheti más javasolt cikkeinket -
- Hogyan kell használni az Excel INDEX függvényt?
- Speciális Excel | Adatbázis funkció
- Példák az Excel TRIM képletére
- Útmutató az Excel OFFSET Formula-hoz