Как удалить содержимое ячейки в excel формула

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

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

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

Можно воспользоваться кнопкой «Показать формулы» на вкладке «Формулы». 

Да, формулы отобразятся, но если таблица будет занимать гораздо больше места? Некоторые формулы будут не видны и их можно по ошибке удалить. Что недопустимо.

Поэтому пойдём другим путём. Воспользуемся возможностями поиска и выделения программы Excel.

Выделяем все числа в нашей таблице.

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

В появившемся окне устанавливаем переключатель на пункт «Константы» и отмечаем галкой «Числа». Жмём «ОК».

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

После нажатия кнопки «Delete» значения формул будут равны нолю. Всё готово. Таким образом можно работать с любыми объёмами данных и не бояться случайно удалить формулу.

Вас также может заинтересовать урок — Данные таблицы на карте в Excel.

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

Очистить содержимое ячейки на основе значения ячейки с кодом VBA

Очистить целые строки, если значение больше или меньше определенного значения с кодом VBA

Очистить содержимое ячейки или строки на основе значения ячейки с помощью удобной функции

Очистить целые строки, если другая ячейка пуста с кодом VBA

Очистить содержимое ячейки на основе цвета фона с помощью кода VBA

Очистить содержимое ячейки на основе значения ячейки с кодом VBA

Например, у вас есть следующий диапазон данных, теперь вы хотите очистить содержимое ячейки «Толстовка», приведенный ниже код VBA может оказать вам услугу.

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код VBA: очистить содержимое ячейки, если ячейка равна определенному значению

Sub ClearContents()
'Updateby ExtendOffice
 Dim xRng As Range
 Dim xCell As Range
 Dim xText As String
 Set xRng = Application.Range("A2:A12")
 xText = "Hoodie" 'Change this text to your own
 For Each xCell In xRng
 If xCell.Value = xText Then
 xCell.clearContents
 End If
 Next xCell
End Sub

Внимание: В приведенном выше коде A2: A12 это диапазон данных, из которого вы хотите очистить содержимое ячейки, текст «Толстовка с капюшоном” — это конкретное значение, которое вы хотите очистить. Пожалуйста, измените их на свои.

3, Затем нажмите F5 key для запуска этого кода, и все ячейки с текстом «Толстовка» сразу очищаются, см. скриншот:

Очистить целые строки, если значение больше или меньше определенного значения с кодом VBA

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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Код VBA: очистить целые строки, если значение ячейки больше или меньше определенного значения

Sub ClearRowInValue()
'Updateby ExtendOffice
Dim xRg As Range
Dim xStrAddress As String
Dim xStrValue As Integer
Dim xCell As Range
Dim xRowRg As Range
Dim xF As Integer
Dim xBol As Boolean
xStrAddress = "D2:D12" 'Change cell range
xStrValue = 300 'change the value
Set xRg = Range(xStrAddress)
For xF = xRg.Rows.Count To 1 Step -1
    Set xRowRg = xRg.Rows.Item(xF)
    xBol = False
    For Each xCell In xRowRg.Cells
        If Application.IsNumber(xCell.Value) Then
        If xCell.Value > xStrValue Then
        xBol = True
        Exit For
        End If
        End If
    Next
    If xBol Then
        xRowRg.EntireRow.clearContents
    End If
Next
End Sub

Внимание: В приведенном выше коде D2: D12 содержит ли диапазон данных конкретное значение, на основе которого вы хотите очистить строки; 300 конкретное значение; в > персонаж в сценарии Если xCell.Value > xStrValue Тогда означает больше чем, используйте меньше чем, измените его на < характер, как вам нужно.

3, Затем нажмите F5 ключ для запуска этого кода, и все содержимое строки очищается, если значение в столбце D больше 300, см. снимок экрана:

Очистить содержимое ячейки или строки на основе значения ячейки с помощью удобной функции

Если у вас есть Kutools for Excel, С его Выбрать определенные ячейки функцию, вы можете выбрать ячейки или строки на основе определенного значения, а затем сразу удалить содержимое ячейки.

После установки Kutools for Excel, пожалуйста, сделайте так:

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

2. В открытом Выбрать определенные ячейки диалоговом окне выполните следующие операции:

• Нажмите Ячейка or Весь ряд что вы хотите выбрать;
• Укажите критерии под Конкретный тип раздел, как вам нужно. Здесь я хочу выбрать строки, значение которых в столбце D больше 300.

3. Затем нажмите Ok кнопка, и будут выбраны все строки со значением больше 300. Теперь вам просто нужно нажать клавишу Delete на клавиатуре, чтобы удалить содержимое ячейки, см. снимок экрана:

Tips: Есть много других критериев, которые вы можете использовать, пожалуйста, укажите свое собственное условие в диалоговом окне, как показано ниже:

Очистить данные строк в диапазоне, если он содержит пустую ячейку с кодом VBA

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

Чтобы завершить эту задачу, примените следующий код VBA:

1. Удерживая нажатой ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модули, а затем вставьте следующий код в Модули Окно.

Код VBA: очистить данные строк, если они содержат пустую ячейку

Sub ClearContents()
'Updateby ExtendOffice
Dim xcell As Range
Set xrng = Application.Range("A2:D12")
For Each xcell In xrng
If xcell.Value = "" Then
Intersect(xcell.EntireRow, xrng).ClearContents
End If
Next
End Sub

Внимание: В приведенном выше коде A2: D12 диапазон данных содержит пустые ячейки, из которых вы хотите очистить содержимое ячеек.

3, Затем нажмите F5 ключ для запуска этого кода, и данные строк с пустыми ячейками очищаются сразу в определенном диапазоне, как вам нужно.

Очистить содержимое ячейки на основе цвета фона с помощью кода VBA

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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модули, а затем вставьте следующий код в Модули Окно.

Код VBA: очистить содержимое ячейки на основе цвета фона

Sub Clearcontents()
'Updateby ExtendOffice
Dim xcell As Range
Dim xrng As Range
Set xrng = Application.Range("A2:D12")
    For Each xcell In xrng
        If xcell.Interior.Color = RGB(252, 228, 214) Then
            xcell.Clearcontents
        End If
    Next
End Sub

Внимание: В приведенном выше коде A2: D12 диапазон данных, который вы хотите использовать, RGB (252, 228, 214) — это конкретный цвет, на основе которого вы хотите очистить ячейки, вы можете извлечь режим RGB цвета фона, используя Рисовать программа на вашем компьютере.

3. А затем нажмите F5 для запуска этого кода, и содержимое ячейки с указанным цветом фона немедленно очищается. Смотрите скриншот:

Как очистить ячейки в Excel? Выборочная очистка ячеек от содержимого

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

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

Как очистить ячейки стандартными средствами Excel?

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

Рассмотрим пункты меню кнопки «Очистить» более детально.

Как очистить все в выделенном диапазоне ячеек?

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

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

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

Как очистить ячейки от содержимого?

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

Как очистить ячейки от примечаний?

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

Как очистить ячейки средствами VBA?

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

Использование надстройки позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. на выбор пользователя удалять значения ячеек, примечания, гиперссылки, форматы, шрифты, заливки, рамки и объединения ячеек;

3. выбирать по своему усмотрению один из четырех режимов обработки данных (смотри скриншот).

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

Надстройка легко устанавливается и запускается одним нажатием кнопки, выведенной прямо в главное меню Excel.

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

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

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

1. На необходимо очистить третий столбец. Чтобы удалить только содержимое ячеек, мы выделяем диапазон с «С1» по «С7» и нажимаем на клавиатуре клавишу «Delete».

1. Необходимо очистить не только содержимое третьего столбца, но и форматы, тогда выделяем снова третий столбец, на верхней панели настроек в закладке «Главная», слева находим блок «Редактирование», нажимаем на иконку в виде ластика с надписью «Очистить» и нажимаем на кнопку «Очистить все».

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

1. Необходимо выделить всю область, чтобы это сделать нажмите на сочетание клавиш на клавиатуре «CTRL+A» или на прямоугольник между цифрой «1» и буквой «А», после нажмите на «Delete».

1. Чтобы удалить все содержимое и форматы, снова выделяете всю область листа, идете в закладку «Главная» в блоке «Редактирование» , нажимаете на иконку «Очистить» и выбираете «Очистить все».

Очистка ячеек содержимого или форматов

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

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой » очистить » , а затем выполните одно из следующих действий:

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

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

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

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

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

Если щелкнуть ячейку, а затем нажать клавишу DELETE или Backspace (удалить), содержимое ячейки будет очищено без удаления каких-либо форматов ячеек или примечаний к ячейкам.

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

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

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

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

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

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

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

Можно воспользоваться кнопкой «Показать формулы» на вкладке «Формулы».

Да, формулы отобразятся, но если таблица будет занимать гораздо больше места? Некоторые формулы будут не видны и их можно по ошибке удалить. Что недопустимо.

Поэтому пойдём другим путём. Воспользуемся возможностями поиска и выделения программы Excel.

Выделяем все числа в нашей таблице.

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

В появившемся окне устанавливаем переключатель на пункт «Константы» и отмечаем галкой «Числа». Жмём «ОК».

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

После нажатия кнопки «Delete» значения формул будут равны нолю. Всё готово. Таким образом можно работать с любыми объёмами данных и не бояться случайно удалить формулу.

Вас также может заинтересовать урок — Данные таблицы на карте в Excel.

Excel works!

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

Excel works!

Thanks for Visiting

Как правильно и быстро очистить ячейки в Excel?

Столкнувшись снова, с тем что люди тратят много времени на очистку ячеек, поймал себя на мысли, что действий по очистке данных, ячеек, таблиц довольно много. Как очистить все данные на листе? Как удалить только форматы? Как убрать формулы? Или может быть вам нужно очистить данные по условию? Действительно, действий много. Наверное стоит подробно описать большинство способов. Как правильно и быстро очистить ячейки в Excel?

На самом деле, я бы разделил вопрос статьи на 3 части

1. Как очистить форматы ячеек?
2. Как очистить данные в ячейках Excel?
3. Очистка/удаление прочих объектов, таких как диаграммы, автофигуры или сводные таблицы

Как быстро очистить ячейки в Excel от форматов?

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

Теперь ищите на ленте задач на вкладке Главная раздел Редактирование — в нем жмите кнопку Очистить

На выбор у вас будет несколько пунктов. Вы сможете

1. Очистить все — т.е. удалить все данные на листе и форматы в том числе
2. Очистить форматы — это как раз, то что нужно для примера
3. Очистить содержимое — очищаем данные в ячейках, не трогая форматы
4. Очистить примечания — удобная функция, если кто-то сильно зарецензировал ваш лист и оставил много примечаний. Появилась относительно недавно.
5. Очистить гиперссылки — совсем новая возможность, появившаяся в 2016 версии. Позволяет очистить все ссылки на листе. Очень удобно если вы скопировали данные из интернета, например с Википедии, почти всегда копируется множество гиперссылок.

Если вы уже нажали пункт 2, то все ваши форматы очистились.

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

Опять выделяете нужную область или лист. Идете Главная — раздел Стили — Условное форматирование — Удалить правила и например Удалить правила со всего листа.

Все, условные форматирования тоже удалились!

Как очистить данные в ячейках Excel? Очистка данных по условиям

Как вы знаете удалить данные/информацию в ячейках Excel можно:

• нажатием кнопки Delete на клавиатуре, после выделения нужной области
• нажатием кнопки Удалить содержимое Главная — раздел Редактирование — в нем жмите кнопку Очистить содержимое (см. описание выше)
• можно удалить не только данные, но и сами ячейки. Выделив к примеру строку, кликнуть правой кнопкой мыши и нажав кнопку Удалить (она же Ctrl + кнопка минус )

Помимо этого можно удалить данные и по нужным условиям.

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

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

Удаление объектов, очистка листов от диаграмм или прочих добавленных объектов

Чтобы удалить диаграмму, или автофигуры, нужно сперва ее выбрать, а затем удалить кнопкой Delete или через правую кнопку мыши — Удалить (см. выше). Но как быть, если вам необходимо удалить сразу несколько объектов — зажмите Ctrl и выберите нужные объекты. Если объектов очень много, то придется воспользоваться макросом, например, предложенном в пункте 4, этой статьи .

Так же объекты можно удалить если вы перейдете в меню Главная — раздел Редактирование — Найти и выделить — пункт Выделение группы ячеек — Объекты. Так можно очистить ячейки в Excel от объектов.

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

Если же вам необходим специальный макрос для удаления специфических объектов, то пишите в комментариях или мне на почту!