змест
Пастаноўка задачы
У нас ёсць дыяпазон вочак з дадзенымі, які змяшчае пустыя вочкі:
Задача складаецца ў тым, каб выдаліць пустыя вочкі, пакінуўшы толькі вочкі з інфармацыяй.
Спосаб 1. Грубы і хуткі
- Выбар зыходнага дыяпазону
- Націсніце клавішу F5, наступная кнопка Вылучыце (Спецыяльны). У якое адкрылася акне абярыце Пустыя вочкі(Нарыхтоўкі) і націсніце OK.
Вылучаюцца ўсе пустыя ячэйкі ў дыяпазоне.
- Даем у меню каманду на выдаленне вылучаных вочак: правая кнопка мышы- Выдаліць ячэйкі (Выдаліць вочкі) са зрухам уверх.
Спосаб 2: формула масіва
Для спрашчэння давайце назавем нашы працоўныя дыяпазоны з дапамогай Менеджэр імёнаў (Менеджэр імёнаў) таб формула (Формулы) або, у Excel 2003 і старэй, меню Уставіць – Імя – Прысвоіць (Insert - Name - Define)
Назавіце дыяпазон 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)))
У англійскай версіі гэта будзе:
=КАЛІ(РАДОК()-РАДОК(НямаПуста)+1>РАДОК(Пусто)-ЛІКАЦЬПУСТЫ(Пусто),””,УСКОСНЫ(АДРАС(МАЛЫ((КАЛІ(Пусто<>“”,РАКОК(Пусто),РАДОК() +РАДКІ(ЁсцьПусты))),РАДОК()-РАДОК(НямаПуста)+1),КАЛОНЦ(ЁсцьПусты),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. Каб выкарыстоўваць гэтую функцыю ў нашым прыкладзе:
- Выберыце дастатковы дыяпазон пустых вочак, напрыклад F3:F10.
- Перайдзіце ў меню Устаўка – функцыя (Уставіць — функцыя)або націсніце на кнопку Функцыя ўстаўкі (Уставіць функцыю) таб формула (Формулы) у новых версіях Excel. У катэгорыі Карыстальнік (Вызначаецца карыстальнікам) выбраць нашу функцыю NoBlanks.
- Укажыце зыходны дыяпазон з пустэчамі (B3:B10) у якасці аргумента функцыі і націсніце Ctrl + Shift + Enterкаб увесці функцыю ў выглядзе формулы масіва.
:
- Выдаленне ўсіх пустых радкоў у табліцы адразу з дапамогай простага макраса
- Выдаленне ўсіх пустых радкоў на аркушы адразу з дапамогай надбудовы PLEX
- Хуткае запаўненне ўсіх пустых вочак
- Што такое макрасы, куды ўставіць код макраса ў VBA