تقريبًا كل من يستخدم Excel لديه تلك الدالة التي يعتمد عليها منذ أيام الجامعة لأنها تعمل بشكل جيد بما يكفي لتجنب تعلم شيء جديد. لهذا السبب ستجد VLOOKUP و CONCATENATE في ملفات جديدة تمامًا، حتى في عام 2026. أنا أتفهم ذلك، لأنني ما زلت أتردد في التخلي عن SUBTOTAL لصالح AGGREGATE بعد أن دعمتني لفترة طويلة.
ومع ذلك، فقد تطور Excel، والعديد من الصيغ التي تعلمناها منذ سنوات أصبحت الآن أكثر تعقيدًا، وأكثر هشاشة، أو أصعب في الصيانة مما ينبغي أن تكون عليه. إذا كانت صيغك تبدو كما كانت في ذلك الوقت وتشعر أن جداول البيانات الخاصة بك أثقل مما ينبغي، فقد تكون هذه علامة على أنك بحاجة إلى التحديث.
روابط سريعة
XLOOKUP يحل محل VLOOKUP و HLOOKUP بالكامل
دالة واحدة تعمل في أي اتجاه
VLOOKUP و HLOOKUP هما في الأساس المجموعة الأولية من عمليات البحث في Excel. إنهما تعملان، مألوفتان، وموجودتان في كل مكان. المشكلة هي أنهما جامدتان بطرق تصبح أكثر إحباطًا مع نمو جداول البيانات الخاصة بك. شيء بسيط مثل إدراج عمود في منتصف الجدول يمكن أن يكسر نتائجك لأن كلتا الدالتين تعتمدان على أرقام فهرس الأعمدة أو الصفوف المشفرة:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
علاوة على ذلك، لا يمكن لـ VLOOKUP البحث إلى يسار عمود المفتاح الخاص بك، مما يجبرك على إعادة ترتيب البيانات أو إنشاء أعمدة مساعدة فقط لجعل البحث الأساسي يعمل. XLOOKUP يصلح كل هذا دفعة واحدة. بدلاً من الإشارة إلى جدول كبير وعد الأعمدة أو الصفوف، فإنك تخبر Excel بالضبط أين تبحث وماذا تريد أن يعيده:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
هذا النهج يعني أن صيغك لن تنكسر لمجرد أن شخصًا ما يضيف عمودًا أو يعيد ترتيب الحقول في جدول. يعمل XLOOKUP أيضًا من اليسار إلى اليمين، ومن اليمين إلى اليسار، ومن أعلى إلى أسفل، مما يجعله أكثر مرونة عندما لا يتم وضع بياناتك في جدول بحث تقليدي وأنيق.
ترقية أخرى ذات مغزى هي أن XLOOKUP تتضمن وسيطة if_not_found، لذلك لم تعد بحاجة إلى IFERROR لتجنب رسائل #N/A القبيحة. تسلط هاتان الصيغتان الضوء على الفرق:
=IFERROR(VLOOKUP(B2, C2:E7, 4, TRUE)" ") =XLOOKUP(B2, C2:E7, D2:D7, "Not found")
تبحث الصيغة الأولى عن B2 في العمود الأول من النطاق C2:E7 وتعيد العمود الرابع، بينما تبحث الصيغة الثانية عن B2 في النطاق C2:E7 وتعيد القيمة المطابقة من D2:D7. تستخدم XLOOKUP المطابقة التامة افتراضيًا، مما يقلل من خطر سحب نتائج تقريبية عن طريق الخطأ من قائمة غير مرتبة، كما أنها تتعامل مع القيم المفقودة دون إجبارك على تضمين كل عملية بحث في IFERROR.
XMATCH هي نسخة أكثر كفاءة من MATCH
تحكم دقيق في أنواع المطابقة واتجاهات البحث
من المحتمل جدًا أنك لا تزال تستخدم MATCH بدافع الذاكرة العضلية. لطالما كانت رائعة في إيجاد موضع قيمة في نطاق، خاصةً عندما تجمع MATCH مع INDEX. المشكلة هي أن MATCH سهلة الاستخدام بشكل خاطئ. إذا نسيت تحديد match_type المطابقة التامة، فقد يعرض Excel أقرب نتيجة بناءً على افتراضات الفرز التي لا تنطبق فعليًا على بياناتك:
=MATCH(lookup_value, lookup_array, [match_type])
نظرًا لأن هذا النوع من الأخطاء غالبًا ما ينتج نتيجة تبدو معقولة، فقد يكون من الصعب اكتشافه وتصحيحه. مع XMATCH، تكون صيغك افتراضيًا مطابقة تامة، وهو ما يريده معظم الأشخاص على أي حال في الغالبية العظمى من الحالات:
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
إذا كنت تكتب =MATCH(25, A1:A3, 0) بدافع العادة، فمن المفيد التبديل إلى =XMATCH(25, A1:A3, 1) والاستفادة من هذا التحسين. بالإضافة إلى الإعدادات الافتراضية الأكثر أمانًا، تتيح لك XMATCH أيضًا التحكم في اتجاه البحث. يمكنه البحث من نهاية القائمة بدلاً من البداية فقط، وهو أمر مفيد بشكل خاص عندما تريد الإدخال الأخير أو آخر تكرار لقيمة ما دون إضافة أعمدة مساعدة:
=XMATCH(E3, C3:C100, 0, -1)
لا يزال MATCH قابلاً للاستخدام بالطبع، ولكن في هذا الوقت، سيكون الأمر كما لو كنت تختار شيئًا يعمل ببساطة بينما يوجد خيار أكثر كفاءة بجواره مباشرةً.
تستبدل صيغ FILTER و UNIQUE و SUM الديناميكية اختراقات SUMPRODUCT
أسهل في القراءة والتصحيح والصيانة بمرور الوقت
باستخدام SUMPRODUCT، يمكنك التعامل مع العمليات الحسابية متعددة المعايير، والمجاميع الموزونة، والمنطق القائم على المصفوفات في صيغة واحدة. هذه المرونة هي السبب في أنها أصبحت حلاً بديلاً شائعًا لسنوات. الجانب السلبي هو أن صيغ SUMPRODUCT غالبًا ما تبدو وكأنها تعليمات برمجية، مما يجعلها أكثر صعوبة في القراءة، وأكثر صعوبة في تصحيح الأخطاء، وأكثر صعوبة في الصيانة بمرور الوقت. الآن بعد أن أصبح لدى Excel دعم أصيل للمصفوفات الديناميكية، فإن العديد من الأشياء التي استخدمنا SUMPRODUCT من أجلها لم تعد تتطلب مثل هذه الصيغ الكثيفة والغامضة.
بفضل المصفوفات الديناميكية في Excel الحديث، يمكن لوظائف قياسية مثل SUM الآن التعامل مع حسابات المصفوفات مباشرةً. عندما تضع الطريقتين جنبًا إلى جنب، يكون النهج الأحدث عادةً أكثر وضوحًا وأسهل في الفهم:
| العملية الحسابية | SUMPRODUCT | SUM |
|---|---|---|
| الجمع البسيط | =SUMPRODUCT($A$20:$A$50) | =SUM($A$20:$A$50) |
| الجمع الموزون الكلاسيكي | =SUMPRODUCT(A2A5, C2:C5) | =SUM(A2:A5 * C2:C5) |
| الجمع متعدد المعايير | =SUMPRODUCT((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) | =SUM((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) |
علاوة على ذلك، مع الدوال الأحدث مثل FILTER و UNIQUE، بدلاً من إنشاء صيغة واحدة طويلة ويصعب قراءتها، يمكنك تقسيم المشكلة إلى خطوات أصغر وأكثر قابلية للقراءة. يمكنك تصفية البيانات التي تهتم بها، واستخراج القيم الفريدة إذا لزم الأمر، ثم تلخيص النتيجة. يبقى المنطق كما هو، ولكن يصبح الغرض من الصيغة أكثر وضوحًا لأي شخص يتعين عليه قراءتها أو صيانتها لاحقًا.
على سبيل المثال، إذا كانت لديك قائمة بالمبيعات في العمود B وأسماء المنتجات في العمود A، وكنت ترغب في جمع مبيعات منتج معين مثل “Apples”، فيمكنك كتابة إحدى هاتين الصيغتين:
=SUMPRODUCT((A2:A10="Apples")*(B2:B10)) =SUM(FILTER(B2:B10, A2:A10="Apples", 0))
في إصدار SUMPRODUCT، يقوم Excel بإنشاء مصفوفة من قيم TRUE و FALSE للتحقق من “Apples”، ويحولها إلى 1 و 0، ويضربها في قيم المبيعات. أي شيء ليس “Apple” يتحول إلى صفر، والذي يسقط من المجموع النهائي. في الصيغة الثانية، تقوم FILTER ببساطة بإرجاع قيم المبيعات حيث يكون المنتج “Apples”، وتضيف SUM إلى تلك القائمة الأصغر والأكثر نظافة بالفعل. النتيجة هي نفسها، ولكن النهج الثاني يجعل فهم الغرض من الصيغة أسهل بكثير في لمحة.
قد تجد SUMPRODUCT مناسبًا لأنه من السهل تعديل هيكل array1 و array2 الخاص به عند إجراء التجارب. ومع ذلك، بالنسبة لمجموعات البيانات الكبيرة التي تحتوي على مئات الآلاف أو الملايين من الصفوف، يمكن أن تكون أبطأ بشكل ملحوظ من البدائل الحديثة سهلة الانتشار. بالإضافة إلى الأداء، تمنحك الدوال الأحدث صيغًا أكثر نظافة وأسهل في التدقيق وأقل ترويعًا للشخص التالي الذي يفتح جدول البيانات الخاص بك.
IFS و SWITCH تقضي على صيغ IF المتداخلة
منطق أنظف بدون أقواس لا نهاية لها
من الناحية الفنية، تعمل عبارات IF المتداخلة، ولكنها سرعان ما تصبح كابوسًا يصعب قراءته أو صيانته، خاصةً عندما لم تكتبه بنفسك. مع IFS، بدلاً من تداخل IF داخل IF داخل IF، فإنك تسرد الشروط والنتائج بالترتيب، مما يجعل الصيغة تبدو أشبه بالمنطق العادي: إذا كان هذا، فذاك؛ إذا كان هذا الشيء الآخر، فشيء آخر. تذهب SWITCH خطوة أخرى إلى الأمام عندما تقارن قيمة واحدة مقابل احتمالات ثابتة متعددة؛ إنها أنظف وأكثر قابلية للقراءة، وتتضمن خيارًا افتراضيًا حتى لا تضطر إلى إضافة معالجة أخطاء إضافية في النهاية.
=IF(test1, result1, IF(test2, result2, IF(test3, result3, ...))) =IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...) =SWITCH(expression, value1, result1, value2, result2, ... [default])
على سبيل المثال، إذا كنت تريد تعيين درجات للنتائج، فيمكنك استخدام عبارات IF متداخلة، ولكن IFS تعبر عن نفس المنطق بشكل أكثر وضوحًا:
=IF(A1>90, “A”, IF(A1>80, “B”, IF(A1>70, “C”, “F”))) =IFS(A1>90, “A”, A1>80, “B”, A1>70, “C”, TRUE, “F”)
تعمل TRUE في نهاية صيغة IFS كشرط شامل، والذي يخبر Excel بما يجب إرجاعه إذا لم يتم استيفاء أي من الشروط السابقة. في هذه الحالة، فإن أي نتيجة أقل من 70 تسقط إلى “F” دون الحاجة إلى تداخل IF آخر. تتعامل SWITCH مع نمط مختلف قليلاً، مثل تعيين الرموز الرقمية للأقسام، وتتضمن وسيطة افتراضية في النهاية، لذلك لا تحتاج إلى حل TRUE:
=SWITCH(A1, 101, “Sales”, 102, “Marketing”, “Other”)
إذا كانت الخلية A1 تحتوي على 101، فسيعيد Excel قيمة “Sales”. إذا كانت تحتوي على 102، فسيعيد Excel قيمة “Marketing”. إذا كانت تحتوي على أي شيء آخر، فسيعيد Excel قيمة “Other”. سيظل المنطق واضحًا حتى مع نمو قائمة الحالات.
TEXTJOIN و CONCAT يحلان محل CONCATENATE تمامًا
ادمج النطاقات، وليس الخلايا الفردية فقط
CONCATENATE هي في الأساس قطعة أثرية في المتحف في هذه المرحلة. ستظل تراها في الكثير من جداول البيانات، ولكن تم استبدالها رسميًا، ولسبب وجيه. إنها لا تعمل بشكل جيد مع النطاقات الكاملة لأن عليك الإشارة إلى كل خلية يدويًا، وليس لديها مفهوم مدمج للمحدد.
CONCAT، البديل الحديث لها، يعمل بنفس الطريقة إلى حد كبير ولكنه يضيف دعمًا لمراجع النطاق الكامل:
=CONCAT(text1, ...)
بدلاً من تحديد كل خلية يدويًا، يمكنك كتابة صيغة كهذه والسماح لـ Excel بمعالجة الباقي:
=CONCAT(B2:C8)
عندما تريد تحديد فاصل، مثل فاصلة أو مسافة، وإخبار Excel بتجاهل الخلايا الفارغة، فإن TEXTJOIN هي الأداة الأفضل لهذه المهمة:
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
=TEXTJOIN(", ", TRUE, B2:C8)
الوسيطة الأولى هي الفاصل الخاص بك، والذي يمكن أن يكون أي شيء تحتاجه، والوسيطة الثانية تخبر Excel ما إذا كان يجب تخطي الخلايا الفارغة. يصبح هذا مفيدًا بشكل خاص عندما تقوم ببناء قوائم قابلة للقراءة، أو إنشاء استعلامات SQL، أو تجميع نص منسق من عمود أو نطاق من البيانات.
حان الوقت للتخلي عن عادات Excel القديمة
الخبر السار هو أن أياً من هذه الترقيات لا يتطلب منك إعادة تعلم Excel من الصفر. في معظم الحالات، تكون الوظائف الحديثة أسهل في الواقع من تلك التي تحل محلها؛ إنها تحتاج فقط إلى القليل من الوقت لتعتاد عليها.
إذا قمت بتحديث عادة واحدة فقط هذا الأسبوع، فابدأ بالوظيفة التي تستخدمها غالبًا واستبدلها بنظيرتها الحديثة. بعد ذلك، يمكنك محاولة إعادة كتابة قسم صغير من مصنف واحد باستخدام هذه التبديلات الخمسة والانتباه إلى مدى بساطة صيغك.
