Mukati
Panenge pese kudzidziswa kwePower Query, kana tasvika pakugadzirisa mibvunzo yakagadzirwa uye vanhu vanoona kuti data nyowani inotsiva sei data rekare kana ichigadziridzwa, mumwe wevateereri anondibvunza: "zvinoita here kuve nechokwadi kuti kana uchigadziridza, data rekare uyewo kumwe kwakachengetwa uye nhoroondo yose yekuvandudza yaionekwa?
Pfungwa yacho haisi itsva uye mhinduro yakajairika kwairi ichave "kwete" - Power Query inogadziriswa nekusarudzika kutsiva yekare data neitsva (inodiwa muhuwandu hwezviitiko). Nekudaro, kana iwe uchida chaizvo, iwe unogona kutenderera ichi chinogumira. Uye iyo nzira, sezvauchaona gare gare, iri nyore kwazvo.
Funga nezve uyu muenzaniso unotevera.
Ngatifungei kuti isu tine faira kubva kumutengi se data yekupinza (ngatiidaidze, ngatiti, mabviro) ine runyoro rwezvigadzirwa zvaanoda kutenga nenzira ye "smart" inoshanduka tafura yakatumidzwa kunyoresa:
Mune imwe faira (ngatidaidzei nekuenzanisa mugamuchiri) isu tinogadzira mubvunzo wakareruka kupinza tafura ine zvigadzirwa kubva kuSisi kuburikidza Dhata - Tora Dhata - Kubva Faira - Kubva kuExcel Workbook (Data - Tora data - Kubva faira - Kubva kuExcel bhuku rebasa) uye rodha tafura inoguma kune pepa:
Kana mune ramangwana mutengi akasarudza kuita shanduko kune kurongeka mufaira rake mabviro, zvino mushure mekugadzirisa chikumbiro chedu (nekudzvanya-kurudyi kana kuburikidza Dhata - Zorora Zvese) tichaona iyo data nyowani mufaira mugamuchiri - ese standard.
Zvino ngative nechokwadi chekuti kana tichivandudza, data rekare haritsiviwa nemamwe matsva, asi matsva anowedzerwa kune ekare - uye nekuwedzera kwezuva-nguva, kuitira kuti ionekwe paiyi shanduko chaiyo yakaitwa. made.
Danho 1. Kuwedzera zuva-nguva kumubvunzo wekutanga
Ngativhure chikumbiro kunyoresakuunza data redu kubva mabviro, uye wedzera koramu ine zuva-nguva yekuvandudza kwairi. Kuti uite izvi, unogona kushandisa bhatani Custom column Tab Kuwedzera chikamu (Wedzera koramu - Tsika mbiru), uye wobva wapinda basa DateTime.LocalNow - analogue yebasa Iye TDATA (ZVINO) muMicrosoft Excel:
Mushure mekunge mavharidza OK iwe unofanirwa kupedzisira uine koramu yakanaka senge iyi (usakanganwe kuseta iyo-yenguva fomati yayo neiyo icon mumusoro wekoramu):
Kana iwe uchida, saka kune ndiro yakarongedzerwa papepa reiyi koramu, unogona kuseta iyo yemazuva-nguva fomati nemasekonzi ekunyatso kurongeka (iwe uchafanirwa kuwedzera colon uye "ss" kune yakajairwa fomati):
Nhanho 2: Kubvunza kune yekare data
Zvino ngatigadzire mumwe mubvunzo uchaita sebhafa inochengetedza data rekare tisati tagadziridza. Kusarudza chero sero yetafura inobuda mufaira mugamuchiri, sarudza pane tab Data murayiro Kubva Tafura/Range (Data - Kubva patafura / renji) or Nemashizha (Kubva pepa):
Hatiite chinhu netafura yakarongedzerwa muSimba Query, isu tinodaidza mubvunzo, semuenzaniso, data yekare uye tigoshingairira Musha — Vhara uye rongedza — Vhara uye rongedza ku… — Gadzira chinongedzo chete (Kumba — Vhara & Rodha — Vhara&Rodha ku… — Gadzira chinongedzo chete).
Danho 3. Kujoinha yekare uye itsva data
Zvino dzokera kumubvunzo wedu wekutanga kunyoresa uye wedzera kwairi kubva pazasi data rekare kubva kune yapfuura buffer chikumbiro nemurairo Kumba - Wedzera Zvikumbiro (Kumba - Wedzera Mibvunzo):
Ndizvo zvose!
Inosara kudzokera kuExcel kuburikidza Musha - Vhara uye dhawunirodha (Kumba - Vhara & Rodha) uye edza kanoverengeka kugadzirisa chimiro chedu chese nebhatani Gadzirisa Zvese Tab Data (Data - Zorora Zvese). Nekuvandudza kwega kwega, iyo data nyowani haizotsiva iyo yekare data, asi ichaisundira pazasi, ichichengeta iyo nhoroondo yese yekuvandudza:
Hunyengeri hwakafanana hunogona kushandiswa kana uchiunza kunze kubva kune chero ekunze masosi (Internet mawebhusaiti, dhatabhesi, ekunze mafaera, nezvimwewo) kuchengetedza tsika dzekare dzenhoroondo kana uchiida.
- Pivot tafura pane akawanda data renji
- Kuunganidza matafura kubva akasiyana mafaera uchishandisa Power Query
- Kuunganidza data kubva pamapepa ese ebhuku mune imwe tafura