Як видалити порожні рядки в таблиці ексель. Видалення рядка в Microsoft Excel

Операцією, що часто зустрічається при роботі з таблицями Excel є видалення порожніх рядків. Існують як стандартні засоби, за допомогою яких можна видалити порожні рядки, так і нестандартні, з використанням програмування на Visual Basic for Application.

Стандартні способи видалення порожніх рядків

Видалення вручну

Найпоширеніший, але при цьому найпримітивніший спосіб, при якому необхідно навести курсор на номер порожнього рядка і клацнути по ньому лівою кнопкою миші. Весь рядок виділиться. Якщо натиснути та утримувати клавішу Ctrl на клавіатурі, можна виділити кілька рядків. Після цього їх можна видалити всі разом, клацнувши правою кнопкою миші у виділеній області та вибравши команду "Видалити" в контекстному меню. Цей спосіб доцільно використовувати, коли необхідно видалити кілька рядків.

Видалення за допомогою сортування

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

Видалення за допомогою виділення групи осередків

Щоб видалити порожні рядки в Excel 2007 і 2010 можна використовувати можливістю виділення групи осередків. Для цього виділяємо стовпець, що містить порожні осередки, після чого знаходимо на стрічці Excel вкладку "Головна", у групі "Редагування" вибираємо кнопку "Знайти та виділити" і вибираємо пункт "Виділення групи осередків". У діалоговому вікні "Виділення групи осередків", що з'явилося, включаємо опцію "Порожні осередки" і натискаємо кнопку ОК. У попередньо виділеному стовпці буде виділено всі порожні осередки. Залишається клікнути правою кнопкою миші в будь-якому місці виділеного поля і вибрати пункт "Видалити..." в контекстному меню, а в діалоговому вікні "Видалення осередків", що з'явилося, вибрати пункт "Рядок" і натиснути ОК. При цьому, строго кажучи, видаляються не порожні рядки, а рядки, що містять порожні комірки попередньо виділеного стовпця.

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

Програмне видалення макросом VBA

Стандартні способи прості у використанні, але мають мінуси. Якщо жоден з стандартних способівне підходить, то для вирішення цього завдання можна використовувати процедуру, написану мовою VBA, яка видаляє всі порожні рядки у діапазоні аркуша, що використовується.

Sub Udalenie_Pustyh_Strok() Dim r As Long, FirstRow As Long, LastRow As Long FirstRow = ActiveSheet.UsedRange.Row LastRow = ActiveSheet.UsedRange.Rows.Count - 1 + ActiveSheet.UsedRange. Application.CountA(Rows(r)) = 0 Then Rows(r).Delete End If Next r End Sub

Автоматичне видалення за допомогою надбудови

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

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

Часто, особливо при імпорті даних уEXCEL, на аркуші можуть формуватися таблиці з ПОВНІСТЮ порожніми рядками. Навчимося швидко видаляти ці непотрібні рядки, які надалі можуть утруднити роботу з таблицею.

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

Наведемо методи, що використовуються при видаленні порожніх рядків. Навіщо потрібно видаляти порожні рядки можна прочитати у статті.

Сортування

Самим простим способомвидалення порожніх рядків є сортування таблиці: виділяємо діапазон, в якому міститься таблиця ( А2: C17 ), потім викликаємо команду меню Дані/ Сортування та фільтр/ Сортування від мінімального до максимальногоабо просто / Сортування .

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

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

Фільтр

Для знаходження порожніх осередків можна також скористатися ( Головна/ Редагування/ Сортування та фільтр/ Фільтр). Вибираємо відповідний стовпець, відображаємо тільки порожні комірки, виділяємо рядки даних, що не містять, і видаляємо їх.

Виділення групи осередків

Іншим способом виділення порожніх осередків є використання інструменту.

виділяємо діапазон А2: C 17 ;
вибираємо пункт меню Головна/ Редагування/ Знайти та виділити/ Виділення групи осередків…,

виберіть пункт порожні осередкив розділі Виділити;

далі натискаємо маленьку стрілочку в меню Головна/ Осередки/ Видалити;
оберемо Видалити комірки (видалити комірки, зі зрушенням догори).

Тут потрібно бути обережним: якщо таблиця стримає крім порожніх рядків, рядки із заповненими та порожніми осередками, то частина осередків перескочить зі своїх рядків на інші, що зіпсує таблицю. Якщо EXCEL не дасть зіпсувати таким чином таблицю: з'явиться повідомлення « Ця командане застосовується для діапазонів, що перекриваються».

ПОРАДА:
Варто пам'ятати, що комірки, що містять формули (навіть якщо це посилання на порожні комірки), не вважаються порожніми за визначенням.

Таблиці, в яких є порожні рядки, виглядають не дуже естетично. До того ж, через зайві рядки навігація по них може ускладнитися, оскільки доведеться прокручувати більший діапазон осередків для переходу з початку таблиці в кінець. Давайте з'ясуємо, які існують способи видалення порожніх рядків у Microsoft Excel, і як їх прибрати швидше і простіше.

Найвідомішим і найпопулярнішим способом видалення порожніх рядків є використання контекстного меню програми Ексель. Щоб прибрати рядки в такий спосіб, виділяємо діапазон комірок, який містить даних, і клацаємо правою кнопкою миші. У контекстному меню, що розкрилося, робимо перехід по пункту «Видалити…». Можна не викликати контекстне меню, а набрати на клавіатурі клавіші «Ctrl+-».

Утворюється маленьке віконце, в якому необхідно вказати, що саме ми хочемо видалити. Виставляємо перемикач у позицію «рядок». Тиснемо на кнопку «OK».

Після цього всі рядки виділеного діапазону будуть видалені.

Як альтернативний варіант, можна виділити комірки у відповідних рядках і, перебуваючи у вкладці «Головна», клацнути по кнопці «Видалити», яка розташована в блоці інструментів «Комірки» на стрічці. Після цього відразу відбудеться видалення без додаткових діалогових вікон.

Звичайно, спосіб дуже простий та відомий. Але, чи є він найзручнішим, найшвидшим і найбезпечнішим?

Сортування

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

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

Після того, як вищезазначена операція буде зроблена, всі порожні осередки перемістяться в самий низ таблиці. Тепер ми можемо видалити ці осередки будь-яким із тих способів, про які йшлося в першій частині уроку.

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

Усі осередки цього стовпця нумеруємо по порядку.

Потім, робимо сортування за будь-яким іншим стовпцем, і видаляємо переміщені вниз комірки, як уже описувалося вище.

Після цього, щоб повернути порядок рядків до того, що був перед сортуванням, проводимо сортування в колонці з номерами рядків «Від мінімального до максимального».

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

Урок:

Застосування фільтра

Ще одним варіантом приховати порожні осередки є застосування фільтра.

Виділяємо всю область таблиці, і, перебуваючи у вкладці «Головна», клацніть по кнопці «Сортування та фільтр», яка розташована в блоці налаштувань «Редагування». У меню, робимо перехід по пункту «Фільтр».

У осередках шапки таблиці з'являється характерний значок. Клацаємо по цьому значку в будь-якому на ваш вибір стовпці.

У меню, що з'явилося, знімаємо галочку з пункту «Порожні». Тиснемо на кнопку «OK».

Як бачимо, після цього всі порожні рядки зникли, оскільки вони були відфільтровані.

Виділення осередків

Ще один спосіб видалення використовує виділення групи порожніх осередків. Щоб використовувати цей спосіб спочатку виділяємо всю таблицю. Потім, перебуваючи у вкладці «Головна», клацаємо по кнопці «Знайти та виділити», яка розташована на стрічці у групі інструментів «Редагування». У меню, клацаємо по пункту «Виділення групи осередків ...».

Відкривається вікно, в якому переставляємо перемикач у позицію «порожні комірки». Тиснемо на кнопку «OK».

Як бачимо, після цього всі рядки, що містять порожні осередки, виділилися. Тепер натискаємо на вже знайому нам кнопку «Видалити», розташовану на стрічці в групі інструментів «Комірки».

Після цього всі порожні рядки будуть видалені з таблиці.

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

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

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

Видалення за допомогою сортування

Звідки можуть з'являтися записи з відсутністю даних? Це може спостерігатися під час вивантаження інформації з бази даних або експорту з іншого файлу або програми. Значення можуть бути знищені вручну без видалення рядка. Так чи інакше, із цим явищем потрібно і можна боротися.

Один із способів - використання сортування. Якщо необхідно видалити ті записи, у яких в жодному осередку немає даних, можна сортувати за будь-яким стовпцем. Якщо тільки ті, що не заповнені в певних колонках - сортуйте тільки цю колонку. Покажемо на прикладі, як це зробити для цілком порожніх записів.

Постачальник косметики надіслав прайс-лист, у якому частину інформації було видалено. Це ускладнює роботу з даними, тому ми хочемо забрати ці рядки. Виберемо для сортування стовпчик «Ціна». Для впорядкування значень можна скористатися меню «Сортування та фільтр» панелі «Редагування» вкладки «Головна», відсортувавши числа за зростанням.

Або скористатися меню заголовка форматованої таблиці. Так чи інакше, після побудови списку рядка з відсутніми цінами виявляться внизу таблиці. Тепер видалімо порожні рядки Excel наприкінці командою контекстного меню.

Використання фільтра

Ще один простий метод – застосування фільтра до даних. Якщо таблицю відформатовано за стилями із заголовками, це полегшує завдання. Якщо ні, встановіть на заголовок будь-якого стовпця або всіх колонок фільтри. Для цього зайдіть на вкладку «Головна», знайдіть панель інструментів «Редагування» та в меню «Сортування та фільтр» виберіть пункт «Фільтр». На заголовках виділеного стовпця або стовпців з'являться кнопки з спрямованими вниз трикутниками, що показують наявність списку, що випадає.

Тепер відкрийте меню цього списку. Внизу переліку елементів колонки знаходиться пункт "Порожні". Виділіть його, попередньо знявши прапорці з решти елементів. В результаті застосування ви залишите лише порожні рядки Excel. Видаліть їх через список операцій правого кліка миші або з блоку операцій «Комірки» вкладки «Головна».

Виділення діапазону полів

Відзначаємо таблицю та викликаємо пункт меню «Виділити групу осередків». Він знаходиться в блоці команд «Редагування» та викликається операцією «Знайти та виділити».

У вікні налаштувань команди вибираємо варіант «порожні комірки» та натискаємо «ОК».

Програма зазначає потрібні записи. Щоб видалити порожні рядки Excel, використовуйте команду «Видалити рядки з аркуша» з меню операції «Видалити» блоку «Комірки».

Є й інший спосіб виділення порожніх полів. Щоб скористатися ним, відкрийте вкладку «Головна» та знайдіть блок «Редагування». У меню «Знайти та виділити» виконайте команду «Перейти». У вікні параметрів переходу позначте таблицю, з якою працюєте, і натисніть кнопку «Виділити» внизу форми.

Недолік методу виділення осередків

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

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

Під час виконання завдань у додатку Excel може виникнути потреба видалити порожні комірки. Вони часто є непотрібним елементом і лише збільшують загальний масив даних, ніж заплутують користувача. Визначимо способи, якими можна швидко видалити порожні елементи.

Насамперед, потрібно розібратися, а чи справді можна у конкретному масиві чи таблиці видаляти порожні осередки? Зазначена процедура призводить до усунення даних, а це далеко не завжди є допустимим. По суті, елементи можна видаляти лише у двох випадках:

Якщо рядок (стовпець) повністю порожній (у таблицях);
Якщо осередки у рядку та стовпці логічно не пов'язані один з одним (у масивах).

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

Спосіб 1: виділення груп осередків

Найпростіший спосіб видалити порожні елементи – це скористатися інструментом виділення груп осередків.

Після цих маніпуляцій всі порожні елементи всередині вказаного діапазону будуть видалені.

Спосіб 2: умовне форматування та фільтрація

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

Спосіб 3: застосування складної формули

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

Насамперед, нам потрібно буде дати ім'я діапазону, який піддається трансформації. Виділяємо область, клацніть правою кнопкою мишки. У меню, що активувалося, вибираємо пункт "Присвоїти ім'я ...".

Відкривається вікно присвоєння найменування. В полі «Ім'я»даємо будь-яку зручну назву. Головна умова - в ньому не повинно бути прогалин. Для прикладу ми надали діапазону найменування «З_порожніми». Більше ніяких змін у вікні вносити не потрібно. Тиснемо на кнопку "OK".

Виділяємо в будь-якому місці на аркуші такий самий за розмірами діапазон порожніх осередків. Аналогічно натискаємо правою кнопкою миші і, викликавши контекстне меню, переходимо по пункту "Присвоїти ім'я ...".

У вікні, як і в попередній раз, присвоюємо будь-яке найменування даної області. Ми вирішили дати їй назву «Без_порожніх».

Виділяємо подвійним клацанням лівої кнопки мишки перший осередок умовного діапазону «Без_порожніх»(у вас він може назватись і по-іншому). Вставляємо до неї формулу наступного типу:
ЯКЩО(РЯДОК()-РЯДОК(Без_порожніх)+1>РЯДОК(З_порожніми)-ВРАХУВАТИ ПУСТОТИ(З_порожніми);""; З_порожніми)));РЯДКУ()-РЯДОК(Без_порожніх)+1);Стовпчик(З_порожніми);4)))

Оскільки це формула масиву, то виведення розрахунку на екран потрібно натиснути комбінацію клавіш Ctrl+Shift+Enterзамість звичайного натискання кнопки Enter.

Але, як бачимо, заповнився лише один осередок. Для того, щоб заповнилися й інші, потрібно скопіювати формулу на частину діапазону, що залишилася. Це можна зробити за допомогою маркера заповнення. Встановлюємо курсор у нижній правий кут комірки, що містить комплексну функцію. Курсор повинен перетворитися на хрестик. Затискаємо ліву кнопку миші і тягнемо його вниз до кінця діапазону «Без_порожніх».

Як бачимо, після цієї дії ми маємо діапазон, у якому поспіль розташовані заповнені осередки. Але виконувати різні дії з цими даними ми зможемо, оскільки вони пов'язані формулою масиву. Виділяємо весь діапазон «Без_порожніх». Тиснемо на кнопку «Копіювати», яка розміщена у вкладці «Головна»у блоці інструментів "Буфер обміну".

Після цього виділяємо первісний масив даних. Клацаємо правою кнопкою миші. У списку, що відкрився в групі "Параметри вставки"тиснемо на піктограму «Значення».

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

Існує кілька способів видалення порожніх елементів у Microsoft Excel. Варіант із виділенням груп осередків найбільш простий і швидкий. Але ситуації бувають різні. Тому, як додаткові способи, можна використовувати варіанти з фільтруванням та застосуванням комплексної формули.