إرشادات وأمثلة حول صيغ الصفيف

الصفائف متعددة الخلايا وأحادية الخلية

يوضح هذا التمرين كيفية استخدام صيغ الصفيف متعددة الخلايا وأحادية الخلية لحساب مجموعة من أرقام المبيعات. تستخدم المجموعة الأولى من الخطوات صيغة متعددة الخلايا لحساب مجموعة من الإجماليات الفرعية. وتستخدم المجموعة الثانية صيغة أحادية الخلية لحساب الإجمالي الكلي.

• صيغة صفيف متعددة الخلايا
  

• هنا نقوم بحساب إجمالي مبيعات سيارات الكوبيه والسيدان لكل مندوب مبيعات عن طريق إدخال =F10:F19*G10:G19 في الخلية H10.
  عند الضغط على Enter، سترى النتائج تمتد لأسفل في الخلايا H10:H19. لاحظ أنه يتم تمييز نطاق المد بحد عند تحديد أي خلية داخل نطاق المد. قد تلاحظ أيضا أن الصيغ الموجودة في الخلايا H10:H19 باللون الرمادي. إنها موجودة كمرجع فقط، وبالتالي إذا كنت تريد ضبط الصيغة، فستحتاج إلى تحديد الخلية H10، حيث توجد الصيغة الرئيسية.

• صيغة صفيف أحادية الخلية
  
  في الخلية H20 من نموذج المصنف، اكتب =SUM(F10:F19*G10:G19) أو انسخه والصقه، ثم اضغط على مفتاح الإدخال Enter.
  في هذه الحالة، يضرب Excel القيم في الصفيف (نطاق الخلايا من F10 إلى G19)، ثم يستخدم الدالة SUM لجمع الإجماليات معا. إن الناتج هو الإجمالي الكلي بقيمة 1590000 ر. س. في المبيعات.
  يوضح هذا المثال مدى فعالية هذا النوع من الصيغ. لنفترض على سبيل المثال أن لديك 1000 صف من البيانات. يمكنك جمع جزء من هذه البيانات أو كلها بإنشاء صيغة صفيف في خلية واحدة بدلاً من سحب الصيغة إلى الأسفل عبر 1000 صف. لاحظ أيضا أن الصيغة أحادية الخلية في الخلية H20 مستقلة تماما عن الصيغة متعددة الخلايا (الصيغة في الخلايا من H10 إلى H19). وهذه ميزة أخرى من مزايا استخدام صيغ الصفيف ، وهي المرونة. يمكنك تغيير الصيغ الأخرى في العمود H دون التأثير على الصيغة في H20. قد يكون من الجيد أيضا أن يكون لديك إجماليات مستقلة مثل هذا، حيث يساعد ذلك في التحقق من دقة النتائج.

• توفر صيغ الصفيف الديناميكية أيضا المزايا التالية:

  • التناسق إذا نقرت فوق أية خلية من الخلايا بدءا من H10 إلى أسفل، فسترى الصيغة نفسها. يساعد هذا التناسق في ضمان الحصول على المزيد من الدقة.
  • السلامة لا يمكنك الكتابة فوق أي مكون من مكونات صيغة الصفيف متعددة الخلايا. على سبيل المثال، انقر فوق الخلية H11 واضغط على Delete. لن يغير Excel إخراج الصفيف. لتغييرها، يجب تحديد الخلية العلوية اليمنى في الصفيف أو الخلية H10.
  • أحجام ملفات أصغر يمكنك استخدام صيغة صفيف واحدة في أغلب الأحيان بدلا من استخدام العديد من الصيغ الوسيطة. على سبيل المثال، يستخدم مثال مبيعات السيارات صيغة صفيف واحدة لحساب النتائج في العمود E. إذا كنت قد استخدمت صيغا قياسية مثل =F10*G10، وF11*G11، وF12*G12، إلخ، لأنك استخدمت 11 صيغة مختلفة لحساب النتائج نفسها. هذه ليست مشكلة كبيرة، ولكن ماذا لو كان لديك آلاف الصفوف لمجموعها؟ ثم يمكن أن يحدث فرقا كبيرا.
  • الكفاءة يمكن أن تكون دالات الصفيف طريقة فعالة لإنشاء صيغ معقدة. صيغة الصفيف =SUM(F10:F19*G10:G19) هي نفسها كما يلي: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
  • انسكاب سيتم تلقائيا امتداد صيغ الصفيف الديناميكية في نطاق الإخراج. إذا كانت البيانات المصدر موجودة في جدول Excel، فسيغير حجم صيغ الصفيف الديناميكية تلقائيا عند إضافة البيانات أو إزالتها.
  • أخطاء #المد! الخطأ قدمت الصفائف الديناميكية الخطأ #SPILL!، الذي يشير إلى حظر نطاق المد المطلوب لسبب ما. عند حل الحظر، سيتم مد الصيغة تلقائيا.

إنشاء ثوابت صفيف أحادية أو ثنائية الأبعاد

إن ثوابت الصفيف مكوّن من مكونات صيغ الصفيف. ويمكنك إنشاء ثوابت الصفيف عبر إدخال قائمة من العناصر ثم إحاطتها يدوياً بقوسين كبيرين ({ })، كالتالي:

={1,2,3,4,5} أو ={"January","February","March"}

إذا قمت بفصل العناصر باستخدام الفواصل، فستنشئ صفيفاً أفقياً (صف). وإذا قمت بفصل العناصر باستخدام الفواصل المنقوطة، فستنشئ صفيفاً عمودياً (عمود). لإنشاء صفيف ثنائي الأبعاد، عليك تحديد العناصر في كل صف باستخدام فواصل، وتحديد كل صف بفواصل منقوطة.

تدرّبك الإجراءات التالية على إنشاء ثوابت أفقية وعمودية وثنائية الأبعاد. سنعرض أمثلة باستخدام الدالة SEQUENCE لإنشاء ثوابت الصفائف تلقائيا، بالإضافة إلى ثوابت الصفائف التي تم إدخالها يدويا.

• إنشاء ثابت أفقي
  استخدم المصنف من الأمثلة السابقة، أو أنشئ مصنفاً جديداً. حدد أي خلية فارغة وأدخل =SEQUENCE(1,5). تقوم الدالة SEQUENCE بإنشاء صفيف من 1 صف في 5 أعمدة تماما ك ={1,2,3,4,5}. يتم عرض النتيجة التالية:
  
• إنشاء ثابت عمودي
  حدد أي خلية فارغة بها مساحة تحتها، وأدخل =SEQUENCE(5) أو ={1; 2; 3; 4; 5}. يتم عرض النتيجة التالية:
  
• إنشاء ثابت ثنائي الأبعاد
  حدد أي خلية فارغة بها مساحة إلى يسارها وتحتها، وأدخل =SEQUENCE(3,4). تظهر أمامك النتيجة التالية:
  
  يمكنك أيضا إدخال: أو ={1,2,3,4; 5,6,7,8; 9,10,11,12}، ولكنك ستحتاج إلى الانتباه إلى المكان الذي تضع فيه الفواصل المنقوطة مقابل الفواصل.
  كما ترى، يوفر خيار SEQUENCE مزايا مهمة مقارنة بإدخال قيم ثابت الصفيف يدويا. فهو يوفر لك الوقت بشكل أساسي، ولكنه يساعد أيضا في تقليل الأخطاء الناتجة عن الإدخال يدويا. كما يسهل قراءته، لا سيما أنه قد يكون من الصعب تمييز الفواصل المنقوطة عن فواصل الفاصلة.

بناء جملة ثابت صفيف

فيما يلي مثال يستخدم ثوابت الصفيف كجزء من صيغة أكبر. في نموذج المصنف، انتقل إلى Constant في ورقة عمل صيغة ، أو أنشئ ورقة عمل جديدة.

في الخلية D9، أدخلنا =SEQUENCE(1,5,3,1)، ولكن يمكنك أيضا إدخال 3 و4 و5 و6 و7 في الخلايا A9:H9. لا يوجد شيء مميز في تحديد الرقم المحدد هذا ، لقد اخترنا شيئا آخر غير 1-5 للتمايز.

في الخلية E11، أدخل =SUM(D9:H9*SEQUENCE(1,5)), أو =SUM(D9:H9*{1,2,3,4,5}). ترجع الصيغ 85.

تقوم الدالة SEQUENCE ببناء ما يعادل ثابت {1,2,3,4,5}الصفيف. نظرا إلى أن Excel ينفذ العمليات على التعابير الموجودة داخل الأقواس أولا، فإن العنصرين التاليين اللذين يؤديان دورا هما قيم الخلايا في D9:H9 وعامل الضرب (*). عند هذه المرحلة، تضرب الصيغة القيم الموجودة في الصفيف المخزن بالقيم المناظرة في الثابت. وهو ما يكافئ:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5)، أو =SUM(3*1,4*2,5*3,6*4,7*5)

وأخيرا، تضيف الدالة SUM القيم، وترجع 85.

لتجنب استخدام الصفيف المخزن والاحتفاظ بالعملية بالكامل في الذاكرة، يمكنك استبداله بثابت صفيف آخر:

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)), أو =SUM({3,4,5,6,7}*{1,2,3,4,5})

العناصر التي يمكنك استخدامها في ثوابت الصفائف

• يمكن أن تحتوي ثوابت الصفائف على أرقام ونصوص وقيم منطقية (مثل TRUE وFALSE) وقيم خطأ مثل #N/A. يمكنك استخدام الأرقام بتنسيقات أعداد صحيحة وعشرية وعلمية. إذا قمت بتضمين نص، فعليك إحاطته بعلامتي اقتباس ("نص").
• لا يمكن أن تحتوي ثوابت الصفيف على صفائف أو صيغ أو دالات إضافية. بعبارات أخرى، لا يمكن أن تتضمن سوى نص أو أعداد مفصولة بفواصل أو فواصل منقوطة. ويعرض Excel رسالة تحذير عند قيامك بإدخال صيغة مثل {‎1,2,A1:D4} أو {1,2‎,SUM(Q2:Z8)‎}. ولا يمكن أن تتضمن القيم الرقمية علامات النسبة المئوية أو علامات الدولار أو الفواصل أو الأقواس.

تسمية ثوابت الصفيف

إن تسمية ثوابت الصفيف هي أحد أفضل الطرق لاستخدامها. فاستخدام الثوابت المسماة سيكون أكثر سهولة، ويمكن لهذه الثوابت إخفاء بعض التعقيدات التي تتضمنها صيغ الصفيف عن المستخدمين الآخرين. لتسمية ثابت صفيف واستخدامه في صيغة، قم بما يلي:

انتقل إلى الصيغ:>الأسماء> المعرفةتعريف اسم. في المربع "الاسم" ، اكتب "ربع السنة1". في المربع يشير إلى، أدخل الثابت التالي (تذكر كتابة الأقواس الكبيرة يدوياً):

{"يناير","فبراير","يناير"}=

سيبدو مربع الحوار الآن على الشكل التالي:

انقر فوق موافق، ثم حدد أي صف به ثلاث خلايا فارغة، وأدخل =Quarter1.

يتم عرض النتيجة التالية:

إذا كنت تريد أن تمتد النتائج عموديا بدلا من أفقيا، يمكنك استخدام =TRANSPOSE(Quarter1).

إذا كنت تريد عرض قائمة من 12 شهرا، كما قد تستخدمها عند إنشاء قائمة مالية، يمكنك إسناد واحدة إلى السنة الحالية باستخدام الدالة SEQUENCE. الشيء الأنيق في هذه الدالة هو أنه على الرغم من عرض الشهر فقط ، إلا أن هناك تاريخا صالحا وراءها يمكنك استخدامه في عمليات حسابية أخرى. ستجد هذه الأمثلة في ورقتي عمل مجموعة بيانات نموذج ثابت صفيف مسمىوسريع في المصنف المثال.

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"MMM")

تستخدم الدالة DATE لإنشاء تاريخ استنادا إلى السنة الحالية، وتنشئ الدالة SEQUENCE ثابت صفيف من 1 إلى 12 لشهر يناير حتى ديسمبر، ثم تقوم الدالة TEXT بتحويل تنسيق العرض إلى "mmm" (يناير، فبراير، مارس، إلخ.). إذا أردت عرض اسم الشهر الكامل، مثل "يناير"، يمكنك استخدام "mmmm".

عند استخدام ثابت مسمى كصيغة صفيف، تذكر أن تدخل علامة المساواة، كما في =Quarter1، وليس Quarter1 فقط. إذا لم تفعل ذلك، فسيفسر Excel الصفيف على أنه سلسلة نصية ولن تعمل الصيغة كما هو متوقع. أخيرا، تذكر أنه بإمكانك استخدام تركيبات من الدالات والنصوص والأرقام. كل هذا يتوقف على مدى إبداعك.

طريقة عمل ثوابت الصفيف

توضح الأمثلة التالية بعض الطرق التي يمكن بواسطتها استخدام ثوابت الصفيف في صيغ الصفيف. تستخدم بعض الأمثلة الدالة TRANSPOSE لتحويل الصفوف إلى أعمدة والعكس.

• ضرب كل عنصر في صفيف
  Enter =SEQUENCE(1,12)*2, or={1,2,3,4; 5,6,7,8; 9,10,11,12}*2
  يمكنك أيضا القسمة باستخدام (/)، والجمع مع (+)، والطرح ب (-).
• تطويق العناصر في صفيف
  Enter =SEQUENCE(1,12)^2, or ={1,2,3,4; 5,6,7,8; 9,10,11,12}^2
• البحث عن الجذر التربيعي للعناصر التربيعية في صفيف
  أدخل =SQRT(SEQUENCE(1,12)^2), أو =SQRT({1,2,3,4; 5,6,7,8; 9,10,11,12}^2)
• تبديل موضع صف أحادي الأبعاد
  Enter =TRANSPOSE(SEQUENCE(1,5)), أو =TRANSPOSE({1,2,3,4,5})
  على الرغم من إدخال ثابت صفيف أفقي، تحوّل دالة TRANSPOSE ثابت الصفيف إلى عمود.
• تبديل موضع عمود أحادي الأبعاد
  أدخل =TRANSPOSE(SEQUENCE(5,1)), أو =TRANSPOSE({1; 2; 3; 4; 5})
  على الرغم من إدخال ثابت صفيف عمودي، تحوّل دالة TRANSPOSE ثابت الصفيف إلى صف.
• تبديل موضع ثابت ثنائي الأبعاد
  Enter =TRANSPOSE(SEQUENCE(3,4)), أو =TRANSPOSE({1,2,3,4; 5,6,7,8; 9,10,11,12})
  تحول دالة TRANSPOSE كل صف إلى سلسلة من الأعمدة.

تشغيل صيغ الصفيف الأساسية

يوفر هذا المقطع أمثلة على صيغ الصفيف الأساسية.

• إنشاء صفيف من قيم موجودة
  يوضح المثال التالي كيفية استخدام صيغ الصفيف لإنشاء صفيف جديد من صفيف موجود.
  أدخل =SEQUENCE(3,6,10,10), أو ={10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}
  تأكد من كتابة { (قوس الفتح) قبل كتابة 10، و} (قوس الإغلاق) بعد كتابة 180، لأنك تقوم بإنشاء صفيف من الأرقام.
  بعد ذلك، أدخل =D9# أو =D9:I11 في خلية فارغة. يظهر صفيف خلايا 3 x 6 بنفس القيم التي تراها في D9:D11. علامة # تسمى عامل تشغيل النطاق الممد، وهي طريقة Excel في الإشارة إلى نطاق الصفيف بأكمله بدلا من الاضطرار إلى كتابته.
  

• إنشاء ثابت صفيف من قيم موجودة
  يمكنك أخذ نتائج صيغة الصفيف الممد وتحويلها إلى أجزاء مكوناتها. حدد الخلية D9، ثم اضغط على F2 للتبديل إلى وضع التحرير. بعد ذلك، اضغط على F9 لتحويل مراجع الخلايا إلى قيم، والتي يقوم Excel بعد ذلك بتحويلها إلى ثابت صفيف. عند الضغط على مفتاح الإدخال Enter، يجب أن تكون الصيغة =D9# الآن ={10,20,30; 40,50,60; 70,80,90}.

• حساب عدد الأحرف في نطاق خلايا
  يوضح المثال التالي كيفية حساب عدد الأحرف في نطاق خلايا. يشمل ذلك المسافات.
  
  =SUM(LEN(C9:C13))
  في هذه الحالة، ترجع الدالة LEN طول كل سلسة نصية في كل خلية من الخلايا في النطاق. بعد ذلك، تقوم الدالة SUM بجمع تلك القيم معا وتعرض النتيجة (66). إذا أردت الحصول على متوسط عدد الأحرف، يمكنك استخدام:
  =AVERAGE(LEN(C9:C13))

• محتويات أطول خلية في النطاق C9:C13
  =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)
  تعمل هذه الصيغة فقط عندما يتضمن نطاق بيانات عموداً واحداً من الخلايا.
  فلنلقِ نظرة عن كثب على الصيغة، بدءاً من العناصر الداخلية وبالاتجاه نحو الخارج. ترجع الدالة LEN طول كل عنصر من العناصر في نطاق الخلايا D2:D6. تحسب الدالة MAX القيمة الكبرى بين هذه العناصر، وهي تتطابق مع أطول سلسلة نصية، أي تلك الموجودة في الخلية D3.
  هنا تصبح الأمور معقدة بعض الشيء. تحسب الدالة MATCH الإزاحة (الموضع النسبي) للخلية التي تحتوي على أطول سلسلة نصية. يتطلب إجراء ذلك ثلاث وسيطات: قيمة البحث وصفيف البحث ونوع المطابقة. تبحث الدالة MATCH في صفيف البحث عن قيمة البحث المُحددة. في هذه الحالة، تكون قيمة البحث أطول سلسلة نصية:
  MAX(LEN(C9:C13)
  وتتواجد السلسلة في الصفيف التالي:
  LEN(C9:C13)
  إن وسيطة نوع المطابقة في هذه الحالة هي 0. يمكن أن يكون نوع المطابقة 1 أو 0 أو 1-.

  • 1 - إرجاع القيمة الكبرى التي تكون أقل من أو تساوي قيمة البحث
  • 0 - إرجاع أول قيمة مساوية تماما لقيمة البحث
  • -1 - إرجاع أصغر قيمة أكبر من قيمة البحث المحددة أو مساوية لها
  • إذا قمت بحذف نوع المطابقة، فسيفترض Excel أنها 1.

  أخيرا، تأخذ الدالة INDEX الوسيطات التالية: صفيف ورقم صف وعمود ضمن هذا الصفيف. يوفر نطاق الخلايا C9:C13 الصفيف، وتوفر الدالة MATCH عنوان الخلية، وتبين الوسيطة الأخيرة (1) أن القيمة تأتي من العمود الأول في الصفيف.
  إذا أردت الحصول على محتويات أصغر سلسلة نصية، يمكنك استبدال الدالة MAX في المثال أعلاه ب MIN.

• البحث عن قيم n الصغرى في النطاق
  يوضح هذا المثال كيفية البحث عن القيم الصغرى الثلاث في نطاق من الخلايا، حيث تم إنشاء صفيف من البيانات النموذجية في الخلايا B9:B18 باستخدام: =INT(RANDARRAY(10,1)*100). تجدر الإشارة إلى أن RANDARRAY عبارة عن دالة متغيرة، وبالتالي ستحصل على مجموعة جديدة من الأرقام العشوائية في كل مرة يقوم فيها Excel بالحساب.
  
  أدخل =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1; 2; 3})
  تستخدم هذه الصيغة ثابت صفيف لتقييم الدالة SMALL ثلاث مرات وإرجاع أصغر 3 أعضاء في الصفيف المضمن في الخلايا B9:B18، حيث 3 عبارة عن قيمة متغير في الخلية D9. للبحث عن المزيد من القيم، يمكنك زيادة القيمة في الدالة SEQUENCE، أو إضافة المزيد من الوسيطات إلى الثابت. يمكنك أيضاً استخدام دالات إضافية مع هذه الصيغة، مثل SUM أو AVERAGE. على سبيل المثال:
  =SUM(SMALL(B9#,SEQUENCE(D9))
  =AVERAGE(SMALL(B9#,SEQUENCE(D9))

• البحث عن قيم n الكبرى في النطاق
  للبحث عن القيم الكبرى في نطاق، يمكنك استبدال الدالة SMALL بالدالة LARGE. بالإضافة إلى ذلك، يستخدم المثال التالي الدالتين ROW وINDIRECT.
  Enter =LARGE(B9#,ROW(INDIRECT("1:3"))), أو =LARGE(B9:B18,ROW(INDIRECT("1:3")))
  في هذه المرحلة، قد تفيدك معرفة القليل حول الدالتين ROW وINDIRECT. يمكنك استخدام الدالة ROW لإنشاء صفيف يتألف من أعداد صحيحة متتالية. على سبيل المثال، حدد فارغا وأدخل:
  =ROW(1:10)
  تنشئ الصيغة عموداً يتألف من 10 أعداد صحيحة متتالية. لرؤية المشكلة المحتملة، أدرج صفاً فوق النطاق الذي يحتوي على صيغة الصفيف (أي فوق الصف 1). يعدل Excel مراجع الصفوف، وتنشئ الصيغة الآن أعدادا صحيحة من 2 إلى 11. ولحل هذه المشكلة، أضِف الدالة INDIRECT إلى الصيغة:
  =ROW(INDIRECT("1:10"))
  تستخدم الدالة INDIRECT السلاسل النصية كوسيطات لها (وهذا هو سبب إحاطة النطاق 1:10 بعلامتي اقتباس). لا يقوم Excel بتعديل القيم النصية عند إدراج صفوف أو نقل صيغة الصفيف بدلاً من ذلك. كنتيجة لذلك، تقوم الدالة ROW دائماً بإنشاء صفيف من الأعداد الصحيحة التي تريدها. يمكنك استخدام الدالة SEQUENCE بسهولة:
  =SEQUENCE(10)
  لنفحص الصيغة التي استخدمتها سابقا — =LARGE(B9#,ROW(INDIRECT("1:3"))) — بدءا من الأقواس الداخلية وبالاتجاه نحو الخارج: ترجع الدالة INDIRECT مجموعة من القيم النصية، وفي هذه الحالة تكون القيم من 1 إلى 3. تقوم الدالة ROW بدورها بإنشاء صفيف أعمدة يتألف من ثلاثة خلايا. تستخدم الدالة LARGE القيم الموجودة في نطاق الخلايا B9:B18، ويتم تقييمها ثلاث مرات، مرة واحدة لكل مرجع يتم إرجاعه بواسطة الدالة ROW. إذا كنت تريد البحث عن المزيد من القيم، فيمكنك إضافة نطاق خلايا أكبر إلى الدالة INDIRECT. أخيرا، كما هو الحال في الأمثلة الصغيرة، يمكنك استخدام هذه الصيغة مع دالات أخرى، مثل SUM وAVERAGE.

التعامل مع الأخطاء

• جمع نطاق يحتوي على قيم خطأ
  لا تعمل الدالة SUM في Excel عندما تحاول جمع نطاق يحتوي على قيمة خطأ، مثل #VALUE! أو #N/A. يوضح لك هذا المثال كيفية جمع القيم الموجودة في نطاق يسمى "بيانات" يحتوي على أخطاء:
  
• ‎=SUM(IF(ISERROR(Data),"",Data))‎
  تنشئ الصيغة صفيفاً جديداً يحتوي على القيم الأصلية من دون أي قيم أخطاء. بدءاً من الدالات الداخلية وباتجاه الخارج، تبحث الدالة ISERROR في نطاق الخلايا (بيانات) عن أخطاء. وتُرجع الدالة IF قيمة معينة إذا تم تقييم الشرط الذي حددته إلى TRUE وتُرجع قيمة أخرى إذ تم تقييم الشرط إلى FALSE. في هذه الحالة، تُرجع الدالة سلاسل فارغة ("") لكافة قيم الخطأ لأنه تم تقييمها إلى TRUE، وكذلك تُرجع القيم الباقية من النطاق (بيانات) لأنه تم تقييمها إلى FALSE، مما يعني عدم احتوائها على قيم خطأ. تحسب الدالة SUM حينئذٍ الإجمالي بالنسبة إلى الصفيف الذي تمت تصفيته.
• حساب عدد قيم الأخطاء في النطاق
  يشبه هذا المثال الصيغة السابقة، ولكنه يرجع عدد قيم الخطأ في نطاق اسمه "بيانات" بدلا من تصفيتها:
  ‎=SUM(IF(ISERROR(Data),1,0))‎
  تنشئ هذه الصيغة صفيفاً يحتوي على القيمة 1 للخلايا التي تحتوي على أخطاء والقيمة 0 للخلايا التي لا تحتوي على أخطاء. يمكنك تبسيط الصيغة والحصول على النتيجة نفسها عبر إزالة الوسيطة الثالثة الخاصة بالدالة IF، على الشكل الآتي:
  =SUM(IF(ISERROR(DATA),1))
  إذا لم تحدد الوسيطة، فتُرجع الدالة IF القيمة FALSE إذا كانت الخلية لا تحتوي على قيمة خطأ. ويمكنك أيضاً تبسيط الصيغة أكثر:
  =SUM(IF(ISERROR(Data)*1))
  تعمل هذه النسخة لأن TRUE*1=1 وFALSE*1=0.

جمع القيم حسب الشروط

قد تحتاج إلى جمع القيم بالاستناد إلى شروط.

على سبيل المثال، تجمع صيغة الصفيف هذه الأعداد الصحيحة الموجبة فقط في نطاق يسمى Sales، وهو يمثل الخلايا E9:E24 في المثال أعلاه:

=SUM(IF(Sales>0,Sales))

تنشئ الدالة IF صفيفا من القيم الموجبة والقيمة الخطأ. وتتجاهل الدالة SUM بشكل أساسي قيم الخطأ 0+0=0. يمكن أن يحتوي نطاق الخلايا الذي تستخدمه في هذه الصيغة على أي عدد من الصفوف والأعمدة.

يمكنك أيضاً جمع القيم التي تحقق أكثر من شرط واحد. على سبيل المثال، تحسب صيغة الصفيف التالية القيم الأكبر من 0 وأقل من 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

تذكر أن هذه الصيغة ترجع خطأ إذا كان النطاق يحتوي على خلية غير رقمية واحدة أو أكثر.

يمكنك أيضاً إنشاء صيغ صفيف تستخدم نوع الشرط OR. على سبيل المثال، يمكنك جمع القيم الأكبر من 0 أو أقل من 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

لا يمكنك استخدام الدالتين AND وOR في صيغ الصفيف مباشرة لأن هذه الدالات ترجع نتيجة فردية، إما TRUE أو FALSE، وتتطلب دالات الصفيف وجود صفائف للنتائج. يمكنك حل هذه المشكلة من خلال استخدام المنطق المبين في الصيغة السابقة. بعبارات أخرى، تقوم بإجراء عمليات حسابية، مثل الجمع أو الضرب على قيم تحقق الشرط OR أو AND.

يبين هذا المثال كيفية إزالة الأصفار من نطاق للحصول على متوسط القيم في النطاق. تستخدم الصيغة نطاق بيانات يسمى "مبيعات":

=AVERAGE(IF(Sales<>0,Sales))

تعمل الدالة IF على إنشاء صفيف من القيم لا يساوي 0 ثم تقوم بتمرير هذه القيم إلى الدالة AVERAGE.

حساب عدد الاختلافات بين نطاقين من الخلايا

تعمل صيغة الصفيف هذه على مقارنة القيم الموجودة في نطاقين من الخلايا تمت تسميتهما MyData وYourData وإرجاع عدد الاختلافات بين النطاقين. إذا كانت محتويات النطاقين متطابقة، فترجع الصيغة 0. لاستخدام هذه الصيغة، يجب أن تكون نطاقات الخلايا بالحجم نفسه والبعد نفسه. على سبيل المثال، إذا كان النطاق MyData يتألف من 3 صفوف و5 أعمدة، فيجب أن يتألف أيضا النطاق YourData من 3 صفوف و5 أعمدة:

=SUM(IF(MyData=YourData,0,1))

تعمل الصيغة على إنشاء صفيف جديد بالحجم نفسه لحجم النطاقات التي تقوم بمقارنتها. وتعمل الدالة IF على تعبئة الصفيف بالقيمة 0 والقيمة 1 (0 للخلايا غير المتطابقة و1 للخلايا المتطابقة). بعد ذلك تقوم الدالة SUM بإرجاع مجموع القيم في الصفيف.

يمكنك تبسيط الصيغة على النحو التالي:

=SUM(1*(MyData<>YourData))

تماماً مثل الصيغة التي تحسب عدد قيم الخطأ في النطاق، تعمل هذه الصيغة لأن TRUE*1=1 وFALSE*1=0.

ترجع صيغة الصفيف هذه رقم الصف الخاص بالقيمة الأعلى في نطاق بعمود واحد يسمى "بيانات":

‎=MIN(IF(Data=MAX(Data),ROW(Data),""))‎

تنشئ الدالة IF صفيفاً جديداً يتطابق مع النطاق المسمى "بيانات". إذا كانت إحدى الخلايا المتطابقة تحتوي على القيمة الأعلى في النطاق، فسيحتوي الصفيف على رقم الصف. في الحالات الأخرى، سيحتوي الصفيف على سلسلة فارغة (""). تستخدم الدالة MIN الصفيف الجديد باعتباره الوسيطة الثانية له وترجع القيمة الأصغر التي تتطابق مع رقم صف القيمة الأعلى في النطاق "بيانات". إذا كان النطاق المسمى "بيانات" يحتوي على قيم أعلى متطابقة، فتُرجع الصيغة صف القيمة الأولى.

إذا كنت تريد إرجاع عنوان الخلية الفعلي لإحدى القيم العليا، فاستخدم الصيغة التالية:

‎=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))‎

ستجد أمثلة مماثلة في المصنف النموذجي في ورقة العمل "الاختلافات بين مجموعات البيانات ".

صيغ الصفيف متعددة الخلايا وأحادية الخلية

يوضح هذا التمرين كيفية استخدام صيغ الصفيف متعددة الخلايا وأحادية الخلية لحساب مجموعة من أرقام المبيعات. تستخدم المجموعة الأولى من الخطوات صيغة متعددة الخلايا لحساب مجموعة من الإجماليات الفرعية. وتستخدم المجموعة الثانية صيغة أحادية الخلية لحساب الإجمالي الكلي.

• صيغة صفيف متعددة الخلايا

انسخ الجدول بكامله ثم الصقه في الخلية A1 في ورقة عمل فارغة.

1. لعرض "إجمالي المبيعات" للسيارات الكوبيه والسيدان لكل مندوب مبيعات، حدد الخلايا E2:E11، أدخل الصيغة =C2:C11*D2:D11، ثم اضغط على Ctrl+Shift+Enter.
2. لمشاهدة الإجمالي الكلي لجميع المبيعات، حدد الخلية F11، أدخل =SUM(C2:C11*D2:D11)، ثم اضغط على Ctrl+Shift+Enter.

عند الضغط على Ctrl+Shift+Enter، يقوم Excel بإحاطة الصيغة بقوسين كبيرين ({ }) وإدراج مثيل للصيغة في كل خلية من النطاق المحدد. يتم هذا الإجراء بسرعة، لذا سترى في العمود E مقدار المبيعات الإجمالية لكل نوع من أنواع السيارات لكل مندوب مبيعات. إذا قمت بتحديد E2، ثم E3، وE4، وهكذا، سترى أن الصيغة نفسها تظهر على الشكل التالي: ‎{=C2:C11*D2:D11}‎. 

• إنشاء صيغة صفيف أحادية الخلية

في الخلية D13 من المصنف، اكتب الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:

‎=SUM(C2:C11*D2:D11)‎

في هذه الحالة، يضرب Excel القيم في الصفيف (نطاق الخلايا من C2 إلى D11) ثم يستخدم الدالة SUM لجمع الإجماليات معا. إن الناتج هو الإجمالي الكلي بقيمة 1590000 ر. س. في المبيعات. يوضح هذا المثال مدى فعالية هذا النوع من الصيغ. لنفترض على سبيل المثال أن لديك 1000 صف من البيانات. يمكنك جمع جزء من هذه البيانات أو كلها بإنشاء صيغة صفيف في خلية واحدة بدلاً من سحب الصيغة إلى الأسفل عبر 1000 صف.

لاحظ أيضا أن الصيغة أحادية الخلية في الخلية D13 مستقلة تماما عن الصيغة متعددة الخلايا (الصيغة في الخلايا من E2 إلى E11). وهذه ميزة أخرى من مزايا استخدام صيغ الصفيف ، وهي المرونة. إذ يمكنك تغيير الصيغ في العمود E أو حذف العمود بكامله، بدون التأثير في الصيغة في D13.

توفر صيغ الصفيف أيضاً المزايا التالية:

• التناسق إذا نقرت فوق أية خلية من الخلايا بدءا من E2 إلى أسفل، فسترى الصيغة نفسها. يساعد هذا التناسق في ضمان الحصول على المزيد من الدقة.
• السلامة لا يمكنك الكتابة فوق أي مكون من مكونات صيغة الصفيف متعددة الخلايا. على سبيل المثال، انقر فوق الخلية E3 واضغط على Delete. عليك أن تحدد نطاق الخلايا بالكامل (من E2 إلى E11) وتغير صيغة الصفيف بالكامل، أو تترك الصفيف كما هو. عليك الضغط على Ctrl+Shift+Enter لتأكيد أي تغيير في الصيغة، وذلك كإجراء أمان إضافي.
• أحجام ملفات أصغر يمكنك استخدام صيغة صفيف واحدة في أغلب الأحيان بدلا من استخدام العديد من الصيغ الوسيطة. على سبيل المثال، يستخدم المصنف صيغة صفيف واحدة لحساب النتائج في العمود E. إذا كنت قد استخدمت صيغاً قياسية (مثل ‎=C2*D2)، فهذا يعني أنك استخدمت 11 صيغة مختلفة لحساب النتائج نفسها.

بناء جملة صيغة صفيف

بشكل عام، تستخدم صيغ الصفيف بناء جملة صيغة قياسية. وتبدأ جميعها بعلامة المساواة (=)، ويمكنك استخدام معظم دالات Excel المضمنة في صيغ الصفيف. إن الاختلاف الرئيسي عند استخدام صيغة صفيف هو أنه يجب الضغط على Ctrl+Shift+Enter لإدخال الصيغة. وعند القيام بذلك، يحيط Excel صيغة الصفيف بقوسين كبيرين -إذا كتبت القوسين يدوياً، فيتم تحويل الصيغة إلى سلسلة نصية، ولن تعمل.

يمكن أن تكون دالات الصفيف طريقة فعالة لإنشاء صيغ معقدة. إن صيغة الصفيف ‎=SUM(C2:C11*D2:D11)‎ هي نفسها هذه الصيغة: ‎=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)‎.

إدخال صيغ الصفيف وتغييرها

عندما تستخدم الصيغ المتعددة الخلايا، تذكر أيضاً:

• حدد نطاق الخلايا الذي سيحتوي على النتائج قبل إدخال الصيغة. لقد قمت بذلك عندما أنشأت صيغة صفيف متعددة الخلايا عندما حددت الخلايا من E2 إلى E11.
• لا يمكنك تغيير محتويات خلية واحدة في صيغة صفيف. لتجربة ذلك، حدد الخلية E3 في المصنف واضغط على Delete. يعرض Excel رسالة تخبرك بأنه لا يمكنك تغيير أي جزء من صفيف.
• يمكنك نقل صيغة صفيف بالكامل أو حذفها، ولكن لا يمكنك نقل جزء منها أو حذفه. بعبارات أخرى، لتقليص صيغة صفيف، احذف الصيغة الموجودة أولاً ثم ابدأ من جديد.
• لحذف صيغة صفيف، حدد نطاق الصيغة بالكامل (على سبيل المثال، E2:E11)، ثم اضغط على Delete.
• لا يمكنك إدراج خلايا فارغة في صيغة صفيف متعددة الخلايا أو حذفها منها.

توسيع صيغة صفيف

قد تحتاج أحياناً إلى توسيع صيغة صفيف. حدد الخلية الأولى في نطاق الصفيف الموجود، ثم تابع حتى تقوم بتحديد النطاق بأكمله الذي تريد توسيع الصيغة إليه. اضغط على F2 لتحرير الصيغة، ثم اضغط على CTRL+SHIFT+ENTER لتأكيد الصيغة بعد ضبط نطاق الصيغة. يكمن المفتاح في تحديد النطاق بأكمله، بدءا من الخلية العلوية اليمنى في الصفيف. الخلية العلوية اليمنى هي التي يتم تحريرها.

سيئات استخدام صيغ الصفيف

إن صيغ الصفيف مفيدة جداً، ولكن لديها بعض السيئات

• قد تنسى أحيانا الضغط على Ctrl+Shift+Enter. وقد يحدث هذا الأمر مع مستخدمي Excel الأكثر تمرساً باستخدام هذا البرنامج. تذكر أن تضغط على تركيبة المفاتيح هذه عند إدخال صيغة صفيف أو تحريرها.
• قد لا يفهم مستخدمو المصنف الآخرون صيغك. عمليا، لا يتم توضيح صيغ الصفيف بشكل عام في ورقة العمل. لذلك، إذا احتاج أشخاص آخرون إلى تعديل مصنفاتك، فعليك إما تجنب استخدام صيغ الصفيف أو التأكد من أن هؤلاء الأشخاص مطلعون على صيغ الصفيف ويعرفون كيفية تغييرها، عند الضرورة.
• قد تؤدي صيغ الصفيف الكبيرة إلى إبطاء العمليات الحسابية ويتوقف ذلك على سرعة المعالجة وذاكرة الكمبيوتر.

التعرف على ثوابت الصفائف

إن ثوابت الصفيف مكوّن من مكونات صيغ الصفيف. يمكنك إنشاء ثوابت الصفيف عبر إدخال قائمة من العناصر ثم إحاطتها يدويا بقوسين كبيرين ({ })، كالتالي:

={1,2,3,4,5}

أصبحت تعلم الآن أن عليك الضغط على Ctrl+Shift+Enter عند إنشاء صيغ صفيف. وبما أن ثوابت الصفيف عبارة عن مكون من مكونات صيغ الصفيف، فعليك إحاطة الثوابت بقوسين كبيرين عبر كتابتهما يدوياً. بعد ذلك، يمكنك استخدام Ctrl+Shift+Enter لإدخال الصيغة بالكامل.

إذا قمت بفصل العناصر باستخدام الفواصل، فستنشئ صفيفاً أفقياً (صف). وإذا قمت بفصل العناصر باستخدام الفواصل المنقوطة، فستنشئ صفيفاً عمودياً (عمود). لإنشاء صفيف ثنائي الأبعاد، عليك تحديد العناصر في كل صف باستخدام الفواصل، وكذلك تحديد كل صف باستخدام الفواصل المنقوطة.

إليك صفيف في صف واحد: {1,2,3,4}. وإليك صفيف في عمود واحد: {4;3;2;1}. وإليك كذلك صفيف من صفين وأربعة أعمدة: {5,6,7,8;1,2,3,4}. في الصفيف المؤلف من صفين، إن الصف الأول هو 1، 2، 3، و4 والصف الثاني هو 5، 6، 7، و8. تفصل فاصلة منقوطة واحدة بين الصفين، بين 4 و5.

كما هو الحال بالنسبة إلى صيغ الصفيف، يمكنك استخدام ثوابت الصفيف مع معظم الدالات المضمنة التي يوفرها Excel. تشرح المقاطع التالية كيفية إنشاء كل نوع من الثوابت وكيفية استخدام هذه الثوابت مع الدالات في Excel.

إنشاء ثوابت أحادية الأبعاد وثنائية الأبعاد

تدرّبك الإجراءات التالية على إنشاء ثوابت أفقية وعمودية وثنائية الأبعاد.

إنشاء ثابت أفقي

1. في ورقة عمل فارغة، حدد الخلايا من A1 إلى E1.
2. في شريط الصيغة، أدخل الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   ={1,2,3,4,5}
   في هذه الحالة، يجب كتابة قوسي الفتح والإغلاق الكبيرين ({ })، وسيضيف Excel المجموعة الثانية نيابة عنك.
   يتم عرض النتيجة التالية.
   

إنشاء ثابت عمودي

1. في المصنف، حدد عموداً يتألف من خمس خلايا.
2. في شريط الصيغة، أدخل الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   ={1;2;3;4;5}
   يتم عرض النتيجة التالية.
   

إنشاء ثابت ثنائي الأبعاد

1. في المصنف، حدد كتلة خلايا بعرض أربعة أعمدة وارتفاع ثلاثة صفوف.
2. في شريط الصيغة، أدخل الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   ‎={1,2,3,4;5,6,7,8;9,10,11,12}‎
   تظهر أمامك النتيجة التالية:
   

استخدام الثوابت في الصيغ

إليك مثال بسيط على استخدام الثوابت:

1. في نموذج المصنف، أنشئ ورقة عمل جديدة.
2. في الخلية A1، اكتب 3، ثم اكتب 4 في B1، و5 في C1 و6 في D1 و7 في E1.
3. في الخلية A3، اكتب الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   =SUM(A1:E1*{1,2,3,4,5})
   لاحظ أن Excel يحيط الثابت بمجموعة أخرى من الأقواس الكبيرة، لأنك أدخلته كصيغة صفيف.
   
   تظهر القيمة 85 في الخلية A3.

يشرح المقطع التالي طريقة عمل الصيغة.

بناء جملة ثابت صفيف

تحتوي الصيغة التي استخدمتها للتو على أجزاء متعددة.

1. الدالة

2. صفيف مخزن

3. عامل التشغيل

4. ثابت صفيف

إن العنصر الأخير داخل الأقواس هو ثابت الصفيف: {1,2,3,4,5}. تذكر أن Excel لا يحيط ثوابت الصفيف بأقواس كبيرة؛ عليك أنت إدخالها. تذكر أيضا أنه بعد إضافة ثابت إلى صيغة صفيف، يجب الضغط على Ctrl+Shift+Enter لإدخال الصيغة.

نظراً إلى أن Excel ينفّذ العمليات على التعابير الموجودة داخل الأقواس أولاً، فإن العنصرين التاليين اللذين يؤديان دوراً هما القيم المخزنة في المصنف (A1:E1) وعامل التشغيل. عند هذه المرحلة، تضرب الصيغة القيم الموجودة في الصفيف المخزن بالقيم المناظرة في الثابت. وهو ما يكافئ:

‎=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)‎

أخيراً، تقوم الدالة SUM بإضافة القيم، ويظهر المجموع 85 في الخلية A3.

لتجنب استخدام الصفيف المخزن ولحفظ العملية بالكامل في الذاكرة، استبدل الصفيف المخزن بثابت صفيف آخر:

=SUM({3,4,5,6,7}*{1,2,3,4,5})

لتجربة ذلك، انسخ الدالة، وحدد خلية فارغة في المصنف، والصق الصيغة في شريط الصيغة، ثم اضغط على Ctrl+Shift+Enter. سترى النتيجة نفسها التي رأيتها في التمرين السابق الذي استخدم صيغة الصفيف:

=SUM(A1:E1*{1,2,3,4,5})

العناصر التي يمكنك استخدامها في الثوابت

يمكن أن تحتوي ثوابت الصفيف على أرقام ونص وقيم منطقية (مثل TRUE وFALSE) وقيم الخطأ (مثل ‎#N/A). يمكنك استخدام الأرقام في تنسيقات أعداد صحيحة وعشرية وتنسيقات علمية. إذا قمت بتضمين نص، فعليك إحاطته بعلامتي اقتباس (").

لا يمكن أن تحتوي ثوابت الصفيف على صفائف أو صيغ أو دالات إضافية. بعبارات أخرى، لا يمكن أن تتضمن سوى نص أو أعداد مفصولة بفواصل أو فواصل منقوطة. ويعرض Excel رسالة تحذير عند قيامك بإدخال صيغة مثل {‎1,2,A1:D4} أو {1,2‎,SUM(Q2:Z8)‎}. ولا يمكن أن تتضمن القيم الرقمية علامات النسبة المئوية أو علامات الدولار أو الفواصل أو الأقواس.

تسمية ثوابت الصفيف

قد تشكل تسمية ثوابت الصفيف أفضل طريقة لاستخدامها. فاستخدام الثوابت المسماة سيكون أكثر سهولة، ويمكن لهذه الثوابت إخفاء بعض التعقيدات التي تتضمنها صيغ الصفيف عن المستخدمين الآخرين. لتسمية ثابت صفيف واستخدامه في صيغة، قم بما يلي:

1. ضمن علامة التبويب صيغ، في المجموعة الأسماء المعرفة، انقر فوق تعريف اسم.
   فيظهر مربع الحوار تعريف اسم.
2. في المربع الاسم، اكتب الربع1.
3. في المربع يشير إلى، أدخل الثابت التالي (تذكر كتابة الأقواس الكبيرة يدوياً):
   {"يناير","فبراير","يناير"}=
   ينبغي أن تظهر محتويات مربع الحوار على النحو التالي:
   
4. انقر فوق موافق، ثم حدد صفاً من ثلاث خلايا فارغة.
5. اكتب الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter.
   =الربع1
   يتم عرض النتيجة التالية.
   

عند استخدام ثابت مسمى كصيغة صفيف، تذكر أن تدخل علامة المساواة. إذا لم تفعل ذلك، فسيفسر Excel الصفيف على أنه سلسلة نصية ولن تعمل الصيغة كما هو متوقع. أخيراً، تذكر أنه بإمكانك استخدام تركيبات من نص وأرقام.

استكشاف أخطاء ثوابت الصفيف وإصلاحها

ابحث عن المشاكل التالية عندما لا تعمل ثوابت الصفيف:

• قد لا تكون بعض العناصر مفصولة بالحرف المناسب. إذا حذفت فاصلة أو فاصلة منقوطة، أو إذا وضعتها في المكان الخطأ، فقد لا يتم إنشاء ثابت الصفيف بشكل صحيح، أو قد تظهر رسالة تحذير.
• ربما حددت نطاق خلايا لا يتطابق مع عدد العناصر في الثابت. على سبيل المثال، إذا قمت بتحديد عمود من ست خلايا لاستخدامه مع ثابت من خمس خلايا، فستظهر قيمة الخطأ ‎#N/A في الخلية الفارغة. في المقابل، إذا حددت عدداً قليلاً جداً من الخلايا، فسيحذف Excel القيم التي ليس لها خلية مناظرة.

طريقة عمل ثوابت الصفيف

توضح الأمثلة التالية بعض الطرق التي يمكن بواسطتها استخدام ثوابت الصفيف في صيغ الصفيف. تستخدم بعض الأمثلة الدالة TRANSPOSE لتحويل الصفوف إلى أعمدة والعكس.

ضرب كل عنصر في صفيف

1. أنشئ ورقة عمل جديدة، ثم حدد كتلة خلايا فارغة بعرض أربعة أعمدة وارتفاع ثلاثة صفوف.
2. اكتب الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   ={1,2,3,4; 5,6,7,8; 9,10,11,12}*2

تطويق العناصر في صفيف

1. حدد كتلة خلايا فارغة بعرض أربعة أعمدة وارتفاع ثلاثة صفوف.
2. اكتب صيغة الصفيف التالية، ثم اضغط على Ctrl+Shift+Enter:
   ‎={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}‎
   بدلاً من ذلك، أدخل صيغة الصفيف هذه التي تستخدم مُشغل علامة الإقحام (^):
   ‎={1,2,3,4;5,6,7,8;9,10,11,12}^2

تبديل موضع صف أحادي الأبعاد

1. حدد عموداً يتألف من خمس خلايا فارغة.
2. اكتب الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   =TRANSPOSE({1,2,3,4,5})
   على الرغم من إدخال ثابت صفيف أفقي، تحوّل دالة TRANSPOSE ثابت الصفيف إلى عمود.

تبديل موضع عمود أحادي الأبعاد

1. حدد صفاً يتألف من خمس خلايا فارغة.
2. أدخل الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   ‎=TRANSPOSE({1;2;3;4;5})‎

على الرغم من إدخال ثابت صفيف عمودي، تحوّل دالة TRANSPOSE ثابت الصفيف إلى صف.

تبديل موضع ثابت ثنائي الأبعاد

1. حدد كتلة خلايا بعرض ثلاثة أعمدة وارتفاع أربعة صفوف.
2. أدخل الثابت التالي، ثم اضغط على Ctrl+Shift+Enter:
   ‎=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})‎
   تحول دالة TRANSPOSE كل صف إلى سلسلة من الأعمدة.

تشغيل صيغ الصفيف الأساسية

يوفر هذا المقطع أمثلة على صيغ الصفيف الأساسية.

إنشاء صفائف وثوابت الصفائف من قيم موجودة

يوضح المثال التالي كيفية استخدام صيغ الصفيف لإنشاء ارتباطات بين نطاقات الخلايا في أوراق عمل مختلفة. ويعرض كيفية إنشاء ثابت صفيف من مجموعة القيم نفسها.

إنشاء صفيف من قيم موجودة

1. حدد الخلايا C8:E10 على ورقة عمل في Excel ثم أدخل الصيغة التالية:
   ={10,20,30;40,50,60;70,80,90}
   تأكد من كتابة { (قوس الفتح) قبل كتابة 10، و } (قوس الإغلاق) بعد كتابة 90، لأنك تقوم بإنشاء صفيف من الأرقام.

2. اضغط على Ctrl+Shift+Enter، مما يؤدي إلى إدخال صفيف الأرقام هذا في نطاق الخلايا C8:E10 باستخدام صيغة صفيف. يجب أن تبدو الخلايا من C8 إلى E10 على ورقة العمل كالتالي:

   10	20	30
   40	50	60
   70	80	90

3. حدد نطاق الخلايا من C1 إلى E3.

4. أدخل الصيغة التالية في شريط الصيغة، ثم اضغط على Ctrl+Shift+Enter:
   ‎=C8:E10
   يظهر صفيف الخلايا 3x3 في الخلايا من C1 إلى E3 التي تحتوي على القيم نفسها الموجودة في الخلايا من C8 إلى E10.

إنشاء ثابت صفيف من قيم موجودة

1. مع الاحتفاظ بتحديد الخلايا C1:C3، اضغط على F2 للتبديل إلى وضع التحرير. 
2. اضغط على F9 لتحويل مراجع الخلايا إلى قيم. يحول Excel القيم إلى ثابت صفيف. يجب أن تكون الصيغة الآن هي ={10,20,30; 40,50,60; 70،80،90}.
3. اضغط على Ctrl+Shift+Enter لإدخال ثابت الصفيف كصيغة صفيف.

حساب عدد الأحرف في نطاق خلايا

يوضح المثال التالي كيفية حساب عدد الأحرف، بما في ذلك المسافات الموجودة في نطاق خلايا.

1. انسخ هذا الجدول بالكامل ثم قم بلصقه في ورقة عمل في الخلية A1.

   بيانات
   هذه هي
   مجموعة من الخلايا
   المُجمعة معاً
   لتكوين
   جملة واحدة.
   إجمالي عدد الأحرف في A2:A6
   ‎=SUM(LEN(A2:A6))‎
   محتويات أطول خلية (A3)
   ‎=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)‎

2. حدد الخلية A8، ثم اضغط على Ctrl+Shift+Enter للاطلاع على العدد الإجمالي للأحرف في الخلايا A2:A6 (66).

3. حدد الخلية A10، ثم اضغط على Ctrl+Shift+Enter للاطلاع على محتويات الخلايا الأطول A2:A6 (الخلية A3).

تستخدم الصيغة التالية في الخلية A8 لحساب العدد الإجمالي للأحرف (66) في الخلايا من A2 إلى A6.

‎=SUM(LEN(A2:A6))‎

في هذه الحالة، تُرجع الدالة LEN طول كل سلسة نصية في كل خلية من الخلايا في النطاق. بعد ذلك، تقوم الدالة SUM بجمع تلك القيم معا وتعرض النتيجة (66).

البحث عن قيم n الصغرى في النطاق

يعرض هذا المثال كيفية البحث عن القيم الصغرى الثلاث في نطاق خلايا.

1. أدخل بعض الأرقام العشوائية في الخلايا A1:A11.
2. حدد الخلايا من C1 إلى C3. تحتفظ مجموعة الخلايا هذه بالنتائج التي تُرجعها صيغة الصفيف.
3. أدخل الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:
   =SMALL(A1:A11,{1; 2; 3})

تستخدم هذه الصيغة ثابت صفيف لتقييم الدالة SMALL ثلاث مرات وإرجاع العضو الأصغر (1)، وثاني أصغر عضو (2)، وثالث أصغر عضو (3) في الصفيف المضمن في الخلايا A1:A10 للبحث عن المزيد من القيم، أضف المزيد من الوسيطات إلى الثابت. يمكنك أيضاً استخدام دالات إضافية مع هذه الصيغة، مثل SUM أو AVERAGE. على سبيل المثال:

=SUM(SMALL(A1:A10,{1,2,3})

=AVERAGE(SMALL(A1:A10,{1,2,3})

البحث عن قيم n الكبرى في النطاق

للبحث عن القيم الكبرى في نطاق، يمكنك استبدال الدالة SMALL بالدالة LARGE. بالإضافة إلى ذلك، يستخدم المثال التالي الدالتين ROW وINDIRECT.

1. حدد الخلايا من D1 إلى D3.
2. في شريط الصيغة، أدخل هذه الصيغة، ثم اضغط على Ctrl+Shift+Enter:
   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

في هذه المرحلة، قد تفيدك معرفة القليل حول الدالتين ROW وINDIRECT. يمكنك استخدام الدالة ROW لإنشاء صفيف يتألف من أعداد صحيحة متتالية. على سبيل المثال، حدد عمودا فارغا يتألف من 10 خلايا في مصنف التمرين، وأدخل صيغة الصفيف هذه، ثم اضغط على Ctrl+Shift+Enter:

=ROW(1:10)

تنشئ الصيغة عموداً يتألف من 10 أعداد صحيحة متتالية. لرؤية المشكلة المحتملة، أدرج صفاً فوق النطاق الذي يحتوي على صيغة الصفيف (أي فوق الصف 1). يعدل Excel مراجع الصفوف، وتنشئ الصيغة أعداداً صحيحة من 2 إلى 11. ولحل هذه المشكلة، أضِف الدالة INDIRECT إلى الصيغة:

=ROW(INDIRECT("1:10"))

تستخدم الدالة INDIRECT السلاسل النصية كوسيطات لها (وهذا هو سبب إحاطة النطاق 1:10 بعلامتي اقتباس مزدوجتين). لا يقوم Excel بتعديل القيم النصية عند إدراج صفوف أو نقل صيغة الصفيف بدلاً من ذلك. كنتيجة لذلك، تقوم الدالة ROW دائماً بإنشاء صفيف من الأعداد الصحيحة التي تريدها.

دعنا نلق نظرة على الصيغة التي استخدمتها سابقا — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — بدءا من الأقواس الداخلية وصولا إلى الخارج: ترجع الدالة INDIRECT مجموعة من القيم النصية، وفي هذه الحالة تكون القيم من 1 إلى 3. تقوم الدالة ROW بدورها بإنشاء صفيف عمودي ثلاثي الخلايا. تستخدم الدالة LARGE القيم الموجودة في نطاق الخلايا A5:A14، ويتم تقييمها ثلاث مرات، مرة واحدة لكل مرجع يتم إرجاعه بواسطة الدالة ROW . يتم إرجاع القيم 3200 و2700 و2000 إلى الصفيف العمودي المكون من ثلاث خلايا. إذا كنت تريد البحث عن المزيد من القيم، فيمكنك إضافة نطاق خلايا أكبر إلى الدالة INDIRECT .

كما في الأمثلة السابقة، يمكنك استخدام هذه الصيغة مع دالات أخرى، مثل SUMوAVERAGE.

البحث عن أطول سلسلة نصية في نطاق خلايا

عد إلى مثال السلسلة النصية السابقة، وأدخل الصيغة التالية في خلية فارغة، واضغط على Ctrl+Shift+Enter:

‎=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)‎

يظهر النص "مجموعة خلايا".

فلنلقِ نظرة عن كثب على الصيغة، بدءاً من العناصر الداخلية وبالاتجاه نحو الخارج. ترجع الدالة LEN طول كل عنصر من العناصر في نطاق الخلايا A2:A6. تحسب الدالة MAX القيمة الكبرى بين هذه العناصر، وهي تتطابق مع أطول سلسلة نصية، أي تلك الموجودة في الخلية A3.

هنا تصبح الأمور معقدة بعض الشيء. تحسب الدالة MATCH الإزاحة (الموقع النسبي) للخلية التي تحتوي على أطول سلسلة نصية. يتطلب إجراء ذلك ثلاث وسيطات: قيمة البحث وصفيف البحث ونوع المطابقة. تبحث الدالة MATCH في صفيف البحث عن قيمة البحث المُحددة. في هذه الحالة، تكون قيمة البحث أطول سلسلة نصية:

(MAX(LEN(A2:A6))

وتتواجد السلسلة في الصفيف التالي:

LEN(A2:A6)

إن وسيطة نوع المطابقة هي 0. ويمكن أن يتألف نوع المطابقة من القيم 1 أو 0 أو 1-. إذا حددت 1، فتُرجع الدالة MATCH القيمة الكبرى التي تكون أقل من قيمة البحث أو مساوية لها. إذا حددت 0، فتُرجع الدالة MATCH أول قيمة مساوية تماماً لقيمة البحث. إذا حددت 1-، فتبحث الدالة MATCH عن القيمة الصغرى التي تكون أكبر من قيمة البحث المحددة أو مساوية لها. إذا قمت بحذف نوع المطابقة، فسيفترض Excel أنها 1.

أخيراً، تأخذ الدالة INDEX الوسيطات التالية: صفيف ورقم صف وعمود ضمن هذا الصفيف. يوفر نطاق الخلايا A2:A6 الصفيف، وتوفر الدالة MATCH عنوان الخلية، وتبين الوسيطة الأخيرة (1) أن القيمة تأتي من العمود الأول في الصفيف.

تشغيل صيغ الصفيف المتقدمة

يوفر هذا المقطع أمثلة حول صيغ الصفائف المتقدمة.

جمع نطاق يحتوي على قيم خطأ

لا تعمل الدالة SUM في Excel عندما تحاول جمع نطاق يحتوي على قيمة خطأ، مثل ‎#N/A. يوضح لك هذا المثال كيفية جمع القيم الموجودة في نطاق يسمى "بيانات" يحتوي على أخطاء.

‎=SUM(IF(ISERROR(Data),"",Data))‎

تنشئ الصيغة صفيفاً جديداً يحتوي على القيم الأصلية من دون أي قيم أخطاء. بدءاً من الدالات الداخلية وباتجاه الخارج، تبحث الدالة ISERROR في نطاق الخلايا (بيانات) عن أخطاء. وتُرجع الدالة IF قيمة معينة إذا تم تقييم الشرط الذي حددته إلى TRUE وتُرجع قيمة أخرى إذ تم تقييم الشرط إلى FALSE. في هذه الحالة، تُرجع الدالة سلاسل فارغة ("") لكافة قيم الخطأ لأنه تم تقييمها إلى TRUE، وكذلك تُرجع القيم الباقية من النطاق (بيانات) لأنه تم تقييمها إلى FALSE، مما يعني عدم احتوائها على قيم خطأ. تحسب الدالة SUM حينئذٍ الإجمالي بالنسبة إلى الصفيف الذي تمت تصفيته.

حساب عدد قيم الأخطاء في النطاق

يتشابه هذا المثال مع الصيغة السابقة، ولكنه يرجع عدد قيم الخطأ في نطاق اسمه "بيانات" بدلاً من تصفيتها:

‎=SUM(IF(ISERROR(Data),1,0))‎

تنشئ هذه الصيغة صفيفاً يحتوي على القيمة 1 للخلايا التي تحتوي على أخطاء والقيمة 0 للخلايا التي لا تحتوي على أخطاء. يمكنك تبسيط الصيغة والحصول على النتيجة نفسها عبر إزالة الوسيطة الثالثة الخاصة بالدالة IF، على الشكل الآتي:

‎=SUM(IF(ISERROR(Data),1))‎

إذا لم تحدد الوسيطة، فتُرجع الدالة IF القيمة FALSE إذا كانت الخلية لا تحتوي على قيمة خطأ. ويمكنك أيضاً تبسيط الصيغة أكثر:

=SUM(IF(ISERROR(Data)*1))

تعمل هذه النسخة لأن TRUE*1=1 وFALSE*1=0.

جمع القيم حسب الشروط

قد تحتاج إلى جمع القيم بالاستناد إلى شروط. على سبيل المثال، تجمع صيغة الصفيف هذه الأعداد الصحيحة الموجبة فقط في نطاق مسمى "مبيعات":

=SUM(IF(Sales>0,Sales))

تنشئ الدالة IF صفيفاً من قيم موجبة وقيم خطأ. وتتجاهل الدالة SUM بشكل أساسي قيم الخطأ 0+0=0. يمكن أن يحتوي نطاق الخلايا الذي تستخدمه في هذه الصيغة على أي عدد من الصفوف والأعمدة.

يمكنك أيضاً جمع القيم التي تحقق أكثر من شرط واحد. على سبيل المثال، تحسب صيغة الصفيف التالية القيم الأكبر من 0 والأقل من 5 أو المساوية له:

=SUM((Sales>0)*(Sales<=5)*(Sales))

تذكر أن هذه الصيغة ترجع خطأ إذا كان النطاق يحتوي على خلية غير رقمية واحدة أو أكثر.

يمكنك أيضاً إنشاء صيغ صفيف تستخدم نوع الشرط OR. على سبيل المثال، يمكنك جمع القيم الأقل من 5 وأكبر من 15:

=SUM(IF((Sales<5)+(Sales>15),Sales))

تبحث الدالة IF عن كافة القيم الأصغر من 5 وأكبر من 15 ثم تقوم بتمرير هذه القيم إلى الدالة SUM.

لا يمكنك استخدام الدالتين AND وOR في صيغ الصفيف مباشرة لأن هذه الدالات ترجع نتيجة فردية، إما TRUE أو FALSE، وتتطلب دالات الصفيف وجود صفائف للنتائج. يمكنك حل هذه المشكلة من خلال استخدام المنطق المبين في الصيغة السابقة. بعبارات أخرى، تقوم بإجراء عمليات حسابية، مثل الجمع أو الضرب، على القيم التي تحقق الشرط OR أو AND.

حساب المتوسط الذي يستثني الأصفار

يبين هذا المثال كيفية إزالة الأصفار من نطاق للحصول على متوسط القيم في النطاق. تستخدم الصيغة نطاق بيانات يسمى "مبيعات":

=AVERAGE(IF(Sales<>0,Sales))

تعمل الدالة IF على إنشاء صفيف من القيم لا يساوي 0 ثم تقوم بتمرير هذه القيم إلى الدالة AVERAGE.

حساب عدد الاختلافات بين نطاقين من الخلايا

تعمل صيغة الصفيف هذه على مقارنة القيم الموجودة في نطاقين من الخلايا تمت تسميتهما MyData وYourData وإرجاع عدد الاختلافات بين النطاقين. إذا كانت محتويات النطاقين متطابقة، فترجع الصيغة 0. لاستخدام هذه الصيغة، يجب أن تكون نطاقات الخلايا بالحجم نفسه والبعد نفسه (على سبيل المثال، إذا كان النطاق MyData يتألف من 3 صفوف و5 أعمدة، فيجب أن يتألف أيضاً النطاق YourData من 3 صفوف و5 أعمدة):

=SUM(IF(MyData=YourData,0,1))

تعمل الصيغة على إنشاء صفيف جديد بالحجم نفسه لحجم النطاقات التي تقوم بمقارنتها. وتعمل الدالة IF على تعبئة الصفيف بالقيمة 0 والقيمة 1 (0 للخلايا غير المتطابقة و1 للخلايا المتطابقة). بعد ذلك تقوم الدالة SUM بإرجاع مجموع القيم في الصفيف.

يمكنك تبسيط الصيغة على النحو التالي:

=SUM(1*(MyData<>YourData))

تماماً مثل الصيغة التي تحسب عدد قيم الخطأ في النطاق، تعمل هذه الصيغة لأن TRUE*1=1 وFALSE*1=0.

البحث عن موقع القيمة الأعلى في النطاق

ترجع صيغة الصفيف هذه رقم الصف الخاص بالقيمة الأعلى في نطاق بعمود واحد يسمى "بيانات":

‎=MIN(IF(Data=MAX(Data),ROW(Data),""))‎

تنشئ الدالة IF صفيفاً جديداً يتطابق مع النطاق المسمى "بيانات". إذا كانت إحدى الخلايا المتطابقة تحتوي على القيمة الأعلى في النطاق، فسيحتوي الصفيف على رقم الصف. في الحالات الأخرى، سيحتوي الصفيف على سلسلة فارغة (""). تستخدم الدالة MIN الصفيف الجديد باعتباره الوسيطة الثانية له وترجع القيمة الأصغر التي تتطابق مع رقم صف القيمة الأعلى في النطاق "بيانات". إذا كان النطاق المسمى "بيانات" يحتوي على قيم أعلى متطابقة، فتُرجع الصيغة صف القيمة الأولى.

إذا كنت تريد إرجاع عنوان الخلية الفعلي لإحدى القيم العليا، فاستخدم الصيغة التالية:

‎=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))‎