كيف تنشئ نظام إدارة مبيعات متكامل على Excel
من الصفر حتى لوحة التحكم والأرباح والخسائر
شرح تفصيلي لبناء نظام مبيعات Excel يشمل الطلبات والعملاء والمنتجات والمصروفات ولوحة تحكم مبيعات وتقرير أرباح وخسائر — مع جدول كامل لكل دوال Excel المستخدمة ونسخ الصيغ دون نقص. مقارنة بين النسخة المجانية (ملف فارغ + شرح) والنسخة المدفوعة (VBA + نسخة صيغ فقط).
كلمات مفتاحية: نظام مبيعات Excel، شيت مبيعات، صيغ Excel للمبيعات، COGS، مصروفات تشغيلية، لوحة تحكم، أرباح وخسائر، إدارة مخزون، جعفر نت.
لماذا نظام مبيعات على Excel؟
كثير من أصحاب المحلات والمشاريع الصغيرة يحتاجون تتبع الطلبات والمخزون والعملاء والمصروفات في ملف واحد يفتحون عليه يوميًا. Excel مناسب لأنه متوفر، لا يحتاج اشتراكًا إلزاميًا، ويمكن تطويره تدريجيًا من جداول بسيطة إلى Dashboard يعرض المؤشرات الرئيسية.
الفكرة التي نشرحها هنا متوافقة مع هيكل نظام المبيعات المتكامل من جعفر نت: فصل تكلفة البضاعة المباعة (COGS) عن المصروفات التشغيلية (OPEX)، ثم حساب صافي الربح وهامش الربح بوضوح — وهذا ما يبحث عنه من يريد إدارة مبيعات احترافية بدون تعقيد.
هيكل الملف: أوراق العمل ووظيفة كل ورقة
ابدأ بإنشاء مصنف Excel (.xlsx) وأضف الأوراق التالية بنفس الأسماء (أو عدّل الصيغ لتطابق أسماءك):
| ورقة العمل | الوظيفة | أعمدة أساسية (مرجع الصيغ) |
|---|---|---|
| الطلبات | سجل الطلبات والتواريخ والمبالغ والديون | B تاريخ، E معرف الطلب، F تكلفة الوحدة، H كمية، I إجمالي البيع، K الدين |
| العملاء | بيانات العملاء والمشتريات والتصنيف | B الاسم، G إجمالي المشتريات، I الديون، K التصنيف |
| المنتجات | المخزون والحالة | C اسم المنتج، J المتاح، K الحالة (نص يحتوي «نفد» عند النفاد) |
| المصروفات | المصروفات مع التاريخ والنوع | B تاريخ، C نوع المصروف، E المبلغ — نوع «شراء بضاعة» = COGS |
| Dashboard | لوحة المؤشرات والملخص الشهري والتنبيهات | E2 اختيار الشهر أو «الكل»، خلايا مؤشرات A5 وE5 وI5 وM5 وA10 وE10 وI10 وM10 حسب التصميم |
| الأرباح والخسائر (أو اسمك لها) | تقرير P&L حسب الشهر | D2 شهر أو «الكل»، وجداول حسب تصميمك |
; بـ , في كل الصيغ أدناه. تجنّب استخدام MONTH(مدى_كامل) داخل SUMPRODUCT لأنه يسبب أخطاء أو نتائج خاطئة؛ الأفضل فلترة التاريخ بـ DATE وEDATE أو SUMIFS.
المنطق المحاسبي (مرجع واحد لكل الصيغ)
- إجمالي المبيعات = مجموع عمود إجمالي البيع (I) من ورقة الطلبات ضمن الفترة المختارة.
- COGS (محاسبيًا من المصروفات) = مجموع مبالغ المصروفات من نوع «شراء بضاعة» في الفترة المختارة.
- COGS (من الطلبات) إن رغبت = مجموع (F × H) لصفوف الطلبات في الشهر (تكلفة الوحدة × الكمية).
- OPEX = مجموع المصروفات حيث نوع المصروف ليس «شراء بضاعة».
- إجمالي الربح = المبيعات − COGS.
- صافي الربح = إجمالي الربح − OPEX.
- هامش الربح % = صافي الربح ÷ المبيعات (مع تجنب القسمة على صفر).
- الديون = مجموع عمود الدين (K) في الطلبات ضمن الفترة المختارة.
قائمة شاملة: كل الدوال المستخدمة في النظام
استخدم الجدول التالي كـ فهرس تحقق عند بناء النسخة المجانية يدويًا — لتتأكد أنك لم تنسَ دالة:
| الدالة | الاستخدام في النظام |
|---|---|
| SUM | جمع إجمالي عند اختيار «الكل» |
| SUMIFS | مبيعات، ديون، COGS، OPEX حسب نطاق تاريخ أو نوع مصروف |
| SUMPRODUCT | ضرب أعمدة (مثل F×H) مع شروط تاريخ؛ أو تنبيهات المخزون مع INDEX/SMALL |
| COUNTA | عد العملاء أو الحقول المملوءة |
| COUNTIFS | عد الطلبات حسب الشهر وعدم فراغ معرف الطلب |
| COUNTIF | عد منتجات تحتوي حالة «نفد» |
| IF / IFERROR | شروط «الكل» مقابل شهر؛ معالجة الأخطاء |
| MATCH | تحويل اسم الشهر العربي إلى رقم 1–12 |
| DATE | بداية الشهر من سنة + رقم شهر |
| YEAR / TODAY | سنة التقرير (أو استبدلها بخلية سنة ثابتة) |
| EDATE | نهاية الشهر (بداية الشهر التالي كحد علوي) |
| N | تحويل فراغ/نص إلى 0 داخل SUMPRODUCT |
| ISNUMBER / SEARCH | البحث عن «نفد» في نص الحالة |
| TEXT | تنسيق تاريخ آخر شراء (مع تجنب 1900) |
| MAXIFS | آخر تاريخ طلب لعميل |
| NUMBERVALUE | تحويل نص يشبه الرقم إلى رقم للتصنيف |
| INDEX / SMALL / ROW | قوائم تنبيهات المخزون في Excel 2016 |
| OR / AND | شروط عمود المساعد في المنتجات |
| LET | اختياري — Excel 365 فقط لتبسيط الصيغ الطويلة |
1) خلايا مساعدة في Dashboard (بدون VBA)
ضع اختيار الشهر في Dashboard!E2 (قائمة تحقق: أسماء الشهور + «الكل»). ثم أضف:
F2 — رقم الشهر أو 0 للكل
=IF($E$2="الكل";0;MATCH($E$2;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0))
G2 — بداية الشهر
=IF($F$2=0;0;DATE(YEAR(TODAY());$F$2;1))
H2 — بداية الشهر التالي (حد علوي للفلترة)
=IF($F$2=0;0;EDATE($G$2;1))
2) مؤشرات أعلى لوحة التحكم (صيغ جاهزة)
عدّل مراجع الخلايا (A5، E5…) إذا كان تخطيط ملفك مختلفًا. النطاقات هنا حتى الصف 503 — غيّرها حسب حجم جدولك.
إجمالي المبيعات — A5
=IFERROR(IF($F$2=0;SUM(الطلبات!I$4:I$503);SUMIFS(الطلبات!I$4:I$503;الطلبات!B$4:B$503;">="&$G$2;الطلبات!B$4:B$503;"<"&$H$2));0)
COGS من المصروفات — (ضعها في الخلية التي تعرض تكلفة البضاعة)
=IFERROR(IF($F$2=0;SUMIFS(المصروفات!E$4:E$503;المصروفات!C$4:C$503;"شراء بضاعة");SUMIFS(المصروفات!E$4:E$503;المصروفات!C$4:C$503;"شراء بضاعة";المصروفات!B$4:B$503;">="&$G$2;المصروفات!B$4:B$503;"<"&$H$2));0)
OPEX — I5
=IFERROR(IF($F$2=0;SUMIFS(المصروفات!E$4:E$503;المصروفات!C$4:C$503;"<>شراء بضاعة");SUMIFS(المصروفات!E$4:E$503;المصروفات!C$4:C$503;"<>شراء بضاعة";المصروفات!B$4:B$503;">="&$G$2;المصروفات!B$4:B$503;"<"&$H$2));0)
إجمالي الديون — M5
=IFERROR(IF($F$2=0;SUM(الطلبات!K$4:K$503);SUMIFS(الطلبات!K$4:K$503;الطلبات!B$4:B$503;">="&$G$2;الطلبات!B$4:B$503;"<"&$H$2));0)
عدد الطلبات — A10
=IFERROR(IF($F$2=0;COUNTA(الطلبات!E$4:E$503);COUNTIFS(الطلبات!E$4:E$503;"<>";الطلبات!B$4:B$503;">="&$G$2;الطلبات!B$4:B$503;"<"&$H$2));0)
عدد العملاء — E10
=IFERROR(COUNTA(العملاء!B$4:B$503);0)
منتجات نفدت — I10
=IFERROR(COUNTIF(المنتجات!K$4:K$503;"*نفد*");0)
صافي الربح — E5 (مثال: المبيعات A5، COGS في D5، OPEX I5)
=IFERROR($A$5-$D$5-$I$5;0)
هامش الربح كنسبة — M10
=IFERROR(IF($A$5=0;0;$E$5/$A$5);0)
ثم طبّق تنسيق النسبة المئوية على الخلية.
3) الملخص الشهري التفصيلي (صف لكل شهر)
في عمود A ضع أسماء الشهور (مثلاً من A16). في عمود المبيعات لصف يناير استخدم (مع مرجع A16 — اسحب لأسفل):
=IFERROR(SUMIFS(الطلبات!I$4:I$503;الطلبات!B$4:B$503;">="&DATE(YEAR(TODAY());MATCH($A16;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);الطلبات!B$4:B$503;"<"&EDATE(DATE(YEAR(TODAY());MATCH($A16;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);1));0)
عدد الطلبات لكل شهر (عمود H مثلاً)
=IFERROR(COUNTIFS(الطلبات!E$4:E$503;"<>";الطلبات!B$4:B$503;">="&DATE(YEAR(TODAY());MATCH($A16;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);الطلبات!B$4:B$503;"<"&EDATE(DATE(YEAR(TODAY());MATCH($A16;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);1));0)
لكل شهر: COGS من المصروفات بنفس منطق SUMIFS مع MATCH($A16;...) ونطاق تاريخ المصروفات؛ OPEX بشرط النوع <>شراء بضاعة. إجمالي الربح = مبيعات − COGS؛ صافي الربح = إجمالي الربح − OPEX؛ الهامش = صافي/مبيعات.
4) صفحة الأرباح والخسائر (حسب D2)
عندما يكون الشهر في D2 (شهر عربي أو «الكل»):
مبيعات الشهر أو الكل
=IFERROR(IF(D2="الكل";SUM(الطلبات!I$4:I$503);SUMIFS(الطلبات!I$4:I$503;الطلبات!B$4:B$503;">="&DATE(YEAR(TODAY());MATCH(D2;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);الطلبات!B$4:B$503;"<"&EDATE(DATE(YEAR(TODAY());MATCH(D2;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);1));0)
الديون المستحقة حسب الشهر
=IFERROR(IF(D2="الكل";SUM(الطلبات!K$4:K$503);SUMIFS(الطلبات!K$4:K$503;الطلبات!B$4:B$503;">="&DATE(YEAR(TODAY());MATCH(D2;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);الطلبات!B$4:B$503;"<"&EDATE(DATE(YEAR(TODAY());MATCH(D2;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);1));0)
COGS من الطلبات (F × H) حسب الشهر في D2
=IFERROR(IF(D2="الكل";SUMPRODUCT(N(الطلبات!F$4:F$503);N(الطلبات!H$4:H$503));SUMPRODUCT((--(الطلبات!B$4:B$503>=DATE(YEAR(TODAY());MATCH(D2;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1)))*(--(الطلبات!B$4:B$503<EDATE(DATE(YEAR(TODAY());MATCH(D2;{"يناير";"فبراير";"مارس";"أبريل";"مايو";"يونيو";"يوليو";"أغسطس";"سبتمبر";"أكتوبر";"نوفمبر";"ديسمبر"};0);1);1)))*N(الطلبات!F$4:F$503)*N(الطلبات!H$4:H$503)));0)
5) العملاء: آخر تاريخ شراء وتصنيف VIP
آخر تاريخ (تجنب ظهور 1900 عند عدم وجود طلبات)
=LET(d;MAXIFS(الطلبات!B$4:B$500;الطلبات!C$4:C$500;العملاء!B4);IFERROR(IF(d>0;TEXT(d;"yyyy-mm-dd");"-");"-"))
بدون LET (Excel 2016):
=IFERROR(IF(MAXIFS(الطلبات!B$4:B$500;الطلبات!C$4:C$500;العملاء!B4)>0;TEXT(MAXIFS(الطلبات!B$4:B$500;الطلبات!C$4:C$500;العملاء!B4);"yyyy-mm-dd");"-");"-")
تصنيف العميل من خلية المشتريات G4
=LET(x;IFERROR(NUMBERVALUE(G4);0);IF(x=0;"جديد";IF(x>=5000;"VIP";IF(x>=2000;"مميز";"عادي"))))
6) تنبيهات المخزون — Excel 2016 (بدون FILTER)
في المنتجات!L4 (اسحب لأسفل):
=IF(C4="";0;IF(OR(N(J4)=0;ISNUMBER(SEARCH("نفد";K4)));1;IF(AND(N(J4)>0;N(J4)<=10);2;0)))
في المنتجات!R4 لتجميع «نفد أولاً ثم قليل» (عدّل 500 إذا زاد عدد الصفوف):
=IF(L4=0;"";IF(L4=1;ROW()-ROW(المنتجات!$C$4)+1;500+ROW()-ROW(المنتجات!$C$4)+1))
في Dashboard عمود مخفي D32 (مصفوفة — Ctrl+Shift+Enter) ثم اسحب لأسفل:
=IFERROR(SMALL(IF(المنتجات!$R$4:$R$503<>"";المنتجات!$R$4:$R$503);ROWS($D$32:$D32));"")
A32 اسم المنتج:
=IF($D32="";"";INDEX(المنتجات!C:C;IF($D32>500;$D32-500;$D32)+ROW(المنتجات!$C$4)-1))
B32 المتاح:
=IF($D32="";"";INDEX(المنتجات!J:J;IF($D32>500;$D32-500;$D32)+ROW(المنتجات!$C$4)-1))
C32 الحالة:
=IF($D32="";"";IF($D32>500;"⚠️ قليل";"⛔ نفد"))
SMALL(IF(...)) في Excel 2016 يجب إدخالها كـ صيغة مصفوفة (Ctrl+Shift+Enter). إذا كان لديك Excel 365 يمكن تبسيط التنبيهات بدوال مثل FILTER.
المقارنة: النسخة المجانية مقابل النسخة المدفوعة
النسخة المجانية ملف Excel فارغ الهيكل أو بدون صيغ يحمّله القارئ بعد إدخال البريد؛ يبني النظام بنفسه باتباع هذا الشرح ونسخ الصيغ خطوة بخطوة — ممتاز للتعلم والفهم العميق لـ صيغ Excel.
النسخة المدفوعة هي المنتج الأصلي من متجر جعفر نت: يشمل ملفًا مع VBA لتحديث لوحة التحكم تلقائيًا عند فتح الملف أو تغيير الشهر، وملفًا بدون ماكرو يعتمد بالكامل على الدوال والصيغ الجاهزة — لتوفير الوقت وتقليل الأخطاء.
| البند | نسخة مجانية (بعد البريد) | نسخة مدفوعة ⭐ |
|---|---|---|
| هيكل أوراق الطلبات والعملاء والمنتجات والمصروفات | ✓ تبنيه يدويًا بالشرح | ✓ جاهز |
| لوحة تحكم مبيعات Dashboard | ✓ بالصيغ من المقالة | ✓ جاهزة + خيار VBA |
| تحديث تلقائي عند فتح الملف / تغيير الشهر | ✗ يدوي أو صيغ فقط | ✓ مع ملف VBA |
| ملف بدون VBA بصيغ كاملة | ✗ تصنعه بنفسك | ✓ مرفوع جاهز |
| تقرير أرباح وخسائر مع فلترة شهر | ✓ تنفذ الصيغ هنا | ✓ مدمج |
| تنبيهات مخزون + أفضل عملاء | ✓ بالصيغ (2016 أو 365) | ✓ جاهز |
| توفير وقت الإعداد | متوسط | ✓ أقصى توفير |
| دعم وتحديثات من الناشر | ✗ | ✓ حسب المنتج في المتجر |
من واقع تجربة تصميم أنظمة على Excel: النسخة التي تكتبها بنفسك تمنحك مرونة مطلقة لكنها تأخذ وقتًا؛ النسخة الجاهزة تناسب من يريد نظام مبيعات جاهز للعمل فورًا مع خيار الأتمتة بـ VBA أو العمل بصيغ فقط حسب سياسة الأمان في شركته.
📥 حمّل النسخة المجانية (ملف ZIP)
أدخل بريدك الإلكتروني وسيبدأ التحميل — نفس آلية المقالات السابقة على الموقع.
الأسئلة الشائعة
خاتمة
بناء نظام مبيعات Excel منظم يبدأ بورقة بيانات نظيفة ثم ربط لوحة تحكم بصيغ موثوقة. هذه المقالة جمعت جميع الدوال والصيغ الأساسية في مكان واحد لتقليل النسيان. إن رغبت في الجاهزية الكاملة مع كود VBA ونسخة بدون برمجة — زر صفحة المنتج في المتجر أدناه.