شرح دورة اكسيل 2021 من الصفر إلى الاحتراف Excel 2019 الوحدة الثالثة
الوحدة 3: الصيغ والدوال الأساسية (المستوى
المتوسط)
الدرس 3.1: مفهوم الصيغ والمراجع (أهم درس في Excel!)
الهدف: فهم
حجر الأساس الذي تُبنى عليه جميع حسابات Excel.
الشرح:
1. ما هي الصيغة (Formula)? هي معادلة تقوم بإجراء عمليات حسابية أو
منطقية على البيانات في الخلايا.
علامة المساواة (=): كل صيغة must must تبدأ بعلامة المساواة `=`. هذا هو ما يخبر Excel أنك تدخل صيغة وليس نصًا عاديًا.
مثال بسيط: `=5+3` سيعطي الناتج `8`.
2. المراجع (Cell References): instead of كتابة الأرقام directly, we نستخدم عناوين الخلايا.
مثال: إذا كان الرقم `5` في الخلية `A1` والرقم `3` في الخلية `B1`، يمكنك كتابة `=A1+B1`
في الخلية `C1`. الناتج سيكون `8`.
الميزة الكبرى: إذا غيرت قيمة `A1` إلى `10`، فإن نتيجة `C1` ستتغير تلقائيًا إلى `13`.
3. المراجع النسبية والمطلقة (Relative vs. Absolute Reference):
مرجع نسبي (مثل: A1):
هو المرجع الافتراضي. عندما تسحب صيغة تحتوي على مرجع نسبي لأسفل أو لليمين، يتغير
المرجع تلقائيًا. تخيل أنك تسحب الصيغة لأسفل، رقم الصف يزيد.
مثال: إذا كان في `C1` الصيغة `=A1+B1`،
وسحبتها لأسفل إلى `C2`، ستتحول الصيغة إلى `=A2+B2`.
مرجع مطلق (مثل: $A$1):
يقوم "بتثبيت" العمود والصف. عند السحب، لا يتغير المرجع مطلقًا. اضغط
على مفتاح F4 على الكيبورد بعد كتابة المرجع لتحويله إلى
مطلق.
مثال: إذا كان في `C1` الصيغة `=$A$1+B1`، وسحبتها لأسفل إلى `C2`،
ستتحول الصيغة إلى `=$A$1+B2`. الخلية `A1` осталась كما هي.
مهمة تطبيقية:
1. في ورقة جديدة، اكتب `5` في `A1` و `3` في `B1`.
2. في `C1`،
اكتب `=A1+B1` وشاهد الناتج.
3. غيّر قيمة `A1`
إلى `10` وشاهد كيف يتغير `C1`.
4. في `D1`،
اكتب `=$A$1+B1`.
5. اسحب (`Drag`)
الخلية `C1` و `D1`
لأسفل إلى الصف `2`. انظر إلى الصيغ في `C2`
و `D2` لترى الفرق.
الدرس 3.2: دوال الرياضيات الأساسية (SUM, AVERAGE, MAX, MIN, COUNT)
الهدف:
استخدام الدوال الجاهزة لإجراء عمليات حسابية معقدة بسرعة.
الشرح:
1. بناء الدالة (Syntax): `=اسم_الدالة(نطاق_الخلايا)`
النطاق (Range):
مجموعة متصلة من الخلايا. نكتبها بالشكل `الخلية_الأولى:الخلية_الأخيرة` (مثال: `A1:A10` لكل الخلايا من A1
إلى A10).
2. الدوال الخمسة الأساسية:
SUM`: `=SUM(A1:A10)`
⮕ يجمع كل القيم في النطاق A1:A10.
AVERAGE`: `=AVERAGE(A1:A10)`
⮕ يحسب متوسط القيم في النطاق.
MAX`: `=MAX(A1:A10)`
⮕ يجد أقصى قيمة في النطاق.
MIN`: `=MIN(A1:A10)`
⮕ يجد أدنى قيمة في النطاق.
COUNT`: `=COUNT(A1:A10)` ⮕ يعد عدد الخلايا التي تحتوي على أرقام في النطاق.
3. طريقتان للكتابة:
الكتابة المباشرة: اكتب `=SUM(` ثم حدد النطاق بزر الماوس واسحبه، ثم أغلق
القوس `)`.
استخدام الأيقونة: اذهب إلى Home > AutoSum (أيقونة Σ)
واختر الدالة.
مهمة تطبيقية:
أنشئ عمودًا به 10 أرقام عشوائية (من A1
إلى A10). في أسفل العمود (مثل A11)، استخدم الدوال الخمسة لحساب المجموع،
المتوسط، القيمة القصوى، القيمة الدنيا، وعدد الخلايا.
الدرس 3.3: دالة الجمع الشرطي (SUMIF, SUMIFS)
الهدف: جمع
قيم بناءً على شرط أو معايير محددة.
الشرح:
1. SUMIF`
(شرط واحد):
البناء: `=SUMIF(نطاق_التحقق,
الشرط, نطاق_الجمع)`
مثال: لديك جدول للمبيعات: العمود A (البائع)، العمود B
(قيمة المبيعات).
`=SUMIF(A:A,
"أحمد", B:B)` ⮕ اجمع كل قيم المبيعات في العمود B
حيث يكون اسم البائع في العمود A
هو "أحمد".
2. SUMIFS`
(شرط واحد أو أكثر - الأقوى):
البناء: `=SUMIFS(نطاق_الجمع, نطاق_الشرط1, الشرط1,
نطاق_الشرط2, الشرط2, ...)`
مثال: نفس جدول المبيعات،عمود C للتاريخ.
`=SUMIFS(B:B, A:A,
"أحمد", C:C,
">=1/1/2024")`
⮕ اجمع مبيعات "أحمد" التي تمت بعد تاريخ 1/1/2024.
مهمة تطبيقية:
أنشئ هذا الجدول البسيط:
استخدم `SUMIF` لحساب إجمالي مبيعات أحمد (يجب أن تحصل على
250).
الدرس 3.4: دوال النصوص (CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM)
الهدف: معالجة
وتنظيم البيانات النصية.
الشرح:
CONCATENATE`
أو `&`: دمج نصوص من خلايا متعددة.
`=CONCATENATE(A2, " ", B2)` أو `=A2 & " " & B2` ⮕ يدمج الاسم الأول والاسم الأخير مع مسافة بينهما.
LEFT(نص,
عدد_الأحرف)`: إرجاع عدد محدد من الأحرف من بداية النص.
`=LEFT("Cairo", 3)`
⮕ يعطي "Cai".
RIGHT(نص,
عدد_الأحرف)`: إرجاع عدد محدد من الأحرف من نهاية النص.
MID(نص,
موقع_البداية, عدد_الأحرف)`: إرجاع عدد محدد من الأحرف من منتصف النص.
`=MID("Hello World", 7, 5)` ⮕ يعطي "World".
LEN(نص)`:
حساب عدد الأحرف في النص (بما في ذلك المسافات).
TRIM(نص)`:
إزالة جميع المسافات الزائدة من النص، except for single spaces between words. مهم جدًا لتنظيف البيانات.
مهمة تطبيقية:
إذا كان الاسم الأول في A2 هو "محمد" والاسم الأخير في B2 هو "علي"، اكتب صيغة في C2 لدمجهما ليصبحا "محمد علي".
الدرس 3.5: دوال التاريخ والوقت (TODAY, NOW, DATE, DATEDIF)
الهدف:
التعامل مع التواريخ والأوقات بشكل ديناميكي لحساب الأعمار، الفترات الزمنية،
وغيرها.
الشرح:
1. ()TODAY`: تُرجع التاريخ الحالي للنظام (يوم، شهر، سنة). لا تحتوسيطات.
مثال: `=()TODAY` ⮕ سيعرض تاريخ اليوم (مثل 11/10/2023). سيتغير هذا التاريخ تلقائيًا كلما فتحت الملف.
2. ()NOW`: تُرجع التاريخ والوقت الحاليين للنظام. لا تحتوسيطات.
مثال: `=()NOW` ⮕ سيعرض (مثل 11/10/2023 14:30). سيتغير هذا أيضًا تلقائيًا.
3. DATE(السنة,
الشهر, اليوم)`: تُنشئ تاريخًا من أرقام السنة، الشهر، اليوم.
مثال: `=DATE(2024, 1, 15)` ⮕ سينشئ التاريخ 15/01/2024.
مفيد لـ: إنشاء تواريخ ديناميكية. `=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)` ⮕ سيعطي أول يوم من الشهر الحالي.
4. DATEDIF(التاريخ_الأقدم,
التاريخ_الأحدث, "الوحدة")`: دالة مخفية وقوية تحسب الفرق بين تاريخين.
الوحدات (الوسيط الثالث):
`"Y"`:
الفرق بالسنوات الكاملة.
`"M"`:
الفرق بالأشهر الكاملة.
`"D"`:
الفرق بالأيام.
`"MD"`:
الفرق بالأيام (تتجاهل الأشهر والسنوات).
`"YM"`:
الفرق بالأشهر (تتجاهل الأيام والسنوات).
`"YD"`:
الفرق بالأيام (تتجاهل السنوات).
مثال: لحساب العمر بالسنين إذا كان تاريخ
الميلاد في الخلية A2:
`=DATEDIF(A2, TODAY(), "Y")`
مهمة تطبيقية:
1. في خلية، اكتب `=()TODAY وشاهد النتيجة.
2. في خلية أخرى، اكتب `=()NOW` ولاحظ الفرق.
3. إذا كان لديك تاريخ ميلاد في خلية (مثلاً A2: 01/01/1990)، استخدم `DATEDIF` لحساب العمر بالسنوات.
الدرس 3.6: التمرين العملي للوحدة (كشف رواتب بسيط)
الهدف: تطبيق جميع الدوال التي تعلمتها في نموذج عملي.
المهمة: أنشئ
كشف الرواتب البسيط التالي في ورقة جديدة:
1. المجموع (العمود D): في الخلية D2، اكتب صيغة لجمع الراتب والعمولة (`=B2+C2`). اسحب هذه الصيغة لأسفل للخلية D4.
2. المجموع الكلي (الصف 5): في الخلية B5، استخدم دالة `SUM`
لجمع عمود "الراتب" (`=SUM(B2:B4)`).
افعل نفس الشيء لعمود "العمولة" في C5
وعمود "المجموع" في D5.
3. المتوسط (الصف 6): في الخلية B6، استخدم دالة `AVERAGE` (`=AVERAGE(B2:B4)`).
4. أعلى راتب (الصف 7): في الخلية B7، استخدم دالة `MAX`
على عمود "المجموع" (`=MAX(D2:D4)`).
5. (تحدي إضافي) في العمود E
"الملاحظات"، اكتب دالة `IF`
في الخلية E2 تعرض "ممتاز" إذا كان المجموع في D2 أكبر من 5500، وتظهر "جيد" إذا
كان غير ذلك.
(`=IF(D2>5500,
"ممتاز", "جيد")`). اسحب هذه الصيغة لأسفل.
النتيجة
النهائية: يجب أن يحسب Excel كل شيء تلقائيًا. جرب تغيير بعض قيم الرواتب
أو العمولات وشاهد كيف تتغير جميع النتائج تلقائيًا.
تهانينا! لقد
أتقنت الوحدة الثالثة، وهي نقطة التحول الكبرى من مستخدم عادي إلى مستخدم قادر على
أتمتة الحسابات.
الوحدة القادمة هي where things get really exciting:
الوحدة 4: الدوال المتقدمة والمنطقية (IF, VLOOKUP, XLOOKUP).
هل أنت مستعد
للغوص في هذه الدوال القوية؟
ان شاء الله واصل معنا التعلم لكى نغوص
الآن في أعماق Excel. هذه الدوال هي التي تفصل بين المستخدم
العادي والمستخدم المحترف.