البحث عن القيم باستخدام VLOOKUP أو INDEX أو MATCH

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

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

ان الدالتين VLOOKUP و HLOOKUP ، مع الفهرسوالتطابق ،هما بعض الدالات الأكثر فائده في Excel.

ملاحظة: لم تعد ميزه "معالج البحث" متوفرة في Excel.

اليك مثال حول كيفيه استخدام VLOOKUP.

‎=VLOOKUP(B2,C2:E7,3,TRUE)‎

في هذا المثال ، تكون B2 هي الوسيطةالاولي — عنصر بيانات يجب ان تعمل الدالة. بالنسبة إلى الدالة VLOOKUP ، تكون هذه الوسيطة الاولي هي القيمة التي تريد البحث عنها. يمكن ان تكون هذه الوسيطة مرجع خليه ، أو قيمه ثابته مثل "سامح" أو 21,000. الوسيطة الثانية هي نطاق الخلايا ، C2-: E7 ، حيث يمكنك البحث عن القيمة التي تريد البحث عنها. الوسيطة الثالثة هي العمود في نطاق الخلايا الذي يحتوي علي القيمة التي تبحث عنها.

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

يوضّح لك هذا المثال طريقة عمل الدالة. عند إدخال قيمه في الخلية B2 (الوسيطة الاولي) ، يقوم VLOOKUP بالبحث عن الخلايا الموجودة في النطاق C2: E7 (الوسيطة 2 nd) وإرجاع أقرب تطابق تقريبي من العمود الثالث في النطاق ، العمود E (الوسيطة 3rd).

استخدام نموذجي للدالة VLOOKUP

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

عندما تشعر بالارتياح باستخدام VLOOKUP ، تكون الدالة HLOOKUP سهله الاستخدام بشكل متساو. تقوم بإدخال الوسيطات نفسها ، ولكنها تبحث في صفوف بدلا من الاعمده.

استخدام INDEX و MATCH بدلا من VLOOKUP

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

يُظهر هذا المثل قائمة صغيرة حيث أن القيمة "شيكاغو" التي نريد البحث عنها ليست ضمن العمود الموجود في أقصى اليمين. لذا لا يمكننا استخدام VLOOKUP. بدلاّ من ذلك، سنستخدم دالة MATCH للعثور على "شيكاغو" ضمن النطاق B1:B11. تم العثور عليها في الصف 4. بعد ذلك، تستخدم INDEX هذه القيمة كوسيطة بحث، وتعثر على سكان "شيكاغو" ضمن العمود الرابع (العمود D). تظهر الصيغة المستخدمة في الخلية A14.

استخدام INDEX وMATCH للبحث عن قيمة

للحصول علي المزيد من الامثله حول استخدام INDEX و MATCH بدلا من VLOOKUP ، راجع المقالة الhttps://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ه بواسطة بيل الكمبيالات ، Microsoft MVP.

لذلك، ما عليك سوى التجربة.

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

مثال VLOOKUP في العمل

انسخ البيانات التالية إلى جدول بيانات فارغ.

تلميح: قبل لصق البيانات في Excel ، عين عرض الاعمده من A إلى C إلى 250 بكسل ، وانقر فوق التفاف النص (علامة التبويبالصفحة الرئيسية ، المجموعة محاذاة ).

الكثافة

اللزوجة

درجة الحرارة

0,457

3,55

500

0.525

3.25

400

0.606

2.93

300

0.675

2.75

250

0.746

2.57

200

0.835

2.38

150

0.946

2.17

100

1.09

1.95

50

1.29

1.71

0

الصيغة

الوصف

النتيجة

‎=VLOOKUP‎(1,‎A2:C10,2)‎

تبحث عن القيمة 1 في العمود A، باستخدام التطابق التقريبي، وتعثر على أكبر قيمة أقل من أو تساوي 1 في العمود A، وهي 0.946، ثم تُرجع القيمة من العمود B في الصف نفسه.

2.17

‎=VLOOKUP(1,‎A2:C10,3,TRUE)‎

تبحث عن القيمة 1 في العمود A، باستخدام التطابق التقريبي، وتعثر على أكبر قيمة أقل من أو تساوي 1 في العمود A، وهي 0.946، ثم تُرجع القيمة من العمود C في الصف نفسه.

100

‎=VLOOKUP‎(0.7,‎A2:C10,3,FALSE)‎

تبحث عن القيمة 0.7 في العمود A، باستخدام التطابق التام. ونظراً إلى عدم وجود تطابق تام في العمود A، يتم إرجاع خطأ.

‎#N/A

‎=VLOOKUP‎(0.1,‎A2:C10,2,TRUE)‎

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

‎#N/A

‎=VLOOKUP‎(2,‎A2:C10,2,TRUE)‎

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

1.71

مثال HLOOKUP

انسخ كل الخلايا في هذا الجدول، والصقها في الخلية A1 في ورقة عمل فارغة في Excel.

تلميح: قبل لصق البيانات في Excel ، عين عرض الاعمده من A إلى C إلى 250 بكسل ، وانقر فوق التفاف النص (علامة التبويبالصفحة الرئيسية ، المجموعة محاذاة ).

المحاور

الصواميل

مسامير الربط

4

4

9

5

7

10

6

8

11

الصيغة

الوصف

النتيجة

A1:C4, 2, TRUE)‎,"المحاور") ‎=HLOOKUP

تبحث عن "المحاور" في الصف 1، وتُرجع القيمة من الصف 2 الموجود في نفس العمود (العمود A).

4

A1:C4,3,FALSE)‎,"الصواميل") ‎=HLOOKUP

تبحث عن "الصواميل" في الصف 1، وتُرجع القيمة من الصف 3 الموجود في نفس العمود (العمود B).

7

‎=HLOOKUP("B", A1:C4, 3, TRUE)‎

تبحث عن "B" في الصف 1، وتُرجع القيمة من الصف 3 الموجود في نفس العمود. نظراً لعدم العثور على تطابق تام للحرف "B"، سيتم استخدام أكبر قيمة في الصف 1 أقل من "B": وهي "المحاور" في العمود A.

5

A1:C4,4)‎,"قلاووظ") ‎=HLOOKUP

تبحث عن "قلاووظ" في الصف 1، وتُرجع القيمة من الصف 4 الموجود في نفس العمود (العمود C).

11

‎=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE‭)

تبحث عن الرقم 3 في ثابت الصفيف المتضمن للصفوف الثلاث، وتُرجع القيمة من الصف 2 الموجود في نفس العمود (وهو العمود الثالث في هذه الحالة). توجد ثلاثة صفوف في ثابت الصفيف، يتم الفصل بين كل صف وآخر بواسطة الفاصلة المنقوطة (;). نظراً لوجود "c" في الصف 2 وفي نفس العمود كما هو الحال في رقم 3، فسيتم إرجاع "c".

c

أمثله الفهرس والمطابقة

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

انسخ كل الخلايا في هذا الجدول، والصقها في الخلية A1 في ورقة عمل فارغة في Excel.

تلميح: قبل ان تقوم بلصق البيانات في Excel ، عين عرض الاعمده من A إلى الاعمده من البداية حتى الآن إلى 250 بكسل ، وانقر فوق التفاف النص (علامة التبويب الشريطالرئيسي ، المجموعة محاذاة ).

الفاتورة

المدينة

تاريخ الفاتورة

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

3115

جدة

07/04/12

(‎$B$2:$B$33,0),3),"m/d/yy"‎,"جدة")TEXT(INDEX($A$2:$C$33,MATCH‎ & " تاريخ الفاتورة: ,‎$B$2:$B$33,0),1)& "‎,"جدة")‎"&INDEX($A$2:$C$33,MATCH =جدة"=

3137

جدة

09/04/12

(‎$B$2:$B$33,0),3),"m/d/yy"‎,"الرياض")TEXT(INDEX($A$2:$C$33,MATCH‎ & " تاريخ الفاتورة: ,‎$B$2:$B$33,0),1)& "‎,"الرياض")‎"&INDEX($A$2:$C$33,MATCH =الرياض"=

3154

جدة

11/04/12

(‎$B$2:$B$33,0),3),"m/d/yy"‎,"الخبر")TEXT(INDEX($A$2:$C$33,MATCH‎ & " تاريخ الفاتورة: ,‎$B$2:$B$33,0),1)& "‎,"الخبر")‎"&INDEX($A$2:$C$33,MATCH =الخبر"=

3191

جدة

21/04/12

(‎$B$2:$B$33,0),3),"m/d/yy"‎,"الدمام")TEXT(INDEX($A$2:$C$33,MATCH‎ & " تاريخ الفاتورة: ,‎$B$2:$B$33,0),1)& "‎,"الدمام")‎"&INDEX($A$2:$C$33,MATCH =الدمام"=

3293

جدة

25/04/12

(‎$B$2:$B$33,0),3),"m/d/yy"‎,"المدينة")TEXT(INDEX($A$2:$C$33,MATCH‎ & " تاريخ الفاتورة: ,‎$B$2:$B$33,0),1)& "‎,"المدينة")‎"&INDEX($A$2:$C$33,MATCH =المدينة"=

3331

جدة

27/04/12

3350

جدة

28/04/12

3390

جدة

01/05/12

3441

جدة

02/05/12

3517

جدة

08/05/12

3124

الرياض

09/04/12

3155

الرياض

11/04/12

3177

الرياض

19/04/12

3357

الرياض

28/04/12

3492

الرياض

06/05/12

3316

Dallas

25/04/12

3346

Dallas

28/04/12

3372

الخبر

01/05/12

3414

الخبر

01/05/12

3451

Dallas

02/05/12

3467

Dallas

02/05/12

3474

Dallas

04/05/12

3490

Dallas

05/05/12

3503

الخبر

08/05/12

3151

الدمام

09/04/12

3438

الدمام

02/05/12

3471

الدمام

04/05/12

3160

المدينة

18/04/12

3328

المدينة

26/04/12

3368

المدينة

29/04/12

3420

المدينة

01/05/12

3501

المدينة

06/05/12

انظر أيضاً

بطاقة مرجعيه سريعة:

table_array

دالات البحث والمراجع الخاصة

التفوق على الآخرين مع Microsoft 365

هل تحتاج إلى مزيد من المساعدة؟

تعزيز مهارات Office
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×