Днес се натъкнах за трети или четвърти път само този месец на въпроса как може бързо да се запълнят празни клетки в Excel, както е показано на картинката по-долу.
Разполагаме с таблицата вляво, която показва някакви числа групирани по марки мобилни устройства и градове. Проблемът е, че градовете са изписани само на най-горния ред на групата, а за някакви цели, без значение какви, на нас ни тябват запълнени на всеки ред, както е в дясната таблица.
Това наистина е доста често срещан проблем и затова реших да споделя няколко решения за него.
Оправете си пивот таблицата
Не е случайно, че таблиците по-горе са форматирани като пивот таблици. Лявата всъщност е копирана от действителна пивот таблица.
В моята практика това е най-често срещаният случай, в който се получава този казус с празни клетки в Excel. Имате нужда от някакви данни, колегата Хикс разполага с тях и го молите да ви ги даде, за да си свършите работата. Той любезно ви ги предоставя, но в този неудобен вид. Възможно е и вие сами да си създавате такива таблици, които после да ви причиняват това главоболие.
Затова първият ми съвет е – пипнете настройките на пивот таблицата по следния начин:
- Десен клик някъде в колоната „Град“ и избираме Field Settings
- Отиваме на Layout & Print и слагаме отметка на Repeat item labels
- Кликаме ОК и таблицата вече е във вида, в който ни е нужна. Въпрос на вкус е дали да оставим градовете болдирани или не
Предимството на този прийом е, че решаваме проблема еднократно и после само си консумираме данните без да губим веме в излишни операции по запълване на празни клетки в Excel.
Лично аз бих отишъл една стъпка по-далеч като се запитам за какво точно използвам данните от подредената вече таблица. Ако целта е да правя някакви обобщения със SUMIF, COUNTIF, които после да използвам в някакви отчети или графики, тогава по-удачно е да го направя в самата пивот таблица. Например така:
Повече за пивот таблиците можете да научите тук и тук
Но да не се отклоняваме от темата, която е за запълване празни клетки в Excel. Често не разполагаме с пивот таблицата, защото колегата е много серт и не желае да му се бъркаме във файловете или пък сме свалили данните от интернет като тази статистика за регистрация на нови автомобили в Европа. Тогава можем да прибегнем до
Go to Special
Това всъщност е една направо древна техника за запълване на празни клетки в Excel, известна и описана във форуми и блогове още в края на миналия век. Може би затова убягва на потребителите днес.
Ето в какво се състои:
- Селектираме областта с проблемните клетки
- Даваме F5 или CTRL+G, за да извикаме менюто Go To
- Избираме Special (ограденото в червено по-горе)
- И след това Blanks
- Когато дадем ОК, Ексел селектира само празните клетки в областта. Тогава без да правим нищо друго натискаме = (равно) и със стрелка нагоре избираме клетката над първата празна клетка. В случая G3
- Последната стъпка е много важна. Трябва да ударим едновременно CTRL+ENTER. И резултатът ще е точно, това което искаме:
Между другото Go To менюто предоставя и други интересни и полезни възможности за обработка на данни. Можете да азберете повече за тях от тази статия.
Този метод е прост за приложение и не губи много време. Стъпки от 2 до 4 се осъществяват за по-малко от две секунди с тази последователност от клавишни комбинации:
CTRL+G > ALT+S > K > ENTER
Но защо трябва да работи човекът, когато това може да прави машината? Особено, ако се налага операцията да се повтори множество пъти. Да не забравяме, че всяка ръчна операция крие риск от грешки.
Ето защо за многократни и повтарящи се действия препоръчвам третия метод за запълване на празни клетки в Excel
Запълване на празни клетки в Excel с Power Query
При този метод зареждаме първоначалната таблица в Power Query. В случая това става като стъпваме в произволна клетка от таблицата и изберем Data > Get & Transform > From Table (така е в Ексел 2019, за други версии е малко по-различно). Разбира се може да са зареди таблицата и от файл.
Когато сме в менюто на Power Query, маркираме въпросната колона, десен клик и избираме Fill > Down
И това е – нашата таблица е във вида, който ни е необходим. Остава само да я заредим в Ексел чрез бутона Close & Load
Следващия път, просто рефрешваме заявката и сме готови. Няма нужда да помним ама CTRL+G ли беше, а накрая само с ENTER или с ENTER и какво друго и т.н.
Разбира се за еднократно действие на малък обем данни едва ли си струва да прибягваме до Power Query.
Това бяха трите метода за запълване празни клетки в Excel, всеки със своите предимства и недостатъци. Решението кой от тях да използвате оставям на вас. Във всички случаи помнете, че е по-добре досадните технически операции да ги прави компютърът. Той е по-бърз от вас и така ви освобождава време за по-творчески и интересни неща. Например да създадете един Word cloud в Excel, с който да шашнете колегите си на следващата презентация