4.3 lecke · 3/3 · kb. 6 perc
Relatív, abszolút és vegyes hivatkozás
Egy képletet ritkán írsz meg csak egyszer: általában lemásolod sok sorba vagy oszlopba. Ebben a leckében megérted, mi történik a hivatkozásokkal másoláskor, és mikor kell a dollárjellel rögzíteni őket.
Relatív hivatkozás: a képlet „magával viszi”
Számold ki mindenki egyenlegét: bevétel mínusz kiadás. A D2-be beírod a képletet, majd a kitöltőnégyzetre duplán kattintva lemásolod.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | név | bevétel | kiadás | egyenleg | D oszlop képlete |
| 2 | Hegyi Félix | 604 920 | 624 270 | -19 350 | =B2-C2 |
| 3 | Szőke Petra | 302 682 | 399 084 | -96 402 | =B3-C3 |
| 4 | Radnai Szilárd | 412 753 | 499 744 | -86 991 | =B4-C4 |
Az eredeti képlet a D2-ben van; a D3 és D4 képletét a másolás készítette.
Az Excel nem a cellaazonosítókat másolja, hanem azok helyzetét a képlethez képest. A D2-ből nézve a B2 „ugyanabban a sorban, kettővel balra”, a C2 „ugyanabban a sorban, eggyel balra” áll. A D3-ba ugyanez a szabály kerül, ezért ott a képlet =B3-C3 lesz. Ez a relatív hivatkozás, és az esetek többségében pontosan ezt szeretnéd.
Abszolút hivatkozás: rögzítés dollárjellel
Most számold ki néhány termék ÁFA-ját. Az ÁFA kulcs (27%) egyetlen cellában, az F2-ben áll. Az első képlet: =B2*F2. Másold le, és nézd meg, mi történik:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | termék | nettó ár | ÁFA | C oszlop képlete | ÁFA kulcs | |
| 2 | kabát | 18 500 | 4 995 | =B2*F2 | 27% | |
| 3 | pulóver | 6 300 | 0 | =B3*F3 | ||
| 4 | kardigán | 5 400 | 0 | =B4*F4 |
Hibás másolás: az F2 hivatkozás is lecsúszott F3-ra és F4-re, amelyek üresek, így az eredmény 0.
Az F2-t rögzíteni kell, hogy minden másolatban F2 maradjon. Ezt a dollárjel jelzi: $F$2. A dollárjel azt mondja az Excelnek: „ezt ne igazítsd a képlet helyéhez”.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | termék | nettó ár | ÁFA | bruttó ár | ÁFA kulcs | |
| 2 | kabát | 18 500 | 4 995 | 23 495 | 27% | |
| 3 | pulóver | 6 300 | 1 701 | 8 001 | ||
| 4 | kardigán | 5 400 | 1 458 | 6 858 | ||
| 5 | sapka | 3 700 | 999 | 4 699 |
Helyes másolás. A C3 képlete =B3*$F$2, a C5-é =B5*$F$2. A D oszlop képlete: =B2+C2.
Tipp
Ha egy értéket (ÁFA kulcs, árfolyam, göngyöleg ára) külön cellába teszel, és abszolút hivatkozással használod, egyetlen cella átírásával az összes képlet eredménye frissül.
Vegyes hivatkozás: csak az oszlop vagy csak a sor
Néha a képletet jobbra és lefelé is másolod, és a hivatkozásnak csak az egyik fele maradhat a helyén. Ilyenkor a dollárjel csak az oszlopbetű vagy csak a sorszám elé kerül:
$B3: az oszlop rögzített (mindig B), a sor változhat,B$6: a sor rögzített (mindig 6), az oszlop változhat.
Oszlop rögzítése: mozgóbér
A munkatársak mozgóbére a havi bérük adott hónapra meghatározott százaléka. A bérek a B oszlopban, a havi százalékok a C és D oszlopban állnak. Az E2 képletét jobbra és lefelé is másolod:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | név | havi bér | jan. % | febr. % | jan. Ft | febr. Ft |
| 2 | Ács Gyöngyvér | 480 000 | 10,0% | 10,0% | 48 000 | 48 000 |
| 3 | Kun Zsuzsanna | 460 000 | 10,0% | 5,0% | 46 000 | 23 000 |
| 4 | Kis Franciska | 390 000 | 12,5% | 7,5% | 48 750 | 29 250 |
| 5 | Bán Magdaléna | 430 000 | 2,5% | 2,5% | 10 750 | 10 750 |
Másolatok: F2 =$B2*D2, E3 =$B3*C3, F4 =$B4*D4. A bér mindig a B oszlopból jön, de mindig a saját sorából.
Ha itt $B$2-t írnál, minden sorban Ács Gyöngyvér bérével számolnál. Ha sima B2-t, a jobbra másolt képlet a C oszlop százalékát venné bérnek.
Sor rögzítése: részesedés az összesből
Egy munkacsoport havi termelése áll a felső táblázatban, a 6. sorban az összesítéssel:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | név | jan. | febr. | márc. |
| 2 | Szalontai Pál | 260 | 160 | 180 |
| 3 | Jankovics Ede | 220 | 230 | 190 |
| 4 | Kútvölgyi Ida | 200 | 270 | 250 |
| 5 | Fehérvári Éva | 250 | 180 | 170 |
| 6 | összesen | 930 | 840 | 790 |
Az alsó táblázatban azt számolod ki, hány százalékkal járult hozzá egy-egy ember a havi termeléshez. Szalontai Pál januári részesedése: a januári darabszáma osztva a januári összessel. Az osztó mindig a 6. sorban van, de hónaponként más oszlopban, ezért csak a sort rögzíted:
| A | B | C | D | |
|---|---|---|---|---|
| 8 | név | jan. | febr. | márc. |
| 9 | Szalontai Pál | 28,0% | 19,0% | 22,8% |
| 10 | Jankovics Ede | 23,7% | 27,4% | 24,1% |
| 11 | Kútvölgyi Ida | 21,5% | 32,1% | 31,6% |
| 12 | Fehérvári Éva | 26,9% | 21,4% | 21,5% |
Másolatok: C9 =C2/C$6, D10 =D3/D$6, B12 =B5/B$6. Egy tizedesjegyre formázva; a januári oszlop látszólagos 100,1%-os összege csak a kerekített megjelenítés miatt van.
Dollárjelek az F4 billentyűvel
A dollárjeleket nem kell begépelned. Képletírás közben, közvetlenül egy hivatkozás beszúrása után (vagy ha a kurzor a hivatkozáson áll) nyomd meg az F4-et. Minden újabb lenyomás a következő típusra vált:
| F4 lenyomása | Hivatkozás | Típus | Másoláskor |
|---|---|---|---|
| (kiinduló) | C9 | relatív | oszlop és sor is változik |
| 1. | $C$9 | abszolút | semmi sem változik |
| 2. | C$9 | vegyes | csak az oszlop változik |
| 3. | $C9 | vegyes | csak a sor változik |
| 4. | C9 | relatív | újra az elejéről |
Tartományra is működik: B2:E10 egy F4-gyel $B$2:$E$10 lesz.
Mielőtt lezárod a képletet
Mindig kérdezd meg magadtól: másolni fogom ezt a képletet? Ha igen, nézd végig a hivatkozásokat: melyiknek kell vándorolnia, melyiknek helyben maradnia? Másolás után kattints duplán egy másolatra, és a színes keretekből ellenőrizd, jó cellákra mutat-e.
Hivatkozás másik munkalapra
Másik munkalap cellájára a lap nevével és egy felkiáltójellel hivatkozhatsz: =Január!B5. Ha a lap nevében szóköz van, a nevet aposztrófok közé kell tenni:
Ezt sem kell begépelned: képletírás közben kattints a másik lap fülére, majd a cellára, és az Excel beírja a teljes hivatkozást.
Jó tudni
A hivatkozások csak másoláskor igazodnak. Ha egy képletet kivágsz és beillesztesz, vagy a cellát a szegélyénél fogva áthúzod, a képlet változatlanul ugyanazokra a cellákra mutat tovább.
Összefoglalás
- Relatív (
A1): másoláskor a képlet helyéhez igazodik. - Abszolút (
$A$1): minden másolatban ugyanaz a cella marad. - Vegyes (
$A1,A$1): csak az oszlopot vagy csak a sort rögzíti. - A dollárjeleket az F4 billentyű váltogatja.
- Másik lapra:
=Január!B5, szóközös névnél='Január bevétel'!B5.
Gyakorlás
Töltsd le a gyakorló munkafüzetet, és oldd meg a megadott munkalapok feladatait. A sárga cellákba kerülnek a képletek. Ha elakadsz, nézd meg a megoldást.
Képletek és hivatkozások: gyakorló feladatok
- Gyümölcsök Ládás és hálós gyümölcsvásárlás összehasonlítása állandókra mutató abszolút hivatkozással.
- Pénztár Házipénztár göngyölített egyenlege: minden sor az előző egyenleghez adja hozzá az új tételt.
Ez volt a modul utolsó leckéje
Ellenőrizd a tudásodat a modul rövid kvízével: 6 kérdés, azonnali visszajelzéssel.