Yuklamadan Excelda hisobot tuzish: oylar, toifalar va reytinglar

Har oy bir xil manzara: axborot tizimidan yoki hisob dasturidan jadval yuklab olinadi — sana, toifa, kontragent, miqdor, summa. Soʻng bir necha soat yigʻma jadval, yakunlar va diagrammalar tuzishga ketadi. Keyingi oyda hammasi yana takrorlanadi.

Bu ishni bir marta sozlab, keyin faqat yangi yuklamani qoʻyish bilan chiqish mumkin. Quyida usulni koʻrsatamiz.

Yuklamani tayyorlang

Hisobot sifati maʼlumot sifatiga bogʻliq. Yuklamada quyidagilarni tekshiring:

• sana ustunida haqiqiy sana bor, matn emas;
• summa va miqdor ustunlarida son bor, boʻsh joy yoki «1 200,50 soʻm» kabi matn emas;
• toifa va kontragent nomlari bir xil yozilgan;
• qatorlar takrorlanmagan.

Matn koʻrinishidagi sanani aniqlash oson: katak chap tomonga tekislanadi. Agar yuklamada sana «2025-03-14» kabi matn boʻlsa, uni =DATEVALUE(A2) yoki =DATE(LEFT(A2;4);MID(A2;6;2);RIGHT(A2;2)) bilan haqiqiy sanaga aylantiring.

Yordamchi ustunlar

Hisobotni osonlashtiradigan narsa — yordamchi ustunlar. Yuklamaning yoniga qoʻshing:

• Oy: =MONTH(A2)
• Yil: =YEAR(A2)

Shunda yakunlarda sana oraligʻini yozmasdan oy raqami bilan ishlaysiz.

Oylar boʻyicha yakunlar

Yanvar uchun summa:

=SUMIFS(Summa;Yil;2025;Oy;1)

Qolgan oylar uchun 1 oʻrniga 2, 3 va hokazo (yoki oy raqami turgan katakka havola). Miqdor — shu formula, boshqa ustun bilan. Misol: yanvar 12 400 000, fevral 13 640 000, mart 15 000 000 soʻm.

Oʻsish: oldingi oyga va oʻtgan yilga nisbatan

Oldingi oyga nisbatan oʻsish:

=(Mart-Fevral)/Fevral

Misol: (15 000 000 − 13 640 000) / 13 640 000 = 0,0997 — taxminan +10,0%. Fevralga nisbatan: (13 640 000 − 12 400 000) / 12 400 000 = +10,0%.

Oʻtgan yilning shu oyiga nisbatan oʻsish mavsumiylikni hisobga oladi: mart martga solishtiriladi, fevralga emas. Formula bir xil, faqat solishtiriladigan katak oʻtgan yil martidagi summa. Boʻlinuvchi nol boʻlishi mumkin, shuning uchun:

=IFERROR((B3-B2)/B2;"")

Reytinglar: top-5 toifa va top-10 kontragent

Toifa boʻyicha summa:

=SUMIFS(Summa;Toifa;A2)

Toifalar roʻyxatini noyob qiymatlar sifatida alohida varaqqa chiqaring (Data → Remove Duplicates yordam beradi). Eng kattasini topish:

=LARGE(B2:B31;1)

Va unga mos nomni olish:

=INDEX(A2:A31;MATCH(LARGE(B2:B31;1);B2:B31;0))

Bu yerda 1 oʻrniga 2, 3, 4, 5. Kontragentlar uchun xuddi shunday, 10 gacha. Muhim: ikki toifa summasi tengligi uchrasa, MATCH birinchisini ikki marta qaytaradi — ehtiyot boʻling.

Toifa × oy matritsasi

Jadvalning qatorlarida — toifalar, ustunlarida — oylar, har bir katakda SUMIFS:

=SUMIFS(Summa;Toifa;$A2;Oy;B$1)

Bitta formulani butun jadvalga tortasiz ($ belgilari qator va ustunni qotiradi). Natija — qaysi toifa qaysi oyda oʻsganini bir qarashda koʻrsatadigan matritsa. Pivot jadval (PivotTable) ham shuni qiladi, ammo yangi yuklamada uni yangilash kerak boʻladi, formulalar esa oʻzi qayta hisoblanadi.

Maʼlumotlarni tekshirish

Hisobotga ishonishdan oldin tez tekshiruv:

• boʻsh sanalar soni: =COUNTBLANK(A2:A5000);
• sana emas (matn): =SUMPRODUCT(--ISTEXT(A2:A5000)) − boʻsh katakchalar hisoblanmaydi;
• davrdan tashqari sanalar;
• dublikatlar: =COUNTIFS(...)>1.

Bu hisoblagichlar nolga teng boʻlmasa, avval maʼlumotni tuzating, keyin xulosaga qarang.

Odatiy xatolar

• Yakunni qoʻlda koʻchirib qoʻyish: yuklama yangilansa, raqam eskirib qoladi.
• Butun ustunga havola (A:A) bilan juda katta formulalar: fayl sekinlashadi. Oraliqni cheklang.
• Toifa nomidagi ortiqcha boʻsh joylar: «Oziq-ovqat » va «Oziq-ovqat» ikki xil toifa.
• Yilni hisobga olmaslik: mart 2024 va mart 2025 bir yigʻindiga qoʻshiladi.

Hisobotni takrorlash tartibi

Sozlangan hisobotdan har oy foydalanish uchun tartib oddiy: eski yuklamani oʻchiring, yangisini qoʻying, yilni tekshiring, maʼlumotlarni tekshirish hisoblagichlariga qarang va faqat shundan keyin xulosani oching. Agar yuklamaning ustun tartibi oʻzgarsa, formulalarni emas, faqat ustun nomlarini moslang. Shunda hisobot bir soatda emas, bir necha daqiqada tayyor boʻladi.

Tayyor shablon: yuklamadan universal hisobot

Tayyor Excel shabloni shu tamoyilni tayyor holda beradi: yuklamani qoʻyasiz, ustun nomlarini moslaysiz, xulosa tayyor.

• «Parametrlar» — hisobot yili va yuklamangiz ustunlarining nomlari;
• «Maʼlumotlar» — sarlavha va qoʻyish uchun 5000 qator, normallashtirish xizmat ustunlari (oy, yil, kalitlar);
• «Xulosa» — oylar boʻyicha yakunlar, oldingi oyga va oʻtgan yilning shu oyiga nisbatan oʻsish, top-5 toifa, top-10 kontragent, «toifa × oy» matritsasi, ustunli va doiraviy diagrammalar;
• «Davr tanlash» — tanlangan toifalar boʻyicha istalgan davr uchun summa va miqdor;
• «Maʼlumotlarni tekshirish» — boʻsh sanalar, son oʻrniga matn, davrdan tashqari sanalar, dublikatlar: hisoblagichlar va izohlar bilan.

Cheklovlar: 5000 qatorgacha, 30 toifagacha va 200 kontragentgacha. Hisobchilar, menejerlar va boʻlim rahbarlari uchun. Shablon pullik. Excel 2007 va undan yangi versiyalar hamda LibreOffice bilan ishlaydi, makroslarsiz va yangi funksiyalarsiz.

Koʻp beriladigan savollar

Yuklama boshqa ustun tartibida boʻlsa nima qilish kerak?

Ustun nomlari «Parametrlar» varagʻida sozlanadi, shuning uchun yuklama tartibini oʻzgartirish shart emas. Sana, toifa, kontragent, miqdor va summa mavjud boʻlsa yetarli.

Mingdan koʻp qator boʻlsa, formulalar sekinlashadimi?

5000 qatorgacha Excel odatda muammosiz ishlaydi. Hajm shundan oshsa, yuklamani davrlar boʻyicha boʻlib yuklash qulayroq.

Pivot jadvaldan nima farqi bor?

Pivot jadval moslashuvchan, lekin yuklama oʻzgarganda yangilash kerak va diagrammalar sozlanadi. Formulali hisobot esa tayyor tuzilmaga ega, yangi maʼlumot qoʻyilsa, oʻzi qayta hisoblanadi.