Иногда формула в ячейке нужна лишь на короткое время — только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда — если удалить ячейки, то формула «ломается», как это говорится. Т.е. в ячейке получается либо #ЗНАЧ! либо #ССЫЛКА!. А иногда необходимо просто зафиксировать значение, полученное в результате работы формулы. Т.е. чтобы формулы там не было, а было только значение. Как ни странно, но делается это проще простого:
Выделяем ячейку(несколько ячеек) с формулами -Копируем -Правая кнопка мыши — Специальная вставка(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) —InsertModule. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш 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

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

Loading

50 комментариев

  1. Отличный макрос, только вот он почему-то всё форматирование сбивает. К примеру и после выполнения макроса становятся обычным текстом. Как-то можно этого избежать?

    1. Если честно макрос форматирование вообще не трогает. Он работает исключительно со значениями. И приведенный текст никак не мог быть преобразован во что-то другое. Видимо у Вас применено не простое форматирование, а что-то еще. Без файла сложно сказать — советую обратиться в форум, там можно приложить пример файла. Или можете выслать файл мне на почту(на странице Контакты есть), я как будет возможность посмотрю.

      1. Согласен, не должен трогать, но тем не менее, делает это :)

        Спасибо, я нашёл корявенький вариант решения, так он форматирование не портит.

        For Each Cell In ActiveSheet.UsedRange
        If Cell.HasFormula() = True Then
        Cell.Activate
        Selection.Value = Selection.Value
        End If
        Next Cell

  2. Корявенько всё это. Забыли о просто функции Excel — выделить нужную ячейку f2 -> f9 -> enter
    Формула удаляется — остаётся только её значение. Связи разорваны.
    Profit!

    1. Да, корявенько…Но что делать — я вот так привык. Только еще один момент: попробуйте своим методом заменить формулу на значения примерно тысячах в двух-трех и более ячеек. Да хотя бы в ста. А потом попробуйте описанным в статье методом выделить их все и разом преобразовать. И сравните количество затраченного времени на замену формул этими двумя методами.
      Это я к чему: прежде чем писать, что корявенько — сначала рассмотрите все за и против в различных ситуациях. В одной ячейке очень редко заменяют значения — его можно тупо вписать руками…Куда чаще это приходится делать как раз с массивами ячеек.

      1. Ну тут, Дмитрий и соглашусь и не соглашусь.
        Слово макрос обывателя пугает и настораживает, особенно при появлении разных предупреждений от excel что тут есть макрос и вообще она щас нас всех заразит вирусами (неважно, что ты тока что сам его написал:)).
        Также писать макрос под две ячейки тоже, на мой взгляд не с руки.
        А вот, когда надо на листе две, три ячейки отвязать от «родителей» — описанный мною метод самое то, особенно, если люди не любят (как я, например) мышь тыркать где надо и не надо. Это просто быстрее, «3 нажатия» vs «Х нажатий + запомни Х-значное число» — на мой взгляд выигрывает первый вариант.
        Экселем пользуюсь давно, но про F9 узнал относительно недавно, и удивлюсь, что скажите что знали, про данный финт не описанный даже в официальном хэлпе. ;) https://support.office.microsoft.com/ru-ru/article/%D0%A1%D0%BE%D1%87%D0%B5%D1%82%D0%B0%D0%BD%D0%B8%D1%8F-%D0%BA%D0%BB%D0%B0%D0%B2%D0%B8%D1%88-%D0%B3%D0%BE%D1%80%D1%8F%D1%87%D0%B8%D0%B5-%D0%BA%D0%BB%D0%B0%D0%B2%D0%B8%D1%88%D0%B8-%D0%B2-Excel-2010-20603861-42b6-4c93-82ec-66924ea9b323?CorrelationId=8bd8413c-616d-4050-923c-82c972e1b91c&ui=ru-RU&rs=ru-RU&ad=RU

        А что касается массивов — с моей стороны тут спорить будет глупо. ) Удобнее описанный Вами метод. ;)

        1. В принципе я вообще не имел ввиду макрос. В начале статьи описан метод через специальную вставку — его я и имел ввиду, когда говорил про применение для массива ячеек. Скопировали ячейки с формулами-правая кнопка мыши — Специальная вставка-Значения. Все. Никаких макросов и настроек безопасности — пара щелчков мыши и тысячи ячеек с формулами стали значениями :-)

  3. Добрый день
    а как удалить из контекстного меню лишнюю строку, если выполнил код более 1 раза?

    Sub Add_PasteSpecials()
    Application.CommandBars(«Cell»).Controls.Add ID:=370, before:=4
    End Sub

    1. Игорь,

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

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

  4. Прошу прощения, если уже было — всё не читал. Есть отличный вариант: вывести на панель быстрого доступа стандартную команду «Вставить значения» и поставить её (к примеру) на ПЕРВОЕ место панели. Теперь после стандартного копирования Ctrl+C можно СРАЗУ вставить ТОЛЬКО ЗНАЧЕНИЯ через Alt+1. Отлично помогает при копировании значений, в зоне условного форматирования — позволяет избегать дублирования кучи условий (см.Условное форматирование —> Управление правилами)

Добавить комментарий

This site uses Akismet to reduce spam. Learn how your comment data is processed.