استخدم INDEX مع دالتي MATCH للتطابق التام لإرجاع الكمية عند تقاطع صف أحد الأصول مع عمود أحد التنسيقات. يشرح هذا الدليل كيفية إنشاء ورقة عمل صغيرة للأصول الفنية، وصيغة البحث، كما يعرض بديلاً متداخلاً باستخدام XLOOKUP لإصدارات Mac المدعومة.

ما طريقة البحث المناسبة في Excel for Mac؟

في هذه المهمة، لديك اسم أصل، مثل Dot Grid، وتنسيق، مثل PNG. وتريد معرفة الكمية عند نقطة تقاطعهما. يُعرف هذا عادةً بالبحث ثنائي الاتجاه.

تذكر Microsoft أن INDEX وMATCH متاحتان في Excel for Microsoft 365 for Mac وExcel 2024 for Mac وExcel 2021 for Mac. وتعمل الطريقتان الواردتان في هذه المقالة ضمن نطاق إصدارات Mac الموثق هذا. راجع وثائق دالة INDEX ووثائق دالة MATCH من Microsoft.

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

ابدأ باستخدام INDEX وMATCH إذا أردت فحص موضعي الصف والعمود كلٌّ على حدة أثناء التعلم. اختر XLOOKUP المتداخلة إذا كنت تستخدم هذه الدالة بالفعل وتفضل تطابقها التام الافتراضي.

إعداد شبكة صغيرة لكميات الأصول الفنية

في ورقة عمل فارغة، أدخل البيانات الاصطناعية التالية في A1:D5. يمثل العمود الأيسر للجدول أدناه عمود ورقة العمل A؛ فلا تضف عمودًا إضافيًا لأرقام الصفوف. هذه الكميات توضيحية.

الأصل SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

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

أنشئ منطقة بحث منفصلة في ورقة العمل نفسها:

الخلية أدخل
F1 Asset to find
F2 Dot Grid
G1 Format to find
G2 PNG
H1 Quantity

لكل من النطاقات المصدرية الثلاثة وظيفة مختلفة: يحتوي A2:A5 على تسميات الأصول، ويحتوي B1:D1 على عناوين التنسيقات، ولا يحتوي B2:D5 إلا على الكميات. إبقاء العناوين خارج نطاق الكميات يضمن تطابق المواضع النسبية على نحو صحيح.

إرجاع الكمية باستخدام INDEX ودالتي MATCH

حدد H2، وأدخل الصيغة الكاملة التالية، ثم اضغط Return:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

ينبغي أن تكون النتيجة 24: كمية Dot Grid في عمود PNG. تشرح تعليمات Microsoft الخاصة بنظام Mac تحديد خلية للنتيجة، وإدخال صيغة تبدأ بـ =، ثم الضغط على Return. Microsoft: إدخال صيغة

فهم الموضعين

تعثر دالة MATCH الأولى على موضع الأصل ضمن A2:A5؛ وتعثر الثانية على موضع التنسيق ضمن B1:D1. تحدد الوسيطة الأخيرة 0 المطابقة التامة. إذا حُذفت هذه الوسيطة، فستستخدم MATCH المطابقة التقريبية افتراضيًا، مع اشتراط ترتيب البيانات. Microsoft: دالة MATCH

بالنسبة إلى المدخلات المعطاة، يكون ناتجَا العمليتين الداخليتين كما يلي:

=MATCH(F2,$A$2:$A$5,0)

النتيجة: 2.

=MATCH(G2,$B$1:$D$1,0)

النتيجة: 2.

لذلك تختار الصيغة الكاملة الصف 2 والعمود 2 ضمن كتلة الكميات:

=INDEX($B$2:$D$5,2,2)

وهذه هي خلية ورقة العمل C3، التي تحتوي على 24. تستخدم INDEX المواضع ضمن النطاق المحدد لها، ولذلك يكون صفها الثاني هو صف ورقة العمل 3 هنا. توضح Microsoft أن توفير الموضعين يعيد القيمة عند تقاطعهما. Microsoft: دالة INDEX

تثبيت النطاقات المصدرية عند النسخ

تثبّت علامات الدولار النطاقات المصدرية. وتبقى مراجع الإدخال F2 وG2 نسبية، لذا فإن نسخ H2 إلى الأسفل حتى H3 يجعل الصيغة التالية تقرأ F3 وG3 مع استخدام شبكة المصدر نفسها. وفي Mac، يمكنك أيضًا تحديد مرجع في شريط الصيغة والضغط على Command+T للتنقل بين أنواع المراجع. Microsoft: التبديل بين المراجع النسبية والمطلقة

على سبيل المثال، أدخل Tile Arc في F3 وJPG في G3، ثم انسخ H2 إلى H3. ينبغي أن تكون النتيجة الجديدة 6. يتيح لك ذلك إنشاء قائمة طلبات صغيرة من دون تغيير شبكة الأصول الأصلية.

استخدام XLOOKUP المتداخلة في إصدارات Mac المدعومة

باستخدام ورقة العمل ومدخلات البحث نفسها، يمكنك بدلًا من ذلك إدخال هذه الصيغة في H2:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

تعيد هذه الصيغة 24 لـ Dot Grid وPNG. تتضمن وثائق Microsoft مثالًا متداخلًا على XLOOKUP ينفذ بحثًا رأسيًا وآخر أفقيًا لإرجاع قيمة عند نقطة تقاطعهما. المطابقة التامة هي الإعداد الافتراضي. Microsoft: دالة XLOOKUP

في هذا المثال العملي، يحدد البحث الداخلي عمود الكميات PNG: 18 و24 و9 و14. ويحدد البحث الخارجي القيمة المتوافقة مع Dot Grid. ويسهّل استخدام النطاقات المصدرية الثلاثة نفسها مقارنة هذه الطريقة بالصيغة السابقة.

التحقق من النتيجة قبل استخدام شبكة أكبر

استخدم الحالات التالية كاختبار قبول صغير. غيّر F2 وG2 لكل صف، ثم قارن H2 بالنتيجة المتوقعة. أعد القيمتين إلى Dot Grid وPNG بعد ذلك إذا أردت الإبقاء على المثال الأصلي.

الأصل المُدخل التنسيق المُدخل النتيجة المتوقعة ما الذي يتحقق منه
Dot Grid PNG 24 تقاطع في الوسط
Tile Arc JPG 6 الأصل الأول والتنسيق الأخير
Soft Square SVG 7 الأصل الأخير والتنسيق الأول
Loop Ribbon JPG 0 كمية صفر صريحة
Tile Arcs PNG #N/A تسمية أصل غير موجودة
Dot Grid TIFF #N/A عنوان تنسيق غير موجود

إذا كان الرقم غير صحيح، فتحقق من حدود النطاق قبل تغيير طريقة البحث. في هذه الشبكة، يجب أن يتوافق A2:A5 مع الصفوف الأربعة في B2:D5، وأن يتوافق B1:D1 مع أعمدته الثلاثة. فمثلًا، تضمين A1 في نطاق البحث عن الأصل فقط يزيح الموضع المحسوب عن صف الكمية المقصود.

عند توسيع هذه الشبكة بإضافة أصل آخر في الصف 6، حدّث نطاق الأصول إلى A2:A6 وكتلة الكميات إلى B2:D6. وعند إضافة تنسيق في العمود E، حدّث نطاق العناوين إلى B1:E1 وكتلة الكميات إلى B2:E5. تحافظ هذه التعديلات المتزامنة على التخطيط الذي تستخدمه الصيغ.

تتطلب التسميات المكررة الانتباه أيضًا. تعيد MATCH للتطابق التام أول موضع مطابق، ولا تميز بين الأحرف الكبيرة والصغيرة؛ كما أنها تتعامل مع * و? كأحرف بدل في عمليات البحث النصي. استخدم تسميات فريدة لا تتضمن هذه الأحرف لهذا الإعداد. Microsoft: دالة MATCH

عرض رسالة مفيدة عند غياب التسمية

بعد أن يعمل البحث الأساسي، يمكنك استبدال خطأ المطابقة المفقودة برسالة واضحة:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

تعيد IFNA رسالتك عندما تنتج الصيغة المضمنة الخطأ #N/A؛ وإلا فإنها تُبقي النتيجة. وهي موثقة في Microsoft 365 for Mac وExcel 2024 for Mac وExcel 2021 for Mac. Microsoft: دالة IFNA

في هذا المثال، تنتج Tile Arcs الرسالة Check asset or format، بينما تظل نتيجة Loop Ribbon مع JPG هي 0. يفصل ذلك بين تسمية غير موجودة وكمية مسجلة تساوي صفرًا. إذا احتوت كمية مصدرية نفسها على #N/A، فستستبدل هذه الصيغة المغلفة ذلك الخطأ أيضًا؛ لذا افحص خلية المصدر عندما تبدو التسميات صحيحة.

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

المصادر