გაშვებული სულ Excel-ში

მეთოდი 1. ფორმულები

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

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

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

• ცხრილი უნდა იყოს დალაგებული თარიღის მიხედვით.
  
• მონაცემებით ახალი სტრიქონების დამატებისას, ფორმულა ხელით უნდა გაფართოვდეს.

მეთოდი 2. საყრდენი ცხრილი

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

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

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

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

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

ამ მიდგომის უპირატესობები:

• დიდი რაოდენობით მონაცემები სწრაფად იკითხება.
• არ არის საჭირო ფორმულების ხელით შეყვანა.
• წყაროს მონაცემების შეცვლისას საკმარისია რეზიუმეს განახლება მაუსის მარჯვენა ღილაკით ან Data – Refresh All ბრძანებით.

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

მეთოდი 3: Power Query

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

შემდეგ ჩვენ ვასრულებთ შემდეგ ნაბიჯებს:

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

2. ცოტა მოგვიანებით, გაშვებული ჯამის გამოსათვლელად, ჩვენ გვჭირდება დამხმარე სვეტი რიგითი რიგის ნომრით. დავამატოთ ბრძანებით დაამატეთ სვეტი - ინდექსის სვეტი - 1-დან (სვეტის დამატება - ინდექსის სვეტი - 1-დან).

3. ასევე, გაშვებული ჯამის გამოსათვლელად, ჩვენ გვჭირდება მითითება სვეტზე გაიყიდა, სადაც დევს ჩვენი შეჯამებული მონაცემები. Power Query-ში სვეტებს ასევე უწოდებენ სიებს (list) და რომ მიიღოთ ბმული, დააწკაპუნეთ მაუსის მარჯვენა ღილაკით სვეტის სათაურზე და აირჩიეთ ბრძანება. დეტალებს (დეტალების ჩვენება). ჩვენ გვჭირდება გამოთქმა გამოჩნდება ფორმულების ზოლში, რომელიც შედგება წინა ნაბიჯის სახელისგან #"ინდექსი დამატებულია", საიდანაც ვიღებთ ცხრილს და სვეტის სახელს [Გაყიდვების] ამ ცხრილიდან კვადრატულ ფრჩხილებში:

დააკოპირეთ ეს გამონათქვამი ბუფერში შემდგომი გამოყენებისთვის.

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

აქ არის ფუნქცია სია.დიაპაზონი იღებს თავდაპირველ სიას (სვეტი [Გაყიდვების]) და ამოიღებს ელემენტებს მისგან, დაწყებული პირველიდან (ფორმულაში ეს არის 0, ვინაიდან Power Query-ში ნუმერაცია იწყება ნულიდან). ამოსაღები ელემენტების რაოდენობა არის მწკრივის ნომერი, რომელსაც ვიღებთ სვეტიდან [ინდექსი]. ასე რომ, ეს ფუნქცია პირველი რიგისთვის აბრუნებს სვეტის მხოლოდ ერთ პირველ უჯრედს გაიყიდა. მეორე ხაზისთვის - უკვე პირველი ორი უჯრედი, მესამესთვის - პირველი სამი და ა.შ.

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

რჩება Index სვეტის წაშლა, რომელიც აღარ გვჭირდება და შედეგების ატვირთვა ისევ Excel-ში Home – Close & Load ბრძანებით.

პრობლემა მოგვარებულია.

სწრაფი და გააფთრებული

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

დასაჩქარებლად შეგიძლიათ გამოიყენოთ ბუფერირება სპეციალური List.Buffer ფუნქციის გამოყენებით, რომელიც არგუმენტად მიცემულ სიას (სიას) ატვირთავს RAM-ში, რაც მნიშვნელოვნად აჩქარებს მასზე წვდომას მომავალში. ჩვენს შემთხვევაში, აზრი აქვს #"დამატებული ინდექსის" [გაყიდული] სიის ბუფერირებას, რომელსაც Power Query უნდა ჰქონდეს წვდომა ჩვენი 2000 მწკრივი ცხრილის თითოეულ მწკრივში გაშვებული ჯამის გაანგარიშებისას.

ამისათვის Power Query რედაქტორში მთავარ ჩანართზე დააწკაპუნეთ Advanced Editor ღილაკზე (Home – Advanced Editor), რათა გახსნათ ჩვენი მოთხოვნის საწყისი კოდი Power Query-ში ჩაშენებულ M ენაზე:

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

ამ ცვლილებების განხორციელების შემდეგ, ჩვენი მოთხოვნა საგრძნობლად უფრო სწრაფი გახდება და 2000 მწკრივიან ცხრილს მხოლოდ 0.3 წამში გაუმკლავდება!

სხვა რამ, არა? 🙂

• პარეტოს სქემა (80/20) და როგორ ავაშენოთ იგი Excel-ში
• საკვანძო სიტყვების ძებნა ტექსტში და შეკითხვის ბუფერირება Power Query-ში