المصفوفات الديناميكية في Excel

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

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

فكر في مثال بسيط لشرح الجوهر.

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

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

حسنًا ، أو سيكون من الممكن لف هذا النطاق في نوع من دالة التجميع مثل = SUM (B2: C4) والحصول على إجمالي إجمالي لها.

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

الآن كل شيء مختلف.

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

هذا ليس سحرًا ، ولكن المصفوفات الديناميكية الجديدة التي يمتلكها Microsoft Excel الآن. اهلا بك في العالم الجديد

ميزات العمل مع المصفوفات الديناميكية

من الناحية الفنية ، يتم تخزين صفيفنا الديناميكي بالكامل في الخلية الأولى G4 ، وملء العدد المطلوب من الخلايا إلى اليمين والأسفل ببياناتها. إذا حددت أي خلية أخرى في المصفوفة ، فسيكون الارتباط الموجود في شريط الصيغة غير نشط ، مما يوضح أننا في إحدى الخلايا "الفرعية":

لن تؤدي محاولة حذف خلية "فرعية" واحدة أو أكثر إلى أي شيء - سيقوم Excel على الفور بإعادة حسابها وتعبئتها.

في الوقت نفسه ، يمكننا الإشارة بأمان إلى هذه الخلايا "الفرعية" في صيغ أخرى:

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

إذا احتجنا إلى تحريك المصفوفة ، فسيكون ذلك كافيًا للتحرك (بالماوس أو مجموعة من CTRL+X, CTRL+V) ، مرة أخرى ، فقط الخلية الرئيسية الأولى G4 - بعد ذلك ، سيتم نقلها إلى مكان جديد وسيتم توسيع صفيفنا بالكامل مرة أخرى.

إذا كنت بحاجة إلى الإشارة في مكان آخر على الورقة إلى المصفوفة الديناميكية التي تم إنشاؤها ، فيمكنك استخدام الحرف الخاص # ("الجنيه") بعد عنوان الخلية البادئة:

على سبيل المثال ، يمكنك الآن بسهولة إنشاء قائمة منسدلة في خلية تشير إلى الصفيف الديناميكي الذي تم إنشاؤه:

أخطاء الصفيف الديناميكي

ولكن ماذا يحدث إذا لم تكن هناك مساحة كافية لتوسيع المصفوفة ، أو إذا كانت هناك خلايا مشغولة بالفعل ببيانات أخرى في مسارها؟ تعرف على نوع جديد من الأخطاء بشكل أساسي في Excel - #نقل! (#تسرب!):

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

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

المصفوفات الديناميكية والجداول الذكية

إذا كانت الصفيف الديناميكي يشير إلى جدول "ذكي" تم إنشاؤه بواسطة اختصار لوحة المفاتيح CTRL+T او بواسطة الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول)، ثم سيرث أيضًا جودته الرئيسية - التحجيم التلقائي.

عند إضافة بيانات جديدة إلى أسفل أو إلى اليمين ، سيتم أيضًا توسيع الجدول الذكي والنطاق الديناميكي تلقائيًا:

ومع ذلك ، هناك قيد واحد: لا يمكننا استخدام مرجع النطاق الديناميكي في المنتديات داخل جدول ذكي:

المصفوفات الديناميكية وميزات Excel الأخرى

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

ليس تماما.

المصفوفات الديناميكية ليست مجرد أداة أخرى في Excel. الآن هم جزء لا يتجزأ من قلب (أو دماغ) Microsoft Excel - محرك الحساب الخاص به. هذا يعني أن صيغ Excel الأخرى والوظائف المألوفة لنا الآن تدعم أيضًا العمل مع المصفوفات الديناميكية. دعنا نلقي نظرة على بعض الأمثلة لنقدم لك فكرة عن عمق التغييرات التي حدثت.

تبديل موضع

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

الآن يمكنك فقط تحديد خلية واحدة ، وإدخال نفس الصيغة فيها والنقر فوق العادي أدخل - المصفوفة الديناميكية ستفعل كل شيء من تلقاء نفسها:

جدول الضرب

هذا هو المثال الذي اعتدت تقديمه عندما طُلب مني تصور فوائد صيغ الصفيف في Excel. الآن ، لحساب جدول فيثاغورس بالكامل ، يكفي الوقوف في الخلية الأولى B2 ، أدخل هناك صيغة تضاعف صفيفتين (مجموعة الأرقام الرأسية والأفقية 1..10) وانقر ببساطة على أدخل:

الإلتصاق وتحويل العلبة

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

أعلى الخاتمة 3

لنفترض أن لدينا مجموعة من الأرقام التي نريد استخلاص النتائج الثلاثة الأولى منها ، وترتيبها تنازليًا. الآن يتم ذلك من خلال صيغة واحدة ، ومرة ​​أخرى ، بدون أي صيغة CTRL+تغير+أدخل مثل السابق:

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

يقوم VLOOKUP باستخراج أعمدة متعددة مرة واحدة

وظائف VPR (البحث) الآن يمكنك سحب القيم ليس من واحد ، ولكن من عدة أعمدة في وقت واحد - فقط حدد أرقامها (بأي ترتيب مرغوب فيه) كمصفوفة في الوسيطة الثالثة للدالة:

دالة OFFSET تعيد مصفوفة ديناميكية

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

الآن هذه المشكلة في الماضي. شاهد كيف يمكنك الآن ، باستخدام صيغة واحدة ومصفوفة ديناميكية تم إرجاعها بواسطة OFFSET ، استخراج جميع الصفوف لمنتج معين من أي جدول تم فرزه:

دعنا نلقي نظرة على حججها:

• A1 - خلية البداية (نقطة مرجعية)
• ПОИСКПОЗ (F2 ؛ A2: A30 ؛ 0) - حساب التحول من خلية البداية إلى أسفل - إلى الملفوف الأول الذي تم العثور عليه.
• 0 - تحول "النافذة" إلى اليمين بالنسبة إلى خلية البداية
  
• СЧЁТЕСЛИ (A2: A30 ؛ F2) - حساب ارتفاع "النافذة" التي تم إرجاعها - عدد الخطوط التي يوجد بها ملفوف.
• 4 - حجم "النافذة" أفقيًا ، أي الناتج 4 أعمدة

وظائف جديدة للصفائف الديناميكية

بالإضافة إلى دعم آلية الصفيف الديناميكي في الوظائف القديمة ، تمت إضافة العديد من الوظائف الجديدة تمامًا إلى Microsoft Excel ، والتي تم شحذها خصيصًا للعمل مع المصفوفات الديناميكية. على وجه الخصوص ، هذه هي:

• درجة (فرز) - يفرز نطاق الإدخال وينتج مصفوفة ديناميكية على الإخراج
  
• سوربو (صنف حسب) - يمكن فرز نطاق واحد بالقيم من نطاق آخر
  
• منقي (منقي) - يسترجع الصفوف من نطاق المصدر التي تفي بالشروط المحددة
• فريدة من نوعها (فريدة من نوعها) - يستخرج قيمًا فريدة من نطاق أو يزيل التكرارات
  
• SLMASSIVE (رانداري) - يولد مجموعة من الأرقام العشوائية بحجم معين
• بعد الولادة (تسلسل) - تشكل مصفوفة من سلسلة من الأرقام بخطوة معينة
  

المزيد عنهم - بعد ذلك بقليل. إنها تستحق مقالة منفصلة (وليست واحدة) للدراسة المتعمقة 🙂

استنتاجات

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

تلخيص النتائج ، плюсы المصفوفات الديناميكية ، يمكنك كتابة ما يلي:

• يمكنك أن تنسى الجمع CTRL+تغير+أدخل. لا يرى Excel الآن أي فرق بين "الصيغ العادية" و "صيغ الصفيف" ويعاملها بنفس الطريقة.
• حول الوظيفة SUMPRODUCT (منتج SUM)، والتي تم استخدامها سابقًا لإدخال صيغ الصفيف بدون CTRL+تغير+أدخل يمكنك أيضًا أن تنسى - الآن أصبح الأمر سهلاً بدرجة كافية مجموع и أدخل.
• تدعم الجداول الذكية والوظائف المألوفة (SUM و IF و VLOOKUP و SUMIFS وما إلى ذلك) الآن المصفوفات الديناميكية بشكل كلي أو جزئي.
• يوجد توافق مع الإصدارات السابقة: إذا فتحت مصنفًا باستخدام مصفوفات ديناميكية في إصدار قديم من Excel ، فسوف تتحول إلى صيغ صفيف (بأقواس معقوفة) وتستمر في العمل "بالنمط القديم".

وجدت بعض الأرقام سلبيات:

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

بالطبع ، هذه ليست النهاية ، وأنا متأكد من أن Microsoft ستواصل تحسين هذه الآلية في المستقبل.

أين يمكنني التنزيل؟

وأخيرًا ، السؤال الرئيسي 🙂

أعلنت Microsoft لأول مرة وعرضت معاينة للمصفوفات الديناميكية في Excel مرة أخرى في سبتمبر 2018 في مؤتمر إشعال. في الأشهر القليلة التالية ، كان هناك اختبار شامل وتشغيل للميزات الجديدة ، أولاً القطط موظفي Microsoft نفسها ، ثم في اختبار المتطوعين من دائرة Office Insiders. هذا العام ، بدأ طرح التحديث الذي يضيف مصفوفات ديناميكية تدريجياً لمشتركي Office 365 العاديين. على سبيل المثال ، لقد استلمته فقط في أغسطس من خلال اشتراكي في Office 365 Pro Plus (شهري مستهدف).

إذا لم يكن لدى Excel الخاص بك مصفوفات ديناميكية حتى الآن ، ولكنك تريد حقًا العمل معهم ، فهناك الخيارات التالية:

• إذا كان لديك اشتراك في Office 365 ، يمكنك ببساطة الانتظار حتى يصل إليك هذا التحديث. تعتمد سرعة حدوث ذلك على عدد المرات التي يتم فيها تسليم التحديثات إلى Office (مرة في السنة ، مرة كل ستة أشهر ، مرة في الشهر). إذا كان لديك جهاز كمبيوتر خاص بالشركة ، فيمكنك أن تطلب من المسؤول إعداد التحديثات ليتم تنزيلها مرات أكثر.
  
• يمكنك الانضمام إلى صفوف المتطوعين في اختبار Office Insiders - ثم ستكون أول من يتلقى جميع الميزات والوظائف الجديدة (ولكن هناك فرصة لزيادة الأخطاء في برنامج Excel ، بالطبع).
• إذا لم يكن لديك اشتراك ، ولكن لديك إصدار مستقل محاصر من Excel ، فسيتعين عليك الانتظار حتى إصدار الإصدار التالي من Office و Excel في عام 2022 ، على الأقل. يتلقى مستخدمو هذه الإصدارات تحديثات الأمان وإصلاحات الأخطاء فقط ، وتنتقل جميع "الأشياء الجيدة" الجديدة الآن إلى مشتركي Office 365 فقط. حزين ولكنه حقيقي 🙂
  

على أي حال ، عندما تظهر المصفوفات الديناميكية في Excel - بعد هذه المقالة ، ستكون جاهزًا لها 🙂

• ما هي صيغ الصفيف وكيفية استخدامها في Excel
• تجميع النافذة (النطاق) باستخدام دالة OFFSET
• 3 طرق لتحويل جدول في Excel