Metódy absolútneho adresovania v programe Microsoft Excel

Ako viete, v tabuľkách programu Excel existujú dva typy adresovania: relatívne a absolútne. V prvom prípade sa prepojenie mení v smere kopírovania o relatívnu hodnotu posunu, zatiaľ čo v druhom prípade je fixné a zostáva nezmenené počas kopírovania. V predvolenom nastavení však všetky adresy v programe Excel sú absolútne. Súčasne je dosť často potrebné používať absolútne (pevné) adresovanie. Pozrime sa, akým spôsobom je možné to urobiť.

Aplikácia absolútneho adresovania

Absolútne adresovanie môže byť potrebné napríklad pri kopírovaní vzorca, z ktorého jedna časť sa skladá z premennej zobrazenej v rade čísel a druhá má konštantnú hodnotu. To znamená, že toto číslo zohráva úlohu nezmeneného koeficientu, s ktorým musíte vykonať určitú operáciu (násobenie, rozdelenie atď.) Na celú sériu premenných čísel.

V programe Excel existujú dva spôsoby zadania pevného adresovania: vytvorením absolútnej referencie a použitím funkcie INDIRECT. Pozrime sa na každú z týchto metód podrobne.

Metóda 1: absolútny odkaz

Samozrejme, najslávnejším a často používaným spôsobom na vytvorenie absolútneho adresovania je použitie absolútnych referencií. Absolútne odkazy majú nielen funkčný, ale aj syntaktický rozdiel. Relatívna adresa má túto syntax:

=A1

Na pevnej adrese je znak dolára nastavený pred hodnotou súradníc:

=$A$1

Znak dolára je možné zadávať ručne. Ak to chcete urobiť, musíte umiestniť kurzor pred prvú hodnotu súradníc (horizontálne), ktoré sú v bunke alebo v lište vzorcov. Ďalej v anglickom rozložení klávesnice kliknite na veľké písmeno "4" ( stlačením tlačidla "Shift" ). Tam sa nachádza symbol dolára. Potom musíte urobiť rovnaký postup so zvislými súradnicami.

Existuje rýchlejší spôsob. Musíte umiestniť kurzor do bunky, kde sa nachádza adresa a kliknúť na funkčné tlačidlo F4. Následne sa nápis dolára okamžite objaví súčasne pred horizontálnymi a vertikálnymi súradnicami danej adresy.

Teraz sa pozrime na to, ako sa používa absolútne adresovanie v praxi pomocou absolútnych referencií.

Vezmite si tabuľku, ktorá vypočítava mzdy zamestnancov. Výpočet sa uskutočňuje vynásobením hodnoty ich osobného platu pevným pomerom, ktorý je rovnaký pre všetkých zamestnancov. Samotný koeficient sa nachádza v samostatnej hárku. Stojíme pred úlohou vypočítať mzdy všetkých zamestnancov čo najrýchlejšie.

1. Takže v prvej bunke stĺpca "Mzdy" zadáme vzorec na vynásobenie miery zodpovedajúceho zamestnanca koeficientom. V našom prípade má tento vzorec nasledujúcu formu:

   =C4*G3

2. Ak chcete vypočítať konečný výsledok, kliknite na kláves Enter na klávesnici. Výsledok sa zobrazí v bunke obsahujúcej vzorec.
3. Vypočítali sme plat prvému zamestnancovi. Teraz to musíme urobiť pre všetky ostatné riadky. Samozrejme, operácia môže byť zapísaná do každej bunky stĺpca "Mzdy" manuálne zadaním rovnakého vzorca s korekciou ofsetu, ale máme úlohu vykonať výpočty čo najrýchlejšie a manuálny vstup bude trvať veľa času. A prečo strácať úsilie pri ručnom vstupe, ak je možné jednoducho skopírovať do iných buniek?

   Ak chcete skopírovať vzorec, použijeme nástroj, ako je značka naplnenia. Staňte sa kurzorom v pravom dolnom rohu bunky, kde je obsiahnutá. V tomto prípade by mal byť samotný kurzor transformovaný do toho istého značkovača v tvare kríža. Uchopte ľavé tlačidlo myši a presuňte kurzor smerom nadol na koniec tabuľky.

4. Ako však vidíme, namiesto správneho výpočtu miezd pre ostatných zamestnancov sme dostali jednu nula.
5. Hľadáme, v čom je dôvod takéhoto výsledku. Ak to chcete urobiť, vyberte druhú bunku v stĺpci "Mzdy" . Výraz zodpovedajúci bunke sa zobrazuje vo formulovom riadku. Ako vidíte, prvý multiplikátor ( C5 ) zodpovedá sadzbe zamestnanca, ktorého plat očakávame. Posun súradníc v porovnaní s predchádzajúcou bunkou bol spôsobený vlastnosťou relativity. V tomto konkrétnom prípade je to však to, čo potrebujeme. Vďaka tomu bol prvým multiplikátorom miera konkrétneho zamestnanca, ktorý sme potrebovali. Avšak posun koordinácie sa vyskytol aj pri druhom faktore. A teraz jeho adresa neodkazuje na koeficient ( 1,28 ), ale na prázdnu bunku nižšie.

   To spôsobilo, že výpočet miezd pre následných zamestnancov zo zoznamu sa ukázal ako nesprávny.

6. Ak chcete napraviť situáciu, musíme zmeniť adresovanie druhého faktora z relatívneho na fixné. Ak to chcete urobiť, vráťte sa na prvú bunku v stĺpci "Mzdy" a vyberte ju. Ďalej sa presunieme na panel vzorcov, kde sa zobrazí požadovaný výraz. Pomocou kurzora zvoľte druhý faktor ( G3 ) a stlačte funkčné tlačidlo na klávesnici.
7. Ako môžete vidieť, okolo súradníc druhého multiplikátora sa objavil znak dolára a toto, ako si spomínam, je atribút absolútneho adresovania. Ak chcete zobraziť výsledok na obrazovke, stlačte kláves Enter .
8. Teraz, ako predtým, nazývame značku naplnenia umiestnením kurzora do pravého dolného rohu prvého prvku stĺpca "Mzdy" . Uchopte ľavé tlačidlo myši a potiahnite ho.
9. Ako vidíme, v tomto prípade bol výpočet vykonaný správne a výška miezd pre všetkých zamestnancov podniku bola vypočítaná správne.
10. Pozrime sa, ako bol vzorec skopírovaný. Ak to chcete urobiť, vyberte druhý prvok stĺpca "Mzdy" . Pozrite sa na výraz v riadku vzorcov. Ako je vidieť, súradnice prvého faktora ( C5 ), ktorý je stále relatívny, posunuli jeden zvislý vertikálny bod v porovnaní s predchádzajúcou bunkou. Ale druhý faktor ( $ G $ 3 ), ktorého adresovanie sme si stanovili, zostalo nezmenené.

V programe Excel sa používa takzvané zmiešané adresovanie. V tomto prípade je buď stĺpec alebo riadok pevne nastavený v adrese prvku. Toto sa dosiahne tak, že znak dolára je umiestnený tesne pred niektorou zo súradníc adresy. Tu je príklad typického zmiešaného odkazu:

=A$1

Táto adresa sa tiež považuje za zmiešanú:

=$A1

To znamená, že absolútne adresovanie v kombinovanom prepojení sa používa iba pre jednu z hodnôt súradníc týchto dvoch.

Pozrime sa, ako možno takýto zmiešaný odkaz v praxi použiť s príkladom rovnakej platovej tabuľky pre zamestnancov podniku.

1. Ako vidíte, skôr sme urobili tak, že všetky súradnice druhého multiplikátora majú absolútne adresovanie. Ale pozrime sa, je v tomto prípade potrebné, aby obe hodnoty boli stanovené? Ako vidíte, pri kopírovaní dochádza k vertikálnemu posunu a vodorovne súradnice zostávajú nezmenené. Preto je možné použiť absolútne adresovanie len na súradnice reťazca a ponechať súradnice stĺpcov tak, ako sú štandardne - relatívne.

   Vyberte prvý prvok stĺpca "Mzdy" a vykonajte vyššie uvedenú manipuláciu v riadku vzorca. Dosiahli sme nasledujúci vzorec:

   =C4*G$3

   Ako vidíte, fixné adresovanie v druhom násobiči je aplikované len s ohľadom na súradnice reťazca. Ak chcete zobraziť výsledok v bunke, kliknite na tlačidlo Enter .

2. Potom pomocou značky naplnenia skopírujte tento vzorec do oblasti buniek, ktoré sú umiestnené nižšie. Ako vidíte, mzdové účty pre všetkých zamestnancov sú správne vykonané.
3. Pozrite sa, ako je kopírovaný vzorec zobrazený v druhej bunke stĺpca, nad ktorým sme vykonali manipuláciu. Ako vidíte v riadku vzorca, po výbere tohto prvku listu napriek skutočnosti, že absolútne adresovanie druhého multiplikátora malo iba súradnice riadkov, nenastala posunutie súradníc stĺpcov. Je to spôsobené tým, že sme kopírovali nie horizontálne, ale vertikálne. Ak by sme v podobnom prípade kopírovali horizontálne, naopak by sme museli urobiť pevné adresovanie súradníc stĺpcov a pre reťazce by bol tento postup nepovinný.

Lekcia: Absolútne a relatívne odkazy v programe Excel

Metóda 2: Funkcia alfa

Druhým spôsobom organizovania absolútneho adresovania v tabuľke programu Excel je použitie operátora INDEX . Určená funkcia patrí do skupiny vstavaných operátorov "Referencie a polia" . Jej úlohou je vytvoriť odkaz na špecifikovanú bunku s výstupom výsledku na ten prvok listu, v ktorom je operátor umiestnený. V tomto prípade je spojenie pripojené k súradnicám ešte silnejšie ako pri použití znaku dolára. Preto je niekedy zvykom odkazovať na odkazy pomocou DVSSYL ako "superabsolút". Toto vyhlásenie má nasledujúcu syntax:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

Funkcia má dva argumenty, z ktorých prvý je povinný a druhý nie je.

Argument "Prepojenie na bunku" je odkaz na položku listu programu Excel v textovej podobe. To znamená, že je to obyčajný odkaz, ale uzavretý v úvodzovkách. To umožňuje poskytnúť vlastnosti absolútneho adresovania.

Argument "a1" je voliteľný a používa sa v zriedkavých prípadoch. Jeho použitie je nevyhnutné len vtedy, keď používateľ zvolí alternatívnu alternatívu adresovania, a nie bežné použitie súradníc typu "A1" (stĺpce majú abecedné označenie a linky majú digitálny). Alternatívou je použitie štýlu R1C1 , v ktorom sú stĺpce, rovnako ako riadky, označené číslami. Prepnite do tohto režimu prevádzky cez okno možností programu Excel. Potom pomocou operátora INDEX použite hodnotu "FALSE" ako argument "a1 " . Ak pracujete v normálnom režime zobrazenia odkazov, ako väčšina ostatných používateľov môžete určiť hodnotu "TRUE" ako argument "a1 " . Táto hodnota je implicitne implicitná, preto je oveľa jednoduchšie v tomto prípade neurčiť argument "a1" .

Poďme sa pozrieť na to, ako absolútne adresovanie organizované pomocou funkcie DVSSYL bude fungovať na príklade našej tabuľky platenia.

1. Vyberieme prvý prvok stĺpca "Mzdy" . Vložte znak "=" . Ako si pamätáte, prvý faktor vo vyššie uvedenom vzorci na výpočet platu musí byť reprezentovaný relatívnou adresou. Preto jednoducho kliknite na bunku obsahujúcu zodpovedajúcu hodnotu platu ( C4 ). Po zobrazení adresy v prvku výstupu výsledku kliknite na tlačidlo "násobenie" ( * ) na klávesnici. Potom musíme prejsť na použitie operátora INDIVIDUAL . Kliknite na ikonu "Vložiť funkciu" .
2. V otvorenom okne Sprievodcov funkcií prejdite do kategórie "Odkazy a matice" . Medzi prezentovaným zoznamom mien rozlišujeme názov "DVSSYL" . Potom kliknite na tlačidlo "OK" .
3. Aktivuje sa okno argumentu operátora DVSSYL . Skladá sa z dvoch polí, ktoré zodpovedajú argumentom tejto funkcie.

   Vložte kurzor do poľa "Odkaz na bunku" . Stačí kliknúť na ten prvok listu, v ktorom sa nachádza koeficient výpočtu platu ( G3 ). Adresa sa okamžite zobrazí v poli okna s argumentmi. Ak by sme sa zaoberali bežnou funkciou, predstavenie adresy sa mohlo považovať za úplné, ale používame funkciu ALGORITMU . Ako si pamätáme, adresy v nej musia mať formu textu. Preto súradnice, ktoré sa nachádzajú v poli okna, obalíme s úvodzovkami.

   Keďže pracujeme v režime štandardného zobrazenia súradníc, pole "A1" zostáva prázdne. Klikneme na tlačidlo "OK" .

4. Aplikácia vypočíta a zobrazí výsledok v prvku listu obsahujúcom vzorec.
5. Teraz skopírujeme tento vzorec do všetkých ostatných buniek v stĺpci "Mzdy" pomocou značky naplnenia, ako sme urobili predtým. Ako vidíte, všetky výsledky boli vypočítané správne.
6. Pozrime sa, ako je vzorec zobrazený v jednej z buniek, do ktorej bol skopírovaný. Vyberte druhý prvok stĺpca a pozrite sa na riadok vzorca. Ako vidíte, prvý násobiteľ, ktorý je relatívnym referenčným údajom, zmenil svoje súradnice. Súčasne sa argument druhého faktora, ktorý je reprezentovaný funkciou ALGORITMU , nezmenil. V tomto prípade bola použitá technika fixného adresovania.

Lekcia: Prevádzkovateľ spoločnosti INDUSTRY v programe Excel

Absolútne adresovanie v tabuľkách programu Excel môže byť poskytnuté dvomi spôsobmi: použitím funkcie INDEX a použitím absolútnych referencií. V tomto prípade funkcia poskytuje pevnejšiu väzbu na adresu. Čiastočné absolútne adresovanie sa môže použiť aj pri použití zmiešaných spojení.

• ← Štvorec v programe Microsoft Excel
• Odhad v programe Microsoft Excel →