მონაცემთა ბილიკის პარამეტრიზაცია Power Query-ში

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

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

პრობლემის ფორმულირება

დავუშვათ, ჩვენ გვაქვს საქაღალდეში E:გაყიდვების ანგარიშები დევს ფაილი ტოპ 100 პროდუქტი.xls, რომელიც არის ატვირთვა ჩვენი კორპორატიული მონაცემთა ბაზიდან ან ERP სისტემიდან (1C, SAP და ა.შ.) ეს ფაილი შეიცავს ინფორმაციას ყველაზე პოპულარული საქონლის შესახებ და შიგნით ასე გამოიყურება:

ალბათ აშკარაა, რომ Excel-ში ამ ფორმით მასთან მუშაობა თითქმის შეუძლებელია: ერთ-ერთში ცარიელი რიგები მონაცემებით, გაერთიანებული უჯრედები, დამატებითი სვეტები, მრავალ დონის სათაური და ა.შ. ჩაერევა.

ამიტომ, იმავე საქაღალდეში ამ ფაილის გვერდით ვქმნით სხვა ახალ ფაილს დამმუშავებელი.xlsx, რომელშიც ჩვენ შევქმნით Power Query მოთხოვნას, რომელიც ჩატვირთავს მახინჯ მონაცემებს წყაროს ატვირთვის ფაილიდან ტოპ 100 პროდუქტი.xlsდა დაალაგეთ ისინი წესრიგში:

მოთხოვნის გაკეთება გარე ფაილზე

ფაილის გახსნა დამმუშავებელი.xlsx, აირჩიეთ ჩანართზე თარიღი Command მიიღეთ მონაცემები – ფაილიდან – Excel სამუშაო წიგნიდან (მონაცემები — მიიღეთ მონაცემები — ფაილიდან — Excel-დან), შემდეგ მიუთითეთ წყაროს ფაილის ადგილმდებარეობა და ჩვენ გვჭირდება ფურცელი. არჩეული მონაცემები ჩაიტვირთება Power Query რედაქტორში:

დავუბრუნდეთ მათ ნორმალურად:

1. წაშალეთ ცარიელი ხაზები მთავარი — ხაზების წაშლა — ცარიელი ხაზების წაშლა (მთავარი — რიგების წაშლა — ცარიელი რიგების ამოღება).
2. წაშალეთ არასაჭირო ზედა 4 ხაზი მთავარი — რიგების წაშლა — ზედა რიგების წაშლა (მთავარი — რიგების წაშლა — ზედა რიგების ამოღება).
3. ღილაკით ასწიეთ პირველი რიგი ცხრილის სათაურზე გამოიყენეთ პირველი ხაზი, როგორც სათაურები tab მთავარი (მთავარი — გამოიყენეთ პირველი რიგი, როგორც სათაური).
4. გამოყავით ხუთნიშნა სტატია პროდუქტის სახელიდან მეორე სვეტში ბრძანების გამოყენებით გაყოფილი სვეტი tab ტრანსფორმაციის (ტრანსფორმა - გაყოფილი სვეტი).
5. წაშალეთ არასაჭირო სვეტები და დაარქვით დარჩენილთა სათაურებს უკეთესი ხილვადობისთვის.

შედეგად, უნდა მივიღოთ შემდეგი, ბევრად უფრო სასიამოვნო სურათი:

რჩება ამ გაძლიერებული ცხრილის ატვირთვა ჩვენს ფაილში არსებულ ფურცელზე დამმუშავებელი.xlsx გუნდი დახურეთ და გადმოწერეთ (მთავარი — დახურვა და ჩატვირთვა) tab მთავარი:

მოთხოვნაში ფაილის გზის პოვნა

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

ფანჯარაში, რომელიც იხსნება, მეორე სტრიქონი მაშინვე ავლენს მყარი კოდირებულ გზას ჩვენი ორიგინალური ატვირთვის ფაილისკენ. თუ ჩვენ შეგვიძლია შევცვალოთ ეს ტექსტური სტრიქონი პარამეტრით, ცვლადით ან Excel ფურცლის უჯრედის ბმულით, სადაც ეს ბილიკი წინასწარ არის დაწერილი, მაშინ ჩვენ შეგვიძლია ადვილად შევცვალოთ მოგვიანებით.

დაამატეთ ჭკვიანი ცხრილი ფაილის ბილიკით

მოდით დავხუროთ Power Query ახლა და დავუბრუნდეთ ჩვენს ფაილს დამმუშავებელი.xlsx. მოდით დავამატოთ ახალი ცარიელი ფურცელი და შევქმნათ მასზე პატარა „ჭკვიანი“ ცხრილი, რომლის ერთადერთ უჯრედში ჩაიწერება ჩვენი წყაროს მონაცემთა ფაილის სრული გზა:

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

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

თუ ვივარაუდებთ, რომ წყაროს მონაცემთა ფაილი ყოველთვის დევს იმავე საქაღალდეში, როგორც ჩვენი პროცესორი, მაშინ ჩვენ გვჭირდება გზა შეიძლება ჩამოყალიბდეს შემდეგი ფორმულით:

=LEFT(CELL("ფაილის სახელი");FIND("[";CELL("ფაილის სახელი"))-1)&"ტოპ 100 პროდუქტი.xls"

ან ინგლისურ ვერსიაში:

=LEFT(CELL(«ფაილის სახელი»);FIND(«[«;CELL(«ფაილის სახელი»))-1)&»Топ-100 товаров.xls»

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

შეკითხვისას ბილიკის პარამეტრიზაცია

რჩება ბოლო და ყველაზე მნიშვნელოვანი შეხება - თხოვნაში ჩაწეროთ გზა წყაროს ფაილისკენ ტოპ 100 პროდუქტი.xlsჩვენს მიერ შექმნილი „ჭკვიანი“ ცხრილის A2 უჯრედზე მითითებით პარამეტრები.

ამისათვის დავუბრუნდეთ Power Query მოთხოვნას და ისევ გავხსნათ გაფართოებული რედაქტორი tab მიმოხილვა (ნახვა - გაფართოებული რედაქტორი). ტექსტის სტრიქონის ბილიკის ნაცვლად ბრჭყალებში “E:Sales ReportsTop 100 product.xlsx” წარმოგიდგენთ შემდეგ სტრუქტურას:

Excel.Current Workbook(){[Name="პარამეტრები"]}[შინაარსი]0 {}[გზა წყაროს მონაცემებისკენ]

ვნახოთ, რისგან შედგება:

• Excel.Current Workbook() არის M ენის ფუნქცია მიმდინარე ფაილის შინაარსზე წვდომისათვის
• {[Name="პარამეტრები"]}[შინაარსი] - ეს არის წინა ფუნქციის დახვეწის პარამეტრი, რაც მიუთითებს იმაზე, რომ გვინდა მივიღოთ "ჭკვიანი" ცხრილის შინაარსი პარამეტრები
• [გზა წყაროს მონაცემებისკენ] არის ცხრილის სვეტის სახელი პარამეტრებირომელსაც ჩვენ მივმართავთ
  
• 0 {} არის რიგის ნომერი ცხრილში პარამეტრებისაიდანაც გვინდა მონაცემების აღება. ქუდი არ ითვლის და ნუმერაცია იწყება ნულიდან და არა ერთიდან.

ეს ყველაფერი, ფაქტობრივად.

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

• რა არის Power Query და რატომ არის საჭირო Microsoft Excel-ში მუშაობისას
• როგორ შემოვიტანოთ მცურავი ტექსტის ფრაგმენტი Power Query-ში
• XNUMXD Crosstab-ის ხელახალი დიზაინი ბრტყელ მაგიდაზე Power Query-ით