يمكن أن تساعدك وظيفة HLOOKUP الخاصة بـ Excel ، وهي اختصار البحث الأفقي ، في العثور على معلومات محددة في جداول البيانات الكبيرة مثل قائمة جرد للأجزاء أو قائمة جهات اتصال عضوية كبيرة.
HLOOKUP يعمل كثيرا نفس وظيفة VLOOKUP في Excel. والفرق الوحيد هو أن VLOOKUP يبحث عن البيانات في الأعمدة بينما يبحث HLOOKUP عن البيانات في الصفوف.
اتباع الخطوات الواردة في المواضيع التعليمية أدناه يمكنك من خلالك استخدام الدالة HLOOKUP للعثور على معلومات محددة في قاعدة بيانات Excel.
تغطي الخطوة الأخيرة من البرنامج التعليمي رسائل الخطأ التي تحدث عادةً مع الدالة HLOOKUP.
مواضيع البرنامج التعليمي
- دخول بيانات البرنامج التعليمي
- بدء تشغيل وظيفة HLOOKUP
- قيمة البحث
- جدول المصفوفة
- رقم فهرس الصف
- بحث المدى
- باستخدام HLOOKUP لاسترداد البيانات
- رسائل الخطأ الشائعة لبرنامج Excel HLOOKUP
01 من 09
دخول بيانات البرنامج التعليمي
عند إدخال البيانات في ورقة عمل Excel ، هناك بعض القواعد العامة الواجب اتباعها:
- كلما أمكن ، لا تترك صفوفًا أو أعمدة فارغة عند إدخال بياناتك.
- قد يؤدي ترك الصفوف والأعمدة الفارغة في جداول البيانات إلى صعوبة استخدام عدد من وظائف Excel - بما في ذلك HLOOKUP.
- أدخل بياناتك في صفوف.
- عند وضع ورقة العمل الخاصة بك ، قم بسرد الأسماء التي تصف البيانات في الصف الأول من الجدول ، وإلى أسفلها البيانات نفسها.
- إذا كان هناك أكثر من سلسلة بيانات واحدة ، فقم بسردها بعد الآخر في صفوف تحمل عنوان كل سلسلة بيانات في الخلية الأولى على اليسار.
لهذا البرنامج التعليمي
- أدخل البيانات كما يظهر في الصورة أعلاه في الخلايا من D4 إلى I5.
- يحتوي الصف الأول من البيانات (الصف 4) على أسماء الأجزاء. الصف الثاني (الصف 5) سعر كل جزء.
02 من 09
بدء تشغيل وظيفة HLOOKUP
قبل بدء تشغيل الدالة HLOOKUP ، عادةً ما يكون من الأفضل إضافة عناوين إلى ورقة العمل لإظهار البيانات التي يتم استردادها بواسطة HLOOKUP. في هذا البرنامج التعليمي ، أدخل العناوين التالية في الخلايا المشار إليها. سيتم وضع الدالة HLOOKUP والبيانات التي تستردها من قاعدة البيانات في الخلايا على يمين هذه العناوين.
- D1 - جزء الاسم
E1 - السعر
على الرغم من أنه من الممكن فقط كتابة الدالة HLOOKUP في خلية في ورقة عمل ، إلا أن العديد من الأشخاص يجدون أنه من الأسهل استخدام مربع الحوار الخاص بالوظيفة.
لهذا البرنامج التعليمي
- انقر فوق الخلية E2 لجعلها الخلية النشطة . هذا هو المكان الذي سنبدأ فيه وظيفة HLOOKUP.
- انقر فوق علامة التبويب صيغ .
- اختر Lookup & Reference من الشريط لفتح القائمة المنسدلة الدالة.
- انقر فوق HLOOKUP في القائمة لإظهار مربع الحوار الخاص بالوظيفة.
ستشكل البيانات التي ندخلها في الصفوف الفارغة الأربعة في مربع الحوار وسيطات الدالة HLOOKUP. هذه الحجج تخبر وظيفة ما نحن بعد المعلومات وأين ينبغي البحث للعثور عليه.
03 من 09
قيمة البحث
الوسيطة الأولى هي Lookup_value . يخبر HLOOKUP عن العنصر الموجود في قاعدة البيانات التي نبحث عنها. يقع Lookup_value في الصف الأول من النطاق المحدد.
تكون دائماً المعلومات التي HLOOKUP ترجع من نفس عمود قاعدة البيانات مثل Lookup_value.
يمكن أن يكون Lookup_value عبارة عن سلسلة نصية أو قيمة منطقية (TRUE أو FALSE فقط) أو رقم أو مرجع خلية لقيمة.
لهذا البرنامج التعليمي
- انقر فوق السطر Lookup_value في مربع الحوار
- انقر فوق الخلية D2 لإضافة مرجع الخلية هذا إلى سطر Lookup_value . هذه هي الخلية التي سنكتب فيها اسم الجزء الذي نبحث عن المعلومات عنه.
04 من 09
جدول المصفوفة
الوسيطة Table_array هي نطاق البيانات التي تبحث عنها الدالة HLOOKUP للعثور على معلوماتك. لاحظ أن هذا النطاق لا يحتاج إلى تضمين كافة الصفوف أو حتى الصف الأول من قاعدة البيانات .
يجب أن يحتوي Table_array على صفين بيانات على الأقل رغم ذلك ، مع الصف الأول الذي يحتوي على Lookup_value (راجع الخطوة السابقة).
إذا قمت بإدخال مراجع الخلية لهذه الوسيطة ، فمن الأفضل استخدام مراجع الخلية المطلقة. يتم الإشارة إلى مراجع الخلية المطلقة في Excel بعلامة الدولار ( $ ). مثال على ذلك هو $ 4 دولار.
إذا لم تستخدم مراجع مطلقة ونسخت الدالة HLOOKUP إلى خلايا أخرى ، فهناك احتمال أن تحصل على رسائل خطأ في الخلايا التي يتم نسخ الدالة منها.
لهذا البرنامج التعليمي
- انقر فوق سطر Table_array في مربع الحوار.
- قم بتمييز الخلايا من E4 إلى I5 في جدول البيانات لإضافة هذا النطاق إلى سطر Table_array . هذا هو نطاق البيانات التي ستبحثها HLOOKUP.
- اضغط المفتاح F4 على لوحة المفاتيح لجعل النطاق مطلق ($ E $ 4: $ I $ 5).
05 من 09
رقم فهرس الصف
تشير وسيطة رقم فهرس الصف (Row_index_num) إلى أي صف من Table_array يحتوي على البيانات التي تتبعها.
فمثلا:
- إذا قمت بإدخال 1 في رقم فهرس الصف ، تُرجع HLOOKUP قيمة من العمود الأول في table_array؛
- إذا كان رقم فهرس الصف هو 2 ، فإنها تُرجع قيمة من الصف الثاني في table_array.
لهذا البرنامج التعليمي
- انقر فوق السطر Row_index_num في مربع الحوار
- اكتب a 2 في هذا السطر للإشارة إلى أننا نريد HLOOKUP لإرجاع معلومات من الصف الثاني من صفيف الجدول.
06 من 09
بحث المدى
تعتبر وسيطة Range_lookup قيمة منطقية (TRUE أو FALSE فقط) تشير إلى ما إذا كنت تريد HLOOKUP للعثور على تطابق دقيق أو تقريبي لـ Lookup_value .
- إذا تم حذف TRUE أو إذا تم حذف هذه الوسيطة ، سيستخدم HLOOKUP مطابقة تقريبية إذا لم يتمكن من العثور على تطابق تام لـ Lookup_value. إذا لم يتم العثور على تطابق تام ، فسوف تُرجع HLOOKUP القيمة الأكبر التالية التي تكون أقل من Lookup_value.
- إذا كانت FALSE ، فلن يستخدم HLOOKUP سوى المطابقة التامة إلى Lookup_value. إذا كان هناك قيمتين أو أكثر في العمود الأول من Table_array تتطابق مع Lookup_value ، فسيتم استخدام أول قيمة تم العثور عليها. في حالة عدم العثور على تطابق تام ، يتم إرجاع خطأ # N / A.
لهذا البرنامج التعليمي
- انقر فوق سطر Range_lookup في مربع الحوار
- اكتب الكلمة False في هذا السطر للإشارة إلى أننا نريد من HLOOKUP إرجاع مطابقة تامة للبيانات التي نسعى إليها.
- انقر فوق موافق لإغلاق مربع الحوار.
- إذا كنت قد اتبعت جميع خطوات هذا البرنامج التعليمي ، يجب أن يكون لديك الآن وظيفة HLOOKUP كاملة في الخلية E2.
07 من 09
باستخدام HLOOKUP لاسترداد البيانات
بمجرد اكتمال وظيفة HLOOKUP ، يمكن استخدامها لاسترداد المعلومات من قاعدة البيانات .
للقيام بذلك ، اكتب اسم العنصر الذي ترغب في استرداده في الخلية Lookup_value واضغط على المفتاح ENTER على لوحة المفاتيح.
يستخدم HLOOKUP رقم الفهرس الصف لتحديد العنصر الذي يجب أن يظهر البيانات في الخلية E2.
لهذا البرنامج التعليمي
- انقر فوق الخلية E1 في جدول البيانات الخاص بك.
- اكتب Bolt في الخلية E1 ثم اضغط المفتاح ENTER على لوحة المفاتيح.
- يجب عرض سعر البرغي - $ 1.54 - في الخلية E2.
اختبار الدالة HLOOKUP أيضاً عن طريق كتابة أسماء أجزاء أخرى في الخلية E1 ومقارنة البيانات التي تم إرجاعها في الخلية E2 مع الأسعار المذكورة في الخلايا من E5 إلى I5.
08 من 09
Excel HLOOKUP رسائل الخطأ
ترتبط رسائل الخطأ التالية مع HLOOKUP.
خطأ # N / A:
- يتم عرض هذا الخطأ إذا لم يتم العثور على قيمة البحث في العمود الأول من صفيف الجدول.
- سيتم أيضًا عرضه إذا كان النطاق لوسيطة صفيف الجدول غير دقيق. إذا تضمنت هذه الوسيطة صفوف فارغة فوق صفيف الجدول.
#REF !:
- يتم عرض هذا الخطأ إذا كانت وسيطة رقم فهرس الصف أكبر من عدد الصفوف في صفيف الجدول. في الصورة أعلاه ، #REF! يحدث الخطأ بسبب تعيين رقم فهرس الصفوف إلى 3 بينما يوجد صفان فقط في Table_array.
هذا يكمل البرنامج التعليمي على إنشاء واستخدام الدالة HLOOKUP في Excel 2007.
09 من 09
مثال باستخدام دالة HLOOKUP في Excel 2007
أدخل البيانات التالية في الخلايا المشار إليها:
بيانات الخلية
- D3 - الجزء
- E3 - وإذ تضع
- F3 - بولت
- G3 - الترس
- H3 - ترس
- I3 - غسالة
- D4 - السعر
- E4 - 17.34 دولارًا
- F4 - 1.54 دولارًا
- G4 - 20.21 دولارًا
- H4 - 23.56 دولارًا
- I4 - 1.43 دولار
انقر فوق الخلية E1 - الموقع حيث سيتم عرض النتائج.
انقر فوق علامة التبويب صيغ.
اختر Lookup & Reference من الشريط لفتح القائمة المنسدلة الدالة.
انقر فوق HLOOKUP في القائمة لإظهار مربع الحوار الخاص بالوظيفة.
في مربع الحوار ، انقر فوق سطر البحث _value.
انقر فوق الخلية D1 في جدول البيانات. هذا هو المكان الذي سنكتب فيه اسم الجزء الذي نرغب في تسعيره.
في مربع الحوار ، انقر فوق سطر Table_array.
قم بتمييز الخلايا من E3 إلى I4 في جدول البيانات لإدخال النطاق في مربع الحوار. هذا هو نطاق البيانات التي نرغب في HLOOKUP البحث عنها.
في مربع الحوار ، انقر فوق سطر Row_index_num.
اكتب الرقم 2 للإشارة إلى أن البيانات التي نريد إرجاعها موجودة في الصف 2 من table_array.
في مربع الحوار ، انقر فوق سطر Range_lookup.
اكتب الكلمة False للإشارة إلى أننا نريد مطابقة تامة للبيانات المطلوبة.
انقر فوق موافق.
في الخلية D1 من جدول البيانات ، اكتب كلمة bolt.
يجب أن تظهر القيمة $ 1.54 في الخلية E1 التي تعرض سعر البرغي كما هو موضح في table_array.
إذا قمت بالنقر فوق الخلية E1 ، تظهر الدالة الكاملة = HLOOKUP (D1 ، E3: I4 ، 2 ، FALSE) في شريط الصيغة أعلى ورقة العمل.