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

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

إن صيغة الصفيف هي صيغة يمكنها إجراء عمليات حسابية متعددة على عنصر واحد أو أكثر في صفيف. يمكنك التفكير في صفيف كصف أو عمود من القيم، أو مجموعة من صفوف وأعمدة القيم. يمكن أن تؤدي صيغ الصفيف إلى إرجاع نتائج متعددة أو نتيجة واحدة.

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

يمكنك استخدام صيغ الصفيف لتنفيذ مهام معقدة، مثل:

  • إنشاء مجموعات بيانات نموذجية بسرعة.

  • حساب عدد الأحرف المضمنة في نطاق من الخلايا.

  • جمع الأرقام التي تفي بشروط معينة فقط، مثل أقل القيم في نطاق، أو الأرقام التي تقع بين حد أعلى وحد أدنى.

  • جمع كل قيمة Nth في نطاق من القيم.

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

تنزيل الأمثلة التي نقدمها

قم بتنزيل مصنف مثال مع كل أمثلة صيغ الصفيف في هذه المقالة.

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

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

    دالة صفيف متعددة الخلايا في الخلية H10 =F10:F19*G10:G19 لحساب عدد السيارات المباعة حسب سعر الوحدة

  • نحن نقوم هنا بحساب إجمالي مبيعات السيارات التي تم بيعها من سيارة Coupes وSedans لكل مندوب مبيعات عن طريق إدخال =F10:F19*G10:G19 في الخلية H10.

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

  • صيغة صفيف ذات خلية واحدة

    صيغة صفيف ذات خلية واحدة لحساب الإجمالي الكلي باستخدام =SUM(F10:F19*G10:G19)

    في الخلية 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,F 13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

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

    • #SPILL! خطأ    لقد قدمت الصفائف الديناميكية #SPILL!، مما يشير إلى أن نطاق الانسكاب المقصود تم حظره لسبب ما. عندما تقوم بحل الانسداد، ستمتد الصيغة تلقائيا.

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

={1,2,3,4,5} أو ={"January","فبراير","مارس"}

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

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

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

    استخدم المصنف من الأمثلة السابقة، أو أنشئ مصنفاً جديداً. حدد أي خلية فارغة وأدخل =SEQUENCE(1,5). تقوم الدالة SEQUENCE ببناء صف واحد في 5 صفيف عمود تماما مثل ={1,2,3,4,5}. يتم عرض النتيجة التالية:

    إنشاء ثابت صفيف أفقي باستخدام =SEQUENCE(1,5) أو ={1,2,3,4,5}

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

    حدد أي خلية فارغة مع مساحة أسفلها، وأدخل =SEQUENCE(5)أو ={1;2;3;4;5}. يتم عرض النتيجة التالية:

    إنشاء ثابت صفيف عمودي باستخدام =SEQUENCE(5) أو ={1;2;3;4;5}

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

    حدد أي خلية فارغة مع مساحة إلى اليمين وتحتها، وأدخل =SEQUENCE(3,4). تظهر أمامك النتيجة التالية:

    إنشاء ثابت صفيف من 3 صفوف إلى 4 أعمدة باستخدام =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.

استخدم ثوابت الصفيف في الصيغ. في هذا المثال، استخدمنا =SUM(D9:H(*SEQUENCE(1,5))

تقوم الدالة 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)‎}. ولا يمكن أن تتضمن القيم الرقمية علامات النسبة المئوية أو علامات الدولار أو الفواصل أو الأقواس.

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

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

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

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

إضافة ثابت صفيف مسمى من الصيغ > الأسماء المعرفة > إدارة الأسماء > جديد

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

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

استخدم ثابت صفيف مسمى في صيغة، مثل =Quarter1، حيث تم تعريف Quarter1 على أنه ={"January","فبراير","مارس"}

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

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

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

استخدام تركيبة من الدالات TEXT و DATE و YEAR و TODAY و SEQUENCE لإنشاء قائمة ديناميكية لمدة 12 شهرا

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

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

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

  • تعدد كل عنصر في صفيف

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

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

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

    Enter =SEQUENCE(1,12)^2, أو ={1,2,3,4;5,6,7,8;9,10,11,12}^2

  • البحث عن الجذر المربع للعناصر المربعة في صفيف

    Enter =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 ثابت الصفيف إلى عمود.

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

    Enter =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 كل صف إلى سلسلة من الأعمدة.

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

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

    يوضح المثال التالي كيفية استخدام صيغ الصفيف لإنشاء صفيف جديد من صفيف موجود.

    Enter =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 الحساب.

    Excel صيغة الصفيف للعثور على أصغر قيمة Nth: =SMALL(B9#,SEQUENCE(D9))

    Enter =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. يوضح لك هذا المثال كيفية جمع القيم الموجودة في نطاق يسمى "بيانات" يحتوي على أخطاء:

    استخدم الصفائف للتعامل مع الأخطاء. على سبيل المثال، تلخص =SUM(IF(ISERROR(Data),"",Data) النطاق المسمى Data حتى لو كان يتضمن أخطاء، مثل #VALUE! أو #NA!.

  • ‎=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)) جمع كافة القيم الأكبر من 0 في نطاق يسمى المبيعات.

على سبيل المثال، ت جمع صيغة الصفيف هذه فقط عدد صحيح موجبة في نطاق يسمى المبيعات، والذي يمثل الخلايا 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. لمشاهدة إجمالي مبيعات السيارات التي تم بيعها من نوع Coupes وSedans لكل مندوب مبيعات، حدد الخلايا 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}‎

يتم احتساب الإجماليات في العمود E بواسطة صيغة صفيف

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

في الخلية 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)‎.

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

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

  • حدد نطاق الخلايا الذي سيحتوي على النتائج قبل إدخال الصيغة. لقد قمت بذلك عندما أنشأت صيغة صفيف متعددة الخلايا عندما حددت الخلايا من 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))‎

إعلام

تستند أجزاء من هذه المقالة إلى سلسلة من أعمدة Excel Power User التي كتبها جبران ويلكx، وقد تم تكييفها من الفصلين 14 و15 من صيغ Excel 2002، وهو كتاب كتبه جبران وكنينكي، وهو عضو سابق في Excel MVP.

هل تحتاج إلى مزيد من المساعدة؟

ويمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel‏ التقني،أو الحصول على الدعم من خلال إجابات المجتمع،أو اقتراح ميزة جديدة أو تحسين عن طريق ‏Excel User Voice‏.

اطلع أيضاً على

صفائف ديناميكية وسلوك الصفيف الممدّ

صيغ صفيف ديناميكية مقابل صيغ صفيف CSE القديمة

الدالة FILTER

دالة RANDARRAY

دالة SEQUENCE

دالة SORT

دالة SORTBY

دالة UNIQUE

أخطاء #SPILL! في Excel

عامل تشغيل التقاطع الضمني: @

نظرة عامة حول الصيغ

هل تحتاج إلى مزيد من المساعدة؟

تعزيز مهارات Office
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×