በኃይል ጥያቄ ውስጥ የጅምላ ጽሑፍ መተካት ከዝርዝር ጋር። ተግባር ያከማቻል

በማጣቀሻ ዝርዝሩ መሰረት ጽሑፉን በፍጥነት እና በጅምላ እንዴት እንደሚተኩት በቀመር - አስቀድመን አውቀናል. አሁን በኃይል መጠይቅ ውስጥ ለማድረግ እንሞክር.

ብዙውን ጊዜ እንደሚከሰት ማከናወን ይህ ተግባር ከማብራራት የበለጠ ቀላል ነው። እንዴት ይሰራል, ግን ሁለቱንም ለመስራት እንሞክር 🙂

ስለዚህ፣ ከቁልፍ ሰሌዳ አቋራጭ ጋር ከተራ ክልሎች የተፈጠሩ ሁለት “ብልጥ” ተለዋዋጭ ሰንጠረዦች አሉን። መቆጣጠሪያ+T ወይም ቡድን ቤት - እንደ ጠረጴዛ ቅርጸት (ቤት - እንደ ሰንጠረዥ ቅርጸት):

የመጀመሪያውን ጠረጴዛ ደወልኩ መረጃሁለተኛው ጠረጴዛ - ማውጫመስክ በመጠቀም የጠረጴዛ ስም (የጠረጴዛ ስም) ትር ግንበኛ (ዲዛይን).

ተግባር: በሰንጠረዡ ውስጥ ባሉ አድራሻዎች ውስጥ ይተኩ መረጃ ሁሉም ክስተቶች ከአምድ ማግኘት መምሪያ መጽሐፍ ከአምዱ ወደ ተጓዳኝ ትክክለኛ ተጓዳኝዎቻቸው ምትክ. በሴሎች ውስጥ ያለው የቀረው ጽሑፍ ሳይነካ መቆየት አለበት።

ደረጃ 1 ማውጫውን ወደ Power Query ጫን እና ወደ ዝርዝር ቀይር

ገባሪውን ሕዋስ በማመሳከሪያ ሠንጠረዡ ውስጥ ወዳለው ቦታ ካቀናበሩ በኋላ ትሩ ላይ ጠቅ ያድርጉ መረጃ (ቀን)ወይም በትሩ ላይ የኃይል ጥያቄ (የድሮው የ Excel ስሪት ካለዎት እና የኃይል መጠይቁን በተለየ ትር ላይ እንደ ተጨማሪ ከጫኑ) በአዝራሩ ላይ ከጠረጴዛ / ክልል (ከጠረጴዛ/ክልል).

የማመሳከሪያ ሠንጠረዡ በኃይል መጠይቅ ጥያቄ አርታዒ ውስጥ ይጫናል፡-

ጣልቃ ላለመግባት, በራስ-ሰር የተጨመረ ደረጃ የተሻሻለው ዓይነት (የተቀየረ ዓይነት) በትክክለኛው ፓነል ውስጥ, የተተገበሩ ደረጃዎች በደህና ሊሰረዙ ይችላሉ, ይህም ደረጃውን ብቻ ይተዋል ምንጭ (ምንጭ):

አሁን, ተጨማሪ ለውጦችን እና ተተኪዎችን ለማከናወን, ይህንን ሰንጠረዥ ወደ ዝርዝር (ዝርዝር) መቀየር አለብን.

የግጥም መፍጨት

• ጠረጴዛ በርካታ ረድፎችን እና አምዶችን ያካተተ ባለ ሁለት አቅጣጫዊ ድርድር ነው።
• መዝገብ (መመዝገብ) - ባለ አንድ-ልኬት ድርድር-ሕብረቁምፊ፣ በርካታ መስኮች-ንጥረ ነገሮችን ያቀፈ ለምሳሌ ስሞች [ስም = “ማሻ”፣ ጾታ = “ረ”፣ ዕድሜ = 25]
  
• ዝርዝር - ባለ አንድ-ልኬት ድርድር-አምድ፣ ለምሳሌ በርካታ አባሎችን ያቀፈ {1, 2, 3, 10, 42} or { "እምነት ተስፋ ፍቅር" }
  

ችግራችንን ለመፍታት በዋናነት በአይነቱ ላይ ፍላጎት እናደርጋለን ዝርዝር.

እዚህ ያለው ብልሃቱ በኃይል መጠይቅ ውስጥ ያሉ እቃዎች ዝርዝር ባናል ቁጥሮች ወይም ጽሁፍ ብቻ ሳይሆን ሌሎች ዝርዝሮች ወይም መዝገቦች ሊሆኑ ይችላሉ. መዝገቦችን (መዝገቦችን) ባካተተው እንደዚህ ባለ አስቸጋሪ ዝርዝር (ዝርዝር) ውስጥ ነው ማውጫችንን ማዞር ያለብን። በPower Query syntactic notation (በካሬ ቅንፎች ውስጥ ያሉ ግቤቶች፣ በተጠማዘዙ ቅንፎች ውስጥ ያሉ ዝርዝሮች) ይህ ይመስላል፡-

{

    [ ፈልግ = "ሴንት. ፒተርስበርግ”፣ ተካ = “ሴንት. ፒተርስበርግ”] ,

    [ ፈልግ = "ሴንት. ፒተርስበርግ”፣ ተካ = “ሴንት. ፒተርስበርግ”] ,

    [ ፈልግ = "ጴጥሮስ", ተካ = "ሴንት. ፒተርስበርግ”] ,

ወዘተ

}

እንዲህ ዓይነቱ ለውጥ የሚከናወነው በኃይል መጠይቅ ውስጥ የተገነባውን የኤም ቋንቋ ልዩ ተግባር በመጠቀም ነው - ሠንጠረዥ.ወደ መዛግብት. በቀመር አሞሌው ውስጥ በቀጥታ ለመተግበር፣ ይህንን ተግባር እዚያ ባለው የደረጃ ኮድ ላይ ያክሉ ምንጭ.

ነበር:

በኋላ:

የሰንጠረዡን ተግባር ከጨመረ በኋላ የጠረጴዛችን ገጽታ ይለወጣል - ወደ መዝገቦች ዝርዝር ይለወጣል. ከየትኛውም ቃል ቀጥሎ ባለው የሕዋስ ዳራ ላይ ጠቅ በማድረግ የነጠላ መዝገቦች ይዘቶች በእይታ መቃን ግርጌ ላይ ይታያሉ። ቅረጽ (ግን በአንድ ቃል አይደለም!)

ከላይ ከተጠቀሰው በተጨማሪ አንድ ተጨማሪ ጭረት መጨመር ምክንያታዊ ነው - ወደ መሸጎጫ (ማቋቋሚያ) የተፈጠረ ዝርዝራችን። ይህ የኃይል መጠይቅ የእኛን የመፈለጊያ ዝርዝራችንን አንድ ጊዜ ወደ ማህደረ ትውስታ እንዲጭን እና በኋላ ላይ ስንደርስ እሱን ለመተካት እንደገና እንዳናሰላ ያስገድደዋል። ይህንን ለማድረግ ቀመራችንን በሌላ ተግባር ጠቅልሉት - ዝርዝር.ማቆያ:

እንዲህ ዓይነቱ መሸጎጫ በከፍተኛ ፍጥነት (በበርካታ ጊዜ!) ከፍተኛ መጠን ያለው የመነሻ ውሂብ እንዲጸዳ በጣም ጉልህ የሆነ ጭማሪ ይሰጣል።

ይህ የመመሪያውን ዝግጅት ያጠናቅቃል.

ጠቅ ለማድረግ ይቀራል ቤት - ዝጋ እና ጫን - ዝጋ እና ጫን ወደ… (ቤት - ዝጋ እና ጫን - ዝጋ እና ጫን ወደ...)፣ አንድ አማራጭ ይምረጡ ዝምድና መፍጠር ብቻ ነው። (ግንኙነት ፍጠር ብቻ) እና ወደ ኤክሴል ይመለሱ.

ደረጃ 2. የውሂብ ሰንጠረዥን በመጫን ላይ

እዚህ ሁሉም ነገር ጨዋ ነው። እንደ ቀድሞው በማመሳከሪያው መጽሐፍ, በጠረጴዛው ውስጥ ወደ ማንኛውም ቦታ እንነሳለን, ትርን ጠቅ ያድርጉ መረጃ ቁልፍ ከጠረጴዛ / ክልል እና የእኛ ጠረጴዛ መረጃ ወደ የኃይል መጠይቅ ውስጥ ይገባል። ደረጃ በራስ-ሰር ታክሏል። የተሻሻለው ዓይነት (የተቀየረ ዓይነት) እንዲሁም ማስወገድ ይችላሉ:

ከእሱ ጋር ምንም ልዩ የዝግጅት እርምጃዎች አያስፈልጉም, እና ወደ በጣም አስፈላጊው ነገር እንቀጥላለን.

ደረጃ 3. ዝርዝሩን በመጠቀም ምትክ ያከናውኑ

ትዕዛዙን ተጠቅመን የተሰላ አምድ ወደ ዳታ ሰንጠረዡ እንጨምር አምድ ማከል - ብጁ አምድ (አምድ አክል - ብጁ አምድ): እና በሚከፈተው መስኮት ውስጥ የተጨመረውን አምድ ስም ያስገቡ (ለምሳሌ ፣ የተስተካከለ አድራሻ) እና የአስማት ተግባራችን ዘርዝር።አከማች:

ጠቅ ለማድረግ ይቀራል OK - እና ከተሰራው ምትክ ጋር አንድ አምድ እናገኛለን-

አስታውስ አትርሳ:

• የኃይል መጠይቅ ጉዳይ ሚስጥራዊነት ያለው በመሆኑ፣በቀጣይ መስመር ውስጥ ምንም ምትክ አልነበረም፣ ምክንያቱም በማውጫው ውስጥ “SPb” ሳይሆን “SPb” አለንና።
• በምንጭ ውሂቡ ውስጥ ብዙ ንዑስ ሕብረቁምፊዎች በአንድ ጊዜ የሚተኩ ከሆኑ (ለምሳሌ በ 7 ኛው መስመር ላይ ሁለቱንም "S-Pb" እና "Prospectus" መተካት ያስፈልግዎታል) ከዚያ ይህ ምንም ችግር አይፈጥርም (ከ ቀመሮች ከመተካት በተለየ መልኩ) የቀድሞው ዘዴ).
• በምንጭ ጽሑፍ (9 ኛ መስመር) ውስጥ የሚተካ ምንም ነገር ከሌለ, ምንም ስህተቶች አይከሰቱም (እንደገና, በቀመር ከመተካት).

የእንደዚህ አይነት ጥያቄ ፍጥነት በጣም በጣም ጨዋ ነው። ለምሳሌ፣ 5000 ረድፎች መጠን ላለው የመጀመሪያ መረጃ ሰንጠረዥ ይህ መጠይቅ ከአንድ ሰከንድ ባነሰ ጊዜ ውስጥ ተዘምኗል (ያለ ማቋት፣ በነገራችን ላይ፣ 3 ሰከንድ ያህል!)

የ List.Accumulate ተግባር እንዴት እንደሚሰራ

በመርህ ደረጃ, ይህ መጨረሻ ሊሆን ይችላል (ለእኔ ለመጻፍ እና ለማንበብ) ይህ ጽሑፍ. መቻል ብቻ ሳይሆን "በመከለያው ስር" እንዴት እንደሚሰራ ለመረዳት ከፈለጉ, ወደ ጥንቸል ጉድጓድ ውስጥ ትንሽ ዘልቀው መግባት እና ከዝርዝሩ ጋር መገናኘት ይኖርብዎታል. ሁሉንም የጅምላ ምትክ ያደረገውን ተግባር ያከማቹ. ለኛ ስራ።

የዚህ ተግባር አገባብ፡-

=ዝርዝር.ሰብስብ(ዝርዝር, ዘር, የማጠራቀሚያ)

የት

• ዝርዝር እየደጋገምን ያለንበት ዝርዝር ነው። 
• ዘር - የመጀመሪያ ሁኔታ
• የማጠራቀሚያ - በሚቀጥለው የዝርዝሩ አካል ላይ አንዳንድ ክዋኔዎችን (ሂሳብ ፣ ጽሑፍ ፣ ወዘተ) የሚያከናውን እና በልዩ ተለዋዋጭ ውስጥ የማስኬድ ውጤቱን የሚያከማች ተግባር።
  

በአጠቃላይ በኃይል መጠይቅ ውስጥ የመጻፍ ተግባራት አገባብ ይህን ይመስላል።

(argument1, argument2, … argumentN) => ከመከራከሪያ ጋር አንዳንድ ድርጊቶች

ለምሳሌ፣ የማጠቃለያው ተግባር እንደሚከተለው ሊወከል ይችላል፡-

(a, b) => ሀ + ለ

ለ List.Accumulate , ይህ የማጠራቀሚያ ተግባር ሁለት አስፈላጊ ነጋሪ እሴቶች አሉት (ማንኛውም ነገር ሊሰየም ይችላል, ነገር ግን የተለመዱ ስሞች ናቸው. ግዛት и የአሁኑ, ለዚህ ተግባር እንደ ኦፊሴላዊ እርዳታ, የት:

• ግዛት - ውጤቱ የተጠራቀመበት ተለዋዋጭ (የመጀመሪያው ዋጋ ከላይ የተጠቀሰው ነው ዘር)
  
• የአሁኑ - ከዝርዝሩ የሚቀጥለው የተደገመ እሴት ዝርዝር
  

ለምሳሌ, የሚከተለውን የግንባታ አመክንዮ ደረጃዎችን እንመልከት.

=ዝርዝር.ሰብስብ({3, 2, 5}, 10, (ግዛት፣ የአሁን) => ሁኔታ + ወቅታዊ)

ይህ የቅርብ ጊዜ የተከማቸ ነው። ግዛት እሴቱ የእኛ ዝርዝር ነው። በውጤቱም ተግባራቸውን እና ውጤቱን ሰብስቡ፡-

ትንሽ ካሰቡ ፣ ከዚያ List.Accumulate ተግባርን በመጠቀም ፣ ለምሳሌ ፣ የ Excel ተግባር CONCATENATE (በኃይል መጠይቅ ፣ አናሎግ ይባላል) ጽሑፍ. አዋህድ) የሚለውን አገላለጽ በመጠቀም፡-

ወይም ደግሞ ከፍተኛውን እሴት ይፈልጉ (የኤክሴል MAX ተግባርን መምሰል ፣ በኃይል መጠይቅ ውስጥ ዝርዝር.ማክስ):

ይሁን እንጂ የዝርዝር ዋና ባህሪይ.Accumulate ቀላል ጽሑፍን ወይም የቁጥር ዝርዝሮችን እንደ ክርክሮች ብቻ ሳይሆን በጣም ውስብስብ ነገሮችን - ለምሳሌ ዝርዝሮችን - ከዝርዝሮች ወይም ዝርዝሮች - ከመዝገብ (ሰላም, ማውጫ!) የማዘጋጀት ችሎታ ነው.

በችግራችን ውስጥ መተኪያውን ያከናወነውን ግንባታ እንደገና እንመልከት.

ዝርዝር። ሰብስብ(ማውጫ, [አድራሻ], (state,current) => ጽሑፍ. ተካ (ግዛት, የአሁኑ [አግኝ], የአሁኑ[ተካ]) )

በእውነቱ እዚህ ምን እየተደረገ ነው?

1. እንደ መጀመሪያው እሴት (ዘር) የመጀመሪያውን የተጨናነቀ ጽሑፍ ከአምዱ እንወስዳለን [አድራሻ] የእኛ ጠረጴዛ: 199034, ሴንት ፒተርስበርግ, ሴንት. ቤሪንጋ፣ ዲ. 1
2. ከዚያ ይዘርዝሩ። የዝርዝሩን ክፍሎች አንድ በአንድ ይደግማል - መምሪያ መጽሐፍ. የዚህ ዝርዝር እያንዳንዱ አካል "ምን ማግኘት - ምን እንደሚተካ" ወይም በሌላ አነጋገር በማውጫው ውስጥ የሚቀጥለው መስመር ጥንድ መስኮችን ያካተተ መዝገብ ነው.
3. የማጠራቀሚያው ተግባር ወደ ተለዋዋጭ ያደርገዋል ግዛት የመጀመሪያ እሴት (የመጀመሪያ አድራሻ 199034, ሴንት ፒተርስበርግ, ሴንት. ቤሪንጋ፣ ዲ. 1) እና በእሱ ላይ የማጠራቀሚያ ተግባርን ያከናውናል - መደበኛውን ኤም-ተግባር በመጠቀም የመተኪያ ክዋኔ ጽሑፍ ይተኩ (ከ Excel's SUBSTITUTE ተግባር ጋር ተመሳሳይ)። አገባቡ፡-

   ጽሑፍ። ተካ(የመጀመሪያው ጽሑፍ፣ የምንፈልገውን፣ የምንተካውን)

   እና እዚህ አለን:

• ግዛት ውስጥ የሚገኝ ቆሻሻ አድራሻችን ነው። ግዛት (ከዚያ መድረስ ዘር)
  
• ወቅታዊ[ፍለጋ] - የመስክ ዋጋ ማግኘት ከሚቀጥለው ተደጋጋሚ የዝርዝሩ ግቤት ማውጫ, በተለዋዋጭ ውስጥ የሚተኛ የአሁኑ
• ወቅታዊ[ተካ] - የመስክ ዋጋ ምትክ ከሚቀጥለው ተደጋጋሚ የዝርዝሩ ግቤት ማውጫውስጥ ተኝቶ የአሁኑ

ስለዚህ, ለእያንዳንዱ አድራሻ, በማውጫው ውስጥ ያሉትን ሁሉንም መስመሮች የመቁጠር ሙሉ ዑደት በእያንዳንዱ ጊዜ ይካሄዳል, ከ [ፈልግ] መስክ ላይ ያለውን ጽሑፍ በ [ተካ] ቦታ ይተካዋል.

ሀሳቡን እንዳገኘህ ተስፋ አደርጋለሁ 🙂

• ቀመሮችን በመጠቀም ጽሑፍን በጅምላ ይተኩ
  
• በኃይል መጠይቅ ውስጥ መደበኛ መግለጫዎች (RegExp)