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.
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.
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)
Képlet feletti cella címe
=CÍM(SOR()-1;OSZLOP())
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()))
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.
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)
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
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.