Забыли пароль?


Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (16)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

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

 

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

Выделяете ячейку/несколько ячеек с формулами - Копируете - Правая кнопка мыши - Специальная вставка - Значения

Специальная вставка

Вот и все. Больше формул нет. Так же подобное можно сделать при помощи кода 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 Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 47 комментариев
  1. Александр, никак. Если у Вас одна формула массива записана в группе ячеек и часть этой группы скрыта и Вы не хотите заменять в этой части формулы на значения - не получится. Формулы массива, введенные разом в диапазон могут быть изменены только если весь диапазон захвачен. И никак иначе.

  2. Александр:

    @Дмитрий(Админ)
    Дмитрий, а как необходимо изменить предложенный код, чтобы заменялись только те ячейки, формулы которых не являются формулами массива??

  3. Только еще одним циклом:

    For Each rCell In Selection
        If rCell.HasArray = False Then
             rCell.Value = rCell.Value
        End If
    Next rCell

    Только под свои условия подгоните(только видимые ячейки или еще как).

  4. Сергей Б.:

    Выполнил три раза код, теперь в контекстном меню три строки "Вставить значения" - как убрать две ненужные ?

  5. Сергей Б.:

    введение строки
    Application.CommandBars("Cell").Reset
    в редакторе VBA не дало результатов... или я не то делаю

  6. Сергей Б.:

    Сработало почему-то только на следующий день перезагрузки компьютера *dont_know*

  7. Станислава:

    Здравствуйте, Дмитрий!
    Подскажите, пожалуйста, есть ли способ быстро:
    1) узнать, на каких листах есть связи с данным листом (в пределах одной книги)
    2) разорвать на листе связи с другим листом данной книги (т.е. заменить на значения только те ячейки, которые ссылаются на такой-то лист).

    Заранее спасибо. Надеюсь, что понятно изложила вопрос... Практически, задача такая: нужно удалить из книги некоторые листы, предварительно убедившись, что на них не ссылаются остающиеся.

  8. andrey135:

    Здравствуйте, как заменить на значения только те формулы которые содержат ссылки на внешние источники, формулы которые ссылаются на листы внутри книги оставить без изменений?

  9. Евгений:

    Я два раза выполнил код
    Sub Add_PasteSpecials()
    Application.CommandBars("Cell").Controls.Add ID:=370, before:=4
    End Sub
    И у меня теперь 2 пункта подряд в выпадающем меню. Подскажите пожалуйста, как удалить один лишний.

  10. Евгений, комментарии к статье читать не пробовали? :-) На первой странице комментариев есть ответ.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2019 Excel для всех   Войти