Maye gurbin rubutu mai girma a cikin Tambayar Wuta tare da Lissafi.Tara aikin

Yadda za a maye gurbin rubutu da sauri da sauri bisa ga lissafin tunani tare da dabaru - mun riga mun tsara shi. Yanzu bari mu yi kokarin yi a cikin Power Query.

Kamar yadda sau da yawa yakan faru yi wannan aikin ya fi sauƙi fiye da bayyanawa dalilin da ya sa yana aiki, amma bari mu yi ƙoƙarin yin duka 🙂

Don haka, muna da tebur masu ƙarfi guda biyu “masu wayo” waɗanda aka ƙirƙira daga jeri na yau da kullun tare da gajeriyar hanyar madannai Ctrl+T ko tawaga Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur):

Na kira tebur na farko data, tebur na biyu - Directoryta amfani da filin Sunan tebur (Sunan tebur) tab Constructor (Zane).

Aiki: maye gurbin a adiresoshin a cikin tebur data duk abubuwan da suka faru daga ginshiƙi Don nemowa Littafin Jagora zuwa ga daidaitattun takwarorinsu daidai daga ginshiƙi Canje. Sauran rubutun a cikin sel yakamata su kasance ba a taɓa su ba.

Mataki 1. Load da directory cikin Power Query kuma juya shi cikin jerin

Bayan saita tantanin halitta mai aiki zuwa kowane wuri a cikin tebur, danna shafin data (Kwanan wata)ko a kan tab Tambayar .arfi (idan kuna da tsohuwar sigar Excel kuma kun shigar da Query Query azaman ƙari akan wani shafin daban) akan maɓallin. Daga tebur / kewayon (Daga Tebur/Range).

Za a loda teburin tunani a cikin editan Query Query:

Don kar a tsoma baki, ƙara mataki ta atomatik nau'in gyarawa (An Canja Nau'in) a cikin sashin dama, matakan da aka yi amfani da su za a iya share su cikin aminci, barin mataki kawai source (Source):

Yanzu, don yin ƙarin canje-canje da maye gurbin, muna buƙatar juya wannan tebur zuwa jeri (jeri).

Lissafin waƙa

• Table jeri ne mai girma biyu wanda ya ƙunshi layuka da ginshiƙai da yawa.
• Yi rikodin (Record) - kirtani-tsara mai girma-ɗaya, wanda ya ƙunshi fagage da yawa- abubuwa masu suna, alal misali [Sunan = "Masha", Gender = "f", Shekaru = 25]
  
• list - ginshiƙi mai girma-ɗaya, wanda ya ƙunshi abubuwa da yawa, misali {1, 2, 3, 10, 42} or {"Faith Bege Love" }
  

Don magance matsalarmu, za mu fara sha'awar nau'in list.

Dabarar a nan ita ce lissafin abubuwa a cikin Query Query na iya zama banal lambobi ko rubutu kawai ba, har ma da wasu jeri ko rikodin. Yana cikin jerin wayo (jeri), wanda ya ƙunshi bayanai (rikodi) waɗanda muke buƙatar juya kundin adireshi. A cikin bayanan synlinic bayanin kula (shigarwar a cikin square browges, jerin abubuwa a cikin brackts na curly) wannan zai yi kama:

{

    [ Nemo = "St. Petersburg", Sauya = "St. Petersburg"] ,

    [ Nemo = "St. Petersburg", Sauya = "St. Petersburg"] ,

    [ Nemo = "Bitrus", Sauya = "St. Petersburg"] ,

da dai sauransu.

}

Ana yin irin wannan sauyi ta amfani da aiki na musamman na yaren M da aka gina cikin Query Query - Table.ToRecords. Don amfani da shi kai tsaye a cikin ma'aunin dabara, ƙara wannan aikin zuwa lambar mataki a can source.

Ya kasance:

Bayan:

Bayan ƙara aikin Table.ToRecords, bayyanar teburin mu zai canza - zai juya cikin jerin bayanan. Ana iya ganin abubuwan da ke cikin bayanan guda ɗaya a kasan rukunin kallo ta danna bayanan tantanin halitta kusa da kowace kalma. Record (amma ba a cikin kalma ɗaya ba!)

Baya ga abin da ke sama, yana da ma'ana don ƙara ƙarin bugun jini guda ɗaya - zuwa cache (buffer) jerin abubuwan da aka ƙirƙira. Wannan zai tilasta Neman Ƙarfi don loda lissafin binciken mu sau ɗaya zuwa ƙwaƙwalwar ajiya kuma ba za mu sake ƙididdige shi ba lokacin da muka isa gare shi don maye gurbinsa. Don yin wannan, kunsa tsarinmu a cikin wani aiki - Jerin.Buffer:

Irin wannan caching zai ba da karuwa mai girma a cikin sauri (ta sau da yawa!) Tare da babban adadin bayanan farko da za a share.

Wannan yana kammala shirye-shiryen littafin Jagora.

Ya rage don dannawa Gida - Rufe kuma Loda - Kusa kuma Loda zuwa… (Gida - Rufe&Load - Rufe & Load zuwa ..), zaɓi zaɓi Kawai ƙirƙirar haɗi (ƙirƙiri haɗi kawai) kuma komawa zuwa Excel.

Mataki 2. Loading da bayanai tebur

Duk abin da yake trite a nan. Kamar yadda a baya tare da littafin tunani, muna tashi zuwa kowane wuri a cikin tebur, danna kan shafin data button Daga Tebura/Range da teburin mu data ya shiga Query Query. Ƙara mataki ta atomatik nau'in gyarawa (An Canja Nau'in) Hakanan zaka iya cire:

Ba a buƙatar wasu ayyuka na shirye-shirye na musamman da za a yi tare da shi, kuma mun ci gaba zuwa abu mafi mahimmanci.

Mataki na 3. Yi maye gurbin ta amfani da List.Tara aikin

Bari mu ƙara ginshiƙi mai ƙididdigewa zuwa teburin bayanan mu ta amfani da umarnin Ƙara ginshiƙi - Shagon Al'ada (Ƙara shafi - ginshiƙi na al'ada): kuma shigar da sunan ƙararrakin shafi a cikin taga da ke buɗewa (misali, adireshin da aka gyara) da aikin sihirinmu Jerin.Tara:

Ya rage don dannawa OK - kuma muna samun shafi tare da maye gurbin da aka yi:

Lura cewa:

• Tun da Power Query yana da mahimmanci, babu wani canji a cikin layi mai mahimmanci, saboda a cikin shugabanci muna da "SPb", ba "SPb".
• Idan akwai ƙananan igiyoyi da yawa don maye gurbin lokaci ɗaya a cikin bayanan tushen (alal misali, a cikin layi na 7 kuna buƙatar maye gurbin duka "S-Pb" da "Prospectus"), to wannan baya haifar da wata matsala (ba kamar maye gurbin da dabara daga hanyar da ta gabata).
• Idan babu wani abu da za a maye gurbin a cikin rubutun tushe (layi na 9), to babu kurakurai da ke faruwa (ba kamar, sake ba, daga maye gurbin ta hanyar dabaru).

Gudun irin wannan buƙatun yana da kyau sosai. Misali, don tebur na bayanan farko tare da girman layuka 5000, an sabunta wannan tambayar a cikin ƙasa da daƙiƙa guda (ba tare da buffering ba, ta hanya, kusan daƙiƙa 3!)

Yadda List.Accumulate ayyuka ke aiki

A ka'ida, wannan na iya zama ƙarshen (a gare ni in rubuta, kuma ku karanta) wannan labarin. Idan kuna son ba kawai ku sami damar ba, amma kuma ku fahimci yadda yake aiki "a ƙarƙashin hular", to, dole ne ku nutse ɗan ƙaramin zurfi a cikin ramin zomo kuma ku yi hulɗa da List.Tara aikin, wanda ya yi duk babban maye yi mana aiki.

Ma'anar wannan aikin shine:

=Jeri.Tara(list, iri, mai tarawa)

inda

• list shine jerin abubuwan da muke maimaitawa akai. 
• iri – yanayin farko
• mai tarawa - aikin da ke yin wasu ayyuka (ilimin lissafi, rubutu, da sauransu) akan kashi na gaba na lissafin kuma yana tara sakamakon aiki a cikin wani maɓalli na musamman.
  

Gabaɗaya, ƙa'idar aikin rubutu a cikin Query Query yayi kama da haka:

(argument1, muhawara2, … muhawaraN) => wasu ayyuka tare da hujja

Misali, ana iya wakilta aikin taƙaitawa kamar:

(a, b) => a + b

Don List.Accumulate , wannan aikin tarawa yana da dalilai guda biyu da ake buƙata (ana iya kiran su wani abu, amma sunayen da aka saba suna. jihar и yanzu, kamar a cikin taimakon hukuma don wannan aikin, inda:

• jihar - mai canzawa inda aka tara sakamakon (kimanin farko shine wanda aka ambata a sama iri)
  
• yanzu – na gaba irated darajar daga lissafin list
  

Misali, bari mu kalli matakan dabarar wannan gini mai zuwa:

=Jeri.Tara({3, 2, 5}, 10, (jihar, halin yanzu) => yanayi + halin yanzu)

Wannan shine sabon tarawa jihar Ƙimar ita ce Jerin mu. Tara ayyuka da fitarwa a sakamakon haka:

Idan ka ɗan yi tunani kaɗan, to, ta amfani da List.Accumulate function, za ka iya simulate, alal misali, aikin Excel CONCATENATE (a cikin Power Query, ana kiran analog ɗinsa). Rubutu.Haɗa) ta amfani da kalmar:

Ko ma bincika mafi girman ƙimar (koyi da aikin MAX na Excel, wanda a cikin Query Query ake kira Jerin.Max):

Duk da haka, babban fasalin Lissafi.Tara shine ikon aiwatarwa ba kawai rubutu mai sauƙi ko lissafin lambobi azaman muhawara ba, amma abubuwa masu rikitarwa - alal misali, jeri-daga-jeri ko lissafin-daga-rikodi (sannu, Directory!)

Bari mu sake duba ginin da ya yi maye gurbin a cikin matsalarmu:

Jerin.Tara(Directory, Adireshin, (jiha, halin yanzu) => Rubutu.Maye gurbin (jiha, halin yanzu [Nemo], na yanzu[Maye gurbin]) )

Menene ainihin ke faruwa a nan?

1. Kamar ƙimar farko (iri) za mu ɗauki rubutu na farko mai banƙyama daga ginshiƙi Adireshin teburin mu: 199034, St. Petersburg, str. Beringa, d. 1
2. Sa'an nan List.Tara tara kan abubuwan da ke cikin jerin ɗaya bayan ɗaya - Littafin Jagora. Kowane kashi na wannan jeri rikodin ne wanda ya ƙunshi filayen biyu "Abin da za a samu - Abin da za a maye gurbinsa da" ko, a wasu kalmomi, layi na gaba a cikin kundin adireshi.
3. Aikin tarawa yana sanyawa cikin mai canzawa jihar ƙimar farko (adireshin farko 199034, St. Petersburg, str. Beringa, d. 1) kuma yana yin aikin tarawa akan shi - aikin maye gurbin ta amfani da daidaitaccen aikin M Rubutu.Maye gurbin (mai kama da aikin SUBSTITUTE na Excel). Ma'anarsa shine:

   Rubutu. Sauya (rubutu na asali, abin da muke nema, abin da muke maye gurbinsa da shi)

   kuma a nan muna da:

• jihar shine adreshin mu mai datti, wanda ke cikinsa jihar (daga can iri)
  
• halin yanzu[Bincike] – darajar filin Don nemowa daga shigar da lissafin na gaba Directory, wanda ya ta'allaka ne a cikin m yanzu
• halin yanzu[Maye gurbin] – darajar filin Canje daga shigar da lissafin na gaba Directorykwance a ciki yanzu

Don haka, ga kowane adireshi, ana gudanar da cikakken tsarin ƙididdige duk layin da ke cikin kundin adireshin kowane lokaci, tare da maye gurbin rubutu daga filin [Nemi] tare da darajar daga filin [Maye gurbin].

Da fatan kun sami ra'ayin 🙂

• Babban maye gurbin rubutu a cikin jeri ta amfani da dabaru
  
• Kalmomi na yau da kullun (RegExp) a cikin Query Query