Премахване на празни редове и колони в данните

Празните редове и колони могат да бъдат проблем в таблиците в много случаи. Стандартните функции за сортиране, филтриране, обобщаване, създаване на обобщени таблици и т.н. възприемат празните редове и колони като прекъсване на таблица, без да прибират данните, разположени по-назад от тях. Ако има много такива пропуски, тогава ръчното им премахване може да бъде много скъпо и няма да работи да премахнете всички наведнъж „на едро“ с помощта на филтриране, тъй като филтърът също ще се „спъва“ при прекъсвания.

Нека да разгледаме няколко начина за решаване на този проблем.

Метод 1. Търсене на празни клетки

Това може да не е най-удобният, но определено най-лесният начин си заслужава да се спомене.

Да предположим, че имаме работа с такава таблица, съдържаща много празни редове и колони вътре (маркирани за яснота):

Да предположим, че сме сигурни, че първата колона на нашата таблица (колона B) винаги съдържа името на град. Тогава празните клетки в тази колона ще бъдат знак за ненужни празни редове. За да премахнете бързо всички, направете следното:

  1. Изберете диапазон с градове (B2:B26)
  2. Натиснете бутона F5 и след това натиснете Маркирайте (Отидете на Специални) или изберете в раздела Начало — Търсене и избор — Изберете група клетки (Начало — Намери и избери — Отиди на специален).
  3. В прозореца, който се отваря, изберете опцията Празни клетки (Празни) и натиснете OK – всички празни клетки в първата колона на нашата таблица трябва да бъдат избрани.
  4. Сега изберете в раздела Начало Команда Изтриване – Изтриване на редове от листа (Изтриване — Изтриване на редове) или натиснете клавишната комбинация Ctrl+минус – и нашата задача е решена.

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

Метод 2: Търсене на празни редове

Както може би вече сте разбрали, предишният метод ще работи само ако нашите данни непременно съдържат напълно попълнени редове и колони, които могат да бъдат закачени при търсене на празни клетки. Но какво ще стане, ако няма такава увереност и данните може да съдържат и празни клетки?

Разгледайте например следната таблица точно за такъв случай:

Тук подходът ще бъде малко по-сложен:

  1. Въведете в клетка A2 функцията COUNT (COUNTA), който ще изчисли броя на попълнените клетки в реда вдясно и ще копира тази формула надолу в цялата таблица:
  2. Изберете клетка A2 и включете филтъра с командата Данни – Филтър (Данни — Филтър) или клавишна комбинация Ctrl+Превключване+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 не е нищо, тогава задайте rng = редове(r) Else задайте rng = съюз(rng, редове(r)) Край, ако следващ r, ако не rng не е нищо, тогава rng.Delete 'удаляем пустые столбцы Задайте rng = нищо за r = 1 до 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 Query Editor. Можете да го конвертирате в динамичен „интелигентен“ с клавишната комбинация Ctrl+T или просто да изберете нашия диапазон от данни и да му дадете име (напр. Дата) в лентата с формули, преобразувайки в named:

Сега използваме командата Data – Get data – From table/range (Данни – Get Data – From table/range) и зареждаме всичко в Power Query:

Тогава всичко е просто:

  1. Изтриваме празни редове с командата Начало – Намаляване на редове – Изтриване на редове – Изтриване на празни редове (Начало – Премахване на редове – Премахване на празни редове).
  2. Щракнете с десния бутон върху заглавието на първата колона за град и изберете командата Unpivot Other Columns от контекстното меню. Нашата маса ще бъде, както технически правилно се нарича, нормализирано – преобразуван в три колони: град, месец и стойност от пресечната точка на града и месеца от оригиналната таблица. Особеността на тази операция в Power Query е, че пропуска празни клетки в изходните данни, което ни е необходимо:
  3. Сега извършваме обратната операция – превръщаме получената таблица обратно в двуизмерна, за да я върнем в първоначалния й вид. Изберете колоната с месеци и в таб Трансформация изберете екип Опорна колона (Трансформация — осева колона). В прозореца, който се отваря, като колона със стойности изберете последната (Value), а в разширените опции – операцията Не агрегирайте (Не събирайте):
  4. Остава да качите резултата обратно в Excel с командата Начало — Затвори и зареди — Затвори и зареди в… (Начало — Затвори&Зареди — Затвори&Зареди в...)

  • Какво е макрос, как работи, къде да копирам текста на макрос, как да стартирам макрос?
  • Попълване на всички празни клетки в списъка със стойностите на родителските клетки
  • Премахване на всички празни клетки от даден диапазон
  • Премахване на всички празни редове в работен лист с добавката PLEX

Оставете коментар