tartalom
Ebben az életben sok üzleti folyamat (sőt egész vállalkozás) korlátozott számú előadó által adott határidőre teljesíti a megrendeléseket. A tervezés ilyenkor, ahogy mondani szokás, „a naptárból” történik, és gyakran felmerül az igény a benne tervezett események (megrendelések, értekezletek, kiszállítások) áthelyezése a Microsoft Excelbe – további elemzéshez képletekkel, pivot táblákkal, diagramokkal, stb.
Természetesen egy ilyen átutalást nem hülye másolással (ami csak nem nehéz), hanem automatikus adatfrissítéssel szeretnék megvalósítani, hogy a jövőben minden naptári változás és menet közbeni új rendelés megjelenjen Excel. Az ilyen importálást percek alatt megvalósíthatja a Microsoft Excelbe épített Power Query bővítménnyel, a 2016-os verziótól kezdődően (Excel 2010-2013 esetén a Microsoft webhelyéről letölthető és a linkről külön telepíthető) .
Tegyük fel, hogy a tervezéshez az ingyenes Google Calendar-t használjuk, amiben a kényelem kedvéért külön naptárt készítettem (a jobb alsó sarokban található pluszjeles gomb Egyéb naptárak) címmel Munka. Ide írunk be minden olyan rendelést, amelyet ki kell tölteni és ki kell szállítani az ügyfeleknek a címükre:
Bármely rendelésre duplán kattintva megtekintheti vagy szerkesztheti annak részleteit:
Vegye figyelembe, hogy:
- Az esemény neve menedzseraki teljesíti ezt a parancsot (Elena) és Rendelésszám
- Jelzett cím kézbesítés
- A megjegyzés tartalmazza (külön sorokban, de tetszőleges sorrendben) a megrendelés paramétereit: fizetés típusa, összege, vevő neve stb. Paraméter=Érték.
Az egyértelműség kedvéért az egyes vezetők megbízásai a saját színükkel vannak kiemelve, bár ez nem szükséges.
1. lépés: Szerezzen linket a Google Naptárhoz
Először meg kell szereznünk egy internetes hivatkozást a rendelési naptárunkhoz. Ehhez kattintson a három ponttal ellátott gombra A naptárbeállítások működnek a naptár neve mellett, és válassza ki a parancsot Beállítások és megosztás:
A megnyíló ablakban kívánság szerint nyilvánossá teheti a naptárt, vagy megnyithatja a hozzáférést az egyes felhasználók számára. Szükségünk van egy linkre is a naptárhoz való privát hozzáféréshez iCal formátumban:
2. lépés: Töltse be az adatokat a naptárból a Power Querybe
Most nyissa meg az Excelt és a lapon dátum (ha Excel 2010-2013 verziója van, akkor a lapon Teljesítmény lekérdezés) válasszon egy parancsot Az internetről (Adatok – Internetről). Ezután illessze be a másolt elérési utat a naptárba, és kattintson az OK gombra.
Az iCal Power Query nem ismeri fel a formátumot, de könnyű segíteni. Lényegében az iCal egy sima szöveges fájl, kettősponttal határolóként, belül pedig valahogy így néz ki:
Így egyszerűen kattintson a jobb gombbal a letöltött fájl ikonjára, és válassza ki a jelentéséhez legközelebb álló formátumot CSV – és az összes rendelésre vonatkozó adataink betöltődnek a Power Query lekérdezésszerkesztőbe, és kettősponttal két oszlopra osztva:
Ha alaposan megnézed, világosan láthatod, hogy:
- Az egyes eseményekre (rendelésekre) vonatkozó információk egy blokkba vannak csoportosítva, amelyek a BEGIN szóval kezdődnek és az END szóval végződnek.
- A kezdési és befejezési dátumokat a DTSTART és DTEND címkével ellátott karakterláncok tárolják.
- A szállítási cím: LOCATION.
- Rendelési megjegyzés – LEÍRÁS mező.
- Esemény neve (menedzser neve és megrendelés száma) — ÖSSZEFOGLALÓ mező.
Már csak ki kell bontani ezt a hasznos információt, és kényelmes táblázattá alakítani.
3. lépés Váltson át normál nézetre
Ehhez hajtsa végre a következő műveleti láncot:
- Az első BEGIN parancs előtt töröljük azt a felső 7 sort, amelyre nincs szükségünk Kezdőlap — Sorok törlése — Felső sorok törlése (Főoldal – Sorok eltávolítása – Felső sorok eltávolítása).
- Szűrés oszlop szerint Column1 sorok, amelyek a szükséges mezőket tartalmazzák: DTSTART, DTEND, DESCRIPTION, LOCATION és SUMMARY.
- A Speciális lapon Oszlop hozzáadása választani Index oszlop (Oszlop hozzáadása — Index oszlop)hogy egy sorszám oszlopot adjunk az adatainkhoz.
- Ott a lapon. Oszlop hozzáadása válassz csapatot Feltételes oszlop (Oszlop hozzáadása – Feltételes oszlop) és minden blokk (sorrend) elején megjelenítjük az index értékét:
- Töltse ki az üres cellákat a kapott oszlopban Blokkjobb gombbal kattintson a címére és válassza ki a parancsot Kitöltés – Le (Kitöltés - Le).
- Távolítsa el a felesleges oszlopot index.
- Válasszon egy oszlopot Column1 és hajtsa végre az oszlopból származó adatok konvolúcióját Column2 parancs segítségével Átalakítás – Pivot Column (Átalakítás – Pivot oszlop). Feltétlenül válasszon a lehetőségek közül Ne összesítse (Ne összesíts)hogy az adatokra ne legyen matematikai függvény:
- A kapott kétdimenziós (kereszt) táblázatban törölje a fordított perjeleket a cím oszlopban (jobb gombbal kattintson az oszlop fejlécére – Értékek cseréje), és távolítsa el a felesleges oszlopot Blokk.
- Az oszlopok tartalmának forgatásához DTSTART и DTEND teljes dátum-időben, kiemelve őket, válassza ki a lapon Átalakítás – Dátum – Elemzés futtatása (Átalakítás – Dátum – Elemzés). Ezután a függvény cseréjével javítjuk a kódot a képletsorban Dátum.Tól on DateTime.Fromhogy ne veszítse el az időértékeket:
- Ezután jobb gombbal a fejlécre kattintva felosztjuk az oszlopot LEÍRÁS rendelési paraméterekkel elválasztóval – szimbólum n, de ugyanakkor a paraméterekben a sorokra osztást választjuk ki, nem pedig az oszlopokra:
- A kapott oszlopot még egyszer két különálló oszlopra osztjuk – a paraméterre és az értékre, de egyenlőségjellel.
- Oszlop kiválasztása LEÍRÁS.1 hajtsa végre a konvolúciót, ahogy korábban tettük, a paranccsal Átalakítás – Pivot Column (Átalakítás – Pivot oszlop). Az érték oszlop ebben az esetben a paraméterértékekkel rendelkező oszlop lesz LEÍRÁS.2 Ügyeljen arra, hogy a paraméterek között válasszon ki egy funkciót Ne összesítse (Ne összesíts):
- Továbbra is be kell állítani az összes oszlop formátumát, és szükség szerint át kell nevezni. Az eredményeket pedig a paranccsal vissza tudod tölteni az Excelbe Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Kezdőlap – Bezárás&Betöltés – Bezárás&Betöltés…)
És itt van a Google Naptárból Excelbe betöltött rendeléseink listája:
A jövőben a rendelések módosításakor vagy új naptárba való felvételekor elég lesz kérésünket a paranccsal frissíteni. Adatok – Összes frissítése (Adatok – Összes frissítése).
- A gyári naptár Excelben frissítve az internetről a Power Query segítségével
- Oszlop átalakítása táblázattá
- Hozzon létre egy adatbázist Excelben