Excel هو أكثر أدوات التحليل انتشاراً في العالم، ومع ذلك لا يستغل أغلب مستعمليه إلا جزءاً صغيراً منه. تقودك هذه الدورة، في اثني عشر فصلاً وأربعة مستويات، من صيغتك الأولى إلى لوحة متابعة احترافية ثم إلى خطواتك الأولى في Power BI. تشتغل من البداية إلى النهاية على مجموعة البيانات نفسها: مبيعات الحالة الدراسية لأكاديمية إشراقة 7 (بيانات تدريبية)، أي 2400 صف على مدى سنتين.
| الفئة المستهدفة | المستويات | المدة المقترحة | البرامج |
|---|---|---|---|
| الطلبة، الأساتذة، المهندسون، الأجراء، المستقلون، روّاد الأعمال | من المبتدئ إلى الخبير، 12 فصلاً | من 30 إلى 40 ساعة، بمعدل فصل في كل حصة | Excel 2016 أو أحدث؛ Excel 2021 أو Microsoft 365 للفصلين 5 (XLOOKUP) و10؛ Power BI Desktop (مجاني، على Windows) للفصل 12 |
كيف تتابع الدورة
- نزّل مصنف التمارين ومصنف الحلول ودليل PDF بلغتك.
- اقرأ فصلاً ثم أنجز تمرينه في الورقة المقابلة (من Ex01 إلى Ex11): الخلايا الصفراء عليك تعبئتها.
- قارن بمصنف الحلول: كل خلية خضراء تتضمن الصيغة، وكل ورقة تشرح الطريقة.
- أجب عن أسئلة التقييم الثلاثة قبل الانتقال إلى الفصل الموالي.
- اختم بالمشروع الختامي: لوحة متابعة المبيعات في الفصل 11.
| المستوى | الفصول | تصير قادراً على… |
|---|---|---|
| 1 · مبتدئ | من 1 إلى 3 | إدخال البيانات وحسابها وتنظيمها دون أخطاء |
| 2 · متوسط | من 4 إلى 7 | الإجابة عن سؤال مهني بالصيغ أو بجدول محوري |
| 3 · متقدم | من 8 إلى 10 | العرض والأتمتة وكتابة الصيغ الحديثة |
| 4 · خبير | 11 و12 | تصميم لوحة متابعة وأول تقرير Power BI |
المستوى 1 · مبتدئ — الفصل 1
التعرّف على Excel: المصنف والخلايا وأولى الحسابات
ينظّم Excel المعلومات في شبكة. كل خانة تسمى خلية، ولها عنوان يتكوّن من عمودها وصفّها: C13 هي الخلية الواقعة في العمود C والصف 13. ملف Excel يسمى مصنفاً، ويضم ورقة واحدة أو أكثر تظهر على شكل ألسنة أسفل الشاشة.
ثلاثة أنواع من المحتوى
تحتوي الخلية على نص، أو على رقم أو تاريخ، أو على صيغة تبدأ دائماً بعلامة =. يحاذي Excel النصوص تلقائياً إلى جهة والأرقام والتواريخ إلى الجهة المقابلة؛ هذه المحاذاة التلقائية أول وسيلة للمراقبة: رقم يصطف مع النصوص هو في الحقيقة نص، وستتجاهله الحسابات.
التاريخ رقم متنكّر: يعدّ Excel الأيام انطلاقاً من فاتح يناير 1900. لذلك يمكن طرح تاريخين للحصول على مدة. أدخل التاريخ دائماً على شكل 15/03/2025، ولا تكتبه بالحروف في خلية مخصصة للحساب.
صيغتك الأولى
- انقر على الخلية التي ينبغي أن تعرض النتيجة.
- اكتب
=ثم انقر على خلية الكمية، واكتب*ثم انقر على خلية السعر: تحصل مثلاً على=B13*C13. - اضغط Enter. تعرض الخلية النتيجة، ويحتفظ شريط الصيغة بالعملية.
- انسخ الصيغة نحو الأسفل بالنقر المزدوج على المربع الصغير في زاوية الخلية (مقبض التعبئة). يكيّف Excel أرقام الصفوف تلقائياً:
=B14*C14ثم=B15*C15…
الدوال الأربع الأساسية
| الحاجة | الدالة | في Excel الفرنسي |
|---|---|---|
| جمع نطاق | =SUM(D13:D20) |
SOMME |
| حساب المتوسط | =AVERAGE(D13:D20) |
MOYENNE |
| إيجاد أكبر قيمة | =MAX(D13:D20) |
MAX |
| عدّ الخلايا غير الفارغة | =COUNTA(A13:A20) |
NBVAL |
الاختصار Alt + = يُدرج جمعاً تلقائياً. والكتابة D13:D20 تعني نطاقاً: كل الخلايا من D13 إلى D20.
المستوى 1 · مبتدئ — الفصل 2
بيانات نظيفة وجداول منظَّمة
كل ما ستنجزه لاحقاً — الصيغ، الجداول المحورية، الرسوم، Power Query — يتوقف على جودة الجدول الأصلي. والجدول الصالح للتحليل يحترم خمس قواعد بسيطة.
- صف واحد = تسجيل واحد (عملية بيع، زبون، فاتورة).
- عمود واحد = معلومة واحدة، بعنوان فريد في صف واحد.
- لا صف ولا عمود فارغ وسط البيانات، ولا خلايا مدمجة.
- نوع واحد في كل عمود: تواريخ فقط، أو أرقام فقط، أو نصوص فقط.
- لا مجاميع ولا مجاميع فرعية داخل الجدول: تُحسب في مكان آخر.
التنظيف بالدوال
| المشكل | الدالة | في Excel الفرنسي |
|---|---|---|
| فراغات زائدة (في البداية، في النهاية، مزدوجة) | =TRIM(A13) |
SUPPRESPACE |
| أحرف لاتينية غير منتظمة ← حرف كبير في أول كل كلمة | =PROPER(A13) |
NOMPROPRE |
| تحويل الكل إلى أحرف كبيرة | =UPPER(B13) |
MAJUSCULE |
يمكن تركيب الدوال: =PROPER(TRIM(A13)) تحذف الفراغات أولاً ثم تصحّح حالة الأحرف. بالنسبة إلى Excel، «Atlas Conseil» و«atlas conseil» زبونان مختلفان؛ بعد التنظيف يصيران زبوناً واحداً. الدالتان PROPER وUPPER تخصّان الأحرف اللاتينية؛ أما TRIM فتفيد مع العربية أيضاً.
كشف المكررات
الصيغة =IF(COUNTIFS($D$13:D13,D13,$E$13:E13,E13)>1,"مكرر","فريد") تعدّ كم مرة ظهر الزوج زبون-مدينة من أعلى اللائحة إلى الصف الحالي. النطاق $D$13:D13 يتمدد مع كل نسخ: الظهور الأول يبقى «فريد» وما بعده يصير «مكرر». الأمر «بيانات ‹ إزالة التكرارات» ينظّف بنقرة واحدة لكنه لا يُريك ما يحذفه: علّم أولاً ثم احذف.
الجدول المنظَّم
انقر داخل بياناتك ثم Ctrl + T، أو «إدراج ‹ جدول» (Insert › Table). يصير النطاق كائناً له اسم تغيّره من تبويب «تصميم الجدول»: Sales، Products…
- يتمدد تلقائياً: كل صف مضاف يدخل في الصيغ والرسوم والجداول المحورية المبنية عليه.
- الصيغة المكتوبة في عمود تُنسخ تلقائياً على العمود كله.
- تصير الصيغ مقروءة:
=SUM(Sales[المبلغ الصافي])بدل=SUM(N2:N2401). - التصفية والفرز وصف الإجمالي مدمجة فيه.
المستوى 1 · مبتدئ — الفصل 3
الصيغ والمراجع: النسبية والمطلقة والمختلطة والمنظَّمة
نسخ الصيغة يعني نقلها مع مراجعها. والمهارة كلها في تحديد ما ينبغي أن يتبع الحركة وما ينبغي أن يبقى ثابتاً.
| النوع | الكتابة | السلوك عند النسخ | الاستعمال المعتاد |
|---|---|---|---|
| نسبي | B16 |
يتبع الصف والعمود | حساب صفاً بصف |
| مطلق | $B$12 |
لا شيء يتحرك | نسبة أو معطى وحيد |
| مختلط، العمود مثبَّت | $A27 |
يبقى العمود ويتبع الصف | القراءة دائماً من العمود A |
| مختلط، الصف مثبَّت | B$26 |
يبقى الصف ويتبع العمود | القراءة دائماً من الصف 26 |
أثناء الكتابة، يمرّ المفتاح F4 بالأشكال الأربعة. اقرأ علامة $ هكذا: «لا يتحرك»؛ فهي تثبّت ما يليها.
المعطى الوحيد
نسبة الضريبة أو الخصم أو سعر الصرف تُدخل مرة واحدة في خلية معنونة، ثم تُستدعى بمرجع مطلق: =C16*$B$12. يوم تتغير النسبة تعدّل خلية واحدة فيتحدث المصنف كله. أما كتابة =C16*0.2 فتفرض تصحيح كل صيغة، مع يقين بنسيان بعضها.
صيغة واحدة لشبكة كاملة
في سلّم يقاطع مبالغ المبيعات (العمود A) مع نسب العمولة (الصف 26)، تُكتب الصيغة =$A27*B$26 مرة واحدة ثم تُنسخ يميناً ويساراً ونحو الأسفل: $A تعيد دائماً إلى مبلغ الصف، و$26 إلى نسبة العمود.
المراجع المنظَّمة
في الجدول المنظَّم نشير إلى الأعمدة بأسمائها. Sales[المبلغ الصافي] هو العمود كله؛ و[@الكمية] هي قيمة الصف الحالي. هكذا يُحسب عمود المبلغ الصافي في جدول Sales: =ROUND([@الكمية]*[@[سعر الوحدة]]*(1-[@الخصم]),2). هذه المراجع لا تنزاح أبداً وتبقى صحيحة حين يكبر الجدول.
أولويات الحساب
يطبق Excel الترتيب الرياضي: الأقواس، ثم القوى، ثم الضرب والقسمة، ثم الجمع والطرح. الصيغة =B16*1-$B$13 لا تحسب خصماً؛ أما =B16*(1-$B$13) فتحسبه.
المستوى 2 · متوسط — الفصل 4
الدوال الشرطية: IF وSUMIFS وCOUNTIFS وAVERAGEIFS
التحليل هو الإجابة عن أسئلة تتضمن تقريباً دائماً «إذا» أو «حسب»: ما رقم المعاملات حسب الجهة؟ كم طلبية إذا كان الأداء متأخراً؟ يجيب Excel بفئتين من الدوال.
IF: اتخاذ القرار
=IF(الاختبار, القيمة_إن_صح, القيمة_إن_خطأ). يقارن الاختبار بين عنصرين بالعلامات = و<> (يختلف عن) و> و>= و< و<=. للحصول على ثلاث نتائج نُدخل IF ثانية مكان قيمة «الخطأ»:
=IF(E13>=$B$19,"الهدف محقق",IF(E13>=$B$19*0.9,"قريب","للمتابعة"))
يتوقف Excel عند أول اختبار صحيح، فترتيب الاختبارات مهم. منذ Excel 2019 تغني الدالة IFS عن التداخل؛ وتجمع AND وOR عدة شروط في اختبار واحد.
دوال …IFS: التجميع بشروط
| السؤال | الدالة | في Excel الفرنسي |
|---|---|---|
| مجموع وفق معايير | SUMIFS(نطاق_الجمع, نطاق1, معيار1, …) |
SOMME.SI.ENS |
| عدد الصفوف وفق معايير | COUNTIFS(نطاق1, معيار1, …) |
NB.SI.ENS |
| متوسط وفق معايير | AVERAGEIFS(نطاق_المتوسط, نطاق1, معيار1, …) |
MOYENNE.SI.ENS |
رقم معاملات الجهة المكتوبة في A13: =SUMIFS(Sales[المبلغ الصافي],Sales[الجهة],$A13). كل المعايير تجتمع بـ«و» منطقية: كل زوج نطاق-معيار يضيّق الصفوف المحتفَظ بها.
معايير المقارنة والتاريخ
معيار المقارنة يُكتب بين علامتي اقتباس والعلامة ملتصقة بالقيمة: ">=50000". وإذا كانت القيمة آتية من خلية أو دالة نضمّها بالعلامة &: ">="&DATE(2025,1,1). فالسنة الكاملة تحتاج إذن معيارين على عمود التواريخ نفسه:
=SUMIFS(Sales[المبلغ الصافي],Sales[الجهة],$A13,Sales[التاريخ],">="&DATE(2025,1,1),Sales[التاريخ],"<="&DATE(2025,12,31))
المستوى 2 · متوسط — الفصل 5
البحث عن معلومة: XLOOKUP وINDEX وMATCH
جدول Sales لا يحتوي إلا على رمز المنتج. أما تسميته وفئته وتكلفته فتوجد في جدول Products. الربط بينهما هو دور دوال البحث: انطلاقاً من مفتاح موجود في الجدولين، نقرأ معلومة من الجدول الآخر.
XLOOKUP، الدالة الحديثة
=XLOOKUP(القيمة_المبحوث_عنها, عمود_البحث, عمود_النتيجة, [إن_لم_توجد])
مثال: =XLOOKUP(A13,Products[الرمز],Products[التسمية],"رمز غير معروف"). يبحث Excel عن محتوى A13 في عمود الرمز ويعيد التسمية الموجودة في الصف نفسه. البحث مطابق تماماً افتراضياً، وعمود النتيجة قد يقع قبل المفتاح أو بعده. توجد XLOOKUP في Excel 2021 وMicrosoft 365.
INDEX + MATCH، الحل الصالح لكل النسخ
في كل نسخ Excel، تنجز دالتان العمل نفسه على مرحلتين:
MATCH(A13,Products[الرمز],0)تعيد موضع الرمز في العمود؛ والصفر الأخير يفرض المطابقة التامة.INDEX(Products[التسمية],الموضع)تعيد القيمة الموجودة في ذلك الموضع.
مجتمعتين: =INDEX(Products[التسمية],MATCH(A13,Products[الرمز],0)).
وماذا عن VLOOKUP؟
ما زالت VLOOKUP واسعة الانتشار، لكن لها ثلاث نقاط ضعف: لا تقرأ إلا في الأعمدة التي تلي المفتاح، وتحدد عمود النتيجة برقم يصير خاطئاً بمجرد إدراج عمود، ووسيطها الأخير إذا نُسي أطلق بحثاً تقريبياً. تعلّم قراءتها؛ وفي مصنفاتك الجديدة فضّل XLOOKUP أو INDEX/MATCH.
الرموز غير الموجودة
المفتاح الغائب يعيد #N/A. نعالجه بالوسيط الرابع في XLOOKUP أو، في كل النسخ، بالدالة IFERROR(الصيغة,"رمز غير معروف"). لا تضع 0: المنتج غير المعروف ليس ثمنه صفراً، بل ثمنه يحتاج إلى تحقق.
بحث بمدخلين
لقراءة هدف شهر وجهة في جدول Targets، تتلقى INDEX موضع صف وموضع عمود:
=INDEX(Targets[[الوسط]:[الشرق]],MATCH(B25,Targets[الشهر],0),MATCH(B26,Targets[[#Headers],[الوسط]:[الشرق]],0))
المستوى 2 · متوسط — الفصل 6
التواريخ والنصوص: حساب الآجال وتقطيع المراجع
التواريخ أرقام
ما دام التاريخ رقم يوم، فالفرق بين تاريخين عدد أيام: =C13-B13 تعطي أجل التسليم. إذا ظهرت النتيجة على شكل تاريخ فأعد الخلية إلى التنسيق «عام».
| الحاجة | الدالة | في Excel الفرنسي |
|---|---|---|
| السنة، الشهر، اليوم | YEAR(B13) وMONTH(B13) وDAY(B13) |
ANNEE، MOIS، JOUR |
| بناء تاريخ | DATE(2025,3,15) |
DATE(2025;3;15) |
| تاريخ اليوم | TODAY() |
AUJOURDHUI() |
| آخر يوم في الشهر بعد n شهراً | EOMONTH(B13,1) |
FIN.MOIS |
| أيام العمل بين تاريخين | NETWORKDAYS(B13,C13) |
NB.JOURS.OUVRES |
EOMONTH(B13,0) تعيد آخر يوم في شهر التاريخ نفسه؛ ومع 1 تعيد آخر يوم في الشهر الموالي: وهو أجل «نهاية الشهر الموالي». NETWORKDAYS تعدّ من الاثنين إلى الجمعة مع احتساب الطرفين، وتقبل في وسيطها الثالث نطاقاً لأيام العطل. إذا كان أسبوع العمل مختلفاً (الأحد إلى الخميس مثلاً) فاستعمل NETWORKDAYS.INTL.
تقطيع النص
مرجع مثل CMD-2025-00123-CAS يتضمن أربع معلومات. حين تكون البنية ثابتة تكفي ثلاث دوال:
LEFT(A13,3): الأحرف الثلاثة الأولى «CMD».RIGHT(A13,3): الأحرف الثلاثة الأخيرة «CAS».MID(A13,10,5): خمسة أحرف ابتداءً من الحرف العاشر «00123».
نتيجة التقطيع نص دائماً. VALUE(MID(A13,10,5)) تحوّله إلى رقم: 123. وتعطي LEN طول النص وFIND موضع حرف، وهما مفيدتان حين تتغير البنية.
التجميع
العلامة & تلصق العناصر: =I13&"-"&D13. ولجمع لائحة بفاصل، الدالة TEXTJOIN("-",TRUE,النطاق) أسرع.
المستوى 2 · متوسط — الفصل 7
الجداول المحورية: تلخيص آلاف الصفوف
يجيب الجدول المحوري (PivotTable) في ثوانٍ عن أسئلة تحتاج عشرات الصيغ: رقم المعاملات حسب الجهة والفئة، التطور الشهري، ترتيب المندوبين. وهو لا يغيّر البيانات الأصلية أبداً: إنه يلخّصها.
إنشاء الجدول المحوري
- انقر داخل جدول Sales.
- إدراج ‹ PivotTable ‹ ورقة عمل جديدة. المصدر المقترح هو
Sales: اسم الجدول لا نطاق ثابت. - في الجزء الجانبي، اسحب الحقول إلى المناطق الأربع.
| المنطقة | الدور | مثال |
|---|---|---|
| الصفوف (Rows) | ما نريد تفصيله | الجهة |
| الأعمدة (Columns) | محور القراءة الثاني | الفئة |
| القيم (Values) | ما نقيسه | مجموع المبلغ الصافي |
| عوامل التصفية (Filters) | ما نريد حصره | القناة، الأداء |
ضبط القيم
انقر بالزر الأيمن على رقم في الجدول ثم إعدادات حقل القيمة (Value Field Settings) لاختيار العملية (مجموع، عدد، متوسط) وتنسيق الرقم. تبويب إظهار القيم كـ يحوّل المبالغ إلى نسب مئوية من المجموع العام أو من مجموع الصف أو العمود دون أي صيغة. إذا اقترح Excel «العدد» بدل «المجموع» فالعمود الأصلي يحتوي على نص أو خلايا فارغة: صحّح المصدر.
تجميع التواريخ
ضع حقل التاريخ في الصفوف: يجمّع Excel حسب السنوات والفصول والأشهر. لتغيير التجميع: زر أيمن على تاريخ ‹ تجميع (Group). لا يمكن ذلك إلا إذا كانت كل خلايا العمود تواريخ حقيقية.
الفرز والتصفية ومقاطع العرض
زر أيمن على قيمة ‹ فرز ‹ من الأكبر إلى الأصغر يرتّب المندوبين. مقاطع العرض (Slicers، من تبويب «تحليل PivotTable ‹ إدراج مقطع عرض») أزرار تصفية مرئية؛ ويمكن لمقطع واحد أن يتحكم في عدة جداول محورية عبر «اتصالات التقرير».
المستوى 3 · متقدم — الفصل 8
الرسوم البيانية: إيصال رسالة
الرسم البياني ليس زينة: إنه جملة. قبل بنائه، اكتب الرسالة التي عليه أن يحملها («الفصل الرابع يتجاوز الهدف»)؛ ومنها ينتج نوع الرسم.
| السؤال المطروح | الرسم المناسب | يُتجنب |
|---|---|---|
| كيف تتطور قيمة عبر الزمن؟ | منحنى خطي | الرسم الدائري |
| أي فئة هي الأقوى؟ | أشرطة أو أعمدة مرتبة | المنحنى |
| ما الحصة التي يمثلها كل عنصر؟ | أشرطة؛ والدائري إلى حدود 3 أو 4 حصص فقط | دائري بعشر حصص، المؤثرات ثلاثية الأبعاد |
| هل بين مقياسين علاقة؟ | مخطط مبعثر | الأعمدة |
| أين نحن من الهدف؟ | أعمدة للمنجَز ومنحنى للهدف | رسمان منفصلان |
البناء
- حضّر جدول تلخيص صغيراً: عمود للتسميات وعمود أو عمودان للقيم. لا نرسم أبداً 2400 صف خام.
- حدده مع العناوين ثم «إدراج ‹ مخططات».
- رتّب الفئات من الأكبر إلى الأصغر، إلا إذا كان لها ترتيب طبيعي (الأشهر، الفئات العمرية).
التخفيف
- عوّض «عنوان المخطط» بالرسالة: «رقم المعاملات يتجاوز الهدف ابتداءً من شتنبر».
- احذف ما لا يحمل معلومة: الإطار، الخلفية، خطوط الشبكة الكثيفة، ووسيلة الإيضاح حين توجد سلسلة واحدة.
- اجعل محور الأشرطة أو الأعمدة يبدأ من الصفر: العمود المبتور يضخّم الفوارق.
- استعمل لوناً محايداً للمجموع ولوناً قوياً للعنصر الذي تتحدث عنه.
- اذكر الوحدة (درهم، آلاف الدراهم، %) في العنوان أو على المحور.
التنسيق الشرطي وخطوط المؤشر
داخل جدول، «الصفحة الرئيسية ‹ تنسيق شرطي ‹ أشرطة البيانات» يحوّل عمود أرقام إلى رسم مصغّر. وخطوط المؤشر (Sparklines، من «إدراج») تضع منحنى داخل خلية واحدة: مثالية لإظهار الاتجاه في كل صف من جدول.
المستوى 3 · متقدم — الفصل 9
Power Query: الاستيراد والتنظيف دون البدء من جديد
تنظيف ملف باليد يعني قبول إعادة العمل كل شهر. يسجّل Power Query كل حركة تنظيف على شكل خطوة. حين يصل ملف جديد، نقرة واحدة على «تحديث» تعيد تنفيذ كل الخطوات. الأداة مدمجة في Excel (تبويب «بيانات») وفي Power BI، ولا تغيّر الملف المصدر أبداً.
الاستيراد
- بيانات ‹ الحصول على البيانات ‹ من ملف ‹ من نص/CSV (Data › Get Data › From Text/CSV).
- اختر الملف. في المعاينة، تحقق من أصل الملف (65001: Unicode UTF-8) ومن المحدِّد.
- انقر على تحويل البيانات (Transform Data) لا على «تحميل»: يُفتح محرر Power Query.
التحويلات الأساسية
| العيب | الأمر في المحرر |
|---|---|
| العناوين في الصف الأول من البيانات | استخدام الصف الأول كرؤوس (Use First Row as Headers) |
| صفوف فارغة وصف «المجموع» في آخر الملف | تصفية عمود التاريخ: إلغاء تحديد (null) والقيم الفارغة |
| فراغات زائدة | تحويل ‹ تنسيق ‹ اقتطاع (Trim) |
| أحرف لاتينية غير منتظمة | تحويل ‹ تنسيق ‹ تكبير أول حرف من كل كلمة |
| الوحدة ملتصقة بالرقم («12450.50 درهم») | تحويل ‹ استبدال القيم: « درهم» بلا شيء |
| تواريخ وأرقام مقروءة كنص | أيقونة النوع ‹ استخدام الإعدادات المحلية (Using Locale) |
| صفوف مكررة | إزالة الصفوف ‹ إزالة التكرارات |
فخ الإعدادات المحلية
«03/04/2025» هو 3 أبريل في ملف مغربي أو فرنسي أو بريطاني، و4 مارس في ملف أمريكي؛ و«12450,50» رقم عشري في فرنسا ونص في غيرها. باختيار تغيير النوع ‹ استخدام الإعدادات المحلية تخبر Power Query بلغة الملف بصرف النظر عن إعدادات حاسوبك. إنها الطريقة الوحيدة للحصول على استعلام يعمل على كل الأجهزة.
الخطوات المطبَّقة ولغة M
الجزء الجانبي يسرد الخطوات المطبَّقة. يمكن النقر على كل خطوة لرؤية حالة البيانات عندها، وإعادة تسميتها وحذفها أو إدراج أخرى. وراء كل خطوة يكتب Power Query سطراً من لغة M يظهر في «المحرر المتقدم». لست مطالباً بكتابته، لكن معرفة قراءته تساعد على فهم الاستعلام وتصحيحه.
التحميل والتحديث
«إغلاق وتحميل» يضع النتيجة في جدول منظَّم. في الشهر الموالي، عوّض الملف المصدر بالجديد (الاسم نفسه والمجلد نفسه) ثم «بيانات ‹ تحديث الكل».
المستوى 3 · متقدم — الفصل 10
المصفوفات الديناميكية: UNIQUE وSORT وFILTER وLET
في Excel 2021 وMicrosoft 365، يمكن لصيغة واحدة أن تعيد عدة نتائج: «تنسكب» في الخلايا المجاورة. نكتب الصيغة في خلية واحدة ويتكيف نطاق النتائج مع حجم البيانات. إذا اعترضت خلية طريق النتائج عرض Excel الخطأ #SPILL!: يكفي إخلاء المكان.
| الدالة | الدور | مثال |
|---|---|---|
UNIQUE |
لائحة دون تكرار | =UNIQUE(Sales[الجهة]) |
SORT |
فرز نتيجة | =SORT(UNIQUE(Sales[الجهة])) |
FILTER |
الاحتفاظ بالصفوف التي تستوفي شرطاً | =FILTER(Sales[المبلغ الصافي],Sales[الجهة]=B12) |
SORTBY |
الفرز حسب عمود آخر | =SORTBY(Products[التسمية],Products[سعر الوحدة],-1) |
عدة شروط في FILTER
كل شرط ينتج سلسلة من TRUE وFALSE. نجمع بينها بالضرب للدلالة على «و»، وبالجمع للدلالة على «أو»:
=FILTER(Sales[المبلغ الصافي],(Sales[الجهة]=B12)*(Sales[المبلغ الصافي]>=B13),"لا مبيعات")
الوسيط الثالث هو الرسالة المعروضة حين لا يطابق أي صف. ولترتيب النتيجة من الأكبر إلى الأصغر نغلّفها: =SORT(FILTER(…),,-1). القيمة -1 تطلب الترتيب التنازلي؛ والوسيط المتروك فارغاً يعني الفرز على العمود الأول.
الإشارة إلى نطاق منسكب
إذا كانت الصيغة في A19 فإن A19# تشير إلى كل النطاق الذي تشغله مهما كان حجمه: =COUNTA(A19#) تعدّ النتائج. ويمكن لقائمة منسدلة في «التحقق من صحة البيانات» أن تشير إلى =$A$19# فتتحدث وحدها.
LET: تسمية مراحل الحساب
تمنح LET اسماً لنتيجة وسيطة ثم تستعمله في الحساب النهائي:
=LET(rev,SUMIFS(Sales[المبلغ الصافي],Sales[الجهة],B12),n,COUNTIFS(Sales[الجهة],B12),rev/n)
تُقرأ الصيغة كجملة: «ليكن rev رقم معاملات الجهة، وليكن n عدد أسطرها، أعد rev ÷ n». كل عنصر يُحسب مرة واحدة فقط، ما يجعل المصنف أسرع أيضاً.
المستوى 4 · خبير — الفصل 11
مؤشرات الأداء والتحليل ولوحة المتابعة: المشروع الختامي
مؤشر الأداء الرئيسي (KPI) رقم نتتبعه بانتظام لأنه يقود إلى قرار. والرقم الذي لا يستعمله أحد ليقرر ليس مؤشراً بل إحصائية.
بطاقة تعريف المؤشر
| العنصر | السؤال | مثال |
|---|---|---|
| التعريف | ماذا يقيس بالضبط؟ | حصة الأسطر المسلَّمة في 5 أيام أو أقل |
| الصيغة | كيف يُحسب؟ | الأسطر ذات الأجل ≤ 5 ÷ مجموع الأسطر |
| المصدر | من أين تأتي البيانات؟ | جدول Sales، عمودا التاريخ وتاريخ التسليم |
| الهدف | ما القيمة المراد بلوغها؟ | 85% |
| الدورية | متى يُقرأ؟ | كل شهر |
| المسؤول | من يتحرك إذا تراجع؟ | مسؤول اللوجستيك |
المؤشرات التجارية الأساسية
- رقم المعاملات والنمو: (رقم السنة − رقم السنة السابقة) ÷ رقم السنة السابقة.
- الهامش الإجمالي: رقم المعاملات − تكلفة المنتجات المبيعة؛ ونسبة الهامش: الهامش ÷ رقم المعاملات.
- متوسط المبلغ لكل سطر: رقم المعاملات ÷ عدد أسطر المبيعات.
- الزبناء النشطون: عدد الزبناء المختلفين الذين طلبوا خلال الفترة.
- نسبة الخدمة: التسليمات داخل الأجل ÷ مجموع التسليمات.
- نسبة تحقيق الهدف: رقم المعاملات ÷ الهدف.
النسبة المئوية تُحسب دائماً انطلاقاً من المجاميع، لا بحساب متوسط النسب: نسبة هامش الشركة هي الهامش الإجمالي مقسوماً على رقم المعاملات الإجمالي، لا متوسط نسب كل سطر.
ثلاث طبقات في المصنف
- البيانات: الجداول المنظَّمة، ولا تُعدَّل يدوياً أبداً.
- الحسابات: ورقة صيغ تقرأ البيانات والمحدِّدات.
- العرض: لوحة المتابعة، ولا تضم إلا روابط نحو ورقة الحسابات ورسوماً ومحدِّدات.
هذا الفصل يسمح بتغيير العرض دون لمس الحسابات، وبمراقبة الحسابات دون الضياع في التنسيق.
محدِّدات دون ماكرو
خلية بقائمة منسدلة (بيانات ‹ التحقق من صحة البيانات ‹ قائمة) تؤدي دور محدِّد السنة أو الجهة، والصيغ تشير إليها. لخيار «الكل» نحوّل الاختيار إلى معيار: =IF(B4="الكل","*",B4). في SUMIFS، المعيار "*" يقبل أي نص.
قواعد التركيب
- صفحة واحدة تُقرأ دون تمرير.
- الأرقام الرئيسية في الأعلى، الاتجاهات في الوسط، والتفاصيل في الأسفل.
- كل مؤشر مرفق بمرجع للمقارنة: الهدف أو الفترة السابقة.
- لونان رئيسيان هما نفسهما في كل مكان؛ والأحمر مخصص للتنبيه.
- تاريخ التحديث والمصدر مذكوران في الصفحة.
المشروع الختامي: لوحة متابعة المبيعات
- السياق: في الحالة الدراسية لأكاديمية إشراقة 7، تريد الإدارة التجارية لوحة متابعة تجارية من صفحة واحدة، تُحدَّث كل شهر ويستعملها شخص لا يعرف Excel.
- المنجَز 1: ورقة «الحسابات» تقرأ جداول Sales وProducts وTargets، ويتحكم فيها محدِّدان: السنة (2024 أو 2025) والجهة (جهة واحدة أو الكل).
- المنجَز 2: ورقة «لوحة المتابعة» بثمانية مؤشرات (رقم المعاملات، السنة السابقة، التطور، الهامش، نسبة الهامش، أسطر المبيعات، التسليم ≤ 5 أيام، حصة المتأخر أداؤه).
- المنجَز 3: ثلاثة رسوم: رقم المعاملات الشهري مقارناً بالسنة السابقة، رقم المعاملات حسب الفئة، وأفضل خمسة منتجات.
- القيود: لا قيمة مكتوبة يدوياً في الحسابات؛ لا ماكرو؛ صفحة واحدة دون تمرير؛ لونان رئيسيان.
- المراقبة: لسنة 2025 ولكل الجهات، يجب أن يظهر رقم المعاملات 6,708,660 درهم ونسبة الهامش 21.1%.
- التقييم: صحة الأرقام (40%)، الهيكلة في ثلاث طبقات (20%)، الوضوح والعناوين (25%)، تعليق من خمسة أسطر للإدارة (15%).
- الحل: مصنف «المشروع الختامي» في الحزمة يتضمن نموذجاً كاملاً؛ قارنه بعملك بعد الانتهاء فقط.
المستوى 4 · خبير — الفصل 12
مدخل إلى Power BI: من المصنف إلى التقرير التفاعلي
Power BI Desktop تطبيق مجاني من مايكروسوفت لبناء تقارير تفاعلية. ستجد فيه أداتين تعرفهما: Power Query لتحضير البيانات، ومنطق الجداول المحورية لتلخيصها. الجديد هو نموذج البيانات: عدة جداول مترابطة تستجوبها مقاييس.
| Excel | Power BI | |
|---|---|---|
| نقطة القوة | حساب حر، إدخال، محاكاة | تقارير تفاعلية، أحجام كبيرة، مشاركة |
| الحجم المريح | إلى بضع مئات الآلاف من الصفوف | عدة ملايين من الصفوف |
| ربط الجداول | صيغ البحث | علاقات داخل النموذج |
| الحسابات | صيغ داخل الخلايا | مقاييس DAX قابلة لإعادة الاستعمال |
الأداتان متكاملتان: Excel للاستكشاف والمحاكاة، وPower BI لنشر تقرير يتحدث تلقائياً.
طرق العرض الثلاث في Power BI Desktop
- التقرير (Report): الصفحة التي توضع فيها المرئيات.
- الجدول (Table): محتوى الجداول المحمَّلة.
- النموذج (Model): الجداول والعلاقات التي تربطها.
النموذج النجمي
في الوسط جدول الوقائع: Sales، صف لكل عملية بيع فيه مبالغ ورموز. وحوله جداول الأبعاد: Products وCustomers والتقويم، صف لكل عنصر موصوف. كل علاقة تربط مفتاحاً فريداً في البعد (Products[الرمز]) بالعمود المقابل في الوقائع (Sales[المنتج]): علاقة «واحد إلى متعدد». نصفّي بالأبعاد ونقيس في الوقائع. تصير عمليات البحث في الفصل 5 غير ضرورية: العلاقة تتكفل بالربط.
جدول التقويم
الحسابات الزمنية تتطلب جدول تواريخ متصلاً. من «النمذجة ‹ جدول جديد»: Dates = CALENDAR(DATE(2024,1,1), DATE(2025,12,31)). اربط Dates[Date] بـSales[التاريخ] ثم علّمه كجدول تواريخ.
أولى مقاييس DAX
المقياس صيغة لها اسم تُحسب حسب سياق المرئية: المقياس نفسه يعطي رقم المعاملات الإجمالي في بطاقة، ورقم المعاملات حسب الجهة في رسم، ورقم الشهر في منحنى. تُكتب DAX بأسماء الدوال الإنجليزية وبالفواصل.
Margin = SUM(Sales[الهامش])
Margin rate = DIVIDE([Margin], [Revenue])
Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR(Dates[Date]))
Change = DIVIDE([Revenue] – [Revenue PY], [Revenue PY])
DIVIDE تتفادى خطأ القسمة على صفر. وCALCULATE تعيد حساب المقياس بعد تغيير التصفية: هنا الفترة نفسها قبل سنة. إنها أهم دالة في DAX.
بناء الصفحة
انقر على نوع مرئية في جزء «المرئيات» ثم اسحب الحقول: بطاقة لكل مؤشر، منحنى محوره Dates[Date]، مخطط أشرطة حسب الفئة، ومقطع عرض للجهة. النقر على شريط يصفّي الصفحة كلها: هذه هي التصفية المتقاطعة. وقواعد الوضوح في الفصلين 8 و11 تنطبق كما هي.
