ვთქვათ, რომ თქვენ აწარმოებთ რამდენიმე პროექტს სხვადასხვა ბიუჯეტით და გსურთ წარმოიდგინოთ თქვენი ხარჯები თითოეული მათგანისთვის. ანუ ამ წყაროს ცხრილიდან:
.. მიიღეთ მსგავსი რამ:
სხვა სიტყვებით რომ ვთქვათ, თქვენ უნდა გაანაწილოთ ბიუჯეტი თითოეული პროექტის დღეებში და მიიღოთ პროექტის Gantt ჩარტის გამარტივებული ვერსია. თქვენი ხელით ამის გაკეთება გრძელი და მოსაწყენია, მაკრო რთულია, მაგრამ Power Query Excel-ისთვის ასეთ სიტუაციაში აჩვენებს მის ძალას მთელი თავისი დიდებით.
დენის მოთხოვნა არის Microsoft-ის დანამატი, რომელსაც შეუძლია მონაცემების იმპორტი Excel-ში თითქმის ნებისმიერი წყაროდან და შემდეგ მისი გარდაქმნა სხვადასხვა გზით. Excel 2016-ში ეს დანამატი უკვე ჩაშენებულია ნაგულისხმევად, ხოლო Excel 2010-2013-ისთვის მისი ჩამოტვირთვა შესაძლებელია Microsoft-ის ვებსაიტიდან და შემდეგ დაინსტალირება თქვენს კომპიუტერში.
პირველი, მოდით, ჩვენი ორიგინალური ცხრილი ვაქციოთ "ჭკვიან" ცხრილად ბრძანების არჩევით ფორმატირება, როგორც ცხრილი tab მთავარი (მთავარი - ფორმატირება როგორც ცხრილი) ან კლავიატურის მალსახმობის დაჭერით Ctrl+T :
შემდეგ გადადით ჩანართზე თარიღი (თუ გაქვთ Excel 2016) ან ჩანართზე დენის მოთხოვნა (თუ გაქვთ Excel 2010-2013 და დააინსტალირეთ Power Query, როგორც ცალკე დანამატი) და დააწკაპუნეთ ღილაკს From Table / Range. :
ჩვენი ჭკვიანი ცხრილი იტვირთება Power Query შეკითხვის რედაქტორში, სადაც პირველი ნაბიჯი არის თითოეული სვეტისთვის ნომრის ფორმატების დაყენება ცხრილის სათაურის ჩამოსაშლელი ჩანართების გამოყენებით:
ბიუჯეტის გამოსათვლელად დღეში, თქვენ უნდა გამოთვალოთ თითოეული პროექტის ხანგრძლივობა. ამისათვის აირჩიეთ (დააჭირეთ ღილაკს Ctrl) სვეტი პირველი ფერი, შემდეგ კი დასაწყისი და აირჩიე გუნდი სვეტის დამატება – თარიღი – გამოკლება დღეები (სვეტის დამატება - თარიღი - დღეების გამოკლება):
მიღებული რიცხვები 1-ით ნაკლებია საჭიროზე, რადგან ყოველი პროექტი პირველ დღეს დილით უნდა დავიწყოთ და საღამოს ბოლო დღეს დავასრულოთ. ამიტომ, აირჩიეთ მიღებული სვეტი და დაამატეთ მას ერთეული ბრძანების გამოყენებით ტრანსფორმაცია – სტანდარტული – დამატება (ტრანსფორმა - სტანდარტული - დამატება):
ახლა დავამატოთ სვეტი, სადაც ვიანგარიშებთ ბიუჯეტს დღეში. ამისათვის, ჩანართზე სვეტის დამატება მე არ ვთამაშობ მორგებული სვეტი (მორგებული სვეტი) და ფანჯარაში, რომელიც გამოჩნდება, შეიყვანეთ ახალი ველის სახელი და გაანგარიშების ფორმულა, სიიდან სვეტების სახელების გამოყენებით:
ახლა ყველაზე დახვეწილი მომენტია - ჩვენ ვქმნით კიდევ ერთ გამოთვლილ სვეტს თარიღების სიით თავიდან ბოლომდე 1 დღის ნაბიჯით. ამისათვის კვლავ დააჭირეთ ღილაკს მორგებული სვეტი (მორგებული სვეტი) და გამოიყენეთ ჩაშენებული Power Query ენა M, რომელსაც ე.წ სია.თარიღები:
ამ ფუნქციას აქვს სამი არგუმენტი:
- დაწყების თარიღი - ჩვენს შემთხვევაში, იგი აღებულია სვეტიდან დასაწყისი
- გენერირებული თარიღების რაოდენობა - ჩვენს შემთხვევაში, ეს არის დღეების რაოდენობა თითოეული პროექტისთვის, რომელიც ადრე დავთვალეთ სვეტში გამოკლება
- დროის ნაბიჯი – დაყენებული დიზაინით #ხანგრძლივობა (1,0,0,0), M-ის ენაზე ნიშნავს – ერთი დღე, ნულოვანი საათი, ნულოვანი წუთი, ნულ წამი.
დაწკაპვით OK ჩვენ ვიღებთ თარიღების სიას (სია), რომელიც შეიძლება გაფართოვდეს ახალ ხაზებად ცხრილის სათაურში ღილაკის გამოყენებით:
… და მივიღებთ:
ახლა რჩება მხოლოდ ცხრილის კოლაფსი, გენერირებული თარიღების გამოყენებით, როგორც ახალი სვეტების სახელები. ამაზე პასუხისმგებელი გუნდია. დეტალების სვეტი (საყრდენი სვეტი) tab გადაიყვანეთ (ტრანსფორმა):
დაწკაპვით OK ჩვენ ვიღებთ შედეგს სასურველთან ძალიან ახლოს:
Null არის, ამ შემთხვევაში, Excel-ში ცარიელი უჯრედის ანალოგი.
რჩება არასაჭირო სვეტების ამოღება და მიღებული ცხრილის განტვირთვა ორიგინალური მონაცემების გვერდით ბრძანებით დახურვა და ჩატვირთვა – დახურვა და ჩატვირთვა… (დახურვა და ჩატვირთვა — დახურვა და ჩატვირთვა…) tab მთავარი (მთავარი):
შედეგად ვიღებთ:
მეტი სილამაზისთვის, შეგიძლიათ დააკონფიგურიროთ მიღებული ჭკვიანი ცხრილების გარეგნობა ჩანართზე კონსტრუქტორი (დიზაინი): დააყენეთ ერთი ფერის სტილი, გამორთეთ ფილტრის ღილაკები, ჩართეთ ჯამები და ა.შ. გარდა ამისა, შეგიძლიათ აირჩიოთ ცხრილი თარიღებით და ჩართოთ მისთვის რიცხვების მონიშვნა ჩანართზე პირობითი ფორმატირების გამოყენებით მთავარი — პირობითი ფორმატირება — ფერის სასწორები (მთავარი — პირობითი ფორმატირება — ფერის სასწორები):
და ყველაზე კარგი ის არის, რომ მომავალში შეგიძლიათ უსაფრთხოდ შეცვალოთ ძველი ან დაამატოთ ახალი პროექტები თავდაპირველ ცხრილში, შემდეგ კი განაახლოთ მარჯვენა ცხრილი თარიღებით მაუსის მარჯვენა ღილაკით – და Power Query გაიმეორებს ყველა მოქმედებას, რაც ჩვენ გავაკეთეთ ავტომატურად. .
ვოილია!
- Gantt სქემა Excel-ში პირობითი ფორმატირების გამოყენებით
- პროექტის ეტაპების კალენდარი
- დუბლიკატი რიგების გენერირება Power Query-ით