Как убрать формулы из ячеек в Excel и оставить только значения
Удалить формулы в Excel можно множеством способов, каждый из которых сильно зависит от поставленной задачи. Как правило, при этом требуется сохранить в ячейках какие-то значения. Вариантов два:
- это значения, вычисленные этими формулами,
- сам текст формул.
Помимо вопроса о том, что оставить в ячейках после удаления формул, могут быть и другие вводные, такие как:
- Где удалять формулы:
- в диапазоне,
- во всем листе,
- во всей книге.
Эта статья даст ответы на все вопросы выше.
Удалить формулы в выделенном диапазоне с сохранением значений
Наиболее распространенная задача — когда нужно оставить в ячейке результат формулы, а саму формулу удалить. В понимании Excel результат формулы является значением ячейки.
Так вот, чтобы перевести формулы в значения, нужно:
- Выделить нужный диапазон ячеек (или одну ячейку, всю строку, столбец или весь лист).
- Скопировать выделенное.
- Вставить процедурой вставки “как значения”.
Можно сделать это клавишами мыши или клавиатуры — суть процедуры от этого не меняется.
С помощью мыши
Не самый быстрый (так как работает только одна рука), но самый удобный и понятный способ. Выделяем, копируем и в контекстном меню (вызывается нажатием правой кнопки мыши по выделенному диапазону) команду со скриншота:
Команда “Вставить как значения” в командном меню вставки в ExcelС помощью клавиатуры (сочетания клавиш)
Копирование делается в соседний столбец или другую книгу. Я предпочитаю этот способ, хоть он и экономит лишь секунды.
- Выделяем диапазон:
- мышью, если это столбец, строка или лист,
- сочетаниями Ctrl + Shift + стрелки или Shift + стрелки , если ячейки или диапазоны ячеек.
- Копируем сочетанием Ctrl + C .
- Перемещаемся стрелками к диапазону, куда нужно вставить данные, и/или нажимаем Ctrl + Tab для перехода в другую книгу.
- Вызываем контекстное меню клавишей на клавиатуре. Иногда ее нет, но обычно на клавиатурах она расположена рядом с правым Alt .
- Стрелками перемещаемся к команде (Вниз-вниз-вправо).
- Нажимаем Enter .
Может показаться, что тут целых 6 шагов и это долго, но попробуйте приловчиться и сравнить работу на больших диапазонах ячеек — мышью будет точно дольше.
С помощью надстройки !SEMTools
Бесплатная процедура моей надстройки чуть-чуть упрощает процедуру, убирая необходимость копирования и вставки данных. Просто выделите диапазон, пара кликов мыши в меню и формулы удалены!
Удалить формулы и сохранить значения в Excel с !SEMTools
Перевод формул в их текст (без сохранения результирующих значений)
Иногда требуется для демонстрационных целей преобразовать ячейки, оставив текст формул. В таких случаях полезной будет процедура замены.
Выделяем диапазон формул, жмем Ctrl + H (“эйч”, англ.), чтобы запустить диалоговое окно “Найти и заменить” и в соответствующих окошках вбиваем значения
Апостроф в результате замены не будет виден, и формулы будут выглядеть идентично их написанию, однако восприниматься программой будут как текст
В видимых ячейках, пропуская скрытые фильтром
Вот здесь при использовании стандартных решений уже могут возникнуть проблемы. Процедура вставки “как значения” даст сбой и повредит данные, если в копируемом диапазоне есть отфильтрованные строки.
Вот код VBA-процедуры, который позволяет обойти проблему:
Он отработает на отлично, как и та же процедура надстройки. В синтаксисе кода присутствует проверка ячеек на предмет того, скрыты ли они.
Удаляем формулы только в отфильтрованных строках, пропуская скрытые
Удалить формулы на всем листе
Когда нужно быстро избавиться от всех формул на листе, можно выделить весь лист и вставить как значения, а также может помочь такая процедура, написанная на VBA:
Удалить формулы из всей книги Excel
Когда речь идет об удалении формул из всей книги, без автоматизации уже не обойтись. Иначе пришлось бы заходить на каждый лист и производить процедуру копирования и вставки как значения столько раз, сколько в книге листов.
Вот процедура, которая сделает это за вас:
Ими можно воспользоваться без внедрения в свои файлы, подключив надстройку !SEMTools.
Удаление формул надстройкой !SEMTools
Быструю замену формул на значения в выделенном диапазоне без необходимости копирования и особой вставки я реализовал в своей надстройке для Excel !SEMTools.
Для тех, кто не готов разбираться в VBA, после установки !SEMTools запуск процедуры доступен в два клика из соответствующего меню в группе «УДАЛИТЬ». Функционал относится к бесплатным возможностям !SEMTools (работают без ограничений в любой версии).
Удаляем формулы со всего листа с !SEMTools
Хотите так же быстро удалять формулы в Excel?
!SEMTools ускорит эту и сотни других задач!Как убрать формулу из ячейки в excel и оставить только значение
При удалении формулы также удаляется ее результат. Если вы не хотите удалять значение, можно удалить только формулу.
Удаление формулы
Вы выберите ячейку или диапазон ячеек, содержащих формулу.
Нажмите клавишу DELETE.
Удаление формулы с сохранением результатов
Для этого нужно скопировать формулу, а затем вкопировать ее в ту же ячейку с помощью параметра Вировать значения.
Выберем ячейку или диапазон ячеек, которые содержат формулу.
Если формула является формулой массива, необходимо сначала выбрать все ячейки в диапазоне ячеек, которые содержат формулу массива:Щелкните ячейку в формуле массива.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
Нажмите кнопку Дополнительный.
Выберите текущий массив.
На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой , а затем выберите вкладки Значения.
Удаление формулы массива
Чтобы удалить формулу массива, выберите все ячейки в диапазоне ячеек, которые содержат формулу массива. Для этого:
Щелкните ячейку в формуле массива.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
Нажмите кнопку Дополнительный.
Выберите текущий массив.
Нажмите клавишу DELETE.
Удаление формулы с сохранением результатов
Выберем ячейку или диапазон ячеек, которые содержат формулу.
Щелкните Главная > Копировать (или нажмите CTRL+C).
Щелкните главная > под кнопкой В >в таблице.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как удалить в ячейке формулу, оставив значения?
Иногда формула в ячейке нужна лишь на короткое время — только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда — если удалить ячейки, то формула «ломается», как это говорится. Т.е. в ячейке получается либо #ЗНАЧ! либо #ССЫЛКА! . А иногда необходимо просто зафиксировать значение, полученное в результате работы формулы. Т.е. чтобы формулы там не было, а было только значение. Как ни странно, но делается это проще простого:
Выделяем ячейку(несколько ячеек) с формулами -Копируем -Правая кнопка мыши — Специальная вставка (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 ) —Insert —Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш 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) . Она полностью дублирует стандартную, только вызвать её быстрее — одно нажатие и все.:6 способов, как удалить формулу из ячейки Excel
Табличный процессор Эксель имеет огромное количество функций, позволяющих эффективно работать с большими объёмами информации и разнообразными вычислениями. Часто бывает так, что пользователю нужно удалить формулу, с помощью которой был рассчитан результат, а итоговый показатель оставить в ячейке. В статье будет рассмотрено несколько методов удаления формул из ячеек табличного процессора Excel.
Удаление формул
В табличном процессоре отсутствует интегрированный инструмент, предназначенный для удаления формул. Это действие возможно реализовать другими методами. Разберем каждый более подробно.
Метод 1: копируем значения с помощью параметров вставки
Первый вариант является самым быстрым и легким. Способ позволяет скопировать наполнение сектора и перенести его в альтернативное место, только уже без формул. Подробная инструкция выглядит следующим образом:
- Производим выделение ячейки или диапазона ячеек, которые будем в дальнейшем копировать.
- Жмем ПКМ по произвольному элементу выделенной области. Появляется небольшое контекстное меню, где следует выбрать элемент «Копировать». Альтернативный вариант копирования – использование комбинации клавиш «Ctrl+C». Третий вариант копирования значений – использование кнопки «Копировать», располагающейся на ленте инструментов раздела «Главная».
- Выбираем ту ячейку, в которую желаем вставить ранее скопированную информацию, кликаем по ней ПКМ. Открывается знакомое нам контекстное меню. Находим блок «Параметры вставки» и нажимаем на элемент «Значения», который имеет вид иконки с изображением последовательности цифр «123».
- Готово! Скопированная информация без формул перенеслась на новую выбранную область.
Метод 2: используем специальную вставку
Существует «Специальная вставка», которая помогает произвести копирование информации и вставить ее в ячейки с сохранением изначального форматирования. Вставленная информация не будет содержать формулы. Подробная инструкция выглядит следующим образом:
- Выделяем диапазон, который желаем вставить в определенное место и копируем любым удобным для вас методом.
- Перемещаемся в ячейку, с которой желаем начать вставку скопированных данных, нажимаем на нее ПКМ. Открылось небольшое контекстное меню. Находим элемент «Специальная вставка» и щелкаем на значок стрелочки, располагающийся правее от этого элемента. В отобразившемся дополнительном меню кликаем на надпись «Значения и исходное форматирование».
- Готово, задача успешно выполнена!
Метод 3: удаляем формулы в исходной таблице
Далее поговорим о том, как удалить формулы в первоначальной таблице. Подробная инструкция выглядит следующим образом:
- Производим копирование диапазона ячеек любым имеющимся способом. К примеру, используя комбинацию клавиш «Ctrl+C».
- Как и в ранее рассмотренном способе, производим вставку с сохранением первоначального форматирования в другой сектор рабочего листа. Не убирая выделение, снова производим копирование данных.
- Перемещаемся в сектор, находящийся в левом верхнем углу, нажимаем ПКМ. Появляется знакомое нам контекстное меню, в котором следует выбрать элемент «Значений».
- Наполнение ячеек без формул скопировалось в изначальное место. Теперь можно произвести удаление остальных таблиц, которые были нам необходимы для процедуры копирования. Выделяем при помощи ЛКМ дубликаты таблицы и кликаем по области выделения ПКМ. Появляется контекстное меню, в котором следует нажать на элемент «Удалить».
- На экране отобразилось небольшое окошко «Удаление ячеек». В нем можно выбрать, что именно удалить. Ставим пункт около надписи «Строку» и кликаем «ОК». В нашем примере в правой части от выделенной области нет ячеек с данными, поэтому вариант «Ячейки, со сдвигом влево» тоже подходит.
- Дубликаты таблиц полностью удалены с рабочего листа. Мы реализовали замену формул на конкретные показатели в исходной таблице.
Метод 4: удаляем формулы без копирования в другое место
Некоторых пользователей табличного процессора Эксель может не устроить предыдущий метод, так как он подразумевает большое количество манипуляций, в которых можно запутаться. Существует еще одна вариация удаления формул из исходной таблицы, но она требует внимательности со стороны пользователя, так как все действия будут проводиться в самой табличке. Важно делать все аккуратно, чтобы случайно не произвести удаление нужных значений или не «поломать» структуру данных. Подробная инструкция выглядит следующим образом:
- Первоначально, как и в прошлых методах, выделяем ту область, из которой необходимо произвести удаление формул любым удобным вам методом. Далее совершаем копирование значений одним из трех способов.
- Не убирая выделения, жмем по области ПКМ. Появляется контекстное меню. В блоке команд «Параметры вставки» нужно выбрать элемент «Значения».
- Готово! В результате выполненных манипуляций в исходной таблице формулы заменились конкретными значениями вычислений.
Метод 5: применяем макрос
Следующий способ подразумевает применение макросов. Перед тем, как приступить к удалению формул из таблицы и замене их конкретными значениями, нужно произвести включение «Режима разработчика». Изначально в табличном процессоре этот режим находится в выключенном состоянии. Подробная инструкция включения «Режима разработчика»:
- Кликаем на вкладку «Файл», которая располагается в верхней части интерфейса программы.
- Открылось новое окно, в котором в левом списке элементов необходимо спуститься в самый низ и кликнуть «Параметры».
- В правой части отобразились настройки. Находим раздел «Настроить ленту» и нажимаем на него. Появилось два окошка со списками. В правом списке находим пункт «Разработчик» и ставим около него галочку. После выполнения всех манипуляций нажимаем «ОК».
- Готово! Режим разработчика включен.
Подробная инструкция по применению макроса:
- Перемещаемся во вкладку «Разработчик», которая находится в верхней части интерфейса табличного процессора. Далее находим группу параметров «Код» и выбираем элемент “Visual Basic”.
- Выбираем нужный лист документа, а затем кликаем на элемент “View Code”. Проделать ту же самую операцию можно двойным кликом ЛКМ по нужному листу. После проведения этого действия на экране появляется редактор макросов. В поле редактора вставляем следующий код:
Sub Удаление_формул()
Selection.Value = Selection.Value
End Sub- После ввода кода жмем на крестик в правом верхнем углу экрана.
- Производим выделение диапазона, в котором располагаются формулы. Далее переходим в раздел «Разработчик», находим блок команд «Код» и нажимаем на элемент «Макросы».
- Появилось небольшое окошко под названием «Макрос». Выбираем только что созданный макрос и жмем «Выполнить».
- Готово! Все формулы в ячейках заменились результатами вычислений.
Метод 6: удаляем формулу вместе с результатом вычислений
Случается, что пользователю табличного процессора Эксель необходимо не только реализовать удаление формул, но и удалить результаты расчётов. Подробная инструкция выглядит следующим образом:
- Как и во всех предыдущих способах, начинаем свою работу с выделения диапазона, в котором располагаются формулы. Затем кликаем ПКМ по области выделения. На экране появилось контекстное меню. Находим элемент «Очистить содержимое» и нажимаем на него. Альтернативный вариант удаления – нажатие на клавишу «Delete».
- В результате проведенных манипуляций удалились все данные в выделенных ячейках.
Удаление формулы с сохранением результатов
Рассмотрим подробно, как удалить формулу с сохранением результата. Этот способ предполагает использование свойства «Вставить значения». Подробная инструкция выглядит следующим образом:
- Выделяем ячейку или же диапазон, где располагается необходимая нам формула. Если она является формулой массива, то первоначально следует произвести выделение всех ячеек в диапазоне, в которых содержится формула массива.
- Нажимаем на ячейку в формуле массива.
- Переходим в раздел «Главная» и находим блок инструментов «Редактирование». Здесь кликаем на элемент «Найти и выделить», а затем на кнопку «Перейти».
- В следующем окошке нажимаем на «Дополнительный», а затем на элемент «Текущий массив».
- Возвращаемся в раздел «Главная», находим элемент «Копировать» и щелкаем на него.
- После проведения процесса копирования нажимаем на стрелочку, которая находится под кнопкой «Вставить». На последнем этапе жмем «Вставить значения».
Удаление формулы массива
Чтобы произвести процедуру удаления формулы массива, первоначально нужно убедиться, что выделены все ячейки в диапазоне, содержащие нужную формулу. Подробная инструкция выглядит следующим образом:
- Выбираем нужный сектор в формуле массива.
- Перемещаемся в раздел «Главная». Находим блок инструментов «Редактирование» и жмем на элемент «Найти и выделить».
- Далее нажимаем «Перейти», а после на элемент «Дополнительный».
- Щёлкаем на «Текущий массив».
- В завершении процедуры нажимаем «Delete».
Заключение
Подводя итог, можно сказать, что в удалении формул из ячеек табличного процессора нет ничего сложного. Существует огромное количество методов удаления, благодаря чему каждый сможет подобрать для себя наиболее удобный.