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

img
لكي تصبح مستخدمًا متميزًا لـ Excel، يجب أن تعرف كيفية استخدام صيغ الصفائف التي تجري عمليات حسابية لا تستطيع إجراءها باستخدام صيغ بدون صفائف. تستند المقالة التالية إلى سلسلة من أعمدة Excel Power User كتبها كولين ولكوكس وتم اقتباسها من الفصلين 14 و15 من كتاب Excel 2002 Formulas لمؤلفه جون والكنباك، أحد أمهر المحترفين المتميزين لـ Excel.

في هذه المقالة

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

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

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

جربها بنفسك!

إنشاء صيغة صفيف متعددة الخلايا

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

نظرة إلى بناء جملة صيغة الصفيف

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

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

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

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

مقدمة موجزة حول ثوابت الصفائف

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

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

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

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

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

نظرة إلى بناء جملة ثابت صفيف

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

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

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

ثوابت الصفائف قيد التنفيذ

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

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

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

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

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

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

الشروع في العمل

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

يتناول هذا المقطع صيغ الصفائف، كما يشرح كيفية إدخالها وتحريرها واستكشاف أخطائها وإصلاحها.

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

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

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

ملاحظة   قد ترى بعض صيغ الصفائف التي أشير إليها بـ “صيغ CSE”، وذلك لأنك تضغط على المفاتيح CTRL+SHIFT+ENTER لإدخالها في المصنفات.

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

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

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

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

جربها بنفسك!

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

إنشاء صيغة صفيف متعددة الخلايا

  1. افتح مصنفًا جديدًا فارغًا.
  2. انسخ بيانات ورقة العمل النموذجية، ثم الصقها في المصنف الجديد بدءًا من الخلية A1.
مندوب المبيعات نوع السيارة العدد المباع سعر الوحدة المبيعات الإجمالية
حاتم سيارة سيدان 5 2200
سيارة رياضية 4 1800
جاكلين سيارة سيدان 6 2300
سيارة رياضية 8 1700
كامل سيارة سيدان 3 2000
سيارة رياضية 1 1600
مهدي سيارة سيدان 9 2150
سيارة رياضية 5 1950
أسامة سيارة سيدان 6 2250
سيارة رياضية 8 2000
  1. استخدم زر خيارات اللصق الذي يظهر في الناحية المجاورة لمطابقة تنسيق الوجهة.
  2. لضرب القيم داخل الصفيف (نطاق الخلايا من C2 إلى D11)، حدد الخلايا من E2 إلى E11، ثم أدخل الصيغة التالية في شريط الصيغة:

    ‎=C2:C11*D2:D11

  3. اضغط CTRL+SHIFT+ENTER.

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

b29c7e4d-e443-457c-b189-6dd9ab45e4f1

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

  1. في الخلية A13 من المصنف، اكتب المبيعات الإجمالية.
  2. في الخلية B13، اكتب الصيغة التالية، ثم اضغط على CTRL+SHIFT+ENTER:

    (SUM(C2:C11*D2:D11=

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

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

تتميز صيغ الصفائف أيضًا بما يلي:

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

نظرة إلى بناء جملة صيغة الصفيف

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

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

‎=C2*D2
‎=C3*D3

وما إلى ذلك. وتعمل الصيغة أحادية الخلايا في الخلية B13 على تكثيف كافة عمليات الضرب هذه، بالإضافة إلى العمليات الحسابية اللازمة لإضافة الإجماليات الفرعية هذه: ‎=E2+E3+E4، وغيرها.

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

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

عندما تستخدم صيغًا متعددة الخلايا، عليك أيضًا اتباع القواعد التالية:

  • يجب أن تحدد نطاق الخلايا للحصول على نتائجك قبل إدخال الصيغة. لقد قمت بذلك في الخطوة 3 من تمرين صيغة الصفيف متعددة الخلايا عندما حددت الخلايا من E2 إلى E11.
  • لا يمكنك تغيير محتويات خلية فردية في صيغة صفيف. لتجربة ذلك، حدد الخلية E3 في نموذج المصنف واضغط على DELETE.
  • يمكنك نقل صيغة صفيف بالكامل أو حذفها، ولكن لا يمكنك نقل جزء منها أو حذفه. بمعنى آخر، لتقليص صيغة صفيف، احذف الصيغة الحالية أولاً ثم ابدأ من جديد.

    تلميح   لحذف صيغة صفيف، حدد الصيغة بالكامل (على سبيل المثال، ‎=C2:C11*D2:D11)، واضغط على DELETE، ثم اضغط على CTRL+SHIFT+ENTER.

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

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

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

  1. في نموذج المصنف، امسح أي نص وكذلك أي صيغ أحادية الخلايا موجودة أسفل الجدول الرئيسي.
  2. قم بلصق خطوط البيانات الإضافية هذه داخل المصنف بدءًا من الخلية A12. استخدم زر خيارات اللصق الذي يظهر في الناحية المجاورة لمطابقة تنسيق الوجهة.
فادي سيارة سيدان 6 2500
سيارة رياضية 7 1900
أنطونيو سيارة سيدان 4 2200
سيارة رياضية 3 2000
نادر سيارة سيدان 8 2300
سيارة رياضية 8 2100
  1. حدد نطاق الخلايا الذي يحتوي على صيغة الصفيف الحالية (E2:E11)، والخلايا الفارغة (E12:E17) المجاورة للبيانات الجديدة. بمعنى آخر، حدد الخلايا E2:E17.
  2. اضغط على F2 للتبديل إلى وضع التحرير.
  3. في شريط الصيغة، قم بتغيير C11 إلى C17، وD11 إلى D17، ثم اضغط على CTRL+SHIFT+ENTER. سيقوم Excel بتحديث الصيغة في الخلايا من E2 إلى E11 ويضع مثيلاً للصيغة في الخلايا الجديدة من E12 إلى E17.

    221159cd-8cef-4c27-be28-5d1dc6998e5a

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

قد تبدو صيغ الصفائف ممتازة لكنها تتضمن بعض السيّئات أيضًا:

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

لمزيد من المعلومات حول استخدام صيغ الصفائف، انظر المواضيع التالية:

  • إنشاء صيغة صفيف تحسب نتيجة واحدة
  • إنشاء صيغة صفيف تحسب عدة نتائج

أعلى الصفحة

 

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

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

مقدمة موجزة حول ثوابت الصفائف

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

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

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

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

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

 

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

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

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

  1. استخدم المصنف من الأمثلة السابقة، أو أنشئ مصنفًا جديدًا.
  2. حدد الخلايا من A1 إلى E1.
  3. في شريط الصيغة، أدخل الصيغة التالية، ثم اضغط على CTRL+SHIFT+ENTER:

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

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

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

656baeb8-f52f-40d0-8495-f8321114f7aa

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

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

  1. في المصنف الخاص بك، حدد عمودًا مكونًا من خمس خلايا.
  2. في شريط الصيغة، أدخل الصيغة التالية واضغط على CTRL+SHIFT+ENTER:

    ‎={1;2;3;4;5}‎

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

e8750e75-ddd5-445c-9d21-817c9adf6828

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

  1. في المصنف الخاص بك، حدد كتلة خلايا من أربعة أعمدة عرضًا وثلاثة صفوف ارتفاعًا.
  2. في شريط الصيغة، أدخل الصيغة التالية، ثم اضغط على CTRL+SHIFT+ENTER:

    ‎={1,2,3,4;5,6,7,8;9,10,11,12}‎

تظهر أمامك النتيجة التالية:

3eec91f6-c859-41ca-a108-1e75a0cab580

 

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

الآن بعد أن أصبحت تجيد إدخال ثوابت الصفائف، إليك مثال بسيط حول استخدام ما تم شرحه:

  1. افتح ورقة عمل فارغة.
  2. انسخ الجدول التالي بدءًا من الخلية A1. استخدم زر خيارات اللصق الذي يظهر بجواره لمطابقة تنسيق الوجهة.
3 4 5 6 7
  1. في الخلية A3، أدخل الصيغة التالية، ثم اضغط على CTRL+SHIFT+ENTER:

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

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

9761df5b-21e1-4f5d-941f-f50196e7344f

تظهر القيمة 85 في الخلية A3. يشرح المقطع التالي كيفية عمل الصيغة.

 

نظرة إلى بناء جملة ثابت صفيف

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

cd2d3d3c-6ab4-4ba1-8f60-c7e94d205ca4

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. في المربع يشير إلى، أدخل الثابت التالي (تذكر كتابة الأقواس يدويًا):

    ‎={“يناير”،”فبراير”،”مارس”}

    ينبغي أن تظهر محتويات مربع الحوار على النحو التالي:

    8da54102-c216-465b-be80-b3c871cab06c

  4. انقر فوق موافق.
  5. في ورقة العمل، حدد صفًا من ثلاث خلايا فارغة.
  6. اكتب الصيغة التالية، ثم اضغط على CTRL+SHIFT+ENTER.

    ‏=الربع 1

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

4da8f5a1-b15a-4092-9580-bb3746d1b8f4

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

 

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

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

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

 

ثوابت الصفائف قيد التنفيذ

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

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

  1. حدد كتلة خلايا فارغة من أربعة أعمدة عرضًا وثلاثة صفوف ارتفاعًا.
  2. اكتب الصيغة التالية، ثم اضغط على CTRL+SHIFT+ENTER.

    ‎={1,2,3,4;5,6,7,8;9,10,11,12}*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. افتح مصنفًا موجودًا أو أنشئ مصنفًا جديدًا، وتأكد من احتوائه على ورقتي عمل فارغتين.
  2. انسخ البيانات الموجودة في الجدول التالي، والصقها في ورقة العمل بدءًا من الخلية A1.
400 من 1 2 3 4
1200 طلب 5 6 7 8
3200 العلا 9 10 11 12
475 سهر 13 14 15 16
500 الليالي
2000
600
1700
800
2700

ينبغي أن تظهر ورقة العمل النهائية بهذا الشكل.

3ad17341-ff50-4b38-b3a2-124de1adc55e

  1. قم بتسمية ورقة العمل الأولى بيانات، وقم بتسمية ورقة العمل الثانية الفارغة صفائف.

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

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

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

  1. في نموذج المصنف لديك، حدد ورقة العمل صفائف.
  2. حدد نطاق الخلايا من C1 إلى E3.
  3. أدخل الصيغة التالية في شريط الصيغة، ثم اضغط على CTRL+SHIFT+ENTER:

    ‎=Data!E1:G3

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

9b6f3343-8e70-4d5b-ba00-d4bc02336614

ترتبط الصيغة بالقيم المخزنة في الخلايا من E1 إلى G3 على ورقة العمل “بيانات”. ويتمثل الخيار البديل لصيغة الصفيف متعددة الخلايا هذه بوضع صيغة فريدة في كل خلية من خلايا ورقة العمل “صفائف”، على النحو التالي.

‎=Data!E1 ‎=Data!F1 ‎=Data!G1
‎=Data!E2 ‎=Data!F2 ‎=Data!G2
‎=Data!E3 ‎=Data!F3 ‎=Data!G3

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

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

  1. على ورقة العمل صفائف، حدد الخلايا من C1 إلى E3.
  2. اضغط على F2 للتبديل إلى وضع التحرير.
  3. اضغط على F9 لتحويل مراجع الخلايا إلى قيم. يحول Excel القيم إلى ثابت صفيف.
  4. اضغط على CTRL+SHIFT+ENTER لإدخال ثابت الصفيف كصيغة صفيف.

يستبدل Excel صيغة الصفيف ‎=Data!E1:G3‎ بثابت الصفيف التالي:

‎={1,2,3;5,6,7;9,10,11}‎

تم قطع الارتباط بين ورقتي العمل “بيانات” و”صفائف”، وتم استبدال صيغة الصفيف بثابت صفيف.

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

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

  • في ورقة العمل بيانات، أدخل الصيغة التالية في الخلية C7، ثم اضغط على CTRL+SHIFT+ENTER:

    ‎=SUM(LEN(C1:C5))‎

تظهر القيمة 47 في الخلية C7.

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

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

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

  1. على ورقة العمل بيانات، حدد الخلايا من A12 إلى A14.

    ينتج من مجموعة الخلايا هذه نتائج صيغة الصفيف نفسها.

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

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

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

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

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

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

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

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

  1. على ورقة العمل بيانات، حدد الخلايا من A12 إلى A14.
  2. اضغط على DELETE لإزالة الصيغة الحالية ولكن اترك الخلايا محددة.
  3. في شريط الصيغة، أدخل هذه الصيغة، ثم اضغط على CTRL+SHIFT+ENTER:

    ‎=LARGE(A1:A10,ROW(INDIRECT(“1:3”)))‎

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

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

‎=ROW(1:10)‎

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

‎=ROW(INDIRECT(“1:10”))‎

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

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

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

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

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

  • على ورقة العمل بيانات، امسح الصيغة الحالية من الخلية C7، وأدخل الصيغة التالية في هذه الخلية، ثم اضغط على CTRL+SHIFT+ENTER:

    ‎=INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)‎

تظهر القيمة العلا في الخلية C7.

فلننظر إلى الصيغة بدءًا من العناصر الداخلية في اتجاه الخارج. ترجع الدالة LEN طول كل عنصر من العناصر في نطاق الخلايا C1:C5. وتحسب الدالة MAX أكبر قيمة من بين هذه العناصر، وهي تتطابق مع أطول سلسلة نصية، أي تلك الموجودة في الخلية C3.

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

(MAX(LEN(C1:C5)‎)

وتحتل السلسلة الصفيف التالي:

(LEN(C1:C5

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

أخيرًا، تأخذ الدالة INDEX الوسيطات التالية: صفيف ورقم الصف والعمود داخل هذا الصفيف. يوفر نطاق الخلايا C1:C5 الصفيف، وتوفر الدالة 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. لاستخدام هذه الصيغة، يجب أن يكون للنطاقين الحجم نفسه والبعد نفسه:

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

أعلى الصفحة

الكاتب Geek4arab

Geek4arab

مواضيع متعلقة

اترك رداً