في Microsoft 365 for Mac أو Excel 2024 for Mac، ادمج CHOOSECOLS وTOCOL وHSTACK وFILTER لتحويل تسميات أصول الرسوم وكمياتها المتكررة إلى صفوف جرد. تحافظ الصيغة التطبيقية أدناه على محاذاة الأزواج والكميات الصفرية والإدخالات غير المكتملة.

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

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

تدرج Microsoft صراحةً Excel for Microsoft 365 for Mac وExcel 2024 for Mac ضمن الإصدارات الداعمة لكل دوال إعادة التشكيل الثلاث: CHOOSECOLS وTOCOL وHSTACK. استخدم أحد هذين الإصدارين للصيغة الأساسية.

يتضمن Excel 2021 for Mac المصفوفات الديناميكية وFILTER وLET وXLOOKUP، لكن ذلك لا يثبت دعمه لدوال إعادة التشكيل الأحدث. توضح وثائق ميزات Excel 2021 for Mac من Microsoft إمكانات المصفوفات والبحث المتاحة فيه؛ ولا تدرج صفحات الدوال الثلاث المذكورة أعلاه Excel 2021.

تحقق من الإصدار ونوع الترخيص في مربع الحوار حول في التطبيق. يوضح دليل إصدارات Office من Microsoft أن مربع الحوار على Mac يعرض كليهما. إذا كان إصدارك لا يتضمن دوال إعادة التشكيل، فاستخدم Microsoft 365 for Mac أو Excel 2024 for Mac للطريقة التلقائية، أو اتبع طريقة التكديس اليدوي الموضحة أدناه.

إعداد مثال افتراضي كامل

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

السمة 1 الكمية 1 السمة 2 الكمية 2 السمة 3 الكمية 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

يمثل كل زوج متجاور إدخالًا واحدًا في الجرد. يقرأ الناتج الأزواج عبر كل صف من المصدر قبل الانتقال إلى الصف التالي. تظهر Circle Tile مرتين لأنهما إدخالان منفصلان؛ ودمج التسميات المتكررة مهمة إضافية.

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

تحويل الأزواج المتكررة إلى صفوف جرد

أدخل Asset في H1 وQuantity في I1. أبقِ منطقة الناتج أسفل هذين العنوانين فارغة. الصق هذه الصيغة كاملة في H2، ثم اضغط Return. تُدخل صيغ المصفوفات الديناميكية مرة واحدة ثم تتمدد إلى الخلايا المجاورة، كما هو موضح في دليل صيغ المصفوفات من Microsoft.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

يُفترض أن يشغل الناتج H2:I9:

الأصل الكمية
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

تم التحقق من ترتيب الأزواج في المثال، وعدد الإدخالات الثمانية في الناتج، والحفاظ على الصفر، ومجموع الكميات البالغ 28 باستخدام عملية حسابية صغيرة وحتمية بلغة Python. كما روجعت الصيغة في Excel مقارنةً ببنية الدوال التي توثقها Microsoft؛ ولم تُنفذ في Excel.

كيف تحافظ الصيغة على محاذاة الأزواج

تمنح LET النتائج الوسيطة أسماء كي تتمكن الصيغة من إعادة استخدامها. الأسماء source وclean وlabels وquantities وkeep موجودة ضمن هذه الصيغة؛ ولا تحتاج إلى إنشاء نطاقات مسماة. توثق Microsoft هذا السلوك في مرجع LET.

تحدد CHOOSECOLS أعمدة التسميات 1 و3 و5، وأعمدة الكميات 2 و4 و6. هذه الأرقام هي مواضع داخل نطاق المصدر المحدد. تؤكد وثائق CHOOSECOLS من Microsoft أنها تُرجع الأعمدة المحددة من مصفوفة.

يستخدم كلا استدعاءَي TOCOL الدالة 0 للاحتفاظ بكل المواضع، والدالة FALSE للمسح عبر الصفوف. ولأنهما يجتازان المواضع المتطابقة بالترتيب نفسه، تبقى كل تسمية محاذية لكميتها. توثق Microsoft الوسيطين في مرجع TOCOL.

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

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

الحفاظ على الخلايا الفارغة والأصفار دون إخفاء الإدخالات غير المكتملة

تحمل الكمية الفارغة والصفر الرقمي معلومتين مختلفتين. الصفر عدد مُدخل؛ أما الخلية الفارغة فتحتاج إلى تفسير. توضح أمثلة Microsoft للدالة TOCOL أن الخلايا الفارغة غير المعالجة قد تُرجع صفرًا. لذلك تستخدم خطوة clean الدالة IF لإرجاع نص فارغ للخلايا المصدر الفارغة قبل إعادة التشكيل. توثق Microsoft قيم الإرجاع الشرطية في مرجع IF.

تُبقي الصيغة الأزواج غير المكتملة ظاهرة عمدًا. استخدم عمليات التحقق التالية قبل الاعتماد على النتيجة:

زوج المصدر سلوك الناتج الإجراء
تسمية وكمية رقمية يُبقي كليهما تحقق من أن الكمية مقصودة
تسمية وصفر يُبقي الصفر احتفظ به عندما يكون الصفر عددًا مقصودًا
الخليتان فارغتان يحذف الزوج لا حاجة إلى تصحيح
تسمية وكمية فارغة يُبقي التسمية والكمية الفارغة أكمل إدخال الكمية
تسمية فارغة وكمية يُبقي التسمية الفارغة والكمية أكمل إدخال التسمية

تجنب حذف الخلايا الفارغة بصورة مستقلة من مصفوفتَي التسميات والكميات. فإذا كانت تسمية مفقودة في موضع ما وكانت كمية مفقودة في موضع آخر، فقد يؤدي حذف الخلايا الفارغة من كل مصفوفة على حدة إلى إزاحة المصفوفتين وإنشاء أزواج غير صحيحة. الاحتفاظ بالمواضع إلى ما بعد الجمع هو خيار التصميم الأساسي في هذه الصيغة.

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

البحث عن إدخال واحد أو إرجاع كل الإدخالات المطابقة

أدخل Circle Tile في K2. لاسترداد كميتها الأولى، أدخل هذه الصيغة في L2:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

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

الناتج هو 4. يطلب وضع المطابقة الصريح 0 تطابقًا تامًا، ويُرجع البحث الافتراضي أول إدخال مطابق. يوثق مرجع XLOOKUP من Microsoft كلا السلوكين. ويؤكد أيضًا أن XLOOKUP غير متاحة في Excel 2016 وExcel 2019.

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

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

يعرض هذا صفين: Circle Tile بكمية 4 وCircle Tile بكمية 7. تُرجع FILTER الصفوف التي تحقق الشرط المحدد، كما هو موضح في مرجع FILTER من Microsoft. اختر صيغة أول تطابق عندما يجيبك إدخال مطابق واحد؛ واختر نتيجة التصفية عندما تحتاج إلى فحص كل مرات الظهور.

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

في العينة، تحقق من وجود ثمانية صفوف في الناتج ومن بقاء كمية Dot Pattern صفرًا. ثم قارن مجموع كميات المصدر بمجموع الكميات بعد إعادة التشكيل. أدخل الصيغتين التاليتين في خليتين فارغتين:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

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

لمزيد من صفوف المصدر، غيّر A2:F4 في الصيغة الأساسية إلى المستطيل الفعلي، مثل A2:F100. لن تُضمّن الصفوف الواقعة خارج هذا المرجع. ولأربعة أزواج متكررة، غيّر المصدر إلى ثمانية أعمدة، مثل A2:H100، وحدّث مواضع التسميات إلى 1,3,5,7 ومواضع الكميات إلى 2,4,6,8.

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

معالجة تعذّر ظهور الناتج أو استخدام طريقة التكديس اليدوي

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

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

لجدول بيانات صغير في Excel 2021 for Mac، يمكنك إنشاء البنية ذات العمودين نفسها يدويًا: انسخ A2:B4 إلى H2:I4، وانسخ C2:D4 إلى H5:I7، وانسخ E2:F4 إلى H8:I10. احذف الصفوف التي تكون الخليتان فيها فارغتين فقط. تكدّس هذه الطريقة الإدخالات بحسب كتلة الزوج بدلًا من ترتيب صفوف المصدر في الصيغة الأساسية، وتتطلب تكرار العمل بعد تغيّر المصدر.

بعد هذا الإعداد اليدوي، يمكن لـExcel 2021 for Mac استخدام إمكانات XLOOKUP الموثقة لديه مع نطاقات ثابتة:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

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

المصادر