Zmiešaná referencia v Exceli (obsah)
- Úvod do kombinovanej referencie v Exceli
- Príklady zmiešanej referencie v Exceli
Úvod do kombinovanej referencie v Exceli
Odkaz je jednou z dôležitých funkcií programu Excel, ktorá pomáha pri práci s vynikajúcimi operáciami pomocou vzorcov. Zmiešaný odkaz, názov sám hovorí, že ide o kombináciu odkazov. Kombinácia obsahuje relatívnu referenciu a absolútnu referenciu.
Človek by mal byť oboznámený s relatívnym a absolútnym odkazom, aby mohol porozumieť zmiešanému odkazu a pracovať s ním. Najprv uvidíme, čo je relatívna referencia a čo je absolútna referencia, ako ju môžeme použiť vo vzorcoch a všetci uvidíme s príbuzenskými príkladmi.
Príklady zmiešanej referencie v Exceli
Tu uvádzame príklady relatívnej a absolútnej referenčnej práce so zmiešanou referenciou v Exceli.
Túto šablónu zmiešaného referenčného Excelu si môžete stiahnuť tu - šablónu zmiešaného referenčného ExceluPríklad č. 1 - relatívna referencia
Štandardne bude odkaz v Exceli považovať za relatívny odkaz. Referencia nie je nič, ak do bunky B1 vložíme „= A1“, potom odkazujeme na A1 v B1. Pozrime sa na príklad na pochopenie relatívnej referencie.
Zoberme si nasledujúcu snímku obrazovky príjmov a výdavkov od januára do mája.
Teraz chceme vypočítať zostávajúcu hotovosť po výdavkoch vyplatených zo zárobkov. Môžeme to dosiahnuť jednoduchým vzorcom odčítania výdavkov z príjmu. Stačí zadať vzorec vedľa výdavkov, aby ste každý mesiac našli zvyšnú hotovosť.
Všimnite si nižšie uvedený screenshot, ktorý som vložil vzorec vedľa výdavkov, ktoré môžete nájsť na paneli vzorcov.
Ak pozorujeme, vzorec v D2 je B2-C2, znamená to, že D2 znamená kombináciu B2 a C2. Keď pretiahneme vzorec k spodným riadkom, bude to podobné ako D2, to znamená, že pre D2 sa vezme odkaz od B2 a C2 podobne pre D3, bude to sa vziať od odkazu B3 a C3, pre D4 sa vezme odkaz od B4 a C4 a tak ďalej.
Môžeme sledovať súvisiace snímky obrazovky pre ďalšie mesiace. Nižšie uvedený obrázok súvisí s D3, ktorý odkazuje na B3 a C3.
Snímka obrazovky súvisiaca s D4, ktorá odkazuje na B4 a C4.
To znamená, že vzorec sa bude vždy považovať za rovnaký odkaz ako prvý vzorec, pretože sa naň vzťahuje. Dúfam, že ste dostali predstavu, čo je relatívna referencia, kde môžeme použiť.
Príklad č. 2 - Absolútna referencia
Zoberme si ten istý príklad aj na porozumenie absolútnej referencie. Uvažujme z vyššie uvedeného snímku obrazovky, že máme príjmy a výdavky. Predpokladajme, že máme fixné náklady na nájomné, okrem toho sú k dispozícii aj výdavky uvedené v tabuľke.
Vo vyššie uvedenom exceli sme spomenuli nájom iba v jednej bunke, ktorá je F2. Teraz musíme zmeniť a doplniť vzorec v stĺpci D, aby sa odpočítali nájomné od „hotovosti zostávajúcej po výdavkoch“.
Zaznamenajte snímku obrazovky, ktorú sme odčítali od výsledku B2-C2. Presuňte vzorec aj do ďalších buniek.
Presunul som vzorec, ale výsledky sa stále nemenia, pretože v bunke D2 sme odpočítali F2, ako bolo vysvetlené skôr v predvolenom nastavení, Excel bude mať relatívnu referenciu, takže vzal F3 pre vzorec v D3 podobne ako F4 v D4. Nemáme však žiadne hodnoty v bunkách F3, F4, F5 a F6, čo je dôvod, prečo tieto bunky považuje za nulové a neovplyvnilo výsledky.
Ako to môžeme vyriešiť bez pretiahnutia nájomného do buniek F3, F4, F5 a F6. Tu Absolútna referencia pomáha dosiahnuť správne výsledky bez pretiahnutia nájomného.
Je veľmi jednoduché vytvoriť absolútny odkaz, stačí kliknúť na vzorec a vybrať bunku F2 a kliknúť na D2, potom sa vytvorí absolútny odkaz.
Ak sa $ použije pre stĺpcový index F aj pre riadkový index 2, potom absolútna referencia je na úrovni bunky. Teraz pretiahnite vzorec v D3, D4, D5 a D6 a pozrite sa, ako sa výsledky menia.
Pozorujte vzorec v bunke D3, je to $ F $ 2, aj keď sme vzorec pretiahli. Pretože je blokovaný na úrovni bunky, bude sa odvolávať iba na F2. Toto je použitie absolútneho odkazu. Týmto sa získa odkaz z uzamknutého indexu.
Príklad č. 3 - Zmiešaná referencia
Ako sme už uviedli v zmiešanom odkaze, budeme používať relatívny aj absolútny odkaz. Pozrime sa, ako môžeme použiť oba tieto príklady.
Zvážte údaje v nižšie uvedenom formáte, ako je to zobrazené na snímke obrazovky.
Z vyššie uvedenej tabuľky musíme nájsť percento zárobkov za každý mesiac oproti „súčtu“ 5 mesačných zárobkov, percentá nákladov a hotovosť po výdavkoch.
Ak to stále nie je jasné, musíme zistiť 5 000 percent z celkových 5 mesačných zárobkov 32100.
Teraz v dolnej prázdnej tabuľke musíme nájsť percento pre každú bunku.
Vzorec na zistenie percentuálneho podielu je rozdelený na Janove zárobky s celkovým počtom 32100.
Ak zistíte, že som použil absolútny odkaz, pretože všetky mesačné zárobky by sa mali deliť iba súčtom. Teraz môžeme vzorec pretiahnuť až do mája.
Teraz potiahnite vzorce aj dole.
Ak budeme stále pozorovať, vzorec pre výdavky berie celkom zárobky, ale v skutočnosti by mal trvať celkom 15200. Za týmto účelom musíme vytvoriť referenciu iba na úrovni stĺpca, ktorá sa nevyžaduje na úrovni riadkov.
Všimnite si, že vzorec $ je k dispozícii pri N, nie pri indexe 11 riadkov. Vykonajte zmeny vždy podľa prvého vzorca a potom ich presuňte do iných buniek.
Všade, kde sa vzorec správne vyberá. Ak pozorujeme, vykonali sme absolútny odkaz v operáciách riadkov a relatívny odkaz v operáciách stĺpcov. Dúfam, že teraz je jasné, ako vykonať zmiešané referencie a v ktorých oblastiach to bude užitočné.
Čo je potrebné pamätať
- Zmiešaná referencia je kombináciou relatívnej referencie a absolútnej referencie.
- Relatívna referencia je predvolená referencia v Exceli pri odkazovaní na všetky bunky v excelovských operáciách. V tejto ďalšej bunke bude mať podobný odkaz ako prvý vzorec.
- Absolútna referencia je ďalším typom, pri ktorom bude mať referenciu ako fixnú, kamkoľvek vložíte vzorec.
- Ak chcete vytvoriť absolútny odkaz, vyberte odkaz na bunku a kliknite na F4, potom sa do indexu riadka a stĺpca pridá symbol $.
- Zmiešaný odkaz je kombináciou oboch, preto môžeme použiť absolútny odkaz iba na stĺpcový index alebo iba na riadkový index.
- $ A1 - Odkaz na prostriedky vytvorený pre index stĺpcov.
- $ 1 - Znamená prostriedky vytvorené pre index riadkov.
- $ A $ 1 - Znamená referenčný prostriedok vytvorený pre indexy riadkov aj stĺpcov.
Odporúčané články
Toto je sprievodca zmiešanou referenciou v Exceli. Tu diskutujeme o tom, ako Relatívne a Absolútne referencie pracujú so Zmiešanou referenciou v Exceli spolu s príkladmi a šablónou programu Excel na stiahnutie. Ďalšie informácie nájdete aj v nasledujúcich článkoch -
- DAY Formula v Exceli
- Vzorec pre veľkosť vzorky
- HLOOKUP Formula v Exceli
- VALUE vzorec v Exceli