Kwesinye isikhathi kuba nezimo lapho kungaziwa kusengaphambili ukuthi mingaki kanye nokuthi yimiphi imigqa edinga ukungeniswa kusuka kudatha yomthombo. Ake sithi kufanele silayishe idatha kusuka efayeleni lombhalo ku-Power Query, okuthi, uma uthi nhlá, ingethuki inkinga enkulu. Ubunzima ukuthi ifayela livuselelwa njalo, futhi kusasa lingase libe nenombolo ehlukile yemigqa enedatha, unhlokweni wabathathu, hhayi imigqa emibili, njll.:
Okungukuthi, asikwazi ukusho kusengaphambili ngokuqiniseka, siqala kumuphi umugqa kanye nokuthi zingaki imigqa edinga ukungeniswa kwamanye amazwe. Futhi lokhu kuyinkinga, ngoba le mingcele inekhodi eliqinile ku-M-code yesicelo. Futhi uma wenza isicelo sefayela lokuqala (ukungenisa imigqa emi-5 kusukela kowe-4), ngeke lisasebenza kahle nelesibili.
Kungaba kuhle uma umbuzo wethu unganquma isiqalo nesiphetho sebhulokhi yombhalo "entantayo" yokungenisa.
Isixazululo engifuna ukusiphakamisa sisekelwe embonweni wokuthi idatha yethu iqukethe amagama angukhiye noma amanani angasetshenziswa njengomaka (izici) zesiqalo nesiphetho sebhulokhi yedatha esiyidingayo. Esibonelweni sethu, isiqalo sizoba umugqa oqala ngegama SKU, futhi isiphetho siwumugqa onegama Ubude. Lokhu kuqinisekiswa komugqa kulula ukukusebenzisa kokuthi Umbuzo Wamandla usebenzisa ikholomu enemibandela - i-analogue yomsebenzi IF (UMA) ku-Microsoft Excel.
Ake sibone ukuthi sikwenza kanjani.
Okokuqala, ake silayishe okuqukethwe kwefayela lethu lombhalo kokuthi Umbuzo Wamandla ngendlela evamile - ngomyalo Idatha - Thola idatha - Kusuka kufayela - Kusuka kumbhalo / ifayela le-CSV (Idatha - Thola Idatha - Kusuka kufayela - Kusuka kumbhalo / ifayela le-CSV). Uma Unombuzo Wamandla ofakiwe njengesengezo esihlukile, imiyalo ehambisanayo izoba kuthebhu Umbuzo Wamandla:
Njengokuvamile, lapho ungenisa, ungakhetha uhlamvu lwesihlukanisi sekholomu (kithi, lena ithebhu), futhi ngemva kokungenisa, ungasusa isinyathelo esengeziwe ngokuzenzakalelayo. uhlobo olushintshiwe (Uhlobo Olushintshiwe), ngoba kusesekuseni kakhulu ukuthi sinikeze izinhlobo zedatha kumakholomu:
Manje ngomyalo Ukwengeza Ikholomu – Ikholomu Enemibandela (Engeza Ikholomu — Ikholomu Enemibandela)ake sengeze ikholomu ngokubheka izimo ezimbili - ekuqaleni nasekupheleni kwebhulokhi - futhi sibonise noma yimaphi amanani ahlukene esimweni ngasinye (isibonelo, izinombolo 1 и 2). Uma kungekho neyodwa imibandela efinyelelwayo, bese uyakhipha null:
Ngemuva kokuchofoza OK sithola isithombe esilandelayo:
Manje ake siye kuthebhu. Uguquko bese ukhetha iqembu Gcwalisa - Phansi (Guqula – Gcwalisa – Phansi) - ezethu nezimbili zizokwelula ikholomu:
Ngakho-ke, njengoba ungase uqagele, ungavele uhlunge amayunithi kukholamu enemibandela - futhi nali ucezu lwethu lwedatha esilulangazelelayo:
Okusele nje ukuphakamisa umugqa wokuqala kunhlokweni ngomyalo Sebenzisa umugqa wokuqala njengezihloko ithebhu Ikhaya (Ekhaya - Sebenzisa Umugqa Wokuqala Njengezihloko) bese ususa ikholomu enemibandela eyengeziwe engadingekile ngokuchofoza kwesokudla kusihloko sayo nokukhetha umyalo Susa ikholomu (Susa Ikholomu):
Inkinga ixazululiwe. Manje, lapho ushintsha idatha kufayela lombhalo womthombo, umbuzo manje uzonquma ngokuzimela isiqalo nesiphetho sesiqephu "esintantayo" sedatha esiyidingayo futhi singenise inombolo elungile yemigqa isikhathi ngasinye. Yiqiniso, le ndlela iyasebenza futhi esimweni sokungenisa i-XLSX, hhayi amafayela e-TXT, kanye nalapho ungenisa wonke amafayela kufolda ngesikhathi esisodwa ngomyalo. Idatha - Thola idatha - Kusuka kufayela - Kusuka kufolda (Idatha - Thola Idatha - Kusuka kufayela - Kusuka kufolda).
- Ukuhlanganisa amatafula avela kumafayela ahlukene kusetshenziswa Umbuzo Wamandla
- Idizayina kabusha i-crosstab ukuze isicaba ngamamakhro kanye Nombuzo Wamandla
- Ukwakha Ishadi Lephrojekthi Ye-Gantt Embuzweni Wamandla