Excel'da akt sverka jadvali: ustunlar va tekshiruv formulalari

Akt sverkani qo'lda solishtirish uchun ikki qog'oz ro'yxatni yonma-yon qo'yib, har bir qatorni ko'z bilan tekshirish kerak. Yuz qatorda bu bir soat oladi va baribir xato qoladi. Excel'da esa ikki tomonning ro'yxatini ikkita varaqqa joylashtirib, mos kelmagan qatorlarni bir necha daqiqada topish mumkin. Maxsus dastur shart emas: bir nechta ustun va to'rt-besh formula yetadi. Quyida Excel'da akt sverka jadvali: ustunlar, formulalar va tuzoqlar.

Ikki varaq, bir xil ustunlar

Ikkita varaq oching: «Biz» (sizning ro'yxatingiz) va «Hamkor» (uning aktidagi ro'yxat). Ikkalasida ustunlar bir xil bo'lsin:

• A: hujjat turi (jo'natma, qaytarish, to'lov);
• B: hujjat raqami;
• C: sana;
• D: summa, bunda jo'natma musbat, qaytarish va to'lov manfiy bo'lib yoziladi;
• E: kalit ustuni, formula bilan to'ldiriladi.

Ikkala tomonda ishorali summa bir xil bo'lsa, ustun jami qoldiq o'zgarishini beradi, uni to'g'ridan-to'g'ri aktdagi bilan solishtirish mumkin.

Kalit va asosiy formulalar

Kalit har bir hujjatning noyob belgisi: raqam va sana. Birinchi qatorning E2 katagiga yozing: =TRIM(B2)&"|"&C2. Sana haqiqiy sana bo'lsa, ikkala varaqda ham bir xil son chiqadi. Rus tilidagi Excel'da TRIM — СЖПРОБЕЛЫ. Ajratuvchi sozlamaga qarab nuqtali vergul yoki vergul bo'lishi mumkin.

«Biz» varaqiga yana uchta ustun qo'shing:

• F: hamkorda shunday kalit bormi: =COUNTIF(Hamkor!E:E;E2). Natija 0 bo'lsa, hujjat hamkorda yo'q (rus tilida СЧЁТЕСЛИ);
• G: hamkordagi summa: =SUMIF(Hamkor!E:E;E2;Hamkor!D:D) (СУММЕСЛИ);
• H: farq: =ROUND(D2-G2;2) (ОКРУГЛ).

Holat ustuni I: =IF(F2=0;"hamkorda yo'q";IF(H2<>0;"summa farqi";"mos")) (ЕСЛИ). Xuddi shu ustunlarni «Hamkor» varaqiga ham, endi «Biz» varaqiga havola qilib qo'shing: shunda bizda yo'q hujjatlar ham ko'rinadi.

Kalit raqam va sanadan iborat bo'lgani uchun raqami bir xil, sanasi boshqa hujjat ham «hamkorda yo'q» deb chiqadi. Bunday juftliklarni ajratish uchun yana bir ustun qo'shing, faqat raqam bo'yicha tekshiruv: =COUNTIF(Hamkor!B:B;B2). Kalit bo'yicha natija 0, raqam bo'yicha 1 chiqsa, hujjat hamkorda bor, lekin sanasi farq qiladi. Bu oy oxiridagi klassik holat: yuk bir kunda jo'natilgan, nakladnoy ikki xil kunda kiritilgan. Shu bitta ustun «hamkorda yo'q» guruhidan sana farqlarini ajratib beradi va sababni bir qarashda ko'rsatadi.

Natijani o'qish

Jadvalga filtr qo'ying va holati «mos» bo'lmagan qatorlarni ko'rsating. Qolganlar uchta guruhga tushadi: hamkorda yo'q, bizda yo'q va summa farqi. Har birini sababi bilan to'ldirish uchun yana bir ustun qo'shing: sana, takror, narx, qaytarish va hokazo. Nazorat: «Biz» va «Hamkor» varaqlaridagi jami summalar farqi quyidagiga teng bo'lishi kerak: «hamkorda yo'q» qatorlari summasi, plyus «summa farqi» qatorlaridagi farqlar, minus «bizda yo'q» qatorlari summasi. Teng bo'lmasa, qayerdadir qator chetda qolgan.

Tuzoqlar

• Raqam matn sifatida saqlanishi: «125» va «00125» turlicha ko'rinadi. Bir xil formatga keltiring.
• Ko'rinmas bo'sh joylar. TRIM bularni olib tashlaydi.
• Sana matn bo'lib qolishi: bunda kalit ikki varaqda turlicha chiqadi. Sana ustunini sanaga o'tkazing.
• Kasr belgisi: bir tomonda nuqta, ikkinchi tomonda vergul bo'lsa, summa matnga aylanadi.
• Bir xil raqam turli yillarda takrorlanishi. Shu sabab kalitga sana ham kiritilgan.
• Yashirin qatorlar va yoqilgan filtr: jami summani hisoblashdan oldin hamma narsa ochiq ekanini tekshiring.
• Birlashtirilgan kataklar formulani buzadi, ulardan voz keching.

Shartli misol

«Biz» varaqida 40 qator, «Hamkor» varaqida 39 qator. Formulalardan keyin: 35 qator «mos», 3 qator «summa farqi», 2 qator «hamkorda yo'q», «Hamkor» varaqida esa 1 qator «bizda yo'q». Summa farqi bo'yicha uchta qator narx farqi sababli ekan. Hamkorda yo'q ikkita hujjat oyning oxirgi kunida kiritilgan nakladnoylar. Bizda yo'q qator esa hamkor yozgan qaytarish. Jami farq yuqoridagi formula bo'yicha teng chiqdi: ish tugadi.

Xulosa

Excel'da akt sverka jadvali bir marta tayyorlanadi va har safar qayta ishlatiladi. Kalit, to'rt formula va jami nazorati qo'lda solishtirishdan ancha ishonchli. Formulalar farqni topadi, sababini esa hujjat tushuntiradi. SkladQarz Excel yoki 1C'dagi ro'yxatni hamkor ma'lumoti va omborning haqiqiy qoldig'i bilan qatorma-qator tenglashtiradi, farqlar reestri va tavsiyalar bilan beradi.