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

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

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

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

• где убрать формулы:
  • в интервале,
  • весь лист
  • или вся книга
• и нужно ли удалять их в скрытых / отфильтрованных строках

Эта статья даст ответы на все эти вопросы.

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

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

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

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

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

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

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

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

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

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

Шаг за шагом:

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

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

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

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

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

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

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

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

Код процедуры VBA:

Sub remFormulas () Уменьшить размер ячейки как диапазон для каждой ячейки в выделенной области, если нет (cell.EntireRow.Hidden = True или cell.EntireColumn.Hidden = True) Then cell.Value2 = cell.Value2 End If Next cell End Sub

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

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

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

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

Sub deleteFormulasFromAllSheet () ActiveSheet.UsedRange.Value2 = ActiveSheet.UsedRange.Value2 End Sub deleteFormulasFromAllBook () Уменьшить w как рабочий лист для каждого w в рабочих таблицах с w.UsedRange .Value = .Value End with Next w End Sub

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

Удалите формулы со всего рабочего листа с помощью! SEMTools

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

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

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

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