المحتويات
لقد أوضحنا سابقًا للمبتدئين كيفية استخدام الوظائف الأساسية لـ VLOOKUP (الإنجليزية VLOOKUP ، الاختصار يشير إلى "وظيفة البحث الرأسي"). وتم عرض العديد من الصيغ المعقدة للمستخدمين المتمرسين.
وفي هذه المقالة سنحاول تقديم معلومات حول طريقة أخرى للعمل مع البحث العمودي.
قد تتساءل: "لماذا هذا ضروري؟". وهذا ضروري لإظهار جميع طرق البحث الممكنة. بالإضافة إلى ذلك ، غالبًا ما تمنع قيود VLOOKUP العديدة الحصول على النتيجة المرجوة. في هذا الصدد ، تعد INDEX () MATCH () أكثر وظيفية وتنوعًا ، ولديها أيضًا قيود أقل.
تطابق الفهرس الأساسيات
نظرًا لأن الغرض من هذا الدليل هو إظهار مدى جودة هذه الميزة ، فإننا دعونا نلقي نظرة على المعلومات الأساسية المتعلقة بمبادئ عملها. وسنعرض أمثلة ، وننظر أيضًا في السبب ، إنه أفضل من VLOOKUP ().
صيغة الدالة INDEX واستخدامها
تساعد هذه الوظيفة في العثور على القيمة المطلوبة بين مناطق البحث المحددة بناءً على العمود أو رقم السطر. بناء الجملة:
= INDEX (صفيف ، رقم صف ، رقم عمود):
- صفيف - المنطقة التي سيجري البحث فيها ؛
- رقم السطر - رقم السطر المراد البحث عنه في المصفوفة المحددة. إذا كان رقم الصف غير معروف ، يجب تحديد رقم العمود ؛
- رقم العمود - رقم العمود الذي سيتم العثور عليه في المصفوفة المحددة. إذا كانت القيمة غير معروفة ، يلزم إدخال رقم سطر.
مثال على صيغة بسيطة:
= INDEX (A1: S10,2,3،XNUMX،XNUMX)
ستبحث الوظيفة في النطاق من A1 إلى C10. توضح الأرقام الصف (2) والعمود (3) لإظهار القيمة المطلوبة منه. ستكون النتيجة الخلية C2.
بسيط جدا ، أليس كذلك؟ ولكن عند العمل باستخدام مستندات حقيقية ، فمن غير المحتمل أن يكون لديك معلومات تتعلق بأرقام الأعمدة أو الخلايا. هذا هو الغرض من وظيفة MATCH ().
MATCH بناء جملة الدالة والاستخدام
تبحث وظيفة MATCH () عن القيمة المطلوبة وتعرض الرقم التقريبي لها في منطقة البحث المحددة.
يبدو بناء جملة searchpos () كما يلي:
= MATCH (قيمة للبحث ، مصفوفة للبحث ، نوع المطابقة)
- قيمة البحث - الرقم أو النص الذي سيتم العثور عليه ؛
- المصفوفة التي تم البحث عنها - المنطقة التي سيجري البحث فيها ؛
- نوع المطابقة - يحدد ما إذا كان سيتم البحث عن القيمة الدقيقة أو القيم الأقرب إليها:
- 1 (أو لا توجد قيمة محددة) - إرجاع أكبر قيمة تساوي أو تقل عن القيمة التي تم تحديدها ؛
- 0 - يظهر تطابقًا تامًا مع القيمة التي تم البحث عنها. في المجموعة INDEX () MATCH () ، ستحتاج دائمًا تقريبًا إلى تطابق تام ، لذلك نكتب 0 ؛
- -1 - إظهار أصغر قيمة أكبر من أو تساوي القيمة المحددة في الصيغة. يتم الفرز بترتيب تنازلي.
على سبيل المثال ، في النطاق B1: B3 New York ، Paris ، London مسجلة. ستظهر الصيغة أدناه الرقم 3 لأن لندن تحتل المرتبة الثالثة في القائمة:
= EXPOSE (لندن، B1: B3,0،XNUMX)
كيفية العمل مع دالة INDEX MATCH
ربما تكون قد بدأت بالفعل في فهم المبدأ الذي يتم من خلاله بناء العمل المشترك لهذه الوظائف. باختصار إذن يبحث INDEX () عن القيمة المطلوبة بين الصفوف والأعمدة المحددة. وتوضح MATCH () أرقام هذه القيم:
= INDEX (العمود الذي يتم إرجاع القيمة منه ، MATCH (قيمة للبحث ، عمود للبحث فيه ، 0))
هل ما زلت تواجه صعوبة في فهم كيفية عملها؟ ربما يشرح المثال بشكل أفضل. افترض أن لديك قائمة بعواصم العالم وسكانها:
من أجل معرفة حجم السكان في عاصمة معينة ، على سبيل المثال ، عاصمة اليابان ، نستخدم الصيغة التالية:
= INDEX (C2: C10، MATCH (اليابان، A2: A10,0،XNUMX))
التفسير:
- تبحث الدالة MATCH () عن القيمة - "Japan" في المصفوفة A2: A10 وتُرجع الرقم 3 ، لأن اليابان هي القيمة الثالثة في القائمة.
- يذهب هذا الرقم إلىرقم الخط"في صيغة INDEX () ويخبر الدالة بطباعة قيمة من هذا الصف.
لذلك تصبح الصيغة أعلاه هي الصيغة القياسية الفهرس (C2: C10,3،XNUMX). تبحث الصيغة من الخلايا C2 إلى C10 وتعيد البيانات من الخلية الثالثة في هذا النطاق ، أي C4 ، لأن العد التنازلي يبدأ من الصف الثاني.
لا تريد أن تصف اسم المدينة في الصيغة؟ ثم اكتبها في أي خلية ، قل F1 ، واستخدمها كمرجع في صيغة MATCH (). وينتهي بك الأمر بصيغة بحث ديناميكية:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
المهم! عدد الخطوط في مجموعة يجب أن يكون INDEX () مماثلاً لعدد الصفوف في تعتبر مجموعة في MATCH () ، وإلا ستحصل على نتيجة خاطئة.
انتظر لحظة ، لماذا لا تستخدم صيغة VLOOKUP () فقط؟
= VLOOKUP (F1، A2: C10، 3، خطأ)
ما الهدف من إضاعة الوقت في محاولة اكتشاف كل هذه التعقيدات في INDEX MATCH؟
في هذه الحالة ، لا يهم الوظيفة التي يجب استخدامها. هذا مجرد مثال لفهم كيفية عمل الدالتين INDEX () و MATCH () معًا. ستوضح الأمثلة الأخرى ما تستطيع هذه الوظائف القيام به في المواقف التي يكون فيها VLOOKUP عاجزًا.
مطابقة الفهرس أو VLOOKUP
عند تحديد صيغة البحث المراد استخدامها ، يتفق الكثيرون على أن INDEX () و MATCH () متفوقان بشكل كبير على VLOOKUP. ومع ذلك ، لا يزال العديد من الأشخاص يستخدمون VLOOKUP (). أولاً ، VLOOKUP () أبسط ، وثانيًا ، لا يفهم المستخدمون تمامًا جميع مزايا العمل مع INDEX () و MATCH (). بدون هذه المعرفة ، لن يوافق أحد على قضاء وقته في دراسة نظام معقد.
فيما يلي المزايا الرئيسية لـ INDEX () و MATCH () على VLOOKUP ():
- البحث من اليمين إلى اليسار. لا يمكن لـ VLOOKUP () البحث من اليمين إلى اليسار ، لذا يجب أن تكون القيم التي تبحث عنها دائمًا في الأعمدة الموجودة في أقصى اليسار من الجدول. لكن يمكن لـ INDEX () و MATCH () التعامل مع هذا دون مشكلة. ستخبرك هذه المقالة كيف تبدو عمليًا: كيفية العثور على القيمة المرغوبة على الجانب الأيسر.
- إضافة أو إزالة آمنة للأعمدة. تُظهر صيغة VLOOKUP () نتائج غير صحيحة عند إزالة الأعمدة أو إضافتها لأن VLOOKUP () يحتاج إلى رقم العمود الدقيق ليكون ناجحًا. بطبيعة الحال ، عند إضافة الأعمدة أو إزالتها ، تتغير أرقامها أيضًا.
وفي الصيغتين INDEX () و MATCH () ، يتم تحديد نطاق من الأعمدة ، وليس الأعمدة الفردية. نتيجة لذلك ، يمكنك إضافة أعمدة وإزالتها بأمان دون الحاجة إلى تحديث الصيغة في كل مرة.
- لا حدود لأحجام البحث. عند استخدام VLOOKUP () ، يجب ألا يتجاوز العدد الإجمالي لمعايير البحث 255 حرفًا أو ستحصل على #VALUE! لذلك إذا كانت بياناتك تحتوي على عدد كبير من الأحرف ، فإن الخيار الأفضل هو INDEX () و MATCH ().
- سرعة معالجة عالية. إذا كانت طاولاتك صغيرة نسبيًا ، فمن غير المرجح أن تلاحظ أي فرق. ولكن ، إذا كان الجدول يحتوي على مئات أو آلاف الصفوف ، وبالتالي ، هناك مئات وآلاف من الصيغ ، فإن INDEX () و MATCH () سوف يتعاملان بشكل أسرع بكثير من VLOOKUP (). الحقيقة هي أن Excel سيعالج الأعمدة المحددة في الصيغة فقط ، بدلاً من معالجة الجدول بأكمله.
سيكون تأثير أداء VLOOKUP () ملحوظًا بشكل خاص إذا كانت ورقة العمل الخاصة بك تحتوي على عدد كبير من الصيغ مثل VLOOKUP () و SUM (). مطلوب تدقيقات منفصلة لوظائف VLOOKUP () لتحليل كل قيمة في المصفوفة. لذلك يتعين على Excel معالجة كمية هائلة من المعلومات ، وهذا يؤدي إلى إبطاء العمل بشكل كبير.
أمثلة الصيغة
لقد اكتشفنا بالفعل فائدة هذه الوظائف ، حتى نتمكن من الانتقال إلى الجزء الأكثر إثارة للاهتمام: تطبيق المعرفة في الممارسة.
صيغة للبحث من اليمين إلى اليسار
كما ذكرنا سابقًا ، لا يمكن لـ VLOOKUP إجراء هذا الشكل من البحث. لذلك ، إذا لم تكن القيم المطلوبة في العمود الموجود في أقصى اليسار ، فلن ينتج عن VLOOKUP () نتيجة. تعد الدالتان INDEX () و MATCH () أكثر تنوعًا ، ولا يلعب موقع القيم دورًا كبيرًا بالنسبة لها للعمل.
على سبيل المثال ، سنضيف عمود تصنيف إلى الجانب الأيسر من جدولنا ونحاول معرفة الترتيب من حيث عدد السكان الذي تحتله عاصمة بلدنا.
في الخلية G1 ، نكتب القيمة التي سيتم العثور عليها ، ثم نستخدم الصيغة التالية للبحث في النطاق C1: C10 وإرجاع القيمة المقابلة من A2: A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
مستعجل. إذا كنت تخطط لاستخدام هذه الصيغة لخلايا متعددة ، فتأكد من إصلاح النطاقات باستخدام العنونة المطلقة (على سبيل المثال ، 2 دولار أسترالي: 10 دولارات أسترالية و 2 دولار كندي: 4 دولارات كندية و 10 دولارات).
مؤشر أكثر انكشافاً أكثر انكشافاً للبحث في الأعمدة والصفوف
في الأمثلة أعلاه ، استخدمنا هذه الوظائف كبديل لـ VLOOKUP () لإرجاع القيم من نطاق محدد مسبقًا من الصفوف. ولكن ماذا لو كنت بحاجة إلى إجراء بحث على الوجهين؟
يبدو الأمر معقدًا ، لكن صيغة مثل هذه الحسابات تشبه صيغة INDEX () MATCH () القياسية ، مع اختلاف واحد فقط: يجب استخدام صيغة MATCH () مرتين. المرة الأولى للحصول على رقم الصف ، والمرة الثانية للحصول على رقم العمود:
= INDEX (صفيف ، MATCH (قيمة البحث العمودي ، عمود البحث ، 0) ، MATCH (قيمة البحث الأفقي ، صف البحث ، 0))
دعنا نلقي نظرة على الجدول أدناه ونحاول عمل صيغة فهرس () صريح () صريح () لعرض التركيبة السكانية في بلد معين لسنة محددة.
البلد الهدف موجود في الخلية G1 (بحث عمودي) والسنة الهدف موجودة في الخلية G2 (بحث أفقي). ستبدو الصيغة كما يلي:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
كيف تعمل هذه الصيغة
كما هو الحال مع أي صيغ معقدة أخرى ، يسهل فهمها عن طريق تقسيمها إلى معادلات فردية. وبعد ذلك يمكنك فهم ما تفعله كل وظيفة على حدة:
- MATCH (G1، A2: A11,0،XNUMX) - يبحث عن قيمة (G1) في النطاق A2: A11 ويظهر رقم هذه القيمة ، في حالتنا هو 2 ؛
- SEARCH (G2، B1: D1,0،XNUMX) - يبحث عن قيمة (G2) في النطاق B1: D1. في هذه الحالة ، كانت النتيجة 3.
يتم إرسال أرقام الصفوف والأعمدة التي تم العثور عليها إلى القيمة المقابلة في صيغة INDEX ():
= INDEX (B2: D11,2,3،XNUMX،XNUMX)
نتيجة لذلك ، لدينا قيمة موجودة في خلية عند تقاطع صفين و 2 أعمدة في النطاق B3: D2. وتوضح الصيغة القيمة المطلوبة الموجودة في الخلية D11.
ابحث بشروط متعددة باستخدام INDEX و MATCH
إذا كنت قد قرأت دليلنا إلى VLOOKUP () ، فمن المحتمل أنك جربت صيغ بحث متعددة. لكن طريقة البحث هذه لها قيد واحد مهم - الحاجة إلى إضافة عمود إضافي.
لكن الخبر السار هو ذلك باستخدام INDEX () و MATCH () ، يمكنك البحث عن شروط متعددة دون الحاجة إلى تحرير ورقة العمل أو تغييرها.
فيما يلي صيغة البحث العامة متعددة الشروط لـ INDEX () MATCH ():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
الملاحظة: يجب استخدام هذه الصيغة مع اختصار لوحة المفاتيح CTRL + SHIFT + ENTER.
لنفترض أنك بحاجة إلى العثور على القيمة التي تبحث عنها بناءً على شرطين: المشتري и المنتج.
هذا يتطلب الصيغة التالية:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
في هذه الصيغة ، C2: C10 هو النطاق الذي سيتم البحث فيه ، F1 - هذا الشرط، أ 2: أ 10 - هو النطاق لمقارنة الحالة ، F2 - الشرط 2 ، V2: V10 - المدى للمقارنة بين الشرط 2.
لا تنس الضغط على المجموعة في نهاية العمل بالصيغة CTRL+SHIFT+إدخال - سيغلق Excel الصيغة تلقائيًا بأقواس متعرجة ، كما هو موضح في المثال:
إذا كنت لا تريد استخدام صيغة صفيف لعملك ، فقم بإضافة INDEX () آخر إلى الصيغة واضغط على ENTER ، سيبدو كما في المثال:
كيف تعمل هذه الصيغ
تعمل هذه الصيغة بنفس طريقة عمل صيغة INDEX () MATCH () القياسية. للبحث عن شروط متعددة ، يمكنك ببساطة إنشاء العديد من الشروط False و True التي تمثل الشروط الفردية الصحيحة وغير الصحيحة. ثم تنطبق هذه الشروط على جميع العناصر المقابلة في المصفوفة. تحول الصيغة الوسيطتين False و True إلى 0 و 1 ، على التوالي ، وتخرج صفيفًا حيث 1 هي القيم المطابقة التي تم العثور عليها في السلسلة. سوف تجد MATCH () القيمة الأولى التي تطابق 1 وتمريرها إلى صيغة INDEX (). وهو بدوره سيعيد القيمة المطلوبة بالفعل في السطر المحدد من العمود المطلوب.
تعتمد الصيغة التي لا تحتوي على صفيف على قدرة INDEX () في معالجتها بمفردها. يطابق INDEX () الثاني في الصيغة falsy (0) ، لذلك يمرر الصفيف بأكمله بهذه القيم إلى صيغة MATCH ().
هذا تفسير مطول إلى حد ما للمنطق الكامن وراء هذه الصيغة. لمزيد من المعلومات ، اقرأ المقال "مطابقة الفهرس بشروط متعددة".
AVERAGE و MAX و MIN في INDEX و MATCH
يحتوي Excel على وظائفه الخاصة للعثور على المتوسطات والحد الأقصى والحد الأدنى. ولكن ماذا لو كنت تريد الحصول على بيانات من الخلية المرتبطة بهذه القيم؟ في هذه الحالة يجب استخدام AVERAGE و MAX و MIN جنبًا إلى جنب مع INDEX و MATCH.
مطابقة الفهرس و MAX
للعثور على أكبر قيمة في العمود D وعرضها في العمود C ، استخدم الصيغة:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
مطابقة الفهرس و MIN
للعثور على أصغر قيمة في العمود D وعرضها في العمود C ، استخدم الصيغة التالية:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
فهرس البحث و SERPENT
للعثور على متوسط القيمة في العمود D وعرض هذه القيمة في C:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
اعتمادًا على كيفية كتابة بياناتك ، تكون الوسيطة الثالثة لـ MATCH () إما 1 أو 0 أو -1:
- إذا تم فرز الأعمدة بترتيب تصاعدي ، فقم بتعيين 1 (عندئذٍ ستحسب الصيغة القيمة القصوى ، وهي أقل من القيمة المتوسطة أو مساوية لها) ؛
- إذا كان الفرز تنازليًا ، فإن -1 (ستخرج الصيغة القيمة الدنيا التي تكون أكبر من أو تساوي المتوسط) ؛
- إذا كانت مصفوفة البحث تحتوي على قيمة تساوي تمامًا المتوسط ، فقم بتعيينها على 0.
في مثالنا ، تم فرز السكان بترتيب تنازلي ، لذلك نضع -1. والنتيجة هي طوكيو ، حيث أن القيمة السكانية (13,189) هي الأقرب لمتوسط القيمة (000).
يمكن لـ VLOOKUP () أيضًا إجراء مثل هذه الحسابات ، ولكن فقط كصيغة صفيف: VLOOKUP مع AVERAGE و MIN و MAX.
مطابقة الفهرس و ESND / IFERROR
ربما لاحظت بالفعل أنه إذا لم تتمكن الصيغة من العثور على القيمة المطلوبة ، فإنها تتسبب في حدوث خطأ # N / A. يمكنك استبدال رسالة الخطأ القياسية بشيء أكثر إفادة. على سبيل المثال ، قم بتعيين الوسيطة في الصيغة في XNUMXth:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
باستخدام هذه الصيغة ، إذا أدخلت بيانات غير موجودة في الجدول ، فسيعطيك النموذج الرسالة المحددة.
إذا كنت تريد التقاط جميع الأخطاء ، فما عدا في XNUMXth ويمكن استخدام خطأ مرجّح:
= IFERROR (INDEX (C2: C10، MATCH (F1، A2: A10,0،XNUMX)) ، "حدث خطأ ما!")
لكن تذكر أن إخفاء الأخطاء بهذه الطريقة ليس فكرة جيدة ، لأن الأخطاء القياسية تبلغ عن الانتهاكات في الصيغة.
نأمل أن تكون قد وجدت دليلنا لاستخدام وظيفة INDEX MATCH () مفيدًا.