зміст
Постановка проблеми
У нас є діапазон клітинок із даними, які містять порожні клітинки:
Завдання - видалити порожні клітинки, залишивши тільки клітинки з інформацією.
Спосіб 1. Грубий і швидкий
- Вибір вихідного діапазону
- Натисніть клавішу F5, наступна кнопка Виділіть (Спеціальний). У вікні, що відкриється, виберіть Порожні клітини(Пробіли) і натисніть кнопку OK.
Виділено всі порожні комірки в діапазоні.
- Даємо команду в меню видалити виділені комірки: правою кнопкою миші- Видалити комірки (Видалити клітинки) зі зсувом вгору.
Спосіб 2: формула масиву
Щоб спростити, давайте назвемо наші робочі діапазони за допомогою Менеджер імен (Менеджер імен) таб формула (Формули) або, в Excel 2003 і старіших версіях, меню Вставити – Ім’я – Призначити (Вставити — Назва — Визначити)
Назвіть діапазон B3:B10 HaveEmpty, діапазон D3:D10 – NoneEmpty. Діапазони повинні бути строго однакового розміру і можуть розташовуватися будь-де відносно один одного.
Тепер виділіть першу клітинку другого діапазону (D3) і введіть у неї цю страшну формулу:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIRECT(ADDRESS(LOWEST((IF(Empty<>“”,ROW(Empty);ROW() + РЯДКИ(Є порожні))); РЯДОК()-РЯДОК(Немає порожніх)+1); СТОВПЕЦ(Є порожні); 4)))
В англійській версії це буде:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Empty),””,INDIRECT(ADDRESS(SMALL((IF(Empty<>“”,ROW(Empty),ROW() +ROWS(Є порожній))),ROW()-ROW(NoEmpty)+1),COLUMN(Є порожній),4)))
Крім того, її потрібно вводити як формулу масиву, тобто натискати після вставки Що натомість? Створіть віртуальну версію себе у (як зазвичай) і Ctrl + Shift + Enter. Тепер формулу можна скопіювати за допомогою автозаповнення (перетягніть чорний хрестик у правому нижньому кутку комірки) – і ми отримаємо вихідний діапазон, але без порожніх комірок:
Спосіб 3. Спеціальна функція у VBA
Якщо є підозра, що вам доведеться часто повторювати процедуру видалення порожніх комірок з діапазонів, то краще один раз додати в стандартний набір власну функцію видалення порожніх комірок і використовувати її в усіх наступних випадках.
Для цього відкрийте редактор Visual Basic (ALT + F11), вставте новий порожній модуль (меню Вставка – модуль) і скопіюйте туди текст цієї функції:
Функція 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) Для кожного Rng у 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 = Результат End If End Функція
Не забудьте зберегти файл і повернутися з редактора Visual Basic на Excel. Щоб використовувати цю функцію в нашому прикладі:
- Виберіть достатній діапазон порожніх клітинок, наприклад F3:F10.
- Перейдіть до меню Вставка – функція (Вставити — функція)або натисніть на кнопку Функція вставки (Вставити функцію) таб формула (Формули) у новіших версіях Excel. У категорії Визначений користувачем (Визначений користувачем) вибрати нашу функцію NoBlanks.
- Укажіть вихідний діапазон із пустотами (B3:B10) як аргумент функції та натисніть Ctrl + Shift + Enterщоб ввести функцію як формулу масиву.
:
- Видалення всіх порожніх рядків у таблиці за допомогою простого макросу
- Видалення всіх порожніх рядків на робочому аркуші за допомогою надбудови PLEX
- Швидке заповнення всіх порожніх клітинок
- Що таке макроси, куди вставляти макрокод у VBA