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

Формулиране на проблема

Имаме набор от клетки с данни, които съдържат празни клетки:

Задачата е да премахнете празните клетки, оставяйки само клетки с информация.

Метод 1. Груб и бърз

1. Избор на оригиналния диапазон
2. Натиснете бутона F5, следващ бутон Маркирайте (Специален). В прозореца, който се отваря, изберете Празни клетки(Празни) и кликнете OK.

   

   Всички празни клетки в диапазона са избрани.

3. Даваме команда в менюто за изтриване на избрани клетки: щракнете с десния бутон- Изтриване на клетки (Изтриване на клетки) с изместване нагоре.

Метод 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() + ROWS(Има празни))); LINE()-ROW(Няма празни)+1); COLUMN(Има празни); 4)))

В английската версия ще бъде:

=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Empty),””,INDIRECT(ADDRESS(SMALL((IF(Empty<>“”,ROW(Empty),ROW() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),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. За да използвате тази функция в нашия пример:

1. Изберете достатъчен диапазон от празни клетки, например F3:F10.
2. Отидете в менюто Вмъкване – функция (Вмъкване — функция)или щракнете върху бутона Вмъкване на функция (Вмъкване на функция) етикет формула (Формули) в по-новите версии на Excel. В категория Потребителски дефиниран (дефиниран от потребителя) изберете нашата функция Няма празни места.
3. Посочете изходния диапазон с празнини (B3:B10) като аргумент на функцията и натиснете Ctrl + Shift + Enterза да въведете функцията като формула за масив.

:

• Изтриване на всички празни редове в таблица наведнъж с прост макрос
• Премахване на всички празни редове в работен лист наведнъж с помощта на добавката PLEX
• Бързо попълване на всички празни клетки
• Какво представляват макросите, къде да вмъкнете макро код във VBA