Как да маркирате клетки с формули в Excel

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

(тилда; за да отмените показването на формула, натиснете Ctrl+

отново). Този трик ще ви позволи да маркирате клетки с формули, като използвате три реда VBA код и условно форматиране. [1]

Ако никога не сте създавали персонализирана функция с VBA, препоръчвам да започнете със Sum By Cell Color в Excel, което обяснява първите стъпки.

Ако имате представа за какво става въпрос, отидете в менюто Разработчик -> Visual Basic и в прозореца на Microsoft Visual Basic за приложения, който се отваря, отидете на менюто Вмъкване –> Модул (фиг. 1). В прозореца на новия модул въведете следния код:

ФункцияIsFormula (Check_Cell As Range) IsFormula = Check_Cell.HasFormula End Function

формули

Ориз. 1. Кодът на персонализираната функция IsFormula в прозореца на Microsoft Visual Basic за приложения

Изтеглете бележка във формат Word или pdf, изтеглете пример във формат Excel (с вграден VBA код)

формули

Ориз. 2. Персонализирана функция е достъпна в прозореца Вмъкване на функция

Можете също да извикате функцията просто като напишете =i в клетката (Фигура 3).

Ориз. 3. Функцията може да бъде избрана чрез подсказка, като започнете да въвеждате в клетката =i

Функцията връща две стойности: TRUE, ако съответната клетка съдържа формула, и FALSE, ако не съдържа. Този булев резултат може да се използва заедно с условно форматиране за автоматично маркиране на всички формули с форматирането, което желаете.

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

Маркирайте диапазон от клетки, пълни с данни, плюс направете място за още, в случай че се добавят данни. Не избирайте целия лист, тъй като това може да увеличи размера на файла (понякога катастрофално). В нашия пример (фиг. 4) избрах областта A1:K28. Освен това клетка A1 трябва да е активна.

Като пример, в диапазона A1:F20, въведох формулата =RAND(). След това избрах целия диапазон, копирах го в буфера и го поставих като стойност. След това в няколко клетки отново вмъкнах формулата =RAND().

маркирате

Ориз. 4. Създайте правило за форматиране за избраната област

Отидете на началната страница –> Условно форматиране -> Създаване на правило (работя в Excel2013). В прозореца Създаване на правило за форматиране, който се отваря, изберете опцията Използване на формула за определяне на клетките за форматиране. И в полето Format values ​​​​for which the next formula is true въведете =IsFormula(A1). Щракнете върху бутона Формат до полето Пример и изберете жълто запълване, за да идентифицирате клетки с формули. Щракнете върху OK (Фигура 5).

формули

Ориз. 5. С помощта на VBA код и условно форматиране беше възможно да се маркират клетки, съдържащи формули

Резултатът беше очакваният ефект, който можете да наблюдавате в прикачения Excel файл. Мислех, че функцията =RAND() се преизчислява само когато се промени поне една стойност в листа. И така, на листа формално няма промяна на стойностите. Но очевидно функцията IsFormula, участваща в условното форматиране, по някакъв начин засяга този процес, така че екранът оживява и стойностите непрекъснато се променят.🙂

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

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

[1] По материали от книгата на Р.Холи, Д.Холи. Excel 2007. Трикове, стр. 70–72

Ако намирането на клетки с формули е еднократна или не много честа операция, тогава е напълно възможно да използвате съществуващата функционалност, без да използвате персонализирани функции. Раздел на лентата „Начало“, раздел „Редактиране“, бутон „Намиране и избор“, в падащото меню изберете елемента „Формули“. Или CTRL+G -> „Избор“ -> "Формули" - резултатът е подобен. Можете да прочетете малко повече за избора на специални диапазони тук: http://allexcel.info/2013/04/04/highlight-tricky-ranges/

Добър ден, Сергей! Благодаря ви за интересните материали за Excel и бизнеса - много добре пишете!

Относно статията бих искал да добавя, че в Excel 2013 можете да използвате новата функция FORMULA, която просто определя дали има формула в клетката или не.