دوال Excel الأساسية تعمل بشكل جيد للحسابات البسيطة، لكنها سرعان ما تصبح معقدة عند التعامل مع تحليل البيانات المعقدة. ينتهي بك الأمر بصيغ متداخلة يصعب قراءتها، وأعمدة مساعدة متعددة تفسد جدول البيانات، وصيغ يمكن أن تتعطل عند تغيير بياناتك. هنا يأتي دور صيغ المصفوفات في Excel.
تتيح لك صيغ المصفوفات إجراء حسابات عبر نطاقات كاملة من البيانات في صيغة واحدة. وبالتالي، يمكنك إجراء عمليات بحث سريعة كالبرق، وتصفية، وفرز بتعبير واحد قوي، بدلاً من كتابة صيغ منفصلة لكل صف أو عمود. إنها ليست جديدة على Excel، ولكن بعض الأشخاص يلتزمون بالطرق القديمة في فعل الأشياء بينما يمكن لهذه الوظائف أن تجعل عملهم أبسط وأكثر كفاءة.
روابط سريعة
5. XLOOKUP
تتفوق على VLOOKUP في كل مرة
XLOOKUP هي دالة البحث التي كان يجب أن تكون موجودة منذ البداية. على عكس VLOOKUP، التي تجبرك على عد الأعمدة وتبحث فقط إلى اليمين، تعمل XLOOKUP في أي اتجاه وتستخدم مراجع الأعمدة الفعلية. لديها الصيغة التالية:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
إليك ما تعنيه كل معلمة:
- lookup_value: القيمة المحددة التي تبحث عنها. يمكن أن يكون هذا رقم جزء، أو رمز منتج، أو أي معرف في مجموعة البيانات الخاصة بك.
- lookup_array: النطاق الذي يبحث فيه Excel عن lookup_value الخاص بك. عادة ما يكون عمودًا أو صفًا واحدًا يحتوي على معايير البحث الخاصة بك.
- return_array: النطاق الذي يحتوي على القيم التي تريد استردادها. يمكن أن يكون عمودًا واحدًا، أو أعمدة متعددة، أو حتى قسم جدول بأكمله.
- if_not_found (اختياري): نص أو قيمة مخصصة لعرضها عندما لا توجد مطابقة. إنها تقضي على أخطاء #N/A المزعجة وتتيح لك عرض “غير موجود” أو “تحقق من رقم الجزء” بدلاً من ذلك.
- match_mode (اختياري): يتحكم في نوع المطابقة. استخدم 0 للمطابقة التامة (افتراضي)، و -1 للمطابقة التامة أو الأصغر التالية، و 1 للمطابقة التامة أو الأكبر التالية، و 2 للمطابقة مع أحرف البدل.
- search_mode (اختياري): يحدد اتجاه البحث. أستخدم 1 للبحث من الأول إلى الأخير (افتراضي)، و -1 للبحث من الأخير إلى الأول، و 2 لإجراء بحث ثنائي على البيانات التي تم فرزها.
لنأخذ مثالاً لجدول بيانات جرد ميكانيكي. تبحث الصيغة التالية عن رقم الجزء “BRG-002” داخل نطاق معرفات الأجزاء وتعيد البيانات المقابلة. إذا لم يكن الجزء موجودًا، فإنه يعرض “الجزء غير موجود” بدلاً من الخطأ.
=XLOOKUP("BRG-002", A:A, A:H, "Part not found")
تتيح لك XLOOKUP استخلاص البيانات من أعمدة مختلفة دون الحاجة إلى حساب الأعمدة المرهق الموجود في VLOOKUP، مما يجعلها واحدة من أهم دوال Excel للعثور على البيانات بسرعة.
4. SUMPRODUCT
محطة توليد الطاقة للحسابات الشرطية
لا تقوم SUMPRODUCT بجمع الأرقام فحسب، بل تضرب المصفوفات ثم تجمع النتائج. وهذا يجعلها مفيدة للحسابات الشرطية المعقدة التي تتطلب أعمدة مساعدة متعددة.
لها الصيغة التالية:
=SUMPRODUCT(array1, [array2], [array3], ...)
هنا، array1 هو النطاق الأول من القيم المراد ضربها – عادةً عمود البيانات الأساسي الخاص بك، مثل الكميات أو التكاليف. و array2 هو نطاق ثانٍ اختياري للضرب، والذي غالبًا ما يحتوي على معايير أو منطق شرطي باستخدام عوامل المقارنة.
تصبح أكثر فائدة عندما نستخدم عوامل التشغيل المنطقية داخل المصفوفات. على سبيل المثال، عندما نكتب شروطًا مثل (المورد = “Siemens”)، يقوم Excel بتحويل نتائج TRUE/FALSE إلى 1/0، مما يسمح بإجراء العمليات الحسابية.
على سبيل المثال، تحسب الصيغة التالية القيمة الإجمالية للمخزون للأجزاء التي توفرها Siemens فقط. تضرب الصيغة الكميات في تكاليف الوحدة، ولكن فقط للصفوف التي يطابق فيها المورد المعايير.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
وبالمثل، تجد الصيغة التالية التكلفة الإجمالية لمخزون المحامل المتوفر منه كميات جيدة:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
يطبق شرطين في وقت واحد – يجب أن تكون الفئة “Bearings” ويجب أن تكون مستويات المخزون 15 وحدة أو أعلى، مما يساعدنا في تحديد فئات المحامل التي لديها تغطية مخزون كافية.
على عكس وظائف SUM التقليدية ذات المعايير المتعددة، لا تتطلب SUMPRODUCT هياكل متداخلة معقدة لأنها تعالج شروطًا متعددة في صيغة واحدة قابلة للقراءة. دوال SUM في Excel، مثل SUMIF و SUMIFS، ممتازة للجمع الشرطي البسيط، ولكن دالة SUMPRODUCT تتفوق عندما تحتاج إلى ضرب القيم قبل الجمع أو التعامل مع عمليات منطقية أكثر تعقيدًا.
3. FILTER (تصفية)
تجعل استخلاص البيانات الديناميكي أمرًا بسيطًا
تقوم FILTER باستخراج الصفوف من مجموعة البيانات الخاصة بك بناءً على الشروط التي تحددها. على عكس التصفية اليدوية، تُنشئ هذه الدالة نتائج ديناميكية يتم تحديثها تلقائيًا عند تغيير بيانات المصدر. صيغة FILTER هي كالتالي:
=FILTER(array, include, [if_empty])
إليك ما يتحكم فيه كل مُدخل:
- array (النطاق): النطاق الكامل للبيانات التي تريد تصفيتها. يتضمن جميع الأعمدة التي تريدها في نتائجك، وليس فقط عمود المعايير.
- include (تضمين): الشرط المنطقي الذي يحدد الصفوف المراد إرجاعها – يستخدم عوامل المقارنة لإنشاء مصفوفات TRUE/FALSE لكل صف.
- if_empty (إذا كان فارغًا) (اختياري): لعرض رسالة مخصصة عندما لا تستوفي أي صفوف معاييرك. يمنع أخطاء #CALC! ويعرض نصًا ذا معنى مثل “لم يتم العثور على نتائج مطابقة”.
تعمل الدالة عن طريق تقييم الشرط الخاص بك مقابل كل صف في النطاق. عندما يُرجع الشرط TRUE، يظهر هذا الصف بأكمله في النتائج التي تمت تصفيتها. فيما يلي مثال من جدول بيانات المخزون الميكانيكي:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
تستخرج هذه الصيغة جميع الصفوف حيث المورد هو “Timken” والفئة هي “Bearings”. العلامة النجمية (*) تنشئ شرط AND عن طريق ضرب المصفوفات المنطقية معًا.
عندما تضيف بيانات جديدة إلى نطاق المصدر الخاص بك، فإن استخدام دالة FILTER في Excel يكون منطقيًا أكثر من الفرز اليدوي والجداول المؤقتة لأن النتائج التي تمت تصفيتها يتم تحديثها تلقائيًا. هذا يجعلها مفيدة لإنشاء لوحات معلومات وتقارير مباشرة.
2. UNIQUE
استخراج القيم المميزة بدون تكرارات
تسحب UNIQUE القيم المميزة من نطاق البيانات الخاص بك وتتجنب التكرارات تلقائيًا. هذه الدالة مهمة إذا كنت ترغب في إنشاء قوائم منسدلة، وتحليل فئات البيانات، وبناء تقارير موجزة. الصيغة هي:
=UNIQUE(array, [by_col], [exactly_once])
إليك كيفية عمل كل مُدخل:
- array (النطاق): النطاق الذي يحتوي على البيانات التي تريد إزالة التكرارات منها—يمكن أن يكون عمودًا واحدًا، أو عدة أعمدة، أو قسمًا كاملاً من الجدول.
- by_col (اختياري): FALSE تقارن الصفوف لتحديد التفرد (افتراضي)، بينما TRUE تقارن الأعمدة. ومع ذلك، فإن معظم السيناريوهات تستخدم المقارنة الافتراضية للصفوف.
- exactly_once (اختياري): FALSE تُرجع جميع القيم الفريدة، بما في ذلك تلك التي تظهر عدة مرات (افتراضي)، و TRUE تُرجع فقط القيم التي تظهر مرة واحدة بالضبط في مجموعة البيانات.
تقوم دالة UNIQUE بتقييم كل صف أو قيمة في المصفوفة الخاصة بك، وتُرجع فقط أول ظهور لكل عنصر مميز. يطابق الترتيب تسلسل البيانات الأصلي. فيما يلي مثال:
=UNIQUE(G2:G22)
تستخرج هذه الصيغة جميع أسماء الموردين الفريدة من عمود المورد G وتنشئ قائمة نظيفة بدون تكرار. أستخدمها لإنشاء قوائم منسدلة للموردين أو تقارير موجزة.
يمكنك أيضًا استخدامه عبر الجدول بأكمله، كما هو موضح أدناه:
=UNIQUE(A2:F100)
تُرجع تركيبات فريدة عبر جميع الأعمدة (من A إلى F)، وتعرض سجلات المخزون المتميزة. إذا كان لجزأين قيم متطابقة في كل عمود، فسيظهر واحد فقط في النتائج.
عند العمل مع مجموعات بيانات كبيرة، تُزيل UNIQUE العملية الشاقة المتمثلة في الإزالة اليدوية للتكرارات. يتم تحديث النتائج الديناميكية عند وصول بيانات جديدة، وبما أن UNIQUE تنشئ مصفوفات انسكاب، فإن هذا النهج ينهي معاناة تغيير حجم الجداول عن طريق التوسع تلقائيًا لاستيعاب جميع القيم الفريدة. أستخدمها للحفاظ على قوائم مرجعية نظيفة وبناء نطاقات تحقق من صحة البيانات موثوقة.
1. SORT و SORTBY
رتّب بياناتك دون المساس بالأصل
تقوم دالتا SORT و SORTBY بتنظيم البيانات ديناميكيًا مع الحفاظ على المصدر سليمًا. تتعامل SORT مع الفرز الأساسي حسب موضع العمود، بينما تقوم SORTBY بالفرز بناءً على القيم الموجودة في أعمدة مختلفة – مما يمنحك مزيدًا من المرونة للترتيبات المعقدة.
تستخدم SORT هذا التركيب:
=SORT(array, [sort_index], [sort_order], [by_col])
إليك ما يتحكم فيه كل معلمة:
- array: نطاق البيانات الذي تريد فرزه—يشمل جميع الأعمدة التي يجب أن تظهر في النتائج التي تم فرزها.
- sort_index (اختياري): رقم العمود داخل المصفوفة المراد الفرز بناءً عليه. استخدم 1 للعمود الأول، و 2 للعمود الثاني، وما إلى ذلك (افتراضيًا 1).
- sort_order (اختياري): استخدم 1 للترتيب التصاعدي (افتراضي)، و -1 للترتيب التنازلي.
- by_col (اختياري): FALSE للفرز حسب الصفوف (افتراضي)، TRUE للفرز حسب الأعمدة—تستخدم معظم السيناريوهات فرز الصفوف.
تأخذ الدالة SORTBY الصيغة التالية:
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
تتضمن معاملاتها ما يلي:
- array: نطاق البيانات المراد فرزه—وهو مماثل للدالة SORT، ويحتوي على جميع الأعمدة التي تريدها في النتائج.
- by_array1: النطاق الذي يحتوي على القيم التي تحدد ترتيب الفرز—يمكن أن يكون أي عمود، حتى خارج نطاق المصفوفة الرئيسية.
- sort_order1 (اختياري): 1 للترتيب التصاعدي (افتراضي)، -1 للترتيب التنازلي.
- by_array2, sort_order2 (اختياري): معايير فرز إضافية للفرز متعدد المستويات.
بالنظر إلى مثال من جدول بيانات المخزون الميكانيكي، تتعامل هذه الدوال مع سيناريوهات الفرز الحقيقية:
=SORT(A2:H22, 4, -1)
يقوم هذا بفرز المخزون بأكمله حسب مستويات المخزون بترتيب تنازلي، مع إظهار العناصر ذات أعلى مخزون أولاً. تقوم الصيغة بالفرز حسب العمود 4 (مستويات المخزون) مع الحفاظ على جميع العلاقات بين الصفوف.
أنا أستخدم الدالة SORTBY بدلاً من SORT للحصول على تحكم أفضل في معايير الفرز ومستويات الفرز المتعددة. على سبيل المثال، تقوم الصيغة التالية بالفرز أولاً حسب الفئة أبجديًا، ثم حسب مستويات المخزون من الأعلى إلى الأدنى داخل كل فئة.
=SORTBY(A2:H22, C2:C22, 1, D2:D22, -1)
جداول بيانات منظمة، نتائج أذكى
تزيل صيغ المصفوفات الفوضى الناتجة عن الأعمدة المساعدة والدوال المتداخلة التي تجعل جداول البيانات صعبة الصيانة. تحصل على صيغ مفردة تتعامل مع عمليات متعددة، وتجعل المصنفات أنظف، وتعطي مظهرًا أكثر احترافية.
تتمثل إحدى المزايا البارزة في الوظائف الديناميكية، حيث يتم تحديث النتائج تلقائيًا عند تغيير البيانات المصدر. لذلك، لا مزيد من التحديثات اليدوية أو سلاسل الصيغ المكسورة، وتصبح جداول البيانات الخاصة بك أكثر موثوقية للتحليل المستمر.
تستمر مكتبة دوال المصفوفات في Excel في التوسع إلى ما بعد هذه الأدوات الأساسية. عندما أحتاج إلى دمج بيانات من مصادر متعددة، أستخدم الدالتين VSTACK و HSTACK لدمج النطاقات. تعمل هذه الدوال معًا لإنشاء مهام سير عمل قوية لمعالجة البيانات والتي كانت ستكون مستحيلة باستخدام الصيغ التقليدية.
