Amikor Excelben egy képlet cellájára hivatkozik, az alapértelmezett hivatkozástípus egy relatív hivatkozás. Ezek a hivatkozások megváltoznak, amikor a képletet más cellákba másolják a relatív oszlop és sor alapján. Ha egy referencia állandót szeretne tartani, függetlenül attól, hogy a képlet hova lett másolva, akkor az abszolút hivatkozást kell használnia.

• Mi az abszolút referencia
• Hogyan készítsünk abszolút hivatkozásokat
• Használjon abszolút hivatkozást a példákkal
• Számítsa ki a teljes százalékarányt
• Keressen egy értéket, és térjen vissza a megfelelő egyezési értékhez
• 2 kattintás a kötegelt cellahivatkozások abszolútá tételéhez a Kutools segítségével
• Relatív hivatkozás és vegyes hivatkozás
• Emlékezetes dolgok

Ingyenesen letölthető a mintafájl

Videó: Abszolút referencia

Mi az abszolút referencia

Az abszolút hivatkozás egyfajta cellahivatkozás az Excelben.

Egy relatív hivatkozáshoz képest, amely a képlet más cellákba másolásakor a relatív helyzete alapján változik, az abszolút hivatkozás állandó marad, függetlenül attól, hogy a képletet hova másolják vagy helyezik át.

Abszolút hivatkozás jön létre egy dollárjel ($) hozzáadásával az oszlop- és sorhivatkozások elé a képletben. Például, ha abszolút hivatkozást szeretne létrehozni az A1 cellához, azt $A$1-ként kell ábrázolnia.

Az abszolút hivatkozások akkor hasznosak, ha egy rögzített cellára vagy tartományra szeretne hivatkozni egy képletben, amelyet több cellába másol, de nem szeretné, hogy a hivatkozás megváltozzon.

Például az A4:C7 tartomány a termékek árait tartalmazza, és az egyes termékek fizetendő adóját a B2 cellában lévő adókulcs alapján szeretné megkapni.

Ha relatív hivatkozást használ a képletben, például "=B5*B2", akkor néhány rossz eredmény jelenik meg, amikor az automatikus kitöltési fogantyút lefelé húzza a képlet alkalmazásához. Mivel a B2 cellára való hivatkozás megváltozik a képlet celláihoz képest. Most a C6 cellában lévő képlet "=B6*B3", a C7 cellában pedig "=B7*B4"

De ha abszolút hivatkozást használ a B2 cellára a „=B5*$B$2” képlettel, akkor az adókulcs minden cellában ugyanaz marad, amikor a képletet lehúzza az automatikus kitöltési fogantyúval, az eredmények helyesek.

Relatív hivatkozás használatával		Abszolút referencia felhasználásával

Hogyan készítsünk abszolút hivatkozásokat

Abszolút hivatkozás létrehozásához az Excelben dollárjeleket ($) kell hozzáadnia az oszlop- és sorhivatkozások elé a képletben. Az abszolút referencia létrehozásának két módja van:

Adjon hozzá kézzel dollárjeleket a cellahivatkozáshoz

Manuálisan hozzáadhat dollárjeleket ($) az abszolútá tenni kívánt oszlop- és sorhivatkozások elé, miközben a képletet beírja egy cellába.

Ha például az A1 és B1 cellában lévő számokat össze szeretné adni, és mindkettőt abszolút értékre szeretné tenni, egyszerűen írja be a képletet a következőképpen: "=$A$1+$B$1". Ez biztosítja, hogy a cellahivatkozások állandóak maradjanak, amikor a képletet másolják vagy más cellákba helyezik át.

Vagy ha egy cellában egy létező képletben a hivatkozásokat abszolútra szeretné módosítani, kijelölheti a cellát, majd a képletsorra lépve hozzáadhatja a dollárjeleket ($).

Az F4 parancsikon használata a relatív hivatkozás abszolúttá konvertálásához

1. Kattintson duplán a képletet tartalmazó cellára, hogy belépjen a szerkesztési módba;
2. Vigye a kurzort arra a cellahivatkozásra, amelyet abszolútá szeretne tenni;
3. nyomja meg F4 gomb a billentyűzeten a referenciatípusok váltásához, amíg a dollárjelek az oszlop- és sorhivatkozások elé kerülnek;
4. nyomja meg belép gombot a szerkesztési módból való kilépéshez és a módosítások alkalmazásához.

Az F4 billentyűvel lehet váltani a relatív referencia, az abszolút referencia és a vegyes referencia között.

A1 → $A$1 → A$1 → $A1 → A1

Ha egy képletben az összes hivatkozást abszolút értékké szeretné tenni, válassza ki a teljes képletet a képletsorban, és nyomja meg a gombot F4 gomb a hivatkozási típusok váltásához, amíg a dollárjelek az oszlop- és sorhivatkozások elé kerülnek.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Használjon abszolút hivatkozást a példákkal

Ez a rész 2 példát mutat be annak bemutatására, hogy mikor és hogyan kell abszolút hivatkozásokat használni egy Excel-képletben.

1. példa Számítsa ki a teljes százalékarányt

Tegyük fel, hogy van egy adattartománya (A3:B7), amely tartalmazza az egyes gyümölcsök eladásait, és a B8 cella tartalmazza ezeknek a gyümölcsöknek a teljes értékesítési mennyiségét, most ki szeretné számítani az egyes gyümölcsértékesítések százalékos arányát az összes gyümölcsből.

Az általános képlet a teljes százalékarány kiszámításához:

Percentage = Sale/Amount

Használja a relatív hivatkozást a képletben, hogy megkapja az első gyümölcs százalékát, így:

=B4/B8

Amikor az automatikus kitöltés fogantyúját lefelé húzza a többi gyümölcs százalékos arányának kiszámításához, #DIV/0! a hibákat visszaküldik.

Mivel amikor az automatikus kitöltési fogantyút húzva másolja a képletet az alábbi cellákba, a B8 relatív hivatkozás automatikusan hozzáigazodik más cellahivatkozásokhoz (B9, B10, B11) azok relatív pozíciói alapján. A B9, B10 és B11 cella pedig üres (nullák), ha az osztó nulla, a képlet hibához tér vissza.

A hibák kijavításához ebben az esetben a B8 cellahivatkozást abszolút ($B$8) kell megadnia a képletben, nehogy megváltozzon, amikor a képletet bárhová áthelyezi vagy másolja. A képlet most frissül a következőre:

=B4/$B$8

Ezután húzza le az automatikus kitöltési fogantyút a többi gyümölcs százalékos arányának kiszámításához.

2. példa Keressen egy értéket, és térjen vissza a megfelelő egyezési értékhez

Feltételezve, hogy meg akarja keresni a névlistát a D4:D5-ben, és vissza szeretné adni a megfelelő fizetésüket az alkalmazottak nevei és a megfelelő éves fizetés alapján (A4:B8).

A keresendő általános képlet a következő:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Ha a képletben a relatív hivatkozást használja egy érték megkeresésére és a megfelelő egyezési érték visszaadására, így:

=VLOOKUP(D4,A4:B8,2,FALSE)

Ezután húzza le az automatikus kitöltési fogantyút az alábbi érték megkereséséhez, és hibaüzenet jelenik meg.

Ha lehúzza a kitöltő fogantyút, hogy a képletet az alábbi cellába másolja, a képletben lévő hivatkozások automatikusan egy sorral lefelé igazodnak. Ennek eredményeként az A4:B8 táblázattartományra való hivatkozás A5:B9 lesz. Mivel a "Lisa: nem található az A5:B9 tartományban, a képlet hibát ad vissza.

A hibák elkerülése érdekében használja a $A$4:$B$8 abszolút hivatkozást az A4:B8 relatív hivatkozás helyett a képletben:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Ezután húzza le az automatikus kitöltési fogantyút, hogy megkapja Lisa fizetését.

2 kattintás a kötegelt cellahivatkozások abszolútá tételéhez a Kutools segítségével

Akár manuálisan gépel, akár F4 billentyűparancsot használ, az Excelben egyszerre csak egy képletet módosíthat. Ha több száz képletben szeretne cellahivatkozásokat abszolút módon megadni az Excelben, a Konvertálás hivatkozások eszköze Kutools az Excel számára 2 kattintással segíthet a munka kezelésében.

Jelölje ki azokat a képletcellákat, amelyekben a cellahivatkozásokat abszolúttá szeretné tenni, majd kattintson Kutools > Tovább (fx) > Konvertálás hivatkozások. Ezután válassza a Az abszolútumig lehetőség és kattintson Ok or alkalmaz. Most a kiválasztott képletek összes cellahivatkozása abszolútra lett konvertálva.

Relatív hivatkozás és vegyes hivatkozás

Az abszolút hivatkozáson kívül további két hivatkozási típus létezik: a relatív hivatkozás és a vegyes hivatkozás.

Relatív hivatkozás az alapértelmezett hivatkozási típus az Excelben, amely dollárjelek ($) nélkül szerepel a sor- és oszlophivatkozások előtt. Ha pedig egy relatív hivatkozásokkal rendelkező képletet másol vagy más cellákba helyez át, a hivatkozások automatikusan megváltoznak a relatív helyzetük alapján.

Ha például beír egy képletet egy cellába, például "=A1+1", majd az automatikus kitöltési fogantyút lefelé húzva tölti ki a képletet a következő cellába, a képlet automatikusan "=A2+1"-re változik.

Vegyes referencia abszolút hivatkozásból és relatív hivatkozásból is áll. Más szavakkal, a vegyes hivatkozás a dollárjelet ($) használja a sor vagy az oszlop rögzítésére a képlet másolásakor vagy kitöltésekor.

Vegyünk példának egy szorzótáblát, a sorok és oszlopok 1-től 9-ig sorolják fel a számokat, amelyeket megszorozunk egymással.

Kezdésként használhatja a "=B3*C2" képletet a C3 cellában, és megszorozza a B1 cellában lévő 3-et az első oszlopban lévő számmal (1). Ha azonban az automatikus kitöltés fogantyúját jobbra húzza a többi cella kitöltéséhez, észre fogja venni, hogy az első kivételével az összes eredmény hibás.

Ennek az az oka, hogy a képlet jobbra másolásakor a sor pozíciója nem változik, de az oszlop pozíciója B3-ról C3-ra, D3-ra stb. változik. Ennek eredményeként a képletek a jobb oldali cellákban (D3, E3, stb.) módosítsa "=C3*D2", "=D3*E2" és így tovább, ha azt szeretné, hogy ezek a következők legyenek: "=B3*D2", "=B3*E2" és így tovább.

Ebben az esetben egy dollárjelet ($) kell hozzáadnia a „B3” oszlophivatkozás zárolásához. Használja az alábbi képletet:

=$B3*C2

Most, ha a képletet jobbra húzza, az eredmények helyesek.

Ezután meg kell szoroznia a C1 cellában lévő 2-et az alábbi sorokban lévő számokkal.

Amikor lemásolja a képletet, a C2 cella oszloppozíciója nem változik, de a sor pozíciója C2-ről C3-ra, C4-re stb. módosul. Ennek eredményeként az alábbi cellák képletei "=$B4C3"-ra változnak. "=$B5C4" stb., amelyek helytelen eredményeket adnak.

A probléma megoldásához módosítsa a „C2” értéket „C$2” értékre, hogy a sorhivatkozás ne változzon, amikor az automatikus kitöltési fogantyút lefelé húzza a képletek kitöltéséhez.

=$B3*C$2

Most az automatikus kitöltési fogantyút jobbra vagy lefelé húzhatja az összes eredmény megjelenítéséhez.

Emlékezetes dolgok

• A cellahivatkozások összefoglalása

  típus	Példa	Összegzésként
  Abszolút referencia	1 USD	Soha ne változtassa meg a képletet más cellákba másolásakor
  Relatív hivatkozás	A1	Mind a sor-, mind az oszlophivatkozás a relatív pozíció alapján változik, amikor a képletet más cellákba másolják
  Vegyes referencia	$A1/A$1	A sorhivatkozás megváltozik, ha a képletet más cellákba másolják, de az oszlophivatkozás rögzítve van/Az oszlophivatkozás megváltozik, ha a képletet más cellákba másolják, de a sorhivatkozás rögzített;

• Általában az abszolút hivatkozások soha nem változnak a képlet mozgatásakor. Az abszolút hivatkozások azonban automatikusan módosulnak, amikor egy sort vagy oszlopot adunk hozzá, vagy eltávolítunk a munkalap tetejéről vagy balról. Például egy „=$A$1+1” képletben, amikor beszúr egy sort a lap tetejére, a képlet automatikusan „=$A$2+1”-re változik.

  

• A F4 gomb válthat relatív referencia, abszolút referencia és vegyes referencia között.