Excelda pivot jadvalini qanday qurish mumkin. Ma'lumotlar bilan qanday ishlash kerak

MS Excel Pivot Table ma'lumotlarni tahlil qilish uchun kuchli vositadir. Ushbu vosita sichqonchani bir necha marta bosish bilan har qanday kontekstda katta hajmdagi ma'lumotlardan ma'lumot olish imkonini beradi. Ushbu maqolada men oddiy va tushunarli tilda bosqichma-bosqich tushuntirishga harakat qilaman, bu juda qulay MS Excel vositasini sizning ixtiyoringizda olish uchun nima va qanday qilish kerak.

Manba ma'lumotlariga qo'yiladigan talablar.

Shunday qilib, har qanday pivot jadvalning asosi to'g'ri tuzilgan ma'lumotlar majmuasi - "to'g'ri jadval". Quyidagi rasmda siz to'g'ri tuzilgan ma'lumotlar massivining namunasini ko'rishingiz mumkin:

Keling, ushbu jadvalning "to'g'riligi" nima ekanligini ko'rib chiqaylik? To'g'riligi shundaki:

• Har bir ustun faqat bir xil turdagi ma'lumotlarni o'z ichiga oladi, ustun A faqat Sanalar, ustunda IN ustunda faqat Hujjatlar BILAN faqat mijozlar, puldagi pullar, yetkazib beruvchilardagi yetkazib beruvchilar, toifalardagi mahsulot toifalari va boshqalar... Bunday jadvalda biz juda oson foydalanishimiz mumkin. Filtr;
• Bir xil turdagi ma'lumotlarga ega ustunlar takrorlanmaydi;
• Jadvalda jami qatorlar yo'q, faqat "toza ma'lumotlar";
• Jadvaldagi matn ma'lumotlarida bo'sh katakchalar yo'q, har bir satrda Mijoz, Mahsulot, Yetkazib beruvchi, Menejer va boshqalar nomi mavjud...

Aytgancha, massivda ustunlar qanday ketma-ketlikda joylashganligi va ma'lumotlar qaysi maydon bo'yicha tartiblanganligi umuman muhim emas. Bu hech qanday tarzda Pivot Table qurilishiga ta'sir qilmaydi.

Va bu erda "noto'g'ri jadval" misoli keltirilgan, undan siz yorsangiz ham, Pivot jadvali tuzilmaydi va u qurilgan bo'lsa ham, ma'lumotlar bilan ishlash mutlaqo mumkin bo'lmaydi... Bizning " Sevimli" buxgalteriya tizimlari odatda bizni keyingi tahlil qilish uchun mutlaqo yaroqsiz bo'lgan hisobotlar ko'rinishida taqdim etadigan jadvallar bilan "iltimos":

Bu erda "Yaroqsiz massiv" ga yana bir misol:

Bu erda juda ko'p "qoidabuzarliklar" mavjud:

• Birinchidan, "Yetkazib beruvchi" va "Kategoriya" ustunlarida bo'sh katakchalar mavjud, shuning uchun biz filtrdan foydalana olmaymiz;
• Ikkinchidan, deyarli har bir ustunda "Jami ..." satrlari mavjud, ular Pivot jadvalini qurish uchun mutlaqo keraksizdir, bundan tashqari, ular faqat yo'lda bo'ladi;
• Uchinchidan, "Grand Total" ustuni ham kerak emas;
• To'rtinchidan, bir xil turdagi ma'lumotlar, ya'ni "Pul" uchta ustunda joylashgan: "yanvar", "fevral" va "mart", bu esa Pivot jadvalini qurishni sezilarli darajada murakkablashtiradi, ya'ni biz buni qilishimiz kerak. u bilan biror narsa qiling ...

Ammo bularning barchasi alohida suhbat uchun, agar siz bunday "qiyshiq jadvallarni" tezda "to'g'ri massivlarga" qanday aylantirishni o'rganishni istasangiz, maqolalarni o'qing: "Qanday qilib 1C yoki SAP hisobotidan pivot jadvalini tezda qurish mumkin?" va "Qanday qilib jadvalni pivot jadvallar uchun massivga tezda aylantirish mumkin?"

Aslida, pivot jadvalini yaratish:

"To'g'ri massiv" ni oling, kursorni massivning istalgan katagiga qo'ying, asosiy menyuning chap burchagidagi "Jadvallar" bo'limidagi "Qo'shish" yorlig'ini tanlang, "Pivot jadvali" tugmasini bosing:

Ochilgan "Pivot jadvalini yaratish" dialog oynasida "OK" tugmasini bosing:

MS Excel yangi varaq yaratadi, unda Pivot Jadval qo'yiladigan joy ko'rsatiladi va o'ng tomonda Pivot Jadval maydonlarini o'rnatish oynasi paydo bo'ladi, unda siz barcha ustunlarning nomlarini ko'rasiz. sizning massivingiz:

Keling, pivot jadvalini o'rnatishni boshlaylik. Biz qanday kesishni xohlaymiz? Menejer bo'yicha mahsulot toifalari - iltimos. Sichqonchani “Kategoriya” maydoniga olib boring, sichqonchaning chap tugmasi bilan ustiga bosing va “QATRLAR” maydoniga torting. “Menejer” maydoni “USTUNLAR”ga, “Miqdor” esa “QIYMATLAR”ga tortiladi:

Pivot jadvallar MS Excel dasturining eng samarali vositalaridan biridir. Ularning yordami bilan siz bir necha soniya ichida bir million qator ma'lumotlarni qisqacha hisobotga aylantirishingiz mumkin. Natijalarni tez sarhisob qilishdan tashqari, pivot jadvallar maydonlarni hisobotning bir maydonidan boshqasiga sudrab borish orqali tahlil qilish usulini tom ma'noda o'zgartirishga imkon beradi.

Pivot jadvallari ham eng kam baholangan Excel vositalaridan biridir. Aksariyat foydalanuvchilar o'z qo'llarida bo'lgan imkoniyatlarni bilishmaydi. Tasavvur qilaylik, pivot jadvallar hali ixtiro qilinmagan. Siz o'z mahsulotlarini turli mijozlarga sotadigan kompaniyada ishlaysiz. Oddiylik uchun assortimentda faqat 4 ta narsa mavjud. Mahsulotlarni turli mintaqalarda joylashgan bir necha o'nlab mijozlar muntazam ravishda sotib oladi. Har bir tranzaksiya ma'lumotlar bazasiga kiritiladi va alohida qatorni ifodalaydi.

Sizning direktoringiz sizga barcha tovarlarni mintaqalar (mintaqalar) bo'yicha sotish bo'yicha qisqacha hisobot berishni buyuradi. Muammoni quyidagicha hal qilish mumkin.

Birinchidan, jadval tartibini yarataylik, ya'ni mahsulotlar va hududlar uchun noyob qiymatlardan iborat sarlavha. Keling, mahsulot ustunining nusxasini yarataylik va dublikatlarni olib tashlaymiz. Keyin, insert special dan foydalanib, biz ustunni qatorga o'tkazamiz. Biz mintaqalar bilan ham xuddi shunday qilamiz, faqat o'tkazmasdan. Biz hisobotning sarlavhasini olamiz.

Ushbu plastinka to'ldirilishi kerak, ya'ni. tegishli mahsulotlar va hududlar bo'yicha daromadlarni umumlashtirish. Buni SUMIFS funksiyasidan foydalanib qilish oson. Keling, natijalarni ham qo'shamiz. Siz mintaqa va mahsulot bo'yicha sotuvlar haqida qisqacha hisobot olasiz.

Siz topshiriqni bajardingiz va hisobotni direktorga ko'rsatasiz. Jadvalga qarab, u bir vaqtning o'zida bir nechta ajoyib g'oyalarni yaratadi.

— Daromad bo‘yicha emas, foyda bo‘yicha hisobot tuzish mumkinmi?

— Mahsulotlarni satrlar bo‘yicha, hududlarni esa ustunlar bo‘yicha ko‘rsatish mumkinmi?

— Bunday jadvallarni har bir rahbar uchun alohida tuzish mumkinmi?

Tajribali Excel foydalanuvchisi bo'lsangiz ham, yangi hisobotlarni yaratish juda ko'p vaqtni oladi. Bu mumkin bo'lgan xatolar haqida gapirmaydi. Biroq, agar siz pivot jadvallarini bilsangiz, javob: ha, menga 5 daqiqa kerak, ehtimol kamroq.

Bu qanday amalga oshirilgani haqida. Manba ma'lumotlarini oching. Pivot jadvalni oddiy diapazon yordamida qurish mumkin, ammo uni o'zgartirish to'g'riroq bo'ladi. Bu darhol yangi ma'lumotlarni avtomatik ravishda qo'lga kiritish masalasini hal qiladi. Har qanday katakchani tanlang va yorliqga o'ting Kiritmoq. Lentaning chap tomonida ikkita tugma mavjud: va Tavsiya etilgan pivot jadvallar.

Mavjud ma'lumotlarni qanday tartibga solishni bilmasangiz, buyruqni ishlatishingiz mumkin Tavsiya etilgan pivot jadvallar. Excel sizning ma'lumotlaringiz asosida mumkin bo'lgan tartiblarning eskizlarini ko'rsatadi.

Tegishli variantni bosing va pivot jadval tayyor. Qolgan narsa uni eslash, chunki... Standart tayyorgarlik sizning xohishingizga to'liq mos kelishi dargumon. Agar siz noldan pivot jadvalini yaratishingiz kerak bo'lsa yoki dasturning eski versiyasi bo'lsa, tugmani bosing. Manba diapazoni (agar siz Excel jadvalining istalgan katakchasini faollashtirsangiz, u o'zi tomonidan aniqlanadi) va kelajakdagi pivot jadvalining joylashishini (sukut bo'yicha yangi varaq tanlanadi) belgilashingiz kerak bo'lgan oyna paydo bo'ladi.

Ko'pincha bu erda biror narsani o'zgartirishga hojat yo'q. OK ni bosgandan so'ng, bo'sh Pivot Table tartibi bilan yangi Excel varag'i yaratiladi.

Sozlamalarga o'tishdan oldin interfeys va asosiy tushunchalar bilan tanishib chiqamiz. Jadval tartibini panelda sozlash mumkin Pivot jadval maydonlari, bu varaqning o'ng tomonida joylashgan.

Panelning yuqori qismida barcha mavjud maydonlar, ya'ni manba ma'lumotlaridagi ustunlar ro'yxati mavjud. Agar siz tartibga yangi maydon qo'shishingiz kerak bo'lsa, uning yonidagi katakchani belgilashingiz mumkin - Excelning o'zi bu maydonni qaerga qo'yish kerakligini aniqlaydi. Biroq, bu har doim ham to'g'ri emas, shuning uchun sichqonchani tartibdagi kerakli joyga sudrab borish yaxshiroqdir. Maydonlarni belgini olib tashlash yoki ularni orqaga sudrab olib oʻchirish ham mumkin.

Pivot jadvali 4 ta maydondan iborat bo'lib, ular panelning pastki qismida joylashgan: qiymatlar, qatorlar, ustunlar, filtrlar. Keling, ularning maqsadini batafsil ko'rib chiqaylik.

Qiymatlar diapazoni- bu tanlangan usul yordamida manba ma'lumotlarini yig'ish orqali olingan qiymatlar bilan pivot jadvalining markaziy qismi.
Aksariyat hollarda agregatsiya tomonidan sodir bo'ladi Xulosa. Agar tanlangan maydondagi barcha ma'lumotlar raqamli formatda bo'lsa, Excel standart summani belgilaydi. Agar manba ma'lumotlarida kamida bitta matn yoki bo'sh katak bo'lsa, u holda yig'indi o'rniga hisoblab chiqiladi Miqdori hujayralar. Bizning misolimizda har bir hujayra tegishli mintaqadagi barcha mos mahsulotlarning yig'indisidir.

PivotTable kataklarida siz boshqa hisoblash usullaridan foydalanishingiz mumkin. 20 ga yaqin tur (o'rtacha, minimal qiymat, nisbat va boshqalar) mavjud. Hisoblash usulini o'zgartirishning bir necha yo'li mavjud. Eng oddiy narsa - pivot jadvalining o'zida kerakli maydonning istalgan katakchasini sichqonchaning o'ng tugmasi bilan bosish va boshqa yig'ish usulini tanlash.

Chiziq maydoni– eng chap ustunda joylashgan satrlarning nomlari. Bu tanlangan maydonning (ustun) barcha noyob qiymatlari. Qator maydonida bir nechta maydonlar bo'lishi mumkin, keyin jadval ko'p darajali bo'ladi. Bu erda odatda mahsulot nomlari, oylar, hududlar va boshqalar kabi sifat o'zgaruvchilari joylashtiriladi.

Ustunlar maydoni- qatorlarga o'xshash, tanlangan maydonning yagona qiymatlarini faqat ustunlarda ko'rsatadi. Ustun sarlavhalari ham odatda sifat ko'rsatkichi hisoblanadi. Masalan, yillar va oylar, mahsulot guruhlari.

Filtr maydoni- nomidan ko'rinib turibdiki, filtrlash uchun ishlatiladi. Misol uchun, hisobotning o'zi mintaqalar bo'yicha mahsulotlarni ko'rsatadi. Pivot jadvalingizni ma'lum bir soha, davr yoki menejer bilan cheklashingiz kerak. Keyin filtr maydoniga filtr maydoni qo'yiladi va ochiladigan ro'yxatdan kerakli qiymat tanlanadi.

Belgilangan maydonlarga maydonlarni qo'shish va olib tashlash orqali siz ma'lumotlaringizning istalgan qismini soniyalar ichida sozlashingiz mumkin.

Keling, bu amalda qanday ishlashini ko'rib chiqaylik. Hozircha, SUMIFS funksiyasi yordamida allaqachon yaratilgan jadvalni yarataylik. Buning uchun maydonga torting Qiymatlar"Daromad" maydoni, mintaqada Strings"Mintaqa" maydonini (savdo hududi) ga torting Ustunlar- "Mahsulot".

Natijada, biz haqiqiy pivot jadvalini olamiz.

Uni qurish uchun tom ma'noda 5-10 soniya kerak bo'ldi. Mavjud pivot jadvalni o'zgartirish ham oson. Keling, rejissyorning istaklari qanday qilib osongina haqiqatga aylanishini ko'rib chiqaylik.

Keling, daromadni foyda bilan almashtiraylik.

Mahsulotlar va hududlarni sichqonchani surish orqali ham almashtirish mumkin.

Pivot jadvallarni filtrlash uchun bir nechta vositalar mavjud. Bunday holda, biz shunchaki filtr maydoniga "Menejer" maydonini joylashtiramiz.

Hammasi bir necha soniya davom etdi. Pivot jadvallari bilan ishlash juda oson. Albatta, barcha vazifalar unchalik ahamiyatsiz emas. Hisoblangan maydonlarni qo'shish, shartli formatlash va hokazolarni qo'shish, yanada murakkab yig'ish usulidan foydalanish kerak bo'lgan holatlar ham mavjud. Lekin bu pivot jadvallarning yanada rivojlangan qo'llanilishi.

Dastlabki ma'lumotlar

Pivot jadvallar bilan muvaffaqiyatli ishlash uchun manba ma'lumotlari bir qator talablarga javob berishi kerak. Har bir maydon (ustun) ustida ushbu maydonlar aniqlanadigan nomlarning mavjudligi majburiy shartdir. Endi ba'zi foydali maslahatlar.

1. Ma'lumotlar uchun eng yaxshi format Excel elektron jadvalidir. Bu yaxshi, chunki har bir maydonning nomi bor va yangi qatorlar qo'shilsa, ular avtomatik ravishda pivot jadvaliga kiritiladi.

2. Ustunlardagi guruhlarni takrorlashdan saqlaning. Misol uchun, barcha sanalar bitta maydonda bo'lishi kerak va, masalan, oylar bo'yicha alohida ustunlarga bo'linmasligi kerak.

3. Bo'shliqlar va bo'sh kataklarni olib tashlang, aks holda bu qator tahlildan chiqib ketishi mumkin.

4. Maydonlarga to'g'ri formatlashni qo'llang. Raqamlar raqamli formatda, sanalar sana bo'lishi kerak. Aks holda, guruhlash va matematik ishlov berish jarayonida muammolar paydo bo'ladi. Lekin Excel bu yerda sizga yordam beradi, chunki... Bu ma'lumotlar formatini juda yaxshi aniqlaydi.

Umuman olganda, talablar kam, lekin siz ularni bilishingiz kerak.

Agar siz manbaga o'zgartirishlar kiritsangiz (masalan, yangi qatorlar qo'shsangiz), pivot jadval sichqonchaning o'ng tugmasi yordamida uni yangilamaguningizcha o'zgarmaydi.

yoki
yorliqdagi buyruq orqali Ma'lumotlar - Hammasini yangilash.

Bu ataylab qilingan, chunki pivot jadvali RAMda juda ko'p joy egallaydi. Shuning uchun, kompyuter resurslaridan tejamkorroq foydalanish uchun ish bevosita manba bilan emas, balki manba ma'lumotlarining oniy tasviri joylashgan kesh bilan amalga oshiriladi.

Ushbu asosiy darajadagi pivot jadvallardan foydalanib, siz katta hajmdagi ma'lumotlarni qayta ishlash tezligi va sifatini sezilarli darajada oshirishingiz mumkin. Agar biror narsa tushunarsiz bo'lib qolsa, izohlarda yozing.

Pivot jadvallari eng kuchli Excel vositalaridan biridir. Ular sizga bir necha marta bosish orqali katta hajmdagi ma'lumotlar uchun turli natijalarni tahlil qilish va umumlashtirish imkonini beradi. Ushbu maqolada biz pivot jadvallari bilan tanishamiz, ular nima ekanligini tushunamiz, ularni qanday yaratish va sozlashni o'rganamiz.

Ushbu maqolani yozishda men Excel 2010 dasturidan foydalandim. Pivot jadvallar tushunchasi ko'p yillar davomida deyarli o'zgarmagan, ammo Excelning har bir yangi versiyasida ularni yaratish usuli biroz farq qiladi. Agar sizda 2010 yildan boshqa Excel versiyasi bo'lsa, unda ushbu maqoladagi skrinshotlar ekraningizda ko'rganingizdan farq qilishiga tayyor bo'ling.

Bir oz tarix

Elektron jadvallarni yaratish dasturlarini ishlab chiqish boshida Lotus qoidalari 1-2-3 edi. Uning hukmronligi shu qadar to'liq ediki, Microsoftning Lotusga muqobil sifatida o'z dasturiy ta'minotini (Excel) ishlab chiqish bo'yicha urinishlari vaqtni behuda sarflashdek tuyuldi. Endi 2010-yilga shoshiling! Excel elektron jadval sanoatida Lotus o'z tarixida qilganidan ko'ra ko'proq hukmronlik qiladi va Lotusdan foydalanadigan odamlar soni nolga yaqinlashmoqda. Bu qanday sodir bo'lishi mumkin? Voqealarning bunday keskin burilish sababi nima edi?

Tahlilchilar ikkita asosiy omilni aniqlaydilar:

• Birinchidan, Lotus Windows deb nomlangan ushbu yangi GUI platformasi uzoq davom etmaydigan o'tkinchi moda ekanligiga qaror qildi. Ular Lotus 1-2-3 ning Windows versiyasini yaratishdan bosh tortdilar (lekin bir necha yilga), ularning dasturiy ta'minotining DOS versiyasi iste'molchilarga hech qachon kerak bo'ladigan narsa ekanligini taxmin qilishdi. Microsoft, albatta, Windows uchun maxsus Excelni ishlab chiqdi.
• Ikkinchidan, Microsoft Excelda pivot jadvallar deb nomlangan vositani ishlab chiqdi, bu Lotus 1-2-3 da mavjud emas. Eksklyuziv Excel bo'lgan pivot jadvallari shunchalik foydali ediki, odamlar Lotus 1-2-3-ga ega bo'lishdan ko'ra, yangi Excel dasturini o'rganishga moyil edilar.

Pivot jadvallari, umuman, Windows-ning muvaffaqiyatini kam baholagan holda, Lotus 1-2-3 uchun o'lim marshini o'ynadi va Microsoft Excel muvaffaqiyatining boshlanishini belgiladi.

Pivot jadvallar nima?

Shunday qilib, pivot jadvallar nima ekanligini tasvirlashning eng yaxshi usuli qanday?

Oddiy so'z bilan aytganda, pivot jadvallar bu ma'lumotlarni tahlil qilishni osonlashtirish uchun yaratilgan ba'zi ma'lumotlarning xulosalari. Qo'lda yaratilgan jamilardan farqli o'laroq, Excel pivot jadvallari interaktivdir. Yaratilgandan so'ng, agar ular siz olishni orzu qilgan rasmni bermasa, ularni osongina o'zgartirishingiz mumkin. Bir-ikki marta bosish bilan jamlamalarni aylantirish mumkin, shunda ustun sarlavhalari satr sarlavhalariga aylanadi va aksincha. Pivot jadvallar bilan juda ko'p turli xil narsalarni qilishingiz mumkin. Pivot jadvallarning barcha imkoniyatlarini so'z bilan tasvirlashga urinishdan ko'ra, uni amalda ko'rsatish osonroq...

PivotTables yordamida tahlil qiladigan maʼlumotlar hech qanday maʼlumot boʻla olmaydi. Bu qandaydir ro'yxat kabi xom ma'lumotlar bo'lishi kerak. Misol uchun, bu kompaniyada so'nggi olti oy ichida yakunlangan savdolar ro'yxati bo'lishi mumkin.

Quyidagi rasmda ko'rsatilgan ma'lumotlarga qarang:

E'tibor bering, bular xom ma'lumotlar emas, chunki ular allaqachon umumlashtirilgan. B3 katakchada biz 30 000 dollarni ko'ramiz, bu Jeyms Kukning yanvar oyida qilgan umumiy summasi. Xo'sh, manba ma'lumotlari qayerda? 30 000 dollarlik raqam qayerdan kelgan? Ushbu oylik jami olingan asl savdo ro'yxati qayerda? Kimdir so'nggi olti oy davomida barcha savdo ma'lumotlarini tartibga solish va saralash va biz ko'rib turgan xulosa jadvaliga aylantirish uchun juda ko'p harakat qilgani aniq. Sizningcha, qancha vaqt ketdi? Soat? Soat o'n?

Gap shundaki, yuqoridagi jadval umumiy jadval emas. U boshqa joyda saqlangan xom ma'lumotlardan qo'lda yaratilgan va qayta ishlash uchun kamida bir necha soat vaqt ketadi. Aynan mana shunday yig'ma jadval bo'lib, unga bir necha soniya vaqt sarflab, pivot jadvallar yordamida yaratish mumkin. Keling, qanday qilib...

Agar biz asl savdo ro'yxatiga qaytsak, u quyidagicha ko'rinadi:

Ushbu savdo operatsiyalari ro'yxatidan pivot jadvallardan foydalangan holda va bir necha soniya ichida Excelda biz yuqorida muhokama qilgan oylik savdo hisobotini yaratishimiz mumkinligiga hayron bo'lishingiz mumkin. Ha, biz buni va yana ko'p narsalarni qila olamiz!

Pivot jadvalini qanday yaratish mumkin?

Birinchidan, Excel varag'ida manba ma'lumotlari mavjudligiga ishonch hosil qiling. Moliyaviy operatsiyalar ro'yxati topilgan eng tipik narsadir. Aslida, bu har qanday narsalar ro'yxati bo'lishi mumkin: xodimlar bilan bog'lanish ma'lumotlari, kompakt disklar to'plami yoki kompaniyangizning yoqilg'i sarfi ma'lumotlari.

Shunday qilib, keling, Excelni ishga tushiramiz ... va ushbu ro'yxatni yuklaymiz ...

Ushbu ro'yxatni Excelda ochganimizdan so'ng, biz pivot jadvalini yaratishni boshlashimiz mumkin.

Ushbu ro'yxatdan istalgan katakchani tanlang:

Keyin yorliqda Kiritmoq(Qo'shish) buyrug'ini tanlang Pivot jadvali(Umumiy jadval):

Muloqot oynasi paydo bo'ladi Pivot jadvalini yarating(Pivot jadvalini yaratish) sizga ikkita savol bilan:

• Yangi pivot jadval yaratish uchun qanday ma'lumotlardan foydalanishim kerak?
• Pivot jadvalini qayerga qo'yishim kerak?

Oldingi bosqichda biz allaqachon ro'yxat hujayralaridan birini tanlaganimiz sababli, pivot jadvalini yaratish uchun butun ro'yxat avtomatik ravishda tanlanadi. E'tibor bering, biz boshqa diapazonni, boshqa jadvalni yoki hatto Access yoki MS-SQL ma'lumotlar bazasi jadvali kabi ba'zi tashqi ma'lumotlar manbalarini tanlashimiz mumkin. Bundan tashqari, biz yangi pivot jadvalini qaerga joylashtirishni tanlashimiz kerak: yangi varaqda yoki mavjudlaridan birida. Ushbu misolda biz variantni tanlaymiz - Yangi ishchi varaq(Yangi varaqda):

Excel yangi ish varag'ini yaratadi va unga bo'sh pivot jadvalini joylashtiradi:

Pivot jadvalining istalgan katakchasini bosganimizdan so'ng, boshqa dialog oynasi paydo bo'ladi: Pivot jadval maydonlari roʻyxati(Pivot jadval maydonlari).

Muloqot oynasining yuqori qismidagi maydonlar ro'yxati - bu asl ro'yxatdagi barcha sarlavhalar ro'yxati. Ekranning pastki qismidagi to'rtta bo'sh maydon sizga ma'lumotlarni qanday umumlashtirishni xohlayotganingizni PivotTablega aytish imkonini beradi. Bu joylar bo'sh bo'lsa-da, jadvalda ham hech narsa yo'q. Biz qilishimiz kerak bo'lgan narsa sarlavhalarni yuqori qismdan pastdagi bo'sh joylarga sudrab borishdir. Bunday holda, bizning ko'rsatmalarimizga muvofiq pivot jadvali avtomatik ravishda yaratiladi. Agar xatoga yo'l qo'ygan bo'lsak, sarlavhalarni pastki qismdan olib tashlashimiz yoki ularni almashtirish uchun boshqalarni sudrab olishimiz mumkin.

Mintaqa Qiymatlar(Maʼnolari) toʻrttasining eng muhimi boʻlsa kerak. Bu sohaga qaysi sarlavha qo'yilgan bo'lsa, qanday ma'lumotlar umumlashtirilishini belgilaydi (yig'indi, o'rtacha, maksimal, minimal, va hokazo) Bu deyarli har doim raqamli qiymatlar. Ushbu sohadagi lavozimga ajoyib nomzod - sarlavha ostidagi ma'lumotlar Miqdori Bizning asl jadvalimiz (narxi). Keling, ushbu sarlavhani hududga tortamiz Qiymatlar(Qiymatlar):

Iltimos, sarlavhaga e'tibor bering Miqdori endi tasdiq belgisi bilan belgilanadi va hududda Qiymatlar(Qiymatlar) yozuvi paydo bo'ldi Miqdor summasi(Sum bo'yicha summa maydoni), ustun ekanligini ko'rsatuvchi Miqdori umumlashtirildi

Agar biz pivot jadvalining o'ziga qarasak, biz ustundagi barcha qiymatlarning yig'indisini ko'ramiz. Miqdori original jadval.

Shunday qilib, bizning birinchi pivot jadvalimiz yaratildi! Qulay, lekin ayniqsa ta'sirchan emas. Ehtimol, biz ma'lumotlarimiz haqida hozir mavjud bo'lgandan ko'ra ko'proq ma'lumot olishni xohlaymiz.

Keling, manba ma'lumotlarini ko'rib chiqaylik va ushbu summani ajratish uchun ishlatilishi mumkin bo'lgan bir yoki bir nechta ustunlarni aniqlashga harakat qilaylik. Misol uchun, biz umumiy jadvalimizni shunday yaratishimiz mumkinki, umumiy savdo miqdori har bir sotuvchi uchun alohida hisoblab chiqiladi. Bular. Satrlar har bir kompaniya sotuvchisining nomi va uning umumiy savdo miqdori ko'rsatilgan asosiy jadvalimizga qo'shiladi. Ushbu natijaga erishish uchun sarlavhani sudrab olib boring Sotuvchi Qator yorliqlari(Qatorlar):

Bu yanada qiziqarli bo'ladi! Bizning asosiy stolimiz shakllana boshladi...

Foydasini ko'ryapsizmi? Bir necha marta bosish orqali biz qo'lda yaratish juda uzoq vaqt talab qiladigan jadval yaratdik.

Yana nima qila olamiz? Xo'sh, qaysidir ma'noda bizning asosiy jadvalimiz allaqachon tayyor. Biz dastlabki maʼlumotlarning foydali xulosasini yaratdik. Muhim ma'lumot allaqachon olingan! Ushbu maqolaning qolgan qismida biz murakkabroq pivot jadvallarni yaratishning ba'zi usullarini ko'rib chiqamiz va ularni qanday sozlashni o'rganamiz.

Pivot jadvalini o'rnatish

Birinchidan, biz ikki o'lchovli pivot jadvalini yaratishimiz mumkin. Buni ustun sarlavhasi yordamida bajaramiz To'lov usuli(To'lov usuli). Faqat sarlavhani torting To'lov usuli mintaqaga Ustun yorliqlari(ustunlar):

Biz natijaga erishamiz:

Juda zo'r ko'rinadi!

Endi uch o'lchamli jadval tuzamiz. Bunday jadval qanday ko'rinishi mumkin? Keling, ko'rib chiqaylik…

Sarlavhani torting Paket(Kompleks) hududga Hisobot filtri(Filtrlar):

U qayerga kelganiga e'tibor bering ...

Bu bizga hisobotni "Qaysi dam olish majmuasi uchun to'langan" atributi bo'yicha filtrlash imkoniyatini beradi. Masalan, biz barcha komplekslar uchun sotuvchilar va to'lov usullari bo'yicha taqsimotni ko'rishimiz mumkin yoki bir necha marta bosish orqali biz yig'ma jadvalning ko'rinishini o'zgartirishimiz va faqat kompleksga buyurtma berganlar uchun bir xil taqsimotni ko'rsatishimiz mumkin. Quyosh tutuvchilar.

Shunday qilib, agar siz buni to'g'ri tushunsangiz, bizning pivot jadvalimizni uch o'lchovli deb atash mumkin. Keling, sozlashni davom ettiramiz...

Agar to'satdan pivot jadvalda faqat chek va kredit karta orqali to'lovlar (ya'ni bank o'tkazmasi) ko'rsatilishi kerakligi aniqlansa, biz sarlavha ekranini o'chirib qo'yishimiz mumkin. Naqd pul(Naqd pul). Buni yonida qilish uchun Ustun yorliqlari pastga o'qni bosing va ochiladigan menyuda element belgisini olib tashlang Naqd pul:

Keling, bizning asosiy jadvalimiz qanday ko'rinishini ko'rib chiqaylik. Ko'rib turganingizdek, ustun Naqd pul undan g'oyib bo'ldi.

Excelda pivot jadvallarini formatlash

Shubhasiz, PivotTables juda kuchli vositadir, ammo hozircha natijalar biroz oddiy va zerikarli ko'rinadi. Misol uchun, biz qo'shadigan raqamlar dollar miqdoriga o'xshamaydi - ular shunchaki raqamlar. Keling, buni tuzataylik.

Bunday vaziyatda odatiy harakatlar qilish vasvasaga soladi va shunchaki butun jadvalni (yoki butun varaqni) tanlang va kerakli formatni o'rnatish uchun asboblar panelidagi standart raqamlarni formatlash tugmalaridan foydalaning. Ushbu yondashuv bilan bog'liq muammo shundaki, agar siz kelajakda pivot jadvalining tuzilishini o'zgartirsangiz (va buning 99% ehtimoli bor), formatlash yo'qoladi. Bizga uni (deyarli) doimiy qilish usuli kerak.

Birinchidan, kirishni topamiz Miqdor summasi hududda Qiymatlar(Qiymatlar) va ustiga bosing. Ko'rsatilgan menyuda elementni tanlang Qiymat maydoni sozlamalari(Qiymat maydoni parametrlari):

Muloqot oynasi paydo bo'ladi Qiymat maydoni sozlamalari(Qiymat maydoni parametrlari).

Tugmasini bosing Raqam formati(Raqam formati), dialog oynasi paydo bo'ladi Hujayralarni formatlash(Hujayra formati):

Ro'yxatdan Turkum(Raqam formatlari) tanlang Buxgalteriya hisobi(Moliyaviy) va kasrlar sonini nolga qo'ying. Endi bir necha marta bosing KELISHDIKMI bizning asosiy jadvalimizga qaytish uchun.

Ko'rib turganingizdek, raqamlar dollar miqdori sifatida formatlangan.

Endi biz formatlashni tugatdik, keling, butun pivot jadval uchun formatni o'rnatamiz. Buning uchun bir necha usullar mavjud. Keling, oddiyroqdan foydalanaylik ...

Yorliqni oching Pivot Table asboblari: Dizayn(Pivot jadvallar bilan ishlash: Dizayner):

Har qanday mos uslubni tanlang va pivot jadvalingizdagi natijaga qarang:

Excelda pivot jadvallar uchun boshqa sozlamalar

Ba'zan siz ma'lumotlarni sanalar bo'yicha filtrlashingiz kerak. Misol uchun, bizning savdo operatsiyalarimiz ro'yxati juda ko'p va ko'p sanalarni o'z ichiga oladi. Excel ma'lumotlarni kun, oy, yil va boshqalar bo'yicha guruhlash vositasini taqdim etadi. Keling, bu qanday amalga oshirilganini ko'rib chiqaylik.

Birinchidan, kirishni olib tashlang. To'lov usuli mintaqadan Ustun yorliqlari(ustunlar). Buni amalga oshirish uchun uni sarlavhalar ro'yxatiga qaytarib olib boring va uning o'rniga sarlavhani siljiting Buyurtma qilingan sana(Bronlash sanasi):

Ko'rib turganingizdek, bu bizning pivot jadvalimizni vaqtinchalik foydasiz qildi. Excel savdo qilingan har bir sana uchun alohida ustun yaratdi. Natijada biz juda keng stol oldik!

Buni tuzatish uchun istalgan sanani o'ng tugmasini bosing va kontekst menyusidan tanlang Guruh(Guruh):

Guruhlash dialog oynasi paydo bo'ladi. Biz tanlaymiz Oylar(Oylar) va bosing KELISHDIKMI:

Voila! Ushbu jadval juda foydali:

Aytgancha, ushbu jadval maqolaning boshida ko'rsatilgan, savdo natijalari qo'lda tuzilgan jadval bilan deyarli bir xil.

Siz bilishingiz kerak bo'lgan yana bir muhim nuqta bor! Siz bitta emas, balki bir nechta satr (yoki ustun) sarlavhalarini yaratishingiz mumkin:

... va u shunday ko'rinadi ...

Xuddi shu narsa ustun sarlavhalari (yoki hatto filtrlar) bilan ham amalga oshirilishi mumkin.

Keling, asl jadval ko'rinishiga qaytaylik va summalar o'rniga o'rtacha qiymatlarni qanday ko'rsatishni ko'rib chiqaylik.

Boshlash uchun ustiga bosing Miqdor summasi va paydo bo'lgan menyudan tanlang Qiymat maydoni sozlamalari(Qiymat maydoni parametrlari):

Ro'yxatda Qiymat maydonini sarhisob qiling(Operatsiya) dialog oynasida Qiymat maydoni sozlamalari(Qiymat maydoni parametrlari) ni tanlang O'rtacha(O'rtacha):

Shu bilan birga, biz bu erda ekanmiz, keling, o'zgaraylik Maxsus nom(Maxsus nom) bilan O'rtacha miqdor(Miqdor bo'yicha miqdor maydoni) qisqaroq narsaga. Ushbu maydonga shunga o'xshash narsani kiriting Oʻrtacha:

bosing KELISHDIKMI va nima bo'lganiga qarang. E'tibor bering, barcha qiymatlar jamidan o'rtachaga o'zgargan va jadval sarlavhasi (yuqori chap katakda) o'zgargan. Oʻrtacha:

Agar xohlasangiz, siz darhol bitta pivot jadvalga joylashtirilgan summani, o'rtacha va miqdorni (sotish) olishingiz mumkin.

Buni qanday qilish bo'yicha bosqichma-bosqich ko'rsatma, bo'sh pivot jadvaldan boshlab:

1. Sarlavhani torting Sotuvchi(Savdo vakili) mintaqaga Ustun yorliqlari(ustunlar).
2. Sarlavhani uch marta torting Miqdori(narxi) hududga Qiymatlar(Qiymatlar).
3. Birinchi maydon uchun Miqdori nomini o'zgartiring Jami(Miqdor) va bu maydondagi raqam formati Buxgalteriya hisobi(Moliyaviy). O'nli kasrlar soni nolga teng.
4. Ikkinchi maydon Miqdori nomi O'rtacha e, buning uchun operatsiyani o'rnating O'rtacha(O'rtacha) va ushbu maydondagi raqam formatini o'zgartiring Buxgalteriya hisobi(Moliyaviy) kasrlar soni nolga teng.
5. Uchinchi maydon uchun Miqdori sarlavha o'rnating Hisoblash va uning uchun operatsiya - Hisoblash(Miqdor)
6. Hududda Ustun yorliqlari(Ustunlar) maydoni avtomatik ravishda yaratiladi S qiymatlari(S qiymatlari) – uni hududga torting Qator yorliqlari(torlar)

Oxir-oqibat biz buni olamiz:

Jami, o'rtacha va savdolar soni - barchasi bitta pivot jadvalda!

Xulosa

Microsoft Excel-dagi pivot jadvallari ko'plab funktsiyalar va sozlamalarni o'z ichiga oladi. Bunday qisqa maqola ularning barchasini qamrab olishga yaqin ham bo'lmaydi. Pivot jadvallarning barcha imkoniyatlarini to'liq tavsiflash uchun kichik kitob yoki katta veb-sayt kerak bo'ladi. Jasur va qiziquvchan o'quvchilar pivot jadvallarini o'rganishda davom etishlari mumkin. Buni amalga oshirish uchun pivot jadvalining deyarli har qanday elementini sichqonchaning o'ng tugmasi bilan bosing va qanday funktsiyalar va sozlamalar ochilganini ko'ring. Tasmada siz ikkita yorliqni topasiz: Pivot Table asboblari: Variantlar(tahlil) va Dizayn(Konstruktor). Xato qilishdan qo'rqmang, har doim pivot jadvalini o'chirib tashlashingiz va qaytadan boshlashingiz mumkin. Sizda uzoq vaqtdan beri DOS va Lotus 1-2-3 foydalanuvchilarida bo'lmagan imkoniyat mavjud.

Excel-dagi pivot jadvallari bitta katta diapazondagi ma'lumotlarni tahlil qilish imkonini beradi. Bundan tashqari, tahlil qilish uchun foydalaniladigan manba jadvali Excelda yoki boshqa ma'lumotlar bazasida yoki boshqa hujjatda yaratilishi mumkin.

Sizga kerak bo'lgan narsa - satrlar va ustunlar uchun kerakli sarlavhalarni mavjudlardan tanlash. Filtrni butun pivot jadvalga yoki faqat satr va ustunlar nomlarida ko'rsatilgan qiymatlarga ham qo'llashingiz mumkin.

Endi uni Excelda yaratishda qanday talablarga rioya qilish kerakligi haqida. Manba ma'lumotlar jadval shaklida taqdim etilishi kerak, unda ustunlar uchun sarlavha, ya'ni sarlavha bo'lishi kerak. Ushbu maqsadlar uchun Excelda aqlli jadval yaratish juda mos keladi. Shuningdek, unda bo'sh qatorlar, ustunlar yoki katakchalar bo'lmasligi kerak. Yashirin qatorlar/ustunlar yoki birlashtirilgan hujayralar bo'lmasligi kerak.

Qanday yaratish kerak

Endi pivot jadvalini yaratish misolini ko'rib chiqamiz. Aytaylik, bizda kiyim-kechak do'konining sotuvi bo'yicha ma'lumotlar bor: qaysi sotuvchi, qaysi raqam, u qanday narsalarni sotgan va qancha miqdorda.

Pivot jadvalini yaratish uchun asl nusxadan istalgan katakchani tanlang, so'ng "Qo'shish" yorlig'iga o'ting va tugmani bosing. "Umumiy jadval".

Quyidagi dialog oynasi paydo bo'ladi. Unda manba ma'lumotlari bilan jadvalning diapazoni yoki nomini ko'rsatishingiz kerak va bu erda uni boshqa manbadan tanlashingiz mumkin. Keyin yaratiladigan hisobotni qayerga joylashtirishni marker bilan belgilang. Keling, buni yangi varaqda qilaylik. OK tugmasini bosing.

Ochiq Excel ish kitobida yangi varaq yaratiladi, unga hali bo'sh pivot jadvali joylashtiriladi.

O'ng tomonda maydonlar va maydonlar ro'yxati paydo bo'ladi. Maydonlar asl diapazondagi barcha ustun sarlavhalaridir. Sichqoncha yordamida biz ularni quyidagi to'rtta maydondan biriga tortamiz. Shunday qilib, pivot jadvalini shakllantirish.

Qo'shilgan maydonlar tasdiq belgisi bilan belgilanadi. Jadval maydonlarida siz manba ma'lumotlaringizni tahlil qilish uchun eng mos keladigan ko'rinishga erishish uchun ularning joylarini o'zgartirishingiz mumkin.

Maydonlarni qanday tartibga solish kerak

Keling, ma'lumotlarni qanday printsip asosida tahlil qilishimizni hal qilaylik. Misol uchun, har oyda qaysi sotuvchi qaysi mahsulotni va qancha miqdorda sotganini aniqlashingiz kerak.

Biz ma'lumotlarni sotuvchilar tomonidan tanlangan diapazonga muvofiq filtrlaymiz. Ya'ni, biz sotuvchini tanlaymiz va jadvalda u sotgan tovarlar ko'rsatiladi. "Vendor" maydonini sichqonchaning chap tugmasi bilan bosing va uni "Hisobot filtri" maydoniga torting. Jadval o'zgardi va qo'shilgan maydon endi tasdiq belgisi bilan belgilangan.

Chiziqlar uchun "Mahsulotlar" ni tanlang. Xuddi shunday, kerakli maydonni maydonga torting "Qator sarlavhalari".

E'tibor bering, siz hududga bir nechta maydonlarni kiritishingiz mumkin. Masalan, satrlarda biz mahsulotlarni tanlaymiz va narxni ko'rsatamiz. Mahsulotlar narxini ko'rsatadigan ochiladigan ro'yxatlarga aylandi. Agar siz avval narxni, keyin esa mahsulotlarni ko'rsatsangiz, ochiladigan ro'yxat narxga aylanadi. Bu erda maydonlarning tartibi muhim ahamiyatga ega.

Agar bizda asl jadvalda "Birliklar" ustuni bo'lsa. Shunda ushbu jamlama jadval qaysi sotuvchi, qaysi oyda, qancha birlik tovarni ma'lum bir narxda sotganini ko'rsatardi.

Mintaqaga "ustun sarlavhalari""Sana" maydonini torting. Savdolarni har kun uchun emas, balki, masalan, oylar bo'yicha ko'rsatish uchun istalgan sanani o'ng tugmasini bosing va menyudan "Guruh" ni tanlang.

Jadval quyidagi shaklni oladi.

Endi "Miqdor" maydonini "Qimmatlar" maydoniga torting.

Ko'rib turganingizdek, faqat raqamlar ko'rsatilgan, garchi asl diapazonda ushbu ustun uchun katakchalarning raqamli formati o'rnatilgan bo'lsa ham, u pul yoki moliyaviy bo'lishi mumkin. "Sana" ustunida hujayra formati ham mos edi - sana.

Buni tuzatish uchun xulosada kerakli katakchalarni tanlang va sichqonchaning o'ng tugmasini bosing. keyin menyudan tanlang "Raqam formati".

Keyingi oynada "Raqamli" ni tanlang, siz katakchani belgilashingiz mumkin "Bit guruhini ajratuvchi" va "OK" tugmasini bosing.

Ma'lumotlar bilan qanday ishlash kerak

Barcha kerakli maydonlarni hisobga olganimizdan so'ng, Excelda pivot jadvali bilan ishlashni boshlashimiz mumkin. Sotuvchini tanlash: katakchani belgilash orqali bitta, bir nechta yoki barchasini birdaniga tanlashingiz mumkin "Bir nechta elementlarni tanlang".

Siz qatorlar va ustunlar uchun filtrdan ham foydalanishingiz mumkin. Misolda, bu mahsulotlar va oylar. Masalan, kostyum va shimlar uchun katakchalarni belgilash orqali siz ular barcha sotuvchilar yoki ma'lum bir sotuvchi tomonidan qanchaga sotilganligini bilib olishingiz mumkin.

Qadriyatlar sohasida siz maydon sozlamalarini sozlashingiz mumkin. Misol qiymatlar yig'indisini ko'rsatadi: Roma fevral oyida 1800,00 miqdorida ko'ylak sotdi. qancha bo'lak ekanligini ko'raylik. Sichqonchaning chap tugmasi bilan chiziqni bosing "Maydon bo'yicha yig'indi..." va menyudan tanlang "Qiymat maydoni parametrlari".

Keyingi oynada ro'yxatdan "Miqdor" ni tanlang, sizning ishingizga mos keladigan narsani tanlang va "OK" tugmasini bosing.

Endi qadriyatlarga qarab, “Roma” fevral oyida ikkita futbolka sotganini tushunishimiz mumkin.

Keling, jadval uchun umumiy filtrni oylar bo'yicha tuzamiz. Biz maydonlarni o'zgartiramiz: "Hisobot filtri" da "Sana" maydonini tortamiz "ustun sarlavhalari"- "Sotuvchi".

Bunday yig'ma jadvalda har bir sotuvchi butun davr yoki ma'lum bir oy uchun qancha va qanday tovarlar sotganligini ko'rsatadi.

Shuningdek, lentaga e'tibor bering. Pivot jadvalidan biron bir katakchani tanlaganingizda, unda yorliq paydo bo'ladi "Pivot jadvallar bilan ishlash" ikkita pastki "Parametrlar" va "Dizayner" bilan.

Hammamiz duch keladigan muammo ma'lumotlarning etishmasligi emas; aksincha - bu juda katta hajmdagi ma'lumotlar! Shuning uchun men ajoyib Excel funktsiyasidan foydalanishni yoqlayman - Pivot jadvallari ma'lumotlaringizni umumlashtirish va tahlil qilish uchun.

PivotTablesning kuchini ko'rish, dastlabki ma'lumotlarni PivotTable versiyasi bilan taqqoslash kabi oddiy; Bir necha marta bosish orqali men shtat bo'yicha mahsulotning o'rtacha narxini ko'rishga muvaffaq bo'ldim.

Moliyaviy mutaxassis sifatida men genetik jihatdan elektron jadvallarni yaxshi ko'raman. Lekin men o‘zimning ijodiy va mustaqil ishimni tartibga solish uchun elektron jadvallardan foydalanishimni ham aniqladim. Elektron jadvallardan nima maqsadda foydalanmasligingizdan qat'iy nazar, pivot jadval ma'lumotlaringizdan ko'proq ma'no topishga yordam beradi.

Ma'lumotlar bilan yaxshiroq ishlash uchun biz ushbu darsni o'zimizga ko'ra quramiz. Men sizga o'zimning eng sevimli pivot jadvallarimning beshtasini ko'rsataman.

Ushbu qo'llanma davomida men ushbu sahifada Microsoft tomonidan taqdim etilgan namunaviy ma'lumotlardan foydalanaman. Ushbu ma'lumotlardan misollarni qayta yaratish yoki men ko'rsatgan funktsiyalarni sinab ko'rish uchun foydalaning.

Excelda pivot-jadvalning ilg'or usullaridan qanday foydalanish (qisqa video)

Menga skrinshotlar yordamida dars berish yoqadi, bu sizga funksiyalarimdan bosqichma-bosqich foydalanishimni kuzatish imkonini beradi. Quyidagi qisqa videoni ko'ring, unda mening eng sevimli Excel PivotTable xususiyatlarimdan beshtasi yoritilgan:

Excelda 5 ta ilg'or pivot jadval fokuslari

Quyidagi darsda ushbu besh xususiyatning har biridan qanday foydalanishni oʻrganish uchun oʻqishni davom eting, jumladan: Slayers, Timeline, Jadval koʻrinishi, Hisoblangan maydonlar va Tavsiya etilgan pivot jadvallari. Qani boshladik.

1. Bo'limlar

Dilimlar- Excel pivot jadvaliga kiritilgan ma'lumotlarni aniqlashtirish uchun nuqta va bosish vositasi. Dilimlagichni joylashtiring va siz pivot jadvaliga kiritilgan ma'lumotlarni osongina o'zgartirishingiz mumkin.

Ushbu misolda men turi uchun tilim qo'shdim Element. Men bosganimdan keyin Ryukzak, pivot jadval jadvalda faqat ushbu parametrni ko'rsatadi.

Ko'p marta men boshqalar tomonidan foydalaniladigan boshqaruv paneli hisobotlarini ishlab chiqaman. Kesuvchilarni qo'shish oxirgi foydalanuvchiga hisobotni o'z xohishiga ko'ra sozlashda yordam berishi mumkin.

Dilimlagichni qo'shish uchun pivot jadvalni bosing va yorliqni toping Tahlil Excel lentasida.

Ushbu ustunlarni kiritish uchun bir nechta katakchalarni belgilang, ularning har biri bir bo'lak bo'ladi.

Tutmoq Ctrl PivotTable ma'lumotlari sifatida ustundan bir nechta tanlovlarni o'z ichiga oladigan bir nechta kesuvchi elementlarni tanlash uchun klaviaturada.

2. Vaqt jadvali

Vaqt jadvallari Bu Pivot Table ma'lumotlarining bir qismi sifatida kiritilgan sanalarni sozlash uchun ishlatiladigan maxsus kesgich turi. Agar maʼlumotlaringizda sanalar boʻlsa, maʼlum vaqt oraligʻidagi maʼlumotlarni tanlash usuli sifatida Xronologiyani sinab koʻrishingiz kerak.

Bo'limga o'ting Tahlil qilish > Vaqt jadvalini kiritish vaqt jadvalini qo'shish uchun, sana asosida kengaytirilgan Excel pivot jadvaliga kiritilgan ma'lumotlarni boshqaradigan maxsus turdagi kesgich.

Maslahat: Agar bu xususiyat siz uchun ishlamasa, manba maʼlumotlaringizda sana elektron jadvalda sana sifatida formatlanganligiga ishonch hosil qiling.

Vaqt jadvalini qo'shish uchun Pivot jadvalini tanlaganingizga ishonch hosil qiling (uning ichiga bosing) va keyin tugmani bosing. Tahlil qilish > Vaqt jadvalini kiritish Excel lentasida. Qalqib chiquvchi oynada sana ustunini (yoki bir nechta ustunlarni) tanlang va ustiga bosing KELISHDIKMI vaqt jadvalini yaratish uchun.

Pivot jadvali uchun ma'lum vaqt oralig'ini tanlash uchun Xronologiya oynasida bosing va torting.

Vaqt jadvali kiritilgandan so'ng, pivot jadvaliga kiritilgan narsalarni o'zgartirish uchun vaqt jadvalidagi tutqichlarni bosishingiz va harakatlantirishingiz mumkin.

Pastki o'ng burchakdagi ochiladigan ro'yxatni bosish orqali vaqt jadvalining ishlash usulini o'zgartirishingiz mumkin. Masalan, ma'lum sanalar o'rniga choraklik yoki yillik ma'lumotlarni ko'rsatish uchun vaqt jadvalini o'zgartirishingiz mumkin.

3. Jadval ko'rinishi

Excelda PivotTablesning standart ko'rinishi sharsharaga o'xshaydi; Ko'proq maydonlarni satrlarga sudrab borsangiz, Excel ma'lumotlarda ko'proq "qatlamlar" yaratadi.

Muammo shundaki, standart ko'rinishdagi umumiy jadvallarga formulalarni joylashtirish qiyin. Agar sizda umumiy jadvalda ma'lumotlar mavjud bo'lsa, lekin uni oddiy jadval sifatida ko'rishni istasangiz, Pivot jadval uchun jadval ko'rinishidan foydalanishingiz kerak.

Tanlang Dizayner > Hisobot tartibi > Jadval shaklida ko‘rsatish pivot jadvalingiz bilan oddiy jadval sifatida ishlash uchun.

Nima uchun jadval ko'rinishidan foydalanish kerak? PivotTable ma'lumotlarini klassik jadval uslubiga keltirish sizga ma'lumotlar formulalarini osonroq kiritish yoki ularni alohida hisobotga kiritish imkonini beradi.

Pivot jadvalingizni standart satr va ustunlarga oʻxshatish uchun Jadval koʻrinishidan foydalaning.

Ko'pincha men Excelning jadval ko'rinishidan foydalanishni afzal ko'raman. U ko'proq standart elektron jadvalga o'xshaydi va formulalar yozish va undagi ma'lumotlar bilan ishlash osonroq. Men bu ko'rinishni olib, uni yangi tabga joylashtirishim mumkin.

4. Hisoblangan maydonlar

Hisoblangan maydonlar - bu manba ma'lumotlarida bo'lmagan Pivot jadvaliga ustun qo'shish usuli. Siz ishlash uchun mutlaqo yangi maydonlarni yaratish uchun standart matematik operatsiyalardan foydalanishingiz mumkin. Ikkita mavjud ustunni oling va mutlaqo yangilarini yaratish uchun matematikadan foydalaning.

Aytaylik, bizda elektron jadvalda savdo ma'lumotlari mavjud. Bizda sotilgan mahsulotlar soni va har bir mahsulot uchun sotish narxi mavjud. Bu buyurtma umumiy miqdorini hisoblash uchun hisoblangan maydondan foydalanish uchun ideal vaqt.

Hisoblangan maydonlar bilan ishlashni boshlash uchun, Pivot jadvali ichidan bosing va keyin tasmada toping. Tahlil. Menyuni bosing Maydonlar, elementlar va to'plamlar va keyin tanlang Hisoblangan maydon.

Foydalanish Tahlil > Maydonlar, a'zolar va to'plamlar > Hisoblangan maydon Hisoblangan maydonni Pivot jadvalingizga kiritish uchun.

Yangi qalqib chiquvchi oynada hisoblangan maydonga nom berishdan boshlang. mening vaziyatimda men uni chaqiraman Jami buyurtma. Buyurtmaning umumiy qiymati har bir birlik narxiga ko'paytiriladigan miqdordir. Keyin ushbu oynadagi maydonlar ro'yxatidagi birinchi sarlavha maydonini (miqdori) ikki marta bosdim.

Buyurtmaning umumiy narxini hisoblash uchun men mavjud birlik narxi maydonini ko'paytirdim ( Donasining narxi) miqdor maydonida ( Miqdori).

Ushbu maydon nomini qo'shgandan so'ng, men ko'paytirish belgisini qo'shaman * , va keyin umumiy miqdor (miqdor) ustiga ikki marta bosing. Oldinga boramiz va bosing KELISHDIKMI.

Endi Excel kengaytirilgan Pivot jadvalimni yangiladi va yangi hisoblangan maydon qo'shdi. Shuningdek, maydonlar roʻyxatida yigʻma jadvallar roʻyxatini koʻrasiz, shunda ularni kerakli vaqtda hisobotning istalgan joyiga sudrab oʻtishingiz mumkin.

Ikki ustunli arifmetikadan foydalanishni xohlamasangiz, hisoblangan maydonga o'zingizning arifmetik qiymatlaringizni ham kiritishingiz mumkin. Misol uchun, agar men har bir buyurtma uchun oddiygina 5% savdo solig'ini qo'shmoqchi bo'lsam, hisoblangan maydonga quyidagilarni yozishim mumkin:

Savdo solig'ini hisobga olgan holda xarajatlarni hisoblash uchun umumiy buyurtma narxini 1,05 ga ko'paytirdim; mavjud maydonlar bilan bir qatorda raqamli qiymatlardan foydalanishingiz mumkin.

Asosan, hisoblangan maydonlar qo'shish, ayirish, ko'paytirish va bo'lish kabi har qanday standart matematik operatorlarni o'z ichiga olishi mumkin. Asl ma'lumotlarni yangilashni xohlamasangiz, ushbu hisoblangan maydonlardan foydalaning.

ga boring Qo'shish > ushbu xususiyatni sinab ko'rish uchun.

Bu xususiyatdan foydalanish shunchalik osonki, buni aytish mumkin emas. Siz undan Excelda tez rivojlangan pivot jadvallarini yaratish uchun foydalanishingiz mumkin. Faqat ma'lumotlaringizni ta'kidlang, yorliqga o'ting Kiritmoq Excel lentasida va tanlang Tavsiya etilgan pivot jadvallar.

Qalqib chiquvchi oynada manba ma'lumotlaridan pivot jadval yaratish uchun ko'plab variantlar mavjud. Excel tomonidan taqdim etilgan tavsiya etilgan Pivot jadval variantlarini ko'rish uchun ushbu oynaning chap tomonidagi eskizlarni bosing.

Pivot Table Tavsiya xususiyati bir marta bosish orqali ma'lumotlarni tahlil qilish uchun ko'plab imkoniyatlarni taklif etadi.

Bu kamdan-kam foydalanuvchilarga ma'lum bo'lgan ilg'or xususiyat bo'lsa ham, u pivot jadvallarini ishga tushirish uchun ajoyib vositadir. Maydonlarni o'zingiz almashtirish orqali Pivot jadvalini o'zgartirishingizga hech narsa to'sqinlik qilmaydi, lekin bu yaxshi boshlanish nuqtasi.

Bundan tashqari, menga ushbu xususiyat ma'lumotlarni o'rganish vositasi sifatida yoqadi. Agar ma'lumotlarni o'rganishni boshlaganimda nimani qidirayotganimni bilmasam, tavsiya etilgan Excel Pivot jadvallari ko'pincha mendan ko'ra tushunarliroq bo'ladi!

Takrorlang va o'rganishni davom eting (ko'proq Excel darslari bilan)

Ushbu ilg'or Excel darsi sizga Excel elektron jadvalini tahlil qilish va ko'rish uchun eng sevimli xususiyatlarimdan biri bo'lgan Pivot Tables bilan chuqurroq tanishishingizga yordam berdi. Yaxshiroq qarorlar qabul qilish va harakat qilishim uchun katta maʼlumotlar toʻplamlarida maʼno topish uchun pivot jadvallardan foydalanaman.

Ushbu darslar Excel va Pivot jadvallari bilan ishlash ko'nikmalaringizni keyingi bosqichga olib chiqadi. Ularni tekshiring:

• ExcelZoo o'zining "Pivot jadvallarini o'zlashtirish uchun 10 ta dars" (ingliz tilida) maqolasida Pivot Table texnikasi haqida ajoyib ma'lumotga ega.
• Biz Envato Tuts+ da yangi boshlanuvchilar uchun qo'llanma bilan pivot jadvallarini yopdik.
• Microsoft Excel bilan osonroq tanishish uchun bizning o'quv qo'llanmalarimizni ko'rib chiqing.

Pivot jadvallar haqida hali nimani bilishni xohlaysiz? Ushbu o'quv qo'llanmasi ostidagi sharhlarda o'z g'oyalaringiz yoki savollaringizni menga xabar bering.