بعد أن تعرفنا على دوال IF الإحصائية وكيفية استخدامها في برنامج اكسل Excel، سنتعرف في هذا المقال الذي هو جزء من سلسلة استخدام مايكروسوفت إكسل على الدوال البحثية وكيفية استخدامها في اكسل.
تُستخدم الدوال البحثية عادةً للبحث عن البيانات الموجودة في أوراق العمل أو المصنفات الأخرى وعرضها. الدالتان البحثيتان اللتان سنستخدمهما في مثالنا لملف الاستثمار الشخصي هما دالتي VLOOKUP و HLOOKUP.
دالة VLOOKUP
تُستخدم دالة VLOOKUP عادةً للوصول إلى البيانات الموجودة في ورقة عمل أو مصنف آخر وعرضها، كما يمكن استخدامها للوصول إلى البيانات الموجودة في نفس ورقة العمل وعرضها. تُعَد هذه دالةً قويةً للغاية ومتعددة الاستخدامات لأنها تُوفر عناء الحاجة إلى نسخ أو إعادة إنشاء البيانات الموجودة في أوراق العمل أو المصنفات الأخرى.
يطلق على هذه الدالة تسمية VLOOKUP لأن الدالة ستبحث عموديًا vertically في العمود الأول من نطاق الخلايا للعثور على ما يسمى Lookup_value، وهي القيمة التي ستبحث عنها، وهذه العملية تشبه إلى حدٍ بعيد دوال IF الإحصائية التي ذكرناها في المقال السابق.
تتشابه دالة VLOOKUP كثيرًا مع دالة IF لكنها بدلًا من تحديد خلايا متعددة من نطاقِ معين، فهي تبحث فقط عن موقع خلية واحد محدد، وبمجرد أن تعثر عليه، ستعرض محتويات موقع الخلية هذا أو موقع خلية آخر في النطاق.
قبل استخدام دالة VLOOKUP في ورقة العمل بهذا المشروع، تحديدًا ملف الاستثمار الشخصي، نوصي بشدة بقراءة التعريفات الخاصة بوسائط الدالة المدرجة في الجدول التالي:
الوسيطة | تعريفها |
---|---|
القيمة التي ستبحث عنها Lookup_value |
تُعَرف هذه الوسيطة عادةً بموقع الخلية أو الرقم أو النص. ويجب أن تكون البيانات النصية محاطة بعلامات اقتباس في هذه الوسيطة. ستبحث الدالة عن المعايير التي اُدخلت في هذه الوسيطة في العمود الأول من النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array. على سبيل المثال: إذا استُخدمت كلمة "قبعة" لتعريف هذه الوسيطة، فستبحث الدالة عن الكلمة "قبعة" في العمود الأول من النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array |
جدول البيانات Table_array |
مجموعة من الخلايا التي تحتوي على بيانات تريد أن تقوم دالة VLOOKUP بالبحث عنها من خلال Lookup_value وعرضها. يجب أن يحتوي نطاق الخلايا هذا على المعايير المستخدمة لتعريف وسيطة Lookup_value في العمود الأول. على سبيل المثال: إذا استُخدم النطاق A2:D15 لتعريف هذه الوسيطة، فيجب أن تكون المعايير المستخدمة لتعريف وسيطة Lookup_value موجودةً في العمود A. |
رقم أو ترتيب العمود Col_index_num |
هذه هي وسيطة رقم العمود وترتيبه. تُعَرف بعدد الأعمدة الموجودة على يمين العمود الأول في النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array التي تحتوي على البيانات التي ترغب في عرضها. على سبيل المثال: افترض أن البيانات التي تريد أن تعرضها الدالة موجودة في العمود C. إذا كان النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array هو A2:D15، فسيكون رقم ترتيب العمود هو 3. بعد الأعمدة على يمين الأول في هذا النطاق، سيكون العمود A هو 1، والعمود B سيكون 2، والعمود C سيكون 3. من المهم تذكر عَد العمود الأول في نطاق الجدول على أنه 1. |
القيمة المنطقية [Range_lookup] |
تُعَرف هذه الوسيطة إما بالكلمة صحيح True، أو الكلمة خاطئ False.
|
جدول شرح وسيطات دالة VLOOKUP
التأكد من صحة العمل
إذا كنت تُعَرف وسيطة القيمة المنطقية [Range_lookup] بالكلمة True في دالة VLOOKUP أو HLOOKUP، فيجب فرز النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array بترتيب تصاعدي. بالنسبة لدالة VLOOKUP، يجب فرز نطاق الجدول من الأصغر إلى الأكبر أو من الألف إلى الياء استنادًا إلى القيم الموجودة في العمود الأول؛ أما بالنسبة لدالة HLOOKUP، فيجب فرز نطاق الجدول من اليسار إلى اليمين بناءً على القيم الموجودة في الصف الأول، من الأصغر إلى الأكبر أو من الألف إلى الياء.
كما نلاحظ، هناك عمود فارغ في ورقة عمل تفاصيل العملية الاستثمارية، وهذا العمود هو عمود الوصف. تم تضمين أوصاف العديد من الاستثمارات في المصنف في ورقة عمل قائمة الاستثمارات. ستُستخدم دالة VLOOKUP للبحث عن رمز معين في العمود A من ورقة عمل قائمة الاستثمارات وعرض وصف هذا الرمز الموجود في العمود B.
ورقة عمل قائمة الاستثمارات
توضح الخطوات التالية كيفية تحقيق ذلك:
- نضغط على الخلية C4 في ورقة عمل تفاصيل العملية الاستثمارية
- نضغط على علامة تبويب "الصيغ" في شريط إكسل
- نضغط على زر البحث والمراجع في مجموعة أوامر مكتبة الدوال
- نحدد دالة VLOOKUP من قائمة الدوال، ونستخدم شريط التمرير للتمرير لأسفل إلى أسفل القائمة. سيؤدي هذا إلى فتح نافذة وسائط الدالة لدالة VLOOKUP
- نضغط على زر طي النافذة الموجود بجوار وسيطة Lookup_value في نافذة وسيطات الدالة
- نضغط على الخلية B4 ثم على مفتاح الإدخال Enter بلوحة المفاتيح. الرمز الموجود في الخلية B4 هو القيمة التي ستبحث عنها في العمود الأول من النطاق المحدد لوسيطة جدول البيانات Table_array
- نضغط على زر طي النافذة الموجود بجوار وسيطة جدول البيانات Table_array في نافذة وسيطات الدالة
- نضغط على علامة تبويب ورقة عمل قائمة الاستثمارات
- نحدد النطاق A3:F23 في ورقة عمل قائمة الاستثمارات، ثم نضغط على مفتاح الإدخال Enter بلوحة المفاتيح. ستبحث الدالة في العمود A لهذا النطاق عن القيمة المطلوبة
- نضغط على مربع الإدخال الخاص بوسيطة جدول البيانات Table_array، مع وضع مرجع مطلق في النطاق A3:F23؛ وذلك عن طريق كتابة علامة الدولار $ أمام حرف العمود ورقم الصف لكل موقع خلية في النطاق
- نضغط على المفتاح Tab في لوحة المفاتيح للتقدم إلى الوسيطة رقم أو ترتيب العمود Col_index_num مع كتابة الرقم 2. بمجرد عثور الدالة على قيمة البحث في العمود A من النطاق A3:F23، ستعرض الوصف الموجود في العمود B من نفس الصف
- نضغط على مفتاح Tab في لوحة المفاتيح للتقدم إلى وسيطة القيمة المنطقية Range_lookup ونكتب الكلمة False. سيؤدي هذا إلى توجيه الدالة للبحث عن التطابقات التامة فقط للقيمة المطلوبة
- نضغط على زر موافق في أسفل نافذة وسيطات الدالة
- ننسخ دالة VLOOKUP في الخلية C4 ونلصقها في النطاق C5:C18 باستخدام أمر لصق الصيغ
يوضح الشكل التالي نافذة وسيطات الدالة المكتملة لدالة VLOOKUP، وكما نلاحظ، فوسيطة القيمة المنطقية Range_lookup محددة بالكلمة False. سيوجه هذا الدالة للبحث عن تطابق تام للقيمة التي تبحث عنها وسيوجه الدالة أيضًا للبحث في العمود الأول بأكمله من جدول النطاق. وأخيرًا، من المهم ملاحظة المرجع المطلق في جدول النطاق. سيمنع هذا الجدول من التغيير عند لصق الدالة في مواقع خلايا أخرى.
نافذة وسائط الدالة المكتملة لدالة VLOOKUP
يوضح الشكل التالي نتائج دالة VLOOKUP في ورقة عمل تفاصيل العملية الاستثمارية. تبحث الدالة عن كل رمز في العمود B من ورقة عمل تفاصيل العملية الاستثمارية في العمود A من ورقة عمل قائمة الاستثمارات. عندما تجد الدالة تطابقًا، فستعرض كل ما هو موجود في العمودين على يمين الخلية، أو العمود B في ورقة عمل قائمة الاستثمارات.
نتائج دالة VLOOKUP في ورقة عمل تفاصيل العملية الاستثمارية
على سبيل المثال، الرمز VDMIX الموجود في الخلية B8 في ورقة عمل تفاصيل العملية الاستثمارية، موجود أيضًا في الخلية A15 في ورقة عمل قائمة الاستثمارات. ونتيجةً لذلك، ستعرض الدالة كل ما هو موجود في الخلية B15 في ورقة عمل قائمة الاستثمارات، وهو وصف الأسواق المطورة.
التأكد من صحة العمل
المراجع مطلقة في جدول النطاق لدوال VLOOKUP و HLOOKUP:
إذا كنا سننسخ دالة VLOOKUP أو HLOOKUP للصقها، فسنحتاج على الأرجح إلى وضع مرجع مطلق على النطاق المستخدم في تعريف وسيطة جدول البيانات Table_array. سيتغير جدول النطاق بسبب المرجع النسبي بمجرد لصق الدالة في مواقع خلايا جديدة. قد ينتج عن هذا ناتجًا خاطئًا لدالة VLOOKUP أو HLOOKUP، وهذا لأن الدالة لن تكون قادرةً على العثور على القيمة التي تبحث عنها بسبب تعديل النطاق.
إذا كنا نعرّف وسيطة القيمة المنطقية Range_lookup بالكلمة True، فسيؤدي التعديل في جدول النطاق إلى ناتج خاطئ.
المراجعة على المهارة المكتسبة: دالة VLOOKUP
من أجل إضافة دالة VLOOKUP خلال المراجعة على المهارة المكتسبة، لا بد من اتباع الخطوات التالية:
- كتابة علامة يساوي =
- كتابة اسم الدالة VLOOKUP متبوعًا بقوس مفتوح (
- تحديد وسيطة Lookup_value بموقع الخلية أو الرقم أو النص الذي ستبحث عنه في مصنف أو ورقة عمل أخرى؛ ويجب وضع النص بين علامات اقتباس
- كتابة فاصلة
- تحديد الوسيطة جدول البيانات Table_array بنطاق من الخلايا التي تحتوي على قيمة البحث في العمود الأول، بالإضافة إلى البيانات التي ستُعرض أو تُستخدم بواسطة الدالة
- كتابة فاصلة
- تحديد وسيطة رقم أو ترتيب العمود Col_index_num برقم يَعُد الأعمدة على يمين جدول النطاق الذي سيُعرض بواسطة الدالة، ثم حساب العمود الأول من جدول النطاق على أنه 1
- كتابة فاصلة
- تحديد وسيطة القيمة المنطقية Range_lookup إما بالكلمة False أو الكلمة True. ستبحث الكلمة False عن التطابقات التامة للقيمة التي تبحث عنها، كما ستبحث الكلمة True عن تطابق تقريبي، أو أقرب تطابق أقل من القيمة التي تبحث عنها. سيُعَرف إكسل هذه الوسيطة على أنها صحيحة True
- كتابة قوس إغلاق )
- الضغط على مفتاح الإدخال Enter بلوحة المفاتيح
دالة HLOOKUP
تؤدي دالة HLOOKUP نفس الغرض تمامًا مثل دالة VLOOKUP، حيث يمكن استخدام دالة HLOOKUP لعرض البيانات من ورقة عمل أو مصنف آخر، لكن بدلًا من البحث عن القيمة المطلوبة عموديًا لأسفل العمود الأول من جدول النطاق، ستبحث دالة HLOOKUP أفقيًا عبر الصف الأول من جدول النطاق.
عندما تعثر الدالة على تطابق مع القيمة المطلوبة، فستعرض المحتويات الموجودة في موقع الخلية بناءً على رقم الصف. يعيّن هذا الرقم عدد الصفوف الموجودة أسفل الصف الأول من جدول النطاق الذي يجب أن تعرضه الدالة، ويوفر الجدول التالي تعريفًا لكل وسيطة للدالة HLOOKUP. من الأفضل مراجعة تعريفات هذه الوسائط بعناية قبل استخدام الدالة.
الوسيطة | تعريفها |
---|---|
القيمة التي ستبحث عنها Lookup_value |
تُعَرف هذه الوسيطة عادةً بموقع الخلية أو الرقم أو النص. يجب أن تكون البيانات النصية محاطةً بعلامات اقتباس في هذه الوسيطة، حيث ستبحث الدالة عن المعايير التي أُدخلت في هذه الوسيطة في الصف الأول من النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array. على سبيل المثال، إذا استُخدمت كلمة "قبعة" لتعريف هذه الوسيطة، فستبحث الدالة عن كلمة "قبعة" في الصف الأول من النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array. |
جدول البيانات Table_array |
مجموعة من الخلايا التي تحتوي على البيانات التي تريد أن تقوم دالة HLOOKUP بالبحث عنها من خلال وسيطة Lookup_value وعرضها. يجب أن يحتوي نطاق الخلايا هذا على المعايير المستخدمة لتعريف وسيطة Lookup_value في الصف الأول. على سبيل المثال، إذا أُستخدم النطاق A2:D15 لتعريف هذه الوسيطة، فيجب أن تكون المعايير المستخدمة لتعريف وسيطة Lookup_value موجودةً في الصف 2. |
رقم أو ترتيب الصف Row_index_num |
هذه هي وسيطة رقم الصف وترتيبه. تُعَرف بعدد الصفوف الموجودة أسفل الصف الأول في النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array الذي يحتوي على البيانات التي ترغب في عرضها. على سبيل المثال، إذا افترضنا أن البيانات التي نريد أن تعرضها الدالة موجودة في الصف 5، فإذا كان النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array هو A2:D15، فسيكون رقم ترتيب العمود هو 4. عدّ الصفوف الموجودة أسفل الصف الأول في هذا النطاق، سيكون الصف 2 هو 1، والصف 3 سيكون 2، والصف 4 سيكون 3، والصف 5 سيكون 4. من المهم أن تتذكر حساب الصف الأول في جدول النطاق على أنه 1. |
القيمة المنطقية Range_lookup |
تُعَرف هذه الوسيطة إما بكلمة True أو كلمة False. عندما تُعَرف هذه الوسيطة بكلمة False، فستبحث الدالة عن تطابق تام مع المعايير المستخدمة لتعريف وسيطة Lookup_value في الصف الأول من جدول النطاق. من المهم ملاحظة أن الدالة ستبحث في النطاق بأكمله للعثور على تطابق. إذا عُرفت هذه الوسيطة بكلمة True، فستبحث الدالة عن قيمة متطابقة تقريبيًا أو أقرب تطابق أقل من القيمة المطلوبة. على سبيل المثال، إذا كانت القيمة المطلوبة 80 وكانت أعلى قيمة في الصف الأول من جدول النطاق 78، فستتخذ الدالة 78 كرقمًا متطابقًا تقريبًا مع الرقم 80؛ أما إذا كانت القيمة المطلوبة 80 وأقل رقم في الصف الأول من جدول النطاق 85، فستنتج الدالة خطأً، وهذا بسبب عدم وجود الرقم 80 وأي قيمة أقل من 80 في الصف الأول من جدول النطاق. |
وسيطات دالة HLOOKUP
ستُستخدم دالة HLOOKUP في ورقة عمل ملخص الملف الاستثماري لعرض معدلات النمو المعيارية في النطاق G4:G7.
اقتباسالمقارنة المعيارية هي عملية حيث تُتخذ قيمة أو مثال نوعي كنقطة قياسية مرجعية. والنتائج التي تكون أعلى أو أفضل من القيمة المعيارية تُعَد إيجابية عادةً، في حين أن النتائج الأقل أو الأسوأ من القيمة المعيارية تُعَد سلبية عادةً.
تحتوي ورقة عمل المقارنة المعيارية على معدلات نمو لفترات سنوية مختلفة للقيم المعيارية التي ستُستخدم لتقييم الأداء لكل نوع استثمار. ولتحقيق أغراض هذا المُصنف سنوازن معدلات النمو لكل نوع استثمار بمتوسط معدل النمو لمدة 5 سنوات لفئات المقارنة المعيارية المدرجة في النطاق H4:H7.
ورقة عمل المقارنة المعيارية
تشرح الخطوات التالية كيفية إنشاء دالة HLOOKUP لعرض القيم المعيارية لمدة 5 سنوات في ورقة عمل ملخص الملف الاستثماري:
- نضغط على الخلية G4 في ورقة عمل ملخص الملف الاستثماري
- نضغط على علامة تبويب "الصيغ" في شريط إكسل
- نضغط على زر البحث والمراجع في مجموعة أوامر مكتبة الدوال
- نحدد دالة HLOOKUP من قائمة الدوال. سيؤدي هذا إلى فتح نافذة وسائط الدالة لدالة HLOOKUP
- نضغط على زر طي النافذة الموجود بجوار وسيطة Lookup_value في نافذة وسيطات الدالة
- نضغط على الخلية H4 ثم على مفتاح الإدخال Enter بلوحة المفاتيح. سيكون الوصف الموجود في الخلية H4 هو القيمة المطلوبة التي ستبحث عنها في الصف الأول من النطاق المحدد لوسيطة جدول البيانات Table_array
- نضغط على زر طي النافذة الموجود بجوار وسيطة جدول البيانات Table_array في نافذة وسيطات الدالة
- نضغط على علامة تبويب ورقة عمل المقارنة المعيارية
- نحدد النطاق B2:E6 في ورقة عمل المقارنة المعيارية، ثم نضغط على مفتاح الإدخال Enter بلوحة المفاتيح. ستبحث الدالة في الصف 2 من هذا النطاق عن القيمة المطلوبة
- نضغط على مربع الإدخال الخاص بالوسيطة جدول البيانات Table_array ونضع مرجعًا مطلقًا في النطاق B2:E6، وذلك عن طريق كتابة علامة الدولار $ أمام حرف العمود ورقم الصف لكل موقع خلية في النطاق
- نضغط على المفتاح Tab في لوحة المفاتيح للتقدم إلى وسيطة رقم أو ترتيب الصف Row_index_num ونكتب الرقم 4. بمجرد أن تعثر الدالة على القيمة المطلوبة في الصف 2 من النطاق B2:E6، فستعرض القيمة الموجودة في الصف 5 من نفس العمود. من المهم هنا تذكر أن الصف 2 يُحسب كصف 1 لرقم ترتيب الصف في هذا المثال
- نضغط على المفتاح Tab في لوحة المفاتيح للتقدم إلى وسيطة القيمة المنطقية Range_lookup في نافذة وسيطات الدالة وكتابة الكلمة False. سيؤدي هذا إلى توجيه الدالة للبحث عن التطابقات التامة فقط للقيمة المطلوبة
- نضغط على زر موافق في أسفل نافذة وسيطات الدالة
- ننسخ دالة HLOOKUP في الخلية G4 ونلصقها في النطاق G5:G7 باستخدام أمر لصق الصيغ
يوضح الشكل نافذة وسيطات الدالة المكتملة لدالة HLOOKUP، ويشير رقم ترتيب الصف 4 إلى أن الدالة ستعرض محتويات موقع الخلية في الصف الرابع من جدول النطاق.
نافذة وسائط الدالة المكتملة لدالة HLOOKUP
يوضح الشكل التالي ناتط دالة HLOOKUP. والذي يمكن من خلاله ملاحظة أن ناتج الدالة في الخلية G4 هو 6.0٪، وهذا لأن القيمة المطلوبة قد حُددت من خلال البيانات الموجودة في الخلية H4، وهو مؤشر باركليز Barclays index. بالنظر إلى شكل ورقة عمل المقارنة المعيارية، إذا عددنا الصف الأول من جدول النطاق على أنه صف 1، فإن القيمة 6.03٪ ستكون في الصف الرابع في عمود باركليز. ونظرًا لأن القيم الموجودة في العمود G في ورقة عمل ملخص الملف الاستثماري على عُينت على منزلة عشرية واحدة، فستُعرض القيمة على أنها 6.0٪.
ورقة عمل ملخص الملف الاستثماري المكتملة
التأكد من صحة العمل
أخطاء #N/A و #REF! مع الدوال البحثية:
إذا تلقينا رمز الخطأ #N/A عند استخدام دالة VLOOKUP أو HLOOKUP، فهذا يشير إلى أن إكسل لم يتمكن من العثور على القيمة المطلوبة في جدول النطاق، وهنا من المهم التحقق من وجود القيمة المطلوبة في العمود الأول لـ VLOOKUP أو الصف الأول لـ HLOOKUP في النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array.
قد نرى أيضًا رمز الخطأ هذا إذا نسخنا الدالة وألصقناها ونسينا وضع مرجعًا مطلقًا على النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array. يشير رمز الخطأ #REF! إلى أن رقم ترتيب العمود أو رقم ترتيب الصف يتجاوز عدد الأعمدة أو الصفوف في النطاق المستخدم لتعريف وسيطة جدول البيانات Table_array.
المراجعة على المهارة المكتسبة: دالة HLOOKUP
من أجل إضافة دالة HLOOKUP خلال المراجعة على المهارة المكتسبة، لا بد من اتباع الخطوات التالية:
- كتابة علامة يساوي =
- كتابة اسم الدالة HLOOKUP متبوعًا بقوس مفتوح (
- تحديد وسيطة Lookup_value بموقع الخلية أو الرقم أو النص الذي ستبحث عنه في مصنف أو ورقة عمل أخرى. ويجب وضع النص بين علامات اقتباس
- كتابة فاصلة
- تحديد الوسيطة جدول البيانات Table_array بنطاق من الخلايا يحتوي على القيمة المطلوبة في الصف الأول مع البيانات التي ستُعرض أو تُستخدم بواسطة الدالة
- كتابة فاصلة
- تعريف وسيطة رقم أو ترتيب الصف Row_index_num برقم يعين الصفوف التي ستُعرض بواسطة الدالة من أعلى جدول النطاق، مع حساب الصف الأول من جدول النطاق على أنه 1
- كتابة فاصلة
- تحديد وسيطة القيمة المنطقية Range_lookup إما بالكلمة False أو الكلمة True. ستبحث الكلمة False عن التطابقات التامة للقيمة المطلوبة؛ وستبحث الكلمة True عن تطابق تقريبي أو أقرب تطابق أقل من القيمة المطلوبة. سيُعرف إكسل هذه الوسيطة على أنها صحيحة أي True إذا حُذفت
- كتابة قوس إغلاق )
- الضغط على مفتاح الإدخال Enter بلوحة المفاتيح
خاتمة
بهذا نكون قد تعرفنا على الدوال البحثية بملفات إكسل وكيفية استخدامها بطريقة صحيحة، انطلاقًا من دالتي VLOOKUP و HLOOKUP
ترجمة وبتصرف للمقال Lookup Functions من "calango website".
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.