hivatkozás-függvények - B

2021-10-19    Excel 2019
sor függvény, oszlop függvény
Egy cella vízszintes vagy függőleges pozícióját a SOR illetve az OSZLOP függvénnyel kérdezhetjük le. A függvények egyetlen, nem kötelező argumentuma, a vizsgálandó cella megadására szolgál. Ha az argumentum hiányzik, akkor a függvényt tartalmazó celláról kapunk információt.
a SOR és az OSZLOP függvények eredménye
Ha a függvények argumentuma tartomány, akkor a tartomány első cellájának sor illetve oszlop-indexét kapjuk eredményül. Természetesen tömbös feldolgozásban más a helyzet.
Nézzünk egy-egy komplett példát a függvények alkalmazására. Van egy hatalmas, egyoszlopos listánk ügyfeleink címével. Minden ügyfélhez négy adat tartozik: <név>, <település>, <utca, szám>, <irányítószám>. Ezt a listát kell táblázattá alakítanunk. Az oszlopok a táblázatban <név>, <irányítószám>, <település>, <utca, szám> sorrendben kell hogy álljanak. A feladat csak látszólag bonyolult… Lássuk a megoldást!
a SOR függvény alkalmazása
Bal oldalon az eredeti táblázatot, középen a képleteket, jobb oldalon a már kitöltött táblázatot látjuk. Eddig rendben vagyunk. De, hogy fogunk megszabadulni a felesleges soroktól? Rendezéssel!
Először beszámozzuk a táblázat sorait. A <név>-vel kezdődő sorok lesznek az egyesek, a <település>-sel kezdődők a kettesek, az <utca, szám> kezdetűek a hármasok és az <irányítószám> kezdetűek a négyesek.
a SOR függvény alkalmazása
A sorszámozós képlethez nem árt egy kis magyarázat. A MARADÉK függvény egy osztás maradékát adja eredményül. Az alábbi szabály szerint.
a MARADÉK függvény alkalmazása
Tehát, a függvény eredménye a hányados és egész részének az osztóval való szorzatának különbsége. A mi esetünkben az osztandó és az osztó is egész szám, ezért a függvény nullát, egyet, kettőt vagy hármat ad eredményül. A számozást eggyel akarjuk kezdeni, ezért adtam a függvény eredményéhez egyet: 0+1, 1+1, 2+1, 3+1. A sor-indexből, amit a SOR függvénnyel generáltam, kettőt levontam, hogy az első egyes szám a munkalap második sorára essen. A folytatás már gyerekjáték.
a SOR függvény alkalmazása
Nézzünk legalább egy OSZLOP-os példát is! Az FKERES függvény talán az egyik leggyakrabban használt kereső-függvény. Tudjuk is használni, de néha furcsa helyzettel szembesülünk…
az OSZLOP függvény alkalmazása
Van egy áruház-lánc, négy áruházzal. A felső táblázatban álló áruk készlet-értékét szeretnénk kiszámítani a „készlet” munkalapon álló darabszámok alapján. Hát ez pofonegyszerű.
Fogom az árat és megszorzom az FKERES függvénnyel megkeresett darabszámmal. Nézzük például, hány forint értékben van sajtreszelő az érdi áruházban: =C2 * FKERES( A2 ; készlet!$A$2:$E$7 ; 2 ). Ez eddig oké, de hogy fogjuk ezt a képletet jobbra másolni, mert az FKERES harmadik argumentuma az ózdi áruház esetében már nem kettő, hanem három, Vácnál meg négy. Módosítanunk kell az FKERES függvény harmadik argumentumát! A „találat” oszlopának indexét képeznünk kell. De, hogyan?
Az érdi áruház képlete a D oszlopban áll. Ez a munkalap 4. oszlopa. A készlet lapon az érdi áruház darabszámait a B oszlopban találjuk. Tehát a lap 2. oszlopában. Az ózdi áruházat vizsgálva, a képlet az 5. a darabszámok a 3. oszlopban állnak. Aztán jön Vác, 6-tal és 4-gyel. Nem folytatom. Mindenki kitalálta: OSZLOP()-2 lesz az FKERES harmadik argumentuma. Most már csak a megfelelő hivatkozás-típusokat kell beállítanunk!
az OSZLOP függvény alkalmazása
sorok függvény, oszlopok függvény
Ezzel a két függvénnyel egy tartomány cellákban mért magasságát (SOROK), illetve szélességét (OSZLOPOK) kérdezhetjük le. Egyetlen argumentumuk a tartomány-hivatkozást képző kifejezés vagy az azt tartalmazó cella hivatkozása.
A képletben önállóan, akár csak a SOR és az OSZLOP függvények, nagyon ritkán fordulnak elő. Inkább beágyazottan, más függvények argumentumaként találkozhatunk velük. Mint a következő példában is.
példa a SOROK és OSZLOPOK függvényekre
Egy kutatás jegyzőkönyvét látjuk. A kutatáshoz csatlakozó városok havonta jelentik a vizsgálat tárgyát képező tulajdonság értékét. A táblázat hónapról-hónapra és újabb és újabb városok csatlakozásával egyre csak bővül. Az egyszerű kezelhetőség érdekében a címkéket tartalmazó tartományokat elneveztük. A nevek „városok” és „hónapok” lettek.
példa a SOROK és OSZLOPOK függvényekre
Hozzunk létre képletet, amelynek eredménye a táblázat aktuálisan utolsó cellájának szöveges hivatkozása! Két megoldást gondolkodás nélkül be tudunk mondani…
példa a SOROK és OSZLOPOK függvényekre
Az első képletben a SOROK és az OSZLOPOK függvényekkel képeztük a CÍM sor-index és oszlop-index argumentumát. A második, tömbös feldolgozású képletben már a SOR és OSZLOP függvényekkel tettük ugyanezt.
Az első képlet nem igényel komolyabb magyarázatot. A statisztikai értékek tartománya két-két cella távolságra van a munkalap szélétől, ezért kellett kettőt hozzáadni az adattartomány szélességéhez és magasságához…
Ha a SOR vagy az OSZLOP függvények tömbös kiértékelését kérjük (a Shift+Ctrl+Enter billentyűkkel), akkor a megadott tartomány sor-indexeinek illetve oszlop-indexeinek tömbjét kapjuk eredményül.
a SOR és OSZLOP függvények tömbös kiértékelése
A második képletben ezeknek a tömböknek az utolsó elemét a MAX függvénnyel választottuk ki.
egyebek
A már ismertetett függvényekhez képest a LAP és LAPOK függvény-páros jelentősége elhanyagolható. Csak nagyon speciális feladatok megoldására használhatók.
a LAP és LAPOK függvények
Tessék mondani, mi az a 3D tartomány? A három dimenziós tartomány röviden. És hosszan? Több munkalap azonos tartománya. Hivatkozásának általános alakja: <első munkalap>:<utolsó munkalap>!<első cella>:<utolsó cella>.
Képzeljünk el egy névvel azonosított 3D tartományt (üzemek), amelybe beszúrunk egy új lapot az első és utolsó munkalapja közé. Ez a művelet nincs hatással a név hivatkozására, mert az első és az utolsó lap nem módosult. Ezek szerint a 3D tartomány lap-száma változó. Illetve változó lehet. És a napi gyakorlatban nagyon ritkán változik. De, ha mégis, akkor ott a LAPOK függvény, hogy lekérdezhessük az aktuális lap-számot: LAPOK(üzemek). Persze, ha nem változott, akkor is lekérdezhetjük.
A TERÜLET függvény is ritka madár. Eredménye egy darabszám. Hány nem szomszédos tartományt azonosít az egyetlen argumentumával megadott név.
azonos tartalmú, de nam szomszédos cellák nevesítése
A kép tanúsága szerint a SzületésiDátumok hivatkozása további tartományokkal bővíthető. Csak teszünk a bejegyzés végére egy pontosvesszőt és már jelölhetjük is ki a következő tartományt. Ha bővíthető, akkor viszont lekérdezhető is kell, hogy legyen: TERÜLET(SzületésiDátumok).
Aki nem hiszi, járjon utána!
margitfalvi.arpad@gmail.com