مجلة المهارات الرقمية والذكاء الاصطناعي تعلّم من البداية حتى الاحتراف
تواصل معنا EDITION 2026
ISHRAQA7 — إشراقة المعرفة الرقمية
٨ مجالات · مرّر لاستكشاف الجميع
أكاديمية إشراقة 7 · Excel والبيانات

Excel وتحليل البيانات: من المبتدئ إلى الخبير

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

  • المستوىمبتدئ ← خبير
  • برنامج الدورة12 وحدات
  • ملفات الدورةPDF · XLSX · CSV · ZIP

متوفر أيضاً بـ Français English

Excel هو أكثر أدوات التحليل انتشاراً في العالم، ومع ذلك لا يستغل أغلب مستعمليه إلا جزءاً صغيراً منه. تقودك هذه الدورة، في اثني عشر فصلاً وأربعة مستويات، من صيغتك الأولى إلى لوحة متابعة احترافية ثم إلى خطواتك الأولى في Power BI. تشتغل من البداية إلى النهاية على مجموعة البيانات نفسها: مبيعات الحالة الدراسية لأكاديمية إشراقة 7 (بيانات تدريبية)، أي 2400 صف على مدى سنتين.

الفئة المستهدفة المستويات المدة المقترحة البرامج
الطلبة، الأساتذة، المهندسون، الأجراء، المستقلون، روّاد الأعمال من المبتدئ إلى الخبير، 12 فصلاً من 30 إلى 40 ساعة، بمعدل فصل في كل حصة Excel 2016 أو أحدث؛ Excel 2021 أو Microsoft 365 للفصلين 5 (XLOOKUP) و10؛ Power BI Desktop (مجاني، على Windows) للفصل 12

كيف تتابع الدورة

  1. نزّل مصنف التمارين ومصنف الحلول ودليل PDF بلغتك.
  2. اقرأ فصلاً ثم أنجز تمرينه في الورقة المقابلة (من Ex01 إلى Ex11): الخلايا الصفراء عليك تعبئتها.
  3. قارن بمصنف الحلول: كل خلية خضراء تتضمن الصيغة، وكل ورقة تشرح الطريقة.
  4. أجب عن أسئلة التقييم الثلاثة قبل الانتقال إلى الفصل الموالي.
  5. اختم بالمشروع الختامي: لوحة متابعة المبيعات في الفصل 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، ولا تكتبه بالحروف في خلية مخصصة للحساب.

صيغتك الأولى

  1. انقر على الخلية التي ينبغي أن تعرض النتيجة.
  2. اكتب = ثم انقر على خلية الكمية، واكتب * ثم انقر على خلية السعر: تحصل مثلاً على =B13*C13.
  3. اضغط Enter. تعرض الخلية النتيجة، ويحتفظ شريط الصيغة بالعملية.
  4. انسخ الصيغة نحو الأسفل بالنقر المزدوج على المربع الصغير في زاوية الخلية (مقبض التعبئة). يكيّف 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) في ثوانٍ عن أسئلة تحتاج عشرات الصيغ: رقم المعاملات حسب الجهة والفئة، التطور الشهري، ترتيب المندوبين. وهو لا يغيّر البيانات الأصلية أبداً: إنه يلخّصها.

إنشاء الجدول المحوري

  1. انقر داخل جدول Sales.
  2. إدراج ‹ PivotTable ‹ ورقة عمل جديدة. المصدر المقترح هو Sales: اسم الجدول لا نطاق ثابت.
  3. في الجزء الجانبي، اسحب الحقول إلى المناطق الأربع.
المنطقة الدور مثال
الصفوف (Rows) ما نريد تفصيله الجهة
الأعمدة (Columns) محور القراءة الثاني الفئة
القيم (Values) ما نقيسه مجموع المبلغ الصافي
عوامل التصفية (Filters) ما نريد حصره القناة، الأداء

ضبط القيم

انقر بالزر الأيمن على رقم في الجدول ثم إعدادات حقل القيمة (Value Field Settings) لاختيار العملية (مجموع، عدد، متوسط) وتنسيق الرقم. تبويب إظهار القيم كـ يحوّل المبالغ إلى نسب مئوية من المجموع العام أو من مجموع الصف أو العمود دون أي صيغة. إذا اقترح Excel «العدد» بدل «المجموع» فالعمود الأصلي يحتوي على نص أو خلايا فارغة: صحّح المصدر.

تجميع التواريخ

ضع حقل التاريخ في الصفوف: يجمّع Excel حسب السنوات والفصول والأشهر. لتغيير التجميع: زر أيمن على تاريخ ‹ تجميع (Group). لا يمكن ذلك إلا إذا كانت كل خلايا العمود تواريخ حقيقية.

الفرز والتصفية ومقاطع العرض

زر أيمن على قيمة ‹ فرز ‹ من الأكبر إلى الأصغر يرتّب المندوبين. مقاطع العرض (Slicers، من تبويب «تحليل PivotTable ‹ إدراج مقطع عرض») أزرار تصفية مرئية؛ ويمكن لمقطع واحد أن يتحكم في عدة جداول محورية عبر «اتصالات التقرير».

المستوى 3 · متقدم — الفصل 8

الرسوم البيانية: إيصال رسالة

الرسم البياني ليس زينة: إنه جملة. قبل بنائه، اكتب الرسالة التي عليه أن يحملها («الفصل الرابع يتجاوز الهدف»)؛ ومنها ينتج نوع الرسم.

السؤال المطروح الرسم المناسب يُتجنب
كيف تتطور قيمة عبر الزمن؟ منحنى خطي الرسم الدائري
أي فئة هي الأقوى؟ أشرطة أو أعمدة مرتبة المنحنى
ما الحصة التي يمثلها كل عنصر؟ أشرطة؛ والدائري إلى حدود 3 أو 4 حصص فقط دائري بعشر حصص، المؤثرات ثلاثية الأبعاد
هل بين مقياسين علاقة؟ مخطط مبعثر الأعمدة
أين نحن من الهدف؟ أعمدة للمنجَز ومنحنى للهدف رسمان منفصلان

البناء

  1. حضّر جدول تلخيص صغيراً: عمود للتسميات وعمود أو عمودان للقيم. لا نرسم أبداً 2400 صف خام.
  2. حدده مع العناوين ثم «إدراج ‹ مخططات».
  3. رتّب الفئات من الأكبر إلى الأصغر، إلا إذا كان لها ترتيب طبيعي (الأشهر، الفئات العمرية).

التخفيف

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

التنسيق الشرطي وخطوط المؤشر

داخل جدول، «الصفحة الرئيسية ‹ تنسيق شرطي ‹ أشرطة البيانات» يحوّل عمود أرقام إلى رسم مصغّر. وخطوط المؤشر (Sparklines، من «إدراج») تضع منحنى داخل خلية واحدة: مثالية لإظهار الاتجاه في كل صف من جدول.

المستوى 3 · متقدم — الفصل 9

Power Query: الاستيراد والتنظيف دون البدء من جديد

تنظيف ملف باليد يعني قبول إعادة العمل كل شهر. يسجّل Power Query كل حركة تنظيف على شكل خطوة. حين يصل ملف جديد، نقرة واحدة على «تحديث» تعيد تنفيذ كل الخطوات. الأداة مدمجة في Excel (تبويب «بيانات») وفي Power BI، ولا تغيّر الملف المصدر أبداً.

الاستيراد

  1. بيانات ‹ الحصول على البيانات ‹ من ملف ‹ من نص/CSV (Data › Get Data › From Text/CSV).
  2. اختر الملف. في المعاينة، تحقق من أصل الملف (65001: Unicode UTF-8) ومن المحدِّد.
  3. انقر على تحويل البيانات (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%
الدورية متى يُقرأ؟ كل شهر
المسؤول من يتحرك إذا تراجع؟ مسؤول اللوجستيك

المؤشرات التجارية الأساسية

  • رقم المعاملات والنمو: (رقم السنة − رقم السنة السابقة) ÷ رقم السنة السابقة.
  • الهامش الإجمالي: رقم المعاملات − تكلفة المنتجات المبيعة؛ ونسبة الهامش: الهامش ÷ رقم المعاملات.
  • متوسط المبلغ لكل سطر: رقم المعاملات ÷ عدد أسطر المبيعات.
  • الزبناء النشطون: عدد الزبناء المختلفين الذين طلبوا خلال الفترة.
  • نسبة الخدمة: التسليمات داخل الأجل ÷ مجموع التسليمات.
  • نسبة تحقيق الهدف: رقم المعاملات ÷ الهدف.

النسبة المئوية تُحسب دائماً انطلاقاً من المجاميع، لا بحساب متوسط النسب: نسبة هامش الشركة هي الهامش الإجمالي مقسوماً على رقم المعاملات الإجمالي، لا متوسط نسب كل سطر.

ثلاث طبقات في المصنف

  1. البيانات: الجداول المنظَّمة، ولا تُعدَّل يدوياً أبداً.
  2. الحسابات: ورقة صيغ تقرأ البيانات والمحدِّدات.
  3. العرض: لوحة المتابعة، ولا تضم إلا روابط نحو ورقة الحسابات ورسوماً ومحدِّدات.

هذا الفصل يسمح بتغيير العرض دون لمس الحسابات، وبمراقبة الحسابات دون الضياع في التنسيق.

محدِّدات دون ماكرو

خلية بقائمة منسدلة (بيانات ‹ التحقق من صحة البيانات ‹ قائمة) تؤدي دور محدِّد السنة أو الجهة، والصيغ تشير إليها. لخيار «الكل» نحوّل الاختيار إلى معيار: =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 بأسماء الدوال الإنجليزية وبالفواصل.

Revenue = SUM(Sales[المبلغ الصافي])
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 تنطبق كما هي.

اختبر نفسك

1. التعرّف على Excel: المصنف والخلايا وأولى الحسابات
1. متى يحاذي Excel المحتوى تلقائياً كما يحاذي النصوص؟
2. ماذا تصير الصيغة =B13*C13 عند نسخها صفاً واحداً إلى الأسفل؟
3. أي دالة تعدّ الخلايا غير الفارغة في نطاق يحتوي على نصوص؟
2. بيانات نظيفة وجداول منظَّمة
1. أي ممارسة تجعل الجدول غير صالح للجدول المحوري؟
2. ماذا تفعل =TRIM(" atlas conseil ")؟
3. ما الفائدة الرئيسية للجدول المنظَّم؟
3. الصيغ والمراجع: النسبية والمطلقة والمختلطة والمنظَّمة
1. ماذا تعني علامة $ في $B$12؟
2. أي صيغة تُكتب في B27 وتُنسخ على الشبكة لتضرب مبلغ العمود A في نسبة الصف 26؟
3. كم تساوي =100*1-0.2؟
4. الدوال الشرطية: IF وSUMIFS وCOUNTIFS وAVERAGEIFS
1. في =SUMIFS(Sales[المبلغ الصافي],Sales[الجهة],"الشمال",Sales[القناة],"مباشر")، أي الصفوف تُجمع؟
2. كيف يُكتب المعيار «أكبر من أو يساوي قيمة الخلية B19»؟
3. في IF متداخلة، ماذا يحدث حين يكون الاختبار الأول صحيحاً؟
5. البحث عن معلومة: XLOOKUP وINDEX وMATCH
1. ماذا تعيد MATCH("P003",Products[الرمز],0)؟
2. لماذا تُعدّ VLOOKUP هشة؟
3. رمز منتج غير موجود. ما أفضل ممارسة؟
6. التواريخ والنصوص: حساب الآجال وتقطيع المراجع
1. B13 فيها 10/03/2025 وC13 فيها 14/03/2025 (يوم/شهر/سنة). ماذا تعيد =C13-B13؟
2. ماذا تعيد =EOMONTH(DATE(2025,1,20),1)؟
3. ماذا تحتوي خلية محسوبة بالصيغة =MID("CMD-2025-00123-CAS",10,5)؟
7. الجداول المحورية: تلخيص آلاف الصفوف
1. في أي منطقة يوضع حقل المبلغ الصافي للحصول على رقم المعاملات؟
2. يعرض الجدول المحوري «عدد المبلغ» بدل «مجموع المبلغ». ماذا نستنتج؟
3. أضفت 50 عملية بيع إلى الجدول الأصلي. ماذا يجب فعله ليأخذها الجدول المحوري في الحسبان؟
8. الرسوم البيانية: إيصال رسالة
1. أي رسم يُظهر أفضل تطور رقم المعاملات على اثني عشر شهراً؟
2. لماذا يجب أن يبدأ محور مخطط الأعمدة من الصفر؟
3. ما أفضل عنوان لرسم بياني؟
9. Power Query: الاستيراد والتنظيف دون البدء من جديد
1. ماذا يفعل Power Query بالملف المصدر؟
2. لماذا نضبط نوع عمود التواريخ «بالإعدادات المحلية»؟
3. وصل ملف الشهر الموالي بالبنية نفسها. ماذا تفعل؟
10. المصفوفات الديناميكية: UNIQUE وSORT وFILTER وLET
1. ماذا يعني الخطأ #SPILL!؟
2. في FILTER، كيف نفرض شرطين معاً؟
3. ما فائدة LET؟
11. مؤشرات الأداء والتحليل ولوحة المتابعة: المشروع الختامي
1. ما الذي يميز مؤشر الأداء عن مجرد إحصائية؟
2. كيف تُحسب نسبة الهامش الإجمالية بطريقة صحيحة؟
3. لماذا نفصل بين البيانات والحسابات والعرض؟
12. مدخل إلى Power BI: من المصنف إلى التقرير التفاعلي
1. في النموذج النجمي، ماذا يتضمن جدول الوقائع؟
2. أي دالة في DAX تغيّر سياق التصفية لحساب ما؟
3. لحساب نسبة الهامش في Power BI، ماذا ينبغي إنشاؤه؟

معجم الصفحة

المصطلحات الأساسية الواردة في هذا المحتوى، مع تعريف مختصر لكل منها.

لوحة المتابعة
صفحة واحدة تجمع المؤشرات والرسوم الأساسية لاتخاذ القرار بسرعة.
Power BI
منصة مايكروسوفت لتحليل البيانات وبناء تقارير تفاعلية ولوحات متابعة قابلة للمشاركة.
Power Query
أداة في Excel وPower BI لاستيراد البيانات وتنظيفها وتحويلها بخطوات مسجّلة يعاد تنفيذها عند كل تحديث.
الأتمتة
جعل مهمة متكررة تُنفَّذ تلقائياً وفق قاعدة: عند حدوث شيء، نفّذ شيئاً آخر.
مؤشر الأداء
رقم يُتابَع بانتظام لقياس التقدّم نحو هدف محدد، مثل هامش الربح أو نسبة التأخر.
DAX
لغة الصيغ في Power BI ونموذج بيانات Excel، تُستعمل لكتابة المقاييس مثل رقم المعاملات أو نسبة النمو.
XLOOKUP
دالة في Excel تبحث عن قيمة في عمود وتعيد القيمة المقابلة من عمود آخر؛ بديل حديث لـVLOOKUP.
الجدول المحوري
أداة في Excel تلخّص آلاف الصفوف في ثوانٍ: مجاميع، متوسطات وعدّ حسب الفئات التي تختارها.
CSV
ملف نصي بسيط تُفصل فيه القيم بفواصل؛ الصيغة الأكثر شيوعاً لتبادل البيانات بين البرامج.

ملفات الدورة

اختر اللغة ثم الصيغة التي تناسبك.

العربية

الملفالصيغةالحجمتحميل
الدليل الكامل للدورة PDF 1.8 MB تحميل الملف — الدليل الكامل للدورة (PDF)
مصنف التمارين XLSX 287 KB تحميل الملف — مصنف التمارين (XLSX)
مصنف الحلول XLSX 302 KB تحميل الملف — مصنف الحلول (XLSX)
المشروع الختامي: لوحة المتابعة (نموذج الحل) XLSX 277 KB تحميل الملف — المشروع الختامي: لوحة المتابعة (نموذج الحل) (XLSX)
ملف خام لـPower Query CSV 9 KB تحميل الملف — ملف خام لـPower Query (CSV)
بيانات ومقاييس Power BI ZIP 58 KB تحميل الملف — بيانات ومقاييس Power BI (ZIP)

Français

الملفالصيغةالحجمتحميل
Guide complet de la formation PDF 1.8 MB تحميل الملف — Guide complet de la formation (PDF)
Classeur d’exercices XLSX 254 KB تحميل الملف — Classeur d’exercices (XLSX)
Classeur des corrigés XLSX 267 KB تحميل الملف — Classeur des corrigés (XLSX)
Projet final : tableau de bord (modèle corrigé) XLSX 244 KB تحميل الملف — Projet final : tableau de bord (modèle corrigé) (XLSX)
Fichier brut pour Power Query CSV 7 KB تحميل الملف — Fichier brut pour Power Query (CSV)
Données et mesures pour Power BI ZIP 54 KB تحميل الملف — Données et mesures pour Power BI (ZIP)

English

الملفالصيغةالحجمتحميل
Complete course guide PDF 1.7 MB تحميل الملف — Complete course guide (PDF)
Exercise workbook XLSX 251 KB تحميل الملف — Exercise workbook (XLSX)
Answer workbook XLSX 264 KB تحميل الملف — Answer workbook (XLSX)
Final project: dashboard (model answer) XLSX 243 KB تحميل الملف — Final project: dashboard (model answer) (XLSX)
Raw file for Power Query CSV 8 KB تحميل الملف — Raw file for Power Query (CSV)
Data and measures for Power BI ZIP 54 KB تحميل الملف — Data and measures for Power BI (ZIP)