Как сохранить только значения в excel без формул

Как убрать формулы из ячеек в Excel и оставить только значения

Удалить формулы в Excel можно множеством способов, каждый из которых сильно зависит от поставленной задачи. Как правило, при этом требуется сохранить в ячейках какие-то значения, вариантов два:

  • либо это значения, вычисленные этими формулами
  • либо сам текст формул

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

  • где удалять формулы:
  • в диапазоне,
  • всем листе
  • или всей книге
  • и удалять ли их в скрытых/отфильтрованных строках
  • Эта статья даст ответы на все такие вопросы.

    Удалить формулы и сохранить данные

    Наиболее распространенная задача — когда нужно оставить в ячейке результат формулы, а саму формулу — удалить. В понимании Excel результат формулы является значением ячейки.

    Так вот, чтобы перевести формулы в значения, нужно:

    1. Выделить нужный диапазон ячеек (или одну ячейку, всю строку, столбец или весь лист)
    2. Скопировать выделенное
    3. Вставить процедурой вставки «как значения»

    Можно сделать это клавишами мыши или клавиатуры, суть процедуры от этого не меняется.

    С помощью мыши

    Не самый быстрый, т.к. работает только одна рука, но самый удобный и понятный способ. Выделяем, копируем, и в контекстном меню, вызываемом нажатием правой кнопки мыши по выделенному диапазону, нужно выбрать вот эту команду (выделена красным):

    Команда

    Команда «вставить как значения» в командном меню вставки в Excel

    С помощью клавиатуры (сочетания клавиш)

    Когда копирование делается в соседний столбец или в другую книгу, я предпочитаю этот способ, хоть он и экономит лишь секунды.

    1. выделяем диапазон
      • мышью, если это столбец, строка или лист
      • сочетаниями Ctrl+Shift+стрелки или Shift+стрелки, если ячейки или диапазоны ячеек
      • копируем сочетанием Ctrl+C
      • перемещаемся стрелками к диапазону, куда нужно вставить данные и/или Ctrl+Tab для перехода в другую книгу
      • вызываем контекстное меню клавишей на клавиатуре, иногда ее нет, но обычно на клавиатурах она есть, рядом с правым ALT
      • Стрелками перемещаемся к команде (Вниз-вниз-вправо)
      • Клавиша Enter

      Может показаться, что тут целых 6 шагов и это долго, но попробуйте приловчиться и сравнить работу на больших диапазонах ячеек — мышью будет точно дольше.

      В 2 клика с надстройкой !SEMTools

      Быструю замену формул на значения в выделенном диапазоне без необходимости копирования и особой вставки я реализовал в своей надстройке для Excel !SEMTools.

      Для тех, кто не готов разбираться в VBA, после установки !SEMTools запуск процедуры доступен в 2 клика из соответствующего меню в группе «УДАЛИТЬ«. Функционал относится к бесплатным возможностям !SEMTools (работают без ограничений в любой версии).

      Удалить формулы и сохранить значения в Excel с !SEMTools

      В видимых ячейках, пропуская скрытые фильтром

      Вот здесь при использовании стандартных решений уже могут возникнуть проблемы. Процедура вставки «как значения» даст сбой и повредит данные, если в копируемом диапазоне есть отфильтрованные строки.

      Он отработает на отлично, как и та же процедура надстройки. В синтаксисе кода присутствует проверка ячеек на предмет того, скрыты ли они.

      Удаляем формулы только в отфильтрованных строках, пропуская скрытые

      Удалить формулы на листе / во всех листах

      Когда нужно быстро избавиться от всех формул на листе или во всей книге, также могут помочь соответствующие простенькие процедуры, написанные на VBA:

      Ими можно воспользоваться без внедрения в свои файлы, подключив надстройку !SEMTools.

      Удаляем формулы со всего листа с !SEMTools

      Перевод формул в их текст (без сохранения результирующих значений)

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

      Перевести формулы в текст

      Выделяем диапазон формул, жмем Ctrl+H («эйч», англ.), чтобы запустить диалоговое окно «Найти и заменить«, и в соответствующих окошках вбиваем значения.

      Формулы как текст

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

      Как в Excel скопировать значения без фоновых формул

      Как в Excel скопировать значения без фоновых формул

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

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

      Вставка значений, а не формул. Значения из ячейки Excel можно скопировать без формул

      Вставка значений, а не формул. Значения из ячейки Excel можно скопировать без формул

      Теперь нажмите правой кнопкой мыши на ту область, куда вы хотите перенести данные, и в контекстном меню выберите опцию «Параметры вставки». Под этим значением выберите значек с командой «Значения». Эту же операцию можно выполнить с помощью горячих клавиш «CTRL+ALT+V».

      В всплывающем окне «Вставить содержимое» вы можете активируйте область «Параметры вставки» и либо нужную опцию. Нажмите на «ОК». Скопированные значения появятся в новой ячейке.

      Фото: компании-производители

      Ссылка на основную публикацию