Ugrás a tartalomra

Excel abszolút referencia (hogyan kell elkészíteni és használni)

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.

Ingyenesen letölthető a mintafájl doc minta


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
doc abszolút hivatkozás 3 1   doc abszolút hivatkozás 4 1

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

abszolút referencia f4 kapcsoló 1

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

abszolút referencia f4 kapcsoló 2


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.

Megjegyzések:

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.

doc abszolút hivatkozás 15 1

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.

doc abszolút hivatkozás 16 1

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.

doc abszolút hivatkozás 17 1

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.

doc abszolút hivatkozás 18 1

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

doc abszolút hivatkozás 19 1

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

doc abszolút hivatkozás 20 1


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.

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

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

kte lap 201905


Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations