სრული ტექსტის ჩანაცვლება Power Query-ში List.Acumulate ფუნქციით

როგორ სწრაფად და ნაყარად შევცვალოთ ტექსტი საცნობარო სიის მიხედვით ფორმულებით - ჩვენ უკვე დავახარისხეთ. ახლა ვცადოთ ამის გაკეთება Power Query-ში.

როგორც ხშირად ხდება ასრულებს ეს ამოცანა ბევრად უფრო ადვილია, ვიდრე ახსნა რატომ მუშაობს, მაგრამ ვცადოთ ორივეს გაკეთება 🙂

ასე რომ, ჩვენ გვაქვს ორი "ჭკვიანი" დინამიური ცხრილი, შექმნილი ჩვეულებრივი დიაპაზონიდან კლავიატურის მალსახმობით Ctrl+T ან გუნდი მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი):

პირველ მაგიდას დავურეკე თარიღიმეორე მაგიდა - დირექტორიაველის გამოყენებით მაგიდის სახელი (მაგიდის სახელი) tab კონსტრუქტორი (დიზაინი).

დავალება: შეცვალეთ მისამართები ცხრილში თარიღი ყველა მოვლენა სვეტიდან Პოვნა სახელმძღვანელო მათ შესაბამის სწორ კოლეგებს სვეტიდან შემცვლელი. უჯრებში დარჩენილი ტექსტი ხელუხლებელი უნდა დარჩეს.

ნაბიჯი 1. ჩატვირთეთ დირექტორია Power Query-ში და გადააქციეთ ის სიაში

აქტიური უჯრედის დაყენების შემდეგ საცნობარო ცხრილის ნებისმიერ ადგილას, დააწკაპუნეთ ჩანართზე თარიღი (თარიღი)ან ჩანართზე დენის მოთხოვნა (თუ გაქვთ Excel-ის ძველი ვერსია და დააინსტალირეთ Power Query, როგორც დანამატი ცალკე ჩანართზე) ღილაკზე მაგიდიდან / დიაპაზონიდან (ცხრილიდან/დიაპაზონიდან).

საცნობარო ცხრილი ჩაიტვირთება Power Query შეკითხვის რედაქტორში:

იმისათვის, რომ არ ჩაერიოთ, ავტომატურად დაემატა ნაბიჯი შეცვლილი ტიპი (შეცვლილი ტიპი) მარჯვენა პანელში, გამოყენებული ნაბიჯები შეიძლება უსაფრთხოდ წაიშალოს და დარჩეს მხოლოდ ნაბიჯი წყარო (წყარო):

ახლა, შემდგომი გარდაქმნებისა და ჩანაცვლების შესასრულებლად, ეს ცხრილი უნდა გადავაქციოთ სიაში (სიაში).

ლირიკული დიგრესია

• მაგიდის არის ორგანზომილებიანი მასივი, რომელიც შედგება რამდენიმე მწკრივისა და სვეტისგან.
• ჩანაწერი (ჩანაწერი) - ერთგანზომილებიანი მასივი-სტრიქონი, რომელიც შედგება რამდენიმე ველისაგან-ელემენტისგან სახელებით, მაგალითად [სახელი = "მაშა", სქესი = "ვ", ასაკი = 25]
  
• სია – ერთგანზომილებიანი მასივი-სვეტი, რომელიც შედგება რამდენიმე ელემენტისგან, მაგალითად {1, 2, 3, 10, 42} or { "რწმენა იმედი სიყვარული" }
  

ჩვენი პრობლემის გადასაჭრელად, პირველ რიგში დავინტერესდებით ტიპი სია.

ხრიკი აქ არის ის, რომ Power Query-ში სიის ელემენტები შეიძლება იყოს არა მხოლოდ ბანალური ნომრები ან ტექსტი, არამედ სხვა სიები ან ჩანაწერები. სწორედ ასეთ სახიფათო სიაში (სიაში), რომელიც შედგება ჩანაწერებისგან (ჩანაწერები), უნდა გადავაქციოთ ჩვენი დირექტორია. Power Query სინტაქსურ აღნიშვნაში (ჩანაწერები კვადრატულ ფრჩხილებში, სიები ხვეული ფრჩხილებში) ასე გამოიყურება:

{

    [ მოძებნა = „წმ. პეტერბურგი“, Replace = „St. პეტერბურგი"] ,

    [ მოძებნა = „წმ. პეტერბურგი“, Replace = „St. პეტერბურგი"] ,

    [ Find = “Peter”, Replace = “St. პეტერბურგი"] ,

ა.შ.

}

ასეთი ტრანსფორმაცია ხორციელდება Power Query-ში ჩაშენებული M ენის სპეციალური ფუნქციის გამოყენებით - Table.ToRecords. პირდაპირ ფორმულების ზოლში გამოსაყენებლად, დაამატეთ ეს ფუნქცია საფეხურის კოდს წყარო.

Ის იყო:

შემდეგ:

Table.ToRecords ფუნქციის დამატების შემდეგ ჩვენი ცხრილის გარეგნობა შეიცვლება – გადაიქცევა ჩანაწერების სიაში. ცალკეული ჩანაწერების შიგთავსის ნახვა შესაძლებელია ხედის პანელის ბოლოში რომელიმე სიტყვის გვერდით უჯრედის ფონზე დაწკაპუნებით. ჩაწერა (მაგრამ არა ერთი სიტყვით!)

გარდა ზემოაღნიშნულისა, აზრი აქვს კიდევ ერთი შტრიხის დამატებას - ჩვენი შექმნილი სიის ქეშირებას (ბუფერს). ეს აიძულებს Power Query-ს, ერთხელ ჩატვირთოს ჩვენი საძიებო სია მეხსიერებაში და არ გადაითვალოს იგი ხელახლა, როცა მოგვიანებით მივიღებთ მას მის ჩანაცვლებისთვის. ამისათვის გადაიტანეთ ჩვენი ფორმულა სხვა ფუნქციაში - სია.ბუფერი:

ასეთი ქეშირება მისცემს სიჩქარის ძალიან შესამჩნევ ზრდას (რამდენჯერმე!) დიდი რაოდენობით საწყისი მონაცემების გასასუფთავებლად.

ამით სრულდება სახელმძღვანელოს მომზადება.

რჩება დაწკაპუნება მთავარი – დახურვა და ჩატვირთვა – დახურვა და ჩატვირთვა… (მთავარი — დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა..), აირჩიეთ ვარიანტი უბრალოდ შექმენით კავშირი (მხოლოდ შექმენით კავშირი) და დაუბრუნდით Excel-ს.

ნაბიჯი 2. მონაცემთა ცხრილის ჩატვირთვა

აქ ყველაფერი ბანალურია. როგორც ადრე საცნობარო წიგნში, ჩვენ ვდგებით ცხრილის ნებისმიერ ადგილას, დააჭირეთ ჩანართს თარიღი ღილაკს ცხრილიდან / დიაპაზონიდან და ჩვენი მაგიდა თარიღი შედის Power Query-ში. ავტომატურად დამატებული ნაბიჯი შეცვლილი ტიპი (შეცვლილი ტიპი) თქვენ ასევე შეგიძლიათ წაშალოთ:

მასთან სპეციალური მოსამზადებელი მოქმედებები არ არის საჭირო და გადავდივართ ყველაზე მნიშვნელოვანზე.

ნაბიჯი 3. შეასრულეთ ჩანაცვლება List.Acumulate ფუნქციის გამოყენებით

მოდით დავამატოთ გამოთვლილი სვეტი ჩვენს მონაცემთა ცხრილში ბრძანების გამოყენებით სვეტის დამატება – მორგებული სვეტი (სვეტის დამატება — მორგებული სვეტი): და შეიყვანეთ დამატებული სვეტის სახელი გახსნილ ფანჯარაში (მაგალითად, შესწორებული მისამართი) და ჩვენი ჯადოსნური ფუნქცია სია.დაგროვება:

რჩება დაწკაპუნება OK – და ჩვენ ვიღებთ სვეტს შესრულებული ჩანაცვლებით:

Გაითვალისწინე:

• მას შემდეგ, რაც Power Query არის რეგისტრის მგრძნობიარე, არ ყოფილა ჩანაცვლება ბოლო სტრიქონში, რადგან დირექტორიაში გვაქვს „SPb“ და არა „SPb“.
• თუ წყაროს მონაცემებში ერთდროულად რამდენიმე ქვესტრიქონია ჩასაცვლელი (მაგალითად, მე-7 სტრიქონში თქვენ უნდა შეცვალოთ როგორც „S-Pb“ და „Prospectus“), მაშინ ეს არ ქმნის რაიმე პრობლემას (განსხვავებით ფორმულებით ჩანაცვლებისგან. წინა მეთოდი).
• თუ საწყის ტექსტში არაფერია ჩასანაცვლებელი (მე-9 სტრიქონი), მაშინ შეცდომები არ ხდება (განსხვავებით, ისევ ფორმულებით ჩანაცვლებისგან).

ასეთი მოთხოვნის სიჩქარე ძალიან, ძალიან ღირსეულია. მაგალითად, საწყისი მონაცემების ცხრილისთვის 5000 მწკრივის ზომით, ეს მოთხოვნა განახლდა წამზე ნაკლებ დროში (სხვათა შორის, ბუფერირების გარეშე, დაახლოებით 3 წამში!)

როგორ მუშაობს List.Acumulate ფუნქცია

პრინციპში, ეს შეიძლება იყოს ამ სტატიის დასასრული (მე რომ დავწერო და შენ წაიკითხო). თუ გსურთ არა მხოლოდ შეძლოთ, არამედ გესმოდეთ, თუ როგორ მუშაობს ის „კაპოტის ქვეშ“, მაშინ მოგიწევთ ცოტა ღრმად ჩაყვინთოთ კურდღლის ხვრელში და გაუმკლავდეთ სიას. Accumulate ფუნქცია, რომელმაც შეასრულა მთელი ძირითადი ჩანაცვლება. იმუშავე ჩვენთვის.

ამ ფუნქციის სინტაქსია:

=List.Acumulate(სია, თესლი, აკუმულატორი)

სადაც

• სია არის სია, რომლის ელემენტებსაც ვიმეორებთ. 
• თესლი - საწყისი მდგომარეობა
• აკუმულატორი – ფუნქცია, რომელიც ასრულებს რაიმე ოპერაციას (მათემატიკას, ტექსტს და ა.შ.) სიის შემდეგ ელემენტზე და აგროვებს დამუშავების შედეგს სპეციალურ ცვლადში.
  

ზოგადად, Power Query-ში ფუნქციების ჩაწერის სინტაქსი ასე გამოიყურება:

(არგუმენტი1, არგუმენტი2, … არგუმენტიN) => რამდენიმე მოქმედება არგუმენტებით

მაგალითად, შეჯამების ფუნქცია შეიძლება წარმოდგენილი იყოს როგორც:

(a, b) => a + b

List.Accumulate-ისთვის, ამ აკუმულატორის ფუნქციას აქვს ორი საჭირო არგუმენტი (მათ შეიძლება რაიმეს დასახელება, მაგრამ ჩვეულებრივი სახელებია იყო и მიმდინარე, როგორც ამ ფუნქციის ოფიციალურ დახმარებაში, სადაც:

• იყო – ცვლადი, სადაც შედეგი გროვდება (მისი საწყისი მნიშვნელობა არის ზემოთ ნახსენები თესლი)
  
• მიმდინარე – შემდეგი გამეორებული მნიშვნელობა სიიდან სია
  

მაგალითად, მოდით შევხედოთ შემდეგი კონსტრუქციის ლოგიკის საფეხურებს:

=List.Acumulate({3, 2, 5}, 10, (მდგომარეობა, მიმდინარე) => მდგომარეობა + მიმდინარე)

ეს არის ბოლო დაგროვილი იყო მნიშვნელობა არის ჩვენი სია. შეაგროვეთ ფუნქცია და გამომავალი შედეგი:

თუ ცოტას ფანტაზიორობთ, შემდეგ ფუნქციის List.Accumulate-ის გამოყენებით შეგიძლიათ, მაგალითად, Excel ფუნქციის CONCATENATE-ის სიმულაცია (Power Query-ში მის ანალოგს ე.წ. ტექსტი.შეთავსება) გამოთქმის გამოყენებით:

ან თუნდაც მოძებნოთ მაქსიმალური მნიშვნელობა (Excel-ის MAX ფუნქციის იმიტაცია, რომელსაც Power Query-ში ე.წ სია.მაქს):

თუმცა, List.Acumulate-ის მთავარი მახასიათებელია არა მხოლოდ მარტივი ტექსტის ან რიცხვითი სიების არგუმენტების სახით დამუშავების შესაძლებლობა, არამედ უფრო რთული ობიექტების - მაგალითად, სიები-სიიდან ან სიები-ჩანაწერები (გამარჯობა, დირექტორია!)

მოდით კიდევ ერთხელ გადავხედოთ კონსტრუქციას, რომელმაც ჩაანაცვლა ჩვენს პრობლემაში:

List.Acumulate(დირექტორია, [მისამართი], (მდგომარეობა, მიმდინარე) => ტექსტი. ჩანაცვლება (მდგომარეობა, მიმდინარე[ძებნა], მიმდინარე [ჩანაცვლება]) )

მართლა რა ხდება აქ?

1. როგორც საწყისი მნიშვნელობა (თესლი) სვეტიდან ვიღებთ პირველ მოუხერხებელ ტექსტს [მისამართი] ჩვენი მაგიდა: 199034, პეტერბურგი, ქ. ბერინგა, დ. 1
2. შემდეგ სია. დააგროვეთ გამეორებები სიის ელემენტებზე სათითაოდ - სახელმძღვანელო. ამ სიის თითოეული ელემენტი არის ჩანაწერი, რომელიც შედგება წყვილი ველისგან „რა ვიპოვო – რით შევცვალო“ ან, სხვა სიტყვებით რომ ვთქვათ, დირექტორიაში შემდეგი ხაზი.
3. აკუმულატორის ფუნქცია აყენებს ცვლადში იყო საწყისი მნიშვნელობა (პირველი მისამართი 199034, პეტერბურგი, ქ. ბერინგა, დ. 1) და ასრულებს მასზე აკუმულატორის ფუნქციას – ჩანაცვლების ოპერაციას სტანდარტული M-ფუნქციის გამოყენებით ტექსტი.ჩანაცვლება (Excel-ის SUBSTITUTE ფუნქციის ანალოგი). მისი სინტაქსია:

   Text.Replace (ორიგინალური ტექსტი, რას ვეძებთ, რითი ვცვლით)

   და აქ გვაქვს:

• იყო არის ჩვენი ბინძური მისამართი, რომელიც დევს იყო (იქიდან მისვლა თესლი)
  
• მიმდინარე[ძებნა] - ველის მნიშვნელობა Პოვნა სიის შემდეგი განმეორებითი ჩანაწერიდან დირექტორია, რომელიც დევს ცვლადში მიმდინარე
• მიმდინარე[ჩანაცვლება] - ველის მნიშვნელობა შემცვლელი სიის შემდეგი განმეორებითი ჩანაწერიდან დირექტორიაიწვა მიმდინარე

ამრიგად, თითოეული მისამართისთვის, დირექტორიაში ყველა სტრიქონის აღრიცხვის სრული ციკლი ყოველ ჯერზე იმართება, ტექსტი [Find] ველიდან [Replace] ველის მნიშვნელობით იცვლება.

იმედია მიხვდით იდეას 🙂

• სიაში ტექსტის ნაყარი ჩანაცვლება ფორმულების გამოყენებით
  
• რეგულარული გამონათქვამები (RegExp) Power Query-ში