Кітапхана қоры мен кітап беруді Excel-де қалай есепке алуға болады

Кітаптар кетеді де, қайтпайды. Қағаз формулярда әр оқырманның парағын ашып қарамай, кім қарыз екенін айту мүмкін емес, ал оқу жылының соңына қарай «қорда неше кітап жетпейді» деген сұрақ қиын санауға айналады. Кішкентай мектеп, ауыл кітапханасы немесе корпоративтік кітап сөресі үшін арнайы бағдарлама ауыр, ал Excel жеткілікті.

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

Үш тізім: қор, оқырмандар, беру

Бір кестеге бәрін жинамаңыз. Үш бөлек тізім жасаңыз:

Қор — кітаптар каталогы. Бір кітап — бір жол: кітап нөмірі (код), атауы, авторы, жанры, даналар саны.

Оқырмандар — оқырман коды, аты-жөні, сынып немесе топ.

Беру — журнал. Бір беру — бір жол: күні, кітап коды, оқырман коды, қайтару мерзімі, қайтарылған күні.

Бұл тәсілдің артықшылығы: кітап атауын бір рет қана жазасыз. Беру журналында тек код пен оқырман коды тұрады, ал атауы мен аты-жөні формуламен тартылады. Сонда «Абай жолы» бір жерде «Абай жолы», екінші жерде «Абай жолы (1-том)» болып кетпейді.

Атауды кодпен тарту

Беру журналында кітап коды B2 ұяшығында болса, атауын қордан алу:

=IFERROR(VLOOKUP(B2;Қор!$A:$C;2;FALSE);"")

Мұнда Қор!$A:$C — қор парағының аймағы, 2 — атауы тұрған баған. Оқырманның аты-жөнін де дәл осылай аласыз. Ашылмалы тізімді кодқа қоссаңыз (Data → Data Validation → List), жазбада қате код болмайды.

Қайтару мерзімін есептеу

Мерзімді қолмен жазбаңыз: ол әрқашан бірдей, мысалы 14 күн. Күнтізбелік күн санын бір ұяшыққа шығарыңыз (мысалы, $K$1), ал беру жолында:

=A2+$K$1

A2 — беру күні. Мерзімді өзгерту керек болса, бір ұяшықты түзетесіз, ал барлық жаңа жазбада мерзім өзгереді.

Борышкерлерді табу

Борышкер — мерзімі өткен, бірақ қайтарылмаған кітабы бар оқырман. Күйді жеке бағанға шығарыңыз (D2 — мерзім, E2 — қайтарылған күн):

=IF(E2<>"";"Қайтарылды";IF(D2<TODAY();"Мерзімі өтті";"Қолда"))

Тізімді сүзу үшін «Мерзімі өтті» күйін таңдайсыз. Әр оқырманның қанша кітапты кешіктіргенін =COUNTIFS(C:C;C2;F:F;"Мерзімі өтті") береді. Кешіккен күндер санын көрсетсеңіз, тізім тиімдірек болады: =IF(F2="Мерзімі өтті";TODAY()-D2;"").

Қордағы қалдықты есептеу

Бір кітаптың бірнеше данасы болуы мүмкін. Қолдағы даналарды беру журналынан санайсыз: қайтарылған күні жоқ жолдар. Қордағы кітап коды A2, даналар саны D2 болса:

=D2-COUNTIFS(Беру!$B:$B;A2;Беру!$E:$E;"")

Бұл «қолда қалғаны» болады. Мысал:

Қалдығы 0 болса, жолды қызылға бояңыз. Сонда кітапты бере алмайтыныңыз бірден көрінеді.

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

• Қайтарылған кітапты журналдан өшіру. Жолды өшірмеңіз, қайтарылған күнді жазыңыз, сонда тарих сақталады.
• Кодсыз, тек атау бойынша жүргізу: бір атаудағы екі шығарылымды ажырату мүмкін емес.
• Оқырманның сыныбын жыл сайын жаңартпау.
• Жоғалған кітапты тізімде қалдыру. Оған бөлек мәртебе беріңіз немесе қордан бөлек белгілеңіз.

Дайын үлгі: кітапхана қоры мен кітап беру есебі

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

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

Жинақта бос кесте және ойдан шығарылған деректері бар толтырылған үлгі бар. Файл қазақ тілінде, Excel 2007 және одан жаңа нұсқаларда және LibreOffice-те макростарсыз жұмыс істейді. Бұл бухгалтерлік бағдарлама емес, қарапайым формулалардағы Excel кестесі. Мерзімдер мен басқа мәндерді жұмыс алдында өз ережелеріңізбен салыстырыңыз. Үлгіні сатып алған соң жобаның барлық түзетулері мен жаңа нұсқаларын тегін аласыз.

Мектеп кітапханашыларына, ауылдық және корпоративтік кітапханаларға, кітап қоры бар үйірмелер мен клубтарға қолайлы.

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

Бір кітапты бірнеше оқырман ала ма?

Иә, егер кітаптың бірнеше данасы болса. Қор парағында даналар санын көрсетіңіз, ал қалдық қолда қалғандарын санап береді.

Қорды жыл сайын қалай тексеру керек?

Жылына бір рет қордағы кітаптарды қолмен санап, кесте қалдығымен салыстырыңыз. Айырма болса, жоғалғандарды анықтап, қордан бөлек белгілеңіз.

Оқырман кітапты ұзартуды сұраса не істеу керек?

Ескі жолға қайтару күнін жазып, жаңа жол қосыңыз. Сонда жаңа беру күні мен мерзім дұрыс есептеледі, ал тарих сақталады.

Бірнеше мың кітапты Excel көтере ме?

Иә, бірнеше мың жол Excel үшін шектеу емес. Жылдамдық төмендесе, ескі беру жазбаларын мұрағат файлына көшіріңіз.