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

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

هام: تمت ترجمة هذه المقالة ترجمة آلية، راجع إقرار إخلاء المسؤولية. يرجى الاطلاع على النسخة الإنجليزية من هذه المقالة. هنا للرجوع إليها.

ل# كي تصبح مستخدم power Excel، يجب عليك معرفه كيفيه استخدام صيغ الصفيف، يمكنك تنفيذ العمليات الحسابيه التي يتعذر عليك القيام بها ب# استخدام صيغ الصفيف. المقاله التاليه استنادا الي سلسله من الاعمده مستخدم Power Excel مكتوب ب# كامل عميره و# تم ضبطه يناسب من فصول 14 و 15 من صيغ 2002 Excel، كتاب ب# والكينباتش John، احد اهم محترفي Excel قلم.

التعرف على صيغ الصفيف

يُشار في أغلب الأحيان إلى صيغ الصفيف كصيغ CSE‏ (Ctrl+Shift+Enter)، إذ بدلاً من مجرد الضغط على Enter، عليك الضغط على Ctrl+Shift+Enter لإكمال الصيغة.

لماذا نستخدم صيغ الصفيف؟

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

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

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

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

مقدمة سريعة حول الصفائف وصيغ الصفيف

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

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

جربها بنفسك!

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

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

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

إنشاء صيغة صفيف متعددة الخلايا
  1. انسخ الجدول بكامله ثم الصقه في الخلية A1 في ورقة عمل فارغة في Excel.

    مندوب
    المبيعات

    نوع
    السيارة

    العدد
    المباع

    سعر
    الوحدة

    إجمالي
    المبيعات

    شامي

    سيارة سيدان

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

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

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

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

إذا كنت تعمل في Excel، فتأكد من أن "ورقة1" نشطة، ثم حدد الخلايا E2:E11. اضغط على F2 واكتب الصيغة =C2:C11*D2:D11 في الخلية الحالية، E2. إذا ضغطت على Enter، فسترى أن ذلك سيؤدي إلى إدخال الصيغة في الخلية E2 فقط وعرض 165000. بدلاً من ذلك، بعد كتابة الصيغة، اضغط على Ctrl+Shift+Enter بدلاً من الضغط على Enter فقط. سترى الآن النتائج في الخلايا E2:E11. لاحظ أن الصيغة في شريط الصيغة تظهر على الشكل التالي ‎{=C2:C11*D2:D11}‎. وهذا يشير إلى أن الصيغة هي صيغة صفيف. ويظهر هذا أدناه.

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

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

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

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

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

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

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

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

  • التناسق    إذا نقرت فوق أية خلية من الخلايا بدءاً من 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 رسالة تخبرك بأنه لا يمكنك تغيير أي جزء من صفيف.

  • يمكنك نقل صيغة صفيف بالكامل أو حذفها، ولكن لا يمكنك نقل جزء منها أو حذفه. بعبارات أخرى، لتقليص صيغة صفيف، احذف الصيغة الموجودة أولاً ثم ابدأ من جديد.

  • لحذف صيغة صفيف، حدد الصيغة بالكامل (على سبيل المثال، ‎=C2:C11*D2:D11)، واضغط على Delete، ثم على Ctrl+Shift+Enter.

  • لا يمكنك إدراج خلايا فارغة في صيغة صفيف متعددة الخلايا أو حذفها منها.

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

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

لقد أضفنا على ورقة العمل هذه بعض صفوف المبيعات الإضافية، في الصفوف من 12 إلى 17. ونريد هنا تحديث صيغ الصفيف لكي تتضمّن هذه الصفوف الإضافية.

تأكد من إجراء ذلك في برنامج Excel لسطح المكتب (بعد تنزيل المصنف على الكمبيوتر).

توسيع صيغة صفيف
  1. انسخ هذا الجدول بكامله إلى الخلية A1 في ورقة عمل Excel.

    مندوب
    المبيعات

    نوع
    السيارة

    العدد
    المباع

    سعر
    الوحدة

    إجمالي
    المبيعات

    شامي

    سيارة سيدان

    5

    33000

    165000

    سيارة كوبيه

    4

    37000

    148000

    جاكلين

    سيارة سيدان

    6

    24000

    144000

    سيارة كوبيه

    8

    21000

    168000

    كامل

    سيارة سيدان

    3

    29000

    87000

    سيارة كوبيه

    1

    31000

    31000

    مهدي

    سيارة سيدان

    9

    24000

    216000

    سيارة كوبيه

    5

    37000

    185000

    أسامة

    سيارة سيدان

    6

    33000

    198000

    سيارة كوبيه

    8

    31000

    248000

    فادي

    سيارة سيدان

    2

    27000

    سيارة كوبيه

    3

    30000

    أنطونيو

    سيارة سيدان

    4

    22000

    سيارة كوبيه

    1

    41000

    نادر

    سيارة سيدان

    5

    32000

    سيارة كوبيه

    3

    36000

    الإجمالي الكلي

  2. حدد الخلية E18، وأدخل صيغة "الإجمالي الكلي" ‎=SUM(C2:C17*D2:D17)‎ في الخلية A20، ثم اضغط على Ctrl+Shift+Enter.
    يجب أن تكون الإجابة 2131000.

  3. حدد نطاق الخلايا الذي يحتوي على صيغة الصفيف الحالية (E2:E11)، بالإضافة إلى الخلايا الفارغة (E12:E17) المجاورة للبيانات الجديدة. بعبارات أخرى، حدد الخلايا E2:E17.

  4. اضغط على F2 للتبديل إلى وضع التحرير.

  5. في شريط الصيغة، غيّر C11 إلى C17، وغيّر D11 إلى D17، ثم اضغط على Ctrl+Shift+Enter.
    يقوم Excel بتحديث الصيغة في الخلايا من E2 إلى E11 ويضع مثيلاً للصيغة في الخلايا الجديدة من E12 إلى E17.

  6. اكتب صيغة الصفيف = SUM(C2:C17*D2*D17) في الخلية F17 بحيث تشير إلى الخلايا من الصف 2 إلى الصف 17، واضغط على Ctrl+Shift+Enter لإدخال صيغة الصفيف.
    يجب أن يكون الإجمالي الكلي الجديد 2131000.

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

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

  • قد تنسى أحياناً الضغط على 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. استخدم المصنف من الأمثلة السابقة، أو أنشئ مصنفاً جديداً.

  2. حدد الخلايا من A1 إلى E1.

  3. في شريط الصيغة، أدخل الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:

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

    في هذه الحالة، يجب عليك كتابة قوسي الفتح والإغلاق الكبيرين ({ }).

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

    ثابت صفيف أفقي في الصيغة

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

  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 للتبديل إلى وضع التحرير.
    يجب أن تظل صيغة الصفيف عبارة عن ‎= C8:E10.

  2. اضغط على F9 لتحويل مراجع الخلايا إلى قيم.
    يحول Excel القيم إلى ثابت صفيف. يجب أن تكون الصيغة الآن هي ‎={10,20,30;40,50,60;70,80,90}‎ مثل C8:E10 تماماً.

  3. اضغط على Ctrl+Shift+Enter لإدخال ثابت الصفيف كصيغة صفيف.

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

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

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

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

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

البيانات

هذه هي

مجموعة من الخلايا

المُجمعة معاً

لتكوين

جملة واحدة.

إجمالي عدد الأحرف في A2:A6

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

محتويات أطول خلية (A3)

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

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

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

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

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

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

  1. حدد الخلايا من A16 إلى A18.
    تحتفظ مجموعة الخلايا هذه بالنتائج التي تُرجعها صيغة الصفيف.

  2. في شريط الصيغة، أدخل الصيغة التالية، ثم اضغط على Ctrl+Shift+Enter:

    =SMALL(A5:A14,{1;2;3})

تظهر القيم 400 و475 و500 في الخلايا من A16 إلى A18، على التوالي.

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

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))

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

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

  1. حدد الخلايا من A1 إلى A3.

  2. في شريط الصيغة، أدخل هذه الصيغة، ثم اضغط على Ctrl+Shift+Enter:

    ‎=LARGE(A5:A14,ROW(INDIRECT("1:3")))‎

تظهر القيم 3200 و2700 و2000 في الخلايا من A1 إلى A3 على التوالي.

في هذه المرحلة، قد تفيدك معرفة القليل حول الدالتين ROW وINDIRECT. يمكنك استخدام الدالة ROW لإنشاء صفيف يتألف من أعداد صحيحة متتالية. على سبيل المثال، حدد عموداً فارغاً يتألف من 10 خلايا في مصنف التمرين، وأدخل صيغة الصفيف هذه في الخلايا A5:A14، ثم اضغط على 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.

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

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

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

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

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

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

(MAX(LEN( A6 : A9 ))

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

LEN( A6:A9 )

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

أخيراً، تأخذ الدالة INDEX الوسيطات التالية: صفيف ورقم صف وعمود ضمن هذا الصفيف. يوفر نطاق الخلايا A6:A9 الصفيف، وتوفر الدالة 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‏ التقني،أو الحصول على الدعم من خلال إجابات المجتمع،أو اقتراح ميزة جديدة أو تحسين عن طريق ‏Excel User Voice‏.

ملاحظة: إقرار إخلاء المسؤولية للترجمة الآلية: تمت ترجمة هذه المقالة بواسطة نظام كمبيوتر دون تدخل العامل البشري. توفر Microsoft هذه الترجمات الآلية لمساعدة المستخدمين الذين لا يتحدثون الإنجليزية على استخدام منتجات Microsoft، وخدماتها، وتقنياتها والاستمتاع بها. قد تحتوي هذه المقالة على أخطاء في المفردات، أو بناء الجملة، أو القواعد نظرًا لترجمتها آليًا.

انظر أيضاً

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

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

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

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

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

×