Vegyes hivatkozás Excelben (tartalomjegyzék)
- A vegyes hivatkozás bevezetése az Excelben
- Példák a vegyes hivatkozásra az Excelben
A vegyes hivatkozás bevezetése az Excelben
A vegyes hivatkozás az Excelben az oszlopok vagy sorok egyidejű rögzítésére szolgál. Csak az oszlopra vagy a sorokra utal a hivatkozott cella. Például, ha vegyes hivatkozást akarunk alkalmazni egy A1-es cellában, akkor az A1-es cella oszlopát úgy tudjuk rögzíteni, hogy dollárt(“$”) teszünk az $A1 oszlopnév elé, vagy az A1-es cella sorának rögzítéséhez dollárt teszünk az A$1 cellaszám elé. Ezzel bármelyik hivatkozás rögzíthető. Röviden, amikor $-t teszünk bármi elé, ha rögzítjük azt.
Kezdje el ingyenes Excel tanfolyamát
Excel függvények, képletek, diagramok, formázás Excel műszerfal létrehozása & mások
Példák a vegyes hivatkozásra az Excelben
Itt vannak a relatív és abszolút hivatkozás munkájának példái az alábbiakban megadott vegyes hivatkozással az Excelben.
Példa #1 – Relatív referencia
Az Excelben a hivatkozás alapértelmezés szerint relatív hivatkozásnak minősül. Hivatkozás semmi, ha a B1 cellába “=A1” beírjuk, akkor a B1-ben lévő A1-re hivatkozunk. Lássunk egy példát a relatív hivatkozás megértéséhez.
Megnézzük az alábbi képernyőképet a januártól májusig tartó időszak bevételeiről és kiadásairól.
Most szeretnénk kiszámolni a bevételből a kifizetett kiadások után megmaradt készpénzt. Ezt a kiadásoknak a bevételből való kivonásának egyszerű képletével kaphatjuk meg. Csak írja be a képletet a kiadások mellé, hogy minden hónapban megtalálja a fennmaradó készpénzt.
Nézze meg az alábbi képernyőképet, ahol a kiadások mellé írtam be a képletet, amelyet a képletsoron talál.
4.9 (6,883 ratings)
View Course
Excel Advanced Training (14 Courses, 23+ Projektek)
Ha megfigyeljük, hogy a D2-ben a képlet B2-C2, ez azt jelenti, hogy a D2 a B2 és C2 kombinációjára utal. Ha a képletet az alsó sorokba húzzuk, akkor hasonló lesz, mint a D2, ami azt jelenti, hogy a D2 esetében a B2 és a C2 hivatkozik, hasonlóan a D3 esetében a B3 és a C3, a D4 esetében a B4 és a C4, és így tovább.
A többi hónap esetében is megfigyelhetjük a kapcsolódó képernyőképeket. Az alábbi képernyőkép a D3-mal kapcsolatos, amely a B3-ra és a C3-ra hivatkozik.
D4-gyel kapcsolatos képernyőkép, amely a B4-re és a C4-re hivatkozik.
Ez azt jelenti, hogy a képlet mindig ugyanazt a hivatkozást fogja figyelembe venni, mint az első képlet, mivel arra vonatkozik. Remélem, kaptál egy képet arról, hogy mi az a relatív referencia, ahol használhatjuk.
Példa #2 – Abszolút referencia
Nézzük meg ugyanezt a példát, hogy megértsük az abszolút referenciát is. Tekintsük a fenti képernyőképből, hogy van bevételünk és kiadásunk tegyük fel, hogy van fix bérleti díj kiadásunk, ezen kívül a táblázatban rendelkezésre álló kiadások.
A fenti excelben a bérleti díjat csak egy cellában említettük, ami az F2. Most a D oszlopban lévő képletet úgy kell módosítanunk, hogy a bérleti díjat kivonjuk a “kiadások után fennmaradó készpénzből”.
Nézzük meg a képernyőképen, hogy a B2-C2 eredményéből kivontuk az F2-t is. Húzzuk át a képletet a többi cellába is.
Húztam a képletet, de még mindig nem változik az eredmény, mert a D2-es cellában kivontuk F2-t, ahogy korábban elmagyaráztuk, alapértelmezés szerint az Excel relatív hivatkozást vesz, így a D3-ban lévő képlethez F3-at vett, hasonlóan F4-et a D4-ben. De az F3, F4, F5 és F6 cellákban nincsenek értékeink, ezért ezeket a cellákat nullának tekinti, és nem befolyásolta az eredményeket.
Hogyan tudjuk ezt megoldani anélkül, hogy az F3, F4, F5 és F6 cellákban bérleti díjat húznánk. Itt az abszolút hivatkozás segít a helyes eredmények elérésében a bérleti díj húzása nélkül.
Az abszolút hivatkozás létrehozása nagyon egyszerű, csak a képletre kell kattintanunk, és ki kell jelölnünk az F2-es cellát, majd a D2-re kattintva létrehozzuk az abszolút hivatkozást.
Ha a $-t mind az F oszlopindexre, mind a 2. sorindexre alkalmazzuk, akkor az abszolút hivatkozás a cellaszinten van. Most húzza a képletet a D3, D4, D5 és D6 cellában, és nézze meg, hogyan változik az eredmény.
Nézze meg, hogy a D3 cellában lévő képlet csak $F$2 még azután is, hogy a képletet áthúztuk. Mivel cellaszinten le van zárva, csak az F2-ből fog hivatkozni. Ez az abszolút hivatkozás használata. Ez a zárolt indexből veszi a hivatkozást.
3. példa – Vegyes hivatkozás
Mint korábban említettük, a vegyes hivatkozásnál relatív és abszolút hivatkozást is használunk. Nézzük meg, hogyan használhatjuk mindkettőt ugyanazon a példán keresztül.
Megnézzük az adatokat az alábbi formátumban, ahogy a képernyőképen látható.
A fenti táblázatból meg kell találnunk az egyes hónapok keresetének százalékos arányát az 5 havi kereset “Összesen” összegéhez képest, hasonlóképpen a kiadások és a kiadások után megmaradó készpénz százalékos arányát.
Ha még mindig nem világos, akkor meg kell találnunk az 5000 százalékot az 5 havi kereset 32100 százalékából.
Az alábbi üres táblázatban most meg kell találnunk a százalékot az egyes cellákhoz.
A százalék megtalálásának képlete: osztott Jan kereset összesen 32100.
Ha megfigyeled, abszolút hivatkozást használtam, mert minden havi keresetnek csak a teljes összeggel kell osztania. Most húzhatjuk a képletet május hónapig.
Most húzza a képleteket az aljára is.
Ha megfigyeljük, hogy a költségekre vonatkozó képlet még mindig csak az Earnings összegét veszi, de valójában 15200 költséget kellene vennie összesen. Ehhez csak oszlopszinten kell létrehoznunk a hivatkozást, ami sorszinten nem szükséges.
Figyeljük meg, hogy a képlet $ szimbóluma az N-nél van, nem a 11-es sorindexnél. Végezze el a változtatásokat mindig az első képleten, majd húzza át a többi cellára.
Mindenhol helyesen szedi a képletet. Ha megfigyeljük, a soros műveleteknél az abszolút hivatkozást, az oszlopos műveleteknél pedig a relatív hivatkozást hajtottuk végre. Remélem, most már világos, hogyan kell vegyes hivatkozást végezni, és mely területeken lesz hasznos.
Megjegyzendő
- A vegyes hivatkozás a relatív hivatkozás és az abszolút hivatkozás kombinációja.
- A relatív hivatkozás az excelben alapértelmezett hivatkozás, miközben bármely cellára hivatkozunk az excel műveletekben. Ebben a másik cella hasonló hivatkozást vesz fel, mint az első képlet.
- Az abszolút hivatkozás egy másik típus, ahol a hivatkozást fixként veszi fel, ahová a képletet beilleszti.
- Az abszolút hivatkozás létrehozásához jelölje ki a cellahivatkozást, és kattintson az F4-re, majd a $ szimbólum hozzáadódik a sorindexhez és az oszlopindexhez.
- A vegyes hivatkozás a kettő kombinációja, ezért ebben az esetben alkalmazhatunk abszolút hivatkozást csak az oszlopindexhez vagy csak a sorindexhez.
- $A1 – Az oszlopindexhez létrehozott hivatkozást jelenti.
- A$1 – A sorindexhez létrehozott hivatkozást jelenti.
- $A$1 – A sor- és oszlopindexhez létrehozott hivatkozást jelenti.
Javasolt cikkek
.