A top 10 speciális Excel-képlet és függvény listája
# 1 - VLOOKUP képlet az Excelben
Ez a fejlett excel funkció az egyik leggyakrabban használt képlet az excelben. Ez főleg ennek a képletnek az egyszerűségének és annak alkalmazásának köszönhető, hogy egy bizonyos értéket más táblákból keres, amelynek egyetlen közös változója van ezeken a táblákon. Tegyük fel, hogy két táblázata van a vállalat alkalmazottainak fizetéséről és nevéről, az elsődleges oszlop a munkavállalói azonosító. A fizetést az A. táblázat B. táblázatából szeretné megkapni.
Használhatja a VLOOKUP-ot az alábbiak szerint.
Ez az alábbi táblázatot eredményezi, amikor ezt a speciális excel-képletet alkalmazzuk az Alkalmazotti fizetés oszlop más celláiban.
Húzza a képletet a többi cellába.
A VLOOKUP három fő elhatárolást tartalmaz:
- Az oszlop jobb oldalán nem lehet elsődleges oszlop, amelynek értékét egy másik táblából szeretné feltölteni. Ebben az esetben az Alkalmazotti fizetés oszlop nem lehet az Alkalmazotti azonosító előtt.
- A B táblázat elsődleges oszlopának duplikált értékeiben az első érték a cellába kerül.
- Ha új oszlopot illeszt be az adatbázisba (Ex Helyezzen be egy új oszlopot az alkalmazotti fizetés elé a B táblázatba), akkor a képlet kimenete eltérő lehet a képletben megemlített pozíció alapján (A fenti esetben az eredmény üres lenne)
# 2 - INDEX képlet az Excelben
Ezt a fejlett excel-képletet arra használjuk, hogy a sorok, oszlopok vagy mindkettő megadásával megkapjuk az adott táblázat cellájának értékét. Volt. Ahhoz, hogy a nevét a munkavállaló a 5 -én megfigyelés, Az alábbiakban az adatokat.
Használhatjuk a fejlett excel képletet az alábbiak szerint:
Ugyanaz az INDEX képlet használható arra, hogy értéket kapjon a sor mentén. A sor és az oszlop számának használata esetén a szintaxis a következőképpen néz ki:
A fenti képlet a „Rajesh Ved” szót adná vissza.
Megjegyzés: Ha egy másik sort a 5 -én sor, a képlet visszatér „Chandan Kale.” Ennélfogva a kimenet attól függ, hogy az adattáblában az idő múlásával milyen változások történnek.
# 3 - MATCH Formula az Excel-ben
Ez az Excel speciális képlet visszaadja a sor vagy oszlop számát, ha van egy adott karakterlánc vagy szám az adott tartományban. Az alábbi példában megpróbáljuk megtalálni a „Rajesh Ved” szót az Alkalmazott neve oszlopban.
A képlet az alábbiak szerint alakul:
A MATCH függvény értéke 5 lesz.
A 3 rd érv használják a pontos egyezés. Az Ön igényei szerint használhatja a +1 és -1 értékeket is.
Megjegyzés: Az INDEX és a MATCH kombinálható a VLOOKUP korlátozásának leküzdésére.
# 4 - HA ÉS Képlet az Excelben
Sok esetben van szükség zászlók létrehozására bizonyos korlátok alapján. Mindannyian ismerjük az IF alapszintaktikáját. Ezt a fejlett excel IF függvényt használjuk egy új mező létrehozására egy meglévő mező valamilyen korlátozása alapján. De mi van akkor, ha több oszlopot kell figyelembe vennünk egy zászló létrehozása közben. Volt. Az alábbi esetben meg szeretnénk jelölni minden olyan alkalmazottat, akinek fizetése meghaladja az 50 000, de a munkavállalói azonosító értéke 3-nál nagyobb.
Ilyen esetekben az IF ÉS-t használnánk. Kérjük, keresse meg ugyanezt a képernyőkép alatt.
0-ként adja vissza az eredményt.
Számos feltételünk vagy korlátozásunk lehet arra, hogy az AND használatával több oszlopon alapuló zászlót hozzunk létre.
# 5 - HA VAGY Formula az Excel-ben
Hasonlóképpen használhatjuk az OR függvényt az excelben is az AND helyett, ha sokak egyikének eleget kell tennünk.
A fenti esetekben, ha az egyik feltétel bármelyike teljesül, akkor a cellát 1 másik 0-ként fogjuk feltölteni. Az 1-et vagy a 0-at helyettesíthetjük néhány alsorral is dupla idézőjelekkel (“”).
# 6 - SUMIF képlet az Excelben
Egyes elemzések során előfordulhat, hogy az összeg vagy a számlálás függvény alkalmazásakor szűrnie kell néhány megfigyelést. Ilyen esetekben ez a fejlett excel SUMIF funkció az excelben a mi segítségünkre van. Ez az összes megfigyelést a fejlett excel-képletben megadott bizonyos feltételek alapján megszűri és összesíti. Volt. Mi van, ha csak azoknak a fizetéseknek az összegét szeretnénk megtudni, akiknél a munkavállalói azonosító 3-nál nagyobb.
A SUMIFS képlet alkalmazásával:
A Formula az eredményeket 322000-ként adja vissza.
A SUMIF helyett a COUNTIF használatával megszámolhatjuk a szervezet azon alkalmazottainak számát is, akiknél a munkavállalói azonosító 3-nál nagyobb.
# 7 - KÉSZÜLÉK Képlet az Excelben
Ez az excel speciális funkció az egyik képlet, amelyet több változatban is használhatunk. Ez a fejlett Excel-képlet segít abban, hogy több szöveges karakterláncot egyesítsünk egy szöveges karakterláncba. Például, ha egyetlen oszlopban szeretnénk megjeleníteni a munkavállalói azonosítót és a munkavállaló nevét.
Ehhez ezt a CONCATENATE képletet használhatjuk.
A fenti képlet „1Aman Gupta” -ot eredményez.
Még egy változatunk lehet, ha egyetlen kötőjelet teszünk az ID és a NAME közé. Volt. A CONCATENATE (B3, ”-“, C3) eredménye „1-Aman Gupta” lesz. Ezt akkor is felhasználhatjuk a VLOOKUP-ba, amikor a LOOKUP az excel értékben több változó keveréke.
# 8 - BAL, MID és RIGHT képlet az Excelben
Használhatjuk ezt a fejlett excel-képletet, hogy kivonjunk egy adott sztringet egy adott karakterláncból. Igényeink alapján felhasználható. Volt. Ha ki akarjuk vonni az első 5 karaktert a Munkavállaló nevéből, használhatjuk a BAL képletet az excelben, az oszlopnév és a második paraméter 5-ként.
A kimenet az alábbiakban látható:
A RIGHT képlet alkalmazása az excelben is ugyanaz. Csak annyit, hogy a karaktert a karakterlánc jobb oldalán néznénk. Az excelben található MID függvény esetében azonban meg kell adnunk a szükséges szöveges karakterlánc kezdő helyzetét és a karakterlánc hosszát.
# 9 - OFFSET képlet az Excelben
Ez a fejlett Excel funkció más funkciók kombinációjával, mint például a SUM vagy az ÁTLAG, dinamikus kapcsolatot adhat a számításhoz. Legjobb azokban az esetekben használni, amikor folyamatos sorokat illesztünk be egy meglévő adatbázisba. Az OFFSET Excel olyan tartományt biztosít számunkra, ahol meg kell említenünk a referencia cellát, a sorok számát és az oszlopokat. Volt. Ha meg akarjuk számolni az első 5 alkalmazott átlagát abban a cégben, ahol a munkavállalók fizetését alkalmazotti azonosító szerint rendezzük, akkor a következőket tehetjük. Az alábbi számítás mindig fizetett.
- Ez megadja az első 5 alkalmazott fizetésének összegét.
# 10 - TRIM képlet az Excelben
Ez a fejlett excel képlet arra szolgál, hogy megtisztítsa a nem fontos szóközöket a szövegből. Volt. Ha el akarjuk távolítani a szóközöket valamely név elején, használhatjuk azt az excelben a TRIM függvény használatával, az alábbiak szerint:
Az eredmény „Chandan Kale” lenne, szóköz nélkül Chandan előtt.
