Okuqukethwe
Cishe kukho konke ukuqeqeshwa kwe-Power Query, lapho sifika ekutheni ungayibuyekeza kanjani imibuzo edaliwe futhi abantu babone ukuthi idatha entsha ithatha indawo yedatha endala lapho ibuyekeza, omunye wabalaleli uyangibuza: "kungenzeka yini ukwenza isiqiniseko sokuthi lapho ubuyekeza, idatha endala futhi ndawana thize zalondolozwa futhi wonke umlando wokubuyekeza wawubonakala?
Umbono awumusha futhi impendulo evamile kuwo izoba "cha" - Umbuzo Wamandla uhlelwa ngokuzenzakalelayo ukuze umiselele idatha endala ngemisha (okudingeka kuningi lamacala). Nokho, uma ufuna ngempela, ungakwazi ukuzungeza lo mkhawulo. Futhi indlela, njengoba uzobona kamuva, ilula kakhulu.
Cabanga ngalesi sibonelo esilandelayo.
Ake sicabange ukuthi sinefayela elivela kuklayenti njengedatha yokufaka (asiyibize, ake sithi, Umthombo) enohlu lwemikhiqizo afuna ukuyithenga ngendlela yethebula eliguquguqukayo “elihlakaniphile” eliqanjwe igama Isicelo:
Kwelinye ifayela (ake silibize ngesifaniso Umamukeli) sakha umbuzo olula wokungenisa ithebula elinemikhiqizo evela kuMthombo nge Idatha - Thola Idatha - Kusuka Efayeleni - Ku-Excel Workbook (Idatha - Thola idatha - Kusuka kufayela - Kusuka encwadini yokusebenzela ye-Excel) bese ulayisha ithebula eliwumphumela eshidini:
Uma esikhathini esizayo iklayenti inquma ukwenza izinguquko ku-oda efayeleni layo Umthombo, ngemva kokubuyekeza isicelo sethu (ngokuchofoza kwesokudla noma nge Idatha - Vuselela Konke) sizobona idatha entsha kufayela Umamukeli - konke okujwayelekile.
Manje ake siqinisekise ukuthi lapho sibuyekeza, idatha endala ayithathelwa indawo entsha, kodwa entsha yenezelwa kwezindala - futhi nokwengezwa kwesikhathi sosuku, ukuze kubonakale ukuthi lezi zinguquko ezithile zenziwa nini. yenziwe.
Isinyathelo 1. Ukwengeza isikhathi sosuku embuzweni wokuqala
Asivule isicelo Isiceloukungenisa idatha yethu kusuka Umthombo, bese wengeza ikholomu enedethi yesikhathi sokubuyekeza kuyo. Ukuze wenze lokhu, ungasebenzisa inkinobho Ikholomu yangokwezifiso ithebhu Yengeza ikholomu (Engeza ikholomu — Ikholomu yangokwezifiso), bese ufaka umsebenzi I-DateTime.LocalNow – i-analogue yomsebenzi Idatha ye-TDATA (MANJE) ku-Microsoft Excel:
Ngemuva kokuchofoza OK kufanele ugcine unekholomu enhle kanje (ungakhohlwa ukusethela ifomethi yesikhathi sosuku ngesithonjana esikusihloko sekholomu):
Uma ufuna, khona-ke epuletini elilayishwe eshidini lale kholomu, ungasetha ifomethi yesikhathi sosuku ngamasekhondi ukuze uthole ukunemba okukhulu (kuzodingeka wengeze ikholoni kanye nokuthi “ss” kufomethi evamile):
Isinyathelo sesi-2: Umbuzo ngedatha endala
Manje ake sakhe omunye umbuzo ozosebenza njengesigcinalwazi esigcina idatha endala ngaphambi kokubuyekeza. Ukukhetha noma iyiphi iseli yethebula elingumphumela efayelini Umamukeli, khetha kuthebhu Idatha Umyalo Kusukela kuThebula/Ububanzi (Idatha - Kusuka kuthebula/ububanzi) or Ngamaqabunga (Kusuka eshidini):
Asenzi lutho ngetafula elilayishwe ku-Power Query, sibiza umbuzo, isibonelo, idatha endala bese ucindezela Ikhaya — Vala bese ulayisha — Vala bese ulayisha ku… — Dala ukuxhumana kuphela (Ekhaya — Vala&Layisha — Vala&Layisha ku… — Dala ukuxhumana kuphela).
Isinyathelo sesi-3. Ukujoyina idatha endala nentsha
Manje buyela embuzweni wethu wasekuqaleni Isicelo bese wengeza kuyo kusuka ngezansi kwedatha endala kusuka esicelweni sebhafa sangaphambilini ngomyalo Ikhaya - Faka izicelo (Ekhaya - Faka imibuzo):
Yilokho kuphela!
Kusele ukubuyela ku-Excel ngokusebenzisa Ikhaya — Vala bese ulanda (Ekhaya — Vala&Layisha) bese uzama izikhathi ezimbalwa ukubuyekeza sonke isakhiwo sethu ngenkinobho Vuselela konke ithebhu Idatha (Idatha — Vuselela Konke). Ngesibuyekezo ngasinye, idatha entsha ngeke ingene esikhundleni sedatha endala, kodwa izoyiphusha ngezansi, igcina wonke umlando wokubuyekeza:
Iqhinga elifanayo lingasetshenziswa lapho ungenisa kusuka kunoma yimiphi imithombo yangaphandle (izingosi ze-inthanethi, isizindalwazi, amafayela angaphandle, njll.) ukuze ugcine amanani amadala omlando uma uwadinga.
- Ithebula le-Pivot kububanzi bedatha eminingi
- Ukuhlanganisa amatafula avela kumafayela ahlukene kusetshenziswa Umbuzo Wamandla
- Iqoqa idatha kusuka kuwo wonke amashidi encwadi etafuleni elilodwa