استخدم FILTER لإرجاع كل كمية مرتبطة بتسمية أصل فني في Excel for Mac. يمكن أن تمتد المصفوفة الناتجة إلى خلايا ورقة العمل، أو تُستخدم في عملية حسابية أخرى، أو تُجمع من دون فقدان الإدخالات المطابقة المتكررة.
يستخدم هذا الشرح تسميات وكميات اصطناعية لأصول رسومية. تتمثل المهمة في تحديد تسمية واحدة، واسترجاع كل كمياتها، ثم إعادة استخدام تلك الأرقام الفردية في عملية حسابية. توثّق Microsoft أن FILTER تُرجع مصفوفة استنادًا إلى معايير محددة. دالة FILTER.
ما إصدارات Excel for Mac التي تدعم هذه الطريقة؟
تدرج وثائق الدالة الحالية من Microsoft صراحةً Excel for Microsoft 365 for Mac وExcel 2024 for Mac وExcel 2021 for Mac ضمن الإصدارات التي تدعم FILTER. استخدم أحد هذه الإصدارات للمعادلات أدناه. دالة FILTER.
لمعرفة الإصدار المثبّت لديك، افتح Excel واختر Excel → About Excel. توثّق Microsoft مسار القوائم هذا على Mac، ويوضح دليل تحديد الإصدار لديها أن نافذة «حول» في تطبيق Mac تعرض رقم الإصدار ونوع الترخيص. تحقّق من الإصدار إلى جانب رقم البنية. مشاركة المستندات مع إصدارات Office for Mac الأخرى؛ حول Office: ما إصدار Office الذي أستخدمه؟.
لا يندرج Excel 2019 for Mac وExcel 2016 for Mac ضمن الإصدارات المتاح فيها FILTER بحسب الوثائق. إذا كنت تستخدم أيًا منهما، فالمسار المدعوم لسير العمل هذا تحديدًا باستخدام المصفوفات الديناميكية هو فتح المصنف في أحد إصدارات Mac المذكورة. تعتمد التعليمات أدناه على هذا الشرط المسبق؛ وهي ليست حلًا بديلًا بصيغة مصفوفة قديمة.
إعداد مثال صغير لكميات الأصول
في ورقة عمل فارغة، أدخل البيانات التالية في A1:B7. يحتوي الصف الأول على العناوين. أدخل الكميات كأرقام، بما فيها الصفر في B6.
| صف ورقة العمل | العمود A: الأصل | العمود B: الكمية |
|---|---|---|
| 1 | الأصل | الكمية |
| 2 | Tile A | 4 |
| 3 | Arc B | 7 |
| 4 | Tile A | 2 |
| 5 | Dot C | 5 |
| 6 | Tile A | 0 |
| 7 | Tile A | 4 |
أدخل الأصل المحدد في D1 وTile A في D2. أدخل الكميات المطابقة في F1. اترك الخلايا أسفل F2 فارغة لإتاحة مساحة لامتداد النتيجة.
يتضمن هذا المثال عمدًا كميتين قيمتهما أربعة وكمية واحدة قيمتها صفر للتسمية نفسها. يجب أن تحافظ النتيجة الصحيحة على الصفوف الأربعة المطابقة كلها: 4 و2 و0 و4. حذف القيم المكررة أو استبعاد الصفر سيغيّر النتيجة المطلوبة.
إرجاع كل الكميات المطابقة باستخدام FILTER
حدد F2، وأدخل المعادلة التالية، ثم اضغط Return:
=FILTER(B2:B7,A2:A7=D2)
تستخدم المعادلة B2:B7 كمصفوفة للكميات وA2:A7=D2 كاختبار للتضمين. يغطي النطاقان صفوف المصدر الستة نفسها، بما يفي بمتطلب Microsoft أن يكون ارتفاع مصفوفة التضمين أو عرضها متوافقًا. دالة FILTER.
باستخدام بيانات المثال، تشغل النتيجة النطاق F2:F5:
| الخلية | النتيجة |
|---|---|
| F2 | 4 |
| F3 | 2 |
| F4 | 0 |
| F5 | 4 |
أدخل المعادلة في F2 فقط؛ إذ يضع Excel النتائج الإضافية تلقائيًا في الخلايا المجاورة. تسمي Microsoft هذا السلوك بالامتداد. لتغيير المعادلة لاحقًا، حرر الخلية العلوية اليسرى للنتيجة، وهي F2 هنا. معادلات المصفوفات الديناميكية وسلوك امتداد المصفوفات.
يمثل ظهور الرقم أربعة مرتين صفين منفصلين مطابقين في المصدر. احتفظ بهما كليهما عندما يجب أن تحتسب العملية الحسابية التالية كل ظهور للأصل المحدد.
لماذا لا تسترجع XLOOKUP هذه الصفوف الأربعة
تصف Microsoft XLOOKUP بأنها تُرجع العنصر المقابل لأول تطابق تعثر عليه. يمكنها إرجاع أعمدة متعددة من صف مطابق، لكن هذه الإمكانية لا تسرد كل صف يحتوي على التسمية نفسها. دالة XLOOKUP.
على سبيل المثال، تُرجع المعادلة التالية 4 فقط من المثال:
=XLOOKUP(D2,A2:A7,B2:B7)
تكون هذه النتيجة مناسبة عندما يوفّر صف مطابق واحد الإجابة. أما في هذه المهمة، فهناك أربعة صفوف منفصلة مطابقة توفر الكميات، لذا استخدم نتيجة FILTER.
إعادة استخدام المصفوفة في عمليات حسابية أخرى
أشر إلى النتيجة كاملةً باستخدام F2#. توثّق Microsoft علامة الرقم باعتبارها عامل تشغيل لنطاق الامتداد: فهي تشير إلى المصفوفة بأكملها الناتجة من خلية البداية. وهذا يجنبك تقييد معادلة لاحقة بنتيجة الخلايا الأربع الحالية. إرشادات وأمثلة على معادلات المصفوفات.
ضع المعادلات اللاحقة بجانب النتيجة بدلًا من أسفلها مباشرةً، واترك مساحة لمزيد من الكميات المطابقة. تستخدم الأمثلة التالية الأعمدة H إلى J.
جمع كل الكميات المطابقة
أدخل إجمالي الكمية في H1، ثم أدخل هذه المعادلة في H2:
=SUM(F2#)
النتيجة التوضيحية هي 10: أربعة زائد اثنين زائد صفر زائد أربعة. تقبل SUM النطاقات وتجمع قيمها الرقمية. دالة SUM.
إذا كنت تحتاج إلى الإجمالي فقط، يمكنك بدلًا من ذلك حسابه مباشرةً في خلية أخرى فارغة:
=SUM(FILTER(B2:B7,A2:A7=D2))
اختر المصفوفة الظاهرة عندما تريد فحص الكميات الفردية أو إعادة استخدامها. واختر المعادلة المتداخلة عندما تحتاج ورقة العمل إلى الإجمالي النهائي فقط.
ضرب كل كمية في عامل مشترك
لنفترض أن الرسم التوضيحي يتطلب ثلاث نسخ من كل كمية مطابقة. أدخل عدد النسخ في I1 والرقم 3 في I2. أدخل الكميات المعدلة في J1، ثم أدخل هذه المعادلة في J2:
=F2#*$I$2
النتائج المتوقعة في J2:J5 هي 12 و6 و0 و12. توثّق Microsoft عمليات حسابية على المصفوفات تضرب القيم وتُرجع نتائج متعددة، ويحدّد دليل الحاسبة لديها على Mac علامة النجمة بوصفها عامل الضرب. إرشادات وأمثلة على معادلات المصفوفات؛ استخدام Excel كآلة حاسبة في Excel for Mac.
للحصول على إجمالي معدل واحد، أدخل ما يلي في H4:
=SUM(F2#*$I$2)
الإجمالي المتوقع هو 30. هذه حسابات توضيحية باستخدام البيانات الاصطناعية المقدمة. جرى التحقق من العمليات الحسابية بشكل مستقل؛ وفُحصت معادلات المصنف بالرجوع إلى وثائق Microsoft بدلًا من تنفيذها في Excel for Mac.
تحديد ما ينبغي أن يحدث عند عدم وجود تطابق
غيّر D2 إلى Wave D، وهي تسمية غير موجودة في المثال. تُرجع معادلة FILTER الأصلية ذات الوسيطين #CALC! لأن Excel لا يستطيع إرجاع مصفوفة فارغة. توثّق Microsoft أن الوسيط الاختياري الثالث هو طريقة تحديد قيمة تُستخدم عند عدم وجود نتائج. كيفية تصحيح الخطأ #CALC!.
اختر هذه القيمة بما يتناسب مع احتياجات العملية الحسابية التالية. ينطبق دليل الاختيار التالي على ورقة العمل هذه:
| المتطلب | المعادلة أو الأسلوب | النتيجة |
|---|---|---|
| يجب أن تظل التطابقات المفقودة ظاهرة أثناء العمليات الحسابية | أبقِ =FILTER(B2:B7,A2:A7=D2) |
عدم وجود تطابق يُنتج #CALC!، ما يستدعي التحقق. |
| الغرض الأساسي من النتيجة هو عرض قائمة سهلة القراءة | استخدم =FILTER(B2:B7,A2:A7=D2,"No matches") |
عدم وجود تطابق يُرجع رسالة نصية. |
| يجب أن تضيف التسمية غير الموجودة صفرًا إلى الإجمالي | استخدم =SUM(FILTER(B2:B7,A2:A7=D2,0)) |
عدم وجود تطابق يُنتج إجماليًا يساوي صفرًا. |
اختيار الصفر كقيمة بديلة هو قاعدة حسابية مقصودة. ولا يعني أن هناك صفًا مطابقًا في المصدر كميته صفر. في هذا المثال، لـ Tile A كمية حقيقية تساوي صفرًا؛ أما Wave D فليس لها أي صفوف في المصدر. حافظ على هذا التمييز كلما كان وجود الأصل مهمًا.
ينبغي أيضًا التعامل بحذر مع الرسالة النصية: توثّق Microsoft أن SUM تتجاهل النص في الخلايا المشار إليها. ونتيجة لذلك، قد يؤدي جمع نتيجة لا تحتوي إلا على لا توجد تطابقات إلى صفر، ما يخفي عدم وجود صفوف مطابقة. استخدم أسلوب الخطأ الظاهر عندما ينبغي للعمليات الحسابية اللاحقة أن تتوقف عند غياب تسمية. دالة SUM.
أعِد D2 إلى Tile A قبل متابعة الأمثلة الرقمية الأصلية.
إضافة شرط ثانٍ فقط إذا كانت المهمة تتطلب ذلك
إذا طلبت مهمة لاحقة تحديدًا الكميات الموجبة، فاستخدم اختبار التسمية واختبار الكمية الموجبة معًا:
=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))
بالنسبة إلى المثال، تُرجع هذه المعادلة 4 و2 و4. توثّق Microsoft استخدام الضرب بين الشروط بوصفه اختبار AND في FILTER. يستبعد هذا التغيير الصفر المطابق عمدًا، لذا احتفظ بالمعادلة الأصلية عندما تحتاج إلى كل كمية مطابقة. دالة FILTER.
استكشاف أخطاء النتيجة وإصلاحها وضمان تضمين الصفوف الجديدة
إذا لم تُنتج المعادلة الكميات الأربع المتوقعة، فتحقق من ورقة العمل بهذا الترتيب:
- افحص مساحة الإخراج. تتسبب إعاقة نطاق النتيجة في ظهور
#SPILL!. انقل المحتوى المطلوب خارج مساحة الامتداد المقصودة قبل إعادة المحاولة. - تحقق من موضع المعادلة. تنتمي معادلات المصفوفات الممتدة إلى شبكة ورقة العمل خارج جدول Excel.
- تحقق من تبعيات المصنف. تدعم Microsoft المصفوفات الديناميكية المرتبطة بين المصنفات فقط عندما يكون كلا المصنفين مفتوحًا؛ وقد يؤدي التحديث بعد إغلاق المصدر إلى ظهور
#REF!.
ترد هذه القيود في معادلات المصفوفات الديناميكية وسلوك امتداد المصفوفات.
بعد ذلك، افحص تسميات المصدر وكمياته. قارن D2 بخلايا الأصل المقصودة، وتحقق من إدخال الكميات كأرقام. لا يكفي التحقق من معقولية الإجمالي وحده، لأن SUM قد تتجاهل القيم النصية. دالة SUM.
تشير معادلات المثال إلى صفوف المصدر من 2 إلى 7. إذا ألحقت بيانات في الصف 8، فعدّل نطاقي المصدر ليشملا الصف الجديد. على سبيل المثال، بعد إدخال Tile A في A8 والكمية الرقمية 3 في B8، استبدل المعادلة في F2 بما يلي:
=FILTER(B2:B8,A2:A8=D2)
تصبح المصفوفة المتوقعة 4 و2 و0 و4 و3، ويصبح الإجمالي 13. يتبع المرجع F2# النتيجة الموسعة، لكنه لا يستطيع جعل نطاقات المصدر الأصلية تشمل صفًا مستبعدًا.
بالنسبة إلى قائمة يزداد حجمها باستمرار، توصي Microsoft باستخدام جدول Excel مع مراجع منظمة، تتكيف عند إضافة صفوف الجدول أو حذفها. أبقِ مخرجات الامتداد خارج ذلك الجدول. معادلات المصفوفات الديناميكية وسلوك امتداد المصفوفات.
أخيرًا، تحقّق من النتائج الفردية قبل الاعتماد على الإجمالي: بالنسبة إلى صفوف المصدر الستة الأصلية، ينبغي أن يُرجع تحديد Tile A أربع كميات، تشمل الرقم أربعة مرتين والصفر. بعد مطابقة ذلك، أعد استخدام F2# في العمليات الحسابية، واختر قاعدة واضحة للتعامل مع عدم وجود تطابق بما يناسب غرض ورقة العمل.
المصادر
- دالة FILTER — دعم Microsoft.
- مشاركة المستندات مع إصدارات Office for Mac الأخرى — دعم Microsoft.
- حول Office: ما إصدار Office الذي أستخدمه؟ — دعم Microsoft.
- معادلات المصفوفات الديناميكية وسلوك امتداد المصفوفات — دعم Microsoft.
- دالة XLOOKUP — دعم Microsoft.
- إرشادات وأمثلة على معادلات المصفوفات — دعم Microsoft.
- دالة SUM — دعم Microsoft.
- استخدام Excel كآلة حاسبة في Excel for Mac — دعم Microsoft.
- كيفية تصحيح الخطأ #CALC! — دعم Microsoft.