أهم 10 وظائف في برنامج excel
1. إذا كانت الوظيفة
تقوم الدالة IF بإرجاع قيمة إذا كان الشرط الذي تحدده هو TRUE، وإلا فستقوم بإرجاع قيمة أخرى. ببساطة، يمكن للدالة IF أولاً اختبار الشرط وإرجاع قيمة بناءً على نتيجة ذلك الشرط.
بناء الجملة
إذا (اختبار منطقي، قيمة_إذا_صحيح، قيمة_إذا_خطأ)
الحجج
- logic_test: الحالة التي تريد تقييمها.
- value_if_true: القيمة التي تريد الحصول عليها إذا كان هذا الشرط صحيحًا.
- value_if_false: القيمة التي تريد الحصول عليها إذا كان هذا الشرط خطأ.
تعليقات
- الحد الأقصى لعدد الشروط المتداخلة التي يمكنك تنفيذها هو 64.
- يمكنك استخدام عوامل المقارنة لتقييم الحالة.
مثال
في المثال أدناه، استخدمنا عامل مقارنة لتقييم الظروف المختلفة.
- استخدمنا نصًا محددًا للحصول على النتيجة إذا تم استيفاء الشرط أم لا.
- يمكنك أيضًا استخدام TRUE وFALSE للحصول على النتيجة.
- إذا تخطيت تحديد قيمة للحصول على النتيجة، إذا كان الشرط صحيحًا فسوف يُرجع صفرًا.
- وإذا تخطيت تحديد قيمة للحصول على النتيجة إذا كان الشرط FALSE فسوف يعود صفراً.
في المثال أدناه، استخدمنا الدالة IF لإنشاء صيغة متداخلة.
لقد حددنا شرطًا وإذا كان هذا الشرط خاطئًا، استخدمنا IF آخر لتقييم شرط آخر وتنفيذ مهمة، وإذا كان هذا الشرط FALSE استخدمنا IF آخر.
بهذه الطريقة، استخدمنا IF خمس مرات لإنشاء صيغة متداخلة. يمكنك استخدام نفس الشيء 64 مرة لصيغة التداخل.
2. وظيفة IFERROR
ترجع الدالة IFERROR قيمة محددة في حالة حدوث خطأ. بكلمات بسيطة يمكنه اختبار القيمة وإذا كانت تلك القيمة خطأ فإنها ترجع القيمة التي حددتها.
بناء الجملة
IFERROR(القيمة، value_if_error)
الحجج
- القيمة: القيمة التي تريد اختبار الخطأ فيها.
- value_if_error: القيمة التي تريد استرجاعها عند حدوث خطأ.
تعليقات
- تتعلق الدالة IFERROR بحدوث خطأ، وليس بنوع الخطأ.
- إذا تجاهلت القيمة أو value_if_error فسوف تُرجع 0 في النتيجة.
- يمكنه اختبار #N/A، #REF!، #DIV/0!، #VALUE!، #NUM!، #NAME؟ و#NULL!.
- إذا قمت بتقييم مصفوفة، فسوف تُرجع مصفوفة من النتائج لكل عنصر محدد.
مثال
في المثال أدناه، استخدمنا الدالة IFERROR لاستبدال #DIV/0! مع نص ذو معنى.
IFERROR متوافق فقط مع إصدارات 2007 والإصدارات السابقة. لحل هذه المشكلة، يمكنك استخدام ISERROR.
3. وظيفة ترونك
تقوم الدالة TRUNC بإرجاع عدد صحيح بعد اقتطاع الرقم الأصلي. بعبارات بسيطة، فهو يزيل المنازل العشرية من رقم بدقة محددة ثم يُرجع الجزء الصحيح من النتيجة.
بناء الجملة
TRUNC(رقم، [number_digits])
الحجج
- الرقم: الرقم الذي تريد اقتطاعه.
- [num_digits]: رقم لتحديد دقة اقتطاع الرقم.
تعليقات
- إذا تجاهلت تحديد المتعدد فسوف يُرجع خطأ.
- إنه يدور من الصفر.
- إذا كان لديك مضاعفين على نفس المسافة، فسيتم إرجاع المضاعف الأعلى للرقم الذي تقوم بتقريبه.
مثال
في المثال أدناه، استخدمنا TRUNC لاقتطاع البيانات لإزالة الوقت من التواريخ.
4. وظيفة سوميف
ترجع الدالة SUMIF مجموع الأرقام التي تستوفي الشرط الذي تحدده . بعبارات بسيطة، فهو يأخذ في الاعتبار ويحسب فقط مجموع القيم التي تستوفي الشرط.
بناء الجملة
SUMIF(النطاق، المعايير، [sum_range])
الحجج
- النطاق: نطاق من الخلايا التي تريد التحقق من المعايير منها.
- المعيار: معيار يمكن أن يكون رقمًا أو نصًا أو تعبيرًا أو مرجع خلية أو دالة.
- [sum_range]: نطاق من الخلايا يحتوي على القيم التي تريد جمعها.
تعليقات
- إذا تم حذف sum_range، فسيتم جمع الخلايا الموجودة في النطاق.
- تأكد من استخدام علامات الاقتباس المزدوجة لتحديد معايير النص أو التي تتضمن رموزًا رياضية، والتي يجب وضعها بين علامات اقتباس مزدوجة.
- يجب أن يكون حجم نطاق المعايير ونطاق المجموع بنفس الحجم.
مثال
في المثال أدناه، حددنا A1:A9 كنطاق المعايير وB1:B9 كنطاق المجموع وبعد ذلك حددنا المعايير في A12 التي لها القيمة C.
يمكنك أيضًا إدراج معايير مباشرة في الوظيفة. في المثال أدناه، استخدمنا حرف بدل النجمة لتحديد معيار يحتوي على الأبجدية “S”.
وإذا تجاهلت نطاق المجموع، فسيعطيك مجموع نطاق المعايير. ولكن هذا لن يكون ممكنًا إلا إذا كان نطاق المعايير يحتوي على قيم رقمية.
5. وظيفة الفهرس
تقوم الدالة INDEX بإرجاع قيمة من قائمة القيم بناءً على رقم الفهرس الخاص بها. بعبارات بسيطة، يقوم INDEX بإرجاع قيمة من قائمة القيم وتحتاج إلى تحديد موضع تلك القيمة.
بناء الجملة
يحتوي INDEX على بناء جملتين مختلفتين. في الأول ، يمكنك استخدام نموذج مصفوفة من الفهرس للحصول على قيمة من القائمة باستخدام موضعها.
INDEX(صفيف،row_num،[column_num])
في الخيار الثاني ، يمكنك استخدام نموذج رعاية وهو أقل استخدامًا في الحياة الواقعية، ولكن يمكنك استخدامه إذا كان لديك أكثر من نطاق واحد للترويج.
INDEX(المرجع،row_num، [column_num]، [are_num])
الحجج
- المصفوفة: نطاق من الخلايا أو ثابت المصفوفة.
- المرجع: نطاق من الخلايا أو عدة نطاقات.
- Row_number: رقم الصف الذي تريد الحصول على القيمة منه.
- [col_number]: رقم العمود الذي تريد الحصول على القيمة منه.
- [area_number]: إذا كنت تشير إلى نطاقات متعددة من الخلايا (باستخدام بناء الجملة المرجعي)، فحدد رقمًا للإشارة إلى نطاق واحد من بينها جميعًا.
تعليقات
- عند تحديد الوسيطتينrow_num وcolumn_num، فسوف تُرجع القيمة الموجودة في الخلية عند تقاطعهما.
- إذا قمت بتحديدrow_num أو column_num كـ 0 (صفر)، فسيتم إرجاع مصفوفة القيم للعمود أو الصف بأكمله، على التوالي.
- عندما يكون Row_num وcolumn_num خارج النطاق، فسوف يُرجع #REF! خطأ.
- إذا كانت قيمة Area_number أكبر من نطاقات الأرقام التي حددتها، فسوف تُرجع #REF!.
المثال 1 – استخدام ARRAY للحصول على قيمة من القائمة
في المثال أدناه، استخدمنا الدالة INDEX للحصول على الكمية لشهر يونيو. في القائمة، يقع Jun في المركز السادس (الصف السادس) ولهذا السبب قمت بتحديد 6 في Row_number. قام INDEX بإرجاع القيمة 1904 في النتيجة.
وإذا كنت تشير إلى نطاق يحتوي على أكثر من عمود، فيجب عليك تحديد رقم العمود.
المثال 2 – استخدام المرجع للحصول على قيمة قوائم متعددة
في المثال أدناه، بدلاً من تحديد النطاق بأكمله مرة واحدة، قمت بتحديده في ثلاثة نطاقات مختلفة. في الوسيطة الأخيرة، حددنا 2 في Area_number الذي سيحدد النطاق المطلوب استخدامه من هذه النطاقات الثلاثة المختلفة.
الآن في الصف الثاني نشير إلى الصف الخامس والعمود الأول. قام INDEX بإرجاع القيمة 172 الموجودة في الصف الخامس من الصف الثاني.
6. وظيفة VLOOKUP
تبحث الدالة VLOOKUP عن قيمة في العمود الأول من الجدول وتقوم بإرجاع القيمة الموجودة في نفس الصف من القيمة المقابلة باستخدام رقم الفهرس. بعبارات بسيطة، يقوم بإجراء بحث عمودي.
بناء الجملة
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
الحجج
- lookup_value: القيمة التي تريد البحث عنها في عمود. يمكنك الإشارة إلى خلية تحتوي على قيمة البحث أو إدخال تلك القيمة مباشرة في الوظيفة.
- table_array: نطاق من الخلايا، وهو نطاق مسمى تريد العثور على القيمة منه.
- col_index_num: يمثل الرقم رقم العمود الذي تريد استرداد القيمة منه.
- range_lookup: استخدم خطأ أو 0 للمطابقة التامة وصحيح أو 1 للمطابقة الصحيحة. الافتراضي هو الصحيح.
تعليقات
- إذا لم تعثر VLOOKUP على القيمة التي تبحث عنها، فسوف تُرجع #N/A.
- يمكن أن توفر لك VLOOKUP فقط القيمة الموجودة على يمين قيمة البحث. إذا كنت تريد النظر من الجانب الأيمن، يمكنك استخدام INDEX وMATCH لذلك.
- إذا استخدمت المطابقة التامة، فستطابق فقط القيمة الموجودة أولاً في العمود.
- يمكنك أيضًا استخدام أحرف البدل مع VLOOKUP .
- يمكنك استخدام TRUE أو 1 إذا كنت تريد تطابقًا مناسبًا وFALSE أو 0 لمطابقة تامة.
- إذا استخدمت تطابقًا مناسبًا (صحيح): فسوف يُرجع القيمة الأصغر التالية في القائمة إذا لم يكن هناك تطابق تام.
- إذا كانت القيمة التي تبحث عنها أقل من أصغر قيمة في القائمة، فستُرجع VLOOKUP #N/A.
- إذا كانت هناك قيمة محددة تبحث عنها، فسوف تعطيك تلك القيمة بالضبط.
- تأكد من قيامك بفرز القائمة بترتيب تصاعدي.
مثال
1. استخدام VLOOKUP للفئات
في المثال أدناه، لدينا قائمة بالطلاب الذين حصلوا على الدرجات، وفي عمود الملاحظات نريد الحصول على درجة بناءً على درجاتهم.
في قائمة العلامات التجارية أعلاه، نود إضافة ملاحظات وفقًا لنطاق الفئة أدناه.
في هذا لدينا خياران للاستخدام.
الأول هو إنشاء صيغة متداخلة باستخدام IF، الأمر الذي يستغرق بعض الوقت، والخيار الثاني هو إنشاء صيغة باستخدام VLOOKUP مع تطابق مناسب. وستكون الصيغة:
=VLOOKUP(B2,$E$2:$G$5,3,TRUE)
كيف تعمل
أستخدم عمود “MIN MARKS” لمطابقة قيمة البحث واستعادة القيمة من عمود “Remarks”.
لقد ذكرت بالفعل أنه عندما تستخدم TRUE ولا توجد قيمة بحث مطابقة تمامًا، فسوف تُرجع القيمة الأصغر التالية من قيمة البحث. على سبيل المثال، عندما نبحث عن القيمة 77 في جدول الفئات، فإن 65 هي أصغر قيمة بعد 77.
ولهذا السبب حصلنا على درجة “جيد” في التعليقات.
2. معالجة الأخطاء في وظيفة VLOOKUP
إحدى المشكلات الأكثر شيوعًا التي تنشأ عند استخدام VLOOKUP هي أنك ستحصل على #N/A عندما لا يتم العثور على أي تطابق. لكن حل هذه المشكلة بسيط وسهل. اسمحوا لي أن تظهر لك مع مثال بسيط.
في المثال أدناه لدينا قائمة بالأسماء وأعمارها وفي الخلية E6 نستخدم وظيفة VLOOKUP للبحث عن اسم في القائمة. في كل مرة أكتب فيها اسمًا غير موجود في القائمة، أحصل على #N/A.
لكن ما أريده هنا هو عرض رسالة ذات معنى بدلاً من الخطأ. ستكون الصيغة: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)
كيف يعمل : يمكن لـ IFNA اختبار قيمة لـ #N/A وإذا كان هناك خطأ، يمكنك تحديد قيمة بدلاً من الخطأ.
7. وظيفة IFNA
ترجع الدالة IFNA قيمة محددة في حالة حدوث خطأ #N/A. وعلى عكس IFERROR، فهو يقوم فقط بتقييم الخطأ #N/A وإرجاع القيمة التي حددتها.
بناء الجملة
IFNA(القيمة، value_if_na)
الحجج
- القيمة: القيمة التي تريد اختبارها لخطأ #N/A.
- value_if_na: القيمة التي تريد إرجاعها في حالة حدوث خطأ.
تعليقات
- إذا لم تقم بتحديد أي وسيطات، فسوف تتعامل IFNA معها كسلسلة فارغة (“”).
- إذا كانت القيمة عبارة عن مصفوفة، فسوف تُرجع النتيجة كمصفوفة.
- سيتم تجاهل كافة الأخطاء الأخرى #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? و#NULL!.
مثال
في الدالة VLOOKUP، يحدث #N/A عندما لا تكون قيمة البحث في نطاق البحث ولهذا قمنا بتحديد رسالة ذات معنى باستخدام IFNA.
ملاحظة: تم تقديم IFNA في Excel 2013، لذا فهو غير متوفر في الإصدارات السابقة.
8. وظيفة راند
ترجع الدالة RAND رقمًا عشوائيًا بين 0 و1. بعبارات بسيطة، يمكنك إنشاء رقم عشوائي بين 0 و1 (يتم تحديث قيمته في كل مرة تقوم فيها بإجراء تغيير على ورقة العمل).
بناء الجملة
راند()
الحجج
- لا توجد وسائط لتحديدها في وظائف RAND
تعليقات
- إذا وضعت صفرًا في المضاعف، فسيعود صفرًا في النتيجة.
- إذا تجاهلت تحديد المتعدد فسوف يُرجع خطأ.
- إنه يدور من الصفر.
- إذا كان لديك مضاعفين على نفس المسافة، فسيتم إرجاع المضاعف الأعلى للرقم الذي تقوم بتقريبه.
مثال
إلى جانب وجود أرقام بين 0 و1، يمكنك أيضًا استخدام RAND للأرقام العشوائية بين رقمين محددين. في المثال أدناه، استخدمته لإنشاء صيغة تولد رقمًا عشوائيًا بين 50 و100.
عندما تقوم بإدخال هذه الصيغة في خلية، فإنها تقوم بإرجاع رقم بين 100 و50 عن طريق ضرب القيم التي أرجعتها RAND بالمعادلة التي استخدمناها. لفهم هذه الصيغة، علينا تقسيمها إلى ثلاثة أجزاء:
- أولاً، عندما يكتشف الرقم الأدنى من الرقم الأعلى، تحصل على الفرق بين الاثنين.
- ثم يقوم بضرب هذا الفرق بالرقم العشوائي الذي يتم إرجاعه بعد الطرح.
- وثالثًا، أضف هذا الرقم مع أقل رقم متبقي في الجزء الثالث من المعادلة.
ذات صلة: كيفية إنشاء رسائل عشوائية بسرعة في Excel
9. الدالة SUM
تقوم الدالة SUM بإرجاع مجموع القيم المتوفرة . بعبارات بسيطة، باستخدام الدالة SUM، يمكنك حساب مجموع قائمة القيم (يمكنك إدخال قيمة مباشرة في الدالة أو الرجوع إلى نطاق من الخلايا.
بناء الجملة
مجموع(رقم1،[رقم2]،…)
الحجج
- number1 : رقم، نطاق الخلايا التي تحتوي على أرقام، أو خلية مفردة تحتوي على رقم.
- [number2] : رقم، نطاق الخلايا التي تحتوي على أرقام، أو خلية واحدة تحتوي على رقم.
تعليقات
- يتجاهل القيم النصية.
مثال
في المثال أدناه، يمكنك إدراج أرقام مباشرة في الدالة باستخدام الفواصل بينها.
يمكنك أيضًا الرجوع إلى نطاق ما لحساب مجموع الأرقام، وإذا كان هناك أي نص أو قيمة منطقية أو خلية فارغة، فسيتم تجاهلها.
إذا كانت هناك قيمة خطأ في الخلية التي تشير إليها، فسوف تُرجع #N/A في النتيجة.
إذا كانت لديك قيم رقمية منسقة كنص، فسوف تتجاهلها. يوصى بتحويلها إلى أرقام قبل استخدام SUM.
10. أو وظيفة
تقوم الدالة OR بإرجاع قيمة منطقية (TRUE أو FALSE) بعد اختبار الشروط التي تحددها. بعبارات بسيطة، يمكنك اختبار شروط متعددة باستخدام الدالة AND وتقوم بإرجاع TRUE إذا كان أي من هذه الشروط (أو جميعها) صحيحًا وترجع FALSE فقط إذا كانت جميع هذه الشروط خاطئة.
بناء الجملة
أو (منطق 1، [منطق 2]، …)
الحجج
- logic1: الحالة التي تريد التحقق منها.
- [منطقي 2]: الشروط الإضافية التي تريد التحقق منها.
تعليقات
- سيتم تجاهل القيم إذا كانت الخلية أو الجدول المرجعي يحتوي على خلية أو نص فارغ.
- يجب أن تكون نتيجة الشروط قيمة منطقية (TRUE أو FALSE).
- سيُرجع خطأ إذا لم يتم إرجاع أي قيمة منطقية.
مثال
في المثال أدناه، قمنا بإنشاء شرط باستخدام الدالة IF أنه إذا حصل الطالب على 60 نقطة أعلى في أي من المادتين، فإن الصيغة ترجع TRUE.
الآن في المثال أدناه استخدمنا رقمًا للحصول على القيم المنطقية في الصيغة. يمكنك أيضًا تنفيذ الشرط أعلاه بترتيب عكسي.
يمكنك استخدام TRUE وFALSE بدلاً من الأرقام. تتعامل الدالة OR مع هذه القيم المنطقية كأرقام.
المزيد من الدروس
الوظائف الإحصائية / وظائف التاريخ / السلسلة – وظائف النص / الوظائف المالية