أسبوع التخفيضات الكبرىClaude Skills — خصم 20%
Tips

كيفية إنشاء متتبع للمخزون في Excel: خطوة بخطوة

Powerdrill Bloom·
كيفية إنشاء متتبع للمخزون في Excel: خطوة بخطوة

لإنشاء متتبع مخزون في Excel، قم ببناء جدولين. يسرد الجدول الأول العناصر الخاصة بك، مع أعمدة لمعرف العنصر (item ID)، والاسم، وتكلفة الوحدة، والمخزون الافتتاحي، ونقطة إعادة الطلب. يسجل الجدول الثاني كل حركة، مع التاريخ، ومعرف العنصر، والنوع (وارد أو صادر)، والكمية. ثم استخدم SUMIFS لجمع المخزون الوارد والمخزون الصادر، واطرحهما للحصول على المخزون المتوفر.

أضف عمود حالة يميز العناصر التي وصلت إلى نقطة إعادة الطلب أو أقل منها، وقم بتظليل تلك الصفوف باستخدام التنسيق الشرطي. يظهر ملخص قصير في الأعلى إجمالي قيمة المخزون وعدد العناصر التي تحتاج إلى إعادة طلب.

يغطي هذا الدليل كل خطوة، من هيكل الجدول إلى الصيغ والتنبيهات. كما يستعرض مثالاً كاملاً لمخزن مستلزمات مكتبية صغير، وطريقة أسرع لبناء المتتبع من ملف مصدر مُصدر. الخطوات مأخوذة من صفحات دعم Microsoft، تمت قراءتها في 18 نوفمبر 2026.

ماذا يفعل متتبع المخزون

يجيب متتبع المخزون على ثلاثة أسئلة في أي لحظة:

  • كم لدينا من كل عنصر؟ هذا هو المخزون المتوفر.
  • ما هي قيمته؟ هذه هي قيمة المخزون، بناءً على تكلفة الوحدة.
  • ما الذي نحتاج لطلبه؟ هذه هي العناصر التي وصلت إلى نقطة إعادة الطلب أو أقل منها.

المتتبع الجيد يحتفظ أيضاً بسجل تاريخي. عندما يبدو العدد غير صحيح، يمكنك الرجوع عبر سجل الحركة ومعرفة متى دخل المخزون أو خرج.

يعمل متتبع المخزون في Excel بشكل جيد للمخزونات الصغيرة والمتوسطة. وهو يناسب المخازن، والمحلات الصغيرة، والمستلزمات المكتبية، وقطع الغيار، ومعدات الفعاليات. بمجرد أن تبدأ في تتبع آلاف العناصر عبر مواقع متعددة، عادة ما يكون نظام المخزون المخصص هو الأنسب.

تخطيط المتتبع: جدولان

قبل كتابة أي صيغ، قرر كيف سيتم هيكلة متتبع المخزون. تستخدم العديد من المتتبعات البسيطة جدولاً واحداً مع عمود كمية واحد يقوم الأشخاص بالكتابة فوقه. هذا النظام يتعطل بسرعة؛ فلا أحد يعرف متى تغير الرقم أو لماذا.

التصميم الأفضل يفصل قائمة العناصر عن الحركات:

الجدول صف واحد لكل الأعمدة الرئيسية
العناصر (Items) منتج أو قطعة Item ID، الاسم، الفئة، تكلفة الوحدة، المخزون الافتتاحي، نقطة إعادة الطلب
الحركات (Movements) تغيير في المخزون التاريخ، Item ID، النوع (In أو Out)، الكمية، ملاحظة

يحتوي جدول العناصر على حقائق نادراً ما تتغير. أما جدول الحركات فينمو كل يوم. تقرأ الصيغ في جدول العناصر جدول الحركات وتحسب المخزون الحالي.

لهذا التصميم ثلاث فوائد: لا تقوم أبداً بالكتابة فوق رقم، ولكل تغيير تاريخ محدد، ويمكنك الإجابة على الأسئلة لاحقاً، مثل كمية المخزون التي خرجت في شهر مارس.

كما أنه يجعل متتبع المخزون أكثر أماناً للمشاركة. يضيف الموظفون صفوفاً فقط إلى جدول الحركات، بينما يمكن أن يظل جدول العناصر، بصيغه، مقفلاً.

ضع كل جدول في ورقة عمل خاصة به. سمِّ الأوراق "Items" و "Movements" لتسهيل قراءة الصيغ.

بناء قائمة العناصر كجدول

ابدأ بورقة العناصر (Items). اكتب العناوين في الصف 1: Item ID، والاسم، والفئة، وتكلفة الوحدة، والمخزون الافتتاحي، ونقطة إعادة الطلب. ثم أدخل صفاً واحداً لكل عنصر.

بعد ذلك، حوّل النطاق إلى جدول Excel. توضح Microsoft السبب: "لجعل إدارة وتحليل مجموعة من البيانات المرتبطة أسهل، يمكنك تحويل نطاق من الخلايا إلى جدول Excel." لإنشاء جدول:

  1. حدد أي خلية في البيانات.
  2. اختر Home > Format as Table.
  3. اختر نمطاً للجدول.
  4. تأكد من تحديد مربع العناوين (headers)، ثم اختر OK.

سمِّ الجدول "Items" في علامة التبويب Table Design. افعل الشيء نفسه لسجل الحركات في الورقة الأخرى، وسمِّه "Movements".

تساعد الجداول بطريقتين: فهي تنمو تلقائياً عند إضافة صفوف، والصيغ في عمود الجدول تمتد للأسفل من تلقاء نفسها. تقول Microsoft إن الصيغة التي يتم إدخالها في خلية واحدة "يتم تطبيقها فوراً على جميع الخلايا الأخرى في عمود الجدول هذا."

اجعل معرفات العناصر (item IDs) قصيرة وفريدة، مثل A-101. استخدمها في كل مكان بدلاً من الأسماء، لأن الأسماء قد تُكتب بطرق مختلفة.

إضافة قوائم منسدلة لسجل الحركة

أخطاء الكتابة هي السبب الرئيسي في عدم دقة أعداد المخزون. القائمة المنسدلة تمنع معظم هذه الأخطاء.

توضح Microsoft الغرض في سطر واحد: "استخدم التحقق من صحة البيانات لتقييد نوع البيانات أو القيم التي يدخلها المستخدمون في خلية، مثل القائمة المنسدلة." لعمود النوع (Type):

  1. حدد عمود النوع في جدول الحركات (Movements).
  2. اختر Data > Data Validation.
  3. في علامة التبويب Settings، في مربع Allow، اختر List.
  4. في مربع Source، اكتب In,Out.
  5. تأكد من تحديد In-cell dropdown، ثم اختر OK.

افعل الشيء نفسه لعمود Item ID، ولكن وجه المصدر إلى عمود Item ID في جدول العناصر (Items). الآن لا يمكن للأشخاص إلا اختيار عنصر موجود بالفعل.

تسرع القوائم المنسدلة أيضاً عملية الإدخال. يمكن لموظف المخزن تسجيل عملية استلام في ثوانٍ معدودة عن طريق اختيار العنصر والنوع، ثم كتابة الكمية.

يمكنك أيضاً تقييد عمود الكمية. في مربع Allow، اختر Whole Number واضبط الحد الأدنى على 1. هذا يمنع الإدخالات الفارغة والسالبة.

حساب المخزون المتوفر باستخدام SUMIFS

الآن أضف الأعمدة المحسوبة إلى جدول العناصر. تقوم SUMIFS بجمع الأرقام التي تستوفي أكثر من شرط واحد. صيغة Microsoft هي:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

أضف عموداً يسمى "Stock in". في خليته الأولى، أدخل:

=SUMIFS(Movements[Quantity],Movements[Item ID],[@[Item ID]],Movements[Type],"In")

يجمع هذا كل كمية في جدول الحركات حيث يتطابق معرف العنصر مع الصف ويكون النوع "In". يقوم الجدول بتطبيق الصيغة للأسفل لكل عنصر.

أضف عمود "Stock out" بنفس الصيغة، ولكن غير "In" إلى "Out".

ثم أضف "On hand":

=[@[Opening stock]]+[@[Stock in]]-[@[Stock out]]

وأخيراً، أضف "Stock value":

=[@[On hand]]*[@[Unit cost]]

تحقق من عنصر واحد يدوياً بعد بناء الصيغ. قم بتصفية جدول الحركات لهذا العنصر، واجمع صفوف الوارد والصادر، وقارن النتيجة مع عمود المخزون المتوفر (On hand).

تمييز العناصر لإعادة الطلب

يكون متتبع المخزون أكثر فائدة عندما يخبرك بما يجب فعله. أضف عمود "Status" بهذه الصيغة:

=IF([@[On hand]]<=[@[Reorder point]],"Reorder","OK")

الآن يظهر في كل صف "Reorder" أو "OK". حدد نقاط إعادة الطلب بناءً على المدة التي يستغرقها الطلب للوصول وكمية استهلاكك خلال تلك الفترة.

اجعل صفوف إعادة الطلب تبرز باستخدام التنسيق الشرطي:

  1. حدد عمود الحالة (Status).
  2. اختر Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
  3. اكتب Reorder واختر لون تعبئة، مثل الأحمر الفاتح.
  4. اختر OK.

تلاحظ Microsoft أنه عندما يظهر مربع حوار "Text that Contains"، فإنك "تُدخل خيار التنسيق الذي تريد تطبيقه وتنقر فوق موافق." يتم تحديث التمييز في كل مرة يتغير فيها المخزون.

يمكنك أيضاً فرز أو تصفية عمود الحالة لرؤية صفوف "Reorder" فقط قبل تقديم الطلبات.

البحث عن تفاصيل العناصر باستخدام XLOOKUP

يظهر سجل الحركة معرفات العناصر، والتي يصعب قراءتها. يضيف عمود البحث اسم العنصر بجانب كل حركة.

تصف Microsoft دالة XLOOKUP بأنها وسيلة "للعثور على أشياء في جدول أو نطاق حسب الصف." صيغتها هي:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

أضف عمود "Name" إلى جدول الحركات (Movements) وأدخل:

=XLOOKUP([@[Item ID]],Items[Item ID],Items[Name],"Unknown ID")

إذا أدخل شخص ما معرفاً غير موجود، ستظهر في الخلية عبارة "Unknown ID". هذا يجعل اكتشاف الأخطاء سهلاً.

تلاحظ Microsoft أن "XLOOKUP غير متوفرة في Excel 2016 و Excel 2019." في تلك الإصدارات، استخدم INDEX و MATCH، أو VLOOKUP مع وضع معرف العنصر في العمود الأول.

إضافة ملخص في الأعلى

الجزء الأخير من متتبع المخزون هو الملخص. يساعد الملخص القصير أي شخص على رؤية حالة المخزون دون الحاجة للتمرير. أضف بضع خلايا فوق جدول العناصر، أو في ورقة "Summary" منفصلة.

استخدم هذه الصيغ:

  • إجمالي قيمة المخزون: =SUM(Items[Stock value])
  • عناصر لإعادة الطلب: =COUNTIF(Items[Status],"Reorder")
  • الوحدات الصادرة هذا الشهر: =SUMIFS(Movements[Quantity],Movements[Type],"Out",Movements[Date],">="&DATE(2026,11,1))

تتبع COUNTIF نمطاً بسيطاً. بكلمات Microsoft، هي تسأل "أين تريد أن تبحث؟" و "عن ماذا تريد أن تبحث؟"

يمكنك إضافة مخطط شريطي لقيمة المخزون حسب الفئة أيضاً. حدد نطاق الملخص واختر Insert > Chart. اجعل الملخص صغيراً، مع ثلاثة إلى خمسة أرقام مهمة فقط.

مثال عملي: مخزن مستلزمات مكتبية

إليك مثال كامل لمتتبع المخزون قيد الاستخدام. يتتبع مكتب صغير خمسة أصناف من المستلزمات. يبدو جدول العناصر كما يلي قبل شهر نوفمبر:

Item ID الاسم تكلفة الوحدة المخزون الافتتاحي نقطة إعادة الطلب
A-101 دفتر ملاحظات A5 $2.50 120 50
A-102 أقلام حبر جاف (صندوق) $6.00 40 20
B-201 ورق طابعة (رزمة) $4.20 80 30
B-202 خرطوشة حبر (تونر) $45.00 12 5
C-301 صندوق شحن (متوسط) $0.80 300 100

خلال شهر نوفمبر، يسجل جدول الحركات هذه التغييرات. دفاتر الملاحظات: 85 صادر و 50 وارد. الأقلام: 25 صادر. الورق: 60 صادر و 40 وارد. التونر: 9 صادر. الصناديق: 180 صادر.

بعد تشغيل الصيغ، تظهر الأعمدة المحسوبة كما يلي:

Item ID Stock in Stock out On hand Stock value Status
A-101 50 85 85 $212.50 OK
A-102 0 25 15 $90.00 Reorder
B-201 40 60 60 $252.00 OK
B-202 0 9 3 $135.00 Reorder
C-301 0 180 120 $96.00 OK

يوضح المثال أيضاً سبب أهمية سجل الحركة. يبدو الورق في حالة جيدة بوجود 60 رزمة، لكن السجل يظهر أن 60 رزمة خرجت في شهر واحد. إذا استمر هذا المعدل، سيصل الورق إلى نقطة إعادة الطلب في غضون أسابيع. عمود الكمية الواحد كان سيخفي هذا الاتجاه.

تحقق من صف واحد يدوياً. بدأت دفاتر الملاحظات بـ 120، زادت 50، ونقصت 85، لذا 120 + 50 − 85 = 85. وهذا يطابق عمود المخزون المتوفر (On hand).

يظهر الملخص إجمالي قيمة مخزون قدرها $785.50 وعنصرين يحتاجان لإعادة الطلب. الأقلام والتونر كلاهما أقل من نقاط إعادة الطلب الخاصة بهما، لذا يتم إدراجهما في طلب هذا الأسبوع.

إنشاء متتبع مخزون في Excel باستخدام الذكاء الاصطناعي

يستغرق بناء المتتبع يدوياً ساعة أو ساعتين. الجزء البطيء غالباً ما يكون البيانات: تنظيف ملف مُصدر من نظام نقاط البيع، ومطابقة أسماء العناصر، وإصلاح المعرفات المكررة.

يمكن لأداة ذكاء اصطناعي القيام بهذا التنظيف والتحليل نيابة عنك. إليك الطريقة في ثلاث خطوات، باستخدام Powerdrill Bloom.

الخطوة 1: رفع ملف Excel أو CSV أو PDF الخاص بك

قم برفع ملف تصدير المخزون، أو قائمة الموردين، أو ورقة جرد الشهر الماضي. يعمل Excel AI Assistant مع ملفات الجداول مباشرة.

قم بتضمين كل من قائمة العناصر والحركات إذا كانت لديك، حتى لو كانت في ملفات منفصلة.

رفع ملف تصدير مخزون لإنشاء متتبع مخزون في Excel باستخدام Powerdrill Bloom

الخطوة 2: اطلب المتتبع بلغة طبيعية

صف ما تحتاجه. على سبيل المثال: "قم ببناء متتبع مخزون مع المخزون المتوفر، وقيمة المخزون، وعلامة إعادة طلب للعناصر التي تصل إلى 20 وحدة أو أقل." اطلب منه إدراج العناصر التي تحتاج إلى طلب هذا الأسبوع.

يمكنك أيضاً طرح أسئلة متابعة، مثل أي فئة تستهلك أكبر قدر من السيولة النقدية.

الخطوة 3: مراجعة النتائج وتصديرها

تحقق من أرقام المخزون المتوفر لبضعة عناصر مقابل آخر جرد لك. ثم قم بتصدير الجدول المنظف مرة أخرى إلى Excel، أو حوّل النتائج إلى تقرير قصير أو شرائح عرض لفريقك.

هذا مفيد عندما يتأخر تحديث متتبع المخزون. يمكن لملف تصدير شهري من نظام المبيعات الخاص بك أن يظهر ما خرج بالفعل، والذي يمكنك مقارنته بالسجل.

احتفظ بتصميم الجدولين للتحديثات اليومية. استخدم خطوة الذكاء الاصطناعي عندما تحتاج إلى تنظيف سريع أو مراجعة شهرية.

مراجعة المخزون المتوفر قبل تصدير متتبع المخزون

أخطاء شائعة يجب تجنبها

تأتي معظم المشاكل في متتبع المخزون من كيفية إدخال الأشخاص للبيانات، وليس من الصيغ. هذه هي المشاكل التي تظهر غالباً مع متتبع المخزون في Excel.

  • الكتابة فوق الكميات. سجل كل تغيير كحركة جديدة بدلاً من ذلك.
  • الأسماء بدلاً من المعرفات. تصبح "أقلام" و "صندوق أقلام" عنصرين مختلفين. استخدم معرفات فريدة.
  • عدم وجود مخزون افتتاحي. بدون عدد بداية، سيكون كل رقم مخزون متوفر خاطئاً.
  • وحدات مختلطة. تتبع الصناديق أو القطع الفردية، وليس كليهما، لنفس العنصر.
  • عدم وجود جرد دوري. قارن المتتبع بجرد فعلي كل شهر أو ربع سنة.
  • عدم مراجعة نقاط إعادة الطلب. قم بتحديثها عندما يتغير الاستهلاك أو مدد توريد الموردين.
  • حذف صفوف السجل. لا تحذف أبداً حركة لتصحيح خطأ. أضف إدخالاً تصحيحياً مع ملاحظة بدلاً من ذلك.
  • الكتابة فوق الصيغ. قم بحماية ورقة العناصر حتى لا يستبدل أحد الصيغة برقم.

للعثور على العناصر التي تبقى على الرف لفترة طويلة، راجع دليلنا حول كيفية اكتشاف المخزون بطيء الحركة. للتنبؤ بالطلب، راجع قائمتنا لـ أدوات الذكاء الاصطناعي لإدارة المخزون والتنبؤ بالطلب.

عندما تكون بيانات مخزونك في ملف تصدير غير منظم، جرب Powerdrill Bloom لتنظيفها وتحويلها إلى متتبع جاهز.

الأسئلة الشائعة

كيف يمكنني إنشاء متتبع مخزون بسيط في Excel؟

أنشئ جدول عناصر (Items) يحتوي على معرف العنصر، والاسم، وتكلفة الوحدة، والمخزون الافتتاحي، ونقطة إعادة الطلب. أضف جدول حركات (Movements) يسجل كل تغيير. ثم استخدم SUMIFS لحساب المخزون الوارد، والصادر، والمتوفر.

ما هي الصيغة التي تحسب المخزون المتوفر في Excel؟

المخزون المتوفر يساوي المخزون الافتتاحي زائد المخزون الوارد ناقص المخزون الصادر. استخدم SUMIFS لجمع كميات الوارد (In) والصادر (Out) لكل عنصر من سجل الحركة.

كيف أجعل Excel يميز المخزون المنخفض؟

أضف عمود حالة (Status) بصيغة IF تعيد "Reorder" عندما يكون المخزون المتوفر عند نقطة إعادة الطلب أو أقل منها. ثم ظلل تلك الخلايا باستخدام التنسيق الشرطي.

كم مرة يجب أن أقوم بتحديث متتبع المخزون؟

سجل كل حركة عند حدوثها، أو على الأقل مرة واحدة في اليوم. ثم قارن المتتبع بجرد فعلي كل شهر أو ربع سنة، وأصلح أي فجوات بإدخال تسوية.

هل يمكنني تتبع المخزون في Excel لمواقع متعددة؟

نعم. أضف عمود "الموقع" (Location) إلى جدول الحركات، وأضف الموقع كشرط ثانٍ في SUMIFS. سيحصل كل عنصر حينها على رقم مخزون متوفر لكل موقع.

المصادر: دعم Microsoft، نظرة عامة على جداول Excel · دعم Microsoft، تطبيق التحقق من صحة البيانات على الخلايا · دعم Microsoft، دالة SUMIFS · دعم Microsoft، دالة XLOOKUP · دعم Microsoft، استخدام التنسيق الشرطي لتمييز المعلومات في Excel · دعم Microsoft، استخدام دالة COUNTIF في Microsoft Excel.