تعيد =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) السعر من الصف الذي يكون فيه المنتج E2 والحجم F2. تتحقق كل مقارنة من كل صف، ويعطي ضربهما 1 فقط حيث يصح الاثنان، وتبحث XLOOKUP عن ذلك الرقم 1. تحتاج إلى Excel 2021 أو Microsoft 365؛ ونسخة INDEX MATCH أدناه تعمل في كل إصدار.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
يلتقي Tea وLarge في الصف 5، لذا تعيد G2 القيمة $3.00. اختر Juice وSmall: $3.00 مرة أخرى، من صف آخر. أضف وسيطًا رابعًا للحالة التي لا يطابق فيها أي صف الشرطين: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item").
كيف تعمل الشروط المضروبة
تقارن A2:A7=E2 كل منتج بالخلية E2 وتعيد ست قيم TRUE أو FALSE. وضرب قائمتين كهاتين يحوّل TRUE إلى 1 وFALSE إلى 0، ويكون الصف 1 فقط إذا كان 1 في القائمتين. يعرض العمود D هذه القائمة، ممتدة من صيغة واحدة.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
D5 وحدها قيمتها 1. غيّر F2 أو G2 فيتحرك الرقم 1. كل شرط إضافي هو *(range=value) آخر، ولا يلزم أن تكون الشروط مساواة: تضيف *(C2:C7<3) شرط "السعر أقل من 3". يجب أن تغطي كل النطاقات الصفوف نفسها (A2:A7 وB2:B7 وC2:C7): إذا كان نطاق الإرجاع بحجم مختلف عن الشروط، تعيد XLOOKUP الخطأ #VALUE!.
INDEX MATCH بعدة شروط
في Excel 2019 وما قبله، يمكن أن تبحث MATCH في المصفوفة نفسها عن الرقم 1، وتعيد INDEX السعر من ذلك الموضع.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Coffee وLarge في الموضع 2 من المصفوفة، وتعيد INDEX القيمة $3.50. في Excel 2019 وما قبله هذه صيغة مصفوفة: اضغط Ctrl+Shift+Enter (Cmd+Shift+Enter على Mac) بدلًا من Enter، فيعرضها الاكسل داخل أقواس معقوصة. والضغط على Enter وحده هناك يعيد عادة #N/A أو #VALUE!. وفي Excel 365 يكفي Enter. وصيغة الشرط الواحد موجودة في صفحة INDEX وMATCH.
اجمع الشروط في مفتاح واحد
الطريقة الأخرى هي تحويل شرطين إلى شرط واحد بجمعهما. تحتاج VLOOKUP إلى القيم المجمّعة في عمود مساعد في بداية الجدول (توضح صفحة VLOOKUP هذه النسخة). أما XLOOKUP فتستطيع جمع النطاقات داخل الصيغة، فلا حاجة إلى عمود مساعد.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
تبني A2:A7&"|"&B2:B7 ستة مفاتيح مثل Juice|Large، وتجد XLOOKUP المفتاح Juice|Large بينها: $4.00. ضع فاصلًا بين الأجزاء. بدونه يجتمع "AB" و"C" إلى "ABC" نفسها التي يعطيها "A" و"BC"، وقد يعيد البحث الصف الخطأ.
إذا كانت القيمة التي تريدها رقمًا وتظهر كل تركيبة مرة واحدة، تعطي SUMIFS الإجابة نفسها دون أي مصفوفة: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). وتعيد 0 بدلًا من الخطأ عندما لا يتطابق شيء، وهذا قد يخفي خطأً إملائيًا.
إرجاع كل التطابقات بالدالة FILTER
تعيد XLOOKUP وINDEX MATCH أول صف مطابق. وعندما تتطابق عدة صفوف وتريدها كلها، استخدم FILTER بالشروط نفسها.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
ثلاثة صفوف هي North وPhone، لذا تمد F2 أرباعها ومبيعاتها في F2:G4. غيّر A3 إلى South فتتقلص القائمة إلى صفين. وإذا لم يتطابق أي صف، تعيد FILTER الخطأ #CALC!؛ ووسيط ثالث مثل "None" يعرض نصًا بدلًا منه. وتجد خيارات أخرى في صفحة FILTER.
تمرين: ثلاثة شروط
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
دورك: أعد في G4 مبيعات المنطقة الموجودة في G1، والمنتج الموجود في G2، والربع الموجود في G3.
الأسئلة الشائعة
كيف أستخدم XLOOKUP بعدة شروط؟
اضرب مقارنة واحدة لكل شرط وابحث عن 1: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7). تعطي كل مقارنة TRUE أو FALSE لكل صف، ويكون حاصل الضرب 1 فقط حيث تكون كلها TRUE، وتعيد XLOOKUP أول صف كهذا.
كيف أستخدم INDEX MATCH بشرطين؟
استخدم الشروط المضروبة نفسها داخل MATCH: =INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0)). في Excel 2019 وما قبله، أكّدها بالضغط على Ctrl+Shift+Enter (Cmd+Shift+Enter على Mac).
هل يمكن أن تستخدم VLOOKUP شرطين؟
ليس مباشرة. أضف عمودًا مساعدًا في بداية الجدول يجمع القيمتين، مثل =A2&"|"&B2، ثم ابحث عن القيمة المجمّعة: =VLOOKUP(E2&"|"&F2,helper_table,col,FALSE).
هل يمكن أن تحل SUMIFS محل البحث بشرطين؟
نعم، عندما تكون القيمة رقمًا وتظهر كل تركيبة مرة واحدة: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). تعيد 0 بدلًا من #N/A عندما لا يتطابق أي صف، وتجمع القيم إذا ظهرت تركيبة مرتين.
كيف أبحث بشروط OR؟
اجمع الشروط بدلًا من ضربها: تكون (A2:A7="Tea")+(A2:A7="Juice") قيمتها 1 أو أكثر حيث يصح أحدهما. ابحث عن قيمة أكبر من 0، مثلًا بالصيغة =XLOOKUP(TRUE,((A2:A7="Tea")+(A2:A7="Juice"))>0,C2:C7).
