نموذج جرد المخزون في Excel
سنتعرف في هذا الدرس على كيفية عمل نموذج جرد المخزون في برنامج Excel يحتوي على حصر لبضاعة أول المدة والوارد والصادر والرصيد , وينبهك أيضا في حالة نقص الكمية من صنف معين عن رقم معين .
تحميل نموذج جرد المخزون
يمكنك تحميل نموذج جرد المخزون الذي سنقوم بشرح كيفية عمله من خلال الرابط التالي :
https://www.mediafire.com/file/eaprxf2h3pg0hvf/stock.xlsx
أوراق العمل التي يتكون منها نموذج جرد المخزون
نموذج جرد المخزون الذي سنقوم بشرحه سنفترض أنه سيكون لمحل بيع موبايلات ويتكون من 3 أوراق عمل وهي :
- المخزون STOCK
- الوارد IN
- الصادر OUT
ورقة عمل المخزون
في ورقة عمل المخزون سنقوم بتسجيل بيانات كل منتج مرة واحدة فقط , حيث تحتوي على البيانات التالية :
- كود المنتج ( كل منتج يجب أن يأخذ كود غير مكرر ) .
- اسم المنتج .
- بضاعة أول المدة .
- الوارد ( اجمالي الكمية الواردة من المنتج وسنقوم بحسابه من ورقة عمل الوارد ) .
- الصادر ( اجمالي الكمية الصادرة من المنتج وسنقوم بحسابه من ورقة عمل الصادر ) .
- الرصيد ( بضاعة أول المدة + الوارد - الصادر ) .
رصيد المنتج سنقوم بحسابه من خلال المعادلة التالية :
ورقة عمل الوارد
في ورقة عمل الوارد سنقوم بتسجيل الكمية الواردة ( المشتراه ) , طبعا كل منتج سيتكرر أكثر من مرة لأننا سنقوم بشراء المنتج أكثر من مرة , حيث تحتوي على البيانات التالية :
- التاريخ .
- كود المنتج .
- اسم المنتج ( لا تحتاج إلى كتابته يدويا حيث سنقوم بأخذه من ورقة عمل المخزون من خلال استخدام دالة VLOOKUP ) .
- الكمية الواردة .
اسم المنتج سنقوم بأخذه من ورقة عمل المخزون من خلال استخدام المعادلة التالية :
في هذه المعادلة ستقوم دالة VLOOKUP بالبحث عن كود المنتج داخل العمود الأول في النطاق A2:B11 في ورقة عمل المخزون , وعندما تجده ترجع اسم المنتج المقابل له في العمود الثاني .
ورقة عمل الصادر
في ورقة عمل الصادر سنقوم بتسجيل الكمية الصادرة ( المباعة ) , طبعا كل منتج سيتكرر أكثر من مرة لأننا سنقوم ببيع المنتج أكثر من مرة , حيث تحتوي على البيانات التالية :
- التاريخ .
- كود المنتج .
- اسم المنتج ( لا تحتاج إلى كتابته يدويا حيث سنقوم بأخذه من ورقة عمل المخزون من خلال استخدام دالة VLOOKUP ) .
- الكمية الصادرة .
اسم المنتج سنقوم بأخذه من ورقة عمل المخزون من خلال استخدام المعادلة التالية :
في هذه المعادلة ستقوم دالة VLOOKUP بالبحث عن كود المنتج داخل العمود الأول في النطاق A2:B11 في ورقة عمل المخزون , وعندما تجده ترجع اسم المنتج المقابل له في العمود الثاني .
اضافة اجمالي الكمية الواردة والصادرة من كل منتج إلى ورقة عمل المخزون
الأن سنقوم بحساب اجمالي الوارد واجمالي الصادر من كل منتج وكتابتهم داخل ورقة عمل المخزون .
اجمالي الوارد من المنتج سنقوم بحسابه من خلال المعادلة التالية :
في هذه المعادلة ستقوم دالة SUMIF بحساب اجمالي الكمية الواردة من المنتج , وذلك من خلال ايجاد كل الخلايا التي تحتوي على كود المنتج داخل النطاق B2:B29 في ورقة عمل الوارد , ثم جمع كل الكميات الواردة المقابلة لهذه الخلايا في النطاق D2:D29 في ورقة عمل الوارد .
اجمالي الصادر من المنتج سنقوم بحسابه من خلال المعادلة التالية :
في هذه المعادلة ستقوم دالة SUMIF بحساب اجمالي الكمية الصادرة من المنتج , وذلك من خلال ايجاد كل الخلايا التي تحتوي على كود المنتج داخل النطاق B2:B29 في ورقة عمل الصادر , ثم جمع كل الكميات الصادرة المقابلة لهذه الخلايا في النطاق D2:D29 في ورقة عمل الصادر .
التنبية في حالة نقص رصيد منتج معين عن رقم معين
حدد النطاق الذي يحتوي على أرصدة المنتجات , ثم اذهب إلى التبويب Home واضغط على التنسيق الشرطي Conditional Formatting ثم اضغط على New Rule
سيظهر مربع حوار New Formatting Rule قم بعمل التالي :
- اضغط على أخر اختيار وهو كتابة معادلة لتحديد الخلايا التي سيتم تنسيقها .
- اكتب المعادلة 5>F2= وهذا معناه أننا سنقوم بتنسيق الخلايا في حالة كان رصيد المنتج أقل من 5 وحدات .
- اضغط على زر Format لكي تقوم بإختيار التنسيق الذي تريده .
سيظهر مربع حوار Format Cells قم بإختيار التنسيق الذي تريده , وليكن مثلا أننا سنجعل لون تعبئة الخلية هو اللون الأحمر في حالة كان رصيد المنتج أقل من 5 وحدات , ثم اضغط على زر OK
سترجع مرة أخرى إلى مربع حوار New Formatting Rule اضغط على زر OK
الأن سيقوم برنامج Excel بتنبيهك في حالة نقص رصيد منتج معين عن 5 وحدات من خلال جعل لون تعبئة الخلية هو اللون الأحمر .
