كيفية عكس vlookup في excel باستخدام index-match؟

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

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

مؤشر تطابق الحصول على القيمة

لكن دعونا نتعلم بالتفصيل كيف يمكننا الجمع بين هاتين الوظيفتين. يوجد أدناه بناء جملة INDEX كما تعلم.

INDEX(صفيف،row_num،[column_num])

في الدالة INDEX، تخبرها الوسيطةrow_num بالصف الذي يجب أن تقوم بإرجاع القيمة منه. لنفترض أنه إذا قمت بإدخال 4، فسوف يُرجع قيمة الصف الرابع.

لإنشاء صيغة بحث عكسي، نحتاج إلى استبدال MATCH بـrow_argument الخاص بـ INDEX.

عندما نستخدم MATCH، فإنه يبحث عن القيمة في عمود البحث ويعيد رقم الخلية الخاص بتلك القيمة. وبعد ذلك، يستخدم INDEX هذا الرقم لتحديد موضع الخلية من عمود القيمة.

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

أدخل مثال على صيغة مطابقة الفهرس

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

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)
أدخل مثال على صيغة مطابقة الفهرس

دعونا نقسم هذه الصيغة إلى قسمين لفهمها.

الجزء 1 : في الجزء الأول، استخدمنا دالة المطابقة للبحث عن القيمة “مومباي” وأرجعت الرقم “5” وهو موضع الخلية التي لديك فيها القيمة “مومباي” في عمود المدن.

تطابق الفهرس كيف تعمل المطابقة

الجزء 2 : في الجزء الثاني، استخدمنا INDEX وأشرنا إلى عمود اسم الموظف للعثور على القيمة. هنا تعرف وظيفة الفهرس أنك تريد قيمة الخلية الخامسة من العمود. فأعاد “سيا” في النتيجة.

الفهرس هو كيفية عمل الفهرس

المزيد من الأمثلة على INDEX والمطابقة

لدينا بعض المشاكل الشائعة التي قمنا بحلها باستخدام صيغة INDEX MATCH. الوصول إلى نماذج الملفات: تأكد من تنزيل نماذج الملفات هذه من هنا لمتابعة كل مثال.

1. البحث الأساسي باستخدام INDEX – MATCH

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

البحث العادي مع جدول بيانات مطابقة الفهرس

لنفترض أنك تريد البحث عن الاسم EMP-132. ولهذا ستكون الصيغة:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

البحث العادي مع تطابق الفهرس أدخل الصيغة

هذه هي الطريقة التي تعمل بها هذه الصيغة

أولاً ، تقوم MATCH بمطابقة المعرف emp في عمود emp id وإرجاع رقم خلية المعرف الذي تبحث عنه. هنا رقم السطر هو 6.

البحث العادي مع تطابق الفهرس يعطي رقم السطر

بعد ذلك يقوم INDEX بإرجاع اسم الموظف من عمود الاسم باستخدام نفس رقم الخلية.

البحث العادي مع فهرس مطابقة الفهرس يُرجع الاسم

2. انظر إلى اليسار

لا يمكن لـ VLOOKUP الانتقال إلى اليسار عند البحث عن قيمة. كما ذكرت، في INDEX وMATCH يمكنك البحث في أي اتجاه. في جدول البيانات أدناه، يوجد عمود رقم الفاتورة بعد عمود المبلغ.

البحث الأيسر مع جدول بيانات مطابقة الفهرس

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

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

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

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

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

وفي النهاية، يستخدم INDEX هذا الرقم لإرجاع المبلغ عن طريق وضع الخلية من عمود المبلغ.

البحث الأيسر مع تطابق الفهرس يرجع المبلغ

3. بحث غامض

تمامًا مثل VLOOKUP، يمكنك أيضًا استخدام INDEX/MATCH لإجراء بحث تقريبي.

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

دقيقة فهرس غامض تطابق جدول البيانات

وإذا كنت ترغب في الحصول على درجة 79، يمكنك استخدام الصيغة أدناه.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

مطابقة فهرس البحث التقريبية أدخل الصيغة min

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

تطابق فهرس البحث الغامض يؤدي إلى إرجاع الدرجة

بالنسبة إلى 79، أول قيمة أقل هي 75، وبالنسبة إلى 75، فإن الدرجة هي B. ولهذا السبب تحصل على B في النتيجة.

4. البحث الأفقي

كما تعلم، فإن HLOOKUP مخصص للبحث الأفقي، ولكن يمكنك أيضًا استخدام INDEX وMATCH لذلك. هنا في جدول البيانات أدناه لديك جدول أفقي للمبيعات الشهرية وتريد الحصول على قيمة المبيعات لشهر “مايو”.

فهرس البحث الأفقي يطابق صف البيانات

وستكون الصيغة:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

مطابقة فهرس البحث الأفقي أدخل الصيغة

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في الصيغة أعلاه، بدلاً من استخدام MATCH في الوسيطةrow_num للفهرس، استخدمناها في column_num. وتقوم المطابقة بإرجاع رقم العمود لشهر مايو.

تؤدي مطابقة فهرس البحث الأفقي إلى إرجاع رقم العمود

ثم تقوم INDEX بإرجاع قيمة عمود النتيجة بناءً على رقم الموضع.

تطابق فهرس البحث الأفقي يُرجع المبلغ

5. البحث في اتجاهين

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

يطابق فهرس البحث ثنائي الاتجاه بيانات المبيعات

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

في تركيبة عادية من INDEX وMATCH، يمكنك استخدام MATCH لرقم الصف، ولكن في البحث ثنائي الاتجاه، يجب أيضًا استخدامه لرقم العمود. الصيغ ستكون:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

مطابقة فهرس البحث ثنائي الاتجاه، أدخل الصيغة

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في الصيغة أعلاه، للحصول على مبلغ المبيعات من الجدول، استخدمت دالة الفهرس ثم دالة المطابقة للوسيطتينrow_num وcolumn_num. تقوم دالة المطابقة، وهي الوسيطة column_num، بإرجاع 5 كقيمة المنتج-D في الصف الخامس من النطاق الذي أشرت إليه.

تؤدي مطابقة فهرس البحث ثنائي الاتجاه إلى إرجاع رقم الصف

وتقوم دالة المطابقة الموجودة في الوسيطةrow_num بإرجاع 2 لأن قيمة المنطقة الشمالية موجودة في العمود الثاني من النطاق الذي أشرت إليه.

تؤدي مطابقة فهرس البحث ثنائي الاتجاه إلى إرجاع رقم العمود

الآن، باستخدام هذه القيم، قامت دالة الفهرس بإرجاع القيمة الموجودة في العمود الثاني والصف الخامس: 1456.

تؤدي مطابقة فهرس البحث ثنائي الاتجاه إلى إرجاع مبلغ المبيعات

6. حساس لحالة الأحرف

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

يتطابق فهرس البحث الحساس لحالة الأحرف مع بيانات الطالب

وفي البداية، هناك أسماء متشابهة ولكن في حالات نصية مختلفة. على سبيل المثال، جون باركر وجون ماثيو. لنفترض أنك تريد البحث عن العلامات التجارية لـ “JOHN” وليس “John”، فيمكنك إنشاء بحث مطابق تمامًا باستخدام INDEX وMATCH. وستكون الصيغة:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

تطابق فهرس البحث الحساس لحالة الأحرف، أدخل الصيغة

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

يمكنها مقارنة قيمتين وإرجاع TRUE إذا كانتا متطابقتين تمامًا (بما في ذلك حالة الأحرف)، ولكن يجب عليك إدخال هذه الصيغة في شكل جدول لأنك تحتاج إلى مقارنة العمود بأكمله بقيمة واحدة في EXACT. عند إدخاله، سيُرجع مصفوفة مثل هذا.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

يتطابق فهرس البحث الحساس لحالة الأحرف مع مصفوفة الإرجاع الدقيقة

من هناك تحتاج إلى استخدام وظيفة المطابقة للحصول على موضع TRUE من المصفوفة.

فهرس البحث الحساس لحالة الأحرف يطابق رقم الصف

عند هذه النقطة لديك رقم الخلية (رقم الصف) للقيمة التي تحتاج إلى البحث عنها. ويمكنك استخدام INDEX لقيمة العمود هذه.

تؤدي مطابقة فهرس البحث الحساس لحالة الأحرف إلى إرجاع درجات الطالب

نقطة مهمة: إذا حصلت على أكثر من TRUE واحد مع EXACT، فإن المطابقة ستعيد ببساطة الرقم الخاص بـ TRUE الأول

7. مع أحرف البدل مع INDEX MATCH

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

ما عليك سوى إلقاء نظرة على قائمة الأسماء أدناه حيث يوجد الاسم الأول والأخير للموظفين وأعمارهم.

ابحث عن بيانات مطابقة فهرس حرف البدل

من هذه القائمة تحتاج إلى الحصول على عمر موظف معين (سوندرا). لكن الحقيقة هي أنك تعرف الاسم الأول فقط.

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

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

البحث عن تطابق فهرس أحرف البدل، وإدخال البيانات

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

تُرجع مطابقة فهرس حرف البدل رقم الصف

8. أدنى قيمة

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

العثور على بيانات مطابقة مؤشر القيمة الأدنى

لهذا، يمكنك استخدام الدالة MIN مع الفهرس والمطابقة وستكون الصيغة:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

العثور على تطابق الفهرس لأدنى قيمة، أدخل الصيغة

لديك أسماء الطلاب في العمود (أ) ونتائجهم في العمود (ب).

لذا، عند إدراج هذه الصيغة في خلية والضغط على Enter، فإنها ستعيد اسم الطالب الحاصل على أقل درجة، أي Librada Bastian.

توضيح

في هذه الصيغة لدينا ثلاثة أجزاء مختلفة.

في الجزء الأول ، تقوم الدالة MIN بإرجاع أدنى درجة.

ابحث عن تطابق الفهرس ذي القيمة الأقل لإرجاع القيمة

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

العثور على مطابقة مؤشر القيمة الأدنى وإرجاع رقم الصف

في النهاية، تقوم دالة الفهرس بإرجاع قيمة عمود اسم الطالب باستخدام نفس موضع الخلية الذي يتم إرجاعه عن طريق المطابقة.

البحث عن اسم إرجاع لمطابقة فهرس القيمة الأقل

نصيحة: بنفس الطريقة، يمكنك أيضًا الحصول على اسم الطالب الذي حصل على أعلى الدرجات.

9. أفضل نتيجة n

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

بيانات مطابقة مؤشر النتيجة العليا

المشكلة هي أنك لا تعرف ما هي ثاني أعلى نتيجة.

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

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

وستكون الصيغة:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

أعلى نتيجة مطابقة لمؤشر النتيجة أدخل الصيغة

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في هذه الصيغة، استخدمت الدالة الكبيرة داخل دالة المطابقة للوسيطة lookup_value. وفي الدالة الكبيرة ذكرت نطاق النتيجة و2 للحصول على ثاني أعلى قيمة.

يتطابق مؤشر النتيجة الأعلى مع العائد الكبير وثاني أعلى قيمة

بعد أن تقوم الدالة الكبيرة بإرجاع ثاني أكبر قيمة، تستخدم دالة المطابقة تلك القيمة وترجع رقم الخلية المقابل.

يتطابق مؤشر النتيجة الأعلى مع رقم سطر الإرجاع

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

أعلى نتيجة مطابقة لمؤشر النتيجة n تُرجع الاسم

10. معايير متعددة

عادةً ما يكون الهدف من الجمع بين الفهرس والمطابقة هو البحث عن قيمة واحدة. ولهذا السبب تستخدم نطاقًا واحدًا فقط في وظيفة المطابقة.

ولكن، في بعض الأحيان عندما تواجه بيانات في العالم الحقيقي، تحتاج إلى استخدام معايير متعددة للعثور على القيمة.

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

ومن هذه البيانات تريد الحصول على سعر منتج معين باستخدام جميع المعايير.

بيانات مطابقة الفهرس متعدد المعايير

وبالتالي فإن الصيغة ستكون:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

تطابق فهرس معايير متعددة أدخل الصيغة

ملاحظة: هذه صيغة مصفوفة، لذا عليك إدخالها باستخدام ctrl + Shift + enter.

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في هذه الصيغة لديك ثلاث صفائف مختلفة لمطابقة ثلاث قيم مختلفة وتقوم هذه الصفائف بإرجاع TRUE وFALSE حيث تتطابق القيم.

بعد ذلك عندما تقوم بضربهم مع بعضهم البعض تحصل على مصفوفة أو شيء من هذا القبيل.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

وتقوم دالة المطابقة بإرجاع الموضع 1 للمصفوفة.

فهرس معايير متعددة يطابق رقم صف الإرجاع

في النهاية، يقوم INDEX بإرجاع سعر عمود السعر باستخدام الرقم الذي تم إرجاعه بواسطة المطابقة.

معايير متعددة مطابقة مؤشر سعر الوحدة

تلميح: إذا كنت لا تريد استخدام صيغة صفيف، فيمكنك استخدام شرط SUMPRODUCT .

11. القيمة العددية الأولى للنطاق

لنفترض أن لديك قائمة تحتوي على قيم نصية ورقمية، والآن تريد الحصول على القيمة الرقمية الأولى من هذه القائمة.

أول بيانات مطابقة لمؤشر القيمة الرقمية

للقيام بذلك، يمكنك دمج الدالة ISNUMBER مع الفهرس/المطابقة. يمكن أن يساعدك ISNUMBER في تحديد القيمة التي تمثل رقمًا والقيمة التي تمثل نصًا.

الصيغة ستكون:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

أول تطابق لمؤشر القيمة الرقمية، أدخل الصيغة

يجب عليك إدخال هذه الصيغة في شكل جدول (باستخدام Ctrl + Shift + Enter).

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في هذه الصيغة تقوم ISNUMBER بإرجاع مصفوفة مساوية لطول القائمة وفي هذه المصفوفة لديك TRUE للقيم التي هي أرقام وFALSE لبقية القيم.

يتطابق فهرس القيمة الرقمية الأول مع مصفوفة الأرقام

بعد ذلك استخدمت TRUE في وظيفة المطابقة كقيمة بحث. لذلك تقوم بإرجاع رقم موضع أول TRUE في المصفوفة.

أول مطابقة لمؤشر القيمة الرقمية تُرجع رقم الصف

في النهاية، يؤدي استخدام مؤشر رقم الموضع هذا إلى إرجاع القيمة الرقمية الأولى.

48-أول-قيمة-رقمية-مطابقة-إرجاع-أول-رقم-دقيقة

12. احصل على القيمة الأولى غير الفارغة

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

بيانات مطابقة الفهرس الأولى غير الفارغة

ويمكنك استخدام هذه الصيغة للحصول على هذه القيمة الأولى غير الفارغة.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

أول تطابق للفهرس غير الفارغ، أدخل الصيغة

…هذه هي الطريقة التي تعمل بها هذه الصيغة

نحن بحاجة إلى تقسيم هذه الصيغة إلى ثلاثة أجزاء مختلفة لفهمها بحكمة.

أولاً ، استخدمت الدالة ISBLANK في دالة المطابقة للحصول على مصفوفة حيث يكون لديك TRUE للخلايا الفارغة وFALSE للخلايا غير الفارغة.

أول تطابق للفهرس غير الفارغ هو مصفوفة فارغة

ثانيًا ، تقوم الدالة MATCH بإرجاع رقم موضع القيمة TRUE الأولى في المصفوفة التي يتم إرجاعها بواسطة ISBLANK.

إذن، في هذه المرحلة لديك رقم الخلية للقيمة غير الفارغة الأولى.

أول تطابق للفهرس غير الفارغ يُرجع رقم الصف

ثالثًا ، تقوم دالة الفهرس ببساطة بإرجاع القيمة الأولى غير الفارغة في القائمة.

تُرجع مطابقة الفهرس الأولى غير الفارغة قيمة الخلية الأولى غير الفارغة

13. النص الأكثر تكرارا

لنفترض الآن أنه في ضوء قائمة القيم النصية، فإنك تحتاج إلى حساب النص الأكثر تكرارًا.

في القائمة أدناه لديك أسماء.

ولكن، هناك بعض الأسماء التي يتم تكرارها أكثر من مرة.

بيانات مطابقة فهرس النص الأكثر شيوعًا

لذا، أنت الآن بحاجة إلى الحصول على الاسم الذي له أعلى تكرار في القائمة. يمكنك استخدام الصيغة أدناه والتي هي عبارة عن مزيج من MODE وINDEX وMATCH.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

تطابق فهرس النص الأكثر شيوعًا، أدخل الصيغة

عندما تقوم بإدراج هذه الصيغة فإنها ستعيد “Tamesha” وهو الاسم الأكثر شيوعًا.

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

لنأخذ مثالاً على اسم “Tamesha” وهو الاسم الأكثر شيوعًا لدينا في القائمة. الآن، إذا نظرت إلى القائمة، ستجد أن هذا حدث أولاً في الخلية الثامنة ثم في الخلية الثانية عشرة.

لكن، إذا نظرت إلى الجدول، فبالنسبة لجميع المواضع التي لدينا فيها “تامشا”، فقد أعادت 8 وهو موضعها الأول.

جدول البحث عن فهرس النص الأكثر شيوعًا

بعد ذلك، من المصفوفة التي تم إرجاعها بواسطة الدالة MATCH، ستقوم دالة الوضع بإرجاع الرقم الأكثر تكرارًا وهو رقم الخلية لأول ظهور لـ ” Tamesha “.

يقوم وضع مطابقة فهرس النص الأكثر شيوعًا بإرجاع رقم الخلية

وفي النهاية، سيُرجع INDEX النص باستخدام رقم الخلية هذا.

يتطابق فهرس النص الأكثر شيوعًا مع اسم الإرجاع

14. قم بإنشاء ارتباط تشعبي

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

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

إنشاء بيانات مطابقة فهرس الارتباط التشعبي

ولهذا يجب علينا استخدام HYPERLINK + Cell مع INDEX وMATCH وستكون الصيغة:

هذه هي الطريقة التي تعمل بها هذه الصيغة

دعونا نقسم هذه الصيغة إلى عدة أجزاء لفهمها بشكل أفضل.

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

الصيغ ذات الصلة

اضف تعليق

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *