Számítások Excelben

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

A cellahivatkozások használata lehetővé teszi a képleteredmények újraszámítását, amikor 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 megadható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 meghatározá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:
  • : hatótávolság;
  • ; Unió;
  • & szöveg csatlakozási operátor.

22. táblázat Képlet példák

Gyakorlat

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

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

1. Válassza ki a kívánt cellát, például B1.
2. Kezdje el beírni a képletet az = jellel.
3. Írja be a 25-ös számot, majd az operátort (- jel).
4. Írjon be egy hivatkozást az első operandusra, például a kívánt A1 cellára kattintva.
5. Írja be a következő operátort (+ jel).
6. Kattintson arra a cellára, amely a képlet második operandusa.
7. Fejezze be a képlet beírását a gomb megnyomásával Belép. A B1 cellában kapja meg az eredményt.

Automatikus összegzés

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

1. Jelölje ki azt a cellát, ahová el szeretné helyezni az összegzés eredményét.
2. Kattintson az AutoSum - ∑ gombra, vagy nyomja meg a billentyűparancsot Alt+=. Az Excel eldönti, hogy melyik területet vegye fel az összegzési tartományba, és kiemeli azt egy szegélynek nevezett, pontozott mozgó dobozzal.
3. Kattintson Belép az Excel által kiválasztott terület elfogadásához, vagy az egérrel jelöljön ki egy új területet, majd nyomja meg az Enter billentyűt.

Az AutoSum funkció automatikusan átalakul, amikor cellákat ad hozzá és eltávolít a területen.

Gyakorlat

Táblázat készítése és képletekkel történő számítás

1. Írja be a numerikus adatokat a cellákba a táblázat szerint. 23.

23. táblázat: Kezdeti adattábla

1. Válassza ki a B7 cellát, amelyben a függőleges összeget kiszámítja.
2. Kattintson az AutoSum - ∑ gombra vagy kattintson Alt+=.
3. Ismételje meg a 2. és 3. lépést a C7 és D7 cellák esetében.

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

1. Válassza ki a B8 cellát, és írja be a (=) jelet.
2. Kattintson a B7 cellára, amely a képlet első operandusa.
3. Írja be a (-) jelet a billentyűzeten, és kattintson az OT cellába, amely a képlet második operandusa (a képlet beírásra kerül).
4. Kattintson Belép(a B8 cellában az eredmény kiszámításra kerül).
5. Ismételje meg az 5-8. lépéseket a megfelelő képlet kiszámításához a C8 és 08 cellákban.
6. Mentse el a fájlt Education_Employees.x1s néven.

24. táblázatSzámítási eredmény

Képletek megkettőzése a kitöltő fogantyúval

Egy cellaterület (cella) replikálható a használatával kitöltési marker. Ahogy az előző részben is látható, a kitöltő fogantyú a töréspont a kijelölt cella jobb alsó sarkában.

Gyakran nem csak az adatokat, hanem a címlinkeket tartalmazó képleteket is meg kell szorozni. A képletek kitöltési fogantyúval történő replikálásának folyamata lehetővé teszi a képlet színezését, miközben módosítja a képlet címhivatkozásait.

1. Válassza ki a replikálni kívánt képletet tartalmazó cellát.
2. Húzza kitöltési marker a helyes irányba. A képlet minden cellában megkettőződik.

Általában ezt a folyamatot használják az azonos típusú adatokat tartalmazó sorok vagy oszlopok képleteinek másolásakor. Képletek kitöltési markerrel történő replikálásakor a képletben szereplő úgynevezett relatív cellacímek megváltoznak (a relatív és abszolút hivatkozásokat később részletesen ismertetjük).

Gyakorlat

Képletek replikációja

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

1. Írja be az E3 cellába a cellák automatikus összegzésének képletét = SUM (OT: 03).
2. A képlet E4:E8 cellákba másolásához húzza el a kitöltő fogantyút.
3. Tekintse meg, hogyan változnak a relatív cellacímek a kapott képletekben (25. táblázat), és mentse el a fájlt.

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

Relatív és abszolút linkek

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 sejtreferencia lehet relatív vagy abszolút.

A relatív hivatkozások használata hasonló az utca mentén történő haladási irány jelzéséhez - "menjen három háztömbnyit északra, majd két háztömbnyit 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. Ezek a képletek relatív hivatkozásokat használnak (lásd az előző példát a 25. táblázatban).

Abszolút cellahivatkozás. A Go cellaterület mindig ugyanarra a sor- és oszlopcímre fog hivatkozni. Az utcák irányával összehasonlítva ez valami ilyesmi lesz: "Menjen az Arbat és a Boulevard Ring kereszteződéséhez." A kiindulási ponttól függetlenül ez ugyanoda fog vezetni. Ha a képlet megköveteli, hogy a cellacím változatlan maradjon másoláskor, akkor abszolút hivatkozást ($A$1 rekordformátum) kell használni. Például, amikor egy képlet az összeg töredékeit számítja ki, az ö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 (pl. $C$2) előtt. Az F4 egymást követő lenyomása hozzáad vagy eltávolít egy jelet az oszlop- vagy sorszám előtt a hivatkozásban (C$2 vagy $C2 - a úgynevezett vegyes linkek).

1. Hozzon létre egy, az alábbihoz hasonló táblázatot.

26. táblázat Bérszámfejtés

1. Az SZ cellába írja be az Ivanov fizetésének kiszámítására szolgáló képletet \u003d V1 * VZ.

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

1. Állítsa be az abszolút hivatkozást a B1 cellára úgy, hogy a kurzort a képletsorban a B1-re helyezi, és megnyomja az F4 billentyűt. A C3 cellában lévő képlet így fog kinézni: =$B$1*B3.
2. Másolja a képletet a C4 és C5 cellákba.
3. Mentse el a fájlt (27. táblázat) a név alatt Fizetés.xls.

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

Nevek a képletekben

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

• a nevek legfeljebb 255 karakter hosszúak lehetnek;
• a neveknek betűvel kell kezdődniük, és a szóköz kivételével bármilyen karaktert tartalmazhatnak;
• a nevek nem nézhetnek ki hivatkozásként, például OT, C4;
• nevek nem használhatnak Excel függvényeket, mint pl SZUM, HA stb.

A menün Beszúrás, Név Az elnevezett területek létrehozásához két különböző parancs létezik: Létrehozás és Hozzárendelés.

Csapat Létrehozás lehetővé teszi a kívánt név beállítását (bevitelét) csak egy), Parancs hozzárendelése a munkalapon elhelyezett címkéket használja területnévként (létrehozása megengedett több név egyszerre).

A névadás

1. Válassza ki a B1 cellát (26. táblázat).
2. Válassza ki a menüből Beszúrás, Név (Beszúrás, Név) parancs Hozzárendelés (meghatározás).
3. Adja meg nevét Óradíj, majd kattintson az OK gombra.
4. Válassza ki a B1 cellát, és győződjön meg arról, hogy a név mezőben szerepel óradíj.

Több név létrehozása

1. Válassza ki a ВЗ:С5 cellákat (27. táblázat).
2. Válassza ki a menüből Beszúrás, Név (Beszúrás, Név) parancs Létrehoz (Létrehozás), megjelenik egy párbeszédpanel. Hozzon létre neveket(88. ábra).
3. Győződjön meg arról, hogy a bal oldali oszlop választógombja be van jelölve, és kattintson rá rendben.
4. Jelölje ki az OT:NW cellákat, és győződjön meg arról, hogy a név mezőben szerepel Ivanov.

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

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

1. A képletsorban helyezze a kurzort oda, ahol a név hozzáadásra kerül.
2. Válassza ki a menüből Beszúrás, Név (Beszúrás, Név) parancs Beillesztés (Beillesztés), Megjelenik a Nevek beszúrása párbeszédpanel.

1. Válassza ki a kívánt nevet a listából, majd kattintson az OK gombra.

Hibák a képletekben

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

Az Excel nem képes felismerni az összes hibát, de a találtakat képesnek kell lennie kijavítani.

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

#DIV/0 hiba! akkor jelenik meg, ha egy képletben nullával próbálnak osztani. Ez leggyakrabban akkor fordul elő, ha az osztó egy cellahivatkozás, amely nullát vagy üres értéket tartalmaz.

#N/A hiba! az "undefined data" rövidítése. Ez a hiba üres cellahivatkozást jelez a képletben.

#NAME hiba? akkor jelenik meg, ha a képletben használt nevet eltávolították vagy korábban nem határozták meg. Az adatterület nevének, a függvénynévnek stb. javításához, meghatározásához vagy javításához.

Hiba #ÜRES! akkor jelenik meg, ha két olyan régió metszéspontját adjuk meg, amelyeknek valójában nincs közös cellája. Leggyakrabban a hiba azt jelzi, hogy hiba történt a cellatartomány-hivatkozások megadásakor.

NUMBER számú hiba! akkor jelenik meg, ha érvénytelen formátumot vagy argumentumértéket használunk egy numerikus argumentummal rendelkező függvényben.

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

A képletek beírásakor a fent felsorolt ​​hibákon kívül körkörös 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ást okozhat a munkalapok számításaiban, ezért a legtöbb alkalmazásban hibának tekintik. Körkörös referencia beírásakor 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.

Funkciók az Excelben

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 parancs kiválasztásakor érhető el Funkció a Beszúrás menüben (Beszúrás, Funkció).

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

A Dátum és Idő funkciók lehetővé teszik, hogy a képletekben a dátum- és időértékekkel dolgozzon. Például használhatja az aktuális dátumot egy képletben a függvény használatával MA.

Rizs. 90. Funkcióvarázsló

A matematikai függvények egyszerűekés összetett matematikai számítások, 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.

Statisztikai függvények lehetővé teszi 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.

Adatbázis funkciók számítások elvégzésére és a rekordok feltétel szerinti kiválasztására használható.

Szöveges függvények szövegfeldolgozási lehetőséget biztosít a felhasználó számára. Például a függvény segítségével több karakterláncot is összefűzhet KAPCSOLAT.

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 a megadott feltétel igaz-e, és egy értéket ad vissza, ha a feltétel igaz, és egy másik értéket, ha hamis.

Funkciók Tulajdonságok és értékek ellenőrzése célja a cellában tárolt adatok meghatározása. Ezek a függvények a feltételnek megfelelően ellenőrzik a cellában lévő értékeket, és az eredménytől függően adják vissza az értékeket. Igaz vagy hamis.

A beépített függvényekkel végzett táblázatos számításokhoz a Függvényvarázsló használatát javasoljuk. A Funkcióvarázsló párbeszédpanel a parancs kiválasztásakor érhető el Funkció a Beszúrás menüben vagy egy gomb megnyomásával , a szabványos eszköztáron. A varázslóval folytatott párbeszéd során be kell állítani a kiválasztott függvény argumentumait, ehhez ki kell tölteni a párbeszédpanel mezőit a táblázatcellák megfelelő értékeivel vagy címeivel.

Gyakorlat

Az oktatás.xls fájl egyes soraihoz tartozó átlagérték kiszámítása.

1. Jelölje ki az F3 cellát, és kattintson a Funkcióvarázsló gombra.
2. A Funkcióvarázsló első párbeszédpanelében a Statisztikai kategóriából válasszon ki egy függvényt ÁTLAGOS, kattintson a gombra További.
3. Az argumentumokat a függvényvarázsló második párbeszédpanelében kell megadni. A beviteli kurzor az első argumentum beviteli mezőjében van. Ebben a mezőben argumentumszámként! írja be a B3:D3 tartomány címét (91. ábra).
4. Kattintson rendben.
5. Másolja a kapott képletet az F4:F6 cellákba, és mentse el a fájlt (28. táblázat).

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

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

Ha a függvényvarázsló ablakában egy cellatartományt szeretne megadni, ezt a tartományt bekarikázhatja a táblázat munkalapján (a B3:D3 példában). 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.