Excel képletek & Funkciók: Tanuljon az alapvető példákkal

A képletek és a függvények alkotják az Excel numerikus adatainak kezelését. Ez a cikk bemutatja a képleteket és a függvényeket.

Ebben a cikkben a következő témákkal foglalkozunk.

• Mi az a képletek az Excelben?
• Elkerülendő hibák az Excel képleteivel való munka során
• Mi a funkció az Excelben?
• A funkciók fontossága
• Közös funkciók
• Numerikus függvények
• Húrfüggvények
• Dátum és idő függvények
• V Keresési funkció

Oktatóanyagok adatai

Ehhez az oktatóanyaghoz a következő adatkészletekkel fogunk dolgozni.

Otthoni kellékek költségvetése

S / N	TÉTEL	QTY	ÁR	ÖSSZESEN	Megfizethető?
1	Mangó	9.	600
2	Narancs	3	1200
3	Paradicsom	1	2500
4	Fözőolaj	5.	6500
5.	Tonik	13.	3900

Házépítési projekt ütemterve

S / N	TÉTEL	KEZDŐ DÁTUM	BEFEJEZÉS DÁTUMA	IDŐTARTAM (DAYS)
1	Földmérés	2015.02.04	2015.02.07
2	Lay Alapítvány	2015.02.10	2015.02.15
3	Tetőszerkezet	2015.02.27	2015.03.03
4	Festés	2015.03.09	2015.03.21

Mi az a képletek az Excelben?

AZ EXCEL FORMULÁK egy olyan kifejezés, amely a cellacímek és operátorok tartományának értékein működik. Például = A1 + A2 + A3, amely megtalálja az A1 és A3 cellák közötti értéktartomány összegét. Példa olyan képletre, amely olyan diszkrét értékekből áll, mint = 6 * 3.

=A2 * D2 / 2

ITT,

• elmondja az Excel-nek, hogy ez egy képlet, és ki kell értékelnie.
• "A2" * D2" hivatkozik az A2 és D2 cellacímekre, majd megsokszorozza az ezekben a cellacímekben található értékeket.
• "/" az osztási számtani operátor
• "2" egy diszkrét érték

Képletek gyakorlati gyakorlat

A részösszeg kiszámításához az otthoni költségvetés mintaadataival fogunk dolgozni.

• Hozzon létre egy új munkafüzetet az Excel programban
• Írja be a fenti háztartási költségkeretben szereplő adatokat.
• A munkalapjának a következőképpen kell kinéznie.

Most megírjuk azt a képletet, amely kiszámítja a részösszeget

Állítsa a fókuszt az E4 cellára

Írja be a következő képletet.

=C4*D4

ITT,

• "C4*D4" az aritmetikai operátor szorzást (*) használja a C4 és D4 cellacím értékének megsokszorozására.

Nyomja meg az Enter billentyűt

A következő eredményt kapja

A következő animált kép bemutatja, hogyan választhatja ki automatikusan a cella címét, és hogyan alkalmazhatja ugyanazt a képletet más sorokra.

Elkerülendő hibák az Excel képleteivel való munka során

1. Ne feledje az osztás, szorzás, összeadás és kivonás zárójelének (BODMAS) szabályait. Ez azt jelenti, hogy a kifejezéseket zárójelben értékelik először. Számtani operátorok esetében először az osztást értékelik, majd szorzást, majd az összeadást és a kivonást értékelik utoljára. Ennek a szabálynak a használatával a fenti képletet = (A2 * D2) / 2 értékkel írhatjuk át. Ez biztosítja, hogy az A2 és D2 értékét először kiértékeljük, majd ketté osztjuk.
2. Az Excel táblázatkezelő képletek általában numerikus adatokkal működnek; kihasználhatja az adatellenőrzés előnyeit annak meghatározásához, hogy milyen típusú adatokat kell elfogadnia egy cellának, azaz csak számokat.
3. Annak érdekében, hogy a képletekben hivatkozott helyes cellacímekkel dolgozzon, nyomja meg az F2 billentyűt a billentyűzeten. Ez kiemeli a képletben használt cellacímeket, és ellenőrizheti, hogy azok a kívánt cellacímek-e.
4. Ha sok sorral dolgozik, akkor az összes sorhoz sorszámot használhat, és a munkalap alján rekordszám szerepelhet. Össze kell hasonlítania a sorozatszámot a rekord összesítésével, hogy megbizonyosodjon arról, hogy a képletek minden sort tartalmaznak.

Nézze meg a legjobb 10 Excel táblázatkezelő képletet

Mi a funkció az Excelben?

A FUNCTION IN EXCEL egy előre definiált képlet, amelyet meghatározott értékekhez használnak egy adott sorrendben. A függvény gyors feladatokhoz használható, például egy cellatartomány összegének, számlálásának, átlagának, maximális értékének és minimális értékének megtalálásához. Például az alábbi A3 cella tartalmazza a SUM függvényt, amely kiszámítja az A1: A2 tartomány összegét.

• SUM egy számtartomány összegzéséhez
• ÁTLAG egy adott számtartomány átlagának kiszámításához
• COUNT az adott tartományban lévő elemek számának megszámolásához

A funkciók fontossága

A funkciók növelik a felhasználói termelékenységet, amikor az excellel dolgoznak . Tegyük fel, hogy szeretné megszerezni a fenti összes háztartási költségkeret végösszegét. Egyszerűbbé tétele érdekében képlettel használhatja az összesítés megszerzését. Képlet segítségével egyenként kell hivatkoznia az E4 és az E8 cellákra. A következő képletet kell használnia.

= E4 + E5 + E6 + E7 + E8

Funkcióval a fenti képletet a következőképpen írná:

=SUM (E4:E8)

Amint az a fenti függvényből kiderül, hogy egy cellatartomány összegét kapjuk-e, sokkal hatékonyabb egy függvényt használni az összeg megszerzéséhez, mint a képlet használata, amelynek sok cellára kell hivatkoznia.

Közös funkciók

Nézzük meg az ms excel képletek leggyakrabban használt függvényeit. Statisztikai függvényekkel kezdjük.

S / N	FUNKCIÓ	KATEGÓRIA	LEÍRÁS	HASZNÁLAT
01	ÖSSZEG	Math & Trig	Összeadja az összes értéket egy cellatartományban	= SUM (E4: E8)
02	MIN	Statisztikai	Megkeresi a minimális értéket egy cellatartományban	= MIN (E4: E8)
03	MAX	Statisztikai	Megkeresi a maximális értéket egy cellatartományban	= MAX (E4: E8)
04	ÁTLAGOS	Statisztikai	Kiszámítja az átlagos értéket egy cellatartományban	= ÁTLAG (E4: E8)
05	SZÁMOL	Statisztikai	Megszámolja a cellák számát egy cellatartományban	= COUNT (E4: E8)
06	LEN	Szöveg	Visszaadja a karaktersorozatot egy karakterláncban	= LEN (B7)
07	SUMIF	Math & Trig	Hozzáadja az összes értéket egy cellatartományban, amely megfelel egy meghatározott feltételnek. = SUMIF (tartomány, feltételek, [összeg_tartomány])	= SUMIF (D4: D8, "> = 1000", C4: C8)
08	ÁTLAGOS	Statisztikai	Kiszámítja a megadott kritériumoknak megfelelő cellatartomány átlagos értékét. = AVERAGEIF (tartomány, kritériumok, [átlagos_tartomány])	= ÁTLAG (F4: F8, "Igen", E4: E8)
09	NAPOK	Dátum idő	Visszaadja a két dátum közötti napok számát	= NAPOK (D4, C4)
10.	MOST	Dátum idő	Visszaadja a rendszer aktuális dátumát és idejét	= MOST ()

Numerikus függvények

Ahogy a neve is mutatja, ezek a függvények numerikus adatokon működnek. Az alábbi táblázat néhány általános numerikus függvényt mutat be.

S / N	FUNKCIÓ	KATEGÓRIA	LEÍRÁS	HASZNÁLAT
1	SZÁM	Információ	Igaz értéket ad vissza, ha a megadott érték numerikus, és hamis, ha nem numerikus	= SZÁM (A3)
2	RAND	Math & Trig	Véletlen számot generál 0 és 1 között	= RAND ()
3	KEREK	Math & Trig	Tizedesértéket kerekít a megadott tizedespontig	= KEREK (3.14455,2)
4	KÖZÉPSŐ	Statisztikai	Visszaadja a megadott számok halmazának közepén található számot	= Közepes (3,4,5,2,5)
5.	PI	Math & Trig	Visszaadja a PI (π) matematikai függvény értékét	= PI ()
6.	ERŐ	Math & Trig	Visszaadja a hatványra emelt szám eredményét. POWER (szám, teljesítmény)	= ERŐ (2,4)
7	MOD	Math & Trig	Visszaadja a maradékot, ha két számot oszt	= MOD (10,3)
8.	RÓMAI	Math & Trig	Számot konvertál római számokká	= ROMÁN (1984)

Húrfüggvények

Ezeket az excel alapvető funkciókat a szöveges adatok manipulálására használják. Az alábbi táblázat néhány gyakori karakterlánc-függvényt mutat be.

S / N	FUNKCIÓ	KATEGÓRIA	LEÍRÁS	HASZNÁLAT	MEGJEGYZÉS
1	BAL	Szöveg	Számos megadott karaktert ad vissza a karakterlánc elejétől (bal oldala)	= BAL ("GURU99", 4)	A "GURU99" 4 karaktere maradt
2	JOBB	Szöveg	Számos megadott karaktert ad vissza a karakterlánc végéből (jobb oldali)	= JOBB ("GURU99", 2)	A "GURU99" jobb 2 karaktere
3	KÖZÉPSŐ	Szöveg	Számos karaktert kap le a karakterlánc közepétől egy megadott kezdőponttól és hosszúságtól. = MID (szöveg, kezdő_szám, szám_karak)	= MID ("GURU99", 2,3)	A 2–5. Karakterek lekérése
4	ISTEXT	Információ	Igaz értéket ad vissza, ha a megadott paraméter a Szöveg	= ISTEXT (érték)	érték - Az ellenőrizendő érték.
5.	MEGTALÁLJA	Szöveg	Visszaadja a szöveges karakterlánc kezdő pozícióját egy másik szöveges karakterláncban. Ez a funkció megkülönbözteti a kis- és nagybetűket. = KERES (keresési_szöveg, szövegszövegben, [kezdő_szám])	= KERES ("oo", "Tetőfedés", 1)	Keresse meg az oo-t a "Tetőfedés" részben, az eredmény 2
6.	KICSERÉLÉS	Szöveg	A karakterlánc egy részét lecseréli egy másik megadott karakterláncra. = KICSERÉLÉS (régi_szöveg, kezdő_szám, számok_karak, új_szöveg)	= KICSERÉLÉS ("Tetőfedés", 2,2, "xx")	Az "oo" helyébe "xx" lépjen

Dátum és idő függvények

Ezeket a függvényeket a dátumértékek manipulálására használják. Az alábbi táblázat néhány általános dátumfüggvényt mutat be

S / N	FUNKCIÓ	KATEGÓRIA	LEÍRÁS	HASZNÁLAT
1	DÁTUM	Dátum idő	Visszaadja a dátumot jelző számot az excel-kódban	= DÁTUM (2015,2,4)
2	NAPOK	Dátum idő	Keresse meg a két dátum közötti napok számát	= NAPOK (D6, C6)
3	HÓNAP	Dátum idő	Visszaadja a hónapot egy dátumértékből	= MONTH ("2015.04.02.")
4	PERC	Dátum idő	Visszaadja a perceket egy időértékből	= PERC ("12:31")
5.	ÉV	Dátum idő	Visszaadja az évet egy dátumértékből	= YEAR ("2015.02.04.")

VLOOKUP funkció

A VLOOKUP függvény függőleges keresést végez a bal oldali oszlopban, és visszaad egy értéket ugyanabban a sorban egy megadott oszlopból. Magyarázzuk el ezt laikus nyelven. A háztartási költségkeretnek van egy sorszáma, amely egyedileg azonosítja a költségvetés egyes tételeit. Tegyük fel, hogy megvan a tétel sorozatszáma, és szeretné tudni a tétel leírását, használhatja a VLOOKUP funkciót. Így működik a VLOOKUP függvény.

=VLOOKUP (C12, A4:B8, 2, FALSE)

ITT,

• "=VLOOKUP" meghívja a függőleges keresési funkciót
• "C12" meghatározza a bal oldali oszlopban keresendő értéket
• "A4:B8" megadja a tábla tömböt az adatokkal
• "2" megadja az oszlop számát a VLOOKUP függvény által visszaadandó sorértékkel
• "FALSE," elmondja a VLOOKUP függvénynek, hogy a megadott keresési érték pontos egyezését keressük

Az alábbi animált kép ezt működés közben mutatja

Töltse le a fenti Excel kódot

Összegzés

Az Excel lehetővé teszi az adatok manipulálását képletek és / vagy függvények segítségével. A funkciók általában produktívabbak, mint a képletek írása. A funkciók pontosabbak a képletekhez képest is, mert a hibázási lehetőség nagyon minimális.

Itt található a fontos Excel képletek és függvények listája

• SUM függvény = =SUM(E4:E8)

• MIN függvény = =MIN(E4:E8)

• MAX függvény = =MAX(E4:E8)

• ÁTLAG függvény = =AVERAGE(E4:E8)

• COUNT függvény = =COUNT(E4:E8)

• DAYS függvény = =DAYS(D4,C4)

• VLOOKUP függvény = =VLOOKUP (C12, A4:B8, 2, FALSE)

• DATE függvény = =DATE(2020,2,4)