Tse ka Hare
Haeba u se u qalile ho sebelisa lisebelisoa tsa kenyelletso ea mahala ea Power Query ho Microsoft Excel, joale haufinyane u tla kopana le bothata bo ikhethang, empa khafetsa le bo tenang bo amanang le ho roba likhokahano khafetsa tsa data ea mohloli. Moko oa bothata ke hore haeba potsong ea hau u bua ka lifaele kapa lifoldara tse kantle, Power Query e tiisa tsela e felletseng ea ho ea ho bona mongolong oa potso. Ntho e 'ngoe le e' ngoe e sebetsa hantle khomphuteng ea hau, empa haeba u etsa qeto ea ho romela faele ka kopo ho basebetsi-'moho le uena, joale ba tla soetseha, hobane. ba na le tsela e fapaneng ea mohloli oa data khomphuteng ea bona, 'me potso ea rona e ke ke ea sebetsa.
Seo u lokelang ho se etsa boemong bo joalo? Ha re shebeng nyeoe ena ka botlalo ka mohlala o latelang.
Tlhahiso ea bothata
A re re re na le foldareng E:Litlaleho tsa thekiso leshano file Lihlahisoa tse 100 tse holimo.xls, e leng ho romelloa ho tsoa ho database ea rona ea lik'hamphani kapa tsamaiso ea ERP (1C, SAP, joalo-joalo) Faele ena e na le tlhahisoleseding e mabapi le lintho tse ratoang ka ho fetisisa tsa thepa 'me e shebahala tjena ka hare:
Ho ka etsahala hore ebe ho hlakile hantle hore ho ke ke ha khoneha ho sebetsa le eona Excel ka mokhoa ona: mela e se nang letho ho pholletsa le e 'ngoe e nang le data, lisele tse kopantsoeng, litšiea tse eketsehileng, hlooho ea mekhahlelo e mengata, joalo-joalo e tla kena-kenana.
Ka hona, haufi le faele ena foldareng e tšoanang, re theha faele e 'ngoe e ncha Handler.xlsx, moo re tla theha potso ea Power Query e tla kenya data e mpe ho tsoa faeleng ea mohloli oa mohloli Lihlahisoa tse 100 tse holimo.xls, mme o di hlophise;
Ho etsa kopo ho faele ea kantle
Ho bula faele Handler.xlsx, khetha ho tab Lintlha taelo Fumana Lintlha - Ho Tsoa Faele - Ho tsoa ho Excel Workbook (Data - Fumana Lintlha - Ho tsoa faeleng - Ho tsoa ho Excel), ebe o hlalosa sebaka sa faele ea mohloli le lakane eo re e hlokang. Lintlha tse khethiloeng li tla kenngoa ho Power Query editor:
Ha re li khutlisetse setloaeleng:
- Hlakola mela e se nang letho ka Lehae — Hlakola mela — Hlakola mela e se nang letho (Lehae - Tlosa Mela - Tlosa Mela e se nang letho).
- Hlakola ho sa hlokahaleng holimo 4 mela ka Lehae — Hlakola Mela — Hlakola Mela e ka Holimo (Lehae - Tlosa Mela - Tlosa Mela e ka Holimo).
- Phahamisa mola oa pele ho hlooho ea tafole ka konopo Sebelisa mola oa pele joalo ka lihlooho tab ya Home (Lehae - Sebelisa mola oa pele joalo ka hlooho).
- Arola sengoloa sa linomoro tse hlano ho tsoa ho lebitso la sehlahisoa kholomong ea bobeli u sebelisa taelo kholomo e arohaneng tab ya ntlafalo (Fetola - Arola Kholomo).
- Hlakola litšiea tse sa hlokahaleng 'me u fetole lihlooho tsa tse setseng bakeng sa ponahalo e ntle.
Ka lebaka leo, re lokela ho fumana setšoantšo se latelang, se monate le ho feta:
E sala ho kenya tafole ena e ennobled hape leqepheng la faele ea rona Handler.xlsx sehlopha koala le ho jarolla (Lehae - Koala&Moroalo) tab ya Home:
Ho fumana tsela ea faele ka kopo
Joale a re boneng hore na potso ea rona e shebahala joang "tlas'a hood", ka puo ea ka hare e hahiloeng ho Power Query ka lebitso le khuts'oane "M". Ho etsa sena, khutlela ho potso ea rona ka ho tobetsa habeli ho eona fenstereng e nepahetseng Likopo le likhokahano le ho tab Review khetha Mohlophisi ea tsoetseng pele (Sheba - Sehlophisi se tsoetseng pele):
Fesetereng e butsoeng, mohala oa bobeli hang-hang o senola mokhoa o thata oa ho kenya faele ea rona ea mantlha. Haeba re ka nkela khoele ena sebaka ka paramethara, e feto-fetohang, kapa sehokelo sa sele ea leqephe la Excel moo tsela ena e ngotsoeng esale pele, joale re ka e fetola habonolo hamorao.
Kenya tafole e bohlale e nang le tsela ea faele
Ha re koala Power Query hajoale 'me re khutlele faeleng ea rona Handler.xlsx. Ha re kenye leqephe le lecha le se nang letho 'me re etse tafole e "bohlale" ho eona, ka seleng e le' ngoe feela moo ho tla ngoloa tsela e felletseng ea faele ea rona ea data:
Ho theha tafole e bohlale ho tsoa maemong a tloaelehileng, o ka sebelisa sekhechana sa keyboard Ctrl+T kapa konopo Sebopeho joalo ka tafole tab ya Home (Lehae - Fometa joalo ka Tafole). Sehlooho sa kholomo (sele A1) e ka ba eng kapa eng. Hape hlokomela hore bakeng sa ho hlaka ke file tafole lebitso entsprechen tab ya Constructor (Moralo).
Ho kopitsa tsela e tsoang ho Explorer kapa ho e kenya ka letsoho, ha e le hantle, ha ho thata haholo, empa ho molemo ho fokotsa ntlha ea motho le ho tseba tsela, haeba ho khoneha, ka tsela e iketsang. Sena se ka etsoa ka mokhoa o tloaelehileng oa Excel worksheet MOLETSANE (SELELE), e ka fanang ka leseli la bohlokoa mabapi le sele e boletsoeng e le khang - ho kenyelletsa le tsela e lebisang faeleng ea hajoale:
Haeba re nka hore faele ea data ea mohloli e lula e le ka har'a foldara e tšoanang le processor ea rona, tsela eo re e hlokang e ka theoa ka foromo e latelang:
= LEFT(CELL("lebitso la faele"); FUMANA("[";CELL("lebitso la faele"))-1)&"Lihlahisoa tse 100 tse kaholimo ho tse XNUMX.xls"
kapa ka Senyesemane:
= LEFT(SELE(«lebitso la faele»); FUMANA(«[«; CELL(«lebitso la faele»)))-1)&»Топ-100 товаров.xls»
… tšebetso e kae LEVSIMV (LEFI) e nka sengoathoana ho tloha sehokelong se felletseng ho ea lebaleng le bulehileng la sekwere (ke hore, tsela e eang foldareng ea hajoale), ebe lebitso le katoloso ea faele ea rona ea data ea mohloli li hokelloa ho eona.
Paramethara tseleng potsong
Ntho ea ho qetela le ea bohlokoa ka ho fetisisa e sala - ho ngola tsela e eang mohloling oa mohloli ka kopo Lihlahisoa tse 100 tse holimo.xls, e bua ka sele A2 ea tafole ea rona e "bohlale" e entsoeng entsprechen.
Ho etsa sena, ha re khutlele ho potso ea Power Query ebe re e bula hape Mohlophisi ea tsoetseng pele tab ya Review (Sheba - Sehlophisi se tsoetseng pele). Sebakeng sa mongolo oa khoele-tsela ea mantsoe a qotsitsoeng "E: Litlaleho tsa thekisoKa holimo ho 100 lihlahisoa.xlsx" Ha re tsebiseng sebopeho se latelang:
Excel.CurrentWorkbook(){[Lebitso="Litlhophiso"]}[Content]0 {}[Tsela ea ho ea mohloling oa data]
Ha re bone hore na e kenyelletsa eng:
- Excel.CurrentWorkbook() ke ts'ebetso ea puo ea M bakeng sa ho fihlella litaba tsa faele ea hajoale
- {[Lebitso="Litlhophiso"]}[Content] - ena ke parameter ea ntlafatso mosebetsing o fetileng, e bontšang hore re batla ho fumana litaba tsa tafole ea "bohlale" entsprechen
- [Tsela ea ho ea mohloling oa data] ke lebitso la kholomo e tafoleng entsprechentseo re buang ka tsona
- 0 {} ke nomoro ea mola tafoleng entsprecheneo re batlang ho nka data ho eona. Kepisi ha e bale 'me lipalo li qala ho tloha ho zero, eseng ho tloha ho e le' ngoe.
Ke phetho, ha e le hantle.
E sala ho tobetsa fihlela qetellong le ho hlahloba hore na kopo ea rona e sebetsa joang. Hona joale, ha u romela foldara eohle ka lifaele tse peli ka hare ho PC e 'ngoe, kopo e tla lula e sebetsa' me e bone tsela ea data ka bo eona.
- Potso ea Matla ke eng 'me hobaneng e hlokahala ha o sebetsa ho Microsoft Excel
- Mokhoa oa ho kenya sekhechana sa mongolo o phaphametseng ho Power Query
- Ho theha bocha Crosstab ea XNUMXD ho Tafole e Sephara ka Potso ea Matla