Удаление пустых ячеек из диапазона

Постановка задачи

Имеем диапазон ячеек с данными, в котором есть пустые ячейки:

delete_blanks1.gif

 

Задача - удалить пустые ячейки, оставив только ячейки с информацией.

Способ 1. Грубо и быстро

  1. Выделяем исходный диапазон
  2. Жмем клавишу F5, далее кнопка Выделить (Special). В открывшмся окне выбираем Пустые ячейки (Blanks) и жмем ОК.

    delete_blanks3.png

    Выделяются все пустые ячейки в диапазоне.
  3. Даем в меню команду на удаление выделенных ячеек: правой кнопкой мыши - Удалить ячейки (Delete Cells) со сдвигом вверх.

Способ 2. Формула массива

Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)

delete_blanks2.gif

 

Диапазону  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. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) - и мы получим исходный диапазон, но без пустых ячеек:

delete_blanks4.gif

 

Способ 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. Чтобы использовать эту функцию в нашем примере:

  1. Выделите достаточный диапазон пустых ячеек, например F3:F10.
  2. Идем в меню Вставка - Функция (Insert - Function) или жмем на кнопку Вставить функцию (Insert Function) на вкладке Формулы (Formulas) в новых версиях Excel. В категории Определенные пользователем (User Defined) выберите нашу функцию NoBlanks.
  3. В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.

Ссылки по теме:

 



Анна
28.10.2012 22:47:38
Я всегда для удаления пустых строк пользуюсь автофильтром, и помоему это самый удобный и простой вариант.
YA
28.10.2012 22:48:34
Для пользующихся автофильтром сообщаю, что автофильтр не отсортировывает полностью пустые строки.
28.10.2012 22:52:31
Ну, если хватит терпения по каждому столбцу включать фильтр "Пустые", то ... :)
28.10.2012 22:53:45
С Автофильтром осторожнее - иногда (в Excel 2007) при этом удаляются не только отфильтрованные строки.
25.11.2016 01:58:46
Николай, добрый день. Подскажите, пожалуйста, метод удаления в столбце пустых ячеек формулами, подходящий для google дока.
2-й метод там не работает (((
Спасибо
Вика
28.10.2012 22:48:07
Огромное спасибо!!!!
давно нуждалась в чем то подобном.
Формула и макрос позволяют сделать этот процесс автоматическим, и при изменении в табличке с пустыми значениями, табличка без пустых значений также обновляется.
Когда я пользовалась автофильтром, то новую табличку без пустых строк созавала путем копирования. Но приходилось отслеживать изменения в исходной табличке, каждый раз копировать, а потом делать еще кучу другой ненужной работы, связанной с изменениями..
Эдуард
28.10.2012 22:49:11
Добрый день, Николай! Если у ВАС будет возможность, подскажите пожалуйста: Какими действиями можно удалить пустые строки в ячейке, которые образуются в результате использования Alt+Enter и в случае копирования файлов из других программ. С уважением, Эдуард.
28.10.2012 22:54:20
Можно попробовать применить функцию ПЕЧСИМВ (CLEAN)
zvenokeeper
28.10.2012 22:50:30
Большое спасибо за материал! Больше нигде не нашёл подобного..
Денис
28.10.2012 22:51:22
Не то что бы автор гений, но молодец что помогает другим людям.
Способ 1 имеет один минус, который иногда вылазит боком - если этот диапазон именован, то удаление со сдвигом вверх может привести к тому, что в его имени станет во такая штука "#ССЫЛКА"
Способ 2 - формула из книжки Уокенбаха "Формулы в Эксель". Надо не забывать что ссылки в массивах ЕстьПустые и НетПустых должны быть абсолютные. Минус его в том, что в таком варианте диапазон должен быть строгим - динамически не получится работать. Можно делать массивы через формулу СМЕЩ(Лист1!$D$2;0;0;СЧЁТЗ(Лист1!$D:$D)-1;1)
Спобоб 3 - выглядит симпатично. Сейчас попробуем .
19.06.2013 09:34:58
Второй способ не всегда помогает. Дело в том, что есть ДЕЙСТВИТЕЛЬНО пустые ячейки, а есть ячейки которые ВЫГЛЯДЯТ как пустые. Но в них может быть текст с нулевой длиной (=""). Или шрифт и заливка ячейки одним цветом.
Гляньте на форуме пару примеров
22.01.2014 15:29:49
подскажите пожалуйста, способ 2 не работает когда список с пустыми ячейками находится в другом листе?
30.04.2014 15:23:32
Если исходный диапазон больше 65 536 ячеек, то макрос выдает ошибку, видимо из-за внутреннего переполнения экселя. Поможет немного измененный вариант (при условии, что в результирующем диапазоне ячеек меньше, чем указанное число):
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)
For Each Rng In DataRange.Cells
    If Rng.Value <> vbNullString Then
         N2 = N2 + 1
    End If
Next Rng
ReDim Result(1 To N2, 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
If Application.Caller.Rows.Count = 1 Then
      NoBlanks = Application.Transpose(Result)
Else
      NoBlanks = Result
End If
End Function
12.08.2016 02:19:54
круто) мне помогло!
07.10.2014 20:59:43
как удалить все пустые ячейки сразу в 3-х столбцах?
03.11.2014 22:53:17
спасибо за формулу, остаётся идиотский вопрос... Столбец В он не просто набор циферок... В строке А есть наименование. строка В это уже отражение есть это наименование или нет... Как мне в другом месте/листе составить список  из столбца А и В тех что выше нуля/не пустые?
28.01.2016 11:36:19
Привет!
А можно чтобы в результате пустые ячейки были в начале диапазона, то есть:

естьПустыенетПустых
16
2
31
42
5
63
77
8
96
10111
112
123
1317
1411
1521
162
Спасибо.
Удаление всех пустых ячеек из выделенного диапазона со сдвигом вверх:
(в том числе с нулевым текстом ="" )

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
05.03.2017 10:37:16
Николай, можно ли то же самое (способ 3) выполнить для строки ?
Поменял в модуле стринги на колумны :)  - строит.. но перестали учитываться нули в исходном диапазоне и появились в конце вместо пустот. :(
31.05.2018 10:23:37
А что делать если мне нужно удалить не строку пустую, а 1 или 2 пустые ячейки в стрке?

cell | cell | cell | <blank> | <blank> | cell | cell

Мне нужно убрать 2 ячейки <blank> что бы получилось
cell | cell | cell | cell | cell
09.07.2018 18:15:23
Присоединяюсь к вопросу Игоря, по поводу удаления пустых ячеек в строке, со  сдвигом влево. Строк 1500, столбцов  около 60 и все по разному заполнены
09.07.2018 18:46:27
Нашел. С помощью "Найти и выделить" решается вопрос. Выделять лучше именно ячейки, а не столбцы или строк. Тогда "Найти и выделить" ругаться будет
17.07.2018 06:14:04
Здравствуйте!
Нужно часто удалять пустые ячейки в массиве. Очень обрадовался, когда увидел приведенный макрос, но у меня он почему-то упорно не работает. Excel пишет, что "не может вычислить формулу. В открытой книге обнаружена циклическая ссылка, однако не получается отобразить источник ошибки". После пустые ячейки-таки удаляет, но непустые ячейки заполняет нулями. Подскажите пожалуйста, как исправить ошибку и сделать, чтобы макрос заработал?  
17.07.2018 12:09:24
Вы в качестве аргумента функции не указываете, часом, ячейку, в которой она сама же и расположена? Циклические ссылки обычно так и получаются.
18.07.2018 09:01:44
Николай, Спасибо! Макрос заработал) Получается ещё один массив, без пустых ячеек, но который привязан к массиву с пустыми ячейками. Содержимое ячеек нового массива - функция NoBlanks. Я не точно сформулировал задачу. Нужен текст, который в ячейках и на том же месте. То есть нужно удалить пустые ячейки в заданном массиве под которым есть ещё данные. Так, чтобы то, что снизу не поплыло вверх. Выделяешь массив, нажимаешь валшэбный кнопка и заполненные ячейки в которых текст "подтягиваются" вверх и друг к другу. Можно так?
06.11.2018 08:15:43
Приветствую!
Непустые ячейки можно перенести/отбирать без формулы массива, которая очень длинная и загружает машину (когда имеем дело с большим количеством строк). Правда это потребует создания двух дополнительных столбцов, но зато формулы лёгкие. Если интересно, могу поделиться.
Спасибо!
06.11.2018 21:44:05
Делитесь, интересно :)
07.11.2018 20:26:03
Егор, приветствую!
В диапазоне 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 соответствующий этому порядковому номеру значение
Вот так. Этот же подход можно использовать для извлечения уникальных значений.
Наверх