مطابقة الفهرس: البديل الأفضل لـ Vlookup

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

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

ومع ذلك ، هناك بديل ، INDEX + MATCH. فهي لا تكرر الوظيفة فحسب ، بل لها مزاياها ومكافآتها الخاصة. إنه شيء ربما لم تسمعه من قبل (خاصة إذا لم تقرأ عن Excel للمتعة) ولكن بحلول نهاية هذه المقالة ، ستتمكن من وضعه موضع التنفيذ وجني الثمار.

تفكيك INDEX + MATCH

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

فهرس

= INDEX (صفيف ، رقم صف)

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

= INDEX (B2: B6، 3)

وهذا سيعيد Rusty Shackleford.

بسيط ولكنه مفيد. لدينا الآن القدرة على إرجاع القيم داخل المصفوفة.

تطابق

= MATCH (قيمة البحث ، صفيف البحث ، نوع المطابقة)

MATCH () هي وظيفة أخرى بسيطة تتعلق بوظيفة INDEX التي قمنا بتغطيتها للتو. بدلاً من إرجاع قيمة ، فإنها بدلاً من ذلك تُرجع رقمًا يشير إلى الموضع النسبي لقيمة داخل المصفوفة. تتطلب MATCH الوسيطات التالية ؛ القيمة والمصفوفة المطلوب البحث عنها ونوع المطابقة. عادةً ما يتم تعيين نوع المطابقة على 0 ولكن هذا يعتمد على استخدامك. تشير القيمة 0 إلى أنك تريد مطابقة تامة فقط ، بينما تشير 1 أو -1 إلى أنه إذا لم يكن هناك تطابق تام ، فأنت تريد أقرب قيمة تالية.

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

= MATCH ("Kilgore Trout"، B2: B6، 0)

سيعود هذا بقيمة 4. منذ أن بدأنا من الأعلى نعلم أن سمك السلمون المرقط قد أنهى المركز الرابع في هذا السباق بالذات.

مطابقة الفهرس

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

تُرجع الدالة INDEX قيمة محددة. هذه هي الوظيفة الأساسية لـ VLOOKUP ، بالنظر إلى مجموعة من المعلمات ؛ يمكنك العثور بسرعة على القيمة المطلوبة. الآن الشيء الوحيد الذي نفتقده هو طريقة لإيجاد موضع الصف. إذا كان عليك العثور على هذا يدويًا ، فستفقد فائدة الوظيفة. باستخدام MATCH في الوسيطة الثانية ، يمكننا أخذ مخرجاتها ، وهي موضع نسبي ، ودفعها إلى دالة الفهرس ، لحل هذه المشكلة. الآن لدينا شيء يقوم بنفس الشيء مثل VLOOKUP ، مع القليل من الكتابة.

وضع الوظيفة في الممارسة

باستخدام الجدول أدناه ، نحن مهتمون بمعرفة نوع المنتج المرتبط بمعرف منتج معين.

لاستخدام INDEX MATCH ، نبدأ بـ INDEX لأننا في النهاية نريد إرجاع قيمة محددة بدلاً من المركز. داخل الفهرس ، نقوم بتضمين MATCH لإرجاع موضع لوظيفة INDEX الخاصة بنا.

= INDEX (A2: A6، MATCH (8316، B2: B6،0))

ما يحدث هنا هو أنني سأبحث في العمود A عن قيمة الصف في العمود B ، التي توفرها وظيفة Match.

داخل وظيفة المطابقة ، أحدد أنني بحاجة إلى إلقاء نظرة على أكواد معرف المنتج في B2: B6 ، وإرسال موضع القيمة 8316 مرة أخرى. في هذه الحالة ، يكون في الموضع 4 وهو ما يعادل الكتابة

= INDEX (A2: A6، 4)

يقوم Excel بتشغيل الوظيفة والعثور على الموضع في صفيف رقم المعرف المحدد. يأخذ هذا الموضع ويبحث عن نفس الصف في العمود A لدينا ، ويعيد نوع الشاحنة للمعرف المحدد. لذلك ، في النهاية ، نحصل على نفس النتائج مع مزيد من المرونة في المدخلات.

امتيازات الوظيفة

تأتي قوة INDEX + MATCH من تحديد البيانات الأصغر. إذا كان لديك 15 عمودًا ولكنك تستخدم فقط الرقمين 1 و 15 ، فلماذا تسحب كل شيء آخر؟ يمكن أن يساعد ذلك في تسريع وقت تشغيل مجموعات البيانات الكبيرة.

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

هل تحتاج إلى المزيد من العناصر المرئية؟ شاهد تسجيلاً للشاشة لمثال "مطابقة الفهرس" حيث نقوم بإدراج عناوين url النهائية في نموذج نسخة الإعلان.

استنتاج

انطلق والعب مع INDEX + MATCH. إنها وظيفة قوية جدًا يمكنها تحسين قدراتك في البحث. بمجرد أن تنقر ، أنا متأكد من أنك ستباع ولن تنظر إلى الوراء أبدًا. هناك دائمًا القليل من منحنى التعلم عند تجربة شيء جديد ، التزم باستخدام Index Match لفترة من الوقت. سيكون الأمر صعبًا في البداية ولكنك سترى الفوائد قريبًا.

———-
تم تحديث المنشور بواسطة جينا كيلي (تاريخ النشر السابق: 2/3/14)