Cuprins
Multe procese de afaceri (și chiar afaceri întregi) din această viață implică onorarea comenzilor de către un număr limitat de executanți până la un anumit termen limită. Planificarea în astfel de cazuri are loc, după cum se spune, „din calendar” și adesea este nevoie de a transfera evenimentele planificate în acesta (comenzi, întâlniri, livrări) în Microsoft Excel - pentru analize ulterioare prin formule, tabele pivot, diagrame, etc.
Desigur, aș dori să implementez un astfel de transfer nu prin copiere stupidă (ceea ce pur și simplu nu este dificil), ci cu actualizarea automată a datelor, astfel încât în viitor toate modificările aduse calendarului și noile comenzi din mers să fie afișate în Excela. Puteți implementa un astfel de import în câteva minute folosind add-in-ul Power Query încorporat în Microsoft Excel, începând cu versiunea 2016 (pentru Excel 2010-2013, acesta poate fi descărcat de pe site-ul Microsoft și instalat separat din link) .
Să presupunem că folosim Google Calendar gratuit pentru planificare, în care eu, pentru comoditate, am creat un calendar separat (butonul cu semnul plus în colțul din dreapta jos, lângă Alte calendare) cu titlul Apartamente. Aici introducem toate comenzile care trebuie finalizate și livrate clienților la adresele acestora:
Făcând dublu clic pe orice comandă, puteți vedea sau edita detaliile acesteia:
Rețineți că:
- Numele evenimentului este managercare îndeplineşte acest ordin (Elena) şi Numar de ordine
- Indicat adresa livrare
- Nota conține (în rânduri separate, dar în orice ordine) parametrii comenzii: tipul plății, suma, numele clientului etc. în format Parametru=Valoare.
Pentru claritate, comenzile fiecărui manager sunt evidențiate în propria culoare, deși acest lucru nu este necesar.
Pasul 1. Obțineți un link către Google Calendar
Mai întâi trebuie să obținem un link web către calendarul nostru de comenzi. Pentru a face acest lucru, faceți clic pe butonul cu trei puncte Opțiuni pentru calendar Funcționează lângă numele calendarului și selectați comanda Setări și Partajare:
În fereastra care se deschide, puteți, dacă doriți, să faceți public calendarul sau să deschideți accesul la acesta pentru utilizatorii individuali. Avem nevoie și de un link pentru acces privat la calendar în format iCal:
Pasul 2. Încărcați datele din calendar în Power Query
Acum deschide Excel și pe filă Date (dacă aveți Excel 2010-2013, atunci pe fila Interogare de alimentare) alegeți o comandă De pe internet (Date - De pe Internet). Apoi lipiți calea copiată în calendar și faceți clic pe OK.
iCal Power Query nu recunoaște formatul, dar este ușor de ajutat. În esență, iCal este un fișier text simplu cu două puncte ca delimitator, iar în interior arată cam așa:
Deci, puteți doar să faceți clic dreapta pe pictograma fișierului descărcat și să selectați formatul care este cel mai apropiat CSV – iar datele noastre despre toate comenzile vor fi încărcate în editorul de interogări Power Query și împărțite în două coloane după două puncte:
Dacă te uiți cu atenție, poți vedea clar că:
- Informațiile despre fiecare eveniment (ordine) sunt grupate într-un bloc care începe cu cuvântul BEGIN și se termină cu END.
- Datele de început și de sfârșit sunt stocate în șiruri etichetate DTSTART și DTEND.
- Adresa de livrare este LOCATION.
- Notă de comandă – câmp DESCRIERE.
- Nume eveniment (numele managerului și numărul comenzii) — câmp REZUMAT.
Rămâne să extragem aceste informații utile și să le transformăm într-un tabel convenabil.
Pasul 3. Convertiți în vizualizarea normală
Pentru a face acest lucru, efectuați următorul lanț de acțiuni:
- Să ștergem primele 7 linii de care nu avem nevoie înainte de prima comandă BEGIN Acasă — Ștergeți rândurile — Ștergeți rândurile de sus (Acasă — Eliminați rândurile — Eliminați rândurile de sus).
- Filtrați după coloană Column1 linii care conțin câmpurile de care avem nevoie: DTSTART, DTEND, DESCRIPTION, LOCATION și SUMMARY.
- În fila Avansat Adăugarea unei coloane alege Coloana index (Adăugați coloană — coloană Index)pentru a adăuga o coloană cu numărul de rând la datele noastre.
- Chiar acolo, pe filă. Adăugarea unei coloane alege o echipa Coloana condiționată (Adăugați coloană — coloană condiționată) iar la începutul fiecărui bloc (ordine) afișăm valoarea indicelui:
- Completați celulele goale din coloana rezultată Blocafăcând clic dreapta pe titlul acestuia și selectând comanda Completați (Completați).
- Eliminați coloana care nu este necesară index.
- Selectați o coloană Column1 și efectuați o convoluție a datelor din coloană Column2 folosind comanda Transformare – coloană pivot (Transformare — coloană Pivot). Asigurați-vă că selectați în opțiuni Nu agregați (Nu agregați)astfel încât nu se aplică nicio funcție matematică datelor:
- În tabelul bidimensional (cruce) rezultat, ștergeți barele oblice inverse din coloana cu adrese (faceți clic dreapta pe antetul coloanei - Înlocuirea valorilor) și eliminați coloana care nu este necesară Bloca.
- Pentru a întoarce conținutul coloanelor DTSTART и DTEND într-o dată completă, evidențiind-le, selectați pe filă Transformare – Data – Executare analiză (Transformare — Data — Analiză). Apoi corectăm codul din bara de formule prin înlocuirea funcției Data din on DateTime.Frompentru a nu pierde valorile timpului:
- Apoi, făcând clic dreapta pe antet, împărțim coloana DESCRIERE cu parametrii de ordine prin separator – simbol n, dar în același timp, în parametri, vom selecta împărțirea în rânduri, și nu în coloane:
- Încă o dată, împărțim coloana rezultată în două separate – parametrul și valoarea, dar după semnul egal.
- Selectarea unei coloane DESCRIERE.1 efectuați convoluția, așa cum am făcut mai devreme, cu comanda Transformare – coloană pivot (Transformare — coloană Pivot). Coloana cu valori în acest caz va fi coloana cu valorile parametrilor − DESCRIERE.2 Asigurați-vă că selectați o funcție în parametri Nu agregați (Nu agregați):
- Rămâne să setați formatele pentru toate coloanele și să le redenumiți după cum doriți. Și puteți încărca rezultatele înapoi în Excel cu comanda Acasă — Închideți și încărcați — Închideți și încărcați în... (Acasă — Închide&Încărcare — Închide&Încărcare la...)
Și iată lista noastră de comenzi încărcate în Excel din Google Calendar:
În viitor, la modificarea sau adăugarea de noi comenzi în calendar, va fi suficient să ne actualizăm solicitarea cu comanda Date – Actualizează toate (Date — Actualizează tot).
- Calendarul fabricii în Excel actualizat de pe internet prin Power Query
- Transformarea unei coloane într-un tabel
- Creați o bază de date în Excel