Telefonszámunk: 1-472-0679

Hivatkozás mindig a felette lévő sorra

2020-02-27 - horvimi - Megtekintések száma: 796 - Kategória: Függvények, Képletek
Hivatkozott Excel verzio: Excel 2010 Excel 2013 Excel 2016 Office365

Ha egy képletben szokásos módon meghivatkozzuk a felette lévő sort (abban egy cellát), akkor az Excel a képlet és a hivatkozott cella közötti relatív távolságot jegyzi meg. Ha a hivatkozott tartományt vagy a képletet tartalmazó cellát mozgatjuk, akkor a képlet és az eredmény nem változik, de a relatív távolság igen. Ez egészen addig nem érdekel minket, sőt, örülünk neki, amíg az nem lesz a feladat, hogy a képlet mozgatásakor is maradjon meg a relatív távolság. A példánkban az lesz a feladat, hogy mindig a képlet feletti sorra hivatkozzunk.

A bal oldali ábrán az utolsó cellában látjuk az eredeti hivatkozást és az eredményt, miszerint a felette lévő cellára hivatkozik, ami a D5. A kettő között egy sor távolság van felfelé. Ha beszúrunk egy sort a képlet elé, akkor a képlet eggyel lejjebb mozog, megtartja azt, hogy a D5-re hivatkozik, de a távolság most már 2 sor.

Mi azt szeretnénk, hogy a hivatkozás a mindenkori felette lévő sorra történjen. A képletet kijavíthatjuk, hogy mutasson a D6-ra, de manuálisan ez a karbantartás nehézkes és hibalehetőséget rejt, főleg ha gyakran fordul elő az ilyesmi.

hivatkozas-valtozasa

Számítsuk ki, hogy hová kell hivatkozni!

Ha tudjuk, hogy a képlet melyik sorban van, akkor a hivatkozásnak az eggyel kevesebbedik sorra kell mutatnia, ugyanabban az oszlopban. A képlet cellájának sorát és oszlopát a SOR() és az OSZLOP() függvényekkel tudjuk lekérdezni.

sor-oszlop-fuggvenyek

Könnyen belátható, hogy a példánkban a képlethez képest eggyel felette lévő cella a SOR()-1-dik sorban van, az OSZLOP()-dik oszlopban. A következő probléma az, hogy a képletekben A1 típusú cellacímeket használunk, tehát ha tudom a sor és oszlop számát, azt címmé kell alakítani, amit a CÍM() függvénnyel lehet megtenni. Alapértelmezésként a sor és oszlop számból egy abszolút (dollárjeles) címet ad vissza.

=CÍM(sor,oszlop)

Cim_fugvenyKeplet_cime

Képlet feletti cella címe

=CÍM(SOR()-1;OSZLOP())

Keplet_feletti-cime

Megvan a cím, hogy lesz ebből hivatkozás?

A cím szövegesen előállt, rá kéne venni az Excelt, hogy ezt a szöveget cellahivatkozásnak érzékelje. Erre való az INDIREKT függvény.

=INDIREKT(CÍM(SOR()-1;OSZLOP()))

keplet-feletti-ertek

Az INDIREKT egy bármilyen módon összerakott, helyes szöveges hivatkozást valódi hivatkozássá alakít. Ezt a képletet bárhová mozgatva vagy akár másolva, mindig ugyanolyan relatív távolságra fog hivatkozni.

A képlethez képesti relatív távolságot az OSZLOP esetén is változtathatjuk, így nem csak az aktuális oszlopra, hanem ahhoz képest valamennyivel balra vagy jobbra is címezhetünk.

Egy alternatív megoldás

Adott oszlop eggyel feljebb lévő cellájára lehet hivatkozni úgy is, hogy az oszlop azonosítóhoz hozzáfűzöm az aktuális sor előtti számot. Tehát ha hivatkozni szeretnék  a”B” oszlop előző sorára, akkor írhatom azt, hogy:

=INDIREKT("B" & SOR()-1)

B-oszlop-elozo-sora

Próbáld ki, hogy mi lesz, ha beszúrsz egy sor a képlet elé!

Az eredmény üres lesz, de ha kitöltöd az új sorban a B oszlop adatát, akkor azt átveszi az eredmény is.

Munkafüzet letöltése

Felette-levo-sor-cimzese-INDIREKT

Mindez videón

 

 

1 Comment
  1. Még alternatívabb megoldás, D3-ba írható:
    =INDIREKT(“B” & (CELLA(“sor”;D3)-1))
    Egyébként ha sorbeszúrásnál lenne fontos, hogy a képlet maradjon, akkor Ctrl+C a képletes sorra, és úgy beszúrni, majd az eredeti sort DELETE.

Vélemény, hozzászólás?