Applies ToExcel لـ Microsoft 365 Excel على الويب Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

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

تعد الدالتان VLOOKUPوHLOOKUP ، جنبا إلى جنب مع INDEXوMATCH، بعض الدالات الأكثر فائدة في Excel.

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

فيما يلي مثال على كيفية استخدام VLOOKUP.

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

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

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

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

استخدام نموذجي للدالة 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/ بواسطة Bill Jelen، 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

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

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

تلميح: قبل لصق البيانات في Excel، قم بتعيين عرض الأعمدة للأعمدة من A إلى D إلى 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

الخبر

25/04/12

3346

الخبر

28/04/12

3372

الخبر

01/05/12

3414

الخبر

01/05/12

3451

الخبر

02/05/12

3467

الخبر

02/05/12

3474

الخبر

04/05/12

3490

الخبر

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

بطاقة مرجعية سريعة: دالات البحث عن تحديث VLOOKUP

والمرجع (مرجع)

استخدم الوسيطة table_array في دالة VLOOKUP

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

الخروج من الخيارات إضافية؟

استكشف مزايا الاشتراك، واستعرض الدورات التدريبية، وتعرف على كيفية تأمين جهازك، والمزيد.

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