Как показать формулы в ячейках Excel и найти ошибки в расчетах
Здравствуйте, друзья. Сегодня я покажу вам, как отобразить в ячейках Excel формулы вместо результатов их вычисления. Казалось бы, зачем это может понадобиться? Я использую этот метод, чтобы:
- Найти ошибки в расчетах
- Разобраться, как произведены подсчеты
- Проверить, все ли нужные ячейки содержат формулы, или где-то они заменены числом
- В других случаях, когда это может пригодиться
Для начала, мы научимся отображать все формулы на экране, а потом попытаемся это с пользой применить.
Как показать формулы в ячейках
Чтобы показать формулы, предложу два варианта:
- Нажмите комбинацию клавиш Ctrl+` . Если не найдете кнопку апострофа, она расположена под клавишей Esc . По нажатию должны отобразиться все формулы вместо результатов вычисления. Однако, это может и не произойти. Если комбинация не работает, скорее всего нужно менять язык ввода Windows по молчанию на английский. Но в этой статье не буду детальнее описывать, ведь есть альтернативные методы. Так что, если горячие клавиши не сработали, переходим ко второму пункту
- Выполним на ленте Файл — Параметры — Дополнительно — Параметры отображения листа — Показывать формулы, а не их значения . Установим галку, чтобы показать формулы. Снимем её, чтобы снова отобразить результаты.
Если комбинация клавиш не работает, а нужно переключаться часто — есть еще один способ: назначить комбинацию с помощью макроса. Если Вам это интересно — пишите в комментариях, обязательно расскажу, как это сделать.
А теперь переходим к примеру. Посмотрим, как можно искать ошибки, отображая все формулы.
Поиск ошибок с помощью отображения формул
В одной из статей блога я рассказывал о расчете аннуитетного кредита, т.е. займа с фиксированным ежемесячным платежом. В следующем примере мы попытались рассчитать платежи помесячно с расшифровкой: погашение тела кредита и оплата процентов. В сумме эти два показателя будут давать ежемесячный платеж.
Взгляните на рисунок с примером выше. В таблице «Исходные данные» указана сумма кредита (10 тыс. Евро), годовая ставка и срок погашения. В таблице «Платежи по периодам» я предусмотрел такие колонки:
- Номер периода — перечень от 1 до 12, соответствующий каждому из платежей по кредиту
- Основной платеж — сумма на погашение тела кредита в данном периоде. Рассчитана с помощью функции ОСПЛТ
- Проценты — оплата процентов по кредиту в указанном периоде. Посчитана функцией ПРПЛТ .
Для первого периода я записал формулы вручную, а потом скопировал их в оставшиеся ячейки. Как видим на рисунке, проценты вычислены корректно, а вот с основным платежом проблемы. Начиная со второго периода, формулы возвращают или неверный результат, или ошибку. Очевидно, что проблема в расчетах есть в целой группе ячеек. Пришло время взглянуть на формулы. Воспользуемся одним из предложенных выше способов и пробуем анализировать то, что видим.
Мне сразу бросилось в глаза, что в формуле ОСПЛТ последний аргумент ссылается величину кредита. То есть, там всегда должны быть наши 10 тыс. Евро, записанные в ячейке В2. А во втором периоде формула почему-то ссылается уже на В3, в третьем — на В4 и так далее. То есть, я применил относительные ссылки вместо абсолютных, и они «поползли» вниз при копировании формулы вниз. Если Вы не знаете, что такое относительные и абсолютные ссылки — прочтите здесь — это очень важная информация для пользователя Эксель. Исправим формулы. Проставлю знаки доллара в ссылке перед цифрой и буквой, скопирую во все строки. Получилось вот так:
Других проблем с формулами я не заметил. Отключаем вывод формул, посмотрим на результаты:
Ура, теперь всё хорошо. Результаты вычислений правдоподобны, т.е. правильные.
Как видите, получив некорректные результаты, мы не поддались панике, а применили «научный» подход и очень быстро получили результат. Берите этот метод в свою копилку навыков, это один из самых быстрых способов найти ошибку.
Спасибо за то, что посетили мой блог. Подписывайтесь на обновления и всегда будете в курсе самых свежих уроков. До встречи!
Как показать формулы в Excel?
Для поиска ошибок в вычислениях бывает очень полезно отобразить формулы, по которым происходят вычисления.
Давайте разберемся как показать формулы в Excel.
По умолчанию в Excel во всех ячейках показываются значения выполнения формул вместо самих формул.
Если мы хотим отобразить формулу в конкретной ячейке, то достаточно будет нажать F2 или посмотреть в строку формул, однако для просмотра всех формул листа такой способ не подходит.
Для включения режима отображения формул в Excel необходимо перейти на панель вкладок и выбрать Формулы -> Зависимости формул -> Показать формулы:
Также показать формулы можно воспользовавшись сочетанием клавиш Ctrl + `.
Пример отображения формул в ячейках в Excel:
Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!
Поделиться с друзьями:
Поиск по сайту:
Похожие статьи:
- Стили ссылок R1C1 и A1 в Excel
- Функция СЧЁТЕСЛИ в Excel
- Функция НАИБОЛЬШИЙ в Excel
- Транспонирование матрицы и таблицы в Excel
- ← Как сохранить файл Excel в формате PDF?
- Число Пи в Excel →
Комментарии (2)
Как показать саму формулу в отдельной ячейке? Вот мы делаем вычисление и надо наглядно видеть по какой формуле рассчитано что-либо. Надо чтоб в одной ячейке формула давалась на вычисление, а в другой эта же формула как есть.
Виталий, можно воспользоваться функцией Ф.ТЕКСТ, писал отдельную статью как показать формулу в ячейке.
Добавить комментарий Отменить ответ
Рубрики
- Даты и время (6)
- Дашборды (5)
- Диаграммы (18)
- Диапазоны (6)
- Защита данных (1)
- Книги и листы (17)
- Макросы/VBA (9)
- Математика (13)
- Основы (5)
- Приемы (13)
- Сводные таблицы (2)
- Текст (15)
- Функции (17)
- Числа (1)
Свежие записи
- Комментарии к ячейке в формуле в Excel
- Умная таблица в Excel
- Группировка данных в сводной таблице в Excel
- Радиальная гистограмма в Excel
- Выделение столбцов или строк на диаграмме в Excel
- Визуализация диаграммы с помощью фигур и рисунков в Excel
- Как закрепить область в Excel?
- Как убрать нули в ячейках в Excel?
- Круговой индикатор выполнения (прогресс бар) в Excel
- Линейный индикатор выполнения (прогресс бар) в Excel
Содержание
- Главная
- Функции
- Диаграммы
- Текст
- Книги и листы
- Математика
- Макросы/VBA
- Приемы
- Даты и время
- Диапазоны
- Основы
Читайте ранее:
Как сохранить файл Excel в формате PDF?
Сохранение файла Excel в формате PDF позволяет прочитать его практически с любого устройства без изменения формата документа, что и делает.