Отображение и печать формул
Результаты вычисления формулы отображаются в ячейках на листе, а сами формулы отображаются только в строке формул, что препятствует их выведению на печать. Отображение формул в ячейках упрощает поиск ячеек, содержащих формулы,или проверку на ошибки.
С помощью быстрого сочетания клавиш вы можете вместо результатов отобразить на листе формулы.
Чтобы отобразить формулы в ячейках, нажмите сочетание клавиш CTRL+` (маленький значок — это значок тупого ударения). Когда формулы будут видны, распечатайте свой таблицу, как обычно.
Чтобы вернуться к отображению результатов в ячейках, снова нажмите CTRL+`.
Совет: Подсказка на случай, если вам не удастся найти клавишу значка тупого ударения (`): обычно (но не всегда) она располагается в верхнем левом углу клавиатуры. Если на вашей клавиатуре ее трудно найти, вы можете отобразить и скрыть формулы, перейдя на вкладку Формулы и выбрав команду Показывать формулы. Чтобы вернуться к стандартному режиму, нажмите эту кнопку еще раз.
Формула отображается только в , а результаты отображаются в ячейках книги Excel в Интернете. Но вы можете сделать снимок экрана или скопировать формулы и вставить их в другое приложение, например Блокнот, а затем распечатать.
Удобный просмотр формул и результатов
Простая ситуация. Имеем таблицу с формулами: 
Для удобства отладки и поиска ошибок, хотелось бы одновременно видеть и значения в ячейках, и формулы, по которым эти значения вычисляются. Например, вот так: 
Чтобы получить такую красоту, вам нужно сделать всего несколько простых шагов:
- Создать копию текущего окна книги, нажав на вкладке Вид (View) кнопку Новое окно (New Window) . В старых версиях Excel это можно сделать через меню Окно — Новое окно (Window — New window) .
- Разместить оба окна сверху вниз друг под другом, нажав на той же вкладке Вид (View) кнопку Упорядочить все (Arrange all) . В Excel 2003 и старше — меню Окно — Упорядочить все (Window — Arrange all) .
- Выбрав одно из получившихся окон, перейти в режим просмотра формул, нажав на вкладке Формулы (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 2013
Если Вы работаете с листом Excel, содержащим множество формул, может оказаться затруднительным отслеживать все эти формулы. В дополнение к Строке формул, Excel располагает простым инструментом, который позволяет отображать формулы.
Этот инструмент также показывает взаимосвязь для каждой формулы (при выделении), так что Вы можете отследить данные, используемые в каждом расчете. Отображение формул позволяет найти ячейки, содержащие их, просмотреть, проверить на ошибки, а также распечатать лист с формулами.
Чтобы показать формулы в Excel нажмите Ctrl+’(апостроф). Формулы отобразятся, как показано на рисунке выше. Ячейки, связанные с формулой, выделены границами, совпадающими по цвету с ссылками, с целью облегчить отслеживание данных.
Вы также можете выбрать команду Show Formulas (Показать формулы) на вкладке Formulas (Формулы) в группе Formula Auditing (Зависимости формул), чтобы показать формулы в Excel.
Даже, если отображение отключено, формулу можно посмотреть в Строке формул при выборе ячейки. Если Вы не желаете, чтобы формулы были видны пользователям Вашей книги Excel, можете скрыть их и защитить лист.
- Чтобы сделать это, выделите ячейки с формулами, которые требуется скрыть.
- На вкладке Home (Главная) в группе команд Cells (Ячейки) нажмите Format (Формат) и из выпадающего меню выберите пункт Format Cells (Формат ячеек).
- Появится диалоговое окно Format Cells (Формат ячеек). Установите флажок Hidden (Скрыть формулы) на вкладке Protection (Защита). Затем нажмите OK.
- Чтобы окончательно скрыть ячейки, Вы должны защитить лист. Для этого снова нажмите Format (Формат) на вкладке Home (Главная) и из выпадающего меню выберите пункт Protect Sheet (Защитить лист).
- Откроется диалоговое окно Protect Sheet (Защита листа). Убедитесь, что установлен флажок у пункта Protect worksheet and contents of locked cells (Защитить лист и содержимое защищаемых ячеек). Введите пароль для защиты листа, который в будущем позволит снять защиту и снова увидеть формулы. В разделе Allow all users of this worksheet to (Разрешить всем пользователям этого листа) установите флажки напротив тех задач, которые хотите разрешить пользователям. Нажмите OK.
- Для продолжения введите свой пароль еще раз в соответствующем поле диалогового окна Confirm Password (Подтверждение пароля).
- Можете заметить, что теперь при выборе ячейки, содержащей формулу, Строка формул остается пустой.
- Чтобы вновь показать формулы, нажмите Format (Формат) и выберите пункт Unprotect Sheet (Снять защиту листа).
- Введите пароль в диалоговом окне Unprotect Sheet (Снять защиту листа) и нажмите OK.
Формулы вновь будут видны при выделении ячеек.
Как узнать есть ли формула в ячейке?
Начиная с версий Excel 2013 среди стандартных функция появилась функция ЕФОРМУЛА (ISFORMULA) , которая позволяет определить наличие формулы в ячейке. Единственный аргумент функции — ссылка на ячейку: =ЕФОРМУЛА( A1 ) .
Функция возвращает ИСТИНА (TRUE) , если в ячейке есть формула и ЛОЖЬ (FALSE) если в ячейке константа.
Статья была написана ранее и описанные в ней способы применимы к любой версии Excel
В более ранних версиях определить наличие формулы в ячейке можно двумя способами.
Способ первый
Создание именованной формулы(без применения VBA — Visual Basic for Application)
- Выделяете ячейку B1
- жметe Ctrl+F3(вызов диспетчера имен) Если по каким-то причинам Ctrl+F3 не работает, то Диспетчер имен можно вызвать через меню:
Excel 2003 :Вставка-Имя-Присвоить.
Excel 2007-2010 :вкладка Формулы-Диспетчер имен. - в поле имя пишем — HasFormula(или любое понравившееся название, но не противоречащее правилам создания имен в Excel. Подробнее можно прочитать в статье: Именованные диапазоны)
- в поле Диапазон пишем формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1)
если изменить первый параметр на 6 =ПОЛУЧИТЬ.ЯЧЕЙКУ( 6 ;Лист1!A1) , то результатом будет текст формулы из ячейки, которая указана в функции ПОЛУЧИТЬ.ЯЧЕЙКУ
Теперь, записав в ячейку В1 : =HasFormula Вы узнаете, есть ли в ячейке А1 формула или же там константа.
Так же при помощи функций листа макросов можно получить и сам текст формулы(если в ячейке записана формула СЕГОДНЯ() , то ПОЛУЧИТЬ.ЯЧЕЙКУ вернет именно текст СЕГОДНЯ()). Для этого достаточно поменять формулу в поле Диапазон на такую:
=ПОЛУЧИТЬ.ФОРМУЛУ(Лист1!A1)
Недостатки данного метода — Вы привязаны к «левостороннему» определению формулы. Т.е. таким методом Вы можете узнать есть ли формула(или какая формула) в ячейке, расположенной слева от ячейки с именованной формулой. Для того, чтобы «узнать формулу справа» именованная формула должна выглядеть так: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!C1) ;для работы формулы обязательно должны быть разрешены макросы. Чтобы не зависеть от левостороннего или правостороннего определения можно закрепить столбцы(выделяете ссылку на ячейку — А1 и нажимаете F4. Ссылка будет меняться, появятся знаки доллара $. Если знак стоит перед именем столбца — $А1 — то смещение по столбцу происходить не будет. Так же со строками)
Для ПОЛУЧИТЬ.ФОРМУЛУ есть еще недостаток: формула будет отображаться в стиле ссылок R1C1, что не совсем удобно..Для работы такой функции должны быть разрешены макросы.
Способ второй
Еще один метод определения(на мой взгляд более удобный в использовании) — создание пользовательской функции:
Function IsFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False) 'Application.Volatile True If ShowFormula Then If Cell.HasFormula Then IsFormula = "Формула: " & IIf(Cell.HasArray, " & Cell.FormulaLocal & ">", Cell.FormulaLocal) Else IsFormula = "Значение: " & Cell.Value End If Else IsFormula = Cell.HasFormula End If End Function
Function IsFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False) ‘Application.Volatile True If ShowFormula Then If Cell.HasFormula Then IsFormula = «Формула: » & IIf(Cell.HasArray, «», Cell.FormulaLocal) Else IsFormula = «Значение: » & Cell.Value End If Else IsFormula = Cell.HasFormula End If End Function
Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Shift + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Теперь в ячейку записываем созданную формулу
=IsFormula( A2 )
Данная функция имеет два аргумента:
- Cell — ссылка на проверяемую ячейку
- ShowFormula — необязательный аргумент. Если присвоено значение ЛОЖЬ или 0 или опущен(не указан) — то функция вернет значение ИСТИНА или ЛОЖЬ(в зависимости от наличия или отсутствия формулы в ячейке). Если присвоено значение ИСТИНА или 1, то в функция вернет не просто ИСТИНА или ЛОЖЬ, а еще и текст самой формулы.
Формулу можно так же найти в диспетчере функций в категории Определенные пользователем — IsFormula и записывать её в ячейку через мастер функций.
Чтобы при изменении формулы в целевой ячейке сразу же изменялось её отображение в ячейке с данной функцией необходимо убрать апостроф(‘) перед Application.Volatile True (в файле-примере это уже сделано).
Недостатки данного метода — для работы функции обязательно должны быть разрешены макросы
Созданные функции можно так же использовать в условном форматировании для подсветки ячеек, содержащих формулы.
Tips_All_HasFormula.xls (31,0 KiB, 4 414 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!