Ha az Excel/Google Sheets programot használja napi munkája során, akkor van néhány nagyon hasznos képlet, amelyekhez visszatérhet, ha egyszer megtanulja őket.
Valószínűleg több mint száz különböző táblázatos képlet létezik, amelyek különböző típusú számításokhoz működnek.
Ebben a blogban az a célom, hogy a legegyszerűbbekről beszéljek, amelyek a későbbiekben megtanulható összes haladó számítás alapját képezik.
Először néhány alapvető aritmetikai számítási parancsot fogunk átnézni, majd bemutatok néhány módszert, amelyekkel fejlettebb elemzéseket végezhet.
Ezek közé tartoznak az IF utasítások, a VLOOKUP, INDEX és MATCH parancsok stb.
És ez egyszerűbb, mint gondolnád!
A legfontosabb tudnivalók
- Az Excel és a Google Sheets számos beépített képletparanccsal rendelkezik, amelyek hasznosak a számítások elvégzéséhez.
- A SUM, AVERAGE, COUNT, VLOOKUP, INDEX és MATCH olyan képletek, amelyek segíthetnek a mindennapi számításoknál. A feltételes logikák bevezetésével is játszhat velük.
- A képlet megírásakor nem szabad hibázni, különben nem kapja meg az eredményt. Különösen ügyeljen arra, hogy elkerülje a körkörös hivatkozásokat, és megakadályozza, hogy az Excel végtelen számítási ciklusba kerüljön.
Mi a táblázatos képlet pontosan
Azokat a parancsokat, amelyekkel számításokat végezhet az adatokkal, táblázatkezelői képleteknek nevezzük.
Mind a Microsoft Excel, mind a Google Dokumentumok tele van ilyen parancsokkal, részben ezért is számítanak minden munkahely alapvető eszközének.
Az Excelben írt képleteket egy (=) jel előzi meg. Ez azt jelzi, hogy az Excel az ezt követő szöveget a kívánt számítás elvégzéséhez szükséges képletként kezeli, nem pedig egyszerű szöveges bejegyzésként.
Soha többé ne aggódj amiatt, hogy a mesterséges intelligencia felismeri a szövegeidet. Undetectable AI Segíthet:
- Tegye láthatóvá az AI-asszisztált írást ember-szerű.
- Bypass az összes főbb AI-érzékelő eszköz egyetlen kattintással.
- Használja a címet. AI biztonságosan és magabiztosan az iskolában és a munkahelyen.
A Google Sheets vagy az Excel táblázatkezelő képletek dátumokra való használatán kívül itt van néhány ok, amiért az Excel és a Google Sheets több száz képletparanccsal rendelkezik:
- Gyorsan automatizálja a számításokat
Mindannyiunknak szükségünk van néhány alapvető számításra, például aritmetikára vagy átlagszámításra, függetlenül a munkánk jellegétől.
Ha az Ön munkája adatigényes, akkor sokkal nagyobb gyakoriságú, kiterjedtebb számításokra van szüksége.
Nagyon kényelmes, ha ezeket a számításokat a táblázatkezelőben tudja elvégezni, ahol az adatokat tárolja, ahelyett, hogy egy számológépen kellene importálnia.
A képletet egyszer is beállíthatja, és annyi sor vagy adat megadása esetén fut tovább, ahány sor vagy adat megadása szükséges.
- Csökkenti a kézi hibákat
Az adatok számológépes alkalmazásba történő exportálása nemcsak időigényes, hanem hibalehetőségekkel is jár. Egyetlen rossz bevitel, és az egész számításod előre megbízhatatlan.
Még többszöri lektorálás után sem lehetsz biztos benne, hogy minden értéket pontosan adtál meg.
Egy táblázatos képlet futtatása csökkenti ezt a hibapontot.
- Logikusan összekapcsolja az adatokat
Számítások segítségével sokkal logikusabban értelmezheti az adatkészleteket. Rendezze az adatait, szűrje meg, futtasson elemzést, és jobban megértse, hogy a táblázat egyes celláiban lévő számok minek felelnek meg.
Kiválóan alkalmas:
- Költségvetés
- Személyes vagy üzleti pénzügyi nyilvántartás vezetése
- Tanárok, akik nyilvántartást szeretnének a diákjaik feladatai/vizsgateljesítményeiről.
- Kutatási adatok
- Befektetési hozamok kiszámítása
Akár a Google táblázatkezelő képleteire van szüksége, akár egy Excel táblázatkezelő képletlistára, a következő részben megtalálhatja őket.
Alapképletek, amelyekre minden felhasználónak szüksége van
Mind az Excel, mind a Google Sheets több száz képletet tartalmaz, amelyek különböző számításokat végeznek. Jó néhány hónapba telik, ha mindet meg akarja tanulni. Az alábbiakban azonban leírtam néhány nagyon hasznosat.
- SUM az összegekhez
A SUM() parancs a kijelölt cellák összeadására szolgál. Természetesen ahhoz, hogy működjön, numerikus értéknek kell lennie a cellában. A képlet nem fog működni semmilyen nem numerikus cellában.
A zárójelen belül meg kell adnia a hozzáadni kívánt táblázatszegmens első és utolsó celláját, kettősponttal elválasztva.
Ha tehát a 3-7. sorok tartalmát akarom hozzáadni az első oszlophoz, akkor a képletet a következőképpen kell megírnom: SUM(A3:A7). A képlet több soron keresztül is működik.
Ez a képlet a Google Sheets és az Excel esetében is ugyanaz marad.
Nézze meg ezt a példát:
A legegyszerűbb SUM függvényen kívül a következő típusú összeadásokat is elvégezheti:
- SUMIF: Egy tartományban lévő, egy feltételnek megfelelő számok összeadása
- SUMIFS: Több feltételnek megfelelő számok összeadása egy tartományban
- SUMPRODUCT: Megfelelő értékek szorzása tömbökben, majd az eredmények összeadása.
- SUMSQ: Számok négyzeteinek összeadása egy tartományban
- SUMX2MY2: Adja össze az (x² - y²) eredményét két tartományban lévő megfelelő értékek esetén.
- SUMX2PY2: Adja össze az (x² + y²) eredményét két tartományban lévő megfelelő értékek esetén.
- SUMXMY2: Adja össze az (x - y)² különbségek négyzeteit két tartományban.
- DSUM: Egy adatbázis-táblában meghatározott kritériumoknak megfelelő értékek hozzáadása egy oszlophoz
- IMSUM: Komplex számok összeadása.
- SZERIESSUM: Számítsa ki egy hatványsorozat összegét adott együtthatók segítségével.
- AVERAGE a betekintésekhez
Az AVERAGE() képlet a táblázat adott cellakészletének átlagának kiszámítására szolgál.
Ismétlem, a munka nagyjából ugyanaz. Az első és az utolsó cellaszámot zárójelbe tesszük, kettősponttal elválasztva, pl. AVERAGE(E2:E11), és a program megadja a kiválasztott cellák átlagát.
Az átlag kiszámításához használt képlet ugyanaz az Excelben, mint a Google Sheetsben.
Az AVERAGE képlet kis eltérései segíthetnek a feltételes számításoknál, például:
- ÁTLAGOS: Egy tartományban lévő számok számtani átlagának kiszámítása.
- AVERAGEA: Akkor használja, ha átlagot szeretne, beleértve a számokat, a szöveget és a logikai értékeket is.
- AVERAGEIF: Ez a parancs az egyik feltételnek megfelelő számok átlagának kiszámítására szolgál.
- AVERAGEIFS: Ez a program több feltételnek megfelelő számok átlagát számítja ki.
- DAVERAGE: Egy adatbázis-oszlopban lévő, megadott kritériumoknak megfelelő értékek átlagának kiszámítása.
- COUNT a tételek nyomon követéséhez
Ez a parancs hasznos a táblázatkezelő táblázatban található, numerikus adatokat tartalmazó cellák számának kiszámításához.
Nem lesz értelme használni, ha az adatok csak néhány sorra korlátozódnak, de ha több száz sornyi adatod van, és tudni szeretnéd, hogy ezek közül hány számérték, akkor ezt fogod elérni.
Ezt is hasonló módon írjuk, azaz az = jellel kezdjük, majd megemlítjük a COUNT parancsot, megnyitjuk a zárójelet, megemlítjük az első és az utolsó cellát kettősponttal elválasztva, és bezárjuk a zárójelet.
A képlet ugyanaz a Google Sheetsben és az Excelben is.
Például, =COUNT(E1:E100)
A SUM és AVERAGE képletekhez hasonlóan a COUNT is többféleképpen használható, pl.,
- COUNTA: A nem üres cellák (számok, szöveg vagy értékek) megszámlálása.
- COUNTBLANK: Csak az üres cellákat számolja egy tartományban.
- COUNTIF: Azokat a cellákat számolja, amelyek megfelelnek egy feltételnek
- COUNTIFS: Használja a több feltételnek megfelelő cellák számolásához.
- DCOUNT: Ez az adatbázis oszlopában lévő numerikus értékeket számolja, amelyek megfelelnek a megadott kritériumoknak.
- DCOUNTA: A megadott feltételeknek megfelelő, nem üres értékek megszámlálására szolgál egy adatbázis-oszlopban.
Haladó képletek az adatelemzéshez
A legtöbb felhasználó számára a korábban tárgyalt képletek bőven elegendőek lesznek. Néhány képletre azonban szükség van a haladó számításokhoz, és nagyon jól jönnek, ha a munkája adatelemzéssel jár.
- VLOOKUP keresésekhez
A VLOOKUP képletet arra használjuk, hogy megkeressünk egy értéket egy táblázatban, és visszaadjuk a kapcsolódó információkat ugyanabból a sorból.
Egyszerűbben fogalmazva, a VLOOKUP beírásával arra kéri a táblázatkezelőt, hogy keressen meg egy értéket a táblázat első oszlopában, majd adjon meg egy értéket a sor egy másik oszlopából.
A mintalapunkban, ha az A oszlopban termékazonosítók vannak, a C oszlopban pedig az egységár, akkor a VLOOKUP segítségével kereshetünk egy bizonyos termékazonosítót, és a C oszlopból megkaphatjuk az árát.
Ez a következőképpen íródik: VLOOKUP(“P-1001”, A:C, 3, FALSE)
- IF utasítások a logikához
Az IF funkciót logikai döntések meghozatalára használják a táblázatkezelőben. Egyszerűen megmondja a lapnak, hogy mit tegyen, ha egy feltétel igaz, és mit tegyen, ha hamis.
Az IF utasítás megírásához az = jellel kezdjük, az IF parancsot beírjuk, a zárójeleket kinyitjuk, majd leírjuk a feltételt.
Ezután tegyen egy vesszőt, és említse meg, hogy mit szeretne, hogy a lap mit csináljon, ha a feltétel teljesül. Ezt követően tegyen még egy vesszőt, és írja meg, hogy mi történjen, ha a parancs nem teljesül.
Például, ha elképzeljük a diákok eredményeit tartalmazó lapot, és szeretnénk megadni egy olyan feltételt, hogy az 50-es vagy annál magasabb pontszám átmenőnek, az 50 alatti pedig sikertelennek számít, akkor használhatjuk az IF logikát.
Így írja le: =IF(A1>=50, “Pass”, “Fail”)
A lap beolvassa a cellában lévő értéket, és ha az nagyobb, mint 50, akkor azt mutatja, hogy a diák átment. Ellenkező esetben megemlíti, hogy megbukott.
- INDEX & MATCH kereséshez
Az INDEX és a MATCH együttesen a LOOKUP formulánál rugalmasabb módon használható a táblázatban lévő információk keresésére.
A MATCH függvény feladata egy érték pozíciójának megkeresése egy sorban vagy oszlopban. A MATCH függvényt egy érték keresésére használja a táblázatban, és nem adja vissza magát az értéket. Egy számot fog visszaadni, amely azt jelzi, hogy az érték hol jelenik meg.
Például, ha egy termék azonosítója az ötödik elem a listában, a MATCH az 5-ös értéket adja vissza.
Az INDEX függvény ezt a pozíciószámot használja arra, hogy visszaadjon egy értéket a táblázat vagy oszlop egy adott helyéről. Tehát egyszerűen lekérdezi azt az értéket, amely a megadott sorban létezik.
Tippek a közös képlet hibák elkerülésére
Mint már láthatta, mind az Excel, mind a Google Sheets megköveteli, hogy a képleteket 100% pontossággal adja meg.
Bármilyen hiba a képleted megírásakor nem fog eredményt szolgáltatni, csak egy hibaüzenetet fogsz kapni, amely azt mondja, hogy #N/A.
Íme néhány tipp, hogy elkerülje ezt a hibaüzenetet.
- A zárójeleket gondosan ellenőrizze
A képletet pontosan a fent leírt formátumban kell megírni. Ha véletlenül nyitva hagyta a zárójeleket, vagy csak zárójelek nélkül írta le a cellatartományt, akkor nem fogja megkapni az adatok kiszámítását.
Mindig győződjön meg róla, hogy a nyitó és a záró zárójel is jól a helyén van. Arra is ügyeljen, hogy a tartomány első és utolsó celláját kettőspont különböztesse meg.
- Adattípusok figyelése
A táblázatképletek képletei csak bizonyos típusú adatokkal működnek a legjobban. Például nem várhat átlagértéket olyan adatokra, amelyek nem számszerűsíthetők, azaz leíró adatformákra.
A képletek beillesztésekor a cellák adatait kétszer is ellenőrizze, nehogy érvénytelen cellatartományt adjon meg.
- Kerülje a körkörös hivatkozásokat
A körhivatkozás azt jelenti, hogy a táblázat visszahalad a saját cellájához, ahol a leírás található. Ez egy végtelen körforgási hurkot hoz létre, ahol nem kaphat választ, és helyette csak egy hibaüzenetet jelenít meg.
Ha például az A10-es cellába beírja a SUM(A1:A10) értéket, akkor egy körkörös hurkot hoz létre, amelyben a cellának folyamatosan hozzá kell adnia az A10-es cellában összegzett értéket, és ez a folyamat soha nem ér véget.
A képletnek ismernie kell a saját eredményét, hogy megtalálja az A10-es cella értékét.
Mindig győződjön meg arról, hogy a táblázat képletét beíró cella nem része a képlet cellatartományának.
Képletek kombinálása a jobb hatékonyság érdekében
A képletek kombinálása a táblázatokban lehetővé teszi az összetett problémák hatékonyabb megoldását. Amikor a korábban említett egyszerű képleteket használja, akkor lépésről lépésre, sok cellán keresztül számolja ki a dolgokat.
Egy kombinált képletben több számítást is beágyazhat úgy, hogy az egyik képlet közvetlenül egy másikba táplálja a másikat.
Például kombinálhatja az IF és az SUM értékeket, hogy csak bizonyos feltételeknek megfelelő értékeket adjon össze.
Az adatok pontosabb elemzése érdekében a COUNT vagy az AVERAGE kombinálható feltételekkel is.
Ez azt jelenti, hogy a formulák kombinálása elsőre nem megy könnyen. Valódi tanulási görbe van, és ha úgy ugrik bele a bonyolult, egymásba ágyazott képletekbe, hogy nem használta már jó néhányszor az egyszerű képleteket, a végén még összezavarodik.
Ezért először mindig egyszerű képletekkel gyakoroljon. Ha már jól érzi magát, fokozatosan építsen fel két függvény kombinálásával, majd három, végül pedig haladóbb összeállításokkal.
Képletek használata valós feladatokhoz
Ha egyszer elsajátítod ezeket a formulákat, észre fogod venni, hogy milyen gyakran alkalmazod őket olyan dolgokra, amelyeket már most is csinálsz.
Például:
- Egy kisvállalkozó a következő képletet használhatja SUMIF a kiadások nyomon követése kategóriánként
- Vagy egy tanár kombinálhatja IF a címen COUNTIF a feladatokkal lemaradó tanulók megjelölésére szolgáló funkciók
- Ugyanez a helyzet az ügyfélszámlákat kezelő szabadúszókkal is. Ők használhatnak egy egyszerű VLOOKUP paranccsal az ügyféldíjakat egy mesterlistából húzhatja ki anélkül, hogy manuálisan görgetné a sorokat.
A helyzet az, hogy az, hogy tudjuk, melyik formula illik az Ön konkrét helyzetéhez, szinte a csata fele. És ez pontosan az a pont, ahol az Undetectable AI Chatbot lehet az útmutatója.
Egyszerű nyelven leírhatja, hogy mit szeretne tenni, és a program a feladathoz megfelelő képletet fog javasolni.
Mostantól van egy táblázatkezeléshez értő munkatársad, akit bármikor megkereshetsz, ha elakadsz, és ez nem csak az Excel vagy Google táblázatkezelési lekérdezésekre korlátozódik.
Fedezze fel, hogy AI Detector és Humanizer hogyan javíthatja tartalmait az alábbiakban!
Végső gondolatok
Remélem, ez a cikk segített megtanulni egy-két dolgot a táblázatkezelési képletekről.
Csak annál jobb lehetsz bennük, minél többet használod őket, és bármikor, amikor összezavarodsz, hogy milyen képletet használj, csak fordulj a Undetectable AI's AI Chat, és ez segít eldönteni, hogy melyik parancs működik a legjobban a kívánt számításhoz.
A képletet duplán is ellenőrizheti, ha lefuttatja az AI Chat-en, hogy megbizonyosodjon arról, hogy nem követ el hibákat.
Próbálja ki Kimutathatatlan AI ma!