Pivot-ის უპირატესობები მონაცემთა მოდელის მიხედვით

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

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

თუმცა, სანამ ამ „ფუნთუშებს“ ახლოდან განვიხილავთ, ჯერ გავიგოთ, რა არის სინამდვილეში ეს მონაცემთა მოდელი?

რა არის მონაცემთა მოდელი

მონაცემთა მოდელი (შემოკლებით, როგორც MD ან DM = მონაცემთა მოდელი) არის სპეციალური არე Excel ფაილის შიგნით, სადაც შეგიძლიათ შეინახოთ ცხრილის მონაცემები - ერთი ან მეტი ცხრილი, თუ სასურველია, ერთმანეთთან მიბმული. სინამდვილეში, ეს არის პატარა მონაცემთა ბაზა (OLAP cube), რომელიც ჩაშენებულია Excel-ის სამუშაო წიგნში. მონაცემთა კლასიკურ შენახვასთან შედარებით, ჩვეულებრივი (ან ჭკვიანი) ცხრილების სახით თავად Excel-ის ფურცლებზე, მონაცემთა მოდელს აქვს რამდენიმე მნიშვნელოვანი უპირატესობა:

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

მოდელი იმართება და გამოითვლება Microsoft Excel-ში ჩაშენებული სპეციალური დანამატით – powerpivotრომლის შესახებაც უკვე დავწერე. ჩასართავად, ჩანართზე დეველოპერი დააჭირეთ COM დანამატები (დეველოპერი — COM დანამატები) და შეამოწმეთ შესაბამისი ველი:

თუ ჩანართები დეველოპერი (დეველოპერი)თქვენ ვერ ხედავთ მას ლენტაზე, შეგიძლიათ ჩართოთ იგი ფაილი – ოფციები – ლენტის დაყენება (ფაილი - ოფციები - ლენტის მორგება). თუ ზემოთ ნაჩვენებ ფანჯარაში COM დანამატების სიაში არ გაქვთ Power Pivot, მაშინ ის არ შედის Microsoft Office-ის თქვენს ვერსიაში 🙁

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

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

ცხრილების ჩატვირთვა მონაცემთა მოდელში

მოდელში მონაცემების ჩასატვირთად, ჯერ ცხრილს ვაქცევთ კლავიატურის დინამიურ „ჭკვიან“ მალსახმობად Ctrl+T და მიეცით მას მეგობრული სახელი ჩანართზე კონსტრუქტორი (დიზაინი). ეს აუცილებელი ნაბიჯია.

შემდეგ შეგიძლიათ გამოიყენოთ სამი მეთოდიდან რომელიმე, რომ აირჩიოთ:

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

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

ჩვენ ვაშენებთ მონაცემთა მოდელის შეჯამებას

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

• დააჭირეთ ღილაკს შემაჯამებელი ცხრილი (კრებითი ცხრილი) Power Pivot ფანჯარაში.
• აირჩიეთ ბრძანებები Excel-ში ჩასმა – PivotTable და გადახვიდეთ რეჟიმში გამოიყენეთ ამ წიგნის მონაცემთა მოდელი (ჩასმა — კრებსითი ცხრილი — გამოიყენეთ ამ სამუშაო წიგნის მონაცემთა მოდელი).
• გუნდების შერჩევა ჩასმა – PivotTable (ჩასმა - კრებსითი ცხრილი) და ჩართეთ ჩამრთველი დაამატეთ ეს მონაცემები მონაცემთა მოდელს (დაამატეთ ეს მონაცემები მონაცემთა მოდელს). მიმდინარე „ჭკვიანი“ ცხრილი ჩაიტვირთება მოდელში და აშენდება შემაჯამებელი ცხრილი მთელი მოდელისთვის.

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

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

რეგულარული შეჯამება შეიძლება შეიქმნას მხოლოდ ერთი წყაროს ცხრილის მონაცემების გამოყენებით. თუ თქვენ გაქვთ რამდენიმე მათგანი, მაგალითად, გაყიდვები, ფასების სია, მომხმარებელთა დირექტორია, კონტრაქტების რეესტრი და ა.შ., მაშინ ჯერ მოგიწევთ მონაცემების შეგროვება ყველა ცხრილიდან ერთში ფუნქციების გამოყენებით, როგორიცაა VLOOKUP. (VLOOKUP), ინდექსი (INDEX), უფრო გამოვლენილი (მატჩი), SUMMESLIMN (SUMIFS) და მსგავსი. ეს გრძელი, დამღლელი და აიძულებს თქვენს Excel-ს „ფიქრად“ დიდი რაოდენობით მონაცემებით.

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

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

სარგებელი 2: დაითვალეთ უნიკალური მნიშვნელობები

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

დააწკაპუნეთ მარჯვენა ღილაკით ველზე – ბრძანება ღირებულების ველის პარამეტრები და ჩანართზე ოპერაცია არჩევა სხვადასხვა ელემენტების რაოდენობა (განსხვავებული რაოდენობა):

სარგებელი 3: მორგებული DAX ფორმულები

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

საზომის შესაქმნელად აირჩიეთ ჩანართზე powerpivot Command ზომები - შექმენით ღონისძიება (ზომები - ახალი ღონისძიება) ან უბრალოდ დააწკაპუნეთ მაგიდაზე Pivot Fields სიაში და აირჩიეთ დაამატეთ ზომა (დაამატე ზომა) კონტექსტურ მენიუში:

ფანჯარაში, რომელიც იხსნება, დააყენეთ:

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

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

სარგებელი 4: მორგებული ველების იერარქია

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

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

შექმნილ იერარქიას შეიძლება ეწოდოს სახელი და მაუსის საშუალებით გადაიტანოს საჭირო ველები, რათა მოგვიანებით ერთი მოძრაობით ისინი გადაიტანონ რეზიუმეში:

სარგებელი 5: მორგებული შაბლონები

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

ამისათვის, ჩანართზე კრებსითი ცხრილის ანალიზი ჩამოსაშლელ სიაში ველები, ნივთები და კომპლექტები არის შესაბამისი ბრძანებები (ანალიზი - ველები, იtems & Sets — შექმენით ნაკრები მწკრივის/სვეტის ელემენტებზე დაყრდნობით):

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

ყველა შექმნილი ნაკრები გამოჩნდება PivotTable Fields პანელში ცალკე საქაღალდეში, საიდანაც მათი თავისუფლად გადატანა შესაძლებელია ნებისმიერი ახალი PivotTable-ის რიგებისა და სვეტების არეალში:

სარგებელი 6: შერჩევითად დამალე ცხრილები და სვეტები

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

ფარული სვეტი ან ცხრილი გაქრება PivotTable Field List-ის პანელიდან. ძალიან მოსახერხებელია, თუ თქვენ გჭირდებათ მომხმარებლისგან დამალვა ზოგიერთი დამხმარე სვეტი (მაგალითად, გამოთვლილი ან სვეტები ძირითადი მნიშვნელობებით ურთიერთობების შესაქმნელად) ან თუნდაც მთელი ცხრილები.

სარგებელი 7. გაფართოებული საბურღი

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

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

ამის შემდეგ, მიმდინარე მნიშვნელობა (Model = Explorer) გადავა ფილტრის ზონაში და შეჯამება შეიქმნება ოფისების მიერ:

რა თქმა უნდა, ასეთი პროცედურა შეიძლება ბევრჯერ განმეორდეს, თანმიმდევრულად ჩავუღრმავდეთ თქვენს მონაცემებს თქვენთვის საინტერესო მიმართულებით.

სარგებელი 8: გადაიყვანეთ Pivot კუბის ფუნქციებად

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

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

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

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