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

Иногда формула в ячейке нужна лишь на короткое время - только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда - если удалить ячейки, то формула "ломается", как это говорится. Т.е. в ячейке получается либо #ЗНАЧ! либо #ССЫЛКА!. А иногда необходимо просто зафиксировать значение, полученное в результате работы формулы. Т.е. чтобы формулы там не было, а было только значение. Как ни странно, но делается это проще простого:
Выделяем ячейку(несколько ячеек) с формулами -Копируем -Правая кнопка мыши - Специальная вставка(Paste Special) -Значения(Values).

КАК БЫСТРО ВЫЗЫВАТЬ СПЕЦИАЛЬНУЮ ВСТАВКУ ЗНАЧЕНИЙ:
Данную команду можно вынести на Панель быстрого доступа и использовать в одно нажатие тогда, когда нужно. Как это сделать:

• 2010 Excel и выше Файл- Параметры- Настройка. В выпадающем списке Выбрать команды из: выбираем Все команды. Находим команду Вставить значения и добавляем
• 2007 Excel Меню- Параметры Excel- Настройка. В выпадающем списке Выбрать команды из: выбираем Все команды. Находим команду Вставить значения и добавляем
• 2003 Excel: Сервис- Настройка- вкладка Команды- категория Правка- Вставить значения.

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

Вставка значений при помощи кода на Visual Basic for Application
Тоже самое можно сделать при помощи кода VBA:

Sub Formulas_To_Values()
    Selection.Value = Selection.Value
End Sub

Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA(Alt+F11) -Insert -Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt+F8 -выделяем Formulas_To_Values -Выполнить(Run).
Данный код заменяет формулы в выделенном диапазоне на значения. Только необходимо учитывать, что выделенный диапазон должен быть неразрывным. Иначе код выдаст ошибку. А такой код:

Sub All_Formulas_To_Values()
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

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

А следующий код заменит формулы на значения во всех ячейках на всех листах активной книги:

Sub All_Formulas_To_Values_In_All_Sheets()
    Dim wsSh As Worksheet
    For Each wsSh In Worksheets
        wsSh.UsedRange.Value = wsSh.UsedRange.Value
    Next wsSh
End Sub

У этого кода нюансы со связями и скрытыми ячейками точно такие же, как и у предыдущего, но уже в масштабах всех листов книги. Плюс, даже если какие-то листы скрыты - замена будет произведена и на них тоже.

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

Sub All_Formulas_To_Values_OnlyVisible()
    Dim rRng As Range, rArea As Range
    If Selection.Count = 1 Then
        Set rRng = ActiveCell
    Else
        Set rRng = Selection.SpecialCells(12)
    End If
    For Each rArea In rRng.Areas
        rArea.Value = rArea.Value
    Next rArea
End Sub

ДОБАВИТЬ КОМАНДУ "ВСТАВИТЬ ЗНАЧЕНИЯ" В КОНТЕКСТНОЕ МЕНЮ:
Если один раз выполнить код ниже, то в контекстном меню по правому клику мыши появится стандартная команда Вставить значения(Insert Values). Она полностью дублирует стандартную, только вызвать её быстрее - одно нажатие и все.:

Sub Add_PasteSpecials()
    Dim cbb
    Set cbb = Application.CommandBars("Cell").FindControl(ID:=370)
    If Not cbb Is Nothing Then cbb.Delete 'удаляем пункт, если он был уже добавлен ранее
    Application.CommandBars("Cell").Controls.Add ID:=370, before:=4
End Sub

Также см.:
Вставить значения
Формулы перестали считать - как исправить?

    Видеоуроки

Поиск по меткам