هل تعلم أن Excel يحمل في داخله محرك تحسين رياضي يستخدمه كبار المديرين الماليين حول العالم — ولا يعرفه أغلب المحاسبين؟
البرمجة الخطية في Excel — عبر أداة Solver المدمجة — تُمكّنك من الإجابة على أسئلة صعبة بدقة رياضية: أي المنتجات تنتج؟ كيف توزع ميزانيتك؟ متى يكون التوسع في الطاقة مربحاً؟
في هذا الدليل ستجد كل ما تحتاجه: المفهوم، المثال العملي، خطوات Excel Solver، وكيف تقرأ نتائج تقرير الحساسية كمدير مالي محترف.
ما هي البرمجة الخطية؟ (بلغة المحاسب لا الرياضيات)
البرمجة الخطية هي أسلوب رياضي لإيجاد أفضل قرار ممكن — تعظيم ربح أو تقليل تكلفة — في ظل موارد محدودة.
كل مسألة برمجة خطية تتكون من ثلاثة عناصر يعرفها كل محاسب:
| العنصر | في المحاسبة | في البرمجة الخطية |
|---|---|---|
| ماذا نقرر؟ | كميات الإنتاج، توزيع الميزانية | متغيرات القرار (x₁, x₂…) |
| ما الهدف؟ | تعظيم الربح أو تخفيض التكلفة | دالة الهدف (Objective Function) |
| ما الحدود؟ | ساعات العمل، المواد الخام، رأس المال | القيود (Constraints) |
الموارد دائماً محدودة، والبرمجة الخطية في Excel تجد أفضل تخصيص لها رياضياً — لا تخمينياً.
مثال عملي: مصنع أثاث يريد تعظيم ربحه الأسبوعي
قبل أن نفتح Excel، لا بد من صياغة المشكلة بوضوح.
البيانات:
| المكتب (x₁) | الكرسي (x₂) | الطاقة المتاحة | |
|---|---|---|---|
| ربح الوحدة | 70 دولار | 50 دولار | — |
| ساعات القطع | 2 ساعة | 1 ساعة | 120 ساعة/أسبوع |
| ساعات التجميع | 2 ساعة | 2 ساعة | 100 ساعة/أسبوع |
السؤال: كم مكتباً وكم كرسياً ننتج أسبوعياً لتحقيق أعلى ربح ممكن؟
الهدف: تعظيم Z = 70x₁ + 50x₂ القيود: 2x₁ + x₂ ≤ 120 (ساعات القطع) و 2x₁ + 2x₂ ≤ 100 (ساعات التجميع) وكلاهما ≥ 0
الآن ننتقل إلى Excel.
كيف تستخدم Excel Solver للبرمجة الخطية — خطوة بخطوة
الخطوة الأولى: تفعيل أداة Solver
أداة Solver موجودة في Excel لكنها غير مفعّلة افتراضياً.
لتفعيلها: File ← Options ← Add-ins ← Manage: Excel Add-ins ← Go ← ✅ Solver Add-in ← OK
بعد التفعيل ستجدها في تبويب Data في أقصى اليمين.
الخطوة الثانية: بناء نموذج البيانات في Excel
أنشئ الجدول التالي في ورقة عمل جديدة:
A B C
1 المتغير المكتب (x₁) الكرسي (x₂)
2 الكمية المثلى (قرار) 0 0 ← B2, C2: خلايا الحل
3
4 دالة الهدف
5 ربح الوحدة 70 50
6 إجمالي الربح =SUMPRODUCT(B5:C5,B2:C2) ← خلية الهدف
7
8 القيود
9 معاملات القطع 2 1
10 معاملات التجميع 2 2
11
12 القطع المستخدم =SUMPRODUCT(B9:C9,$B$2:$C$2) الحد: 120
13 التجميع المستخدم =SUMPRODUCT(B10:C10,$B$2:$C$2) الحد: 100ملاحظة مهمة: اترك B2 و C2 بالقيمة صفر — Solver سيحسب القيم المثلى تلقائياً.
الخطوة الثالثة: ضبط إعدادات Solver
اذهب إلى Data ← Solver، ثم اضبط الإعدادات التالية:
Set Objective: اختر خلية إجمالي الربح (B6)
To: اختر Max
By Changing Variable Cells: حدد B2:C2
Subject to the Constraints — اضغط Add لإضافة كل قيد:
- B12 <= 120 (قيد ساعات القطع)
- B13 <= 100 (قيد ساعات التجميع)
Make Unconstrained Variables Non-Negative: ✅ ضع علامة
Select a Solving Method: اختر Simplex LP
الخطوة الرابعة: تشغيل Solver واستخراج تقرير الحساسية
اضغط Solve. في نافذة النتائج:
- اختر Sensitivity من قائمة Reports
- اضغط OK
النتيجة المثلى:
- المكاتب (x₁) = 20 مكتباً
- الكراسي (x₂) = 30 كرسياً
- أقصى ربح أسبوعي = $2,900
تقرير الحساسية: أين تكمن القيمة الحقيقية للمحاسب
معظم المستخدمين يأخذون النتيجة ويغلقون Excel. هذا خطأ.
تقرير الحساسية الذي أنشأه Solver في ورقة منفصلة يجيب على الأسئلة الإدارية الأهم.
أسعار الظل (Shadow Prices) — ما قيمة الساعة الإضافية من الموارد؟
| القيد | الاستخدام الفعلي | الحد المتاح | سعر الظل |
|---|---|---|---|
| ساعات القطع | 120 | 120 | $10 |
| ساعات التجميع | 100 | 100 | $25 |
التفسير العملي:
- كل ساعة إضافية في التجميع تضيف $25 للربح الأسبوعي
- كل ساعة إضافية في القطع تضيف $10 للربح الأسبوعي
قرار التوظيف المبني على البيانات: إذا كانت تكلفة ساعة عمل إضافية في التجميع (راتب + تكاليف) أقل من $25، فالتوظيف الإضافي مربح رياضياً. هذا ليس تقديراً — هو حساب دقيق.
القاعدة الذهبية: سعر الظل = الحد الأقصى المبرر دفعه مقابل وحدة إضافية من أي مورد.
نطاقات الاستقرار — متى تتغير خطة الإنتاج؟
يُخبرك التقرير: طالما ربح المكتب يبقى بين $50 و$120، تبقى الكميات المثلى (20 مكتباً، 30 كرسياً) كما هي.
هذا مفيد جداً عند: تقلب أسعار المواد الخام، مراجعة التسعير، تفاوض عقود التوريد.
4 تطبيقات مباشرة في عمل المحاسب اليومي
البرمجة الخطية في Excel لا تقتصر على المصانع. إليك تطبيقات من بيئة المحاسب الفعلية:
1. تحسين مزيج المنتجات
لديك 5 منتجات بهوامش ربح مختلفة وموارد مشتركة محدودة. Solver يحدد الكميات المثلى لكل منتج لتعظيم إجمالي هامش المساهمة.
2. تخصيص ميزانية التسويق
ميزانية 500,000 ريال على قنوات متعددة (رقمي، طباعة، معارض) بعوائد مختلفة. Solver يوزعها بما يعظم العائد الكلي مع احترام الحدود الدنيا والقصوى لكل قناة.
3. جدولة القوى العاملة
تغطية ورديات يومية بأقل تكلفة ممكنة مع احترام قواعد العمل ومتطلبات التغطية لكل فترة.
4. تخطيط الإنتاج الموسمي
توزيع طاقة الإنتاج على أشهر الموسم بما يوازن بين تكاليف التخزين وتكاليف الإنتاج والطلب المتوقع.
متى يكفي Excel Solver؟ ومتى تحتاج أداة أخرى؟
| الحالة | Excel Solver |
|---|---|
| أقل من 200 متغير وقيد | ✅ يكفي تماماً |
| تحليل سريع وعروض إدارية | ✅ الأنسب |
| نموذج يُحدَّث يومياً آلياً | ⚠️ يحتاج VBA |
| أكثر من 200 متغير | ❌ تحتاج Python أو Gurobi |
| تكامل مع ERP أو قواعد بيانات | ❌ حلول برمجية متخصصة |
لغالبية المحاسبين والمديرين الماليين في الشركات المتوسطة، Excel Solver يغطي 90% من الحاجة الفعلية.
خلاصة
البرمجة الخطية في Excel ليست أداة للمبرمجين أو الرياضيين — هي أداة للمحاسب الذي يريد أن تكون قراراته مبنية على أرقام لا تخمينات.
الفرق بين “نبدو نركّز على المنتج A” وبين “المنتج A يجب أن يأخذ 60% من الطاقة الإنتاجية لأن كل ساعة إضافية فيه تضيف $18 للربح” — هو فرق Excel Solver.
الأداة موجودة في جهازك الآن. ما ينقص فقط هو معرفة كيف تُشغّلها.
هل طبّقت هذا على حالة فعلية في شركتك؟ شارك تجربتك في التعليقات — أو أرسل لي تفاصيل مسألتك وسأساعدك في صياغتها.
حمل ملف اكسيل جاهز لتوضيح الفكره
محاسب قانوني ومراقب حسابات معتمد بخبرة تمتد منذ عام 2006، متخصص في الضرائب المصرية وتأسيس الشركات، وخبير في تصميم الأنظمة المحاسبية المتقدمة وحلول الربط الإلكتروني. مؤسس منصة “محاسب عربي/arabicaccountant.com” لتقديم الحلول المالية والتعليمية المتكاملة.
