содржина
Во многу случаи, празните редови и колони можат да бидат мака во табелите. Стандардните функции за сортирање, филтрирање, сумирање, креирање на стожерни табели итн. ги перцепираат празните редови и колони како прекин на табелите, без прибирање на податоците лоцирани понатаму зад нив. Ако има многу такви празнини, тогаш рачното отстранување на нив може да биде многу скапо и нема да работи да се отстрани одеднаш „на големо“ користејќи филтрирање, бидејќи филтерот исто така ќе се „сопнува“ на паузи.
Ајде да погледнеме неколку начини за решавање на овој проблем.
Метод 1. Пребарајте празни ќелии
Ова можеби не е најзгодно, но дефинитивно најлесниот начин вреди да се спомене.
Да претпоставиме дека имаме работа со таква табела која содржи многу празни редови и колони внатре (нагласени за јасност):
Да претпоставиме дека сме сигурни дека првата колона од нашата табела (колона Б) секогаш го содржи името на градот. Тогаш празните ќелии во оваа колона ќе бидат знак за непотребни празни редови. За брзо да ги отстраните сите, направете го следново:
- Изберете опсег со градови (B2:B26)
- Притиснете го копчето F5 и потоа притиснете Истакнете (Оди во Специјални) или изберете на јазичето Дома — Најдете и изберете — Изберете група на ќелии (Дома - Најдете и изберете - Одете во специјално).
- Во прозорецот што се отвора, изберете ја опцијата Празни ќелии (Ќорци) и притиснете OK – треба да се изберат сите празни ќелии во првата колона од нашата табела.
- Сега изберете на јазичето Почетна Команда Избриши – Бришење редови од листот (Избриши - Избриши редови) или притиснете ја кратенката на тастатурата Ctrl+минус – и нашата задача е решена.
Се разбира, можете да се ослободите од празните колони на ист начин, користејќи го заглавието на табелата како основа.
Метод 2: Пребарајте празни редови
Како што можеби веќе сте сфатиле, претходниот метод ќе работи само ако нашите податоци нужно содржат целосно пополнети редови и колони, на кои може да се закачат кога се бараат празни ќелии. Но, што ако нема таква доверба, а податоците може да содржат и празни ќелии?
Погледнете ја следната табела, на пример, токму за таков случај:
Овде пристапот ќе биде малку посложен:
- Внесете ја функцијата во ќелијата А2 COUNT (COUNTA), кој ќе го пресмета бројот на пополнети ќелии во редот десно и ќе ја ископира оваа формула на целата табела:
- Изберете ќелија A2 и вклучете го филтерот со командата Податоци - Филтер (Податоци - Филтер) или кратенка на тастатура Ctrl+Префрлат+L.
- Да ги филтрираме нулите по пресметаната колона, односно сите редови каде што нема податоци.
- Останува да ги изберете филтрираните линии и да ги избришете со командата Дома — Избриши -' Избришете редови од листот (Дома - Избриши - Избриши редови) или кратенка на тастатура Ctrl+минус.
- Го исклучуваме филтерот и ги добиваме нашите податоци без празни линии.
За жал, овој трик повеќе не може да се прави со колони - Excel сè уште не научил како да филтрира по колони.
Метод 3. Макро за отстранување на сите празни редови и колони на лист
Можете исто така да користите едноставно макро за да ја автоматизирате оваа задача. Притиснете ја кратенката на тастатурата Alt+F11 или изберете од табот инвеститорот — Visual Basic (Програмер - уредник на Visual Basic). Ако јазичиња инвеститорот не е видлив, можете да го овозможите преку Датотека – Опции – Поставување лента (Датотека - Опции - Приспособете ја лентата).
Во прозорецот за уредувач на Visual Basic што се отвора, изберете ја командата од менито Вметни – Модул и во празниот модул што се појавува, копирајте ги и залепете ги следните редови:
Под DeleteEmpty() Dim r додека, rng како опсег 'удаляем пустые строки За r = 1 До ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Редови(r)) = 0 Потоа ако rng е ништо, тогаш Постави rng = Редови(r) Друго Постави rng = Унија(rng, редови(r)) Крај ако следно r Ако не rng не е ништо, тогаш rng. Избриши „удаляем пустые столбцы Постави rng = Ништо За r = 1 до ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Тогаш ако rng не е ништо, тогаш Постави rng = колони (r) Друго Постави rng = унија(rng, колони( р)) Крај ако следно r Ако не rng не е ништо, тогаш rng.Избриши крај на под
Затворете го уредникот и вратете се во Excel.
Сега погоди комбинација Alt+F8 или копче Макроа табот инвеститорот. Прозорецот што се отвора ќе ги наведе сите макроа што моментално ви се достапни за извршување, вклучително и макрото што штотуку го создадовте. Избриши празно. Изберете го и кликнете на копчето Испратена (трчај) – сите празни редови и колони на листот ќе бидат веднаш избришани.
Метод 4: Побарување за напојување
Друг начин да го решиме нашиот проблем и многу вообичаено сценарио е да ги отстраниме празните редови и колони во Power Query.
Прво, ајде да ја вчитаме нашата табела во Уредувачот на Query Query. Можете да го претворите во динамичен „паметен“ со кратенката на тастатурата Ctrl+T или само да го изберете нашиот опсег на податоци и да му дадете име (на пример податоци) во лентата со формула, претворајќи се во именуван:
Сега ја користиме командата Data – Get data – From table / range (Data – Get Data – From table / range) и вчитуваме сè во Power Query:
Тогаш сè е едноставно:
- Ги бришеме празните линии со наредбата Home – Reduce lines – Delete lines – Delete lines (Дома – Отстрани редови – Отстрани празни редови).
- Десен-клик на насловот на првата колона Град и изберете ја командата Unpivot Other Columns од контекстното мени. Нашата маса ќе биде, како што технички правилно се нарекува, нормализиран – претворена во три колони: град, месец и вредност од пресекот на градот и месец од оригиналната табела. Особеноста на оваа операција во Power Query е тоа што ги прескокнува празните ќелии во изворните податоци, што ни треба:
- Сега ја извршуваме обратната операција - ја претвораме добиената табела назад во дводимензионална за да ја вратиме во првобитната форма. Изберете ја колоната со месеци и на јазичето Трансформација изберете тим Стожерна колона (Трансформација - Стожерна колона). Во прозорецот што се отвора, како колона со вредности, изберете ја последната (Вредност), а во напредните опции - операцијата Не агрегирајте (Не собирај):
- Останува да го испратиме резултатот назад во Excel со командата Дома — Затвори и вчитај — Затвори и вчитај во… (Дома - Затвори&Вчитај - Затвори&Вчитај до…)
- Што е макро, како функционира, каде да се копира текстот на макро, како да се изврши макро?
- Пополнување на сите празни ќелии во списокот со вредностите на матичните ќелии
- Отстранување на сите празни ќелии од даден опсег
- Отстранување на сите празни редови во работниот лист со додатокот PLEX