Pivot jadvallari hamma uchun yaxshi - ular tez hisoblab chiqadi va moslashuvchan tarzda sozlanadi va agar kerak bo'lsa, dizayn ularga chiroyli tarzda o'rnatilishi mumkin. Ammo malhamda bir nechta chivinlar ham bor, xususan, xulosani yaratish imkoniyati yo'qligi, bu erda qiymat maydoni raqamlarni emas, balki matnni o'z ichiga olmaydi.
Keling, ushbu cheklovni engib o'tishga harakat qilaylik va shunga o'xshash vaziyatda "bir nechta qo'ltiq tayoqchasini" ishlab chiqaylik.
Faraz qilaylik, kompaniyamiz o'z mahsulotlarini konteynerlarda Yurtimiz va Qozog'istonning bir qancha shaharlariga olib boradi. Konteynerlar oyiga bir martadan ko'p bo'lmagan holda yuboriladi. Har bir konteynerda alfanumerik raqam mavjud. Dastlabki ma'lumotlar sifatida etkazib berishlar ro'yxatining standart jadvali mavjud bo'lib, har bir shaharga va har oyga yuborilgan konteynerlar sonini aniq ko'rish uchun siz qandaydir xulosa qilishingiz kerak:
Qulaylik uchun, keling, buyruq yordamida dastlabki ma'lumotlarga ega jadvalni oldindan "aqlli" qilaylik Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash) va unga ism bering Yetkazib berish tab konstruktor (Dizayn). Kelajakda bu hayotni soddalashtiradi, chunki. jadval nomini va uning ustunlarini to'g'ridan-to'g'ri formulalarda ishlatish mumkin bo'ladi.
1-usul. Eng oson - Power Query-dan foydalaning
Power Query Excelga ma'lumotlarni yuklash va o'zgartirish uchun juda kuchli vositadir. Ushbu plagin 2016 yildan beri sukut bo'yicha Excelga o'rnatilgan. Agar sizda Excel 2010 yoki 2013 bo'lsa, uni alohida yuklab olishingiz va o'rnatishingiz mumkin (to'liq bepul).
Butun jarayon, aniqlik uchun men quyidagi videoda bosqichma-bosqich tahlil qildim:
Agar Power Query-dan foydalanish imkoni bo'lmasa, siz boshqa yo'llarga - pivot jadval yoki formulalar orqali o'tishingiz mumkin.
2-usul. Yordamchi xulosa
Keling, asl jadvalimizga yana bitta ustun qo'shamiz, bu erda oddiy formuladan foydalanib, jadvaldagi har bir qatorning sonini hisoblaymiz:
Shubhasiz, -1 kerak, chunki bizning jadvalimizda bir qatorli sarlavha mavjud. Agar sizning jadvalingiz varaqning boshida bo'lmasa, siz joriy qator va jadval sarlavhasi raqamlaridagi farqni hisoblaydigan biroz murakkabroq, ammo universal formuladan foydalanishingiz mumkin:
Endi, standart tarzda, biz ma'lumotlarimiz asosida kerakli turdagi pivot jadvalini tuzamiz, ammo qiymat maydonida biz maydonni qoldiramiz. Qator raqami biz xohlagan narsaning o'rniga idish:
Xuddi shu oyda bir shaharda bir nechta konteynerimiz yo'qligi sababli, bizning xulosamiz aslida miqdorni emas, balki bizga kerak bo'lgan konteynerlarning qator raqamlarini beradi.
Bundan tashqari, yorliqda umumiy va oraliq jamilarni o'chirib qo'yishingiz mumkin Konstruktor - umumiy yig'indi и Oraliq summalar (Dizayn - Umumiy jami, oraliq jami) va xuddi shu joyda tugma yordamida xulosani qulayroq jadval tartibiga o'tkazing Hisobot maketi (Hisobot tartibi).
Shunday qilib, biz allaqachon natijaning yarmidamiz: bizda shahar va oyning kesishmasida manba jadvalidagi qator raqami mavjud bo'lgan jadval mavjud, u erda bizga kerak bo'lgan konteyner kodi joylashgan.
Keling, xulosani nusxalaymiz (xuddi shu varaqga) va uni qiymatlar sifatida joylashtiramiz va keyin formulamizni qiymat maydoniga kiritamiz, bu xulosada joylashgan qator raqami bo'yicha konteyner kodini chiqaradi:
vazifa IF (Agar), bu holda, xulosadagi keyingi katak bo'sh emasligini tekshiradi. Agar bo'sh bo'lsa, "" bo'sh matn qatorini chiqaring, ya'ni katakchani bo'sh qoldiring. Agar bo'sh bo'lmasa, ustundan chiqarib oling konteyner manba jadvali Yetkazib berish funksiyadan foydalangan holda qator raqami bo'yicha hujayra tarkibi INDEKS (INDEKS).
Ehtimol, bu erda unchalik aniq bo'lmagan yagona nuqta - bu qo'sh so'z konteyner formulada. Bunday g'alati yozish shakli:
Materiallar[[Idish]:[Idish]]
… faqat ustunga murojaat qilish uchun kerak konteyner mutlaq edi (oddiy "aqlli bo'lmagan" jadvallar uchun $ belgilariga ega bo'lgan ma'lumotnoma kabi) va formulamizni o'ngga nusxalashda qo'shni ustunlarga o'tmadi.
Kelajakda, manba jadvalidagi ma'lumotlarni o'zgartirganda Yetkazib berish, biz yordamchi xulosani sichqonchaning o'ng tugmasi bilan bosish va buyruqni tanlash orqali satr raqamlari bilan yangilashni unutmasligimiz kerak. Yangilash va saqlash (yangilash).
3-usul. Formulalar
Ushbu usul oraliq pivot jadvalini yaratishni va qo'lda yangilashni talab qilmaydi, lekin Excelning "og'ir quroli" - funktsiyadan foydalanadi. SUMMESLIMN (SUMIFS). Xulosadagi qator raqamlarini qidirish o'rniga, ularni quyidagi formuladan foydalanib hisoblashingiz mumkin:
Ba'zi tashqi kattalik bilan, aslida, bu tanlab yig'ish funktsiyasi uchun standart foydalanish holatidir SUMMESLIMNBerilgan shahar va oy uchun qator raqamlarini jamlaydigan A. Shunga qaramay, bir oyda bir shaharda bir nechta konteynerimiz yo'qligi sababli, bizning funktsiyamiz, aslida, miqdorni emas, balki chiziq raqamini o'zi beradi. Va keyin oldingi usuldan allaqachon tanish bo'lgan funktsiya INDEKS Siz konteyner kodlarini ham chiqarib olishingiz mumkin:
Albatta, bu holda siz endi xulosani yangilash haqida o'ylashingiz shart emas, lekin katta jadvallarda funksiya SUMMESLI sezilarli darajada sekinlashishi mumkin. Keyin formulalarni avtomatik yangilashni o'chirib qo'yishingiz yoki birinchi usuldan - pivot jadvalidan foydalanishingiz kerak bo'ladi.
Agar xulosaning ko'rinishi hisobotingiz uchun unchalik mos kelmasa, siz undan satr raqamlarini yakuniy jadvalga biz qilgandek to'g'ridan-to'g'ri emas, balki funktsiyadan foydalanib olishingiz mumkin. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Buni qanday qilishni bu erda topishingiz mumkin.
- Pivot jadval yordamida hisobotni qanday yaratish mumkin
- Pivot jadvallarida hisob-kitoblarni qanday o'rnatish kerak
- SUMIFS, COUNTIFS va boshqalar bilan tanlab hisoblash.