تلميح
حاول استخدام الدالتين XLOOKUPوXMATCH الجديدتين، وهما إصداران محسنة من الدالات الموضحة في هذه المقالة. تعمل هذه الدالات الجديدة في أي اتجاه وترجع تطابقات دقيقة بشكل افتراضي، مما يجعلها أسهل وأكثر ملاءمة للاستخدام من سابقاتها.
لنفترض أن لديك قائمة بأرقام مواقع المكاتب، وكنت بحاجة إلى معرفة الموظفين الموجودين في كل مكتب. جدول البيانات ضخم، لذلك قد تعتقد أنها مهمة صعبة. من السهل جدا فعلا القيام بذلك باستخدام وظيفة البحث.
تعد الدالتان VLOOKUPوHLOOKUP ، بالإضافة إلى INDEXوMATCH، بعض الدالات الأكثر فائدة في Excel.
ملاحظة
لم تعد ميزة "معالج البحث" متوفرة في Excel.
فيما يلي مثال حول كيفية استخدام VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
في هذا المثال، B2 هي الوسيطة الأولى، وهي عنصر بيانات تحتاج إليه الدالة لكي تعمل. بالنسبة إلى VLOOKUP، هذه الوسيطة الأولى هي القيمة التي تريد البحث عنها. يمكن أن تكون هذه الوسيطة مرجعا لخلية أو قيمة ثابتة مثل "أبو" أو 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.
لذلك، ما عليك سوى التجربة.
إذا كنت تريد تجربة دالات البحث قبل تجربة استخدامها مع البيانات الخاصة بك، فإليك بعض البيانات النموذجية.
مثال 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