تحليل المشتريات في Excel: الإنفاق حسب الصنف والمورد وتركّز الموردين

كيف توزّع قيمة المشتريات على الأصناف والموردين والفترات من ملف سطور الشراء، وتحسب حصة كل مورد والأصناف ذات المورد الواحد: مثال من 12 سطراً في Excel.

بقلم فريق زاديتا · نُشرت في 1 نوفمبر 2026

هذه المهمة ينفّذها على ملفاتك منتج تحليل أسعار الشراء على جهازك بلا إنترنت. المقالة تشرح المهمة نفسها وكيف تُنجز يدوياً.

تحليل الإنفاق على المشتريات (Spend Analysis) هو توزيع قيمة سطور الشراء على ثلاثة محاور: الصنف، والمورد، والفترة. من هذا التوزيع تُحسب حصة كل مورد من الإنفاق، والأصناف التي تُشترى من مورد واحد، والأصناف القليلة التي تستهلك معظم المبلغ. تحليل المشتريات في Excel بهذه الطريقة لا يحتاج إلى أكثر من تقرير سطور فواتير الشراء وجدول محوري. والناتج ليس رقماً واحداً بل خريطة: أين يذهب المال، ومع من، ومتى؛ وهي الخريطة التي تحدد الأصناف والموردين الذين يستحقون تحليل السعر والتفاوض قبل غيرهم.

ما الذي يجيب عنه تحليل الإنفاق على المشتريات

تقرير المشتريات في النظام يعطي إجمالي الفترة وربما إجمالي كل مورد. تحليل الإنفاق يذهب أبعد بثلاثة أسئلة يجيب عنها الملف نفسه: على ماذا ننفق؟ ومع من؟ وكيف يتغير ذلك عبر الفترات؟ مكتب المحاسبة الحكومي الأمريكي (GAO) يصف تحليل الإنفاق في تقريره عن أفضل الممارسات بأنه أداة تعطي معرفة بمن يشتري، ومن يورّد، وكم يُنفق على أي سلع وخدمات، وأين توجد فرص استغلال القوة الشرائية؛ ويذكر أن شركات القطاع الخاص تستخدمه أساساً لنهج استراتيجي في المشتريات.

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

مثال عملي: ملف مشتريات من 12 سطراً

البيانات توضيحية لشركة افتراضية تشغّل أسطول مركبات: 5 أصناف، و3 موردين (أ، ب، ج)، والنصف الأول من 2026، والأسعار بالريال قبل ضريبة القيمة المضافة. القيمة = الكمية × سعر الوحدة.

إجمالي المشتريات 98,910.00 ريال.

الإنفاق حسب الصنف ونظرة 80/20

تُجمع القيمة لكل صنف، ثم تُرتّب الأصناف تنازلياً وتُحسب الحصة التراكمية:

صنفان من خمسة يستهلكان 74.8% من الإنفاق. هذه هي نظرة 80/20: النسب لا تكون 80 و20 بالضبط وتختلف من ملف لآخر، والغرض ترتيب الجهد: الأصناف القليلة التي تحمل معظم القيمة هي التي يُبدأ بها. سطر واحد من الإطارات (16,800 ريال) يفوق كل مشتريات مواد التنظيف والفلاتر معاً (13,920)، فالتفاوض على 2% في سعر الإطارات (773 ريالاً) يساوي أكثر من التفاوض على 10% في سعر مواد التنظيف (576 ريالاً).

الإنفاق حسب المورد وتركّز الموردين

المورد أ يحمل أكثر من نصف الإنفاق، لكن الرقم الأهم في الجدول السابق هو عمود «عدد الموردين»: الأصناف الثلاثة الأولى تُشترى كلٌّ منها من مورد واحد، ومجموعها 84,990.00 ريال، أي 85.9% من الإنفاق بلا سعر بديل في الملف. هذا هو تركّز الموردين بمعناه العملي: ليس حصة مورد كبيرة في ذاتها، بل إنفاق كبير لا يملك مرجعاً سعرياً. سعر الإطارات ارتفع من 420 إلى 440 (4.8%) وسعر الزيت من 1,150 إلى 1,200 (4.3%) خلال ستة أشهر، ولا شيء في الملف يقول إن الارتفاع مبرر أو مبالغ فيه، لأنه لا يوجد مورد ثانٍ للمقارنة.

الصنف الوحيد الذي يمكن مقارنة مورديه هو الفلاتر: المورد أ بسعر 38.00 والمورد ج بسعر 36.00، أي أن سعر أ أعلى بنسبة 5.6%. على 120 حبة الفرق 240 ريالاً، رقم صغير، لكنه الرقم الوحيد في هذا الملف الذي يمكن مساءلة أحد عنه.

الإنفاق حسب الفترة

الربع الأول 51,350.00 (51.9%) والربع الثاني 47,560.00 (48.1%). التوزيع مستقر إجمالاً، لكن تقاطع الصنف مع الفترة يكشف ما يخفيه الإجمالي: إنفاق الإطارات ارتفع من 16,800 في الربع الأول إلى 21,850 في الربع الثاني، وجزء من ذلك كمية (50 حبة مقابل 40) وجزء سعر (435 و440 مقابل 420). فصل أثر الكمية عن أثر السعر هو تحديداً ما يفعله تحليل الأسعار بعد تحليل الإنفاق.

الطريقة اليدوية: تحليل المشتريات في Excel بالجدول المحوري

تقرير المشتريات في Excel الذي يصلح لهذا التحليل هو تقرير سطور فواتير الشراء: سطر لكل صنف في كل فاتورة، بالتاريخ والمورد وكود الصنف والوحدة والكمية وسعر الوحدة. مع الأعمدة A (التاريخ) وB (المورد) وC (الصنف) وE (الوحدة) وF (الكمية) وG (سعر الوحدة) والبيانات في الصفوف 2 إلى 13:

1. عمود القيمة في H: =F2*G2، وعمود الربع في I: =YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3,0).
2. الجدول المحوري: حدد النطاق، ثم إدراج ← PivotTable. الحقول النصية تذهب افتراضياً إلى الصفوف والرقمية إلى القيم، وتُلخَّص القيم بالمجموع (Sum) ما لم يفسّرها Excel نصاً فتُلخَّص بالعدد. ضع الصنف في الصفوف والقيمة في القيم، ورتّب تنازلياً.
3. الحصة: من خيارات حقل القيمة اختر «إظهار القيم كـ» ← نسبة من الإجمالي الكلي. أضف القيمة مرة ثانية إلى منطقة القيم واجعلها «نسبة إجمالي تراكمي» على حقل الصنف، فتحصل على عمود 80/20.
4. تركّز الموردين: جدول محوري ثانٍ بالمورد في الصفوف والقيمة نسبةً من الإجمالي. وللأصناف ذات المورد الواحد: الصنف في الصفوف ثم المورد تحته؛ الصنف الذي لا يظهر تحته إلا مورد واحد هو صنف المورد الواحد.
5. الفترة: الربع في الأعمدة والصنف في الصفوف، فيظهر تقاطع الصنف مع الفترة.

بالصيغ بدل الجدول المحوري: حصة المورد ب =SUMIFS($H$2:$H$13,$B$2:$B$13,"ب")/SUM($H$2:$H$13)، وإنفاق صنف في ربع =SUMIFS($H$2:$H$13,$C$2:$C$13,"M-130",$I$2:$I$13,"2026-Q2").

الجدول المحوري كافٍ لملف الفترة مهما كبر، بشرط نظافة عمودي المورد والصنف. وهنا تبدأ المشكلات.

الأخطاء الشائعة

أكثر الأخطاء أثراً هو المورد الواحد بأكثر من اسم: «مؤسسة النخبة» و«مؤسسة النخبة للتوريدات» و«النخبة» تظهر في الجدول المحوري ثلاثة موردين، فتنخفض حصة المورد الحقيقية ويختفي التركّز. يُكتشف ذلك بفرز قائمة الموردين أبجدياً وقراءتها كاملة قبل التحليل، وبالاعتماد على كود المورد لا اسمه. والصنف بأكثر من كود يعمل بالاتجاه المعاكس: يتفتت إنفاق الصنف فيختفي من قمة قائمة 80/20.

خلط أساس القيمة يشوّه المقارنة بين الموردين: فاتورة بقيمة شاملة ضريبة القيمة المضافة وأخرى قبلها تُظهر فرقاً بنسبة 15% لا وجود له، ويُكتشف عندما تتكرر النسبة نفسها بالضبط بين سطور مورد وآخر. وإبقاء المرتجعات بكمية سالبة يخفض إنفاق الصنف من دون أن يكون ذلك إنفاقاً أقل. والتاريخ الخطأ: الفترة تُبنى على تاريخ الفاتورة لا تاريخ السداد، وإلا انتقل إنفاق مارس إلى مايو تبعاً لشرط الدفع.

وأخيراً قراءة التركّز كمشكلة في ذاته. صنف يُشترى من مورد واحد وتكلفته 2% من الإنفاق لا يستحق مناقصة؛ وصنف يُشترى من مورد واحد وتكلفته 39% يستحقها حتى لو كان المورد ممتازاً. الأولوية هي الإنفاق مع غياب البديل، لا أحدهما وحده.

كيف يختصر المنتج المهمة

أداة تحليل أسعار الشراء تقرأ ملف سطور المشتريات نفسه (Purchases.xlsx: التاريخ والمورد والكمية إلزامية، وكود الصنف أو اسمه، وسعر الوحدة أو إجمالي القيمة، والوحدة اختيارية) وتُخرج عن الموردين ورقتين: «تركّز الموردين» بحصة كل مورد من المشتريات والأصناف التي تُشترى من مورد واحد فقط، و«مقارنة الموردين» بأقل وأعلى ومتوسط سعر لكل مورد للصنف نفسه، للأصناف التي تختلف أسعار مورديها. الوحدات المختلفة للصنف نفسه لا تُقارن ببعضها، والسطور بكمية سالبة تُستبعد افتراضياً. وورقة الملخص تعرض إجمالي قيمة المشتريات المحللة وعدد الأصناف والموردين قبل نتائج التحليلات الخمسة.

ما لا تفعله الأداة: توزيع الإنفاق حسب الصنف والفترة ونظرة 80/20 يبقيان في الجدول المحوري؛ الأداة تبدأ من حيث ينتهي، بحساب فروق الأسعار وارتفاعها وتذبذبها على كل الملف. في اختبار السرعة على جهاز البناء حلّلت 100,000 سطر مشتريات في 37.1 ثانية، وتعمل على جهازك دون رفع الملف.

من خريطة الإنفاق إلى طاولة التفاوض

ترتيب التفاوض يخرج من تقاطع الجدولين: الأصناف الأعلى إنفاقاً أولاً، وبينها ذات المورد الواحد قبل غيرها. في المثال: الإطارات والزيت (74.8% من الإنفاق، مورد واحد لكل منهما، وسعر يرتفع) قبل الفلاتر (8.2%، موردان، وفرق معروف 5.6%). للأصناف ذات المورد الواحد لا يوجد سطر تفاوض في الملف، فالخطوة هي طلب عروض أسعار من موردين آخرين لإيجاد المرجع الغائب. وللأصناف متعددة الموردين، تُستخرج فروق الأسعار من الملف سطراً سطراً.

وفي الحالتين، السعر المدفوع سابقاً هو المرجع. لائحة المشتريات الفيدرالية الأمريكية (FAR 15.404-1) تعدّ مقارنة الأسعار المعروضة بالأسعار التاريخية المدفوعة للصنف نفسه أو المماثل أحد أسلوبي تحليل السعر المفضلين، بشرط صلاحية السعر السابق للمقارنة وتعديله لفروق الشروط والكميات وعوامل السوق. ملف المشتريات النظيف هو مصدر هذه الأسعار التاريخية، وتحليل الإنفاق هو ما يجعله مقروءاً.

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

حصة المورد ونظرة 80/20 وقائمة الأصناف ذات المورد الواحد مؤشرات توجّه الجهد، لا أحكاماً على مورد أو مشترٍ؛ تفسيرها وما يُبنى عليه من قرارات التفاوض والتوريد يبقيان ضمن الحكم المهني ومسؤولية الإدارة. وعندما يتجاوز الملف ما يحتمله الجدول المحوري من تنظيف وتكرار، تنجز أداة تحليل أسعار الشراء جزء التركّز وفروق الأسعار على الملف كاملاً.

أسئلة شائعة

المراجع

1. Best Practices: Using Spend Analysis to Help Agencies Take a More Strategic Approach to Procurement (GAO-04-870) — U.S. GAO — تعريف تحليل الإنفاق: أداة تعطي معرفة بمن يشتري ومن يورّد وكم يُنفق على أي سلع وخدمات وأين فرص استغلال القوة الشرائية؛ وأن الشركات تستخدمه أساساً لنهج استراتيجي في المشتريات.
2. Create a PivotTable to analyze worksheet data — Microsoft Support — خطوات إنشاء الجدول المحوري، والتلخيص الافتراضي للحقول الرقمية بالمجموع وللنصية بالعدد، وعرض القيم نسبةً من الإجمالي الكلي.
3. FAR 15.404-1 Proposal analysis techniques — Acquisition.gov — مقارنة الأسعار المعروضة بالأسعار التاريخية المدفوعة للصنف نفسه أو المماثل أحد الأسلوبين المفضلين لتحليل السعر، بشرط صلاحية السعر السابق للمقارنة وتعديله لفروق الشروط والكميات وعوامل السوق.
4. الدليل الإرشادي الخاص بتعديل نسبة ضريبة القيمة المضافة الأساسية إلى 15% — هيئة الزكاة والضريبة والجمارك — النسبة الأساسية 15% اعتباراً من 1 يوليو 2020م

مقالات ذات صلة

• تحليل أسعار الشراء: كم دفعت زيادة عن أقل سعر؟ · تحليل أسعار الشراء
• مراجعة المشتريات: اختبارات التسعير وتقسيم أوامر الشراء بمثال محسوب · تحليل أسعار الشراء
• مقارنة أسعار الموردين قبل الشراء مقابل تحليل أسعار الشراء بعده: ما الذي يفوت المصفوفة؟ · تحليل أسعار الشراء
• الفواتير المدفوعة مرتين: كيف تحدث وكيف تُكتشف · كشف الفواتير المدفوعة مرتين

الأداة التي تنفّذ هذه المهمة: تحليل أسعار الشراء. الدور: مخازن ومشتريات · مراجع.