შექმენით ცხრილები სხვადასხვა სათაურებით მრავალი წიგნიდან

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

ჩვენ გვაქვს რამდენიმე ფაილი (ჩვენს მაგალითში - 4 ცალი, ზოგადად - რამდენიც გსურთ) ერთ საქაღალდეში რეპორტაჟი:

შიგნით, ეს ფაილები ასე გამოიყურება:

სადაც:

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

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

ნაბიჯი 1. სვეტების სახელების დირექტორიას მომზადება

პირველი, რაც უნდა გააკეთოთ, არის საცნობარო წიგნის მომზადება სვეტების სახელების ყველა შესაძლო ვარიანტით და მათი სწორი ინტერპრეტაციით:

ჩვენ ვაქცევთ ამ სიას დინამიურ „ჭკვიან“ ცხრილად, ჩანართზე არსებული ღილაკის ფორმატირება როგორც ცხრილის გამოყენებით მთავარი (მთავარი - ფორმატირება როგორც ცხრილი) ან კლავიატურის მალსახმობი Ctrl+T და ჩატვირთეთ იგი Power Query-ში ბრძანებით მონაცემები – ცხრილიდან/დიაპაზონიდან (მონაცემები - ცხრილიდან/დიაპაზონიდან). Excel-ის ბოლო ვერსიებში მას დაარქვეს სახელი ფოთლებით (ფურცლიდან).

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

=Table.ToRows(წყარო)

ეს ბრძანება გარდაქმნის წინა ეტაპზე დატვირთულს წყარო საცნობარო ცხრილი სიაში, რომელიც შედგება წყობილი სიებისგან (List), რომელთაგან თითოეული, თავის მხრივ, არის მნიშვნელობების წყვილი ეს იყო-გახდა ერთი ხაზიდან:

ჩვენ დაგვჭირდება ამ ტიპის მონაცემები ცოტა მოგვიანებით, ყველა ჩატვირთული ცხრილიდან სათაურების მასობრივი გადარქმევისას.

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

ნაბიჯი 2. ჩვენ ვტვირთავთ ყველაფერს ყველა ფაილიდან, როგორც არის

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

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

და შემდეგ გააფართოვეთ ყველა გადმოწერილი ფაილის შინაარსი (ორობითი) ღილაკი ორმაგი ისრებით სვეტის სათაურში Content:

Power Query პირველი ფაილის მაგალითზე (Vostok.xlsx) გვკითხავს იმ ფურცლის სახელს, რომლის აღება გვინდა თითოეული სამუშაო წიგნიდან – აირჩიეთ ფოტო და დააჭირეთ OK:

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

1. Power Query აიღებს პირველ ფაილს საქაღალდიდან (ჩვენ გვექნება იგი Vostok.xlsx - ვხედავ ფაილის მაგალითი) მაგალითად და ახდენს მისი შინაარსის იმპორტს მოთხოვნის შექმნით ნიმუშის ფაილის კონვერტაცია. ამ შეკითხვას ექნება რამდენიმე მარტივი ნაბიჯი, როგორიცაა წყარო (ფაილის წვდომა) ნავიგაცია (ფურცლის შერჩევა) და შესაძლოა სათაურების ამაღლება. ამ მოთხოვნას შეუძლია მონაცემების ჩატვირთვა მხოლოდ ერთი კონკრეტული ფაილიდან Vostok.xlsx.
   
2. ამ მოთხოვნის საფუძველზე შეიქმნება მასთან დაკავშირებული ფუნქცია ფაილის კონვერტაცია (მითითებულია დამახასიათებელი ხატით f_x), სადაც წყარო ფაილი აღარ იქნება მუდმივი, არამედ ცვლადი მნიშვნელობა – პარამეტრი. ამრიგად, ამ ფუნქციას შეუძლია მონაცემების ამოღება ნებისმიერი წიგნიდან, რომელსაც მასში არგუმენტად ჩავსვამთ.
   
3. ფუნქცია თავის მხრივ გამოყენებული იქნება სვეტიდან თითოეულ ფაილზე (ორობითი). Content – ამაზე პასუხისმგებელია ნაბიჯი მორგებული ფუნქციის გამოძახება ჩვენს მოთხოვნაში, რომელიც ამატებს სვეტს ფაილების სიას ფაილის კონვერტაცია თითოეული სამუშაო წიგნიდან იმპორტის შედეგებით:

   

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

   
   

ნაბიჯი 3. მოსახვეწი

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

• სვეტები შებრუნებულია.
• ბევრი დამატებითი ხაზი (ცარიელი და არა მხოლოდ).
• ცხრილის სათაურები არ აღიქმება როგორც სათაურები და შერეულია მონაცემებთან.
  

თქვენ შეგიძლიათ მოაგვაროთ ყველა ეს პრობლემა ძალიან მარტივად – უბრალოდ შეცვალეთ Convert Sample File მოთხოვნა. ყველა კორექტირება, რომელსაც ჩვენ მას ვუკეთებთ, ავტომატურად მოხვდება დაკავშირებულ Convert ფაილის ფუნქციაში, რაც ნიშნავს, რომ ისინი მოგვიანებით იქნება გამოყენებული თითოეული ფაილიდან მონაცემების იმპორტისას.

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

იმისათვის, რომ სხვადასხვა ფაილებიდან სვეტები ავტომატურად მოთავსდეს ერთმანეთზე მოგვიანებით, მათ უნდა დაერქვას იგივე სახელი. თქვენ შეგიძლიათ განახორციელოთ ასეთი მასობრივი გადარქმევა ადრე შექმნილი დირექტორიას მიხედვით M-კოდის ერთი ხაზით. ისევ დავაჭიროთ ღილაკს f_x ფორმულების ზოლში და დაამატეთ ფუნქცია შესაცვლელად:

= Table.RenameColumns(#"Elevated Headers", Headers, MissingField.Ignore)

ეს ფუნქცია იღებს ცხრილს წინა საფეხურიდან ამაღლებული სათაურები და გადარქმევს მასში შემავალ ყველა სვეტს წყობილი საძიებო სიის მიხედვით სიახლეები. მესამე არგუმენტი MissingField.იგნორირება საჭიროა ისე, რომ იმ სათაურებზე, რომლებიც დირექტორიაშია, მაგრამ არ არის ცხრილში, შეცდომა არ მოხდეს.

სინამდვილეში, ეს ყველაფერია.

თხოვნას ვუბრუნდები რეპორტაჟი ჩვენ ვნახავთ სრულიად განსხვავებულ სურათს - ბევრად უფრო ლამაზს, ვიდრე წინა:

• რა არის Power Query, Power Pivot, Power BI და რატომ სჭირდება ისინი Excel-ის მომხმარებელს
  
• მონაცემების შეგროვება ყველა ფაილიდან მოცემულ საქაღალდეში
• წიგნის ყველა ფურცლიდან მონაცემების შეგროვება ერთ ცხრილში