المطابقة التقريبية لجداول الشرائح

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

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

هنا تتألق المطابقة التقريبية؛ إذ تعثر على الشريحة الصحيحة بدلًا من اشتراط تطابق تام.

استخدمنا حتى الآن MATCH(value, range, 0) لإجراء مطابقة تامة. تعني الوسيطة الثالثة 0 «اعثر على هذه القيمة بدقة، وإلا فأعد #N/A».

أما مع الشرائح فنستخدم نوع المطابقة 1. فهو يعثر على أكبر قيمة أقل من قيمة البحث أو مساوية لها. وهذا بالضبط هو السلوك المطلوب للبحث عن شريحة.

هناك قاعدة صارمة: عند استخدام نوع المطابقة 1، يجب ترتيب قائمة الحدود تصاعديًا.

تخيّل جدولًا للتقديرات. يحتوي العمود E على الحدود الدنيا مرتبة تصاعديًا: 0، 60، 70، 80، 90. ويحتوي العمود F على التسميات: F، D، C، B، A.

الدرجة من 0 إلى 59 هي F، ومن 60 إلى 69 هي D، وهكذا. نحن نخزّن بداية كل شريحة فقط، وليس كل درجة.

هدفنا هو إعادة التقدير الحرفي عند إدخال درجة في G1.

استخدم MATCH التقريبي لمعرفة الشريحة التي تقع فيها الدرجة. فالصيغة MATCH(G1, E2:E6, 1) مع درجة مقدارها 87 تبحث عن أكبر حد أقل من 87 أو مساوٍ له.

الحدود هي 0، 60، 70، 80، 90. وأكبر حد لا يتجاوز 87 هو 80، وهو في الموضع 4. لذلك تُرجع MATCH القيمة 4.

يشير هذا الموضع إلى الشريحة الصحيحة، رغم أن 87 نفسها ليست موجودة في القائمة.

مرّر الموضع الآن إلى INDEX على عمود التسميات F2:F6.

تأخذ الصيغة INDEX(F2:F6, MATCH(G1, E2:E6, 1)) الموضع 4 وتُرجع التسمية الرابعة، وهي "B".

لذلك تُحوَّل الدرجة 87 بشكل صحيح إلى التقدير B. غيّر G1 إلى 95، فتعيد MATCH القيمة 5 وتحصل على "A"؛ وغيّرها إلى 55، فتعيد MATCH القيمة 1 وتحصل على "F".

تتطلب MATCH التقريبية (النوع 1) ترتيبًا تصاعديًا في نطاق البحث. فهي تفترض أن البيانات تزداد، وتتوقف بمجرد تجاوز قيمة البحث.

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

يمكن لـ XLOOKUP أيضًا إجراء مطابقة تقريبية. وتقبل وسيطتها الخامسة، أي نمط المطابقة، القيمة -1 بمعنى «تطابق تام أو العنصر الأصغر التالي»، وهو مثالي لجداول الشرائح.

يعثر هذا على أكبر حد أقل من G1 أو مساوٍ له، ويُرجع التسمية المطابقة من دون الحاجة إلى INDEX. وغالبًا ما تكون قراءته أسهل من INDEX-MATCH عند البحث في النطاقات.

لنأخذ الآن خصم الكمية. الحدود في E (الكمية المطلوبة): 0، 10، 50، 100. والخصومات في F: ‏0%، 5%، 10%، 15%.

• طلب مقداره 7: أكبر حد أقل من 7 أو مساوٍ له هو 0، وموضعه 1، لذا تُرجع الصيغة 0%.
• طلب مقداره 60: أكبر حد أقل من 60 أو مساوٍ له هو 50، وموضعه 3، لذا تُرجع 10%.
• طلب مقداره 200: أكبر حد أقل من 200 أو مساوٍ له هو 100، وموضعه 4، لذا تُرجع 15%.

تتعامل صيغة واحدة مع كل الكميات.

ماذا يحدث إذا كانت القيمة أصغر من جميع الحدود؟ عند استخدام MATCH التقريبية، لا توجد قيمة أقل منها أو مساوية لها، ولذلك تُرجع MATCH القيمة #N/A.

لتجنب ذلك، تأكد من أن الحد الأول يغطي أدنى قيمة (وغالبًا ما تكون 0)، أو ضع الصيغة داخل IFERROR لعرض رسالة واضحة عندما تكون القيمة المدخلة خارج النطاق.

انتبه إلى مشكلات جداول الشرائح التالية:

• الحدود غير المرتبة: السبب الأول للنتائج الخاطئة الصامتة.
• استخدام نوع المطابقة 0: يفرض تطابقًا تامًا ويُرجع #N/A لأي قيمة بينية.
• تخزين نهايات الشرائح بدلًا من بداياتها: تتوقع MATCH من النوع 1 الحد الأدنى لكل شريحة، وليس الحد الأعلى.
• الحدود النصية: تؤدي الأرقام المخزّنة كنص إلى كسر المقارنة؛ لذا أبقِها رقمية.

يمكنك الجمع بين المطابقة التقريبية والأسلوب ثنائي الاتجاه. تخيّل تكلفة شحن تعتمد على كل من شريحة الوزن (الصفوف) وشريحة المنطقة (الأعمدة).

استخدم MATCH تقريبية من النوع 1 للعثور على صف الوزن، وأخرى للعثور على عمود المنطقة، ثم مرّر النتيجتين إلى INDEX. وبما أن كلا المحورين يحتويان على حدود مرتبة، تصل كل MATCH إلى الشريحة الصحيحة.

يجمع هذا الأسلوب بين INDEX-MATCH-MATCH ومنطق الشرائح لإنشاء جداول أسعار متقدمة.

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

لعمليات البحث في الشرائح والنطاقات:

• خزّن الحد الأدنى لكل شريحة، ورتّبه تصاعديًا.
• استخدم MATCH(value, thresholds, 1) للعثور على موضع الشريحة (أكبر قيمة أقل من الإدخال أو مساوية له).
• ضعها داخل INDEX(labels, ...) لإعادة الشريحة، أو استخدم XLOOKUP(..., -1) للحصول على النتيجة نفسها.

غطِّ أدنى قيمة بحد مقداره 0، أو استخدم IFERROR للمدخلات الخارجة عن النطاق، ولا تترك الحدود غير مرتبة مطلقًا.