Asosiy jadvallardan foydalanish

Ilgari, men allaqachon hujayrani nazarda tutganim haqida gapirdim pivot jadvali oddiy havola o'rniga GET.DATA.PUMP.TABLE funktsiyasi qaytariladi (qarang). Agar sizga qiziq bo'lsa kabi ushbu noqulaylikni bartaraf etish uchun men ushbu eslatmaga murojaat qilishni maslahat beraman. Agar siz hayron bo'lsangiz, nima uchun bu shunday bo'ladi, shuningdek GET DATA.COMPLISHED TABLE funktsiyasining ijobiy tomonlari qanday bo'lsa, men Jelen, Aleksandr kitobining bir qismini taklif qilaman. (15-bob). Ko'rib chiqilayotgan texnik sizga pivot jadvallari foydalanuvchilari uchun bosh og'rig'ini keltirib chiqaradigan ko'plab muammolarni engishga imkon beradi, xususan:

PivotTable yangilanganida, avval qo'llanilgan formatlash yo'qoladi. Raqam formatlari yo'qolgan. Ustun kengligini sozlash natijalari yo'qoladi.
Mavjud emas oson yo'l assimetrik burilish jadvalini yaratish. Yagona variant - nomlangan to'plamlardan foydalanish, ammo bu usul faqat oddiy modeldagi Pivot Stollardan emas, balki "Model Model Pivot Stables" dan foydalanadiganlar uchun mavjud.
Excel shablonlarni eslay olmaydi. Agar siz PivotTables-ni qayta-qayta tiklashingiz kerak bo'lsa, siz qayta guruhlashingiz, hisoblangan maydonlar va a'zolarni qo'llashingiz va shunga o'xshash boshqa bir qator vazifalarni bajarishingiz kerak bo'ladi.

Aslida, bu erda tasvirlangan hamma narsa yangi emas. Shu kabi usullar Excel 2002 yildan beri qo'llanilib kelinmoqda. Ammo mening foydalanuvchilar bilan muloqotim shuni ko'rsatadiki, 1 foizdan kamrog'i ular bilan tanish. Foydalanuvchilarning yagona savoli - bu g'alati GET DATA OF PANEL.TABLE funktsiyasini qanday o'chirish. Afsus…

Yozuvni formatda yoki misollarni formatida yuklab oling

Xo'sh, tartibda boshlaylik.

Muammoli funktsiyadan qanday qilib voz kechish mumkin DATA. Shaxsiy jadvallar

GET PIVOTTABLE DATA funktsiyasi uzoq vaqtdan beri ko'plab foydalanuvchilar uchun bosh og'rig'i bo'lib kelgan. To'satdan, hech qanday ogohlantirishsiz, PivotTables-ning xatti-harakatlari Excel 2002 da o'zgardi. PivotTable-dan tashqarida uning ma'lumotlariga mos keladigan formulalarni yaratishni boshlashingiz bilanoq, bu funktsiya yo'q bo'lib chiqadi.

Shaklda ko'rsatilgan burilish jadvalida deylik. 1, siz 2015 va 2014 yillardagi ma'lumotlarni taqqoslashingiz kerak.

Shakl: 1. Asl pivot jadvali

D3 katakka "% Balandligi" sarlavhasini qo'shing.
Formatni C3 katakchadan D3 katakka nusxalash.
D4 katakchaga teng belgini kiriting.
C4 katakchani bosing.
Bo'linish amalini ko'rsatish uchun / (oldinga slash) belgisini kiriting.
B4 katakchani bosing.
-1 raqamini kiriting va tugmalar birikmasini bosing bitta kamerada qolish. Natijani foiz sifatida formatlang. G'arbiy mintaqada daromadning 43,8% pasayishi kuzatilganini ko'rasiz (2-rasm). Juda yaxshi natijalar emas.
Birinchi formulani kiritishni tugatgandan so'ng, D4 katakchani tanlang.
Hujayraning pastki o'ng burchagida joylashgan kichik kvadrat ustiga ikki marta bosing. Ushbu kvadrat hisobotdagi butun ustunni to'ldirish uchun formuladan nusxa olish uchun foydalanishingiz mumkin bo'lgan to'ldirish dastagini aks ettiradi.

Formulani nusxalashni tugatgandan so'ng, ekranga qarab, nimadir noto'g'ri ekanligini tushunasiz - har bir mintaqa yil davomida 43,8% pasayishni ko'rsatdi (3-rasm).

Shakl: 3. Formulani ustundagi barcha katakchalarga nusxalashni tugatgandan so'ng, har bir mintaqada 43,8% pasayish kuzatilganini ko'rasiz

Haqiqiy hayotda bunday bo'lishi mumkin emas. Har kim sizga yuqoridagi amallarni bajargandan so'ng Excel \u003d C4 / B4-1 formulasini yaratishini aytadi. D4 katakka qayting va formulalar qatoriga e'tibor bering (4-rasm). Faqat bir oz shayton! Oddiy formula \u003d C4 / B4-1 endi mavjud emas. Buning o'rniga dastur GET.DATA.PUMP.TABLE funktsiyasi bilan murakkab qurilishni almashtiradi. Nima uchun ushbu formula D4 katakchada to'g'ri natijalarni beradi, ammo uning ostidagi katakchalarga ko'chirgandan so'ng ishlashdan bosh tortadi?

Har qanday foydalanuvchining sodir bo'lgan voqeaga birinchi reaktsiyasi quyidagicha bo'ladi: "Mening hisobotimni buzgan bu g'alati GET.DATA.PERSONAL.TABLE konstruktsiyasi nima?" Ko'pgina foydalanuvchilar ushbu xususiyatdan darhol xalos bo'lishni xohlashadi. Ba'zilar: "Nima uchun Microsoft ushbu xususiyatni bizga topshirdi?", Degan savolni berishadi.

Ichida bunday narsa yo'q edi excel vaqtlari 2000. GET.DATA.PERSONAL.TABLES funktsiyasini muntazam ravishda uchratishni boshlaganimdan, shunchaki nafratlandim. Seminarlardan birida kimdir mendan uni qanday qilib yaxshi maqsadda ishlatish mumkinligini so'raganida, men dovdirab qoldim. Men hech qachon bunday savol bermagan edim! Mening fikrimcha va aksariyat Excel foydalanuvchilari fikriga ko'ra GET.DATA.PUMP.TABLE funktsiyasi yaxshilik kuchlari bilan hech qanday aloqasi bo'lmagan yovuzlik mahsuli edi. Yaxshiyamki, ushbu xususiyatni o'chirishning ikkita usuli mavjud.

Formulani kiritish orqali GET PIVOTTABLE DATA funktsiyasini blokirovka qilish.GET PIVOTTABLE funktsiyasi paydo bo'lishining oldini olishning oddiy usuli mavjud. Buning uchun sichqonchani yoki kursor tugmachalarini ishlatmasdan formulani yaratish kerak. Faqat quyidagi bosqichlarni bajaring.

D4 katakchaga o'ting va \u003d (teng belgi) yozing.
C4 raqamini kiriting.
A / (bo'linish uchun oldinga chiziq) kiriting.
B4 kiriting.
-1 raqamini kiriting.
Ustiga bosing Kirish.

Siz doimiy ravishda yaratdingiz excel formulasi, bu ustun ostidagi katakchalarga ko'chirilishi mumkin va shu bilan siz to'g'ri natijalarni olishingiz mumkin (5-rasm). Ko'rib turganingizdek, siz O'rnatilgan jadval ichidagi ma'lumotlarga havola qiladigan formulalar yaratishingiz mumkin. Va buning mumkinligiga ishonmaydiganlar, ta'riflangan harakatlarni o'zlari bajarishga ijozat bersinlar.

Shakl: 5. Faqat klaviaturadan \u003d C4 / B4-1 raqamini kiriting va formulalar kerakli darajada ishlaydi

Ba'zi foydalanuvchilar formulalarni kiritish odatiy tartibidan bezovtalanishadi. Bundan tashqari, taklif qilingan variant ko'proq mehnatkash. Agar siz ushbu foydalanuvchilardan bo'lsangiz, ikkinchi yo'l siz uchun ...

GET PANEL.TABLE DATA funktsiyasini o'chirib qo'ying.GET PANEL.TABLE DATA funktsiyasini butunlay o'chirib qo'yishingiz mumkin. Menyu tasmasini bosing Fayl → Parametrlar... Ochilgan oynada ParametrlarExcel hissa qo'shish Formulalar va parametr yonidagi katakchani olib tashlang Funktsiyadan foydalaningPivot jadvali havolalari uchun GetPivotData... Ustiga bosing Ok.

Muqobil variant. Pivot jadvalini va paydo bo'lgan kontekstli yorliqni bosing Tahlil tugma yonidagi ochiladigan ro'yxatni bosing Parametrlar... Yonidagi katakchani olib tashlang GetPivotData-ni yarating (rasm 7). Odatiy ravishda tasdiqlash qutisi yoniq.

Nima uchun Microsoft bizga GET PANEL.TABLES xususiyatini taklif qildi.Agar bu xususiyat juda dahshatli bo'lsa, nima uchun Microsoft uni sukut bo'yicha yoqdi? Nima uchun ular ushbu xususiyatni Excelning yangi versiyalarida qo'llab-quvvatlashni davom ettirishlari kerak? Ular foydalanuvchi kayfiyatidan xabardormi? Va biz qiziqarli qismga o'tamiz ...

PivotTables-ni yaxshilash uchun GET PIVOTTABLE DATA-dan foydalanish

Pivot jadvallar insoniyatning buyuk ixtirosi. Burilish jadvali faqat bir necha marta bosish bilan yaratilgan bo'lib, kengaytirilgan filtr, BDSUMM funktsiyasi va ma'lumotlar jadvallarini qo'llash zaruriyatini yo'q qiladi. Asosiy jadvallar yordamida siz katta hajmdagi ma'lumotlardan bir sahifali hisobotlarni yaratishingiz mumkin. Ushbu afzalliklarning fonida, aks etuvchi formatlash va pivot jadvallarini qiymatlarga aylantirish zarurati bo'lgan pivot jadvallarining ba'zi kamchiliklari qo'shimcha sozlash... Shakl. 8-rasmda pivot jadvalini yaratish uchun odatiy jarayon ko'rsatilgan. Bunday holda, hamma narsa dastlabki ma'lumotlardan boshlanadi. Biz pivot jadvalini yaratamiz va uni sozlash va takomillashtirish uchun barcha mumkin bo'lgan usullardan foydalanamiz. Ba'zan biz pivot jadvalini qiymatlarga aylantiramiz va yakuniy formatlashni amalga oshiramiz.

Rob Colley (Microsoft-ning ishlab chiquvchisi) tomonidan taklif qilingan va quyida muhokama qilingan yangi PivotTable metodologiyasi yuqorida tavsiflangan jarayonni takomillashtirish natijasidir. Bunday holda, avval ibtidoiy pivot jadvali tuziladi. Ushbu jadvalni formatlashingiz shart emas. So'ngra yakuniy hisobotni joylashtiradigan chiroyli formatlangan qobiqni yaratish uchun bir bosqichli, nisbatan ko'p vaqt sarflaydigan jarayon kuzatiladi. Shundan so'ng, GET.DATA.PERSONAL.TABLE funktsiyasi qobiqdagi hisobotni ma'lumotlar bilan tezda to'ldirish uchun ishlatiladi. Yangi ma'lumotlarni olgandan so'ng, siz ularni varaqqa qo'yishingiz, ibtidoiy pivot jadvalini yangilashingiz va qobiqdagi hisobotni chop etishingiz mumkin (9-rasm). Ushbu texnikaning bir qator inkor etilmaydigan afzalliklari bor. Masalan, hisobotni yaratgandan so'ng darhol uni formatlash haqida tashvishlanishingiz shart emas. Burilish jadvallarini yaratish jarayoni deyarli to'liq avtomatlashtiriladi.

Keyingi bo'limlarda o'tgan oylarning haqiqiy ko'rsatkichlari va kelgusi oylarning maqsadlari aks ettirilgan dinamik hisobotni qanday yaratish haqida gap boradi.

Ibtidoiy pivot jadvalini yarating.Dastlabki ma'lumotlar (10-rasm) kompaniyaning filiallari joylashgan har bir mintaqa uchun rejalashtirilgan va haqiqiy ko'rsatkichlar to'g'risidagi ma'lumotlarni o'z ichiga olgan bitimlar shaklida taqdim etiladi. Rejalashtirilgan ko'rsatkichlar oy darajasida, haqiqiy kunlar esa alohida kunlar darajasida batafsil bayon etilgan. Rejalashtirilgan ko'rsatkichlar kelgusi yil uchun, dolzarb ko'rsatkichlar o'tgan oylar uchun tuziladi. Hisobot har oy yangilanib turishi sababli, quyida yangi ma'lumotlar qo'shilganda PivotTable-ning ma'lumotlar manbai hajmi kattalashsa, bu jarayon ancha soddalashtiriladi. Eskirgan versiyalarda Excel yaratish shunga o'xshash ma'lumotlar manbai OFFSET funktsiyasidan foydalangan holda nomlangan dinamik diapazon yordamida amalga oshirildi (batafsil ma'lumotga qarang). Excel 2013 da ishlash paytida ma'lumotlar katakchasidan birini tanlang va Ctrl + T tugmalar birikmasini bosing (Jadval yarating). Bu yangi qatorlar va ustunlar qo'shilganda avtomatik ravishda kengayadigan nomlangan ma'lumotlar to'plamini yaratadi.

Endi pivot jadvalini yarataylik. GET PIVOTTABLE DATA funktsiyasi etarlicha kuchli, ammo u faqat haqiqiy PivotTable-da ko'rinadigan qiymatlarni qaytarishi mumkin. Ushbu funktsiya pivot jadvalida bo'lmagan narsalarni hisoblash uchun keshni tekshirishni amalga oshira olmaydi.

Asosiy jadvalni yarating:

Jamoani tanlang Kiritmoq → Umumiy jadvalva keyin dialog oynasida Qaytgan jadval yaratishbosing OK.
PivotTable Field ro'yxatida maydonni tanlang sana... Burilish jadvalining chap tomonida sanalar ro'yxati paydo bo'ladi (11-rasm).
Har qanday sana katakchasini tanlang, masalan A4. Kontekst yorlig'ida Tahlilkontekstli yorliqlar to'plamida joylashgan Burilish jadvallari bilan ishlashtugmasini bosing Maydonlar bo'yicha guruhlash (batafsil ma'lumotga qarang). Muloqot oynasida Guruhlash variantni tanlang Oylar (12-rasm). Bosing OK... Burilish stolining chap tomonida oylarning nomlari paydo bo'ladi (13-rasm).
Maydonni torting sana PivotTable-ning ustunlar maydoniga.
Maydonni torting Indeks PivotTable Field ro'yxatining ustunlar maydoniga.
Maydonni tanlang Mintaqapivot jadvalining chap ustunida ko'rsatilishi kerak.
Maydonni tanlang DaromadPivotTable Values \u200b\u200bmaydonida paydo bo'ladi.

Shakl: 11. Maydonlar bo'yicha guruhlash bilan boshlang sana

Ayni paytda bizning asosiy jadvalimiz juda ibtidoiy ko'rinadi (14-rasm). Men, albatta, xat yozishni yoqtirmayman Qator nomlari va Ustun nomlari... Umumiy ko'rsatkichlarni ko'rsatish maqsadga muvofiq emas Yan rejasi va Yan Fakt D ustunida va boshqalar. Ammo tashvishlanmang tashqi ko'rinish bu burilish jadvali, chunki uni sizdan boshqa hech kim ko'rmaydi. Shu vaqtdan boshlab biz yangi yaratilgan pivot jadvali bo'lgan ma'lumotlar manbai bo'lgan hisobot plyonkasini yaratamiz.

Hisobot qobig'ini yaratish.Ish daftaringizga bo'sh varaq kiriting. PivotTable vositalarini bir muddat chetga surib, oddiy Excel vositalariga o'tamiz. Bizning vazifamiz menejerga ko'rsatishdan uyalmaydigan chiroyli hisobot yaratish uchun formulalar va formatlashdan foydalanishdir.

Quyidagi tarzda harakat qiling (15-rasm).

A1 katakka hisobot nomini kiriting - Mintaqalar bo'yicha rejalashtirilgan va haqiqiy ko'rsatkichlar.
Yorliqqa o'ting uy, tugmachani bosing Hujayra uslublari formatni tanlang Sarlavha 1.
A2 katakchaga \u003d EON MONTHS (BUGUN (); 0) formulani kiriting. Ushbu funktsiya joriy oyning oxirgi kunini qaytaradi. Masalan, 2014 yil 14 avgustda ushbu satrlarni o'qigan bo'lsangiz, A2 katakchada 2014 yil 31 avgust sanasi ko'rsatiladi.
A2 katakchani tanlang. Muloqot oynasini ko'rsatish uchun Ctrl + 1 tugmalar birikmasini bosing Hujayra formati... Yorliqda Raqam elementni bosing Barcha formatlar... Sifatida maxsus raqam formatini kiriting "Oydan" MMMM "rejalashtirilgan ko'rsatkichlar" (16-rasm). Natijada hisoblangan sana matnga o'xshaydi.
A5 katakka sarlavha kiriting Mintaqa.
A ustunidagi qolgan katakchalarga mintaqa sarlavhalarini kiriting. Mintaqa sarlavhalari burilish jadvalida ko'rsatilgan mintaqa nomlariga mos kelishi kerak.
Agar kerak bo'lsa, bo'lim bo'yicha jami ustunlarga yorliqlar qo'shing.
Hisobotning pastki qismida qatorni qo'shing Kompaniya uchun jami.
B4 katakka \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1) formulasini kiriting. Ushbu formulada joriy yilning barcha 12 oyining birinchi kunlari 01/01/2014, 01/02/2104 va boshqalar sanalari qaytariladi.
B4 katakchani tanlang. Oynani ochish uchun Ctrl + 1 tugmalar birikmasini bosing Hujayra formati... Yorliqda Raqam Bobda Barcha formatlar maxsus raqam formatini kiriting Mmm... Ushbu format uch harfli oy nomini ko'rsatadi. Matnni katakning o'ng tomoniga tekislang.
B4 katakchaning tarkibini C4: M4 diapazoniga nusxalash. Asosiy stolning yuqori qismida oylarning nomlari ko'rsatilgan qator paydo bo'ladi.
B5 katakka \u003d IF (MONTH (B4)) formulasini kiriting<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Faktva hozirgi va kelajak uchun - Reja.
N5 katakka sarlavha qo'shing Natija... O4 katakchaga - Natija, O5 - Reja, P5 - % og'ish.
Bo'lim yig'indilarini, kompaniyaning umumiy qatorini, umumiy ustunini va% dispersiya ustunini hisoblash uchun ishlatiladigan odatdagi Excel formulalarini kiriting:
1. b8 katagiga \u003d SUM (B6: B7) formulasini kiriting va uni qatordagi boshqa katakchalarga nusxalash;
2. n6 katakchaga \u003d SUM (B6: M6) formulasini kiriting va uni ustundagi boshqa katakchalarga nusxalash;
3. p6 katakchaga \u003d IFERROR ((N6 / O10) -1; 0) formulasini kiriting va uni ustundagi boshqa katakchalarga nusxalash;
4. b13 katakchaga \u003d SUM (B10: B12) formulani kiriting va uni qatordagi boshqa katakchalarga nusxalash;
5. b17 katakchaga \u003d SUM (B15: B16) formulasini kiriting va uni qatordagi boshqa katakchalarga nusxalash;
6. b19 katakchaga \u003d SUM (B6: B18) / 2 formulasini kiriting va uni qatordagi boshqa katakchalarga nusxalash.
Sarlavha 4 uslubini A ustunidagi yorliqlarga va 4 va 5 qatorlar sarlavhalariga qo'llang.
B6: O19 kataklar oralig'i uchun # ## 0 raqamli formatni tanlang.
P ustunidagi kataklar uchun raqam formatini 0,0% tanlang.

Shunday qilib, biz shakl .da ko'rsatilgan hisobot qobig'ini yaratishni yakunladik. 15. Ushbu hisobot barcha kerakli formatlashni o'z ichiga oladi. Keyingi bo'lim hisobotni to'ldirish uchun GET PIVOTTABLE DATA funktsiyasidan qanday foydalanishni namoyish etadi.

Shakl: 15. GET.DATA.PERSONAL.TABLES formulalarini qo'shishdan oldin doka haqida xabar bering

Hisobot qobig'ini ma'lumotlar bilan to'ldirish uchun GET PIVOTTABLE DATA funktsiyasidan foydalanish.Bundan buyon siz GET PANEL.TABLE DATA funktsiyasidan foydalanishning barcha afzalliklarini boshdan kechirishingiz mumkin. Agar siz ushbu funktsiyani yoqadigan katakchani olib tashlagan bo'lsangiz, mos keladigan parametrga qayting va tasdiqlash qutisini qaytaring (6 yoki 7-rasmdagi tavsifga qarang).

Hisobot qobig'ining B6 katagini tanlang. Ushbu katak Shimoliy-Sharqiy mintaqaga va yanvar oyining haqiqiy ko'rsatkichlariga to'g'ri keladi.

Formulani kiritishni boshlash uchun \u003d (teng belgi) kiriting.
Burilish jadvalidagi varaqqa o'ting va shimoliy-sharqiy mintaqaga va yanvar - C12 uchun haqiqiy ko'rsatkichlarga mos keladigan katakchani bosing (17-rasm).
Tugmani bosing Kirishformulani kiritishni tugatish va hisobot qobig'iga qaytish. Natijada, Excel B6 katakchasiga GET PIVOTTABLE DATA funktsiyasini qo'shadi. Yacheykada 277 435 dollar qiymat ko'rsatiladi.

Ushbu raqamni eslang, chunki siz keyinchalik tahrir qiladigan formulalar natijalari bilan taqqoslaganda talab qilinadi. Dastur tomonidan ishlab chiqarilgan formulalar quyidagi shaklga ega: \u003d GET.DATA.PUMP.TABLE ("Daromad"; '11-14-rasm ′! $ A $ 3; "Hudud"; "Shimoliy-Sharq"; "Sana" ; 1; "Ko'rsatkich"; "Fakt"). Agar siz hozirgacha GET PANEL.TABLE funktsiyasini e'tiborsiz qoldirgan bo'lsangiz, diqqat bilan qarash vaqti keldi. Shakl. 18 ushbu formula tahrirlash rejimida ko'rsatma bilan birga ko'rsatilgan.

Funktsiya argumentlari:

Ma'lumotlar maydoni. Burilish jadvalining qiymat maydonidan maydon. Iltimos, diqqat qiling: bu holda maydon ishlatiladi Daromad, lekin emas Daromad sohasidagi summa.
Umumiy jadval. Ushbu parametr yordamida Microsoft sizdan "Qaysi pivot jadvalidan foydalanmoqchisiz?" Asosiy jadval hujayralaridan birini ko'rsatish kifoya. Kirish "Shakl. 11-14 '! $ A $ 3 ma'lumotlar kiritiladigan pivot jadvalidagi birinchi katakchani anglatadi. Bizning holatimizda siz pivot jadvaliga tegishli har qanday katakchani belgilashingiz mumkin, argumentni o'zgarishsiz qoldiring. Uyali telefon manzili $ 3 $ har jihatdan mos keladi.
1-maydon; element 1. Avtomatik ravishda yaratilgan formulada maydon nomi tanlanadi Mintaqava maydon qiymati sifatida - Shimoli-sharq... GET DATA.PUMP.TABLE funktsiyasi bilan ishlashda yuzaga keladigan muammolarning sababi shu erda. Avtomatik tanlangan qiymatlarni nusxalash mumkin emas, chunki ular qattiq kodlangan. Shuning uchun, agar siz formulalarni butun hisobot maydoniga nusxa ko'chirsangiz, ularni qo'lda o'zgartirishingiz kerak bo'ladi. Shimoliy-sharqni $ A6 shaklidagi katakka mos yozuvlar bilan almashtiring. A ustunining oldiga dollar belgisini qo'yib, formulani ustundagi katakchalarga nusxalashda ma'lumotnomaning satr qismini o'zgartirish mumkinligini aniqlaysiz.
2-maydon; modda 2. Ushbu juft argument maydonni belgilaydi sana qiymati 1 bilan. Agar asl PivotTable oylar bo'yicha guruhlangan bo'lsa, oy maydoni asl maydon nomini saqlab qoladi sana... Oyning raqamli qiymati 1 ga teng, bu yanvarga to'g'ri keladi. Bunday qiymatdan o'nlab, hatto yuzlab hisobot hujayralarida ko'rsatilgan ulkan formulalarni yaratishda ishlatish maqsadga muvofiq emas. Maydon qiymatlarini hisoblaydigan formuladan foydalanish yaxshiroqdir sana, B4 katakchadagi formula kabi. Bunday holda 1 o'rniga, siz MONTH (4 dollar ichida) formulasidan foydalanishingiz mumkin. 4-dan oldingi dollar belgisi formulaning maydonga qiymatlarni belgilashi mumkinligini ko'rsatadi sana qator boshqa katakchalarga formuladan nusxa ko'chirilganligi sababli boshqa oylarga asoslanadi.
3-maydon; element 3. Bu holda maydon nomi avtomatik ravishda tayinlanadi Indeks va maydon qiymati Fakt... Ushbu qiymatlar yanvar oyi uchun to'g'ri keladi, ammo keyingi oylar uchun maydon qiymati Rejaga o'zgartirilishi kerak. Maydonning qattiq kodlangan qiymatini o'zgartiring Fakt B $ 5 havolasiga.
4-maydon; 4-element. Ushbu dalillardan foydalanilmaydi, chunki dalalar tugadi.

Yangi formulalar shakl. 19. Bir daqiqada bitta qiymat bilan ishlashga mo'ljallangan qattiq kodlangan formulaning o'rniga, ma'lumotlar bazasidagi barcha kataklarga ko'chirilishi mumkin bo'lgan egiluvchan formula yaratildi. Tugmani bosing Kirishva siz formulani tahrirlashdan oldingi natijani olasiz. Tahrirlangan formulalar quyidagi shaklga ega: \u003d GET.PUMP.TABLE DATA ("Daromad"; 'Shakl 11-14 ′! $ A $ 3; "Region"; $ A6; "Sana"; MONTH (B $ 4) ; "Ko'rsatkich"; B $ 5)

Shakl: 19. Tahrirlash tugagandan so'ng, GET.DATA.PERSONAL.TABLE formulasi diapazonning barcha katakchalariga nusxalash uchun javob beradi.

Formulani natijalar hisoblangan B: M ustunlaridagi bo'sh katakchalarga nusxalash. Endi hisobotda haqiqiy raqamlar mavjud bo'lib, siz ustun kengliklariga yakuniy tuzatishlar kiritishingiz mumkin.

Keyingi bosqichda biz yakuniy maqsadlarni hisoblash uchun GET.PUMP.TABLE formulasini o'rnatamiz. Agar siz faqat formulani O6 katakchaga ko'chirsangiz, xato xabari #REF! Ushbu xatoning sababi bu so'z Natija O4 katakchada oy nomi emas. GET PIVOTTABLE DATA funktsiyasining to'g'ri ishlashini ta'minlash uchun kerakli qiymat PivotTable-da bo'lishi kerak. Ammo asl pivot jadvalida maydon Indeks ustunlar sohasidagi ikkinchi maydon, ma'lumotlar ustunidir Reja xulosasi aslida yo'q. Maydonni siljiting Indeks shunday qilib u ustun maydonida birinchi bo'ladi (20-rasm).

Shakl: 20. Ustun maydonidagi maydonlarning tartibini ustun paydo bo'lishi uchun sozlang Reja rejasi

Anjir bilan solishtiring. 14. U erda, KOLONN maydonida birinchi maydon bo'lgan sanaBu birinchi navbatda ustunlar sana bo'yicha va har oy ichida reja / fakt bo'yicha guruhlanganligiga olib keldi. Endi birinchisi - bu Indikator maydoni va xulosada ustunlar birinchi Reja, ichida oylar bo'yicha tartiblangan va keyin barcha ustunlar Fakt.

Hisobot konvertining varag'iga qaytib, O6 katakchada turing, \u003d (teng belgi) yozing va shimoliy-sharqiy mintaqaning rejalashtirilgan natijalariga mos keladigan, burilish jadvali varag'idagi N12 katakchaga murojaat qiling. Ustiga bosing Kirish... Natijada \u003d GET.DATA.PERSONAL.TABLES ("Daromad"; "Shakl 11-14"! $ A $ 3; "Region"; "Shimoliy-Sharq"; "Indikator"; "Reja") formulasi olinadi. Uni tahrirlash: \u003d GET. DATA.SUMMARY.TABLES ("Daromad"; ’Shakl 11-14 ′! $ A $ 3;" Region "; $ A6;" Indikator "; O $ 5). Ushbu formulani O ustunidagi boshqa katakchalarga nusxalash (21-rasm). Iltimos, e'tibor bering, agar siz PivotTable hisobotining turli sohalarini siljitsangiz ham, o'ram to'g'ri ishlaydi. Albatta, siz bir nechta burilish maydonlarini harakatsiz qilsangiz, qobiq bunga dosh berolmaydi ...

Shakl: 21. Menejerga taqdim etilishi mumkin bo'lgan yakuniy hisobot

Endi dinamik PivotTable jadvalidagi qiymatlardan foydalanadigan yaxshi formatlangan hisobot paketi mavjud. Hisobotni yaratish juda ko'p vaqt talab etgan bo'lsa-da, uni yangilash atigi bir necha daqiqa davom etdi.

Hisobotni yangilash.Hisobotni kelgusi oylar ma'lumotlari bilan yangilash uchun quyidagi amallarni bajaring.

Haqiqiy raqamlarni asl ma'lumotlar to'plamining ostiga qo'ying. Dastlabki ma'lumotlar jadval formatida bo'lganligi sababli jadvalni formatlash avtomatik ravishda yangi ma'lumotlar qatorlariga tarqaladi. Bundan tashqari, asl PivotTable-ning ta'rifini kengaytiradi (Excel faylida men butun yil uchun haqiqiy ko'rsatkichlarni qo'shib qo'ydim).
Asosiy jadvalga o'ting. Sichqonchaning o'ng tugmachasini bosing va tanlang Yangilang... Burilish jadvalining ko'rinishi o'zgaradi, ammo bu yaxshi.
Hisobot qobig'iga o'ting. Aslida, hisobotni yangilash uchun hamma narsa qilingan, ammo natijalarni sinab ko'rish zarar qilmaydi. Masalan, A2 katakchadagi formulani quyidagicha o'zgartiring: \u003d EON MONTHS (BUGUN ()) +31 ; 0) va nima bo'lishini ko'ring.

Har oyda yangi real savdo ma'lumotlarini qo'shib, siz formatlar, formulalar va boshqalarni qayta tiklashdan xavotir olmaysiz. Hisobotni yangilashning ta'riflangan jarayoni shunchalik oddiyki, oylik hisobotlarni tayyorlash paytida yuzaga kelgan muammolarni abadiy unutasiz. Agar kompaniya qayta tashkil etilsa, natijada asosiy jadvalda yangi hududlar paydo bo'lishi mumkin bo'lgan yagona muammo paydo bo'lishi mumkin. Formulalarning to'g'ri ishlashiga ishonch hosil qilish uchun hisobotingizdagi umumiy summaning asosiy jadvaldagi jami bilan mos kelishini tekshiring. Yangi mintaqa paydo bo'lganda, uni faqat o'ralgan varaqqa qo'shing va tegishli formulalarni torting.

Men hech qachon quyidagi so'zlarni aytaman deb o'ylamagan edim: "GET.DATA.PERSONAL.TABLES funktsiyasi eng buyuk ne'matdir. Ilgari biz u holda qanday qilib mavjud edik? "

Aslini olib qaraganda, Jelenaning dastlabki ma'lumotlari shunday tuzilganki, keyingi formulalar faqat 2015 yil iyul oyida to'g'ri ishlagan. Ushbu yozuvga biriktirilgan Excel faylida men asl ma'lumotni va ba'zi formulalarni o'zgartirganman. siz biriktirilgan Excel fayli bilan tajriba o'tkazasiz. Afsuski, formulalar murakkab bo'lishi kerak edi.

Ilgari men odatiy havola o'rniga pivot jadval katakchasiga murojaat qilganda GET.PIVOTTABLE DATA funktsiyasi qaytarilganligi haqida gapirdim (qarang). Agar sizga qiziq bo'lsa kabi ushbu noqulaylikni bartaraf etish uchun men ushbu eslatmaga murojaat qilishni maslahat beraman. Agar siz hayron bo'lsangiz, nima uchun bu shunday bo'ladi, shuningdek GET DATA.COMPLISHED TABLE funktsiyasining ijobiy tomonlari qanday bo'lsa, men Jelen, Aleksandr kitobining bir qismini taklif qilaman. (15-bob). Ko'rib chiqilayotgan texnik sizga pivot jadvallari foydalanuvchilari uchun bosh og'rig'ini keltirib chiqaradigan ko'plab muammolarni engishga imkon beradi, xususan:

PivotTable yangilanganida, avval qo'llanilgan formatlash yo'qoladi. Raqam formatlari yo'qolgan. Ustun kengligini sozlash natijalari yo'qoladi.
Asimmetrik PivotTable yaratishning oson usuli yo'q. Yagona variant - nomlangan to'plamlardan foydalanish, ammo bu usul faqat oddiy modeldagi Pivot Stollardan emas, balki "Model Model Pivot Stables" dan foydalanadiganlar uchun mavjud.
Excel shablonlarni eslay olmaydi. Agar siz PivotTables-ni qayta-qayta tiklashingiz kerak bo'lsa, siz qayta guruhlashingiz, hisoblangan maydonlar va a'zolarni qo'llashingiz va shunga o'xshash boshqa bir qator vazifalarni bajarishingiz kerak bo'ladi.

Yozuvni formatda yoki misollarni formatida yuklab oling

Xo'sh, tartibda boshlaylik.

Muammoli funktsiyadan qanday qilib voz kechish mumkin DATA. Shaxsiy jadvallar

Shaklda ko'rsatilgan burilish jadvalida deylik. 1, siz 2015 va 2014 yillardagi ma'lumotlarni taqqoslashingiz kerak.

Shakl: 1. Asl pivot jadvali

D3 katakka "% Balandligi" sarlavhasini qo'shing.
Formatni C3 katakchadan D3 katakka nusxalash.
D4 katakchaga teng belgini kiriting.
C4 katakchani bosing.
Bo'linish amalini ko'rsatish uchun / (oldinga slash) belgisini kiriting.
B4 katakchani bosing.
-1 raqamini kiriting va tugmalar birikmasini bosing bitta kamerada qolish. Natijani foiz sifatida formatlang. G'arbiy mintaqada daromadning 43,8% pasayishi kuzatilganini ko'rasiz (2-rasm). Juda yaxshi natijalar emas.
Birinchi formulani kiritishni tugatgandan so'ng, D4 katakchani tanlang.
Hujayraning pastki o'ng burchagida joylashgan kichik kvadrat ustiga ikki marta bosing. Ushbu kvadrat hisobotdagi butun ustunni to'ldirish uchun formuladan nusxa olish uchun foydalanishingiz mumkin bo'lgan to'ldirish dastagini aks ettiradi.

Formulani nusxalashni tugatgandan so'ng, ekranga qarab, nimadir noto'g'ri ekanligini tushunasiz - har bir mintaqa yil davomida 43,8% pasayishni ko'rsatdi (3-rasm).

Shakl: 3. Formulani ustundagi barcha katakchalarga nusxalashni tugatgandan so'ng, har bir mintaqada 43,8% pasayish kuzatilganini ko'rasiz

Excel 2000 yillarida bunday narsa yuz bermagan. GET.DATA.PERSONAL.TABLES funktsiyasini muntazam ravishda uchratishni boshlaganimda, shunchaki nafratlanardim. Seminarlardan birida kimdir mendan uni qanday qilib yaxshi maqsadda ishlatish mumkinligini so'raganida, men dovdirab qoldim. Men hech qachon o'zimga bunday savol bermaganman! Mening fikrimcha va aksariyat Excel foydalanuvchilari fikriga ko'ra GET.DATA.PUMP.TABLE funktsiyasi yaxshilik kuchlari bilan hech qanday aloqasi bo'lmagan yovuzlik mahsuli edi. Yaxshiyamki, ushbu xususiyatni o'chirishning ikkita usuli mavjud.

D4 katakchaga o'ting va \u003d (teng belgi) yozing.
C4 raqamini kiriting.
A / (bo'linish uchun oldinga chiziq) kiriting.
B4 kiriting.
-1 raqamini kiriting.
Ustiga bosing Kirish.

Endi siz odatdagi Excel formulasini yaratdingiz, uni ustun ostidagi katakchalarga ko'chirishingiz va u bilan to'g'ri natijalarni olishingiz mumkin (5-rasm). Ko'rib turganingizdek, siz O'rnatilgan jadval ichidagi ma'lumotlarga havola qiladigan formulalar yaratishingiz mumkin. Va buning mumkinligiga ishonmaydiganlar, ta'riflangan harakatlarni o'zlari bajarishga ijozat bersinlar.

Shakl: 5. Faqat klaviaturadan \u003d C4 / B4-1 raqamini kiriting va formulalar kerakli darajada ishlaydi

PivotTables-ni yaxshilash uchun GET PIVOTTABLE DATA-dan foydalanish

Pivot jadvallar insoniyatning ajoyib ixtirosi. Burilish jadvali faqat bir necha marta bosish bilan yaratilgan bo'lib, rivojlangan filtr, BDSUMM funktsiyasi va ma'lumotlar jadvallarini qo'llash zaruriyatini yo'q qiladi. Asosiy jadvallar yordamida siz katta hajmdagi ma'lumotlardan bir sahifali hisobotlarni yaratishingiz mumkin. Ushbu afzalliklar pivot jadvallarining ba'zi kamchiliklarini soya soladi, masalan, noaniq formatlash va qo'shimcha sozlash uchun pivot jadvallarini qiymatlarga aylantirish zaruriyati. Shakl. 8-rasmda pivot jadvalini yaratish uchun odatiy jarayon ko'rsatilgan. Bunday holda, hamma narsa dastlabki ma'lumotlardan boshlanadi. Biz pivot jadvalini yaratamiz va uni sozlash va takomillashtirish uchun barcha mumkin bo'lgan usullardan foydalanamiz. Ba'zan biz pivot jadvalini qiymatlarga aylantiramiz va yakuniy formatlashni amalga oshiramiz.

Keyingi bo'limlarda o'tgan oylarning haqiqiy ko'rsatkichlari va kelgusi oylarning maqsadlari aks ettirilgan dinamik hisobotni qanday yaratish haqida gap boradi.

Ibtidoiy pivot jadvalini yarating.Dastlabki ma'lumotlar (10-rasm) kompaniyaning filiallari joylashgan har bir mintaqa uchun rejalashtirilgan va haqiqiy ko'rsatkichlar to'g'risidagi ma'lumotlarni o'z ichiga olgan bitimlar shaklida taqdim etiladi. Rejalashtirilgan ko'rsatkichlar oy darajasida, haqiqiy ko'rsatkichlar esa alohida kunlar darajasida batafsil bayon etilgan. Rejalashtirilgan ko'rsatkichlar kelgusi yil uchun, dolzarb ko'rsatkichlar o'tgan oylar uchun tuziladi. Hisobot har oy yangilanib turishi sababli, quyida yangi ma'lumotlar qo'shilganda PivotTable ma'lumot manbasi hajmi kattalashsa, bu jarayon ancha soddalashtiriladi. Excelning eski versiyalarida bunday ma'lumotlar manbai OFFSET funktsiyasidan foydalangan holda nomlangan dinamik diapazon yordamida yaratilgan (batafsil ma'lumotga qarang). Excel 2013 da ishlash paytida ma'lumotlar katakchasidan birini tanlang va Ctrl + T tugmalar birikmasini bosing (Jadval yarating). Bu yangi qatorlar va ustunlar qo'shilganda avtomatik ravishda kengayadigan nomlangan ma'lumotlar to'plamini yaratadi.

Asosiy jadvalni yarating:

Jamoani tanlang Kiritmoq → Umumiy jadvalva keyin dialog oynasida Qaytgan jadval yaratishbosing OK.
PivotTable Field ro'yxatida maydonni tanlang sana... Burilish jadvalining chap tomonida sanalar ro'yxati paydo bo'ladi (11-rasm).
Har qanday sana katakchasini tanlang, masalan A4. Kontekst yorlig'ida Tahlilkontekstli yorliqlar to'plamida joylashgan Burilish jadvallari bilan ishlashtugmasini bosing Maydonlar bo'yicha guruhlash (batafsil ma'lumotga qarang). Muloqot oynasida Guruhlash variantni tanlang Oylar (12-rasm). Bosing OK... Burilish stolining chap tomonida oylarning nomlari paydo bo'ladi (13-rasm).
Maydonni torting sana PivotTable-ning ustunlar maydoniga.
Maydonni torting Indeks PivotTable Field ro'yxatining ustunlar maydoniga.
Maydonni tanlang Mintaqapivot jadvalining chap ustunida ko'rsatilishi kerak.
Maydonni tanlang DaromadPivotTable Values \u200b\u200bmaydonida paydo bo'ladi.

Shakl: 11. Maydonlar bo'yicha guruhlash bilan boshlang sana

Ayni paytda bizning asosiy jadvalimiz juda ibtidoiy ko'rinadi (14-rasm). Men, albatta, xat yozishni yoqtirmayman Qator nomlari va Ustun nomlari... Umumiy ko'rsatkichlarni ko'rsatish maqsadga muvofiq emas Yan rejasi va Yan Fakt D ustunida va boshqalar. Ammo bu asosiy jadvalning paydo bo'lishi haqida tashvishlanmang, chunki uni sizdan boshqa hech kim ko'rmaydi. Shu vaqtdan boshlab biz yangi yaratilgan pivot jadvali bo'lgan ma'lumotlar manbai bo'lgan hisobot qobig'ini yaratamiz.

Quyidagi tarzda harakat qiling (15-rasm).

A1 katakka hisobot nomini kiriting - Mintaqalar bo'yicha rejalashtirilgan va haqiqiy ko'rsatkichlar.
Yorliqqa o'ting uy, tugmachani bosing Hujayra uslublari formatni tanlang Sarlavha 1.
A2 katakchaga \u003d EON MONTHS (BUGUN (); 0) formulani kiriting. Ushbu funktsiya joriy oyning oxirgi kunini qaytaradi. Masalan, 2014 yil 14 avgustda ushbu satrlarni o'qigan bo'lsangiz, A2 katakchada 2014 yil 31 avgust sanasi ko'rsatiladi.
A2 katakchani tanlang. Muloqot oynasini ko'rsatish uchun Ctrl + 1 tugmalar birikmasini bosing Hujayra formati... Yorliqda Raqam elementni bosing Barcha formatlar... Sifatida maxsus raqam formatini kiriting "Oydan" MMMM "rejalashtirilgan ko'rsatkichlar" (16-rasm). Natijada hisoblangan sana matnga o'xshaydi.
A5 katakka sarlavha kiriting Mintaqa.
A ustunidagi qolgan katakchalarga mintaqa sarlavhalarini kiriting. Mintaqa sarlavhalari burilish jadvalida ko'rsatilgan mintaqa nomlariga mos kelishi kerak.
Agar kerak bo'lsa, bo'lim bo'yicha jami ustunlarga yorliqlar qo'shing.
Hisobotning pastki qismida qatorni qo'shing Kompaniya uchun jami.
B4 katakka \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1) formulasini kiriting. Ushbu formulada joriy yilning barcha 12 oyining birinchi kunlari 01/01/2014, 01/02/2104 va boshqalar sanalari qaytariladi.
B4 katakchani tanlang. Oynani ochish uchun Ctrl + 1 tugmalar birikmasini bosing Hujayra formati... Yorliqda Raqam Bobda Barcha formatlar maxsus raqam formatini kiriting Mmm... Ushbu format uch harfli oy nomini ko'rsatadi. Matnni katakning o'ng tomoniga tekislang.
B4 katakchaning tarkibini C4: M4 diapazoniga nusxalash. Asosiy stolning yuqori qismida oylarning nomlari ko'rsatilgan qator paydo bo'ladi.
B5 katakka \u003d IF (MONTH (B4)) formulasini kiriting<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Faktva hozirgi va kelajak uchun - Reja.
N5 katakka sarlavha qo'shing Natija... O4 katakchaga - Natija, O5 - Reja, P5 - % og'ish.
Bo'lim yig'indilarini, kompaniyaning umumiy qatorini, umumiy ustunini va% dispersiya ustunini hisoblash uchun ishlatiladigan odatdagi Excel formulalarini kiriting:
1. b8 katagiga \u003d SUM (B6: B7) formulasini kiriting va uni qatordagi boshqa katakchalarga nusxalash;
2. n6 katakchaga \u003d SUM (B6: M6) formulasini kiriting va uni ustundagi boshqa katakchalarga nusxalash;
3. p6 katakchaga \u003d IFERROR ((N6 / O10) -1; 0) formulasini kiriting va uni ustundagi boshqa katakchalarga nusxalash;
4. b13 katakchaga \u003d SUM (B10: B12) formulani kiriting va uni qatordagi boshqa katakchalarga nusxalash;
5. b17 katakchaga \u003d SUM (B15: B16) formulasini kiriting va uni qatordagi boshqa katakchalarga nusxalash;
6. b19 katakchaga \u003d SUM (B6: B18) / 2 formulasini kiriting va uni qatordagi boshqa katakchalarga nusxalash.
Sarlavha 4 uslubini A ustunidagi yorliqlarga va 4 va 5 qatorlar sarlavhalariga qo'llang.
B6: O19 kataklar oralig'i uchun # ## 0 raqamli formatni tanlang.
P ustunidagi kataklar uchun raqam formatini 0,0% tanlang.

Shakl: 15. GET.DATA.PERSONAL.TABLES formulalarini qo'shishdan oldin doka haqida xabar bering

Hisobot qobig'ining B6 katagini tanlang. Ushbu katak Shimoliy-Sharqiy mintaqaga va yanvar oyining haqiqiy ko'rsatkichlariga to'g'ri keladi.

Formulani kiritishni boshlash uchun \u003d (teng belgi) kiriting.
Burilish jadvalidagi varaqqa o'ting va shimoliy-sharqiy mintaqaga va yanvar - C12 uchun haqiqiy ko'rsatkichlarga mos keladigan katakchani bosing (17-rasm).
Tugmani bosing Kirishformulani kiritishni tugatish va hisobot qobig'iga qaytish. Natijada, Excel B6 katakchasiga GET PIVOTTABLE DATA funktsiyasini qo'shadi. Yacheykada 277 435 dollar qiymat ko'rsatiladi.

Funktsiya argumentlari:

Ma'lumotlar maydoni. Burilish jadvalining qiymat maydonidan maydon. Iltimos, diqqat qiling: bu holda maydon ishlatiladi Daromad, lekin emas Daromad sohasidagi summa.
Umumiy jadval. Ushbu parametr yordamida Microsoft sizdan "Qaysi pivot jadvalidan foydalanmoqchisiz?" Asosiy jadval hujayralaridan birini ko'rsatish kifoya. Kirish "Shakl. 11-14 '! $ A $ 3 ma'lumotlar kiritiladigan pivot jadvalidagi birinchi katakchani anglatadi. Bizning holatimizda siz pivot jadvaliga tegishli har qanday katakchani belgilashingiz mumkin, argumentni o'zgarishsiz qoldiring. Uyali telefon manzili $ 3 $ har jihatdan mos keladi.
1-maydon; element 1. Avtomatik ravishda yaratilgan formulada maydon nomi tanlanadi Mintaqava maydon qiymati sifatida - Shimoli-sharq... GET DATA.PUMP.TABLE funktsiyasi bilan ishlashda yuzaga keladigan muammolarning sababi shu erda. Avtomatik tanlangan qiymatlarni nusxalash mumkin emas, chunki ular qattiq kodlangan. Shuning uchun, agar siz formulalarni butun hisobot maydoniga nusxa ko'chirsangiz, ularni qo'lda o'zgartirishingiz kerak bo'ladi. Shimoliy-sharqni $ A6 shaklidagi katakka mos yozuvlar bilan almashtiring. A ustunining oldiga dollar belgisini qo'yib, formulani ustundagi katakchalarga nusxalashda ma'lumotnomaning satr qismini o'zgartirish mumkinligini aniqlaysiz.
2-maydon; modda 2. Ushbu juft argument maydonni belgilaydi sana qiymati 1 bilan. Agar asl PivotTable oylar bo'yicha guruhlangan bo'lsa, oy maydoni asl maydon nomini saqlab qoladi sana... Oyning raqamli qiymati 1 ga teng, bu yanvarga to'g'ri keladi. Bunday qiymatdan o'nlab, hatto yuzlab hisobot hujayralarida ko'rsatilgan ulkan formulalarni yaratishda ishlatish maqsadga muvofiq emas. Maydon qiymatlarini hisoblaydigan formuladan foydalanish yaxshiroqdir sana, B4 katakchadagi formula kabi. Bunday holda 1 o'rniga, siz MONTH (4 dollar ichida) formulasidan foydalanishingiz mumkin. 4-dan oldingi dollar belgisi formulaning maydonga qiymatlarni belgilashi mumkinligini ko'rsatadi sana qator boshqa katakchalarga formuladan nusxa ko'chirilganligi sababli boshqa oylarga asoslanadi.
3-maydon; element 3. Bu holda maydon nomi avtomatik ravishda tayinlanadi Indeks va maydon qiymati Fakt... Ushbu qiymatlar yanvar oyi uchun to'g'ri keladi, ammo keyingi oylar uchun maydon qiymati Rejaga o'zgartirilishi kerak. Maydonning qattiq kodlangan qiymatini o'zgartiring Fakt B $ 5 havolasiga.
4-maydon; 4-element. Ushbu dalillardan foydalanilmaydi, chunki dalalar tugadi.

Shakl: 19. Tahrirlash tugagandan so'ng, GET.DATA.PERSONAL.TABLE formulasi diapazonning barcha katakchalariga nusxalash uchun javob beradi.

Formulani natijalar hisoblangan B: M ustunlaridagi bo'sh katakchalarga nusxalash. Endi hisobotda haqiqiy raqamlar mavjud bo'lib, siz ustun kengliklariga yakuniy tuzatishlar kiritishingiz mumkin.

Shakl: 20. Ustun maydonidagi maydonlarning tartibini ustun paydo bo'lishi uchun sozlang Reja rejasi

Shakl: 21. Menejerga taqdim etilishi mumkin bo'lgan yakuniy hisobot

Hisobotni yangilash.Hisobotni kelgusi oylar ma'lumotlari bilan yangilash uchun quyidagi amallarni bajaring.

Haqiqiy raqamlarni asl ma'lumotlar to'plamining ostiga qo'ying. Dastlabki ma'lumotlar jadval formatida bo'lganligi sababli jadvalni formatlash avtomatik ravishda yangi ma'lumotlar qatorlariga tarqaladi. Bundan tashqari, asl PivotTable-ning ta'rifini kengaytiradi (Excel faylida men butun yil uchun haqiqiy ko'rsatkichlarni qo'shib qo'ydim).
Asosiy jadvalga o'ting. Sichqonchaning o'ng tugmachasini bosing va tanlang Yangilang... Burilish jadvalining ko'rinishi o'zgaradi, ammo bu yaxshi.
Hisobot qobig'iga o'ting. Aslida, hisobotni yangilash uchun hamma narsa qilingan, ammo natijalarni sinab ko'rish zarar qilmaydi. Masalan, A2 katakchadagi formulani quyidagicha o'zgartiring: \u003d EON MONTHS (BUGUN ()) +31 ; 0) va nima bo'lishini ko'ring.

Men hech qachon quyidagi so'zlarni aytaman deb o'ylamagan edim: "GET.DATA.PERSONAL.TABLES funktsiyasi eng buyuk ne'matdir. Ilgari biz u holda qanday qilib mavjud edik? "

Burilish jadvallari uchun GET.DATA.PIVOTTABLE funktsiyasi - bu pivot jadvali hisobotida saqlangan ma'lumotlarni qaytaradigan funktsiya.

Funktsiyaga tez kirish uchun siz (\u003d) katakchaga teng belgini kiritishingiz va burilish jadvalidagi kerakli katakchani tanlashingiz kerak. Excel avtomatik ravishda GET PIVOTTABLE DATA funktsiyasini yaratadi.

GetPivotData yaratishni o'chirib qo'ying

GET PIVOTTABLE funktsiyasini avtomatik ravishda ishlab chiqarishni o'chirish uchun pivot jadvalidagi istalgan katakchani tanlang, yorliqni bosing Asosiy jadvallar bilan ishlash -\u003e Variantlarguruhga Umumiy jadval.Yorliq yonidagi pastga o'qni bosing Parametrlar.Ochilgan menyuda katakchani olib tashlang YaratingGetPivotData.

GET DATA.PUMP.TABLE funktsiyasida uyali ma'lumotlardan foydalanish

GET.PUMP.TABLE funktsiyasida elementlar yoki maydonlarning nomlarini ko'rsatish o'rniga, varaqdagi katakchalarga murojaat qilishingiz mumkin. Quyidagi misolda E3 katakchada mahsulot nomi bor va E4 katakchadagi formulada unga ishora qilinadi. Natijada, tortlarning umumiy hajmi qaytarib beriladi.

PivotTable Field ma'lumotnomalaridan foydalanish

Qaytgan jadval elementlariga havolalar qanday ishlashi haqida savollar yo'q; agar ma'lumotlar maydoniga murojaat qilmoqchi bo'lsak, muammolar paydo bo'ladi.

Misolda, E3 katakchada ma'lumotlar maydoni nomi "Miqdor" mavjud bo'lib, GET.DATA.PUMP.TABLE formulasida maydon nomiga ega bo'lish o'rniga, funktsiyadagi ushbu katakka murojaat qilish yaxshi bo'lar edi.

Ammo, agar biz birinchi dalilni o'zgartirsak ma'lumotlar maydonie3 katakchasiga havola, Excel bizga #REF xatosini qaytaradi!

GET.PERSONAL.TABLE DATA (E3; $ A $ 3)

Muammo shunchaki bo'sh satr ("") katakchaning boshiga yoki oxiriga qo'shilishi bilan hal qilinadi.

RECEIVE.SUMMARY.TABLE.DATA (E3 & ""; $ A $ 3)

Formulaga oddiy tuzatish to'g'ri qiymatni qaytaradi.

GET DATA.PUMP.TABLE funktsiyasida sanalardan foydalanish

GET PIVOTTABLE DATA funktsiyasida sanalardan foydalansangiz, sana PivotTable-da ko'rsatilgan bo'lsa ham, sizda muammolar bo'lishi mumkin. Masalan, quyida keltirilgan formulada "21.04.2013" sana ko'rsatilgan va pivot jadvalida sotilish sanalari ko'rsatilgan maydon mavjud. Biroq, E4 katakchadagi formula xatolikni qaytaradi.

GET.DATA.COMPLETE.TABLE ("Jild"; $ A $ 3; "Sana"; "21.04.2013")

Sana bilan bog'liq xatolarga yo'l qo'ymaslik uchun siz quyidagi usullardan birini qo'llashingiz mumkin:

Sana formatlarini formulalar va asosiy jadvallar bilan taqqoslang
DATEVALUE funktsiyasidan foydalaning
DATE funktsiyasidan foydalaning
To'g'ri sanasi ko'rsatilgan katakchaga murojaat qiling

Sana formatlarini formulalar va asosiy jadvallar bilan taqqoslang.

To'g'ri natijani olish uchun GET PIVOTTABLE DATA funktsiyasidan foydalanganda formula argumenti va PivotTable jadvalining sana formatlari bir xil ekanligiga ishonch hosil qiling.

E4 katakchada formula "DD.MM.YYYY" sana formatidan foydalanadi va natijada to'g'ri ma'lumotlarni qaytaradi.

DATEVALUE funktsiyasidan foydalanish

Formulaga sanani qo'lda kiritish o'rniga, sanani qaytarish uchun DATEVALUE funktsiyasini qo'shishingiz mumkin.

E4 katakchasiga sana DATEVALUE funktsiyasi yordamida kiritiladi va Excel kerakli ma'lumotlarni qaytaradi.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Sana"; DATEVALUE ("04.21.2013"))

DATE funktsiyasidan foydalanish

Formulaga sanani qo'lda kiritish o'rniga, kerakli ma'lumotlarni to'g'ri qaytarishga imkon beradigan DATE funktsiyasidan foydalanishingiz mumkin.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Sana"; DATE (2013; 4; 21))

Sana katakchasiga havola

Sana formulaga qo'lda kiritish o'rniga, sana bo'lgan katakka murojaat qilishingiz mumkin (Excel ma'lumotni sana deb sharhlaydigan har qanday formatda). E4 katakchadagi misolda formulalar E3 katakchaga ishora qiladi va Excel to'g'ri ma'lumotlarni qaytaradi.

RECEIVE.SUMMARY.TABLE.DATA ("Jild"; $ A $ 3; "Sana"; E3)