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

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

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

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

Способ 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 - визуальным средам

Знаете ли Вы, что, как и всякая идолопоклонническая религия, релятивизм ложен в своей основе. Он противоречит фактам. Среди них такие:

1. Электромагнитная волна (в религиозной терминологии релятивизма - "свет") имеет строго постоянную скорость 300 тыс.км/с, абсурдно не отсчитываемую ни от чего. Реально ЭМ-волны имеют разную скорость в веществе (например, ~200 тыс км/с в стекле и ~3 млн. км/с в поверхностных слоях металлов, разную скорость в эфире (см. статью "Температура эфира и красные смещения"), разную скорость для разных частот (см. статью "О скорости ЭМ-волн")

2. В релятивизме "свет" есть мифическое явление само по себе, а не физическая волна, являющаяся волнением определенной физической среды. Релятивистский "свет" - это волнение ничего в ничем. У него нет среды-носителя колебаний.

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

4. В гравитационном релятивизме (ОТО) вопреки наблюдаемым фактам утверждается об угловом отклонении ЭМ-волн в пустом пространстве под действием гравитации. Однако астрономам известно, что свет от затменных двойных звезд не подвержен такому отклонению, а те "подтверждающие теорию Эйнштейна факты", которые якобы наблюдались А. Эддингтоном в 1919 году в отношении Солнца, являются фальсификацией. Подробнее читайте в FAQ по эфирной физике.

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

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