Kereső függvények és használatuk a Microsoft Excel programban
dr. Nyári Tibor
FKERES, VKERES
melyik táblában kell keresni az értéket a tábla azon oszlopának táblán belüli sorszáma, amelyből az eredményt meg kívánjuk kapni HAMIS – pontos egyezés szükséges; IGAZ (elhagyjuk) – növekvő sorba rendezés szükséges
Tulajdonságok: FKERES (függőlegesen keres)
- bal oldali oszlopban végzi a keresést
- növekvő sorrendbe rendezett adatok VKERES (vízszintesen keres)
- első sorban végzi a keresést - növekvő sorrendbe rendezett adatok
rendszáma AHA-111 IBI-222 PAP-333 KOD-444 LAP-555 KOR-666
típusa Trabant Moszkvics Lada Volga Skoda Watrburg
A gépkocsi tulajdonosa összsúlya Winch Eszter Zöld Virág Piros Alma In Dia Virs Lee Bonv Iván
800 1500 1250 2100 1200 1150
A súlyadó megoszlása kilógrammonként: Gépkocsi összsúlya: Súlyadó: 0 501 801 1201
- 500 kg - 800 kg - 1200 kg kg-tól
1 2 3 4
000 000 000 000
Ft Ft Ft Ft
kg kg kg kg kg kg
súlyadó mértéke 2 4 4 4 3 3
000 000 000 000 000 000
Ft Ft Ft Ft Ft Ft
KERES
I.
egy sorból, vagy oszlopból álló tartomány, - növekvő sorrendben egy sorból, vagy oszlopból álló tartomány, mérete azonos a keresési_vektor méretével
Tulajdonságok: - nincs helymegkötés!!!
KERES
2. feladat
3. feladat
4. feladat
5. feladat
6. feladat
7. feladat
8. feladat
Összesen:
Osztályzat:
12 11 10 11 13 12 10 5 7 12 10,3
14 10 12 11 10 4 0 0 11 3 7,5
14 13 1 11 12 10 8 9 6 12 9,6
10 10 11 12 12 11 12 9 5 10 10,2
12 14 12 13 11 11 13 12 10 10 11,8
12 12 12 13 12 10 11 11 10 7 11
12 12 13 12 12 10 11 11 10 4 10,7
12 14 16 15 12 13 9 6 7 2 10,6
98 96 87 98 94 81 74 63 66 60 81,7
5 5 4 5 5 4 3 2 2 2 3,70
Név
1. feladat
- növekvő sorrendbe rendezett adatok
Melle Imre Mekk Elek Lakodalom Lajos Medve László Rugka Pál Minden Áron Hiány Zoltán Ének Elek Rend Elek Nyik Hajnalka Átlagok: Ponthatárok:
elégtelen elégséges közepes jó jeles
1 2 3 4 5
0 60 70 80 90
59 69 79 89 100
Osztályzatok megoszlása: 0 db 3 db 1 db 2 db 4 db
KERES II.
az a cellatartomány, amelyben a keresési_érték-et meg kell keresni
AB.MEZŐ
a keresett érték oszlopfeliratának hivatkozása a megadott feltételeket tartalmazó cellatartomány (egy oszlopfelirat és egy feltételt tartalmazó cella – előkészítés!)
Tulajdonságok: AB.MEZŐ
- nincs helymegkötés!!! - nincs sorrendi megkötés sem!!!
Áru megnevezése:
Ing Kabát Nadrág Cipő Szoknya
Megrendelt mennyiség: 121 150 165 110 170
db db db db db
ÁFA kulcs: Végösszeg: Átlagos teljesítés: Maximális egységár: Minimális leszállított mennyiség: Legkisebb rendelt mennyiség teljesítése:
Leszállított mennyiség:
Teljesítés aránya:
110 db 135 db 95 db 56 db 105 db
90,91% 90,00% 57,58% 50,91% 61,76%
25% 10 902 500 Ft 70,23% 35 600 Ft 56 db 50,91%
Egységár: 4 35 13 18 9
900 600 700 500 900
Ft Ft Ft Ft Ft
Nettó ár: 4 1 1 1
539 806 301 036 039
000 000 500 000 500
Ft Ft Ft Ft Ft
Bruttó ár: 6 1 1 1
673 007 626 295 299
750 500 875 000 375
Ft Ft Ft Ft Ft
Megrendelt mennyiség: 110 db
Egyéb kereső függvények: HOL.VAN
a lehetséges keresési_érték-et tartalmazó összefüggő cellatartomány
Tulajdonságok: Egyezés_típus: értéke -1, 0 vagy 1 lehet; azt határozza meg, hogy a keresési_értékkel való milyen típusú egyezéseket keres a Microsoft Excel a táblában. Ha az egyezés_típus értéke 1, akkor a HOL.VAN azt a legnagyobb értéket keresi meg, amely egyenlő vagy kisebb, mint a keresési_érték. A táblának emelkedő sorrendbe rendezettnek kell lennie. Ha az egyezés_típus értéke 0, akkor a HOL.VAN az első olyan értéket keresi meg, amely pontosan egyenlő a keresési_értékkel. A táblának nem kell rendezettnek lennie. Ha az egyezés_típus értéke -1, akkor a HOL.VAN azt a legkisebb értéket keresi meg, amely egyenlő vagy nagyobb, mint a keresési_érték. A táblának csökkenő sorrendben rendezettnek kell lennie.
Ha az egyezés_típus argumentumot nem adjuk meg, akkor az alapértéke 1.
Például: Termék: alma körte banán búza rozs citrom
Darabszám (db): 41 54 63 78 91 102
keresett érték
keresési hely (oszlop vagy sor)
HOL.VAN(50;B2:B7;1)
1
HOL.VAN(78;B2:B7;0)
4
HOL.VAN(90;B2:B7;-1)
#HIÁNYZIK
hányadik sorban (oszlopban) van a keresett érték
INDEX
a tömb vagy hivatkozás azon sora, amelyből az értéket vissza kell adni
a tömb vagy hivatkozás azon oszlopa, amelyből az értéket vissza kell adni
Például: sor száma oszlop száma Termék: alma körte banán búza rozs citrom
Darabszám (db): 41 54 63 78 91 102
tömb (sor vagy oszlop) INDEX(A2:B7;2;2)
54
INDEX(A2:B7;5;1)
rozs
INDEX(A2:B7;6;2)
102
érték
KÖTÖTTSÉGEK NÉLKÜLI KERESÉS OSZLOPBAN:
KÖTÖTTSÉGEK NÉLKÜLI KERESÉS SORBAN:
INDIREKT
a hivatkozás típusa (S1O1 típus vagy A1 típus), alapértelmezésben elhagyható
A függvény eredménye a szövegként megadott hivatkozás. A hivatkozásokat a Microsoft Excel azonnal kiértékeli és megjeleníti tartalmukat. Az INDIREKT függvényt akkor használjuk, ha egy cella hivatkozását meg szeretnénk változtatni egy képletben, de magát a képletet nem. Amikor egy cellára hivatkozó képletet készítünk, a cella hivatkozása frissítésre kerül, ha a cellát áthelyezzük a Kivágás lehetőséggel, vagy ha a cella sorok vagy oszlopok beszúrása, illetve törlése miatt került áthelyezésre. Ha azt szeretnénk, hogy a képlet mindig ugyanarra a cellára hivatkozzon, függetlenül attól, hogy a felette levő sort töröljük, vagy a cellát áthelyezzük, használjuk az INDIREKT munkalapfüggvényt. Ha például mindig az C12 cellára szeretnénk hivatkozni, használjuk az alábbi szintaxist: =INDIREKT(„C12")
Például: Termék: B2 B3 banán búza B6 citrom
Darabszám (db): 41 54 63 78 91 102
INDIREKT(A2)
41
INDIREKT(A3)
54
INDIREKT(A6)
91
VÁLASZT
a VÁLASZT függvény ezek közül választ (képlet, hivatkozás, érték, stb.)
A függvény az érték argumentumok közül az index sorszámút adja vissza. A VÁLASZT függvényben az érték argumentumok száma legfeljebb 29 lehet. Amennyiben például az érték1; ... ; érték7 argumentum a hét napjainak neve, akkor a VÁLASZT függvény a napok egyikét adja eredményül, ha az index értékének egy 1 és 7 közötti számot adunk meg.
Például: Termék: 1. 2. 3. 4. 5. 6.
Termékek alma körte ló citrom narancs kolbász
VÁLASZT(3;A2;A3;A4;A5;A6;A7)
3.
VÁLASZT(6;A2;A3;A4;A5;A6;A7)
6.
VÁLASZT(5;B2;B3;B4;B5;B6;B7)
narancs
