Mik a képletek és függvények az Excel-ben, és hogyan kell használni őket?

Az adatok csak akkor kerülnek felhasználásra, ha valóban dolgozhat rajta, és Excel egy olyan eszköz, amely nagyszerű kényelmet nyújt, ha saját egyenleteket kell megfogalmaznia, vagy ki kell használnia a beépítetteket. Ebben a cikkben megtudhatja, hogyan működhet valójában ezekkel az Excel képletek hirdetési függvényekkel.

Itt van egy rövid áttekintés az itt tárgyalt témákról:

• Mi az a Formula?
• Excel képletek írása
• Képlet szerkesztése
• Képlet másolása / beillesztése
• Képletek elrejtése az Excelben
• Operátor elsőbbségi Excel képletek
• Mik az Excel funkciói?
• A legfontosabb funkciók

Mi az a Formula?

Általában a képlet egy tömörített módja annak, hogy bizonyos információkat szimbólumok formájában ábrázoljunk. Az Excelben a képletek olyan kifejezések, amelyeket bevihetünk egy Excel lap celláiba, és ennek eredményeként megjelennek a kimeneteik.

Az Excel képletek a következő típusúak lehetnek:

Excel képletek írása:

Képlet írása az Excel munkalap cellájába a következőket teheti:

• Válassza ki azt a cellát, ahol meg szeretné jeleníteni az eredményt
• Kezdetben írja be a „=” jelet, hogy az Excel tudja, hogy képletet fog beírni a cellába
• Ezt követően beírhatja a cellacímeket, vagy megadhatja a kiszámítani kívánt értékeket
• Például, ha két cella értékét szeretné hozzáadni, akkor a következőképpen írja be a cella címét:

• Kiválaszthatja azokat a cellákat is, amelyek összegét ki szeretné számolni, ha kiválasztja az összes szükséges cellát, miközben lenyomja a Ctrl billentyűt:

Képlet szerkesztése:

Abban az esetben, ha néhány korábban beírt képletet szeretne szerkeszteni, egyszerűen válassza ki a célképletet tartalmazó cellát, és a képletsávban elvégezheti a kívánt módosításokat. Az előző példában kiszámoltam az A1 és A2 összegét. Most ugyanezt szerkesztem, és megváltoztatom a képletet a két cellában lévő értékek szorzatának kiszámításához:

Ha ez megtörtént, nyomja meg az Enter billentyűt a kívánt kimenet megtekintéséhez.

Képlet másolása vagy beillesztése:

Az Excel nagyon hasznos, ha képleteket kell másolnia / beillesztenie. Valahányszor képletet másol, az Excel automatikusan gondoskodik az adott pozícióban szükséges cellahivatkozásokról. Ez egy as nevű rendszeren keresztül történik Relatív cellacímek .

Képlet másolásához válassza ki az eredeti képletet tartalmazó cellát, majd húzza azt addig a celláig, amelyhez a képlet másolata szükséges:

Mint a képen látható, a képletet eredetileg A3-ban írják, majd a B3 és C3 mentén húztam lefelé a B1, B2 és C1, C2 összegének kiszámításához anélkül, hogy kizárólag a cellacímeket írtam volna fel.

Abban az esetben, ha bármelyik cella értékét megváltoztatom, az Excel a kimenetet automatikusan frissíti a Relatív cellacímek , Abszolút cellacímek vagy Vegyes cellacímek .

Képletek elrejtése az Excelben:

Abban az esetben, ha el akar rejteni valamilyen képletet egy excel lapról, az alábbiak szerint teheti meg:

• Válassza ki azokat a cellákat, amelyek képletét el kívánja rejteni
• Nyissa meg a Betűtípus ablakot, és válassza a Védelem ablaktáblát
• Jelölje be a Rejtett opciót, és kattintson az OK gombra
• Ezután a szalag lapon válassza az Ellenőrzés lehetőséget
• Kattintson a Védelem lapra (a képletek nem működnek, ha ezt nem teszi meg)
• Az Excel arra kéri, hogy adjon meg egy jelszót a képletek későbbi felhasználása elrejtése érdekében

Az Excel képletek kezelői elsőbbsége:

Az Excel-képletek a BODMAS (zárójeles sorrend osztás szorzás összeadás kivonás) szabályait követik. Ha van egy zárójelet tartalmazó képlet, akkor a zárójelben lévő kifejezés a teljes képlet bármely más része előtt megoldódik. Vessen egy pillantást az alábbi képre:

Amint a fenti példában láthatja, van egy képletem, amely zárójelből áll. Tehát a BODMAS-szabályoknak megfelelően az Excel először megtalálja az A2 és B1 közötti különbséget, majd az eredményt hozzáadja A1-hez.

Mit a „függvények” az Excel-ben vannak?

Általában egy függvény meghatároz egy képletet, amelyet valamilyen adott sorrendben hajtanak végre. Az Excel hatalmas számot kínál beépített funkciók amelyek felhasználhatók a különböző képletek eredményének kiszámításához.

Az Excel képletei a következő kategóriákra vannak felosztva:

Most nézzük meg, hogyan lehet használni a legfontosabb és leggyakrabban használt Excel-képleteket.

A legfontosabb Excel-funkciók:

Íme néhány legfontosabb Excel-függvény, leírásuk és példájuk.

DÁTUM:

Az Excel egyik legfontosabb és legszélesebb körben használt Date függvénye a DATE függvény. Szintaxisa a következő:

Dátum (év, hónap, nap)

Ez a függvény olyan számot ad vissza, amely az adott dátumot képviseli az MS Excel dátum-idő formátumban. A DATE függvény a következőképpen használható:

PÉLDA:

1. = DÁTUM (2019,11,7)
2. = DATE (2019,11,7) -7 (az aktuális dátumot adja vissza - hét nap)

NAP:

Ez a függvény visszaadja a hónap napértékét (1-31). Szintaxisa a következő:

DAY (sorozatszám)

Itt a sorozatszám az a dátum, amelynek napját be szeretné szerezni. Bármilyen módon megadható, például valamilyen más funkció eredménye, amelyet a DATE függvény szolgáltat, vagy egy cellahivatkozás.

PÉLDA:

1. = NAP (A7)
2. = NAP (TODAY ())
3. = NAP (Dátum (2019, 11,8))

rendezés függvény c ++ tömb

HÓNAP:

A DAY függvényhez hasonlóan az Excel egy másik funkciót is biztosít, azaz a MONTH függvényt a hónap lekérdezéséhez egy adott dátumtól. A szintaxis a következő:

MONTH (sorozatszám)

PÉLDA:

1. = HÓNAP (TODAY ())
2. = HÓNAP (Dátum (2019, 11,8))

Százalék:

Mint mindannyian tudjuk, a százalék a 100 töredékének számított arány. Ezt a következőképpen jelölhetjük:

Százalék = (Rész / Teljes) x 100

Az Excelben kiszámíthatja a kívánt értékek százalékos arányát. Például, ha a rész és az egész értékek szerepelnek az A1 és az A2 értékekben, és szeretné kiszámítani a százalékot, akkor a következőképpen teheti meg:

• Válassza ki azt a cellát, ahol meg szeretné jeleníteni az eredményt
• Írja be a „=” jelet
• Ezután írja be a képletet A1 / A2 néven, és nyomja meg az Enter billentyűt
• A kezdőlap számai csoportból válassza ki a „%” szimbólumot

HA:

Az IF utasítás egy feltételes utasítás, amely True-t ad vissza, ha a megadott feltétel teljesül, és Flase-t, ha a feltétel nem teljesül. Az Excel egy beépített „IF” funkciót biztosít, amely ezt a célt szolgálja. Szintaxisa a következő:

IF (logikai_teszt, érték_ha_igaz, érték_ha_hamis)

Itt, logikai_teszt az az állapot, amelyet ellenőrizni kell

PÉLDA:

• Adja meg az összehasonlítandó értékeket
• Válassza ki a cellát, amely megjeleníti a kimenetet
• Írja be a képletsávba „= IF (A1 = A2,„ Igen ”,„ Nem ”) és nyomja meg az Enter billentyűt

TEKINTÉS:

Ezt a funkciót arra használják, hogy kikereszen és lekérjen bizonyos adatokat egy oszlopból egy Excel lapról. A VLOOKUP „V” jelentése függőleges keresés. Ez az egyik legfontosabb és legszélesebb körben használt képlet az Excelben, és ennek a függvénynek a használatához a táblázatot növekvő sorrendben kell rendezni. A függvény szintaxisa a következő:

VLOOKUP (keresési_érték, tábla_rajz, oszlop_index, szám_tartomány, keresés)

hol,

keresési_érték a keresendő érték

table_array az a táblázat, amelyet meg kell keresni

col_index az az oszlop, ahonnan az értéket be kell szerezni

range_lookup (opcionális) az IGAZ értéket adja vissza kb. mérkőzés és FALSE egy pontos meccsre

PÉLDA:

Amint a képen látható, az általam megadott érték 2, a táblázat tartománya pedig A1 és D4 között van. Be akarom szerezni az alkalmazott nevét, ezért az oszlop értékét 2-nek adtam meg, és mivel azt szeretném, hogy pontos egyezés legyen, a False értéket használtam a tartománykereséshez.

Jövedelemadó:

Tegyük fel, hogy annak a személynek a jövedelemadóját szeretné kiszámítani, akinek teljes fizetése 300 dollár. A jövedelemadót a következőképpen kell kiszámítania:

• Sorolja fel a teljes fizetést, a fizetés levonásait, az adóköteles jövedelmet és a jövedelemadó százalékát
• Adja meg a Teljes fizetés, Fizetés levonások értékeit
• Ezután számítsa ki az adóköteles jövedelmet úgy, hogy megtalálja a különbözetet a teljes fizetés és a fizetés levonás között
• Végül számítsa ki az adó összegét

ÖSSZEG:

Az Excel SUM függvénye kiszámítja az eredményt az Excel összes megadott értékének hozzáadásával. A függvény szintaxisa a következő:

SUM (szám1, szám2,…)

Hozzáadja az összes paraméterként megadott számot.

PÉLDA:

Abban az esetben, ha ki szeretné számolni a zöldségek vásárlására fordított összeg összegét, sorolja fel az összes árat, majd használja a SUM képletet az alábbiak szerint:

Kamatos kamat:

Az összetett kamat kiszámításához használhatja az FV nevű Excel képletek egyikét. Ez a funkció visszatér egy befektetés jövőbeli értékéhez, időszakos, állandó kamatláb és kifizetések alapján. A függvény szintaxisa a következő:

FV (ráta, nper, pmt, pv, típus)

A kamat kiszámításához el kell osztania az éves kamatlábat az időszakok számával, azaz az éves kamatlábbal / periódusokkal. Az időszakok számát vagy az npert úgy számítják ki, hogy megszorozzuk a futamidőt (évek száma) az időszakokkal, azaz a futamidő * időszakokkal. A pmt jelentése időszakos fizetés, és bármilyen érték lehet, beleértve a nullát is.

Tekintsük a következő példát:

A fenti példában kiszámoltam a vegyes kamatot 500 dollárra, 10% -os arányban 5 évig, és feltételezve, hogy az időszakos fizetési érték 0. Kérjük, vegye figyelembe, hogy a -B1 jelentést használtam, 500 dollárt vettek el tőlem.

Átlagos:

Az átlag, mint mindannyian tudjuk, számos érték medián értékét ábrázolja. Az Excelben az átlag könnyen kiszámítható az „ÁTLAG” nevű beépített funkció segítségével. A függvény szintaxisa a következő:

ÁTLAG (szám1, szám2,…)

PÉLDA:

Abban az esetben, ha ki akarja számolni a hallgatók által az összes vizsgán elért átlagos pontszámokat, egyszerűen létrehozhat egy táblázatot, majd az ÁTLAG képlet segítségével kiszámíthatja az egyes hallgatók által elért átlagértékeket.

A fenti példában kiszámoltam két vizsga átlagának két tanulóját. Abban az esetben, ha kettőnél több értéke van, amelyek átlagát meg kell határozni, akkor csak meg kell adnia azt a cellatartományt, ahol az értékek jelen vannak. Például:

SZÁMOL:

Az Excel számláló funkciója megszámolja az adott tartományban számokat tartalmazó cellák számát. A függvény szintaxisa a következő:

COUNT (érték1, érték2,…)

PÉLDA:

Abban az esetben, ha az előző példában létrehozott táblázatból ki akarom számolni a számokat tartó cellák számát, akkor egyszerűen ki kell választanom azt a cellát, amelyben meg akarom jeleníteni az eredményt, majd a következőképpen kell használni a COUNT függvényt:

KEREK:

Az értékek egyes tizedesjegyekre történő kerekítéséhez használhatja a KEREK funkciót. Ez a függvény visszaad egy számot, kerekítve azt a megadott tizedesjegyig. A függvény szintaxisa a következő:

hogyan lehet összekapcsolt listát létrehozni a c-ben

KÖR (szám, számjegy)

PÉLDA:

A fokozat keresése:

Az osztályzatok megtalálásához be kell használnia az Excel beágyazott IF utasításait. Például az Átlagos példában kiszámoltam a hallgatók által a teszteken elért átlagos pontszámokat. Most, hogy megtaláljam a hallgatók által elért osztályzatokat, létre kell hoznom egy beágyazott IF függvényt az alábbiak szerint:

Amint láthatja, az átlagos jegyek a G. oszlopban vannak. Az osztályzat kiszámításához egy beágyazott IF képletet használtam. A kód a következő:

= IF (G19> 90, „A”, (IF (G19> 75, „B”, IF (G19> 60, „C”, IF (G19> 40, „D”, „F”)))))

Ezt követően csak át kell másolnia a képletet az összes cellába, ahol meg szeretné jeleníteni az osztályzatokat.

RANG:

Abban az esetben, ha meg akarja határozni az osztály tanulói által elért rangot, használhatja a beépített Excel képletek egyikét, azaz a RANK értéket. Ez a függvény egy adott tartomány rangját adja vissza, ha összehasonlít egy adott tartományt növekvő vagy csökkenő sorrendben. A függvény szintaxisa a következő:

RANK (hivatkozás, szám, megrendelés)

PÉLDA:

Mint látható, a fenti példában a Rank függvény segítségével számoltam ki a hallgatók rangját. Itt az első paraméter az egyes diákok által elért átlagos pontszám, a tömb pedig az osztály többi tanulója által elért átlag. Nem adtam meg sorrendet, ezért a kimenetet csökkenő sorrendben határozzuk meg. A növekvő sorrend rangsoraihoz meg kell adnia minden nem nulla értéket.

COUNTIF:

A cellák bizonyos feltétel alapján történő számlálásához használhatja a beépített Excel képletek egyikét, a „COUNTIF” nevet. Ez a függvény visszaadja azon cellák számát, amelyek teljesítenek bizonyos feltételeket egy adott tartományban. A függvény szintaxisa a következő:

COUNTIF (tartomány, kritériumok)

PÉLDA:

Amint láthatja, a fenti példában megtaláltam a 80-nál nagyobb értékű cellák számát. Adhat némi szöveges értéket is a feltételek paraméterének.

INDEX:

Az INDEX függvény értéket vagy cellahivatkozást ad vissza egy meghatározott tartomány bizonyos pontján. A függvény szintaxisa a következő:

INDEX (tömb, row_num, column_num) vagy

INDEX (hivatkozás,sor_száma, oszlop_száma, terület_száma)

Az index függvény a következőképpen működik a Sor forma:

• Ha a sor és az oszlop száma is meg van adva, akkor a metszéspont cellában lévő értéket adja vissza
• Ha a sor értéke nullára van állítva, akkor a megadott oszlopban a teljes oszlopban jelen lévő értékeket adja vissza
• Ha az oszlop értéke nullára van állítva, akkor a megadott tartományban a teljes sorban lévő értékeket adja vissza

Az index függvény a következőképpen működik a Referencia forma:

• Visszaadja annak a cellának a referenciáját, ahol a sor és az oszlop értéke metszik egymást
• A area_num jelzi, hogy melyik tartományt kell használni, ha több tartományt adtak meg

PÉLDA:

Amint láthatja, a fenti példában az INDEX függvény segítségével határoztam meg az A18 és G20 közötti cellatartomány 2. és 4. oszlopában jelen lévő értékét.

Hasonlóképpen használhatja az INDEX függvényt is, ha több hivatkozást ad meg az alábbiak szerint:

Ezzel az Excel képletekről és függvényekről szóló cikk végére jutunk. Remélem tisztában van mindazzal, amit megosztottak veled. Győződjön meg arról, hogy a lehető legtöbbet gyakorolja, és állítsa vissza a tapasztalatait.

Van egy kérdésünk? Kérjük, említse meg ennek az „Excel képletek és funkciók” blognak a megjegyzés rovatában, és a lehető leghamarabb kapcsolatba lépünk Önnel.

Ha mélyreható ismereteket szeretne szerezni a felkapott technológiákról és azok alkalmazásairól, regisztrálhat élőben 24/7 támogatással és élethosszig tartó hozzáféréssel.