Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

 

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

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

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

Вот и все. Больше формул нет. Так же подобное можно сделать при помощи кода VBA:

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

заменит все формулы на листе в значения. При этом будут разорваны все связи на данном листе. Это стоит учитывать. Кстати, иногда именно для этого и применяют подобный код.

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

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

КАК БЫСТРО ВЫЗЫВАТЬ:
Вообще данную команду можно вывести на Быструю панель и использовать в одно нажатие тогда, когда нужно.

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

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

ДОБАВИТЬ В КОНТЕКСТНОЕ МЕНЮ ЯЧЕЕК:
Если один раз выполнить код:

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

Также см.:
Вставить значения


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

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

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 46 коммент.
  1. Василий Пупкин:

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

  2. Василий Пупкин:

    Сайт скушал текст :) Там было [sup] и [sub]

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

      • Василий Пупкин:

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

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

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

  3. PsevDANIm:

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

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

      • PsevDANIm:

        Ну тут, Дмитрий и соглашусь и не соглашусь.
        Слово макрос обывателя пугает и настораживает, особенно при появлении разных предупреждений от 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

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

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

  4. Татьяна:

    Очень полезный макрос, но у меня при попытки его использовать выскакивает ошибка "RunTime Error 7 - Out of Memory" ((

  5. Игорь:

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

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

    • Игорь,

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

  6. Русаньля:

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

  7. Jack Fisher:

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

  8. Jack Fisher:

    Забыл сказать ГЛАВНЫЙ плюс: откатывается/отменяется стандартным методом (в отличие от макросов)

  9. Михаил:

    Спасибо! помогло

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти