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

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

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

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

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

მეთოდი 1: შექმენით ცხრილები კრებსისთვის Power Query-ის გამოყენებით

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

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

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

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

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

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

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

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

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

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

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

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

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

მეთოდი 2. ჩვენ ვაერთებთ ცხრილებს UNION SQL ბრძანებით მაკროში

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

მაკროს გამოსაყენებლად გამოიყენეთ Visual Basic ღილაკი ჩანართზე დეველოპერი (დეველოპერი) ან კლავიატურის მალსახმობი Alt+F11. შემდეგ მენიუში ჩავსვით ახალი ცარიელი მოდული ჩასმა – მოდული და დააკოპირეთ შემდეგი კოდი იქ:

Sub New_Multi_Table_Pivot() Dim i როგორც დიდხანს Dim arSQL() როგორც სტრიქონი Dim objPivotCache როგორც PivotCache Dim objRS როგორც ობიექტი Dim ResultSheetName როგორც სტრიქონი Dim SheetsNames როგორც Variant 'sheet name, სადაც შედეგად მიღებული pivot იქნება გამოსახული "RrezultSheiP" სახელები წყაროს ცხრილებით SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'ჩვენ ვქმნით ქეშს ცხრილებისთვის SheetsNames-ის ფურცლებიდან ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" შემდეგი i Set objRS = CreateObject("ADODB.Recordset") objRS .გახსენით Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) დასრულება "ხელახლა შექმენით ფურცელი, რათა გამოჩნდეს მიღებული კრებსითი ცხრილი შეცდომის განახლების შემდეგ შემდეგი განაცხადის.DisplayAlerts = False Worksheets(ResultSheetName). წაშლა კომპლექტი wsPivot = Worksheets.დამატება wsPivo ტ. Name = ResultSheetName 'აჩვენეთ გენერირებული ქეშის შეჯამება ამ ფურცელზე Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivotachet. objPivotCache = Nothing Range("A3"). აირჩიეთ End With End Sub    

დასრულებული მაკრო შეიძლება გაუშვათ კლავიატურის მალსახმობით Alt+F8 ან ღილაკი მაკროები ჩანართზე დეველოპერი (დეველოპერი - მაკრო).

ამ მიდგომის უარყოფითი მხარეები:

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

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

ვოილია!

ტექნიკური შენიშვნა: თუ მაკროს გაშვებისას მიიღებთ შეცდომას, როგორიცაა „პროვაიდერი არ არის რეგისტრირებული“, მაშინ, სავარაუდოდ, გაქვთ Excel-ის 64-ბიტიანი ვერსია ან დაინსტალირებულია Office-ის არასრული ვერსია (წვდომა არ არის). სიტუაციის გამოსასწორებლად, შეცვალეთ ფრაგმენტი მაკრო კოდში:

	 პროვაიდერი=Microsoft.Jet.OLEDB.4.0;  

დან:

	პროვაიდერი=Microsoft.ACE.OLEDB.12.0;  

და ჩამოტვირთეთ და დააინსტალირეთ მონაცემთა დამუშავების უფასო ძრავა Access-დან Microsoft-ის ვებსაიტიდან – Microsoft Access Database Engine 2010 Redistributable

მეთოდი 3: PivotTable Wizard-ის კონსოლიდაცია Excel-ის ძველი ვერსიებიდან

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

ასეთ პივოტში, ველების სიაში არ არის სვეტების სათაურები, არ არის მოქნილი სტრუქტურის პარამეტრი, გამოყენებული ფუნქციების ნაკრები შეზღუდულია და, ზოგადად, ეს ყველაფერი დიდად არ ჰგავს კრებსით ცხრილს. ალბათ ამიტომაა, რომ 2007 წლიდან მაიკროსოფტმა ამოიღო ეს ფუნქცია სტანდარტული დიალოგიდან კრებსითი ცხრილების ანგარიშების შექმნისას. ახლა ეს ფუნქცია ხელმისაწვდომია მხოლოდ მორგებული ღილაკის საშუალებით PivotTable Wizard(Pivot Table Wizard), რომელიც სურვილის შემთხვევაში შეიძლება დაემატოს სწრაფი წვდომის ინსტრუმენტთა პანელს მეშვეობით ფაილი – ოფციები – სწრაფი წვდომის ხელსაწყოთა ზოლის მორგება – ყველა ბრძანება (ფაილი — ოფციები — სწრაფი წვდომის ხელსაწყოთა ზოლის მორგება — ყველა ბრძანება):

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

შემდეგ ფანჯარაში რიგრიგობით აირჩიეთ თითოეული დიაპაზონი და დაამატეთ იგი ზოგად სიაში:

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

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