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

Содержание

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

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

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

Выберем ячейку или диапазон ячеек, которые содержат формулу.
Если формула является формулой массива, необходимо сначала выбрать все ячейки в диапазоне ячеек, которые содержат формулу массива:
1. Щелкните ячейку в формуле массива.
2. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
3. Нажмите кнопку Дополнительный.
4. Выберите текущий массив
  .
На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой , а затем выберите вкладки Значения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel works!

Thanks for Visiting

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Добавление команды «Очистить все, кроме формул» . Excel. Трюки и эффекты

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

Для реализации поставленной задачи необходимо в стандартном модуле редактора VBA написать код, который выглядит следующим образом (листинг 3.103).

Листинг 3.103. Команда «Очистить все, кроме формул»

Sub AddMenuItem()

Dim cbrpMenu As CommandBarPopup

‘ Удаление аналогичной команды (при ее наличии)

Call DeleteMenuItem

‘ Получение доступа к меню «Сервис»

Set cbrpMenu = CommandBars(1).FindControl(ID:=30007)

If cbrpMenu Is Nothing Then

‘ Не удалось получить доступ

MsgBox «Невозможно добавить элемент. »

Exit Sub

Else

‘ Добавление новой команды в меню

With cbrpMenu.Controls.Add(Type:=msoControlButton)

‘ Название команды

.Caption = «Очистить в&се, кроме формул»

‘ Значок

.FaceId = 348

‘ Сочетание клавиш (только надпись на кнопке)

.ShortcutText = «Ctrl+Shift+C»

‘ Сопоставленный макрос

.OnAction = «ExecuteCommand»

‘ Добавление разделителя перед командой

.BeginGroup = True

End With

End If

‘ Сопоставление с макросом сочетания клавиш Ctrl+Shift+C

Application.MacroOptions _

Macro:=»ExecuteCommand», _

HasShortcutKey:=True, _

ShortcutKey:=»C»

End Sub

Sub ExecuteCommand()

‘ Очистка содержимого всех ячеек (кроме формул)

On Error Resume Next

Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents

End Sub

Sub DeleteMenuItem()

‘ Удаление команды из меню

On Error Resume Next

CommandBars(1). FindControl(ID:=30007). _

Controls(«Очистить в&се, кроме формул»).Delete

End Sub

В результате написания кода будут созданы три макроса: AddMenuItem (добавление команды Очистить все, кроме формул на вкладку Надстройки), DeleteMenultem (удаление созданной команды) и ExecuteCommand (макрос запускается при выполнении команды).

Новая команда на вкладке Надстройки показана на рис. 3.41.

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

Рис. 3.41. Новая команда на вкладке Надстройки

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

Как удалить запятые в Excel — Вокруг-Дом

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

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

Шаг 1

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

Шаг 2

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

Шаг 3

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

= ЕСЛИ (ПРАВЫЙ (A1,1) = «» ЛЕВАЯ (А1, LEN (A1) -1), А1)

Замените адрес ячейки вашей первой ячейки данных вместо всех экземпляров «A1» в приведенном выше примере.

Шаг 4

Нажмите Ввод.» Сначала Excel определяет, является ли самое правое значение в ячейке данных запятой. Если это так, он определяет количество символов в ячейке с помощью функции «Len», а затем возвращает только крайний левый N минус 1 символ, пропуская, таким образом, запятую. Если в конце строки запятая не обнаружена, Excel возвращает исходное значение ячейки.

Шаг 5

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

Шаг 6

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

Шаг 7

Выделите исходные ячейки данных, затем щелкните правой кнопкой мыши массив и выберите «Специальная вставка». Нажмите переключатель рядом с «Значения», затем нажмите кнопку «ОК». Excel скопирует выходные строки из ячеек формулы без запятых в ваши исходные ячейки данных в виде строк статических символов.

Шаг 8

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

vba — Автоматическое удаление формулы из пустых ячеек Excel

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

=IF('filepath[filename.xls]Sheet1'!$A$1:A$65536="","",'filepath[filename.xls]Sheet1'!$A$1:A$65536).

Благодарность

-2

Kinfe 5 Окт 2015 в 20:09

3 ответа

Лучший ответ

Это будет простой макрос:

Sub Macro1()



For Each c In Worksheets("your sheet name"). Range("your range")

   If c.Value = "" Then c.Select: Selection.ClearContents
   Next c

End Sub

Где «имя вашего листа» может быть, например, «Лист1»

И «ваш диапазон» может быть, например, «a1: a10»

Balinti 5 Окт 2015 в 17:30

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

Создайте оператор if, который в случае ложного результата возвращает ERROR.TYPE(1) .
Оттуда выберите диапазон ячеек, из которого вы хотите удалить предполагаемые «пробелы», и используйте «Найти и выбрать» >> «Перейти к специальным» .
Щелкните переключатель «Формулы» и оставьте флажок «Ошибки» в качестве единственного флажка . Нажатие кнопки «ОК» выделит все ячейки, которым присвоено значение «# Н / Д» .
Теперь просто нажмите клавишу удаления.

-1

J. Chomel 27 Июл 2016 в 06:13

Private Sub Worksheet_Calculate()
    Dim cell As Range
    On Error GoTo finish
    Application.EnableEvents = False

    For Each cell In UsedRange
       If cell.Text = "" Then cell.Clear
    Next

finish:
    Application.EnableEvents = True

End Sub

A.S.H 5 Окт 2015 в 17:41

Горячие клавиши Microsoft Excel для MacOS

F2	Редактировать выбранную ячейку
⌘ + T или Control + T	Создать таблицу
⌘ + Option + Return или Contol + Option + Enter	Вставить разрыв строки в ячейке
Control + Пробел	Вставить специальные символы
⌘ + Shift + >	Увеличить размер шрифта
⌘ + Shift +	Уменьшить размер шрифта
⌘ + E	Выровнять по центру
⌘ + L	Выровнять по левому краю
⌘ + Shift + L	Отобразить диалоговое окно «Изменить стиль ячейки»
⌘ + 1	Отобразить диалоговое окно «Формат ячеек»
Control + Shift + ~	Применить общий числовой формат
Control + Shift + $	Применить формат валюты с двумя десятичными знаками
Control + Shift + %	Применить формат в процентах без десятичных знаков
Control + Shift + ^	Применить экспоненциальный числовой формат с двумя десятичными знаками
Control + Shift + #	Применить формат даты с указанием дня, месяца и года
Control + Shift + @	Применить формат времени с часами и минутами
⌘ + Option + 0	Применить контурную границу вокруг выделенных ячеек
⌘ + Option + Стрелка вправо	Добавить контурную границу справа от выделения
⌘ + Option + Стрелка влево	Добавить контурную границу слева от выделения
⌘ + Option + Стрелка вверх	Добавить контурную границу в верхнюю часть выделения
⌘ + Option + Стрелка вниз	Добавить контурную границу внизу выделения
⌘ + Option + —	Удалить границы контура
⌘ + B	Применить или удалить жирный формат
⌘ + I	Применить или удалить курсивный формат
⌘ + U	Применить или удалить подчеркивание
⌘ + Shift + X	Применить или удалить зачеркнутый формат
⌘ + ) или Control + )	Скрыть столбец
⌘ + Shift + ) или Control + Shift + )	Показать столбец
⌘ + ( или Control + (	Скрыть строку
⌘ + Shift + ( или Control + Shift + (	Показать строку
Control + U	Редактировать активную ячейку
⌘ + V	Вставить текст в активную ячейку
Enter	Вставить запись в ячейку

Руководство по проверке данных Excel

Автор Дмитрий Якушев На чтение 9 мин. Просмотров 3.5k.

Вступление

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

значение является числом от 1 до 6
дата произойдет в следующие 30 дней
текстовая запись содержит менее 25 символов

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

Сообщение отображается автоматически при выборе ячейки

Проверка данных также может остановить неправильный ввод данных пользователем. Например, если код сотрудника не проходит проверку, вы можете увидеть следующее сообщение:

Пример сообщения об ошибке

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

Пример раскрывающегося меню проверки данных

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

Контроль достоверности данных

Проверка данных осуществляется с помощью правил, определенных в пользовательском интерфейсе Excel на вкладке «Данные» на ленте.

Элементы управления проверкой данных на вкладке ДАННЫЕ

Важное ограничение

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

Определение правил проверки данных

Проверка данных определяется в окне с 3 вкладками: Параметры, Сообщение для ввода и Сообщение об ошибке:

Окно проверки данных имеет три основные вкладки

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

Пример вкладки настроек проверки данных

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

Вкладка настройки сообщения проверки данных

Вкладка «Сообщение об ошибке» определяет, как выполняется проверка. Например, когда вид установлен на «Останов», неверные данные вызывают окно с сообщением, и ввод не разрешен.

Вкладка предупреждения об ошибке проверки данных

Пользователь видит сообщение, подобное этому:

Пример сообщения об ошибке проверки данных

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

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

Вид: Предупреждение
Предупреждает пользователей о том, что данные неверны. Предупреждение ничего не делает, чтобы остановить ввод неверных данных. В окне «Предупреждение» есть три параметра: «Да» (для принятия недействительных данных), «Нет» (для редактирования недействительных данных), «Отмена» (для удаления недействительных данных) и «Справка».

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

Параметры проверки данных

При создании правила проверки данных доступно восемь параметров:

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

Целое число — разрешены только целые числа. Как только опция целого числа выбрана, другие опции становятся доступными для дальнейшего ограничения ввода. Например, вам может потребоваться целое число от 1 до 10.

Действительное — работает как опция целого числа, но допускает десятичные значения. Например, если для параметра «Действительное» задано значение от 0 до 3, допустимы все значения, такие как 0,5 и 2,5.

Список — разрешены только значения из предварительно определенного списка. Значения представляются пользователю как выпадающее меню. Допустимые значения могут быть жестко заданы непосредственно на вкладке «Параметры» или указаны в виде диапазона на рабочем листе.

Дата — разрешены только даты. Например, вам может потребоваться дата между 1 января 2018 года и 31 декабря 2021 года или дата после 1 июня 2018 года.

Время — разрешено только время. Например, вы можете указать время между 9:00 и 17:00 или разрешить время только после 12:00.

Длина текста — проверяет ввод на основе количества символов или цифр. Например, вам может потребоваться код из 5 цифр.

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

На вкладке параметров также есть два флажка:

Игнорировать пустые ячейки — говорит Excel не проверять ячейки, которые не содержат значений. На практике этот параметр влияет только на команду «Обвести неверные данные». Когда эта опция включена, пустые ячейки не обведены, даже если они не прошли проверку.

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

Простое выпадающее меню

Вы можете предоставить пользователю раскрывающееся меню опций, жестко закодировав значения в поле настроек или выбрав диапазон на листе. Например, чтобы ограничить записи действиями «ПРИНЯТ», «В ОБРАБОТКЕ» или «ОТГРУЖЕН», вы можете ввести эти значения через точку с запятой:

Раскрывающееся меню проверки данных с жестко заданными значениями

При применении к ячейке на рабочем листе раскрывающееся меню работает следующим образом:

Используются жестко заданные значения
выпадающего меню проверки данных

Другой способ ввода значений в раскрывающееся меню — использование ссылки на рабочий лист. Например, с размерами (то есть маленький, средний и т.д.) в диапазоне F4:F6. Вы можете указать этот диапазон непосредственно в окне настроек проверки данных:

Значения выпадающего меню проверки
данных со ссылкой на диапазон

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

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

Вы также можете использовать именованные диапазоны для указания значений. Например, с именованным диапазоном под названием «размер» для F4:F6, вы можете ввести имя непосредственно в окне, начиная со знака равенства:

Значения выпадающего меню проверки
данных с именованным диапазоном

Именованные диапазоны автоматически являются абсолютными, поэтому они не изменятся.

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

Проверка данных с помощью пользовательской формулы

Формулы проверки данных должны быть логическими формулами, которые возвращают ИСТИНА, если ввод действителен, и ЛОЖЬ, если ввод недействителен. Например, чтобы разрешить ввод любого числа в ячейку A1, вы можете использовать функцию ЕЧИСЛО (ISNUMBER) в формуле, подобной этой:

= ЕЧИСЛО (А1)

Если пользователь вводит значение 10 в A1, ЕЧИСЛО (ISNUMBER) возвращает ИСТИНА, и проверка данных завершается успешно. Если вводится значение типа «яблоко» в A1, ЕЧИСЛО (ISNUMBER) возвращает ЛОЖЬ, и проверка данных завершается неудачно.

Чтобы включить проверку данных с помощью формулы, выберите «Другой» на вкладке «Параметры», затем введите формулу, начиная со знака равенства (=), как обычно.

Формулы устранения неполадок

Excel игнорирует формулы проверки данных, которые возвращают ошибки. Если формула не работает, и вы не можете понять, почему, настройте фиктивные формулы, чтобы убедиться, что формула работает так, как вы ожидаете. Фиктивные формулы — это просто формулы проверки данных, введенные непосредственно на листе, чтобы вы могли легко увидеть, что они возвращают. На приведенном ниже экране показан пример:

Проверка достоверности данных
с помощью фиктивных формул

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

Примеры формул проверки данных

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

Чтобы разрешить только 5 символьных значений, начинающихся с «z», вы можете использовать:

= И (ЛЕВСИМВ (А1) = «z»; ДЛСТР (A1) = 5)

Эта формула возвращает ИСТИНА только тогда, когда код длиной 5 цифр и начинается с «z». Два значения в примере выше возвращают ЛОЖЬ с этой формулой.

Чтобы разрешить ввод даты в течение 30 дней с сегодняшнего дня:

= И (А1> СЕГОДНЯ (), А1 <= (СЕГОДНЯ () + 30))

Чтобы разрешить только уникальные значения:

= СЧЁТЕСЛИ (диапазон, А1) <2

Разрешить только адрес электронной почты

= ЕЧИСЛО (НАЙТИ ( «@», A1)

Проверка данных, чтобы обвести недействительные записи

После проверки данных можно попросить Excel обвести ранее введенные недействительные значения. На вкладке «Данные» на ленте нажмите «Проверка данных» и выберите «Обвести неверные данные»:

Обведите недействительные значения с проверкой данных

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

= И (ЛЕВСИМВ (А1) = «z», ДЛСТР (A1) = 5)

Недопустимые значения для проверки данных обведены на листе

Найти ячейки с проверкой данных

Чтобы найти ячейки с примененной проверкой данных, используйте диалоговое окно Переход — Выделить. Введите сочетание клавиш Ctrl + G, затем нажмите кнопку «Выделить». Когда появится окно, выберите «Проверка данных»:

Переход к кнопке Выделить Выберите проверку данных

Скопировать данные проверки из одной ячейки в другую

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

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

Примечание: вы можете использовать сочетание клавиш Ctrl + Alt + V, чтобы вызвать Специальную вставку без мыши.

Очистить все данные проверки

Чтобы очистить все данные проверки из диапазона ячеек:

выделите ячейки
нажмите кнопку «Проверка данных» на вкладке «Данные»
нажмите кнопку «Очистить все»

Очистить данные проверки

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

Удалить или удалить формулу

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

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

Выберите ячейку или диапазон ячеек, содержащих формулу.
Если формула является формулой массива, необходимо сначала выбрать все ячейки в диапазоне ячеек, который содержит формулу массива:
1. Щелкните ячейку в формуле массива.
2. На вкладке Домашняя страница в группе Редактирование щелкните Найти и выбрать , а затем щелкните Перейти к .
3. Щелкните Special .
4. Щелкните Текущий массив .
На вкладке Home в группе Буфер обмена щелкните Копировать .
На вкладке Домашняя страница в группе Буфер обмена щелкните стрелку под Вставить , а затем щелкните Вставить значения .

Удалить формулу массива

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

Щелкните ячейку в формуле массива.
На вкладке Домашняя страница в группе Редактирование щелкните Найти и выбрать , а затем щелкните Перейти к .
Щелкните Special .
Щелкните Текущий массив .
Нажмите УДАЛИТЬ.

Как удалить формулы в Excel (и сохранить данные)

Посмотреть видео — Как удалить формулы в Excel

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

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

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

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

Предположим, у вас есть набор данных, показанный ниже (где у вас есть формулы в столбце B), и вы хотите удалить формулы, но сохранить данные.

Ниже приведены шаги по удалению формулы с сохранением данных:

Выберите ячейки в столбце C, которые имеют формулу
Щелкните правой кнопкой мыши любую из выбранных ячеек и нажмите Копировать (или используйте Control + C)
Снова щелкните любую ячейку в выделении и щелкните правой кнопкой мыши.
Щелкните на опции «Специальная вставка». Это откроет специальное диалоговое окно вставки
В диалоговом окне «Специальная вставка» выберите «Значения».
Нажмите ОК

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

Если вы предпочитаете использовать клавиатуру, вы также можете использовать следующие сочетания клавиш:

Скопируйте ячейки — Control + C
Вставить как значения — ALT + E + S + V + Enter

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

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

Надеюсь, вы нашли это руководство по Excel полезным.

Вам также могут понравиться следующие руководства по Excel:

Очистить ячейки и оставить формулы — Блог Contextures

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

Рабочий лист формы заказа

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

Очистить данные

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

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

Если вы хотите удалить старые данные, не удаляя формулы, как бы вы это сделали?

Выбрать ячейки без формул

На этом листе ячейки формулы заблокированы, а лист защищен. Если вы выберете весь раздел цен и нажмете «Удалить», Excel покажет вам предупреждение и не очистит ячейки.

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

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

Выберите все ячейки в разделе цен
На вкладке «Главная» ленты нажмите «Найти и выбрать» (справа).
Щелкните Константы, чтобы выбрать только ячейки без формул

Затем нажмите клавишу «Удалить», чтобы очистить эти ячейки.

Назовите диапазоны ввода данных

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

Чтобы назвать диапазон:

Снять защиту с листа
Выберите ячейки ввода данных
Щелкните поле имени (слева от строки формул)
Введите имя диапазона из одного слова, например DataEntryCells
Нажмите Enter, чтобы ввести имя
Защитить рабочий лист

Чтобы очистить названный диапазон:

В поле «Имя» щелкните стрелку раскрывающегося списка
Выберите имя диапазона — DataEntryCells — для выбора ячеек
На клавиатуре нажмите клавишу Delete, чтобы очистить ячейки.

Запись макроса очистки

Если вы создаете лист ввода данных для кого-то еще, вы можете добавить на лист кнопку «Очистить» или «Сброс».Это может удержать людей от блуждания по листу и попыток прояснить ситуацию самостоятельно!

Для создания макроса:

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

Для создания кнопки:

Добавьте на рабочий лист прямоугольник с закругленными углами с надписью «Очистить».
Щелкните прямоугольник правой кнопкой мыши и выберите Назначить макрос
Выберите макрос, который вы записали, и нажмите ОК
Щелкните рабочий лист, чтобы отменить выбор кнопки
Защитите лист еще раз, если вы сняли защиту

Сохранить файл

Если вы храните макрос в своей книге, вам нужно будет сохранить его в формате с поддержкой макросов (xlsm) или в двоичном (xlsb) формате.

_____________________

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

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

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

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

Итак, приступим!

Очистить ячейки с помощью Go To Special

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

Ниже приведены шаги по очистке содержимого этих ячеек, оставив формулы нетронутыми:

Выбрать весь набор данных
Нажмите клавишу F5.Откроется диалоговое окно «Перейти»
Щелкните по кнопке «Особый». Это откроет диалоговое окно «Перейти к специальному».
Выберите опцию «Постоянная».
Щелкните OK.

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

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

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

Выделив ячейки (в которых есть только значения), щелкните вкладку «Главная»
В группе редактирования нажмите на опцию «Очистить».
В появившихся параметрах нажмите «Очистить все».

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

Очистить ячейки с помощью VBA

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

Если вы не против использовать VBA, вы можете ускорить этот процесс с помощью простого кода VBA.

Ниже приведен код VBA, который очищает содержимое всех ячеек (в выделенном фрагменте), сохраняя при этом формулы нетронутыми:

 Выбор.SpecialCells (xlCellTypeConstants, 23). Очистить

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

 Activesheet.cells.SpecialCells (xlCellTypeConstants, 23) .Clear

Ниже приведены шаги, по которым вы используете этот код VBA в Excel:

Откройте книгу Excel, из которой вы хотите очистить содержимое
Щелкните правой кнопкой мыши любую вкладку рабочего листа
Нажмите на опцию «Просмотреть код». Откроется редактор Visual Basic
Если у вас еще нет видимого окна «Немедленное», выберите в меню пункт «Просмотр», а затем нажмите «Немедленное окно».
Скопируйте и вставьте приведенный выше код в «Немедленное окно»
Поместите курсор в конец строки
Нажмите клавишу Enter

Вышеупомянутые шаги мгновенно очистили бы содержимое от всего выделения (кроме формул).

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

 для каждого ws в листах: ws.Cells.SpecialCells (xlCellTypeConstants, 23) .Clear: Next ws

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

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

Надеюсь, вы нашли этот урок полезным!

Вам также могут понравиться следующие руководства по Excel:

Как удалить комментарий в Excel (или удалить ВСЕ комментарии)
Как отобразить все строки в Excel с помощью VBA
Как автоматически форматировать формулы в Excel
Excel, показывающая формулу вместо результата
Как удалить апостроф в Excel

Очистка всего, кроме формул (Microsoft Excel)

Обратите внимание: Эта статья написана для пользователей следующих версий Microsoft Excel: 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.Если вы используете более раннюю версию (Excel 2003 или более раннюю), , этот совет может не сработать для вас . Чтобы ознакомиться с версией этого совета, написанного специально для более ранних версий Excel, щелкните здесь: Очистка всего, кроме формул.

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

Если вы хотите выполнить очистку вручную, вы можете выполнить следующие действия:

Пресс F5 . Excel отображает диалоговое окно «Перейти». (См. Рисунок 1.)

Рисунок 1. Диалоговое окно «Перейти».

Щелкните по специальной кнопке. Excel отображает диалоговое окно «Перейти к специальному». (См. Рисунок 2.)

Рисунок 2. Диалоговое окно «Перейти к специальному».

Установите переключатель «Константы». После этого станут доступны четыре флажка под параметром Формулы. (Это может немного сбивать с толку. Не сразу понятно, почему Microsoft установила переключатель «Константы» для управления некоторыми флажками под другим переключателем.)
Убедитесь, что все флажки под переключателем «Формулы» установлены. (Они должны быть выбраны по умолчанию.)
Щелкните OK. Excel выбирает все константы (ячейки, не содержащие формул) на листе.
Нажмите кнопку Del .

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

Подложка ClearAllButFormulas ()
    Dim wks как рабочий лист

    'игнорировать ошибки, если есть только формулы
    При ошибке Возобновить Далее
    За каждую неделю в рабочих таблицах
        нед.Cells.SpecialCells (xlCellTypeConstants) .ClearContents
    Следующий
    При ошибке GoTo 0
    Установить wks = ничего
Конец подписки

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

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

Подложка ClearAllButFormulas2 ()
    Dim wks как рабочий лист
    Dim sTemp As String
    Dim iCheck As Integer

    sTemp = "Этот макрос удаляет все в текущем"
    sTemp = sTemp & "рабочая книга, кроме формул. После завершения"
    sTemp = sTemp & "его нельзя отменить."& vbCrLf & vbCrLf
    sTemp = sTemp & "Вы уверены, что хотите продолжить?"

    iCheck = MsgBox (sTemp, vbYesNo + vbExclamation, «Предупреждение!»)

    Если iCheck = vbYes, то
        'игнорировать ошибки, если есть только формулы
        При ошибке Возобновить Далее
        За каждую неделю в рабочих таблицах
            wks.Cells.SpecialCells (xlCellTypeConstants) .ClearContents
        Следующий
        При ошибке GoTo 0
    Еще
        MsgBox "Операция отменена."
    Конец, если
Конец подписки

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel. Этот совет (9097) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь: Очистка всего, кроме формул .

Автор Биография

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Извлечение имен файлов INCLUDEPICTURE

Если вы используете поле INCLUDEPICTURE для добавления изображений в документ, вам может понравиться макрос из этого совета.Это позволяет …

Открой для себя больше

Работа с несколькими книгами

Вам нужно работать более чем с одной книгой одновременно? Для многих эта способность является необходимостью. Excel позволяет легко …

Открой для себя больше

Добавление настраиваемого формата к формату, предлагаемому Excel

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

Открой для себя больше

Пошаговое руководство и 5 примеров

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

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

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

Связанные руководства по VBA и макросам

Следующие руководства по VBA и макросам могут помочь вам лучше понять и реализовать приведенное ниже содержимое:

Общие конструкции и структуры VBA:
- Узнайте о важных конструкциях VBA здесь.
- Узнайте, как работать с редактором Visual Basic здесь.
- Узнайте, как работать с подпроцедурами Excel.
- Узнайте об объектной модели Excel и о том, как создавать ссылки на объекты, здесь.
- Узнайте об объекте Range и о том, как обращаться к ячейкам здесь.
- Узнайте, как работать с недвижимостью здесь.
- Узнайте, как работать с методами здесь.
- Узнайте, как объявлять переменные и работать с ними.
- Узнайте о типах данных здесь.
- Узнайте, как работать с петлями здесь.
Практические приложения VBA и примеры макросов:
- Узнайте, как работать с листами с помощью VBA, здесь.
- Узнайте, как проверить, пуста ли ячейка здесь.
- Узнайте, как удалять строки здесь.
- Узнайте, как удалить пустые или пустые строки здесь.

Дополнительные руководства по VBA и макросам можно найти в архивах.

# 1: Очистить ячейку

Код VBA для очистки ячейки

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

Cells.Clear

Процесс, за которым следует VBA для очистки ячейки

Описание заявления VBA

Артикул: Ячейки.
- VBA Construct: Range объект.
- Описание: Объект Range, представляющий ячейки, которые нужно очистить.
  Обычно объект Range можно вернуть с такими конструкциями, как свойства Worksheet.Range, Worksheet.Cells (с Range.Item), Range.Offset, Range.Resize или Application.ActiveCell. Если вы явно объявляете объектную переменную для представления ячеек, используйте тип данных объекта Range.
Артикул: Прозрачный.
- VBA Construct: Range.Clear method.
- Описание: Метод Range.Clear очищает указанный объект Range (ячейки). Range.Clear очищает весь объект Range, включая значения, формулы и форматирование.

Пример макроса для очистки ячейки

В следующем примере макроса очищаются ячейки от A5 до C9 (myRange) на листе с именем «Очистить ячейку» книги, содержащей макрос (ThisWorkbook).

Sub clearCell ()

    Источник: https: // powerspreadsheets.com /
    'Для получения дополнительной информации: https://powerspreadsheets.com/excel-vba-clear-cell/

    'объявить объектную переменную для хранения ссылки на ячейки для очистки
    Dim myRange As Range

    'определить ячейки для очистки
    Установить myRange = ThisWorkbook.Worksheets («Очистить ячейку»). Диапазон («A5: C9»)

    'очистить ячейки (включая форматирование)
    myRange.Clear

Конец подписки

Эффекты выполнения примера макроса для очистки ячейки

Следующие изображения иллюстрируют результаты выполнения примера макроса.

Перед выполнением макроса: Ячейки от A5 до C9 содержат строку «данные», имеют светло-голубую заливку, а шрифт отформатирован как полужирный.
После выполнения макроса: Ячейки от A5 до C9 (включая данные и форматирование) очищаются.

# 2: Очистить содержимое ячейки и сохранить форматирование

Код VBA для очистки содержимого ячеек и сохранения форматирования

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

Ячейки.ClearContents

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

Описание заявления VBA

Артикул: Ячейки.
- VBA Construct: Range объект.
- Описание: Объект Range, представляющий ячейки, в которых нужно очистить содержимое, но не форматирование.
  Обычно вы можете вернуть объект Range с такими конструкциями, как Worksheet.Range, Worksheet.Cells (с Range.Item), Range.Offset, Range.Resize или Application.ActiveCell. Если вы явно объявляете объектную переменную для представления ячеек, используйте тип данных объекта Range.
Артикул: Прозрачное Содержание.
- VBA Construct: Range.ClearContents метод.
- Описание: Метод Range.ClearContents очищает значения и формулы из указанного вами объекта Range (Cells). Range.ClearContents оставляет форматирование без изменений.

Пример макроса для очистки содержимого ячейки и сохранения форматирования

В следующем примере макроса очищается содержимое (но не форматирование) ячеек от A10 до C14 (myRange) на листе с именем «Очистить ячейку» книги, содержащей макрос (ThisWorkbook).

Sub clearCellContentsKeepFormatting ()

    Источник: https://powerspreadsheets.com/
    'Для получения дополнительной информации: https://powerspreadsheets.com/excel-vba-clear-cell/

    'объявить объектную переменную для хранения ссылки на ячейки для очистки содержимого, но не для форматирования
    Dim myRange As Range

    'определить ячейки, чтобы очистить содержимое и сохранить форматирование
    Установить myRange = ThisWorkbook.Worksheets («Очистить ячейку»). Диапазон («A10: C14»)

    'очистить содержимое ячейки (но не форматировать)
    myRange.ClearContents

Конец подписки

Эффекты выполнения примера макроса для очистки содержимого ячеек и сохранения форматирования

Следующие изображения иллюстрируют результаты выполнения примера макроса.

Перед выполнением макроса: Ячейки с A10 по C14 содержат строку «данные», имеют светло-золотую заливку, а шрифт отформатирован как полужирный.
После выполнения макроса: Содержимое ячеек от A10 до C14 очищается. Форматирование сохраняется.

# 3: Очистить форматирование ячеек

Код VBA для очистки форматирования ячеек

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

Cells.ClearFormats

Процесс, за которым следует VBA для очистки форматирования ячеек

Описание заявления VBA

Артикул: Ячейки.
- VBA Construct: Range объект.
- Описание: Объект диапазона, представляющий ячейки, для которых требуется очистить форматирование ячеек.
  Обычно объект Range можно вернуть с такими конструкциями, как свойства Worksheet.Range, Worksheet.Cells (с Range.Item), Range.Offset, Range.Resize или Application.ActiveCell. Если вы явно объявляете объектную переменную для представления ячеек, используйте тип данных объекта Range.
Артикул: ClearFormats.
- VBA Construct: Метод Range.ClearFormats.
- Описание: Метод Range.ClearFormats очищает форматирование указанного объекта Range (Cells).Range.ClearFormats не очищает значения или формулы.

Пример макроса для очистки форматирования ячеек

Следующий макрос очищает форматирование ячеек от A15 до C19 (myRange) листа с именем «Очистить ячейку» в книге, содержащей макрос (ThisWorkbook).

Sub clearCellFormatting ()

    Источник: https://powerspreadsheets.com/
    'Для получения дополнительной информации: https://powerspreadsheets.com/excel-vba-clear-cell/

    'объявить объектную переменную для хранения ссылки на ячейки для очистки форматирования
    Dim myRange As Range

    'определить ячейки, чтобы очистить форматирование
    Установите myRange = ThisWorkbook.Рабочие листы («Очистить ячейку»). Диапазон («A15: C19»)

    'очистить форматирование ячеек
    myRange.ClearFormats

Конец подписки

Эффекты выполнения примера макроса для очистки форматирования ячеек

Следующие изображения иллюстрируют результаты выполнения примера макроса.

Перед выполнением макроса: Ячейки с A15 по C19 содержат строку «данные», имеют светло-зеленую заливку, а шрифт отформатирован как полужирный.
После выполнения макроса: Форматирование ячеек с A15 по C19 очищается.

# 4: Очистить цвет ячейки

Код VBA для очистки цвета ячейки

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

Cells.Interior.Color = xlColorIndexNone

Процесс, за которым следует VBA для очистки цвета ячеек

Описание заявления VBA

Артикул: Ячейки.
- VBA Construct: Range объект.
- Описание: Объект диапазона, представляющий ячейки, для которых требуется очистить форматирование ячеек.
  Обычно объект Range можно вернуть с такими конструкциями, как свойства Worksheet.Range, Worksheet.Cells (с Range.Item), Range.Offset, Range.Resize или Application.ActiveCell. Если вы явно объявляете объектную переменную для представления ячеек, используйте тип данных объекта Range.
Артикул: Интерьер.
- VBA Construct: Range.Interior property и Interior object.
- Описание: Ассортимент. Свойство Interior возвращает объект Interior, представляющий внутреннюю часть указанного диапазона ячеек (Cells).
Артикул: Цвет.
- VBA Construct: Свойство Interior.Color.
- Описание: Свойство Interior.Color позволяет вам установить основной цвет внутренней части ячейки, представленной объектом Interior, возвращаемым Range.Interior.
Товар: =.
- VBA Construct: Оператор присваивания.
- Описание: Оператор присваивания присваивает значение xlColorIndexNone параметру Interior.Свойство цвета.
Пункт: xlColorIndexNone.
- VBA Construct: xlColorIndexNone constant.
- Описание: Константа xlColorIndexNone указывает, что цвет объекта Interior, представляющего внутреннюю часть Cells, равен none.

Пример макроса для очистки цвета ячейки

Следующий макрос очищает цвет ячеек от A20 до C24 (myRange) на листе с именем «Очистить ячейку» книги, содержащей макрос (ThisWorkbook).

Sub clearCellColor ()

    Источник: https://powerspreadsheets.com/
    'Для получения дополнительной информации: https://powerspreadsheets.com/excel-vba-clear-cell/

    'объявить объектную переменную для хранения ссылки на ячейки, чтобы очистить цвет ячейки
    Dim myRange As Range

    'определить ячейки, чтобы очистить их цвет
    Установить myRange = ThisWorkbook.Worksheets («Очистить ячейку»). Диапазон («A20: C24»)

    'чистый цвет ячейки
    myRange.Interior.Color = xlColorIndexNone

Конец подписки

Эффекты выполнения примера макроса для очистки цвета ячейки

Следующие изображения иллюстрируют результаты выполнения примера макроса.

Перед выполнением макроса: Ячейки с A20 по C24 содержат строку «данные», имеют светло-оранжевую заливку, а шрифт отформатирован как полужирный.
После выполнения макроса: Цвет заливки ячеек с A20 по C24 очищается.

# 5: Прозрачные ячейки с нулем

Код

VBA для очистки ячеек с помощью нуля

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

Для каждой ячейки в диапазоне
    Если Cell.Значение = myValue Затем Cell.Clear
Следующая ячейка

Процесс, за которым следует VBA для очистки ячеек с помощью нуля

Описание заявления VBA

Строки №1 и №3: для каждой ячейки в диапазоне | Следующая ячейка

Позиция: Для каждого… В… Далее.
- VBA Construct: For Each… Next statement.
- Описание: Оператор For Each… Next повторяет оператор в цикле (строка № 2) для каждого элемента (Cell) в диапазоне ячеек (Range), в котором вы хотите искать нули.
Артикул: Ячейка.
Артикул: Диапазон.
- VBA Construct: Группа оператора For Each… Next и объекта Range.
- Описание: Оператор For Each… Next повторяет операторы в цикле (строка №2) для каждого элемента (ячейки) в группе (диапазоне). Range — это объект Range, представляющий ячейки, в которых вы хотите искать нули.
  Обычно вы можете вернуть объект Range с такими конструкциями, как Worksheet.Свойства Range, Worksheet.Cells (с Range.Item), Range.Offset, Range.Resize или Application.ActiveCell. Если вы явно объявляете объектную переменную для представления Range, используйте тип данных объекта Range.

Строка № 2: Если Cell.Value = myValue, то Cell.Clear

Товар: Если… Тогда.
- VBA Construct: If… Then… Else statement.
- Описание: Оператор If… Then… Else условно выполняет группу операторов в зависимости от значения выражения.Для этих целей:
  - Оператор If… Then… Else проверяет указанное условие (Cell.Value = myValue).
  - Если условие выполнено и возвращает True: выполняется Cell.Clear.
  - Если условие не выполняется и возвращает False: Выполнение продолжается с оператора, следующего за оператором If… Then… Else (Следующая ячейка).
Артикул: Ячейка.
Товар: Стоимость.
- VBA Construct: Range.Ценность собственности.
- Описание: Свойство Range.Value возвращает значение в ячейке, которую цикл For Each… Next в настоящее время выполняет итерацию.
Элемент: myValue.
- VBA Construct: Переменная числового типа данных.
- Описание: myValue представляет значение, которое вы хотите найти и использовать, чтобы определить, какие ячейки нужно очистить. В макроструктуре, используемой в этом руководстве по VBA, myValue равно 0 (нулю).
  Если вы явно объявляете переменную для представления myValue, используйте числовой тип данных, например Long, Single или Double (в зависимости от значения, которое вы ищете).
Элемент: Cell.Value = myValue.
- VBA Construct: Условие оператора If… Then… Else.
- Описание: Это условие является выражением, которое принимает значение Истина или Ложь. Cell.Value = myValue возвращает True или False, как показано ниже:
  - True: Значение ячейки равно myValue (ноль).
  - False: Значение ячейки не равно myValue (нулю).
Артикул: Прозрачный.
- VBA Construct: Range.Clear method.
- Описание: Метод Range.Clear очищает ячейку, которую в настоящее время выполняет цикл For Each… Next. Range.Clear очищает весь объект Range, включая значения, формулы и форматирование.
  Если вы не хотите очищать весь объект Range, а только его содержимое, форматирование или цвет ячеек, обратитесь к соответствующим разделам выше.

Пример макроса для очистки ячеек с помощью нуля

В следующем примере макроса очищаются ячейки с нулем (0) в ячейках от A25 до C29 (myRange) на листе с именем «Очистить ячейку» книги, содержащей макрос (ThisWorkbook).

Sub clearCellsWithZero ()

    Источник: https://powerspreadsheets.com/
    'Для получения дополнительной информации: https://powerspreadsheets.com/excel-vba-clear-cell/

    'объявлять объектные переменные для хранения ссылок на диапазон ячеек, в котором вы ищите нули
    Dim myRange As Range

    'объявить объектную переменную, используемую для перебора элементов диапазона ячеек
    Dim iCell As Range

    'объявить переменную для хранения значения (нуля), которое вы ищете
    Dim myValue As Long

    'определить ячейки для поиска нулей
    Установите myRange = ThisWorkbook.Рабочие листы («Очистить ячейку»). Диапазон («A25: C29»)

    'установить значение (ноль) для поиска
    myValue = 0

    'перебирать каждую ячейку (iCell) диапазона ячеек (myRange)
    Для каждой iCell в myRange

        'проверить, если значение равно нулю. Если условие выполнено, очистить ячейку
        Если iCell.Value = myValue, то iCell.Clear

    Следующий iCell

Конец подписки

Эффекты выполнения примера макроса для очистки ячеек с нулевым значением

Следующие изображения иллюстрируют результаты выполнения примера макроса.

Перед выполнением макроса: Ячейки с A25 по C29 содержат строку «данные» или нулевое значение (0), имеют светло-серую заливку, а шрифт отформатирован как полужирный.
После выполнения макроса: Ячейки от A25 до C29, содержащие ноль (0), очищаются (включая данные и форматирование).

Ссылки на конструкции VBA, используемые в этом руководстве по VBA

Используйте следующие ссылки, чтобы посетить соответствующую веб-страницу в Microsoft Developer Network:

Укажите книгу и лист, на которых расположены очищаемые ячейки:
Определите ячейки, которые необходимо очистить:
Ячейки прозрачные:
Работа с переменными и типами данных:

Использование VBA ClearContents для очистки ячеек в Excel

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

Например, вы могли заметить, что процесс A лучше, когда требуется переменная X, а процесс B лучше, когда переменная X является необязательной. В этом сценарии вы получаете Sub A и Sub B, к которым вы можете получить доступ независимо.

Так часто возникают похожие встроенные функции. В нашем случае мы рассмотрим разницу между методами VBA ClearContents и VBA Clear как частью диапазона в Excel.Их имена подразумевают сходство, и оба они очищают информацию из ячеек. Но есть важные отличия.

VBA

Прозрачный Метод

Метод VBA Clear связан с объектом Range в Excel. Диапазон — это просто группа ячеек. Допустим, у вас есть таблица с информацией об общественном центре, которая выглядит так:

Здесь у нас есть формулы в столбцах «Время ожидания» и «Дополнительные элементы», а также условное форматирование для столбцов «Член», «Время возврата» и «Время ожидания».Кроме того, мы использовали форматирование даты для столбца Date, который может автоматически определять день недели. Мы могли бы даже подумать о добавлении проверки данных для времени выезда, чтобы пользователи не могли вводить недопустимое время, например, до открытия общественного центра.

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

При небольшом количестве посетителей было бы тривиально выбрать стол мышью и сбросить его. Но что, если у вас сотни посетителей? Вероятно, вам не захочется прокручивать вниз, чтобы найти нижнюю часть, затем выделить всю таблицу и удалить ее. Более того, если вы просто выберете и нажмете Del , вы потеряете формулы. В нашем случае вам понадобится как минимум два выбора, чтобы не удалять столбец Time Out.

По этой причине вы можете просто реализовать кнопку для очистки всего с помощью этого кода:

  Sub clear_customer_table ()
Диапазон (Ячейки (4, 1), Ячейки (10000, 4)).ясно
Диапазон (Ячейки (4, 6), Ячейки (10000, 7)). Очистить
Концевой переводник

Это сохранит ваши формулы в столбце Time Out (5-й столбец).

К сожалению, метод .Clear удалил все нашего форматирования в очищенных диапазонах:

Время возврата после 17:30 больше не выделяется условно, а элементы условно не выделяются зеленым цветом.
У нас больше нет форматирования даты. Если кто-то введет дату в обычном формате, например 15.09.2019, он не увидит ни запятых, ни дня недели.
Исчезли границы ячеек и цвета заливки.
Все настройки форматирования текста и ячеек, такие как полужирный, размер шрифта и заливка, очищены.
Если бы у нас были какие-либо правила проверки данных, такие как отсутствие времени выезда до 9 утра, они тоже были бы удалены.

Метод VBA Clear сбрасывает ячейки до «основной формы», которую вы видите при первом открытии новой книги. Единственное исключение — это размер ячейки, который остается. Если мы просто хотим удалить содержимое, но сохранить форматирование, мы можем использовать близкого родственника Clear, ClearContents .

VBA

ClearContents Метод

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

  Sub clear_customer_table_contents ()
Диапазон (Ячейки (4, 1), Ячейки (Rows.Count, 4)). ClearContents
Диапазон (Ячейки (4, 6), Ячейки (Rows.Count, 7)). ClearContents
Концевой переводник

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

Создавайте эффективные макросы с помощью нашего бесплатного комплекта разработчика VBA

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

Этот макрос можно назначить кнопке или фигуре прямо на листе Excel для упрощения очистки. Но можем ли мы сделать код красивее? Ответ: да.

Очистить содержимое с сохранением формул

Хотя человеку, очищающему таблицу, может потребоваться только один шаг (нажатие кнопки «Очистить», которую вы добавляете на лист), код по-прежнему состоит из двух шагов: очистить содержимое слева от столбца Time Out и очистить содержимое справа от столбец Тайм-аут.По умолчанию метод VBA ClearContents удаляет формулы, введенные в ячейку, поэтому в нашем примере мы пропустили столбец «Время ожидания».

К счастью, есть способ очистить определенные типы данных в ячейках. Все данные, вводимые пользователями, будут , константа , потому что они не меняются, пока пользователь не изменит их вручную. Это контрастирует с ячейками на основе формул, которые пересчитываются при изменении рабочего листа. Маркеры типа видны механизму VBA, и вы можете выделить их, вызвав свойство SpecialCells объекта Range :

  Sub clear_all_contents_but_formulas ()
Диапазон (ячейки (4, 1), ячейки (строки.Count, 7)). SpecialCells (xlCellTypeConstants) .ClearContents
Концевой переводник

Эта единственная строка кода идентифицирует все ячейки констант от столбца 1 до столбца 7, начиная с 4-й строки, и очищает их содержимое. Поскольку мы указали макросу очистить только xlCellTypeConstants в нашем диапазоне, никакие формулы не будут очищены, и, поскольку мы использовали .ClearContents вместо .Clear , все форматирование ячеек сохраняется.

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

Альтернативный метод «клиринга»

Есть еще один метод «очистки» данных. Это не настоящая операция очистки, а действительно операция перезаписи.

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

  Sub clear_by_overwriting ()
Диапазон (ячейки (4, 1), ячейки (строки.Счетчик, 4)). Значение = ""
Диапазон (Ячейки (4, 6), Ячейки (Rows.Count, 7)). Значение = ""
Концевой переводник

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

  Sub ovewrite_all_but_formulas ()
Диапазон (Ячейки (4, 1), Ячейки (Rows.Count, 7)). SpecialCells (xlCellTypeConstants) .Value = ""
Концевой переводник

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

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

  Range.Value = vbNullString

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

В этом руководстве мы объяснили два похожих встроенных метода VBA для удаления данных из диапазонов в Excel. Это методы .Clear и .ClearContents . Метод Clear сбрасывает ваши ячейки в основную форму без текста или форматирования ячеек и без правил для проверки данных или условного форматирования, в то время как метод ClearContents очищает содержимое ячеек, но сохраняет ваше форматирование и любые правила проверки данных.

Это руководство также показало вам другой способ «очистки» содержимого, который включает перезапись уже имеющихся данных пустой строкой. Этот метод приводит к тому же результату, что и .ClearContents на поверхностном уровне (то есть то, что вы можете видеть в электронной таблице).

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

Если вам понравился этот учебник, у нас есть более 20 бесплатных электронных писем, которые помогут вам узнать больше о приемах VBA, подобных этому.Проверьте это, используя форму ниже, и поделитесь этой статьей в Twitter и Facebook.

Внимание, фанаты VBA!
Я каждый день вижу, как люди борются с Excel, и хочу помочь. Вот почему я бесплатно раздаю свой VBA Developer’s Kit . Он полон ярлыков, советов и готовых макросов, которые помогут вам стать более умным программистом.