Phetisetso ea mongolo ka bongata ho Potso ea Matla ka Lenane.Bokella tšebetso

Mokhoa oa ho khutlisa mongolo kapele le ka bongata ho latela lethathamo la litšupiso ka liforomo - re se re e hlophisitse. Joale ha re leke ho e etsa ka Power Query.

Joalokaha hangata ho etsahala etsa mosebetsi ona o bonolo haholo ho feta ho hlalosa ke hobane'ng ha ea sebetsa, empa a re lekeng ho etsa ka bobeli 🙂

Kahoo, re na le litafole tse peli tse "bohlale" tse matla tse entsoeng ho tloha mefuteng e tloaelehileng e nang le sekhechana sa keyboard Ctrl+T kapa sehlopha Lehae – Fometa joalo ka tafole (Lehae - Fometa joalo ka Tafole):

Ke bitsitse tafole ea pele Lintlha, tafole ea bobeli - Directorysebelisa tšimo Lebitso la tafole (Lebitso la tafole) tab ya Constructor (Moralo).

Mosebetsi: fetola liaterese tafoleng Lintlha liketsahalo tsohle ho tsoa kholomong Ho fumana Buka ea letsoho ho balekane ba bona ba nepahetseng ho tloha kholomong Sebaka. Karolo e setseng ea mongolo ka liseleng e lokela ho lula e sa ameha.

Mohato oa 1. Kenya bukana ho Power Query 'me u e fetole lethathamo

Ha u se u behile sele e sebetsang sebakeng sefe kapa sefe tafoleng ea litšupiso, tobetsa ho tab Lintlha (Letsatsi)kapa ho tab Potso ea Matla (haeba u na le mofuta oa khale oa Excel mme o kentse Power Query joalo ka kenyelletso ho tab e arohaneng) konopo Ho tloha tafoleng/lethathamong (Ho tsoa ho Tafole/Boemo).

Tafole ea litšupiso e tla kenngoa ho mohlophisi oa potso ea Power Query:

E le hore u se ke ua kena-kenana, mohato o ekelitsoeng ka ho iketsa mofuta o fetotsoeng (Mofuta o Fetotsoeng) ka phanele e nepahetseng, mehato e sebelisitsoeng e ka hlakoloa ka mokhoa o sireletsehileng, e siea mohato feela mohloli o moholo (Mohloli):

Hona joale, ho etsa liphetoho tse ling le tse ling, re hloka ho fetola tafole ena lenane (lethathamo).

Tlokotsi ea mantsoe

• Lethathamo ke mokoloko oa mahlakore a mabeli o nang le mela le litšiea tse 'maloa.
• Rekota (Rekoto) - khoele ea mahlakore a le mong, e nang le likarolo tse 'maloa tse nang le mabitso, mohlala. [Lebitso = “Masha”, Bong = “f”, Lilemo = 25]
  
• List - kholomo e nang le mahlakore a le mong, e nang le likarolo tse 'maloa, mohlala {1, 2, 3, 10, 42} or {“Tumelo Tšepo Lerato” }
  

Ho rarolla bothata ba rona, re tla thahasella haholo mofuta List.

Leqheka mona ke hore lethathamo la lintho tse ho Potso ea Matla e ka se be feela linomoro tsa banal kapa mongolo, empa hape le manane a mang kapa lirekoto. Ke lethathamong le qhekellang (lenane), le nang le lirekoto (lirekoto) moo re hlokang ho fetolela bukana ea rona. Ho Power Query syntactic notation (menyako ka masakaneng a lisekoere, manane a masakaneng a curly) sena se ka shebahala tjena:

{

    [ Fumana = “St. Petersburg", Fetola = "St. Petersburg”] ,

    [ Fumana = “St. Petersburg", Fetola = "St. Petersburg”] ,

    [ Fumana = “Petrose”, Replace = “St. Petersburg”] ,

joalo-joalo

}

Phetoho e joalo e etsoa ho sebelisoa mosebetsi o khethehileng oa puo ea M e hahiloeng ho Power Query - Tafole.ToRecords. Ho e sebelisa ka kotloloho bareng ea foromo, eketsa ts'ebetso ena ho khoutu ea mohato moo mohloli o moholo.

E ne le:

Ka morao:

Ka mor'a ho kenyelletsa Table.ToRecords mosebetsi, ponahalo ea tafole ea rona e tla fetoha - e tla fetoha lethathamo la litlaleho. Likahare tsa lirekoto ka bomong li ka bonoa botlaaseng ba fenstere ea pono ka ho tobetsa bokamorao ba sele haufi le lentsoe lefe kapa lefe. Record (empa eseng ka lentsoe le le leng!)

Ho phaella ho tse ka holimo, hoa utloahala ho eketsa stroke e le 'ngoe - ho cache (buffer) lenane la rona le entsoeng. Sena se tla qobella Power Query ho kenya lenane la rona la ho sheba hang ka mohopolong mme re se ke ra le bala hape ha re le fumana hamorao ho le fetola. Ho etsa sena, thatela foromo ea rona ka ts'ebetso e 'ngoe - Lenane.Buffer:

Caching e joalo e tla fana ka keketseho e hlokomelehang haholo ea lebelo (ka makhetlo a 'maloa!) Ka palo e kholo ea data ea pele e lokelang ho hlakoloa.

Sena se phethela boitokiso ba bukana.

E sala ho tobetsa Lehae - Koala 'me U Lokise - Koala 'me U Lokise ho... (Lehae - Koala&Mojaro - Koala&Moroalo ho..), khetha khetho Etsa feela khokahano (Etsa khokahano feela) ebe o khutlela ho Excel.

Mohato oa 2. Ho kenya tafole ea data

Ntho e 'ngoe le e 'ngoe e thata mona. Joalo ka pele ka buka ea litšupiso, re nyolohela sebakeng sefe kapa sefe se tafoleng, tobetsa ho tab Lintlha konopo Ho tloha ho Tafole/Range le tafole ea rona Lintlha e kena ho Power Query. Mohato o ekelitsoeng ka boiketsetso mofuta o fetotsoeng (Mofuta o Fetotsoeng) o ka boela oa tlosa:

Ha ho na liketso tse khethehileng tsa litokisetso tse hlokahalang ho etsoa ka eona, 'me re fetela pele ho ntho ea bohlokoa ka ho fetisisa.

Mohato 3. Etsa tse ncha ka ho sebelisa Lenane.Bokella tshebetso

Ha re kenye kholomo e baloang tafoleng ea rona ea data re sebelisa taelo Ho Eketsoa Kholomo - Kholomo e Tloaelehileng (Eketsa kholomo - kholomo e ikhethileng): ebe o kenya lebitso la kholomo e kentsoeng fensetereng e butsoeng (mohlala, aterese e lokisitsoeng) le mosebetsi oa rona oa boselamose Lenane.Bokella:

E sala ho tobetsa OK - 'me re fumana kholomo e nang le liphetoho tse entsoeng:

Lemoha hore:

• Kaha Power Query ke taba e hlokolosi, ha hoa ka ha e-ba le phetoho moleng oa pele, hobane bukeng re na le "SPb", eseng "SPb".
• Haeba ho na le likhoele tse 'maloa tse lokelang ho nkeloa sebaka hang-hang mohloling oa data (mohlala, moleng oa 7 o hloka ho khutlisa "S-Pb" le "Prospectus"), joale sena ha se bake mathata (ho fapana le ho nkela liforomo tse tsoang ho mokhoa o fetileng).
• Haeba ho se letho le ka nkelang sebaka mohloling oa mohloli (mola oa 9), ha ho na liphoso tse hlahang (ho fapana le, hape, ho tloha ho nkeloa sebaka ke liforomo).

Lebelo la kopo e joalo le nepahetse haholo. Mohlala, bakeng sa tafole ea data ea pele e nang le mela e 5000, potso ena e ile ea ntlafatsoa ka nako e ka tlase ho motsotsoana (ntle le ho buffer, ka tsela, metsotsoana e ka bang 3!)

Kamoo Lenane.Bokella tshebetso e sebetsang ka teng

Ha e le hantle, ena e ka ba qetello (hore ke ngole, le hore u bale) sehlooho sena. Haeba u batla ho se khone feela, empa hape le ho utloisisa hore na e sebetsa joang "tlas'a hood", joale u tla tlameha ho qoela hanyenyane ka mokoting oa mmutla 'me u sebetsane le List.Accumulate function, e entseng phetoho eohle e ngata. sebetsa bakeng sa rona.

Syntax ea mosebetsi ona ke:

=Lenane.Bokella(siyo lenaneng, peo, pokello)

moo

• siyo lenaneng ke lenane leo re ntseng re pheta lintlha tsa lona. 
• peo - boemo ba pele
• pokello - ts'ebetso e etsang ts'ebetso e itseng (lipalo, mongolo, joalo-joalo) karolong e latelang ea lenane mme e bokella sephetho sa ts'ebetso ka phetoho e khethehileng.
  

Ka kakaretso, syntax ea ho ngola mesebetsi ho Power Query e shebahala tjena:

(khang1, khang2, … khangN) => liketso tse ling ka likhang

Mohlala, ts'ebetso ea kakaretso e ka emeloa joalo ka:

(a, b) => a + b

Bakeng sa Lethathamo.Bokella , mosebetsi ona oa accumulator o na le mabaka a mabeli a hlokahalang (a ka bitsoa eng kapa eng, empa mabitso a tloaelehileng ke. boemo и hajwale, joalo ka thuso ea semmuso bakeng sa ts'ebetso ena, moo:

• boemo - phetoho moo sephetho se bokelloang teng (boleng ba eona ba pele ke bo boletsoeng ka holimo peo)
  
• hajwale – boleng bo latelang bo phethiloeng ho tsoa lenaneng siyo lenaneng
  

Ka mohlala, a re shebeng mehato ea logic ea mohaho o latelang:

=Lenane.Bokella({3, 2, 5}, 10, (boemo, jwale) => boemo + jwale)

Ena ke ea morao-rao e bokelletsoeng boemo boleng ke Lenane la rona.Bokella tšebetso le liphetho ka lebaka leo:

Haeba u nahana hanyenyane, joale u sebelisa Lenane.Bokella mosebetsi, u ka etsisa, mohlala, mosebetsi oa Excel CONCATENATE (ho Power Query, analogue ea eona e bitsoa. Mongolo.Kopanya) ho sebelisa poleloana:

Kapa u batle boleng bo holimo (ho etsisa mosebetsi oa Excel oa MAX, oo ka Power Query o bitsoang List.Max):

Leha ho le joalo, tšobotsi e ka sehloohong ea Lethathamo.Bokella ke bokhoni ba ho sebetsana e seng feela mongolo o bonolo kapa manane a linomoro e le likhang, empa le lintho tse rarahaneng ho feta - mohlala, manane-ho tloha-mathathamong kapa manane-ho tsoa ho lirekoto (hello, Directory!)

Ha re shebeng hape kaho e entseng phetoho bothateng ba rona:

Lenane.Bokella(Directory, [Aterese], (boemo, jwale) => Mongolo.Fetola(boemo, jwale[Fumana], ya jwale[Emela sebaka]) )

Hantle-ntle ho etsahalang moo?

1. Joalo ka boleng ba pele (peo) re nka mongolo oa pele o sa hlakang kholomong [Aterese] tafole ea rona: 199034, St. Petersburg, str. Beringa, d. 1
2. Ebe List.Bokella e pheta-pheta lintlha tsa lenane ka bonngoe - Buka ea letsoho. Karolo ka 'ngoe ea lethathamo lena ke rekoto e nang le likarolo tse peli "Seo u ka se fumanang - Seo u ka se nkelang sebaka" kapa, ka mantsoe a mang, mola o latelang bukeng.
3. Mosebetsi oa accumulator o kenya phetoho boemo boleng ba pele (aterese ea pele 199034, St. Petersburg, str. Beringa, d. 1) mme e etsa mosebetsi oa accumulator ho eona - ts'ebetso e ncha e sebelisang M-function e tloaelehileng Mongolo.Fetola (e tšoana le mosebetsi oa SUBSTITUTE oa Excel). Syntax ea eona ke:

   Text.Replace( mongolo oa mantlha, seo re se batlang, seo re se nkelang sebaka)

   mme mona re na le:

• boemo ke aterese ea rona e litšila, e lutseng ho boemo (ho tloha moo peo)
  
• jwale[Batla] – boleng ba tšimo Ho fumana ho tsoa lethathamong le latelang le phetoang Directory, e leng ka ho feto-fetoha hajwale
• ya jwale[Emela sebaka] – boleng ba tšimo Sebaka ho tsoa lethathamong le latelang le phetoang Directoryrapaletse hajwale

Ka hona, bakeng sa aterese e 'ngoe le e' ngoe, nako le nako ho etsoa potoloho e feletseng ea ho bala mela eohle e bukeng, ho nkela mongolo sebakeng sa [Fumana] ka boleng bo tsoang tšimong ea [Replace].

Ke tšepa hore u fumane mohopolo 🙂

• Bongata nka sebaka sa mongolo lethathamong le sebelisa liforomo
  
• Mantsoe a Tloaelehileng (RegExp) ho Potso ea Matla