Problémy s výpočtom vzorcov v programe Microsoft Excel. Režim stránky pracovného hárka v programe Excel. Informácie v komentároch
Jednou z najžiadanejších funkcií Excelu je práca so vzorcami. Vďaka tejto funkcii program nezávisle vykonáva rôzne druhy výpočtov v tabuľkách. Niekedy sa však stane, že používateľ zadá vzorec do bunky, ale nesplní svoj priamy účel - vypočítať výsledok. Poďme zistiť, s čím to môže súvisieť a ako tento problém vyriešiť.
Dôvody problémov s výpočtom vzorcov v programe Excel môžu byť úplne odlišné. Môžu byť spôsobené jednak nastavením konkrétnej knihy alebo dokonca samostatným rozsahom buniek, jednak rôznymi chybami syntaxe.
Metóda 1: Zmeňte formát buniek
Jedným z najčastejších dôvodov, prečo Excel vôbec nevypočítava alebo nevypočítava vzorce, je nesprávne nastavený formát buniek. Ak má rozsah textový formát, potom sa výpočet výrazov v ňom vôbec nevykonáva, to znamená, že sa zobrazujú ako bežný text. V ostatných prípadoch, ak formát nezodpovedá podstate vypočítaných údajov, môže byť výsledok zobrazený v bunke zobrazený nesprávne. Poďme zistiť, ako tento problém vyriešiť.
Teraz sa vzorec vypočíta v štandardnom poradí s výstupom výsledku v zadanej bunke.
Metóda 2: vypnite režim „Zobraziť vzorce“
Ale možno dôvod, prečo namiesto výsledkov výpočtu zobrazujete výrazy, je ten, že program povolil režim "Zobraziť vzorce".
Metóda 3: Opravte chybu syntaxe
Vzorec môže byť tiež zobrazený ako text, ak boli v jeho syntaxi chyby, napríklad chýbalo alebo bolo zmenené písmeno. Ak ste ho zadali ručne, nie prostredníctvom Sprievodca funkciami, potom je to celkom pravdepodobné. Veľmi častou chybou pri zobrazení výrazu ako textu je prítomnosť medzery pred znakom «=» .
V takýchto prípadoch musíte starostlivo skontrolovať syntax tých vzorcov, ktoré sa zobrazujú nesprávne, a vykonať príslušné úpravy.
Metóda 4: Povoliť prepočet vzorca
Stáva sa tiež, že vzorec zdá sa, že zobrazuje hodnotu, ale keď sa bunky s ním spojené zmenia, nezmenia sa samy, to znamená, že výsledok sa neprepočítava. To znamená, že ste nesprávne nakonfigurovali parametre výpočtu v tejto knihe.
Všetky výrazy v tomto zošite sa teraz automaticky prepočítajú vždy, keď sa zmení akákoľvek priradená hodnota.
Metóda 5: chyba vo vzorci
Ak program stále počíta, ale v dôsledku toho ukazuje chybu, je pravdepodobné, že používateľ pri zadávaní výrazu jednoducho urobil chybu. Chybné vzorce sú tie, pri ktorých výpočte sa v bunke zobrazia nasledujúce hodnoty:
- #ČÍSLO !;
- #HODNOTA!;
- # PRÁZDNY!;
- # DIV / 0!;
- # N / A.
V takom prípade musíte skontrolovať, či sú údaje v bunkách, na ktoré odkazuje výraz, správne zapísané, či nedochádza k chybám syntaxe alebo či vzorec samotný obsahuje nesprávnu akciu (napríklad delenie 0).
Ak je funkcia komplexná s veľkým počtom prepojených buniek, je jednoduchšie sledovať výpočty pomocou špeciálneho nástroja.
Ako vidíte, dôvody, prečo Excel neberie do úvahy alebo nesprávne vypočítava vzorce, môžu byť úplne odlišné. Ak namiesto výpočtu používateľ zobrazí samotnú funkciu, potom je v tomto prípade s najväčšou pravdepodobnosťou buď bunka naformátovaná na text, alebo je zapnutý režim zobrazenia výrazu. Je tiež možná chyba syntaxe (napríklad prítomnosť medzery pred znakom «=» ). Ak sa po zmene údajov v prepojených bunkách výsledok neaktualizuje, potom tu musíte vidieť, ako sa v parametroch knihy konfiguruje automatická aktualizácia. Tiež sa pomerne často namiesto správneho výsledku v bunke zobrazí chyba. Tu je potrebné zobraziť všetky hodnoty, na ktoré sa funkcia odvoláva. Ak sa nájde chyba, mala by byť odstránená.
Čitatelia Lifehackera sú už oboznámení Denis Batyanov ktorí sa s nami podelili. Dnes Denis porozpráva o tom, ako sa vyhnúť najčastejším problémom s Excelom, ktoré si často vytvárame sami.
Hneď urobím rezerváciu, že materiál článku je určený pre začínajúcich používateľov Excelu. Skúsení užívatelia už neraz na tomto hrable tancovali zápalne, takže mojou úlohou je pred týmto uchrániť mladých a neskúsených „tanečníkov“.
Stĺpcom tabuľky nedávate názvy
Mnoho nástrojov Excelu, ako napríklad triedenie, filtrovanie, inteligentné tabuľky, kontingenčné tabuľky, predpokladá, že vaše údaje obsahujú hlavičky stĺpcov. V opačnom prípade ich buď nebudete môcť používať vôbec, alebo nebudú fungovať celkom správne. Vždy sa uistite, že vaše tabuľky obsahujú nadpisy stĺpcov.
Prázdne stĺpce a riadky vo vašich tabuľkách
Pre Excel je to mätúce. Keď v tabuľke narazíte na prázdny riadok alebo stĺpček, začne si myslieť, že máte 2 tabuľky, nie jednu. Budete to musieť neustále opravovať. Do tabuľky tiež neskrývajte nepotrebné riadky / stĺpce, je lepšie ich odstrániť.
Na jednom hárku je umiestnených niekoľko tabuliek
Ak nejde o malé tabuľky obsahujúce referenčné hodnoty, nemali by ste to robiť.
Bude pre vás nepohodlné plne pracovať s viac ako jednou tabuľkou na list. Ak je napríklad jedna tabuľka vľavo a druhá vpravo, filtrovanie na jednej tabuľke ovplyvní druhú. Ak sú tabuľky umiestnené pod sebou, nie je možné použiť pripnutie oblastí a jedna z tabuliek bude musieť neustále hľadať a vykonávať zbytočné manipulácie, aby sa na ňu postavila s kurzorom tabuľky. Potrebuješ to?
Údaje rovnakého typu sú umelo usporiadané v rôznych stĺpcoch
Používatelia, ktorí poznajú Excel, veľmi často uprednostňujú tento formát tabuľky:
Zdá sa, že čelíme neškodnému formátu zhromažďovania informácií o predaji agentov a ich pokutách. Takéto rozloženie stola je osobou dobre vnímané vizuálne, pretože je kompaktné. Verte mi však, že je skutočnou nočnou morou pokúsiť sa extrahovať údaje z takýchto tabuliek a získať medzisúčty (súhrnné informácie).
Faktom je, že tento formát obsahuje 2 dimenzie: takže sa musíte rozhodnúť pre linku prechodom cez vetvu, skupinu a agenta. Keď nájdete požadované zásoby, budete musieť vyhľadať požadovaný stĺpec, pretože ich je tu veľa. A táto „dvojrozmernosť“ značne komplikuje prácu s takouto tabuľkou pre štandardné nástroje Excelu - vzorce a kontingenčné tabuľky.
Ak vytvoríte kontingenčnú tabuľku, zistíte, že neexistuje spôsob, ako ľahko získať údaje podľa roku alebo štvrťroka, pretože ukazovatele sú rozdelené do rôznych polí. Nemáte jedno pole predaja, s ktorým by sa dalo pohodlne manipulovať, ale existuje 12 oddelených polí. Budete musieť ručne vytvoriť oddelené vypočítané polia pre štvrťroky a roky, aj keď, všetko v jednom stĺpci, kontingenčná tabuľka urobil by to pre teba.
Ak chcete použiť štandardné súčtové vzorce ako SUMIF, SUMIFS, SUMPRODUCT, tiež zistíte, že s týmto rozložením tabuľky nebudú efektívne fungovať.
Distribúcia informácií na rôznych listoch knihy „pre pohodlie“
Ďalšou bežnou chybou je, že máte nejaký štandardný formát tabuľky a potrebujete na základe týchto údajov analytiku, distribuujte ju do samostatných hárkov zošita programu Excel. Napríklad často vytvárajú samostatné listy pre každý mesiac alebo rok. Výsledkom je, že množstvo práce s analýzou údajov sa skutočne vynásobí počtom vytvorených listov. Nerob to. Zhromažďujte informácie na JEDNOM liste.
Informácie v komentároch
Používatelia často pridávajú dôležitá informácia môžu potrebovať v komentári k bunke. Majte na pamäti, že to, čo je v komentároch, môžete iba pozerať (ak to nájdete). Vytiahnuť ho do cely je náročné. Odporúčam, je lepšie mať samostatný komentár.
Neporiadok vo formátovaní
Rozhodne vám na stôl nepridá nič dobré. Ľuďom, ktorí používajú vaše tabuľky, to pripadá nevhodné. V najlepšom prípade tomu nebudú prikladať dôležitosť, v najhoršom prípade si budú myslieť, že nie ste v obchode organizovaní a nedbalo. Usilujte sa o nasledujúce:
Zlúčenie buniek
Reťazenie buniek používajte iba vtedy, ak sa bez neho nezaobídete. Zlúčené bunky veľmi sťažujú manipuláciu s rozsahmi, v ktorých sa nachádzajú. Problémy nastávajú pri presúvaní buniek, vkladaní buniek atď.
Skombinujte text a čísla do jednej bunky
Bolestivý dojem vytvára bunka obsahujúca číslo, ktorá je vzadu vyplnená textovou konštantou „RUB“. alebo "USD" zadané ručne. Zvlášť ak nejde o tlačenú formu, ale o bežnú tabuľku. Aritmetické operácie s takýmito bunkami sú prirodzene nemožné.
Čísla ako text v bunke
Vyhnite sa ukladaniu číselných údajov do bunky v textovom formáte. Časom budú mať niektoré bunky v takom stĺpci textový formát a niektoré budú mať obyčajný. Z tohto dôvodu budú problémy so vzorcami.
Ak bude váš stôl prezentovaný prostredníctvom LCD projektora
Vyberte najkontrastnejšie kombinácie farieb a pozadia. Na projektore to vyzerá dobre tmavé pozadie a ľahké písmená. Najstrašnejší dojem robí červená na čiernej a naopak. Táto kombinácia vyzerá na projektore extrémne nízkym kontrastom - vyhnite sa tomu.
Pracovný list režimu stránky v Exceli
Je to rovnaký režim, v ktorom Excel ukazuje, ako bude hárok pri tlači stránkovaný. Okraje stránok sú zvýraznené modrou farbou. Neodporúčam neustále pracovať v tomto režime, čo mnohí robia, pretože ovládač tlačiarne sa podieľa na procese zobrazovania údajov na obrazovke, a to v závislosti od mnohých dôvodov (napríklad sieťová tlačiareň nie je v súčasnosti k dispozícii ), je plná zmrznutí v procese vykresľovania a prepočítavania vzorcov. Pracujte ako obvykle.
Ešte užitočnejšie informácie o programe Excel nájdete na stránke
Dosť často údaje z programov tretích strán sa nahrajú do Excelu na ich ďalšie spracovanie a často ďalšie použitie týchto údajov vo vzorcoch poskytuje nepredvídateľný výsledok: čísla nie sú zhrnuté, nie je možné vypočítať počet dní medzi dátumami atď.
Tento článok pojednáva o príčinách takýchto problémov a rôzne cesty ich eliminácia
Dôvod prvý ... Číslo bolo uložené ako text
V tomto prípade môžete vidieť, že čísla alebo dátumy sú stlačené na ľavý okraj bunky (ako text) a spravidla v ľavom hornom rohu bunky je značka chyby (zelený trojuholník) a značka že keď prejdete myšou, vysvetlí to, že číslo je uložené ako text.
Žiadne zmeny vo formáte na číselné, obecné alebo dátum situáciu neopravia, ale ak kliknete na panel vzorcov (alebo stlačíte F2) a potom zadáte kláves Enter, číslo sa zmení na číslo a dátum sa zmení na dátum. Pri veľkom počte takýchto čísel je táto možnosť, ako vidíte, neprijateľná.
Existuje niekoľko spôsobov, ako tento problém vyriešiť.
- Použitie značky chyby a značky... Ak vidíte značku chyby ( zelený trojuholník) a označte, potom vyberte bunky, kliknite na značku a vyberte možnosť Previesť na číslo
- Použitie operácie Hľadať / nahradiť... Predpokladajme, že v tabuľke sú čísla s desatinnou čiarkou uložené ako text. Vyberte rozsah s číslami - kliknite Ctrl + h(alebo nájdete na karte Domov alebo v ponuke Upraviť pre verzie staršie ako 2007 príkaz Vymeňte) - v teréne Nájsť predstaviť , (čiarka) - v poli Nahradené tiež predstavujeme , (čiarka) - Nahradiť všetko... Nahradením čiarky čiarkou teda simulujeme úpravu buniek rovnakým spôsobom F2 - Enter
Podobnú operáciu je možné vykonať s dátumami, s jediným rozdielom, že musíte zmeniť bod za bodom.
Programy tretích strán môžu navyše vykladať čísla s bodkou ako oddeľovač desatinných miest, potom pomôže nahradenie bodky čiarkou.
Podobnú náhradu je možné vykonať pomocou vzorca (pozri nižšie) pomocou funkcie SUBSTITUTE ()
- Použitie Paste Special... Táto metóda je univerzálnejšia, pretože funguje s zlomkovými číslami, celými číslami a dátumami. Vyberte ľubovoľnú prázdnu bunku - spustite príkaz Kopírovať- vyberte rozsah s číslami problémov - Prilepiť špeciálne -- Zložiť-- OK... K číslam (alebo dátumom) teda pripočítame 0, ktoré nijako neovplyvňuje ich hodnotu, ale prevádza sa na číselný formát
Variáciou tejto techniky by mohlo byť vynásobenie rozsahu 1
- Použitie nástroja Text podľa stĺpcov... Túto techniku je vhodné použiť, ak potrebujete transformovať jeden stĺpec, pretože ak existuje niekoľko stĺpcov, kroky sa budú musieť opakovať pre každý stĺpec osobitne. Vyberte teda stĺpec s číslami alebo dátumami uloženými ako text a nastavte formát bunky Generál(pri číslach môžete nastaviť napr. Numerické alebo Finančné). Ďalej vykonáme príkaz Údaje-- Text stĺpca -- Pripravený
- Použitie vzorcov... Ak tabuľka povoľuje ďalšie stĺpce, na konverziu na číslo môžete použiť vzorce. Na konverziu textovej hodnoty na číslo môžete použiť dvojité mínus, sčítanie s nulou, násobenie jednou, funkcie VALUE (), SUBSTITUTE (). Môžete si prečítať podrobnejšie. Po transformácii môže byť výsledný stĺpec skopírovaný a vložený ako hodnoty namiesto pôvodných údajov
- Používaním... V skutočnosti je možné ktorúkoľvek z uvedených metód vykonať s makrom. Ak musíte takúto konverziu vykonávať často, má zmysel napísať makro a spustiť ho podľa potreby.
Tu sú dva príklady makier:
1) vynásobte 1
Podzvuk () Dim c ako rozsah pre každé c vo výbere, ak je číselný (c.hodnota) Potom c.Hodnota = c.Hodnota * 1 c.NumberFormat = "#, ## 0,00" Koniec Ak nasledujúci koniec Sub
2) text podľa stĺpcov
Sub conv1 () Selection.TextToColumns Selection.NumberFormat = "#, ## 0,00" End Sub
Dôvod dva ... Číslo obsahuje cudzie znaky.
Tieto cudzie znaky sú najčastejšie medzery. Môžu byť umiestnené vo vnútri čísla ako oddeľovač číslic, ako aj pred / za číslom. V tomto prípade sa číslo prirodzene zmení na text.
Odložiť extra medzery je možné vykonať aj pomocou operácie Nájsť / nahradiť... V poli Nájsť zadajte medzeru a pole Nahradené nechajte teda prázdne Nahradiť všetko... Ak by v počte boli obyčajné medzery, potom tieto akcie budú stačiť. Číslo však môže obsahovať takzvané medzery (symbol s kódom 160). Takýto priestor bude musieť byť skopírovaný priamo z bunky a potom vložený do poľa Nájsť dialógové okno Nájsť / nahradiť... Alebo môžete v teréne Nájsť stlačte klávesovú skratku Alt + 0160(čísla sa zadávajú na numerickej klávesnici).
Medzery je možné tiež odstrániť pomocou vzorca. Možnosti sú tieto:
Pre bežné priestory: = - NÁHRADA (B4; ""; "")
Pre neporušené medzery:= - NÁHRADA (B4, SYMBOL (160), "")
Ihneď pre tieto a ďalšie priestory: = - NÁHRADA (NÁHRADA (B4; SYMBOL (160); ""); ""; "")
Niekedy, aby ste dosiahli požadovaný výsledok, musíte kombinovať uvedené metódy. Môžete napríklad najskôr odstrániť medzery a potom previesť formát bunky