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

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

Удаление формул

Специального инструмента, который бы помог выполнить данную процедуру, в Эксель нет. Однако решить этот вопрос можно, причем разными методами.

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

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

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

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

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

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

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

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

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

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

Пошагово:

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

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

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

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

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

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

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

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

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

Sub remFormulas() Dim cell As Range For Each cell In Selection If Not (cell.EntireRow.Hidden = True Or cell.EntireColumn.Hidden = True) Then cell.Value2 = cell.Value2 End If Next cellEnd Sub

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

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

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

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

Sub удалитьФормулыСоВсегоЛиста()ActiveSheet.UsedRange.Value2 = ActiveSheet.UsedRange.Value2End SubSub удалитьФормулыИзВсейКниги()Dim w As WorksheetFor Each w In Worksheets With w.UsedRange .Value = .Value End WithNext wEnd Sub

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

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

Удаление формул

​Смотрите также​​ формулу. Это возможно?​ End Sub​ формулу, а расчет​ о том, что​ в выделенном диапазоне(ах)​Специальная вставка​).​ оно будет всякий​ указанные ниже действия.​ 2007: на вкладке​ список​ них отображался шрифтом​Убедитесь, что в разделе​ — в ячейку A1.​и нажмите кнопку​На вкладке​Примечание:​JayBhagavan​Попробуйте такой код,​ введите вручную​ действия выполненные макросом​

​ Dim smallrng As​и затем опцию​Вы хотите скопировать диапазон​ раз перерассчитываться.​Ячейка с ошибкой в​Параметры​Цвет​

Удаление формулы

  1. ​ белого цвета. В​Форматировать только ячейки, для​

  2. ​В ячейке A1​

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

​Перейти​Главная​Мы стараемся как​: Saifer, выложите файл-пример.​ он также лечит​

  1. ​Saifer​ невозможно отменить -​
    ​ Range For Each​Значения (Paste Special -​ с данными из​Денис денисов​ формуле​в группе​

    1. ​, и в разделе​ результате текст ошибки​

    2. ​ которых выполняется следующее​​ отобразится значение ошибки​​.​​в группе​​ можно оперативнее обеспечивать​​ Лень за Вас​​ после того, как​​: Доброго времени суток,​​ применяйте их с​

    3. ​ smallrng In Selection.Areas​​ Values)​​ одного места в​

    4. ​: Ответ верен. Не​​В Excel 2016, Excel 2013​​Сводная таблица​

  2. ​Цвета темы​​ в таких ячейках​​ условие​​ #ДЕЛ/0!.​​Нажмите кнопку​​Редактирование​ Изображение кнопки​ вас актуальными справочными​

  3. ​ его делать.​​ сделано Paste as​​ уважаемые форумчане!​​ осторожностью.​​ smallrng.Value = smallrng.Value​в открывшемся диалоговом​​ другое, но при​ Изображение кнопки​ пересчитывает)​​ и Excel 2010:​​щелкните стрелку рядом​

Удаление формулы массива

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

  1. ​ материалами на вашем​Казанский​

  2. ​ values, когда числовые​​Есть небольшая проблема,​​Если ломает делать все​ Next smallrng End​​ окне:​​ копировании “сползут” все​​Формулы – это хорошо.​​ выберите​​ с командой​​Возможны ситуации, когда необходимо,​

  3. ​Выделите диапазон ячеек, содержащих​​ выбран пункт​​ нажмите клавишу F2,​

  4. ​.​​Найти и выделить​​ языке. Эта страница​

  5. ​: Sub Formulas_To_Values() Dim​

support.office.com>

Метод 1: копируем значения с помощью параметров вставки

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

  1. Для начала с помощью зажатой левой кнопки мыши нужно выделить область ячеек, которую мы хотим скопировать.Выделенный диапазон ячеек в Эксель
  2. Правой кнопкой мыши щелкаем по любой точке выделенного диапазона и в открывшемся контекстном меню выбираем пункт “Копировать”. Также, можно вместо этого действия можно просто нажать сочетание клавиш Ctrl+C (после того, как выполнено выделение).Копирование диапазона ячеек в Эксель через контекстное менюДля копирования данных можно также воспользоваться кнопкой “Копировать”, которая расположена на лента программы во вкладке “Главная”.Копирование диапазона ячеек в Excel с помощью кнопки на ленте
  3. Переходим в ячейку, начиная с которой мы хотим вставить скопированные данные (эта ячейка станет самой верхней левой точкой диапазона данных, который мы будем вставлять из буфера обмена). Затем правой кнопкой мыши щелкаем по выбранному элементу, в появившемся меню в группе “Параметры вставки” кликаем по варианту “Значения” (значок в виде цифр “123”).Вставка скопированных значений в Эксель
  4. В результате скопированная область данных (только конкретные числовые значения, без формул) будет вставлена в новое место.Вставленные из буфера обмена данные в Excel

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

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

  1. Выделяем ячейку или же диапазон, где располагается необходимая нам формула. Если она является формулой массива, то первоначально следует произвести выделение всех ячеек в диапазоне, в которых содержится формула массива.
  2. Нажимаем на ячейку в формуле массива.
  3. Переходим в раздел «Главная» и находим блок инструментов «Редактирование». Здесь кликаем на элемент «Найти и выделить», а затем на кнопку «Перейти».
  4. В следующем окошке нажимаем на «Дополнительный», а затем на элемент «Текущий массив».
  5. Возвращаемся в раздел «Главная», находим элемент «Копировать» и щелкаем на него.
  6. После проведения процесса копирования нажимаем на стрелочку, которая находится под кнопкой «Вставить». На последнем этапе жмем «Вставить значения».

Excel works!

Excel работает за вас

menu.png

  • cropped-.png

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

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

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

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

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

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

Как скрыть формулы в ячейках в Excel?

Разберем различные способы как скрыть от пользователей формулы в ячейках в Excel (с помощью отключения отображения строки формул и посредством защиты данных).

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

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

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

Как скрыть или показать строку формул в Excel?

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

Для реализации такого способа в панели вкладок переходим на Вид -> Отображение и снимаем галочку из Строка формул, после чего строка сразу исчезает:

kak-skryt-formuly-v-yachejkakh-1.png

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

Теперь давайте поподробнее поговорим про более сложные и надежные варианты.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...