Как отобразить все записанные формулы на листе книги excel

На листе с множеством формул часто бывает трудно ориентироваться. Например, чтобы удалить ненужные формулы нужно их сначала найти. Для поиска формул на листе в EXCEL есть ряд интересных инструментов

.

Для того, чтобы посмотреть какие ячейки на листе содержат формулы можно воспользоваться пунктом меню кнопкой

(маленькая кнопочка).

Если нажать в том же меню кнопку

Влияющие ячейки

или

Зависимые ячейки

, то появятся синие стрелки, которые также могут быть полезными при нахождении формул.

Также можно выделить ячейки с формулами другим способом:

. Затем, выделяем ячейки путем изменения фона или цвета шрифта.

Также можно прочитать статью

Выделение с помощью Условного форматирования ячеек, содержащих и НЕ содержащих формулы

.

Результаты вычисления формулы отображаются в ячейках на листе, а сами формулы отображаются только в строке формул, что препятствует их выведению на печать. Отображение формул в ячейках упрощает поиск ячеек, содержащих формулы,или проверку на ошибки.

С помощью быстрого сочетания клавиш вы можете вместо результатов отобразить на листе формулы.

Чтобы отобразить формулы в ячейках, нажмите сочетание клавиш CTRL+` (маленький значок — это значок тупого ударения). Когда формулы будут видны, распечатайте свой таблицу, как обычно.

Чтобы вернуться к отображению результатов в ячейках, снова нажмите CTRL+`.

Совет: Подсказка на случай, если вам не удастся найти клавишу значка тупого ударения (`): обычно (но не всегда) она располагается в верхнем левом углу клавиатуры. Если на вашей клавиатуре ее трудно найти, вы можете отобразить и скрыть формулы, перейдя на вкладку Формулы и выбрав команду Показывать формулы. Чтобы вернуться к стандартному режиму, нажмите эту кнопку еще раз.

Удобный просмотр формул и результатов

Простая ситуация. Имеем таблицу с формулами:

Для удобства отладки и поиска ошибок, хотелось бы одновременно видеть и значения в ячейках, и формулы, по которым эти значения вычисляются. Например, вот так:

Чтобы получить такую красоту, вам нужно сделать всего несколько простых шагов:

1. Создать копию текущего окна книги, нажав на вкладке Вид (View) кнопку Новое окно (New Window). В старых версиях Excel это можно сделать через меню Окно — Новое окно (Window — New window).
2. Разместить оба окна сверху вниз друг под другом, нажав на той же вкладке Вид (View) кнопку Упорядочить все (Arrange all). В Excel 2003 и старше — меню Окно — Упорядочить все (Window — Arrange all).
3. Выбрав одно из получившихся окон, перейти в режим просмотра формул, нажав на вкладке Формулы (Formulas) кнопку Показать формулы (Show Formulas). В старом Excel это меню Сервис — Зависимости формул — Режим проверки формул (Tools — Formula Auditing — Show Formulas).

Если подобный режим просмотра формул вам полезен и будет нужен часто, то можно использовать простой макрос, который проделает все вышеперечисленные действия за вас:

'макрос включения режима просмотра формул
Sub FormulaViewOn()
    ActiveWindow.NewWindow
    ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlHorizontal
    ActiveWindow.DisplayFormulas = True
End Sub

'макрос выключения режима просмотра формул
Sub FormulaViewOff()
    If ActiveWindow.WindowNumber = 2 Then
        ActiveWindow.Close
        ActiveWindow.WindowState = xlMaximized
        ActiveWindow.DisplayFormulas = False
    End If
End Sub

Нажмите сочетание клавиш ALT+F11, чтобы перейти в редактор Visual Basic. Затем создайте новый пустой модуль через меню Insert — Module и скопируйте туда текст двух вышеприведенных макросов. Первый включает, а второй отключает наш двухоконный режим просмотра формул. Для запуска макросов можно использовать сочетание клавиш ALT+F8, затем кнопка Выполнить (Run) или назначить макросам горячие клавиши в том же окне с помощью кнопки Параметры (Options).

Ссылки по теме

• Выделение сразу всех ячеек с формулами или константами на листе
• Что такое макросы, куда вставлять код макроса на VBA, как запускать макросы.
• Цветовая карта для подсветки ячеек с определенными типами содержимого в надстройке PLEX

Как отобразить формулу в ячейке эксель

Как показать формулы в ячейках Excel и найти ошибки в расчетах

Здравствуйте, друзья. Сегодня я покажу вам, как отобразить в ячейках Excel формулы вместо результатов их вычисления. Казалось бы, зачем это может понадобиться? Я использую этот метод, чтобы:

1. Найти ошибки в расчетах
2. Разобраться, как произведены подсчеты
3. Проверить, все ли нужные ячейки содержат формулы, или где-то они заменены числом
4. В других случаях, когда это может пригодиться

Для начала, мы научимся отображать все формулы на экране, а потом попытаемся это с пользой применить.

Как показать формулы в ячейках

Чтобы показать формулы, предложу два варианта:

1. Нажмите комбинацию клавиш Ctrl+` . Если не найдете кнопку апострофа, она расположена под клавишей Esc . По нажатию должны отобразиться все формулы вместо результатов вычисления. Однако, это может и не произойти. Если комбинация не работает, скорее всего нужно менять язык ввода Windows по молчанию на английский. Но в этой статье не буду детальнее описывать, ведь есть альтернативные методы. Так что, если горячие клавиши не сработали, переходим ко второму пункту
2. Выполним на ленте Файл — Параметры — Дополнительно — Параметры отображения листа — Показывать формулы, а не их значения . Установим галку, чтобы показать формулы. Снимем её, чтобы снова отобразить результаты.
   

Если комбинация клавиш не работает, а нужно переключаться часто — есть еще один способ: назначить комбинацию с помощью макроса. Если Вам это интересно — пишите в комментариях, обязательно расскажу, как это сделать.

А теперь переходим к примеру. Посмотрим, как можно искать ошибки, отображая все формулы.

Поиск ошибок с помощью отображения формул

В одной из статей блога я рассказывал о расчете аннуитетного кредита, т.е. займа с фиксированным ежемесячным платежом. В следующем примере мы попытались рассчитать платежи помесячно с расшифровкой: погашение тела кредита и оплата процентов. В сумме эти два показателя будут давать ежемесячный платеж.

Взгляните на рисунок с примером выше. В таблице «Исходные данные» указана сумма кредита (10 тыс. Евро), годовая ставка и срок погашения. В таблице «Платежи по периодам» я предусмотрел такие колонки:

• Номер периода — перечень от 1 до 12, соответствующий каждому из платежей по кредиту
• Основной платеж — сумма на погашение тела кредита в данном периоде. Рассчитана с помощью функции ОСПЛТ
• Проценты — оплата процентов по кредиту в указанном периоде. Посчитана функцией ПРПЛТ .

Для первого периода я записал формулы вручную, а потом скопировал их в оставшиеся ячейки. Как видим на рисунке, проценты вычислены корректно, а вот с основным платежом проблемы. Начиная со второго периода, формулы возвращают или неверный результат, или ошибку. Очевидно, что проблема в расчетах есть в целой группе ячеек. Пришло время взглянуть на формулы. Воспользуемся одним из предложенных выше способов и пробуем анализировать то, что видим.

Мне сразу бросилось в глаза, что в формуле ОСПЛТ последний аргумент ссылается величину кредита. То есть, там всегда должны быть наши 10 тыс. Евро, записанные в ячейке В2. А во втором периоде формула почему-то ссылается уже на В3, в третьем — на В4 и так далее. То есть, я применил относительные ссылки вместо абсолютных, и они «поползли» вниз при копировании формулы вниз. Если Вы не знаете, что такое относительные и абсолютные ссылки — прочтите здесь — это очень важная информация для пользователя Эксель. Исправим формулы. Проставлю знаки доллара в ссылке перед цифрой и буквой, скопирую во все строки. Получилось вот так:

Других проблем с формулами я не заметил. Отключаем вывод формул, посмотрим на результаты:

Ура, теперь всё хорошо. Результаты вычислений правдоподобны, т.е. правильные.

Как видите, получив некорректные результаты, мы не поддались панике, а применили «научный» подход и очень быстро получили результат. Берите этот метод в свою копилку навыков, это один из самых быстрых способов найти ошибку.

Спасибо за то, что посетили мой блог. Подписывайтесь на обновления и всегда будете в курсе самых свежих уроков. До встречи!

Как отобразить формулы в Microsoft Excel 2013

Многие пользователи сталкивались с такой проблемой, когда при работе с таблицами может понадобиться просмотр ячеек, в которых указана формула для расчета данных. Тогда как в обычно режиме Вам доступен просмотр результат вычислений данной формулы. Для этого в программе есть два способа просмотра формул, а именно возможность просмотра всех формул одновременно, тогда вместо числового значении в ячейках будут показаны формулы, а также Вы можете установить отображение формулу в виде строки в отдельной ячейке. В этой пошаговой инструкции с фотографиями я покажу Вам, как отобразить формулы в программе Microsoft Excel 2013.

1. Как отобразить все формулы на листе Microsoft Excel 2013
2. Как отобразить значение формулы в виде строки в Microsoft Excel 2013

Как отобразить все формулы на листе Microsoft Excel 2013

Рассмотрим отображение всех формул на листе Microsoft Excel 2013. Начните с того, что перейдите во вкладку «Формулы» и щелкните кнопку «Показать формулы».

В таком режиме вместо значений Вам будут показаны все введенные формулы. Выйди из данного режима можно аналогично щелкнув кнопку «Показать формулы».

Как отобразить значение формулы в виде строки в Microsoft Excel 2013

Если Вам необходимо видеть значение и формулу определенной ячейки необходимо выбрать пустую ячейку, в которой будет показано текстовое содержание формулы и ввести в ней «=ф.текст».

Затем в круглых скобках указать ссылку на ячейку (Например, «=ф.текст(D6)»), формула которой будет показана и нажать кнопку «Enter».

В ячейке, в виде текста, показана формула. Если информация помогла Вам – жмите Спасибо!

Отображение и скрытие формул

Отображением формул можно управлять следующим образом:

Нажмите клавиши CTRL+` (апостроф).

Примечание: Эта процедура делает невозможным редактирование ячеек, содержащих формулы.

Выделите диапазон ячеек, в которых вы хотите скрыть формулы. Вы также можете выбрать несмежные диапазоны ячеек или весь лист.

На вкладке Главная нажмите кнопку Формат и выберите команду Формат ячеек.

На вкладке Защита снимите флажок Скрытый.

Нажмите кнопку ОК.

На вкладке Рецензирование нажмите кнопку Защитить лист.

Убедитесь в том, что флажок Защитить лист и содержимое защищаемых ячеек установлен, и нажмите кнопку ОК.

На вкладке Рецензирование нажмите кнопку Снять защиту листа. Если кнопка «Снять защиту листа» недоступна, сначала отключите функцию Общая книга.

Если вы больше не хотите скрывать формулы на защищенном листе, щелкните ячейки правой кнопкой мыши и выберите Формат ячеек. На вкладке Защита снимите флажок Скрытый.

Скрытие и защита формул в настоящее время в Excel Online не поддерживается.

Если у вас есть классическое приложение Excel, нажмите кнопку Открыть в Excel , чтобы открыть книгу, чтобы Скрыть и защитить формулы от просмотра и изменения.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Как отобразить формулы в Microsoft Excel 2013

Многие пользователи сталкивались с такой проблемой, когда при работе с таблицами может понадобиться просмотр ячеек, в которых указана формула для расчета данных. Тогда как в обычно режиме Вам доступен просмотр результат вычислений данной формулы. Для этого в программе есть два способа просмотра формул, а именно возможность просмотра всех формул одновременно, тогда вместо числового значении в ячейках будут показаны формулы, а также Вы можете установить отображение формулу в виде строки в отдельной ячейке. В этой пошаговой инструкции с фотографиями я покажу Вам, как отобразить формулы в программе Microsoft Excel 2013.

1. Как отобразить все формулы на листе Microsoft Excel 2013
2. Как отобразить значение формулы в виде строки в Microsoft Excel 2013

Как отобразить все формулы на листе Microsoft Excel 2013

Рассмотрим отображение всех формул на листе Microsoft Excel 2013. Начните с того, что перейдите во вкладку «Формулы» и щелкните кнопку «Показать формулы».

В таком режиме вместо значений Вам будут показаны все введенные формулы. Выйди из данного режима можно аналогично щелкнув кнопку «Показать формулы».

Как отобразить значение формулы в виде строки в Microsoft Excel 2013

Если Вам необходимо видеть значение и формулу определенной ячейки необходимо выбрать пустую ячейку, в которой будет показано текстовое содержание формулы и ввести в ней «=ф.текст».

Затем в круглых скобках указать ссылку на ячейку (Например, «=ф.текст(D6)»), формула которой будет показана и нажать кнопку «Enter».

В ячейке, в виде текста, показана формула. Если информация помогла Вам – жмите Спасибо!

Microsoft Excel

трюки • приёмы • решения

Как в таблице Excel отобразить и напечатать формулы?

Когда вы вводите формулы в ячейку Excel отображает рассчитанное значение формулы. Для просмотра формулы активизируйте ячейку; Excel покажет формулу в строке формул.

Чтобы просмотреть все свои формулы, переключитесь в режим формул. Самый простой способ перейти в этот режим — нажать Ctrl+V. В результате в ячейках Excel отобразит формулы, а не их результаты. Кроме того, вы можете увидеть, на какие ячейки ссылается выбранная формула, поскольку Excel отображает их с цветной рамкой. На рис. 84.1 показан лист в режиме формул. Выбрана ячейка D8 . Она ссылается на ячейки В8 и С8 , которые выделены цветной рамкой.

Рис. 84.1. Режим просмотра формул в Excel отображает сами формулы, а не их результаты

В режиме формул вы можете использовать все команды Excel и даже изменять формулы. На практике может оказаться, что режим формул не очень полезен. Хотя Excel расширяет столбцы, вы можете увидеть всю формулу, только если она очень короткая. Если же вы попробуете распечатать лист в режиме формул, то получите бессмысленный и беспорядочный набор данных.

Чтобы задокументировать лист, распечатав его формулы, лучше всего использовать макрос VBA. Следующая простая процедура VBA выводит список всех формул на активном листе:

Sub ListFormulas() Dim FormulaCells As Range Dim FormulaSheet As Worksheet Dim Row As Long Dim Cell As Range On Error Resume Next Set FormulaCells = Range(«A») .SpecialCells(xlFormulas, 23) If FormulaCells Is Nothing Then Exit Sub Set FormulaSheet = ActiveWorkbook.Worksheets.Add Row = 1 For Each Cell In FormulaCells With FormulaSheet Cells(Row, 1) = Cell.Address(False, False) Cells(Row, 2) = » » & Cell.Formula Cells(Row, 3) = Cell.Value Row = Row + 1 End With Next Cell End Sub

Чтобы использовать этот макрос, нажмите Alt+F11 для активизации редактора Visual Basic. Выберите книгу в окне Project и выполните команду Insert ► Module, чтобы вставить новый модуль VBA. Наберите точно такой же код, который приведен выше. Чтобы выполнить макрос, активизируйте лист, в котором есть формулы, нажмите Alt+F8, выберите ListFormulas из списка макросов и нажмите Выполнить. На рис. 84.2 показан пример вывода этого макроса. Первый столбец содержит адрес формулы, второй столбец содержит формулу, а третий показывает текущий результат формулы.

Рис. 84.2. Список формул, сгенерированный макросом VBA

Человечество уже давно и прочно «подсело» на лекарственные препараты. С помощью таблеток лечится все: и действительно тяжелые недуги, и небольшие недомогания вроде легкой головной боли или бурления в животе вследствие обильного ужина. Что же заставляет нас без особой необходимости идти в аптеку и покупать очередную нарядную коробочку с таблетками? Прежде всего легковерие и лень. Почему легковерие? Да потому, что мы по своей доверчивости полагаем, что создатели таблеток в первую очередь заботятся о нашем здоровье, и только во вторую — о своем кармане. На самом деле все совсем наоборот, и даже гораздо хуже: нередко бизнесмены от фармацевтики о нашем с вами здоровье вообще не думают, для них главное, чтобы мы как можно чаще покупали их лекарства, все остальное не так уж важно. Фармацевтическое производство — такой же бизнес, как и любой другой.

How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily.

Steps to Display all Formulas at Once in Excel

1. Go to the worksheet where you want to view the formulas (this feature is worksheet specific).
2. Go to the Formulas tab and then click Show Formulas or use the keyboard shortcut  Ctrl + ` (that is an accent, the button located above the Tab key on the left side of the keyboard usually).

Here is the worksheet before displaying the formulas:

Here is the worksheet after displaying the formulas:

You can see that, not only are the formulas displayed, but all of the number formatting that was applied to the cells is removed.  There is no more currency or percentage formatting and this is done in an attempt to make it easier to read the spreadsheet, though that is not always how it works out.

Steps to Go Back to Normal and Hide the Formulas

This is the same as showing the formulas.

1. Go to the worksheet where you want to hide the formulas and go back to normal (this feature is worksheet specific).
2. Go to the Formulas tab and click Show Formulas or use the keyboard shortcut  Ctrl + ` (that is an accent, the button located above the Tab key on the left side of the keyboard usually).

Quickly View Ranges Used in Formulas

Once you have shown the formulas, you only have to select a cell to show all of the cell references that are used in the formula.  Normally you have to double-click a cell to do this.  In some cases, it will also enlarge the text of the selected cell so it is easier to read.

Navigate with the arrow keys to do this quickly and it helps to troubleshoot a spreadsheet even more.

Why Should You Show Formulas in Excel?

The benefit of this feature is that it allows you to quickly see all of the formulas in a worksheet at once so that you can troubleshoot them or change them.  This eliminates the, quickly annoying, task of having to double-click a cell to see what is in it or to click a cell and look to the formula bar each time.

With this feature, you can quickly scan the worksheet to see what, if anything, needs to be done.

I often use this feature when troubleshooting complex spreadsheets, especially if the spreadsheet was sent to me by another person and I’m not sure exactly what they did to make it.

Don’t forget to download the Excel file that accompanies this tutorial so you can try this out.

Similar Content on TeachExcel

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!