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

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

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

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

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

1.	Выделяем исходный диапазон

2.	Выбираем в меню команду Правка - Перейти (Edit - Go To), далее кнопка Выделить (Special). В открывшмся окне выбираем Пустые ячейки (Blank) и жмем ОК.

Выделяются все пустые ячейки в диапазоне.

3.	Даем в меню команду на удаление выделенных ячеек (Правка - Удалить ячейки) со сдвигом вверх.

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

Для упрощения дадим нашим рабочим диапазонам имена, используя меню Вставка - Имя - Присвоить (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. Чтобы использовать эту функцию в нашем примере:

1.	Выделите достаточный диапазон пустых ячеек, например F3:F10.

2.	В окне Мастера функций (меню Вставка - Функция) в категории Определенные пользователем выберите нашу функцию NoBlanks.

3.	В качестве аргумента функции укажите исходный диапазон с пустотами (B3:B10) и нажмите Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива.

к библиотеке 3GL к оглавлению к экономической информатике 4GL - визуальным средам

Знаете ли Вы, как разрешается парадокс Ольберса?
(Фотометрический парадокс, парадокс Ольберса - это один из парадоксов космологии, заключающийся в том, что во Вселенной, равномерно заполненной звёздами, яркость неба (в том числе ночного) должна быть примерно равна яркости солнечного диска. Это должно иметь место потому, что по любому направлению неба луч зрения рано или поздно упрется в поверхность звезды.
Иными словами парадос Ольберса заключается в том, что если Вселенная бесконечна, то черного неба мы не увидим, так как излучение дальних звезд будет суммироваться с излучением ближних, и небо должно иметь среднюю температуру фотосфер звезд. При поглощении света межзвездным веществом, оно будет разогреваться до температуры звездных фотосфер и излучать также ярко, как звезды. Однако в дело вступает явление "усталости света", открытое Эдвином Хабблом, который показал, что чем дальше от нас расположена галактика, тем больше становится красным свет ее излучения, то есть фотоны как бы "устают", отдают свою энергию межзвездной среде. На очень больших расстояниях галактики видны только в радиодиапазоне, так как их свет вовсе потерял энергию идя через бескрайние просторы Вселенной. Подробнее читайте в FAQ по эфирной физике.

НОВОСТИ ФОРУМА

Рыцари теории эфира