Sisällys
Monet liiketoimintaprosessit (ja jopa kokonaiset yritykset) tässä elämässä edellyttävät tilausten täyttämistä rajoitetulla määrällä esiintyjiä tiettyyn määräaikaan mennessä. Suunnittelu tapahtuu tällaisissa tapauksissa, kuten sanotaan, "kalenterista" ja usein joudutaan siirtämään siihen suunnitellut tapahtumat (tilaukset, kokoukset, toimitukset) Microsoft Exceliin - jatkoanalyysiä varten kaavoilla, pivot-taulukoilla, kaavioilla, jne.
Tietenkin haluaisin toteuttaa tällaista siirtoa ei typerällä kopioimalla (mikä ei vain ole vaikeaa), vaan automaattisella tietojen päivityksellä, jotta tulevaisuudessa kaikki kalenteriin tehdyt muutokset ja uudet tilaukset näkyisivät Excel. Voit toteuttaa tällaisen tuonnin muutamassa minuutissa Microsoft Exceliin sisäänrakennetulla Power Query -apuohjelmalla 2016-versiosta alkaen (Excel 2010-2013:lle se voidaan ladata Microsoftin verkkosivuilta ja asentaa erikseen linkistä) .
Oletetaan, että käytämme suunnitteluun ilmaista Google-kalenteria, johon loin mukavuussyistä erillisen kalenterin (oikeassa alakulmassa plus-merkkinen painike Muut kalenterit) otsikolla Referenssit. Tänne kirjoitamme kaikki tilaukset, jotka on täytettävä ja toimitettava asiakkaille heidän osoitteisiinsa:
Kaksoisnapsauttamalla mitä tahansa tilausta voit tarkastella tai muokata sen tietoja:
Ota huomioon, että:
- Tapahtuman nimi on johtajajoka täyttää tämän tilauksen (Elena) ja Tilausnumero
- Ilmoitettu osoite toimitus
- Huomautus sisältää (erillisillä riveillä, mutta missä tahansa järjestyksessä) tilauksen parametrit: maksutapa, summa, asiakkaan nimi jne. muodossa Parametri = Arvo.
Selvyyden vuoksi kunkin esimiehen tilaukset on korostettu omalla värillään, vaikka tämä ei ole välttämätöntä.
Vaihe 1. Hanki linkki Google-kalenteriin
Ensin meidän on saatava verkkolinkki tilauskalenterimme. Voit tehdä tämän napsauttamalla painiketta, jossa on kolme pistettä Kalenterin asetukset toimivat kalenterin nimen vieressä ja valitse komento Asetukset ja jakaminen:
Avautuvassa ikkunassa voit halutessasi tehdä kalenterin julkiseksi tai avata sen pääsyn yksittäisille käyttäjille. Tarvitsemme myös linkin kalenterin yksityiseen käyttöön iCal-muodossa:
Vaihe 2. Lataa tiedot kalenterista Power Queryyn
Avaa nyt Excel ja välilehti Päiväys (jos sinulla on Excel 2010-2013, välilehdellä Tehokysely) valitse komento Internetistä (Tiedot – Internetistä). Liitä sitten kopioitu polku kalenteriin ja napsauta OK.
iCal Power Query ei tunnista muotoa, mutta sitä on helppo auttaa. Pohjimmiltaan iCal on pelkkä tekstitiedosto, jonka erottimena on kaksoispiste ja jonka sisällä se näyttää suunnilleen tältä:
Joten voit napsauttaa hiiren kakkospainikkeella ladatun tiedoston kuvaketta ja valita merkitykseltään lähimmän muodon CSV – ja tietomme kaikista tilauksista ladataan Power Queryn kyselyeditoriin ja jaetaan kahteen sarakkeeseen kaksoispisteellä:
Jos katsot tarkkaan, näet selvästi, että:
- Jokaisen tapahtuman (tilauksen) tiedot on ryhmitelty lohkoon, joka alkaa sanalla BEGIN ja päättyy sanaan END.
- Aloitus- ja lopetuspäivämäärät tallennetaan merkkijonoihin DTSTART ja DTEND.
- Toimitusosoite on LOCATION.
- Tilaushuomautus – DESCRIPTION-kenttä.
- Tapahtuman nimi (johtajan nimi ja tilausnumero) — YHTEENVETO-kenttä.
On vielä purettava tämä hyödyllinen tieto ja muutettava se käteväksi taulukoksi.
Vaihe 3. Muunna normaalinäkymään
Voit tehdä tämän suorittamalla seuraavan toimintoketjun:
- Poistetaan 7 ylintä riviä, joita emme tarvitse ennen ensimmäistä BEGIN-komentoa Etusivu — Poista rivit — Poista ylimmät rivit (Etusivu — Poista rivit — Poista ylimmät rivit).
- Suodata sarakkeen mukaan Column1 rivit, jotka sisältävät tarvitsemamme kentät: DTSTART, DTEND, DESCRIPTION, LOCATION ja SUMMARY.
- Lisäasetukset -välilehdellä Sarakkeen lisääminen valita Hakemistosarake (Lisää sarake — Hakemistosarake)lisätäksesi tietoihimme rivinumerosarakkeen.
- Tuossa välilehdellä. Sarakkeen lisääminen valitse joukkue Ehdollinen sarake (Lisää sarake — ehdollinen sarake) ja jokaisen lohkon (järjestyksen) alussa näytämme indeksin arvon:
- Täytä tyhjät solut tuloksena olevaan sarakkeeseen Tukkianapsauttamalla hiiren oikealla painikkeella sen otsikkoa ja valitsemalla komennon Täytä (Täytä).
- Poista tarpeeton sarake indeksi.
- Valitse sarake Column1 ja suorita sarakkeen tietojen konvoluutio Column2 komentoa käyttämällä Muunnos – Pivot Column (Muunnosta – Pivot-sarake). Muista valita vaihtoehdoista Älä yhdistä (Älä yhdistä)jotta dataan ei sovelleta matemaattista funktiota:
- Tyhjennä tuloksena olevasta kaksiulotteisesta (risti)taulukosta kenoviivat osoitesarakkeesta (klikkaa hiiren kakkospainikkeella sarakkeen otsikkoa - Korvaa arvoja) ja poista tarpeeton sarake Tukkia.
- Sarakkeiden sisällön kääntäminen DTSTART и DTEND koko päivämäärä-aika korostaen ne, valitse välilehdeltä Muunna – Päivämäärä – Suorita analyysi (Muunna — Päivämäärä — Jäsenny). Sitten korjaamme kaavapalkin koodin korvaamalla funktion Päivämäärä.Alkaen on DateTime.Fromjotta et menetä aika-arvoja:
- Sitten jaamme sarakkeen napsauttamalla otsikkoa hiiren kakkospainikkeella KUVAUS tilausparametreilla erottimella – symbolilla n, mutta samalla valitsemme parametreissa jaon riveihin, emme sarakkeisiin:
- Jälleen kerran jaamme tuloksena olevan sarakkeen kahteen erilliseen sarakkeeseen - parametriin ja arvoon, mutta yhtäsuuruusmerkillä.
- Sarakkeen valitseminen KUVAUS.1 suorita konvoluutio, kuten teimme aiemmin, komennolla Muunnos – Pivot Column (Muunnosta – Pivot-sarake). Arvosarake on tässä tapauksessa sarake, jossa on parametriarvot - KUVAUS.2 Muista valita toiminto parametreista Älä yhdistä (Älä yhdistä):
- On vielä määritettävä kaikkien sarakkeiden muodot ja nimettävä ne uudelleen haluamallasi tavalla. Ja voit ladata tulokset takaisin Exceliin komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa...)
Ja tässä on luettelo Google-kalenterista Exceliin ladatuista tilauksista:
Jatkossa kalenteriin tilauksia muutettaessa tai uusia tilauksia lisättäessä riittää, että päivität pyyntömme komennolla Tiedot – Päivitä kaikki (Data – Päivitä kaikki).
- Tehdaskalenteri Excelissä päivitetty netistä Power Queryn kautta
- Sarakkeen muuntaminen taulukoksi
- Luo tietokanta Excelissä