Hogyan lehet strukturált hivatkozásokat létrehozni az Excelben?
A strukturált hivatkozások az excel táblákkal kezdődnek. Amint a táblázatok elkészülnek az excelben, automatikusan strukturált referenciákat hoz létre az Ön számára.
Most vessen egy pillantást az alábbi képre.
- 1. lépés: Hivatkozást adtam a B3 cellára. Ahelyett, hogy a linket B2-ként jelenítené meg, az 1. táblázatként (@Sales) jelenik meg. Itt a Table1 a tábla neve, a @Sales pedig az az oszlop, amelyre hivatkozunk. Az oszlop összes cellájára egy táblázat neve hivatkozik, majd az oszlop címsora követi.
- 2. lépés: Most meg fogja változtatni a tábla nevet Data_Table és módosítsa az oszlop fejlécében az összeg .
- 3. lépés: A táblázat nevének megváltoztatásához helyezzen egy kurzort a táblázat belsejébe, majd válassza a Tervezés> Tábla neve menüpontot.
- 4. lépés: Adja meg a táblázat nevét Data_Table néven.
- 5. lépés: Most a változás adjon hivatkozást a B3 cellára.
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
1. példa
Strukturált hivatkozások segítségével 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 képletet 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áblák 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épleten.
2. példa
Most nézzen még egy példát. Terméknév-, mennyiség- és árinformációm van. 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ázatra.
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 az alábbiakban sorolunk fel.
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 excelben alkalmaztam a normál tartományra.
Ha összegezni akarom az Ár és az Értékértéket, 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ék 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 összes árat.
Istenem!!! Nem mutatja az Ár oszlop teljes összegét; inkább csak a Mennyiség oszlopot mutatja. 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 tudjuk a korlátozását. A másolás-beillesztés feladatot strukturált hivatkozásokkal már nem tudjuk elvégezni. 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 más képletekre húzzuk a képletet.
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 az adatokat az SUMIF függvény használatával szeretném konszolidálni 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.
Jan hónapra alkalmaztam a képletet. Mivel strukturált hivatkozásról van szó, nem tudjuk másolni és beilleszteni a képletet a maradék két oszlopba. Ez nem változtatja meg a február és március hivatkozást, 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ény első argumentuma a Criteria Range Sales_Table (Product), mivel a képletet húztuk . Értékesítési táblára változott (jan.).
Tehát hogyan kezeljük? Meg kell adnunk az első argumentumot, vagyis a Product oszlopot abszolútnak, a többi oszlopot pedig relatív referenciának. A normál referenciával ellentétben 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, amint az az alábbi képen látható.
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 referenciává tételéhez 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, kikapcsolhatja az alábbi lépések végrehajtásával.
- 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 az abszolút referenciát strukturált referenciává tegyük, meg kell dupláznunk az oszlop nevét.
- Nem másolhatjuk a strukturált hivatkozási képletet; ehelyett meg kell húznunk 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.
