Strukturált hivatkozások az Excel-ben | Lépésről lépésre útmutató példákkal

Hogyan lehet strukturált hivatkozásokat létrehozni az Excelben?

A strukturált hivatkozások az excel táblákkal kezdődnek. Amint az excelben létrehozott táblázatok automatikusan strukturált referenciákat hoznak létre az Ön számára.

Most vessen egy pillantást az alábbi képre.

  • 1. lépés: Adtam egy linket a B3 cellához, ahelyett, hogy a linket B2-ként mutattam volna, hanem 1. táblázatként [@Sales]. Itt Table1 a neve az asztalra, és @Sales az oszlop vagyunk utalva. Az oszlop összes cellájára egy táblázat neve hivatkozik, majd az oszlop címsora követi.
  • 2. lépés: Most a tábla nevét Data_Table-re változtatom, és az oszlop fejlécét Amount-ra változtatom .
  • 3. lépés: A táblázat nevének megváltoztatásához vigye a kurzort a táblázatba, majd válassza a Tervezés> A táblázat neve lehetőséget.

  • 4. lépés: Adja meg a táblázat nevét Data_Table néven.

  • 5. lépés: Most változtassa meg a B3 cellára való hivatkozást.

Tehát megértettük, hogy a strukturált hivatkozásnak két része van: Tábla neve és Oszlop neve.

Példák

Ezt a Strukturált hivatkozások Excel sablont innen töltheti le - Strukturált hivatkozások Excel sablont

1. példa

Strukturált hivatkozásokkal dinamikussá teheti a képletet. A normál cellahivatkozásoktól eltérően lehetővé teszi a képlet élését az adattartományban történő hozzáadás és törlés esetén.

Hadd alkalmazzam a SUM formulát mind a normál tartományra, mind az excel táblára.

SUM képlet a normál tartományhoz.

SUM képlet az Excel táblához.

Hadd adjak néhány sort a normál és az excel tábla adataihoz is. Hozzáadtam 2 sort az adatokhoz, most lásd a különbséget.

Az excel táblázat strukturált hivatkozása a frissített értéket mutatja, de a normál adattartomány csak akkor jeleníti meg a frissített értékeket, ha manuálisan végez valamilyen módosítást a képletben.

2. példa

Most nézzen még egy példát. Van egy terméknév-, mennyiség- és árinformációm. Ezen információk felhasználásával el kell érnem az Értékértéket.

Az eladási érték megszerzéséhez a képlet Mennyiség * Ár . Alkalmazzuk ezt a képletet a táblázatban.

A képlet szerint [@QTY] * [@PRICE]. Ez érthetőbb, mint a B2 * C2 normál referenciája . Nem kapjuk meg a táblázat nevét, ha a képletet a táblázatba tesszük.

Problémák az Excel strukturált referenciáival

A strukturált referenciák használata során néhány problémával szembesülünk, amelyeket alább felsorolunk.

1. probléma

A strukturált referenciáknak is megvannak a maguk problémái. Mindannyian ismerjük az excel képlet alkalmazását és másolását vagy áthúzását a többi megmaradt cellába. Ez nem ugyanaz a folyamat a Strukturált hivatkozásokban, kicsit másképp működik.

Most nézze meg az alábbi példát. A SUM képletet alkalmaztam excelben a normál tartományra.

Ha összegezni akarom az Ár és az Értékértéket, akkor egyszerűen átmásolom, beillesztem vagy áthúzom az aktuális képletet a másik két cellába, és ezzel megkapom az Ár és eladás értéke SUM értékét.

Most alkalmazza ugyanazt a képletet az Excel táblázatra a Mennyiség oszlopra.

Most megkaptuk a Mennyiség oszlop összegét. A normál tartományhoz hasonlóan a képlet másolja az aktuális képletet, és illessze be az Ár oszlopba, hogy megkapja az Ár teljes értékét.

Istenem!!! Nem az Ár oszlop teljes számát mutatja, hanem csak a Mennyiség oszlopot. Tehát nem tudjuk átmásolni és beilleszteni ezt a képletet a szomszédos cellába vagy bármely más cellába, hogy a relatív oszlopra vagy sorra utaljunk.

Húzza a képletet a hivatkozás módosításához

Most már ismerjük a korlátozását, strukturált hivatkozásokkal már nem tudjuk elvégezni a copy-paste munkát. Akkor hogyan lehet legyőzni ezt a korlátot?

A megoldás nagyon egyszerű, csak másolni kell a képletet a másolás helyett. Válassza ki a képlet cellát, és használja a kitöltő fogantyút, és húzza a maradék két cellába az oszlop hivatkozásának az Ár és Eladó értékre való megváltoztatásához.

Most frissítettük a képleteket a megfelelő összegek megszerzéséhez.

2. probléma

Láttunk egy problémát a szerkezeti hivatkozásokkal, és megtaláltuk a megoldást is, de van még egy problémánk, nem tudjuk abszolút referenciaként megadni a hívást, ha a képletet más cellákba húzzuk.

Vessünk egy pillantást az alábbi példára. Van egy értékesítési táblázatom több bejegyzéssel, és szeretném konszolidálni az adatokat a SUMIF függvény használatával az excelben.

Most a SUMIF függvényt fogom alkalmazni az egyes termékek összesített értékesítési értékeinek megszerzéséhez.

A januári hónapra alkalmaztam a képletet, mivel ez egy strukturált hivatkozás, ezért nem tudjuk átmásolni és beilleszteni a képletet a fennmaradó két oszlopba, ez nem változtatja meg a hivatkozást februárra és márciusra, ezért húzni fogom a képletet.

Oh !! Nem kaptam értékeket a február és március oszlopban. Mi lenne a probléma ??? Nézze meg szorosan a képletet.

Jan hónaptól húztuk a képletet. A SUMIF függvényben az első argumentum a Criteria Range Sales_Table [Product],  mivel az általa módosított képletet áthúztuk az Sales _Table [Jan] -ra.

Tehát hogyan kezeljük? Meg kell adnunk az első argumentumot, azaz a Product oszlopot abszolútnak, a többi oszlopot pedig relatív referenciának. A normál referenciától eltérően nincs olyan luxusunk, hogy az F4 billentyűt használjuk a hivatkozási típus megváltoztatására.

A megoldás az, hogy meg kell másolnunk a hivatkozási oszlopot az alábbi képen látható módon.

Most áthúzhatjuk a képletet két másik oszlopra. A kritériumtartomány állandó lesz, és az egyéb oszlop hivatkozások ennek megfelelően változnak.

Pro tipp: A ROW abszolút referenciaként való használatához kettős ROW bejegyzést kell készítenünk, de a ROW név elé @ jelet kell tennünk.

= Értékesítési táblázat [@ [termék]: [termék]]

Hogyan lehet kikapcsolni a strukturált referenciát az Excelben?

Ha nem rajong a strukturált hivatkozásokért, az alábbi lépéseket követve kikapcsolhatja.

  • 1. lépés: Válassza a FÁJL> Opciók menüpontot.
  • 2. lépés: Képletek> Törölje a A táblák nevének használata a képletekben jelölőnégyzet jelölését.

Dolgok, amikre emlékezni kell

  • Annak érdekében, hogy a strukturált hivatkozásban abszolút referenciát kapjunk, meg kell dupláznunk az oszlop nevét.
  • Nem másolhatjuk a strukturált hivatkozás képletét, hanem húznunk kell a képletet.
  • A strukturált hivatkozásokban nem láthatjuk pontosan, melyik cellára hivatkozunk.
  • Ha nem érdeklik a strukturált hivatkozások, akkor kikapcsolhatja őket.