Экспресс-анализ формул в программе 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. Если переключатель «Выделить» установить в положение «формулы», будут доступны четыре флажка: «числа», «текст», «логические», «ошибки». С их помощью можно выделить на листе только те формулы, которые возвращают в ячейку значения соответствующего типа. И последнее.