Excel: pivot chuqurroq, ABC tahlil va dashboard

5-bob 3 kod bloki 1532 so'z

4-bobda pivot bilan 60 sekundda hisobot yasadik. Bu bobda pivotning ishchi rejimi: yordamchi ustunlar, hisoblanadigan maydon va uning tuzog'i, foizda ko'rsatish, top-N, va ikkita klassik biznes tahlili — ABC (qaysi mahsulot pul keltiradi) va XYZ (qaysisi barqaror). Oxirida bir varaqlik dashboard.

Yordamchi ustun: pivotdan oldingi qadam

order_items varag'ida savdo summasi yo'q — faqat quantity va unit_price bor. Mahsulot nomi ham yo'q, faqat product_id. Pivot esa faqat bor ustunlarni yig'a oladi. Demak avval ikkita yordamchi ustun yozamiz:

H2 = XLOOKUP(C2, products!A:A, products!B:B)      → mahsulot nomi
I2 = IF(G2="delivered", D2*E2, 0)                  → yetkazilgan bo'lsa savdo summasi

Birinchisi — 4-bobdagi XLOOKUP, ikkinchisi — qator darajasidagi hisob. Formulani bitta katakka yozib, ustunning oxirigacha cho'zasiz (Ctrl+Shift+↓, keyin Ctrl+D). 39 629 qator uchun Excel buni bir necha soniyada bajaradi.

Nega IF ichida? Chunki bekor qilingan va qaytarilgan qatorlar savdo emas. Filtrlashni pivotda ham qilish mumkin edi, lekin ustunda qilinsa, hamma pivot avtomatik to'g'ri bo'ladi.

Hisoblanadigan maydon va uning tuzog'i

Pivotning o'zida ham formula yozish mumkin: PivotTable Analyze → Fields, Items & Sets → Calculated Field. Mantiqan tushum = quantity * unit_price deb yozish kerakdek tuyuladi. Natijaga qarang:

Hisoblanadigan maydon bilan: eng yaxshi mahsulot 3 012 mlrd so'm, jami 118 995 mlrd. Butun do'konning yillik savdosi esa 33 mlrd. Raqam mingdan ortiq barobar katta.

Sabab: hisoblanadigan maydon formulani har qatorga emas, yig'indilarga qo'llaydi. Ya'ni SUM(quantity) * SUM(unit_price) hisoblanadi — hamma sonlarning yig'indisi bir-biriga ko'paytiriladi. Yuzlab qatorli guruhda bu raqam ma'nosiz katta chiqadi.

To'g'ri yo'l — yuqoridagi yordamchi ustun: ko'paytirish har qatorda bajariladi, pivot esa tayyor sonlarni yig'adi.

Yordamchi ustun bilan: Noutbuk Lenovo IdeaPad 5.18 mlrd so'm. Bu raqam SQL va pandas bergan raqam bilan bir xil.

Qoida: pivotda ko'paytirish va bo'lish kerak bo'lsa, yordamchi ustun yozing. Hisoblanadigan maydon faqat qo'shish/ayirish uchun xavfsiz (masalan, savdo - xarajat). Bu Excel ning eng ko'p uchraydigan jim xatolaridan biri: hech qanday ogohlantirish chiqmaydi, hisobot esa yolg'on.

Tartiblash va top-N

Ikkala jadvalda ham ikkita sozlama ishlatildi. Tartiblash: qiymat ustuniga o'ng tugma → Sort → Largest to Smallest. Top-N: qator maydoniga o'ng tugma → Filter → Top 10 (sonini o'zgartirish mumkin). Ikkalasi ham pivotda saqlanadi: ma'lumot yangilanganda tartib va filtr o'zi qayta qo'llanadi.

Diqqat: top-10 filtri qo'yilganda "Grand Total" faqat ko'rinayotgan 10 qatorning yig'indisini ko'rsatadi. Hisobotda "jami" deb shu raqamni yozib yubormang.

Foizda ko'rsatish: ABC tahlil

Qiymat ustuniga o'ng tugma → Show Values As. Eng foydali ikkitasi: % of Grand Total (har qator jamining necha foizi) va % Running Total In (yig'ilib boruvchi foiz). Ikkinchisi — ABC tahlilning o'zi.

ABC savoli: tushumning 80 foizini qaysi mahsulotlar beradi? Pareto qoidasi bo'yicha odatda mahsulotlarning kichik qismi. Filtrsiz, hamma 82 mahsulotni tushum bo'yicha tartiblab, yig'ilib boruvchi foizni ko'ramiz:

Yig'ilib boruvchi foiz: birinchi mahsulot tushumning 15.5%, birinchi uchtasi 32.9%, o'n uchtasi 73.8% ini beradi. Jadvalning birinchi 13 qatori ko'rsatilgan.

Sinflarga bo'lish chegaralari kelishuv bo'yicha olinadi: A — yig'ilib boruvchi ulush 80% gacha, B — 95% gacha, C — qolgani. Aniq sonlarni hisoblaymiz:

it = pd.read_csv(CSV / "order_items.csv")
pr = pd.read_csv(CSV / "products.csv")
d = it[it.item_status == "delivered"].copy()
d["tushum"] = d.quantity * d.unit_price
g = d.groupby(d.product_id.map(pr.set_index("product_id").product_name)).tushum.sum().sort_values(ascending=False)
ulush = g.cumsum() / g.sum()
sinf = pd.cut(ulush, [0, 0.8, 0.95, 1.0], labels=["A", "B", "C"])
show(pd.DataFrame({"mahsulotlar": sinf.value_counts().sort_index(),
                   "mahsulot ulushi": (sinf.value_counts().sort_index() / len(g) * 100).round(0),
                   "tushum ulushi": (g.groupby(sinf, observed=True).sum() / g.sum() * 100).round(1)}))

Natija klassik: mahsulotlarning 22 foizi tushumning 80 foizini beradi, eng pastki 37 ta mahsulot esa hammasi bo'lib 5 foizni. Qaror uchun ma'no: A sinfdagi 18 ta mahsulot hech qachon omborda tugamasligi kerak, C sinfdagilarni esa assortimentdan chiqarish yoki buyurtma bo'yicha ishlash mumkin. Marketing byudjeti ham shu tartibda bo'linadi.

XYZ: barqarorlikni o'lchash

ABC "qancha" degan savolga javob beradi, XYZ esa "qanchalik barqaror" degan savolga. O'lchov — variatsiya koeffitsiyenti (CV): standart chetlanishni o'rtachaga bo'lamiz. CV kichik bo'lsa, oylar bir-biriga o'xshaydi (X sinf, oldindan aytish oson); katta bo'lsa — sakrab turadi (Z sinf).

Excel da bu SUMIFS jadvali: qatorlar — kategoriya, ustunlar — oylar, keyin AVERAGE, STDEV.P va bo'lish.

Q2 = SUMIFS($I:$I, $J:$J, $L2, $K:$K, Q$1)      → kategoriya va oy bo'yicha tushum
M2 = AVERAGE(Q2:AB2)                             → o'rtacha oylik
N2 = STDEV.P(Q2:AB2) / M2                        → CV
O2 = IF(N2<=0.15, "X", IF(N2<=0.3, "Y", "Z"))    → sinf

XYZ tahlil: hamma kategoriya Z sinfida. CV 0.49 dan 0.60 gacha — oylar orasidagi farq katta.

Natijani o'qing: hammasi Z. Bu — tahlilning muvaffaqiyatsizligi emas, ma'lumot haqidagi xabar. Bu do'konning savdosi yil davomida o'sgan (1-bob), o'suvchi qatorda esa oylar orasidagi farq tabiiy ravishda katta bo'ladi va CV hamma narsani "beqaror" deb belgilaydi.

To'g'ri yo'l: avval trendni ajratish, keyin qolgan tebranishni o'lchash (20-bob: harakatlanuvchi o'rtacha va mavsumiylik). XYZ ni o'sish bo'lmagan yoki trenddan tozalangan qatorda ishlating. Bu — kitobning asosiy qoidasi: formulani qo'llashdan oldin, ma'lumot shu formulaning shartiga mos keladimi degan savolni bering.

Sana bo'yicha guruhlash

Bizning orders varag'ida tayyor oy ustuni bor, lekin real faylda faqat sana bo'ladi. Pivotda sana ustunini qator maydoniga qo'ying, ustiga o'ng tugma → Group → Months va Years ni tanlang. Excel avtomatik oy va yil darajalarini yasaydi; chorak ham shu ro'yxatda. Yil tanlanmasa, har xil yillarning bir xil oylari qo'shilib ketadi — bu ham jim xato.

Slicer: filtr tugmalari

Pivotni tanlab Insert → Slicer bosing va ustun tanlang. Varaqda tugmalar paydo bo'ladi: bosgan zahoti pivot filtrlanadi. Pivotning o'z filtri ham bor, lekin slicerning ustunligi shuki, qaysi filtr yoqilganini ko'rsatib turadi — hisobotni ochgan odam nimani ko'rayotganini biladi.

Oylik savdo va to'lov usuli sliceri. Hech narsa tanlanmagan: pivot hamma buyurtmalarni ko'rsatyapti, jami 35.86 mlrd so'm.

Endi slicerdan cash tanlaymiz:

Faqat naqd to'lov: jami 5.48 mlrd so'm — umumiy savdoning 15.3% i. Slicer tugmasi yonib turadi, o'ng yuqoridagi belgi esa filtrni tozalash uchun.

Bitta slicerni bir nechta pivotga ulash mumkin (Slicer → Report Connections): shunda bitta tugma butun sahifadagi jadval va diagrammalarni birga filtrlaydi. Dashboard aynan shunday yig'iladi — pastdagi bo'limga qarang.

Diqqat: slicer faqat o'zi ulangan pivotlarni filtrlaydi. Varaqdagi oddiy formulalar (SUMIFS va boshqalar) slicerni sezmaydi va eski raqamni ko'rsatib turaveradi. Bu — aralash hisobotlardagi eng ko'p uchraydigan nomuvofiqlik sababi.

PivotChart: pivotga bog'langan diagramma

Pivotni tanlab PivotTable Analyze → PivotChart bosing. Oddiy diagrammadan farqi: u pivotga bog'langan — filtrni o'zgartirsangiz yoki ma'lumotni yangilasangiz, diagramma ham o'zi o'zgaradi. Ustidagi tugmalar (field buttons) bevosita diagrammadan filtrlash imkonini beradi.

Top-10 pivotdan qurilgan PivotChart. Gorizontal bar tanlandi, chunki mahsulot nomlari uzun (22-bob qoidasi). Chapdagi tugmalar — pivot maydonlari: ulardan turib filtrlash mumkin.

Diqqat: bar diagrammada Excel eng katta qiymatni pastga qo'yadi. Ko'z yuqoridan pastga o'qigani uchun eng muhim qatorni tepaga chiqarish kerak — buning uchun pivotni teskari tartibda (Smallest to Largest) saralang yoki diagramma o'qi sozlamasida Categories in reverse order ni yoqing.

Bir varaqlik dashboard

Hisobot varag'ini yig'ish tartibi (30-bobdagi qoidalar bilan bir xil):

1. Ma'lumot alohida. Xom ma'lumot va yordamchi ustunlar bitta varaqda, pivotlar ikkinchisida, ko'rinadigan dashboard uchinchisida.
2. Yuqorida — eng muhim uchta raqam (jami savdo, o'rtacha chek, bekor foizi). Katta shrift, formatlangan son.
3. Chapda — filtrlar (slicerlar), chunki ko'z chapdan o'qiydi.
4. O'rtada — trend (oylik chiziq), pastda — bo'linishlar (hudud, kategoriya).
5. Grafik soni 4–6 tadan oshmasin. Ortig'i sahifani emas, o'quvchini yuklaydi.
6. Yangilash tartibi yozilsin: ma'lumot qayerdan keladi, kim yangilaydi, qachon. Eng yaxshisi — Power Query bilan bir tugma (6-bob).

Xulosa

Talaba ishi

1. Hisoblanadigan maydon bilan marja = unit_price - cost ni pivotga qo'shing (cost products da). Bu holda hisoblanadigan maydon to'g'ri ishlaydimi? Nega ko'paytirishda xato, ayirishda to'g'ri?
2. ABC ni kategoriya darajasida takrorlang. Nechta kategoriya A sinfiga tushadi? Mahsulot darajasidagi xulosa bilan solishtiring: qaysi biri qaror uchun foydaliroq?
3. Hududlar bo'yicha XYZ qiling (orders varag'idan total_amount, hudud uchun customers bilan XLOOKUP). Hamma hudud Z ga tushdimi? Endi har oyning o'sishini emas, o'tgan oyga nisbatan o'zgarishni ((oy - oldingi)/oldingi) oling va shu qator uchun CV hisoblang — natija o'zgaradimi?
4. Top-10 pivotdan ustun diagramma yasang (PivotChart) va slicer ulang. Slicerdan bitta hududni tanlaganda diagramma va "Grand Total" qanday o'zgaradi?