Как убрать функцию в ячейке excel

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

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

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

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

Нажмите клавишу DELETE.

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

Для этого нужно скопировать формулу, а затем вставить ее в ту же ячейку с помощью параметра «вставить значения».

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

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

На вкладке Главная в группе буфер обмена нажмите кнопку Копировать .

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

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

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

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

Нажмите клавишу DELETE.

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

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

Щелкните главная > Копировать (или нажмите клавиши CTRL + C).

На вкладке главная > щелкните стрелку под кнопкой Вставить > Вставить значения.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Удаление формул из ячеек таблицы Excel

Функциональные возможности программы Excel позволяют не только структурировать и работать с большими объемами данных, но и производить различные расчеты. Зачастую после того, как получен результат, рассчитанный по формуле, в ней больше нет необходимости, и в ячейке требуется оставить именно само значение. Более того, в некоторых случаях наличие формулы будет только мешать в последующей работе. Например, если попробовать перенести или скопировать данные из ячейки с формулой в другое место таблицы, результат вычислений будет утерян или изменится, т.к. при выполнении данной процедуры будут изменены ссылки на ячейки, указанные в формуле, за исключением тех случаев, когда вместо относительные ссылок (по умолчанию) использовались абсолютные.

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

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

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

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

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

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

Метод 2: используем специальную вставку

Если требуется скопировать-вставить данные с сохранение первоначального вида (форматирования) ячеек, можно воспользоваться “Специальной вставкой”.

1. Выделяем и копируем требуемый диапазон данных. На этот раз давайте отметим всю таблицу, а для копирования используем кнопку на ленте инструментов.
2. Переходим в ячейку, начиная с которой планируем вставить скопированные элементы. Щелкаем правой кнопкой мыши по ней, в раскрывшемся контекстном меню кликаем (или наводим указатель мыши) по небольшой стрелке вправо рядом с командой “Специальная вставка”. Всплывет дополнительное подменю, в котором щелкаем по пункту “Значения и исходное форматирование”.
3. Мы получим исходную таблицу в новом месте с охранением форматирования и конкретными числовыми значениями вместо формул.

Метод 3: удаляем формулы в исходной таблице

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

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

Метод 4: удаляем формулы без копирования в другое место

Что делать, если не хочется дублировать таблицу в другом месте листа для ее последующего переноса в исходное с конкретными значениями? Такая возможность в Эксель также предусмотрена, однако, требует большой концентрации внимания, так как все действия мы будем выполнять непосредственно в самой таблице, и из-за неверных шагов можно удалить или нарушить структурную целостность данных.

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

Метод 5: применяем макрос

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

1. Щелкаем по меню “Файл”.
2. В открывшемся окне в боковом перечне слева в самом низу выбираем раздел “Параметры”.
3. В параметрах программы переходим в подраздел “Настроить ленту”. В правой части окна ставим галочку напротив пункта “Разработчик”, после чего щелкаем OK.

Теперь все готово, чтобы выполнить поставленную задачу:

1. Переключаемся во вкладку “Разработчик”, в которой щелкаем по кнопке “Visual Basic” (группа “Код”).
2. Выбрав нужный лист книги нажимаем на кнопку “View Code” (или дважды щелкаем левой кнопкой мыши по выбранному листу), чтобы запустить редактор макросов, куда вставляем код ниже, после чего закрываем данное окно.
   Sub Удаление_формул()
   Selection.Value = Selection.Value
   End Sub
   
3. Выделяем диапазон ячеек, содержащих формулы, и нажимаем кнопку “Макросы”, которая расположена во вкладке “Разработчик” (блок инструментов “Код”).
4. В открывшемся окне выбора макросов отмечаем нужный и щелкаем по кнопке “Выполнить”.
5. После того, как процедура будет выполнена, все формулы в выбранных ячейках будут заменены результатами расчетов по ним.

Метод 6: удаляем формулу вместе с результатом вычислений

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

1. Начинаем с того, что выделяем область ячеек, содержащих формулы. Кликом правой кнопки мыши по выделенному диапазону вызываем меню, в котором щелкаем по команде “Очистить содержимое”.Также, вместо этого, после того, как нужные элементы отмечены, можно просто нажать клавишу Del (Delete) на клавиатуре.
2. В итоге все данные в ячейках, в том числе, формулы будут стерты.

Заключение

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

Как удалить в ячейке формулу, оставив значения?

Иногда формула в ячейке нужна лишь на короткое время — только для получения результата. А затем уже не нужна ни сама формула, ни ячейки, на которые она ссылается. Но вот беда — если удалить ячейки, то формула «ломается», как это говорится. Т.е. в ячейке получается либо #ЗНАЧ! либо #ССЫЛКА! . Или Вам надо просто зафиксировать значение, полученное в результате работы формулы. Т.е. чтобы формулы там не было, а было только значение. Как ни странно, но делается это проще простого:

Выделяете ячейку/несколько ячеек с формулами — Копируете — Правая кнопка мыши — Специальная вставка — Значения

Вот и все. Больше формул нет.
Так же подобное можно сделать при помощи кода VBA:

Sub Formulas_To_Values() Selection.Value = Selection.Value End Sub

Для использования кода переходим в редактор VBA( Alt + F11 ) —Insert —Module и вставляем приведенный код. Чтобы выполнить код надо на листе нажать сочетание клавиш Alt + F8 , выбрать нужный код и нажать Выполнить
Данный код заменяет формулы в выделенном диапазоне на значения. Только необходимо учитывать, что выделенный диапазон должен быть неразрывным. Иначе код выдаст ошибку. А такой код

Sub All_Formulas_To_Values() ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value End Sub

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

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

Sub All_Formulas_To_Values_In_All_Sheets() Dim wsSh As Worksheet For Each wsSh In Sheets 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

КАК БЫСТРО ВЫЗЫВАТЬ:
Вообще данную команду можно вывести на Быструю панель и использовать в одно нажатие тогда, когда нужно.

• 2010 ExcelФайл— Параметры— Настройка. Выбрать команды из:Все команды. Находите команду Вставить значения и добавляете;
• 2007 ExcelМеню— Параметры Excel— Настройка. Выбрать команды из:Все команды. Находите команду Вставить значения и добавляете;
• 2003 Excel : Сервис— Настройка— вкладка Команды— категория Правка— Вставить значения.

Теперь у Вас появится значок на Быстрой панели.

ДОБАВИТЬ В КОНТЕКСТНОЕ МЕНЮ ЯЧЕЕК:
Если один раз выполнить код:

Sub Add_PasteSpecials() Dim cbb Set cbb = Application.CommandBars(«Cell»).FindControl( ).Controls.Add >

то в контекстном меню мыши появится команда — «Вставить значения». Она полностью дублирует стандартную, только вызвать её быстрее — одно нажатие и все.

Статья помогла? Поделись ссылкой с друзьями!

Удаление пустых ячеек из диапазона

Постановка задачи

Имеем диапазон ячеек с данными, в котором есть пустые ячейки:

Задача — удалить пустые ячейки, оставив только ячейки с информацией.

Способ 1. Грубо и быстро

1. Выделяем исходный диапазон
2. Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.

Выделяются все пустые ячейки в диапазоне.

Способ 2. Формула массива

Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или — в Excel 2003 и старше — меню Вставка — Имя — Присвоить (Insert — Name — Define)

Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 — НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.

Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:

В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),»»,INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>«»,ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))

Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) — и мы получим исходный диапазон, но без пустых ячеек:

Способ 3. Пользовательская функция на VBA

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

Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert — Module) и скопируйте туда текст этой функции:

Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:

1. Выделите достаточный диапазон пустых ячеек, например F3:F10.
2. Идем в меню Вставка — Функция (Insert — Function) или жмем на кнопку Вставить функцию(Insert Function) на вкладке Формулы (Formulas) в новых версиях Excel. В категории Определенные пользователем (User Defined) выберите нашу функцию NoBlanks.
3. В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.