4 وظائف بحث Excel للبحث في جداول البيانات بكفاءة

4 وظائف بحث Excel للبحث في جداول البيانات بكفاءة

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





بمجرد أن تعرف كيفية البحث في Excel باستخدام البحث ، فلن يهم حجم جداول البيانات لديك ، وستتمكن دائمًا من العثور على شيء ما في Excel!



1. وظيفة VLOOKUP

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





فيما يلي بناء جملة الوظيفة:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [ابحث عن القيمة] هي جزء من المعلومات التي لديك بالفعل. على سبيل المثال ، إذا كنت بحاجة إلى معرفة الولاية التي تقع فيها المدينة ، فسيكون هذا هو اسم المدينة.
  • [table_array] يتيح لك تحديد الخلايا التي ستبحث فيها الوظيفة عن قيم البحث والإرجاع. عند تحديد النطاق الخاص بك ، تأكد من أن العمود الأول المضمن في الصفيف الخاص بك هو العمود الذي سيتضمن قيمة البحث الخاصة بك!
  • [col_index_num] هو رقم العمود الذي يحتوي على قيمة الإرجاع.
  • [مجموعة البحث] هي وسيطة اختيارية ، وتأخذ 1 أو 0. إذا أدخلت 1 أو حذفت هذه الوسيطة ، فستبحث الدالة عن القيمة التي أدخلتها أو الرقم التالي الأدنى. لذلك في الصورة أدناه ، سيعرض VLOOKUP الذي يبحث عن درجة SAT 652 ، حيث إنه أقرب رقم في القائمة أقل من 652 ، ويتم تعيين [range_lookup] افتراضيًا على 1.

دعنا نلقي نظرة على كيفية استخدام هذا. يحتوي جدول البيانات هذا على أرقام المعرفات ، والأسماء الأولى والأخيرة ، والمدينة ، والولاية ، ودرجات SAT. لنفترض أنك تريد العثور على درجة SAT لشخص يحمل الاسم الأخير 'Winters'. يجعل VLOOKUP الأمر سهلاً. إليك الصيغة التي ستستخدمها:



=VLOOKUP('Winters', C2:F101, 4, 0)

نظرًا لأن درجات SAT هي العمود الرابع من عمود الاسم الأخير ، فإن 4 هي وسيطة فهرس العمود. لاحظ أنه عندما تبحث عن نص ، يعد تعيين [range_lookup] على 0 فكرة جيدة. بدونها يمكنك الحصول على نتائج سيئة.

ها هي النتيجة:



عادت 651 ، درجة SAT الخاصة بالطالب المسمى Kennedy Winters ، الموجود في الصف 92 (معروض في الشكل أعلاه). كان من الممكن أن يستغرق التمرير خلال البحث عن الاسم وقتًا أطول بكثير مما استغرقه الأمر لكتابة بناء الجملة بسرعة!

ملاحظات حول VLOOKUP

من الجيد تذكر بعض الأشياء عند استخدام VLOOKUP. تأكد من أن العمود الأول في النطاق الخاص بك هو العمود الذي يتضمن قيمة البحث الخاصة بك. إذا لم يكن موجودًا في العمود الأول ، فستُرجع الدالة نتائج غير صحيحة. إذا كانت أعمدتك منظمة جيدًا ، فلن تكون هذه مشكلة.



أيضًا ، ضع في اعتبارك أن VLOOKUP سيعيد قيمة واحدة فقط. إذا كنت قد استخدمت 'جورجيا' كقيمة بحث ، فستكون قد أعادت نتيجة الطالب الأول من جورجيا ، ولم تعط أي إشارة إلى وجود طالبين في الواقع من جورجيا.

2. وظيفة HLOOKUP

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

فيما يلي بناء جملة الوظيفة:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [ابحث عن القيمة] هي القيمة التي تعرفها وتريد إيجاد قيمة مقابلة لها.
  • [table_array] هي الخلايا التي تريد البحث فيها.
  • [row_index_num] يحدد الصف الذي ستأتي منه القيمة المرجعة.
  • [مجموعة البحث] هو نفسه الموجود في VLOOKUP ، اتركه فارغًا للحصول على أقرب قيمة عندما يكون ذلك ممكنًا ، أو أدخل 0 للبحث فقط عن التطابقات التامة.

يحتوي جدول البيانات هذا على صف لكل ولاية ، جنبًا إلى جنب مع درجة SAT في السنوات 2000-2014. يمكنك استخدام HLOOKUP للعثور على متوسط ​​الدرجات في ولاية مينيسوتا في عام 2013. وإليك كيفية القيام بذلك:

=HLOOKUP(2013, A1:P51, 24)

كما ترى في الصورة أدناه ، يتم إرجاع النتيجة:

بلغ متوسط ​​نقاط مينيسوتا 1014 في عام 2013. لاحظ أن 2013 ليس ضمن علامات الاقتباس لأنه رقم وليس سلسلة. أيضًا ، يأتي الـ 24 من مينيسوتا في الصف الرابع والعشرين.

هنا كيفية حساب المتوسط ​​المرجح في Excel .

ملاحظات حول HLOOKUP

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

3-4. وظائف الفهرس والمطابقة

INDEX و MATCH هما وظيفتان مختلفتان ، ولكن عند استخدامهما معًا ، يمكنهما جعل البحث في جدول بيانات كبير أسرع كثيرًا. كلتا الوظيفتين لهما عيوب ، ولكن من خلال الجمع بينهما سنبني على نقاط القوة في كليهما.

أولاً ، على الرغم من ذلك ، فإن تركيب كلتا الوظيفتين:

=INDEX([array], [row_number], [column_number])
  • [مجموعة مصفوفة] هي المصفوفة التي ستبحث فيها.
  • [رقم الصف] و [العمود_number] يمكن استخدامها لتضييق نطاق البحث (سنلقي نظرة على ذلك بعد قليل).
=MATCH([lookup_value], [lookup_array], [match_type])
  • [ابحث عن القيمة] هي عبارة بحث يمكن أن تكون سلسلة أو رقمًا.
  • [lookup_array] هي المصفوفة التي يبحث فيها Microsoft Excel عن مصطلح البحث.
  • [نوع مباراة] هي وسيطة اختيارية يمكن أن تكون 1 أو 0 أو -1. 1 سيعيد أكبر قيمة أصغر من أو تساوي مصطلح البحث الخاص بك. 0 سيعيد فقط المصطلح بالضبط ، و -1 سيعيد أصغر قيمة أكبر من أو تساوي مصطلح البحث الخاص بك.

قد لا يكون من الواضح كيف سنستخدم هاتين الوظيفتين معًا ، لذا سأوضحها هنا. تأخذ MATCH مصطلح بحث وتقوم بإرجاع مرجع خلية. في الصورة أدناه ، يمكنك أن ترى أنه في البحث عن القيمة 646 في العمود F ، تُرجع MATCH 4.

من ناحية أخرى ، يقوم مؤشر INDEX بالعكس: يأخذ مرجع خلية ويعيد القيمة فيه. يمكنك أن ترى هنا أنه عندما يُطلب منك إرجاع الخلية السادسة من عمود المدينة ، تُرجع الدالة INDEX 'Anchorage' ، وهي القيمة من الصف 6.

ما سنفعله هو الجمع بين الاثنين بحيث تُرجع MATCH مرجع خلية ويستخدم INDEX هذا المرجع للبحث عن القيمة في خلية. لنفترض أنك تتذكر أنه كان هناك طالب اسمه الأخير ووترز ، وتريد أن ترى نتيجة هذا الطالب. ها هي الصيغة التي سنستخدمها:

لن يستيقظ الكمبيوتر من نظام التشغيل windows 10 في وضع السكون
=INDEX(F:F, MATCH('Waters', C:C, 0))

ستلاحظ أنه تم تعيين نوع المطابقة على 0 هنا. عندما تبحث عن سلسلة ، هذا ما تريد استخدامه. إليك ما نحصل عليه عند تشغيل هذه الوظيفة:

كما ترى من الشكل الداخلي ، سجل Owen Waters 1720 ، وهو الرقم الذي يظهر عند تشغيل الوظيفة. قد لا يبدو هذا مفيدًا للغاية عندما يمكنك فقط إلقاء نظرة على بعض الأعمدة ، ولكن تخيل مقدار الوقت الذي ستوفره إذا كان عليك القيام بذلك 50 مرة في جدول قاعدة بيانات كبير التي احتوت على عدة مئات من الأعمدة!

دع عمليات البحث في Excel تبدأ

يحتوي Microsoft Excel على الكثير من ملفات وظائف قوية للغاية لمعالجة البيانات ، والأربعة المذكورة أعلاه تخدش السطح فقط. تعلم كيفية استخدامها سيجعل حياتك أسهل بكثير.

إذا كنت تريد حقًا إتقان Microsoft Excel ، فيمكنك حقًا الاستفادة من إبقاء ورقة الغش الأساسية في Excel في متناول اليد!

حقوق الصورة: Cico / صراع الأسهم

يشارك يشارك سقسقة بريد الالكتروني مقارنة بين Canon و Nikon: ما هي ماركة الكاميرا الأفضل؟

Canon و Nikon هما أكبر اسمين في صناعة الكاميرات. ولكن ما هي العلامة التجارية التي تقدم أفضل تشكيلة من الكاميرات والعدسات؟

اقرأ التالي مواضيع ذات صلة
  • إنتاجية
  • جدول
  • مايكروسوفت اكسل
  • حيل البحث
نبذة عن الكاتب إيان باكلي(216 مقالة منشورة)

إيان باكلي صحفي مستقل وموسيقي وفنان ومنتج فيديو يعيش في برلين بألمانيا. عندما لا يكتب أو على خشبة المسرح ، فإنه يعبث بإلكترونيات أو رموز DIY على أمل أن يصبح عالمًا مجنونًا.

المزيد من Ian Buckley

اشترك في نشرتنا الإخبارية

انضم إلى النشرة الإخبارية لدينا للحصول على نصائح تقنية ومراجعات وكتب إلكترونية مجانية وصفقات حصرية!

انقر هنا للاشتراك