تلميح: حاول استخدام الدالتين 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 (الوسيطة الثالثة).
الوسيطة الرابعة فارغة، لذلك ترجع الدالة تطابقا تقريبيا. وإذا لم تفعل ذلك، فعليك إدخال إحدى القيم في العمود C أو D للحصول على نتيجة.
عندما تكون مرتاحا مع VLOOKUP، تكون الدالة HLOOKUP سهلة الاستخدام بنفس القدر. يمكنك إدخال الوسيطات نفسها، ولكنها تبحث في صفوف بدلا من الأعمدة.
استخدام INDEX وMATCH بدلا من VLOOKUP
هناك قيود معينة على استخدام VLOOKUP - يمكن لدالة VLOOKUP البحث عن قيمة فقط من اليسار إلى اليمين. وهذا يعني أن العمود الذي يحتوي على القيمة التي تبحث عنها يجب أن يكون دائما موجودا إلى يسار العمود الذي يحتوي على القيمة المرجعة. الآن إذا لم يتم إنشاء جدول البيانات بهذه الطريقة، فلا تستخدم VLOOKUP. استخدم تركيبة الدالتين INDEX وMATCH بدلا من ذلك.
يُظهر هذا المثل قائمة صغيرة حيث أن القيمة "شيكاغو" التي نريد البحث عنها ليست ضمن العمود الموجود في أقصى اليمين. لذا لا يمكننا استخدام VLOOKUP. بدلاّ من ذلك، سنستخدم دالة MATCH للعثور على "شيكاغو" ضمن النطاق B1:B11. تم العثور عليها في الصف 4. بعد ذلك، تستخدم INDEX هذه القيمة كوسيطة بحث، وتعثر على سكان "شيكاغو" ضمن العمود الرابع (العمود D). تظهر الصيغة المستخدمة في الخلية A14.
لمزيد من الأمثلة على استخدام 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 |
