اكسل

صيغ الصفيف الديناميكية في Excel

Dynamic Array Formulas Excel

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





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

التوفر

المصفوفات الديناميكية والوظائف الجديدة أدناه متوفرة فقط اكسل 365 . لا يقدم Excel 2016 و Excel 2019 دعم صيغة الصفيف الديناميكي. للراحة ، سأستخدم 'Dynamic Excel' (Excel 365) و 'Excel التقليدي' (2019 أو ما قبله) للتمييز بين الإصدارات أدناه.





جديد: فيديو تدريب صيغة المصفوفة الديناميكية

وظائف جديدة

كجزء من تحديث الصفيف الديناميكي ، يشتمل Excel الآن على 8 وظائف جديدة تستفيد بشكل مباشر من المصفوفات الديناميكية لحل المشكلات التي يصعب حلها تقليديًا باستخدام الصيغ التقليدية. انقر فوق الروابط أدناه للحصول على تفاصيل وأمثلة لكل وظيفة:

وظيفة غرض
منقي تصفية البيانات وإرجاع السجلات المطابقة
رانداري توليد مجموعة من الأرقام العشوائية
تسلسل توليد مجموعة من الأرقام المتسلسلة
نوع نطاق الفرز حسب العمود
صنف حسب نطاق الفرز حسب نطاق أو مجموعة أخرى
فريدة من نوعها استخراج القيم الفريدة من قائمة أو نطاق
XLOOKUP بديل حديث لـ VLOOKUP
XMATCH بديل حديث لوظيفة MATCH

فيديو: وظائف صفيف ديناميكية جديدة في Excel (حوالي 3 دقائق).



قرّب لأقرب مائة في Excel

ملحوظة: XLOOKUP و XMATCH لم تكن في المجموعة الأصلية لوظائف المصفوفة الديناميكية الجديدة ، لكنها تعمل بشكل رائع على محرك الصفيف الديناميكي الجديد. يحل XLOOKUP محل VLOOKUP ويقدم أسلوبًا حديثًا ومرنًا يستفيد من المصفوفات. XMATCH هو ترقية لوظيفة MATCH ، مما يوفر إمكانيات جديدة لـ الفهرس والمطابقة الصيغ.

مثال

قبل الدخول في التفاصيل ، دعونا نلقي نظرة على مثال بسيط. أدناه نحن نستخدم الجديد وظيفة فريدة لاستخراج قيم فريدة من النطاق B5: B15 ، مع a غير مرتبطة الصيغة التي تم إدخالها في E5:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

مثال دالة فريدة

والنتيجة هي قائمة بأسماء المدن الخمسة الفريدة ، والتي تظهر في E5: E9.

مثل جميع الصيغ ، سيتم تحديث UNIQUE تلقائيًا عندما تتغير البيانات. أدناه ، استبدلت فانكوفر بورتلاند في الصف 11. والنتيجة من UNIQUE تشمل الآن فانكوفر:

مثال دالة فريدة بعد التغيير

إراقة - صيغة واحدة ، العديد من القيم

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

يسمى المستطيل الذي يحيط بالقيم ' نطاق اللعبة '. ستلاحظ أن نطاق الانسكاب له تمييز خاص. في مثال UNIQUE أعلاه ، يكون نطاق الانسكاب هو E5: E10.

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

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

فيديو: الانسكاب ومدى الانسكاب

مرجع نطاق الانسكاب

للإشارة إلى نطاق الانسكاب ، استخدم رمز التجزئة (#) بعد الخلية الأولى في النطاق. على سبيل المثال ، للإشارة إلى النتائج من وظيفة UNIQUE أعلاه ، استخدم:

 
=E5# // reference UNIQUE results

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

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

 
= COUNTA (E5#) // count unique cities

مثال على مرجع نطاق انسكاب الصفيف الديناميكي

عندما يتغير نطاق الانسكاب ، ستعكس الصيغة أحدث البيانات.

تبسيط هائل

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

  • استخراج وسرد القيم الفريدة ( قبل | بعد، بعدما )
  • عد القيم الفريدة ( قبل | بعد، بعدما )
  • تصفية السجلات واستخراجها ( قبل | بعد، بعدما )
  • استخراج التطابقات الجزئية ( قبل | بعد، بعدما )

قوة الفرد

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

على سبيل المثال ، أدناه نستخدم وظيفة FILTER لاستخراج السجلات في المجموعة 'أ'. في الخلية F5 ، يتم إدخال صيغة واحدة:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

صيغة واحدة للصفيف الديناميكي مثال فقط

لاحظ أن كلا النطاقين عبارة عن مراجع نسبية غير مؤمنة ، لكن الصيغة تعمل بشكل مثالي.

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

وظائف التسلسل

تصبح الأشياء مثيرة للاهتمام حقًا عندما تقوم بربط أكثر من دالة مصفوفة ديناميكية معًا. ربما تريد فرز النتائج التي تم إرجاعها بواسطة UNIQUE؟ سهل. فقط قم بلف ملف دالة SORT حول وظيفة UNIQUE مثل هذا:

مثال على UNIQUE و SORT معًا

كما كان من قبل ، عندما تتغير بيانات المصدر ، تظهر نتائج فريدة جديدة تلقائيًا ، مرتبة بشكل جيد.

السلوك الأصلي

من المهم أن نفهم أن سلوك المصفوفة الديناميكية هو ملف أصلي ومتكامل بعمق . متي أي تُرجع الصيغة نتائج متعددة ، وستنتشر هذه النتائج في خلايا متعددة في ورقة العمل. يتضمن هذا الوظائف القديمة التي لم يتم تصميمها في الأصل للعمل مع المصفوفات الديناميكية.

على سبيل المثال ، في برنامج Excel التقليدي ، إذا قدمنا ​​الامتداد وظيفة LEN إلى نطاق من القيم النصية ، سنرى أ غير مرتبطة نتيجة. في Dynamic Excel ، إذا أعطينا الدالة LEN نطاقًا من القيم ، فسنرى مضاعف النتائج. توضح هذه الشاشة أدناه السلوك القديم على اليسار والسلوك الجديد على اليمين:

وظيفة LEN مع المصفوفات - القديمة والجديدة

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

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

سيعيد VLOOKUP أعمدة متعددة:

نتائج متعددة باستخدام VLOOKUP والمصفوفات الديناميكية

بمعنى آخر ، على الرغم من أن VLOOKUP لم يتم تصميمه أبدًا لإرجاع قيم متعددة ، إلا أنه يمكنه الآن القيام بذلك ، بفضل محرك الصيغة الجديد في Dynamic Excel.

كل الصيغ

أخيرًا ، لاحظ أن المصفوفات الديناميكية تعمل مع كل الصيغ ليس فقط المهام . في المثال أدناه ، تحتوي الخلية C5 على صيغة واحدة:

جعل الحرف الأول في التفوق
 
=B5:B14*C4:L4

تتسرب النتيجة إلى نطاق 10 × 10 يتضمن 100 خلية:

جدول ضرب الصفيف الديناميكي

ملاحظة: في Excel التقليدي ، يمكنك مشاهدة نتائج متعددة تُرجع بواسطة صيغة الصفيف إذا كنت تريد ذلك استخدم F9 لفحص الصيغة . ولكن ما لم تدخل الصيغة كملف صيغة صفيف متعددة الخلايا ، سيتم عرض قيمة واحدة فقط في ورقة العمل.

المصفوفات تتجه نحو الاتجاه السائد

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

 
{1,2,3} // horizontal array {123} // vertical array

Array هو مصطلح برمجة يشير إلى قائمة العناصر التي تظهر بترتيب معين. السبب وراء ظهور المصفوفات كثيرًا في صيغ Excel هو أن المصفوفات يمكنها ذلك تعبر بشكل مثالي عن القيم في نطاق من الخلايا .

فيديو: ما هي المصفوفة؟

تصبح عمليات المصفوفة مهمة

نظرًا لأن صيغ Excel الديناميكية يمكن أن تعمل بسهولة مع قيم متعددة ، ستصبح عمليات الصفيف أكثر أهمية. يشير المصطلح 'عملية المصفوفة' إلى تعبير يقوم بإجراء اختبار منطقي أو عملية حسابية على مصفوفة. على سبيل المثال ، يختبر التعبير أدناه ما إذا كانت القيم في B5: B9 تساوي 'ca'

 
=B5:B9='ca' // state = 'ca'

اختبار مثال عملية الصفيف أ

نظرًا لوجود 5 خلايا في B5: B9 ، تكون النتيجة 5 قيم TRUE / FALSE في صفيف:

 
{FALSETRUEFALSETRUETRUE}

تتحقق عملية الصفيف أدناه من المبالغ الأكبر من 100:

 
=C5:C9>100 // amounts > 100

اختبار مثال عملية الصفيف ب

تجمع عملية الصفيف النهائية بين الاختبار A والاختبار B في تعبير واحد:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

مثال على عملية صفيف اختبار أ و ب

ملاحظة: يقوم Excel تلقائيًا بفرض قيم TRUE و FALSE على 1 و 0 أثناء عملية الرياضيات.

لإعادة هذا إلى صيغ الصفيف الديناميكية في Excel ، يوضح المثال أدناه كيف يمكننا استخدام نفس عملية الصفيف بالضبط داخل وظيفة FILTER مثل يشمل جدال:

عملية صفيف مع وظيفة التصفية

تُرجع الدالة FILTER السجلين حيث تكون الحالة = 'ca' والمبلغ> 100.

للحصول على مظاهرة ، انظر: كيفية التصفية بمعيارين (فيديو).

صيغ الصفيف الجديدة والقديمة

في Dynamic Excel ، ليست هناك حاجة لإدخال صيغ الصفيف باستخدام control + shift + enter. عند إنشاء صيغة ، يتحقق Excel مما إذا كانت الصيغة قد ترجع قيمًا متعددة. إذا كان الأمر كذلك ، فسيتم حفظه تلقائيًا كصيغة صفيف ديناميكية ، لكنك لن ترى الأقواس المتعرجة. يوضح المثال أدناه صيغة صفيف نموذجية تم إدخالها في Dynamic Excel:

صيغة الصفيف الأساسية في Excel التقليدي

إذا فتحت نفس الصيغة في Excel التقليدي ، فسترى الأقواس المتعرجة:

صيغة الصفيف الأساسية في Excel الديناميكي

الذهاب في الاتجاه الآخر ، عندما يتم فتح صيغة صفيف 'تقليدية' في Dynamic Excel ، سترى الأقواس المتعرجة في شريط الصيغة. على سبيل المثال ، تُظهر الشاشة أدناه صيغة صفيف بسيطة في Excel التقليدي:

صيغة صفيف بسيطة ذات أقواس متعرجة مرئية

ومع ذلك ، إذا أعدت إدخال الصيغة بدون أي تغييرات ، فستتم إزالة الأقواس المتعرجة ، وتُرجع الصيغة نفس النتيجة:

صيغة صفيف بسيطة ذات أقواس متعرجة غير مرئية

خلاصة القول هي أن صيغ الصفيف التي تم إدخالها باستخدام control + shift + enter (CSE) لا تزال تعمل للحفاظ على التوافق ، ولكن لا يجب عليك إدخال صيغ الصفيف باستخدام محرك البحث المخصص في Dynamic Excel.

الشخصية

مع إدخال المصفوفات الديناميكية ، سترى الحرف @ يظهر في كثير من الأحيان في الصيغ. يمكّن الحرف @ سلوكًا يُعرف باسم ' تقاطع ضمني '. التقاطع الضمني هو عملية منطقية حيث يتم تقليل العديد من القيم إلى قيمة واحدة.

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

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

بمعنى آخر ، تتم إضافة @ لمنع الصيغة القديمة من إراقة نتائج متعددة على ورقة العمل. اعتمادًا على الصيغة ، قد تتمكن من إزالة الحرف @ ولن يتغير سلوك الصيغة.

ملخص

  • ستجعل المصفوفات الديناميكية كتابة بعض الصيغ أسهل بكثير.
  • يمكنك الآن تصفية البيانات المطابقة وفرز القيم الفريدة واستخراجها بسهولة باستخدام الصيغ.
  • يمكن ربط صيغ الصفيف الديناميكية (متداخلة) للقيام بأشياء مثل التصفية والفرز.
  • الصيغ التي ترجع أكثر من قيمة سوف تنسكب تلقائيًا.
  • ليس من الضروري استخدام Ctrl + Shift + Enter لإدخال صيغة صفيف.
  • تتوفر صيغ الصفيف الديناميكية فقط في Excel 365.
المؤلف ديف برونز


^