Excel függvényhasználat

Cellahivatkozások

 

Hivatkozási módok
 abszolút hivatkozás: $B$5 - a lemásolt képletbe ugyanígy másolódik át
 relatív hivatkozás: B5 - a lemásolt képlet a másolás irányának megfelelően módosul (A5,B4,B6 vagy C5)
 vegyes hivatkozás: B$5 vagy $B5 - az első esetben az oszlop, a másodikban a sor módosulhat (C$5 vagy $B6)
 (összefoglalva: a "$" szimbólum a mögötte álló jelet rögzíti, fixálja)

 

Hivatkozás másik munkalap(ok)ra
egy cellára: Munka2!B13 vagy '9.E osztály'!B13
 egy tartományra: 'Bródy Imre'!B4:C5

 

Hivatkozás másik munkafüzetre
könyvtárbelire: [grafikonok.xls]Országok!$A$26
 másik könyvtárbelire: 'C:\Dokument\excel\[2005.xls]Személyi adatok'!$A$2

 

Hivatkozás másik könytárban levő xls filere
'd:/Dokumentumok\[studium.XLS]nevek'!$A$7

'file:///d:/Dokumentumok/Excel_fájl.xls'#$Munka2.A1

 DDE("Excel";"d:/Dokumentumok/Excel_fájl.xls]Munka1";"S1O1:S21O3")

 tömb függvénnyel:

{='file:///D:/Dokumentumok/Kovács_2012/OOo_Fórum/Adatok Excelből/Excel Fájl.xls'#$Munka1.A1:C21}  

 



Függvények
Azokat a számításokat, amelyeket az alapműveletekkel nem tudunk leírni, a megfelelő
    függvények segítségével végezhetjük el.

    A függvények a következőképpen épülnek fel:
        függvénynév (paraméter1; paraméter2; ...)
    Természetesen lehet paraméter/argumentum nélküli függvény is, a zárójelpár ilyenkor is kötelező.


    Egyszerűbb függvények
     szum(tart) - a megadott tartomány (tart) összege (az üres 0)
        átlag(tart) - a megadott tartomány átlaga (az üres nem számít)
        max(tart) - a megadott tartomány legnagyobb értéke
        min(tart) - a megadott tartomány legkisebb értéke
        gyök(szám) - a megadott szám négyzetgyöke
        hatvány(szám;kitevő) - a megadott szám adott kitevűjű hatványa
        vél() - 0 és 1 közötti véletlenszám (0 is lehet, de 1 nem)
        csonk(szám;jegyszám) - a megadott szám törtrészét csonkolja "jegyszám"-nyi jegyre
        kerek(szám;jegyszám) - a megadott számot kerekíti "jegyszám"-nyi jegyre
        darab(tart) - a tartományban lévő számokat tartalmazó cellák száma
        darab2(tart) - a tartományban lévő kitöltött cellák száma
        darabüres(tart) - a tartományban lévő üres cellák száma
        darabteli(tart;feltétel) - a tartományban lévő, adott feltételnek megfelelő cellák száma


    Bonyolultabb függvények
        nagy(tart;k) - a tartományban lévő k. legnagyobb cella értéke
        kicsi(tart;k) - a tartományban lévő k. legkisebb cella értéke
        és(állítás1,állítás2) - a két állítás közti logikai és eredménye (IGAZ, ha mindkettő az)
        vagy(állítás1,állítás2) - a két állítás közti logikai vagy eredménye (IGAZ, ha egyik az)
        ha(feltétel;kif1;kif2) - ha a feltétel teljesül, a kif1 egyébként a kif2 értéke kerül a cellába
        szumha (tart1;feltétel) - a tartomány azon elemeit összegzi, amelyek megfelelnek a feltételnek
        szumha (tart1;feltétel;tart2) - a tart1-ben értékeli a feltételt, de csak a tart2 megfelelő
            elemeit összegzi (a tart1 és tart2 csak egydimenziós! lehet)


    Beágyazott függvények
Összetett feladat gyakran csak több függvény segítségével oldható meg úgy, hogy az egyik argumentuma
        esetleg újabb függvényt vagy függvényeket tartalmaz (beágyazott vagy egymásba ágyazott függvények).
     csonk(90*vél()+1) - az egyik jövő heti ötös-lottó számot adja
     =ha(a1>80%;"jeles";ha(a1>50%;"jó";"elégtelen")) - szöveges értékelés az a1 alapján

MATEMATIKAI FÜGGVÉNYEK
ABS Egy szám abszolút értékét adja eredményül.
=ABS(32) eredménye 32, =ABS(-32) eredménye ugyancsak 32

DARABTELI Összeszámolja egy tartományban a megadott feltételeknek eleget tevő nem
üres cellákat. DARABTELI(tartomány;feltétel) (AB.DARAB2 függvénnyel is
megoldható)

ELŐJEL Egy szám előjelétől függően
1 ha a szám pozitív
0 ha a szám értéke 0
-1 ha a szám negatív

GYÖK Egy pozitív szám pozitív négyzetgyökét adja eredményül. =GYÖK(25)
eredménye 5.

HATVÁNY Egy szám adott kitevőjű hatványát adja eredményül.
HATVÁNY(szám;hatvány)
pl.: HATVÁNY(2;3) eredménye 8 (kettő a harmadik hatványon)
=HATVÁNY(4;0,5) eredménye 2 (négy négyzetgyöke)

KEREK.FEL Egy számot mindig felfelé, a nullától távolabbra kerekít.
KEREK.FEL(szám;hány jegy). Pl.: KEREK.FEL(2,13456;2) eredménye 2, 14,
KEREK.FEL(2,12345;1) eredménye 2,2

KEREK.LE Egy számot mindig lefelé, a nulla felé kerekít. KEREK.LE(szám;hány jegy)
KEREK.LE(2,13456;2) eredménye 2, 13, KEREK.LE(2,12345;1) eredménye
2,1.

KEREKÍTÉS Egy számot adott számú számjegyre kerekít. KEREKÍT(szám;hány jegy) Ha
második argumentuma 0 vagy nem adjuk meg, akkor a függvény a számot a
legközelebbi egészre kerekíti. Pl.: KEREKÍT(2,136;2) eredménye 2,14.

MARADÉK Tetszőleges szám osztásával kapott osztási maradékát adja eredményül.
MARADÉK(szám;osztó). Pl.:MARADÉK(5;2) eredménye 1

SZORZAT Argumentumainak szorzatát adja eredményül.

SZUMHA Összegzi a megadott feltételeknek eleget tevő cellákban található értékeket.

SZUMHA(tartomány;kritérium;összegtartomány).

Tartomány: a feltétel tartománya (ahol a feltétel megfelelését keresi)
Kritérium: feltétel
Összegtartomány: amit össze akarunk adni

SZUM Összeadja az argumentumlistájában található számokat.

LOGIKAI FÜGGVÉNYEK
ÉS Eredménye IGAZ, ha minden argumentuma IGAZ, egyébként HAMIS értéket
ad.

VAGY Ha bármely argumentuma IGAZ, akkor eredményül IGAZ logikai értéket ad.

HA Egy logikai feltételtől függően más és más kimenetet eredményez.
HA(feltétel;érték ha igaz;érték ha hamis)

Feltétel: A feltételnél szerepelnie kell egy relációs jelnek
 (kisebb<, nagyobb>, egyenlő=,legfeljebb <=,legalább >=, nem egyenlő <>) 
és a relációs jel mindkét oldalán lennie kell 1-1 értéknek vagy hivatkozásnak.
Érték ha igaz: mit írjon ki, ha a feltétel IGAZ
Érték ha hamis: mit írjon ki, ha a feltétel HAMIS
Összetett feltételeknél egymásba lehet ágyazni több HA függvényt

INFORMÁCIÓS FÜGGVÉNYEK
DARABÜRES Összeszámolja a megadott tartomány üres celláit.

SZÖVEG IGAZ eredményt ad vissza, ha argumentuma szám.

NEM.SZÖVEG IGAZ eredményt ad vissza, ha argumentuma nem szöveg.

SZÁM IGAZ eredményt ad vissza, ha argumentuma szám.

STATISZTIKA
ÁTLAG Argumentumainak átlagát adja eredményül

DARAB Megszámolja, hogy argumentumai között hány szám található

DARAB2 Megszámolja, hogy argumentumai között hány nem üres érték található

(számot, szöveget, logikai értéket, stb. mindent számol)

MAX Az argumentumai között szereplő legnagyobb számot keresi ki.

MIN Az argumentumai között szereplő legkisebb számot keresi ki.

NAGY Egy adathalmaz (sorba rendezés utáni) k-adik legnagyobb elemét adja vissza.

NAGY(tömb;k). pl A NAGY függvénnyel megállapíthatjuk az első (k=1), a
második(k=2) és a harmadik helyezett értéket (k=3). Ha k= 1 akkor
megegyezik a MAX függvénnyel

KICSI Egy adathalmaz (sorba rendezés utáni) k-adik legkisebb elemét adja vissza.
KICSI(tömb;k). Ha k=1, akkor megegyezik a MIN függvénnyel.

MÓDUSZ Egy tömbből kikeresi a leggyakrabban előforduló számot.

MEDIÁN Adott számhalmaz mediánját számítja ki, melynél a számok fele kisebb, másik
fele nagyobb.

DÁTUM- ÉS IDŐFÜGGVÉNYEK
ÉV 1900 feletti dátumértéket évvé alakít át, az eredmény egész szám. Pl.
=ÉV(2009.06.10) eredménye 2009.

HÓNAP A dátumérték argumentumnak megfelelő hónap értéket egész számként (1-12)
adja eredményül. Pl.: =HÓNAP(2009.06.10) eredménye 6.

NAP Egy dátumértéket a hónap napjává (1-31) alakít.=NAP(2009.06.10) eredménye
10.

MA A rendszerórából vett aktuális napi dátum dátumértéket adja eredményül.
=MA() eredménye az aznapi dátum.

MOST A rendszerórából vett aktuális napi dátumot és a pontos időt adja eredményül.

MÁTRIX FÜGGVÉNYEK
FKERES Egy tömb bal szélső oszlopában megkeres egy értéket, annak sora és a
megadott oszlop metszéspontjában található értéket adja eredményül.
Ha egy segédtáblában kell kikeresni egy értéket, amely segédtábla első oszlopában számok
találhatók növekvő sorrendben, akkor FKERES a megoldás. FKERES (mit keres;hol
keres;oszlopszám).

oszlopszám: a segédtábla hányadik oszlopát akarod kiíratni, pl.: 2 vagy 3.
mit keres: 1 cellahivatkozás,
hol keres: ki kell jelölni az egész segédtáblát, ha másolni akarod a képletet, akkor F4,

VIGYÁZZ: ha neked kell elkészíteni a segédtáblát, akkor az első oszlopba kerülnek növekvő
sorrendben a mettől értékek (és nem a meddig), csak számok lehetnek és ne hagyd ki a az első
értéket, ami legtöbbször 0.

VKERES Hasonló az előbbivel, csak ez a tömb első sorában keres elemet.

HOL.VAN Megkeres egy megadott értéket egy tömbben (sor- vagy oszloprészben), és
megadja a viszonylagos helyét a tömbben (hányadik). HOL.VAN(keresési
érték;tömb;egyezés típusa).

Egyezés típusa: 1 ha a tömb emelkedő sorrendben rendezett és a keresett értékkel egyenlő
Keresési érték: az a szám vagy szöveg vagy hivatkozás, aminek a helyét keressük
Tömb: összefüggő cellatartomány, amelyben a keresett értéket keressük 
vagy annál kisebb legnagyobb értéket keresi meg 
0 ha nem rendezett 
-1 ha a tömb emelkedő sorrendben rendezett és a keresett értékkel egyenlő 
vagy annál nagyobb legkisebb értéket keresi meg.

INDEX Egy tartomány megadott sorának és oszlopának metszéspontjában levő értéket
vagy hivatkozást adja eredményül. INDEX(tömb;sorszám;oszlopszám). Egy
tartományból szeretnénk kiíratni egy elemet, megadjuk a tartományt, és azt,
hogy hányadik sorban és hányadik oszlopban van.

Nagyon gyakran az INDEX és HOL.VAN függvényt egymásba ágyazzák, úgy hogy az
INDEX sor- vagy oszlopszámát a HOL.VAN függvény adja.

ADATBÁZIS FÜGGVÉNYEK
Akkor használjuk, ha feltételtől függően kell számolni összeget, átlagot, minimumot stb.
általánosan 3 argumentumot kell megadni:
1 adatbázis: ki kell jelölni a teljes adatbázist, címsorral együtt, ha a képletet másolod
akkor többnyire abszolúttá kell tenni (F4)
2 mező: 1 cella a címsorból amelyikra a számolás vonatkozik
3 kritérium: mindig két cella egymás alatt, a felső tartalmazza a címsorból az egyiket,
alatta az erre vonatkozó feltételt.
Pl. Keleti országok összlakossága. AB:SZUM Mező=lakosság, kritérium: égtáj
kelet

Figyelem: a mezőneveknek a feltételben ugyanúgy kell szerepelniük mint az adattábla
címsorában. Ha elírod, hibát jelezz, ezért jó megoldás hivatkozással kiíratni, pl. =C1.

Ha a kérdés úgy kezdődik Melyik…vagy Ki… általában AB.MEZŐ a megoldás.
Ha a kérdésben megjelenik a darab szó, vagy a kérdésbe beilleszthető úgy, hogy értelmes
marad, akkor valószínűleg az AB.DARAB vagy AB.DARAB2 a megoldás. Az AB.DARAB
csak a számokat számolja meg (nem összeadja, csak megszámolja hány darab szám), a
AB.DARAB2 mindent megszámol, megadja hány kitöltött cellát talál.

AB.ÁTLAG Kiszámolja az adatbázisban a mező argumentummal megadott oszlop azon
értékeinek átlagát, amelyek teljesítik a kritériumot.

AB.SZUM A feltételeknek megfelelő adatbázisrekordok adott mezőinek összegét számolja
ki. (SZUMHA függvénnyel kiváltható)

AB.MAX Az adatbázis adott feltételeknek eleget tevő rekordjaiból álló mezőben lévő
legnagyobb számot adja eredményül.

AB.MIN Az adatbázis adott feltételeknek eleget tevő rekordjaiból álló mezőben lévő
legkisebb számot adja eredményül.

AB.DARAB Megszámolja, hogy az adatbázisban az adott feltételeknek megfelelő
rekordokban hány darab szám van egy adott oszlopban.

AB.DARAB2 Megszámolja az adatbázisban a mező argumentummal megadott oszlop azon
nem üres celláinak számát, amelyek teljesítik a kritériumot. (kiváltható a
DARABTELI függvénnyel)

AB.MEZŐ Egy adatbázisból egy olyan mezőt ad vissza, amely megfelel a megadott
kritériumoknak. (pl.: Melyik


SZÖVEGFÜGGVÉNYEK

AZONOS Megvizsgálja, azonos-e két karakterlánc, IGAZ értéket eredményez a teljes
egyezés esetén.

BAL Egy karakterlánc bal szélső karaktereit eredményezi. BAL(szöveg; hányat).
Pl.: BAL(kakukktojás;6) eredménye „kakukk”.

JOBB Egy karakterlánc jobb szélső karaktereit eredményezi. JOBB(szöveg; hányat).
Pl.: JOBB(kakukktojás;5) eredménye „tojás”.

NAGYBETŰS Az argumentumban megadott kisbetűket nagybetűkre alakítja át, a többi
karakterre hatástalan.

KISBETŰ Az argumentumban megadott nagybetűket kisbetűkre alakítja át, a többi
karakterre hatástalan.

CSERE Szövegen belül kicserél adott számú karaktert.
CSERE(régi szöveg;honnan;hányat;új szöveg)
CSERE (2008;3;2;09) eredménye 2009 (a harmadik karaktertől kettő karaktert
09-re módosít)