Batla mantsoe a sehlooho mongolong

Ho batla mantsoe a bohlokoa ho mongolo oa mohloli ke o mong oa mesebetsi e atileng haholo ha o sebetsa ka data. A re shebeng tharollo ea eona ka litsela tse 'maloa re sebelisa mohlala o latelang:

Ha re nke hore 'na le uena re na le lethathamo la mantsoe a bohlokoa - mabitso a mefuta ea likoloi - le tafole e kholo ea mefuta eohle ea li-spare parts, moo litlhaloso ka linako tse ling li ka bang le mofuta o le mong kapa tse' maloa ka nako e le 'ngoe, haeba karolo e setseng e lekana ho feta e le' ngoe. mofuta oa koloi. Mosebetsi oa rona ke ho fumana le ho bonts'a mantsoe ohle a bohlokoa a fumanoeng liseleng tsa boahelani ka sebopeho se fanoeng se arohaneng (mohlala, phegelwana).

Mokhoa oa 1. Potso ea Matla

Ehlile, pele re fetola litafole tsa rona hore e be tse matla ("bohlale") re sebelisa sekhechana sa keyboard Ctrl+T kapa ditaelo Lehae – Fometa joalo ka tafole (Lehae - Fometa joalo ka Tafole), ba fane ka mabitso (mohlala Litempeи Likarolo tsa tlatsetso) ebe o kenya ka bonngoe ho Power Query ka ho khetha ho tab Lintlha - Ho tsoa ho Tafole / Range (Boitsebiso — Ho Tsoa ho Lethathamo/Boemo). Haeba u na le liphetolelo tsa khale tsa Excel 2010-2013, moo Power Query e kentsoeng e le kenyelletso e arohaneng, joale konopo e lakatsehang e tla ba ho tab. Potso ea Matla. Haeba u na le mofuta o mocha oa Excel 365, tobetsa konopo Ho tloha ho Tafole/Range bitsetsoa moo hona joale Ka mahlaku (Ho tsoa Leqepheng).

Kamora ho kenya tafole ka 'ngoe ho Power Query, re khutlela Excel ka taelo Lehae — kwala o lae — Kwala o kenye ho… — Theha kgokelo feela (Lapeng — Koala & Lorse — Koala & Laela ho… — Theha feela khokahano).

Joale ha re theheng kopo ea kopo Likarolo tsa tlatsetsoka ho tobetsa ho yona le ho kgetha Kopo e kopitsoang (Pheta potso), ebe u reha lebitso la kopo e hlahisitsoeng ho Liphetho mme re tla tswelapele ho sebetsa le yena.

The logic ea liketso ke tse latelang:

1. Ho Advanced tab Ho eketsa kholomo khetha sehlopha Kholomo e ikhethileng (Eketsa kholomo - kholomo e ikhethileng) ebe o kenya foromo = Brands. Ka mor'a ho tobetsa OK re tla fumana kholomo e ncha, moo ka seleng e 'ngoe le e' ngoe ho tla ba le tafole ea sehlaha e nang le lethathamo la mantsoe a rona a bohlokoa - li-brand tsa automaker:

   

2. Sebelisa konopo e nang le metsu e habeli hloohong ea kholomo e ekelitsoeng ho holisa litafole tsohle tse behiloeng. Ka nako e ts'oanang, mela e nang le litlhaloso tsa likarolo tse setseng e tla ata ka bongata ba palo ea lihlahisoa, 'me re tla fumana li-pair tse ka khonehang tsa "brand" ea "spare part-brand":

   

3. Ho Advanced tab Ho eketsa kholomo khetha sehlopha Kholomo e nang le maemo (Kholomo e nang le maemo) 'me u behe boemo ba ho lekola ho hlaha ha lentsoe la sehlooho (brand) mohloling oa mohloli (tlhaloso ea karolo):

   

4. Ho etsa hore taba ea ho batla e se be le kutlo, ka bowena kenya khang ea boraro bareng ea foromo Bapisa.OrdinalIgnoreCase ho ts'ebetso ea ho hlahloba ketsahalo Mongolo.E na le (haeba mochini oa foromo o sa bonahale, o ka khona ho nolofalloa ho tab Review):

   

5. Re sefa tafole e hlahisoang, re siea e 'ngoe feela kholomong ea ho qetela, ke hore, lipapali ebe re tlosa kholomo e sa hlokahaleng. Liketsahalo.
6. Ho arola litlhaloso tse tšoanang le taelo Sehlopha ka tab ya ntlafalo (Fetola - Sehlopha ka). E le ts'ebetso ea ho kopanya, khetha Mela eohle (Mela eohle). Ka tlhahiso, re fumana kholomo e nang le litafole, e nang le lintlha tsohle bakeng sa karolo e 'ngoe le e' ngoe e setseng, ho kenyelletsa le mefuta ea li-automaker tseo re li hlokang:

   

7. Ho ntša limaraka bakeng sa karolo ka 'ngoe, eketsa kholomo e baliloeng ho tab Ho Eketsoa Kholomo - Kholomo e Tloaelehileng (Eketsa kholomo - kholomo e ikhethileng) 'me u sebelise foromo e nang le tafole (li fumaneha kholomong ea rona Details) le lebitso la kholomo e nkiloeng:

   

8. Re tobetsa konopo ka metsu e habeli hloohong ea kholomo e hlahisoang ebe u khetha taelo Hlakola litekanyetso (Ntša boleng)ho hlahisa litempe tse nang le litlhaku tsa delimiter tseo u li batlang:

   

9. Ho tlosa kholomo e sa hlokahaleng Details.
10. Ho kenyelletsa tafoleng e hlahisitsoeng likarolo tse nyametseng ho eona, moo ho neng ho se na mabitso a fumanehang litlhalosong, re etsa mokhoa oa ho kopanya potso. Phello ka kopo ea mantlha Likarolo tsa tlatsetso konopo Kopanya tab ya Home (Lehae - Kopanya lipotso). Mofuta oa khokahano - Outer Kopana ka ho le letona (Kopano ka ntle ka ho le letona):

    

11. Ho setseng feela ke ho tlosa litšiea tse ling le ho reha tse setseng hape - mme mosebetsi oa rona o rarollotsoe:

    
    

Mokhoa oa 2. Mekhoa

Haeba u na le mofuta oa Excel 2016 kapa hamorao, joale bothata ba rona bo ka rarolloa ka mokhoa o kopaneng le o boreleli o sebelisa ts'ebetso e ncha. KOPANE (TEXTJOIN):

Melemo ea foromo ena e bonolo:

• mosebetsi SEARCH (FUMANA) e batla ho hlaha ha mofuta o mong le o mong ka ho latellana tlhalosong ea hajoale ea karolo ebe e khutlisa nomoro ea serial ea letšoao, ho qala moo lebitso le fumanoeng, kapa phoso #VALUE! haeba lets'oao le le sieo tlhalosong.
• Ebe o sebelisa ts'ebetso IF (HAEBA) и EOSHIBKA (ISERROR) re fetola liphoso ka khoele e se nang letho "", le linomoro tsa ordinal tsa litlhaku tse nang le mabitso a marang-rang ka bobona.
• Letoto le hlahisoang la lisele tse se nang letho le mefuta e fumanoeng li bokelloa ka khoele e le 'ngoe ka tlhaku e arohaneng e sebelisang tšebetso. KOPANE (TEXTJOIN).

Papiso ea Ts'ebetso le Potso ea Matla Potso Buffering bakeng sa Speedup

Bakeng sa tlhahlobo ea ts'ebetso, ha re nke tafole ea litlhaloso tsa likarolo tse 100 e le data ea pele. Ho eona re fumana liphetho tse latelang:

• Nako ea ho bala hape ka liforomo (Mokhoa oa 2) - 9 sec. ha u qala ho kopitsa foromo ho kholomo eohle le 2 sec. ka ho pheta-pheta (buffering e ama, mohlomong).
  
• Nako ea ntlafatso ea potso ea Power Query (Mokhoa oa 1) e mpe haholo - metsotsoana ea 110.
  

Ehlile, ho hongata ho ipapisitse le lisebelisoa tsa PC e itseng le mofuta o kentsoeng oa Office le liapdeite, empa setšoantšo se akaretsang, ke nahana, se hlakile.

Ho potlakisa potso ea Power Query, ha re bolokeng tafole ea ho sheba Litempe, hobane ha e fetohe ts'ebetsong ea ts'ebetso ea potso 'me ha ho hlokahale hore u lule u e bala hape (joalo ka ha Power Query de facto e etsa). Bakeng sa sena re sebelisa ts'ebetso Tafole.Buffer ho tsoa ho puo ea Potso ea Matla e hahiloeng kahare M.

Ho etsa sena, bula potso Liphetho le ho tab Review tobetsa konopo Mohlophisi ea tsoetseng pele (Sheba - Sehlophisi se tsoetseng pele). Fesetereng e butsoeng, eketsa mola o nang le phetoho e ncha Mareka 2, e tla ba mofuta oa buffered oa directory ea rona ea automaker, 'me u sebelise mofuta ona o mocha hamorao ka taelo e latelang ea potso:

Kamora ntlafatso e joalo, lebelo la ntlafatso ea kopo ea rona le eketseha ka makhetlo a ka bang 7 - ho fihlela metsotsoana e 15. Ntho e fapaneng haholo 🙂

• Patlisiso e makatsang ea mongolo ho Power Query
• Phetisetso ea mongolo ka bongata ka liforomo
• Phetisetso ea mongolo ka bongata ho Potso ea Matla ka Lenane.Bokella tšebetso