ცხრილების აწყობა სხვადასხვა Excel ფაილებიდან Power Query-ით

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

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

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

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

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

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

ჩვენ ვირჩევთ იარაღს

გადაწყვეტისთვის, ჩვენ გვჭირდება Excel 2016-ის უახლესი ვერსია (აუცილებელი ფუნქციონირება უკვე ჩაშენებულია მასში ნაგულისხმევად) ან Excel 2010-2013 წინა ვერსიები უფასო დანამატით დაინსტალირებული. დენის მოთხოვნა Microsoft-ისგან (ჩამოტვირთეთ აქ). Power Query არის სუპერ მოქნილი და სუპერ ძლიერი ინსტრუმენტი Excel-ში მონაცემების გარე სამყაროდან ჩასატვირთად, შემდეგ მისი ამოღებისა და დამუშავებისთვის. Power Query მხარს უჭერს მონაცემთა თითქმის ყველა არსებულ წყაროს – ტექსტური ფაილებიდან SQL-მდე და Facebook-მდეც კი 🙂

თუ არ გაქვთ Excel 2013 ან 2016, მაშინ აღარ შეგიძლიათ წაიკითხოთ (უბრალოდ ხუმრობთ). Excel-ის ძველ ვერსიებში ასეთი ამოცანის შესრულება შესაძლებელია მხოლოდ მაკროს პროგრამირებით Visual Basic-ში (რაც ძალიან რთულია დამწყებთათვის) ან ერთფეროვანი ხელით კოპირებით (რასაც დიდი დრო სჭირდება და შეცდომებს წარმოშობს).

ნაბიჯი 1. ნიმუშის სახით ერთი ფაილის იმპორტი

პირველ რიგში, მოდით, მაგალითის სახით შევიყვანოთ მონაცემები ერთი სამუშაო წიგნიდან, რათა Excel-მა „აიღოს იდეა“. ამისათვის შექმენით ახალი ცარიელი სამუშაო წიგნი და…

• თუ გაქვთ Excel 2016, გახსენით ჩანართი თარიღი და მაშინ შექმენით შეკითხვა – ფაილიდან – წიგნიდან (მონაცემები - ახალი მოთხოვნა - ფაილიდან - Excel-დან)
• თუ გაქვთ Excel 2010-2013 დაინსტალირებული Power Query დანამატით, გახსენით ჩანართი დენის მოთხოვნა და აირჩიეთ მასზე ფაილიდან – წიგნიდან (ფაილიდან - Excel-დან)

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

თუ დააჭირეთ ღილაკს ამ ფანჯრის ქვედა მარჯვენა კუთხეში ಡೌನ್‌ಲೋಡ್ ಮಾಡಿ (ჩატვირთვა), მაშინ ცხრილი დაუყოვნებლივ შემოიტანება ფურცელზე თავდაპირველი სახით. ერთი ფაილისთვის, ეს კარგია, მაგრამ ჩვენ გვჭირდება ბევრი ასეთი ფაილის ჩატვირთვა, ასე რომ, ჩვენ ოდნავ განსხვავებულად წავალთ და დააჭირეთ ღილაკს კორექცია (რედაქტირება). ამის შემდეგ, Power Query შეკითხვის რედაქტორი უნდა იყოს ნაჩვენები ცალკე ფანჯარაში ჩვენი მონაცემებით წიგნიდან:

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

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

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

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

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

თქვენ შეგიძლიათ გამორიცხოთ ხაზები შეცდომებით ან ცარიელი ხაზებით, ისევე როგორც არასაჭირო მენეჯერები ან მომხმარებლები, მარტივი ფილტრის გამოყენებით:

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

მსუბუქი და ელეგანტური, არა?

ნაბიჯი 2. მოდით გარდავქმნათ ჩვენი მოთხოვნა ფუნქციად

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

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

ახლა მოდით გავაკეთოთ რამდენიმე კორექტირება:

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

ყველა. Დააკლიკეთ ფერი და უნდა ნახოთ ეს:

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

ნაბიჯი 3. ყველა ფაილის შეგროვება

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

დაწკაპეთ შეცვლა (რედაქტირება) და ისევ შევდივართ ნაცნობ შეკითხვის რედაქტორის ფანჯარაში.

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

დაწკაპვით OK შექმნილი სვეტი უნდა დაემატოს ჩვენს ცხრილს მარჯვნივ.

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

ახლა კი "wow moment" - დააწკაპუნეთ ხატულაზე საკუთარი ისრებით დამატებული სვეტის ზედა მარჯვენა კუთხეში ჩვენი ფუნქციით:

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

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

ყველა! Დააკლიკეთ მთავარი – დახურვა და ჩატვირთვა (მთავარი — დახურვა და ჩატვირთვა). ყველა ქალაქისთვის მოთხოვნით შეგროვებული ყველა მონაცემი აიტვირთება მიმდინარე Excel ფურცელზე „ჭკვიანი ცხრილის“ ფორმატში:

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

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

PS

შესწორება. 2017 წლის იანვრის განახლებების შემდეგ Power Query-მ ისწავლა Excel-ის სამუშაო წიგნების დამოუკიდებლად შეგროვება, ანუ ცალკე ფუნქციის გაკეთება აღარ არის საჭირო – ეს ხდება ავტომატურად. ამრიგად, ამ სტატიიდან მეორე ნაბიჯი აღარ არის საჭირო და მთელი პროცესი შესამჩნევად მარტივი ხდება:

1. არჩევა შექმენით მოთხოვნა – ფაილიდან – საქაღალდედან – აირჩიეთ საქაღალდე – OK
2. ფაილების სიის გამოჩენის შემდეგ დააჭირეთ ღილაკს შეცვლა
3. შეკითხვის რედაქტორის ფანჯარაში გააფართოვეთ ორობითი სვეტი ორმაგი ისრით და აირჩიეთ ფურცლის სახელი, რომელიც უნდა აიღოთ თითოეული ფაილიდან

Და სულ ეს არის! Სიმღერა!

• ჯვარედინი ტაბლეტის ხელახალი დიზაინი ბრტყელად, რომელიც შესაფერისია საყრდენი მაგიდების შესაქმნელად
• ანიმაციური ბუშტების დიაგრამის აგება Power View-ში
• მაკრო ფურცლების შეკრება სხვადასხვა Excel ფაილებიდან ერთში