Zamkatimu
Pafupifupi maphunziro onse a Power Query, tikafika pa momwe mungasinthire mafunso omwe adapangidwa ndipo anthu amawona momwe deta yatsopano imasinthira deta yakale pokonzanso, m'modzi mwa omvera amandifunsa kuti: "Kodi ndizotheka kuwonetsetsa kuti pokonzanso, deta yakale ndi komanso kwinakwake zidasungidwa ndipo mbiri yonse yosinthika idawoneka?
Lingaliro siliri lachilendo ndipo yankho lokhazikika kwa ilo lidzakhala "ayi" - Funso la Mphamvu limakonzedwa mwachisawawa kuti lisinthe deta yakale ndi zatsopano (zomwe zimafunika nthawi zambiri). Komabe, ngati mukufunadi, mutha kudutsa malire awa. Ndipo njira, monga muwona pambuyo pake, ndiyosavuta.
Taonani chitsanzo ichi.
Tiyerekeze kuti tili ndi fayilo yochokera kwa kasitomala ngati data yolowera (tiyeni tiyitchule, tinene, gwero) ndi mndandanda wazinthu zomwe akufuna kugula mu mawonekedwe a tebulo lamphamvu la "anzeru" lotchedwa ntchito:
Mu fayilo ina (tiyeni tiyitchule mofananiza wolandila) timapanga funso losavuta kuitanitsa tebulo ndi zinthu zochokera ku Source kudzera Deta - Pezani Zambiri - Kuchokera Fayilo - Kuchokera ku Excel Workbook (Deta - Pezani zambiri - Kuchokera pafayilo - Kuchokera ku buku la Excel) ndikukweza tebulo lotsatira papepala:
Ngati m'tsogolo kasitomala asankha kusintha dongosolo mu fayilo yake gwero, ndiye mutasintha pempho lathu (podina kumanja kapena kudzera Deta - Tsitsani Zonse) tidzawona deta yatsopano mufayilo wolandila - zonse muyezo.
Tsopano tiyeni tiwonetsetse kuti pokonzanso, deta yakale siisinthidwa ndi zatsopano, koma zatsopano zimawonjezeredwa ku zakale - komanso ndi kuwonjezera kwa nthawi ya tsiku, kuti ziwoneke pamene kusintha kwapadera kumeneku kunachitika. zopangidwa.
Khwerero 1. Powonjezera tsiku la funso loyamba
Tiyeni titsegule pempho ntchitokuitanitsa deta yathu kuchokera gwero, ndikuwonjezera gawo lokhala ndi tsiku lakusintha kwa icho. Kuti muchite izi, mutha kugwiritsa ntchito batani Mzati mwamakonda tsamba Kuwonjezera ndime (Onjezani gawo - Mzere Wamakonda), ndiyeno lowetsani ntchito DateTime.LocalNow - analogue ya ntchito Mtengo TDATA (TSOPANO) mu Microsoft Excel:
Pambuyo pang'anani OK muyenera kukhala ndi gawo lokongola ngati ili (musaiwale kukhazikitsa mtundu wanthawi yake ndi chithunzi chomwe chili pamutu wagawo):
Ngati mukufuna, ndiye kuti mbale yomwe idakwezedwa patsamba ili, mutha kuyika mawonekedwe anthawi ndi masekondi kuti muwonetsetse bwino (muyenera kuwonjezera colon ndi "ss" pamtundu wokhazikika):
Khwerero 2: Funsani za data yakale
Tsopano tiyeni tipange funso lina lomwe lingakhale ngati buffer lomwe limasunga zakale musanasinthe. Kusankha selo lililonse lazotsatira zomwe zili mufayilo wolandila, sankhani pa tabu Deta lamulo Kuchokera pa Table/Range (Deta - Kuchokera patebulo/mitundu) or Ndi masamba (Kuchokera pa pepala):
Sitichita chilichonse ndi tebulo lodzaza mu Power Query, timatcha funso, mwachitsanzo, data yakale Ndi kukanikiza Kunyumba — Tsekani ndikutsegula — Tsekani ndikutsegula ku… — Pangani kulumikizana kokha (Kunyumba — Tsekani & Katundu — Tsekani & Kwezani ku… — Pangani kulumikizana kokha).
Khwerero 3. Kujowina deta yakale ndi yatsopano
Tsopano bwererani ku funso lathu loyambirira ntchito ndikuwonjezerapo kuchokera pansi pa data yakale kuchokera ku pempho lakale la buffer ndi lamulo Kunyumba - Onjezani Zopempha (Kunyumba - Onjezerani Mafunso):
Ndizomwezo!
Zimatsalira kubwerera ku Excel kudzera Kunyumba - Tsekani ndikutsitsa (Kunyumba - Tsekani & Katundu) ndipo yesani kangapo kuti musinthe mawonekedwe athu onse ndi batani Sinthani Zonse tsamba Deta (Deta - Tsitsani Zonse). Ndikusintha kulikonse, zatsopano sizidzalowa m'malo mwa data yakale, koma zidzakankhira pansipa, ndikusunga mbiri yonse yosinthira:
Chinyengo chofananira chitha kugwiritsidwa ntchito potumiza kuchokera kuzinthu zilizonse zakunja (mawebusayiti, ma database, mafayilo akunja, ndi zina zambiri) kuti musunge mbiri yakale ngati mukufuna.
- Pivot tebulo pamitundu ingapo ya data
- Kusonkhanitsa matebulo kuchokera kumafayilo osiyanasiyana pogwiritsa ntchito Power Query
- Kusonkhanitsa deta kuchokera pamasamba onse a bukhulo mu tebulo limodzi