Видалення порожніх рядків і стовпців у даних

У багатьох випадках порожні рядки та стовпці можуть бути проблемою для таблиць. Стандартні функції для сортування, фільтрації, підсумовування, створення зведених таблиць тощо сприймають порожні рядки та стовпці як розрив таблиці, не збираючи дані, розташовані далі за ними. Якщо таких розривів багато, то ручне їх усунення може бути дуже дорогим, а прибрати всі відразу «масово» за допомогою фільтрації не вийде, тому що фільтр буде «спотикатися» і на обривах.

Давайте розглянемо кілька способів вирішення цієї проблеми.

Спосіб 1. Пошук порожніх клітинок

Можливо, це не найзручніший, але безперечно найпростіший спосіб вартий згадки.

Припустимо, ми маємо справу з такою таблицею, що містить багато порожніх рядків і стовпців всередині (виділено для ясності):

Припустимо, ми впевнені, що перший стовпець нашої таблиці (стовпець B) завжди містить назву міста. Тоді порожні клітинки в цьому стовпці будуть ознакою непотрібних порожніх рядків. Щоб швидко видалити їх усі, виконайте такі дії:

  1. Виберіть діапазон із містами (B2:B26)
  2. Натисніть клавішу F5 і натисніть Виділіть (Перейти до спеціального) або виберіть на вкладці Головна — Знайти та вибрати — Виділення групи клітинок (Додому — Знайти&Вибрати — Перейти до спеціального).
  3. У вікні, що відкриється, виберіть опцію Порожні клітини (Пробіли) і натисніть OK – всі порожні комірки в першому стовпці нашої таблиці повинні бути виділені.
  4. Тепер виберіть на вкладці Головна Command Видалити – видалити рядки з аркуша (Delete — Видалити рядки) або натисніть комбінацію клавіш Ctrl+мінус – і наше завдання вирішене.

Звичайно, точно таким же чином можна позбутися порожніх стовпців, взявши за основу заголовок таблиці.

Спосіб 2. Пошук порожніх рядків

Як ви вже могли з’ясувати, попередній спосіб буде працювати лише в тому випадку, якщо наші дані обов’язково містять повністю заповнені рядки та стовпці, які можна підключити під час пошуку порожніх клітинок. Але що, якщо такої впевненості немає, і дані можуть містити порожні клітинки?

Подивіться на наступну таблицю, наприклад, саме для такого випадку:

Тут підхід буде трохи складнішим:

  1. Введіть у клітинку A2 функцію COUNT (COUNTA), який обчислить кількість заповнених комірок у рядку праворуч і скопіює цю формулу на всю таблицю:
  2. Виберіть комірку A2 і ввімкніть фільтр за допомогою команди Дані – Фільтр (Дані — Фільтр) або комбінацію клавіш Ctrl+Shift+L.
  3. Відфільтруємо нулі по обчислюваному стовпчику, тобто всі рядки, де немає даних.
  4. Залишається вибрати відфільтровані рядки і видалити їх командою Головна — Видалити -' Видалити рядки з аркуша (Домашня сторінка — Видалити — Видалити рядки) або комбінацію клавіш Ctrl+мінус.
  5. Відключаємо фільтр і отримуємо дані без порожніх рядків.

На жаль, цей трюк більше не можна використовувати зі стовпцями – Excel ще не навчився фільтрувати за стовпцями.

Спосіб 3. Макрос для видалення всіх порожніх рядків і стовпців на аркуші

Ви також можете використовувати простий макрос для автоматизації цього завдання. Натисніть комбінацію клавіш інший+F11 або виберіть із вкладки розробник — Visual Basic (Розробник — редактор Visual Basic). Якщо вкладки розробник не видно, ви можете ввімкнути його через Файл – Параметри – Налаштування стрічки (Файл — Параметри — Налаштувати стрічку).

У вікні редактора Visual Basic, що відкриється, виберіть команду меню Вставка – модуль і в порожньому модулі, що з’явиться, скопіюйте та вставте такі рядки:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub

Закрийте редактор і поверніться до Excel. 

Тепер натисніть комбінацію інший+F8 або кнопку Макрос таб розробник. У вікні, яке відкриється, буде перелічено всі макроси, доступні для запуску, включаючи макрос, який ви щойно створили. DeleteEmpty. Виберіть його та натисніть кнопку прогін (бігти) – усі порожні рядки та стовпці на аркуші будуть миттєво видалені.

Спосіб 4: Power Query

Інший спосіб вирішення нашої проблеми та дуже поширений сценарій — це видалення порожніх рядків і стовпців у Power Query.

Спочатку завантажимо нашу таблицю в редактор запитів Power Query. Ви можете перетворити його на динамічний «розумний» за допомогою комбінації клавіш Ctrl+T або просто вибрати наш діапазон даних і дати йому назву (наприклад, дані) у рядку формул, перетворюючи на named:

Тепер використовуємо команду Data – Get data – From table / range (Дані – Get Data – From table / range) і завантажуємо все в Power Query:

Далі все просто:

  1. Видаляємо порожні рядки командою Головна – Зменшити рядки – Видалити рядки – Видалити порожні рядки (Головна – Видалити рядки – Видалити порожні рядки).
  2. Клацніть правою кнопкою миші на заголовку першого стовпця Місто та виберіть у контекстному меню команду Скасувати розведення інших стовпців. Наша таблиця буде, як це технічно правильно називається, нормалізується – конвертується в три стовпці: місто, місяць і значення з перетину міста і місяця з вихідної таблиці. Особливість цієї операції в Power Query полягає в тому, що вона пропускає порожні клітинки у вихідних даних, що нам і потрібно:
  3. Тепер виконуємо зворотну операцію – повертаємо отриману таблицю в двовимірну, щоб повернути їй початковий вигляд. Виберіть стовпець з місяцями і на вкладці Перетворення вибрати команду Зведений стовпець (Перетворення — зведений стовпець). У вікні, що відкриється, в якості стовпця значень виберіть останнє (Значення), а в додаткових параметрах – операцію Не агрегувати (Не агрегувати):
  4. Залишилося завантажити результат назад в Excel командою Головна — Закрийте та завантажте — Закрийте та завантажте в… (Додому — Закрити&Завантажити — Закрити&Завантажити до…)

  • Що таке макрос, як він працює, куди скопіювати текст макросу, як запустити макрос?
  • Заповнення всіх порожніх комірок у списку значеннями батьківських комірок
  • Видалення всіх порожніх клітинок із заданого діапазону
  • Видалення всіх порожніх рядків на аркуші за допомогою надбудови PLEX

залишити коментар