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

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

ПРЕОБРАЗОВАНИЕ ФОРМУЛ В Фактическое значение

• Нажмите F2 + F9, чтобы преобразовать формулы в Фактическое значение
• Используйте Excel Вставить специально, чтобы преобразовать формулы в Фактическое значение
  • Нажмите Alt + E + S + V (Excel Вставить специально)
  • Используйте контекстное меню (Excel Вставить специально)
• Хитрость с перетаскиванием правой кнопкой мыши для преобразования формул в Фактическое значение
• Скрипт VBA (макрос) для преобразования формул в Фактическое значение
• Всего один щелчок для преобразования формул в Фактическое значение

СТРОКИ Преобразовать формулы в текст

• Добавьте апостроф (') к строкам Преобразовать формулы в текст
• Хитрость с заменой (Ctrl + H) для преобразования строк в Преобразовать формулы в текст
• Функция Показать формулы Excel для преобразования строк в Преобразовать формулы в текст
• Скрипт VBA (макрос) для преобразования строк в Преобразовать формулы в текст
• Всего два щелчка для преобразования строк в Преобразовать формулы в текст

Преобразовать текст в формулу

• Скрипт VBA (макрос) для преобразования в Преобразовать текст в формулу
• Всего два щелчка для преобразования в Преобразовать текст в формулу

Возможно, вы столкнётесь с различными ситуациями, когда потребуется преобразовать формулы в Преобразовать формулы в текст или значения в Excel:

Вам нужно скопировать фактические значения из файла Excel в другой файл…
Вам нужно отправить файл Excel другим пользователям, но некоторые функции являются volatile (нестабильными) — их значения будут пересчитываться при открытии файла на другом компьютере или в другой версии Excel…
Вы не хотите, чтобы получатель файла Excel знал, какие формулы вы использовали: ведь они — результат вашей напряжённой работы, или ваша компания взимает плату с клиентов за создание отчётов со сложными формулами. Поэтому вам необходимо заменить формулы их результатами…

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

---

ПРЕОБРАЗОВАНИЕ ФОРМУЛ В Фактическое значение

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

Нажмите F2 + F9, чтобы преобразовать формулы в Фактическое значение

Этот способ работает только с одной ячейкой, зато он предельно прост в использовании и легко запоминается. Он идеально подойдёт тем, кому нужно отредактировать всего одну или несколько ячеек. Конкретные шаги следующие:

1. Наведите курсор на ячейку и нажмите F2 или дважды щёлкните по ячейке, чтобы начать редактирование.

2. Нажмите F9, а затем — ENTER.

---

Используйте Excel Вставить специально, чтобы преобразовать формулы в Фактическое значение

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

• Нажмите Alt + E + S + V (Excel Вставить специально)

1. Скопируйте исходную ячейку или диапазон, которые вы хотите преобразовать в значения.

2. Удерживая Alt, последовательно нажмите E, S и V.

3. Нажмите OK или клавишу ENTER.

• Используйте контекстное меню (Excel Вставить специально)

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

1. Скопируйте исходную ячейку или диапазон, которые вы хотите преобразовать в значения.

2. Щёлкните правой кнопкой мыши по целевой ячейке (ячейкам) и выберите значок «Значения», расположенный ниже.

Или выполните следующие действия для получения более подробных инструкций:

1. Скопируйте исходную ячейку или диапазон, которые вы хотите преобразовать в значения.

2. Щёлкните правой кнопкой мыши по нужной ячейке (ячейкам) и выберите пункт Вставить специально.

3. Выберите Значения и нажмите OK.

√ Небольшой совет: если в верхнем левом углу целевых ячеек появились зелёные треугольники, выделите диапазон ячеек — появится кнопка «Отслеживание ошибки» (жёлтый значок с восклицательным знаком). Нажмите её и выберите пункт «Преобразовать в число». Зелёные треугольники исчезнут.

---

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

Если вам не очень нравится Вставить специально, вы можете воспользоваться Преобразовать формулы в значения следующим способом:

1. Выделите исходную ячейку или диапазон ячеек.

2. Установите курсор на границу выделенной области — он изменится на значок перемещения.

3. Нажмите правую кнопку мыши (не отпуская её), перетащите ячейку в нужное место и отпустите — вы можете даже вернуть её обратно в исходное положение.

4. Выберите пункт «Копировать сюда как значения».

---

Скрипт VBA (макрос) для преобразования формул в Фактическое значение

Если вы привыкли работать с VBA (Visual Basic for Applications) в Excel, мы подготовили для вас специальный макрос. Выполните следующие действия: (Внимание: после запуска приведённого ниже макроса отменить его действие будет невозможно.)

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

2. Нажмите Alt + F11 — откроется окно Microsoft Visual Basic for Applications.

3. Нажмите Вставка, затем выберите пункт Модуль в раскрывающемся списке.

4. Вставьте приведённый ниже код в окно Модуль.

Sub ConvertFormulasToValues()
'Update by Extendoffice
    Dim xRg As Range
    Dim xCell As Range
    Dim xRet As VbMsgBoxResult
    Dim SLocation As String
    On Error Resume Next
    xRet = MsgBox("You can't undo this operation. " & "Click Yes to copy the workbook automatically?", vbYesNoCancel, "Kutools for Excel")
    Select Case xRet
        Case vbYes
            'Backup a copy of the workbook to the same location.
            ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & "\Backup" & " " & ActiveWorkbook.Name
            SLocation = ActiveWorkbook.Path & "\Backup" & " " & ActiveWorkbook.Name
            MsgBox "The backup is: " & SLocation, vbInformation, "Kutools for Excel"
        Case vbCancel
            Exit Sub
    End Select
    'Please select the range which you want to convert formulas to values.
    Set xRg = Selection
    If TypeName(xRg) = "Range" Then
        For Each xCell In xRg
            If xCell.HasFormula Then
                xCell.Formula = xCell.Value
            End If
        Next
    End If
End Sub

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

После выполнения операции результат будет выглядеть так:

---

Всего один щелчок для преобразования формул в Фактическое значение

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

1. Выделите исходный диапазон ячеек и найдите группу Диапазоны и ячейки на вкладке Kutools.

2. Нажмите кнопку Преобразовать в фактические.

Теперь вы можете наслаждаться результатом.

Нажмите, чтобы скачать Kutools для Excel и получить 30-дневную бесплатную пробную версию.

---

СТРОКА Преобразовать формулы в текст

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

Добавление апострофа (') к строкам Преобразовать формулы в текст

Апостроф предотвращает интерпретацию текстовой строки Excel как формулы. Поэтому добавление апострофа всегда полезно, когда вы хотите отобразить именно текстовую строку, а не результат формулы. Это очень просто — выполните следующие шаги:

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

2. Добавьте апостроф (') перед знаком равенства (=).

3. Нажмите ENTER, и в ячейке появится текстовая строка.

---

Приём с заменой (Ctrl + H) для Преобразовать формулы в текст строк

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

1. Нажмите Ctrl + H, и вы увидите окно Поиск и замена.

2. Введите = в поле Найти, а затем — ‘= в поле Заменить на.

3. Нажмите Заменить все. Все формулы появятся на текущем листе.

---

Функция Показать формулы Excel для Преобразовать формулы в текст строк

В Excel есть скрытая на вкладке Формулы функция под названием Показать формулы, с помощью которой результаты формул легко преобразуются в текстовые строки формул:

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

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

---

Скрипт VBA (макрос) для Преобразовать формулы в текст строк

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

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. Нажмите Вставка, затем в раскрывающемся списке выберите Модуль.

3. Вставьте следующий код в окно Модуль.

Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

4. Вернитесь на лист и введите формулу =ShowF(source cell) в целевую ячейку, например:

5. Нажмите ENTER. Теперь вы увидите отображаемую формулу.

---

Всего два щелчка для Преобразовать формулы в текст строк

Если вы не хотите удалять формулы, но вам нужно легко переключаться между формулами и текстовыми строками, инструмент Kutools для Excelотлично подойдёт для этой цели в Excel:

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

Вот как выглядит результат после преобразования.

Нажмите, чтобы скачать Kutools для Excel и получить 30-дневную бесплатную пробную версию.

---

Преобразовать текст в формулу

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

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

Скрипт VBA (макрос) для Преобразовать текст в формулу

1. Нажмите Alt + F11, и откроется окно Microsoft Visual Basic for Applications.

2. Нажмите Вставка, затем выберите Модульна Раскрывающийся список.

3. Вставьте следующий код в окно Модуль.

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

4. Вернитесь на лист и введите формулу =Eval(source cell) в целевую ячейку, например:

5. Нажмите ENTER. Теперь вы увидите результат формулы.

---

Всего два щелчка для Преобразовать текст в формулу

Если у вас установлен Kutools для Excelв Excel для Преобразовать текст в формулу, это займёт всего два щелчка: на вкладке Kutoolsпросто нажмите Содержимоеи выберите Преобразовать текст в формулупосле выделения диапазона ячеек, которые требуется преобразовать, как показано ниже:

Вот как выглядит результат после преобразования.

Нажмите, чтобы скачать Kutools для Excel и получить 30-дневную бесплатную пробную версию.