1-usul. Formulalar
Keling, isitish uchun eng oddiy variant - formulalar bilan boshlaylik. Agar bizda sana bo'yicha kiritilgan kichik jadval mavjud bo'lsa, unda ishlayotgan jami alohida ustunda hisoblash uchun bizga elementar formula kerak bo'ladi:
Bu erda asosiy xususiyat SUM funksiyasi ichidagi diapazonni qiyin aniqlashdir - diapazonning boshiga havola mutlaq (dollar belgilari bilan) va oxirigacha - nisbiy (dollarsiz) qilingan. Shunga ko'ra, formulani butun ustunga nusxalashda biz kengaytiruvchi diapazonni olamiz, uning yig'indisini hisoblaymiz.
Ushbu yondashuvning kamchiliklari aniq:
- Jadval sana bo'yicha saralanishi kerak.
- Ma'lumotlar bilan yangi qatorlarni qo'shganda, formulani qo'lda kengaytirish kerak bo'ladi.
2-usul. Pivot jadvali
Bu usul biroz murakkabroq, ammo juda yoqimli. Va yanada jiddiyroq muammoni ko'rib chiqaylik - 2000 qatorli ma'lumotlar jadvali, bu erda sana ustuni bo'yicha tartiblash yo'q, lekin takrorlashlar mavjud (ya'ni biz bir kunda bir necha marta sotishimiz mumkin):
Biz asl jadvalimizni "aqlli" (dinamik) klaviatura yorlig'iga aylantiramiz Ctrl+T yoki jamoa Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash), va keyin buyruq bilan uning ustiga pivot jadval quramiz Qo'shish - Pivot jadval (Qo'shish - umumiy jadval). Xulosadagi qatorlar maydoniga sanani va qiymatlar maydonida sotilgan tovarlar sonini qo'yamiz:
E'tibor bering, agar sizda Excelning unchalik eski bo'lmagan versiyasi bo'lsa, sanalar avtomatik ravishda yillar, choraklar va oylar bo'yicha guruhlanadi. Agar sizga boshqa guruh kerak bo'lsa (yoki umuman kerak bo'lmasa), uni istalgan sanani sichqonchaning o'ng tugmasi bilan bosish va buyruqlarni tanlash orqali tuzatishingiz mumkin. Guruhlash / guruhdan chiqarish (Guruhlash / Guruhdan chiqarish).
Agar siz nuqtalar bo'yicha olingan jamlamalarni ham, ishlayotgan jami ham alohida ustunda ko'rishni istasangiz, maydonni qiymat maydoniga tashlash mantiqan to'g'ri keladi. Sotildi maydonning dublikatini olish uchun yana - unda biz ishlayotgan jami ko'rsatkichni yoqamiz. Buning uchun maydonni o'ng tugmasini bosing va buyruqni tanlang Qo'shimcha hisob-kitoblar - jami jami (Qiymatlarni ko'rsatish - Ishlayotgan jami):
U erda siz foiz sifatida jami o'sish variantini tanlashingiz mumkin va keyingi oynada siz jamg'arish ketadigan maydonni tanlashingiz kerak - bizning holatlarimizda bu sana maydoni:
Ushbu yondashuvning afzalliklari:
- Katta hajmdagi ma'lumotlar tezda o'qiladi.
- Formulalarni qo'lda kiritish shart emas.
- Manba ma'lumotlarini o'zgartirganda, sichqonchaning o'ng tugmasi yoki "Ma'lumotlar - Hammasini yangilash" buyrug'i bilan xulosani yangilash kifoya.
Kamchiliklar bu xulosa ekanligidan kelib chiqadi, ya'ni unda siz xohlagan narsani qila olmaysiz (chiziqlarni kiritish, formulalar yozish, har qanday diagrammalarni qurish va h.k.) endi ishlamaydi.
3-usul: Power Query
Buyruq yordamida Power Query so'rovlar muharririga manba ma'lumotlari bilan "aqlli" jadvalimizni yuklaymiz Ma'lumotlar - Jadvaldan/diapazondan (Ma'lumotlar - Jadvaldan/diapazondan). Aytgancha, Excelning so'nggi versiyalarida u qayta nomlandi - endi u chaqiriladi Barglari bilan (Vaqtdan):
Keyin biz quyidagi amallarni bajaramiz:
1. Buyruq yordamida jadvalni sana ustuni bo'yicha o'sish tartibida tartiblang O'sish bo'yicha tartiblash jadval sarlavhasidagi filtr ochiladigan ro'yxatida.
2. Biroz vaqt o'tgach, ishlayotgan jami hisoblash uchun bizga tartibli qator raqami bilan yordamchi ustun kerak bo'ladi. Keling, uni buyruq bilan qo'shamiz Ustun qo'shish - indeks ustuni - 1 dan (Ustun qo'shish - indeks ustuni - 1 dan).
3. Bundan tashqari, ishlaydigan jami hisoblash uchun ustunga havola kerak Sotildi, bizning umumlashtirilgan ma'lumotlarimiz qaerda joylashgan. Power Query-da ustunlar ro'yxatlar (ro'yxat) deb ham ataladi va unga havolani olish uchun ustun sarlavhasini o'ng tugmasini bosing va buyruqni tanlang. Tafsilotlar (Tafsilotlarni ko'rsatish). Bizga kerak bo'lgan ifoda oldingi bosqich nomidan iborat formulalar qatorida paydo bo'ladi #"Indeks qo'shildi", biz jadval va ustun nomini qaerdan olamiz [Sotish] kvadrat qavs ichida ushbu jadvaldan:
Keyinchalik foydalanish uchun ushbu ifodani vaqtinchalik xotiraga nusxalang.
4. Keraksiz boshqa oxirgi qadamni o'chirib tashlang Sotildi va uning o'rniga buyruq bilan ishlaydigan jami hisoblash uchun hisoblangan ustunni qo'shing Ustun qo'shish - Maxsus ustun (Ustun qo'shish - Maxsus ustun). Bizga kerak bo'lgan formula quyidagicha ko'rinadi:
Mana funktsiya Roʻyxat.Range asl ro'yxatni oladi (ustun [Sotish]) va undan elementlarni birinchisidan boshlab chiqaradi (formulada bu 0, chunki Power Query-da raqamlash noldan boshlanadi). Qabul qilinadigan elementlar soni biz ustundan oladigan qator raqamidir [Indeks]. Shunday qilib, birinchi qator uchun bu funksiya faqat ustunning birinchi katakchasini qaytaradi Sotildi. Ikkinchi qator uchun - allaqachon birinchi ikkita hujayra, uchinchisi uchun - birinchi uchta va boshqalar.
Xo'sh, keyin funktsiya Ro'yxat.sum olingan qiymatlarni yig'adi va biz har bir satrda barcha oldingi elementlarning yig'indisini olamiz, ya'ni jami jami:
Bizga endi kerak bo'lmagan Indeks ustunini o'chirish va natijalarni "Uy - Yopish va yuklash" buyrug'i bilan Excelga qayta yuklash qoladi.
Muammo hal qilindi.
Tez va shiddatli
Asos sifatida, bu to'xtatilishi mumkin edi, lekin malhamda kichik bir chivin bor - biz yaratgan so'rov toshbaqa tezligida ishlaydi. Masalan, mening eng zaif kompyuterimda atigi 2000 qatordan iborat jadval 17 soniyada qayta ishlanadi. Agar ko'proq ma'lumot bo'lsa nima bo'ladi?
Tezlashtirish uchun siz maxsus List.Buffer funksiyasi yordamida buferlashni qo'llashingiz mumkin, bu unga argument sifatida berilgan ro'yxatni (ro'yxatni) RAMga yuklaydi, bu esa kelajakda unga kirishni sezilarli darajada tezlashtiradi. Bizning holatda, 2000-qatorli jadvalning har bir satrida bajariladigan jami hisoblashda Power Query kirishi kerak boʻlgan #“Qoʻshilgan indeks”[Sotildi] roʻyxatini buferlash mantiqan.
Buni amalga oshirish uchun Asosiy yorlig'idagi Power Query muharririda "Kengaytirilgan muharrir" tugmasini bosing (Uy - Kengaytirilgan muharrir) Power Query ichiga o'rnatilgan M tilida so'rovimizning manba kodini oching:
Va keyin u erda o'zgaruvchiga ega qatorni qo'shing Mening roʻyxatim, uning qiymati buferlash funktsiyasi tomonidan qaytariladi va keyingi bosqichda biz ro'yxatga qo'ng'iroqni ushbu o'zgaruvchi bilan almashtiramiz:
Ushbu o'zgarishlarni amalga oshirgandan so'ng, bizning so'rovimiz sezilarli darajada tezlashadi va 2000 qatorli jadvalni atigi 0.3 soniya ichida engib chiqadi!
Yana bir narsa, to'g'rimi? 🙂
- Pareto diagrammasi (80/20) va uni Excelda qanday qurish kerak
- Matnda kalit so'zlarni qidirish va Power Query-da so'rovlarni buferlash