استخدم FILTER لعزل فئة محددة بدقة من الأصول الفنية، ثم XLOOKUP لإرجاع المستوى عند أكبر حدّ للكمية لا يتجاوز العدد لديك. يوضح هذا الدليل مثالًا متكاملًا في Excel for Mac باستخدام تسميات افتراضية واختبارات للحدود.

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

تستخدم الصيغة أدناه LET وFILTER وXLOOKUP. تسرد وثائق Microsoft الدوال الثلاث في Excel for Microsoft 365 for Mac وExcel 2024 for Mac وExcel 2021 for Mac. كما تشير Microsoft إلى هذه الدوال في نظرتها العامة إلى الجديد في Excel 2021 for Mac.

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

إذا لم تكن متأكدًا من الإصدار المثبّت، فتحقّق من الإصدار ونوع الترخيص في مربع الحوار «حول» بالتطبيق. توضّح Microsoft كيفية التعرّف على هذه التفاصيل في حول Office: ما إصدار Office الذي أستخدمه؟.

إعداد الحد الأدنى للكميات حسب الفئة

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

في ورقة عمل فارغة، أدخل هذا الجدول في A1:C7. يوجد رأس الجدول في الصف 1، والصفوف الستة للبيانات في الصفوف 2–7. أدخل الحدود الدنيا للكميات كأرقام واملأ كل خلايا الفئة والمستوى.

الفئة الحد الأدنى للكمية تسمية المستوى
بلاطة نسيجية 5 مدمج
بلاطة نسيجية 20 موسّع
بلاطة نسيجية 50 واسع
ختم أشكال 5 مدمج
ختم أشكال 30 موسّع
ختم أشكال 80 واسع

بعد ذلك، أدخل الفئة المطلوب التحقق منها في E1، والكمية في F1، ومستوى المخزون في G1. أدخل Texture Tile في E2 والعدد 34 في F2. اترك العمود D فارغًا للفصل بين البيانات المرجعية والمدخلات.

اعتبر الحدود الدنيا للكميات حدودًا دنيا شاملة. بالنسبة إلى Texture Tile، يبدأ المستوى Compact عند 5، ويبدأ Expanded عند 20، ويبدأ Extensive عند 50. لذلك، ينتمي العدد 34 إلى Expanded. أما بالنسبة إلى Shape Stamp، فتبدأ التسميات نفسها عند كميات مختلفة: فالعدد 20 لا يزال ينتمي إلى Compact.

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

إدخال صيغة الفئة والكمية كاملة

حدّد G2، والصق الصيغة الكاملة التالية في شريط الصيغة، ثم اضغط على Return. تستخدم الصيغة تخطيط ورقة العمل أعلاه كما هو؛ ولا تتطلب أي نطاقات مسماة أو أعمدة مساعدة.

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

بالنسبة إلى Texture Tile و34، النتيجة المتوقعة هي Expanded. تُرجع الصيغة تسمية واحدة رغم أن الحسابات الوسيطة تستخدم مصفوفات. تستخدم صيغ المصفوفات الديناميكية الحديثة الإدخال العادي؛ وتوثّق Microsoft تأكيدها بالضغط على Enter في صيغ المصفوفات الديناميكية وسلوك انسكاب النتائج.

كيف يحافظ عامل تصفية الفئة على محاذاة المصفوفات

ينشئ التعبير categories=category مصفوفة التضمين المسماة keep. في هذا المثال، يختار التعبير صفوف Texture Tile الثلاثة. ويستخدم كلا الاستدعاءين لـ FILTER المصفوفة نفسها، فينتج عن ذلك الحدود الدنيا 5 و20 و50 إلى جانب التسميات المقابلة Compact وExpanded وExtensive.

توثّق Microsoft أن FILTER يختار البيانات باستخدام مصفوفة تضمين منطقية تتطابق أبعادها مع نطاق المصدر. يضمن استخدام مصفوفة التضمين نفسها لكلا العمودين بقاء التطابق بينهما. راجع دالة FILTER.

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

كيف تحدد الكمية المستوى

تطلب الوسيطة الخامسة في XLOOKUP، وهي -1، تطابقًا تامًا للكمية أو استخدام القيمة الأصغر التالية. ضمن صفوف Texture Tile بعد التصفية، يختار العدد 34 الحد الأدنى 20 ويُرجع Expanded. ويختار العدد المطابق تمامًا 50 المستوى Extensive. تحدد Microsoft هذه الأوضاع في وثائق دالة XLOOKUP.

كما تميّز الصيغة بين فئة غير معروفة وعدد يقل عن أول مستوى. تحصي SUM(--keep) صفوف الفئة المطابقة. إذا لم يوجد أي صف، تُرجع الصيغة Category not found قبل محاولة البحث في البيانات المصفّاة. وإذا كانت الفئة موجودة لكن كل حدودها الدنيا تتجاوز العدد، يُرجع البحث Below first tier.

اختبار الحدود وعزل الفئات

استبدل E2 وF2 بالأزواج التالية، زوجًا واحدًا في كل مرة. هذه الاختبارات أنفع من اختبار قيمة متوسطة فقط؛ فهي تختبر الحد الأول والحدود الدقيقة واختلافات الفئات والفئات غير الموجودة.

الفئة في E2 الكمية في F2 النتيجة المتوقعة
Texture Tile 0 Below first tier
Texture Tile 5 Compact
Texture Tile 19 Compact
Texture Tile 20 Expanded
Texture Tile 34 Expanded
Texture Tile 50 Extensive
Shape Stamp 20 Compact
Shape Stamp 34 Expanded
Shape Stamp 100 Extensive
Color Swatch 34 Category not found

الاختباران اللذان يستخدمان العدد 20 كاشفان على نحو خاص. يجب أن تُرجع Texture Tile القيمة Expanded، بينما يجب أن تُرجع Shape Stamp القيمة Compact. إذا أعاد كلاهما Expanded، فتحقّق مما إذا كان البحث عن الكمية يستخدم صفوف الفئة المصفّاة.

تم التحقق من هذه النتائج العشر المتوقعة باستخدام عملية حسابية صغيرة بلغة Python تطبّق قاعدة الحدود الدنيا المذكورة. وقد روجعت صيغة Excel بمقارنتها بالصياغة الموثّقة من Microsoft؛ ولم تُنفّذ في Excel.

تحقّق أيضًا من رسائل المدخلات: ينبغي أن تُرجع الفئة الفارغة أو الكمية المُدخلة كنص Enter category and numeric quantity؛ وينبغي أن تُرجع الكمية الرقمية السالبة Use zero or more. استخدم أعدادًا صحيحة لعدّ الأصول. تتحقق الصيغة من أن الكميات أرقام غير سالبة، لكنها لا تفرض أن تكون أعدادًا صحيحة.

تشخيص النتائج غير المتوقعة وتوسيع ورقة العمل

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

  1. تحقّق من نص الفئة. قارن المدخلات بالتسميات المرجعية، بما في ذلك المسافات. تشير Microsoft إلى أن الأحرف غير المتوقعة والمسافات المخفية من مشكلات البحث الشائعة في إرشادات استكشاف أخطاء البحث وإصلاحها.
  2. تحقّق من الخلايا الرقمية. ينبغي أن تحتوي مدخلات الكمية والحدود الدنيا على أرقام، لا نصوص تبدو كأنها أرقام. وتذكر إرشادات Microsoft نفسها أنواع البيانات غير المتسقة بوصفها مشكلة في البحث.
  3. تحقّق من محاذاة النطاقات. يجب أن تغطي نطاقات الفئة والحد الأدنى والتسمية صفوف البيانات نفسها. في هذا المثال، يبدأ كل نطاق من الصف 2 وينتهي عند الصف 7.
  4. تحقّق من القواعد المكررة. خصّص تسمية مستوى واحدة لكل زوج من الفئة والحد الأدنى للكمية. إذا أسند صفّان تسميتين مختلفتين إلى الحد نفسه، فأزل هذا الالتباس من البيانات المرجعية.
  5. تحقّق من اصطلاح الحدود. قد تشير نتيجة تبدو أعلى أو أدنى بمستوى واحد مما ينبغي إلى أن القيم المرجعية لديك هي قيم قصوى، بينما تتوقع هذه الصيغة قيمًا دنيا.

لإجراء فحص بصري، أدخل صيغة التشخيص التالية في خلية غير مستخدمة مثل I2. عند إدخال Texture Tile في E2، ينبغي أن تعرض الصفوف الثلاثة الكاملة الخاصة بـ Texture Tile. هذه صيغة تشخيص منفصلة، وليست بديلًا عن G2.

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

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

عند إضافة المزيد من صفوف البيانات المرجعية، وسّع نطاقات المصدر الثلاثة في الصيغة الرئيسية معًا. على سبيل المثال، إذا أصبح صف البيانات الأخير هو 10، فغيّر التعريفات إلى categories,$A$2:$A$10 وminimums,$B$2:$B$10 وlabels,$C$2:$C$10. ثم أعد اختبارات الحدود للفئة المضافة.

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

المصادر