Excel táblázatkezelés kezdőknek › 10.4 lecke

Excel tanfolyam · 10.4 lecke

VKERES és vízszintes adatok

Nem minden táblázat „álló”. Az órarendben a napok a felső sorban állnak, egy havi kimutatásban a hónapok, egy jegyhatár-táblázat is lehet vízszintes. Ilyenkor a kulcsok nem egy oszlopban, hanem egy sorban vannak, és az FKERES nem segít. A VKERES ugyanazt tudja, csak „elfektetve”: az első sorban keres, és egy lejjebb lévő sorból ad vissza.

  • ⏱ kb. 45 perc
  • 🎯 kezdőknek
  • 🎒 10. osztálytól

A lecke végére

  • VKERES-sel keresel vízszintes táblában;
  • helyesen számolod a sorszámot;
  • pontos és közelítő egyezést is használsz;
  • a sorszámot is cellából veszed;
  • a táblát szükség esetén transzponálod.

Amit már tudsz

  • az FKERES-t pontos és közelítő egyezéssel (10.2, 10.3);
  • a heti órarendet (1.8);
  • a beillesztési lehetőségeket (2.3);
  • a feltételt cellából (9.5).

A VKERES

VKERES(keresési_érték;tábla;sor_szám;[tartományban_keres]): a tábla első sorában keres, és a megadott sorszámú sorból ad vissza értéket. Minden más ugyanúgy működik, mint az FKERES-nél: HAMIS = pontos, IGAZ = közelítő egyezés, a táblát rögzítsd.

AB: hétfőC: keddD: szerdaE: csütörtökF: péntek
1. óramagyarmatematikaangolfizikatörténelem
2. óramatematikaangolbiológiamagyarmatematika
3. óraangoltörténelemmatematikainformatikakémia
4. óratestneveléskémiamagyarangolrajz

Ha a napok a B1:F1 cellákban, az órák a 2–5. sorban vannak: =VKERES("szerda";$B$1:$F$5;4;HAMIS) → matematika (a szerdai 3. óra).

💡 F és V

Az FKERES függőlegesen (oszlopban) keres, a VKERES vízszintesen (sorban). Az angol nevük VLOOKUP (vertical) és HLOOKUP (horizontal): a betűk keresztben vannak, ezért érdemes a magyar „f = függőleges” szabályt megjegyezni.

A sorszám

🔮 Jósold meg!

A tábla a B1:F5, az 1. sorban a napok, a 2. sorban az 1. óra, a 3. sorban a 2. óra, és így tovább. Melyik sorszám adja a 3. órát: 3, 4 vagy az, hogy „a munkalap 4. sora”?

Megoldás

4, de nem azért, mert a munkalap 4. sora, hanem mert a tábla 4. sora: 1 = a napok, 2 = 1. óra, 3 = 2. óra, 4 = 3. óra. Ha a tábla például az 5. sorban kezdődne, a 3. óra a munkalap 8. sorában lenne, a sorszám mégis 4 maradna. Általánosan: sorszám = óra + 1, így cellából is számolható: =VKERES($H$1;$B$1:$F$8;$H$2+1;HAMIS), ahol H1 a nap, H2 az óra.

Vízszintes határtábla

A határtábla vízszintesen is elkészíthető (első sor: alsó határok növekvő sorrendben, második sor: jegyek):

HIJKL
1. sor: alsó határ025405570
2. sor: jegy12345

=VKERES(B2;$H$1:$L$2;2;IGAZ): ugyanaz az eredmény, mint a 10.3 álló tábláján. A szabályok is ugyanazok: az első sor növekvő sorrendben, a legkisebb lehetséges értékkel kezdve.

Transzponálás

Ha egy vízszintes táblát inkább függőlegesen használnál (például FKERES-sel vagy szűréssel), fordítsd el:

  • Egyszeri átfordítás: jelöld ki a táblát → másolás → kattints egy üres cellára → jobb gomb → Irányított beillesztés → jelöld be a Transzponálás lehetőséget (2.3). A sorokból oszlopok lesznek, és fordítva. Az eredmény nem frissül, ha az eredeti változik.
  • Élő átfordítás: az újabb Excel-verziókban a =TRANSZPONÁLÁS(B1:F5) képlet egy élő, frissülő, elforgatott másolatot ad.

✅ Melyik a jobb?

Ha a táblázatot te tervezed, általában az álló (függőleges) szerkezet a jobb: egy rekord egy sor (7.1), és rendezni, szűrni, bővíteni is könnyebb. A VKERES akkor kell, ha a vízszintes táblát kapod, és nem akarod átalakítani.

Kihívás: mi a következő órám?

🗓️ Órarend-kereső

  1. Vedd elő az 1.8-ban készült heti órarendet (vagy a fenti mintát bővítsd 7 órára). A napok legyenek az első sorban, az órák alattuk.
  2. Készíts két bemeneti cellát: Nap (legördülő lista: hétfő … péntek, 9.5) és Óra (1–7).
  3. VKERES-sel írd ki a tantárgyat, a sorszámot az Óra cellából számolva.
  4. Bővítsd egy második táblával (Tantárgy | Terem), és FKERES-sel írd ki a tantárgy mellé a termet is (egy VKERES és egy FKERES egymás után, vagy egymásba ágyazva: 5.7).
  5. Rossz napnév beírásakor mit kapsz? (Vagy épp ezért kell a legördülő lista!)
  6. Készíts egy vízszintes jegyhatár-táblát, és ellenőrizd VKERES-sel, hogy ugyanazokat a jegyeket adja, mint a 10.3 FKERES-e.

Hibavadászat

🐞 1. Peti rossz iránya

Peti az órarendben FKERES-sel kereste a „szerda” szót: =FKERES("szerda";$B$1:$F$5;4;HAMIS). Az eredmény #HIÁNYZIK.

Mi a megoldás?

Az FKERES a tábla első oszlopában keres (B2:B5: a hétfői tantárgyak), ott nincs „szerda”. A napok az első sorban vannak, ezért VKERES kell: =VKERES("szerda";$B$1:$F$5;4;HAMIS) (1. rész).

🐞 2. Lili elcsúszott órái

Lili táblája a B3:F9 tartományban van (a 3. sorban a napok). A 2. órát keresve a munkalap sorszámát adta meg: =VKERES("kedd";$B$3:$F$9;5;HAMIS), és a 3. órát kapta.

Mit tegyen?

A sorszámot a tábla első sorától kell számolni (2. rész): 1 = napok, 2 = 1. óra, 3 = 2. óra. Lili a 2. órához a 3-as sorszámot adja meg (vagy általánosan: óra + 1).

Összefoglalás

  • =VKERES(érték;tábla;sor_szám;HAMIS/IGAZ): az első sorban keres, lejjebb lévő sorból ad vissza.
  • A sorszámot a tábla első sorától számold; cellából is számolható.
  • Közelítő egyezésnél az első sor növekvő sorrendű, a legkisebb értékkel kezdve.
  • F = függőleges (oszlopban keres), V = vízszintes (sorban keres).
  • Transzponálás: Irányított beillesztés vagy TRANSZPONÁLÁS függvény.

Ellenőrizd magad!

Hol keres a VKERES?

A tábla első sorában.

A tábla A5:G12. Melyik sorszám adja a 8. munkalapsort?

4 (az 5. sor = 1, a 6. = 2, a 7. = 3, a 8. = 4).

Mikor jobb FKERES-t használni VKERES helyett?

Ha a kulcsok egy oszlopban vannak (álló tábla).

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények használata, adatok táblázatos megjelenítése); a táblázatszerkezet tudatos megválasztása (álló és fekvő tábla).

Módszertan

A lecke a VKERES-t az FKERES „elfektetett” változataként, az ismert fogalmakra építve tanítja, így rövidebb bevezetés után a diák gyorsan alkalmazhatja. A jóslás a sorszám leggyakoribb félreértését (munkalapsor vagy táblasor) dolgozza fel. A saját órarend (1.8) ismerős, motiváló adatforrás. A transzponálás és az „álló tábla jobb” elv az adatszerkezet tervezésének szempontját erősíti (7.1).

Időterv

A VKERES: 8 perc. A sorszám és jóslás: 6 perc. Vízszintes határtábla: 5 perc. Transzponálás: 5 perc. Kihívás: 17 perc. Hibavadászat: 4 perc.

Következő lecke: 10.5 HOL.VAN: egy adat helyének megkeresése

Nem az értéket, hanem a helyét keressük: hányadik a listában? Az INDEX + HOL.VAN első fele.