Relatívna a absolútna bunková referencia a formátovanie

V tejto lekcii sa budeme zaoberať bunkovými referenciami, ako kopírovať alebo presúvať vzorec a formátovať bunky. Na úvod by sme mali objasniť to, čo máme na mysli odkazmi na bunky, ktoré podporujú veľkú časť sily a všestrannosti vzorcov a funkcií. Konkrétne pochopenie toho, ako fungujú odkazy na bunky, vám umožní získať čo najviac z vašich tabuliek programu Excel!

ŠKOLSKÁ NAVIGÁCIA

1. Prečo potrebujete vzorce a funkcie?
2. Definovanie a vytváranie vzorca
3. Relatívna a absolútna bunková referencia a formátovanie
4. Užitočné funkcie, ktoré by ste mali vedieť
5. Vyhľadávanie, grafy, štatistiky a kontingenčné tabuľky

Poznámka: budeme predpokladať, že už viete, že bunka je jedným z políčok v tabuľke, usporiadaných do stĺpcov a riadkov, na ktoré sa odkazujú písmenami a číslami, ktoré sa pohybujú horizontálne a vertikálne.

Čo je bunková referencia?

"Bunkový odkaz" znamená bunku, na ktorú sa vzťahuje iná bunka. Napríklad, ak v bunke A1 máte = A2. Potom A1 odkazuje na A2.

Pozrime sa, čo sme v Lekcii 2 povedali o riadkoch a stĺpcoch, aby sme mohli ďalej preskúmať odkazy na bunky.

Bunky v tabuľke sú označené riadkami a stĺpcami. Stĺpce sú vertikálne a označené písmenami. Riadky sú horizontálne a označené číslami.

Prvá bunka v tabuľke je A1, čo znamená, že stĺpec A, riadok 1, B3 označuje bunku umiestnenú v druhom stĺpci, tretí riadok atď..

Na účely učenia sa o odkazoch na bunky budeme ich niekedy napísať ako riadok, stĺpec, toto nie je platná notácia v tabuľke a je jednoducho určená na to, aby veci boli jasnejšie.

Typy odkazov na bunky

Existujú tri typy odkazov na bunky.

Absolútna - to znamená, že odkaz na bunku zostane rovnaký, ak skopírujete alebo presuniete bunku do akejkoľvek inej bunky. Robí to tak, že ukotvíte riadok a stĺpec, takže sa pri kopírovaní alebo presune nemení.

Relatívny - Relatívny odkaz znamená, že adresa bunky sa počas kopírovania alebo presunu mení; t.j. referencia buniek je vzhľadom na jeho polohu.

Zmiešané - znamená to, že sa pri kopírovaní alebo presúvaní bunky rozhodnete ukotviť jeden riadok alebo stĺpec tak, aby sa jedna zmenila a druhá nie. Môžete napríklad ukotviť odkaz na riadok, potom presunúť bunku do dvoch riadkov a na štyri stĺpce a odkaz na riadok zostane rovnaký. Vysvetlíme to nižšie.

Relatívne odkazy

Pozrime sa na tento predchádzajúci príklad - predpokladajme, že v bunke A1 máme vzorec, ktorý jednoducho hovorí = A2. To znamená Excel výstup v bunke A1, čo je vložené do bunky A2. V bunke A2 sme zadali "A2", takže Excel zobrazí hodnotu "A2" v bunke A1.

Predpokladajme, že v našej tabuľke musíme vytvoriť priestor na získanie ďalších údajov. Musíme pridať stĺpce nad a riadky doľava, takže musíme posunúť bunku nadol a napravo, aby sme vytvorili priestor.

Keď presuniete bunku doprava, číslo stĺpca sa zvýši. Pri pohybe nadol sa zvýši počet riadkov. Bunka, na ktorú odkazuje, odkaz na bunku sa tiež mení. Toto je ilustrované nižšie:

Pokračovaním v našom príklade a pri pohľade na nižšie uvedenú grafiku, ak skopírujete obsah bunky A1 dve doprava a štyri dole ste ju presunuli do bunky C5.

Skopírovali sme bunku dva stĺpce napravo a štyri nadol. Znamená to, že sme zmenili bunku, ktorá sa vzťahuje na dva a štyri. A1 = A2 je teraz C5 = C6. Namiesto odkazu na A2 sa teraz bunka C5 vzťahuje na bunku C6.

Zobrazená hodnota je 0, pretože bunka C6 je prázdna. V bunke C6 uvádzame "Ja C6" a teraz zobrazuje C5 "Ja som C6".

Príklad: Formulácia textu

Pokúsme sa o ďalší príklad. Nezabudnite si z lekcie 2, kde sme museli rozdeliť celé meno na meno a priezvisko? Čo sa stane, keď skopírujeme tento vzorec?

Napíšte vzorec = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1) alebo skopírujte text do bunky C3. Nekopírujte aktuálnu bunku, iba text, skopírujte text, inak aktualizuje odkaz.

Môžete upraviť obsah bunky v hornej časti tabuľky v poli vedľa miesta, kde sa píše "fx." Toto pole je dlhšie ako celá oblasť je široká, takže je ľahšie upraviť.

Teraz máme:

Nič zložité, práve sme napísali nový vzorec do bunky C3. Teraz skopírujte C3 do buniek C2 a C4. Dodržujte nižšie uvedené výsledky:

Teraz máme mená Alexander Hamilton a Thomas Jefferson.

Použite kurzor na zvýraznenie buniek C2, C3 a C4. Umiestnite kurzor na bunku B2 a vložte obsah. Pozrite sa na to, čo sa stalo - dostali sme chybu: "#REF". Prečo to je??

Keď sme bunky skopírovali zo stĺpca C do stĺpca B, aktualizovali referenčný jeden stĺpec doľava = RIGHT (A2, LEN (A2) - HĽADAŤ ("," A2).

Zmenil každý odkaz na A2 do stĺpca naľavo od A, ale vľavo od stĺpca A nie je žiadny stĺpec. Počítač teda nevie, čo tým myslíte.

Nový vzorec v B2 napríklad = RIGHT (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) a výsledkom je #REF:

Kopírovanie vzorca na rozsah buniek

Kopírovanie buniek je veľmi užitočné, pretože môžete napísať jeden vzorec a skopírovať ho do veľkej oblasti a referencia sa aktualizuje. Tým sa zabráni nutnosti upraviť každú bunku, aby sa ubezpečilo, že smeruje na správne miesto.

Výrazom "rozsah" sa myslí viac ako jedna bunka. Napríklad (C1: C10) znamená všetky bunky od bunky C1 k bunke C10. Takže je to stĺpec buniek. Ďalším príkladom (A1: AZ1) je horný riadok zo stĺpca A do stĺpca AZ.

Ak sa rozmedzie prekríži päť stĺpcov a desať riadkov, označte rozsah písaním vľavo hore a vpravo dole, napríklad A1: E10. Toto je štvorcová oblasť, ktorá pretína riadky a stĺpce a nielen časť stĺpca alebo časti riadku.

Tu je príklad, ktorý ilustruje, ako skopírovať jednu bunku na viaceré miesta. Predpokladajme, že chceme ukázať naše plánované výdavky za mesiac v tabuľkovom procesore, aby sme mohli vytvoriť rozpočet. Vytvárame tabuľku takto:

Teraz skopírujte vzorec v bunke C3 (= B3 + C2) do zvyšku stĺpca, aby ste získali bežný zostatok pre náš rozpočet. Aplikácia Excel aktualizuje odkaz na bunku pri jej kopírovaní. Výsledok je uvedený nižšie:

Ako môžete vidieť, každá nová bunka sa aktualizuje relatívna do nového umiestnenia, takže bunka C4 aktualizuje vzorec na hodnotu = B4 + C3:

Bunka C5 sa aktualizuje na = B5 + C4 a tak ďalej:

Absolútne odkazy

Absolútny odkaz sa nemení, keď presuniete alebo kopírujete bunku. Používame znamienko $, aby ste urobili absolútny odkaz - pamätajte si, že si myslíte, že znak dolára ako kotva.

Napríklad zadajte vzorec = $ A $ 1 do akejkoľvek bunky. Hodnota $ pred stĺpcom A znamená nemeniť stĺpec, hodnota $ pred riadkom 1 znamená, že nemeníte stĺpec pri kopírovaní alebo presune bunky do akejkoľvek inej bunky.

Ako vidíte v nižšie uvedenom príklade, v bunke B1 máme relatívny odkaz = A1. Keď kopírujeme B1 do štyroch buniek pod ním, relatívna referencia = A1 sa zmení na bunku vľavo, takže B2 sa stáva A2, B3 stať sa A3 atď. Tieto bunky zrejme nemajú žiadnu hodnotu, takže výstup je nulový.

Ak však použijeme = $ A1 $ 1, napríklad v C1 a kopírujeme ho do štyroch buniek pod ním, referencia je absolútna, takže sa nikdy nezmení a výstup sa vždy rovná hodnote v bunke A1.

Predpokladajme, že sledujete svoj záujem, napríklad v nižšie uvedenom príklade. Vzorec v C4 = B4 * B1 je "úroková sadzba" * "zostatok" = "úroky za rok".

Teraz ste zmenili svoj rozpočet a ušetrili ďalšie 2 000 dolárov na nákup podielového fondu. Predpokladajme, že ide o fond s pevnou úrokovou sadzbou a platí rovnakú úrokovú sadzbu. Zadajte nový účet a vyvážte do tabuľky a potom skopírujte vzorec = B4 * B1 z bunky C4 do bunky C5.

Nový rozpočet vyzerá takto:

Nový podielový fond zarába za rok úroky vo výške 0 USD, čo nemôže byť správne, pretože úroková sadzba je jasne 5%.

Program Excel zvýrazňuje bunky, na ktoré odkazuje vzorec. Môžete vidieť vyššie, že odkaz na úrokovú sadzbu (B1) sa presunie do prázdnej bunky B2. Mali sme urobiť odkaz na B1 absolútne písaním $ B $ 1 pomocou znaku dolárov na ukotvenie riadku a stĺpca referencie.

Prepočítajte prvý výpočet v C4 a čítajte = B4 * $ B $ 1, ako je uvedené nižšie:

Potom skopírujte tento vzorec od C4 do C5. Tabuľka teraz vyzerá takto:

Keďže sme skopírovali vzorec jedna bunka dole, t.j. zvýšili riadok o jednu, nový vzorec je = B5 * $ B $ 1. Úroková miera podielového fondu sa teraz správne vypočíta, pretože úroková sadzba je ukotvená do bunky B1.

Je to dobrý príklad toho, kedy by ste mohli používať "meno" na označenie bunky. Názov je absolútny odkaz. Ak napríklad priradíte názov "úroková sadzba" do bunky B1, kliknite pravým tlačidlom myši na bunku a potom zvoľte "define name".

Mená sa môžu vzťahovať na jednu bunku alebo rozsah a môžete použiť názov vo vzorci, napríklad = interest_rate * 8 je to isté ako písanie = $ B $ 1 * 8.

Zmiešané referencie

Zmiešané odkazy sú kedy buď riadok alebo stĺpec je ukotvený.

Predstavte si napríklad, že ste poľnohospodár, ktorý vytvára rozpočet. Tiež vlastníte obchod s krmivami a predávate osivo. Budete pestovať kukuricu, sóju a lucerny. Tabuľka nižšie zobrazuje cenu za aker. "Cena za akr" = "cena za libru" * "libier semien na aker" - to je to, čo vás bude stáť,.

Zadajte cenu za akr ako = $ B2 * C2 v bunke D2. Hovoríte, že chcete ukotviť stĺpec ceny za libru. Potom skopírujte tento vzorec do ostatných riadkov v tom istom stĺpci:

Teraz chcete poznať hodnotu vášho inventára semien. Potrebujete cenu za libru a počet libier v inventári, aby ste vedeli hodnotu zásob.

Pridáme dva stĺpce: "libra semena v inventári" a potom "hodnota zásob". Teraz skopírujte bunku D2 na F4 a všimnite si, že odkaz na riadok v prvej časti pôvodného vzorca ($ B2) je aktualizovaný na riadok 4, ale stĺpec zostáva pevný, pretože $ ho ukotví na "B."

Toto je zmiešaná referencia, pretože stĺpec je absolútny a riadok je relatívny.

Kruhové odkazy

Kruhový odkaz je, keď vzorec odkazuje na seba.

Napríklad, nemôžete písať c3 = c3 + 1. Tento druh výpočtu sa nazýva "iterácia", čo znamená, že sa opakuje. Program Excel nepodporuje opakovanie, pretože počíta všetko iba raz.

Ak sa to pokúsite, zadajte hodnotu SUM (B1: B5) do bunky B5:

Zobrazí sa varovná obrazovka:

Aplikácia Excel vám povie iba, že máte kruhovú referenciu v dolnej časti obrazovky, aby ste si ju nevšimli. Ak máte kruhovú referenciu a zatvoríte tabuľku a opäť ju otvoríte, program Excel vám v pop-up okne povie, že máte kruhový odkaz.

Ak máte kruhovú referenciu, pri každom otvorení tabuľky vám program Excel s týmto vyskakovacím oknom povie, že máte kruhový odkaz.

Odkazy na iné pracovné listy

"Zošit" je zbierka "pracovných hárkov". Jednoducho povedané, znamená to, že v jednom súbore programu Excel (pracovný zošit) môžete mať viacero tabuliek (pracovných hárkov). Ako vidíte v nižšie uvedenom príklade, príklad nášho pracovného zošitu obsahuje mnoho pracovných hárkov (červenou farbou).

Štandardne sú pracovné hárky pomenované Sheet1, Sheet2 a tak ďalej. Vytvoríte nový kliknutím na "+" v dolnej časti obrazovky programu Excel.

Môžete zmeniť názov pracovného hárka na niečo užitočné ako "úver" alebo "rozpočet" kliknutím pravým tlačidlom myši na kartu pracovného hárka zobrazenú v dolnej časti obrazovky programu Excel, výberom premenovania a zadaním nového mena.

Alebo jednoducho dvakrát kliknite na kartu a premenujte ju.

Syntax pre odkaz na pracovný hárok je = pracovný hárka! Bunka. Tento druh referencie môžete použiť, ak sa rovnaká hodnota použije v dvoch pracovných hárkoch, príkladmi môžu byť:

• Dnešný dátum
• Mena konverzného kurzu z dolárov na euro
• Všetko, čo je relevantné pre všetky pracovné hárky v zošite

Nižšie je uvedený príklad pracovného hárku "záujem" odkazujúci na pracovný hárok "pôžička", bunka B1.

Ak sa pozrieme na pracovný hárok "úver", môžeme vidieť odkaz na výšku úveru:

Nasleduje…

Dúfame, že teraz máte pevné pochopenie bunkových odkazov vrátane relatívnych, absolútnych a zmiešaných. Určite je veľa.

To je pre dnešnú lekciu, v lekcii 4, budeme diskutovať o niektorých užitočných funkciách, ktoré by ste chceli vedieť pri každodennom používaní programu Excel.