Експрес-аналіз формул у програмі Excel

Експрес-аналіз формул у програмі Excel

Шановні працівники «Б & К»! Час від часу я стикаюся з такою ситуацією. Є таблиця у форматі Excel. У ній записані дані та розрахункові формули. Із таблицею працюють різні співробітники. Іноді трапляється так, що до таблиці (помилково) на місце формули заносять число. Зовні це непомітно. Із таблицею продовжують працювати. А коли помилку вдається виявити, — уже пізно. Розрахунки зроблено неправильно. Підкажіть, як у програмі Excel можна переглянути всі формули та переконатися, що вони в порядку? Користуюся MS Excel 2003. Спасибі.

Олександр Тимофєєв, головний бухгалтер, м. Харків

Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

Знайома ситуація. Сам неодноразово стикався з подібною проблемою. Найчастіше вона виникала у випадках, коли готову таблицю коригують різні люди. Структура таблиці їм може бути невідома. І тому ймовірність видалити формулу та внести замість неї числову константу різко зростає. Для пошуку такої помилки потрібний інструмент, який допоможе швидко знайти всі формули на робочому листі. І не лише знайти, а й указати, де ці формули розташовані. Як це зробити, я покажу на прикладі таблиці, фрагмент якої наведено на рис. 1. У ній шість колонок. Стовпці «A:D» містять вихідні дані. У колонках «E:I» знаходяться формули для розрахунку суми ПДВ та загальної вартості. Наше завдання: перевірити, щоб у колонках «E:I» були тільки формули. Якщо це не так, після зміни вихідних даних частину комірок Excel не перераховуватиме, і в розрахунках з’явиться помилка. Вирішити завдання можна різними методами.

Спосіб 1. Виділити всі комірки з формулами та призначити для них спеціальний формат.

Для цього робимо так.

1.  Відкриваємо таблицю (рис. 1), виділяємо блок колонок «A:I».

2. Викликаємо меню «Правка → Перейти…» або натискуємо «Ctrl+G». Відкриється вікно «Переход», зображене на рис. 2.

3. У цьому вікні натискуємо кнопку «Выделить…». Відкриється вікно «Выделение группы ячеек», як наведено на рис. 3.

4. У ньому ставимо перемикач «Выделить» у положення «формулы» та натискуємо «ОК». Усі комірки з формулами на поточному листі буде виділено в один блок.

5. Не знімаючи виділення, клацаємо по іконці «Цвет заливки» та вибираємо добре помітний колір фону для комірок з формулами. Результат показано на рис. 4.

Із завданням ми впоралися. Усі комірки з формулами виділено заливкою. Тепер достатньо подивитись на таблицю, щоб побачити помилку, — у комірці «E5» замість формули записано значення «108».

Спосіб 2. Переключити робочий лист у режим відображення формул

Цей спосіб мені подобається менше, проте він теж може допомогти у вирішенні завдання, і тому має право на життя. Робимо так.

1. Відкриваємо таблицю.

2. Натискуємо комбінацію «Ctrl+~». На робочому листі замість значень Excel покаже розрахункові формули (рис. 5). Усе, що залишається зробити, — це уважно переглянути таблицю.

До речі, комбінація «Ctrl+~» (перемикання робочого листа в режим відображення формул) працює в усіх версіях програми Excel, уключаючи Excel 2007/2010.

Пропоную повернутися до параметрів вікна, наведеного на рис. 3. Якщо перемикач «Выделить» установити в положення «формулы», будуть доступними чотири прапорці: «числа», «текст», «логические», «ошибки». З їх допомогою можна виділити на листі тільки ті формули, які повертають до комірки значення відповідного типу. І останнє.