عند كتابة المعادلات في Excel، تعتبر SUM و VLOOKUP من الأساسيات للمبتدئين. ولكن نظرًا لأن Excel يحتوي على أكثر من 400 دالة، فمن المفيد معرفة المزيد من الدوال المفيدة للغاية، خاصةً إذا كنت تتطلع إلى أن تصبح مستخدمًا متوسطًا وما بعده. دعني أساعدك في اكتشاف الدوال التي ستزيد من تبسيط سير عملك في Excel.
الدوال التي سأغطيها هنا ليست معقدة، وستتمكن من استخدامها في إعدادات مختلفة، سواء كنت طالبًا أو محترف أعمال أو محللًا. حان الوقت للارتقاء بمستوى مهاراتك في Excel باستخدام بعض الدوال الإضافية الموفرة للوقت التي قد لا تعرفها.
روابط سريعة
دالة COUNT تقدم طريقة أفضل لحساب الخلايا
إذا كانت تحتوي على بيانات رقمية
تقوم دالة COUNT بحساب جميع الخلايا التي تحتوي على قيم رقمية وتعيد النتيجة. هذا يوفر عليك عناء حسابها يدويًا، الأمر الذي قد يكون مملًا ويستغرق وقتًا طويلاً في مجموعات البيانات الكبيرة.
=COUNT(value1, value2, ... value_n)
يجب أن تُخرج المعادلة المثال أدناه 10 إذا كانت جميع الخلايا تحتوي على أرقام.
=COUNT(A1:A10)
دالة AVERAGE تبسط حساب المتوسط
تعمل مع أي نطاق
كما يوحي الاسم، تحسب دالة AVERAGE المتوسط (المعدل) عن طريق جمع كل القيم الرقمية في نطاق وتقسيمها على العدد الإجمالي.
=AVERAGE(value1, value2, ... value_n)
على سبيل المثال، إليك صيغة لإيجاد متوسط القيم في الخلايا من A1 إلى A10:
=AVERAGE(A1:A10)
دالة MIN تحدد القيمة الأصغر في نطاق
توجد أيضًا دالة MAX
لنفترض أن لديك مجموعة بيانات كبيرة، وتحتاج إلى إيجاد أصغر قيمة. دالة MIN هي أسرع طريقة للقيام بذلك.
=MIN(value1, value2, ... value_n)
إليك مثال لكيفية عملها:
=MIN(A1:A10)
على الجانب الآخر، إذا كنت تريد إيجاد أكبر قيمة في نطاق، فيجب عليك استخدام دالة MAX.
=MIN(value1, value2, ... value_n)
SUMIF: النسخة الأذكى من دالة SUM
المنطق الشرطي مطلوب
دالة SUM تجمع ببساطة أي قيم رقمية تُدخلها. أما دالة SUMIF، فلا تجمع إلا القيم التي تحقق معيارًا محددًا. إنها تجمع بين دالتي SUM و IF في Excel، لتوفير عليك عناء الفرز أو التصفية اليدوية للبيانات قبل جمعها.
=SUMIF(range_to_evaluate, criteria, [range_to_sum])
هنا، range_to_evaluate هي الخلايا التي تحتوي على القيم التي يجب على الدالة التحقق منها مقابل الشرط المحدد في المعامل criteria. أما الوسيط الاختياري range_to_sum، فهو الخلايا المحددة في النطاق الذي تريد جمعه. إذا حذفت هذا المعامل، فستجمع الدالة كل شيء في النطاق الذي يحقق الشرط.
فيما يلي مثال على كيفية عمل الدالة. هذه الصيغة تجمع فقط القيم في النطاق A1:A10 إذا كانت أكبر من 50.
=SUMIF(A1:A10, ">50")
هناك اختلافات أخرى لدالة SUMIF يمكنك استكشافها أيضًا، بما في ذلك COUNTIF و AVERAGEIF و MINIF و MAXIF. تسمح لك بعض الإصدارات بتحديد معايير متعددة، مثل SUMIFS و COUNTIFS و AVERAGEIFS.
SUBTOTAL: تقدم تجميعًا أنظف
البيانات غير الضرورية يجب ألا تفسد حساباتك
يستخدم محترفو Excel دالة SUBTOTAL لأنها تستبعد بذكاء البيانات التي تمت تصفيتها أو إخفاؤها، بينما تتضمن دالة SUM والدوال المماثلة كل شيء. هذا يجعلها الخيار الأمثل عند العمل مع مجموعات بيانات ديناميكية، خاصة عندما يضمن الإخفاء والتصفية الدقة.
=SUBTOTAL(function_code, range)
المعامل function_code هو رقم من 1-11 أو 101-111 يحدد الدالة المراد استخدامها (على سبيل المثال، 1 لـ AVERAGE، 2 لـ COUNT، 9 لـ SUM). الأرقام 1-11 ستتضمن جميع البيانات، بينما 101-111 ستستبعد الصفوف المخفية. المعامل range هو الخلايا المراد حساب المجموع الفرعي لها.
المثال أدناه سيجمع النطاق A1:A10 ولكنه سيستبعد أي صفوف مخفية:
=SUBTOTAL(109, A1:A10)
XLOOKUP تتغلب على عيوب VLOOKUP
الطريقة الحديثة لإجراء البحث
بما أنك على دراية بـ VLOOKUP، فربما تعرف عيبها الرئيسي – يمكنها البحث فقط من اليسار إلى اليمين وتتطلب أن يكون عمود البحث على الجانب الأيسر من عمود الإرجاع. XLOOKUP أكثر قوة ومرونة، مما يسمح لك بالبحث في أي اتجاه. لا تحتاج حتى إلى فرز الأعمدة.
=XLOOKUP(lookup_value, lookup_range, return_range)
هنا، lookup_value هي القيمة التي تبحث عنها، و lookup_range هو المكان الذي تبحث فيه عن القيمة، و return_range هي القيمة التي سيتم إرجاعها عند العثور على القيمة. ضع في اعتبارك أيضًا أن هذه نسخة مبسطة من XLOOKUP، مع وجود معالجة الأخطاء كمعامل اختياري في النسخة الكاملة.
إليك مثال حيث يحتوي النطاق A2:A5 على معرفات العملاء ويحتوي النطاق B2:B5 على أسماء العملاء. نستخدم XLOOKUP للعثور على اسم العميل الذي يوجد معرفه في الخلية D1.
=XLOOKUP(D1, A2:A5, B2:B5)
INDEX/MATCH هي المجموعة الكلاسيكية للبحث
قبل ظهور XLOOKUP
XLOOKUP غير متوفر في إصدارات Excel الأقدم من 2021 و بدائل Office الأخرى مثل LibreOffice أو OnlyOffice. لذلك، إذا كنت تريد مرونة XLOOKUP في هذه السيناريوهات، فستحتاج إلى الجمع بين وظيفتي INDEX و MATCH. علاوة على ذلك، يمنحك هذا الجمع تحكمًا أفضل في كل خطوة من خطوات البحث، على الرغم من أنه لا يحتوي على معالجة الأخطاء الموجودة في XLOOKUP.
=INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))
هنا، return_range هو نطاق الخلايا الذي يحتوي على القيمة التي تريد استردادها، و lookup_value هي القيمة التي تريد البحث عنها، و lookup_range هو نطاق الخلايا الذي تريد البحث فيه عن قيمة البحث. يقبل المعامل match_type القيم التالية: 0 للمطابقة التامة، و 1 للأقل من، و -1 للأكبر من.
بالاستمرار مع مثال XLOOKUP في القسم السابق، ستكون نسخة INDEX MATCH كالتالي:
=INDEX(B2:B5, MATCH(D1, A2:A5, 0))
كما ذكرنا سابقًا، لديك تحكم هنا. يمكنك، على سبيل المثال، استخدام ال
دالة XMATCH بدلاً من MATCH للحصول على عمليات بحث أكثر تقدماً. بل إن البعض يستخدم دالة FILTER إذا كانوا لا يريدون القيام بالتصفية والترتيب اليدويين أثناء عمليات البحث.
SUM و VLOOKUP مجرد بداية
إتقان Excel يتجاوز بكثير SUM و VLOOKUP. من خلال تعلم دوال مثل COUNT و AVERAGE و SUMIF و SUBTOTAL و XLOOKUP و INDEX/MATCH، يمكنك إيجاد فرص لتحسين ورقة Excel الخاصة بك. بعض الدوال تساعدك على تحليل البيانات بشكل أسرع، وأتمتة المهام، وجعل جداولك الذكية أكثر ذكاءً، ولكن هذا موضوع لوقت آخر.
