7 съвета за работа с формули на Excel

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

Приоритет на главния оператор

Един от най-важните фактори при писането на формули е коректността на операторите и нямам предвид операторите на телефонни компании. Това е свързано с математическите оператори - знаете, малки подробности като знаци плюс и знаци за умножение и къде отиват скобите. Приоритетът на оператора - редът, в който се изпълняват операциите - може да направи голяма разлика в резултата. Имате лесен начин да поддържате приоритета на вашия оператор в ред. Всичко, което трябва да запомните, е „Моля, извинете моята скъпа леля Сали“.

Тази фраза е мнемоника за следното:

• Скоби

• Експоненти

• Умножение

• дивизия

• Добавяне

• Изваждане

Така скобите имат първо (най-високо) предимство, а изваждането има последно предимство. Е, честно казано, умножението има същия приоритет като делението, а събирането има същия приоритет като изваждането, но разбирате идеята!

Например, формулата =1 + 2 × 15 е равна на 31. Ако смятате, че трябва да е равно на 45, по-добре отидете да посетите леля си! Отговорът е равен на 45, ако включите скоби, като това: =(1 + 2) × 15.

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

Няколко минути освежаване на паметта ви по поръчка на оператор може да ви спести много главоболия в бъдеще.

Показване на формули

В случай, че не сте забелязали, е малко трудно да видите формулите си, без случайно да ги редактирате. Това е така, защото всеки път, когато сте в режим „редактиране“ и активната клетка има формула, формулата може да включва адреса на всяка друга клетка, върху която щракнете. Това тотално обърква нещата.

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

Опции за настройка.

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

Преглед на формули по лесния начин.

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

Поправете формули

Да предположим, че вашият работен лист има някои грешки. Не се паникьосвайте! Случва се дори на най-смелите потребители и Excel може да ви помогне да разберете какво не е наред. В раздела Формули в раздела Одит на формули е бутонът Проверка на грешки. Щракването върху бутона показва диалоговия прозорец Проверка на грешки, показан тук. Това означава, че диалоговият прозорец се появява, ако вашият работен лист има грешки. В противен случай просто изскача съобщение, че проверката за грешка е завършена. Толкова е умно!

Проверка за грешки.

Когато има са грешки, се появява диалогов прозорец и пръчки около докато работите върху всяка грешка. Бутоните Next и Previous ви позволяват да преминете през всички грешки, преди диалоговият прозорец да се затвори. За всяка открита грешка вие избирате какво действие да предприемете:

• Помощ за тази грешка: Това води до системата за помощ и показва темата за конкретния тип грешка.

• Показване на стъпките за изчисление: Отваря се диалоговият прозорец Оценяване на формула и можете да наблюдавате стъпка по стъпка как се изчислява формулата. Това ви позволява да идентифицирате конкретната стъпка, която е причинила грешката.

• Игнориране на грешка: Може би Excel не е наред. Игнорирайте грешката.

• Редактиране в лентата с формули: Това е бърз начин да коригирате формулата сами, ако не се нуждаете от друга помощ.

Диалоговият прозорец Проверка на грешки също има бутон Опции. Щракването върху бутона отваря раздела Формули на диалоговия прозорец Опции на Excel. В раздела Формули можете да изберете настройки и правила за това как се разпознават и задействат грешките.

Използвайте абсолютни препратки

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

Често това е намерението. Когато има една колона с данни и съседна колона с формули, обикновено всяка клетка в колоната с формули се отнася до съседа си в колоната с данни. Но ако всички формули препращат към клетка, която не е съседна, намерението обикновено е всички клетки с формули да препращат към непроменена препратка към клетка. Накарайте това да работи правилно, като използвате абсолютна препратка към клетката.

За да използвате абсолютна препратка към клетка, използвайте знака за долар ($) преди номера на реда, преди буквата на колоната или преди и двете. Направете това, когато пишете първата формула, преди да я плъзнете в други клетки, или ще трябва да актуализирате всички формули.

Например, не пишете това:

=A4 x (B4 + A2)

Вместо това го напишете така:

=A4 x (B4 + $A$2)

По този начин всички формули се позовават на A2, независимо къде ги копирате, вместо тази препратка да се превръща в A3, A4 и т.н.

Използвайте одит на формули

Има прецеденти и зависими. Има външни препратки. Навсякъде има взаимодействие. Как можете да проследите откъде идват и отиват препратките към формулите?

Използвайте инструментите за одит на формули, ето как! В раздела Формули е секцията Одит на формули. В секцията има различни бутони, които контролират видимостта на стрелките за одитиране на проследяване.

Формули за одит.

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

Използвайте условно форматиране

Точно както функцията IF връща определена стойност, когато условието за първия аргумент е вярно, и друга стойност, когато е фалшиво, условното форматиране ви позволява да приложите определен формат към клетка, когато условието е вярно. В раздела Начало в секцията Стилове има падащо меню с много опции за условно форматиране.

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

Прилагане на формат, когато е изпълнено условие.

Условията са определени като правила. Типовете правила са

• Форматирайте всички клетки въз основа на техните стойности.

• Форматирайте само клетки, които съдържат. . . .

• Форматирайте само стойностите с най-висока или долна позиция.

• Форматирайте само стойности, които са над или под средните.

• Форматирайте само уникални или дублиращи се стойности.

• Използвайте формула, за да определите кои клетки да форматирате.

Когато условието е вярно, форматирането може да контролира следното:

• Граници

• Настройки на шрифта (стил, цвят, получер, курсив и т.н.)

• Запълване (цвят или модел на фона на клетка)

Клетките могат също да бъдат форматирани с цветови схеми или изображения на икони, поставени в клетката.

Използвайте проверка на данните

В раздела Данни, в секцията Инструменти за данни, е Проверка на данни. Проверката на данни ви позволява да приложите правило към клетка (или клетки), така че записът да се придържа към правилото. Например, клетка може да бъде настроена да приема само цяло число между 50 и 100.

Задаване на валидиране на данните.

Когато влизането не отговаря на правилото, се показва съобщение.

Хванат при лош вход.

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