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

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

ما إصدارات Excel for Mac التي تدعم هذه الطريقة؟

استخدم Excel for Microsoft 365 for Mac أو Excel 2024 for Mac أو Excel 2021 for Mac. تسرد وثائق Microsoft هذه الإصدارات لكل من SEQUENCE وXLOOKUP، كما يوثّق دليل ميزات Excel 2021 for Mac المصفوفات الديناميكية صراحةً. دالة SEQUENCE، دالة XLOOKUP، ما الجديد في Excel 2021 for Mac.

توافر الدوال مهم عند نسخ الصيغ من شروحات أخرى. فعلى سبيل المثال، تدرج Microsoft الدالة VSTACK لإصداري Microsoft 365 وExcel 2024 على Mac، لكنها لا تدرجها في Excel 2021. تتجنب الطريقة هنا هذا الاعتماد. وتذكر Microsoft أيضاً صراحةً أن XLOOKUP غير متاحة في Excel 2016 وExcel 2019، لذا لا يمكن لهذه الإصدارات تشغيل الصيغ أدناه كما هي. دالة VSTACK، دالة XLOOKUP.

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

في ورقة عمل فارغة، أدخل البيانات الافتراضية التالية في A1:C4. يحتوي العمود الأول على تسميات الأصول، ويحتوي صف العناوين على التنسيقات، أما الخلايا المتبقية فتحتوي على الكميات.

الأصل صغير كبير
بلاطة قوسية 2 0
بلاطة منقطة 1 0
بلاطة متموجة 2 1

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

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

تسطيح المصفوفة في قائمة مساعدة يسهل التحقق منها

اترك العمود D فارغاً. في E1:I1، أدخل العناوين Position وAsset وFormat وQuantity وEnd item. أدخل الأرقام من واحد إلى ستة في E2:E7 بالترتيب. هناك ستة صفوف مساعدة لأن ضرب ثلاثة أصول في تنسيقين ينتج ست تركيبات.

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

استرجاع تسميات كل تركيبة وكميتها

أدخل هذه الصيغة في F2:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

أدخل هذه الصيغة في G2:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

أدخل هذه الصيغة في H2:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

انسخ F2:H2 إلى الأسفل حتى الصف السابع. هذه صيغ خلايا عادية، وتنتج كل خلية نتيجة واحدة. تسترجع INDEX قيمة في صف وعمود محددين. وهنا تجعل INT صف المصدر يتقدم بعد كل موضعين، بينما تجعل MOD عمود المصدر يتناوب بين التنسيقين. دالة INDEX، دالة INT، دالة MOD.

حساب رقم العنصر الأخير لكل تركيبة

في I2، أدخل الصيغة التالية وانسخها إلى الأسفل حتى I7:

=SUM($H$2:H2)

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

ينبغي أن تبدو القائمة المساعدة المكتملة كما يلي:

الموضع الأصل التنسيق الكمية العنصر الأخير
1 بلاطة قوسية صغير 2 2
2 بلاطة قوسية كبير 0 2
3 بلاطة منقطة صغير 1 3
4 بلاطة منقطة كبير 0 3
5 بلاطة متموجة صغير 2 5
6 بلاطة متموجة كبير 1 6

اعتبر End item حداً فاصلاً. تشغل التركيبة الأولى رقمي العنصر واحداً واثنين. وتشغل التركيبة الثالثة رقم العنصر ثلاثة. وتشغل الخامسة الرقمين أربعة وخمسة، فيما تشغل السادسة الرقم ستة. لا تحرّك الكمية صفر الحد إلى الأمام، لذا لا تحصل على أي أرقام للعناصر.

إنشاء صف إخراج واحد لكل عنصر

أدخل Asset في K1، وFormat في L1. اترك الخلايا أسفل كلا العنوانين فارغة. ضع الإخراج في خلايا عادية في ورقة العمل خارج جدول Excel: توثّق Microsoft أن صيغ المصفوفات المنسكبة غير مدعومة داخل الجداول. صيغ المصفوفات الديناميكية وسلوك انسكاب المصفوفات.

أدخل هذه الصيغة الكاملة في K2:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))

أدخل هذه الصيغة الكاملة في L2:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))

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

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

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

بالنسبة إلى المصفوفة المقدمة، ينبغي أن يحتوي K1:L7 على ما يلي:

الأصل التنسيق
بلاطة قوسية صغير
بلاطة قوسية صغير
بلاطة منقطة صغير
بلاطة متموجة صغير
بلاطة متموجة صغير
بلاطة متموجة كبير

يستخدم عمودا الإخراج أرقام العناصر المولدة ونطاق الحدود نفسيهما. يتغير نطاق الإرجاع فقط: يوفّر العمود F تسميات الأصول، ويوفّر العمود G التنسيقات. حافظ على تزامن مدخلات البحث هذه عند تعديل الصيغ.

التحقق من النتيجة قبل توسيع المصدر

أجرِ ثلاثة فحوص باستخدام بيانات المثال:

  1. قارن الإجماليين: يجب أن تُرجع كل من =SUM(B2:C4) و=SUM(H2:H7) القيمة ستة.
  2. قارن التركيبات: يجب أن يحتوي الإخراج على التركيبات الأربع غير الصفرية بأعداد التكرار المذكورة.
  3. غيّر B2 من اثنين إلى ثلاثة: تصبح النتيجة المتوقعة سبعة صفوف، تتضمن ثلاثة إدخالات لبلاطة قوسية صغيرة. أعد القيمة إلى اثنين بعد ذلك.

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

إضافة مزيد من الأصول أو التنسيقات عن قصد

يستخدم تغيير الكميات داخل B2:C4 الإعداد الحالي. أما زيادة أبعاد المصفوفة فتتطلب تغيير المراجع وطول القائمة المساعدة.

في المثال الكامل، وسّع المصدر ليشمل أربعة أصول وثلاثة تنسيقات في A1:D5. انقل المنطقة المساعدة أو أبقها في E:I، وأدخل الأرقام من واحد إلى اثني عشر في E2:E13، واستبدل الصيغ في F2:H2 بما يلي:

=INDEX($A$2:$A$5,INT((E2-1)/3)+1)

=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)

=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)

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

معالجة الإخراج غير المكتمل أو المحجوب

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

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

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

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

المصادر