Попълнете празни клетки до следващата стойност в Excel (3 лесни начина)

Много пъти може да срещнете набор от данни на Excel, където само една клетка е пълна с данни, а клетките под нея са празни до следващата стойност.

Както е показано по-долу:

Въпреки че този формат работи за някои хора, проблемът с този вид данни е, че не можете да ги използвате за създаване на обобщени таблици или да ги използвате в изчисления.

Лесно се поправя!

В този урок ще ви покажа какБързо попълване на клетки до следващата стойност за попълване в Excel.

Можете да направите това лесно, като използвате лесни специални диалогови техники за Go-To, VBA или Power Query.

Така че нека започваме!

Метод 1 – Попълнете с помощта на формулата Go To Special +

Да предположим, че имате набор от данни, както е показано по-долу, и искате да попълните данните в колони A и B.

В колона B целта е да попълните „принтер“ до последната празна клетка под нея, след това, когато стартира „скенер“, след това да попълните „скенер“ в клетката отдолу, докато клетката се изпразни.

Стъпките по-долу използват go to special, за да изберете всички празни клетки и да ги попълните с проста формула:

1. Изберете данните за попълване (A1:D21 в нашия пример)
2. Отидете в раздела "Начало".

3. В групата за редактиране щракнете върху иконата Намиране и избор (това ще разкрие повече опции в падащото меню)
4. Кликнете върху опцията „Отиди“.

5. В диалоговия прозорец Отиди на, който се отваря, щракнете върху бутона Специални.Това ще отвори диалоговия прозорец „Отиди на специален“.

6. В диалоговия прозорец Go to Special щракнете върху Blank

7. Щракнете върху OK

Горните стъпки ще изберат всички празни клетки в набора от данни (показан по-долу).

Сред избраните празни клетки ще забележите, че една клетка е по-ярка от останалата част от селекцията.Тази клетка е активната клетка, в която искаме да въведем формулата.

Не се притеснявайте за позицията на клетката в селекцията, тъй като нашият метод ще работи във всеки случай.

Ето стъпките за попълване на данните в избраните празни клетки:

1. Щракнете върху знака за равенство (=) на клавиатурата.Това ще вмъкне знак за равенство в активната клетка
2. Натиснете клавиша със стрелка нагоре.Това ще вмъкне препратката към клетката над активната клетка.Например в нашия случай B3 е активната клетка и когато направим тези две стъпки, тя въвежда =B2 в клетката
3. Задръжте клавиша Control и натиснете Enter

Горните стъпки автоматично ще вмъкнат всички празни клетки с горните стойности.

Въпреки че това може да изглежда като твърде много стъпки, след като овладеете нещата, ще можете бързо да попълвате данни в Excel за секунди.

Сега има още две неща, които трябва да знаете, когато използвате този метод.

Преобразуване на формула в стойност

Първото е да се уверите, че конвертирате формулите в стойности (така че да имате статични стойности и да не се прецакате, когато промените данните в бъдеще).

Промяна на формата на датата

Ако използвате дати във вашите данни (както направих в моите примерни данни), ще забележите, че попълнените стойности в колоната с дати са числа вместо дати.

Ако изходът в колоната за дата е в желания формат за дата, значи сте добре и не е нужно да правите нищо друго.

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

Ето стъпките, за да направите това:

1. Изберете колони, съдържащи дати
2. Щракнете върху раздела Начало
3. В групата Числа щракнете върху падащото меню Формат и изберете формат на датата.

Ако трябва да правите тази подложка от време на време, препоръчвам тази Go-To специална и формулна техника.

Въпреки че има няколко стъпки, той е прост и ви дава резултати в набора от данни.

Но ако ви се налага да правите това често, предлагам ви да разгледате VBA и метода на Power Query, описан по-нататък

Метод 2 - Попълнете с прост VBA код

Можете да използвате много прост VBA макро код за бързо попълване на клетки в Excel до последната празна стойност.

Трябва само да добавите VBA код към файл веднъж, след което лесно можете да използвате повторно този код няколко пъти в една и съща работна книга или дори в множество работни книги на вашата система.

Ето кода на VBA, който ще премине през всяка клетка в селекцията и ще попълни всички празни клетки:

Sub FillDown() За всяка клетка в селекцията If cell = "" Then cell.FillDown End If Next End Sub

Кодът по-горе използва For цикъл за итерация през всяка клетка в селекцията.

В цикъла For използвам условие If-Then, за да проверя дали клетката е празна.

Ако клетката е празна, тя се запълва със стойността от клетката по-горе, ако не, for цикълът игнорира клетката и преминава към следващата клетка.

След като вече имате VBA кода, нека ви покажа къде да поставите този код в Excel:

1. Изберете данните за попълване
2. Щракнете върху раздела за програмисти

3. Щракнете върху иконата на Visual Basic (или можете да използвате клавишната комбинация ALT+F11).Това ще отвори редактора на Visual Basic в Excel

4. В редактора на Visual Basic отляво ще имате Project Explorer.Ако не го виждате, щракнете върху опцията Изглед в менюто, след което щракнете върху Project Explorer

5. Ако имате отворени няколко работни книги на Excel, Project Explorer показва всички имена на работни книги.Намерете името на работната книга, където имате данните.
6. Щракнете с десния бутон върху който и да е обект в работната книга, отидете на Вмъкване и щракнете върху Модули.Това ще вмъкне нов модул за тази работна книга

7. Щракнете двукратно върху „Модула“, който току-що сте вмъкнали в стъпките по-горе.Той ще отвори прозореца с код за този модул

8. Копирайте и поставете VBA кода в прозореца на кода
9. Поставете курсора навсякъде в кода и стартирайте макро кода, като щракнете върху зеления бутон в лентата с инструменти или използвате клавишната комбинация F5

Горните стъпки ще изпълнят VBA кода и вашите данни ще бъдат попълнени.

Ако искате да използвате този VBA код отново по-късно, ще трябва да запишете този файл като работна книга на Excel с активирани макроси (разширение .XLSM)

Друго нещо, което можете да направите, е да добавите този макрос към лентата с инструменти за бърз достъп, която е винаги видима и можете да получите достъп до този макрос само с едно щракване (в работна книга с код в бекенда).

Така че следващия път, когато имате данни, които трябва да бъдат попълнени, можете просто да направите избор и да щракнете върху бутона за макроси в лентата с инструменти за бърз достъп.

Можете също да добавите този макрос към личната си работна книга с макроси и след това да го използвате във всяка работна книга на вашата система.

Щракнете тук, за да прочетете повече за вашата лична работна книга с макроси и как да добавите код към нея.

Метод 3 - Попълнете с помощта на Power Query

Запитване за захранванеИма вградена функционалност, която ви позволява да го попълните с едно кликване.

Препоръчва се, когато все още използвате Power Query, за да трансформирате данни или да комбинирате данни от множество работни листове или множество работни книги.

Като част от съществуващ работен процес можете бързо да попълвате празни клетки с помощта на опцията за попълване в Power Query.

За да използвате Power Query, се препоръчва вашите данни да са във формат на таблица на Excel.Ако не можете да конвертирате данните в таблица на Excel, трябва да създадете именуван диапазон за данните и след това да използвате този именуван диапазон в Power Query.

По-долу имам набор от данни, който е преобразуван в таблица на Excel.Можете да направите това, като изберете набора от данни, отидете в раздела Вмъкване и щракнете върху иконата Таблица.

Ето стъпките за използване на Power Query за попълване на данните до следващата стойност:

1. Изберете която и да е клетка в набора от данни
2. Щракнете върху раздела Данни

3. В групата Получаване и преобразуване на данни щракнете върху От работен лист.Това ще отвори редактора на Power Query.Обърнете внимание, че празните клетки ще показват стойността "null" в Power Query

4. В редактора на Power Query изберете колоната, която искате да попълните.За да направите това, задръжте клавиша Control и щракнете върху заглавката на колоната, която искате да изберете.В нашия пример това ще бъдат колоните за дата и продукт.
5. Щракнете с десния бутон върху всяко избрано заглавие

6. Отидете до опцията Запълване и щракнете върху Надолу.Това ще попълни всички празни клетки с данни

7. Щракнете върху раздела Файл и след това щракнете върху Затвори и зареди.Това ще вмъкне нов работен лист с таблицата с резултатите в работната книга

Въпреки че този подход може да звучи като пресилено, едно от големите предимства на използването на Power Query е, че ви позволява бързо да актуализирате получените данни, когато оригиналните данни се променят.

Например, ако добавите повече записи към оригиналните данни, не е необходимо да правите всичко по-горе отново.Можете просто да щракнете с десния бутон върху получената таблица и да щракнете върху обновяване.

Въпреки че методът на Power Query работи добре, най-добре е да го използвате, когато вече използвате Power Query във вашия работен процес.

Ако имате само един набор от данни с празни клетки за попълване, по-удобно е да използвате специалния метод Go To или метода VBA (описан по-горе).

Така че можете да използвате тези три прости метода заПопълнете празните клетки до следващата стойност в Excel.

Надявам се, че сте намерили този урок за полезен.