Hogyan készítsünk képleteket Excelben. Összetett képletek létrehozása Microsoft Excelben. Képletek cellákkal

A képlet egy matematikai kifejezés, amelyet az eredmény kiszámításához hoznak létre, és amely függhet más cellák tartalmától. Egy cellában lévő képlet tartalmazhat adatokat, hivatkozásokat más cellákra, és jelezheti a végrehajtandó műveleteket.

A cellahivatkozások használata lehetővé teszi a képleteredmények újraszámítását, ha a képletekben szereplő cellák tartalma megváltozik.

Az Excelben a képletek = jellel kezdődnek. A zárójelek () segítségével meghatározhatók a matematikai műveletek sorrendje.

Az Excel a következő operátorokat támogatja:

• Aritmetikai műveletek:
  • összeadás (+);
  • szorzás (*);
  • százalék megállapítása (%);
  • kivonás(-);
  • osztály(/);
  • kitevő (^).
• Összehasonlító operátorok:
  • = egyenlő;
  • < меньше;
  • > több;
  • = nagyobb vagy egyenlő;
  • nem egyenlő.
• Távközlési szolgáltatók:
  • : tartomány;
  • ; egyesület;
  • & operátor szövegek összekapcsolásához.

22. táblázat. Példák képletekre

Gyakorlat

Szúrja be a -25-A1+AZ képletet

Írjon be előre bármilyen számot az A1 és A3 cellába.

Az AutoSum gomb - ∑ segítségével automatikusan létrehozható egy képlet, amely összegzi a közvetlenül szomszédos cellák területét. balra ebben a sorban és közvetlenül magasabb ebben az oszlopban.

Nyomja meg az Enter billentyűt az Excel által kiválasztott terület elfogadásához, vagy használja az egeret egy új terület kiválasztásához, majd nyomja meg az Enter billentyűt.

Az AutoSum funkció automatikusan átalakul, amikor cellákat adnak hozzá vagy törölnek egy területen.

Gyakorlat

23. táblázat Eredeti adattábla

Számítsa ki a felsőfokú végzettséggel nem rendelkező alkalmazottak számát (a B7-VZ képlet segítségével).

24. táblázat.Számítási eredmény

A cellák területe (cella) kitöltő fogantyúval bővíthető. Ahogy az előző részben is látható, a kitöltő fogantyú egy vezérlőpont a kiválasztott cella jobb alsó sarkában.

Gyakran nem csak adatokat kell reprodukálni, hanem címhivatkozásokat tartalmazó képleteket is. A képletek kitöltési fogantyúval történő replikálásának folyamata lehetővé teszi a képlet másolását, miközben egyidejűleg módosítja a képlet címhivatkozásait.

Ezt a folyamatot jellemzően az azonos típusú adatokat tartalmazó sorok vagy oszlopok képleteinek másolásakor használják. A képletek kitöltési markerrel történő replikálásakor a képlet celláinak úgynevezett relatív címei megváltoznak (a relatív és abszolút hivatkozásokat az alábbiakban részletesen ismertetjük).

Az AutoSum funkció automatikusan átalakul, amikor cellákat adnak hozzá vagy törölnek egy területen.

Képletek replikációja

1. Nyissa meg az Employee_Education.x1s fájlt.

25. táblázat: Cellacímek módosítása képletek replikálásakor

A számításokat táblázatokban megvalósító képletek úgynevezett hivatkozásokat használnak a cellák megcímzésére. A cellahivatkozás lehet relatív vagy abszolút.

A relatív hivatkozások használata hasonló a haladási irány megadásához egy utcán – "menjen három háztömbnyit északra, majd két háztömböt nyugatra." Ha követi ezeket az utasításokat a különböző kiindulási helyekről, más-más úti célhoz vezet.

Például egy oszlopban vagy sorban lévő számokat összegző képlet gyakran más sor- vagy oszlopszámokra másolódik. Az ilyen képletek relatív hivatkozásokat használnak (lásd az előző példát a 25. táblázatban).

Egy cellára vagy cellaterületre történő abszolút hivatkozás mindig ugyanarra a sor- és oszlopcímre vonatkozik. Ha összehasonlítjuk az utcai útbaigazítással, ez valami ilyesmi lesz: „Menjen az Arbat és a Boulevard Ring kereszteződéséhez.” Függetlenül attól, hogy honnan indul, ugyanoda fog vezetni. Ha egy képlet megköveteli, hogy a cella címe változatlan maradjon másoláskor, akkor abszolút hivatkozást kell használni ($A$1 rekordformátum). Például amikor egy képlet a teljes összeg töredékeit számítja ki, a teljes összeget tartalmazó cellára való hivatkozás nem változhat másoláskor.

Egy dollárjel ($) megjelenik mind az oszlophivatkozás, mind a sorhivatkozás előtt (például az F4 billentyű egymás utáni lenyomása hozzáad egy jelet az oszlop- vagy sorszám előtt a hivatkozásban (C$2 vagy $C2). - az úgynevezett vegyes linkek).

26. táblázat Bérszámítás

A példa képletének relatív hivatkozásokkal történő replikálásakor hibaüzenet (#ÉRTÉK!) jelenik meg a C4 cellában, mivel a B1 cella relatív címe megváltozik, és a =B2*B4 képlet a C4 cellába másolódik;

27. táblázat Bérszámítási eredmények

Nevek a képletekben

A képletekben található neveket könnyebb megjegyezni, mint a cellacímeket, így abszolút hivatkozások helyett névvel ellátott hatóköröket (egy vagy több cellát) használhat. A nevek létrehozásakor a következő szabályokat kell betartani:

• a nevek legfeljebb 255 karakterből állhatnak;
• a neveknek betűvel kell kezdődniük, és a szóköz kivételével bármilyen karaktert tartalmazhatnak;
• a nevek nem hasonlíthatnak a hivatkozásokhoz, például VZ, C4;
• a nevek nem használhatnak Excel függvényeket, például SUM, IF stb.

A Beszúrás, Név menüben két különböző parancs található az elnevezett területek létrehozására: Új és Hozzárendelés.

A Create paranccsal megadhatja (beírhatja) a kívánt nevet ( csak egy), A Hozzárendelés parancs a munkalapon elhelyezett címkéket használja területnévként (lehetővé teszi a létrehozását több név egyszerre).

Rizs. 88. Nevek létrehozása párbeszédpanel

Abszolút hivatkozás helyett nevet is beszúrhat a képletbe.

Ha hiba történik a képletek vagy adatok megadásakor, hibaüzenet jelenik meg a kapott cellában. Az összes hibaérték első karaktere a # karakter. A hibaértékek az elkövetett hiba típusától függenek.

Az Excel nem ismer fel minden hibát, de az észlelteket ki kell javítani.

Hiba # # # # akkor jelenik meg, ha a beírt szám nem fér el a cellában. Ebben az esetben növelni kell az oszlop szélességét.

#DIV/0 hiba!

akkor jelenik meg, amikor egy képlet nullával próbál osztani. Ez leggyakrabban akkor fordul elő, ha az osztó egy cellahivatkozás, amely nulla vagy üres értéket tartalmaz.

#N/A hiba!

az "undefined data" kifejezés rövidítése. Ez a hiba azt jelzi, hogy egy képlet üres cellahivatkozást használ.

#NAME hiba?

Hiba: #VALUE! akkor jelenik meg, ha egy képlet érvénytelen argumentumot vagy operandustípust használ. Például egy operátor vagy függvény numerikus vagy logikai értéke helyett szöveget írt be.

A felsorolt ​​hibák mellett képletek bevitelekor kör alakú hivatkozás is megjelenhet.

Körhivatkozás akkor fordul elő, ha egy képlet közvetlenül vagy közvetve tartalmaz hivatkozásokat a saját cellájára. A körkörös hivatkozás torzulásokat okozhat a munkalapok számításaiban, ezért a legtöbb alkalmazásban hibának tekintik. Ha körkörös hivatkozást ír be, figyelmeztető üzenet jelenik meg (89. ábra).

A hiba kijavításához törölje a körkörös hivatkozást okozó cellát, szerkessze vagy írja be újra a képletet.

Az Excel-táblázatok bonyolultabb számításait speciális függvények segítségével hajtják végre (90. ábra). A funkciókategóriák listája a Beszúrás, Funkció menü Funkció parancsának kiválasztásával érhető el.

A pénzügyi funkciók olyan számításokat végeznek, mint a kölcsön kifizetésének összege, a befektetésekből származó nyereség kifizetésének összege stb.

A Dátum és Idő függvények segítségével a képletekben a dátum- és időértékekkel dolgozhat. Például használhatja az aktuális dátumot egy képletben a TODAY függvény használatával.

Rizs. 90. Funkcióvarázsló

A matematikai függvények egyszerű és összetett matematikai számításokat hajtanak végre, mint például egy cellatartomány összegének, egy szám abszolút értékének kiszámítása, számok kerekítése stb.

A statisztikai funkciók lehetővé teszik az adatok statisztikai elemzését. Például meghatározhatja egy minta átlagát és szórását, és még sok mást.

Az adatbázis-funkciók számítások elvégzésére és a rekordok feltételek alapján történő szűrésére használhatók.

A szöveges funkciók lehetővé teszik a felhasználó számára a szöveg feldolgozását. Például a CONCATENATE funkcióval több karakterláncot is összefűzhet.

A logikai függvények egy vagy több feltétel tesztelésére szolgálnak. Például az IF függvény lehetővé teszi annak meghatározását, hogy egy megadott feltétel igaz-e, és egy értéket ad vissza, ha a feltétel igaz, és egy másik értéket, ha hamis.

A Property és Value Validation függvények a cellában tárolt adatok meghatározására szolgálnak. Ezek a függvények tesztelik a cellában lévő értékeket egy feltételhez képest, és az eredménytől függően IGAZ vagy FALSE-t adnak vissza.

A táblázatos számítások beépített függvényekkel történő végrehajtásához javasoljuk a Függvényvarázsló használatát. A Funkcióvarázsló párbeszédpanel a Beszúrás menü Funkció parancsának kiválasztásával vagy a gombra kattintva érhető el. , a szabványos eszköztáron. A varázslóval folytatott párbeszéd során meg kell adnia a kiválasztott függvény argumentumait, és ki kell töltenie a párbeszédpanel mezőit a megfelelő értékekkel vagy a táblázatcellák címeivel.

Az AutoSum funkció automatikusan átalakul, amikor cellákat adnak hozzá vagy törölnek egy területen.

Számítsa ki az Education.xls fájl egyes soraihoz az átlagos értéket.

Rizs. 91. Argumentum bevitele a Függvényvarázslóba

28. táblázat: Számítási eredmények táblázata a függvényvarázsló használatával

Ha egy cellatartományt szeretne beírni a Funkcióvarázsló ablakába, az egérrel körbeírhatja ezt a tartományt a táblázat munkalapján (a példában B3:D3). Ha a Funkcióvarázsló ablaka lefedi a kívánt cellákat, áthelyezheti a párbeszédpanelt. Egy cellatartomány (B3:D3) kijelölése után egy futó pontozott keret jelenik meg körülötte, és a kiválasztott cellatartomány címe automatikusan megjelenik az argumentummezőben.

Az Excel egy többfunkciós eszköz. Amivel sokféle képletet alkalmazhat, és hatékonyabbá teheti a program használatát.

A képletek használata az Excelben hasznos funkció. Ennek köszönhetően gyorsan kiszámolhatja bizonyos értékek összegét, különbségét, szorzatát és hányadosát. Ha megtanulja a képletek használatát közvetlenül a táblázatban, akkor könnyen kiszámíthatja az összetett értékeket. Még akkor is, ha nincs kéznél számológép.

A számológép nem tud összetett képleteket kiszámítani, hacsak nem írja le a közbenső eredményeket. Az Excelben a teljes képletet megírhatja, sőt, automatikusan egy képletet használhat különböző számértékekhez.

Ha bármilyen képletet szeretne beírni az Excelbe, először be kell szúrnia az „=” jelet. Ha bármilyen érték megadása előtt megadja ezt a jelet, a program megérti, hogy képletet ír be. Ha e szimbólum nélkül ad meg egy képletet, a program nem tudja elvégezni a számítást.

• Válasszon ki egy cellát, például A2;
• Az Fx jellel ellátott sorban (a táblázat tetején található) írja be a „=1+3” értéket, és nyomja meg az „Enter” gombot;
• A kiválasztott cellában látni fogja az eredményt (4). A megadott érték a képletsorban marad.

Az Excelben más számtani szimbólumokat is használhat: „-” a kivonáshoz, „*” a szorzáshoz, „/” az osztáshoz. Próbáljon meg különböző képleteket beírni az Fx sorba.

Alapvető belépési szabályok:

Az Excelben cellák megadásával lehet képleteket alkalmazni. Tegyük fel, hogy a B2 cellába írja be a 7-et, a B3-ba a 20-at. A B4-es cellába írja be a „=B2+B3” képletet. A B4 cellában látni fogja a B2 és B3 - 27 cellák összeadásának eredményét. Megváltoztathatja bennük az értékeket, majd a B4 cellában lévő eredmény automatikusan megváltozik.

A képletek használatához manuális helyett használhatja a manipulátort.

3 típusuk van:

• A relatív a cella cellához viszonyított helyzetét jelzi a képlettel. Például B2-nek jelölik;
• Az abszolút értéke olyan cellát jelöl, amelynek helyzete nem változik. Megjelölése $B$2;
• A vegyes hivatkozás az első két hivatkozás kombinációját tartalmazza. A jelölése 2 $, 2 $.

Az Excel különböző hivatkozásainak jobb megértéséhez írjunk be néhány képletet.

A táblázat mezőiben adja meg:

• B2 - 10 C2 - 100
• B3 - 70 C3 - 700

A B4 cellába írja be a „=B2+B3”, majd az „Enter” parancsot.

Ezután helyezze a kurzort a B4 jobb alsó sarkába, tartsa lenyomva a jobb egérgombot, húzza át a C4 cellát, és engedje fel a manipulátor billentyűt. A megnyíló helyi menüben válassza a „Cellák másolása” lehetőséget. A B4 mező értéke a C4 cellába másolódik. Aktiválja a C4 cellát, látni fogja benne a képletet - „C2 + C3”. Vagyis úgy tűnik, hogy a képletet a B4 mezőből másolta ki, de az érték más lett. Ez különbözteti meg az ilyen típusú hivatkozásokat. Például, ha kiveszi a B4 cellát, és bemásolja például az A54 cellába, akkor a képletet a felette lévő cellák hozzáadásával kapja meg, azaz „A52 + A53”.

A cellákban tüntesse fel:

• B2 - 10 C2 - 100
• B3 - 70 C3 - 700
• Írja be a 10 értéket a D2 mezőbe.

A B4 mezőbe írja be a „=B2+B3+$D$2” képletet. Másolja a képletet B4-ből C4-be. Mi történt végül? A normál linkek megváltoztak, de az abszolút hivatkozások nem változtak.

Tegyük fel, hogy a B2 mezőben (1. munkalap) B7 mezőre (2. lap) mutató hivatkozás megadásához kövesse az alábbi lépéseket:

• Válassza ki a B2-t, és illessze be a „=” jelet;
• Kattintson a „2. lap” parancsikonra;
• Jelölje ki a B7-et és nyomja meg az enter billentyűt;
• Az 1. lap ismét megnyílik, és a B2 mezőben megjelenik a „=Sheet2!B7” képlet.

A képletértékeket ugyanúgy szerkesztheti, mint a szöveget. Azaz aktiválja a cellát az egér dupla kattintásával, és hajtsa végre a szerkesztést.

Használjon szöveges értékeket a képletekhez, ha azok a következő szimbólumokat tartalmazzák: „0 – 9”, „+”, „-”, „e”, „E”, „/”. A következő formázási karaktereket is használhatja: "$", "%", "(", ")", "szóköz", "";;".

Például, ha a B1 cella az „Ivan” értéket tartalmazza, a B2 cella pedig az „Ivanov” értéket, a B4 cella pedig a „=B2&B3” képletet tartalmazza, akkor az eredmény „IvanIvanov” lesz.

"&;" jel egy szöveges operátor, egy "és" jel, amely összefűzi a szöveget.

Szóköz beszúrásához használja az „&”; „&”-t, vagyis a képlet a következő lesz: „=B2&”, „&B3”.

Az „és” jelet a számok szöveggel kombinálására is használják. Például, ha az első cella az 5-ös számot tartalmazza, a második cella pedig gyűjtődobozokat, akkor az eredmény „5 vödör”.

Összetett függvények megadásához nyissa meg a Beszúrás menüt, és kattintson ott a Funkció ikonra. Válassza ki a kívánt funkciót. Ebben a részben láthatja, hogy az Excel milyen sokrétű, és milyen bonyolult számításokat lehet benne végezni. Használhat hatványokat, gyököket, törteket, gyököket, indexeket, mátrixokat hozhat létre és még sok mást.

Ha állandóan képleteket használ, gyorsan eszébe jut, hogyan kell használni őket az Excelben. Nekik köszönhetően időt takaríthat meg, és egy perc alatt összetett számításokat hajthat végre.

Az EXCEL használata nem olyan nehéz, mint amilyennek első pillantásra tűnhet. Ezt a programot egy nagyvállalat, a Microsoft fejlesztette ki, és amikor megalkották, tudták, hogy ennek a programnak a segítségével egyszerűsíthető a különböző területeken dolgozók élete a képletek hozzáadásának funkciójával. Ez a program hihetetlenül hatalmas funkcionalitással rendelkezik, amely különféle célokra használható, mind személyes, mind munkához vagy tanuláshoz.

Maguk a képletek nem nehezek és nagyon könnyen érthetőek. De annak ellenére, hogy nem nehéz megtanulni, hasznosak, mert ezeknek a képleteknek köszönhetően csökkentheti a számításokhoz szükséges időt. A képleteknek köszönhetően egy cellában kaphat bizonyos kifejezési eredményeket, amelyek változói más cellák lesznek. Ennek a programnak köszönhetően a legbonyolultabb matematikai és pénzügyi számításokat is elvégezheti úgy, hogy gyakorlatilag nem kell fáradnia.

Ezenkívül a kifejezések változójaként működő cellák nemcsak számbevitelt fogadhatnak el a billentyűzetről, hanem más képletek eredményeit is. A szokásos matematikai számítások mellett a képletek segíthetnek a logikai számítások elvégzésében.
Ennek a programnak köszönhetően olyan mutatókat találhat, mint:

Maximum, minimum és átlag.
- A számok százalékos aránya
- Különféle kritériumok, beleértve a Student t tesztet
- És még sok hasznos mutató

A programnak számos előnye van, de a legfontosabb természetesen az, hogy képes számokat konvertálni és alternatív lehetőséget, bármilyen forgatókönyvet létrehozni, ugyanakkor minden számítás szinte azonnali.

Egyszerű képletek alkalmazása Excelben.

Nézzük meg a képleteket két szám összegének egyszerű példájával, hogy megértsük működésük elvét. Példa erre két szám összege. A változók az A1 és B1 cellák lesznek, amelyekbe a felhasználó számokat ír be. A C3 cella ennek a két számnak az összegét jeleníti meg, ha a következő képlet van megadva:
=SZUM(A1;B1).

Az összeget magad is ki tudod számolni, de bonyolultabb példákban, ahol tíz- és százezreket kell összeadni, ezt fejben nehezebb lesz megtenni, és az összegképlet segítségével az érték automatikusan, gyakorlatilag , mint a számológépben.

A cellákban lévő adatokat módosíthatja változókkal, de nem kell módosítania a cellát a képlettel, hacsak nem szeretné lecserélni egy másik képlettel. Az összegzésen kívül más matematikai műveleteket is végrehajthat, például különbséget, osztást és szorzást. A képlet mindig "=" jellel kezdődik. Ha nincs ott, a program nem veszi figyelembe a képletet.

Hogyan készítsünk képletet a programban?

Az előző példában megnéztünk egy példát két szám összegére, amit bárki meg tud kezelni Excel segítsége nélkül, de amikor 2-nél több érték összegét kell kiszámolni, az több időt vesz igénybe, így hajtsa végre három cella összegét egyszerre, ehhez csak a következő képletet kell beírnia a D1 cellába:
=SZUM(A1:B1;C1).

De vannak esetek, amikor például 10 értéket kell hozzáadnia, ehhez használhatja a képlet következő verzióját:
=SZUM(A1:A10), amely így fog kinézni.

És pontosan ugyanígy a terméknél, csak a TERMÉKET használja a SUM helyett.

Ehhez több tartományhoz is használhatunk képleteket, ehhez meg kell írni a képlet alábbi változatát, esetünkben a szorzatot:
=TERMÉK(A1-A10, B1-B10, C1-C10)

Képletek kombinációi.

Amellett, hogy nagy számtartományt adhat meg, különböző képleteket is kombinálhat. Például össze kell adnunk egy bizonyos tartományba tartozó számokat, és ki kell számítanunk a szorzatukat különböző együtthatókkal megszorozva a különböző opciókhoz. Tegyük fel, hogy az (A1:C1) tartomány összegéből ki kell derítenünk az 1,4-es együtthatót, ha az összegük kisebb, mint 90, de ha az összegük nagyobb vagy egyenlő, mint 90, akkor meg kell találnunk az együtthatót 1,5 ugyanabból az összegből. Ehhez a bonyolultnak tűnő feladathoz csak egy egyszerű képletet adunk meg, amely két alapképletet egyesít, és így néz ki:

IF(SUM(A1:C1) Enter a formátummódosítás kényszerítéséhez.

A SUM függvény nem frissül.

Ellenőrizze, hogy a számítás Automatikusra van-e állítva. A Képletek lapon nyissa meg a Számítási beállításokat. A számítást az F9 megnyomásával is kikényszerítheti a munkalapon.

Néhány érték nincs hozzáadva.

#NAME hiba? jelenik meg a várt eredmény helyett.

Ez általában azt jelenti, hogy a képlet hibát tartalmaz: például az =SZUM(A1:A10) helyett az =SZUM(A1:A10) értéket adta meg.

A SUM függvény egész számot jelenít meg, amikor decimális számot kell megjelenítenie.

Ellenőrizze, hogy a decimális számok cellaformátumban történő megjelenítése van-e kiválasztva. Válassza ki a megfelelő cellát vagy tartományt, és nyomja meg a Ctrl+1 billentyűkombinációt a Cellák formázása párbeszédpanel megnyitásához, majd kattintson a Szám fülre, és válassza ki a kívánt formátumot, megadva a tizedesjegyek számát.

A képlet egy kifejezés, amely kiszámítja egy cella értékét. A függvények előre meghatározott képletek, és már be vannak építve az Excelbe.

Például az alábbi ábrán az A3 cella egy képletet tartalmaz, amely hozzáadja az A2 és A1 cellák értékeit.

Egy másik példa. Az A3 cella egy SUM függvényt tartalmaz, amely az A1:A2 tartomány összegét számítja ki.

SZUM(A1:A2)
=SZUM(A1:A2)

A képlet beírásához kövesse az alábbi utasításokat:

Tipp: Az A1 és A2 manuális beírása helyett egyszerűen kattintson az A1 és A2 cellákra.

Amikor kiválaszt egy cellát, az Excel megjeleníti az értéket vagy képletet a képletsor cellájában.

Az Excel beépített sorrendet használ a számítások elvégzéséhez. Ha a képlet egy része zárójelben van, akkor először az kerül kiszámításra. Ezután szorzás vagy osztás történik. Az Excel ezután összeadja és kivonja. Lásd az alábbi példát:

Az Excel először szoroz (A1*A2), majd hozzáadja az A3 cella értékét ehhez az eredményhez.

Egy másik példa:

Az Excel először kiszámítja a zárójelben lévő értéket (A2+A3), majd az eredményt megszorozza az A1 cella értékével.

Képlet másolásakor az Excel automatikusan módosítja a hivatkozásokat minden új cellához, amelybe a képlet másolásra kerül. Ennek megértéséhez kövesse az alábbi lépéseket:

Minden funkciónak ugyanaz a felépítése. Például:

SZUM(A1:A4)
SZUM(A1:A4)

Ennek a függvénynek a neve SUM. A zárójelek (argumentumok) közötti kifejezés azt jelenti, hogy az A1:A4 tartományt adtuk meg bemenetként. Ez a funkció hozzáadja az A1, A2, A3 és A4 cellák értékeit. Nem könnyű megjegyezni, hogy az egyes feladatokhoz mely függvényeket és argumentumokat kell használni. Szerencsére az Excelben van egy Függvény beszúrása parancs.

Függvény beszúrásához tegye a következőket:

Megjegyzés: A Függvény beszúrása eszköz használata helyett egyszerűen írja be a =COUNTIF(A1:C2,>5″) parancsot. Amikor az „A1:C2” beírása helyett a „=COUNTIF(“ parancsot írja be, manuálisan válassza ki a tartományt az egérrel.