Excelda «qoldiq = kirim − chiqim» formulasi

Excel jadvalida «qoldiq = kirim − chiqim» formulasini odatda har bir qatorga yozib, pastga tortishadi. Tovar kam bo'lganda bu ishlaydi. Lekin ro'yxat o'sib, qatorlar qo'shilib, jadval saralana boshlasa, tortilgan formulalar uziladi va qoldiq «sirli» tarzda o'zgarib ketadi. Ishonchliroq yo'l bor: harakatlarni bitta uzun jurnalga yozasiz, qoldiqni esa alohida varaqda formula o'zi sanaydi.

Ikki varaq: jurnal va qoldiq

Birinchi varaqni «Harakat» deb nomlang. Unda har bir kirim va chiqim alohida qator bo'ladi, ustunlar quyidagicha:

• A: sana;
• B: tovar kodi (masalan, LMP-09);
• C: tur (faqat «kirim» yoki «chiqim»);
• D: miqdor (doim musbat son);
• E: izoh (sotuv, singan, qaytgan, sovg'a).

Ikkinchi varaq «Qoldiq». Unda har bir tovar bir marta turadi: A ustunda kod, B ustunda nom, C ustunda hisoblangan qoldiq, D ustunda minimal qoldiq. Ishni boshlaganda har bir tovarning haqiqiy sanalgan sonini jurnalga birinchi qator qilib yozing: turi «kirim», izohi «boshlang'ich».

Bu usulning afzalligi shundaki, jurnalga ming qator qo'shsangiz ham formula bitta joyda turadi. Qatorni o'chirib yuborish yoki jadvalni saralash qoldiqni buzmaydi, har bir tovarning butun tarixini esa filtr bilan ko'rasiz.

Asosiy formula

«Qoldiq» varag'ining C2 katagiga quyidagini yozing:

=SUMIFS(Harakat!D:D; Harakat!B:B; A2; Harakat!C:C; "kirim") - SUMIFS(Harakat!D:D; Harakat!B:B; A2; Harakat!C:C; "chiqim")

Formula jurnaldan shu kodli barcha «kirim» qatorlarini qo'shadi va barcha «chiqim» qatorlarini ayiradi. Uni pastga tortasiz va ish tugaydi: yangi yozuvlar qo'shilgan sari qoldiq o'zi yangilanadi. Ba'zi kompyuterlarda nuqtali vergul o'rniga oddiy vergul ishlatiladi, ruscha Excelda esa funksiya СУММЕСЛИМН deb nomlanadi.

Misol: elektr jihozlari do'koni, LED lampochka 9 W, kodi LMP-09.

• 2 mart, boshlang'ich sanoq: kirim 120;
• 5 mart, yetkazib beruvchidan: kirim 200;
• 5 mart, kunlik sotuv: chiqim 35;
• 6 mart, kunlik sotuv: chiqim 48;
• 7 mart, singan: chiqim 2.

Hisob: 120 + 200 − 35 − 48 − 2 = 235 dona. Formula ham aynan shu sonni chiqaradi.

Nazorat ustunlari

«Qoldiq» varag'iga yana uchta ustun qo'shing:

• E, «Holat»: =IF(C2<=D2; "Buyurtma"; ""). Qoldiq minimaldan oshmasa, «Buyurtma» yozuvi chiqadi. Shartli formatlash bilan katakni qizil qilsangiz, ko'z tashlashning o'zi yetadi;
• F, «Sanalgan»: javonda haqiqatda sanalgan son, qo'lda yoziladi;
• G, «Farq»: =F2-C2. Nolga teng bo'lmagan qatorlar kamomad yoki ortiqcha tovar borligini ko'rsatadi.

Haftalik sanoqdan keyin G ustunini filtrlang: faqat farqi bor tovarlar qoladi, sababni izlash shulardan boshlanadi.

Masalan, LMP-09 bo'yicha javonda 231 dona sanaldi, formulada esa 235 turibdi. Farq −4: ehtimol, ikki dona sotuv yozilmagan, ikki donasi yetkazib berishda kam kelgan. Sababni darhol izlang va topsangiz, jurnalga izohi bilan tuzatish qatorini qo'shing. Eski qatorlarni o'chirmang: tuzatish ham tarixning bir qismi.

Yangi tovar va zaxira nusxa

Yangi tovar paydo bo'lsa, uni «Qoldiq» varag'ining oxiriga qo'shing: kod, nom va minimal qoldiqni yozing, C, E va G ustunlaridagi formulalarni shu qatorgacha tortib qo'ying, jurnalga esa boshlang'ich qatorni kiriting. Jurnal faylini haftada bir marta sana bilan nusxalang (masalan, qoldiq-07-mart) va nusxani bulutga yoki Telegramdagi o'zingizning chatingizga yuklab qo'ying. Bitta buzilgan fayl butun oylik hisobni yo'qotib qo'yishi mumkin.

Excelda tez-tez to'sqinlik qiladigan narsalar

1. Kodlar bir xil yozilsin. «LMP-09» bilan «LMP-09 » (oxirida bo'sh joy) Excel uchun ikki xil tovar.
2. Kasr sonlar. Kabelni metrda yuritsangiz, ko'p kompyuterda vergul ishlating (12,5). Nuqta bilan yozilgan «12.5» sanaga yoki matnga aylanib ketishi mumkin, formula esa uni qo'shmaydi.
3. Tur ro'yxatdan tanlansin. «Ma'lumotlar → Ma'lumotlarni tekshirish → Ro'yxat» orqali C ustuniga faqat «kirim, chiqim» variantlarini qo'ying. «Chiqm» deb xato yozilgan qator hisobga tushmay qoladi.
4. Birlik bitta bo'lsin. Lampochka donada, kabel metrda: buni bir marta belgilang va jurnalga boshqacha yozmang.
5. Birlashtirilgan kataklardan qoching, ular formulalarni buzadi.

Xulosa

Jurnal, qoldiq varag'i va bitta formula: kichik do'kon uchun shu uch narsa yetarli, dasturga o'tishga hojat qoldirmaydi. Faqat boshlang'ich raqam haqiqiy bo'lishi shart. Sanoqqa vaqtingiz yetmasa, Sanab jamoasi tovarni sanab, natijani Excel jadvalida topshiradi: uni «boshlang'ich» qatorlar sifatida to'g'ridan-to'g'ri jurnalga qo'yish mumkin.