Как вывести формулу в excel: Отображение и печать формул — Служба поддержки Майкрософт

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

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

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

Для пользователя Excel всегда встаёт вопрос: как же узнать где в ячейке есть формула, а где нет? 

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

Способ №1

Определить есть ли формула в ячейке нам поможет функция =ЕФОРМУЛА.

Результатом вычислений будет два значения: ИСТИНА — формула в ячейке есть; ЛОЖЬ — формулы в ячейке нет.

Ячейки с формулами найдены. Преобразовать формулу в текст поможет функция =Ф.ТЕКСТ. Она возвращает формулу в виде строки. Есть и минус: её можно использовать только с версии Excel 2013 и выше.

 

Способ №2

Воспользуемся гибкими возможностями именованных диапазонов в Excel. Переходим на вкладку «Формулы», в блоке кнопок «Определенные имена» нажимаем «Диспетчер имён».

Щёлкаем «Создать», далее вводим имя диапазона (например, ПОЛУЧИТЬФОРМУЛУ), область оставим «Книга», поле «Примечание» можно оставить пустым, в поле «Формула» напишем =ПОЛУЧИТЬ.ЯЧЕЙКУ(6;Лист2!C1). В примере формула находится в ячейке С1, у вас она может быть где угодно, но важно помнить одно — такой способ работает только в ячейке правее ячейки с формулой!

 

Имя создано, осталось ввести его в ячейку. Вводим равно и пишем начальные буквы имени, созданного выше.

 

Как только оно появилось сразу щелкаем по нему два раза.

 

Подтверждаем ввод. Видим результат.

 

Способ №3

Воспользуемся созданием пользовательской функции на VBA.

Добавим новый модуль (как это сделать можно прочитать ЗДЕСЬ):

  1. Вкладка «Разработчик», блок кнопок «Код», кнопка «Visual Basic»;
  2. Далее «Insert» — > «Module».

Вставляем код:

Function FFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False)
‘Application.Volatile True
If ShowFormula Then
If Cell.HasFormula Then
FFormula = «Текст формулы: » & IIf(Cell.HasArray, «{» & Cell.FormulaLocal & «}», Cell.FormulaLocal)
Else
FFormula = «Значение ячейки: » & Cell.Value
End If
Else
FFormula = Cell.HasFormula
End If
End Function

Разбор функции будет в видео ниже. Теперь нам остаётся ввести созданную функцию. Обязательно ввести через точку с запятой «Истина», чтобы активировать возможность показа формулы переменной «ShowFormula«.

 

Подтверждаем ввод, смотрим на результат.

 

Если вы знаете ещё варианты решения этой задачи — оставляйте комментарии под статьёй!

Видео:

Преобразование формул в значения

2364
10.01.2015
Скачать пример


Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из «калькулятора-переростка» в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:

  • Вы хотите зафиксировать цифры в вашем отчете на текущую дату.

  • Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).

  • Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).

  • Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании «сползут» все ссылки в формулах.


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

Способ 1. Классический


Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

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

  2. Скопируйте его правой кнопкой мыши – Копировать (Copy).

  3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):


    либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:


    Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.

    В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

Способ 2. Только клавишами без мыши


При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

  1. Копируем выделенный диапазон Ctrl+C

  2. Тут же вставляем обратно сочетанием Ctrl+V

  3. Жмём Ctrl, чтобы вызвать меню вариантов вставки

  4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:

Способ 3. Только мышью без клавиш или Ловкость Рук


Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

  1. Выделяем диапазон с формулами на листе

  2. Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место

  3. В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).


После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)

Способ 4. Кнопка для вставки значений на Панели быстрого доступа


Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:



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



Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:


Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу


Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.


Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так: 

Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
    Dim smallrng As Range
    For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub


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

Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
    For Each ws In ActiveWorkbook. Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub


Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью. 

Способ 6. Для ленивых


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


В этом случае:

  • всё будет максимально быстро и просто

  • можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно

  • в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры

  • любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

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

  • Что такое макросы, как их использовать, копировать и запускать

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

  • Как считать в Excel без формул


 

Различные способы отображения формул в Excel

  1. Информационный бюллетень
  2. ДОПОЛНИТЕЛЬНЫЙ КРЕДИТ

Венди Титц, дипломированный бухгалтер, доктор философии; Дженнифер Кайнас, CPA, DBA; и Трейси Миллер-Ноблс, CPA

Существует несколько различных способов отображения формул в Excel. Это может быть полезно при выяснении, почему определенная формула работает неправильно, или при оценке рабочих листов учащихся, чтобы убедиться, что они правильно используют формулы.

Отображение формул с помощью команды «Показать формулы»

Чтобы отобразить формулы в Excel, вы можете щелкнуть вкладку «Формулы» и затем нажать «Показать формулы» .

Это покажет все формулы на листе Excel.

Включение и выключение формул

Вы также можете использовать сочетание клавиш для включения и выключения формул. На клавиатуре, набрав комбинацию клавиш Ctrl+`, вы включите (или отобразите) формулы. (Обратите внимание, что ` в этом ярлыке — это знак серьезного ударения, обычно расположенный слева от клавиши 1 на клавиатуре.) Повторный ввод сочетания клавиш Ctrl+` отключит формулы.

Отображение формул с помощью функции FORMULATEXT

Иногда вам может понадобиться, чтобы формула отображалась в виде текстовой строки рядом с формулой. Это может быть особенно полезно, когда вы хотите увидеть и результат формулы, и формулу на одном листе, или при представлении формул учащимся. В этих случаях вы можете использовать функцию ФОРМУЛАТЕКСТ.

Функция FORMULATEXT возвращает формулу в виде текстовой строки, как показано на снимке экрана ниже. В этом примере, если вы хотите отобразить формулу ячейки D3 в ячейке F3, вы должны ввести следующую формулу: =ФОРМУЛАТЕКСТ(D3).

Есть несколько важных моментов, о которых следует помнить при использовании FORMULATEXT. Во-первых, обратите внимание, что вы можете использовать его для ссылки на ячейку на другом листе или в книге, но книга должна быть открыта. В противном случае Excel вернет значение ошибки #Н/Д.

Excel также вернет значение ошибки #Н/Д, если вы используете ФОРМУЛАТЕКСТ в ячейке, не содержащей формулы, или если формула не может быть отображена из-за защиты рабочего листа.

— Венди Титц, CPA, CGMA, Ph.D., 9 лет0021 — профессор бухгалтерского учета в Кентском государственном университете в Кенте. Огайо; Дженнифер Кайнас, CPA, DBA, , преподаватель бухгалтерского учета в Университете Южной Флориды в Тампе; и Трейси Миллер-Ноублс, CPA, , является адъюнкт-профессором бухгалтерского учета в Общественном колледже Остина в Остине, штат Техас. См. их сайт AccountingIsAnalytics.com для ресурсов, которые они разработали для обучения аналитике данных в начальном бухгалтерском учете. Чтобы прокомментировать эту статью или предложить идею для другой статьи, свяжитесь со старшим редактором Кортни Вьен по адресу [email protected].

Особенности

{/ne}
{#featureImage.renditions.breakpoints}

{/featureImage.renditions.breakpoints}

{Название статьи}

Как отображать формулы в Excel

В этом кратком руководстве вы узнаете, как легко отображать формулы в Excel 2016, 2013, 2010 и более ранних версиях. Кроме того, вы узнаете, как печатать формулы и почему Excel иногда показывает формулу, а не результат в ячейке.

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

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

  • Как отображать формулы в Excel
  • Печать формул в Excel
  • Почему Excel не показывает результат формулы и как это исправить

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

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

1. Параметр «Показать формулы» на ленте Excel

На листе Excel перейдите на вкладку Формулы > группу Аудит формул и нажмите кнопку Показать формулы .

Microsoft Excel сразу отображает формулы в ячейках вместо их результатов. Чтобы вернуть рассчитанные значения, нажмите кнопку Показать формулы еще раз, чтобы отключить ее.

2. Показывать формулы в ячейках вместо их результатов в параметрах Excel

В Excel 2010 и более поздних версиях перейдите к Файл > Параметры . В Excel 2007 нажмите Кнопка Office > Параметры Excel .

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

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

3. Ярлык Excel для отображения формул

Самый быстрый способ просмотреть каждую формулу в электронной таблице Excel — нажать следующий ярлык: Ctrl + ` строку с цифровыми клавишами (рядом с клавишей с цифрой 1).

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

Примечание. Какой бы из вышеперечисленных методов вы ни использовали, Microsoft Excel покажет все формулы текущего рабочего листа . Чтобы отобразить формулы на других листах и ​​книгах, вам нужно будет повторить процесс для каждого листа в отдельности.

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

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

Как распечатать формулы в Excel

Если вы хотите распечатать формулы в электронной таблице Excel вместо распечатки вычисленных результатов этих формул, просто используйте любой из 3 методов, чтобы отобразить формулы в ячейках, а затем распечатайте рабочий лист, как вы обычно печатайте файлы Excel ( File > Print ). Вот и все!

Почему Excel показывает формулу, а не результат?

С вами когда-нибудь случалось, что вы вводите формулу в ячейку, нажимаете клавишу Enter… и Excel все равно показывает формулу вместо результата? Не волнуйтесь, с вашим Excel все в порядке, и мы скоро исправим эту ошибку.

В целом, Microsoft Excel может отображать формулы вместо расчетных значений по следующим причинам:

  1. Возможно, вы случайно активировали режим Показать формулы, нажав соответствующую кнопку на ленте или нажав сочетание клавиш CTRL+`. Чтобы вернуть рассчитанные результаты, просто отключите Показать кнопку «Формулы» или снова нажать CTRL+`.
  2. Возможно, вы случайно ввели пробел или одинарную кавычку (‘) перед знаком равенства в формуле:

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

  3. Перед вводом формулы в ячейку вы, возможно, установили форматирование ячейки на Текст . В этом случае Excel также воспринимает формулу как обычную текстовую строку и не вычисляет ее.

Чтобы исправить эту ошибку, выберите ячейку, перейдите на вкладку Главная > группу Номер и установите форматирование ячейки на Общее , а в ячейке нажмите F2 и ENTER.

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

Читайте также: