Содержание:
Постановка задачи
У нас есть диапазон ячеек с данными, содержащими пустые ячейки:
Задача — удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выбор исходного диапазона
- Нажмите кнопку F5, следующая кнопка Выделите (Особый). В открывшемся окне выберите Пустые ячейки(Пробелы) и нажмите OK.
Все пустые ячейки в диапазоне выбраны.
- Даём в меню команду на удаление выделенных ячеек: клик правой кнопкой мыши- Удалить ячейки (Удалить ячейки) со сдвигом вверх.
Метод 2: формула массива
Для упрощения назовем наши рабочие диапазоны, используя Менеджер имен (имя-менеджер) таб формула (Формулы) или, в Excel 2003 и более ранних версиях, меню Вставить – Имя – Назначить (Вставить — Имя — Определить)
Назовите диапазон B3:B10. Естьпустой, диапазон D3:D10 – НетПустой. Диапазоны должны быть строго одинакового размера и могут располагаться в любом месте относительно друг друга.
Теперь выделите первую ячейку второго диапазона (D3) и введите в нее эту страшную формулу:
=ЕСЛИ(СТРОКА() -СТРОКА(NoEmpty)+1>NOTROWS(ДаПусто)-СЧЕТПУСТО(ДаПусто);»»;ДВССЫЛ(АДРЕС(НИЖНИЙ((ЕСЛИ(Пусто<>“»,СТРОКА(Пусто);СТРОКА() + ROWS(Есть пусто))); LINE()-СТРОКА(Нет пусто)+1); COLUMN(Есть пусто); 4)))
В английской версии это будет:
=ЕСЛИ(СТРОКА()-СТРОКА(NoEmpty)+1>СТРОКИ(Пусто)-СЧЕТПУСТО(Пусто)»,»,ДВССЫЛ(АДРЕС(МАЛЕНЬ((ЕСЛИ(Пусто<>“»,СТРОКА(Пусто),СТРОКА() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))
Причем его нужно вводить как формулу массива, т.е. нажать после вставки Enter (как обычно) и Shift + Ctrl + Enter. Теперь формулу можно скопировать с помощью автозаполнения (перетащите черный крестик в правом нижнем углу ячейки) — и мы получим исходный диапазон, но без пустых ячеек:
Способ 3. Пользовательская функция в VBA
Если есть подозрение, что процедуру удаления пустых ячеек из диапазонов придется часто повторять, то лучше один раз добавить в стандартный набор собственную функцию удаления пустых ячеек и использовать ее во всех последующих случаях.
Для этого откройте редактор Visual Basic (ALT + F11), вставьте новый пустой модуль (меню Вставка – Модуль) и скопируйте туда текст этой функции:
Функция NoBlanks(DataRange As Range) Как Variant() Dim N Как длинный Dim N2 Как длинный Dim Rng Как диапазон Dim MaxCells Как длинный Dim Result() Как вариант Dim R Как длинный Dim C Как длинный MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) Результат ReDim (от 1 до MaxCells, от 1 до 1) Для каждого кольца в DataRange.Cells Если Rng.Value <> vbNullString Тогда N = N + 1 Результат (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 Тогда NoBlanks = Application.Transpose(Result) Else NoBlanks = Result Конец, если Конечная функция
Не забудьте сохранить файл и вернуться из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:
- Выберите достаточный диапазон пустых ячеек, например F3:F10.
- Перейти в меню Вставка – Функция (Вставка — Функция)или нажмите на кнопку Вставить функцию (Вставить функцию) таб формула (Формулы) в новых версиях Excel. В категории User Defined (Определяемые пользователем) выберите нашу функцию NoBlanks.
- Укажите исходный диапазон с пустотами (B3:B10) в качестве аргумента функции и нажмите Shift + Ctrl + Enterчтобы ввести функцию как формулу массива.
:
- Удаление всех пустых строк в таблице сразу с помощью простого макроса
- Удаление всех пустых строк на листе одновременно с помощью дополнения PLEX
- Быстрое заполнение всех пустых ячеек
- Что такое макросы, куда вставлять код макроса в VBA