Videó megtekintése - Töltse ki az Excelben
Sokszor találkozhat olyan adathalmazzal, ahol csak egy cella van kitöltve adatokkal, és az alatta lévő cellák üresek a következő értékig.
Valami az alábbiak szerint:
Bár ez a formátum bizonyos embereknél működik, az ilyen típusú adatokkal az a probléma, hogy nem használhatja őket kimutatástáblák létrehozásához vagy számításokhoz.
És ezen van egy egyszerű megoldás!
Ebben az oktatóanyagban megmutatom, hogyan kell gyorsan töltse ki az Excel celláit a következő kitöltött értékig.
Ezt egyszerűen megteheti egy egyszerű Ugrás speciális párbeszédpanel technikával, VBA vagy Power Query használatával.
Kezdjük hát el!
1. módszer - Töltse le a Speciális + képlet használatával
Tegyük fel, hogy rendelkezik egy adathalmazzal az alábbiak szerint, és ki szeretné tölteni az A és a B oszlop adatait.
A B oszlopban a cél a „Nyomtató” kitöltése az alatta lévő utolsó üres celláig, majd amikor a „Szkenner” elindul, majd töltse ki a „Szkenner” -t az alábbi cellákban, amíg a cellák ki nem ürülnek.
Az alábbi lépések alatt lépjen a speciális menüpontra, és válassza ki az összes üres cellát, és töltse ki egy egyszerű képlet segítségével:
- Válassza ki a kitölteni kívánt adatokat (példánkban A1: D21)
- Lépjen a „Kezdőlap” fülre
- A Szerkesztés csoportban kattintson a „Keresés és kiválasztás” ikonra (ez további lehetőségeket jelenít meg a legördülő menüben)
- Kattintson az „Ugrás” lehetőségre
- A megnyíló Ugrás párbeszédpanelen kattintson a Speciális gombra. Ekkor megnyílik a „Go to Special” párbeszédpanel
- Az Ugrás a speciális párbeszédpanelen kattintson az Üres elemekre
- Kattintson az OK gombra
A fenti lépések kiválasztják az adatkészlet összes üres celláját (az alábbiak szerint).
A kiválasztott üres cellákban észreveheti, hogy az egyik cella világosabb, mint a kijelölés többi része. Ez a cella az az aktív cella, ahol egy képletet fogunk megadni.
Ne aggódjon, hogy a cella hol van a kiválasztásban, mivel a módszerünk minden esetben működne.
Most az alábbiakban bemutatjuk a kiválasztott üres cellák adatainak kitöltésének lépéseit:
- Nyomja meg az egyenlő (=) gombot a billentyűzeten. Ezzel beilleszti az egyenlő előjelet az aktív cellába
- Nyomja meg a felfelé mutató nyíl gombot. Ez beilleszti a cella cellahivatkozását az aktív cella fölé. Például a mi esetünkben a B3 az aktív cella, és amikor ezt a két lépést elvégezzük, akkor beírja = B2 a cellába
- Tartsa lenyomva a Control billentyűt, majd nyomja meg az Enter billentyűt
A fenti lépések automatikusan beszúrják az összes üres cellát a fölöttük lévő értékkel.
Bár ez túl sok lépésnek tűnhet, miután megismerkedett ezzel a módszerrel, néhány másodpercen belül gyorsan kitölti az adatokat az Excelben.
Most még két dologra kell ügyelnie, amikor ezt a módszert használja.
Képletek konvertálása értékekké
Az első az, hogy győződjön meg arról, hogy a képleteket értékekké alakítja át (hogy statikus értékei legyenek, és a dolgok ne zavarjanak össze, ha a jövőben adatokat változtatnak).
Az alábbiakban egy videó található, amely bemutatja, hogyan lehet gyorsan átalakítani a képleteket értékekké az Excelben.
Módosítsa a dátumformátumot
Abban az esetben, ha dátumokat használ az adataiban (ahogy én is a példaadatokban), akkor észre fogja venni, hogy a dátum oszlop kitöltött értékei számok, nem pedig dátumok.
Ha a dátum oszlop kimenete a kívánt dátumformátumban van, akkor jó, és nem kell mást tennie.
De ha ezek nem a megfelelő dátumformátumban vannak, akkor kisebb módosítást kell végrehajtania. Bár a megfelelő értékekkel rendelkezik, csak meg kell változtatnia a formátumot, hogy az dátumként jelenjen meg a cellában.
Ennek lépései az alábbiakban találhatók:
- Válassza ki a dátumokat tartalmazó oszlopot
- Kattintson a Kezdőlap fülre
- A Szám csoportban kattintson a Formátum legördülő menüre, majd válassza ki a dátum formátumát.
Ha időnként meg kell tennie ezt a kitöltést, azt javaslom, hogy használja ezt a Go-To speciális és formulázási technikát.
Bár néhány lépésből áll, egyszerű, és az eredményt ott adja meg az adathalmazban.
De ha ezt gyakran kell tennie, akkor javaslom, hogy nézze meg a VBA és a Power Query következő módszereit
2. módszer - Töltse ki egy egyszerű VBA kód használatával
Egy igazán egyszerű VBA makrókód segítségével gyorsan kitöltheti az Excel celláit az utolsó üres értékig.
Csak egyszer kell hozzáadnia a VBA -kódot a fájlhoz, majd könnyedén újra felhasználhatja a kódot többször ugyanabban a munkafüzetben vagy akár több munkafüzetben a rendszeren.
Az alábbiakban látható a VBA kód, amely végigmegy a kijelölés minden celláján, és kitölti az összes üres cellát:
'Ezt a VBA kódot Sumit Bansal hozta létre a TrumpExcel.com Sub FillDown () Minden kiválasztott cellához If cell = "" then cell.FillDown End If Next End Sub
A fenti kód For -ciklus segítségével megy végig a kijelölés minden celláján.
A For cikluson belül Ha-Akkor feltételt használtam, amely ellenőrzi, hogy a cella üres-e vagy sem.
Abban az esetben, ha a cella üres, akkor a fenti cella értékével van kitöltve, és ha nem üres, akkor a for ciklus egyszerűen figyelmen kívül hagyja a cellát, és a következőre lép.
Most, hogy megvan a VBA -kód, hadd mutassam meg, hová tegye ezt a kódot az Excelben:
- Válassza ki azokat az adatokat, amelyeket ki szeretne tölteni
- Kattintson a Fejlesztő fülre
- Kattintson a Visual Basic ikonra (vagy használhatja az ALT + F11 billentyűparancsot). Ezzel megnyílik a Visual Basic Editor Excelben
- A Visual Basic Editor bal oldalán a Project Explorer lesz. Ha nem látja, kattintson a menü „Nézet” opciójára, majd a Project Explorer elemre
- Ha több Excel munkafüzet van nyitva, a Project Explorer megmutatja az összes munkafüzet nevét. Keresse meg a munkafüzet nevét, ahol az adatok vannak.
- Kattintson a jobb gombbal a munkafüzet bármelyik objektumára, lépjen a Beszúrás elemre, majd kattintson a Modul elemre. Ez új modult illeszt be az adott munkafüzetbe
- Kattintson duplán a „Modul” elemre, amelyet az előző lépésben illesztett be. Megnyitja az adott modul kódablakát
- Másolja be a VBA kódot a kódablakba
- Vigye a kurzort a kód bármely pontjára, majd futtassa a makrókódot az eszköztár zöld gombjára kattintva vagy az F5 billentyűparancs használatával
A fenti lépések futtatják a VBA kódot, és az adatok le lesznek töltve.
Ha a jövőben újra szeretné használni ezt a VBA-kódot, akkor ezt a fájlt makró-kompatibilis 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 ehhez a makróhoz egyetlen kattintással férhet hozzá (abban a munkafüzetben, ahol a kód a háttérben található).
Tehát, ha legközelebb olyan adatokkal rendelkezik, amelyeket ki kell töltenie, mindössze annyit kell tennie, hogy kiválasztja, majd kattintson a makró gombra a Gyorselérési eszköztáron.
Hozzáadhatja ezt a makrót a Személyes makró munkafüzethez, majd használhatja a rendszer bármely munkafüzetében.
Kattintson ide, ha többet szeretne megtudni a személyes makró munkafüzetekről és a kódok hozzáadásáról.
3. módszer - Töltse ki a Power Query használatával
A Power Query beépített funkcióval rendelkezik, amely lehetővé teszi, hogy egyetlen kattintással töltse ki.
A Power Query használata akkor ajánlott, ha egyébként is az adatok átalakítására vagy több munkalap vagy több munkafüzet adatainak kombinálására használja.
A meglévő munkafolyamat részeként a Power Query kitöltési beállításával gyorsan kitöltheti az üres cellákat.
A Power Query használatához ajánlott, hogy az adatok Excel táblázat formátumban legyenek. Ha nem tudja konvertálni az adatokat Excel táblává, létre kell hoznia egy megnevezett tartományt az adatokhoz, majd ezt a megnevezett tartományt kell használnia a Power Queryben.
Az alábbiakban megvan az az adatkészlet, amelyet Excel táblává alakítottam át. Ezt az adatkészlet kiválasztásával, a Beszúrás fülre kattintva, majd a Táblázat ikonra kattintva teheti meg.
Az alábbiakban a Power Query használatával le kell töltenie az adatokat a következő értékig:
- Válassza ki az adathalmaz bármely celláját
- Kattintson az Adatok fülre
- Az Adatok lekérése és átalakítása csoportban kattintson a „Lapról” lehetőségre. Ez megnyitja a Power Query szerkesztőt. Ne feledje, hogy az üres cellák a „null” értéket mutatják a Power Query -ben
- A Power Query szerkesztőben válassza ki azokat az oszlopokat, amelyeket ki szeretne tölteni. Ehhez tartsa lenyomva a Control billentyűt, majd kattintson a kijelölni kívánt oszlopok fejlécére. Példánkban a Dátum és a Termék oszlopok lennének.
- Kattintson a jobb gombbal a kiválasztott fejlécek bármelyikére
- Lépjen a "Kitöltés" opcióra, majd kattintson a "Le" gombra. Ez kitölti az adatokat az összes üres cellában
- Kattintson a Fájl fülre, majd a „Bezárás és betöltés” gombra. Ez új munkalapot illeszt be a munkafüzetbe a kapott táblával
Bár ez a módszer kissé túlzásnak tűnik, a Power Query használatának nagy előnye, hogy lehetővé teszi a kapott adatok gyors frissítését, ha az eredeti adatok megváltoznak.
Például, ha további rekordokat ad hozzá az eredeti adatokhoz, akkor a fentieket nem kell újra elvégeznie. 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, ha már használja a Power Query -t a munkafolyamatban.
Ha csak egy adatkészlete van, ahol ki szeretné tölteni az üres cellákat, akkor kényelmesebb lenne az Ugrás speciális módszer vagy a VBA módszer (a fentiekben) használata.
Tehát ez három egyszerű módszer, amelyeket használhat töltse ki az üres cellákat az Excel következő értékéig.
Remélem, hasznosnak találta ezt az oktatóanyagot.
