Шарттар тізілімін Excel-де қалай жүргізуге болады

Шарттар үш-төрт жерде жатқанда мерзімді өткізіп алу уақыт мәселесі ғана. Жалдау шарты өздігінен ұзарады, жеткізушімен келісімнің мерзімі аяқталады, ал бір клиент соңғы төлемді төлемегенін ешкім көрмейді. Тізілім болмаса, мұны бәрі тек мәселе туындағанда біледі.

Excel-дегі шарттар тізілімі бұл жағдайды ретке келтіреді: бір жол бір шарт, мерзім мен төлем бір жерде, ал ескерту алдын ала шығады.

Мақалада тізілімнің құрылымын, мерзімді бақылау формулаларын, қалдық пен берешекті есептеуді, жиі қателерді және дайын үлгіні қарастырамыз.

Тізілім құрылымы

Бір шарт — бір жол. Ең қажетті бағандар:

Түр мен контрагентті ашылмалы тізімнен таңдаңыз (Data Validation). Сонда бір контрагент бес түрлі жазылмайды, ал кейін сүзгі мен жиынтық дұрыс жұмыс істейді.

Мерзімді бақылау

Аяқталуына дейінгі күнді былай есептейсіз:

=IF(E2="мерзімсіз";"";E2-TODAY())

Ескерту бағанын осыдан жасайсыз:

=IF(F2="";"";IF(F2<0;"Мерзімі өтті";IF(F2<=30;"Жақында аяқталады";"Жарамды")))

Бұл жерде 30 — ескертуге дейінгі күн саны. Оны бір параметр ұяшығына шығарыңыз, мысалы $K$1. Бағдаршам үшін шартты пішімдеуде жасыл, сары және қызыл түсті қолданыңыз. Мерзімі аяқталуға жақын шарттар бірден көзге түседі.

Төлемдер, қалдық және берешек

Төлемдерді бөлек парақта жазыңыз: күні, шарт нөмірі, сома. Тізілімде төленгенін SUMIFS жинайды:

=SUMIFS(Төлемдер!C:C;Төлемдер!B:B;A2)

Қалдық пен берешек:

• Қалдық = шарт сомасы − төленгені
• Берешек = қалдық, егер шарттың мерзімі өтіп кеткен болса, әйтпесе 0

Excel-де: =IF(AND(F2<0;H2>0);H2;0), мұнда F2 — мерзімге дейінгі күн, H2 — қалдық.

ҚҚС-ты ескеру

Шарт сомасы ҚҚС-мен («қоса»), ҚҚС үстінен немесе ҚҚС-сыз болуы мүмкін. Егер сома «қоса» болса, ҚҚС-сыз сома = сома / (1 + мөлшерлеме). Мөлшерлемені бір параметр ұяшығында ұстаңыз және әрдайым қолданыстағы мәнімен тексеріңіз.

Санмен мысал

Жалдау шартының сомасы 1 200 000 ₸, мерзімі 31 желтоқсанда аяқталады, төленгені 800 000 ₸.

• Қалдық: 1 200 000 − 800 000 = 400 000 ₸
• Бүгін 5 желтоқсан болса, аяқталуына 26 күн қалды, статус «Жақында аяқталады»
• Мерзімі өткен соң қалдық төленбесе, 400 000 ₸ берешекке өтеді

Кестеде мұндай шарттарды бірден сүзіп, жауаптыға еске салуға болады.

Жиі кездесетін қателер

• Мерзімсіз шартты бос қалдыру. Формула қате береді, сондықтан «мерзімсіз» деген мәнді келісіп қойыңыз.
• Қосымша келісімдерді тіркемеу. Мерзім не сома өзгерсе, жолды жаңартыңыз.
• Контрагентті әртүрлі жазу.
• Төлемдерді тізілімге қолмен жазу. Бөлек журнал жүргізіп, қорытындыны формуламен алған жөн.
• Ескертуге дейінгі күн санын тым аз қою. Ұзарту келісімге уақыт қажет.

Тізілімді жұмыста пайдалану

Тізілім тек толтырылса, пайдасы аз. Айына бір рет, мысалы айдың бірінші жұмыс күні, «Жақында аяқталады» күйіндегі шарттарды сүзіп, әр жауаптыға тізімін жіберіңіз. Бұл жиырма минуттық әдет көптеген ұзартылмай қалған шарттан сақтайды.

Басқа пайдалы көрініс: контрагент бойынша жалпы қалдықты көру. Бір жеткізушімен немесе клиентпен бірнеше шарт болса, жалпы берешек шарттардың әрқайсысынан көрінбей қалады. SUMIFS арқылы контрагент бойынша жиынтық жасап, ең үлкен бес қалдықты алдымен қарастырыңыз.

Деректерді ретке келтіру үшін жаңа шартты бірден тізілімге енгізу ережесін белгілеңіз: қол қойылған күні жол жасалады, әйтпесе кейін мұны ешкім жасамайды.

Дайын шарттар тізілімі үлгісі

Кестені өзіңіз құрмай-ақ дайын ақылы үлгіні қолдануға болады.

• «Параметрлер»: ҚҚС мөлшерлемесі мен режимі, ескертуге дейінгі күн саны, шарт түрлері, контрагенттер және жауаптылар анықтамалықтары.
• «Тізілім»: 300 шартқа дейін. Әрекет ету мерзімі (күн немесе «мерзімсіз»), сома, ҚҚС «қоса» немесе «үстіне», ұзарту, жауапты, төленгені, қалдық, берешек, ескертулер және бағдаршам.
• «Төлемдер»: шарт нөмірі бойынша 1500 төлемге дейін.
• «Мерзімі бітетіндер»: мерзімі аяқталуға жақын шарттардың тізімі, шұғылдығы бойынша сұрыпталған.
• «Жиынтық»: түрлер мен контрагенттер бойынша, қалдық бойынша топ-10, айлар бойынша аяқталу диаграммасы.

Макростар мен сыртқы байланыстар жоқ: файл Excel 2007 және одан жаңа нұсқаларда және LibreOffice-те ашылады. ҚҚС мөлшерлемесі өзгертілетін параметр. Файл қазақ тілінде.

Жиі қойылатын сұрақтар

Тізілімді кім жүргізуі керек?

Бір жауапты адам белгіленсе жақсы: заңгер, бухгалтер немесе әкімші. Ал әр шарттың өз жауаптысы болуы да пайдалы, сонда мерзім жақындағанда кімге айту керегі белгілі.

Шарттың көшірмесін қайда сақтау керек?

Кестеде файлдың орнын немесе қапшық атауын жазыңыз. Шарттың өзін бөлек, реттелген қапшықта сақтаған дұрыс.

Ескертуді қанша күн бұрын қою керек?

Жалдау мен жабдықтау үшін 30–60 күн жиі жеткілікті. Күрделі келісім қажет шарттарға көбірек уақыт беріңіз.

Тізілім құқықтық құжат па?

Жоқ, бұл ішкі бақылау құралы. Шарттың заңды күші қол қойылған құжатта.