Как удалить значения ячеек? Выборочное удаление значений
Многим пользователям, в том числе и мне, приходится ежедневно, еженедельно, ежемесячно либо ежегодно использовать одни и те же таблицы, сделанные в Excel. Для повторного и последующего использования этих таблиц, как правило приходится удалять значения некоторых ячеек для того, чтобы освободить место для внесения в них новых данных. Удалить устаревшие значения в таблице тем дольше и сложнее, чем старее версия Excel, чем больше таблица и чем больше промежуточных расчетов и результатов в этой таблице. Как сделать это быстро?
Чтобы было понятно о чем пойдет речь, вспомним, что ячейка — это часть табличного пространства, образованная пересечением строки и столбца. Ячейка может содержать какие-то значения, например числа, текст, формулы и так далее, а также может содержать информацию о шрифтах, их цветах и размерах, заливках, рамках, защите и так далее. Таким образом, удалять можно как сами ячейки со всем их содержимым (при этом место удаленной ячейки занимает новая, а всё табличное пространство смещается либо вверх, либо влево) так и часть информации, например только значения или только форматы. В этой публикации речь пойдет об удалении значений, когда сами ячейки остаются на своих местах и информация об их форматах остается неизменной.
Стандартные средства для удаления значений ячеек
Представим (либо посмотрим на видео ниже), что на рабочем листе Excel расположена таблица. В этой таблице шапка и некоторые столбцы, содержат различные текстовые значения, основное пространство таблицы содержит ячейки с числовыми значениями, которые в свою очередь участвуют в расчетах, определенных формулами. К примеру, необходимо удалить из этой таблицы только числа, не удаляя при этом формулы, или только текст, не удаляя при этом числа и всё остальное.
Для того чтобы очистить место в таблице для внесения новых значений можно выделять поочередно нужные диапазоны ячеек и нажимать кнопку «Delete» на клавиатуре, как говорится, медленно, но верно. Проделав такую операцию полтора десятка раз невольно появляется вопрос «а можно ли как-нибудь быстрее?». Конечно можно! Даже нужно. В Microsoft Excel 2007 и выше для этих целей подойдет такой инструмент как «Выделение группы ячеек». Для этого выбираем на ленте Excel вкладку «Главная», переходим в группу «Редактирование», нажимаем кнопку «Найти и выделить» и выбираем команду «Выделение группы ячеек». Появляется одноименное диалоговое окно, при помощи которого можно выделить определенные ячейки таблицы и после этого удалить их значения.
Выделение группы ячеек производится последовательно, нельзя например одновременно выделить и константы и формулы. Так уже гораздо быстрее, не правда ли? Но и это не предел. Все-таки удаление происходит в два этапа, сначала выделяем, затем удаляем. Если таблицы на разных листах и их много, то и с таким способом можно провозиться достаточно долго. В Excel 2003 выделение группы ячеек вообще не предусмотрено и в этой версии без дополнительных инструментов не обойтись.
Дополнительные инструменты для выборочного удаления значений
В некоторых случаях ускорить выборочное удаление значений ячеек поможет надстройка, которая легко устанавливается и запускается одним нажатием кнопки, выведенной прямо в главное меню. Надстройка позволяет удалять значения из ячеек с числами, с текстом, с формулами, с примечаниями, с заливкой, с рамками, как по раздельности, так и в любых сочетаниях. Кроме того предусмотрен выбор диапазона для удаления значений. Возможен используемый диапазон (с первой до последней заполненной ячейки), используемые диапазоны (используемый диапазон на каждом листе активной рабочей книги), выделенный пользователем диапазон, а также выделенный диапазон, но на каждом листе рабочей книги.
Использование надстройки позволяет:
1. выборочно удалять значения из ячеек с числами, с текстом, с формулами, с датами, ячеек содержащих примечания, цветную заливку, цветной шрифт, границы;
2. проводить выборочное удаление значений из ячеек используемых диапазонов, предварительно выделенных диапазонов, диапазонов, выбранных вручную, а также столбцов с заданными именами;
3. осуществлять очистку ячеек от значений на видимых, скрытых, непустых листах, листах с заданными именами, с заданными номерами, с заданным значением в определенном диапазоне, а также на всех листах рабочей книги Excel.
Как удалить значение из ячейки в excel
Подскажите решение задачи.
Имеется ряд ячеек, заполненных какими-то значениями. Пусть этих ячеек будет 10 000. По условию, если значение ячейки
Циклом это легко сделать. Но это слишком долго при большом количестве ячеек. Как удалить эти значения мгновенно?
For i = 1 To 10000 If Cells(i, 1).Value Прикрепленные файлы
- Удаление по условию.xlsm (106.81 КБ)
| Цитата |
|---|
| НСС написал: мгновенно |
Private Sub CommandButton2_Click() Dim i&, t!, usl, tbl t = Timer usl = Range("J5").Value tbl = Range("A1:A10000").Value For i = 1 To 10000 If tbl(i, 1)
Пользователь
Сообщений: 102 Регистрация: 19.08.2019
26.10.2019 00:02:26
Задачу решил "с обратного". Если значения подходят условию, то сохраняем их в массиве. Далее удаляем абсолютно все значения и на их место вставляем из массива с использованием свойства Range.Resize. Но хотелось бы узнать, можно ли ее решить другим способом?
Private Sub CommandButton2_Click() Dim a(1 To 10000), j(1 To 10000, 1 To 1), i&, t&, m& t = Timer! m = 1 For i = 1 To 10000 a(i) = Range("A" & i) If a(i) > Range("J5").Value Then j(m, 1) = a(i) m = m + 1 End If Next i Range("A:A").ClearContents Range("A1").Resize(m, 1).Value = j Range("B2").Value = "Подходящих условию ячеек " & m Range("B3").Value = "Удалено значений " & 10000 - m Range("B4").Value = "Время выполнения макроса " & Timer - t End Sub
Пример прилагаю.
Прикрепленные файлы
- Удаление по условию.xlsm (108.52 КБ)
Пользователь
Сообщений: 102 Регистрация: 19.08.2019
26.10.2019 00:08:02
| Цитата |
|---|
| ocet p написал: "мгновенно" никогда не будет . |
Ну как же, меньше секунды для меня мгновение. ))) Спасибо за решение . Все, как всегда, просто. Я был близок, но мозгов и опыта не хватило. Пришлось с обратного идти. Теперь у меня есть два способа. Может еще кто подкинет какой.
покупка
Как удалить строки на основе значения ячейки в Excel?
Чтобы быстро удалить или удалить несколько строк на основе значения ячейки в Excel, вам может потребоваться сначала выделить эти целые строки, содержащие определенное значение ячейки, а затем перейти к их удалению. Кажется, что нет быстрого способа удалить строки на основе значения ячейки, но с использованием кода VBA. Вот несколько простых приемов, которые могут вам помочь.
- Удалите строки на основе одного значения ячейки с помощью функции поиска и замены
- Удалить строки на основе одного значения ячейки с кодом VBA
- Удалять строки на основе одного или двух значений ячеек
- Удаление строк на основе нескольких значений ячеек
Get a 30 -day full-featured free trial now!
Удалите строки на основе значения ячейки с помощью функции поиска и замены
В Excel вы можете применить мощную функцию «Найти и заменить», чтобы легко удалять строки на основе определенного значения ячейки. Пожалуйста, сделайте следующее:
1. Выберите диапазон, в котором вы будете удалять строки на основе определенного значения ячейки, и откройте диалоговое окно «Найти и заменить», нажав кнопку Ctrl + F ключи одновременно.
2. В диалоговом окне «Найти и заменить» введите определенное значение ячейки (в нашем случае мы вводим Soe) в Find what и нажмите Find All кнопка. См. Первый снимок экрана ниже:
 |
 |
3. Выберите все результаты поиска в нижней части диалогового окна «Найти и заменить» и закройте это диалоговое окно. (Внимание: Вы можете выбрать один из результатов поиска, а затем Ctrl + A клавиши для выбора всех найденных результатов. См. Второй снимок экрана выше.)
И тогда вы можете увидеть, что все ячейки, содержащие определенное значение, выбраны.
4. Щелкните правой кнопкой мыши выбранные ячейки и выберите Delete из контекстного меню. А затем проверьте Entire row во всплывающем диалоговом окне Удалить и щелкните OK кнопка. Теперь вы увидите, что все ячейки, содержащие определенное значение, удалены. Смотрите скриншоты ниже:
 |
 |
А затем были удалены целые строки на основе уже определенного значения.
Удалить строки на основе значения ячейки с кодом VBA
С помощью следующего кода VBA вы можете быстро удалить строки с определенным значением ячейки, выполните следующие действия:
1, нажмите Alt + F11 ключи одновременно, чтобы открыть Microsoft Visual Basic for applications окно,
2. Нажмите Insert > Module , и введите в модуль следующий код:
VBA: удалить целые строки на основе значения ячейки
Sub DeleteRows() 'Updateby20211217 Dim rng As Range Dim InputRng As Range Dim DeleteRng As Range Dim DeleteStr As String Dim xTitleId As String Dim xArr Dim xF As Integer Dim xWSh As Worksheet On Error Resume Next xTitleId = "KutoolsforExcel" Set rng = Application.Selection Set InputRng = Application.InputBox("Range :", xTitleId, rng.Address, Type:=8) If InputRng Is Nothing Then Exit Sub DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2) Set xWSh = InputRng.Worksheet For Each rng In InputRng If rng.Value = DeleteStr Then If DeleteRng Is Nothing Then Set DeleteRng = rng Else Set DeleteRng = Application.Union(DeleteRng, rng) Set DeleteRng = DeleteRng.EntireRow End If End If Next xArr = Split(DeleteRng.AddressLocal, ",") DeleteRng.Select DeleteRng.Delete For xF = UBound(xArr) To 0 Step -1 Set DeleteRng = xWSh.Range(xArr(xF)) DeleteRng.Delete Next End Sub3, Затем нажмите Run кнопку для запуска кода.
4. Во всплывающем диалоговом окне выберите диапазон, в котором вы будете удалять строки на основе определенного значения, и нажмите кнопку OK кнопку.
5. В другом диалоговом окне введите определенное значение, на основе которого вы будете удалять строки, и нажмите кнопку OK кнопка. Смотрите скриншот:
И тогда вы увидите, что целые строки были удалены на основе уже указанного значения.
Удалить строки на основе одного или двух значений ячейки с помощью Kutools for Excel
Если вы установили Kutools for Excel , Его Select Specific Cells Функция может помочь вам быстро удалить строки с определенным значением. Пожалуйста, сделайте следующее:
Kutools for Excel - Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now
1. Выберите диапазон, в котором вы будете удалять строки на основе определенного значения, и нажмите Kutools > Select > Select Specific Cells . Смотрите скриншот:
2. В открывшемся диалоговом окне Select Specified Cells установите флажок Entire row выберите, выберите Contains от Specific type раскрывающийся список, введите указанное значение в правое поле и щелкните значок Ok кнопку (см. снимок экрана выше).
После применения этой функции появится диалоговое окно, в котором будет показано, сколько ячеек было найдено в соответствии с указанными критериями. Пожалуйста, нажмите на OK чтобы закрыть его.
3. Теперь выбираются целые строки с определенным значением. Щелкните правой кнопкой мыши выбранные строки и щелкните значок Delete из контекстного меню. Смотрите скриншот ниже: 
Внимание: Это Select Specific Cells функция поддерживает удаление строк на одно или два определенных значения. Для удаления строк на основе двух указанных значений укажите другое значение в поле Specific type раздел диалогового окна Select Specific Cells, как показано на следующем снимке экрана: 
Kutools for Excel - Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now
Удалить строки на основе нескольких значений ячеек с помощью Kutools for Excel
В некоторых случаях вам может потребоваться удалить строки на основе нескольких значений ячеек из другого столбца / списка в Excel. Здесь я представлю Kutools for Excel 's Select Same & Different Cells функция, чтобы быстро и легко решить эту проблему.
Kutools for Excel - Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now
1. Нажмите Kutools > Select > Select Same & Different Cells , чтобы открыть диалоговое окно Выбрать одинаковые и разные ячейки.
2. В открытии Select Same & Different Cells диалоговое окно, сделайте следующее (см. снимок экрана):
(1) В Find values in поле, выберите столбец, в котором вы найдете определенные значения;
(2) В According to поле, выберите столбец / список с несколькими значениями, на основе которых вы будете удалять строки;
(3) В Based on раздел, пожалуйста, проверьте Each row вариант;
(4) В Find раздел, пожалуйста, проверьте Same Values вариант;
(5) Проверьте Select entire rows опция внизу открывающегося диалогового окна.
Внимание: Если два указанных столбца содержат один и тот же заголовок, проверьте My data has headers опцию.
3, Нажмите Ok кнопку, чтобы применить эту утилиту. Затем появляется диалоговое окно, в котором показано, сколько строк было выбрано. Просто нажмите на OK чтобы закрыть его.
Затем были выбраны все строки, содержащие значения из указанного списка.
4. Нажмите Home > Delete > Delete Sheet Rows для удаления всех выбранных строк.
Демо: удаление строк на основе одного или нескольких значений ячеек в Excel
Kutools for Excel: Более 300 удобных инструментов у вас под рукой! Начните свой 30 Бесплатная пробная версия на день без каких-либо ограничений сегодня. Скачать
Очистить значение ячейки
Активити Очистить значение ячейки позволяет удалить содержимое ячеек при работе с таблицами в Microsoft Excel.
Настройки активити
Чтобы открыть окно настроек, нажмите на активити на графической модели процесса.
Вкладка «Параметры»
На вкладке Параметры отображаются основные параметры активити.
Наименование — название активити на графической модели процесса. При добавлении активити его название задается по умолчанию. В этом поле название можно изменить.
Путь до файла Excel — расположение файла Excel в файловой системе. При указании пути можно использовать контекстные переменные процесса, в которых хранится путь до файла или часть этого пути. Нажмите и укажите переменную. Ее можно выбрать из списка или добавить новую, нажав Создать параметр . Для выбора доступны переменные типа Строка . Значение добавляется в формате . Подробнее о контекстных переменных читайте в статье «Контекст процесса».
Способ получения листа — выберите способ определения листа в файле Excel, содержимое ячеек таблицы в котором требуется удалить:
- По номеру — в поле Номер листа нужно указать порядковый номер листа в файле Excel;
- По имени — в поле Название листа нужно указать название листа в файле Excel.
Адрес ячейки — введите адрес ячейки, значение которой нужно удалить. Например, B2.
Когда вы указываете номер, название листа или адрес ячейки, можно использовать значения контекстных переменных процесса. Нажмите и укажите переменную. Ее можно выбрать из списка или добавить новую, нажав Создать параметр . Для выбора доступны переменные типа Строка . Значение добавляется в формате .
Вкладка «Обработчики»
О вкладке Обработчики можно прочитать в статье «Общие принципы настройки активити».