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

ما إصدارات Excel لنظام Mac التي تدعم هذا الأسلوب؟

تستخدم الصيغة أدناه LET وXLOOKUP وحسابات المصفوفات الديناميكية. توثّق Microsoft هذه الإمكانات في Excel لـ Microsoft 365 لنظام Mac وExcel 2024 لنظام Mac وExcel 2021 لنظام Mac. ويتضمن دليل ميزات Excel 2021 لنظام Mac صراحةً LET وXLOOKUP والمصفوفات الديناميكية. الجديد في Excel 2021 لنظام Mac، دالة LET.

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

ما المقصود بـ«الأكثر تحديدًا» في مخزونك؟

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

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

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

إعداد قواعد الأصول الفنية الاصطناعية

في ورقة عمل فارغة، أدخل العناوين والقيم التالية في A1:D6. يجب أن تظل الخلايا الفارغة في الجدول أدناه فارغة في Excel. جميع التسميات والكميات توضيحية.

صف ورقة العمل A: الأصل B: التشطيب C: الحجم D: الكمية
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

القاعدة في الصف 2 افتراضية عن قصد: فهي لا تحدد أي سمات، ولذلك درجة تحديدها صفر. يحدد الصف 3 سمة واحدة، والصف 4 سمتين، والصف 5 ثلاث سمات. هذه الكميات بدائل لقيم مُسنَدة، لذا يُرجع البحث قيمة واحدة. ولا يجمع كمية القاعدة الافتراضية مع كميات الاستثناءات.

أدخل العناوين Asset وFinish وSize وResult في H1:K1. ثم أدخل Tile في H2 وMatte في I2 وSmall في J2. املأ سمات الإدخال الثلاث كلها. في هذا التصميم، تعمل الخلايا الفارغة شروطًا غير مقيّدة في جدول القواعد فقط؛ أما الإدخال غير المكتمل فيُظهر رسالة.

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

إدخال الصيغة التي تختار القاعدة الفائزة

حدّد K2، وأدخل الصيغة الكاملة التالية، ثم اضغط Return. تستخدم الصيغة جدول القواعد وخلايا الإدخال المحددة أعلاه. تُقيَّم حسابات المصفوفات الديناميكية داخل الصيغة، وتشغل النتيجة النهائية خلية واحدة. توضّح وثائق Microsoft أنه يمكن إدخال صيغ المصفوفات الديناميكية بالطريقة المعتادة، من دون مفاتيح إدخال الصيغ القديمة للمصفوفات. صيغ المصفوفات الديناميكية وسلوك انسكاب المصفوفات.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

مع مدخلات العينة، ينبغي أن تُرجع K2 القيمة 7. أربع قواعد مؤهلة، لكن القاعدة في الصف 5 وحدها تحدد السمات الثلاث كلها.

آلية الأهلية ودرجة التحديد

تُسنِد LET أسماء محلية إلى النطاقات والنتائج الوسيطة، ما يجعل فحص عملية الاختيار أسهل. توثّق Microsoft أن نطاق هذه الأسماء يقتصر على الصيغة. دالة LET.

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

تحسب specificity عدد حقول القاعدة المُعبّأة. وتحول علامتا السالب الأحاديان كل اختبار لعدم الفراغ إلى رقم، فتنتج درجات من صفر إلى ثلاثة. وتُسنِد IF درجة سالب واحد إلى الصفوف غير المؤهلة لأغراض الترتيب؛ ثم تعثر MAX على أعلى درجة مؤهلة. وتُبقي درجة سالب واحد الصف غير المؤهل أدنى حتى من درجة القاعدة الافتراضية غير المقيّدة، وهي صفر. دالة IF، دالة MAX.

تضع winner علامة على الصفوف المؤهلة التي حصلت على تلك الدرجة الأعلى وحدها. وتتحقق الصيغة من عدم وجود تطابق أو وجود أكثر من فائز قبل إرجاع الكمية. ويرفض فحصها الرقمي النهائي عمدًا الكمية الفارغة أو الرقم المخزّن كنص. توثّق Microsoft أن ISNUMBER لا تحوّل الأرقام النصية إلى قيم رقمية. ويظل الصفر الرقمي نتيجة صالحة. دوال IS.

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

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

الأصل التشطيب الحجم النتيجة المتوقعة شرط الفوز
Tile Matte Small 7 ثلاث حقول محددة
Tile Matte Large 12 الأصل والتشطيب
Tile Gloss Small 18 الأصل فقط
Disc Gloss Large 10 الأصل والحجم
Arc Matte Small 30 القاعدة الافتراضية غير المقيّدة

بعد ذلك، غيّر الصف 6 مؤقتًا إلى الأصل Tile والتشطيب Matte والحجم Small والكمية 9. بعد استعادة المدخلات الأصلية، ينبغي أن تصبح النتيجة Tie: review rules: إذ تحدد قاعدتان الآن السمات الثلاث المطابقة كلها. وحتى إذا تطابقت الكميات، ستظل النتيجة تعادلًا لأن اختيار القاعدة ملتبس. أعد الصف 6 إلى حالته الأصلية بعد ذلك.

لاختبار حالة عدم وجود تطابق، غيّر مؤقتًا نطاقات القواعد الأربعة في الصيغة لتبدأ من الصف 3، مستبعدًا القاعدة الافتراضية. ينبغي أن يُرجع الإدخال Arc وMatte وSmall القيمة No matching rule. أعد النطاقات لتبدأ من الصف 2 عند الانتهاء. ولاختبار فحص الكمية، امسح D5 مع استخدام المدخلات الأصلية؛ توقّع ظهور Check rule quantity، ثم استعد 7.

فحص القواعد المتداخلة وحسم التعادل

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

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

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

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

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

توسيع البحث من دون تغيير معناه

لإضافة أصل آخر، أدخل سماته الثلاث في H3:J3 وانسخ K2 إلى K3. تتحرك مراجع الإدخال مع الصيغة، بينما تُبقي علامات الدولار نطاقات القواعد ثابتة. عند إضافة قواعد بعد الصف 6، وسّع النطاقات الأربعة في الصيغة الرئيسية وبُعدَي صيغة الفحص بالاتساق نفسه.

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

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

المصادر