Как удалить только строки с нулевым значением в excel - Word и Excel

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

Удалить строку, если ячейка содержит ноль, с помощью функции фильтра в Excel
Удалить строку, если ячейка содержит ноль, с помощью VBA в Excel
Удалить строку, если ячейка содержит ноль с Kutools for Excel

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

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

1. Выберите ячейки столбца, содержащие нулевые значения, на основе которых вы хотите удалить все строки, затем щелкните Данные > Фильтр. Смотрите скриншот:

2. Затем в первой ячейке выбранного столбца отобразится стрелка раскрывающегося списка, щелкните стрелку и выберите Числовые фильтры > Равно из раскрывающегося списка.

3. в Пользовательский автофильтр диалоговое окно введите номер 0 в текстовое поле, как показано на скриншоте ниже, а затем щелкните OK кнопку.

4. Затем все ячейки с нулевым значением в этом столбце отфильтровываются. Выберите все видимые ячейки в диапазоне фильтра и щелкните их правой кнопкой мыши, выберите Удалить строки из контекстного меню. И во всплывающем окне подсказки щелкните значок OK кнопка. Смотрите скриншот:

5. Теперь все видимые строки удалены. Вы можете нажать Данные > Фильтр снова, чтобы показать все данные без ячеек с нулевым значением. Смотрите скриншот:

Удалить строку, если ячейка содержит ноль, с помощью VBA в Excel

В этом разделе будет показан метод VBA для удаления всех строк, если в определенном столбце Excel существуют нулевые значения.

1. Нажмите Alt + F11 одновременно кнопки для отображения Microsoft Visual Basic для приложений окно.

2. В окне Microsoft Visual Basic для приложений щелкните Вставить > Модули, затем скопируйте и вставьте следующий код VBA в окно модуля.

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

Sub DeleteZeroRow () 'Обновить ) Application.ScreenUpdating = False Do Set Rng = WorkRng.Find ("20140616", LookIn: = xlValues) Если не Rng Is Nothing, то Rng.EntireRow.Delete End If Loop While Not Rng Is Nothing Application.ScreenUpdating = True End Sub

3. нажмите F5 ключ для запуска кода, во всплывающем окне KutoolsforExcel в диалоговом окне выберите диапазон столбцов, в котором вы хотите удалить целые строки на основе нулевых значений внутри, затем щелкните OK кнопка. Смотрите скриншот:

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

Удалить строку, если ячейка содержит ноль с Kutools for Excel

Для многих пользователей Excel использование кода VBA опасно для удаления данных в Excel. Если вы не совсем доверяете коду VBA, здесь мы рекомендуем вам попробовать Выбрать определенные ячейки полезности Kutools for Excel.

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

2. в Выбрать определенные ячейки диалог, вам необходимо:

(1) Выберите Весь ряд вариант в Тип выбора .

(2) Выберите Равно в первом Конкретный тип раскрывающийся список, затем введите номер 0 в текстовое поле.

(3) Щелкните значок OK кнопку.

3. Появится диалоговое окно, в котором указано, сколько строк было выбрано, щелкните значок OK кнопка. Теперь выбраны все строки с нулевыми значениями в указанном диапазоне столбцов. Щелкните правой кнопкой мыши любую выбранную строку, затем щелкните Удалить в контекстном меню. Смотрите скриншот:

Теперь все строки с нулевыми значениями, существующие в указанном столбце, немедленно удаляются. Смотрите скриншот:

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

Удалить строку, если ячейка содержит ноль с Kutools for Excel

Относительные статьи:

Удалить строки, не содержащие определенного текста в Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

dx48

Пользователь

Сообщений: 66
Регистрация: 18.04.2014

28.08.2014 11:49:33

Здравствуйте! Нашел макрос который скрывает строки.
Макрос скрывает те строки в которых пустые ячейки в столбце F.
Уважаемые подскажите что нужно сделать что бы макрос работал не по условию пустых ячеек а по условию нулевого значения в ячейках.

Код
Dim cell As Range: Application.ScreenUpdating = False For Each cell In [F2:F25].Cells If cell.Find("*", , xlValues, xlPart) Is Nothing Then cell.EntireRow.Hidden = True End If Next cell

JayBhagavan Пользователь Сообщений: 11833 Регистрация: 17.01.2014 ПОЛ: МУЖСКОЙ \| Win10x64, MSO2019x64	Используйте макрорекордер. (например, найти в диапазоне ячейку целиком с нулём) <#0> Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori

Sanja

Пользователь

Сообщений: 14838
Регистрация: 10.01.2013

28.08.2014 11:57:33

Код
.............................. If cell.Value = 0 Then ..............................

Согласие есть продукт при полном непротивлении сторон.

dx48 Пользователь Сообщений: 66 Регистрация: 18.04.2014	Правда говоря я в excel новичок. Если вас не затруднит не могли бы выложить полную версию переделанного кода для моего случая.

Sanja

Пользователь

Сообщений: 14838
Регистрация: 10.01.2013

28.08.2014 12:14:14

Код
Dim cell As Range: Application.ScreenUpdating = False For Each cell In [F2:F25].Cells If cell.Value = 0 Then cell.EntireRow.Hidden = True Next cell

Согласие есть продукт при полном непротивлении сторон.

Пользователь

Сообщений: 5018
Регистрация: 22.12.2012

28.08.2014 12:16:45

Код
Dim cell As Range: Application.ScreenUpdating = False For Each cell In [F2:F25] If cell.Value = 0 Then cell.EntireRow.Hidden = True End If Next cell

п.с. пустые тоже спрячет.

Изменено: V — 28.08.2014 12:20:00

SAS888

Пользователь

Сообщений: 757
Регистрация: 01.01.1970

28.08.2014 12:19:53

Можно сделать и вовсе не используя циклы. Например так:

Код

Sub Main()
    Dim x As Range
    Set x = GetRange([F2:F25], 0)
    If Not x Is Nothing Then x.EntireRow.Hidden = True
End Sub

Function GetRange(rng As Range, Item As String) As Range
    rng.Replace "", "/", xlWhole: rng.Replace Item, "", xlWhole
    On Error Resume Next: Set GetRange = rng.SpecialCells(xlCellTypeBlanks): On Error GoTo 0
    rng.Replace "", Item, xlWhole: rng.Replace "/", "", xlWhole
End Function

Функция «GetRange» получает 2 аргумента: диапазон и значение. Возвращает диапазон ячеек, содержащий данное значение.

Изменено: SAS888 — 28.08.2014 12:21:24

Чем шире угол зрения, тем он тупее.

RAN

Пользователь

Сообщений: 7091
Регистрация: 21.12.2012

28.08.2014 12:23:19

Цитата
dx48 пишет: Правда говоря я в excel новичок

Не правда. Обычный халявщик.

PS И не надо слать подобные ЛС. От этого вы не перестанете быть халявщиком.

Изменено: RAN — 28.08.2014 23:17:43

dx48

Пользователь

Сообщений: 66
Регистрация: 18.04.2014

28.08.2014 19:09:36

Цитата
V пишет: п.с. пустые тоже спрячет.

Подскажите а как сделать так что бы пустые не скрывало?

dx48 Пользователь Сообщений: 66 Регистрация: 18.04.2014	Sanja, Ваш код тоже скрывает пустые строки а нужно только с нулевым значением

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

#11

28.08.2014 19:28:10

Или добавить проверку на «не пусто» или попробовать вот так:

Код
If cell.Value = "0" Then

dx48 Пользователь Сообщений: 66 Регистрация: 18.04.2014	В ответе V эта строка прописана только его код скрывает пустые ячейки тоже а мне надо что бы скрывало только с значением с ноль.

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

#13

28.08.2014 19:51:48

Цитата
dx48 пишет: В ответе V эта строка прописана

Сравните его строку и мою…

dx48 Пользователь Сообщений: 66 Регистрация: 18.04.2014	Юрий М, Спасибо выручили!

Алексей Волков

Пользователь

Сообщений: 30
Регистрация: 05.01.2013

#15

05.10.2014 20:51:09

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

Код

Public Sub Delete_Row()
 
 Dim DR As Range, LastRow As Long, r As Long
 On Error Resume Next
 Set DR = Application.InputBox("Выделите диапазон, в котором необходимо удалить строки с нулевым значением:", _
"Выделите диапазон", Default:=ActiveCell.Address, Type:=8
 On Error GoTo 0
 If DR Is Nothing Then
     Exit Sub
 Else
     LastRow = DR.Rows.Count
     LastRow = LastRow + DR.Row - 1
     Application.ScreenUpdating = False
     For r = LastRow To DR.Row Step -1
        If Cells(r, DR.Column).Value = 0 Then Rows(r).Hidden = True       
     Next r
  End If
 
End Sub

Изменено: Алексей Волков — 05.10.2014 20:57:49

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

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

Алексей Волков

Пользователь

Сообщений: 30
Регистрация: 05.01.2013

#17

05.10.2014 21:35:46

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

Код

Sub СкрытиеСтрокПоУсловию() 
Dim DR As Range, LastRow As Long, r As Long 
Dim ra As Range, delra As Range, ТекстДляПоиска As String 
Application.ScreenUpdating = False ' отключаем обновление экрана 

ТекстДляПоиска = "Наименование ценности" ' скрываем строки с таким текстом 

' перебираем все строки в используемом диапазоне листа 
For Each ra In ActiveSheet.UsedRange.Rows 
' если в строке найден искомый текст 
If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) IsNothing Then 
' добавляем строку в диапазон для скрытия 
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra) 
End If 
Next 
' если подходящие строки найдены - скрываем их 
If Not delra Is Nothing Then delra.EntireRow.Hidden=TRUE 
End Sub

Исправил, спасибо за замечание,

Изменено: Алексей Волков — 05.10.2014 21:41:31

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Вы видели, как другие оформляют код? Для этого есть специальный тег — кнопка <…>. Исправляйте.

	Спасибо за замечание — исправил — на будущее учту

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	В каком столбце будете искать ноль?

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

#22

05.10.2014 22:08:46

Код

Sub СкрытиеСтрокПоУсловию()
Dim LastRow As Long, r As Long
Dim ra As Range, delra As Range
    Application.ScreenUpdating = False ' отключаем обновление экрана
    LastRow = Cells(Rows.Count, 9).End(xlUp).Row 'нашли последнюю строку по столбцу I
    ' перебираем все ячейки в столбце I
    For Each ra In Range(Cells(2, 9), Cells(LastRow, 9))
        ' если в ячейке ноль, то
        If ra = 0 Then
            ' добавляем строку в диапазон для скрытия
            If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
        End If
    Next
    ' если подходящие строки найдены - скрываем их
    If Not delra Is Nothing Then delra.EntireRow.Hidden = True
End Sub

	Благодарю за оказанное содействие

SAS888

Пользователь

Сообщений: 757
Регистрация: 01.01.1970

#24

06.10.2014 06:46:51

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

Код

Sub Main()
    Dim i As Long, x As Range: Application.ScreenUpdating = False
    i = Cells(Rows.Count, "I").End(xlUp).Row: Set x = Range("I1:I" & i)
    Cells(i + 2, "I") = [I1]: Cells(i + 3, "I") = "<>0"
    x.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Cells(i + 2, "I").Resize(2), Unique:=False
    Cells(i + 2, "I").Resize(2).ClearContents
End Sub

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

Код
ActiveSheet.ShowAllData

ПРИМЕЧАНИЕ: Ячейка «I1» должна содержать какое-либо значение (заголовок). Если это не так, то диапазон фильтрации нужно изменить на требуемый.

Изменено: SAS888 — 06.10.2014 08:27:09

Чем шире угол зрения, тем он тупее.

gyuntner

Пользователь

Сообщений: 2
Регистрация: 30.03.2015

#25

04.08.2015 17:22:26

Добрый день!
Я использую такой макрос в прайсе, чтоб убрать незаполненные строки

Код

Sub отфильтровать()
Dim cell As Range: Application.ScreenUpdating = False
For Each cell In [D8:D1089].Cells
If cell.Find("*", , xlValues, xlPart) Is Nothing Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

Но после того как отфильтрую, больше изменения внести не могу, если забыла что-то…
Как можно опять отобразить скрытые строки ??? Все перерыла не могу найти((( Помогите пожалуйста

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

#26

04.08.2015 21:58:47

Цитата
gyuntner написал: Как можно опять отобразить скрытые строки ???

Вариант:

Код
Sub ShowHidden() With [D8:D1089].Cells .EntireRow.Hidden = False End With End Sub

gyuntner Пользователь Сообщений: 2 Регистрация: 30.03.2015	#27 05.08.2015 15:30:53 Спасибо, Юрий!!!!!

Источник

Удалить строки в которых одни ноли.

Mark1976

Дата: Четверг, 28.07.2016, 20:40 |
Сообщение № 1

Группа: Проверенные

Ранг: Ветеран

Сообщений: 685

Репутация:
3
±

Замечаний:
0% ±

Excel 2010, 2013

Всем привет. Есть таблица, которая имеет несколько тысяч строк, и имеет 4 столбца. Как мне удалить те строки, в которых стоят одни ноли?. Но не трогая те строки в которых имеются цифры отличные от ноля. Фильтром это не получится сделать. Помогите.

Ответить

Nic70y

Дата: Четверг, 28.07.2016, 20:52 |
Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8136

Репутация:
1999
±

Замечаний:
0% ±

Excel 2010

Фильтром это не получится

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

ЮMoney 41001841029809

Ответить

Pelena

Дата: Четверг, 28.07.2016, 21:07 |
Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 18797

Репутация:
4284
±

Замечаний:
±

Excel 2016 & Mac Excel

Здравствуйте.
В доп. столбец формулу массива

фильтр по значению ИСТИНА — удалить строки
или
и фильтр по значению 0

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

Ответить

_Boroda_

Дата: Четверг, 28.07.2016, 21:11 |
Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Или в соседний столбец формулу массива (вводится одновременным нажатием Контрл Шифт Ентер) и фильтр по ИСТИНА

Или обычная формула
и фильтруете четверки

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

sv2014

Дата: Четверг, 28.07.2016, 21:23 |
Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 226

Репутация:
61
±

Замечаний:
0% ±

Excel 2013

Mark1976, добрый вечер,вариант макроса,кнопки test и повтор

[vba]

Код

Sub test()
   Dim z, i&: z = Range(«A2:E» & Range(«A» & Rows.Count).End(xlUp).Row).Value
  For i = UBound(z) To 1 Step -1
If z(i, 2) = 0 And z(i, 3) = 0 And z(i, 4) = 0 And z(i, 5) = 0 Then Rows(i + 1).Delete
   Next
End Sub

[/vba]

Ответить

Mark1976

Дата: Четверг, 28.07.2016, 21:37 |
Сообщение № 6

Группа: Проверенные

Ранг: Ветеран

Сообщений: 685

Репутация:
3
±

Замечаний:
0% ±

Excel 2010, 2013

Всем спасибо за помощь.

Ответить

Mark1976

Дата: Суббота, 06.08.2016, 22:52 |
Сообщение № 7

Группа: Проверенные

Ранг: Ветеран

Сообщений: 685

Репутация:
3
±

Замечаний:
0% ±

Excel 2010, 2013

Здравствуйте. Скажите как в макросе, изменить диапазон, для удаления нолей.
[vba]

Код

Sub test()
Dim z, i&: z = Range(«A2:E» & Range(«A» & Rows.Count).End(xlUp).Row).Value
For i = UBound(z) To 1 Step -1
If z(i, 2) = 0 And z(i, 3) = 0 And z(i, 4) = 0 And z(i, 5) = 0 Then Rows(i + 1).Delete
Next
End Sub

[/vba]

Ответить

_Boroda_

Дата: Суббота, 06.08.2016, 23:04 |
Сообщение № 8

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Диапазон столбцов вот здесь (с ячейки А2)
[vba][/vba]
Последнюю строку макрос считает той, где в столбце А что-то написано: [vba]

Код

Range(«A» & Rows.Count).End(xlUp).Row

[/vba]
Проверка нулей идет по столбцам В,C,D,E (2,3,4,5 столбцы): [vba]

Код

z(i, 2) = 0 And z(i, 3) = 0 And z(i, 4) = 0 And z(i, 5)

[/vba]

Ответить

Mark1976

Дата: Воскресенье, 07.08.2016, 18:10 |
Сообщение № 9

Группа: Проверенные

Ранг: Ветеран

Сообщений: 685

Репутация:
3
±

Замечаний:
0% ±

Excel 2010, 2013

_Boroda_, спасибо. Буду разбираться.

Ответить

Источник

Remove From My Forums

Question
Hi,

This is my first participation in this forum and I hope you can assist me.

I have a query with 3 columns and there are rows that have zero value across all the 3 rows, how can I delete these rows?

Thanks

Amr
- Edited by
  
  Tuesday, October 22, 2019 6:30 PM
  Making the question clearer

Answers

Hi,

As far as I understand you want to filter out rows, where Col1 = 0, Col2 = 0 and Col3 =
0. If it’s the case, you may use this code:
```
= Table.SelectRows(Source, each not List.MatchesAll({[Col1],[Col2],[Col3]}, each _ = 0))
```
- Edited by
  Aleksei Zhigulin
  Wednesday, October 23, 2019 8:44 AM
- Proposed as answer by
  Lz._
  Wednesday, October 23, 2019 4:15 PM
- Marked as answer by
  AmrMatar
  Thursday, October 24, 2019 9:54 AM
Hi Amr

Assuming you want to do this after your existing #»Filtered Rows» step, use Aleksei’s proposal changing [Col1],[Col2],[Col3] with YOUR column names:
```
= Table.SelectRows(#"Filtered Rows", each not List.MatchesAll({[Rooms],[IN_GUEST],[REVENUE]}, each _ = 0))
```
- Marked as answer by
  AmrMatar
  Thursday, October 24, 2019 9:54 AM
Amr

1/ There MUST be a comma at the end of the #»Filtered Rows» step as it’s not the last one

2/ You MUST give a name to the step = Table.SelectRows(#»Filtered Rows», each not…) and reference it after the
in line. So the end of your query code should look like this:
```
#"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Rate Code] <> "{NULL}")),
RemovedRowsWithZero = Table.SelectRows(#"Filtered Rows", each not List.MatchesAll({[Rooms],[IN_GUEST],[REVENUE]}, each _ = 0))

in
    RemovedRowsWithZero
```
- Marked as answer by
  AmrMatar
  Thursday, October 24, 2019 9:54 AM

Источник

Через найти/заменить: найти 0 — заменить пусто — в параметрах поставить Ячейка целиком — Заменить все

Если не поможет, прикладывайте файл с примером

Через найти/заменить: найти 0 — заменить пусто — в параметрах поставить Ячейка целиком — Заменить все

Если не поможет, прикладывайте файл с примером Pelena

“Черт возьми, Холмс! Но как. ”
ЯД 41001765434816

Ответить

Сообщение Через найти/заменить: найти 0 — заменить пусто — в параметрах поставить Ячейка целиком — Заменить все

Если не поможет, прикладывайте файл с примером Автор – Pelena
Дата добавления – 25.12.2013 в 17:09

Удаление нулевых значений в Microsoft Excel

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

Алгоритмы удаления нулей

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

Способ 1: настройки Excel

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

«Файл»

«Параметры»

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

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

Способ 2: применение форматирования

Скрыть значения пустых ячеек можно при помощи изменения их формата.

«Формат ячеек…»

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

Для сохранения введенных изменений жмем на кнопку «OK».

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

Способ 3: условное форматирование

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

«Главная»

«Условное форматирование»

«Стили»

«Правила выделения ячеек»

«Равно»

Открывается окошко форматирования. В поле «Форматировать ячейки, которые РАВНЫ» вписываем значение «0». В правом поле в раскрывающемся списке кликаем по пункту «Пользовательский формат…».

Открывается ещё одно окно. Переходим в нем во вкладку «Шрифт». Кликаем по выпадающему списку «Цвет», в котором выбираем белый цвет, и жмем на кнопку «OK».

Вернувшись в предыдущее окно форматирования, тоже жмем на кнопку «OK».

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

Способ 4: применение функции ЕСЛИ

Ещё один вариант скрытия нулей предусматривает использование оператора ЕСЛИ.

«Вставить функцию»

Запускается Мастер функций. Производим поиск в списке представленных функций оператора «ЕСЛИ». После того, как он выделен, жмем на кнопку «OK».

Активируется окно аргументов оператора. В поле «Логическое выражение» вписываем ту формулу, которая высчитывает в целевой ячейке. Именно результат расчета этой формулы в конечном итоге и может дать ноль. Для каждого конкретного случая это выражение будет разным. Сразу после этой формулы в том же поле дописываем выражение «=0» без кавычек. В поле «Значение если истина» ставим пробел – « ». В поле «Значение если ложь» опять повторяем формулу, но уже без выражения «=0». После того, как данные введены, жмем на кнопку «OK».

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

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

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

Способ 5: применение функции ЕЧИСЛО

Следующий способ является своеобразной комбинацией функций ЕСЛИ и ЕЧИСЛО.

«Логическое выражение»

ЕЧИСЛО

«ИСТИНА»

«ЛОЖЬ»

А вот значения следующих двух аргументов оператора ЕСЛИ мы переставляем местами. То есть, в поле «Значение если истина» указываем формулу расчета, а в поле «Значение если ложь» ставим пробел – « ».

После того, как данные введены, жмем на кнопку «OK».

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Как удалить только 0 (нулевые) значения из столбца в excel 2010

6 cihata87 [2013-06-18 14:38:00]

Я хочу удалить значения из целого столбца, где значение ячейки равно 0.

Результирующие ячейки должны быть пустыми.

Как я могу написать формулу для этого? Какие-либо предложения?

12 ответов

4 Решение cihata87 [2013-06-18 16:06:00]

Я выбрал столбцы, которые хочу удалить 0, а затем щелкнул DATA > FILTER. В заголовке столбца появляется значок фильтра. Я нажал на этот значок и выбрал только 0 значений и нажал OK. Выбирается только 0 значений. Наконец, очистите содержимое или используйте кнопку DELETE. Проблема решена!

12 Quichy [2015-01-29 22:20:00]

Нажмите Control + H , затем выберите Options и отметьте Match entire cell contents и Match case . В поле Find what введите a 0 и оставьте поле Replace with пустым. Затем замените все. Это приведет к удалению всех нулей, которые являются автономными.

3 Josh Riv [2014-07-04 11:49:00]

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

= IF (желаемая ячейка = 0, “”, желаемая ячейка)

Это должно вернуть скорректированный столбец, который затем можно скопировать и вставить обратно в исходный столбец AS TEXT – если вы просто скопируете вставку, будет выполняться циклическая ссылка, и все данные будут стерты из обоих столбцов. Не волнуйтесь, если вы не прочитали это внимательно. Ctrl + Z – ваш лучший друг.

1 endowdly [2013-06-18 15:12:00]

Это не отличный ответ, но он должен привести вас к правильному. Я не написал VBA в течение твердой минуты, поэтому не могу вспомнить точный синтаксис, но здесь для вас есть какой-то “код psudeo”. Я знаю, что вы можете легко реализовать это в макросе VBA

В принципе, VBA проходит через каждую строку. Если ячейка в этой строке является целым числом и равна нулю, просто замените ее на “”. Он не будет пустым, но, похоже, будет. Я думаю, что есть также свойство cell.value is empty , которое может очищать содержимое ячейки. Используйте библиотеку в VBA, я уверен, что там что-то есть.

В качестве альтернативы, если это одноразовый задание, вы можете использовать специальный фильтр. Просто выберите фильтр с ленты и замените все 0 s по строке с пробелом.

Надеюсь, это поможет вам начать.

Решение (Ctrl + F) действительно близко – только последний шаг в этом процессе не был сформулирован. Хотя автор прав в пространстве = “0”, это не будет иметь никакого отношения к этому методу. Данные, которые вы ищете (все, что вы хотите удалить), могут быть любыми.

Найдите данные, которые вы хотите удалить (в поле “Найти” ). В поле “Заменить” оставьте поле пустым. Затем замените или замените все. Ячейки с этими конкретными данными будут опустошены.

Спасибо, что встретил меня на правильном пути.

Вы не должны использовать пробел “вместо” 0″, потому что excel обрабатывает пробел как значение.

Итак, ответ с опцией (Ctrl + F). Затем щелкните параметры и поставьте в Find с “0”. Затем нажмите (Сопоставьте все содержимое ячейки. Наконец, замените или замените все до вас.

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

0 Matt [2015-11-20 05:36:00]

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

Измените тип правила, чтобы “форматировать только ячейки, которые содержат” Значение ячейки > равно > 0.

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

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

Проблема с решением Command + F. Он заменит все 0, если вы нажмете “заменить все”. Это означает, что если вы не просмотрите каждый нуль, нуль, содержащийся в важных ячейках, также будет удален. Например, если у вас есть номера телефонов, которые имеют (420) кодов областей, все они будут изменены на (40).

Самый простой способ заключается в следующем: Нажмите кнопку “Офис” (вверху слева) Нажмите “Параметры Excel” Нажмите “Дополнительно” Прокрутите вниз до пункта “Показать параметры для этого рабочего листа” Разблокируйте поле “Показывать нуль в ячейках с нулевым значением” Нажмите “okay”

Это все, что нужно.

(Ctrl + H) → Найти и заменить окно открывается → Найти то, что “0”, заменить на “(оставьте поле пустым) → параметры кликов соответствуют всем содержимым ячейки → нажмите кнопку” Заменить все”.

Рассмотрим ваши данные в столбце A и теперь напишите кодировку

Затем удалить пустые строки из удаленных 0 значений. Я нажимаю DATA > FILTER Я нажал на этот значок фильтра, а невыбранные пробелы копируют и вставляют оставшиеся данные в новый лист.

Excel — как удалить дубликаты но оставить уникальные значения?

Всем добрый вечер! Случалось ли Вам когда нибудь работать с данными в excel строковое значение которых переваливает за пару десятков тысяч? А вот мне человеку который создает и продвигает сайты и интернет магазины приходится сталкиваться достаточно часто, особенно когда дело касается загрузки и выгрузки данных от поставщика на сайт интернет магазина. Данная заметка родилась не на пустом месте, а прямо так сказать с пылу жару! Сегодня делал загрузку на свой интернет магазин по интим тематике (см портфолио) и после того как скачал прайс от поставщика и открыв его в excel (перед загрузкой на сайт я сначала все сверяю, на случай ошибок и случайных изменений столбцов со стороны поставщика) и увидел что из 25 тыс строк более 6-8 тыс являются дубликатами, зачем и почему так делает поставщик мы сейчас обсуждать не будем, на это не хочется тратить не сил, ни времени, а просто понимаем, что так сделал программист и по другому это делать нельзя!

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

Удаление дубликатов в Microsoft Excel

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

В данной статье будет представлено два варианта решения проблемы.

1 Вариант — Стандартная функция в эксель — Удалить дубликаты

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

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

2 Вариант — Пометить дубликаты строк в Лож или Истина

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

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

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

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

ЛОЖ — если А1 не будет равно А2

Истина — если А1 будет ровно А2

если применить этот вариант на столбце Пример2, то как вы поняли везде будет значение Лож

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

3 Вариант — Удалить дубликаты в столбе

Вот этот вариант уже более сложный, но он решит вашу проблему на все 100% и сразу ответит на все вопросы.

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

Мы как и в прошлый раз в соседнюю ячейку вставляем следующую формулу

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

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

Таким образом я в документе который который скачал у поставщика создал перед артикулом пустой столбце и далее применил эту формулу и после отфильтровав получил документ который был на 6-8 тыс строк меньше и самое главное после удаление дубликатов у меня не поднимались значения вверх, все стояло на своих местах

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

Оценка статьи:

Загрузка…

Adblock
detector

Источник

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

Удалить строку, если ячейка содержит ноль, с помощью VBA в Excel

Удалить строку, если ячейка содержит ноль с Kutools for Excel

Удалить строку, если ячейка содержит ноль с Kutools for Excel

Относительные статьи:

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Question

Answers

Удаление нулевых значений в Microsoft Excel

Алгоритмы удаления нулей

Способ 1: настройки Excel

Способ 2: применение форматирования

Способ 3: условное форматирование

Способ 4: применение функции ЕСЛИ

Способ 5: применение функции ЕЧИСЛО

Как удалить только 0 (нулевые) значения из столбца в excel 2010

12 ответов

Excel — как удалить дубликаты но оставить уникальные значения?

Удаление дубликатов в Microsoft Excel

1 Вариант — Стандартная функция в эксель — Удалить дубликаты

2 Вариант — Пометить дубликаты строк в Лож или Истина

3 Вариант — Удалить дубликаты в столбе

А вот еще интересные статьи: