Relativní a absolutní odkaz na buňky a formátování

V této lekci se budeme zabývat buněčnými odkazy, jak kopírovat nebo přesouvat vzorec a formátovat buňky. Začněme, abychom objasnili, co máme na mysli odkazy na buňky, které jsou základem pro moc a všestrannost vzorců a funkcí. Konkrétní pochopení toho, jak fungují reference článků, vám umožní získat co nejvíce z vašich tabulek aplikace Excel!

ŠKOLNÍ NAVIGACE

1. Proč potřebujete vzorce a funkce?
2. Definování a vytváření vzorce
3. Relativní a absolutní odkaz na buňky a formátování
4. Užitečné funkce, které byste měli vědět
5. Vyhledávání, grafy, statistiky a kontingenční tabulky

Poznámka: budeme předpokládat, že už víte, že buňka je jeden z čtverců v tabulce, uspořádané do sloupců a řádků, které jsou označeny písmeny a čísly probíhajícími vodorovně a svisle.

Co je odkaz na buňky?

"Odkaz na buňku" znamená buňku, na kterou odkazuje další buňka. Například pokud v buňce A1 máte = A2. Pak A1 odkazuje na A2.

Podívejme se, co jsme v lekci 2 řekli o řádcích a sloupcích, abychom mohli dále zkoumat odkazy na buňky.

Buňky v tabulce jsou označeny řádky a sloupci. Sloupce jsou vertikální a jsou označeny písmeny. Řádky jsou vodorovné a označeny čísly.

První buňka v tabulce je A1, což znamená sloupec A, řádek 1, B3 odkazuje na buňku umístěnou ve druhém sloupci, třetí řádek atd..

Pro účely učení o buněčných referencích budeme je občas napsat jako řádek, sloupec, toto není platná notace v tabulce a je prostě určena k tomu, aby věci byly jasnější.

Typy odkazů na buňky

Existují tři typy odkazů na buňky.

Absolutní - to znamená, že odkaz buňky zůstane stejný, pokud zkopírujete nebo přesunete buňku do libovolné jiné buňky. To se provádí ukotvení řádku a sloupce, takže se při kopírování nebo přesunu nezmění.

Relativní - Relativní odkaz znamená, že se adresa buňky mění při kopírování nebo přesunutí; tj. odkaz na buňky je relativní vzhledem k jeho umístění.

Smíšené - znamená to, že se při kopírování nebo přesunutí buňky rozhodnete ukotvit buď řádek nebo sloupec, takže jeden se změní a druhý ne. Můžete například ukotvit referenční řádek, potom přesunout buňku dolů na dva řádky a na čtyři sloupce a odkaz na řádek zůstane stejný. Vysvětlíme to níže.

Relativní reference

Ukažme se na tento dřívější příklad - předpokládejme, že v buňce A1 máme vzorec, který jednoduše říká = A2. To znamená, že výstup Excel v buňce A1 je vložen do buňky A2. V buňce A2 jsme zadali "A2", takže Excel zobrazí v buňce A1 hodnotu "A2".

Nyní předpokládejme, že potřebujeme vytvořit místo v naší tabulce pro další data. Musíme přidat sloupce výše a řádky doleva, takže musíme přesunout buňku dolů a doprava, abychom uvolnili místo.

Když přesunete buňku doprava, zvýší se číslo sloupce. Při posunu dolů se číslo řady zvyšuje. Buňka, na kterou odkazuje, odkaz na buňku se také mění. To je ilustrováno níže:

Pokračujeme s naším příkladem a podíváme-li se na níže uvedený obrázek, zkopírujete-li obsah buňky A1 dvěma doprava a čtyři dolů jste je přesunuli do buňky C5.

Zkopírovali jsme buňku dva sloupce doprava a čtyři dolů. To znamená, že jsme změnili buňku, kterou označuje dvěma a čtyři dolů. A1 = A2 je nyní C5 = C6. Místo odkazu na A2 se nyní buňka C5 týká buňky C6.

Zobrazená hodnota je 0, protože buňka C6 je prázdná. V buňce C6 zadejte "Jsem C6" a nyní C5 zobrazí "Jsem C6".

Příklad: Formulář textu

Zkusme další příklad. Zapamatujte si ze lekce 2, kde jsme museli rozdělit celé jméno na příjmení a příjmení? Co se stane, když zkopírujeme tento vzorec?

Napište vzorec = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1) nebo zkopírujte text do buňky C3. Nekopírujte aktuální buňku, pouze text, zkopírujte text, jinak aktualizuje odkaz.

Obsah buňky v horní části tabulky můžete upravit v poli vedle pole "fx." Toto pole je delší než je šířka buňky, takže je jednodušší editovat.

Nyní máme:

Není nic komplikovaného, ​​právě jsme napsali nový vzorec do buňky C3. Nyní kopírujte C3 do buněk C2 a C4. Dodržujte níže uvedené výsledky:

Nyní máme jména Alexander Hamilton a Thomas Jefferson.

Pomocí kurzoru zvýrazněte buňky C2, C3 a C4. Umístěte kurzor na buňku B2 a vložte obsah. Podívejte se na to, co se stalo - dostáváme chybu: "#REF." Proč to je??

Když jsme zkopírovali buňky ze sloupce C do sloupce B, aktualizovali referenční sloupec vlevo = RIGHT (A2, LEN (A2)) - FIND ("," A2).

Změnila každý odkaz na A2 ke sloupci nalevo od A, ale vlevo od sloupce A není žádný sloupec. Počítač tedy neví, co myslíte.

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

Kopírování vzorce na rozsah buněk

Kopírování buněk je velmi užitečné, protože můžete napsat jeden vzorec a zkopírovat ho do velké oblasti a odkaz je aktualizován. Tím se zabrání nutnosti upravit každou buňku, aby bylo zajištěno, že odkazuje na správné místo.

Výrazem "rozsah" rozumíme více než jednu buňku. Například (C1: C10) znamená všechny buňky od buňky C1 k buňce C10. Takže to je sloupec buněk. Dalším příkladem (A1: AZ1) je horní řádek ze sloupce A do sloupce AZ.

Pokud rozsah překročí pět sloupců a deset řádků, označte rozsah písmenem vlevo nahoře a vpravo dole, např. A1: E10. Toto je čtvercová oblast, která přechází řádky a sloupce a ne jen část sloupce nebo části řádku.

Zde je příklad, který ilustruje, jak zkopírovat jednu buňku na více míst. Předpokládejme, že chceme uvést plánované výdaje za měsíc v tabulce, abychom mohli sestavit rozpočet. Vytváříme tabulku takto:

Nyní zkopírujte vzorec do buňky C3 (= B3 + C2) do zbývající části sloupce, abyste získali běžný zůstatek pro náš rozpočet. Aplikace Excel aktualizuje odkaz na buňky při kopírování. Výsledek je uveden níže:

Jak vidíte, každá nová buňka se aktualizuje relativní do nového umístění, takže buňka C4 aktualizuje svůj vzorec na = B4 + C3:

Buňka C5 se aktualizuje na = B5 + C4 a tak dále:

Absolutní odkazy

Absolutní odkaz se nezmění při pohybu nebo kopírování buňky. Používáme značku $, abychom získali absolutní odkaz - pamatujte si na to, že uvažujete o dolaru jako kotvu.

Například zadejte vzorec = $ A $ 1 v libovolné buňce. $ $ Před sloupcem A znamená, že neměníte sloupec, $ před řádkem 1 znamená, že při kopírování nebo přesunutí buňky do jiné buňky nezměníte sloupec.

Jak vidíte v příkladu níže, v buňce B1 máme relativní referenci = A1. Když kopírujeme B1 do čtyř buněk pod ním, relativní odkaz = A1 se změní na buňku vlevo, takže B2 se stává A2, B3 stávají se A3 apod. Tyto buňky zjevně nemají žádnou hodnotu, takže výstup je nulový.

Nicméně pokud používáme = $ A1 $ 1, například v jazyce C1 a zkopírujeme jej do čtyř buněk pod ním, je absolutní hodnota, takže se nikdy nezmění a výstup je vždy stejný jako hodnota v buňce A1.

Předpokládejme, že sledujete svůj zájem, například v níže uvedeném příkladu. Vzorec C4 = B4 * B1 je "úroková sazba" * "zůstatek" = "úroky za rok".

Nyní jste změnili svůj rozpočet a ušetřili jste dalších 2 000 dolarů na nákup podílových fondů. Předpokládejme, že jde o fond s pevnou úrokovou sazbou a platí stejnou úrokovou sazbu. Zadejte nový účet a zůstatek do tabulky a potom zkopírujte vzorec = B4 * B1 z buňky C4 do buňky C5.

Nový rozpočet vypadá takto:

Nový podílový fond vydělává za rok za úroky v hodnotě 0 dolarů, což nemůže být správné, neboť úroková sazba je jasně 5 procent.

Excel zvýrazní buňky, na které odkazuje vzorec. Můžete vidět výše, že odkaz na úrokovou sazbu (B1) se přesune do prázdné buňky B2. Měli jsme odkaz na absolutní hodnotu B1 napsáním $ B $ 1 pomocí dolaru pro ukotvení řádku a sloupce reference.

Přepsat první výpočet v jazyce C4 na čtení = B4 * $ B $ 1, jak je uvedeno níže:

Pak zkopírujte tento vzorec od C4 do C5. Tabulka nyní vypadá takto:

Protože jsme zkopírovali vzorec o jednu buňku dolů, tj. Zvýšili řádek o jednu, nový vzorec je = B5 * $ B $ 1. Úroková míra podílových fondů je nyní správně vypočtena, protože úroková sazba je ukotvena do buňky B1.

To je dobrý příklad, kdy byste mohli použít "název" pro odkaz na buňku. Název je absolutní reference. Například k přiřazení názvu "úrokové sazby" buňce B1, klepněte pravým tlačítkem myši na buňku a vyberte "definovat název."

Názvy mohou odkazovat na jednu buňku nebo rozsah a můžete použít název ve vzorci, například = interest_rate * 8 je stejná jako písemně = $ B $ 1 * 8.

Smíšené odkazy

Smíšené odkazy jsou kdy buď řada nebo sloupec je ukotven.

Předpokládejme například, že jste farmář, který vytváří rozpočet. Vlastníte také obchod s krmivem a prodáváte semena. Budete vyrábět kukuřici, sóju a lucernu. Tabulka níže uvádí náklady na akr. "Cena za akr" = "cena za libru" * "libry semen na akr" - to je to, co vás bude stát na to,.

Zadejte cenu za akr as = $ B2 * C2 v buňce D2. Říkáte, že chcete ukotvit cenu za libru. Potom zkopírujte tento vzorec do ostatních řádků ve stejném sloupci:

Nyní byste chtěli znát hodnotu vašeho inventáře semen. Potřebujete cenu za libru a počet kilogramů v inventáři, abyste zjistili hodnotu inventáře.

Přidáme dva sloupce: "libra osiva v inventáři" a pak "hodnota zásob". Nyní zkopírujte buňku D2 na F4 a poznamenejte, že odkaz na řádek v první části původního vzorce ($ B2) je aktualizován na řádek 4, ale sloupec zůstává pevný, protože $ jej ukotví na "B."

Toto je smíšený odkaz, protože sloupec je absolutní a řádek je relativní.

Kruhové odkazy

Kruhový odkaz je, když vzorec odkazuje na sebe.

Například nemůžete psát c3 = c3 + 1. Tento druh výpočtu se nazývá "iterace", což znamená, že se opakuje. Aplikace Excel nepodporuje opakování, protože vše počítá pouze jednou.

Pokud se to pokusíte zadáním SUM (B1: B5) do buňky B5:

Objeví se varovná obrazovka:

Aplikace Excel vám pouze říká, že v dolní části obrazovky máte kruhový odkaz, abyste si ji nevšimli. Pokud máte kruhovou referenci a zavřete tabulku a znovu ji otevřete, aplikace Excel vám v překryvném okně poví, že máte kruhový odkaz.

Pokud máte kruhovou referenci, při každém otevření tabulky vám aplikace Excel s tímto vyskakovacím oknem oznámí, že máte kruhový odkaz.

Odkazy na jiné pracovní listy

"Sešit" je sada "pracovních listů". Jednoduše řečeno, znamená to, že můžete mít ve stejném souboru aplikace Excel (sešit) více tabulek (listů). Jak je vidět na příkladu níže, náš příklad sešit má mnoho pracovních listů (červeně).

Pracovní listy jsou ve výchozím nastavení pojmenovány Sheet1, Sheet2 a tak dále. Nový nástroj vytvoříte klepnutím na tlačítko + v dolní části obrazovky aplikace Excel.

Název pracovního listu můžete změnit na něco užitečného jako "půjčka" nebo "rozpočet" klepnutím pravým tlačítkem myši na kartu pracovního listu zobrazenou v dolní části obrazovky aplikace Excel, výběrem přejmenování a zadáním nového jména.

Nebo můžete jednoduše poklepat na kartu a přejmenovat ji.

Syntaxe reference pracovního listu je = workheet! Cell. Tento typ odkazu můžete použít, pokud je ve dvou pracovních listech použita stejná hodnota, příklady mohou být:

• Dnešní datum
• Měnový přepočítací koeficient od dolarů k eurem
• Všechno, co je relevantní pro všechny listy v sešitu

Níže je uveden příklad pracovního listu "zájem" odkazující na pracovní list "úvěr", článek B1.

Pokud se podíváme na pracovní list "úvěr", můžeme vidět odkaz na výši úvěru:

Další informace ...

Doufáme, že nyní máte pevný přehled o buněčných referencích, včetně relativních, absolutních a smíšených. Určitě je toho hodně.

To je pro dnešní hodinu, v lekci 4, budeme diskutovat některé užitečné funkce, které byste chtěli vědět pro každodenní použití aplikace Excel.