Како открити грешке у сложеним формулама помоћу интелигентног механизма за отклањање грешака у Екселу

Рад са табелама пуним формула може бити диван... све док нешто не крене по злу и не појави се мистериозан проблем. Грешка у Екселу за коју не знате одакле долази и препознати Најчешће грешке у Excel формуламаКако формуле постају дуже и сложеније, откривање извора проблема оком постаје готово немогуће, и много времена се губи проверавајући ћелију по ћелију.

Са новим интелигентним механизмом за отклањање грешака и алатима за проверу грешака, Excel нуди све више помоћи за проналажење, разумевање и исправљање грешака у сложеним формуламаМеђутим, да бисте из њих извукли максимум, неопходно је савладати неколико основних правила за писање формула, разумети шта значе различите поруке о грешкама и знати како аутоматизовати групне исправке, посебно када подаци увезени из других програма и долазе у разним необичним форматима.

Основни концепти како би се осигурало да формуле не закажу од самог почетка

Пре него што се упустимо у интелигентне системе и напредне дебагере, важно је разумети нека основна правила која спречавају већину грешака. Ексел је прилично строг по питању синтаксе и сваки мали детаљ може изазвати проблеме. формула се тумачи као текст или потпуно не успева.

Прва и најважнија ствар је то Свака формула мора почети знаком једнакости (=)Ако се знак једнакости изостави, Excel не разуме да желите да извршите прорачун, већ да уносите текст или датум. На пример, ако унесете SUM(A1:A10) без знака једнакости, ћелија ће буквално приказати SUM(A1:A10) и неће извршити никакав прорачун. Нешто слично се дешава са датумима: ако унесете 11/2, а формат ћелије је Општи, Excel може приказати 2-nov (2. новембар) уместо да дели 11 са 2, јер тумачи да му дајете датум, а не прорачун. Ово је повезано са Екселов чудан проблем са датумима.

Још једна кључна тачка је правилна употреба заграда. Свака функција која их користи мора имати добро постављена почетна и завршна заградаПриликом рада са угнежђене функцијеВеома је уобичајено да се остави једна додатна или једна недостајућа заграда, што доводи до синтаксичких грешака. Замислите формулу попут =IF(B5<0,"Није валидно",B5*1,05). Ако случајно напишете =IF(B5<0,"Није валидно",B5*1,05)) са додатном затварајућом заградом, формула ће престати да ради јер се број заграда неће подударати. Увек проверавајте да ли постоји исти број отварајућих и затварајућих заграда и да ли су на исправном положају како бисте избегли грешке које је тешко пронаћи.

Такође је важно правилно руковати опсезима. Да бисте референцирали неколико узастопних ћелија, морате користити двотачка (:) између прве и последње ћелијеОпсег се пише, на пример, као A1:A5. Ако изоставите двотачку и напишете нешто попут =SUM(A1 A5), Excel ће ово протумачити као неважеће референце између неповезаних ћелија и вратиће грешку. #НУЛА!, што је онај који се појављује када постоје непостојећи пресеци или недоследне референце.

Коначно, мора се поштовати број и редослед аргумената за сваку функцију. Неке функције захтевају обавезни аргументи и у одређеној позицијиНеким функцијама је потребно више аргумената, док друге дозвољавају опционе параметре. Ако вам понестане аргумената или додате више него што је потребно, Excel ће вас упозорити порукама о грешци које се односе на функцију. Познавање тачне синтаксе сваке функције (број аргумената, очекивани тип података итд.) помаже у минимизирању проблема пре него што се појаве.

Типови аргумената: бројеви, текст и ограничења угнежђивања

Функције програма Excel не захтевају само одређени број аргумената, већ очекују и да им се проследи исправан тип података у сваком аргументуФункција дизајнирана за сабирање бројева није иста као она дизајнирана за манипулацију текстом; ако се замене, резултати могу бити неочекивани или чак произвести грешку.

На пример, функције попут SUM, AVERAGE или PRODUCT захтевају нумеричке аргументе. Ако им проследите текстуалне вредности тамо где очекују бројеве, Excel може вратити 0, игнорисати ћелије или приказати грешку у зависности од контекста. Супротно томе, функције попут REPLACE, CONCAT или RIGHT су дизајниране за рад са текстуалним стринговима. Код њих, барем један од аргумената мора бити текстуална вредност или референца на ћелију која садржи текст; ако присилите број да се третира као текст без одговарајуће обраде, функција се можда неће понашати како се очекује.

Штавише, Excel поставља значајно ограничење на комбиновање функција једне унутар друге. Не можете угнездите више од 64 нивоа функција у једној формулиТо значи да ако имате формулу са IF наредбама унутар IF наредби унутар IF наредби и тако даље, постоји тачка након које Excel неће прихватити више нивоа. Иако у пракси мало људи достиже ове крајности, у веома сложеним или лоше дизајнираним моделима ово ограничење може бити прекорачено, што узрокује грешке које је тешко интерпретирати. Изнад одређеног нивоа сложености, обично је паметније поделити логику у неколико помоћних ћелија него покушавати да се све концентрише у једну, изузетно дугачку формулу; за ово је такође препоручљиво прегледати Узроци и решења за проблеме са перформансама у Екселу.

Типичан пример грешке због типа и броја аргумента је функција ABS. Ова функција прихвата само један нумерички аргумент и враћа његову апсолутну вредност. Ако откуцате нешто попут =ABS(-2;134), Excel ће приказати грешку јер функција не зна шта да ради са два аргумента раздвојена тачком-зарезом. Исправан начин би био нешто попут =ABS(-2134) или =ABS(A1) ако ћелија A1 садржи негативан број који желите да конвертујете у позитиван.

С друге стране, употреба сепаратора мора се узети у обзир у зависности од регионалних подешавања. У многим шпанским инсталацијама програма Excel користи се следеће: тачка-зарез (;) као раздвајач аргумената и зарез (,) за децимални део. Ако формула грешком помеша зарезе и тачка-зарезе на неодговарајућим позицијама, може доћи до додатних синтаксних грешака које компликују читљивост и отклањање грешака у формули.

Укратко, избор правог типа података за прослеђивање свакој функцији, поштовање броја аргумената дефинисаних у њеној синтакси и непрекорачење ограничења угнежђивања су три основна стуба за смањење појаве грешака у софистицираним формулама.

Референце на друге странице и књиге: како избећи грешке при повезивању

Када формуле почну да се повезују са другим листовима у истој радној свесци или чак са спољним радним свескама, прилично је лако направити мале грешке у куцању које на крају генеришу неважеће референтне поруке. Екселу је потребно да Називи листова, књига и рута су написани на веома прецизан начин како би се подаци правилно пронашли.

Ако у формули позивате се на лист чије име садржи размаке или знакове који нису слова (бројеве, цртице, симболе...), то име увек мора бити укључено. у једноструким наводницимаНа пример, ако имате лист под називом Квартални подаци, исправна референца на ћелију D3 на том листу би била ='Квартални подаци'!D3. Слично, ако се лист зове 123, написали бисте ='123'!A1 како би Excel разумео назив листа и не би га помешао са случајним бројем.

Поред тога, кад год формула указује на други лист, цртица мора бити постављена одмах после имена. узвичник (!)Узвичник („```“) означава прелаз између идентификатора листа и референце одређене ћелије. Комплетан пример би био: ='Квартални подаци'!D3. Ако се узвичник изостави или погрешно постави, формула ће бити неважећа и доћи ће до грешака у референцама.

Када се информације налазе у другој радној свесци, Excel-у је потребан додатни контекст. У тим случајевима, спољна референца мора да садржи име датотеке у заградама, име листа и опсегНа пример, ако желите да избројите број редова у опсегу A1:A8 радне свеске Operations T2.xlsx, на листу Sales, можете користити формулу као што је =ROWS('Sales'!A1:A8). Ако датотека није отворена, такође је потребно да у формули наведете пуну путању датотеке, као што је 'C:\My Documents\Sales'!A1:A8.

Типична формула у овом контексту би била нешто попут =ROWS('C:\МојиДокументи\Продаја'!A1:A8). Ова инструкција ће вратити број редова у опсегу A1:A8 у тој другој радној свесци, што би у овом случају било 8. Ако се направи било каква грешка приликом куцања путање, имена радне свеске, имена листа или угластих заграда, резултат ће бити грешка. грешка референце или вредност која се не ажурира јер Excel не може да пронађе спољни извор података.

Такође је препоручљиво пратити шта се дешава када се датотеке које садрже повезане податке преместе, преименују или обришу. Механизам за проверу грешака може открити неке проблеме, али ако екстерна датотека више не постоји на очекиваној локацији, мораћете ручно да ажурирате везе или редефинишете путање да бисте вратили функционалност формуле.

Форматирање бројева у формулама: грешке због симбола и сепаратора

Један од најчешћих проблема у табеларним програмима са пуно података, посебно када долазе из других апликација, јесте употреба Неправилни формати бројева у формуламаЕксел јасно разликује стварну вредност сачувану у ћелији и форматирање које се користи за приказивање те вредности. Збуњивање ова два нивоа често доводи до суптилних грешака.

Бројеви не треба да се форматирају у формулама. То јест, ако је вредност 1000 евра, она мора да се појави у формули као 1000, без симбола €, сепаратора хиљада или зареза. Ако напишете 1.000 или 1,000 (у зависности од регионалних подешавања), Excel ће то вероватно протумачити као сепаратор аргумената или другачију вредност од оне коју сте намеравали. Бројеви се форматирају помоћу валута, сепаратора хиљада или децималних сепаратора. послеКоришћење опција форматирања ћелија, не унутар израза; за више детаља о томе како правилно применити формате, погледајте формат података у програму Excel 365.

Замислите да желите да додате 3100 садржају ћелије А3. Ако инстинктивно откуцате =SUM(3.100,A3) мислећи да мислите на 3100, Excel ће то протумачити као прво сабирање 3 и 100, а затим додавање вредности A3 резултату. Другим речима, израчунаће (3 + 100) + A3, што није исто што и A3 + 3100. Исправна формула би била =SUM(3100,A3), без покушаја представљања формата сепаратора хиљада.

Нешто слично се дешава са функцијама које прихватају само одређени број аргумената, као што је ABS, која прихвата само нумеричку вредност. Ако покушате да напишете зарезе или тачка-зарезе унутар броја да бисте симулирали хиљаде, Excel ће то сматрати неколико одвојених аргумената и функција неће радити. вратиће синтаксичку грешкуСтога, изрази попут =ABS(-2;134) не функционишу, док су =ABS(-2134) валидни.

Ово је посебно важно имати на уму приликом увоза података из рачуноводствених система, ERP-ова или програма за фактурисање. Многи од њих извозе износе са форматирањем, симболима валута, размацима или чак речју „EUR“ на крају. Све ово трансформише оно што би требало да буде нумеричка вредност у текстуални стринг који Excel не може директно да користи у својим прорачунима, што доводи до грешака у куцању, празних резултата или збира који се не сабирају.

Најбоља пракса је да се сачувани подаци чувају као сирови бројеви у ћелијама без посебног форматирања Затим примените визуелно форматирање за раздвајање валута, процента или хиљада из менија за форматирање. Ово спречава да симболи ометају прорачун и омогућава механизму за грешке да боље детектује недоследности.

Интелигентна провера грешака и отклањање грешака у Екселу

Поред исправљања синтаксе, Ексел укључује и систем провера грешака која анализира формуле и предлаже исправке Када открије нешто необично, овај све интелигентнији механизам помаже у проналажењу недостајућих референци, недоследности и проблема са типом у сложеним формулама, смањујући време потребно за проналажење извора грешке.

У десктоп верзији програма Excel, провери грешака може се приступити са картице Формуле, у групи Уређивање формула, где се појављује опција Провера грешака. Овај чаробњак скенира ћелије са упозорењима и приказује поруке са могућим узроцима проблема, као и предлаже одређене промене које се могу применити једним кликом. Користан је за откривање недоследних опсега, формула које се нису ажурирале приликом превлачења, референци на празне ћелије или недоследности у укупним износима.

Међутим, у програму Excel Online (веб верзија), тренутно није могуће конфигурисати или користити ова напредна правила за проверу грешака на исти начин. Клауд сервис нуди основне функције, али не укључује комплетан скуп правила за преглед формула Доступно на десктопу. Стога, када радите са веома сложеним радним свескама, најпрактичније је да их отворите помоћу десктоп апликације како бисте у потпуности искористили предности механизма за отклањање грешака.

Ако имате верзију за рачунаре, можете користити дугме „Отвори помоћу програма Excel“ у програму Excel Online да бисте покренули радну свеску у пуној апликацији. Када сте тамо, можете извршити све потребне провере да бисте открили потенцијалне грешке у формулама, активирали одређена правила и прегледали сва упозорења која систем пружа. То је ефикасан начин да комбинујете погодност рада на мрежи са снагом десктоп апликације.

Да бисте били у току са новим могућностима система за верификацију и побољшањима паметних алата за отклањање грешака, препоручљиво је да повремено проверавате документацију. Званични блог Мицрософт Екцел-аОвде се објављују вести, ажурирања и измене десктоп и онлајн верзија, што вам помаже да знате када су доступне нове функције за исправљање грешака.

Ако вам је потребан свеобухватнији приступ свим Office апликацијама (Word, Excel, PowerPoint, итд.) и њиховим повезаним услугама, увек постоји могућност да испробате или купите комплетан пакет преко Office.com-а, што вам осигурава најнапредније алате за проверу грешака за све ваше табеле.

Грешке изазване бројевима сачуваним као текст и како их исправити у већем броју

Веома чест сценарио, посебно у рачуноводственом или финансијском окружењу, настаје приликом извоза детаљне главне књиге (детаљне ГК) из софтвера за управљање. У многим случајевима, ове врсте апликација Они извозе нумеричке износе као текстРезултат је да, када се датотека отвори у Екселу, формуле које покушавају да саберу, одузму или анализирају та поља не препознају вредности као бројеве, а операције не успевају или производе нетачне резултате.

Уграђена провера грешака у Екселу може да открије овај проблем и прикаже класични зелени троугао у углу ћелија, што указује да је „број сачуван као текст“. Кликом на икону упозорења, Ексел добија опцију да конвертује текст у број. Мана је што се, подразумевано, ако користите алатку Провера грешака са картице Формуле > Уређивање формула > Провера грешака, корекција обично примењује. ћелија по ћелијишто у GL-у са хиљадама редова може бити излуђујуће.

Штавише, ако је цео документ изабран, провера грешака се не фокусира искључиво на нумеричке колоне, јер и друге колоне које садрже датуме, текст и описе могу покренути упозорења, што додатно компликује процес групне корекције. Логично питање у овом контексту је да ли постоји начин да се изабере цео лист и да Excel исправи све ове грешке тако што ће текст претворити у стварне бројеве одједном.

Постоји неколико стратегија које се могу применити директно из програма Excel за рачунаре како би се решиле ове врсте ситуација. Једна од најдиректнијих укључује коришћење специјални лепак са математичком операцијомНа пример, можете укуцати број 1 у празну ћелију, копирати ту ћелију, изабрати опсег ћелија које садрже бројеве сачуване као текст (на пример, целу колону са износима) и користити функцију „Специјално лепљење“ > „Множење“. Ова операција доводи до тога да Excel тумачи сваки унос „текста“ као број, помножи га са 1 и сачува резултат као реалну нумеричку вредност, чувајући исти износ. То је веома ефикасан начин за групно конвертовање великих количина података.

Друга алтернатива је коришћење функције VALUE у помоћној колони. Ако су, на пример, износи текста у колони B, можете креирати формулу као што је =VALUE(B2) у колони C, копирати је, а затим копирати и налепити резултате као вредности у оригиналну колону, замењујући текст. Ова техника вам такође омогућава да визуелно проверите да ли се све подудара пре него што избришете оригиналне податке.

У неким случајевима, упозорења „број сачуван као текст“ вам омогућавају да изаберете шири опсег и примените конверзију на више ћелија одједном из контекстног менија малог зеленог троугла. Међутим, њихова ефикасност зависи од тога како су подаци генерисани и да ли Excel детектује све случајеве као исту врсту грешке.

Приликом рада са великим количинама рачуноводствених података, препоручљиво је прегледати и регионална подешавања програма Excel и формат извоза изворног програма. Подешавање ових параметара како би се осигурала компатибилност децималних сепаратора, симбола валута и формата датума значајно смањује појаву бројева у текстуалном формату и широко распрострањених грешака, и омогућава формулама да правилно функционишу од самог почетка.

Укратко, иако стандардна провера грешака има тенденцију да ради ред по ред, комбиновање њених упозорења са техникама као што су посебне функције лепљења и конверзије је могуће. да се одједном исправе велики блокови погрешно форматираних података и вратити њихов статус као бројева без потребе да иду ћелију по ћелију.

Читав овај скуп препорука у вези са знаком једнакости, заградама, опсезима, типовима аргумената, форматирањем бројева, спољним референцама и алатима за проверу грешака дели заједнички циљ: да омогући новом интелигентном механизму за отклањање грешака у Екселу да ради са максималном ефикасношћу. Када су формуле добро конструисане и подаци користе исправан тип и формат, систем упозорења и предлога постаје моћан савезник за брзо проналажење недоследности, неисправних референци или проблематичних ћелија, чак и у радним листовима са веома сложеним формулама и подацима увезеним из више извора.

Повезани чланак:

Најчешће грешке у формулама у Екселу и како их исправити

Исак

Страствени писац о свету бајтова и технологије уопште. Волим да делим своје знање кроз писање, и то је оно што ћу радити на овом блогу, показивати вам све најзанимљивије ствари о гаџетима, софтверу, хардверу, технолошким трендовима и још много тога. Мој циљ је да вам помогнем да се крећете у дигиталном свету на једноставан и забаван начин.