Может ли Excel показать формулу и ее результат одновременно?

Question

Я знаю, что в Excel можно переключаться между отображением значений и отображением формул. Мне необходимо сдать задания для класса статистики в виде печатного листа Excel, в котором указаны как формула, так и результат. Прямо сейчас инструктор заставляет нас либо скопировать формулу и вставить ее в виде текста рядом с вычисленным значением, либо скопировать значение и вставить его рядом с формулой. Это очень неэффективно, подвержено ошибкам (если вы изменяете формулу или значения после выполнения копирования-вставки) и, как правило, трата времени.

Можно ли в Excel показать формулу и ее значение в одной ячейке? Если нет, есть ли какая-либо функция, которая будет отображать формулу из указанной ячейки в виде обычного текста, например, =showformula(A1) которая выводит =sum(A2:A5) вместо 25 (если это были формула и значение ячейки A1)?

Я использую Excel 2010, но общий ответ, который подходит для любого недавнего выпуска Excel, был бы неплох.

Answer 1

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

Нажмите Alt+F11, чтобы открыть редактор VBA, щелкните правой кнопкой мыши в Project Explorer и выберите Вставить -> Модуль. Вставьте следующий код:

Function GetFormula(cell)  
  GetFormula = cell.Formula  
End Function

Теперь вы можете использовать =GetFormula(A1) чтобы показать формулу этой ячейки.

Answer 2

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

Sub Show_Formulas()
    Dim rng As Range, cell As Range
    row_offset = 10
    col_offset = 0
    Set rng = Range(Cells(1, 1), Cells(5, 7))

    On Error GoTo ErrHandler
    For Each cell In rng
        cell.Offset(row_offset, col_offset) = "'" & cell.Formula
    Next cell
    Exit Sub

ErrHandler:
    MsgBox "The cow jumped over the moon", , "Error!", Err.HelpFile, Err.HelpContext

End Sub

Для каждой ячейки в указанном диапазоне (здесь жестко закодировано как A1:G5), она копирует формулу ячейки, защищенную апострофом, в ячейку с фиксированным смещением.  (См. Как добавить VBA в MS Office? руководство по использованию VBA в Excel.)  Вы просто должны помнить, чтобы запустить этот макрос, прежде чем печатать.

Или добавьте оператор PrintOut , , , True непосредственно перед Exit Sub , и этот макрос напечатает лист для вас.  (Вы просто должны помнить, чтобы печатать, используя этот макрос.)  Четвертый параметр PrintOut - это Preview , для которого установлено значение True чтобы Microsoft Excel вызывал предварительный просмотр перед печатью листа (что дает вам возможность отменить), или False (или опускал) для немедленной печати листа без каких-либо условий.

Или, если вы действительно хотите забыть запустить это, вы можете использовать Private Sub Worksheet_Change для обновления отображаемых формул в любое время, когда вы вносите какие-либо изменения в таблицу.

Answer 3

Альтернативой может быть использование LibreOffice.

Существует расширение с именем Показать формулу и значение одновременно. Он должен делать то, что вам нужно.

Answer 4

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

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

[Раскрытие информации: я автор FormulaDesk]

Answer 5

Вы можете создать дополнительный столбец, содержащий формулы:= REPLACE(target_cell; 1; 1; "")

Answer 6

Вот решение:

1. Поставьте апостроф (') в начале формулы
2. Скопируйте его в строку ниже или столбец рядом с ним
3. Вернитесь к исходной формуле и удалите апостроф

Это работает.

Answer 7

Объединить текст со значением Предположим, ячейка A1 содержит число 9999

="Total: "&TEXT(A1,"$#,##0.00")

Эта формула будет отображать «Итого: 9 999,00 $»

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

="Report printed on: "&TEXT(NOW(),"mmmm d, yyyy at h:mm AM/PM")