Гледайте видео - попълнете в Excel
Много пъти може да срещнете набор от данни, където само една клетка е запълнена с данни, а клетките под нея са празни до следващата стойност.
Нещо, както е показано по -долу:
Въпреки че този формат работи за някои хора, проблемът с този вид данни е, че не можете да го използвате за създаване на обобщени таблици или да го използвате при изчисления.
И това има лесно решение!
В този урок ще ви покажа как бързо попълнете клетките в Excel до следващата попълнена стойност.
Можете лесно да направите това, като използвате проста техника за диалогов прозорец Go-To, VBA или Power Query.
Така че нека започнем!
Метод 1 - Попълнете, като използвате Go to Special + Formula
Да предположим, че имате набор от данни, както е показано по -долу и искате да попълните данни в колони А и колона Б.
В колона Б целта е да попълните „Принтер“ до последната празна клетка под нея, а след това, когато „Скенер“ стартира, след това попълнете „Скенер“ в клетките по -долу, докато клетките са празни.
Под стъпките, които да използвате, отидете на специални, за да изберете всички празни клетки и да ги попълните, като използвате проста формула:
- Изберете данните, които искате да попълните (A1: D21 в нашия пример)
- Отидете в раздела „Начало“
- В групата Редактиране кликнете върху иконата „Намери и изберете“ (това ще покаже още някои опции в падащото меню)
- Кликнете върху опцията „Go To“
- В диалоговия прозорец Go-To, който се отваря, щракнете върху бутона Special. Това ще отвори диалоговия прозорец „Отиди на специално“
- В диалоговия прозорец Отиди на Специално щракнете върху Празни
- Щракнете върху OK
Горните стъпки биха избрали всички празни клетки в набора от данни (както е показано по -долу).
В празните клетки, които са избрани, ще забележите, че една клетка е по -лека от останалата част от селекцията. Тази клетка е активната клетка, в която ще въведем формула.
Не се притеснявайте къде е клетката в селекцията, тъй като нашият метод би работил във всеки случай.
Следват стъпките за попълване на данните в избраните празни клетки:
- Натиснете клавиша равно на (=) на клавиатурата. Това ще вмъкне знак за равенство в активната клетка
- Натиснете клавиша със стрелка нагоре. Това ще вмъкне референтната клетка на клетката над активната клетка. Например в нашия случай B3 е активната клетка и когато направим тези две стъпки, тя влиза = B2 в клетката
- Задръжте клавиша Control и натиснете клавиша Enter
Горните стъпки автоматично биха вмъкнали всички празни клетки със стойността над тях.
Въпреки че това може да изглежда като прекалено много стъпки, след като разберете този метод, ще можете бързо да попълните данни в Excel в рамките на няколко секунди.
Сега има още две неща, за които трябва да се погрижите, когато използвате този метод.
Преобразувайте формулите в стойности
Първият е да се уверите, че преобразувате формулите в стойности (така че да имате статични стойности и нещата да не се объркат, в случай че промените данните в бъдеще).
По -долу е видео, което ще ви покаже как бързо да конвертирате формули в стойности в Excel.
Променете формата на датата
В случай, че използвате дати във вашите данни (както аз съм в примерните данни), ще забележите, че попълнените стойности в колоната за дата са числа, а не дати.
Ако изходът в колоната за дата е в желания формат за дата, вие сте добре и не е нужно да правите нищо друго.
Но ако те не са в правилния формат на датата, трябва да направите малка корекция. Докато имате правилните стойности, просто трябва да промените формата, така че да се показва като дата в клетката.
По -долу са описани стъпките за това:
- Изберете колоната с дати
- Щракнете върху раздела Начало
- В групата Число щракнете върху падащото меню Формат и след това изберете формата на датата.
Ако се налага да правите това попълване от време на време, препоръчвам ви да използвате тази специална техника на Go-To и формула.
Въпреки че има няколко стъпки, той е прост и ви дава резултата точно във вашия набор от данни.
Но в случай, че се налага да правите това доста често, предлагам ви да разгледате методите на VBA и Power Query, обхванати по -долу
Метод 2 - Попълнете с помощта на прост VBA код
Можете да използвате наистина прост VBA макро код за бързо попълване на клетки в Excel до последната празна стойност.
Трябва само да добавите VBA кода към файла веднъж и след това лесно можете да използвате кода многократно в една и съща работна книга или дори няколко работни книги във вашата система.
По -долу е кодът VBA, който ще премине през всяка клетка в селекцията и ще запълни всички клетки, които са празни:
„Този код на VBA е създаден от Sumit Bansal от TrumpExcel.com Sub FillDown () За всяка клетка в селекция If cell =" "Тогава клетка.
Горният код използва цикъл For, за да премине през всяка клетка в селекцията.
В рамките на цикъла For използвах условие If-Then, което проверява дали клетката е празна или не.
В случай, че клетката е празна, тя се запълва със стойността от клетката по -горе, а в случай, че не е празна, тогава цикълът for просто игнорира клетката и се премества към следващата.
Сега, когато имате кода VBA, нека ви покажа къде да поставите този код в Excel:
- Изберете данните, които искате да попълните
- Щракнете върху раздела Разработчик
- Кликнете върху иконата Visual Basic (или можете да използвате клавишната комбинация ALT + F11). Това ще отвори редактора на Visual Basic в Excel
- В редактора на Visual Basic вляво ще имате Project Explorer. Ако не го виждате, щракнете върху опцията „Преглед“ в менюто и след това върху Project Explorer
- В случай, че имате отворени няколко работни книги на Excel, Project Explorer ще ви покаже всички имена на работната книга. Намерете името на работната книга, където имате данните.
- Щракнете с десния бутон върху някой от обектите в работната книга, отидете на Вмъкване и след това щракнете върху Модул. Това ще вмъкне нов модул за тази работна книга
- Щракнете двукратно върху „Модул“, който току-що поставихте в горната стъпка. Той ще отвори прозореца с код за този модул
- Копирайте и поставете VBA кода в кодовия прозорец
- Поставете курсора навсякъде в кода и след това стартирайте кода на макроса, като щракнете върху зеления бутон в лентата с инструменти или като използвате клавишната комбинация F5
Горните стъпки ще стартират VBA кода и вашите данни ще бъдат попълнени.
Ако искате да използвате този VBA код отново в бъдеще, трябва да запишете този файл като работна книга на Excel с поддръжка на макроси (с разширение .XLSM)
Друго нещо, което можете да направите, е да добавите този макрос към лентата с инструменти за бърз достъп, която винаги е видима и ще имате достъп до този макрос с едно щракване (в работната книга, където имате кода в задната част).
Така че, следващия път, когато имате данни, където трябва да попълните, всичко, което трябва да направите, е да направите избора и след това да кликнете върху бутона за макрос в лентата с инструменти за бърз достъп.
Можете също да добавите този макрос към работната книга за лични макроси и след това да го използвате във всяка работна книга във вашата система.
Щракнете тук, за да прочетете повече за личните работни макроси и как да добавите код към тях.
Метод 3 - Попълнете с помощта на Power Query
Power Query има вградена функционалност, която ви позволява да попълвате с едно щракване.
Използването на Power Query се препоръчва, когато все пак го използвате за трансформиране на вашите данни или комбиниране на данни от множество работни листове или множество работни книги.
Като част от съществуващия ви работен поток можете да използвате опцията за попълване в Power Query, за да запълните бързо празните клетки.
За да използвате Power Query, се препоръчва данните ви да са във формат на таблица на Excel. Ако не можете да конвертирате данните си в таблица на Excel, ще трябва да създадете именен диапазон за данните и след това да използвате този именован диапазон в Power Query.
По -долу имам набор от данни, който вече съм преобразувал в таблица на Excel. Можете да направите това, като изберете набора от данни, отидете в раздела Вмъкване и след това щракнете върху иконата Таблица.
Под стъпките за използване на Power Query за попълване на данни до следващата стойност:
- Изберете всяка клетка в набора от данни
- Щракнете върху раздела Данни
- В групата Получаване и преобразуване на данни кликнете върху „От лист“. Това ще отвори редактора на Power Query. Обърнете внимание, че празните клетки ще показват стойността „null“ в Power Query
- В редактора на Power Query изберете колоните, за които искате да попълните. За да направите това, задръжте клавиша Control и след това щракнете върху заглавката на колоните, които искате да изберете. В нашия пример това ще бъдат колоните Дата и Продукт.
- Щракнете с десния бутон върху някой от избраните заглавки
- Отидете на опцията „Попълване“ и след това кликнете върху „Надолу“. Това ще запълни данните във всички празни клетки
- Щракнете върху раздела Файл и след това върху „Затваряне и зареждане“. Това ще вмъкне нов работен лист в работната книга с получената таблица
Въпреки че този метод може да звучи малко пресилено, едно голямо предимство от използването на Power Query е, че ви позволява бързо да актуализирате получените данни в случай, че първоначалните ви данни се променят.
Например, ако добавите още записи към първоначалните данни, не е нужно да правите всичко по -горе отново. Можете просто да щракнете с десния бутон върху получената таблица и след това да кликнете върху Опресняване.
Въпреки че методът Power Query работи добре, най -добре е да го използвате, когато вече използвате Power Query в работния си процес.
Ако просто имате набор от данни, където искате да попълните празните клетки, тогава използването на специалния метод Go To или метода VBA (обхванати по -горе) би било по -удобно.
Това са три прости начина, по които можете да използвате попълнете празни клетки до следващата стойност в Excel.
Надявам се, че сте намерили този урок за полезен.
