Как удалить в ячейке формулу, оставив значения?
Иногда формула в ячейке нужна лишь на короткое время - только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда - если удалить ячейки, то формула "ломается", как это говорится. Т.е. в ячейке получается либо
Выделяете ячейку/несколько ячеек с формулами - Копируете - Правая кнопка мыши - Специальная вставка - Значения
Вот и все. Больше формул нет.
Так же подобное можно сделать при помощи кода VBA:
Sub Formulas_To_Values() Selection.Value = Selection.Value End Sub |
Данный код заменяет формулы в выделенном диапазоне на значения. Только необходимо учитывать, что выделенный диапазон должен быть неразрывным. Иначе код выдаст ошибку. А такой код
Sub All_Formulas_To_Values() ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value End Sub |
заменит все формулы на листе в значения. При этом будут разорваны все связи на данном листе. Это стоит учитывать. Кстати, иногда именно для этого и применяют подобный код.
И в качестве бонуса ниже приведен код, который заменит все формулы на всех листах активной книги в значения:
Sub All_Formulas_To_Values_In_All_Sheets() Dim wsSh As Worksheet For Each wsSh In Sheets 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 |
КАК БЫСТРО ВЫЗЫВАТЬ:
Вообще данную команду можно вывести на Быструю панель и использовать в одно нажатие тогда, когда нужно.
- 2010 Excel Файл- Параметры- Настройка. Выбрать команды из: Все команды. Находите команду Вставить значения и добавляете;
- 2007 Excel Меню- Параметры Excel- Настройка. Выбрать команды из: Все команды. Находите команду Вставить значения и добавляете;
- 2003 Excel: Сервис- Настройка- вкладка Команды- категория Правка- Вставить значения.
Теперь у Вас появится значок на Быстрой панели.
ДОБАВИТЬ В КОНТЕКСТНОЕ МЕНЮ ЯЧЕЕК:
Если один раз выполнить код:
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 |
то в контекстном меню мыши появится команда - "Вставить значения". Она полностью дублирует стандартную, только вызвать её быстрее - одно нажатие и все.
Также см.:
Вставить значения
Формулы перестали считать - как исправить?
Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Очень помогло.
Формула "только в видимых ячейках" супер.
Уже несколько месяцев мучаюсь копированием значений не трогав итоговых формул между строками.
Спасибо!