Проблеми с изчисляването на формули в Microsoft Excel. Режим на страница на работен лист в Excel. Информация в коментарите

Една от най -търсените функции на Excel е работата с формули. Благодарение на тази функция програмата независимо извършва различни видове изчисления в таблици. Но понякога се случва потребителят да въведе формула в клетка, но тя не изпълнява пряката си цел - да изчисли резултата. Нека да разберем с какво може да е свързано това и как да решим този проблем.

Причините за проблеми с изчисляването на формули в Excel могат да бъдат напълно различни. Те могат да бъдат причинени както от настройките на определена книга или дори от отделен диапазон от клетки, така и от различни грешки в синтаксиса.

Метод 1: променете формата на клетките

Една от най -честите причини, поради които Excel изобщо не изчислява или не изчислява правилно формулите, е неправилно зададеният формат на клетките. Ако диапазонът има текстов формат, тогава изчисляването на изрази в него изобщо не се извършва, тоест те се показват като обикновен текст. В други случаи, ако форматът не съответства на същността на изчислените данни, резултатът, показан в клетката, може да се покаже неправилно. Нека да разберем как да решим този проблем.



Сега формулата ще бъде изчислена в стандартен ред с изхода на резултата в посочената клетка.


Метод 2: изключете режима „Показване на формули“

Но може би причината, че вместо резултатите от изчисленията показвате изрази, е, че програмата е активирала режима „Показване на формули“.




Метод 3: Поправете синтаксична грешка

Формулата може да се показва и като текст, ако са допуснати грешки в нейния синтаксис, например, буква е липсвала или е променена. Ако сте го въвели ръчно, а не през Съветник за функции, тогава това е доста вероятно. Много често срещана грешка при показване на израз като текст е наличието на интервал преди знака «=» .


В такива случаи трябва внимателно да прегледате синтаксиса на тези формули, които се показват неправилно, и да направите съответните корекции.

Метод 4: активирайте преизчисляването на формулата

Също така се случва формулата да показва стойността, но когато клетките, свързани с нея, се променят, тя не се променя сама, тоест резултатът не се преизчислява. Това означава, че сте неправилно конфигурирали изчислителните параметри в тази книга.




Всички изрази в тази работна книга сега ще бъдат автоматично преизчислени всеки път, когато някоя свързана стойност се промени.

Метод 5: грешка във формулата

Ако програмата все още изчислява, но в резултат показва грешка, тогава е вероятно потребителят просто да е направил грешка при въвеждане на израза. Погрешни формули са тези, при изчисляване на които следните стойности се появяват в клетката:

  • #НОМЕР !;
  • #ЦЕННОСТ!;
  • # ПРАЗНО!;
  • # DIV / 0!;
  • # Н / А

В този случай трябва да проверите дали данните в клетките, посочени от израза, са правилно записани, дали има някакви синтаксични грешки или дали самата формула съдържа някакво неправилно действие (например разделяне на 0).


Ако функцията е сложна, с голям брой свързани клетки, е по -лесно да се проследят изчисленията с помощта на специален инструмент.




Както можете да видите, причините, поради които Excel не взема предвид или не изчислява правилно формулите, могат да бъдат напълно различни. Ако вместо да изчислява, потребителят показва самата функция, тогава в този случай най -вероятно или клетката е форматирана за текст, или режимът на изглед на израз е включен. Възможна е и синтаксична грешка (например наличието на интервал преди знака «=» ). Ако след промяна на данните в свързаните клетки резултатът не се актуализира, тогава трябва да видите как е настроено автоматичното актуализиране в параметрите на книгата. Също така доста често вместо правилния резултат в клетката се показва грешка. Тук трябва да видите всички стойности, към които се отнася функцията. Ако се установи грешка, тя трябва да бъде отстранена.

Читателите на Lifehacker вече са запознати Денис Батяновкойто сподели с нас. Днес Денис ще говори за това как да избегнем най -често срещаните проблеми с Excel, които често създаваме за себе си.

Веднага ще направя резервация, че материалът за статията е предназначен за начинаещи потребители на Excel. Опитните потребители вече са танцували запалително на този рейк повече от веднъж, така че моята задача е да защитя младите и неопитни „танцьори“ от това.

Не давате заглавия на колони в таблица

Много инструменти на Excel, като сортиране, филтриране, интелигентни таблици, обобщени таблици, приемат, че вашите данни съдържат заглавки на колони. В противен случай или няма да можете да ги използвате, или те няма да работят съвсем правилно. Винаги се уверете, че вашите таблици съдържат заглавия на колони.

Празни колони и редове във вашите таблици

Това е объркващо за Excel. Когато срещнете празен ред или колона в таблицата си, той започва да мисли, че имате 2 таблици, а не една. Ще трябва постоянно да го коригирате. Също така, не скривайте редовете / колоните, които не ви трябват, в таблицата, по -добре е да ги изтриете.

Няколко маси са разположени на един лист

Ако това не са малки таблици, съдържащи референтни стойности, не трябва да правите това.

Ще бъде неудобно да работите напълно с повече от една маса на лист. Например, ако една таблица е отляво, а втората отдясно, тогава филтрирането върху едната маса ще повлияе на другата. Ако таблиците са разположени една под друга, тогава е невъзможно да се използва фиксирането на области и една от таблиците ще трябва постоянно да търси и да извършва ненужни манипулации, за да застане върху нея с курсор на таблица. Имате ли нужда от него?

Данните от същия тип са изкуствено подредени в различни колони

Много често потребителите, които познават Excel доста повърхностно, предпочитат този формат на таблицата:

Изглежда, че сме изправени пред безобиден формат за натрупване на информация за продажбите на агенти и техните глоби. Такова оформление на таблицата се възприема добре визуално от човек, тъй като е компактно. Повярвайте ми, обаче е истински кошмар да се опитате да извлечете данни от такива таблици и да получите междинни суми (обобщена информация).

Факт е, че този формат съдържа 2 измерения: така че трябва да вземете решение за ред, да сортирате клон, група и агент. Когато намерите правилния запас, тогава ще трябва да потърсите необходимата колона, тъй като тук има много от тях. И тази „двуизмерност“ значително усложнява работата с такава таблица за стандартни инструменти на Excel - формули и обобщени таблици.

Ако изградите обобщена таблица, ще откриете, че няма начин лесно да получите данните по година или тримесечие, тъй като индикаторите са разделени на различни полета. Нямате едно поле за продажби, с което можете удобно да манипулирате, но има 12 отделни полета. Ще трябва да създадете на ръка отделни изчислени полета за тримесечия и години, въпреки че, било то всичко в една колона, обобщена таблицаби го направил вместо теб.

Ако искате да приложите стандартни формули за сумиране като SUMIF, SUMIFS, SUMPRODUCT, ще откриете също, че те няма да работят ефективно с това оформление на таблицата.

Разпространение на информация на различни листове на книгата „за удобство“

Друга често срещана грешка е, че имате някакъв стандартен формат на таблицата и се нуждаете от анализи на базата на тези данни, разпределете ги в отделни листове на работна книга на Excel. Например, те често създават отделни листове за всеки месец или година. В резултат на това количеството работа по анализ на данните всъщност се умножава по броя на създадените листове. Не правете това. Съберете информация на ЕДИН лист.

Информация в коментарите

Често потребителите добавят важна информацияможе да се нуждаят от коментар в клетката. Имайте предвид, че това, което е в коментарите, можете да разгледате само (ако го намерите). Издърпването му в килията е трудно. Препоръчвам по -добре да имате отделна колона за коментари.

Каша с форматирането

Определено няма да добави нищо добро към вашата маса. Това изглежда отблъскващо за хората, използващи вашите електронни таблици. В най -добрия случай те няма да придадат значение на това, в най -лошия - ще си помислят, че не сте организирани и небрежни в бизнеса. Стремете се към следното:

Сливане на клетки

Използвайте конкатенацията на клетки само когато не можете без нея. Обединените клетки затрудняват много манипулирането на диапазоните, в които влизат. Проблеми възникват при преместване на клетки, вмъкване на клетки и т.н.

Комбинирайте текст и числа в една клетка

Болезнено впечатление създава клетка, съдържаща число, подплатено отзад с текстовата константа "RUB". или „USD“, въведени ръчно. Особено ако това не е печатна форма, а обикновена таблица. Аритметичните операции с такива клетки са естествено невъзможни.

Числата като текст в клетка

Избягвайте да съхранявате цифрови данни в клетка в текстов формат. С течение на времето някои от клетките в такава колона ще имат текстов формат, а някои ще имат обикновен формат. Поради това ще има проблеми с формулите.

Ако вашата маса ще бъде представена чрез LCD проектор

Изберете най -контрастните комбинации от цвят и фон. Изглежда добре на проектор тъмен фони светли букви. Най -страшното впечатление прави червеното върху черното и обратно. Тази комбинация изглежда изключително нискоконтрастна на проектора - избягвайте я.

Работен лист за режим на страница в Excel

Това е същият режим, в който Excel показва как ще бъде странициран листът при отпечатване. Границите на страниците са подчертани в синьо. Не препоръчвам постоянно да работите в този режим, което правят много, тъй като драйверът на принтера участва в процеса на показване на данни на екрана и това, в зависимост от много причини (например мрежов принтер не е наличен в момента ) е изпълнен със замръзване в процеса на изобразяване и преизчисляване на формули. Работете както обикновено.

За още по -полезна информация за Excel посетете

Достатъчно често данните от програми на трети страни се качват в Excelза по -нататъшната им обработка и често по -нататъшното използване на тези данни във формули дава непредсказуем резултат: числата не се сумират, невъзможно е да се изчисли броят на дните между датите и т.н.

Тази статия обсъжда причините за такива проблеми и различни начинитяхното премахване

Причина първа ... Номерът е запазен като текст

В този случай можете да видите, че числата или датите са притиснати до левия ръб на клетката (като текст) и като правило в горния ляв ъгъл на клетката има маркер за грешка (зелен триъгълник) и маркер това, когато задържите мишката, обяснява, че номерът е записан като текст.

Никакви промени във формата на Numeric, General или Date не коригират ситуацията, но ако щракнете в лентата с формули (или натиснете F2) и след това Enter, числото става число, а датата става дата. С голям брой такива числа, виждате, опцията е неприемлива.

Има няколко начина за решаване на този проблем.

  • Използване на маркер и маркер за грешка... Ако видите маркер за грешка ( зелен триъгълник)и маркер, след това изберете клетките, щракнете върху маркера и изберете опцията Преобразувайте в число
  • Използване на операцията Find / Replace... Да предположим, че в таблицата има числа с десетична запетая, съхранени като текст. Изберете диапазон с числа - щракнете Ctrl + h(или намерете в раздела У домаили в менюто редактиранеза версии преди 2007 командата Заменете) - в полето намирамвъвеждам , (запетая) - в полето Заменено свъвеждаме и ние , (запетая) - Сменете всички... По този начин, като заменим запетая със запетая, симулираме редактирането на клетки по същия начин F2 - Въведете


Подобна операция може да се извърши с дати, с единствената разлика, че трябва да промените точка в точка.

В допълнение, програмите на трети страни могат да разтоварват числа с точка като десетичен разделител, след което замяната на точка със запетая ще помогне.

Подобна подмяна може да се направи с формулата (вижте по -долу) с помощта на функцията SUBSTITUTE ()

  • Използване на специална паста... Този метод е по -универсален, тъй като работи с дробни числа, цели числа и дати. Изберете всяка празна клетка - изпълнете командата копие- изберете диапазона с номера на проблема - Специално поставяне -- За сгъване-- Добре... По този начин добавяме 0 към числата (или датите), което не влияе по никакъв начин на тяхната стойност, но се преобразува в числов формат


Вариант на тази техника може да бъде умножаването на диапазона с 1

  • Използване на инструмента Текст по колони... Тази техника е удобна за използване, ако трябва да трансформирате една колона, тъй като ако има няколко колони, стъпките ще трябва да се повтарят за всяка колона поотделно. Така че, изберете колоната с числа или дати, запазени като текст, задайте формата на клетката Общ(за числа можете да зададете, например, Числовоили Финансови). След това изпълняваме командата Данни-- Текст на колона -- Готов


  • Използване на формули... Ако таблицата позволява допълнителни колони, можете да използвате формули за преобразуване в число. За да преобразувате текстова стойност в число, можете да използвате двоен минус, добавяне с нула, умножение по едно, функциите VALUE (), SUBSTITUTE (). Можете да прочетете по -подробно. След трансформацията, получената колона може да бъде копирана и поставена като стойности вместо първоначалните данни


  • Като се използва... Всъщност всеки от изброените методи може да бъде изпълнен с макрос. Ако трябва често да правите този вид преобразуване, тогава има смисъл да напишете макрос и да го стартирате според нуждите.

Ето два примера за макроси:

1) умножете по 1

Sub conv () Dim c като диапазон за всеки c в селекция If IsNumeric (c.Value) Тогава c.Value = c.Value * 1 c.NumberFormat = "#, ## 0.00" End If Next End Sub

2) текст по колони

Sub conv1 () Selection.TextToColumns Selection.NumberFormat = "#, ## 0.00" End Sub

Причина втора ... Номерът съдържа странични знаци.

Най -често тези външни знаци са интервали. Те могат да бъдат разположени както вътре в числото като разделител на цифри, така и преди / след номера. В този случай естествено числото става текст.

Приберете допълнителни пространстваможе да се направи и с помощта на операцията Намерете / заменете... В полето намирамвъведете интервал и поле Заменено составете празно, тогава Сменете всички... Ако в числото имаше обикновени интервали, тези действия ще бъдат достатъчни. Но числото може да съдържа така наречените непрекъснати интервали (символ с код 160). Такова пространство ще трябва да бъде копирано директно от клетката и след това да се постави в полето намирамдиалогов прозорец Намерете / заменете... Или можете на полето намирамнатиснете клавишната комбинация Alt + 0160(цифрите се въвеждат на цифровата клавиатура).

Пространствата могат да бъдат премахнати и с формула. Вариантите са:

За обикновени пространства: = - ЗАМЕСТНИК (B4; ""; "")

За непрекъснати пространства:= - ЗАМЕСТНИК (B4, СИМВОЛ (160), "")

Веднага за тези и други пространства: = - ЗАМЕСТИТЕЛ (ЗАМЕСТНИК (B4; СИМВОЛ (160); ""); ""; "")

Понякога, за да постигнете желания резултат, трябва да комбинирате изброените методи. Например, първо премахнете интервалите и след това конвертирайте клетъчния формат



Препоръчва се за четене