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

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

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

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

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

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

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

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

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

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

    formulas-to-values3.png

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

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

  1. Копируем выделенный диапазон Ctrl+C
  2. Тут же вставляем обратно сочетанием Ctrl+V
  3. Жмём Ctrl, чтобы вызвать меню вариантов вставки
  4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:
Замена формул на значения соч.клавиш

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

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

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

formulas-to-values4.gif

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

Способ 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, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

formulas-to-values.png

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

  • всё будет максимально быстро и просто
  • можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
  • в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
  • любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

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

 



Добрый день, Николай.
По классическому способу можно добавить вариант с панелью быстрого доступа (либо в приемах, либо на форуме это было подсмотрено, уже и  не вспомнить) - это для тех, кто привык к 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
Здравствуйте. макрос отличный, но вот у меня в книге есть защищённые паролем листы и они же ещё и супер скрыты. при выполнении макрос выдаёт ошибку и говорит что в книге есть эти самые защищённые листы и не хочет удалять от туда формулы, как быть с этим ???
Заранее спасибо!
02.07.2019 18:09:25
Здравствуйте Николай можно ли написать макрос для превращение в значение ячеек с заливкой?:)
03.07.2019 19:55:24
Привет!


Public Function Диап_Формулы_в_Значения( _
        r As Range, _
        ByVal interior_Color As Long)
'
    Dim r_Small As Range
    For Each r_Small In r.Areas
        With r_Small
            .Value = .Value
            .Interior.Color = interior_Color
        End With
    Next r_Small
End Function
03.07.2019 21:08:55
что это? не запускается вообше
02.07.2019 21:45:31
Здравствуйте!

Подскажите, пожалуйста, по какой причине не работает для вставки значения при использовании способа 4. Кнопка для вставки значений на Панели быстрого доступа в Microsoft Office 2007?

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

Кнопка добавляется, но она не активна!!!

Я подозреваю, что, может быть, данный способ в  Microsoft Office 2007 реализован по-другому? И описанный способ реализуется на версиях, начиная с 2010?
06.07.2019 01:18:05
Станислав, а вы предварительно ячейку с формулой-то скопировали? Эта кнопка вставляет значение, но сначала его надо в буфер скопировать.
04.07.2019 09:29:43
Добрый день,
Я часто использую ещё один способ превращения формул со ссылками на другие файлы в значения. К примеру, формула VLOOKUP (он же ВПР) или любые другие, где есть ссылка на другие Книги Excel
Способ достаточно удобный и быстрый (особенно, если вывести его выполнение на кнопку с макро-командой)
Идём в меню Data -> Edit Links -> жмём Break Links. Всё. Все формулы Книги, связанные с другими файлами, превращаются в значения
Вот макро-команда, если кому-нибудь интересно:

Sub BreakAllWorkbookLinks()
'Breaks all workbook links and transforms all external references into values
Dim aLinks As Variant
Dim i As Integer
With Application.ActiveWorkbook
   aLinks = .LinkSources(xlExcelLinks)
   If Not IsEmpty(aLinks) Then
 For i = 1 To UBound(aLinks)
Debug.Print aLinks(i)
.BreakLink _
 Name:=aLinks(i), _
 Type:=xlLinkTypeExcelLinks
 Next i
   End If
End With

End Sub
06.07.2019 01:19:34
Это замена на значения не формул, а внешних ссылок на другие книги Excel. Внутренние ссылки между листами и обычные формулы это не заменит :)
Наверх