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

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

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

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

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

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

  1. Выделите диапазон с формулами, которые нужно заменить на значения.
  2. Скопируйте его (Ctrl+C или правой кнопкой мыши – Копировать).
  3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):

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

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

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

    formulas-to-values3.png

Способ 2. Ловкость рук

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

  1. выделяем диапазон с формулами на листе
  2. хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
  3. в появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).

formulas-to-values4.gif

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

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

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

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с 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. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью. 

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

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

formulas-to-values.png

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

 



Добрый день, Николай.
По классическому способу можно добавить вариант с панелью быстрого доступа (либо в приемах, либо на форуме это было подсмотрено, уже и  не вспомнить) - это для тех, кто привык к CTRl +C, CTRL +V -  только будет ALT+ назначенная клавиша.
12.01.2015 23:37:12
Тоже использую данный способ - вывел на панель быстрого доступа кнопку и использую ее
13.01.2015 06:34:27
+1 Так же пользуюсь данным способом.:)
14.01.2015 15:04:44
Всегда пользуюсь сочетанием Ctl+Alt+V и одно нажатие кнопки "вверх"
26.01.2015 12:08:31
Можно записать макрос через кнопку записи макроса и назначить комбинацию клавиш для тех кто боится Visual Basic
20.02.2015 10:46:57
Для одной ячейки самый быстрый способ: F2 -> F9 -> Enter
18.05.2015 15:27:21
Спасибо! Помогли решить проблему превращения в значения группы выделеных ячеек используя фильтр :)
18.12.2015 20:16:24
Добрый день!
Вы просто мастер.
Есть книга с несколькими листами. С помощью Shift выделяю листы, далее диапазон столбца и запускаю макрос по замене формулы на значение


Sub Заменятель()
For Each cell In Selection
cell.Formula = cell.Value
Next cell
MsgBox "Я всё сделал!"
End Sub

но его приходится повторять на каждом листе.
Подскажите возможно ли выполнить этот макрос на всех выделенных листах без постоянных повторений.
Спасибо, огромное!
13.01.2016 15:27:18
Хотел помочь (стало интересно как это его сделать.
При том как я эксперементировал - поставил ваш код в модуль - запустил - и все... ничего больше я в Excel сделать не мог - пришлось через диспетчер задач отключать excel! (хотел поставить паузу у выполнении не помогло. - также повис)... сtrl+alt+pause - у меня не работает.... так что это дурная затея
Только в одном листе (формат книги 97-2003 у меня на 100 строк ушло - чуть более 10 секунд... (С Excel 2007 - трудно себе представить сколько бы вы ждали ))- Так что удачи через макрос я бы это точно не делал!!!!
Намного проще 1 классическим методом. - все делается в 3 клика - без задержек. Лист только со значениями готов!!!
17.06.2016 10:09:41
Добрый день!
Пробовал способ с макросом, но он некорректно работает на отфильтрованных диапазонах.
После первой же скрытой строки он начинает вставлять некорректные значения.

Мне кажется что это из-за того что он делает это для всего выделенного диапазона, однако если его в цикле заставить это делать для каждой выделенной ячейки по отдельности то все будет нормально, но я не могу реализовать такой вариант (не хватает знаний).
Не могли бы вы помочь???
14.11.2016 16:26:13
Здравствуйте, Николай!
Передо мной стоит более комплексная задача:
1) нужно скопировать ячейку из Книга1 в первую пустую ячейку в Книга2,
2) в этой самой ячейке изначально записана формула, ее нужно заменить на значение.
3) всё это с нажатия кнопки в Книга1.

У вас конечно можно найти эти макросы по отдельности, но вот объединить их вряд ли у меня получится(
Прошу вас, помогите разобраться с этим!
01.05.2017 02:42:44
Огромное спасибо )) Помогло)) Полезно))
23.11.2017 18:31:45
В последнем макросе ошибка, вот исправленный:
Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
    For Each ws In ActiveWorkbook.Worksheets
     ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub
23.11.2017 21:44:28
Спасибо, Даниил! Поправил опечатку.
20.12.2017 13:46:39
"Ловкость рук" - супер метод. Открыл для себя.
Прочие уже утомили )
08.08.2018 09:29:13
Здравствуйте. макрос отличный, но вот у меня в книге есть защищённые паролем листы и они же ещё и супер скрыты. при выполнении макрос выдаёт ошибку и говорит что в книге есть эти самые защищённые листы и не хочет удалять от туда формулы, как быть с этим ???
Заранее спасибо!
Наверх