Zamkatimu
Njira zambiri zamabizinesi (komanso mabizinesi onse) m'moyo uno zimaphatikizapo kukwaniritsidwa kwa madongosolo ndi owerengeka ochepa pofika tsiku lomaliza. Kukonzekera muzochitika zotere kumachitika, monga amanenera, "kuchokera pa kalendala" ndipo nthawi zambiri pamafunika kusamutsa zochitika zomwe zakonzedwa mmenemo (maoda, misonkhano, zobweretsera) ku Microsoft Excel - kuti mufufuze mozama ndi ma formula, ma pivot tables, charting, ndi zina.
Zachidziwikire, ndikufuna kugwiritsa ntchito kusamutsa koteroko osati kukopera kopusa (komwe sikuli kovuta), koma ndikungosintha zokha za data kuti mtsogolomo zosintha zonse zomwe zapangidwa pakalendala ndi madongosolo atsopano pa ntchentche ziziwonetsedwa. Excel. Mutha kukhazikitsa kuitanitsa koteroko pakangopita mphindi pang'ono pogwiritsa ntchito Power Query add-in yomangidwa mu Microsoft Excel, kuyambira mtundu wa 2016 (wa Excel 2010-2013, itha kutsitsidwa patsamba la Microsoft ndikuyika padera ndi ulalo) .
Tiyerekeze kuti timagwiritsa ntchito Google Calendar yaulere pokonzekera, momwe ine, kuti zithandizire, ndidapanga kalendala yosiyana (batani lokhala ndi chizindikiro chowonjezera pakona yakumanja yakumanja pafupi ndi Makalendala ena) ndi mutu ntchito. Apa tikulowetsa maoda onse omwe akuyenera kumalizidwa ndikuperekedwa kwa makasitomala pama adilesi awo:
Podina kawiri kuyitanitsa kulikonse, mutha kuwona kapena kusintha zambiri zake:
Zindikirani kuti:
- Dzina la chochitikacho ndi bwanaamene amakwaniritsa dongosolo ili (Elena) ndi Nambala yogulira
- Zatsimikizika adiresi yobereka
- Cholembacho chili (m'mizere yosiyana, koma mwanjira iliyonse) magawo oyitanitsa: mtundu wamalipiro, kuchuluka, dzina lamakasitomala, etc. Parameter=Mtengo.
Kuti zimveke bwino, malamulo a manejala aliyense amawonetsedwa mumtundu wawo, ngakhale izi sizofunikira.
Gawo 1. Pezani ulalo Google Calendar
Choyamba tiyenera kupeza ulalo wapaintaneti ku kalendala yathu yoyitanitsa. Kuti muchite izi, dinani batani lomwe lili ndi madontho atatu Zosankha za Kalendala Zimagwira Ntchito pafupi ndi dzina la kalendala ndikusankha lamulo Zokonda ndi Kugawana:
Pazenera lomwe limatsegulidwa, mutha, ngati mungafune, kupanga kalendala pagulu kapena kutsegula mwayi kwa ogwiritsa ntchito aliyense. Tikufunanso ulalo wofikira mwachinsinsi pa kalendala mu mtundu wa iCal:
Gawo 2. Kwezani deta kuchokera kalendala mu Power Query
Tsopano tsegulani Excel ndi tabu Deta (ngati muli ndi Excel 2010-2013, ndiye pa tabu Kufunsa Mphamvu) sankhani lamulo Kuchokera pa intaneti (Deta - Kuchokera pa intaneti). Kenako ikani njira yojambulidwa ku kalendala ndikudina OK.
ICal Power Query sichizindikira mawonekedwe, koma ndiyosavuta kuthandiza. Kwenikweni, iCal ndi fayilo yomveka bwino yokhala ndi colon ngati delimiter, ndipo mkati mwake imawoneka motere:
Kotero inu mukhoza basi-alemba-kumanja pa chithunzi cha dawunilodi wapamwamba ndi kusankha mtundu kuti ali pafupi tanthauzo CSV - ndipo zambiri zathu zamaoda onse zidzakwezedwa mu Power Query query editor ndikugawa magawo awiri ndi colon:
Ngati muyang'anitsitsa, mukhoza kuona kuti:
- Zambiri za chochitika chilichonse (kuyitanitsa) zimasanjidwa mumdawu woyambira ndi mawu akuti BEGIN ndi kutha ndi END.
- Nthawi zoyambira ndi zomaliza zimasungidwa muzingwe zolembedwa DTSTART ndi DTEND.
- Adilesi yotumizira ndi LOCATION.
- Cholembera - DESCRIPTION gawo.
- Dzina la chochitika (dzina la woyang'anira ndi nambala ya dongosolo) - SUMMARY field.
Zimakhalabe kuchotsa zambiri zothandizazi ndikuzisintha kukhala tebulo losavuta.
Gawo 3. Sinthani kukhala Normal View
Kuti muchite izi, chitani zotsatirazi:
- Tiyeni tifufute mizere 7 yapamwamba yomwe sitikufuna isanayambe lamulo loyamba Kunyumba - Chotsani Mizere - Chotsani Mizere Yapamwamba (Kunyumba - Chotsani mizere - Chotsani mizere yapamwamba).
- Sefa ndi chigawo Column1 mizere yokhala ndi magawo omwe tikufuna: DTSTART, DTEND, DESCRIPTION, LOCATION ndi SUMMARY.
- Pa Advanced tabu Kuwonjezera ndime kusankha Chigawo cha index (Onjezani ndime - index column)kuwonjezera mzere wa nambala ku data yathu.
- Pomwepo pa tabu. Kuwonjezera ndime sankhani gulu Conditional column (Onjezani gawo - Conditional column) ndipo kumayambiriro kwa chipika chilichonse (dongosolo) tikuwonetsa mtengo wa index:
- Lembani ma cell opanda kanthu mugawo lotsatira Dulanipodina kumanja pamutu wake ndikusankha lamulo Dzazani - Pansi (Dzazani - Pansi).
- Chotsani gawo losafunika Index.
- Sankhani gawo Column1 ndi kupanga convolution wa deta kuchokera pagawo Column2 pogwiritsa ntchito lamulo Sinthani - Pivot Column (Sinthani - gawo la Pivot). Onetsetsani kuti mwasankha muzosankha Osaphatikiza (Osaphatikiza)kotero kuti palibe masamu omwe amagwiritsidwa ntchito pa data:
- Pazotsatira zamitundu iwiri (mtanda), chotsani zobwerera m'mbuyo mugawo la adilesi (dinani kumanja pamutu wandalama - Kusintha makhalidwe) ndikuchotsa mzati wosafunikira Dulani.
- Kutembenuza zomwe zili m'mizere DTSTART и DTEND mu nthawi yathunthu, kuwawunikira, sankhani pa tabu Kusintha – Tsiku – Thamangani Analysis (Sinthani - Date - Parse). Kenako timakonza kachidindo mu bar ya formula posintha ntchitoyo Tsiku.Kuchokera on DateTime.Kuchokerakuti musataye zikhalidwe za nthawi:
- Kenako, podina kumanja pamutu, timagawaniza gawolo DESCRIPTION ndi magawo a dongosolo ndi olekanitsa - chizindikiro n, koma nthawi yomweyo, mu magawo, tidzasankha magawano m'mizere, osati m'mizere:
- Apanso, timagawaniza gawolo kukhala magawo awiri osiyana - parameter ndi mtengo, koma ndi chizindikiro chofanana.
- Kusankha ndime MAWU OLANKHULIDWA.1 kuchita convolution, monga tinachitira poyamba, ndi lamulo Sinthani - Pivot Column (Sinthani - gawo la Pivot). Mzere wamtengo pankhaniyi udzakhala ndime yokhala ndi magawo - MAWU OLANKHULIDWA.2 Onetsetsani kuti mwasankha ntchito mu magawo Osaphatikiza (Osaphatikiza):
- Zimatsalira kukhazikitsa mafomu a mizati yonse ndikuwatchanso monga momwe mukufunira. Ndipo mutha kukweza zotsatira kubwerera ku Excel ndi lamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…)
Ndipo nayi mndandanda wamaoda omwe adayikidwa mu Excel kuchokera ku Google Calendar:
M'tsogolomu, posintha kapena kuwonjezera madongosolo atsopano ku kalendala, zidzakhala zokwanira kukonzanso pempho lathu ndi lamulo. Deta - Tsitsani Zonse (Deta - Tsitsani Zonse).
- Kalendala ya fakitale ku Excel yosinthidwa kuchokera pa intaneti kudzera pa Power Query
- Kusintha gawo kukhala tebulo
- Pangani database mu Excel