Excel pivot jadvali qatorlar bo'yicha jami. "Eng yaxshi o'nlik" ma'lumotlarini ko'rsatish. Ma'lumotlarni batafsil ko'rsatish

Qaytgan jadvallarni bezovta qiladigan narsalardan biri shundaki, ma'lumotlar yig'indisi umumiy jadval har doim jadvalning pastki qismida joylashgan bo'lib, demak siz raqamlarni ko'rish uchun butun jadval bo'ylab aylanib o'tishingiz kerak. Keling, umumiy summani topish osonroq bo'lgan joyga ko'chiraylik.

Qaytgan jadvallar ma'lumotlarni sarhisob qilish va mazmunli ma'lumotlarni ta'kidlash uchun juda yaxshi vosita bo'lsa-da, ular umumiy summani topish oson bo'lgan joyga ko'tarish uchun ichki funktsiyaga ega emas. Katta summani yuqoriga ko'tarishning umumiy usulini tavsiflashdan oldin, avval aylanma jadvaldan ma'lumotlarni olish uchun mo'ljallangan GETPIVOTDATA funktsiyasi yordamida buni qanday amalga oshirishingizni ko'rib chiqamiz.

Ushbu funktsiyadan quyidagicha foydalanish mumkin: \u003d GETPIVOTDATA ("Summa summasi"; $ B $ 5), Excelning rus tilidagi versiyasida \u003d GET.DATA.PERSONAL.TABLE ("Maydonlar bo'yicha summa"; $ B $ 5) yoki shunga o'xshash: \u003d GETPIVOTOATA ("Miqdor"; $ B $ 5), Excelning rus tilidagi versiyasida GET.DATA.COMPLETE.TABLES ("Miqdor"; $ In $ 5).

Ikkala funktsiya ham kerakli ma'lumotlarni ajratib turadi va umumiy, yuqoriga, pastga, chapga yoki o'ngga siljitish paytida kuzatib boradi. Biz $ B $ 5 katak manzilidan foydalandik, lekin agar siz pivot jadvalidagi biron bir katakni ko'rsatsangiz, har doim ham jami bo'ladi.

Birinchi funktsiya "Sum summasi" maydonini, ikkinchisi "summa" maydonini ishlatadi. Agar PivotTable-ning Ma'lumotlar maydonida Miqdor maydoni bo'lsa, maydonni Miqdor deb nomlashingiz kerak. Agar Ma'lumotlar maydonida Miqdor maydoni bir necha marta ishlatilsa, siz unga o'zingiz tayinlagan ismni yoki standart ismni ko'rsatishingiz kerak (4.5-rasm).

Ushbu maydonlarni o'zgartirish uchun ularni ikki marta bosish kerak. Agar siz hali ham asosiy jadvallarni to'liq tushunmasangiz, bu chalkash bo'lishi mumkin. Yaxshiyamki, Excel 2002 va undan keyingi versiyalarda jarayon ancha osonlashadi, chunki sichqoncha yordamida argumentlarni katakchaga joylashtirish va to'g'ri sintaksisni qo'llash mumkin. Har qanday katakka \u003d (teng belgi) kiriting va umumiy yig'indisi bo'lgan katakchani bosing. Excel avtomatik ravishda kerakli argumentlarni kiritadi.

Afsuski, agar siz Funktsiya ustasini ishlatsangiz yoki birinchi \u003d GETPIVOTDATA ()) turini kiritsangiz va keyin umumiy sonni o'z ichiga olgan katakchani bosgan bo'lsangiz, Excel ushbu katakchaga yana bitta GETPIVOTDATA funktsiyasini kiritishga urinib, tartibsizliklarni keltirib chiqaradi.


Katta summani olishning eng oson va kamroq chalkash usuli bu \u003d MAX (PivGTCol) funktsiyasidan foydalanish, ya'ni Excel \u003d MAKC (PivGTCol) ning rus tilidagi versiyasida, bu erda katta jamlangan ustun PivGTCol deb nomlangan.

Bunga qo'shimcha ravishda, siz ularning kattaligiga qarab pivot jadvalidan raqamlar to'plamini olish uchun LARGE va SMALL funktsiyalaridan foydalanishingiz mumkin. Masalan, quyidagi formula asosiy jadvaldan ikkinchi eng katta sonni tanlaydi: \u003d LARGE (PivGTCol; 2), Excel rus tilidagi versiyasida \u003d LARGE (PivGTCol; 2).

Siz darhol Dastur jadvalining ustiga bir nechta satrlarni qo'shishingiz va ushbu formulalarni o'sha erda joylashtirishingiz mumkin, shunda siz ushbu turdagi ma'lumotlarni darhol PivotTable-ning pastki qismiga o'girmasdan ko'rishingiz mumkin.

Burilish jadvallari bilan ishlash uchun maxsus asboblar paneli mavjud PivotTable , bu sizga pivot jadvallarini tahrirlash va formatlash bilan bog'liq asosiy operatsiyalarni tezda bajarishga imkon beradi.

Ochiladigan menyu PivotTable asboblar paneli panelning o'zida va boshqa ba'zi buyruqlarda bajariladigan operatsiyalarni o'z ichiga oladi. Masalan, pastki menyudan foydalanish Formulalar siz hisoblangan maydonni yaratishingiz va hisoblash tartibini belgilashingiz mumkin.

Jamoa Tanlovlar pivot jadvali nomini belgilash, namoyish qilish va formatlash parametrlarini, shuningdek jadval tashqi ma'lumotlar manbasida qurilgan bo'lsa, tashqi ma'lumotlar parametrlarini sozlash imkonini beradi.


Ma'lumotlarni chiqarish.

Burilish jadvalida ma'lumotlarni ta'kidlashning ikkita rejimi mavjud: Siz jadval qismlarini tanlashingiz yoki oddiy tanlovdan foydalanishingiz mumkin. Odatiy bo'lib, tanlov rejimi pivot jadvalining barcha qismlari uchun yoqilgan. Ushbu rejimda maydon sarlavhasini bosish orqali siz uni maydon elementlari bilan birga tanlaysiz va maydon elementini bosganingizda ushbu elementga mos keladigan ma'lumotlar tanlanadi. Bundan tashqari, pivot jadvalining qismlarini ajratib ko'rsatish bilan siz jadvalning sarlavhalarini ham, ma'lumotlarning o'zi ham ajratib ko'rsatasiz. Asboblar panelidagi mos vositani tanlab, faqat sarlavhalarni yoki faqat ma'lumotlarni tanlashingiz mumkin PivotTable :

Buyruq yordamida pivot jadvalining qismlarini tanlash yo'lini ham belgilashingiz mumkin Tanlang ochiladigan menyu PivotTable asboblar panelida. Bu erda buyruqni tanlash orqali butun jadvalni bir butun sifatida tanlashingiz mumkin Butun jadval ... Oddiy tanlov rejimiga o'tish uchun (ya'ni qismlarni emas, balki jadvalning katakchalarini tanlang), tugmani bosing xuddi shu menyuda. Burilish jadvali tuzilmasi yangilanganidan va o'zgartirilgandan so'ng uning mintaqalari formatlarini saqlash uchun tugma Tanlashni yoqish bosilishi kerak.


Maydonlar, maydon elementlari va jami yig'ilish jadvalidan o'chirilishi mumkin. Buning uchun jadvalning o'chiriladigan qismini tanlang (tugma) Tanlashni yoqish bosilishi kerak) va buyruqni tanlang O'chirish kontekst menyusidan.

Dastlabki ma'lumotlar yangilanmoqda.

Agar siz asl jadvallardagi ma'lumotlarni o'zgartirgan bo'lsangiz, ushbu ma'lumotlar asosida PivotTable-ni yangilashingiz kerak. Buning uchun tugmani bosing asboblar paneli PivotTable yoki buyruqni tanlang Ma'lumotlarni yangilang ochiladigan menyudan PivotTable ... Agar qatorlar (yoki qatorlarni birlashtirish holatida ustunlar) qo'shsangiz ma'lumotlar ham yangilanadi ichida original diapazonlar.

Agar qatorlar yoki ustunlar qo'shilgan bo'lsa ga asl diapazonlar, tugmachani bosish orqali pivot jadvali ustasini chaqirishingiz kerak O'zaro jadvallar ustasi asboblar panelida. Sehrgarning ikkinchi bosqichiga qaytsangiz, asl diapazonlarni bekor qilishingiz mumkin.

Umumiy va pastki natijalar.

Buyruqni tanlash orqali Tanlovlar ochiladigan menyudan PivotTable , Siz burilish jadvaliga satrlar va / yoki ustunlar bo'yicha jami kerak bo'ladimi-yo'qligini belgilashingiz mumkin. Agar burilish jadvalida sahifa maydoni bo'lsa va ushbu maydonning ba'zi qiymatlari yashiringan bo'lsa, bu erda siz jami sahifalar maydonlarining yashirin qiymatlariga mos keladigan ma'lumotlarni o'z ichiga oladimi-yo'qligini ham aniqlab olishingiz mumkin. Agar katakcha bo'lsa Yalpi sahifaning umumiy summasi (maxfiy qiymatlarni o'z ichiga oladi) o'chirilgan, sahifa maydonining barcha qiymatlariga mos keladigan asosiy jadvalni ko'rishda yashirin qiymatlar umumiy yig'indilarga kiritilmaydi.


Agar umumiy jadval bir nechta ustun yo'naltirilgan maydonlarga yoki bir nechta sahifaga yo'naltirilgan maydonlarga ega, foydalanuvchi har bir bunday maydon uchun subtallarni belgilashi mumkin.


Maydon uchun jami summalarni aniqlash uchun uni ikki marta bosish yoki belgini tanlash kifoya PivotTable maydoni asboblar panelida. Maydon uchun jami summalar tanlanishi mumkin

  • avtomatik - miqdori maydonning har bir elementi uchun hisoblab chiqiladi. Avtomatik jami faqat tashqi maydon uchun tanlanishi mumkin (bizning misolimizda ushbu maydon) yil);
  • ro'yxatdan tanlangan standart funktsiya yordamida hisoblangan (tashqi va ichki maydonlar uchun bir nechta bunday jami tanlashingiz mumkin);
  • oraliq natijalardan butunlay voz kechish.
Agar sizda jami summalar aniqlangan bo'lsa, har bir maydon elementi uchun subtotal ustun (satr) qo'shiladi. Agar jami bir nechta bo'lsa, har bir element uchun qo'shimcha ustunlar soni jami soniga ko'paytiriladi.
Ma'lumotlarni guruhlash.


Ehtimol, sizga barcha maydon elementlari uchun emas, balki maydon ichidagi ba'zi ma'lumotlar guruhlari uchun jami kerak bo'lishi mumkin (masalan, maydon elementlari oylarning nomlari va jami choraklar bilan umumlashtirilishi kerak). Bunday holda, maydon elementlarini ularni tanlab, belgini bosish orqali guruhlash mumkin Guruh asboblar panelida. Sizda yana bitta maydon bo'ladi, uning elementlari guruh sarlavhalari bo'ladi. Bundan tashqari, har bir guruhlanmagan element mustaqil guruhni tashkil qiladi, uning nomi element nomiga to'g'ri keladi. Yuqoridagi misolda to'rtta elementni o'z ichiga olgan bitta ustun maydoni mavjud: yanvar, fevral, mart va aprel... Uch element ( yanvar, fevral, mart) guruhlangan. Element aprel guruhga kiritilmagan. Natijada yana bir maydon paydo bo'ldi oy2ikkita elementni o'z ichiga oladi: 1-guruh va aprel... Maydon oy hali ham oy nomlarini o'z ichiga oladi: yanvar, fevral, mart va aprel... Maydon oy2 tashqi maydon, ya'ni. ichki maydonni guruhlaydi oy... Siz yangi maydon uchun jami natijalarni belgilashingiz mumkin va har bir ma'lumotlar guruhi uchun kerakli jami hisoblab chiqiladi. Maydon elementlarini guruhdan chiqarish uchun kerakli guruhni tanlang va belgini bosing Guruhdan chiqarish asboblar panelida. Agar guruhlangan narsalar qolmasa, qo'shimcha tashqi chekka yo'qoladi.


Hisoblangan maydonlar va maydon a'zolari.


Odatda jadval tuzilishi ma'lumotlarning bitta diapazonida yoki ob'ektlardan asl ro'yxatning maydon sarlavhalaridan yaratiladi Chiziq, Ustun, Qiymat, Sahifaagar bir nechta diapazon mavjud bo'lsa. Manba jadvallaridagi ma'lumotlarni tahlil qilish uchun mavjud maydonlar ko'pincha etarli emas. Masalan, pivot jadvalida ikkita ma'lumotlar maydoni mavjud: miqdor va sum, va siz ushbu ikki maydonning miqdori sifatida aniqlanadigan mahsulotning o'rtacha narxini aniqlashingiz kerak. Bunday holda, siz o'zingiz yaratishingiz kerak hisoblangan maydon - PivotTable-dagi boshqa maydonlarning qiymatlari asosida hisob-kitoblarni amalga oshirishga imkon beradigan foydalanuvchi tomonidan belgilangan formuladan foydalanadigan PivotTable maydoni. Buning uchun buyruqlarni ketma-ket tanlash kifoya Formulalar , Hisoblangan maydon ochiladigan menyudan PivotTable ... Ro'yxat ichidan tanlashingiz mumkin bo'lgan maydonlardan foydalanib, hisoblangan maydon uchun o'zingizning formulangizni yaratishingiz mumkin. PivotTable Wizard-dan foydalanib, siz yangi maydonni PivotTable-ning kerakli maydoniga joylashtirishingiz mumkin.




Ba'zan hisobotlarda nafaqat qo'shimcha maydonlar, balki maydon elementlari ham talab qilinadi. Bu sizga mavjud maydon elementlari ma'lumotlarini qandaydir tarzda tahlil qiladigan maydon kerak bo'lgan hollarda sodir bo'ladi. Masalan, qator satri yoki ustun maydoni elementlarni o'z ichiga oladi miqdor va sum, va siz ushbu ikki elementning miqdori sifatida aniqlangan mahsulotning o'rtacha narxini aniqlashingiz kerak. Bunday hollarda siz hisoblangan maydonni yaratmasligingiz kerak, lekin hisoblangan element - boshqa PivotTable maydon elementlari qiymatlari asosida hisob-kitoblarni amalga oshirishga imkon beradigan foydalanuvchi tomonidan yaratilgan formuladan foydalanadigan PivotTable maydon elementi. Hisoblangan elementni yaratish uchun siz yangi ob'ekt qo'shiladigan maydon yoki maydon elementini tanlashingiz va ketma-ket buyruqlarni tanlashingiz kerak Formulalar , Hisoblangan element ochiladigan menyudan PivotTable ... Shundan so'ng siz yangi elementning nomini ko'rsatishingiz va uni hisoblashingiz kerak bo'lgan formulani o'rnatishingiz kerak. Qo'shimcha elementlarni hisoblaydigan formulalar mavjud PivotTable maydon elementlari nomlaridan foydalanadi. Agar bir nechta maydonlarda bir xil nomdagi elementlar bo'lsa, siz maydon nomini element nomidan oldin kiritishingiz kerak. Hisoblangan ob'ektni yaratishdan oldin siz tanlagan maydonga (yoki siz kimning elementini tanlagan) yangi element qo'shiladi.

Excelni ma'lumotlarni qayta ishlash vositalari bilan to'ldirish uchun (qo'shimchalar) Hisobot menejeri va Yechim topish ), dialog oynasida Qo'shimchalar menyu Xizmat tegishli radio tugmachasini tekshiring va bosing OK (1-rasm).

Agar siz tanlangan plaginni topib bo'lmaydigan deb so'ralsa, u ehtimol o'rnatilmagan bo'lishi mumkin. Bunday holda, plaginni o'rnatish disketidan o'rnating.

Shakl.1. Muloqot oynasi Qo'shimchalar.

      Yakuniy hisobotlarni tuzish

Topshiriqning maqsadi: subtotallarni tuzishni o'rganing.

Masalan, korxona tomonidan uning bo'limlari uchun printerlar va skanerlarni sotib olish to'g'risidagi ma'lumotlarni o'z ichiga olgan jadval uchun qiymatlarni tahlilini o'tkazaylik (1-jadval). Ushbu muammoni oddiy formulalar yordamida hal qilish mumkin, ammo jami avtomatik hisoblash funktsiyasidan foydalanish uning bajarilishini sezilarli darajada soddalashtirishi mumkin.

1-jadval

Mahsulot

Ism

Narx

Raqam- yilda

Miqdor

Matritsa

Matritsa

Matritsa

Jet

Epson Stylus 1520

Jet

Epson Stylus 3000

Jet

Epson Stylus 1500

Jet

Epson Stylus Photo 700

Lazer

HP Laser Jet 4000

Lazer

HP Laser Jet 5000

Lazer

HP Laser Jet Color 8500

Varaq

Paragon Page 630

Varaq

Paragon sahifasi oson

Tabletka

Paragon 800IIEP

Tabletka

ScanExpress 6000 SP

Tabletka

Paragon 1200 SP

Tabletka

ScanExpress A3 P

Sotib olishga sarflangan mablag'ni jami avtomatik hisoblash yordamida barcha printerlar uchun alohida va barcha brauzerlar uchun alohida topamiz. Buning uchun:

    Jadval ma'lumotlarini, agar ular hali tartiblanmagan bo'lsa, avval mahsulot turlari bo'yicha saralash. Ikkinchi saralash mezonlari sifatida siz, masalan, Ism;

    Jadvalni yoki kamida bitta jadval katakchasini tanlang;

    Menyuda Ma'lumotlar jamoani tanlang Natija . Muloqot oynasi ochiladi Subtotallar (2.-rasm).

Ochiladigan ro'yxat Har qanday o'zgarish bilan yilda jadvaldagi har bir ma'lumot o'zgargandan so'ng oraliq jami hisoblashni istagan ustun sarlavhasini tanlang. (sizning holatingizda, elementni tanlashingiz kerak Mahsulot).Umumiy miqdorni aniqlashda ma'lumotlar sarhisob qilinishi uchun, ro'yxat maydonidan Ishlash funktsiyani tanlang Miqdor. Ushbu dialog oynasida siz jami hisoblash uchun kataklardan foydalaniladigan ustunni ham ko'rsatishingiz kerak. Bizning holatlarimizda, tovarlarning miqdori va sotib olishga sarflangan mablag'larning ko'rsatkichlarini sarhisob qilish uchun Hammasini qo'shish chiziqlar yonidagi katakchalarni belgilang Miqdor va Miqdor.

Funktsiyani bajarish natijasida jadval qatorlar bilan to'ldiriladi, ular har bir guruh uchun jami summalarni alohida-alohida aks ettiradi (3-rasm). Jadvalga kiritilgan so'nggi qatorda umumiy summa haqida ma'lumot mavjud.


Shakl.3. Jadval avtomatik jamlama funktsiyasidan foydalanishga misoldir.

Muloqot oynasida tanlangan har bir ustunli guruh ma'lumotlari uchun Subtotallar , quyidagi funktsiyalarni bajarish mumkin:

Funktsiya

Uchrashuv

Barcha qadriyatlarni qo'shadi va katta natijalarni beradi

Qiymatlar soni

Guruhdagi elementlar sonini aniqlaydi

Guruhning o'rtacha arifmetikasini aniqlaydi

Maksimal

Guruhdagi eng katta qiymatni va butun ustundagi eng katta qiymatni aniqlaydi

Har bir guruh va butun ustun uchun eng kichik qiymatni aniqlaydi

Tarkibi

Guruhdagi barcha qiymatlarning hosilasini va butun ustunning hosilasini aniqlaydi

Raqamlar soni

Guruhdagi raqamli qiymatlarni o'z ichiga olgan kataklar sonini va barcha ustunlar guruhlari uchun raqamli qiymatlarga ega bo'lgan kataklarning umumiy sonini aniqlaydi

Xolis og'ish

Ma'lumotlar tanlangan bo'lsa, populyatsiya uchun standart og'ishning qiymatini aniqlaydi

Ofset og'ishi

Agar ma'lumotlar populyatsiyani tashkil etsa, populyatsiyaning standart og'ishini belgilaydi

Xolis farq

Agar ma'lumotlar namuna olinadigan bo'lsa, dispersiya qiymatini aniqlaydi

Ko'chirilgan dispersiya

Agar ma'lumotlar populyatsiyani tashkil qilsa, populyatsiya uchun dispersiya qiymatini belgilaydi

Umumiy hisoblaganda jadval tuzilgan edi - buni ekranga qarab ko'rishingiz mumkin. Tuzilish darajalari yordamida siz ma'lumotlaringizni vizual boshqarishni yaxshiroq ta'minlashingiz mumkin. Ekranda faqat xulosa ma'lumotlarini ko'rsatish uchun strukturaning ikkinchi darajasi tugmachasini bosing, natijada uchinchi darajadagi ma'lumotlar (individual qiymatlar) yashirinadi (4, 5-rasm). Shaxsiy qadriyatlarni qayta ko'rsatish uchun siz uchinchi darajadagi tugmani bosishingiz kerak

Shakl.4. Xulosa ma'lumotlarini ko'rsatish opsiyasi (uchinchi daraja yashiringan).


Shakl.5. Umumiy ko'rsatkichlarni ko'rsatish uchun variant.

Kiritilgan jami summalarni o'chirish uchun faqat buyruqni tanlang Natija menyuda Ma'lumotlar tugmachasini bosing Barchasini olib tashlang .

O'zingizni hisoblang :

    Printerlar va skanerlarning o'rtacha narxi;

    Printerlar va skanerlar nomlarining navlari soni;

    Har bir turdagi mahsulotni sotib olishga sarflangan mablag '(6-rasm)


Shakl.6. Har bir turdagi mahsulotni sotib olishga sarflangan mablag'lar.

Odatiy bo'lib, umumiy qiymatlarni o'z ichiga olgan qatorlar ma'lumotlar guruhining alohida qiymatlari bo'lgan satrlarning ostiga kiritiladi. Agar jami ma'lumotlar guruh ma'lumotlaridan oldin taqdim etilsa, parametr o'chirib qo'yilishi kerak. Natija dialog oynasidagi ma'lumotlar ostida Subtotallar .

Bosib chiqarishda jami ma'lumotlarning har bir guruhi alohida sahifada ko'rsatilishi mumkin. Buning uchun variantni o'rnating Sahifaning oxiri dialog oynasidagi guruhlar o'rtasida Subtotallar .

      Konsolidatsiyalangan hisobotlarni tayyorlash

Topshiriqning maqsadi: konsolidatsiyalangan hisobotlarni tuzishni o'rganing.

Konsolidatsiya funktsiyasi jadvalning turli sohalarida joylashgan ma'lumotlar uchun jami natijalarni hisoblash zarur bo'lganda ishlatiladi. Konsolidatsiya funktsiyasidan foydalangan holda, siz avtomatik subtotal funktsiyadagi kabi bir xil bo'lmagan hujayralar oralig'idagi qiymatlar bo'yicha amallarni bajarishingiz mumkin. Birlashtiriladigan kataklarning diapazonlari bitta ishchi varaqda yoki turli varaqlarda, shuningdek turli xil ish daftarlarida joylashgan bo'lishi mumkin.

Biz oldingi misolni misol sifatida ishlatamiz. Faqat printer ma'lumotlarini va brauzer ma'lumotlarini ikkita ishchiga ajrating varaq, qayta nomlash “ Varaq1"Printerlarga" va " Varaq2"in" Skanerlar”.

Biz printerlar va skanerlarni sotib olish bo'yicha birlashtirilgan hisobotni tuzamiz (ma'lumotlar ish daftarining turli varaqlariga joylashtirilgan). Buning uchun:

    “Tanlang 3. varaq"Va unga yangi nom bering" Hisobot”. Yangi jadval uchun "sarlavha" yarating (8-rasm);

    Yacheykani tanlang A 7 (“Mahsulot”), Va menyuda Ma'lumotlar jamoani tanlang Mustahkamlash . Ochilgan shu nomdagi dialog oynasida birlashtiriladigan kataklarning diapazonlarini va ishlash turini (funktsiyasini) ko'rsating (7.-rasm):


Shakl.7. Muloqot oynasi Mustahkamlash

          qutiga ishonch hosil qiling Funktsiya funktsiya Miqdor ,

          katakchani bosing Havola keyin varaq yorlig'ida “ Printerlar", Printerlar haqida ma'lumot, shu jumladan sarlavha bilan hujayralar blokini tanlang, tugmachani bosing qo'shish . Brauzerlar uchun ma'lumotlar bilan bir xil amallarni bajaring.

          qutiga o'rnatilgan Ism sifatida foydalaning imkoniyatlari Yuqori satrdagi sarlavhalar va Chap ustun qiymatlari konsolidatsiyani nom bo'yicha belgilash va bir xil yorliqli qatorlardagi qiymatlar, hatto hujayralarning tutashmagan diapazonlarida joylashgan bo'lsa ham, yig'iladi,

          Bosing OK , natijada jamlangan hisobotda “ustunlari Turi” va " Ism” yashirin bo'lishi mumkin (8-rasm)


Shakl.8. Konsolidatsiyalangan hisobot

Agar asl katakdagi qiymatlarni o'zgartirsangiz, konsolidatsiya takrorlanishi kerak. Agar manba jadvallarining tuzilishi (hujayralar diapazoni) o'zgarmagan bo'lsa, unda konsolidatsiya qilingan ma'lumotlarni asl nusxaga bog'lash orqali ushbu protsedurani doimiy takrorlashdan saqlanish mumkin. Bunday holda, katakchalarning birlashtirilgan diapazonida asl ma'lumotlarga tashqi havolalari bo'lgan kataklar bo'ladi. Hujayralarning asl diapazonidagi ma'lumotlar o'zgarganda, konsolidatsiya qilingan ma'lumotlar ham avtomatik ravishda o'zgaradi. Birlashtirilgan va asl ma'lumotlar o'rtasida aloqalarni o'rnatish uchun parametrni o'rnating Manba ma'lumotlariga havolalar yarating dialog oynasida Mustahkamlash (7-rasm) .

Bunday holda, tugmachani bosgandan so'ng OK ma'lumotlar avtomatik ravishda yangilanib turishini ta'minlash uchun dastlabki ma'lumotlar va konsolidatsiya natijalari o'rtasida dinamik pasayish o'rnatiladi. Bog'lanish bilan konsolidatsiya qilishning yana bir afzalligi bor: bu holda ma'lumotlar strukturalash funktsiyasi yordamida birlashtiriladi va strukturaning ikkinchi darajasi konsolidatsiya qilingan qiymatlar hisoblanadigan individual qiymatlarni taqdim etadi.

Agar asl ma'lumotlar bir xil ish kitobida bo'lsa, yangilanish avtomatik ravishda amalga oshiriladi. Ammo boshqa ish daftarlarida joylashgan asl ma'lumotlar uchun buyruq yordamida ma'lumotlarni yangilash majbur bo'ladi Aloqalar menyu Tahrirlash .

      Burilish jadvallari va burilish diagrammalarini yaratish

Pivot jadvallar katta hajmdagi ma'lumotlarni tahlil qilish uchun mo'ljallangan. Ularning yordami bilan tahlil qilingan jadval ma'lumotlari tanlangan holda ular orasidagi bog'liqlikni eng yaxshi aks ettiradigan shaklda taqdim etilishi mumkin.

Burilish jadvali Excelda saqlangan ma'lumotlarni tahlil qilish uchun ishlatiladi, ya'ni ish uchun biz 9-rasmdagi quyidagi jadvaldan bizning ish sahifamizga ko'chirishni taklif qiladigan ma'lumotlar kerak.


Shakl.9. Burilish jadvali uchun ma'lumotlar.

        Asosiy jadval yarating.

    Jamoani tanlang Umumiy jadval menyu Ma'lumotlar yoki tugmani bosing Pivot jadvallar PivotTable va Chart Wizard dasturini ishga tushirish uchun asboblar paneli. Ekranda dialog oynasi paydo bo'ladi Pivotli stol ustalari (10-rasm) .


Shakl: 10 PivotTable ustasi.

    Guruhda Joylashgan ma'lumotlar asosida jadval yarating : Mavjud ma'lumot manbalariga mos keladigan radio tugmachasini tanlang. Bizning misolimizda ma'lumotlar Microsoft Excel ro'yxatida keltirilgan.

    Guruhda Hisobot turi: pivot jadvalini o'rnating. Tugmani bosing Keyinchalik - oyna paydo bo'ladi:

    Dalada Oraliq : (11-rasm) Dastlabki ma'lumotlarni o'z ichiga olgan oraliqni ko'rsating. Buning ikkita usuli bor: klaviatura yordamida katakchalarning manzilini yoki nomini kiriting yoki sichqoncha yordamida ish varag'idagi kataklar qatorini tanlang.


    Yaratilgan pivot jadvalining o'rnini tanlang (12-rasm). Agar siz bitta varaqdan joy tanlagan bo'lsangiz, joylashuv oralig'ini yoki faqat boshlang'ich katakchani ko'rsating.

    Tugmani bosing Maket…. Burilish jadvalini yaratish uchun dialog oynasi paydo bo'ladi (13-rasm).


    Maketni belgilashda sichqoncha bilan sohadagi maydon tugmachalarini harakatlantiring:

Tortish tugmasi Ism mintaqaga Chiziq , tugmasi Ishlab chiqaruvchi mintaqaga Sahifa , tugma Chorak mintaqaga Ustun , tugmalar Tovar aylanishiva raqam mintaqaga Ma'lumotlar (14-rasm).


    Tugmani bosing OK , undan keyin Bajarildi ... Siz burilish stolini olasiz (15-rasm).


15-rasm. Xulosa jadvali.

        Burilish jadvali parametrlarini sozlash.

    Allaqachon yaratilgan PivotTable-ning istalgan katagiga o'ng tugmasini bosing va elementni tanlang Jadval parametrlari. Ekranda oyna paydo bo'ladi Pivot jadvali parametrlari (16-rasm).


16-rasm. Pivot jadval parametrlari oynasi.

Qutidagi ushbu dialog oynasida Ism pivot jadvalining nomi o'rnatilgan. Belgilangan kataklar Jami ustunlar bo'yicha va Jami chiziqlar bo'yicha haqidapivot jadvali chop etilishini anglatadi Umumiy natija va Dala jami... Belgilangan katak Avtomatik formatlash sukut pivot jadvaliga qo'llaniladi degan ma'noni anglatadi Avtomatik formatlash... Belgilangan katak Yashirin qiymatlarni qo'shing jami yashirin kataklarning qiymatlari hisobga olinishini bildiradi. Hujayralarni barcha tashqi qatorlar va ustunlar taglavhalari bilan birlashtirish uchun katakchani belgilash kerak Sarlavha hujayralarini birlashtiring ... Qaytgan jadval yoki jadval o'zgarganida, burilish jadvalining katak formatlarini himoya qilish uchun katagiga belgi qo'ying. Formatlashni davom eting ... Dalalarda Ko'rsatilgan xatolar uchun , Bo'sh hujayralar uchun ko'rsatiladi xato yoki ma'lumotlar etishmayotgan bo'lsa, katakchalarda ko'rsatiladigan qiymatlarni kiriting. Ushbu maydonlar faqat tegishli katakchalar mavjud bo'lganda mavjud bo'ladi. Qutini belgilang Ma'lumotlarni jadval bilan birga saqlang u yaratilgan dastlabki ma'lumotlarni nusxalash ushbu jadvalhar doim ularga kirish huquqiga ega bo'lish. Qutini belgilang Joylashtirishga ruxsat berilgan pivot jadvalining ma'lumotlar maydonini ikki marta bosish orqali batafsil ma'lumotlarni namoyish qilish. Qutini belgilang Yangilang ochilayotganda PivotTable-ni o'z ichiga olgan Excel ish kitobini ochganingizda yangilashingiz kerak. Agar siz pivot jadvalini yaratishda tashqi manbalardan foydalansangiz, guruhga kerakli parametrlarni o'rnating Tashqi ma'lumotlar .

    Tugmani bosish orqali oynani yoping OK .

        Ma'lumotlarni filtrlash.

Bosing OK , sizning ish daftaringizda qo'shimcha varaqlar paydo bo'ldi. Excel har bir ma'lumot guruhini alohida ish sahifasiga joylashtiradi.

        Ma'lumotlarni batafsil ko'rsatish.

    Tafsilotlarni ko'rsatmoqchi bo'lgan maydon elementini ikki marta bosing. Ekranda oyna paydo bo'ladi Tafsilotlarni ko'rsatish (18-rasm).


18-rasm. To'liq ma'lumotlarni ko'rsatish uchun oyna.

    Taqdim etilgan ro'yxatdan ko'rsatmoqchi bo'lgan qismlarni o'z ichiga olgan maydonni tanlang va tugmani bosing OK ... Tegishli qatorlar yoki ustunlar burilish jadvaliga kiritilgan.


19-rasm. Pivot jadvalining batafsil ma'lumotlari.

    Batafsil ma'lumotni namoyish qilmaslik uchun ma'lumotlar maydonini yana ikki marta bosing.

        Ma'lumotlarni saralash.

Pivot jadvalidagi maydonlar avtomatik ravishda ularning nomlari bo'yicha ortish tartibida saralanadi; keyin tartiblashtirish tartibini o'zgartirishingiz mumkin.

    Ma'lumotlarni maydonlar bo'yicha saralash Umumiy natija... Buning uchun saralash uchun ma'lumotlarni tanlang, menyudan tanlang Ma'lumotlar element Tartiblash ... Ekranda dialog oynasi paydo bo'ladi Tartiblash ... Guruhdagi oraliqni o'rnating Saralash turi va guruhda Saralash kalitni o'rnating ma'no ... Tugmani bosing OK (20-rasm).


20-rasm Ma'lumotlarni saralash oynasi.

    Jadvalni yangilashda (o'zgartirishda) ma'lumotlar maydonlarini nomlari bo'yicha avtomatik ravishda saralash uchun tartiblangan maydon tugmachasini va paydo bo'lgan oynada ikki marta bosing. tugmachani bosing Keyinchalik ... Ekranda oyna paydo bo'ladi (21-rasm). Guruhda Saralash imkoniyatlari radio tugmachasini o'sish yoki tushish tartibida o'rnating, maydondan foydalangan holda ro'yxatda elementlari saralanadigan maydonni tanlang. Derazani yop.



21-rasm. Qo'shimcha parametrlar oynasi.

    Muloqot oynasida ma'lumotlar majmuasi kataklari qiymatlari bo'yicha qo'lda tartiblash uchun Kengaytirilgan PivotTable maydon parametrlari guruhda Saralash imkoniyatlari kalitni o'rnating qo'lda , keyin oynani yoping. Saralash uchun maydonni tanlang va buyruqni tanlang Tartiblash menyu Ma'lumotlar . Maydonda paydo bo'lgan oynada saralash tomonidan saralanadigan ma'lumotlar maydonining maydonini ko'rsating. Saralashni boshlang. Agar burilish jadvalida odatda alfavit bo'yicha emas, balki xronologik tartibda ajratilgan narsalar bo'lsa, masalan yakshanba, dushanba, seshanba va hk. Excel avtomatik ravishda maxsus tartiblash tartibini ishlatadi. Excelda bir nechta tartiblash ro'yxatlari va yorliqdan foydalanish mavjud Ro'yxatlar oyna Tanlovlar (element Tanlovlar menyu Xizmat ) o'zingizning saralash ro'yxatingizni belgilashingiz mumkin.

        "Eng yaxshi o'nlik" ma'lumotlarini ko'rsatish.

Yaratilgan pivot jadvalida siz belgilangan elementlarning sonini faqat maksimal yoki minimal qiymatlar bilan aks ettirishingiz mumkin.

    Elementlari tanlab ko'rsatiladigan ma'lumotlar maydoniga ikki marta bosing.

    Ko'rsatilgan oynada PivotTable maydonini hisoblash tugmachani bosing Keyinchalik ... Ekranda oyna paydo bo'ladi Kengaytirilgan PivotTable maydon parametrlari .

    Guruhda Displey parametrlari kalitni o'rnating avtomatik(21-rasm). Dalada displey ko'rsatilgan elementlarning sonini va ro'yxatda ko'rsating displey kerakli saralash variantini tanlang: eng buyuk yoki eng kichigi ... Ro'yxatda maydondan foydalanishelementlari ko'rsatiladigan ma'lumotlar maydonini tanlang. Tugmani bosing OK ... Derazani yop.

        Umumiy natijalarni hisoblash funktsiyasidagi o'zgarishlar.

Agar siz PivotTable yaratgan bo'lsangiz, Excel avtomatik ravishda umumiy yig'indilarni aks ettiradi, yig'indisi funktsiyasidan foydalangan holda raqamli qiymatlarga ega maydonlarda hisoblash uchun va boshqa ma'lumotlar bilan maydonlardagi qiymatlar sonini hisoblash uchun. Ushbu funktsiyalarni keyinchalik o'zgartirish mumkin.

    Tugmani bosing Maydon parametrlari asboblar panelida Pivot jadvallar ... Ekranda oyna paydo bo'ladi Pivot jadval maydonini hisoblash.

    Ro'yxatdan mos funktsiyani tanlang Ishlash (22-rasm) va tugmani bosing OK .


Shakl.22. Umumiy natijalarni hisoblash uchun funktsiyalarni qo'llash.

        Xulosa qilishda qo'shimcha hisob-kitoblarni ko'rsatish.

Xulosa qilish uchun yana bir funktsiya guruhidan foydalanishingiz mumkin, bu ma'lumotlar maydonidagi qiymatlar uchun jami natijalarni solishtirish uchun ma'lumotlar maydonidagi boshqa qiymatlarga asoslanib hisoblash imkonini beradi.

    Avtomatik ravishda umumlashtirilgan jadvalni o'z ichiga olgan pivot jadvalidagi maydonni tanlang.

    Tugmani bosing Maydon parametrlari asboblar panelida Pivot jadvallar . Ekranda oyna paydo bo'ladi PivotTable maydonini hisoblash ... Tugmani bosing Qo'shimcha , oynani kengaytirish uchun.

    Ro'yxatdan tanlang Qo'shimcha hisob-kitoblar kerakli funktsiya. Agar kerak bo'lsa, ro'yxatda maydon maydon nomini tanlang va ro'yxatda element - qo'shimcha hisob-kitoblarni amalga oshirishda asosiy qiymat sifatida ishlatiladigan element. Tugmani bosing OK (23-rasm.)


23-rasm. Qo'shimcha hisob-kitoblarni amalga oshirish.



O'qishni tavsiya etamiz