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

MS Excel Pivot Table არის მონაცემთა ანალიზის ძლიერი ინსტრუმენტი. ეს ინსტრუმენტი საშუალებას გაძლევთ ამოიღოთ ინფორმაცია მონაცემთა დიდი მასივიდან, ნებისმიერ კონტექსტში, მაუსის მხოლოდ რამდენიმე დაწკაპუნებით. ამ სტატიაში შევეცდები მარტივ და გასაგებ ენაზე ეტაპობრივად აგიხსნათ რა და როგორ უნდა გავაკეთოთ იმისათვის, რომ თქვენს განკარგულებაში მივიღოთ ეს ძალიან მოსახერხებელი MS Excel ინსტრუმენტი.

მოთხოვნები წყაროს მონაცემებისთვის.

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

მოდი ვნახოთ, სინამდვილეში რა არის ამ ცხრილის „სისწორე“? სისწორე ის არის, რომ:

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

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

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

აქ არის კიდევ ერთი მაგალითი "არასწორი მასივი":

აქ არის მთელი რიგი "უწესოებანი":

• პირველ რიგში, სვეტებს "მიმწოდებელი" და "კატეგორია" აქვს ცარიელი უჯრედები, ამიტომ ფილტრს ვერ გამოვიყენებთ;
• მეორეც, თითქმის ყველა სვეტში არის სტრიქონები "სულ...", ისინი სრულიად არასაჭიროა Pivot Table-ის ასაშენებლად, უფრო მეტიც, ისინი მხოლოდ ხელს შეუშლიან;
• მესამე, სვეტი "გრანდ ტოტალი" ასევე არ არის საჭირო;
• მეოთხე, იგივე ტიპის მონაცემები, სახელდობრ „ფული“, არის სამ სვეტში: „იანვარი“, „თებერვალი“ და „მარ“, რაც მნიშვნელოვნად გაართულებს Pivot Table-ის აგებას, რაც ნიშნავს, რომ ჩვენ მოგვიწევს გააკეთე რამე მასთან...

მაგრამ ეს ყველაფერი ცალკე საუბრისთვისაა, თუ გსურთ გაიგოთ, თუ როგორ სწრაფად გადაიყვანოთ ასეთი „დახრილი ცხრილები“ ​​„სწორ მასივებად“, წაიკითხეთ სტატიები: „როგორ სწრაფად ავაშენოთ საყრდენი ცხრილი 1C ან SAP ანგარიშიდან?“ და "როგორ სწრაფად გადავიტანოთ ცხრილი კრებსითი ცხრილების მასივად?"

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

აიღეთ „სწორი მასივი“, მოათავსეთ კურსორი მასივის ნებისმიერ უჯრედში, აირჩიეთ ჩანართი „ჩასმა“ მთავარ მენიუში, მარცხენა კუთხეში, „ცხრილების“ განყოფილებაში, დააჭირეთ ღილაკს „Pivot Table“:

დიალოგურ ფანჯარაში "Create Pivot Table", რომელიც იხსნება, დააწკაპუნეთ "OK":

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

დავიწყოთ Pivot Table-ის დაყენება. როგორი ჭრა გვინდა? პროდუქტის კატეგორიები მენეჯერის მიერ - გთხოვთ. გადაიტანეთ მაუსი "კატეგორიის" ველზე, დააწკაპუნეთ მასზე მაუსის მარცხენა ღილაკით და გადაიტანეთ "ROWS" ველში. ველი „მენეჯერი“ ჩაითვლება „COLUMNS“-ში, ხოლო „Amount“ ჩაითვლება „VALUES“-ში:

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

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

თქვენი დირექტორი ავალებს, მოამზადოთ მოკლე ანგარიში ყველა საქონლის გაყიდვების შესახებ რეგიონის (რეგიონის) მიხედვით. პრობლემის გადაჭრა შესაძლებელია შემდეგნაირად.

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

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

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

— შესაძლებელია თუ არა ანგარიშის გაკეთება არა შემოსავალზე, არამედ მოგებაზე?

— შესაძლებელია თუ არა პროდუქტების ჩვენება მწკრივების მიხედვით და რეგიონების სვეტების მიხედვით?

— შესაძლებელია თუ არა ასეთი ცხრილების შექმნა თითოეული მენეჯერისთვის ცალ-ცალკე?

მაშინაც კი, თუ Excel-ის გამოცდილი მომხმარებელი ხართ, ახალი ანგარიშების შექმნას დიდი დრო დასჭირდება. ეს არ არის საუბარი შესაძლო შეცდომებზე. თუმცა, თუ იცით კრებსითი ცხრილები, მაშინ პასუხი არის: დიახ, მჭირდება 5 წუთი, შეიძლება ნაკლები.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

შედეგად, ჩვენ ვიღებთ ნამდვილ საყრდენ ცხრილს.

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

შემოსავალი მოგებით შევცვალოთ.

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

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

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

საწყისი მონაცემები

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

1. მონაცემთა საუკეთესო ფორმატი არის Excel-ის ცხრილი. კარგია, რადგან თითოეულ ველს აქვს სახელი და როდესაც ახალი სტრიქონები ემატება, ისინი ავტომატურად შედის კრებსით ცხრილში.

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

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

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

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

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

ან
ჩანართში ბრძანების მეშვეობით მონაცემები - განაახლეთ ყველა.

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

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

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

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

ცოტა ისტორია

ცხრილების შესაქმნელად პროგრამების შემუშავების გარიჟრაჟზე, Lotus-ის წესები იყო 1-2-3. მისი დომინირება იმდენად სრული იყო, რომ მაიკროსოფტის ძალისხმევა, შეემუშავებინა საკუთარი პროგრამული უზრუნველყოფა (Excel), როგორც Lotus-ის ალტერნატივა, დროის კარგვად ჩანდა. ახლა კი 2010 წლამდე! Excel დომინირებს ცხრილების ინდუსტრიაში იმაზე მეტად, ვიდრე ლოტუსს ოდესმე გაუკეთებია თავის ისტორიაში და იმ ადამიანების რიცხვი, ვინც კვლავ იყენებს Lotus-ს, უახლოვდება ნულს. როგორ შეიძლებოდა ეს მომხდარიყო? რა იყო მოვლენების ასეთი დრამატული განვითარების მიზეზი?

ანალიტიკოსები გამოყოფენ ორ ძირითად ფაქტორს:

• პირველ რიგში, Lotus-მა გადაწყვიტა, რომ ეს ახლადშექმნილი GUI პლატფორმა სახელწოდებით Windows იყო მხოლოდ წარმავალი მოდა, რომელიც დიდხანს არ გაგრძელდებოდა. მათ უარი თქვეს Lotus 1-2-3-ის Windows ვერსიის შექმნაზე (მაგრამ მხოლოდ რამდენიმე წლის განმავლობაში), იწინასწარმეტყველეს, რომ მათი პროგრამული უზრუნველყოფის DOS ვერსია იყო ყველაფერი, რაც მომხმარებელს ოდესმე დასჭირდებოდა. Microsoft-მა, რა თქმა უნდა, შეიმუშავა Excel სპეციალურად Windows-ისთვის.
• მეორეც, Microsoft-მა შეიმუშავა ინსტრუმენტი Excel-ში, სახელწოდებით pivot tables, რომელიც არ იყო ხელმისაწვდომი Lotus 1-2-3-ში. Pivot ცხრილები, Excel-ის ექსკლუზივი, იმდენად წარმოუდგენლად სასარგებლო იყო, რომ ადამიანები მიდრეკილნი იყვნენ ესწავლათ ახალი Excel პროგრამული უზრუნველყოფა, ვიდრე შეენარჩუნებინათ Lotus 1-2-3, რომელსაც ისინი არ გააჩნდა.

კრებსითი ცხრილები, ზოგადად Windows-ის წარმატების შეუფასებლობასთან ერთად, Lotus 1-2-3-ისთვის სიკვდილის მსვლელობა ითამაშა და Microsoft Excel-ის წარმატების დასაწყისი იყო.

რა არის Pivot Tables?

მაშ, რა არის საუკეთესო გზა აღწეროთ რა არის კრებსითი ცხრილები?

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

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

შეხედეთ ქვემოთ მოცემულ ფიგურაში მოცემულ მონაცემებს:

გთხოვთ გაითვალისწინოთ, რომ ეს არ არის დაუმუშავებელი მონაცემები, რადგან ისინი უკვე შეჯამებულია. საკანში B3 ჩვენ ვხედავთ $30,000, რაც სავარაუდოდ არის ჯამური, რაც ჯეიმს კუკმა გააკეთა იანვარში. მაშინ სად არის წყაროს მონაცემები? საიდან გაჩნდა 30000 დოლარის მაჩვენებელი? სად არის ორიგინალური გაყიდვების სია, საიდანაც ეს ყოველთვიური ჯამი იყო მიღებული? აშკარაა, რომ ვიღაცამ დიდი ძალისხმევა გასწია ბოლო ექვსი თვის განმავლობაში გაყიდვების ყველა მონაცემის ორგანიზებისა და დასალაგებლად და გადაქცევის შემაჯამებელ ცხრილად, რომელსაც ჩვენ ვხედავთ. როგორ ფიქრობთ, რამდენი დრო დასჭირდა? საათი? Ათ საათზე?

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

თუ დავუბრუნდებით გაყიდვების თავდაპირველ სიას, ეს ასე გამოიყურება:

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

როგორ შევქმნათ საყრდენი ცხრილი?

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

მოდით გავუშვათ Excel... და ჩატვირთოთ ეს სია...

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

აირჩიეთ ნებისმიერი უჯრედი ამ სიიდან:

შემდეგ ჩანართზე ჩასმა(ჩასმა) აირჩიეთ ბრძანება PivotTable(შემაჯამებელი ცხრილი):

გამოჩნდება დიალოგური ფანჯარა შექმენით PivotTable(PivotTable-ის შექმნა) ორი კითხვით თქვენთვის:

• რა მონაცემები უნდა გამოვიყენო ახალი კრებსითი ცხრილის შესაქმნელად?
• სად უნდა განვათავსო საყრდენი ცხრილი?

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

Excel შექმნის ახალ სამუშაო ფურცელს და განათავსებს მასზე ცარიელ კრებსით ცხრილს:

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

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

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

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

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

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

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

სულ უფრო საინტერესო ხდება! ჩვენი კრებსითი ცხრილი იწყებს ფორმირებას...

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

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

საყრდენი ცხრილის დაყენება

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

ჩვენ ვიღებთ შედეგს:

ძალიან მაგრად გამოიყურება!

ახლა მოდით გავაკეთოთ სამგანზომილებიანი ცხრილი. როგორი შეიძლება იყოს ასეთი მაგიდა? მოდით შევხედოთ…

გადაიტანეთ სათაური პაკეტი(კომპლექსი) ტერიტორიამდე მოხსენების ფილტრი(ფილტრები):

დააკვირდით სად დასრულდა...

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

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

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

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

Pivot ცხრილების ფორმატირება Excel-ში

ცხადია, PivotTables ძალიან ძლიერი ინსტრუმენტია, მაგრამ ჯერჯერობით შედეგები ცოტა მარტივი და მოსაწყენი ჩანს. მაგალითად, რიცხვები, რომლებსაც ჩვენ ვაგროვებთ, არ ჰგავს დოლარის ოდენობას - ისინი უბრალოდ რიცხვებია. გამოვასწოროთ ეს.

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

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

გამოჩნდება დიალოგური ფანჯარა ღირებულების ველის პარამეტრები(მნიშვნელობის ველის პარამეტრები).

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

სიიდან კატეგორია(ნომრის ფორმატები) აირჩიეთ Აღრიცხვა(ფინანსური) და დააყენეთ ათწილადების რაოდენობა ნულზე. ახლა დააჭირეთ რამდენჯერმე კარგიდავუბრუნდეთ ჩვენს კრებულს.

როგორც ხედავთ, ნომრები საბოლოოდ ჩამოყალიბდა, როგორც დოლარის თანხები.

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

გახსენით ჩანართი PivotTable ინსტრუმენტები: დიზაინი(საყრდენი ცხრილებთან მუშაობა: დიზაინერი):

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

სხვა პარამეტრები კრებსითი ცხრილებისთვის Excel-ში

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

პირველი, ამოიღეთ ჩანაწერი. Გადახდის საშუალებარეგიონიდან სვეტის ეტიკეტები(Სვეტები). ამისათვის გადაიტანეთ იგი სათაურების სიაში და მის ადგილას გადაიტანეთ სათაური დაჯავშნის თარიღი(დაჯავშნის თარიღი):

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

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

გამოჩნდება დაჯგუფების დიალოგური ფანჯარა. Ჩვენ ვირჩევთ თვეები(თვეები) და დააჭირეთ კარგი:

ვოილა! ეს ცხრილი ბევრად უფრო სასარგებლოა:

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

არის კიდევ ერთი ძალიან მნიშვნელოვანი წერტილი, რომელიც უნდა იცოდე! თქვენ შეგიძლიათ შექმნათ არა ერთი, არამედ რამდენიმე დონის მწკრივის (ან სვეტის) სათაურები:

...და ასე გამოიყურება...

იგივე შეიძლება გაკეთდეს სვეტების სათაურებით (ან თუნდაც ფილტრებით).

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

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

სიაში მნიშვნელობის ველის შეჯამება(ოპერაცია) დიალოგურ ფანჯარაში ღირებულების ველის პარამეტრები(Value Field Options) აირჩიეთ საშუალო(საშუალო):

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

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

თუ გსურთ, შეგიძლიათ დაუყოვნებლივ მიიღოთ ერთ კრებულში მოთავსებული ჯამი, საშუალო და რაოდენობა (გაყიდვები).

აქ მოცემულია ნაბიჯ-ნაბიჯ სახელმძღვანელო, თუ როგორ უნდა გააკეთოთ ეს, დაწყებული ცარიელი კრებსითი ცხრილით:

1. გადაიტანეთ სათაური გამყიდველი(გაყიდვების წარმომადგენელი) რეგიონში სვეტის ეტიკეტები(Სვეტები).
2. გადაიტანეთ სათაური სამჯერ თანხა(ღირებულება) ფართობზე ღირებულებები(ღირებულებები).
3. პირველი სფეროსთვის თანხასახელის შეცვლა სულ(თანხა) და რიცხვის ფორმატი ამ ველში არის Აღრიცხვა(Ფინანსური). ათობითი ადგილების რაოდენობა არის ნული.
4. მეორე ველი თანხასახელი საშუალოე, დააყენეთ მისთვის ოპერაცია საშუალო(საშუალო) და შეცვალეთ რიცხვის ფორმატი ამ ველში Აღრიცხვა(ფინანსური) ათწილადების რაოდენობა ნულის ტოლია.
5. მესამე სფეროსთვის თანხადააყენეთ სათაური დათვალეთდა ოპერაცია მისთვის - დათვალეთ(რაოდენობა)
6. ტერიტორიაზე სვეტის ეტიკეტები(სვეტები) ველი ავტომატურად შეიქმნა Σ ღირებულებები(Σ მნიშვნელობები) - გადაიტანეთ იგი ზონაში მწკრივის ეტიკეტები(სიმები)

ეს არის ის, რასაც მივიღებთ საბოლოოდ:

გაყიდვების ჯამი, საშუალო და რაოდენობა - ყველაფერი ერთ კრებულში!

დასკვნა

კრებსითი ცხრილები Microsoft Excel-ში შეიცავს ბევრ, ბევრ ფუნქციას და პარამეტრს. ასეთი მოკლე სტატია ვერც კი მიახლოვდება ყველა მათგანის გაშუქებას. კრებსითი ცხრილების ყველა შესაძლებლობის სრულად აღწერისთვის საჭიროა პატარა წიგნი ან დიდი ვებსაიტი. მამაც და ცნობისმოყვარე მკითხველებს შეუძლიათ გააგრძელონ კრებსითი ცხრილების შესწავლა. ამისათვის უბრალოდ დააწკაპუნეთ მაუსის მარჯვენა ღილაკით კრებსითი ცხრილის თითქმის ნებისმიერ ელემენტზე და ნახეთ რა ფუნქციები და პარამეტრები იხსნება. ლენტზე ნახავთ ორ ჩანართს: PivotTable Tools: ოფციები(ანალიზი) და დიზაინი(კონსტრუქტორი). ნუ შეგეშინდებათ შეცდომის დაშვების; თქვენ ყოველთვის შეგიძლიათ წაშალოთ კრებსითი ცხრილი და დაიწყოთ თავიდან. თქვენ გაქვთ შესაძლებლობა, რომელიც დიდი ხნის განმავლობაში DOS და Lotus 1-2-3 მომხმარებლებს არასდროს ჰქონიათ.

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

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

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

როგორ შევქმნათ

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

კრებსითი ცხრილის შესაქმნელად, აირჩიეთ ნებისმიერი უჯრედი ორიგინალიდან, შემდეგ გადადით "ჩასმა" ჩანართზე და დააჭირეთ ღილაკს "საყრდენი მაგიდა".

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

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

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

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

როგორ მოვაწყოთ ველები

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

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

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

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

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

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

ცხრილი მიიღებს შემდეგ ფორმას.

ახლა გადაიტანეთ "თანხის" ველი "ღირებულებების" ზონაში.

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

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

შემდეგ ფანჯარაში აირჩიეთ "რიცხობრივი", შეგიძლიათ მონიშნოთ ყუთი "ბიტი ჯგუფის გამყოფი"და დააჭირეთ "OK".

როგორ ვიმუშაოთ მონაცემებთან

მას შემდეგ რაც გავითვალისწინებთ ყველა საჭირო ველს, შეგვიძლია დავიწყოთ მუშაობა Excel-ში კრებსითი ცხრილით. გამყიდველის არჩევა: შეგიძლიათ აირჩიოთ ერთი, რამდენიმე ან ყველა ერთდროულად ველის მონიშვნით "აირჩიეთ მრავალი ელემენტი".

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

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

შემდეგ ფანჯარაში აირჩიეთ "რაოდენობა" სიიდან, აირჩიეთ ის, რაც კონკრეტულად არის შესაფერისი თქვენი შემთხვევისთვის და დააჭირეთ "OK".

ახლა, ღირებულებებს რომ გადავხედოთ, შეგვიძლია გავიგოთ, რომ რომამ თებერვალში ორი მაისური გაყიდა.

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

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

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

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

PivotTables-ის სიმძლავრის დანახვა ისეთივე მარტივია, როგორც ორიგინალური მონაცემების შედარება PivotTable-ის ვერსიასთან; მხოლოდ რამდენიმე დაწკაპუნებით, მე შევძელი საქონლის საშუალო ფასი სახელმწიფოების მიხედვით მენახა.

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

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

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

როგორ გამოვიყენოთ გაფართოებული PivotTable ტექნიკები Excel-ში (მოკლე ვიდეო)

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

5 გაფართოებული Pivot Table ხრიკი Excel-ში

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

1. სექციები

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

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

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

სლაისერის დასამატებლად დააწკაპუნეთ კრებსით ცხრილში და იპოვეთ ჩანართი ანალიზი Excel-ის ლენტაზე.

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

გამართავს Ctrlკლავიატურაზე, რათა აირჩიოთ რამდენიმე სლაისერი ელემენტი, რომელიც მოიცავს მრავალ არჩევანს სვეტიდან, როგორც PivotTable მონაცემები.

2. ვადები

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

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

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

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

დააწკაპუნეთ და გადაიტანეთ Timeline ფანჯარაში, რათა აირჩიოთ კონკრეტული დროის დიაპაზონი PivotTable-ისთვის.

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

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

3. ტაბულური ხედი

Excel-ში PivotTables-ის სტანდარტული ხედი ჩანჩქერს ჰგავს; რაც უფრო მეტ ველს გადაათრევთ მწკრივებზე, Excel ქმნის უფრო მეტ „ფენას“ მონაცემებში.

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

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

რატომ უნდა გამოიყენოთ ცხრილის ხედი? თქვენი PivotTable მონაცემების კლასიკურ ცხრილის სტილში გადატანა საშუალებას მოგცემთ უფრო მარტივად შეიყვანოთ მონაცემთა ფორმულები ან ჩასვათ ისინი ცალკე ანგარიშში.

გამოიყენეთ Table View, რათა თქვენი PivotTable უფრო სტანდარტულ სტრიქონებსა და სვეტებს დაემსგავსოს.

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

4. გამოთვლილი ველები

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

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

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

გამოყენება ანალიზი > ველები, წევრები და ნაკრები > გამოთვლილი ველიჩასვით გამოთვლილი ველი თქვენს PivotTable-ში.

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

შეკვეთის მთლიანი ღირებულების გამოსათვლელად გავამრავლე არსებული ერთეულის ფასის ველი ( Ერთეულის ფასი) რაოდენობის ველზე ( რაოდენობა).

ამ ველის სახელის დამატების შემდეგ დავამატებ გამრავლების ნიშანს * და შემდეგ ორჯერ დააწკაპუნეთ მთლიან რაოდენობაზე (რაოდენობაზე). მოდით წავიდეთ წინ და დავაწკაპუნოთ კარგი.

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

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

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

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

Წადი ჩასმა >სცადოთ ეს ფუნქცია.

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

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

PivotTable Recommendation ფუნქცია გთავაზობთ მრავალ ვარიანტს თქვენი მონაცემების ერთი დაწკაპუნებით გასაანალიზებლად.

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

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

გაიმეორეთ და განაგრძეთ სწავლა (კიდევ მეტი Excel გაკვეთილებით)

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

ეს გაკვეთილები თქვენს უნარებს Excel-ისა და Pivot Tables-ის გამოყენებით შემდეგ დონეზე გადაიყვანს. შეამოწმეთ ისინი:

• ExcelZoo-ს აქვს შესანიშნავი მიმოხილვა Pivot Table-ის ტექნიკის შესახებ მათ სტატიაში, 10 გაკვეთილი კრებსითი ცხრილების დაუფლებისთვის (ინგლისურად).
• ჩვენ Envato Tuts+-ში დავფარეთ საყრდენი ცხრილები დამწყებთათვის გაკვეთილით.
• Microsoft Excel-ის უფრო მარტივი შესავალისთვის, იხილეთ ჩვენი გაკვეთილების სერია.

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