Több munkalap hozzáfűzése az Excelben a LuckyTemplateshez

Ez az oktatóanyag arról szól, hogyan importálhat és nyithat meg egy több lapot tartalmazó Excel-fájlt egy LuckyTemplates táblába.

Ez az Excel adatfájl lesz felhasználva ehhez az oktatóanyaghoz.

A fájl 60 azonos elrendezésű adatlapot tartalmaz.

Ennek az oktatóanyagnak az a célja, hogy betöltse az összes Excel-lapot a LuckyTemplatesbe, és egyetlen táblázatként lekérje a kötvényinformációkat. Be kell szereznie a Security tickert, és hozzá kell adnia egy külön oszlopban. Ezután fűzze hozzá ezeket egyetlen táblázatba, amely kihasználja a felhasználói felületet a szükséges kód létrehozásához.

Tartalomjegyzék

• Az Excel-táblázatok importálása a LuckyTemplates alkalmazásba
• A biztonsági ticker kiemelése az Excelből a LuckyTemplatesbe
• Üres sorok eltávolítása
• Lekérdezés megkettőzése
• Adattípusok hozzárendelése oszlopokhoz
• Biztonsági ticker oszlop hozzáadása az Excelből a LuckyTemplateshez
• Egyéni oszlop létrehozása kötvénylekérdezéshez
• Következtetés

Az Excel-táblázatok importálása a LuckyTemplates alkalmazásba

Az első dolog, hogy nyissa meg a fájlt, és hozzon létre egy paramétert a fájl helyének tárolására a Paraméterek kezelése opcióval.

A Paraméterek kezelése mezőben kattintson az Új gombra, és adja meg a FileLocation paraméter nevét. Ezután állítsa az Adattípust Szöveg értékre , a Javasolt értékeket pedig Bármely értékre . Másolja ki az Excel fájl elérési útját, és illessze be az Aktuális érték mezőkbe.

Az OK gomb megnyomása után kattintson az Új forrás elemre , és válassza az Excel-munkafüzet lehetőséget .

Ezután válassza ki és nyissa meg az Excel fájlt. Ha elkészült, megnyílik a Navigátor képernyő, és felsorolja az Excel fájlban található összes lapot.

Válassza ki az első lapot, és nyomja meg az OK gombot. Ezután megjelenik a táblázat a Power Query Editorban.

A következő teendő az, hogy módosítsa a keményen kódolt fájl elérési útját a fájlparaméterre. Nyissa meg a Speciális szerkesztő ablakot, és módosítsa a fájl elérési útját a forráskódban FileLocation értékre .

A legjobb, ha az adatokat Excel-táblázatokban tárolja, mert a határok vagy az adattartomány már azelőtt meg van határozva, hogy bevinné őket. Mivel a fájl lapokat és táblázatokat nem tartalmaz, fennáll annak a veszélye, hogy üres oszlopokat és üres sorokat hoz létre, ezért ezeket rendezni kell.

A biztonsági ticker kiemelése az Excelből a LuckyTemplatesbe

Ha tudjuk, hogy a lapok elrendezése rögzített, az segít a megoldás felépítésében, különösen akkor, ha maximalizálni szeretnénk, és a felhasználói felületet szeretnénk használni a kód generálásához. Például az egyik követelmény az, hogy adjon hozzá egy oszlopot, amely tartalmazza a Biztonsági jelzőt. Ha megnézi az adatokat, láthatja a tickert.

A felhasználói felület segíthet a ticker értékének kinyerésében. Kattintson a jobb gombbal a cellára, és válassza a Hozzáadás új lekérdezésként lehetőséget .

A képletsorban látni fogja, hogy a táblázat nevét egy nulla alapú sorszám követi a zárójelek között. A göndör zárójeleket pozícióindex operátoroknak nevezzük . A mező nevét is láthatja szögletes zárójelek között, amelyeket mezőhozzáférési operátoroknak nevezünk .

Ezekkel a kódokkal most kivonhatja az értéket. Menjen vissza a Bond-táblázathoz, és kérje le a kötvényadatokat. Először távolítsa el a felső 8 nyolc sort. Kattintson a Sorok eltávolítása elemre , és válassza a Felső sorok eltávolítása lehetőséget .

Ezután írja be a 8-at a Sorok száma mezőbe, és nyomja meg az OK gombot.

Ezután kattintson az Első sor használata fejlécként lehetőségre a fejlécek beállításához.

Ha elkészült, a Bond-tábla most így fog kinézni.

Közvetlenül a fejléc alatt egy vékony vonal látható, amely az oszlop minőségét jelzi. Innen látható, hogy elég sok üres hely van az oszlopokban. Ez azt jelenti, hogy a fájl sok üres sort hozott.

Üres sorok eltávolítása

Az üres sorok eltávolításához kattintson a Sorok eltávolítása elemre, és válassza az Üres sorok eltávolítása lehetőséget .

Ez az átalakítás a következő szintaxist generálja:

A Record.FieldValues ​​a táblázat aktuális sorának összes értékét listaként kapja meg. A List.RemoveMatchingItems eltávolítja az első listából az összes olyan értéket, amely egyezést mutat a második listában. A második lista csak üres szöveges karakterláncot vagy nullát tartalmaz. Ezek azok az értékek, amelyek kimaradnak az első listából.

Ha az összes üres szöveges karakterláncot és nullát eltávolította a listából a rekordmező értékeivel, akkor a listának üresnek kell lennie, és a List.IsEmpty értéke True lesz. Ezután a Table.SelectRows megtartja a Trues értéket.

Nem szabad olyan táblázatot készítenie, amely csak üres sorokat tartalmaz. Ezért kerül a not kulcsszó a List.IsEmpty elé . Ez egy olyan táblát ad vissza, amely nem üres sorokat tartalmaz.

Az üres sorokon kívül az üres oszlopokat is el kell távolítania. De előtte nézze meg, mit generál a Power Query, amikor eltávolít egy oszlopot. Jelölje ki a negyedik oszlopot, és kattintson az Oszlopok eltávolítása gombra .

Az átalakítás végrehajtása után ezt a szintaxist fogja látni a képletsorban.

Meghívja a Table.RemoveColumns függvényt, majd első argumentumként hivatkozik és átadja az előző lépést az Alkalmazott lépések panelen. Az átalakítás továbbad egy listát, amely tartalmazza az eltávolítani kívánt oszlopok oszlopnevét.

Lekérdezés megkettőzése

Most készítse el a lekérdezést, és válassza ki a Fejlécek előmozdítása lépést az Alkalmazott lépések ablaktáblán. Ezután kattintson a jobb gombbal az adott lépésre, és válassza a Törlés végéig lehetőséget .

Ne feledje, hogy a pozícióindex operátort használhatja nulla alapú sorszámok átadására szögletes zárójelben. Tehát csak az első sort tartsa meg úgy, hogy a képletsávban két göndör zárójelbe 0-t ír be.

Ha elkészült, a Bond-tábla így fog kinézni.

Innentől kezdve használja újra az Eltávolított üres sorok lépéssel létrehozott logika egy részét a rekord listává alakításához és a nulla eltávolításához. Menjen vissza a Kötvény lekérdezéshez, és válassza az Üres sorok eltávolítása lépést. Ezután másolja ki ezt az M-kódot.

Térjen vissza a duplikált lekérdezéshez, és illessze be a kódot a képletsávba. Ezután rendezzen el néhány karakterláncot, hogy a képlet így nézzen ki.

Létrehozott egy listát az oszlopnevekkel, amelyeket meg szeretne tartani. Nevezze át a lekérdezést Oszlopnevek .

Ezután térjen vissza a Bond-lekérdezéshez. Mivel olyan lekérdezést hozott létre, amely az összes megtartani kívánt oszlopot tartalmazza, a Table.RemoveColumns függvényben listáznia kell az összes kizárni kívánt oszlopot .

Változtassa meg a {Oszlop4} értéket ugyanazzal a szintaxissal, mint az Eltávolított üres sorok lépésből másolt. Módosítsa a {“”, null} elemet is ColumnNames értékre .

Ezután át kell adnia egy listát a tényleges oszlopnevekkel a Bond táblából. Módosítsa a Record.FieldValues(_) paramétert Table.ColumnNames() értékre . Írja be a „Removed Blank Rows” számot a zárójelek közé a táblázat hivatkozási argumentumának átadásához.

Adattípusok hozzárendelése oszlopokhoz

A következő lépés a megfelelő adattípusok hozzárendelése az oszlopokhoz. A Dátum oszlopban kattintson a fejléc melletti ikonra, és válassza a Dátum lehetőséget.

A PX_LAST és YLD_YTM_MID oszlopokhoz válassza ki a Tizedes szám adattípust.

Ezzel a három lekérdezéssel létrehozta az összes építőelemet, amelyek segítségével olyan megoldást tervezhet, amely a fájl összes lapját megcímzi. Ehhez meg kell másolnia a Bond lekérdezést, és törölnie kell az összes lépést, kivéve a Forrás lépést az Alkalmazott lépések panelen.

Biztonsági ticker oszlop hozzáadása az Excelből a LuckyTemplateshez

A Forrás lépésben megtekintheti az Excel-fájlban található összes adatot. Ahelyett, hogy minden egyes laphoz külön lekérdezést hozna létre, használhatja a Bond lekérdezést, és átalakíthatja az Adatok oszlopban lévő beágyazott táblákat.

Először adja hozzá a Biztonsági tickert. Ha bármelyik táblázat cellájában rákattint a szóközre, megjelenik a táblázat tartalmának előnézete.

Létre kell hoznia egy logikát egy utasítás segítségével, hogy megkapja a Security tickert. Lépjen a 2. oszlop lekérdezésre, és másolja ki a Security ticker címét a képletsorról.

Ezután térjen vissza a Bond-lekérdezéshez, és adjon hozzá egy egyéni oszlopot.

Nevezze el az oszlopot Security Ticker, és írja be a következő M kódot.

A képletnek van egy IF utasítása, amely szerint ha az 1. oszlopban a Security szó található, akkor a 2. oszlopból származó, vele szomszédos cella értékét adja meg. Ellenkező esetben null érték lesz megadva.

Az OK megnyomása után egy új oszlop kerül a táblába a Biztonsági jelölőkkel.

Kattintson a Security Ticker oszlop legördülő gombjára, és törölje a null jelet az oszlop összes nullának eltávolításához.

Ezt követően a kötvényekre vonatkozó összes információ megmarad az összes lapról. Csak annyit kell tennie, hogy megismétli a Bond(2) lekérdezésben végrehajtott átalakításokat, és alkalmazza azokat az Adat oszlop beágyazott tábláira.

Egyéni oszlop létrehozása kötvénylekérdezéshez

Lépjen a Bond(2) lekérdezésre, nyissa meg a Speciális szerkesztő ablakot, és másolja ki a következő kódot:

Ezután térjen vissza a Bond-lekérdezéshez, és hozzon létre egy másik egyéni oszlopot. Mivel több átalakítást kell alkalmaznia több lépésben, ezért let utasítást kell használnia. Tehát írja be a let-et , és illessze be a kódot a Speciális szerkesztőből.

Ezután módosítsa a Bond_Sheet értéket Data értékre , hogy átalakítsa a táblázatot az Adat oszlopban.

Az OK megnyomása után egy új oszlop kerül be a táblázatba.

Ha rákattint a szóközre az oszlop bármely cellájában, látni fogja a Bond(2) lekérdezés adatait.

Mindössze a Bond-lekérdezés utolsó két oszlopában van szüksége. Tehát válassza ki a Biztonsági ticker és az Egyéni oszlopot, és kattintson az Egyéb oszlopok eltávolítása elemre az Oszlopok eltávolítása szalagról.

Bontsa ki az Egyéni oszlopot, és törölje a jelet az Eredeti név használata előtagként négyzetből.

Ha elkészült, érvényesítse a táblázatot. A táblázat alatt válassza ki az Oszlopprofilozás a teljes adatkészlet alapján lehetőséget.

Az átalakítás után hiba jelenik meg az Adat oszlopban.

Ezért vizsgálja meg a hibát úgy, hogy az Adatok oszlopra kattint, és a Sorok megtartása szalagon a Hibák megtartása lehetőséget választja.

Ha az Adat oszlopban az Error értékre kattint, az alábbi hibaüzenetet olvashatja el:

A problémák megoldásához először távolítsa el a Megőrzött hibák lépést. Kattintson az Adatok oszlopra, és válassza a Hibák eltávolítása lehetőséget a Sorok eltávolítása szalagról.

Ezután állítsa vissza az oszlopprofilozási beállítást a felső 1000 sorra. És ez az!

Következtetés

Ez a felhasználói felület által vezérelt megoldás segíthet több lapot hozzáfűzni egy Excel-fájlból a LuckyTemplates-hez. Ahelyett, hogy 60 külön lekérdezést hozna létre, és minden átalakítást ismételten végrehajtana, ez a megoldás egyetlen lekérdezést hozhat létre, amely végrehajtja az összes átalakítást. Használja és maximalizálja ezt a megoldást.

Méhfű