Mündəricat
Pivot masaları Excel-də ən güclü vasitələrdən biridir. Onlar sizə bir neçə siçan klikləməklə böyük həcmli məlumatların müxtəlif xülasəsini təhlil etməyə və ümumiləşdirməyə imkan verir. Bu yazıda biz pivot cədvəlləri ilə tanış olacağıq, onların nə olduğunu anlayacağıq, onları necə yaratmağı və fərdiləşdirməyi öyrənəcəyik.
Bu məqalə Excel 2010-dan istifadə etməklə yazılmışdır. Pivot Cədvəllər konsepsiyası illər ərzində çox dəyişməyib, lakin Excel-in hər yeni versiyasında onları yaratmağınız bir qədər fərqlidir. Əgər 2010-cu il deyil, Excel versiyasınız varsa, bu məqalədəki ekran görüntülərinin ekranınızda gördüyünüzdən fərqli olacağına hazır olun.
tarixi bir az
Elektron cədvəl proqramının ilk günlərində Lotus 1-2-3 qayda topu idi. Onun üstünlüyü o qədər tam idi ki, Microsoft-un Lotus-a alternativ olaraq öz proqram təminatını (Excel) inkişaf etdirmək səyləri vaxt itkisi kimi görünürdü. İndi 2010-cu ilə sürətlə irəliləyin! Excel öz tarixində Lotus kodundan daha çox cədvəllərdə üstünlük təşkil edir və hələ də Lotusdan istifadə edənlərin sayı sıfıra yaxındır. Bu necə baş verə bilərdi? Hadisələrin belə dramatik dönüşünün səbəbi nə idi?
Analitiklər iki əsas amili müəyyənləşdirirlər:
- Birincisi, Lotus qərara gəldi ki, Windows adlı bu yeni yaradılan GUI platforması sadəcə olaraq uzun sürməyəcək bir dəbdir. Onlar Lotus 1-2-3-ün Windows versiyasını yaratmaqdan imtina etdilər (lakin yalnız bir neçə il ərzində), onların proqram təminatının DOS versiyasının bütün istehlakçıların ehtiyac duyacağı olacağını proqnozlaşdırdılar. Microsoft təbii olaraq Excel-i Windows üçün xüsusi olaraq inkişaf etdirdi.
- İkincisi, Microsoft Excel-də Lotus 1-2-3-də mövcud olmayan PivotTables adlı alət təqdim etdi. Excel üçün eksklüziv olan Pivot Cədvəllər o qədər faydalı olduğunu sübut etdi ki, insanlar Lotus 1-2-3 ilə davam etməkdənsə, yeni Excel proqram dəstinə sadiq qalmağa meyilli oldular.
PivotTables, ümumiyyətlə Windows-un uğurunu qiymətləndirməməklə yanaşı, Lotus 1-2-3 üçün ölüm marşını oynadı və Microsoft Excel-in uğurunun başlanğıcını qoydu.
Pivot masalar nədir?
Beləliklə, Pivot Cədvəllərin nə olduğunu xarakterizə etməyin ən yaxşı yolu nədir?
Sadə dillə desək, pivot cədvəllər bu məlumatların təhlilini asanlaşdırmaq üçün yaradılmış bəzi məlumatların xülasəsidir. Əl ilə yaradılmış cəmlərdən fərqli olaraq, Excel Pivot Cədvəlləri interaktivdir. Yaradılandan sonra, ümid etdiyiniz şəkli verməsələr, onları asanlıqla dəyişdirə bilərsiniz. Cəmi bir neçə siçan klikləməklə, cəmiləri çevirmək olar ki, sütun başlıqları sıra başlıqlarına çevrilsin və əksinə. Pivot cədvəlləri ilə çox şey edə bilərsiniz. Pivot cədvəllərinin bütün xüsusiyyətlərini sözlə təsvir etməyə çalışmaq əvəzinə, onu praktikada nümayiş etdirmək daha asandır...
PivotTables ilə təhlil etdiyiniz məlumatlar təsadüfi ola bilməz. Bu, bir növ siyahı kimi xam məlumat olmalıdır. Məsələn, bu, şirkətin son altı ayda etdiyi satışların siyahısı ola bilər.
Aşağıdakı şəkildə göstərilən məlumatlara baxın:
Qeyd edək ki, bu, artıq ümumiləşdirildiyi üçün xam məlumat deyil. B3 xanasında biz 30000 dollar görürük ki, bu, yəqin ki, Ceyms Kukun yanvar ayında etdiyi ümumi nəticədir. Bəs orijinal məlumatlar haradadır? 30000 dollar rəqəmi haradan gəldi? Bu aylıq məbləğin əldə edildiyi satışların orijinal siyahısı haradadır? Aydındır ki, kimsə son altı ayda bütün satış məlumatlarını təşkil etmək və çeşidləmək və bizim gördüyümüz yekunlar cədvəlinə çevirmək üçün əla iş görüb. Sizcə nə qədər vaxt apardı? Saat? Saat on?
Fakt budur ki, yuxarıdakı cədvəl pivot cədvəl deyil. O, başqa yerdə saxlanılan xam məlumatlardan əl ilə hazırlanmışdı və emal edilməsi ən azı bir neçə saat çəkdi. Məhz belə bir xülasə cədvəli bir neçə saniyə ərzində pivot cədvəllərindən istifadə etməklə yaradıla bilər. Gəlin anlayaq necə…
Orijinal satış siyahısına qayıtsaq, bu belə görünəcək:
Siz təəccüblənə bilərsiniz ki, bu ticarət siyahısından pivot cədvəllərin köməyi ilə və cəmi bir neçə saniyə ərzində Excel-də yuxarıda təhlil etdiyimiz aylıq satış hesabatı yarada bilərik. Bəli, biz bunu və daha çoxunu edə bilərik!
Pivot cədvəlini necə yaratmaq olar?
Əvvəlcə Excel vərəqində bəzi mənbə məlumatlarınız olduğundan əmin olun. Maliyyə əməliyyatlarının siyahısı baş verən ən tipikdir. Əslində, bu, hər hansı bir şeyin siyahısı ola bilər: işçinin əlaqə məlumatları, CD kolleksiyası və ya şirkətinizin yanacaq sərfiyyatı məlumatları.
Beləliklə, Excel-i işə salırıq ... və belə bir siyahı yükləyirik ...
Bu siyahını Excel-də açdıqdan sonra pivot cədvəli yaratmağa başlaya bilərik.
Bu siyahıdan istənilən xananı seçin:
Sonra tabda Taxmaq (Daxil et) əmrini seçin Pivot Cədvəl (Pivot cədvəli):
Dialoq qutusu görünəcək Pivot Cədvəl yaradın (Pivot cədvəlinin yaradılması) sizin üçün iki sual:
- Yeni pivot cədvəli yaratmaq üçün hansı məlumatlardan istifadə edilməlidir?
- Pivot masasını hara qoymaq lazımdır?
Əvvəlki addımda biz artıq siyahı xanalarından birini seçdiyimiz üçün pivot cədvəli yaratmaq üçün bütün siyahı avtomatik olaraq seçiləcək. Qeyd edək ki, biz fərqli diapazon, fərqli cədvəl və hətta Access və ya MS-SQL verilənlər bazası cədvəli kimi bəzi xarici məlumat mənbəyi seçə bilərik. Bundan əlavə, yeni pivot masasının harada yerləşdirilməsini seçməliyik: yeni vərəqdə və ya mövcud olanlardan birində. Bu nümunədə biz seçimi seçəcəyik - Yeni İş Vərəqi (yeni vərəqə):
Excel yeni vərəq yaradacaq və üzərinə boş pivot cədvəli yerləşdirəcək:
Pivot cədvəlindəki hər hansı bir xana üzərinə kliklədiyimiz anda başqa bir dialoq qutusu görünəcək: Pivot Cədvəl Sahə Siyahısı (Pivot cədvəl sahələri).
Dialoq qutusunun yuxarısındakı sahələrin siyahısı orijinal siyahıdakı bütün başlıqların siyahısıdır. Ekranın altındakı dörd boş sahə Pivot Cədvəldə məlumatları necə ümumiləşdirmək istədiyinizi söyləməyə imkan verir. Nə qədər ki, bu sahələr boşdur, cədvəldə də heç nə yoxdur. Etməli olduğumuz tək şey başlıqları yuxarı hissədən aşağıdakı boş sahələrə çəkməkdir. Eyni zamanda, təlimatlarımıza uyğun olaraq avtomatik olaraq pivot cədvəli yaradılır. Səhv etsək, başlıqları alt sahədən silə və ya onları əvəz etmək üçün başqalarını sürükləyə bilərik.
Sahə Dəyərlər, (Mənalar) yəqin ki, dördündən ən mühümüdür. Bu sahədə hansı başlığın yerləşdirildiyi hansı məlumatların ümumiləşdiriləcəyini müəyyən edir (cəm, orta, maksimum, minimum və s.) Bunlar demək olar ki, həmişə ədədi qiymətlərdir. Bu sahədə bir yer üçün əla namizəd başlıq altındakı məlumatlardır Məbləği Orijinal masamızın (qiyməti). Bu başlığı sahəyə sürükləyin Dəyərlər, (Dəyərlər):
Qeyd edək ki, başlıq Məbləği indi bir onay işarəsi ilə qeyd olunur və ərazidə Dəyərlər, (Dəyərlər) giriş göründü Məbləğin cəmi (Məbləğ sahəsi Məbləğ), sütun olduğunu ifadə edir Məbləği yekunlaşdırdı.
Pivot cədvəlinin özünə baxsaq, sütundakı bütün dəyərlərin cəmini görəcəyik Məbləği orijinal masa.
Beləliklə, ilk pivot cədvəlimiz yaradıldı! Rahat, lakin xüsusilə təsir edici deyil. Yəqin ki, məlumatlarımız haqqında indi əldə etdiyimizdən daha çox məlumat əldə etmək istəyirik.
Orijinal məlumatlara müraciət edək və bu məbləği bölmək üçün istifadə edilə bilən bir və ya bir neçə sütunu müəyyən etməyə çalışaq. Məsələn, biz pivot cədvəlimizi elə formalaşdıra bilərik ki, satışın ümumi məbləği hər bir satıcı üçün ayrıca hesablansın. Bunlar. sıralar şirkətdəki hər bir satıcının adı və ümumi satış məbləği ilə pivot cədvəlimizə əlavə olunacaq. Bu nəticəyə nail olmaq üçün sadəcə başlığı sürükləyin satıcı (satış nümayəndəsi) rayona Sıra Etiketləri (Simlər):
Daha maraqlı olur! Pivot Cədvəlimiz formalaşmağa başlayır...
Faydalarına baxın? Bir neçə kliklə biz əl ilə yaradılması çox uzun vaxt aparan bir cədvəl yaratdıq.
Başqa nə edə bilərik? Yaxşı, müəyyən mənada pivot masamız hazırdır. Orijinal məlumatların faydalı xülasəsini yaratdıq. Vacib məlumat artıq alınıb! Bu məqalənin qalan hissəsində daha mürəkkəb Pivot Cədvəllər yaratmağın bəzi yollarına baxacağıq, həmçinin onları necə fərdiləşdirməyi öyrənəcəyik.
Pivot Cədvəl Quraşdırma
Əvvəlcə iki ölçülü pivot cədvəli yarada bilərik. Bunu sütun başlığından istifadə edərək edək Ödəniş üsulu (Ödəniş üsulu). Sadəcə başlığı sürükləyin Ödəniş üsulu əraziyə Sütun Etiketləri (Sütunlar):
Nəticəni alırıq:
Çox gözəl görünür!
İndi üç ölçülü bir cədvəl hazırlayaq. Belə bir masa necə görünəcək? Görək…
Başlığı sürükləyin Paketi (Kompleks) əraziyə hesabat filtrləri (Filtrlər):
Onun harada olduğuna diqqət yetirin...
Bu, bizə hesabatı "Hansı tətil kompleksi üçün ödənilib" əsasında filtrasiya etmək imkanı verir. Məsələn, bütün komplekslər üçün satıcılar və ödəniş üsulları üzrə bölgü görə bilərik və ya bir neçə siçan klikləməklə, pivot cədvəlinin görünüşünü dəyişdirə və yalnız kompleksi sifariş edənlər üçün eyni bölgüyü göstərə bilərik. Günəşpərəstlər.
Beləliklə, bunu düzgün başa düşsəniz, pivot cədvəlimizi üç ölçülü adlandırmaq olar. Quraşdırmağa davam edək...
Birdən məlum olarsa ki, pivot cədvəlində yalnız çek və kredit kartı ilə ödəniş (yəni nağdsız ödəniş) göstərilməlidir, onda başlığın ekranını söndürə bilərik. Pul (Nağd pul). Bunun üçün yanında Sütun Etiketləri aşağı oxu klikləyin və açılan menyuda qutunun işarəsini çıxarın Pul:
İndi pivot masamızın necə göründüyünü görək. Gördüyünüz kimi, sütun Pul ondan yoxa çıxdı.
Excel-də pivot cədvəllərinin formatlanması
PivotTables açıq-aydın çox güclü bir vasitədir, lakin indiyə qədər nəticələr bir az sadə və darıxdırıcı görünür. Məsələn, topladığımız rəqəmlər dollar məbləğlərinə bənzəmir – onlar sadəcə rəqəmlərdir. Gəlin bunu düzəldək.
Belə bir vəziyyətdə vərdiş etdiyiniz şeyi etmək və sadəcə olaraq bütün cədvəli (və ya bütün vərəqi) seçmək və istədiyiniz formatı təyin etmək üçün alətlər panelindəki standart nömrə formatlaşdırma düymələrindən istifadə etmək cazibədardır. Bu yanaşma ilə bağlı problem ondan ibarətdir ki, əgər siz gələcəkdə pivot cədvəlinin strukturunu dəyişdirsəniz (bu 99% şansla baş verir), formatlaşdırma itiriləcək. Bizə lazım olan, onu (demək olar ki) daimi etmək üçün bir yoldur.
Əvvəlcə girişi tapaq Məbləğin cəmi in Dəyərlər, (Dəyərlər) və üzərinə klikləyin. Görünən menyuda elementi seçin Dəyər Sahəsi Parametrləri (Dəyər sahəsi seçimləri):
Dialoq qutusu görünəcək Dəyər Sahəsi Parametrləri (Dəyər sahəsi seçimləri).
mətbuat Sayı formatı (Nömrə Format), bir dialoq qutusu açılacaq. Hüceyrələri formatlaşdırın (hüceyrə formatı):
Siyahıdan kateqoriya (Nömrə formatları) seçin Mühasibat (Maliyyə) və onluq yerlərin sayını sıfıra təyin edin. İndi bir neçə dəfə basın OKəsas masamıza qayıtmaq üçün.
Gördüyünüz kimi, rəqəmlər dollar məbləğləri kimi formatlanıb.
Formatlaşdırma ilə məşğul olduğumuz müddətdə gəlin bütün Pivot Cədvəl üçün formatı quraşdıraq. Bunu etmək üçün bir neçə üsul var. Biz daha sadə olanından istifadə edirik...
Basın PivotTable Alətləri: Dizayn (Pivot Cədvəllərlə İş: Konstruktor):
Sonra, bölmənin aşağı sağ küncündəki oxu klikləməklə menyunu genişləndirin Pivot Cədvəl Üslubları (Pivot Cədvəl Üslubları) daxili üslubların geniş kolleksiyasına baxmaq üçün:
İstənilən uyğun üslubu seçin və pivot cədvəlinizdə nəticəyə baxın:
Excel-də digər Pivot Cədvəl Parametrləri
Bəzən məlumatları tarixlərə görə filtrləməlisiniz. Məsələn, bizim ticarət siyahımızda çoxlu, çoxlu tarixlər var. Excel məlumatları gün, ay, il və s. üzrə qruplaşdırmaq üçün alət təqdim edir. Bunun necə edildiyini görək.
Əvvəlcə girişi çıxarın. Ödəniş üsulu bölgədən Sütun Etiketləri (Sütunlar). Bunu etmək üçün onu başlıqlar siyahısına geri çəkin və onun yerinə başlığı köçürün Rezervasiya Tarixi (sifariş tarixi):
Gördüyünüz kimi, bu, pivot cədvəlimizi müvəqqəti olaraq yararsız hala gətirdi. Excel ticarətin edildiyi hər tarix üçün ayrıca sütun yaratdı. Nəticədə çox geniş bir masa əldə etdik!
Bunu düzəltmək üçün istənilən tarixə sağ klikləyin və kontekst menyusundan seçin QRUP (Qrup):
Qruplaşdırma dialoq qutusu görünəcək. Biz seçirik Ayların (Aylar) və klikləyin OK:
Voila! Bu cədvəl daha faydalıdır:
Yeri gəlmişkən, bu cədvəl məqalənin əvvəlində göstərilənlə demək olar ki, eynidir, burada satışların yekunları əl ilə tərtib edilmişdir.
Bilməli olduğunuz başqa bir çox vacib məqam var! Siz bir deyil, bir neçə səviyyəli sətir (və ya sütun) başlıqları yarada bilərsiniz:
... və bu belə görünəcək ...
Eyni şey sütun başlıqları (və ya hətta filtrlər) ilə edilə bilər.
Cədvəlin ilkin formasına qayıdaq və cəmin əvəzinə ortaların necə göstəriləcəyini görək.
Başlamaq üçün üzərinə klikləyin Məbləğin cəmi və görünən menyudan seçin Dəyər Sahəsi Parametrləri (Dəyər sahəsi seçimləri):
Siyahı Dəyər sahəsini ümumiləşdirin (Əməliyyat) dialoq qutusunda Dəyər Sahəsi Parametrləri (Dəyər sahəsi seçimləri) seçin orta (Orta):
Eyni zamanda, burada olduğumuz müddətdə gəlin dəyişək XüsusiAd (Fərdi ad) ilə Orta məbləğ (Məbləğ sahəsi Məbləğ) daha qısa bir şeyə. Bu sahəyə bənzər bir şey daxil edin Ort:
mətbuat OK və nə baş verdiyinə baxın. Qeyd edək ki, bütün dəyərlər ümumilərdən ortalara dəyişib və cədvəl başlığı (yuxarı sol xanada) olaraq dəyişib. Ort:
İstəsəniz, bir pivot cədvəldə yerləşdirilmiş məbləği, orta və rəqəmi (satışları) dərhal əldə edə bilərsiniz.
Boş pivot cədvəlindən başlayaraq, bunu necə etmək barədə addım-addım təlimat budur:
- Başlığı sürükləyin satıcı (satış nümayəndəsi) rayona Sütun Etiketləri (Sütunlar).
- Başlığı üç dəfə dartın Məbləği (Xərc) əraziyə Dəyərlər, (Dəyərlər).
- Birinci sahə üçün Məbləği başlığını dəyişdirin Ümumi (Məbləğ) və bu sahədə rəqəm formatı belədir Mühasibat (Maliyyə). Onluq yerlərin sayı sıfırdır.
- İkinci sahə Məbləği ad Ortae, bunun üçün əməliyyat təyin edin orta (Orta) və bu sahədə rəqəm formatı da olaraq dəyişir Mühasibat (Maliyyə) sıfır onluq yerləri ilə.
- Üçüncü sahə üçün Məbləği başlıq təyin edin Saymaq və onun üçün əməliyyat - Saymaq (Kəmiyyət)
- Ci Sütun Etiketləri (Sütunlar) sahəsi avtomatik olaraq yaradılmışdır Σ Dəyərlər (Σ Dəyərlər) – onu sahəyə sürükləyin Sıra Etiketləri (sətirlər)
Sonumuz budur:
Ümumi məbləğ, orta dəyər və satışların sayı – hamısı bir pivot cədvəldə!
Nəticə
Microsoft Excel-də pivot cədvəlləri bir çox funksiya və parametrləri ehtiva edir. Belə kiçik bir məqalədə onların hamısını əhatə etməyə yaxın deyillər. Pivot masaların bütün imkanlarını tam təsvir etmək üçün kiçik bir kitab və ya böyük bir veb sayt lazımdır. Cəsarətli və maraqlanan oxucular pivot cədvəlləri araşdırmağa davam edə bilərlər. Bunu etmək üçün pivot cədvəlinin demək olar ki, hər hansı bir elementinə sağ vurun və hansı funksiyaların və parametrlərin açıldığına baxın. Lentdə iki nişanı tapa bilərsiniz: Pivot Cədvəl Alətləri: Seçimlər (analiz) və Layihə (konstruktor). Səhv etməkdən qorxmayın, hər zaman Pivot Cədvəli silib yenidən başlaya bilərsiniz. Uzun müddət DOS və Lotus 1-2-3 istifadəçilərinin heç vaxt sahib olmadığı bir fürsətə sahibsiniz.