tartalom
YouTube csatornám egyik legnézettebb videója a Flash Fillről szóló videó a Microsoft Excelben. Ennek az eszköznek az a lényege, hogy ha valamilyen módon át kell alakítania a forrásadatait, akkor csak el kell kezdenie beírni a kívánt eredményt a szomszédos oszlopba. Több manuálisan begépelt cella után (általában 2-3 is elegendő) az Excel „megérti” a szükséges átalakítások logikáját, és automatikusan folytatja a begépeltet, és elvégzi helyetted az összes monoton munkát:
A hatékonyság kvintesszenciája. A varázslatos „csináld jól” gomb, amit mindannyian annyira szeretünk, igaz?
Valójában van egy ilyen eszköz analógja a Power Queryben – ott hívják Oszlop példákból (Oszlop a példákból). Valójában ez egy kis mesterséges intelligencia, amely a Power Querybe van beépítve, amely gyorsan tanul az Ön adataiból, majd átalakítja azokat. Nézzük meg közelebbről a képességeit több gyakorlati forgatókönyvben, hogy megértsük, hol lehet hasznos számunkra a valós feladatok során.
Példa 1. Szöveg ragasztása/kivágása
Tegyük fel, hogy van egy ilyen „okos” táblázatunk az Excelben az alkalmazottak adataival:
Töltse be a Power Querybe a szokásos módon – a gombbal Táblázatból/Tartományból lap dátum (Adatok – táblázatból/tartományból).
Tegyük fel, hogy minden alkalmazotthoz hozzá kell adnunk egy oszlopot vezetéknevekkel és kezdőbetűkkel (Ivanov SV az első alkalmazottnál stb.). A probléma megoldásához két módszer egyikét használhatja:
- kattintson a jobb gombbal a forrásadatokat tartalmazó oszlopfejlécre, és válassza ki a parancsot Oszlop hozzáadása a példákból (Oszlop hozzáadása a példákból);
- válasszon ki egy vagy több oszlopot az adatokkal és a lapon Oszlop hozzáadása válassz csapatot Oszlop példákból. Itt a legördülő listában megadhatja, hogy az összes vagy csak a kiválasztott oszlopot kell-e elemezni.
Ezután minden egyszerű – a jobb oldalon megjelenő oszlopban elkezdjük beírni a kívánt eredményeket, a Power Query-be épített mesterséges intelligencia pedig megpróbálja megérteni az átalakítási logikánkat, és önállóan folytatni:
Egyébként ennek az oszlopnak bármelyik cellájába beírhatja a helyes beállításokat, azaz nem feltétlenül felülről lefelé és sorban. Ezenkívül a címsorban található jelölőnégyzetekkel később könnyen hozzáadhat vagy eltávolíthat oszlopokat az elemzésből.
Ügyeljen az ablak tetején található képletre – ezt hozza létre az intelligens Power Query, hogy elérje a szükséges eredményeket. Ez egyébként az alapvető különbség ezen eszköz és Azonnali kitöltés Excelben. Az azonnali töltés úgy működik, mint egy „fekete doboz” – nem az átalakítások logikáját mutatják meg, hanem egyszerűen kész eredményt adnak, amit természetesnek vesszük. Itt minden átlátható, és mindig teljesen világosan érthető, hogy pontosan mi történik az adatokkal.
Ha azt látja, hogy a Power Query „elkapta az ötletet”, akkor nyugodtan megnyomhatja a gombot OK vagy billentyűparancsot Ctrl+belép – egy egyedi oszlop jön létre a Power Query által kitalált képlettel. Egyébként később könnyen szerkeszthető normál, kézzel készített oszlopként (a paranccsal Oszlop hozzáadása – Egyéni oszlop) a lépés nevétől jobbra található fogaskerék ikonra kattintva:
2. példa: Az eset, mint a mondatokban
Ha jobb gombbal kattint a szöveget tartalmazó oszlopfejlécre, és kiválasztja a parancsot Átalakítás (Átalakítás), akkor három parancsot láthat, amelyek felelősek a regiszter módosításáért:
Kényelmes és menő, de például ebből a listából nekem személy szerint mindig hiányzott még egy lehetőség – az eset, mint a mondatokban, amikor a nagybetűs (nagybetűs) nem minden szó első betűje, hanem csak a cella első betűje, és a szöveg többi része, amikor Ez kisbetűkkel (kis) jelenik meg.
Ez a hiányzó funkció mesterséges intelligenciával könnyen megvalósítható Oszlopok példákból – csak adjon meg néhány lehetőséget, hogy a Power Query ugyanabban a szellemben folytassa:
Képletként itt a Power Query egy csomó függvényt használ Szöveg.Felső и Szöveg.Alsó, a szöveget nagy- és kisbetűssé alakítja, valamint függvényeket Szöveg.Start и Szöveg.Közép – az Excel LEFT és PSTR függvények analógjai, amelyek képesek egy részstringet kivonni a szövegből balról és középről.
3. példa Szavak permutációja
Néha a kapott adatok feldolgozása során szükségessé válik a cellákban lévő szavak adott sorrendbe történő átrendezése. Természetesen az oszlopot az elválasztóval külön szóoszlopokra oszthatjuk, majd a megadott sorrendben visszaragaszthatjuk (ne felejtsünk el szóközt tenni), de az eszköz segítségével Oszlop példákból minden sokkal könnyebb lesz:
4. példa: Csak számok
Egy másik nagyon fontos feladat, hogy csak számokat (számokat) húzzunk ki a cella tartalmából. Mint korábban, az adatok Power Querybe való betöltése után lépjen a lapra Oszlop hozzáadása – Oszlop példákból és töltsön ki néhány cellát manuálisan, hogy a program megértse, hogy pontosan mit is szeretnénk elérni:
Bingó!
Ismét érdemes megnézni az ablak tetejét, hogy megbizonyosodjunk arról, hogy a Query megfelelően generálta a képletet – ebben az esetben tartalmaz egy függvényt Szöveg. Válassza ki, amely, ahogy sejthető, a lista szerint kivonja a megadott karaktereket a forrásszövegből. Később ez a lista természetesen szükség esetén könnyen szerkeszthető a képletsorban.
5. példa: Csak szöveg
Az előző példához hasonlóan kihúzhatja és fordítva – csak a szöveg, az összes szám törlése, írásjelek stb.
Ebben az esetben egy már ellentétes jelentésű függvényt használunk – a Text.Remove-ot, amely egy adott lista szerint eltávolítja a karaktereket az eredeti karakterláncból.
6. példa: Adatok kinyerése alfanumerikus zabkása
A Power Query segíthet a nehezebb esetekben is, amikor egy cellában található alfanumerikus zabkása hasznos információkat kell kinyernie, például a bankszámlakivonat fizetési céljának leírásából kapja meg a számlaszámot:
Vegye figyelembe, hogy a Power Query által generált konverziós képlet meglehetősen összetett lehet:
Az olvasás és a megértés megkönnyítése érdekében egy ingyenes online szolgáltatás segítségével sokkal értelmesebb formára alakítható. Power Query formázó:
Nagyon praktikus dolog – tisztelet az alkotóknak!
7. példa: Dátumok konvertálása
Szerszám Oszlop példákból dátum vagy dátumidő oszlopokra is alkalmazható. Amikor megadja a dátum első számjegyeit, a Power Query segítőkészen megjeleníti az összes lehetséges átalakítási lehetőség listáját:
Így könnyen konvertálhatja az eredeti dátumot bármilyen egzotikus formátumra, például „év-hónap-nap”:
8. példa: Kategorizálás
Ha használjuk az eszközt Oszlop példákból numerikus adatokat tartalmazó oszlophoz, másként működik. Tegyük fel, hogy az alkalmazottak teszteredményeit betöltjük a Power Querybe (feltételes pontszámok 0-100 tartományban), és a következő feltételes fokozatokat használjuk:
- Mesterek – azok, akik 90-nél többet szereztek
- Szakértők – 70-től 90-ig pontozták
- Felhasználók – 30 és 70 között
- Kezdők – azok, akik kevesebb mint 30 pontot értek el
Ha a példákból hozzáadunk egy oszlopot a listához, és elkezdjük manuálisan rendezni ezeket az átmeneteket, akkor a Power Query hamarosan felveszi az ötletünket, és hozzáad egy oszlopot egy képlettel, ahol az operátorok egymásba ágyazódnak. if logika kerül megvalósításra, nagyon hasonló ahhoz, amire szükségünk van:
Ismét nem lehet a végére szorítani a helyzetet, hanem kattintani kell OK majd korrigálja a küszöbértékeket már a képletben – ez így gyorsabb:
Következtetések
Természetesen eszköz Oszlop példákból nem egy „varázstabletta”, és előbb-utóbb előfordulnak nem szabványos helyzetek vagy különösen figyelmen kívül hagyott „kolhoz” esetek az adatokban, amikor a Power Query meghibásodik, és nem tudja kidolgozni, amit akarunk. helyesen nekünk. Viszont segédeszközként nagyon jó. Ráadásul az általa generált képletek tanulmányozásával bővítheti ismereteit az M nyelv funkcióiról, ami a jövőben mindig jól fog jönni.
- Szöveg elemzése reguláris kifejezésekkel (RegExp) a Power Queryben
- Fuzzy szöveges keresés a Power Queryben
- Flash Töltse ki a Microsoft Excel programot