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

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

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

انسخ الجدول بأكمله أدناه والصقه في الخلية 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))‎