1نظرة عامة
يشرح هذا الدليل نظام جرد مخزون على Excel لإدارة الأصناف والرصيد المتحرك (وارد وصادر) مع سجل حركات وتسوية مخزون ولوحة تحكم، اعتماداً على جدول مركزي tbl_items ودوال مثل SUMIF وINDEX وMATCH وIFERROR. الملف يعتمد على الصيغ دون الاعتماد على VBA في المنطق المعروض هنا، ويضم ثماني شيتات و344 صيغة جاهزة في النسخة الكاملة.
الأصناف
رصيد متحرك = أول رصيد + وارد − صادر، وقيمة المخزون
المستلمات / الصرف
سحب الفئة والسعر تلقائياً من الجدول
سجل الحركات
دمج وارد وصادر في سطر واحد مرتب
تسوية المخزون
مقارنة الجرد الفعلي بالرصيد النظامي
2الشيتات الثمانية
| الشيت | الدور |
|---|---|
| لوحة التحكم | عدّ الأصناف، قيم المخزون، تلخيص وارد/صادر، توزيع الفئات |
| الأصناف | بطاقة صنف: رصيد، حد أدنى/أقصى، حالة لونية (نفد / تحذير / زائد / طبيعي) |
| المستلمات | تسجيل وارد مع كمية وسعر وقيمة تلقائية |
| أذون الصرف | تسجيل صادر بنفس منطق المستلمات |
| سجل الحركات | عرض مدمج من المستلمات وأذون الصرف مع تسلسل وتصنيف وارد/صادر |
| تسوية المخزون | مقارنة الرصيد النظامي بكمية الجرد الفعلي والفرق والقيمة |
| طلبات الشراء | تقدير احتياجات بقيمة تلقائية من سعر الوحدة |
| الموردون | تجميع قيمة المشتريات حسب اسم المورد من المستلمات |
3مسار العمل
عرّف الأصناف
من شيت الأصناف أو عبر الجدول tbl_items — الاسم، الفئة، سعر الوحدة، حدود التنبيه.
سجّل الوارد
شيت المستلمات: اختر الصنف، أدخل الكمية؛ الفئة والسعر والقيمة تُملأ وحدها.
سجّل الصادر
شيت أذون الصرف بنفس الفكرة.
راجع السجل واللوحة
سجل الحركات ولوحة التحكم تتحدثان من نفس البيانات.
الجرد الفعلي
عند التسوية، أدخل الكمية الفعلية في تسوية المخزون لمقارنة النظام بالأرض.
4نقاط سريعة لكل شيت
يستخدم COUNTA لعد الأصناف المعرّفة، TEXT(SUM(...)) لعرض القيم بصيغة جنيه، COUNTIF لحالات التنبيه، وSUMIF مع IFERROR لحصص الفئات من إجمالي قيمة المخزون.
عمود الرصيد يجمع: الرصيد الافتتاحي + مجموع وارد الصنف من المستلمات − مجموع صادر الصنف من أذون الصرف عبر SUMIF وIFERROR. عمود الحالة يستخدم IF متداخلة مع حدود الجرد.
INDEX + MATCH على tbl_items لسحب الفئة وسعر الوحدة عند اختيار اسم الصنف. القيمة = كمية × سعر بـ IF لتفادي الصفر.
جدول مساعد يربط تسلسل الحركات بمصدرها (مستلمات=1 أو صرف=2) ثم INDEX/MATCH/ROW/INT لاستخراج الصف المناسب من كل شيت. عمود النوع يعرض «وارد» أو «صادر».
INDEX لسحب اسم الصنف والرصيد النظامي بالصف، OR مع IF للفرق، وتقييم الحالة (مطابق / زيادة / عجز).
5الصيغ والدوال في الملف — شرح تعليمي
يحتوي الملف الحالي على 344 خلية تتضمن صيغة. فيما يلي الدوال المستخدمة فعلياً، مع تعريفها، وسبب اختيارها في سياق جرد المخزون، ومثال تطبيقي مبسّط يوضح الفكرة للمتعلم. بعد جدول «مكتبة الصيغ» تجد شرحاً تعليمياً لكل دالة على حدة.
الصيغ التالية نُسخت من بنية الملف الفعلية (أسماء الشيتات والنطاقات كما في النسخة المرجعية). الصفوف من 4 إلى 13 أو 11 حسب الشيت؛ يتكرر النمط لأسفل مع تغيير رقم الصف. أسماء أعمدة الجدول tbl_items تظهر بصيغة Excel Structured References.
أمثلة صيغ من الملف — حسب الشيت
عدّ الأصناف، تنسيق إجماليات القيمة بالجنيه، تنبيهات الحالة، وحصص الفئات من قيمة المخزون.
=COUNTA(الأصناف!$D$4:$D$13)
=TEXT(SUM(الأصناف!$I$4:$I$13),"#,##0.00")&" ج.م"
=COUNTIF(الأصناف!$L$4:$L$13,"🔴 نفد")
=SUMIF(الأصناف!$C$4:$C$13,E9,الأصناف!$I$4:$I$13) =IFERROR(G9/SUM(الأصناف!$I$4:$I$13),0)
=COUNTA(المستلمات!D4:D11)
=SUM(المستلمات!H4:H11)
=COUNTA('أذون الصرف'!D4:D11)
=SUM('أذون الصرف'!H4:H11)
الرصيد المتحرك، قيمة المخزون للسطر، وحالة التنبيه.
=F4+IFERROR(SUMIF(المستلمات!$D$4:$D$500,D4,المستلمات!$F$4:$F$500),0)-IFERROR(SUMIF('أذون الصرف'!$D$4:$D$500,D4,'أذون الصرف'!$F$4:$F$500),0)
=G4*H4
=IF(G4<=0,"🔴 نفد",IF(G4<=J4,"🟠 تحذير",IF(G4>=K4,"🔵 زائد","🟢 طبيعي")))
=SUM(I4:I13)
نفس منطق السطر في المستلمات والصرف وطلبات الشراء: سحب من tbl_items ثم قيمة السطر.
=IFERROR(INDEX(tbl_items[الفئة],MATCH(D4,tbl_items[اسم الصنف],0)),"") =IFERROR(INDEX(tbl_items[سعر الوحدة],MATCH(D4,tbl_items[اسم الصنف],0)),"") =IF(D4="","",F4*G4) =IF(D4="","",IF(F4>0,"✅ مستلم","⚠️ مراجعة"))
=SUM(H4:H11)
=IFERROR(INDEX(tbl_items[الفئة],MATCH(D4,tbl_items[اسم الصنف],0)),"") =IF(D4="","",F4*G4) =SUM(H4:H11)
يعتمد على جدول مساعد في الصفوف 29–44 والأعمدة A:D وB وC لتحديد التسلسل والمصدر، ثم يجلب الحقول من المستلمات أو أذون الصرف.
=IFERROR(IF(ISNUMBER(MATCH((ROW()-ROW($E$4)+1),$B$1:$B$1000,0)),(ROW()-ROW($E$4)+1),""),"")
=IFERROR(INT(INDEX($A$29:$A$44,MATCH((ROW()-ROW($E$4)+1),$B$29:$B$44,0))/100),"")
=IFERROR(IF(INDEX($C$29:$C$44,MATCH((ROW()-ROW($E$4)+1),$B$29:$B$44,0))=1,
INDEX(المستلمات!$D$4:$D$11,INDEX($D$29:$D$44,MATCH((ROW()-ROW($E$4)+1),$B$29:$B$44,0))),
INDEX('أذون الصرف'!$D$4:$D$11,INDEX($D$29:$D$44,MATCH((ROW()-ROW($E$4)+1),$B$29:$B$44,0)))
),"")
=IFERROR(IF(INDEX($C$29:$C$44,MATCH((ROW()-ROW($E$4)+1),$B$29:$B$44,0))=1,"📥 وارد","📤 صادر"),"")
=SUM(المستلمات!$H$4:$H$11)
=SUM('أذون الصرف'!$H$4:$H$11)
=SUM(المستلمات!$H$4:$H$11)-SUM('أذون الصرف'!$H$4:$H$11)
=IFERROR(INDEX(tbl_items[اسم الصنف],1),"") =IFERROR(INDEX(الأصناف!$G$4:$G$13,1),"")
=IF(OR(E5="",D5=""),"",E5-D5) =IF(F5="","",F5*IFERROR(INDEX(الأصناف!$H$4:$H$13,1),0)) =IF(F5="","—",IF(F5=0,"✅ مطابق",IF(F5>0,"📈 زيادة","📉 عجز")))
=COUNTIF(H5:H14,"✅ مطابق") =COUNTIF(F5:F14,">"&0) =COUNTIF(F5:F14,"<"&0) =SUM(G5:G14)
=IFERROR(SUMIF(المستلمات!$I$4:$I$11,C4,المستلمات!$H$4:$H$11),0)
طلبات الشراء: تستخدم مطابقة المستلمات نفسها لـ INDEX/MATCH على tbl_items[الفئة] وtbl_items[سعر الوحدة] مع =IF(D4="","",F4*G4) لقيمة السطر، مع إزاحة مرجع الصف (D5، D6…) في الصفوف التالية.
شرح الدوال — مرجع تعليمي
SUM — جمع القيم
التعريف: تجمع القيم الرقمية ضمن نطاق خلايا واحد.
الاستخدام في النظام: حساب إجماليات القيم أو الكميات (مثل إجمالي قيمة أصناف معروضة، أو تجميع أعمدة في لوحة التحكم وسجل الحركات).
مثال تعليمي: =SUM(I4:I13) يعيد مجموع القيم من الخلية I4 إلى I13.
SUMIF — جمع بشرط واحد
التعريف: يجمع خلايا في نطاق «مجال المجموع» عندما تطابق الخلايا المقابلة في «نطاق الشرط» قيمة شرط محددة.
الاستخدام في النظام: حساب وارد صنف معيّن من شيت المستلمات، وصادره من أذون الصرف، لاشتقاق الرصيد المتحرك في شيت الأصناف؛ وتجميع قيم المشتريات حسب المورد في شيت الموردون.
مثال تعليمي: جمع عمود الكميات لكل الصفوف التي يساوي فيها عمود الصنف اسم «قلم».
COUNTA — عدّ الخلايا غير الفارغة
التعريف: يحسب عدد الخلايا التي تحتوي على أي بيانات (نص، رقم، تاريخ، إلخ) ضمن النطاق.
الاستخدام في النظام: تقدير عدد الأصناف المسجّلة، أو عدد بنود المستلمات/أذون الصرف في ملخصات لوحة التحكم.
مثال تعليمي: =COUNTA(A2:A100) يعيد عدد الخلايا المملوءة في النطاق.
COUNTIF — عدّ الخلايا التي تحقق شرطاً
التعريف: يعدّ الخلايا في نطاق التي تطابق شرطاً نصياً أو رقمياً.
الاستخدام في النظام: فرز عدد الأصناف حسب حالة التنبيه (نفد، تحذير، زائد، طبيعي)؛ وفي تسوية المخزون لعدّ حالات المطابقة والزيادة والعجز.
مثال من الملف: COUNTIF(الأصناف!$L$4:$L$13,"🔴 نفد")
IF — الشرط المنطقي
التعريف: إذا تحقق شرط، تُرجع القيمة الأولى؛ وإلا تُرجع القيمة الثانية. يمكن تداخل عدة دوال IF لبناء قرارات متعددة المستويات.
الاستخدام في النظام: تصنيف حالة المخزون وفق الحد الأدنى والأقصى؛ وحساب قيمة السطر (كمية × سعر) فقط عند اختيار صنف غير فارغ؛ وعرض حالة التسوية (مطابق، زيادة، عجز).
مثال تعليمي: =IF(A1>10,"مرتفع","منخفض")
IFERROR — معالجة أخطاء الصيغ
التعريف: إذا أدت الصيغة إلى خطأ (مثل #N/A أو #DIV/0!)، تُستبدل النتيجة بقيمة بديلة تحددها (مثل 0 أو نص فارغ).
الاستخدام في النظام: تجنب ظهور أخطاء مرئية عند عدم وجود بيانات كافية لـ SUMIF، أو عند فشل INDEX/MATCH لعدم تطابق اسم الصنف مع الجدول.
مثال تعليمي: =IFERROR(A1/B1,0)
INDEX — استرجاع قيمة من موضع في النطاق
التعريف: تُرجع قيمة خلية من نطاق بناءً على رقم الصف (واختيارياً رقم العمود). غالباً ما تُقرن بـ MATCH لتحويل معرف (مثل اسم الصنف) إلى موضع صف.
الاستخدام في النظام: سحب الفئة وسعر الوحدة من tbl_items؛ وملء صفوف التسوية من ترتيب الأصناف؛ واستخراج حقول من المستلمات أو أذون الصرف في سجل الحركات.
مثال تعليمي: =INDEX(A1:A10,3) يعيد القيمة في الصف الثالث من النطاق.
MATCH — البحث عن ترتيب الصف
التعريف: تبحث عن قيمة داخل نطاق عمودي أو أفقي وتُرجع ترتيبها النسبي (1، 2، 3…). القيمة 0 في المعامل الأخير تعني تطابقاً تامّاً.
الاستخدام في النظام: تحديد صف اسم الصنف داخل جدول الأصناف لاستخدامه مع INDEX في المستلمات وأذون الصرف وطلبات الشراء.
مثال من الملف: MATCH(D4,tbl_items[اسم الصنف],0)
TEXT — تنسيق الأرقام كنص
التعريف: تحويل رقم إلى نص وفق نمط عرض (فواصل آلاف، منازل عشرية، إلخ)، ويمكن دمج الناتج مع نص ثابت.
الاستخدام في النظام: عرض إجماليات لوحة التحكم بصيغة قراءة واضحة مع لاحقة العملة «ج.م».
مثال من الملف: TEXT(SUM(...),"#,##0.00")&" ج.م"
OR — شرط منطقي (أو)
التعريف: تُرجع TRUE إذا تحقق أحد الشروط على الأقل، وFALSE إذا لم يتحقق أي منها.
الاستخدام في النظام: في تسوية المخزون، تجنب حساب الفرق عندما تكون إحدى الخليتين (الرصيد النظامي أو الكمية الفعلية) فارغة.
مثال تعليمي: داخل IF: IF(OR(A1="",B1=""),"",A1-B1)
ROW — رقم الصف
التعريف: تُرجع رقم صف المرجع؛ غالباً ما يُستخدم ROW()-ROW(مرجع_ثابت) لإنتاج تسلسل 1، 2، 3… لكل صف في عمود.
الاستخدام في النظام: في سجل الحركات، لربط كل صف عرض بتسلسل حركة من جدول مساعد دون نسخ صيغ يدوياً لكل رقم.
فائدة تعليمية: تكرار نمط واحد من الصيغ على عمود طويل مع بقاء المراجع نسبية بشكل صحيح.
ISNUMBER مع MATCH في السجل
التعريف: ISNUMBER تتحقق مما إذا كانت القيمة رقماً؛ ونتيجة MATCH الرقمية تعني أن التسلسل وُجد في جدول المساعد.
الاستخدام في النظام: إظهار بيانات السطر فقط عندما يوجد إدخال مطابق في جدول تسلسل الحركات، وإلا تُترك الخلية فارغة لتقليل الضوضاء البصرية والأخطاء.
فائدة تعليمية: الجمع بين دالة بحث ودالة تحقق لبناء واجهة عرض «تظهر عند الحاجة».
INT — الجزء الصحيح من الرقم
التعريف: تُرجع الجزء الصحيح من الرقم بعد إزالة المنزل العشرية وفق قواعد Excel لدالة INT.
الاستخدام في النظام: في سجل الحركات، مع قيمة مُشتقة من عمود مساعد ومقسومة على 100، لاستخراج جزء صحيح يُستخدم في منطق الترميز أو التسلسل المعروض في الملف.
مثال تعليمي: =INT(9.7) يعيد 9.
يعتمد النظام على نموذج «رصيد متحرك» (وارد ناقص صادر مع رصيد افتتاحي)، مع استعلامات شرطية (SUMIF، COUNTIF) واسترجاع مرن للبيانات (INDEX، MATCH)، وعرض منسّق (TEXT)، وحماية من أخطاء الصيغ (IFERROR)، ومقارنة الجرد الفعلي بالرصيد النظامي في شيت التسوية — وهي مبادئ شائعة في تطبيقات المخازن على جداول البيانات.
6قواعد مهمة
تطابق اسم الصنف
يجب أن يطابق اسم الصنف في المستلمات وأذون الصرف الاسم في tbl_items حرفياً حتى تعمل دوال MATCH دون خطأ.
الحفاظ على صفوف المعادلات
يعتمد سجل الحركات والتسوية على صفوف جاهزة؛ يُفضّل مسح محتوى الإدخال بدلاً من حذف الصفوف التي تحتوي صيغاً.
توسيع النطاقات عند الحاجة
بعض دوال SUMIF في شيت الأصناف ممتدة حتى 500 صف؛ عند زيادة الحجم حدّث النطاقات بنفس النمط.
7أسئلة شائعة
tbl_items، أو عند وجود فرق بسيط في الاسم (مسافة زائدة أو اختلاف إملائي).8 حمّل النظام — اختر النسخة المناسبة لك
النسختان تحتويان نفس أوراق العمل والتصميم والقوائم المنسدلة — الفرق الجوهري هو الصيغ والمعادلات التي تجعل كل شيء يعمل تلقائياً.
| الميزة | 🆓 مجاني | ⭐ مدفوع |
|---|---|---|
| 8 ورقات عمل كاملة | ✓ | ✓ |
| التصميم الاحترافي والألوان | ✓ | ✓ |
| القوائم المنسدلة (Dropdown) | ✓ | ✓ |
| الصيغ والمعادلات (INDEX، MATCH، SUMIF…) | ✗ تحتاج إضافتها يدوياً | ✓ مدمجة وجاهزة |
| سحب الفئة وسعر الوحدة عند اختيار الصنف | ✗ | ✓ |
| حساب الإجمالي تلقائياً | ✗ | ✓ |
| سجل الحركات يتجمع تلقائياً | ✗ | ✓ |
| لوحة التحكم وتنبيهات المخزون تلقائية | ✗ | ✓ |
| Data Validation لمنع الأخطاء | ✗ | ✓ |
| الجاهزية للاستخدام الفوري | ✗ يحتاج إعداد | ✓ افتح وابدأ مباشرة |
النسخة المجانية
- 8 شيتات كاملة
- تصميم احترافي
- قوائم منسدلة
- بدون معادلات
- تحتاج إعداد يدوي
اشترك أولاً لتفعيل خانة البريد وزر التحميل
النسخة المدفوعة
- 8 شيتات كاملة
- تصميم احترافي
- قوائم منسدلة
- كل الصيغ والمعادلات ✓
- جاهزة للتشغيل الفوري ✓
- سجل حركات تلقائي
- لوحة تحكم مباشرة
- Data Validation كامل
افتح الملف وابدأ العمل مباشرة بدون أي إعداد
إذا كنت محاسباً أو خبير Excel وتريد قالباً تبني عليه معادلاتك — المجاني يكفيك. إذا كنت صاحب عمل وتريد نظاماً يعمل فوراً بدون أي إعداد — النسخة المدفوعة هي الاختيار الصحيح.