Как посмотреть формулы в Excel (показать, найти и извлечь формулы)

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

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

Когда следует просматривать формулы в Excel?

Просмотр формул особенно полезен, когда:

• Аудит финансовых таблиц
• Отладка ошибок вычислений
• Проверка формул, созданных другими пользователями
• Проверка, ссылаются ли скопированные формулы на правильные ячейки
• Подготовка таблиц к проверкам на соответствие требованиям

Просмотр формулы в одной ячейке

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

Вариант 1: использование строки формул

1. Выделите ячейку, содержащую формулу.
2. Посмотрите на строку формул над листом, чтобы увидеть формулу.

Например, предположим, что B2 и C2 содержат 100 и 150 соответственно. Ячейка D2 использует формулу =SUM(B2:C2) для вычисления результата 250. При выделении D2 формула отображается в строке формул.

Вариант 2: использование режима редактирования

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

1. Дважды щелкните ячейку с формулой или нажмите F2 в Windows.

2. Excel отображает формулу внутри ячейки и выделяет ее ссылки.

   

3. Нажмите Esc, чтобы выйти без внесения изменений.

Совет: если строка формул скрыта, включите ее в разделе:

Вид > Показать > Строка формул.

Показать все формулы на листе Excel

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

Вариант 1: использование кнопки "Показать формулы"

1. Откройте лист, который хотите проверить.

2. Перейдите в Формулы > Показать формулы в группе Зависимости формул.

3. Excel отображает формулы вместо вычисленных результатов.

   

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

Вариант 2: использование сочетания клавиш

Нажмите Ctrl + ` (клавиша обратного апострофа, обычно расположена под Esc) в Windows, чтобы переключаться между формулами и вычисленными значениями.

Примечание: эта функция применяется к активному листу и изменяет только отображение формул. Сами вычисления остаются неизменными.

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

Поиск ячеек, содержащих формулы в Excel

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

1. Выделите лист или диапазон, который хотите найти.

2. Перейдите в Главная > Найти и выделить > Перейти к специальным.

   

3. Выберите Формулы.

4. Нажмите OK.

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

Просмотр формул и результатов рядом

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

Например, предположим, что ячейка D2 отображает 250, вычисленное с помощью формулы =SUM(B2:C2).

Чтобы отобразить формулу рядом с ее результатом:

1. Выберите пустую ячейку рядом с ней, например E2.

2. Введите следующую формулу:

   =FORMULATEXT(D2)
   

3. Нажмите Enter. Ячейка E2 отображает =SUM(B2:C2), а D2 продолжает показывать 250.

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

Результат выглядит так:

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

Примечание: согласно документации Microsoft по FORMULATEXT, функция возвращает #N/A, если указанная ячейка не содержит формулу, формула превышает 8 192 символа, формулу невозможно отобразить из-за защиты листа или указанная внешняя книга закрыта.

Чтобы оставить ячейки пустыми, когда FORMULATEXT возвращает ошибку, используйте:

=IFERROR(FORMULATEXT(D2),"")

Поскольку IFERROR перехватывает все ошибки, а не только #N/A, используйте этот вариант только тогда, когда пустой результат предпочтительнее отображения сообщения об ошибке.

Почему я не вижу формулы в Excel?

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

• Строка формул отсутствует: перейдите в Вид > Показать > Строка формул, чтобы включить ее.
• Формулы скрыты: лист может быть защищен, из-за чего скрытые формулы не отображаются в строке формул. Если у вас есть разрешение, перейдите в Рецензирование > Снять защиту листа. Чтобы формулы оставались видимыми при повторной защите листа, выделите нужные ячейки, откройте Формат ячеек > Защита и снимите флажок Скрытый.
• Формула отображается как текст, а не как результат: сначала проверьте, включена ли функция Показать формулы на вкладке Формулы. Если только некоторые ячейки отображают формулы как текст, проверьте, имеют ли они формат "Текст" или содержат ведущий апостроф. При необходимости измените формат ячейки на Общий, удалите непреднамеренный ведущий апостроф и введите формулу заново, нажав F2, а затем Enter.
• FORMULATEXT возвращает #N/A: проверьте, содержит ли указанная ячейка формулу. Ссылки на ячейки в закрытых внешних книгах также могут вызывать эту ошибку.

Продвинуто: извлечение формул Excel программно с помощью Python

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

В следующем примере используется Free Spire.XLS for Python, чтобы извлечь формулы из нескольких книг Excel и сохранить их в текстовый файл. Microsoft Excel не требуется устанавливать.

Шаг 1: Установите Free Spire.XLS

Выполните следующую команду pip в терминале:

pip install spire.xls.free

Шаг 2: Извлеките формулы из нескольких книг Excel

Создайте папку с именем Excel Files и поместите в нее книги .xlsx, которые хотите обработать.

Следующий скрипт сканирует каждую книгу, определяет ячейки, содержащие формулы, и экспортирует их расположение и выражения в текстовый файл.

from pathlib import Path
from spire.xls import Workbook

# Input folder and output file
input_dir = Path("Excel Files")
output_file = Path("Extracted Formulas.txt")

# Check the input folder
if not input_dir.is_dir():
    raise FileNotFoundError(f"Input folder not found: {input_dir}")

# Get all Excel files in the folder
excel_files = sorted(
    file for file in input_dir.glob("*.xlsx")
    if file.is_file() and not file.name.startswith("~{BODY_CONTENT}quot;)
)

if not excel_files:
    raise FileNotFoundError(f"No XLSX files found in: {input_dir}")

formula_count = 0

# Extract formulas from all workbooks
with output_file.open("w", encoding="utf-8") as output:
    for input_file in excel_files:

        # Load the Excel workbook
        workbook = Workbook()
        try:
            workbook.LoadFromFile(str(input_file))

            # Loop through all worksheets
            for i in range(workbook.Worksheets.Count):
                sheet = workbook.Worksheets[i]

                # Get the used range
                used_range = sheet.AllocatedRange

                # Find and extract formulas
                for cell in used_range:
                    if cell.HasFormula:
                        cell_address = cell.RangeAddressLocal.replace("{BODY_CONTENT}quot;, "")
                        formula = cell.Formula

                        # Write formula information to the text file
                        output.write(f"{input_file.name} | {sheet.Name}!{cell_address}: {formula}\n")
                        formula_count += 1
        finally:
            workbook.Dispose()

Шаг 3: Проверьте извлеченные формулы

После запуска скрипта откройте Extracted Formulas.txt. Результат будет выглядеть примерно так:

Budget.xlsx | Forecast!E2: =IF(D2>1000,D2*0.1,0)
Sales Report.xlsx | Summary!D2: =SUM(B2:C2)
Sales Report.xlsx | Summary!D3: =SUM(B3:C3)

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

Примечание: скрипт читает выражения формул, не изменяя исходные книги. Он явно не пересчитывает формулы. Free Spire.XLS имеет ограничения на количество листов и строк для старых файлов .xls, но эти ограничения не применяются к файлам .xlsx.

Итоги

Для одной формулы используйте строку формул или режим редактирования. Чтобы отобразить формулы на всем листе, используйте "Показать формулы". Чтобы найти ячейки с формулами, выберите "Перейти к специальным"; чтобы сравнить выражения и результаты, используйте FORMULATEXT. Если вам нужны выражения формул из многих книг .xlsx, метод Python может автоматизировать извлечение.

Часто задаваемые вопросы

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

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

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

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

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

Если формулы отображаются по всему листу, возможно, включена функция "Показать формулы". Нажмите Ctrl + ` или отключите Формулы > Показать формулы. Если только некоторые ячейки отображают формулы как текст, проверьте, имеют ли они формат "Текст" или содержат ведущий апостроф.

Можно ли извлечь формулы Excel без открытия Microsoft Excel?

Да. Библиотеки Python, такие как Free Spire.XLS, могут загружать книги Excel и извлекать формулы без запуска Microsoft Excel.

Связанные руководства по Excel

• Как группировать строки в Excel
• Как очистить или удалить фильтры в Excel
• Как удалить ячейки в Excel
• Как выделить пустые ячейки в Excel