Ako zistiť chyby v zložitých vzorcoch pomocou inteligentného ladiaceho nástroja programu Excel

Práca s tabuľkami plnými vzorcov môže byť úžasná… až kým sa niečo nepokazí a neobjaví sa záhadný problém. Chyba v Exceli, o ktorej neviete, odkiaľ pochádza a rozpoznať Najčastejšie chyby vo vzorcoch programu ExcelKeďže vzorce sú dlhšie a zložitejšie, odhalenie zdroja problému okom sa stáva takmer nemožným a veľa času sa stráca kontrolou bunka po bunke.

Vďaka novému inteligentnému ladiacemu nástroju a nástrojom na kontrolu chýb ponúka Excel čoraz viac pomoci pre lokalizácia, pochopenie a oprava chýb v zložitých vzorcochAby ste z nich však vyťažili maximum, je nevyhnutné ovládať niekoľko základných pravidiel písania vzorcov, pochopiť význam rôznych chybových hlásení a vedieť, ako automatizovať hromadné opravy, najmä keď údaje importované z iných programov a prichádzajú v rôznych nezvyčajných formátoch.

Základné koncepty, ktoré zabezpečia, že vzorce nezlyhajú od začiatku

Predtým, ako sa ponoríme do inteligentných nástrojov a pokročilých ladiacich nástrojov, je nevyhnutné pochopiť niektoré základné pravidlá, ktoré predchádzajú väčšine chýb. Excel je dosť prísny, čo sa týka syntaxe, a akýkoľvek malý detail môže spôsobiť problémy. Vzorec sa interpretuje ako text alebo úplne zlyhá.

Prvá a najdôležitejšia vec je, že Každý vzorec musí začínať znamienkom rovnosti (=)Ak sa znamienko rovnosti vynechá, Excel nepochopí, že chcete vykonať výpočet, ale že zadávate text alebo dátum. Napríklad, ak zadáte SUM(A1:A10) bez znamienka rovnosti, bunka doslova zobrazí SUM(A1:A10) a nevykoná žiadny výpočet. Niečo podobné sa stane s dátumami: ak zadáte 11/2 a formát bunky je Všeobecný, Excel môže zobraziť 2-nov (2. novembra) namiesto delenia 11 číslom 2, pretože interpretuje, že zadávate dátum a nie výpočet. Súvisí to s... Zvláštna chyba dátumu v Exceli.

Ďalším kľúčovým bodom je správne používanie zátvoriek. Každá funkcia, ktorá ich používa, musí mať správne umiestnená otváracia a uzatváracia zátvorkaPri práci s vnorené funkcieVeľmi bežne sa vynecháva jedna zátvorka navyše alebo jedna chýbajúca, čo spôsobuje syntaktické chyby. Predstavte si vzorec ako =IF(B5<0;"Neplatné";B5*1,05). Ak omylom napíšete =IF(B5<0;"Neplatné";B5*1,05)) s ďalšou uzatváracou zátvorkou, vzorec prestane fungovať, pretože počet zátvoriek sa nebude zhodovať. Vždy je dôležité skontrolovať, či je počet otváracích a uzatváracích zátvoriek rovnaký a či sú na správnej pozícii, aby sa predišlo chybám, ktoré sa ťažko hľadajú.

Je tiež dôležité správne pracovať s rozsahmi. Ak chcete odkazovať na niekoľko po sebe idúcich buniek, musíte použiť dvojbodka (:) medzi prvou a poslednou bunkouRozsah sa zapisuje napríklad ako A1:A5. Ak vynecháte dvojbodku a dostanete niečo ako =SUM(A1 A5), Excel to interpretuje ako neplatné odkazy medzi nesúvisiacimi bunkami a vráti chybu. #NULOVÝ!, ktorý sa zobrazí, keď existujú neexistujúce priesečníky alebo nekonzistentné referencie.

Nakoniec sa musí rešpektovať počet a poradie argumentov každej funkcie. Niektoré funkcie vyžadujú povinné argumenty a v špecifickej pozíciiNiektoré funkcie vyžadujú viacero argumentov, zatiaľ čo iné umožňujú voliteľné parametre. Ak vám dôjdu argumenty alebo pridáte viac, ako je potrebné, Excel vás upozorní chybovými hláseniami súvisiacimi s funkciou. Znalosť presnej syntaxe každej funkcie (počet argumentov, očakávaný dátový typ atď.) pomáha minimalizovať problémy skôr, ako vzniknú.

Typy argumentov: čísla, text a limity vnorenia

Funkcie programu Excel nielenže vyžadujú určitý počet argumentov, ale očakávajú aj odovzdanie... správny dátový typ v každom argumenteFunkcia určená na sčítanie čísel nie je to isté ako funkcia určená na manipuláciu s textom; ak sa zamenia, výsledky môžu byť neočakávané alebo dokonca spôsobiť chybu.

Napríklad funkcie ako SUM, AVERAGE alebo PRODUCT vyžadujú číselné argumenty. Ak im predáte textové hodnoty tam, kde očakávajú čísla, Excel môže vrátiť 0, ignorovať bunky alebo zobraziť chybu v závislosti od kontextu. Naopak, funkcie ako REPLACE, CONCAT alebo RIGHT sú navrhnuté tak, aby pracovali s textovými reťazcami. V nich musí byť aspoň jeden z argumentov textová hodnota alebo odkaz na bunku obsahujúcu text; ak vynútite, aby sa číslo považovalo za text bez správneho spracovania, funkcia sa nemusí správať podľa očakávania.

Okrem toho, Excel kladie značné obmedzenie na kombinovanie funkcií v rámci seba. Nemôžete vnoriť viac ako 64 úrovní funkcií do jedného vzorcaTo znamená, že ak máte vzorec s príkazmi IF vo vnútri príkazov IF vo vnútri príkazov IF atď., existuje bod, za ktorým Excel neakceptuje žiadne ďalšie úrovne. Hoci v praxi len málo ľudí dosiahne tieto extrémy, vo veľmi zložitých alebo zle navrhnutých modeloch môže byť tento limit prekročený, čo spôsobuje chyby, ktoré je ťažké interpretovať. Za určitou úrovňou zložitosti je zvyčajne múdrejšie rozdeliť logiku do niekoľkých pomocných buniek, než sa snažiť všetko sústrediť do jedného, ​​extrémne dlhého vzorca; na tento účel je tiež vhodné skontrolovať Príčiny a riešenia problémov s výkonom v Exceli.

Typickým príkladom chyby spôsobenej typom a číslom argumentu je funkcia ABS. Táto funkcia akceptuje iba jeden číselný argument a vráti jeho absolútnu hodnotu. Ak zadáte niečo ako =ABS(-2;134), Excel zobrazí chybu, pretože funkcia nevie, čo má robiť s dvoma argumentmi oddelenými bodkočiarkou. Správny spôsob by bol niečo ako =ABS(-2134) alebo =ABS(A1), ak bunka A1 obsahuje záporné číslo, ktoré chcete previesť na kladné.

Na druhej strane, použitie oddeľovačov sa musí zohľadniť v závislosti od regionálnych nastavení. V mnohých španielskych inštaláciách Excelu sa používa nasledovné: bodkočiarka (;) ako oddeľovač argumentov a čiarka (,) pre desatinnú časť. Ak vzorec omylom zmieša čiarky a bodkočiarky na nevhodných pozíciách, môže to vygenerovať ďalšie syntaktické chyby, ktoré komplikujú čitateľnosť a ladenie vzorca.

Stručne povedané, výber správneho dátového typu, ktorý sa má odovzdať každej funkcii, rešpektovanie počtu argumentov definovaných v jej syntaxi a neprekračovanie limitov vnorenia sú tri základné piliere na zníženie výskytu chýb v sofistikovaných vzorcoch.

Odkazy na iné stránky a knihy: ako sa vyhnúť chybám pri prepájaní

Keď vzorce začnú odkazovať na iné hárky v tom istom zošite alebo dokonca na externé zošity, je celkom ľahké urobiť malé preklepy, ktoré nakoniec vygenerujú neplatné referenčné správy. Excel od vás potrebuje Názvy listov, kníh a trás sú napísané veľmi presným spôsobom aby sa údaje správne našli.

Ak vo vzorci odkazujete na hárok, ktorého názov obsahuje medzery alebo znaky iné ako písmená (čísla, pomlčky, symboly…), tento názov musí byť vždy uvedený. v jednoduchých úvodzovkáchNapríklad, ak máte hárok s názvom Štvrťročné údaje, správny odkaz na bunku D3 v tomto hárku by bol ='Štvrťročné údaje'!D3. Podobne, ak sa hárok nazýva 123, napíšete ='123'!A1, aby Excel rozumel názvu hárka a nepomýlil si ho s náhodným číslom.

Okrem toho, vždy keď vzorec odkazuje na iný hárok, musí sa za jeho názov umiestniť pomlčka. výkričník (!)Výkričník („```“) označuje prechod medzi identifikátorom hárka a konkrétnym odkazom na bunku. Úplný príklad by bol: ='Štvrťročné údaje'!D3. Ak je výkričník vynechaný alebo nesprávne umiestnený, vzorec bude neplatný a vyskytnú sa chyby v odkazoch.

Keď sa informácie nachádzajú v inom zošite, Excel potrebuje viac kontextu. V týchto prípadoch musí externý odkaz obsahovať názov súboru v zátvorkách, názov hárka a rozsahNapríklad, ak chcete spočítať počet riadkov v rozsahu A1:A8 zošita Operations T2.xlsx na hárku Predaj, môžete použiť vzorec ako =ROWS('Predaj'!A1:A8). Ak súbor nie je otvorený, musíte vo vzorci zadať aj úplnú cestu k súboru, napríklad 'C:\Moje dokumenty\Predaj'!A1:A8.

Typický vzorec v tomto kontexte by bol niečo ako =ROWS('C:\Moje dokumenty\Predaj'!A1:A8). Táto inštrukcia vráti počet riadkov v rozsahu A1:A8 v danom druhom zošite, čo by v tomto prípade bolo 8. Ak sa pri zadávaní cesty, názvu zošita, názvu hárka alebo hranatých zátvoriek urobí chyba, výsledkom bude chyba. chyba odkazu alebo hodnota, ktorá sa neaktualizuje pretože Excel nedokáže nájsť externý zdroj údajov.

Taktiež je vhodné sledovať, čo sa stane, keď sa súbory obsahujúce prepojené údaje presunú, premenujú alebo odstránia. Modul kontroly chýb môže zistiť niektoré problémy, ale ak externý súbor už neexistuje v očakávanom umiestnení, budete musieť manuálne aktualizovať prepojenia alebo predefinovať cesty, aby sa obnovila funkčnosť vzorca.

Formátovanie čísel vo vzorcoch: chyby spôsobené symbolmi a oddeľovačmi

Jedným z najčastejších problémov v tabuľkových procesoroch s veľkým množstvom údajov, najmä ak pochádzajú z iných aplikácií, je použitie Nesprávne formáty čísel vo vzorcochExcel jasne rozlišuje medzi skutočnou hodnotou uloženou v bunke a formátovaním použitým na zobrazenie tejto hodnoty. Zamieňanie týchto dvoch úrovní často vedie k jemným chybám.

Čísla by sa nemali vo vzorcoch formátovať. To znamená, že ak je hodnota 1000 eur, musí sa vo vzorci zobraziť ako 1000 bez symbolu €, oddeľovačov tisícov alebo čiarok. Ak napíšete 1.000 alebo 1,000 (v závislosti od miestnych nastavení), Excel to pravdepodobne interpretuje ako oddeľovač argumentov alebo inú hodnotu, ako ste zamýšľali. Čísla sa formátujú pomocou meny, oddeľovačov tisícov alebo desatinných oddeľovačov. poPoužitie možností formátovania buniek, nie vo výraze; ďalšie podrobnosti o správnom použití formátov nájdete v časti formát údajov v Exceli 365.

Predstavte si, že chcete k obsahu bunky A3 pripočítať číslo 3100. Ak inštinktívne napíšete =SUM(3;100;A3) s domnienkou, že máte na mysli 3.100, Excel to interpretuje ako najprv pripočítanie 3 a 100 a potom pripočítanie hodnoty bunky A3 k výsledku. Inými slovami, vypočíta (3 + 100) + A3, čo nie je to isté ako A3 + 3100. Správny vzorec by bol =SUM(3100;A3) bez pokusu o reprezentáciu formátu oddeľovača tisícov.

Niečo podobné sa deje s funkciami, ktoré akceptujú iba určitý počet argumentov, ako napríklad ABS, ktorá akceptuje iba číselnú hodnotu. Ak sa pokúsite napísať čiarky alebo bodkočiarky do čísla, aby ste simulovali tisíce, Excel to bude považovať za niekoľko samostatných argumentov a funkcia nebude fungovať. vráti syntaktickú chybuPreto výrazy ako =ABS(-2;134) nefungujú, zatiaľ čo =ABS(-2134) sú platné.

Toto je obzvlášť dôležité mať na pamäti pri importe údajov z účtovných systémov, ERP alebo fakturačných programov. Mnohé z nich exportujú sumy s formátovaním, symbolmi mien, medzerami alebo dokonca slovom „EUR“ na konci. Toto všetko transformuje to, čo by mala byť číselná hodnota, na textový reťazec, ktorý Excel nemôže priamo použiť vo svojich výpočtoch, čo vedie k chybám v prepise, prázdnym výsledkom alebo súčtom, ktoré sa nesčítavajú.

Najlepším postupom je uchovávať uložené údaje tak, ako sú surové čísla v bunkách bez špeciálneho formátovania Potom z ponuky formátovania použite vizuálne formátovanie pre oddeľovač meny, percent alebo tisícov. Tým sa zabráni tomu, aby symboly rušili výpočet, a umožní sa chybovému mechanizmu lepšie odhaliť nezrovnalosti.

Inteligentná kontrola a ladenie chýb v Exceli

Okrem opravy syntaxe obsahuje Excel aj systém kontrola chýb, ktorá analyzuje vzorce a navrhuje opravy Keď tento čoraz inteligentnejší nástroj zistí niečo nezvyčajné, pomôže nájsť chýbajúce odkazy, nekonzistentnosti a typové problémy v zložitých vzorcoch, čím sa skracuje čas potrebný na nájdenie zdroja chyby.

V desktopovej verzii programu Excel je kontrola chýb dostupná na karte Vzorce v skupine Úprava vzorcov, kde sa zobrazuje možnosť Kontrola chýb. Tento sprievodca prehľadáva bunky s upozorneniami a zobrazuje správy s možnými príčinami problému, ako aj navrhuje konkrétne zmeny, ktoré je možné vykonať jedným kliknutím. Je užitočný na detekciu nekonzistentných rozsahov, vzorcov, ktoré sa neaktualizovali po presunutí myšou, odkazov na prázdne bunky alebo nekonzistentností v súčtoch.

V Excel Online (webová verzia) však momentálne nie je možné konfigurovať ani používať tieto rozšírené pravidlá kontroly chýb rovnakým spôsobom. Cloudová služba ponúka základné funkcie, ale nezahŕňa kompletný súbor pravidiel kontroly vzorcov Dostupné na počítači. Preto je pri práci s veľmi zložitými zošitmi najpraktickejšie otvoriť ich pomocou desktopovej aplikácie, aby ste naplno využili ladiaci nástroj.

Ak máte desktopovú verziu, môžete použiť tlačidlo „Otvoriť v Exceli“ v Exceli Online na spustenie zošita v plnej aplikácii. V tejto aplikácii môžete vykonať všetky potrebné kontroly na zistenie potenciálnych chýb vo vzorcoch, aktivovať konkrétne pravidlá a skontrolovať všetky upozornenia, ktoré systém poskytuje. Je to efektívny spôsob, ako spojiť pohodlie práce online s výkonom desktopovej aplikácie.

Aby ste zostali informovaní o nových možnostiach overovacieho nástroja a vylepšeniach nástrojov na inteligentné ladenie, odporúča sa občas skontrolovať dokumentáciu. Oficiálny blog programu Microsoft ExcelTu sa zverejňujú novinky, aktualizácie a zmeny v desktopovej a online verzii, čo vám pomôže zistiť, kedy sú k dispozícii nové funkcie na opravu chýb.

Ak potrebujete komplexnejší prístup ku všetkým aplikáciám balíka Office (Word, Excel, PowerPoint atď.) a súvisiacim službám, môžete si kedykoľvek vyskúšať alebo zakúpiť celý balík prostredníctvom stránky Office.com, čím si zabezpečíte najpokročilejšie nástroje na kontrolu chýb pre všetky vaše tabuľky.

Chyby spôsobené číslami uloženými ako text a ako ich hromadne opraviť

Veľmi častý scenár, najmä v účtovnom alebo finančnom prostredí, vzniká pri exporte podrobnej hlavnej účtovnej knihy (podrobnej hlavnej účtovnej knihy) zo softvéru na správu. V mnohých prípadoch tieto typy aplikácií Exportujú číselné sumy ako textVýsledkom je, že pri otvorení súboru v Exceli vzorce, ktoré sa pokúšajú sčítať, odčítať alebo analyzovať tieto polia, nerozpoznávajú hodnoty ako čísla a operácie zlyhajú alebo vygenerujú nesprávne výsledky.

Vstavaná kontrola chýb v Exceli dokáže tento problém odhaliť a zobraziť klasický zelený trojuholník v rohu buniek, ktorý označuje, že „číslo je uložené ako text“. Kliknutím na ikonu upozornenia získate v Exceli možnosť previesť text na číslo. Nevýhodou je, že ak použijete nástroj Kontrola chýb z karty Vzorce > Úprava vzorcov > Kontrola chýb, oprava sa zvyčajne použije. bunka po bunkečo v GL s tisíckami riadkov môže byť šialené.

Okrem toho, ak je vybratý celý dokument, kontrola chýb sa nezameriava výlučne na číselné stĺpce, pretože aj iné stĺpce obsahujúce dátumy, text a popisy môžu spúšťať upozornenia, čo ďalej komplikuje proces hromadnej opravy. Logickou otázkou v tejto súvislosti je, či existuje spôsob, ako vybrať celý hárok a nechať Excel opraviť všetky tieto chyby jedným ťahom konverzie textu na skutočné čísla.

Existuje niekoľko stratégií, ktoré možno priamo použiť z desktopového programu Excel na riešenie týchto typov situácií. Jednou z najpriamejších je použitie špeciálne lepidlo s matematickou operáciouNapríklad môžete zadať číslo 1 do prázdnej bunky, skopírovať túto bunku, vybrať rozsah buniek obsahujúcich čísla uložené ako text (napríklad celý stĺpec s číslami) a použiť príkaz Vložiť špeciálne > Násobiť. Táto operácia spôsobí, že Excel interpretuje každú položku „textu“ ako číslo, vynásobí ju číslom 1 a výsledok uloží ako reálnu číselnú hodnotu, pričom zachová rovnakú hodnotu. Je to veľmi efektívny spôsob hromadnej konverzie veľkého množstva údajov.

Ďalšou alternatívou je použiť funkciu VALUE v pomocnom stĺpci. Ak sú napríklad textové sumy v stĺpci B, môžete v stĺpci C vytvoriť vzorec ako =VALUE(B2), skopírovať ho a potom skopírovať a vložiť výsledky ako hodnoty do pôvodného stĺpca, čím nahradíte text. Táto technika vám tiež umožňuje vizuálne overiť, či sa všetko zhoduje pred odstránením pôvodných údajov.

V niektorých prípadoch vám upozornenia „číslo uložené ako text“ umožňujú vybrať širší rozsah a použiť konverziu na viacero buniek naraz z kontextovej ponuky malého zeleného trojuholníka. Ich účinnosť však závisí od spôsobu generovania údajov a od toho, či Excel detekuje všetky prípady ako rovnaký typ chyby.

Pri práci s veľkými objemami účtovných údajov je tiež vhodné skontrolovať regionálne nastavenia programu Excel a formát exportu zdrojového programu. Úprava týchto parametrov s cieľom zabezpečiť kompatibilitu desatinných oddeľovačov, symbolov mien a formátov dátumu výrazne znižuje výskyt čísel v textovom formáte a rozsiahlych chýb a umožňuje, aby vzorce fungovali správne od začiatku.

Stručne povedané, hoci štandardná kontrola chýb má tendenciu pracovať riadok po riadku, je možné kombinovať jej varovania s technikami, ako sú špeciálne funkcie vkladania a konverzie. hromadne opraviť veľké bloky chybne formátovaných údajov a obnoviť ich stav ako čísel bez toho, aby ste museli prechádzať bunku po bunke.

Celá táto sada odporúčaní týkajúcich sa znamienka rovnosti, zátvoriek, rozsahov, typov argumentov, formátovania čísel, externých odkazov a nástrojov na kontrolu chýb má spoločný cieľ: umožniť novému inteligentnému ladiacemu nástroju programu Excel pracovať s maximálnou efektivitou. Keď sú vzorce dobre zostavené a údaje používajú správny typ a formát, systém upozornení a návrhov sa stáva silným spojencom na rýchle vyhľadávanie nezrovnalostí, poškodených odkazov alebo problematických buniek, a to aj v pracovných hárkoch s veľmi zložitými vzorcami a údajmi importovanými z viacerých zdrojov.

Súvisiaci článok:

Najčastejšie chyby vo vzorcoch programu Excel a ako ich opraviť

Isaac

Vášnivý spisovateľ o svete bajtov a technológií všeobecne. Milujem zdieľanie svojich vedomostí prostredníctvom písania, a to je to, čo urobím v tomto blogu, ukážem vám všetko najzaujímavejšie o gadgetoch, softvéri, hardvéri, technologických trendoch a ďalších. Mojím cieľom je pomôcť vám orientovať sa v digitálnom svete jednoduchým a zábavným spôsobom.