Удаление пустых ячеек из диапазона
Постановка задачи
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача - удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу F5, далее кнопка Выделить (Special). В открывшмся окне выбираем Пустые ячейки (Blanks) и жмем ОК.
Выделяются все пустые ячейки в диапазоне. - Даем в меню команду на удаление выделенных ячеек: правой кнопкой мыши - Удалить ячейки (Delete Cells) со сдвигом вверх.
Способ 2. Формула массива
Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)
Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 - НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.
Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:
=ЕСЛИ(СТРОКА()-СТРОКА(НетПустых)+1>ЧСТРОК(ЕстьПустые)-СЧИТАТЬПУСТОТЫ(ЕстьПустые);"";ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(ЕстьПустые<>"";СТРОКА(ЕстьПустые);СТРОКА()+ЧСТРОК(ЕстьПустые)));СТРОКА()-СТРОКА(НетПустых)+1);СТОЛБЕЦ(ЕстьПустые);4)))
В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),"",INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>"",ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))
Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) - и мы получим исходный диапазон, но без пустых ячеек:
Способ 3. Пользовательская функция на VBA
Если есть подозрение, что вам часто придется повторять процедуру удаления пустых ячеек из диапазонов, то лучше один раз добавить в стандартный набор свою функцию для удаления пустых ячеек, и пользоваться ей во всех последующих случаях.
Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этой функции:
Function NoBlanks(DataRange As Range) As Variant()
Dim N As Long
Dim N2 As Long
Dim Rng As Range
Dim MaxCells As Long
Dim Result() As Variant
Dim R As Long
Dim C As Long
MaxCells = Application.WorksheetFunction.Max( _
Application.Caller.Cells.Count, DataRange.Cells.Count)
ReDim Result(1 To MaxCells, 1 To 1)
For Each Rng In DataRange.Cells
If Rng.Value <> vbNullString Then
N = N + 1
Result(N, 1) = Rng.Value
End If
Next Rng
For N2 = N + 1 To MaxCells
Result(N2, 1) = vbNullString
Next N2
If Application.Caller.Rows.Count = 1 Then
NoBlanks = Application.Transpose(Result)
Else
NoBlanks = Result
End If
End Function
Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:
- Выделите достаточный диапазон пустых ячеек, например F3:F10.
- Идем в меню Вставка - Функция (Insert - Function) или жмем на кнопку Вставить функцию (Insert Function) на вкладке Формулы (Formulas) в новых версиях Excel. В категории Определенные пользователем (User Defined) выберите нашу функцию NoBlanks.
- В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.
Ссылки по теме:
- Удаление сразу всех пустых строк в таблице простым макросом
- Удаление сразу всех пустых строк на листе с помощью надстройки PLEX
- Быстрое заполнение всех пустых ячеек
- Что такое макросы, куда вставлять код макросов на VBA
2-й метод там не работает (((
Спасибо
давно нуждалась в чем то подобном.
Формула и макрос позволяют сделать этот процесс автоматическим, и при изменении в табличке с пустыми значениями, табличка без пустых значений также обновляется.
Когда я пользовалась автофильтром, то новую табличку без пустых строк созавала путем копирования. Но приходилось отслеживать изменения в исходной табличке, каждый раз копировать, а потом делать еще кучу другой ненужной работы, связанной с изменениями..
Способ 1 имеет один минус, который иногда вылазит боком - если этот диапазон именован, то удаление со сдвигом вверх может привести к тому, что в его имени станет во такая штука "#ССЫЛКА"
Способ 2 - формула из книжки Уокенбаха "Формулы в Эксель". Надо не забывать что ссылки в массивах ЕстьПустые и НетПустых должны быть абсолютные. Минус его в том, что в таком варианте диапазон должен быть строгим - динамически не получится работать. Можно делать массивы через формулу СМЕЩ(Лист1!$D$2;0;0;СЧЁТЗ(Лист1!$D:$D)-1;1)
Спобоб 3 - выглядит симпатично. Сейчас попробуем .
Гляньте на форуме
А можно чтобы в результате пустые ячейки были в начале диапазона, то есть:
(в том числе с нулевым текстом ="" )
Sub delNullandShiftUP()
'
' Используется промежуточное значение ячейки "delzxcvbnm", которого естественно не должно быть в выделенном диапазоне.
Selection.Replace What:="", Replacement:="delzxcvbnm", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="delzxcvbnm", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub
Поменял в модуле стринги на колумны
cell | cell | cell | <blank> | <blank> | cell | cell
Мне нужно убрать 2 ячейки <blank> что бы получилось
cell | cell | cell | cell | cell
Нужно часто удалять пустые ячейки в массиве. Очень обрадовался, когда увидел приведенный макрос, но у меня он почему-то упорно не работает. Excel пишет, что "не может вычислить формулу. В открытой книге обнаружена циклическая ссылка, однако не получается отобразить источник ошибки". После пустые ячейки-таки удаляет, но непустые ячейки заполняет нулями. Подскажите пожалуйста, как исправить ошибку и сделать, чтобы макрос заработал?
Непустые ячейки можно перенести/отбирать без формулы массива, которая очень длинная и загружает машину (когда имеем дело с большим количеством строк). Правда это потребует создания двух дополнительных столбцов, но зато формулы лёгкие. Если интересно, могу поделиться.
Спасибо!
В диапазоне A2:A20, в часть ячеек введены значения (остальная часть, соответственно пустая).
1) В B2 вводим =ЕСЛИ(ЕПУСТО(A2);"";СЧЁТ(B$1:B1)+1) и копируем формулу до B20. Эта формула пронумерует только непустые ячейки;
2) В C2 вводим 1, в C3 =ЕСЛИ(ИЛИ(МАКС($B$2:$B$20)=C2;C2="";);"";C2+1) и копируем её до C20. Эта формула заполняет порядковыми номерами столбец C;
3) В D2 вводим =ЕСЛИ(C2="";"";ИНДЕКС($A$2:$A$20;ПОИСКПОЗ(C2;$B$2:$B$20;0);1)). Эта формула (пока по столбцу C есть порядковый номер) находит в столбце A соответствующий этому порядковому номеру значение
Вот так. Этот же подход можно использовать для извлечения уникальных значений.
Кстати Том Кукуруз спрашивал как быть с ячейками в которых написаны формулы.
Заменил в формулах пустое значение на 0 , в B2 сделал правку ЕСЛИ(A2=0; "" и т.д.
Можно и другое применить значение. если 0 это тоже результат поиска или вычисления и с ним нужно работать.
Ее раз благодарю за подсказку с подсчетом нужных строчек и сборку подсчета в последовательные строчки. классно!
Необходимо таблицу 1 преобразовать в таблицу 2, с помощью формул. Все ячекки в таблице 1 содержат формулы.