Sokszor találkozhat olyan Excel adatkészlettel, ahol csak egy cella van kitöltve adatokkal, és az alatta lévő cellák üresek a következő értékig.
Az alábbiak szerint:
Bár ez a formátum néhány ember számára működik, az ilyen típusú adatokkal az a probléma, hogy nem használható kimutatási táblák létrehozására vagy számításokhoz.
Könnyen javítható!
Ebben az oktatóanyagban megmutatom, hogyanGyorsan töltse ki a cellákat a következő kitöltési értékig az Excelben.
Ezt egyszerűen megteheti az egyszerű Go-To speciális párbeszédtechnikák, a VBA vagy a Power Query használatával.
Tehát kezdjük!
tartalom
1. módszer – Töltse ki a Go To Special + képlet segítségével
Tegyük fel, hogy van egy adatkészlete az alábbiak szerint, és az A és B oszlopban lévő adatokat szeretné kitölteni.
A B oszlopban a cél a "nyomtató" kitöltése az alatta lévő utolsó üres celláig, majd amikor elindul a "szkenner", akkor az alatta lévő cellában a "szkenner" kitöltése a cella kiürüléséig.
Az alábbi lépések segítségével lépjen a speciális elemre, hogy kijelölje az összes üres cellát, és töltse ki egy egyszerű képlettel:
- Válassza ki a kitöltendő adatokat (példánkban A1:D21)
- Lépjen a "Főoldal" fülre
- A szerkesztési csoportban kattintson a Keresés és kijelölés ikonra (ez további lehetőségeket jelenít meg a legördülő menüben)
- Kattintson a "Go" opcióra
- A megnyíló Ugrás párbeszédpanelen kattintson a Speciális gombra.Ezzel megnyílik a "Go to Special" párbeszédpanel
- Az Ugrás a speciális párbeszédpanelen kattintson az Üres gombra
- Kattintson az OK gombra
A fenti lépések kijelölik az összes üres cellát az adatkészletben (lásd lent).
A kijelölt üres cellák között észreveheti, hogy egy cella világosabb, mint a kijelölés többi része.Ez a cella az aktív cella, ahová be akarjuk vinni a képletet.
Ne törődj azzal, hogy a cella milyen pozícióban van a kijelölésben, mivel módszerünk minden esetben beválik.
Most a következő lépésekkel kell kitölteni az adatokat a kijelölt üres cellákban:
- Kattintson az egyenlőségjel (=) billentyűre a billentyűzeten.Ezzel egyenlőségjelet szúr be az aktív cellába
- Nyomja meg a felfelé mutató nyíl gombot.Ezzel beszúrja a cella hivatkozását az aktív cella fölé.Például esetünkben a B3 az aktív cella, és amikor ezt a két lépést megtesszük, akkor a =B2 értéket írja be a cellába.
- Tartsa lenyomva a Control billentyűt, és nyomja meg az Enter billentyűt
A fenti lépések automatikusan beillesztik az összes üres cellát a fenti értékekkel.
Bár ez túl sok lépésnek tűnhet, amint rájött a dologra, másodpercek alatt gyorsan kitöltheti az adatokat az Excelben.
Most még két dologra kell ügyelnie, amikor ezt a módszert használja.
Képlet konvertálása értékké
Az első az, hogy győződjön meg róla, hogy a képleteket értékekké konvertálja (így statikus értékei vannak, és ne rontsa el, ha a jövőben módosítja az adatokat).
Dátumformátum módosítása
Ha dátumokat használ az adatokban (ahogyan a példaadataimban tettem), akkor észre fogja venni, hogy a dátum oszlopban a kitöltött értékek dátumok helyett számok.
Ha a dátum oszlopban a kimenet a kívánt dátumformátumban van, akkor jó, és nem kell mást tennie.
De ha ezek a dátumok nem a megfelelő formátumban vannak, finomhangolásra van szükség.Míg megvan a helyes érték, csak módosítania kell a formátumot, hogy dátumként jelenjen meg a cellában.
Íme a lépések ehhez:
- Válassza ki a dátumokat tartalmazó oszlopokat
- Kattintson a Kezdőlap fülre
- A Számok csoportban kattintson a Formátum legördülő menüre, és válassza ki a dátumformátumot.
Ha időnként meg kell csinálni ezt a párnázást, akkor ezt a Go-To speciális és formula technikát ajánlom.
Bár néhány lépésből áll, egyszerű, és eredményeket ad az adatkészletben.
De ha ezt gyakran kell megtennie, akkor azt javaslom, hogy nézze meg a VBA-t és a következőkben ismertetett Power Query módszert
2. módszer – Töltse ki egyszerű VBA-kóddal
Egy nagyon egyszerű VBA makrókód segítségével gyorsan kitöltheti a cellákat az Excelben az utolsó üres értékig.
Csak egyszer kell VBA-kódot hozzáadnia egy fájlhoz, és ezután könnyen újrafelhasználhatja a kódot ugyanabban a munkafüzetben, vagy akár több munkafüzetben is a rendszeren.
Itt van a VBA-kód, amely végighalad a kijelölés minden egyes celláján, és kitölti az üres cellákat:
Sub FillDown() Minden cellához a kijelölésben If cell = "" Akkor cella.FillDown End If Next End Sub
A fenti kód egy For ciklust használ a kijelölés egyes celláiban való iterációhoz.
A For ciklusban egy If-Then feltételt használok annak ellenőrzésére, hogy a cella üres-e.
Ha a cella üres, akkor a fenti cellából származó értékkel töltődik fel, ha nem, a for ciklus figyelmen kívül hagyja a cellát, és a következő cellára lép.
Most, hogy megvan a VBA-kód, hadd mutassam meg, hova kell elhelyezni ezt a kódot az Excelben:
- Válassza ki a kitöltendő adatokat
- Kattintson a fejlesztő fülre
- Kattintson a Visual Basic ikonra (vagy használja az ALT+F11 billentyűkombinációt).Ezzel megnyílik a Visual Basic szerkesztő az Excelben
- A bal oldali Visual Basic szerkesztőben található a Project Explorer.Ha nem látja, kattintson a Nézet lehetőségre a menüben, majd kattintson a Project Explorer elemre
- Ha több Excel-munkafüzet van megnyitva, a Project Explorer megjeleníti az összes munkafüzet nevét.Keresse meg annak a munkafüzetnek a nevét, ahol az adatok vannak.
- Kattintson a jobb gombbal bármely objektumra a munkafüzetben, lépjen a Beszúrás menüpontra, és kattintson a Modul parancsra.Ezzel egy új modult szúr be ehhez a munkafüzethez
- Kattintson duplán a fenti lépések során beillesztett „Modulra”.Megnyitja az adott modul kódablakát
- Másolja ki és illessze be a VBA kódot a kód ablakba
- Helyezze a kurzort bárhová a kódban, és futtassa a makrókódot az eszköztár zöld gombjára kattintva vagy az F5 billentyűkombinációval
A fenti lépések futtatják a VBA-kódot, és az adatok kitöltődnek.
Ha a jövőben újra szeretné használni ezt a VBA-kódot, akkor ezt a fájlt makróképes Excel-munkafüzetként kell mentenie (.XLSM kiterjesztéssel).
Egy másik dolog, amit tehet, hogy hozzáadja ezt a makrót a Gyorselérési eszköztárhoz, amely mindig látható, és egyetlen kattintással elérheti ezt a makrót (a munkafüzetben, ahol a kód a háttérben található).
Így a következő alkalommal, amikor ki kell töltenie az adatokat, egyszerűen válasszon egyet, és kattintson a makró gombra a Gyorselérési eszköztáron.
Ezt a makrót a személyes makró-munkafüzetéhez is hozzáadhatja, majd a rendszer bármely munkafüzetében felhasználhatja.
Kattintson ide, ha többet szeretne megtudni a személyes makró-munkafüzetéről és arról, hogyan adhat hozzá kódot.
3. módszer – Töltse ki a Power Query segítségével
Teljesítmény lekérdezésBeépített funkcióval rendelkezik, amely lehetővé teszi, hogy egyetlen kattintással kitöltse.
Akkor javasolt, ha még mindig a Power Queryt használja az adatok átalakítására vagy több munkalapból vagy több munkafüzetből származó adatok kombinálására.
Egy meglévő munkafolyamat részeként gyorsan kitöltheti az üres cellákat a Power Query Kitöltés opciójával.
A Power Query használatához azt javasoljuk, hogy az adatok Excel táblázat formátumban legyenek.Ha nem tudja az adatokat Excel táblává konvertálni, létre kell hoznia egy elnevezett tartományt az adatokhoz, majd ezt a tartományt kell használnia a Power Queryben.
Lent van egy adatkészletem, amelyet Excel táblává alakítottam át.Ezt úgy teheti meg, hogy kiválasztja az adatkészletet, lépjen a Beszúrás fülre, és kattintson a Táblázat ikonra.
Az alábbiakban bemutatjuk a Power Query használatának lépéseit az adatok következő értékig történő kitöltéséhez:
- Válassza ki az adatkészlet bármelyik celláját
- Kattintson az Adatok fülre
- Az Adatok lekérése és átalakítása csoportban kattintson a Munkalapról elemre.Ezzel megnyílik a Power Query szerkesztő.Vegye figyelembe, hogy az üres cellák a „null” értéket jelenítik meg a Power Queryben
- A Power Query szerkesztőben válassza ki a kitölteni kívánt oszlopot.Ehhez tartsa lenyomva a Control billentyűt, és kattintson a kiválasztani kívánt oszlop fejlécére.Példánkban ez a dátum és a termék oszlopa lesz.
- Kattintson a jobb gombbal bármelyik kiválasztott címre
- Lépjen a Kitöltés lehetőségre, és kattintson a Le gombra.Ezzel az összes üres cellát kitölti adatokkal
- Kattintson a Fájl fülre, majd a Bezárás és Betöltés gombra.Ezzel egy új munkalapot szúr be a munkafüzetbe az eredménytáblázattal
Bár ez a megközelítés túlzásnak tűnhet, a Power Query használatának egyik nagy előnye, hogy lehetővé teszi a kapott adatok gyors frissítését, amikor az eredeti adatok megváltoznak.
Például, ha további rekordokat ad hozzá az eredeti adatokhoz, akkor nem kell újra végrehajtania a fentieket.Egyszerűen kattintson a jobb gombbal a kapott táblázatra, majd kattintson a Frissítés gombra.
Bár a Power Query módszer jól működik, a legjobb, ha akkor használja, amikor már használja a Power Queryt a munkafolyamatban.
Ha csak egy adatkészletet kell kitöltenie üres cellákkal, kényelmesebb a speciális Ugrás vagy a VBA módszer használata (lásd fent).
Tehát ezt a három egyszerű módszert használhatjaTöltse ki az üres cellákat a következő értékig az Excelben.
Remélem hasznosnak találtad ezt az oktatóanyagot.
