змест
У многіх выпадках пустыя радкі і слупкі могуць быць праблемай у табліцах. Стандартныя функцыі сартавання, фільтрацыі, падвядзення вынікаў, стварэння зводных табліц і г. д. ўспрымаюць пустыя радкі і слупкі як разрыў табліцы, не выбіраючы дадзеныя, размешчаныя далей за імі. Калі такіх прабелаў шмат, то ручное іх выдаленне можа абысціся вельмі дорага, а выдаліць усё адразу «масам» з дапамогай фільтрацыі не атрымаецца, таму што фільтр таксама будзе «затыкацца» на абрывах.
Давайце разгледзім некалькі спосабаў вырашэння гэтай праблемы.
Спосаб 1. Пошук пустых вочак
Магчыма, гэта не самы зручны, але, безумоўна, самы просты спосаб варты згадкі.
Выкажам здагадку, што мы маем справу з такой табліцай, якая змяшчае мноства пустых радкоў і слупкоў ўнутры (выдзелена для яснасці):
Дапусцім, мы ўпэўненыя, што першы слупок нашай табліцы (слупок B) заўсёды змяшчае назву горада. Тады пустыя вочкі ў гэтым слупку будуць прыкметай непатрэбных пустых радкоў. Каб хутка выдаліць іх усе, зрабіце наступнае:
- Выберыце дыяпазон з гарадамі (B2:B26)
- Націсніце клавішу F5 а затым націсніце Вылучыце (Перайсці да спецыяльных) або абярыце на ўкладцы Галоўная — Знайсці і выбраць — Выбраць групу вочак (Галоўная — Знайсці&Выбраць — Перайсці да спецыяльных).
- У якое адкрылася акне абярыце опцыю Пустыя вочкі (Нарыхтоўкі) і націсніце OK – усе пустыя клеткі ў першым слупку нашай табліцы павінны быць вылучаны.
- Цяпер абярыце на ўкладцы Галоўная Каманда Выдаліць – Выдаліць радкі з ліста (Выдаліць — Выдаліць радкі) або націсніце спалучэнне клавіш Ctrl+мінус – і наша задача вырашана.
Безумоўна, сапраўды гэтак жа можна пазбавіцца ад пустых слупкоў, узяўшы за аснову загаловак табліцы.
Спосаб 2: пошук пустых радкоў
Як вы ўжо маглі зразумець, папярэдні спосаб будзе працаваць толькі ў тым выпадку, калі нашы дадзеныя абавязкова ўтрымліваюць цалкам запоўненыя радкі і слупкі, якія можна зачапіць пры пошуку пустых вочак. Але што рабіць, калі такой упэўненасці няма, а дадзеныя могуць утрымліваць і пустыя вочкі?
Зірніце на наступную табліцу, напрыклад, для такога выпадку:
Тут падыход будзе крыху больш складаным:
- Увядзіце ў вочка A2 функцыю COUNT (COUNTA), які вылічыць колькасць запоўненых вочак у радку справа і скапіюе гэтую формулу ва ўсю табліцу:
- Выберыце вочка A2 і ўключыце фільтр з дапамогай каманды Дадзеныя – Фільтр (Даныя — Фільтр) або спалучэнне клавіш Ctrl+Зрух+L.
- Адфільтруем нулі па вылічальнай калонцы, гэта значыць усе радкі, дзе няма дадзеных.
- Засталося выбраць адфільтраваныя радкі і выдаліць іх камандай Галоўная — Выдаліць -' Выдаліць радкі з ліста (Дадому — Выдаліць — Выдаліць радкі) або спалучэнне клавіш Ctrl+мінус.
- Мы адключаем фільтр і атрымліваем нашы дадзеныя без пустых радкоў.
На жаль, гэты прыём больш нельга зрабіць са слупкамі - Excel яшчэ не навучыўся фільтраваць па слупках.
Спосаб 3. Макрас для выдалення ўсіх пустых радкоў і слупкоў на аркушы
Вы таксама можаце выкарыстоўваць просты макрас для аўтаматызацыі гэтай задачы. Націсніце спалучэнне клавіш Alt+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 Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To 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.
Цяпер націсніце камбінацыю Alt+F8 альбо кнопка Макрас таб распрацоўшчык. У акне, якое адкрыецца, будуць пералічаны ўсе макрасы, даступныя для запуску, у тым ліку толькі што створаны вамі макрас. ВыдаліцьПусто. Выберыце яго і націсніце кнопку прагон (бег) – усе пустыя радкі і слупкі на аркушы будуць імгненна выдалены.
Спосаб 4: Power Query
Іншы спосаб вырашыць нашу праблему і вельмі распаўсюджаны сцэнар - выдаліць пустыя радкі і слупкі ў Power Query.
Спачатку давайце загрузім нашу табліцу ў рэдактар запытаў Power Query. Вы можаце пераўтварыць яго ў дынамічны «разумны» з дапамогай спалучэння клавіш Ctrl+T або проста выбраць наш дыяпазон даных і даць яму імя (напрыклад Дата) у радку формул, пераўтвараючы ў імя:
Цяпер выкарыстоўваем каманду Data – Get data – From table / range (Дадзеныя – Атрымаць дадзеныя – З табліцы / дыяпазону) і загружаем усё ў Power Query:
Далей усё проста:
- Пустыя радкі выдаляем камандай Home – Reduce lines – Delete lines – Delete пустыя радкі (Home – Remove Rows – Remove пустыя радкі).
- Пстрыкніце правай кнопкай мышы загаловак першага слупка «Горад» і абярыце ў кантэкстным меню каманду «Адмяніць паварот іншых слупкоў». Наш стол будзе, як гэта тэхнічна правільна называецца, нармалізаваны – ператвораны ў тры слупкі: горад, месяц і значэнне ад скрыжавання горада і месяца з зыходнай табліцы. Асаблівасць гэтай аперацыі ў Power Query заключаецца ў тым, што яна прапускае пустыя вочкі ў зыходных дадзеных, што нам і трэба:
- Цяпер выконваем зваротную аперацыю - ператвараем атрыманую табліцу ў двухмерную, каб вярнуць ёй першапачатковы выгляд. Выберыце слупок з месяцамі і на ўкладцы Пераўтварэнне выбраць каманду Зводны слупок (Пераўтварэнне — зводны слупок). У якое адкрылася акне ў якасці слупка значэнняў выберыце апошняе (Value), а ў дадатковых параметрах – аперацыю Не аб'ядноўвайце (Не аб'ядноўваць):
- Засталося камандай загрузіць вынік назад у Excel Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у…)
- Што такое макрас, як ён працуе, куды скапіяваць тэкст макраса, як запусціць макрас?
- Запаўненне ўсіх пустых вочак у спісе значэннямі бацькоўскіх вочак
- Выдаленне ўсіх пустых вочак з зададзенага дыяпазону
- Выдаленне ўсіх пустых радкоў на аркушы з дадаткам PLEX