Есть много способов удалить формулы в Excel, каждый из которых сильно зависит от выполняемой задачи. Как правило, в этом случае нужно сохранить какие-то значения в ячейках, есть два варианта:
- или это значения, рассчитанные по этим формулам
- или сам текст формул
Помимо вопроса о том, что оставить в ячейках после удаления формул, могут быть другие входные данные, такие как:
- где убрать формулы:
- в интервале,
- весь лист
- или вся книга
- и нужно ли удалять их в скрытых / отфильтрованных строках
Эта статья даст ответы на все эти вопросы.
Удалить формулы и сохранить данные
Наиболее частая проблема — это когда нужно оставить результат формулы в ячейке и удалить саму формулу. В понимании Excel результатом формулы является значение ячейки.
Итак, чтобы перевести формулы в значения, вам потребуется:
- Выберите желаемый диапазон ячеек (ячейку, всю строку, столбец или весь лист)
- Копировать выделение
- Ввод с использованием процедуры ввода «как значения»
Сделать это можно с помощью мыши или клавиш клавиатуры, суть процедуры от этого не меняется.
С помощью мыши
Не самый быстрый, потому что работает только одна рука, но самый удобный и понятный способ. Выберите, скопируйте и в контекстном меню, вызываемом щелчком правой кнопкой мыши по выделенному диапазону, нужно выбрать эту команду (выделено красным):
Команда «Вставить как значения» в меню команды «Вставить» в Excel
С помощью клавиатуры (сочетания клавиш)
При копировании в соседний столбец или другую книгу я предпочитаю этот метод, хотя он позволяет сэкономить секунды.
Шаг за шагом:
- выберите диапазон
- с помощью мыши, если это столбец, строка или лист
- сочетания клавиш Ctrl + Shift + стрелки или Shift + стрелки, если ячейки или диапазоны ячеек
- копировать с помощью Ctrl + C
- переместите стрелки в области, где вам нужно ввести данные и / или Ctrl + Tab, чтобы перейти к другой книге
- вызвать контекстное меню с помощью клавиши на клавиатуре, иногда его нет, но обычно оно есть на клавиатуре, рядом с правой клавишей ALT
- Используйте стрелки для перемещения в команде (Вниз-Вниз-Вправо)
- Клавиша ввода
Может показаться, что есть до 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 («привет», англ.), Чтобы открыть диалоговое окно «Найти и заменить», и введите значения в соответствующие поля.
Апострофы в результате замены не будут видны и будут выглядеть так же, как их написание, но формулы будут восприниматься как текст.