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 sablont1. 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.