صيغة الصفيف، وتسمّى أيضًا صيغة Ctrl Shift Enter (وتُعرف اختصارًا بـ CSE ) هي عبارة عن صيغة تقوم بتنفيذ عمليات حسابية على قيمة واحدة أو أكثر من بيانات المصفوفة وترجع نتيجة واحدة أو أكثر. والمصفوفة هي عبارة عن مجموعة بيانات مرتبة في صف، عمود، أو مجموعة من الصفوف والأعمدة.
تعتبر صيغة الصفيف وسيلة مفيدة لتلخيص البيانات، ويمكن باستخدامها اختصار أكثر من صيغة عادية في صيغة واحدة. سنتعلم في هذا الدرس كيفية إنشاء صيغة صفيف واستخدامها مع بعض الدوال، مثل Sum، Average، وغيرها، بالإضافة إلى كيفية تحريرها وحذفها.
إنشاء صيغة صفيف Array Formula
لتوضيح طريقة إنشاء صيغة الصفيف واستخدامها سنستخدم الجدول التالي كمثال. لدينا عدد الوحدات المباعة لكل منتج خلال فترة معينة بالإضافة إلى سعره، والمطلوب هو حساب مجموع المبيعات لكل المنتجات:
ربّما يختار الكثير من مستخدمي اكسل حساب مجموع المبيعات عن طريق إضافة عمود لإيجاد قيمة المبيعات لكل منتج ومن ثم جمعها للحصول على مجموع المبيعات الكلّي. لكن يمكن اختصار هذه الخطوات بكتابة صيغة واحدة.
أولًا، نحدد الخلية التي نريد إيجاد النتيجة فيها، وهي الخلية D11 في مثالنا، ونكتب علامة = ثم دالة الجمع SUM. بعد ذلك نفتح قوسًا هلاليًا ونحدد عدد الوحدات المباعة لكل المنتجات (أي نطاق الخلايا B2:B8)، وأخيرًا نحدد سعر الوحدة لكل منتج (أي نطاق الخلايا C2:C8) ونغلق القوس لتصبح الصيغة كالتالي:
نأتي الآن إلى الخطوة المهمة، وهي الضغط على المفاتيح Ctrl+ Shift+ Enter بدلا من المفتاح Enter فقط للحصول على النتيجة:
لاحظ ظهور قوسين متعرجين في شريط الصيغة في بداية ونهاية الصيغة بشكل تلقائي، وهما يشيران إلى صيغة الصفيف. لا تحاول إنشاء صيغة صفيف بإدخال الأقواس المتعرّجة يدويًا، فلن تحصل على نتيجة معينة، لأن اكسل سيتعامل مع المدخلات كنصوص وليس كصيغة.
ستعمل الصيغة كالتالي: سيتم ضرب كل قيمة في عمود "عدد الوحدات المباعة" بما يقابلها في عمود "السعر"، ومن ثم يتم جمع النتائج للحصول على المجموع الكلي للمبيعات.
يمكن أيضًا استخدام صيغة الصفيف لإرجاع نتائج متعددة. على سبيل المثال إذا رغبنا في إيجاد مبيعات كل منتج على حدة، نحدد نطاق الخلايا D2:D8 في عمود "المبيعات" ونكتب الصيغة التالية:
ولا ننس الضغط على المفاتيح Ctrl+ Shift+ Enterلإرجاع النتائج. لأنّ الضغط على المفتاح Enter فقط سيرجع نتيجة واحدة فقط في الخلية D2:
ليست دالة الجمع هي الوحيدة التي تستخدم في صيغة الصفيف، بل يمكن استخدام الدوال الأخرى مثل المتوسط AVERAGE، الأكبر MAX، الأصغر MIN أو أيّة دالة أخرى مضمّنة في اكسل.
على سبيل المثال، لحساب متوسط المبيعات لنفس الجدول، نكتب الصيغة التالية:
ثم نضغط على المفاتيح Ctrl+ Shift+ Enter لإيجاد النتيجة. وبنفس الطريقة ستعمل الصيغة على ضرب القيم في عمود "عدد الوحدات المباعة" بما يقابلها في عمود "السعر"، ومن ثم يتم إيجاد مجموع المبيعات وقسمته على عدد المنتجات:
وإذا أردنا إيجاد المنتج ذي المبيعات الأعلى، نحدد الخلية التي نريد إيجاد النتيجة فيها ثم نكتب الصيغة التالية:
نضغط على المفاتيح Ctrl+ Shift+ Enter وسيتم حساب مبيعات كل منتج ومن ثم إرجاع أعلى قيمة للمبيعات في خلية الناتج:
تحرير وحذف صيغة الصفيف
إنّ تحرير وحذف صيغ الصفيف يتطلب بعض الخطوات الخاصة، وخصوصًا إذا كانت تطبّق على خلايا متعددة وليست خلية مفردة.
لتحرير صيغة الصفيف في خلية مفردة، حدد الخلية، ثم اضغط على مفتاح F2 لتشغيل وضع التحرير، ثم عدّل الصيغة كما هو مطلوب، كأن يكون تقليص النطاق أو تمديده. على سبيل المثال، نريد تعديل صيغة مجموعة المبيعات لأنّنا قمنا بإضافة منتج ثامن إلى الجدول، لذلك سنحرر الصيغة ونحدد النطاقات B2:B9 وC2:C9 بدلا من B2:B8 وC2:C8، ثم نضغط على المفاتيح Ctrl+ Shift+ Enter:
أما إذا كانت صيغة الصفيف مطبقة على نطاق خلايا، كما فعلنا في إيجاد قيمة المبيعات لكل منتج على حدة، ففي هذه الحالة يجب أن تحدد نطاق الخلايا بأكمله، تضغط على F2، تجري التعديل المطلوب، ثم تضغط Ctrl+ Shift+ Enter:
لا يمكن حذف خلية مفردة تنتمي لنطاق خلايا يحتوي على صيغة صفيف، وعند محاولة ذلك بتحديد الخلية والضغط على مفتاح Delete سيُنبّهك اكسل إلى عدم إمكانية تغيير جزء من المصفوفة من خلال مربع حوار صغير:
فإذا رغبت في حذف خلية ضمن المصفوفة، يجب أن تقوم بحذف المصفوفة كلّها ثم إعادة كتابة صيغة الصفيف من جديد. والأمر نفسه ينطبق على نقل (قص ولصق) جزء من المصفوفة، حيث يظهر لك نفس التنبيه. لكن بإمكانك قص المصفوفة بأكملها ولصقها في مكان آخر. ولا تتغير مراجع الخلايا عند النقل بل ستبقى كما هي:
لصيغ الصفيف العديد من المزايا. فبالإضافة إلى كونها وسيلة لتلخيص البيانات، يمكن أن يجنّبك استخدامها الأخطاء التي يمكن أن تنتج من تعدد الصيغ وخصوصا في جداول البيانات الكبيرة، حيث يمكنك كتابة صيغة واحدة اختصارًا لعدة صيغ كما رأينا في حساب إجمالي المبيعات ومتوسط المبيعات. ولهذا الأمر فائدة أيضًا في تقليل حجم المصنف. فكلما قل عدد الصيغ نقص الحجم.
إذا كان لديك أي سؤال أو استفسار بخصوص صيغ الصفيف لا تتردد بطرحه في صندوق التعليقات.
تم التعديل في بواسطة يوغرطة بن علي
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.