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

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

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

  

• هنا نحن نحسب إجمالي مبيعات الكوبيهات وsedans لكل مندوب مبيعات عن طريق إدخال =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! خطأ    قدمت الصفائف الديناميكية الخطأ #SPILL!، مما يشير إلى حظر نطاق الانسكاب المقصود لسبب ما. عند حل الانسداد، سيتم تسرب الصيغة تلقائيا.

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

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

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

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

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

  استخدم المصنف من الأمثلة السابقة، أو أنشئ مصنفاً جديداً. حدد أي خلية فارغة وأدخل =SEQUENCE(1,5). تقوم الدالة SEQUENCE بإنشاء صف واحد بمقدار 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 مزايا كبيرة على إدخال القيم الثابتة للصفيف يدويا. في المقام الأول، يوفر لك الوقت، ولكن يمكن أن يساعد أيضا في تقليل الأخطاء من الإدخال اليدوي. من الأسهل أيضا القراءة، خاصة وأن الفاصلات المنقوطة قد يكون من الصعب تمييزها عن فواصل الفاصلة.

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

في الخلية 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), or =SUM(3*1,4*2,5*3,6*4,7*5)

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

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

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)), or =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)‎}. ولا يمكن أن تتضمن القيم الرقمية علامات النسبة المئوية أو علامات الدولار أو الفواصل أو الأقواس.

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

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

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

يجب أن يبدو مربع الحوار الآن كما يلي:

انقر فوق موافق، ثم حدد أي صف بثلاث خلايا فارغة، وأدخل =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 لتحويل الصفوف إلى أعمدة والعكس صحيح.

• عدة عناصر في صفيف

  أدخل =SEQUENCE(1,12)*2 أو ={1,2,3,4; 5,6,7,8; 9,10,11,12}*2

  يمكنك أيضا القسمة مع (/)، وإضافة مع (+)، والطرح باستخدام (-).

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

  أدخل =SEQUENCE(1,12)^2 أو ={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)

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

  أدخل =TRANSPOSE(SEQUENCE(1,5)) أو =TRANSPOSE({1,2,3,4,5})

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

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

  أدخل =TRANSPOSE(SEQUENCE(5,1)) أو =TRANSPOSE({1; 2; 3; 4; 5})

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

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

  أدخل =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 × 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:B18has باستخدام: =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.

  أدخل =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. وأخيرا، كما هو الحال مع الأمثلة SMALL، يمكنك استخدام هذه الصيغة مع دالات أخرى، مثل SUM و AVERAGE.

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

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

  

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

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

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

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

  ‎=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 AND أقل من 2500:

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

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

يمكنك أيضاً إنشاء صيغ صفيف تستخدم نوع الشرط OR. على سبيل المثال، يمكنك جمع القيم الأكبر من 0 OR أقل من 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 في ورقة عمل فارغة.

مندوب المبيعات	نوع السيارة	رقم مباع	سعر الوحدة	إجمالي المبيعات
شامي	سيارة سيدان	5	33000
	سيارة كوبيه	4	37000
جاكلين	سيارة سيدان	6	24000
	سيارة كوبيه	8	21000
كامل	سيارة سيدان	3	29000
	سيارة كوبيه	1	31000
مهدي	سيارة سيدان	9	24000
	سيارة كوبيه	5	37000
أسامة	سيارة سيدان	6	33000
	سيارة كوبيه	8	31000
الصيغة (الإجمالي الكلي)			الإجمالي الكلي
‎'=SUM(C2:C11*D2:D11)‎			‎=SUM(C2:C11*D2:D11)‎

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))‎