Як видалити всі порожні рядки в Excel

У цій статті я поясню, навіщо видаляти порожні рядки в Excel за допомогою виділіть порожні клітинки > видалити рядок це погана ідея, і я покажу вам 2 швидких і правильних способи видалення порожніх рядків без руйнування даних. Усі ці методи працюють у Excel 2013, 2010 та старіших версіях.

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

Існує багато причин появи порожніх рядків. Наприклад, ви отримали книгу Excel від іншої людини або в результаті експорту з корпоративної бази даних, або непотрібні дані в рядках були видалені вручну. У будь-якому випадку, якщо ваша мета — видалити всі ці порожні рядки та мати чисту та охайну таблицю, виконайте наведені нижче прості кроки:

• Ніколи не видаляйте порожні рядки з виділенням порожніх клітинок
• Видалення порожніх рядків за допомогою ключового стовпця
• Видалення порожніх рядків у таблиці без ключового стовпця

Ніколи не видаляйте порожні рядки з виділенням порожніх клітинок

По всьому Інтернету ви знайдете просту підказку, яка нібито дозволяє видаляти порожні рядки:

• Виділіть дані від першої до останньої клітинки.
• прес F5щоб відкрити діалогове вікно До (Перехід).
• У діалоговому вікні натисніть кнопку спеціальний (Виділити).
• У діалоговому вікні Перейти до спец (Виділити групу клітинок) поставте прапорець заготовки (Порожні клітинки) і натисніть OK.
• Клацніть правою кнопкою миші будь-яку з виділених комірок і натисніть видаляти (Видалити).
• У діалоговому вікні видаляти (Видалити клітинки) вибрати Весь ряд (рядок) і натисніть OK.

Це дуже поганий спосіб., робіть це лише з дуже простими таблицями з парою десятків рядків, які поміщаються на одному екрані, або ще краще – не робіть цього взагалі! Основна причина полягає в тому, що якщо рядок з важливими даними містить хоча б одну порожню комірку, то весь рядок буде видалено.

Наприклад, у нас є таблиця клієнтів із 6 рядками. Ми хочемо видалити лінії 3 и 5тому що вони порожні.

Зробіть, як запропоновано вище, і отримаєте такий результат:

Лінія 4 (Роджер) також зник, тому що кел D4 в колонку Джерело трафіку виявився порожнім

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

Далі в цій статті я покажу вам 2 швидких і надійних способи видалення порожніх рядків із аркушів Excel.

Видалення порожніх рядків за допомогою ключового стовпця

Цей метод працює, якщо у вашій таблиці є стовпець, який допомагає визначити, порожній цей стовпець чи ні (ключовий стовпець). Наприклад, це може бути ідентифікатор клієнта, номер замовлення чи щось подібне.

Для нас важливо зберегти порядок рядків, тому ми не можемо просто відсортувати таблицю за цим стовпцем, щоб перемістити всі порожні рядки вниз.

1. Виберіть всю таблицю, від першого до останнього рядка (натисніть Ctrl + Home, А потім Ctrl + Shift + End).
2. Додати автофільтр до таблиці. Для цього на табл дані (дані) натисніть фільтр (Фільтр).
3. Застосувати фільтр до стовпця Cust#. Для цього натисніть кнопку зі стрілкою в заголовку стовпця, зніміть прапорець Вибрати всі (Вибрати все), прокрутіть униз до кінця списку (на практиці цей список може бути досить довгим) і поставте прапорець заготовки (Пусто) у самому низу списку. Натисніть OK.
4. Виберіть усі відфільтровані рядки: натисніть Ctrl + Home, потім стрілку вниз, щоб перейти до першого рядка даних, а потім натисніть Ctrl + Shift + End.
5. Клацніть правою кнопкою миші будь-яку виділену комірку та виберіть у контекстному меню Видалити рядок (Видалити рядок) або просто клацнути Ctrl + -(знак мінус).
6. У вікні, яке з'являється з питанням Видалити весь рядок аркуша? (Видалити весь рядок аркуша?) клацніть OK.
7. Очистити застосований фільтр: на вкладці дані (дані) натисніть Очистити (Очистити).
8. Чудово! Усі порожні рядки повністю видаляються, а рядок 3 (Роджер) все ще на місці (порівняйте з результатом попередньої спроби).

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

Використовуйте цей метод, якщо у вашій таблиці багато порожніх клітинок, розкиданих по різних стовпцях, і вам потрібно видалити лише ті рядки, у яких немає жодної клітинки з даними.

У цьому випадку ми не маємо стовпця ключа, щоб визначити, порожній рядок чи ні. Тому додаємо в таблицю допоміжний стовпець:

1. У кінці таблиці додайте стовпець з назвою заготовки і вставте таку формулу в першу клітинку стовпця:

   =COUNTBLANK(A2:C2)

   =СЧИТАТЬПУСТОТЫ(A2:C2)

   Ця формула, як випливає з назви, підраховує порожні клітинки в заданому діапазоні. A2 и C2 є першою та останньою комірками поточного рядка відповідно.

   

2. Скопіюйте формулу на весь стовпець. Як це зробити – дивіться покрокову інструкцію Як вставити одну і ту ж формулу в усі виділені комірки одночасно.
3. Тепер у нашій таблиці є ключовий стовпець! Застосувати фільтр до стовпця заготовки (вище наведено крок за кроком, як це зробити), щоб відображати лише рядки з максимальним значенням (3). Номер 3 означає, що всі клітинки в цьому рядку порожні.
4. Далі виберіть усі відфільтровані рядки та видаліть їх повністю. Як це зробити описано вище. У результаті порожній рядок (рядок 5) буде видалено, всі інші рядки (з порожніми клітинками або без них) залишаться на своєму місці.
5. Тепер допоміжну колону можна прибрати. Або ви можете застосувати інший фільтр, щоб відображати лише ті комірки, які мають одну або кілька порожніх клітинок. Для цього зніміть прапорець у рядку зі значенням 0 (нуль) і натисніть OK.