A naptártípus elrendezésének átalakítása táblázatos formátumra a LuckyTemplates alkalmazásban a Lekérdezésszerkesztővel

A rendelkezésünkre álló adatok elemzése nehézkes lehet, ha nem megfelelően vannak elrendezve. Ebben az oktatóanyagban elmagyarázom, hogyan lehet a függőleges és vízszintes ismétlődő adatokat táblázatos formátummá alakítani. Ezt azért tesszük, hogy a LuckyTemplates segítségével könnyebben elemezhessük. Az oktatóanyag teljes videóját a blog alján tekintheti meg.

Ez az oktatóanyag egy tag kérdésére készült. A tag egy módot kért arra, hogy adatait egy naptár típusú elrendezésből táblázatos formátumba alakíthassa át.

Mielőtt elkezdenénk, először vizsgáljuk meg a rendelkezésünkre álló adatokat.

Itt van egy adatelrendezés, amelyben a napok vízszintesen, a hetek pedig függőlegesen vannak beállítva. Ez a fajta elrendezés legtöbbünk számára ismerős, hiszen így tudjuk könnyen megérteni az adatokat.

Az ilyen típusú elrendezést azonban nehezebb elemezni, mivel sok oszlopot és sort kell figyelembe venni.

Ennek ellenére alakítsuk át az adatokat megfelelő táblázatformátumba, ahol a végén egy oszlopot adunk a dátumoknak, egy oszlopot a mozgásoknak és egy másik oszlopot a neveknek.

Tartalomjegyzék

• Adatok törlése a megfelelő táblázatos formátumhoz
• Az asztalok feltörése
• Az új táblázatok listájának azonosítása a megfelelő táblázatos formátumhoz
• Annak meghatározása, hogy mely értékek tartoznak a táblázatba
• Az adatok csoportosítása megfelelő táblázatos formátumhoz
• A külön táblázatok csatolása
• Következtetés

Adatok törlése a megfelelő táblázatos formátumhoz

A dolgok megkönnyítése érdekében alakítsuk át adatainkat a. Mielőtt azonban ezt megtennénk, győződjünk meg arról, hogy a Képletsáv látható a képernyőn. Ha nem, lépjen a Nézet fülre, majd jelölje be a Képletsor melletti négyzetet.

Az első lépés az, hogy eltávolítsuk az adatainkból a szükségtelen értékeket. Távolítsuk el az első oszlopot és a felső két sort az adatokból, mert nincs rájuk igazán szükségünk. Ehhez kattintson a Sorok eltávolítása fülre, kattintson a Felső sorok eltávolítása elemre, írja be a 2-t, majd nyomja meg az OK gombot.

Most pedig az első oszlop eltávolításához lépjen az Oszlopok kiválasztása lapra, majd szüntesse meg az 1. oszlop kijelölését a mellette lévő jelölőnégyzet törlésével.

Távolítsuk el a 14-től 16-ig terjedő indexsorokat is, mivel ezek teljesen üres értékekkel vannak kitöltve. Ismét lépjen a Sorok eltávolítása lapra, majd kattintson az Üres sorok eltávolítása elemre .

Egy másik probléma, hogy a dátumértékek minden alkalommal kihagynak egy oszlopot. A dátumokat mindig egy null érték követi, ahogy az alább látható. Ez nem csak a felső sorban történik, hanem az alatta lévő sorokban is.

A hiányosságok pótlásához először transzponáljuk át az adattáblázatunkat úgy, hogy az  Átalakítás fülre kattintunk, majd a Transpone gombra kattintunk.

Az asztalnak most úgy kell kinéznie, mintha az oldalára fordították volna a művelet végrehajtása után.

Ezután jelölje ki a dátumokat tartalmazó összes oszlopot, kattintson jobb gombbal az oszlop fejlécére, majd válassza a Kitöltés lehetőséget.

Ez megkettőzi a dátumokat, és kitölti a hiányosságokat a kijelölt oszlopokban.

Az asztalok feltörése

Ha megnézzük a most rendelkezésünkre álló adatokat, azt látjuk, hogy négy tábla van egymás mellett. Az 1–7. oszlop egy táblázatot, a 8–13. oszlop egy másik táblázatot alkot, és így tovább.

Ahhoz, hogy ezeket egyetlen táblázatba fűzzük, egy alapvető unpivot műveletet kell végrehajtanunk, hogy megfelelő táblázatos formátummá alakítsuk át. De mivel az asztalok egymás mellett vannak, ezt nem tehetjük meg egyszerűen. Ezt az asztalt szét kell szednünk, és össze kell fűznünk őket kisebb asztalokká.

Ehhez elkészítjük ennek az oktatóanyagnak az első képletét. Kattintson az fx gombra, majd adjuk hozzá a képletet, amelyet használni fogunk. A Table.ToColumns műveletet fogjuk használni . Ezzel létrehozza a táblázatból az oszlopértékek beágyazott listáit. Minden oszlop listává lesz alakítva.

Így állítottam össze a képletet.

A képlet létrehozza a korábban meglévő oszlopok listáját. A lista első értéke az 1. oszlopot tartalmazza, a második értéke a 2. oszlopot, és így tovább.

Az új táblázatok listájának azonosítása a megfelelő táblázatos formátumhoz

Most meg kell határoznunk, hogy a lista mely értékei tartalmaznak dátumértékeket. Ha tudjuk, hol vannak ezek a dátumok, meg tudjuk határozni, hogy az egyes táblázataink hol kezdődnek vagy végződnek.

Ehhez először alakítsuk vissza a listát táblázattá a bal felső sarokban található Táblázathoz gombra kattintva.

Ezután adjon hozzá egy index oszlopot.

A következő lépés az, hogy hozzáadunk egy másik oszlopot az indexoszlop mellé. Ez az oszlop segít meghatározni, hogy az 1. oszlop mely értékei tartalmazzák a dátumértékeket. Nevezzük el ezt az oszlopot Index2-nek.

Ehhez beépítünk egy képletet az Index2-be, amely visszaadja az 1. oszlop indexértékét, ha dátumot tartalmaz.

A , majd az 1. oszlop hivatkozást fogjuk használni. A zárójelek kezelőként szolgálnak; ha ezek közé a zárójelek közé 0-t adunk, akkor az első értéket kapjuk a listából; majd eldöntjük, hogy randevú-e. Ha igen, azt akarom, hogy a képlet az indexszámot adja vissza; ha nem, akkor egyáltalán nem akarom, hogy értéket adjon vissza.

Mivel az 1. oszlop első értéke dátumot tartalmaz, az Index2 első értékének a 0 indexszámát kell tartalmaznia. Továbbá, mivel az 1. oszlop második értéke nem tartalmaz dátumot, az Index2 második értéke csak null értéket tartalmazhat.

Így állítottam be az Index2 képletét. Csak győződjön meg arról, hogy a képletben nincsenek szintaktikai hibák.

Ezt követően az Index2 oszlopban most már szerepelnie kell az 1. oszlopban lévő értékek indexének, amely tartalmazza a dátumot. Ez is tartalmazni fog néhány null értéket az általunk beállított képlet eredményeként.

Annak meghatározása, hogy mely értékek tartoznak a táblázatba

Most ezek az indexértékek az Index2-n belül jelzik a rendelkezésünkre álló különálló táblák kezdetét és végét. Vagyis a 0 az első tábla kezdetét, a 7 az első táblázat végét, a második tábla elejét és így tovább.

Annak érdekében, hogy azonosítani tudjuk, hogy az 1. oszlop mely értékei tartoznak ugyanabba a táblázatba, alkalmazzunk egy kitöltési műveletet az új oszlopunkban.

A művelet végrehajtása után a null értékeket most nullákkal, hetesekkel, tizenhárommal és húszasokkal kell helyettesíteni. Ezek a számok azt jelzik, hogy ugyanabba a táblázatba tartoznak. Vagyis a nullák egy táblázatot alkotnak, a hetesek egy másik táblázatot, és így tovább.

Az adatok csoportosítása megfelelő táblázatos formátumhoz

Mivel már tudjuk, hogy az 1. oszlop mely értékei tartoznak ugyanabba a táblázatba, most csoportosítsuk őket. Ehhez a Group By műveletet fogjuk használni.

Ezzel csak 4 értéket kapunk az Index2 oszlopban. Figyelje meg, hogy az Index2 oszlop mellett található egy másik, Számláló oszlop. Ez az oszlop az Index2 oszlopban található egyes értékek sorainak számát számolja.

Ez azonban nem igazán az, amit tenni akarunk. Amit tennünk kell, az az, hogy létrehozunk egy új táblázatot a rendelkezésünkre álló oszlopok felhasználásával.

Ehhez a Table.FromColumns műveletet fogjuk használni, hivatkozni kell a lista értékeit tartalmazó oszlopra, amely a Column1, majd módosítani kell a visszatérési típust egy táblázatra.

Fontos, hogy a képletben a megfelelő oszlopra hivatkozzunk. Ellenőrizze, hogy az 1. oszlop valóban tartalmazza-e a listák értékeit.

Ez összefűzi az 1. oszlop összes értékét, amelynek Index2 értéke 0, egy táblázatba, a 7-es Index2 értékűeket egy másik táblázatba, és így tovább.

Minden rekordot 0 értékre ellenőriztünk, majd lekértük a listaértéket az 1. oszlopból. Végül összefűztük ezeket a listákat, hogy egyetlen táblázatot alkossunk ezzel az M kóddal.

Megragadtuk az első 7 oszlopot és összefűztük őket egyetlen táblázatba, megragadtuk a következő néhány oszlopot, összefűztük őket egy másik táblázathoz, és így tovább.

A külön táblázatok csatolása

Most, hogy már csoportosítottuk őket, próbáljuk meg az összes táblázatot hozzáfűzni egy újabb lépés hozzáadásával. Ismét nyomjuk meg az fx gombot egy új képlet hozzáadásához.

A Table.Combine művelet összevonja a táblázatok listáját, majd egyetlen táblát hoz létre belőlük. Itt a táblázatok listáját tartalmazó oszlopra fogunk hivatkozni, amely a Szám oszlop.

Most már van egy táblázatunk, ahol az összes beágyazott tábla egyetlen nagy táblába van visszafűzve.

Az összes táblázat hozzáfűzése után elvégezhetjük az adatok megfelelő táblázatos formátumúvá alakításának utolsó néhány lépését. Jelölje ki a táblázat első két oszlopát, majd kattintson az Átalakítás lapon található Egyéb oszlopok elforgatása gombra.

Ezután távolítsa el az attribútum oszlopot, mert nincs rá igazán szükségünk. Lépjen az Oszlopok kiválasztása elemre , majd szüntesse meg az attribútumoszlop kijelölését.

Végül állítsa be az összes oszlop megfelelő típusát, és címkézze fel őket megfelelően.

Kattintson duplán az egyes oszlopok oszlopfejlécére a nevük megfelelő módosításához.

Az első oszlop típusát módosítsa dátumra, mivel az tartalmazza a dátumértékeinket.

Ezután válassza ki a második és a harmadik oszlopot, és állítsa be a típusukat szövegre.

Minden kész! Így kell kinéznie a megfelelő táblázatos formátumunknak.

Következtetés

Ebben az oktatóanyagban néhány adatot, amelyeket naptár típusú elrendezésben mutattunk be, megfelelő táblázatos formátumba alakítottunk át. Először megtisztítottuk az adatainkat, kisebb táblákra bontottuk, az összetartozó táblákat csoportosítottuk, majd a végén mindet hozzáfűztük.

Ez az oktatóanyag sok ismeretet kínál az adatok megfelelő modellezéséhez a könnyebb elemzés érdekében.

Az itt használt fogalmak megértése nagyon fontos, mivel sok más módon is alkalmazhatók.

Ha a fogalmak még mindig nem világosak az Ön számára, az alábbi linkek ellenőrzése segíthet.

Élvezze ezt a munkát!

Méhfű