شرح دورة اكسيل 2021 من الصفر إلى الاحتراف Excel 2019 الوحدة الرابعة
الوحدة 4: الدوال المتقدمة والمنطقية (المستوى المتوسط/المتقدم)
الدرس 4.1: الدالة المنطقية (IF)
الهدف: جعل Excel يتخذ قرارات بناءً على شرط معين.
الشرح:
البناء
(Syntax): `=IF(الشرط, القيمة_إذا_كان_الشرط_صحيحاً,
القيمة_إذا_كان_الشرط_خاطئاً)`
كيف تعمل: تقوم الدالة بتقييم
"الشرط". إذا كان الشرط TRUE
(صحيح)، تقوم بإرجاع القيمة الأولى. إذا كان FALSE
(خاطئ)، تقوم بإرجاع القيمة الثانية.
مشغلات المقارنة: `>` (أكبر من)، `<` (أقل من)، `>=` (أكبر من أو يساوي)، `<=` (أقل من أو يساوي)، `=` (يساوي)، `<>` (لا يساوي).
أمثلة:
`=IF(A1 >= 50,
"ناجح", "راسب")` ⮕ إذا كانت درجة A1
أكبر من أو تساوي 50، اطبع "ناجح"، وإلا اطبع "راسب".
`=IF(B2 = "نعم", 10, 0)` ⮕ إذا كانت الخلية B2 تحتوي على كلمة "نعم"، أعطِ قيمة 10، وإلا أعطِ 0.
مهمة تطبيقية: في عمود "المجموع" من التمرين السابق، أضف عمودًا جديدًا叫做 "الحالة". اكتب دالة `IF` تظهر "ممتاز" إذا كان المجموع أكبر من 5500، وإلا تظهر "جيد".
الدرس 4.2: الدوال (AND, OR) داخل الدالة IF
الهدف: اختبار شرطين أو أكثر داخل دالة IF واحدة.
الشرح:
AND(شرط1,
شرط2, ...)`: تُرجع TRUE فقط إذا كان جميع الشروط صحيحة. Think of it as "و".
OR(شرط1, شرط2, ...)`: تُرجع TRUE إذا كان أي من الشروط صحيحًا. Think of it as "أو".
مثال مع IF:
لنفترض أن شرط
الحصول على مكافأة هو أن يكون الراتب أكبر من 5000 و العمولة أكبر من 400.
`=IF(AND(B2>5000, C2>400), "مكافأة", "لا يوجد مكافأة")`
مثال آخر:
مكافأة إذا كان الراتب > 5000 أو المجموع > 6000.
`=IF(OR(B2>5000, D2>6000), "مكافأة", "لا يوجد
مكافأة")`
مهمة تطبيقية: في جدول الرواتب، أضف عمود "قرار". امنح "مكافأة" فقط للموظفين whose راتب > 5000 و عمولة > 400. استخدم دالة `AND` داخل `IF`.
الدرس 4.3: الدالة (IFERROR)
الشرح:
البناء: `=IFERROR(الصيغة_التي_قد_تعطي_خطأ,
القيمة_التي_ستعرض_في_حالة_الخطأ)`
مثال شائع مع VLOOKUP: إذا لم يعثر `VLOOKUP` على قيمة، يظهر خطأ `#N/A`.我们可以
تحسينه:
`=IFERROR(VLOOKUP(...), "لم يتم العثور على القيمة")`
مهمة تطبيقية: جرب كتابة صيغة تقسيم على صفر، like `=10/0`. ستظهر `#DIV/0!`. الآن غلّفها بـ`IFERROR`: `=IFERROR(10/0, "خطأ في الحساب")`.
الدرس 4.4: دوال البحث (VLOOKUP)
الهدف: البحث عن قيمة في جدول وإرجاع قيمة مقابلة لها من عمود آخر. أيقونة Excel.
الشرح:
البناء: `=VLOOKUP(القيمة_التي_تبحث_عنها, نطاق_الجدول_الكامل,
رقم_عمود_القيمة_المطلوبة, FALSE)`
القيمة التي تبحث عنها: What you're looking for (e.g., an
Employee ID or a Name).
نطاق الجدول الكامل: The entire table where the data lives,
including the column you're searching in and the columns you want to pull data
from.
رقم عمود القيمة المطلوبة: The column number within the table
range that contains the answer you want.
FALSE: Means "find an exact match". Always use FALSE.
مثال عملي :
لدى جدولين:
جدول الموظفين (Employees):
جدول المبيعات (Sales):
نريد إضافة
اسم الموظف إلى جدول المبيعات.
1. أضف عمود "الاسم" في جدول المبيعات.
2. في أول خلية تحت "الاسم"، اكتب:
`=VLOOKUP(A2, Employees!A:B, 2, FALSE)`
`A2`:
هو الـ ID في جدول المبيعات الذي نبحث عنه.
`Employees!A:B`:
هو نطاق الجدول الكامل في ورقة "Employees"
(من العمود A إلى B).
`2`: لأن عمود "الاسم" هو
العمود الثاني في نطاق الجدول المحدد (A:B).
`FALSE`: نريد مطابقة تامة للـ ID.
مهمة تطبيقية: انشئ الجدولين أعلاه في ورقتين different. ثم طبق `VLOOKUP` لاستخراج الأسماء إلى جدول المبيعات.
الدرس 4.5: دالة (XLOOKUP) - بديل أقوى وأفضل
الهدف: استخدام دالة Microsoft الحديثة التي تحل تقريبًا جميع مشاكل `VLOOKUP` وتتفوق عليها.
مميزاتها:
1. لا تحتاج لعد الأعمدة.
2. يمكنها البحث من اليسار إلى اليمين ومن اليمين
إلى اليسار.
3. بناء جملة أبسط وأقوى.
4. لها وسيط مضمّن للتعامل مع الأخطاء.
البناء:
=XLOOKUP(القيمة_المطلوبة, نطاق_البحث, نطاق_النتيجة, [القيمة_إذا_لم_توجد], [مود_المطابقة])
نفس مثال VLOOKUP بالأعلى بـ XLOOKUP:
`A2`:
القيمة المطلوبة (ID).
`Employees!A:A`:
نطاق البحث (ابحث في عمود ID).
`Employees!B:B`:
نطاق النتيجة (أرجع القيمة من عمود الاسم).
`"غير موجود"`: (اختياري) القيمة التي ستظهر إذا لم يتم العثور على الـ ID.
=XLOOKUP(A2, Employees!A:A, Employees!B:B, "غير موجود")
مهمة تطبيقية: كرر المهمة السابقة، ولكن استخدم `XLOOKUP` instead of `VLOOKUP`. لاحظ البساطة والقوة.
,والسؤال الان هل تريد أن
نستمر في شرح `INDEX` و `MATCH`
والتمرين العملي للوحدة؟
نعم بالتأكيد! الان لنغوص أعمق قليلاً. هذا هو المستوى الذي يصنع الفارق الحقيقي.
الدرس 4.6: الدالتان (INDEX) و (MATCH) - بديل أقوى لـ VLOOKUP
الهدف: فهم
مزيج `INDEX` و `MATCH`،
الذي يعتبره الكثيرون أقوى وأكثر مرونة من `VLOOKUP` وحتى `XLOOKUP` في بعض السيناريوهات المعقدة.
الشرح
المنطقي:
فكر فيها كطلب pizza: `MATCH` تعطيك رقم الصف للبيبروني في قائمة البيتزا
(تجد موقع الشيء). `INDEX` تذهب إلى رقم الصف الذي قلته لها في قائمة
البيتزا وتعطيك اسم البيتزا الموجودة في ذلك الصف (تسترجع القيمة من موقع معين).
1. دالة `MATCH(القيمة,
النطاق, 0)`:
ماذا تفعل: تبحث عن `القيمة` داخل
`النطاق` (عمود أو صف واحد) وترجع رقم موقعها (مثل، هي الصف الثالث في النطاق).
0`: تعني "مطابقة تامة".
استخدمها دائمًا.
2. دالة `INDEX(النطاق,
رقم_الصف, [رقم_العمود])`:
ماذا تفعل: تذهب إلى `النطاق` الكبير، وتذهب إلى `رقم_الصف` و`رقم_العمود` الذي تحدده، وترجع القيمة الموجودة في ذلك التقاطع.
3. دمجهما معًا:
الصيغة السحرية: `=INDEX(نطاق_النتائج,
MATCH(القيمة_المطلوبة, نطاق_البحث, 0))`
كيف تعمل:
1.
`MATCH` تبحث عن `القيمة_المطلوبة` في `نطاق_البحث`
وتعطينا `رقم_الصف`.
2.
هذا `رقم_الصف` يذهب مباشرة إلى دالة `INDEX`.
3.
`INDEX` تذهب إلى ذلك `رقم_الصف` داخل
`نطاق_النتائج` وترجع لنا القيمة.
مثال:
نفس مثال
الجدولين من الدرس السابق. نريد اسم الموظف ذو الـ ID `2`.
=INDEX(Employees!B:B, MATCH(2, Employees!A:A, 0))
MATCH(2, Employees!A:A, 0):
تبحث عن الرقم `2` في العمود `A` (أي بين 1, 2, 3).
تجده في الصف الثاني (لأن `1` في الصف 2،
`2` في الصف 3، `3` في الصف 4 - تذكر أن الصف الأول هو العنوان!). لذلك ترجع الرقم
`2`.
2. `INDEX(Employees!B:B, 2)`:
تذهب إلى العمود `B`
(أسماء الموظفين).
تذهب إلى الصف الثاني فيه.
ترجع القيمة الموجودة، وهي "أحمد".
لماذا هذه
الطريقة رائعة؟
مرونة مطلقة: يمكنك البحث في أي عمود وإرجاع
القيمة من أي عمود آخر، بغض النظر عن موقعه (يسار أو يمين).
أكثر كفاءة في الملفات العملاقة جدًا.
مهمة تطبيقية: في جدول المبيعات، استخدم `INDEX` و `MATCH` لاستخراج اسم الموظف بدلاً من `VLOOKUP`. لاحظ أنك لست مضطرًا حتى لتحديد النطاق الكامل، فقط حدد العمودين المنفصلين.
الدرس 4.7: التمرين العملي للوحدة (لوحة تحكم
بسيطة)
الهدف: بناء لوحة تحكم بسيطة where يمكنك كتابة اسم وتظهر لك تفاصيله تلقائيًا.
المهمة:
1. أنشئ قاعدة بيانات صغيرة في نطاق `A1:D4`:
2. في مكان آخر (مثل `A10`),
اكتب "ابحث عن موظف:".
3. في الخلية `B10`,
أنشئ قائمة منسدلة (Data
Validation > Allow: List > Source: `=$A$2:$A$4`).
4. في `A11`
اكتب "القسم:"، وفي `A12`
اكتب "الراتب:"، وفي `A13`
اكتب "المبيعات:".
5. بجانب كل عنوان (`B11, B12, B13`), استخدم دالة البحث التي تفضل (`VLOOKUP`, `XLOOKUP`, أو `INDEX+MATCH`) للبحث عن الاسم المحدد في `B10` وإرجاع التفاصيل الخاصة به.
مثال بـ `XLOOKUP` في `B11`:
- =XLOOKUP($B$10, $A$2:$A$4,
$B$2:$B$4, "غير موجود")
استخدم `$` لتثبيت المرجع عندما تسحب الصيغة لأسفل.
النتيجة: عندما تختار اسمًا مختلفًا من القائمة المنسدلة في `B10`, يجب أن تتغير جميع التفاصيل في `B11:B13` تلقائيًا على الفور.
تهانينا! لقد
أتقنت بعضًا من أقوى أدوات Excel على الإطلاق. هذه المهارات هي قلب التحليل
الاحترافي للبيانات.
الوحدة
القادمة ستكون مثيرة جدًا للاهتمام: الوحدة 5: تحليل البيانات والتقارير (المخططات
البيانية، الجداول المحورية Pivot
Tables).
هل أنت مستعد لرؤية كيف يحول Excel البيانات إلى رؤى مرئية؟
انتقل الى الوحدة الخامسة من هنا