Навигация:  Формулы и функции >

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

Print this Topic Previous pageReturn to chapter overviewNext page
скрыть/показать скрытый текст

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

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

 

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

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

Знаете ли Вы, что, когда некоторые исследователи, пытающиеся примирить релятивизм и эфирную физику, говорят, например, о том, что космос состоит на 70% из "физического вакуума", а на 30% - из вещества и поля, то они впадают в фундаментальное логическое противоречие. Это противоречие заключается в следующем.

Вещество и поле не есть что-то отдельное от эфира, также как и человеческое тело не есть что-то отдельное от атомов и молекул его составляющих. Оно и есть эти атомы и молекулы, собранные в определенном порядке. Также и вещество не есть что-то отдельное от элементарных частиц, а оно состоит из них как базовой материи. Также и элементарные частицы состоят из частиц эфира как базовой материи нижнего уровня. Таким образом, всё, что есть во вселенной - это есть эфир. Эфира 100%. Из него состоят элементарные частицы, а из них всё остальное. Подробнее читайте в FAQ по эфирной физике.

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

Форум Рыцари теории эфира


Рыцари теории эфира
 10.11.2021 - 12:37: ПЕРСОНАЛИИ - Personalias -> WHO IS WHO - КТО ЕСТЬ КТО - Карим_Хайдаров.
10.11.2021 - 12:36: СОВЕСТЬ - Conscience -> РАСЧЕЛОВЕЧИВАНИЕ ЧЕЛОВЕКА. КОМУ ЭТО НАДО? - Карим_Хайдаров.
10.11.2021 - 12:36: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от д.м.н. Александра Алексеевича Редько - Карим_Хайдаров.
10.11.2021 - 12:35: ЭКОЛОГИЯ - Ecology -> Биологическая безопасность населения - Карим_Хайдаров.
10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> Проблема государственного терроризма - Карим_Хайдаров.
10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> ПРАВОСУДИЯ.НЕТ - Карим_Хайдаров.
10.11.2021 - 12:34: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вадима Глогера, США - Карим_Хайдаров.
10.11.2021 - 09:18: НОВЫЕ ТЕХНОЛОГИИ - New Technologies -> Волновая генетика Петра Гаряева, 5G-контроль и управление - Карим_Хайдаров.
10.11.2021 - 09:18: ЭКОЛОГИЯ - Ecology -> ЭКОЛОГИЯ ДЛЯ ВСЕХ - Карим_Хайдаров.
10.11.2021 - 09:16: ЭКОЛОГИЯ - Ecology -> ПРОБЛЕМЫ МЕДИЦИНЫ - Карим_Хайдаров.
10.11.2021 - 09:15: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Екатерины Коваленко - Карим_Хайдаров.
10.11.2021 - 09:13: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вильгельма Варкентина - Карим_Хайдаров.
Bourabai Research - Технологии XXI века Bourabai Research Institution