მიიღეთ Excel-ის კრებსითი ცხრილის მონაცემების მაგალითი

ადრე მე უკვე ვისაუბრე იმაზე, თუ როგორ, როდესაც თქვენ მიუთითებთ უჯრედს საყრდენი მაგიდაჩვეულებრივი ბმულის ნაცვლად, ფუნქცია GET.PIVORTABLE.DATA ბრუნდება (იხ.). Თუ ხარ დაინტერესებული Როგორამ უხერხულობის დასაძლევად გირჩევთ მიმართოთ აღნიშნულ შენიშვნას. Თუ ხარ დაინტერესებული, რატომეს ხდება და ასევე რა დადებითი ასპექტებიფუნქციას აქვს GET.DATA.PIVOT.TABLE, შემდეგ მე გთავაზობთ ჯელენის, ალექსანდრეს წიგნის ფრაგმენტს. (თავი 15). განხილული ტექნიკა საშუალებას მოგვცემს გავუმკლავდეთ უამრავ პრობლემას, რაც იწვევს თავის ტკივილს საყრდენი ცხრილების მომხმარებლებისთვის, კერძოდ:

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

სინამდვილეში, აქ აღწერილი ყველაფერი ახალი არ არის. უფრო მეტიც, მსგავსი ტექნიკები გამოიყენება Excel 2002 წლიდან. თუმცა, მომხმარებლებთან ჩემი კომუნიკაცია აჩვენებს, რომ 1%-ზე ნაკლები იცნობს მათ. ერთადერთი კითხვა, რაც მომხმარებლებს აქვთ არის ის, თუ როგორ უნდა გამორთოთ უცნაური ფუნქცია GET.PIRTTABLE.DATA. Სამწუხაროა…

ჩამოტვირთეთ შენიშვნა ფორმატში ან ფორმატში, მაგალითები ფორმატში

კარგი, დავიწყოთ თანმიმდევრობით.

როგორ მივატოვოთ პრობლემური ფუნქცია GET.PIVOT.TABLE.DATA

GET.PIVO.TABLE.DATA ფუნქცია დიდი ხანია თავის ტკივილია მრავალი მომხმარებლისთვის. სრულიად მოულოდნელად, ყოველგვარი გაფრთხილების გარეშე, Excel 2002-ში შეიცვალა Pivot Tables-ის ქცევა. როგორც კი დაიწყებთ ფორმულების შექმნას PivotTable-ის გარეთ, რომლებიც მიუთითებენ მის მონაცემებზე, ეს ფუნქცია არსაიდან მოდის.

დავუშვათ, კრებულში ნაჩვენები ნახ. 1, თქვენ უნდა შეადაროთ 2015 და 2014 წლების მონაცემები.

ბრინჯი. 1. ორიგინალური PivotTable

1. დაამატეთ სათაური „% ზრდა“ უჯრედს D3.
2. დააკოპირეთ ფორმატი C3 უჯრედიდან D3 უჯრედში.
3. უჯრედში D4 შეიყვანეთ თანაბარი ნიშანი.
4. დააჭირეთ უჯრედს C4.
5. ჩაწერეთ a / (slash) გაყოფის ოპერაციის წარმოსადგენად.
6. დააჭირეთ უჯრედს B4.
7. ჩაწერეთ -1 და დააჭირეთ კლავიშთა კომბინაციას რომ დარჩეს იმავე საკანში. შედეგის ფორმატირება პროცენტულად. დაინახავთ, რომ დასავლეთის რეგიონში შემოსავლების 43.8%-იანი ვარდნა მოხდა (სურათი 2). არ არის ძალიან კარგი შედეგები.
8. თქვენი პირველი ფორმულის შეყვანის დასრულების შემდეგ აირჩიეთ უჯრედი D4.
9. ორჯერ დააწკაპუნეთ პატარა კვადრატზე, რომელიც მდებარეობს უჯრედის ქვედა მარჯვენა კუთხეში. ეს ველი წარმოადგენს შევსების სახელურს, რომელიც საშუალებას გაძლევთ დააკოპიროთ ფორმულა ანგარიშის მთელი სვეტის შესავსებად.

ფორმულის კოპირების დასრულების შემდეგ, ეკრანის დათვალიერებისას, მიხვდებით, რომ რაღაც არასწორია - თითოეულმა რეგიონმა აჩვენა კლება 43,8%-ით წლის განმავლობაში (ნახ. 3).

ბრინჯი. 3. როგორც კი დაასრულებთ ფორმულის კოპირებას სვეტის ყველა უჯრედში, დაინახავთ, რომ თითოეულ რეგიონში 43,8%-იანი ვარდნაა.

ნაკლებად სავარაუდოა, რომ ეს მოხდეს ნამდვილი ცხოვრება. ნებისმიერი მომხმარებელი გეტყვით, რომ ზემოთ ჩამოთვლილი ნაბიჯების შესრულების შემდეგ Excel შექმნის ფორმულას =C4/B4–1. დაბრუნდით D4 უჯრედში და ყურადღება მიაქციეთ ფორმულის ზოლს (სურათი 4). უბრალოდ რაღაც ეშმაკი! მარტივი ფორმულა =C4/B4–1 აღარ არსებობს. ამის ნაცვლად, პროგრამა ანაცვლებს კომპლექსურ კონსტრუქციას ფუნქციით GET.PIVOT.TABLE.DATA. რატომ იძლევა ეს ფორმულა სწორ შედეგებს D4 უჯრედში, მაგრამ როდესაც კოპირდება ქვემოთ მოცემულ უჯრედებში, ის უარს ამბობს მუშაობაზე?

პირველი რეაქცია იმაზე, რაც მოხდა ნებისმიერი მომხმარებლისთვის, იქნება შემდეგი: "რა არის ეს უცნაური GET.PIVOT.TABLE.DATA კონსტრუქცია, რომელმაც გაანადგურა ჩემი ანგარიში?" მომხმარებელთა უმეტესობას სურს დაუყოვნებლივ მოიცილოს ეს ფუნქცია. ზოგიერთი დასვამს კითხვას: "რატომ მოგვცა Microsoft-მა ეს ფუნქცია?"

მსგავსი არაფერი ყოფილა Excel ჯერ 2000 წელი. ერთხელ დავიწყე GET.PIVOT.TABLE.DATA ფუნქციის რეგულარულად შეხვედრები, უბრალოდ შემძულდა იგი. როდესაც ერთ-ერთ სემინარზე ვიღაცამ მკითხა, როგორ შეიძლება მისი გამოყენება საქმისთვის სასარგებლოდ, დავმუნჯდი. ასეთი კითხვა არასდროს დამისვამს ჩემს თავს! ჩემი აზრით, და Excel-ის მომხმარებელთა უმეტესობის აზრით, GET.DATA.PICTTABLE ფუნქცია იყო ბოროტების პროდუქტი, რომელსაც არაფერი აქვს საერთო სიკეთის ძალებთან. საბედნიეროდ, ამ ფუნქციის გამორთვის ორი გზა არსებობს.

GET.PIVOT.TABLE.DATA ფუნქციის დაბლოკვა ფორმულის შეყვანით.არსებობს მარტივი გზა GET.PIVOTABLE.DATA ფუნქციის გაჩენის თავიდან ასაცილებლად. ამისათვის თქვენ უნდა შექმნათ ფორმულა მაუსის ან კურსორის კლავიშების გამოყენების გარეშე. უბრალოდ მიჰყევით ამ ნაბიჯებს.

1. გადადით D4 უჯრედში და შეიყვანეთ = (ტოლი ნიშანი).
2. შეიყვანეთ C4.
3. შეიყვანეთ / (სლაიტი, რომელიც მიუთითებს გაყოფის ოპერაციაზე).
4. შეიყვანეთ B4.
5. შეიყვანეთ -1.
6. დააწკაპუნეთ შედი.

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

ბრინჯი. 5. უბრალოდ შეიყვანეთ =С4/В4–1 კლავიატურიდან და ფორმულა იმუშავებს ისე, როგორც უნდა

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

GET.PIVOT.TABLE.DATA ფუნქციის გამორთვა.შეგიძლიათ სამუდამოდ გამორთოთ GET.PIVOTABLE.DATA ფუნქცია. დააჭირეთ მენიუს ლენტს ფაილი → Პარამეტრები. ფანჯარაში, რომელიც იხსნება ᲞარამეტრებიExcelდეპოზიტზე წასვლა ფორმულებიდა მოხსენით ველი ამ ვარიანტის გვერდით გამოიყენეთ ფუნქციამიიღეთ PivotData ლინკებისთვის კრებსითი ცხრილისთვის. დააწკაპუნეთ Კარგი.

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

რატომ შემოგვთავაზა Microsoft-მა GET.PICTTABLE.DATA ფუნქცია.თუ ეს ფუნქცია ასეთი საშინელია, რატომ ჩართო მაიკროსოფტმა ის ნაგულისხმევად? რატომ ზრუნავენ ისინი Excel-ის ახალ ვერსიებში ამ ფუნქციის მხარდაჭერის შენარჩუნებაზე? იციან მათ მომხმარებლის განწყობა? და ჩვენ გადავდივართ ყველაზე საინტერესოზე...

GET.PIVORTABLE.DATA ფუნქციის გამოყენება PivotTables-ის გასაუმჯობესებლად

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

საყრდენი ცხრილების შექმნის ახალი ტექნიკა, შემოთავაზებული რობ კოლის (მაიკროსოფტის დეველოპერი) მიერ და განხილული შემდეგში, არის ზემოთ აღწერილი პროცესის გაუმჯობესება. ამ შემთხვევაში, პირველად იქმნება პრიმიტიული საყრდენი ცხრილი. ამ ცხრილს არ სჭირდება ფორმატირება. შემდეგ ის გადის ერთსაფეხურიან, შედარებით შრომატევად პროცესს, რათა შეიქმნას ლამაზად ფორმატირებული გარსი, რომელიც განთავსდება საბოლოო ანგარიშში. ამის შემდეგ, GET.PIVOT.TABLE.DATA ფუნქცია გამოიყენება ჭურვში მდებარე ანგარიშის მონაცემებით სწრაფად შესავსებად. ახალი მონაცემების მიღების შემდეგ შეგიძლიათ განათავსოთ იგი ფურცელზე, განაახლოთ პრიმიტიული კრებსითი ცხრილი და დაბეჭდოთ გარსში განთავსებული ანგარიში (ნახ. 9). ამ ტექნიკას აქვს მრავალი უდავო უპირატესობა. მაგალითად, თქვენ არ უნდა ინერვიულოთ ანგარიშის დაფორმატებაზე მისი შექმნისთანავე. კრებსითი ცხრილების შექმნის პროცესი თითქმის მთლიანად ავტომატიზირებული ხდება.

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

პრიმიტიული საყრდენი ცხრილის შექმნა.წყაროს მონაცემები (ნახ. 10) წარმოდგენილია ტრანზაქციების სახით, რომელიც შეიცავს ინფორმაციას დაგეგმილი და ფაქტობრივი მაჩვენებლების შესახებ თითოეული რეგიონისთვის, სადაც კომპანიას აქვს ფილიალები. დაგეგმილი ინდიკატორები დეტალურად არის აღწერილი თვის დონეზე, ხოლო ფაქტობრივი მაჩვენებლები - ცალკეული დღეების დონეზე. დაგეგმილი ინდიკატორები იქმნება მომავალი წლისთვის, ხოლო რეალური ინდიკატორები იქმნება გასული თვეებისთვის. მას შემდეგ, რაც ანგარიში განახლდება ყოველთვიურად, ეს პროცესი მნიშვნელოვნად გამარტივდება, თუ კრებსითი ცხრილის მონაცემთა წყარო იზრდება ზომაში, როდესაც ახალი მონაცემები დაემატება ბოლოში. Excel-ის ძველ ვერსიებში, მონაცემთა ასეთი წყაროს შექმნა ხდებოდა დასახელებული დინამიური დიაპაზონის გამოყენებით OFFSET ფუნქციის გამოყენებით (დამატებითი ინფორმაციისთვის იხ.). Excel 2013-ში მუშაობისას უბრალოდ აირჩიეთ მონაცემთა ერთ-ერთი უჯრედი და დააჭირეთ Ctrl+T კლავიშთა კომბინაციას (შექმენით ცხრილი). ეს ქმნის დასახელებულ მონაცემთა ნაკრებს, რომელიც ავტომატურად ფართოვდება ახალი რიგებისა და სვეტების დამატებისას.

ახლა შევქმნათ კრებსითი ცხრილი. GET.PIVORTABLE.DATA ფუნქცია საკმაოდ მძლავრია, მაგრამ მას შეუძლია დააბრუნოს მხოლოდ მნიშვნელობები, რომლებიც გამოჩნდება რეალურ კრებსით ცხრილში. ამ ფუნქციას არ შეუძლია ქეშის გადახედვა იმ ელემენტების გამოსათვლელად, რომლებიც არ არის PivotTable-ში.

შექმენით კრებსითი ცხრილი:

1. აირჩიეთ გუნდი ჩასმა → საყრდენი მაგიდადა შემდეგ დიალოგურ ფანჯარაში Pivot Table-ის შექმნადააწკაპუნეთ კარგი.
2. PivotTable ველების სიაში აირჩიეთ ველი თარიღი. თარიღების სია გამოჩნდება კრებსითი ცხრილის მარცხენა მხარეს (ნახ. 11).
3. აირჩიეთ ნებისმიერი თარიღის უჯრედი, მაგალითად A4. კონტექსტური ჩანართზე ანალიზიმდებარეობს კონტექსტური ჩანართების ერთობლიობაში Pivot Tables-თან მუშაობა, დააჭირეთ ღილაკს დაჯგუფება სფეროს მიხედვით(იხილეთ დეტალები). დიალოგურ ფანჯარაში დაჯგუფებააირჩიეთ ვარიანტი თვეები(სურ. 12). დააწკაპუნეთ კარგი. კრებსითი ცხრილის მარცხენა მხარეს გამოჩნდება თვეების სახელები (ნახ. 13).
4. გადაიტანეთ ველი თარიღიკრებსითი ცხრილის სვეტების არეში.
5. გადაიტანეთ ველი ინდექსიველის სიის სვეტების არეში კრებსითი ცხრილის.
6. აირჩიეთ ველი რეგიონი, რომელიც გამოჩნდება PivotTable-ის მარცხენა სვეტში.
7. აირჩიეთ ველი შემოსავალირომელიც გამოჩნდება PivotTable მნიშვნელობების არეალში.

ბრინჯი. 11. დაიწყეთ ველების მიხედვით დაჯგუფებით თარიღი

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

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

მიჰყევით ამ ნაბიჯებს (ნახ. 15).

1. A1 უჯრედში შეიყვანეთ მოხსენების სახელი - დაგეგმილი და ფაქტობრივი მაჩვენებლები რეგიონების მიხედვით.
2. გადადით ჩანართზე სახლში, დააჭირეთ ღილაკს უჯრედის სტილებიაირჩიეთ ფორმატი სათაური 1.
3. A2 უჯრედში შეიყვანეთ ფორმულა =MONTH(TODAY();0). ეს ფუნქცია აბრუნებს მიმდინარე თვის ბოლო დღეს. მაგალითად, თუ ამას კითხულობთ 2014 წლის 14 აგვისტოს, უჯრედი A2 აჩვენებს თარიღს 2014 წლის 31 აგვისტოს.
4. აირჩიეთ უჯრედი A2. დააჭირეთ კლავიშთა კომბინაციას Ctrl+1 დიალოგური ფანჯრის გამოსაჩენად უჯრედის ფორმატი. ჩანართზე ნომერიდააწკაპუნეთ ნივთზე ყველა ფორმატი. შეიყვანეთ მორგებული ნომრის ფორმატი როგორც "თვიდან "MMMM" დაგეგმილი მაჩვენებლები"(სურ. 16). შედეგად, გამოთვლილი თარიღი გამოჩნდება ტექსტად.
5. A5 უჯრედში შეიყვანეთ სათაური რეგიონი.
6. შეიყვანეთ რეგიონის სათაურები A სვეტის დარჩენილ უჯრედებში. რეგიონის სათაურები უნდა ემთხვეოდეს კრებსით ცხრილში ჩამოთვლილ რეგიონების სახელებს.
7. საჭიროების შემთხვევაში, დაამატეთ ეტიკეტები სვეტში დეპარტამენტის ჯამებისთვის.
8. ანგარიშის ბოლოში დაამატეთ ხაზი სულ კომპანიისთვის.
9. უჯრედში B4 შეიყვანეთ ფორმულა =DATE(YEAR($A$2),COLUMN(A1),1). ეს ფორმულა აბრუნებს 01/01/2014, 02/01/2104 და ა.შ., მიმდინარე წლის 12 თვის პირველ დღეებს.
10. აირჩიეთ უჯრედი B4. ფანჯრის გასახსნელად დააჭირეთ კლავიშთა კომბინაციას Ctrl+1 უჯრედის ფორმატი. ჩანართზე ნომერითავში ყველა ფორმატიშეიყვანეთ მორგებული ნომრის ფორმატი MMM. ეს ფორმატი აჩვენებს თვის სამ ასოს სახელს. ტექსტის გასწორება უჯრედის მარჯვენა კიდეზე.
11. დააკოპირეთ B4 უჯრედის შინაარსი C4:M4 დიაპაზონში. მწკრივი თვეების სახელებით გამოჩნდება კრებსითი ცხრილის ზედა ნაწილში.
12. B5 უჯრედში შეიყვანეთ ფორმულა =IF(MONTH(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово ფაქტიდა ამჟამინდელი და მომავლისთვის - Გეგმა.
13. დაამატეთ სათაური N5 უჯრედში ქვედა ხაზი. O4 უჯრედამდე - ქვედა ხაზი, O5 - Გეგმა, P5 - % გადახრა.
14. შეიყვანეთ ჩვეულებრივი Excel ფორმულები, რომლებიც გამოიყენება დეპარტამენტის ჯამების, კომპანიის ჯამური რიგების, გრანდიოზული ჯამური სვეტების და პროცენტული დისპერსიის სვეტების გამოსათვლელად:
    1. უჯრედში B8 შეიყვანეთ ფორმულა =SUM(B6:B7) და დააკოპირეთ იგი რიგის სხვა უჯრედებში;
    2. უჯრედში N6 შეიყვანეთ ფორმულა =SUM(B6:M6) და დააკოპირეთ იგი სვეტის სხვა უჯრედებში;
    3. უჯრედში P6 შეიყვანეთ ფორმულა =IFERROR((N6/O10)-1;0) და დააკოპირეთ იგი სვეტის სხვა უჯრედებში;
    4. უჯრედში B13 შეიყვანეთ ფორმულა =SUM(B10:B12) და დააკოპირეთ იგი რიგის სხვა უჯრედებში;
    5. უჯრედში B17 შეიყვანეთ ფორმულა =SUM(B15:B16) და დააკოპირეთ იგი რიგის სხვა უჯრედებში;
    6. უჯრედში B19 შეიყვანეთ ფორმულა =SUM(B6:B18)/2 და დააკოპირეთ იგი რიგის სხვა უჯრედებში.
15. გამოიყენეთ Heading 4 სტილი A სვეტის წარწერებზე და 4 და 5 რიგების სათაურებზე.
16. უჯრედების დიაპაზონისთვის B6:O19 აირჩიეთ რიცხვის ფორმატი ##0.
17. სვეტის P უჯრედებისთვის აირჩიეთ რიცხვის ფორმატი 0.0%.

ასე რომ, ჩვენ დავასრულეთ მოხსენების გარსის შექმნა, რომელიც ნაჩვენებია ნახ. 15. ეს ანგარიში მოიცავს ყველა საჭირო ფორმატირებას. შემდეგი სექცია აჩვენებს, თუ როგორ გამოიყენოთ GET.PIVORTABLE.DATA ფუნქცია ანგარიშის დასასრულებლად.

ბრინჯი. 15. შეატყობინეთ ჭურვი GET.PIVOT.TABLE.DATA ფორმულების დამატებამდე

GET.PIVORTABLE.DATA ფუნქციის გამოყენებით ანგარიშის გარსი მონაცემებით შევსება.ამიერიდან თქვენ გექნებათ შესაძლებლობა მიიღოთ GET.PIVOT.TABLE.DATA ფუნქციის გამოყენების ყველა უპირატესობა. თუ თქვენ გაასუფთავეთ მოსანიშნი ველი, რომელმაც ჩართო ეს ფუნქცია, დაუბრუნდით შესაბამის პარამეტრს და დააბრუნეთ ჩამრთველი (იხ. სურათი 6 ან 7-ის აღწერა).

აირჩიეთ მოხსენების გარსის B6 უჯრედი. ეს უჯრედი შეესაბამება ჩრდილო-აღმოსავლეთ რეგიონს და იანვრის რეალურ მაჩვენებლებს.

1. შეიყვანეთ = (ტოლი ნიშანი) ფორმულის შეყვანის დასაწყებად.
2. გადადით ფურცელზე კრებსითი ცხრილიდან და დააწკაპუნეთ უჯრედზე, რომელიც შეესაბამება ჩრდილო-აღმოსავლეთ რეგიონს და იანვრის ფაქტობრივ მაჩვენებლებს - C12 (ნახ. 17).
3. დააჭირეთ ღილაკს შედიდაასრულეთ ფორმულის შეყვანა და დაბრუნდით მოხსენების გარსში. შედეგად, Excel დაამატებს GET.PIOTTABLE.DATA ფუნქციას B6 უჯრედში. უჯრედი აჩვენებს $277,435 ღირებულებას.

დაიმახსოვრეთ ეს რიცხვი, რადგან დაგჭირდებათ ის ფორმულის გაშვების შედეგებთან შედარებისას, რომელსაც მოგვიანებით დაარედაქტირებთ. პროგრამის მიერ გენერირებული ფორმულა ასეთია: =GET.DATA.PIVOTABLE(" შემოსავალი " ;'ნახ. 11-14′!$A$3; " რეგიონი " ; " ჩრდილო-აღმოსავლეთი " ; " თარიღი " ;1; "ინდიკატორი" "ფაქტი"). თუ აქამდე უგულებელყოფთ GET.PIRTTABLE.DATA ფუნქციას, დროა, უფრო დეტალურად შეხედოთ მას. ნახ. 18 ეს ფორმულა ნაჩვენებია რედაქტირების რეჟიმში მინიშნებასთან ერთად.

ფუნქციის არგუმენტები:

• მონაცემთა_ველი. ველი PivotTable მნიშვნელობების არედან. გთხოვთ გაითვალისწინოთ: ამ შემთხვევაში ველი გამოიყენება შემოსავალი, მაგრამ არა თანხა სფეროს მიხედვით შემოსავალი.
• Pivot_table. ამ პარამეტრით, Microsoft გეკითხებათ: „რომელი PivotTable გსურთ გამოიყენოთ? საკმარისია მიუთითოთ კრებსითი ცხრილის ერთ-ერთი უჯრედი. ჩანაწერი 'ნახ. 11-14'!$A$3 ეხება კრებსითი ცხრილის პირველ უჯრედს, რომელშიც შეყვანილია მონაცემები. ვინაიდან ჩვენს შემთხვევაში შეგვიძლია მივუთითოთ კრებსითი ცხრილთან დაკავშირებული ნებისმიერი უჯრედი, არგუმენტი უცვლელი დავტოვოთ. მობილურის მისამართი $A$3 შესაფერისია ყველა თვალსაზრისით.
• ველი 1; ელემენტი 1. ავტომატურად გენერირებულ ფორმულაში სახელი არჩეულია ველის სახელად რეგიონიდა როგორც ველის მნიშვნელობა - ჩრდილო-აღმოსავლეთი. სწორედ აქ არის პრობლემები, რომლებიც წარმოიქმნება GET.PIVOT.TABLE.DATA ფუნქციასთან მუშაობისას. ავტომატურად შერჩეული მნიშვნელობების კოპირება შეუძლებელია, რადგან ისინი მყარი კოდირებულია. ამიტომ, თუ ფორმულები კოპირებულია მოხსენების მთელ არეალში, თქვენ მოგიწევთ მათი ხელით შეცვლა. ჩრდილო-აღმოსავლეთის მნიშვნელობის ნაცვლად, შეცვალეთ უჯრედის მითითება ფორმაში $A6. A სვეტის სახელის წინ დოლარის ნიშნის მითითებით, თქვენ მიუთითებთ, რომ მითითების მწკრივის ნაწილი შეიძლება შეიცვალოს, როდესაც ფორმულას დააკოპირებთ სვეტის უჯრედებში.
• ველი 2; ელემენტი 2. ეს არგუმენტის წყვილი განსაზღვრავს ველს თარიღიმნიშვნელობით 1. თუ თავდაპირველი კრებსითი ცხრილი დაჯგუფებულია თვეების მიხედვით, თვის ველი ინარჩუნებს ველის თავდაპირველ სახელს თარიღი. თვის რიცხვითი მნიშვნელობა არის 1, რომელიც შეესაბამება იანვარს. ძნელად მიზანშეწონილია ასეთი მნიშვნელობის გამოყენება ათობით ან თუნდაც ასობით მოხსენების უჯრედში მითითებული უზარმაზარი ფორმულების შექმნისას. უმჯობესია გამოიყენოთ ფორმულა, რომელიც ითვლის ველის მნიშვნელობებს თარიღი, B4 უჯრედის ფორმულის მსგავსი. ამ შემთხვევაში 1-ის ნაცვლად, შეგიძლიათ გამოიყენოთ ფორმულა MONTH(B$4). დოლარის ნიშანი 4-მდე მიუთითებს, რომ ფორმულას შეუძლია ველზე მნიშვნელობების მინიჭება თარიღისხვა თვეებზე დაყრდნობით, რადგან ფორმულა კოპირდება მწკრივის უჯრედებში.
• ველი 3; ელემენტი 3. ამ შემთხვევაში ველის სახელი ავტომატურად ენიჭება ინდექსიდა ველის მნიშვნელობა ფაქტი. ეს მნიშვნელობები სწორია იანვრისთვის, მაგრამ მომდევნო თვეებისთვის ველის მნიშვნელობა უნდა შეიცვალოს გეგმაზე. შეცვალეთ მყარი კოდირებული ველის მნიშვნელობა ფაქტითითო ბმულზე 5 B$.
• ველი 4; ელემენტი 4. ეს არგუმენტები არ გამოიყენება იმიტომ მინდვრები დასრულდა.

ახალი ფორმულა ნაჩვენებია ნახ. 19. ერთ წუთში, მყარი კოდირებული ფორმულის ნაცვლად, რომელიც შექმნილია ერთი მნიშვნელობით მუშაობისთვის, შეიქმნა მოქნილი ფორმულა, რომლის კოპირება შესაძლებელია მონაცემთა ნაკრების ყველა უჯრედში. დააჭირეთ ღილაკს შედიდა თქვენ მიიღებთ იგივე შედეგს, როგორც ფორმულის რედაქტირებამდე. რედაქტირებული ფორმულა იღებს შემდეგ ფორმას: =GET.DATA.PIVORTABLE("შემოსავლები" ;'ნახ. 11-14'!$A$3; "რეგიონი" ;$A6; "თარიღი" ;MONTH(B$4); "ინდიკატორი "; B$5)

ბრინჯი. 19. რედაქტირების დასრულების შემდეგ, GET.PIOTTABLE.DATA ფორმულა შესაფერისია დიაპაზონის ყველა უჯრედში კოპირებისთვის.

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

შემდეგ ეტაპზე ჩვენ დავაკონფიგურირებთ GET.DATA.PICTTABLE ფორმულას საბოლოო დაგეგმილი ინდიკატორების გამოსათვლელად. თუ უბრალოდ დააკოპირებთ ფორმულას O6 უჯრედში, ნახავთ შეცდომის შეტყობინებას #LINK! ამ შეცდომის გამოჩენის მიზეზი არის ის, რომ სიტყვა ქვედა ხაზისაკანში O4 არ არის თვის სახელი. GET.PIVORTABLE.DATA ფუნქციის სწორი მუშაობის უზრუნველსაყოფად, საჭირო მნიშვნელობა უნდა იყოს კრებსით ცხრილში. მაგრამ რადგან თავდაპირველ კრებულში ველი ინდექსიარის მეორე ველი სვეტების არეში, მონაცემთა სვეტი დაგეგმეთ შედეგირეალურად არ არსებობს. გადაიტანე ველი ინდექსიისე, რომ იგი გახდეს პირველი სვეტის არეში (სურ. 20).

ბრინჯი. 20. დაარეგულირეთ ველების განლაგება სვეტების არეში ისე, რომ გამოჩნდეს სვეტი დაგეგმეთ შედეგი

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

მოხსენების გარსის ფურცელს დაუბრუნდით, გადადით O6 უჯრედში, აკრიფეთ = (ტოლი ნიშანი) და მიუთითეთ უჯრედი N12 კრებსითი ცხრილის ფურცელზე, რომელიც შეესაბამება დაგეგმილ შედეგებს ჩრდილო-აღმოსავლეთ რეგიონისთვის. დააწკაპუნეთ შედი. შედეგად მიღებული ფორმულა =GET.DATA.PIVORTABLE("შესავალი";'ნახ. 11-14'!$A$3;"რეგიონი";"ჩრდილო-აღმოსავლეთი";"ინდიკატორი";"გეგმა"). დაარედაქტირე: =GET. SUMMARY.TABLE DATA("შემოსავლები";'ნახ. 11-14'!$A$3;"რეგიონი";$A6;"ინდიკატორი";O$5). დააკოპირეთ ეს ფორმულა O სვეტის სხვა უჯრედებში (ნახ. 21). გაითვალისწინეთ, რომ PivotTable ანგარიშის სხვადასხვა უბნების გადაადგილებისას კი, გარსი სწორად მუშაობს. რა თქმა უნდა, თუ რამდენიმე შემაჯამებელ ველს უმოქმედოდ გახდით, ჭურვი ამას ვერ უმკლავდება...

ბრინჯი. 21. საბოლოო ანგარიში, რომელიც შეიძლება წარედგინოს მენეჯერს

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

განაახლეთ ანგარიში.ანგარიშის მომავალი თვეების მონაცემებით განახლებისთვის, მიჰყევით ამ ნაბიჯებს:

1. ჩადეთ რეალური მეტრიკა ორიგინალური მონაცემთა ნაკრების ქვემოთ. იმის გამო, რომ წყაროს მონაცემები ცხრილის ფორმატშია, ცხრილის ფორმატირება ავტომატურად ვრცელდება მონაცემთა ახალ რიგებში. ასევე გაფართოვებულია ორიგინალური კრებსითი ცხრილის განმარტება (ექსელის ფაილში მე უკვე დავამატე მთელი წლის რეალური ინდიკატორები).
2. გადადით PivotTable-ზე. დააწკაპუნეთ მარჯვენა ღილაკით და აირჩიეთ განახლება. კრებსითი ცხრილის გარეგნობა შეიცვლება, მაგრამ არაუშავს.
3. გადადით მოხსენების გარსზე. პრინციპში, ყველაფერი უკვე გაკეთდა ანგარიშის განახლებისთვის, მაგრამ შედეგების ტესტირება არ ავნებს. შეცვალეთ ფორმულა A2 უჯრედში, მაგალითად, შემდეგზე: =MONTH(TODAY() +31 ;0) და ნახეთ რა მოხდება.

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

არ მეგონა, რომ ამას ოდესმე ვიტყოდი: „GET.DATA.PICTTABLE ფუნქცია უდიდესი დალოცვაა. როგორ ვარსებობდით მის გარეშე აქამდე?

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

PivotTables-ისთვის არის GET.PIVORTABLE.DATA ფუნქცია, რომელიც აბრუნებს PivotTable ანგარიშში შენახულ მონაცემებს.

ფუნქციაზე სწრაფად წვდომისთვის, თქვენ უნდა შეიყვანოთ ტოლობის ნიშანი უჯრედში (=) და მონიშნოთ სასურველი უჯრედი PivotTable-ში. Excel ავტომატურად გამოიმუშავებს GET.PIRTTABLE.DATA ფუნქციას.

GetPivotData შექმნის გამორთვა

GET.PIOTTABLE.DATA ფუნქციის ავტომატური გენერირების გამორთვისთვის აირჩიეთ კრებსითი ცხრილის ნებისმიერი უჯრედი, გადადით ჩანართზე Pivot Tables-თან მუშაობა -> ოფციებიჯგუფს საყრდენი მაგიდა.დააწკაპუნეთ ქვემოთ ისარს ჩანართის გვერდით Პარამეტრები.ჩამოსაშლელ მენიუში მოხსენით მონიშვნა ელემენტი ᲨექმნაGetPivotData.

უჯრედების მითითებების გამოყენება GET.PIVOTABLE.DATA ფუნქციაში

GET.PIOTTABLE.DATA ფუნქციაში ერთეულების ან ველების სახელების მითითების ნაცვლად, შეგიძლიათ მიუთითოთ ფურცელზე განთავსებული უჯრედები. ქვემოთ მოყვანილ მაგალითში, უჯრედი E3 შეიცავს პროდუქტის სახელს და E4 უჯრედის ფორმულა მას ეხება. შედეგად, ნამცხვრების მთლიანი მოცულობა დაბრუნდება.

კრებსითი ცხრილის ველზე მითითებების გამოყენება

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

მაგალითში, უჯრედი E3 შეიცავს მონაცემთა ველის სახელს „რაოდენობა“ და კარგი იქნება, თუ ფუნქციის ამ უჯრედს მივმართავთ, ნაცვლად იმისა, რომ ველის სახელი იყოს GET.PIVOT.TABLE.DATA ფორმულაში.

თუმცა პირველ არგუმენტს თუ შევცვლით მონაცემთა_ველი E3 უჯრედზე მითითებით, Excel დააბრუნებს შეცდომას #REF!

GET.PIVORTABLE.DATA(E3,$A$3)

უბრალოდ ცარიელი სტრიქონის (“”) დამატება უჯრედის მითითების დასაწყისში ან ბოლოს მოაგვარებს პრობლემას.

GET.PIVORTABLE.DATA(E3&"";$A$3)

ფორმულის უბრალოდ კორექტირება დააბრუნებს სწორ მნიშვნელობას.

თარიღების გამოყენება GET.PIRTTABLE.DATA ფუნქციაში

თუ იყენებთ თარიღებს GET.PIVORTABLE.DATA ფუნქციაში, შეიძლება პრობლემები შეგექმნათ, მიუხედავად იმისა, რომ თარიღი გამოჩნდება PivotTable-ში. მაგალითად, ქვემოთ მოცემული ფორმულის არგუმენტი არის თარიღი „04/21/2013“ და კრებსითი ცხრილი შეიცავს ველს გაყიდვების თარიღებით. თუმცა, ფორმულა E4 უჯრედში აბრუნებს შეცდომას.

GET.PIVORTABLE.DATA ("ტომი";$A$3;"თარიღი";"04/21/2013″)

თარიღის შეცდომების თავიდან ასაცილებლად, შეგიძლიათ გამოიყენოთ ერთ-ერთი შემდეგი მეთოდი:

• შეადარეთ თარიღის ფორმატები ფორმულაში და კრებსით ცხრილში
• გამოიყენეთ DATEVALUE ფუნქცია
• გამოიყენეთ DATE ფუნქცია
• იხილეთ უჯრედი სწორი თარიღით

შეადარეთ თარიღის ფორმატები ფორმულაში და კრებსით ცხრილში.

სწორი შედეგის მისაღებად, GET.PIVORTABLE.DATA ფუნქციის გამოყენებისას დარწმუნდით, რომ ფორმულის არგუმენტისა და კრებსითი ცხრილის თარიღის ფორმატები ერთნაირია.

E4 უჯრედში ფორმულამ გამოიყენა თარიღის ფორმატი „DD.MM.YYYY“ და დააბრუნა სწორი ინფორმაცია.

DATEVALUE ფუნქციის გამოყენებით

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

E4 უჯრედში თარიღი შეიყვანება DATEVALUE ფუნქციის გამოყენებით და Excel აბრუნებს საჭირო ინფორმაციას.

GET.PIVORTABLE.DATA ("ტომი";$A$3;"თარიღი";DATEVALUE("04/21/2013"))

DATE ფუნქციის გამოყენებით

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

GET.PIVORTABLE.DATA ("ტომი";$A$3;"თარიღი";DATE(2013,4,21))

თარიღის უჯრედის მითითება

ფორმულაში თარიღის ხელით შეყვანის ნაცვლად, შეგიძლიათ მიუთითოთ თარიღის შემცველი უჯრედი (ნებისმიერი ფორმატით, რომელშიც Excel მიიღებს მონაცემებს თარიღად). E4 უჯრედის მაგალითში ფორმულა მიუთითებს E3 უჯრედზე და Excel აბრუნებს სწორ მონაცემებს.

GET.PIVORTABLE.DATA ("ტომი";$A$3;"თარიღი";E3)

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

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

სინამდვილეში, აქ აღწერილი ყველაფერი ახალი არ არის. უფრო მეტიც, მსგავსი ტექნიკები გამოიყენება Excel 2002 წლიდან. თუმცა, მომხმარებლებთან ჩემი კომუნიკაცია აჩვენებს, რომ 1%-ზე ნაკლები იცნობს მათ. ერთადერთი კითხვა, რაც მომხმარებლებს აქვთ არის ის, თუ როგორ უნდა გამორთოთ უცნაური ფუნქცია GET.PIRTTABLE.DATA. Სამწუხაროა…

ჩამოტვირთეთ შენიშვნა ფორმატში ან ფორმატში, მაგალითები ფორმატში

კარგი, დავიწყოთ თანმიმდევრობით.

როგორ მივატოვოთ პრობლემური ფუნქცია GET.PIVOT.TABLE.DATA

GET.PIVO.TABLE.DATA ფუნქცია დიდი ხანია თავის ტკივილია მრავალი მომხმარებლისთვის. სრულიად მოულოდნელად, ყოველგვარი გაფრთხილების გარეშე, Excel 2002-ში შეიცვალა Pivot Tables-ის ქცევა. როგორც კი დაიწყებთ ფორმულების შექმნას PivotTable-ის გარეთ, რომლებიც მიუთითებენ მის მონაცემებზე, ეს ფუნქცია არსაიდან მოდის.

დავუშვათ, კრებულში ნაჩვენები ნახ. 1, თქვენ უნდა შეადაროთ 2015 და 2014 წლების მონაცემები.

ბრინჯი. 1. ორიგინალური PivotTable

1. დაამატეთ სათაური „% ზრდა“ უჯრედს D3.
2. დააკოპირეთ ფორმატი C3 უჯრედიდან D3 უჯრედში.
3. უჯრედში D4 შეიყვანეთ თანაბარი ნიშანი.
4. დააჭირეთ უჯრედს C4.
5. ჩაწერეთ a / (slash) გაყოფის ოპერაციის წარმოსადგენად.
6. დააჭირეთ უჯრედს B4.
7. ჩაწერეთ -1 და დააჭირეთ კლავიშთა კომბინაციას რომ დარჩეს იმავე საკანში. შედეგის ფორმატირება პროცენტულად. დაინახავთ, რომ დასავლეთის რეგიონში შემოსავლების 43.8%-იანი ვარდნა მოხდა (სურათი 2). არ არის ძალიან კარგი შედეგები.
8. თქვენი პირველი ფორმულის შეყვანის დასრულების შემდეგ აირჩიეთ უჯრედი D4.
9. ორჯერ დააწკაპუნეთ პატარა კვადრატზე, რომელიც მდებარეობს უჯრედის ქვედა მარჯვენა კუთხეში. ეს ველი წარმოადგენს შევსების სახელურს, რომელიც საშუალებას გაძლევთ დააკოპიროთ ფორმულა ანგარიშის მთელი სვეტის შესავსებად.

ფორმულის კოპირების დასრულების შემდეგ, ეკრანის დათვალიერებისას, მიხვდებით, რომ რაღაც არასწორია - თითოეულმა რეგიონმა აჩვენა კლება 43,8%-ით წლის განმავლობაში (ნახ. 3).

ბრინჯი. 3. როგორც კი დაასრულებთ ფორმულის კოპირებას სვეტის ყველა უჯრედში, დაინახავთ, რომ თითოეულ რეგიონში 43,8%-იანი ვარდნაა.

ეს ნაკლებად სავარაუდოა, რომ რეალურ ცხოვრებაში მოხდეს. ნებისმიერი მომხმარებელი გეტყვით, რომ ზემოთ ჩამოთვლილი ნაბიჯების შესრულების შემდეგ Excel შექმნის ფორმულას =C4/B4–1. დაბრუნდით D4 უჯრედში და ყურადღება მიაქციეთ ფორმულის ზოლს (სურათი 4). უბრალოდ რაღაც ეშმაკი! მარტივი ფორმულა =C4/B4–1 აღარ არსებობს. ამის ნაცვლად, პროგრამა ანაცვლებს კომპლექსურ კონსტრუქციას ფუნქციით GET.PIVOT.TABLE.DATA. რატომ იძლევა ეს ფორმულა სწორ შედეგებს D4 უჯრედში, მაგრამ როდესაც კოპირდება ქვემოთ მოცემულ უჯრედებში, ის უარს ამბობს მუშაობაზე?

პირველი რეაქცია იმაზე, რაც მოხდა ნებისმიერი მომხმარებლისთვის, იქნება შემდეგი: "რა არის ეს უცნაური GET.PIVOT.TABLE.DATA კონსტრუქცია, რომელმაც გაანადგურა ჩემი ანგარიში?" მომხმარებელთა უმეტესობას სურს დაუყოვნებლივ მოიცილოს ეს ფუნქცია. ზოგიერთი დასვამს კითხვას: "რატომ მოგვცა Microsoft-მა ეს ფუნქცია?"

Excel 2000-ის დღეებში მსგავსი არაფერი იყო. როცა რეგულარულად დავიწყე GET.PIVOTABLE.DATA ფუნქციასთან შეხვედრები, უბრალოდ მეზიზღებოდა. როდესაც ერთ-ერთ სემინარზე ვიღაცამ მკითხა, როგორ შეიძლება მისი გამოყენება საქმისთვის სასარგებლოდ, დავმუნჯდი. ასეთი კითხვა არასდროს დამისვამს ჩემს თავს! ჩემი აზრით, და Excel-ის მომხმარებელთა უმეტესობის აზრით, GET.DATA.PICTTABLE ფუნქცია იყო ბოროტების პროდუქტი, რომელსაც არაფერი აქვს საერთო სიკეთის ძალებთან. საბედნიეროდ, ამ ფუნქციის გამორთვის ორი გზა არსებობს.

GET.PIVOT.TABLE.DATA ფუნქციის დაბლოკვა ფორმულის შეყვანით.არსებობს მარტივი გზა GET.PIVOTABLE.DATA ფუნქციის გაჩენის თავიდან ასაცილებლად. ამისათვის თქვენ უნდა შექმნათ ფორმულა მაუსის ან კურსორის კლავიშების გამოყენების გარეშე. უბრალოდ მიჰყევით ამ ნაბიჯებს.

1. გადადით D4 უჯრედში და შეიყვანეთ = (ტოლი ნიშანი).
2. შეიყვანეთ C4.
3. შეიყვანეთ / (სლაიტი, რომელიც მიუთითებს გაყოფის ოპერაციაზე).
4. შეიყვანეთ B4.
5. შეიყვანეთ -1.
6. დააწკაპუნეთ შედი.

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

ბრინჯი. 5. უბრალოდ შეიყვანეთ =С4/В4–1 კლავიატურიდან და ფორმულა იმუშავებს ისე, როგორც უნდა

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

GET.PIVOT.TABLE.DATA ფუნქციის გამორთვა.შეგიძლიათ სამუდამოდ გამორთოთ GET.PIVOTABLE.DATA ფუნქცია. დააჭირეთ მენიუს ლენტს ფაილი → Პარამეტრები. ფანჯარაში, რომელიც იხსნება ᲞარამეტრებიExcelდეპოზიტზე წასვლა ფორმულებიდა მოხსენით ველი ამ ვარიანტის გვერდით გამოიყენეთ ფუნქციამიიღეთ PivotData ლინკებისთვის კრებსითი ცხრილისთვის. დააწკაპუნეთ Კარგი.

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

რატომ შემოგვთავაზა Microsoft-მა GET.PICTTABLE.DATA ფუნქცია.თუ ეს ფუნქცია ასეთი საშინელია, რატომ ჩართო მაიკროსოფტმა ის ნაგულისხმევად? რატომ ზრუნავენ ისინი Excel-ის ახალ ვერსიებში ამ ფუნქციის მხარდაჭერის შენარჩუნებაზე? იციან მათ მომხმარებლის განწყობა? და ჩვენ გადავდივართ ყველაზე საინტერესოზე...

GET.PIVORTABLE.DATA ფუნქციის გამოყენება PivotTables-ის გასაუმჯობესებლად

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

საყრდენი ცხრილების შექმნის ახალი ტექნიკა, შემოთავაზებული რობ კოლის (მაიკროსოფტის დეველოპერი) მიერ და განხილული შემდეგში, არის ზემოთ აღწერილი პროცესის გაუმჯობესება. ამ შემთხვევაში, პირველად იქმნება პრიმიტიული საყრდენი ცხრილი. ამ ცხრილს არ სჭირდება ფორმატირება. შემდეგ ის გადის ერთსაფეხურიან, შედარებით შრომატევად პროცესს, რათა შეიქმნას ლამაზად ფორმატირებული გარსი, რომელიც განთავსდება საბოლოო ანგარიშში. ამის შემდეგ, GET.PIVOT.TABLE.DATA ფუნქცია გამოიყენება ჭურვში მდებარე ანგარიშის მონაცემებით სწრაფად შესავსებად. ახალი მონაცემების მიღების შემდეგ შეგიძლიათ განათავსოთ იგი ფურცელზე, განაახლოთ პრიმიტიული კრებსითი ცხრილი და დაბეჭდოთ გარსში განთავსებული ანგარიში (ნახ. 9). ამ ტექნიკას აქვს მრავალი უდავო უპირატესობა. მაგალითად, თქვენ არ უნდა ინერვიულოთ ანგარიშის დაფორმატებაზე მისი შექმნისთანავე. კრებსითი ცხრილების შექმნის პროცესი თითქმის მთლიანად ავტომატიზირებული ხდება.

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

პრიმიტიული საყრდენი ცხრილის შექმნა.წყაროს მონაცემები (ნახ. 10) წარმოდგენილია ტრანზაქციების სახით, რომელიც შეიცავს ინფორმაციას დაგეგმილი და ფაქტობრივი მაჩვენებლების შესახებ თითოეული რეგიონისთვის, სადაც კომპანიას აქვს ფილიალები. დაგეგმილი ინდიკატორები დეტალურად არის აღწერილი თვის დონეზე, ხოლო ფაქტობრივი მაჩვენებლები - ცალკეული დღეების დონეზე. დაგეგმილი ინდიკატორები იქმნება მომავალი წლისთვის, ხოლო რეალური ინდიკატორები იქმნება გასული თვეებისთვის. მას შემდეგ, რაც ანგარიში განახლდება ყოველთვიურად, ეს პროცესი მნიშვნელოვნად გამარტივდება, თუ კრებსითი ცხრილის მონაცემთა წყარო იზრდება ზომაში, როდესაც ახალი მონაცემები დაემატება ბოლოში. Excel-ის ძველ ვერსიებში, მონაცემთა ასეთი წყაროს შექმნა ხდებოდა დასახელებული დინამიური დიაპაზონის გამოყენებით OFFSET ფუნქციის გამოყენებით (დამატებითი ინფორმაციისთვის იხ.). Excel 2013-ში მუშაობისას უბრალოდ აირჩიეთ მონაცემთა ერთ-ერთი უჯრედი და დააჭირეთ Ctrl+T კლავიშთა კომბინაციას (შექმენით ცხრილი). ეს ქმნის დასახელებულ მონაცემთა ნაკრებს, რომელიც ავტომატურად ფართოვდება ახალი რიგებისა და სვეტების დამატებისას.

ახლა შევქმნათ კრებსითი ცხრილი. GET.PIVORTABLE.DATA ფუნქცია საკმაოდ მძლავრია, მაგრამ მას შეუძლია დააბრუნოს მხოლოდ მნიშვნელობები, რომლებიც გამოჩნდება რეალურ კრებსით ცხრილში. ამ ფუნქციას არ შეუძლია ქეშის გადახედვა იმ ელემენტების გამოსათვლელად, რომლებიც არ არის PivotTable-ში.

შექმენით კრებსითი ცხრილი:

1. აირჩიეთ გუნდი ჩასმა → საყრდენი მაგიდადა შემდეგ დიალოგურ ფანჯარაში Pivot Table-ის შექმნადააწკაპუნეთ კარგი.
2. PivotTable ველების სიაში აირჩიეთ ველი თარიღი. თარიღების სია გამოჩნდება კრებსითი ცხრილის მარცხენა მხარეს (ნახ. 11).
3. აირჩიეთ ნებისმიერი თარიღის უჯრედი, მაგალითად A4. კონტექსტური ჩანართზე ანალიზიმდებარეობს კონტექსტური ჩანართების ერთობლიობაში Pivot Tables-თან მუშაობა, დააჭირეთ ღილაკს დაჯგუფება სფეროს მიხედვით(იხილეთ დეტალები). დიალოგურ ფანჯარაში დაჯგუფებააირჩიეთ ვარიანტი თვეები(სურ. 12). დააწკაპუნეთ კარგი. კრებსითი ცხრილის მარცხენა მხარეს გამოჩნდება თვეების სახელები (ნახ. 13).
4. გადაიტანეთ ველი თარიღიკრებსითი ცხრილის სვეტების არეში.
5. გადაიტანეთ ველი ინდექსიველის სიის სვეტების არეში კრებსითი ცხრილის.
6. აირჩიეთ ველი რეგიონი, რომელიც გამოჩნდება PivotTable-ის მარცხენა სვეტში.
7. აირჩიეთ ველი შემოსავალირომელიც გამოჩნდება PivotTable მნიშვნელობების არეალში.

ბრინჯი. 11. დაიწყეთ ველების მიხედვით დაჯგუფებით თარიღი

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

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

მიჰყევით ამ ნაბიჯებს (ნახ. 15).

1. A1 უჯრედში შეიყვანეთ მოხსენების სახელი - დაგეგმილი და ფაქტობრივი მაჩვენებლები რეგიონების მიხედვით.
2. გადადით ჩანართზე სახლში, დააჭირეთ ღილაკს უჯრედის სტილებიაირჩიეთ ფორმატი სათაური 1.
3. A2 უჯრედში შეიყვანეთ ფორმულა =MONTH(TODAY();0). ეს ფუნქცია აბრუნებს მიმდინარე თვის ბოლო დღეს. მაგალითად, თუ ამას კითხულობთ 2014 წლის 14 აგვისტოს, უჯრედი A2 აჩვენებს თარიღს 2014 წლის 31 აგვისტოს.
4. აირჩიეთ უჯრედი A2. დააჭირეთ კლავიშთა კომბინაციას Ctrl+1 დიალოგური ფანჯრის გამოსაჩენად უჯრედის ფორმატი. ჩანართზე ნომერიდააწკაპუნეთ ნივთზე ყველა ფორმატი. შეიყვანეთ მორგებული ნომრის ფორმატი როგორც "თვიდან "MMMM" დაგეგმილი მაჩვენებლები"(სურ. 16). შედეგად, გამოთვლილი თარიღი გამოჩნდება ტექსტად.
5. A5 უჯრედში შეიყვანეთ სათაური რეგიონი.
6. შეიყვანეთ რეგიონის სათაურები A სვეტის დარჩენილ უჯრედებში. რეგიონის სათაურები უნდა ემთხვეოდეს კრებსით ცხრილში ჩამოთვლილ რეგიონების სახელებს.
7. საჭიროების შემთხვევაში, დაამატეთ ეტიკეტები სვეტში დეპარტამენტის ჯამებისთვის.
8. ანგარიშის ბოლოში დაამატეთ ხაზი სულ კომპანიისთვის.
9. უჯრედში B4 შეიყვანეთ ფორმულა =DATE(YEAR($A$2),COLUMN(A1),1). ეს ფორმულა აბრუნებს 01/01/2014, 02/01/2104 და ა.შ., მიმდინარე წლის 12 თვის პირველ დღეებს.
10. აირჩიეთ უჯრედი B4. ფანჯრის გასახსნელად დააჭირეთ კლავიშთა კომბინაციას Ctrl+1 უჯრედის ფორმატი. ჩანართზე ნომერითავში ყველა ფორმატიშეიყვანეთ მორგებული ნომრის ფორმატი MMM. ეს ფორმატი აჩვენებს თვის სამ ასოს სახელს. ტექსტის გასწორება უჯრედის მარჯვენა კიდეზე.
11. დააკოპირეთ B4 უჯრედის შინაარსი C4:M4 დიაპაზონში. მწკრივი თვეების სახელებით გამოჩნდება კრებსითი ცხრილის ზედა ნაწილში.
12. B5 უჯრედში შეიყვანეთ ფორმულა =IF(MONTH(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово ფაქტიდა ამჟამინდელი და მომავლისთვის - Გეგმა.
13. დაამატეთ სათაური N5 უჯრედში ქვედა ხაზი. O4 უჯრედამდე - ქვედა ხაზი, O5 - Გეგმა, P5 - % გადახრა.
14. შეიყვანეთ ჩვეულებრივი Excel ფორმულები, რომლებიც გამოიყენება დეპარტამენტის ჯამების, კომპანიის ჯამური რიგების, გრანდიოზული ჯამური სვეტების და პროცენტული დისპერსიის სვეტების გამოსათვლელად:
    1. უჯრედში B8 შეიყვანეთ ფორმულა =SUM(B6:B7) და დააკოპირეთ იგი რიგის სხვა უჯრედებში;
    2. უჯრედში N6 შეიყვანეთ ფორმულა =SUM(B6:M6) და დააკოპირეთ იგი სვეტის სხვა უჯრედებში;
    3. უჯრედში P6 შეიყვანეთ ფორმულა =IFERROR((N6/O10)-1;0) და დააკოპირეთ იგი სვეტის სხვა უჯრედებში;
    4. უჯრედში B13 შეიყვანეთ ფორმულა =SUM(B10:B12) და დააკოპირეთ იგი რიგის სხვა უჯრედებში;
    5. უჯრედში B17 შეიყვანეთ ფორმულა =SUM(B15:B16) და დააკოპირეთ იგი რიგის სხვა უჯრედებში;
    6. უჯრედში B19 შეიყვანეთ ფორმულა =SUM(B6:B18)/2 და დააკოპირეთ იგი რიგის სხვა უჯრედებში.
15. გამოიყენეთ Heading 4 სტილი A სვეტის წარწერებზე და 4 და 5 რიგების სათაურებზე.
16. უჯრედების დიაპაზონისთვის B6:O19 აირჩიეთ რიცხვის ფორმატი ##0.
17. სვეტის P უჯრედებისთვის აირჩიეთ რიცხვის ფორმატი 0.0%.

ასე რომ, ჩვენ დავასრულეთ მოხსენების გარსის შექმნა, რომელიც ნაჩვენებია ნახ. 15. ეს ანგარიში მოიცავს ყველა საჭირო ფორმატირებას. შემდეგი სექცია აჩვენებს, თუ როგორ გამოიყენოთ GET.PIVORTABLE.DATA ფუნქცია ანგარიშის დასასრულებლად.

ბრინჯი. 15. შეატყობინეთ ჭურვი GET.PIVOT.TABLE.DATA ფორმულების დამატებამდე

GET.PIVORTABLE.DATA ფუნქციის გამოყენებით ანგარიშის გარსი მონაცემებით შევსება.ამიერიდან თქვენ გექნებათ შესაძლებლობა მიიღოთ GET.PIVOT.TABLE.DATA ფუნქციის გამოყენების ყველა უპირატესობა. თუ თქვენ გაასუფთავეთ მოსანიშნი ველი, რომელმაც ჩართო ეს ფუნქცია, დაუბრუნდით შესაბამის პარამეტრს და დააბრუნეთ ჩამრთველი (იხ. სურათი 6 ან 7-ის აღწერა).

აირჩიეთ მოხსენების გარსის B6 უჯრედი. ეს უჯრედი შეესაბამება ჩრდილო-აღმოსავლეთ რეგიონს და იანვრის რეალურ მაჩვენებლებს.

1. შეიყვანეთ = (ტოლი ნიშანი) ფორმულის შეყვანის დასაწყებად.
2. გადადით ფურცელზე კრებსითი ცხრილიდან და დააწკაპუნეთ უჯრედზე, რომელიც შეესაბამება ჩრდილო-აღმოსავლეთ რეგიონს და იანვრის ფაქტობრივ მაჩვენებლებს - C12 (ნახ. 17).
3. დააჭირეთ ღილაკს შედიდაასრულეთ ფორმულის შეყვანა და დაბრუნდით მოხსენების გარსში. შედეგად, Excel დაამატებს GET.PIOTTABLE.DATA ფუნქციას B6 უჯრედში. უჯრედი აჩვენებს $277,435 ღირებულებას.

დაიმახსოვრეთ ეს რიცხვი, რადგან დაგჭირდებათ ის ფორმულის გაშვების შედეგებთან შედარებისას, რომელსაც მოგვიანებით დაარედაქტირებთ. პროგრამის მიერ გენერირებული ფორმულა ასეთია: =GET.DATA.PIVOTABLE(" შემოსავალი " ;'ნახ. 11-14′!$A$3; " რეგიონი " ; " ჩრდილო-აღმოსავლეთი " ; " თარიღი " ;1; "ინდიკატორი" "ფაქტი"). თუ აქამდე უგულებელყოფთ GET.PIRTTABLE.DATA ფუნქციას, დროა, უფრო დეტალურად შეხედოთ მას. ნახ. 18 ეს ფორმულა ნაჩვენებია რედაქტირების რეჟიმში მინიშნებასთან ერთად.

ფუნქციის არგუმენტები:

• მონაცემთა_ველი. ველი PivotTable მნიშვნელობების არედან. გთხოვთ გაითვალისწინოთ: ამ შემთხვევაში ველი გამოიყენება შემოსავალი, მაგრამ არა თანხა სფეროს მიხედვით შემოსავალი.
• Pivot_table. ამ პარამეტრით, Microsoft გეკითხებათ: „რომელი PivotTable გსურთ გამოიყენოთ? საკმარისია მიუთითოთ კრებსითი ცხრილის ერთ-ერთი უჯრედი. ჩანაწერი 'ნახ. 11-14'!$A$3 ეხება კრებსითი ცხრილის პირველ უჯრედს, რომელშიც შეყვანილია მონაცემები. ვინაიდან ჩვენს შემთხვევაში შეგვიძლია მივუთითოთ კრებსითი ცხრილთან დაკავშირებული ნებისმიერი უჯრედი, არგუმენტი უცვლელი დავტოვოთ. მობილურის მისამართი $A$3 შესაფერისია ყველა თვალსაზრისით.
• ველი 1; ელემენტი 1. ავტომატურად გენერირებულ ფორმულაში სახელი არჩეულია ველის სახელად რეგიონიდა როგორც ველის მნიშვნელობა - ჩრდილო-აღმოსავლეთი. სწორედ აქ არის პრობლემები, რომლებიც წარმოიქმნება GET.PIVOT.TABLE.DATA ფუნქციასთან მუშაობისას. ავტომატურად შერჩეული მნიშვნელობების კოპირება შეუძლებელია, რადგან ისინი მყარი კოდირებულია. ამიტომ, თუ ფორმულები კოპირებულია მოხსენების მთელ არეალში, თქვენ მოგიწევთ მათი ხელით შეცვლა. ჩრდილო-აღმოსავლეთის მნიშვნელობის ნაცვლად, შეცვალეთ უჯრედის მითითება ფორმაში $A6. A სვეტის სახელის წინ დოლარის ნიშნის მითითებით, თქვენ მიუთითებთ, რომ მითითების მწკრივის ნაწილი შეიძლება შეიცვალოს, როდესაც ფორმულას დააკოპირებთ სვეტის უჯრედებში.
• ველი 2; ელემენტი 2. ეს არგუმენტის წყვილი განსაზღვრავს ველს თარიღიმნიშვნელობით 1. თუ თავდაპირველი კრებსითი ცხრილი დაჯგუფებულია თვეების მიხედვით, თვის ველი ინარჩუნებს ველის თავდაპირველ სახელს თარიღი. თვის რიცხვითი მნიშვნელობა არის 1, რომელიც შეესაბამება იანვარს. ძნელად მიზანშეწონილია ასეთი მნიშვნელობის გამოყენება ათობით ან თუნდაც ასობით მოხსენების უჯრედში მითითებული უზარმაზარი ფორმულების შექმნისას. უმჯობესია გამოიყენოთ ფორმულა, რომელიც ითვლის ველის მნიშვნელობებს თარიღი, B4 უჯრედის ფორმულის მსგავსი. ამ შემთხვევაში 1-ის ნაცვლად, შეგიძლიათ გამოიყენოთ ფორმულა MONTH(B$4). დოლარის ნიშანი 4-მდე მიუთითებს, რომ ფორმულას შეუძლია ველზე მნიშვნელობების მინიჭება თარიღისხვა თვეებზე დაყრდნობით, რადგან ფორმულა კოპირდება მწკრივის უჯრედებში.
• ველი 3; ელემენტი 3. ამ შემთხვევაში ველის სახელი ავტომატურად ენიჭება ინდექსიდა ველის მნიშვნელობა ფაქტი. ეს მნიშვნელობები სწორია იანვრისთვის, მაგრამ მომდევნო თვეებისთვის ველის მნიშვნელობა უნდა შეიცვალოს გეგმაზე. შეცვალეთ მყარი კოდირებული ველის მნიშვნელობა ფაქტითითო ბმულზე 5 B$.
• ველი 4; ელემენტი 4. ეს არგუმენტები არ გამოიყენება იმიტომ მინდვრები დასრულდა.

ახალი ფორმულა ნაჩვენებია ნახ. 19. ერთ წუთში, მყარი კოდირებული ფორმულის ნაცვლად, რომელიც შექმნილია ერთი მნიშვნელობით მუშაობისთვის, შეიქმნა მოქნილი ფორმულა, რომლის კოპირება შესაძლებელია მონაცემთა ნაკრების ყველა უჯრედში. დააჭირეთ ღილაკს შედიდა თქვენ მიიღებთ იგივე შედეგს, როგორც ფორმულის რედაქტირებამდე. რედაქტირებული ფორმულა იღებს შემდეგ ფორმას: =GET.DATA.PIVORTABLE("შემოსავლები" ;'ნახ. 11-14'!$A$3; "რეგიონი" ;$A6; "თარიღი" ;MONTH(B$4); "ინდიკატორი "; B$5)

ბრინჯი. 19. რედაქტირების დასრულების შემდეგ, GET.PIOTTABLE.DATA ფორმულა შესაფერისია დიაპაზონის ყველა უჯრედში კოპირებისთვის.

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

შემდეგ ეტაპზე ჩვენ დავაკონფიგურირებთ GET.DATA.PICTTABLE ფორმულას საბოლოო დაგეგმილი ინდიკატორების გამოსათვლელად. თუ უბრალოდ დააკოპირებთ ფორმულას O6 უჯრედში, ნახავთ შეცდომის შეტყობინებას #LINK! ამ შეცდომის გამოჩენის მიზეზი არის ის, რომ სიტყვა ქვედა ხაზისაკანში O4 არ არის თვის სახელი. GET.PIVORTABLE.DATA ფუნქციის სწორი მუშაობის უზრუნველსაყოფად, საჭირო მნიშვნელობა უნდა იყოს კრებსით ცხრილში. მაგრამ რადგან თავდაპირველ კრებულში ველი ინდექსიარის მეორე ველი სვეტების არეში, მონაცემთა სვეტი დაგეგმეთ შედეგირეალურად არ არსებობს. გადაიტანე ველი ინდექსიისე, რომ იგი გახდეს პირველი სვეტის არეში (სურ. 20).

ბრინჯი. 20. დაარეგულირეთ ველების განლაგება სვეტების არეში ისე, რომ გამოჩნდეს სვეტი დაგეგმეთ შედეგი

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

მოხსენების გარსის ფურცელს დაუბრუნდით, გადადით O6 უჯრედში, აკრიფეთ = (ტოლი ნიშანი) და მიუთითეთ უჯრედი N12 კრებსითი ცხრილის ფურცელზე, რომელიც შეესაბამება დაგეგმილ შედეგებს ჩრდილო-აღმოსავლეთ რეგიონისთვის. დააწკაპუნეთ შედი. შედეგად მიღებული ფორმულა =GET.DATA.PIVORTABLE("შესავალი";'ნახ. 11-14'!$A$3;"რეგიონი";"ჩრდილო-აღმოსავლეთი";"ინდიკატორი";"გეგმა"). დაარედაქტირე: =GET. SUMMARY.TABLE DATA("შემოსავლები";'ნახ. 11-14'!$A$3;"რეგიონი";$A6;"ინდიკატორი";O$5). დააკოპირეთ ეს ფორმულა O სვეტის სხვა უჯრედებში (ნახ. 21). გაითვალისწინეთ, რომ PivotTable ანგარიშის სხვადასხვა უბნების გადაადგილებისას კი, გარსი სწორად მუშაობს. რა თქმა უნდა, თუ რამდენიმე შემაჯამებელ ველს უმოქმედოდ გახდით, ჭურვი ამას ვერ უმკლავდება...

ბრინჯი. 21. საბოლოო ანგარიში, რომელიც შეიძლება წარედგინოს მენეჯერს

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

განაახლეთ ანგარიში.ანგარიშის მომავალი თვეების მონაცემებით განახლებისთვის, მიჰყევით ამ ნაბიჯებს:

1. ჩადეთ რეალური მეტრიკა ორიგინალური მონაცემთა ნაკრების ქვემოთ. იმის გამო, რომ წყაროს მონაცემები ცხრილის ფორმატშია, ცხრილის ფორმატირება ავტომატურად ვრცელდება მონაცემთა ახალ რიგებში. ასევე გაფართოვებულია ორიგინალური კრებსითი ცხრილის განმარტება (ექსელის ფაილში მე უკვე დავამატე მთელი წლის რეალური ინდიკატორები).
2. გადადით PivotTable-ზე. დააწკაპუნეთ მარჯვენა ღილაკით და აირჩიეთ განახლება. კრებსითი ცხრილის გარეგნობა შეიცვლება, მაგრამ არაუშავს.
3. გადადით მოხსენების გარსზე. პრინციპში, ყველაფერი უკვე გაკეთდა ანგარიშის განახლებისთვის, მაგრამ შედეგების ტესტირება არ ავნებს. შეცვალეთ ფორმულა A2 უჯრედში, მაგალითად, შემდეგზე: =MONTH(TODAY() +31 ;0) და ნახეთ რა მოხდება.

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

არ მეგონა, რომ ამას ოდესმე ვიტყოდი: „GET.DATA.PICTTABLE ფუნქცია უდიდესი დალოცვაა. როგორ ვარსებობდით მის გარეშე აქამდე?

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