7 tipov na prácu so vzorcami v Exceli

Niekoľko prvkov vám môže pomôcť byť čo najproduktívnejší pri písaní a opravách vzorcov v Exceli. Môžete zobraziť všetky svoje vzorce naraz a opraviť chyby jeden po druhom. Na pomoc pri písaní funkcií môžete použiť sprievodcov doplnkami.

Prednosť hlavného operátora

Jedným z najdôležitejších faktorov pri písaní vzorcov je správne nastavenie operátorov, a tým nemyslím operátorov telefónnych spoločností. Súvisí to s matematickými operátormi – viete, s malými detailmi, ako sú znamienka plus a násobenie, a kam idú zátvorky. Priorita operátora – poradie, v ktorom sa operácie vykonávajú – môže mať veľký rozdiel vo výsledku. Máte jednoduchý spôsob, ako udržať poradie s prioritou operátora. Jediné, čo si musíte zapamätať, je "Prosím, ospravedlňte moju drahú tetu Sally."

Táto fráza je mnemotechnická pomôcka pre nasledovné:

• Zátvorky

• Exponenty

• Násobenie

• divízie

• Doplnenie

• Odčítanie

Zátvorky majú teda prvú (najvyššiu) prednosť a odčítanie poslednú. No, aby som bol úprimný, násobenie má rovnakú prednosť ako delenie a sčítanie má rovnakú prednosť ako odčítanie, ale pochopíte!

Napríklad vzorec =1 + 2 × 15 sa rovná 31. Ak si myslíte, že by sa mal rovnať 45, choďte radšej navštíviť svoju tetu! Odpoveď sa rovná 45, ak zahrniete zátvorky, napríklad: =(1 + 2) × 15.

Správne poradie operátorov je rozhodujúce pre dobrý stav vášho pracovného hárka. Excel generuje chybu, keď sa čísla otvorených a uzavretých zátvoriek nezhodujú, ale ak chcete pred násobením pridať dve čísla, Excel nevie, že ste zátvorky jednoducho vynechali!

Niekoľko minút osvieženia pamäte na príkaz operátora vám môže ušetriť veľa bolesti hlavy.

Zobraziť vzorce

V prípade, že ste si nevšimli, je dosť ťažké zobraziť vzorce bez toho, aby ste ich náhodou upravili. Je to preto, že kedykoľvek ste v režime „úprav“ a aktívna bunka má vzorec, vzorec môže obsahovať adresu akejkoľvek inej bunky, na ktorú kliknete. Toto totálne kazí veci.

Nebolo by ľahké, keby ste sa mohli pozrieť na všetky svoje vzorce? Existuje spôsob! Je to jednoduché. Kliknite na položku Súbor v ľavej hornej časti pracovného priestoru programu Excel, kliknite na položku Možnosti, kliknite na kartu Rozšírené a prejdite nadol na časť Možnosti zobrazenia pre tento pracovný hárok.

Možnosti nastavenia.

Všimnite si začiarkavacie políčko Zobraziť vzorce v bunkách namiesto ich vypočítaných výsledkov. Toto pole informuje Excel, že pre všetky bunky, ktoré obsahujú vzorce, sa namiesto vypočítaného výsledku zobrazí samotný vzorec. Nasledujúci obrázok zobrazuje pracovný hárok, ktorý zobrazuje vzorce. Ak sa chcete vrátiť do normálneho zobrazenia, zopakujte tieto kroky a zrušte výber možnosti. Táto možnosť uľahčuje zobrazenie všetkých vzorcov!

Prezeranie vzorcov jednoduchým spôsobom.

Funkcie môžete náhodne upravovať, aj keď ste vybrali možnosť Zobraziť vzorce. Buďte opatrní pri klikaní po pracovnom hárku.

Opravte vzorce

Predpokladajme, že váš pracovný hárok obsahuje nejaké chyby. Neprepadajte panike! Stáva sa to aj tým najšikovnejším používateľom a Excel vám môže pomôcť zistiť, čo sa deje. Na karte Vzorce v časti Auditovanie vzorcov je tlačidlo Kontrola chýb. Kliknutím na tlačidlo sa zobrazí dialógové okno Kontrola chýb, ktoré je zobrazené tu. To znamená, že ak má váš pracovný hárok nejaké chyby, zobrazí sa dialógové okno. V opačnom prípade sa zobrazí iba správa, že kontrola chýb je dokončená. Je to také chytré!

Kontrola chýb.

Keď tam sú chyby, sa zobrazí dialógové okno box a tyčinky okolo pri práci na každú chybu. Tlačidlá Ďalej a Predchádzajúce vám umožňujú prechádzať všetkými chybami pred zatvorením dialógového okna. Pre každú chybu, ktorú nájde, vyberiete akciu, ktorú vykonáte:

• Pomoc pri tejto chybe: Toto vedie do systému pomoci a zobrazuje tému pre konkrétny typ chyby.

• Zobraziť kroky výpočtu: Otvorí sa dialógové okno Vyhodnotiť vzorec a môžete krok za krokom sledovať, ako sa vzorec vypočítava. To vám umožní identifikovať konkrétny krok, ktorý spôsobil chybu.

• Ignorovať chybu: Možno sa Excel mýli. Chybu ignorujte.

• Upraviť v riadku vzorcov: Toto je rýchly spôsob, ako sami opraviť vzorec, ak nepotrebujete žiadnu inú pomoc.

Dialógové okno Kontrola chýb obsahuje aj tlačidlo Možnosti. Kliknutím na tlačidlo sa otvorí karta Vzorce v dialógovom okne Možnosti programu Excel. Na karte Vzorce môžete vybrať nastavenia a pravidlá na rozpoznávanie a spúšťanie chýb.

Používajte absolútne referencie

Ak sa chystáte použiť rovnaký vzorec pre veľa buniek, ako sú tie, ktoré idú nadol v stĺpci, najlepším spôsobom je napísať vzorec raz a potom ho potiahnuť nadol do ostatných buniek pomocou rukoväte výplne. Problém je v tom, že keď pretiahnete vzorec na nové miesta, všetky relatívne odkazy sa zmenia.

Často je to zámer. Ak existuje jeden stĺpec údajov a susedný stĺpec vzorcov, zvyčajne každá bunka v stĺpci vzorca odkazuje na svojho suseda v stĺpci údajov. Ale ak všetky vzorce odkazujú na bunku, ktorá nie je susedná, zámerom je, aby všetky bunky vzorcov odkazovali na nemenný odkaz na bunku. Aby to fungovalo správne, použite absolútny odkaz na bunku.

Ak chcete použiť absolútny odkaz na bunku, použite znak dolára ($) pred číslom riadka, pred písmenom stĺpca alebo pred oboma. Urobte to pri písaní prvého vzorca pred jeho presunutím do iných buniek, inak budete musieť aktualizovať všetky vzorce.

Nepíšte napríklad toto:

=A4 x (B4 + A2)

Napíš to radšej takto:

=A4 x (B4 + $A$2)

Týmto spôsobom všetky vzorce odkazujú na A2 bez ohľadu na to, kam ich skopírujete, namiesto toho, aby sa tento odkaz zmenil na A3 a A4 atď.

Použite auditovanie vzorcov

Existujú precedensy a závislosti. Existujú externé referencie. Všade je interakcia. Ako môžete sledovať, odkiaľ odkazy na vzorce pochádzajú a kam smerujú?

Použite nástroje na auditovanie vzorcov, je to tak! Na karte Vzorce sa nachádza časť Kontrola vzorcov. V sekcii sú rôzne tlačidlá, ktoré ovládajú viditeľnosť šípok sledovania auditu.

Vzorce auditu.

Panel s nástrojmi auditovania vzorcov má niekoľko funkcií, ktoré vám umožňujú prechádzať vzorcami. Okrem zobrazenia šípok sledovania vám panel s nástrojmi tiež umožňuje kontrolovať chyby, vyhodnocovať vzorce, kontrolovať neplatné údaje a pridávať komentáre do hárkov.

Použite podmienené formátovanie

Rovnako ako funkcia IF vracia určitú hodnotu, keď je podmienka prvého argumentu pravdivá, a inú hodnotu, keď je nepravdivá, podmienené formátovanie vám umožňuje použiť určitý formát na bunku, keď je podmienka pravdivá. Na karte Domov v časti Štýly sa nachádza rozbaľovacia ponuka s mnohými možnosťami podmieneného formátovania.

Tento obrázok ukazuje niektoré hodnoty, ktoré boli spracované podmieneným formátovaním. Podmienené formátovanie vám umožňuje nastaviť podmienku a vybrať formát, ktorý sa použije, keď je podmienka splnená. Môžete napríklad určiť, aby sa bunka zobrazila tučnou kurzívou, ak je hodnota, ktorú obsahuje, väčšia ako 100.

Použitie formátu, keď je splnená podmienka.

Podmienky sú stanovené ako pravidlá. Typy pravidiel sú

• Naformátujte všetky bunky na základe ich hodnôt.

• Formátovať len bunky, ktoré obsahujú. . . .

• Formátovať iba najvyššie alebo najnižšie hodnotené hodnoty.

• Formátovať iba hodnoty, ktoré sú nad alebo pod priemerom.

• Formátujte iba jedinečné alebo duplicitné hodnoty.

• Použite vzorec na určenie, ktoré bunky sa majú formátovať.

Ak je podmienka pravdivá, formátovanie môže ovládať nasledovné:

• Hranice

• Nastavenia písma (štýl, farba, tučné písmo, kurzíva atď.)

• Výplň (farba pozadia bunky alebo vzor)

Bunky môžu byť tiež naformátované pomocou farebných schém alebo obrázkov ikon umiestnených v bunke.

Použite overenie údajov

Na karte Údaje v časti Nástroje údajov je Overenie údajov. Overenie údajov vám umožňuje aplikovať pravidlo na bunku (alebo bunky), takže položka musí dodržiavať pravidlo. Napríklad bunka môže byť nastavená tak, aby akceptovala iba celočíselný záznam medzi 50 a 100.

Nastavenie overenia údajov.

Keď záznam nevyhovie pravidlu, zobrazí sa správa.

Pristihnutý pri zlom vstupe.

Chybové hlásenie je možné prispôsobiť. Napríklad, ak niekto zadá nesprávne číslo, zobrazené chybové hlásenie môže znieť Noodlehead — naučte sa počítať! Len nech to šéf nevidí.