Pivot cədvəlinin məlumatlarını əldə etmək funksiyası necə işləyir

Əvvəllər mən artıq bir hüceyrəyə istinad edərkən ondan danışdım dönmə cədvəli adi istinad əvəzinə GET.PIVOTO.TABLE.DATA funksiyası qaytarılır (bax). Əgər siz maraqlanırsınızsa Necə Bu narahatlığı aradan qaldırmaq üçün qeyd olunan qeydə müraciət etməyi məsləhət görürəm. Əgər siz maraqlanırsınızsa, Niyə bu baş verir və həmçinin GET.DATA.PIVOTO.TABLE funksiyasının müsbət tərəfləri nələrdir, onda mən Jelen, Aleksandrın kitabından bir fraqment təqdim edirəm. (15-ci fəsil). Baxılan texnika pivot masaların istifadəçiləri üçün baş ağrısına səbəb olan bir çox problemin öhdəsindən gəlməyə imkan verəcək, xüsusən:

Pivot Cədvəli təzələmək əvvəllər tətbiq edilmiş formatı silir. Rəqəmsal formatlar itir. Sütunların eninin tənzimlənməsinin nəticələri yox olur.
Mövcud deyil asan yol asimmetrik pivot cədvəlinin yaradılması. Yeganə seçim adlandırılmış dəstlərdən istifadə etməkdir, lakin bu üsul yalnız Data Modeli Pivot Cədvəllərindən istifadə edənlər üçün əlçatandır, adi Pivot Cədvəlləri deyil.
Excel şablonları yadda saxlaya bilmir. Pivot Cədvəlləri təkrar-təkrar yaratmaq lazımdırsa, siz yenidən qruplaşdırmalı, hesablanmış sahələri və üzvləri tətbiq etməli və bir sıra digər oxşar tapşırıqları yerinə yetirməli olacaqsınız.

Əslində burada təsvir edilən hər şey yeni deyil. Üstəlik, oxşar üsullar Excel 2002-dən bəri istifadə olunur. Lakin mənim istifadəçilərlə ünsiyyətim göstərir ki, 1%-dən az adam onlarla tanışdır. İstifadəçilərin yeganə sualı qəribə GET.PIVOTO.TABLE.DATA funksiyasını necə söndürməkdir. heyif...

Qeydi və ya formatda yükləyin, nümunələri formatda

Yaxşı, sıra ilə başlayaq.

Problemli GET.DATA.PIVOTO.TABLE funksiyasından necə imtina etmək olar

GET.DATA.Pivot.TABLE funksiyası uzun müddətdir ki, bir çox istifadəçilər üçün başağrısı olub. Excel 2002-də heç bir xəbərdarlıq edilmədən birdən-birə Pivot Cədvəllərin davranışı dəyişdi. Məlumatlarına istinad edən Pivot Cədvəldən kənar düsturlar yaratmağa başladıqdan sonra bu xüsusiyyət heç bir yerdən yaranır.

Tutaq ki, Şəkildə göstərilən pivot cədvəlində. 1, 2015 və 2014-cü illərin məlumatlarını müqayisə etməlisiniz.

düyü. 1. İlkin pivot cədvəli

D3 xanasına "% artım" başlığını əlavə edin.
Formatı C3 xanasından D3 xanasına köçürün.
D4 xanasına bərabər işarəni daxil edin.
C4 xanasına klikləyin.
Bölmə əməliyyatını göstərmək üçün / (slash) işarəsini daxil edin.
B4 xanasına klikləyin.
-1 yazın və düymələr birləşməsini basın eyni kamerada qalmaq. Nəticəni faizlə formatlayın. Qərb bölgəsinin gəlirlərində 43,8% azalma olduğunu görəcəksiniz (Şəkil 2). Çox yaxşı nəticələr deyil.
İlk düsturunuzu daxil etməyi bitirdikdən sonra D4 xanasını seçin.
Hüceyrənin aşağı sağ küncündə yerləşən kiçik kvadrata iki dəfə klikləyin. Bu qutu bütün hesabat sütununu doldurmaq üçün düsturun surətini çıxarmaq üçün istifadə edilə bilən doldurma sapını təmsil edir.

Formulun surəti tamamlandıqdan sonra ekrana baxaraq, bir şeyin səhv olduğunu başa düşəcəksiniz - hər bir bölgə il ərzində 43,8% azalma göstərdi (şəkil 3).

düyü. 3. Düsturu sütunun bütün xanalarına köçürdükdən sonra hər bölgənin 43,8% azalma göstərdiyini görəcəksiniz.

Bu, çətin ki, olur həqiqi həyat. Hər hansı bir istifadəçi yuxarıda göstərilənləri etdikdən sonra sizə xəbər verəcəkdir Excel hərəkətləri=C4/B4–1 düsturu yaradacaq. D4 xanasına qayıdın və düstur çubuğuna diqqət yetirin (Şəkil 4). Bu sadəcə lənətə gəlmiş bir şeydir! Sadə =C4/B4–1 düsturu artıq mövcud deyil. Əvəzində proqram kompleks strukturu GET.PIVOTAL.TABLE.DATA funksiyası ilə əvəz edir. Niyə bu düstur D4 xanasında düzgün nəticələr verir, lakin aşağıdakı xanalara kopyalandıqda işləmir?

İstənilən istifadəçinin baş verənlərə ilk reaksiyası belə olacaq: "Mənim hesabatımı qarışdıran bu qəribə GET.DATA.PIVOTO.TABLE quruluşu nədir?" Əksər istifadəçilər bu xüsusiyyətdən dərhal xilas olmaq istəyəcəklər. Bəziləri sual verəcək: "Microsoft bizə bu xüsusiyyəti niyə verdi?"

İçində belə bir şey yox idi excel dəfə 2000. Mən müntəzəm olaraq GET.DATA.PIVOTOMATIC.TABLE-ni görməyə başlayanda ona nifrət etdim. Seminarların birində kimsə məndən işin xeyrinə necə istifadə oluna biləcəyini soruşduqda, mat qaldım. Heç vaxt özümə belə sual verməmişəm! Mənim fikrimcə və əksər Excel istifadəçilərinin fikrincə, GET.DATA.PIVOTAL.TABLE funksiyası yaxşılıq qüvvələri ilə heç bir əlaqəsi olmayan şərin məhsulu idi. Xoşbəxtlikdən, bu funksiyanı söndürməyin iki yolu var.

Düstur daxil etməklə GET.PIVOTO.TABLE.DATA funksiyasının bloklanması. GET.DATA.PIVOTO.TABLE funksiyasının görünməsinin qarşısını almağın asan yolu var. Bunun üçün siçan və ya kursor düymələrindən istifadə etmədən formul yaratmalısınız. Sadəcə bu addımları izləyin.

D4 xanasına keçin və = (bərabər işarə) yazın.
C4 daxil edin.
/ (bölmə üçün kəsik) yazın.
B4 daxil edin.
-1 daxil edin.
basın Daxil edin.

İndi bir adət yaratdınız Excel düsturu, aşağıdakı sütunun xanalarına kopyalana bilən və düzgün nəticələr əldə edə biləcəyiniz (şək. 5). Gördüyünüz kimi, siz Pivot Cədvəlin daxilindəki məlumatlara istinad edən Pivot Cədvəldən kənar sahələrdə düsturlar yarada bilərsiniz. Bunun mümkün olduğuna inanmayanlar isə təsvir olunan hərəkətləri öz başlarına yerinə yetirsinlər.

düyü. 5. Klaviaturadan =С4/В4–1 yazın və düstur lazım olduğu kimi işləyəcək

Bəzi istifadəçilər düsturların daxil edilməsinin adi qaydasının pozulmasından narahat olacaqlar. Bundan əlavə, təklif olunan seçim daha zəhmətlidir. Əgər siz bu istifadəçilərdənsinizsə, ikinci yol sizin üçündür...

GET.PIVOTO.TABLE.DATA funksiyasını söndürün. Siz GET.PIVOTO.TABLE.DATA funksiyasını həmişəlik söndürə bilərsiniz. Menyu lentinə klikləyin Fayl → Seçimlər. Açılan pəncərədə Seçimlərexcel nişana keçin Formulalar və seçimdən işarəni çıxarın funksiyasından istifadə edinPivotTable bağlantıları üçün GetPivotData. basın Tamam.

Alternativ variant. Pivot cədvəlinə və görünən kontekst tabına klikləyin Təhlil düyməsinin yanındakı açılan menyuya klikləyin Seçimlər. yanındakı qutunun işarəsini silin GetPivotData yaradın(Şəkil 7). Qeyd xanası standart olaraq aktivdir.

Niyə Microsoft bizə GET.PIVOTO.TABLE.DATA funksiyasını təklif etdi? Bu xüsusiyyət belə dəhşətlidirsə, niyə Microsoft-un tərtibatçıları onu standart olaraq aktivləşdirdilər? Nə üçün onlar Excel-in daha yeni versiyalarında bu funksiyaya dəstək verməklə maraqlanırlar? İstifadəçi əhval-ruhiyyəsindən xəbərdardırlarmı? Və ən maraqlısına keçirik ...

Pivot cədvəllərini təkmilləşdirmək üçün GET.PIVOTO.TABLE.DATA funksiyasından istifadə

Pivot masalar bəşəriyyətin böyük ixtirasıdır. Təkmil filtrlərə, BSUM funksiyasına və məlumat cədvəllərinə ehtiyacı aradan qaldıran Pivot Cədvəl yalnız bir neçə siçan klikləməklə yaradılır. Pivot Cədvəlləri ilə siz çoxlu məlumatlara əsaslanan bir səhifəlik hesabatlar yarada bilərsiniz. Bu üstünlüklər fonunda, ifadəsiz formatlaşdırma və əlavə fərdiləşdirmə üçün Pivot Cədvəlləri dəyərlərə çevirmək ehtiyacı olan Pivot Cədvəllərin bəzi çatışmazlıqları arxa plana keçir. Əncirdə. Şəkil 8 tipik pivot cədvəlinin yaradılması prosesini göstərir. Baxılan halda hər şey ilkin məlumatlardan başlayır. Biz pivot cədvəli yaradırıq və onu fərdiləşdirmək və təkmilləşdirmək üçün bütün mümkün üsullardan istifadə edirik. Bəzən biz Pivot Cədvəli dəyərlərə çeviririk və bəzi son formatlaşdırma edirik.

Rob Colley (Microsoft inkişaf etdiricisi) tərəfindən təqdim edilən və aşağıda müzakirə edilən yeni PivotTable texnikası yuxarıda təsvir edilən prosesin təkmilləşdirilməsidir. Bu halda ilk növbədə primitiv pivot cədvəli yaradılır. Bu cədvəlin formatlanmasına ehtiyac yoxdur. Sonra, yekun hesabatı ehtiva edən gözəl formatlaşdırılmış qabıq yaratmaq üçün bir addımlı, nisbətən vaxt aparan proses izlənilir. Bundan sonra qabıqdakı hesabatı verilənlərlə tez doldurmaq üçün GET.DATA.PIVOTOMATIC.TABLE funksiyasından istifadə olunur. Yeni məlumatları aldıqdan sonra siz onu vərəqə yerləşdirə, primitiv pivot cədvəlini yeniləyə və qabıqda olan hesabatı çap edə bilərsiniz (şək. 9). Bu texnikanın bir sıra danılmaz üstünlükləri var. Məsələn, hesabat yaradıldıqdan dərhal sonra onu formatlaşdırmaqdan narahat olmaq lazım deyil. Pivot cədvəllərinin yaradılması prosesi demək olar ki, tamamilə avtomatlaşdırılır.

Aşağıdakı bölmələr sizə ötən aylar üçün faktiki məlumatları və gələcək aylar üçün planları əks etdirən dinamik hesabatın necə yaradılacağını göstərir.

Primitiv pivot cədvəlinin yaradılması.İlkin məlumatlar (şək. 10) şirkətin filiallarının yerləşdiyi hər bir region üzrə planlı və faktiki göstəricilər haqqında məlumatları ehtiva edən əməliyyatlar şəklində təqdim olunur. Planlaşdırılan göstəricilər aylar səviyyəsində, faktiki isə ayrı-ayrı günlər səviyyəsində təfərrüatlıdır. Planlı göstəricilər gələn il üçün, faktiki isə ötən aylar üçün yaradılır. Hesabat hər ay yenilənəcəyi üçün Pivot Cədvəl məlumat mənbəyi aşağıya yeni məlumatlar əlavə olunduqca ölçüdə böyüyərsə, bu proses xeyli sadələşir. Köhnə versiyalarda Excel yaradılması belə bir məlumat mənbəyi OFFSET funksiyasından istifadə edərək adlandırılmış dinamik diapazondan istifadə etməklə həyata keçirilmişdir (ətraflı məlumat üçün bax). Excel 2013-də işləyərkən məlumat xanalarından birini seçin və Ctrl+T (cədvəl yaradın) düymələrini basın. Yeni sətirlər və sütunlar əlavə olunduqca avtomatik olaraq genişlənən adlandırılmış verilənlər toplusu görünəcək.

İndi pivot cədvəli yaradaq. GET.PIVOT.TABLE.funksiyası olduqca güclüdür, lakin o, yalnız faktiki pivot cədvəlində görünən dəyərləri qaytara bilər. Bu funksiya Pivot Cədvəldə olmayan elementləri hesablamaq üçün keşi skan edə bilmir.

Pivot cədvəli yaradın:

Komanda seçin Daxil et → dönmə cədvəli və sonra dialoq qutusunda Pivot Cədvəl yaradın basın tamam.
Pivot Cədvəl Sahələri siyahısında sahəni seçin Tarix. Pivot cədvəlinin sol tərəfində tarixlərin siyahısı görünəcək (şək. 11).
İstənilən tarix xanasını seçin, məsələn, A4. Kontekst nişanında Təhlil, kontekstli nişanlar dəstində yerləşir Pivot masalarla işləmək, düyməsini basın Sahəyə görə qruplaşdırın(ətraflı məlumat üçün bax). Dialoq qutusunda Qruplaşdırma seçimi seçin aylar(şək. 12). basın tamam. Ayların adları pivot cədvəlinin sol tərəfində görünəcək (şək. 13).
Qutunu sürükləyin Tarix Pivot Cədvəl Sütunları sahəsində.
Qutunu sürükləyin indeks Pivot Table Sahə Siyahısının sütun sahəsinə.
Sahəni seçin Region pivot cədvəlinin sol sütununda göstəriləcək.
Sahəni seçin Gəlir Pivot Table Dəyərləri sahəsində görünəcək.

düyü. 11. Sahəyə görə qruplaşdırmaqla başlayın Tarix

Bu mərhələdə bizim pivot masamız olduqca primitiv görünür (şək. 14). Mən həqiqətən etiketləri sevmirəm Sıra adları Və Sütun adları. Cəmilərin göstərilməsi uyğun deyil Yanvar Planı Və Jan Fakt D sütununda və s. Amma narahat olma görünüş bu pivot masa, çünki sizdən başqa heç kim onu görməyəcək. Bu nöqtədən başlayaraq, məlumat mənbəyi yeni yaratdığımız pivot cədvəli olacaq bir hesabat qabığı yaradacağıq.

Hesabat sarğısının yaradılması. Yapışdırın iş dəftəri boş vərəq. PivotTable alətlərini bir anlığa kənara qoyub adi Excel alətlərinə keçək. Bizim vəzifəmiz, menecerə göstərməkdən utanmayan gözəl bir hesabat yaratmaq üçün düsturlardan və formatlaşdırmadan istifadə etməkdir.

Aşağıdakı addımları yerinə yetirin (şək. 15).

A1 xanasına hesabatın adını daxil edin - Regionlar üzrə planlı və faktiki göstəricilər.
Taba keçin ev, düyməsini basın Hüceyrə üslubları format seçin Başlıq 1.
A2 xanasına = AY (BUGÜN (); 0) düsturu daxil edin. Bu funksiya son günü qaytarır cari ay. Məsələn, əgər siz bunu 14 avqust 2014-cü ildə oxuyursunuzsa, A2 xanası 31 avqust 2014-cü il tarixini oxuyacaq.
A2 xanasını seçin. Dialoq qutusunu göstərmək üçün Ctrl+1 düymələr kombinasiyasını basın Hüceyrə formatı. Nişanda Nömrə elementə klikləyin Bütün formatlar. Formaya xüsusi nömrə formatını daxil edin "Aydan" MMMM "planlı göstəricilər"(Şəkil 16). Nəticədə hesablanmış tarix mətn kimi görünəcək.
A5 xanasına başlıq daxil edin Region.
A sütununda qalan xanalara bölgə başlıqlarını daxil edin. Bölgə başlıqları Pivot Cədvəldəki bölgə adlarına uyğun olmalıdır.
Lazım gələrsə, şöbənin cəmi üçün sütuna etiketlər əlavə edin.
Hesabatın altına bir xətt əlavə edin Şirkət üçün cəmi.
B4 xanasına =TARİX(İL($A$2),SÜTUN(A1),1) düsturu daxil edin. Bu düstur cari ilin bütün 12 ayının ilk günləri olan 01/01/2014, 02/01/2104 və s. tarixləri qaytarır.
B4 xanasını seçin. Pəncərəni açmaq üçün Ctrl+1 düymələr kombinasiyasını basın Hüceyrə formatı. Nişanda Nömrə Fəsildə Bütün formatlar fərdi nömrə formatını daxil edin MMM. Bu format ayın üç hərfli adını göstərir. Mətni xananın sağ kənarına düzün.
B4 xanasının məzmununu C4:M4 diapazonuna köçürün. Pivot cədvəlinin yuxarısında ayların adları ilə bir sıra göstəriləcək.
B5 xanasına =ƏGƏR(AY(B4)) düsturu daxil edin<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt və indiki və gələcək üçün - Plan.
N5 xanasına başlıq əlavə edin Nəticə. O4 hüceyrəsində - Nəticə, O5 - Plan, Р5 - % sapma.
Şöbə cəmini, şirkətin ümumi sətirini, ümumi ümumi sütununu və % fərq sütununu hesablayarkən istifadə olunan adi Excel düsturlarını daxil edin:
1. B8 xanasına = SUM (B6: B7) düsturu daxil edin və onu cərgənin digər xanalarına köçürün;
2. N6 xanasına = SUM (B6: M6) düsturu daxil edin və onu sütunun digər xanalarına köçürün;
3. P6 xanasına =IFERROR((N6/O10)-1;0) düsturunu daxil edin və onu sütunun digər xanalarına köçürün;
4. B13 xanasına = SUM (B10: B12) düsturu daxil edin və onu cərgənin digər xanalarına köçürün;
5. B17 xanasına = SUM (B15: B16) düsturu daxil edin və onu cərgənin digər xanalarına köçürün;
6. B19 xanasına =SUM(B6:B18)/2 düsturunu daxil edin və onu cərgənin digər xanalarına köçürün.
Başlıq 4 üslubunu A sütununda və 4 və 5-ci sətirlərdə başlıqlara tətbiq edin.
B6:O19 xana diapazonu üçün ###0 nömrə formatını seçin.
P sütunundakı xanalar üçün rəqəm formatını 0,0% seçin.

Beləliklə, Şəkildə göstərilən hesabat sarğısının yaradılmasını tamamladıq. 15. Bu hesabata bütün tələb olunan formatlar daxildir. Aşağıdakı bölmə hesabatı tamamlamaq üçün GET.PIVOTAL.TABLE DATA funksiyasından necə istifadə olunacağını nümayiş etdirir.

düyü. 15. Düsturlar əlavə etməzdən əvvəl sarğı haqqında hesabat verin

Hesabat qabığını verilənlərlə doldurmaq üçün GET.PIVOTO.DATA.funksiyasından istifadə edin. Bundan sonra siz GET.PIVOTO.TABLE.DATA funksiyasından istifadə etməyin bütün üstünlüklərini yaşaya biləcəksiniz. Bu funksiyanı aktivləşdirən qeyd qutusunu silmisinizsə, müvafiq parametrə qayıdın və qeyd xanasını qaytarın (şək. 6 və ya 7-dəki təsvirə baxın).

Hesabat qabığının B6 xanasını seçin. Bu xana şimal-şərq bölgəsinə və yanvar ayının faktiki rəqəmlərinə uyğundur.

Düsturu daxil etməyə başlamaq üçün = (bərabər işarə) yazın.
Pivot cədvəlindən vərəqə keçin və şimal-şərq bölgəsinə və yanvar ayı üçün faktiki rəqəmlərə uyğun gələn hüceyrəni basın - C12 (şəkil 17).
düyməsini basın Daxil edin formulun daxil edilməsini başa çatdırmaq və hesabat qabığına qayıtmaq üçün. Nəticədə, Excel B6 xanasına GET.PICTABLE.TABLE DATA funksiyasını əlavə edəcək. Hüceyrə 277,435 dollar göstərəcək.

Bu rəqəmi yadda saxlayın, çünki onu sonra redaktə edəcəyiniz düsturun nəticələri ilə müqayisə edərkən sizə lazım olacaq. Proqram tərəfindən yaradılan düstur aşağıdakı formaya malikdir: =GET.DATA.PIVOTAL.TABLE(" Gəlir " ;'Şəkil 11-14′!$A$3; "Region" ; "Şimal-Şərq" ; "Tarix" ; 1; "Göstərici"; "Fakt"). Əgər indiyə qədər GET.PIVOTAL.TABLE funksiyasına məhəl qoymursunuzsa, onunla daha yaxından tanış olmağın vaxtıdır. Əncirdə. 18 bu düstur işarə ilə birlikdə redaktə rejimində göstərilir.

Funksiya arqumentləri:

Data_field. Pivot Cədvəl Dəyərləri sahəsindən sahə. Diqqət edin: bu halda sahə istifadə olunur Gəlir, amma yox Gəlir sahəsindəki məbləğ.
Pivot Cədvəl. Bu seçimlə Microsoft sizdən "Hansı Pivot Cədvəldən istifadə etmək istəyirsiniz?" Pivot cədvəl xanalarından birini təyin etmək kifayətdir. Giriş 'Şəkil. 11-14'!$A$3 məlumatların daxil edildiyi Pivot Cədvəldəki ilk xanaya aiddir. Bizim vəziyyətimizdə pivot cədvəli ilə əlaqəli hər hansı bir xana təyin edə biləcəyiniz üçün arqumenti dəyişməz qoyun. $A$3 mobil ünvanı hər şəkildə uyğun gəlir.
Sahə 1; element 1. Avtomatik yaradılan düsturda sahə adı kimi ad seçilir Region, və sahə dəyəri kimi - Şimal-şərq. GET.PIVOT.TABLE DATA funksiyası ilə işləyərkən yaranan problemlərin səbəbi məhz buradadır. Avtomatik seçilmiş dəyərlər sərt kodlaşdırıldığı üçün kopyalana bilməz. Buna görə də, düsturları bütün hesabat sahəsinə köçürsəniz, onları əl ilə dəyişməli olacaqsınız. Şimal-şərq üçün xana istinadını $A6 ilə əvəz edin. A sütununun adının qarşısında dollar işarəsini göstərməklə, düsturu sütunun xanalarına köçürərkən istinadın sətir hissəsinin dəyişdirilə biləcəyini müəyyən edirsiniz.
Sahə 2; element 2. Bu arqumentlər cütü sahəni təyin edir Tarix 1 dəyəri ilə. Orijinal pivot cədvəli aya görə qruplaşdırılıbsa, ay sahəsi orijinal sahə adını saxlayır Tarix. Ayın rəqəmli dəyəri yanvar ayına uyğun gələn 1-dir. Onlarla, hətta yüzlərlə hesabat xanasında qurulmuş nəhəng düsturlar yaratarkən belə bir dəyərdən istifadə etmək çətin ki, məqsədəuyğundur. Sahə dəyərlərini hesablayan düsturdan istifadə etmək daha yaxşıdır Tarix, B4 xanasındakı düstur kimi. 1 əvəzinə, bu halda, AY (B $ 4) düsturundan istifadə edə bilərsiniz. 4-dən əvvəl dollar işarəsi düsturun sahəyə dəyərlər təyin edə biləcəyini göstərir Tarix düstur cərgə xanalarına kopyalandığı üçün digər aylara əsaslanır.
Sahə 3; element 3. Bu halda sahənin adı avtomatik olaraq təyin olunur indeks və sahə dəyəri Fakt. Bu dəyərlər yanvar ayı üçün düzgündür, lakin sonrakı aylar üçün sahə dəyəri Plan olaraq dəyişdirilməlidir. Sabit kodlu sahə dəyərini dəyişdirin Fakt B$5 ilə əlaqələndirmək.
Sahə 4; element 4. Bu arqumentlər ona görə istifadə edilmir sahələr bitdi.

Yeni formula Şəkildə göstərilmişdir. 19. Bir dəqiqə ərzində tək bir dəyərlə işləmək üçün nəzərdə tutulmuş sərt kodlu düstur əvəzinə verilənlər bazasındakı bütün hüceyrələrə kopyalana bilən çevik bir düstur yaradıldı. düyməsini basın Daxil edin, və düsturu redaktə etməzdən əvvəl eyni nəticəni alacaqsınız. Redaktə edilmiş düstur belə olur: =GET.PIVOTOMAT.TABLE DATA("Gəlir" ;'Şəkil 11-14′!$A$3; "Region" ;$A6; "Tarix" ;MONTH(B$4); " Göstərici " ; B$5)

düyü. 19. Redaktə başa çatdıqdan sonra GET.DATA.PIVOTO.TABLE düsturu diapazonun bütün xanalarına köçürmək üçün uyğundur.

Düsturu nəticələrin hesablandığı B:M sütunlarının bütün boş xanalarına köçürün. İndi hesabatda faktiki rəqəmli dəyərlər var, siz sütun genişliklərində bəzi son düzəlişlər edə bilərsiniz.

Növbəti mərhələdə biz planlaşdırılmış ümumi göstəriciləri hesablamaq üçün GET.PIVOTO.TABLE.DATA düsturunu quracağıq. Sadəcə olaraq düsturu O6 xanasına köçürməklə #REF! Bu səhvin səbəbi sözün olmasıdır Nəticə O4 xanasındakı ayın adı deyil. GET.PIVOTO.TABLE.DATA funksiyasının düzgün işləməsini təmin etmək üçün tələb olunan dəyər pivot cədvəlində olmalıdır. Ancaq orijinal pivot cədvəlində sahə olduğundan indeks sütun sahəsində ikinci sahə, məlumat sütunudur Nəticəni planlaşdırınəslində yoxdur. Qutunu köçürün indeks belə ki, o, sütun sahəsində birinci olur (şək. 20).

düyü. 20. Sütunlar sahəsində sahələrin tərtibatını elə tənzimləyin ki, sütun görünsün Nəticəni planlaşdırın

Əncir ilə müqayisə edin. 14. Orada, KOLUMN sahəsində, sahə birinci getdi Tarix, bu, sütunların əvvəlcə tarixə görə, hər ayda isə plan / fakta görə qruplaşdırılmasına səbəb oldu. İndi ilk sahə Göstəricidir və xülasədə sütunlar birinci yerdədir Plan, daxili olaraq aya görə çeşidlənir, ardınca bütün sütunlar Fakt.

Hesabatın qabıq vərəqinə qayıdaraq, O6 xanasında durun, = (=) yazın və Şimal-şərq bölgəsi üçün planlaşdırılan cəmlərə uyğun gələn pivot cədvəlinin vərəqindəki N12 xanasına baxın. basın Daxil edin. Nəticə = GET. DATA. OF. A. PIVOT. TABLE ("Gəlir"; 'Şəkil 11-14′! $ A $ 3; "Region"; "Şimal-Şərq"; "Göstərici"; " Plan"). Redaktə edin: = GET. XÜLASƏ.CƏDVƏL.MƏLUMAT("Gəlir";'Şək. 11-14′!$A$3;"Region";$A6;"Göstərici";O$5). Bu düsturu O sütununun digər xanalarına köçürün (şək. 21). Nəzərə alın ki, hətta Pivot Cədvəl hesabatının müxtəlif sahələrini köçürərkən belə, sarğı düzgün işləyir. Əlbəttə ki, xülasənin bəzi sahələrini qeyri-aktiv etsəniz, qabıq bunun öhdəsindən gəlməyəcək ...

düyü. 21. Menecerə təqdim edilə bilən yekun hesabat

İndi dinamik pivot cədvəlindəki dəyərlərdən istifadə edən yaxşı formatlaşdırılmış hesabat paketiniz var. İlkin hesabatın yaradılması kifayət qədər uzun vaxt aparsa da, onu yeniləmək bir neçə dəqiqəlik məsələdir.

Hesabat yeniləməsi. Hesabatı gələcək ayların məlumatları ilə yeniləmək üçün bu addımları yerinə yetirin:

Orijinal məlumat dəstinin altına faktiki daxil edin. Cədvəl formatı mənbə məlumatları üçün seçildiyi üçün cədvəl formatı avtomatik olaraq yeni verilənlər sətirlərinə yayılır. O, həmçinin orijinal Pivot Cədvəlin tərifini genişləndirir (Mən artıq Excel faylında bütün il üçün faktikiləri əlavə etmişəm).
Pivot cədvəlinə keçin. Sağ klikləyin və seçin təzələmək. Pivot masanın görünüşü dəyişəcək, lakin bu, qorxulu deyil.
Hesabat paketinə keçin. Prinsipcə, hesabatı yeniləmək üçün hər şey artıq edilib, lakin bu, nəticələrin sınaqdan keçirilməsinə mane olmur. Məsələn, A2 xanasındakı düsturu belə dəyişin: =AYDAN(BUGÜN() +31 ;0) və nə baş verdiyinə baxın.

Hər ay yeni faktiki satış məlumatlarını əlavə etməklə, formatları, düsturları və s. yenidən yaratmaqdan narahat olmaq lazım deyil. Təsvir edilən hesabatın yenilənməsi prosesi o qədər sadədir ki, siz aylıq hesabatların hazırlanması zamanı yaranan problemləri həmişəlik unutacaqsınız. Yeganə problem şirkət yenidən təşkil olunarsa yarana bilər, bunun nəticəsində pivot cədvəlində yeni bölgələr görünə bilər. Düsturların düzgün işləməsini təmin etmək üçün hesabatınızdakı cəmlərin Pivot Cədvəldəki cəmlərə uyğun olduğundan əmin olun. Yeni bir bölgə görünəndə, sadəcə onu sarğı vərəqinə əlavə edin və müvafiq düsturları sürükləyib buraxın.

Heç vaxt aşağıdakıları deyəcəyimi düşünmürdüm: “GET.PIVOTO.TABLE.Funksiya ən böyük nemətdir. Onsuz biz necə mövcud idik?

Orijinalda, Jelenin ilkin məlumatları sonrakı düsturların yalnız 2015-ci ilin iyul ayında düzgün işləməsi üçün tərtib edilmişdir. Bu qeydə əlavə edilmiş Excel faylında mən ilkin məlumatları, eləcə də bəzi düsturları dəyişdirdim ki, tarixdən asılı olmayaraq hər şey işləsin. Siz əlavə edilmiş Excel faylı ilə sınaqdan keçirəcəksiniz. Təəssüf ki, düsturlar mürəkkəb olmalı idi.

Əvvəllər mən artıq ondan danışmışdım ki, pivot cədvəl xanasına istinad edərkən adi istinad əvəzinə GET.PIVOTO.TABLE.DATA funksiyası qaytarılır (bax). Əgər siz maraqlanırsınızsa Necə Bu narahatlığı aradan qaldırmaq üçün qeyd olunan qeydə müraciət etməyi məsləhət görürəm. Əgər siz maraqlanırsınızsa, Niyə bu baş verir və həmçinin GET.DATA.PIVOTO.TABLE funksiyasının müsbət tərəfləri nələrdir, onda mən Jelen, Aleksandrın kitabından bir fraqment təqdim edirəm. (15-ci fəsil). Baxılan texnika pivot masaların istifadəçiləri üçün baş ağrısına səbəb olan bir çox problemin öhdəsindən gəlməyə imkan verəcək, xüsusən:

Pivot Cədvəli təzələmək əvvəllər tətbiq edilmiş formatı silir. Rəqəmsal formatlar itir. Sütunların eninin tənzimlənməsinin nəticələri yox olur.
Asimmetrik pivot masası yaratmağın asan yolu yoxdur. Yeganə seçim adlandırılmış dəstlərdən istifadə etməkdir, lakin bu üsul yalnız Data Modeli Pivot Cədvəllərindən istifadə edənlər üçün əlçatandır, adi Pivot Cədvəlləri deyil.
Excel şablonları yadda saxlaya bilmir. Pivot Cədvəlləri təkrar-təkrar yaratmaq lazımdırsa, siz yenidən qruplaşdırmalı, hesablanmış sahələri və üzvləri tətbiq etməli və bir sıra digər oxşar tapşırıqları yerinə yetirməli olacaqsınız.

Qeydi və ya formatda yükləyin, nümunələri formatda

Yaxşı, sıra ilə başlayaq.

Problemli GET.DATA.PIVOTO.TABLE funksiyasından necə imtina etmək olar

Tutaq ki, Şəkildə göstərilən pivot cədvəlində. 1, 2015 və 2014-cü illərin məlumatlarını müqayisə etməlisiniz.

düyü. 1. İlkin pivot cədvəli

D3 xanasına "% artım" başlığını əlavə edin.
Formatı C3 xanasından D3 xanasına köçürün.
D4 xanasına bərabər işarəni daxil edin.
C4 xanasına klikləyin.
Bölmə əməliyyatını göstərmək üçün / (slash) işarəsini daxil edin.
B4 xanasına klikləyin.
-1 yazın və düymələr birləşməsini basın eyni kamerada qalmaq. Nəticəni faizlə formatlayın. Qərb bölgəsinin gəlirlərində 43,8% azalma olduğunu görəcəksiniz (Şəkil 2). Çox yaxşı nəticələr deyil.
İlk düsturunuzu daxil etməyi bitirdikdən sonra D4 xanasını seçin.
Hüceyrənin aşağı sağ küncündə yerləşən kiçik kvadrata iki dəfə klikləyin. Bu qutu bütün hesabat sütununu doldurmaq üçün düsturun surətini çıxarmaq üçün istifadə edilə bilən doldurma sapını təmsil edir.

Formulun surəti tamamlandıqdan sonra ekrana baxaraq, bir şeyin səhv olduğunu başa düşəcəksiniz - hər bir bölgə il ərzində 43,8% azalma göstərdi (şəkil 3).

düyü. 3. Düsturu sütunun bütün xanalarına köçürdükdən sonra hər bölgənin 43,8% azalma göstərdiyini görəcəksiniz.

Real həyatda çətin ki, olur. İstənilən istifadəçi sizə deyəcək ki, yuxarıdakı addımları yerinə yetirdikdən sonra Excel =C4/B4-1 düsturu yaradacaq. D4 xanasına qayıdın və düstur çubuğuna diqqət yetirin (Şəkil 4). Bu sadəcə lənətə gəlmiş bir şeydir! Sadə =C4/B4–1 düsturu artıq mövcud deyil. Əvəzində proqram kompleks strukturu GET.PIVOTAL.TABLE.DATA funksiyası ilə əvəz edir. Niyə bu düstur D4 xanasında düzgün nəticələr verir, lakin aşağıdakı xanalara kopyalandıqda işləmir?

Excel 2000-də belə bir şey yox idi. Mən müntəzəm olaraq GET.DATA.PIVOTE.TABLE funksiyasını görməyə başlayanda, sadəcə olaraq, nifrət etdim. Seminarların birində kimsə məndən işin xeyrinə necə istifadə oluna biləcəyini soruşduqda, mat qaldım. Heç vaxt özümə belə sual verməmişəm! Mənim fikrimcə və əksər Excel istifadəçilərinin fikrincə, GET.DATA.PIVOTAL.TABLE funksiyası yaxşılıq qüvvələri ilə heç bir əlaqəsi olmayan şərin məhsulu idi. Xoşbəxtlikdən, bu funksiyanı söndürməyin iki yolu var.

D4 xanasına keçin və = (bərabər işarə) yazın.
C4 daxil edin.
/ (bölmə üçün kəsik) yazın.
B4 daxil edin.
-1 daxil edin.
basın Daxil edin.

İndi siz aşağıdakı sütun xanalarına köçürə və düzgün nəticələr əldə edə biləcəyiniz adi Excel düsturu yaratdınız (Şəkil 5). Gördüyünüz kimi, siz Pivot Cədvəlin daxilindəki məlumatlara istinad edən Pivot Cədvəldən kənar sahələrdə düsturlar yarada bilərsiniz. Bunun mümkün olduğuna inanmayanlar isə təsvir olunan hərəkətləri öz başlarına yerinə yetirsinlər.

düyü. 5. Klaviaturadan =С4/В4–1 yazın və düstur lazım olduğu kimi işləyəcək

Pivot cədvəllərini təkmilləşdirmək üçün GET.PIVOTO.TABLE.DATA funksiyasından istifadə

Aşağıdakı bölmələr sizə ötən aylar üçün faktiki məlumatları və gələcək aylar üçün planları əks etdirən dinamik hesabatın necə yaradılacağını göstərir.

Primitiv pivot cədvəlinin yaradılması.İlkin məlumatlar (şək. 10) şirkətin filiallarının yerləşdiyi hər bir region üzrə planlı və faktiki göstəricilər haqqında məlumatları ehtiva edən əməliyyatlar şəklində təqdim olunur. Planlaşdırılan göstəricilər aylar səviyyəsində, faktiki isə ayrı-ayrı günlər səviyyəsində təfərrüatlıdır. Planlı göstəricilər gələn il üçün, faktiki isə ötən aylar üçün yaradılır. Hesabat hər ay yenilənəcəyi üçün Pivot Cədvəl məlumat mənbəyi aşağıya yeni məlumatlar əlavə olunduqca ölçüdə böyüyərsə, bu proses xeyli sadələşir. Excel-in köhnə versiyalarında belə bir məlumat mənbəyinin yaradılması OFFSET funksiyasından istifadə edərək adlandırılmış dinamik diapazondan istifadə etməklə həyata keçirilirdi (ətraflı məlumat üçün bax). Excel 2013-də işləyərkən məlumat xanalarından birini seçin və Ctrl+T (cədvəl yaradın) düymələrini basın. Yeni sətirlər və sütunlar əlavə olunduqca avtomatik olaraq genişlənən adlandırılmış verilənlər toplusu görünəcək.

Pivot cədvəli yaradın:

Komanda seçin Daxil et → dönmə cədvəli və sonra dialoq qutusunda Pivot Cədvəl yaradın basın tamam.
Pivot Cədvəl Sahələri siyahısında sahəni seçin Tarix. Pivot cədvəlinin sol tərəfində tarixlərin siyahısı görünəcək (şək. 11).
İstənilən tarix xanasını seçin, məsələn, A4. Kontekst nişanında Təhlil, kontekstli nişanlar dəstində yerləşir Pivot masalarla işləmək, düyməsini basın Sahəyə görə qruplaşdırın(ətraflı məlumat üçün bax). Dialoq qutusunda Qruplaşdırma seçimi seçin aylar(şək. 12). basın tamam. Ayların adları pivot cədvəlinin sol tərəfində görünəcək (şək. 13).
Qutunu sürükləyin Tarix Pivot Cədvəl Sütunları sahəsində.
Qutunu sürükləyin indeks Pivot Table Sahə Siyahısının sütun sahəsinə.
Sahəni seçin Region pivot cədvəlinin sol sütununda göstəriləcək.
Sahəni seçin Gəlir Pivot Table Dəyərləri sahəsində görünəcək.

düyü. 11. Sahəyə görə qruplaşdırmaqla başlayın Tarix

Bu mərhələdə bizim pivot masamız olduqca primitiv görünür (şək. 14). Mən həqiqətən etiketləri sevmirəm Sıra adları Və Sütun adları. Cəmilərin göstərilməsi uyğun deyil Yanvar Planı Və Jan Fakt D sütununda və s. Ancaq bu pivot masanın görünüşündən narahat olmayın, çünki onu sizdən başqa heç kim görməyəcək. Bu nöqtədən başlayaraq, məlumat mənbəyi yeni yaratdığımız pivot cədvəli olacaq bir hesabat qabığı yaradacağıq.

Hesabat sarğısının yaradılması.İş dəftərinizə boş vərəq daxil edin. PivotTable alətlərini bir anlığa kənara qoyub adi Excel alətlərinə keçək. Bizim vəzifəmiz, menecerə göstərməkdən utanmayan gözəl bir hesabat yaratmaq üçün düsturlardan və formatlaşdırmadan istifadə etməkdir.

Aşağıdakı addımları yerinə yetirin (şək. 15).

A1 xanasına hesabatın adını daxil edin - Regionlar üzrə planlı və faktiki göstəricilər.
Taba keçin ev, düyməsini basın Hüceyrə üslubları format seçin Başlıq 1.
A2 xanasına = AY (BUGÜN (); 0) düsturu daxil edin. Bu funksiya cari ayın son gününü qaytarır. Məsələn, əgər siz bunu 14 avqust 2014-cü ildə oxuyursunuzsa, A2 xanası 31 avqust 2014-cü il tarixini oxuyacaq.
A2 xanasını seçin. Dialoq qutusunu göstərmək üçün Ctrl+1 düymələr kombinasiyasını basın Hüceyrə formatı. Nişanda Nömrə elementə klikləyin Bütün formatlar. Formaya xüsusi nömrə formatını daxil edin "Aydan" MMMM "planlı göstəricilər"(Şəkil 16). Nəticədə hesablanmış tarix mətn kimi görünəcək.
A5 xanasına başlıq daxil edin Region.
A sütununda qalan xanalara bölgə başlıqlarını daxil edin. Bölgə başlıqları Pivot Cədvəldəki bölgə adlarına uyğun olmalıdır.
Lazım gələrsə, şöbənin cəmi üçün sütuna etiketlər əlavə edin.
Hesabatın altına bir xətt əlavə edin Şirkət üçün cəmi.
B4 xanasına =TARİX(İL($A$2),SÜTUN(A1),1) düsturu daxil edin. Bu düstur cari ilin bütün 12 ayının ilk günləri olan 01/01/2014, 02/01/2104 və s. tarixləri qaytarır.
B4 xanasını seçin. Pəncərəni açmaq üçün Ctrl+1 düymələr kombinasiyasını basın Hüceyrə formatı. Nişanda Nömrə Fəsildə Bütün formatlar fərdi nömrə formatını daxil edin MMM. Bu format ayın üç hərfli adını göstərir. Mətni xananın sağ kənarına düzün.
B4 xanasının məzmununu C4:M4 diapazonuna köçürün. Pivot cədvəlinin yuxarısında ayların adları ilə bir sıra göstəriləcək.
B5 xanasına =ƏGƏR(AY(B4)) düsturu daxil edin<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakt və indiki və gələcək üçün - Plan.
N5 xanasına başlıq əlavə edin Nəticə. O4 hüceyrəsində - Nəticə, O5 - Plan, Р5 - % sapma.
Şöbə cəmini, şirkətin ümumi sətirini, ümumi ümumi sütununu və % fərq sütununu hesablayarkən istifadə olunan adi Excel düsturlarını daxil edin:
1. B8 xanasına = SUM (B6: B7) düsturu daxil edin və onu cərgənin digər xanalarına köçürün;
2. N6 xanasına = SUM (B6: M6) düsturu daxil edin və onu sütunun digər xanalarına köçürün;
3. P6 xanasına =IFERROR((N6/O10)-1;0) düsturunu daxil edin və onu sütunun digər xanalarına köçürün;
4. B13 xanasına = SUM (B10: B12) düsturu daxil edin və onu cərgənin digər xanalarına köçürün;
5. B17 xanasına = SUM (B15: B16) düsturu daxil edin və onu cərgənin digər xanalarına köçürün;
6. B19 xanasına =SUM(B6:B18)/2 düsturunu daxil edin və onu cərgənin digər xanalarına köçürün.
Başlıq 4 üslubunu A sütununda və 4 və 5-ci sətirlərdə başlıqlara tətbiq edin.
B6:O19 xana diapazonu üçün ###0 nömrə formatını seçin.
P sütunundakı xanalar üçün rəqəm formatını 0,0% seçin.

düyü. 15. Düsturlar əlavə etməzdən əvvəl sarğı haqqında hesabat verin

Hesabat qabığının B6 xanasını seçin. Bu xana şimal-şərq bölgəsinə və yanvar ayının faktiki rəqəmlərinə uyğundur.

Düsturu daxil etməyə başlamaq üçün = (bərabər işarə) yazın.
Pivot cədvəlindən vərəqə keçin və şimal-şərq bölgəsinə və yanvar ayı üçün faktiki rəqəmlərə uyğun gələn hüceyrəni basın - C12 (şəkil 17).
düyməsini basın Daxil edin formulun daxil edilməsini başa çatdırmaq və hesabat qabığına qayıtmaq üçün. Nəticədə, Excel B6 xanasına GET.PICTABLE.TABLE DATA funksiyasını əlavə edəcək. Hüceyrə 277,435 dollar göstərəcək.

Funksiya arqumentləri:

Data_field. Pivot Cədvəl Dəyərləri sahəsindən sahə. Diqqət edin: bu halda sahə istifadə olunur Gəlir, amma yox Gəlir sahəsindəki məbləğ.
Pivot Cədvəl. Bu seçimlə Microsoft sizdən "Hansı Pivot Cədvəldən istifadə etmək istəyirsiniz?" Pivot cədvəl xanalarından birini təyin etmək kifayətdir. Giriş 'Şəkil. 11-14'!$A$3 məlumatların daxil edildiyi Pivot Cədvəldəki ilk xanaya aiddir. Bizim vəziyyətimizdə pivot cədvəli ilə əlaqəli hər hansı bir xana təyin edə biləcəyiniz üçün arqumenti dəyişməz qoyun. $A$3 mobil ünvanı hər şəkildə uyğun gəlir.
Sahə 1; element 1. Avtomatik yaradılan düsturda sahə adı kimi ad seçilir Region, və sahə dəyəri kimi - Şimal-şərq. GET.PIVOT.TABLE DATA funksiyası ilə işləyərkən yaranan problemlərin səbəbi məhz buradadır. Avtomatik seçilmiş dəyərlər sərt kodlaşdırıldığı üçün kopyalana bilməz. Buna görə də, düsturları bütün hesabat sahəsinə köçürsəniz, onları əl ilə dəyişməli olacaqsınız. Şimal-şərq üçün xana istinadını $A6 ilə əvəz edin. A sütununun adının qarşısında dollar işarəsini göstərməklə, düsturu sütunun xanalarına köçürərkən istinadın sətir hissəsinin dəyişdirilə biləcəyini müəyyən edirsiniz.
Sahə 2; element 2. Bu arqumentlər cütü sahəni təyin edir Tarix 1 dəyəri ilə. Orijinal pivot cədvəli aya görə qruplaşdırılıbsa, ay sahəsi orijinal sahə adını saxlayır Tarix. Ayın rəqəmli dəyəri yanvar ayına uyğun gələn 1-dir. Onlarla, hətta yüzlərlə hesabat xanasında qurulmuş nəhəng düsturlar yaratarkən belə bir dəyərdən istifadə etmək çətin ki, məqsədəuyğundur. Sahə dəyərlərini hesablayan düsturdan istifadə etmək daha yaxşıdır Tarix, B4 xanasındakı düstur kimi. 1 əvəzinə, bu halda, AY (B $ 4) düsturundan istifadə edə bilərsiniz. 4-dən əvvəl dollar işarəsi düsturun sahəyə dəyərlər təyin edə biləcəyini göstərir Tarix düstur cərgə xanalarına kopyalandığı üçün digər aylara əsaslanır.
Sahə 3; element 3. Bu halda sahənin adı avtomatik olaraq təyin olunur indeks və sahə dəyəri Fakt. Bu dəyərlər yanvar ayı üçün düzgündür, lakin sonrakı aylar üçün sahə dəyəri Plan olaraq dəyişdirilməlidir. Sabit kodlu sahə dəyərini dəyişdirin Fakt B$5 ilə əlaqələndirmək.
Sahə 4; element 4. Bu arqumentlər ona görə istifadə edilmir sahələr bitdi.

düyü. 19. Redaktə başa çatdıqdan sonra GET.DATA.PIVOTO.TABLE düsturu diapazonun bütün xanalarına köçürmək üçün uyğundur.

düyü. 20. Sütunlar sahəsində sahələrin tərtibatını elə tənzimləyin ki, sütun görünsün Nəticəni planlaşdırın

düyü. 21. Menecerə təqdim edilə bilən yekun hesabat

Hesabat yeniləməsi. Hesabatı gələcək ayların məlumatları ilə yeniləmək üçün bu addımları yerinə yetirin:

Orijinal məlumat dəstinin altına faktiki daxil edin. Cədvəl formatı mənbə məlumatları üçün seçildiyi üçün cədvəl formatı avtomatik olaraq yeni verilənlər sətirlərinə yayılır. O, həmçinin orijinal Pivot Cədvəlin tərifini genişləndirir (Mən artıq Excel faylında bütün il üçün faktikiləri əlavə etmişəm).
Pivot cədvəlinə keçin. Sağ klikləyin və seçin təzələmək. Pivot masanın görünüşü dəyişəcək, lakin bu, qorxulu deyil.
Hesabat paketinə keçin. Prinsipcə, hesabatı yeniləmək üçün hər şey artıq edilib, lakin bu, nəticələrin sınaqdan keçirilməsinə mane olmur. Məsələn, A2 xanasındakı düsturu belə dəyişin: =AYDAN(BUGÜN() +31 ;0) və nə baş verdiyinə baxın.

Heç vaxt aşağıdakıları deyəcəyimi düşünmürdüm: “GET.PIVOTO.TABLE.Funksiya ən böyük nemətdir. Onsuz biz necə mövcud idik?

Pivot Cədvəllər üçün GET.PIVOTO.TABLE DATA funksiyası Pivot Cədvəl hesabatında saxlanılan məlumatları qaytarır.

Funksiyaya tez daxil olmaq üçün xanaya bərabər işarəni (=) daxil etməli və pivot cədvəlində istədiyiniz xananı seçməlisiniz. Excel GET.PIVOTOMATIC.DATA funksiyasını avtomatik olaraq yaradacaq.

GetPivotData yaradılmasını söndürün

GET.PIVOTO.TABLE.DATA funksiyasının avtomatik yaradılmasını söndürmək üçün pivot cədvəlində istənilən xananı seçin, nişana keçin. Pivot cədvəlləri ilə işləmək -> Seçimlər qrupa Pivot cədvəli. Nişanın yanındakı aşağı oxu klikləyin Seçimlər. Açılan menyuda qutunun işarəsini çıxarın YaradınGetPivotData.

GET.PIVOTO.TABLE DATA Funksiyasında Hüceyrə Referanslarından istifadə

GET.DATA.PIVOTO.TABLE funksiyasında elementlərin və ya sahələrin adlarını göstərmək əvəzinə vərəqdə yerləşən xanalara müraciət edə bilərsiniz. Aşağıdakı nümunədə E3 xanası məhsulun adını ehtiva edir və E4 xanasındakı düstur ona istinad edir. Nəticədə tortların ümumi həcmi geri qaytarılacaq.

Pivot Cədvəl Sahə İstinadlarından istifadə

Pivot cədvəl elementlərinə keçidlərin necə işləməsi ilə bağlı heç bir sual yoxdur, məlumat sahəsinə müraciət etmək istəsək problemlər yaranır.

Nümunədə E3 xanası "Saymaq" məlumat sahəsinin adını ehtiva edir və GET.DATA.PIVOTO.TABLE düsturunda sahənin adının olması əvəzinə, funksiyada bu xanaya istinad etmək yaxşı olardı.

Ancaq birinci arqumenti dəyişdirsək data_field E3 xanasına istinad üçün Excel #LINK səhvini qaytaracaq!

GET.PIVOTO.TABLE.DATA(E3,$A$3)

Hüceyrə istinadının əvvəlinə və ya sonuna boş sətir (“”) əlavə etmək problemi həll edəcək.

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

Düsturun sadə düzəlişi düzgün dəyəri qaytaracaq.

GET DATA-da tarixlərdən istifadə

GET.PIVOTO.TABLE funksiyasında tarixlərdən istifadə etsəniz, tarix Pivot Cədvəldə görünsə belə, probleminiz ola bilər. Məsələn, aşağıdakı formula arqumenti "04/21/2013" tarixidir və Pivot Cədvəldə satış tarixləri olan sahə var. Bununla belə, E4 xanasındakı düstur xəta qaytarır.

GET.PIVOTO.TABLE.DATA("Həcmi",$A$3,"Tarix","21/04/2013")

Tarixlə bağlı səhvlərin qarşısını almaq üçün aşağıdakı üsullardan birini istifadə edə bilərsiniz:

Düstur və pivot cədvəlində tarix formatlarını müqayisə edin
DATEVALUE funksiyasından istifadə edin
TARİX funksiyasından istifadə edin
Etibarlı tarixi olan xanaya baxın

Düstur və pivot cədvəlində tarix formatlarını müqayisə edin.

Düzgün nəticə əldə etmək üçün GET.PIVOTOMAT.DATA funksiyasından istifadə edərkən formula arqumentinin və pivot cədvəlinin tarix formatlarının eyni olduğundan əmin olun.

E4 xanasında düstur “GG.AA.YYYY” tarix formatından istifadə edir və düzgün məlumatı qaytarır.

DATEVALUE funksiyasından istifadə

Düsturda tarixi əl ilə daxil etmək əvəzinə, tarixi qaytarmaq üçün TARİXVALUE funksiyasını əlavə edə bilərsiniz.

E4 xanasına tarix TARİXVALUE funksiyasından istifadə etməklə daxil edilir və Excel tələb olunan məlumatları qaytarır.

GET.PIVOTO.TABLE.DATA("Cild",$A$3,"Tarix",TARİXVALUE("21/04/2013"))

TARİX funksiyasından istifadə

Düsturda tarixi əl ilə daxil etmək əvəzinə, lazımi məlumatları düzgün qaytaracaq TARİX funksiyasından istifadə edə bilərsiniz.

GET.PIVOTO.TABLE.DATA("Həcm",$A$3,"Tarix",TARİX(2013,4,21))

Tarixi olan xanaya keçid

Düstura tarixi əl ilə daxil etmək əvəzinə, siz tarixdən ibarət xanaya müraciət edə bilərsiniz (hər hansı formatda Excel verilənləri tarix kimi qəbul edir). E4 xanasındakı nümunədə düstur E3 xanasına aiddir və Excel düzgün məlumatları qaytarır.

GET.PIVOTO.TABLE.DATA("Həcm",$A$3,"Tarix",E3)