Mundarija
Pivot jadvallar Excelning eng kuchli vositalaridan biri hisoblanadi. Ular sichqonchani bir necha marta bosish orqali katta hajmdagi ma'lumotlarning turli xulosalarini 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 maqola Excel 2010 yordamida yozilgan. Yillar davomida yig'ma jadvallar tushunchasi unchalik o'zgarmadi, lekin ularni yaratish usuli Excelning har bir yangi versiyasida biroz farq qiladi. Agar sizda Excelning 2010 emas versiyasi bo'lsa, unda ushbu maqoladagi skrinshotlar ekranda ko'rganingizdan farq qilishi uchun tayyor bo'ling.
tarixi bir bit
Elektron jadval dasturining dastlabki kunlarida Lotus 1-2-3 qoida to'pi. Uning hukmronligi shu qadar to'liq ediki, Microsoftning Lotusga muqobil sifatida o'z dasturiy ta'minotini (Excel) yaratishga bo'lgan sa'y-harakatlari vaqtni behuda sarflashdek tuyuldi. Endi 2010-yilga shoshiling! Excel elektron jadvallarda Lotus kodi o'z tarixida qilganidan ko'ra ko'proq ustunlik qiladi va hali ham Lotusdan foydalanadigan odamlar soni nolga yaqin. 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 yil davomida), ularning dasturiy ta'minotining DOS versiyasi barcha iste'molchilarga kerak bo'lishini bashorat qilishdi. Microsoft tabiiy ravishda Excelni Windows uchun maxsus ishlab chiqdi.
- Ikkinchidan, Microsoft Excelda Lotus 1-2-3 da mavjud bo'lmagan PivotTables deb nomlangan vositani taqdim etdi. Excelga xos bo'lgan umumiy jadvallar juda foydali bo'lib chiqdiki, odamlar Lotus 1-2-3 bilan davom etishdan ko'ra, yangi Excel dasturiy ta'minot to'plamiga yopishib olishdi.
PivotTables, umuman olganda, Windowsning muvaffaqiyatini kam baholagan holda, Lotus 1-2-3 uchun o'lim marshini o'ynadi va Microsoft Excel muvaffaqiyatiga erishdi.
Pivot jadvallar nima?
Shunday qilib, Pivot jadvallari nima ekanligini tavsiflashning 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 kutgan rasmni bermasa, ularni osongina o'zgartirishingiz mumkin. Sichqonchani bir-ikki marta bosish bilan jamilarni aylantirish mumkin, shunda ustun sarlavhalari satr sarlavhalariga aylanadi va aksincha. Pivot jadvallar yordamida siz ko'p narsalarni qilishingiz mumkin. Pivot jadvallarning barcha xususiyatlarini so'z bilan ta'riflashga harakat qilish o'rniga, uni amalda ko'rsatish osonroq ...
PivotTables yordamida tahlil qilgan maʼlumotlaringiz tasodifiy boʻlishi mumkin emas. Bu qandaydir ro'yxat kabi xom ma'lumotlar bo'lishi kerak. Misol uchun, bu kompaniya tomonidan so'nggi olti oy ichida amalga oshirilgan savdolar ro'yxati bo'lishi mumkin.
Quyidagi rasmda ko'rsatilgan ma'lumotlarga qarang:
E'tibor bering, bu xom ma'lumot emas, chunki u allaqachon umumlashtirilgan. B3 katakchasida biz 30000 30000 dollarni ko'ramiz, bu Jeyms Kukning yanvar oyida qilgan umumiy natijasidir. Asl ma'lumotlar qayerda? XNUMX XNUMX dollarlik raqam qayerdan kelgan? Ushbu oylik jami olingan savdolarning asl ro'yxati qayerda? Kimdir so'nggi olti oy davomida barcha savdo ma'lumotlarini tartibga solish va saralash va biz ko'rgan jami jadvalga aylantirish bo'yicha juda yaxshi ish qilgani aniq. Sizningcha, qancha vaqt ketdi? Soat? Soat o'n?
Gap shundaki, yuqoridagi jadval pivot jadval emas. U boshqa joyda saqlangan xom ma'lumotlardan qo'lda yaratilgan va qayta ishlash uchun kamida bir necha soat vaqt ketadi. Aynan shunday yig'ma jadvalni bir necha soniya ichida pivot jadvallar yordamida yaratish mumkin. Keling, qanday qilishni aniqlaymiz ...
Agar biz asl savdo ro'yxatiga qaytsak, u quyidagicha ko'rinadi:
Ushbu savdo ro'yxatidan pivot jadvallar yordamida va bir necha soniya ichida Excelda biz yuqorida tahlil qilgan oylik savdo hisobotini yaratishimiz mumkinligiga hayron bo'lishingiz mumkin. Ha, biz buni va boshqalarni qila olamiz!
Pivot jadvalini qanday yaratish mumkin?
Birinchidan, Excel varag'ida manba ma'lumotlari mavjudligiga ishonch hosil qiling. Moliyaviy operatsiyalar ro'yxati sodir bo'lgan eng tipik hisoblanadi. Aslida, bu har qanday narsalar ro'yxati bo'lishi mumkin: xodimlar bilan bog'lanish ma'lumotlari, CD to'plami yoki kompaniyangizning yoqilg'i sarfi ma'lumotlari.
Shunday qilib, biz Excelni ishga tushiramiz ... va bunday ro'yxatni yuklaymiz ...
Ushbu ro'yxatni Excelda ochganimizdan so'ng biz pivot jadvalini yaratishni boshlashimiz mumkin.
Ushbu ro'yxatdan istalgan katakchani tanlang:
Keyin yorliqda Qo'shish (Qo'shish) buyrug'ini tanlang Pivot jadvali (Pivot jadvali):
Muloqot oynasi paydo bo'ladi Pivot jadvalini yarating (Pivot jadvalini yaratish) sizga ikkita savol bilan:
- Yangi pivot jadval yaratish uchun qanday ma'lumotlardan foydalanish kerak?
- Pivot jadvalini qayerga qo'yish kerak?
Oldingi bosqichda biz allaqachon ro'yxat kataklaridan birini tanlaganimiz sababli, pivot jadvalini yaratish uchun butun ro'yxat avtomatik ravishda tanlanadi. E'tibor bering, biz boshqa diapazonni, boshqa jadvalni va 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 varaqga):
Excel yangi varaq 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 aytib berish imkonini beradi. Bu joylar bo'sh ekan, jadvalda ham hech narsa yo'q. Biz qilishimiz kerak bo'lgan narsa sarlavhalarni yuqori qismdan pastdagi bo'sh joylarga sudrab borishdir. Shu bilan birga, bizning ko'rsatmalarimizga muvofiq, pivot jadvali avtomatik ravishda yaratiladi. Agar biz xato qilsak, sarlavhalarni pastki qismdan olib tashlashimiz yoki ularni almashtirish uchun boshqalarni sudrab olishimiz mumkin.
maydon Qadriyatlar (Maʼnolari) toʻrttasining eng muhimi boʻlsa kerak. Ushbu sohada qaysi sarlavha joylashtirilganligi qanday ma'lumotlarning umumlashtirilishini belgilaydi (yig'indi, o'rtacha, maksimal, minimal va boshqalar) Bu deyarli har doim raqamli qiymatlar. Sarlavha ostidagi ma'lumotlar ushbu sohadagi o'rin uchun ajoyib nomzoddir miqdor Bizning asl jadvalimiz (narxi). Ushbu sarlavhani hududga torting Qadriyatlar (Qiymatlar):
Iltimos, sarlavhaga e'tibor bering miqdor endi tasdiq belgisi bilan belgilangan va hududda Qadriyatlar (Qiymatlar) yozuv paydo bo'ldi Miqdor summasi (Miqdor maydoni Miqdori), ustun ekanligini ko'rsatuvchi miqdor umumlashtirildi.
Agar biz pivot jadvalining o'ziga qarasak, ustundagi barcha qiymatlarning yig'indisini ko'ramiz miqdor 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, asl ma'lumotlarga murojaat qilaylik va bu summani bo'lish uchun ishlatilishi mumkin bo'lgan bir yoki bir nechta ustunlarni aniqlashga harakat qilaylik. Misol uchun, biz umumiy jadvalimizni shunday shakllantirishimiz mumkinki, sotishning umumiy miqdori har bir sotuvchi uchun alohida hisoblanadi. Bular. qatorlar bizning asosiy jadvalimizga kompaniyadagi har bir sotuvchining nomi va ularning umumiy savdo miqdori ko'rsatilgan holda qo'shiladi. Ushbu natijaga erishish uchun sarlavhani sudrab olib boring sotuvchi (savdo vakili) mintaqaga Qator yorliqlari (Strlar):
Bu yanada qiziqarli bo'ladi! Bizning Pivot Tableimiz shakllana boshladi…
Foydasini ko'rasizmi? 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 tayyor. Biz asl ma'lumotlarning foydali xulosasini yaratdik. Muhim ma'lumot allaqachon olingan! Ushbu maqolaning qolgan qismida biz murakkabroq pivot jadvallarini yaratishning ba'zi usullarini ko'rib chiqamiz, shuningdek ularni qanday sozlashni o'rganamiz.
Pivot jadvalini sozlash
Birinchidan, biz ikki o'lchovli pivot jadvalini yaratishimiz mumkin. Buni ustun sarlavhasi yordamida bajaramiz To'lov uslubi (To'lov uslubi). Faqat sarlavhani torting To'lov uslubi hududga Ustun yorliqlari (ustunlar):
Biz natijaga erishamiz:
Juda zo'r ko'rinadi!
Endi uch o'lchamli jadval tuzamiz. Bunday stol qanday ko'rinishga ega bo'ladi? Ko'raylikchi…
Sarlavhani torting To'plam (Kompleks) hududga hisobot filtrlari (Filtrlar):
Uning qayerda ekanligiga e'tibor bering ...
Bu bizga hisobotni "Qaysi dam olish majmuasi uchun to'langanligi" asosida filtrlash imkoniyatini beradi. Masalan, biz barcha komplekslar uchun sotuvchilar va to'lov usullari bo'yicha taqsimotni ko'rishimiz yoki sichqonchani bir necha marta bosish orqali pivot jadvalining 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. Sozlashni davom ettiramiz…
Agar to'satdan pivot jadvalda faqat chek va kredit karta orqali to'lov (ya'ni naqd pulsiz to'lov) ko'rsatilishi kerakligi aniqlansa, biz sarlavhani ko'rsatishni o'chirib qo'yishimiz mumkin. naqd pul (Naqd pul). Buning uchun, yonida Ustun yorliqlari pastga o'qni bosing va ochiladigan menyuda belgini 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
PivotTables juda kuchli vosita ekanligi aniq, 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 o'zingiz uchun odatiy bo'lgan narsani 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 (bu 99% imkoniyat bilan sodir bo'ladi), formatlash yo'qoladi. Bizga kerak bo'lgan narsa - uni (deyarli) doimiy qilish usuli.
Birinchidan, kirishni topamiz Miqdor summasi in Qadriyatlar (Qiymatlar) va ustiga bosing. Ko'rsatilgan menyuda elementni tanlang Qiymat maydoni sozlamalari (Qiymat maydoni variantlari):
Muloqot oynasi paydo bo'ladi Qiymat maydoni sozlamalari (Qiymat maydoni variantlari).
matbuot Raqam formati (Raqam formati), dialog oynasi ochiladi. Hujayralarni formatlash (hujayra formati):
Ro'yxatdan kategoriya (Raqam formatlari) tanlang Buxgalteriya (Moliyaviy) va kasrlar sonini nolga qo'ying. Endi bir necha marta bosing OKbizning asosiy jadvalimizga qaytish uchun.
Ko'rib turganingizdek, raqamlar dollar miqdori sifatida formatlangan.
Biz formatlash bilan shug'ullanayotganimizda, keling, butun Pivot jadvali uchun formatni o'rnatamiz. Buning uchun bir necha usullar mavjud. Biz eng oddiyidan foydalanamiz ...
bosing Pivot Table asboblari: Dizayn (Pivot-jadvallar bilan ishlash: Konstruktor):
Keyinchalik, bo'limning pastki o'ng burchagidagi o'qni bosish orqali menyuni kengaytiring Pivot jadval uslublari (Pivot Table Uslublari) ichki uslublarning keng to'plamini ko'rish uchun:
Har qanday mos uslubni tanlang va pivot jadvalingizdagi natijaga qarang:
Exceldagi boshqa pivot jadval sozlamalari
Ba'zan ma'lumotlarni sanalar bo'yicha filtrlash kerak bo'ladi. Misol uchun, bizning savdolar ro'yxatida juda ko'p sanalar mavjud. Excel ma'lumotlarni kun, oy, yil va boshqalar bo'yicha guruhlash vositasini taqdim etadi. Keling, bu qanday amalga oshirilganini ko'rib chiqaylik.
Avval kirishni olib tashlang. To'lov uslubi mintaqadan Ustun yorliqlari (ustunlar). Buning uchun uni sarlavhalar ro'yxatiga qaytarib olib boring va uning o'rniga sarlavhani ko'chiring Buyurtma qilingan sana (bron qilingan sana):
Ko'rib turganingizdek, bu bizning asosiy 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 GROUP (Guruh):
Guruhlash dialog oynasi paydo bo'ladi. Biz tanlaymiz oy (Oylar) va bosing OK:
Voila! Ushbu jadval ancha foydali:
Aytgancha, ushbu jadval maqolaning boshida ko'rsatilgan bilan deyarli bir xil bo'lib, unda savdo jami qo'lda tuzilgan.
Siz bilishingiz kerak bo'lgan yana bir muhim nuqta bor! Siz bitta emas, balki bir nechta satr (yoki ustun) sarlavhalarini yaratishingiz mumkin:
... va bu shunday ko'rinadi ...
Xuddi shu narsani ustun sarlavhalari (yoki hatto filtrlar) bilan qilish mumkin.
Keling, jadvalning asl shakliga qaytaylik va yig'indi o'rniga o'rtachalarni qanday ko'rsatishni ko'rib chiqamiz.
Boshlash uchun ustiga bosing Miqdor summasi va paydo bo'lgan menyudan tanlang Qiymat maydoni sozlamalari (Qiymat maydoni variantlari):
Ro'yxat Qiymat maydonini sarhisob qiling (Operatsiya) dialog oynasida Qiymat maydoni sozlamalari (Qiymat maydoni variantlari) tanlang o'rtacha (O'rtacha):
Shu bilan birga, biz bu erda ekanmiz, keling, o'zgaraylik Maxsus nom (Maxsus nom) bilan O'rtacha miqdor (Miqdor maydoni miqdori) qisqaroq narsaga. Ushbu maydonga shunga o'xshash narsani kiriting O'rtacha:
matbuot OK va nima bo'lishini ko'ring. E'tibor bering, barcha qiymatlar jamidan o'rtachaga o'zgargan va jadval sarlavhasi (yuqori chap katakda) o'zgargan. O'rtacha:
Agar xohlasangiz, darhol bitta pivot jadvalga joylashtirilgan miqdorni, o'rtacha va raqamni (sotish) olishingiz mumkin.
Buni qanday qilish bo'yicha bosqichma-bosqich ko'rsatma, bo'sh pivot jadvaldan boshlab:
- Sarlavhani torting sotuvchi (savdo vakili) mintaqaga Ustun yorliqlari (ustunlar).
- Sarlavhani uch marta torting miqdor (narxi) hududga Qadriyatlar (Qiymatlar).
- Birinchi maydon uchun miqdor sarlavhasini o'zgartiring jami (Miqdor) va bu maydondagi raqam formati Buxgalteriya (Moliyaviy). O'nli kasrlar soni nolga teng.
- Ikkinchi maydon miqdor ism O'rtachae, buning uchun operatsiyani o'rnating o'rtacha (O'rtacha) va bu maydondagi raqam formati ham ga o'zgaradi Buxgalteriya (Moliyaviy) nol kasrli.
- Uchinchi maydon uchun miqdor sarlavha o'rnating hisoblash va unga operatsiya - hisoblash (Miqdor)
- In Ustun yorliqlari (Ustunlar) maydoni avtomatik ravishda yaratiladi S Qadriyatlar (S qiymatlari) – uni hududga torting Qator yorliqlari (chiziqlar)
Mana biz nima bilan yakunlaymiz:
Umumiy miqdor, o'rtacha qiymat va sotuvlar soni - barchasi bitta pivot jadvalda!
Xulosa
Microsoft Excel-dagi pivot jadvallari juda ko'p xususiyatlar va sozlamalarni o'z ichiga oladi. Bunday kichik maqolada ular hammasini qamrab olishga yaqin ham emas. Pivot jadvallarning barcha imkoniyatlarini to'liq tasvirlash uchun kichik kitob yoki katta veb-sayt kerak bo'ladi. Jasur va qiziquvchan o'quvchilar pivot jadvallarini o'rganishni davom ettirishlari 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 Design (Konstruktor). Xato qilishdan qo'rqmang, siz har doim Pivot Tableni o'chirib tashlashingiz va qaytadan boshlashingiz mumkin. Sizda uzoq vaqt davomida DOS va Lotus 1-2-3 foydalanuvchilarida bo'lmagan imkoniyat mavjud.