أقسام الوصول السريع (مربع البحث)

دوال البحث المتقدمة في إكسل

 دوال البحث المتقدمة في Excel 

تشمل INDEX وMATCH وDGET.

دوال البحث المتقدمة في الاكسل
دوال البحث في الاكسل

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

تخيل أنك تعمل على مشروع ضخم، وتحتاج إلى العثور على بيانات معينة وسط بحر من الأرقام. مع دالة INDEX، يمكنك استرجاع القيم بسهولة، كأنك تلمس نقطة مضيئة في ظلام. بينما تأتي MATCH لتساعدك على تحديد موقع تلك القيم بدقة، مما يجعلك تشعر وكأنك تمتلك خريطة كنز في عالم معقد. أما DGET، فتعتبر بمثابة الحارس الذي يضمن لك الوصول إلى معلومات محددة ضمن قاعدة بيانات كبيرة، مما يوفر لك الوقت والجهد ويساعدك على اتخاذ قرارات مستنيرة. إنها تدل على قوة الكفاءة، حيث تجعل من الممكن استخراج المعلومات الهامة بسرعة ودقة. مع كل استخدام لهذه الدوال، نكتشف أن التلاعب بالبيانات ليس مجرد عملية حسابية، بل هو فن يحتاج إلى إبداع وتفكير استراتيجي. إنها تدعونا لاستكشاف إمكانيات جديدة، وتمكننا من تحويل الأرقام إلى رؤى قيمة. لنتوجه معًا نحو عمق هذه الدوال المتقدمة، ونحتفل بكل لحظة نحقق فيها إنجازًا من خلال فهمنا الأفضل لكيفية البحث واستخراج البيانات. فكل دالة تمثل خطوة نحو تحسين مهاراتنا، وتفتح لنا آفاقًا جديدة في عالم Excel الواسع. في هذا الدرس سنقدم لكم مقدمة حول دوال البحث المتقدمة في Excel. سنشرح ما هي هذه الدوال، ومزايا استخدامها، وكيفية استخدامها لتحسين مهاراتكم في تحليل البيانات.

ما هي دوال البحث المتقدمة؟

دوال البحث المتقدمة في Excel هي مجموعة من الدوال التي تسمح لك بالبحث عن البيانات واستخراجها من مصفوفات وقواعد البيانات. تختلف هذه الدوال عن دوال البحث الأساسية مثل VLOOKUP وHLOOKUP في أنها أكثر مرونة وقوة.

مزايا استخدام دوال البحث المتقدمة:

استخدام دوال البحث المتقدمة في Excel وغيرها من قواعد البيانات يوفر العديد من المزايا، بما في ذلك:

مرونة: 

يمكن استخدام دوال البحث المتقدمة للبحث عن البيانات واستخراجها من أي مكان في مصفوفة أو قاعدة بيانات. يمكن تحديد الشروط والمعايير المطلوبة للبحث والحصول على النتائج المطلوبة بدقة.

قوة:

تمكنك دوال البحث المتقدمة من البحث عن قيم متعددة واستخدام معايير متعددة لتحديد البيانات المطلوبة. تستطيع تحديد مجموعة متنوعة من المعايير مثل المساواة، أكبر من، أقل من، تكرار القيم، والكثير من الشروط الأخرى لتحقيق نتائج دقيقة وشاملة.

سهولة:

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

اقرأ أيضاً: أفضل 100 اختصار في الاكسل.

في هذا الدرس سنغطي ثلاث من أهم دوال البحث المتقدمة:

  • دالة INDEX: تُستخدم لاستخراج قيمة من مصفوفة بناءً على رقم الصف والعمود.
  • دالة MATCH: تُستخدم للبحث عن قيمة معينة في مصفوفة وإرجاع موقعها.
  • دالة DGET: تُستخدم لاستخراج قيمة من قاعدة بيانات بناءً على معايير محددة.
ما هي دالة INDEX
INDEX

هذه الدوال توفر مرونة وإمكانيات عالية للبحث واسترجاع البيانات في جداول Excel.

♦ الدالة INDEX:

تستخدم دالة INDEX لاسترجاع قيمة محددة من نطاق معين في جدول Excel. تأخذ هذه الدالة متغيرات المصفوفة وتعطيك قيمة الخلية المحددة في هذا المصفوفة. يمكن استخدام الدالة INDEX للاسترجاع قيمة واحدة أو مجموعة من القيم في الجدول.

دالة INDEX في Excel تستخدم لاسترجاع قيمة محددة من نطاق معين في جدول. تستخدم الدالة INDEX للوصول إلى القيم في الجدول استنادًا إلى الصفوف والأعمدة المحددة.

صيغة الدالة INDEX هي كالتالي:

INDEX(array, row_num, [column_num])

  • array: هو النطاق الذي ترغب في البحث فيه واسترجاع القيم منه.
  • row_num: رقم الصف الذي ترغب في استرجاع قيمته.
  • column_num:

 اختياري، رقم العمود الذي ترغب في استرجاع قيمته. إذا تم تجاوز هذا الوسيط، فسيتم استرجاع القيمة من الصف المحدد بدون الاعتبار بالعمود.

يمكن استخدام الدالة INDEX لاسترجاع قيمة واحدة أو لإنشاء مصفوفة جديدة تحتوي على مجموعة من القيم من الجدول. يمكن استخدام معلمات الصف والعمود لتحديد الخلية المحددة بدقة.

مثال:

لنفترض أن لديك جدول بيانات بعنوان "بيانات المبيعات" وترغب في استرجاع قيمة المبيعات في الصف 3 والعمود 2. يمكنك استخدام الدالة INDEX بهذا الشكل:

INDEX(بيانات المبيعات, 3, 2)

ستسترجع هذه الدالة قيمة الخلية في الصف 3 والعمود 2 في جدول بيانات المبيعات. باستخدام دالة INDEX، يمكنك الوصول بدقة إلى القيم في الجداول والمصفوفات في Excel.

♦ الدالة MATCH:

الدالة MATCH

MATCH
MATCH

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

MATCH(Lookup-value, Lookup-array, [match-type])

  • الوسيط الأول Lookup-value:- ويمثل قيمة البحث المراد معرفة رقم الصف أو العمود المطابق لها.
  • الوسيط الثاني Lookup-array:- ويمثل نطاق الخلايا المطلوب بحث الدالة بداخلها.
  • الوسيط الثالث match-type:- وهو نوع المطابقة ويحدد أسلوب التطابق الذي تقوم به الدالة، ويأخذ القيم الآتية:-

القيمة (1) :- للحصول على نتائج قيمتها أكبر من قيمة دالة البحث.
القيمة (0) :- للحصول على نتائج قيمتها مطابقة تماما لقيمة دالة البحث.
القيمة (1-) :- للحصول على نتائج قيمتها أصغر من قيمة دالة البحث.

مثال على الدالة MATCH:-

ويمكننا تطبيق هذه الدالة على المثال السابق لمعرفة رقم الصف الموجود فيه اسم الطالب أحمد:-

(B2:B14)”أحمد”) MATCH=

سوف تبحث الدالة في نطاق الخلايا B2:B14 وترجع لنا رقم الصف الذي تتطابق فيه قيمة دالة البحث مع أول خلية بجدول الإكسل ليظهر رقم الصف 10. ولمعرفة رقم العمود الذي يحتوي على نتيجة الدراسات:-

(B2:B14)”الدراسات”) MATCH=

سوف تبحث الدالة في نطاق الخلايا B2:B14 وترجع لنا رقم العمود الذي تتطابق فيه قيمة دالة البحث مع أول خلية بجدول الإكسل ليظهر رقم العمود 8.

تستخدم دالة MATCH للعثور على الموقع النسبي لقيمة محددة في نطاق معين. تأخذ هذه الدالة القيمة التي تبحث عنها والنطاق الذي تبحث فيه وتعيد الموقع النسبي لهذه القيمة في النطاق. 

يمكن استخدام الدالة MATCH كوسيلة لتوجيه الدالة INDEX إلى الموقع الصحيح للاسترجاع القيمة المطلوبة.

دالة MATCH في Excel تستخدم للعثور على الموقع النسبي لقيمة محددة في نطاق معين. تستخدم هذه الدالة لتحديد الموقع الذي يتواجد فيه القيمة المحددة في النطاق المعطى.

صيغة الدالة MATCH هي كالتالي:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value: 

القيمة التي ترغب في البحث عنها في النطاق.

lookup_array: 

النطاق الذي ترغب في البحث فيه عن القيمة.

match_type: 

اختياري، يحدد نوع المطابقة المطلوبة. 

قيمة 0 تعني المطابقة الدقيقة، وقيمة 1 تعني البحث عن أقرب قيمة أكبر، وقيمة -1 تعني البحث عن أقرب قيمة أصغر.

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

مثال:

لنفترض أن لديك قائمة بأسماء الموظفين في النطاق A1:A10 وترغب في معرفة الموقع النسبي لاسم "أحمد".

استخدام الدالة MATCH بهذا الشكل:

MATCH("أحمد", A1:A10, 0)

ستعيد هذه الدالة الموقع النسبي للاسم "أحمد" في النطاق A1:A10.

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

♦ الدالة DGET:

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

تلك الدوال المتقدمة توفر مرونة وقوة في البحث والاسترجاع في Excel وتمثل أدوات قوية للتلاعب بالبيانات بشكل متقدم.

الدالة INDEX في Excel تستخدم لاسترجاع القيمة المطلوبة من نطاق محدد في جدول البيانات. تقوم الدالة INDEX بإرجاع القيمة الموجودة في التقاطع بين رقم الصف ورقم العمود في النطاق المحدد.

تعد الدالة DGET من دوال البحث في إكسل وتستخدم في البحث عن قيمة واحدة في مجموعة البيانات الموجودة داخل العمود الواحد وفقاً للشروط والمعايير التي يتم تحديدها بواسطة المستخدم.

هو ما يميز الدالة DGET عن الدوال الأخرى إذ يمكنك من البحث بدلالة أكثر من معيار(البحث عن شخص وعمره وجنسيته) والبحث باستخدام معايير أحرف البدل(؟ و*)، كما يمكنك استخدام معايير المعاملات المنطقية(>، <، أو =).

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

ويتم كتابة الدالة كالتالي:-

DGET (database, field, criteria)

  • الحقل الأول: database قاعدة البيانات:- يمثل نطاق الخلايا التي تتكون منها القائمة أو قاعدة البيانات التي تبحث فيها.
  • الحقل الثاني: field ويشير إلى العمود الذي تستخدمه الدالة المكتوبة ويتم فيه تسمية العمود بين علامتي إقتباس.
  • الحقل الثالث criteria المعيار:- ويمثل نطاق الخلايا الذي يحتوي على الشروط التي يتم إدخالها من قبل المستخدم.

هذا هو الشكل العام لكتابة الدالة INDEX في Excel:

INDEX(array, row-num, [column-num])

- array: هو النطاق الذي يحتوي على البيانات التي ترغب في استرجاع قيمتها.

- row-num: هو رقم الصف الذي ترغب في استرجاع قيمته.

- [column-num]: اختياري، وهو رقم العمود الذي ترغب في استرجاع قيمته. إذا تم تجاوز هذا الوسيط، فسيتم افتراض استرجاع القيمة في نفس العمود الذي تم تحديده في النطاق array.

في نتيجة الطلاب إذا كانت العمود B يمثل اسم الطالب والعمود H يمثل نتيجة الدراسات وكنت تبحث عن مجموع الطالب أحمد في مادة الدراسات

الدالة INDEX(B2:H11, 10, 8) ستقوم بالبحث عن تقاطع الصف رقم 10 والعمود رقم 8 في نطاق الجدول B2:H11، وسترجع القيمة الموجودة في هذا التقاطع. 

INDEX(B2:H11, MATCH("أحمد", B2:B11, 0), MATCH("نتيجة الدراسات", B1:H1, 0))

في هذا المثال، تم استخدام دالة MATCH للبحث عن صف البيانات الذي يحتوي على اسم الطالب "أحمد" في العمود B، وأيضًا استخدمت دالة MATCH للبحث عن عمود "نتيجة الدراسات" في الصف الأول. ثم يتم استخدام الدالة INDEX لاسترجاع القيمة في التقاطع بين صف البيانات وعمود النتائج. في هذا السياق، إذا كان العمود B يحتوي على أسماء الطلاب والعمود H يحتوي على نتائج دراساتهم، وكنت ترغب في استرجاع نتيجة الطالب أحمد، يمكنك استخدام الدالة INDEX بهذا الشكل:

إذن يتم كتابة الدالة INDEX (B2:H11, 10,8)

يمثل B2:H11 نطاق الجدول الذي تبحث فيه، الرقم 10 هو صف البيانات، والرقم 8 عمود البيانات، فإن الدالة سوف تبحث عن تقاطع الصف رقم 10 والذي يمثل اسم الطالب أحمد مع العمود رقم 8 والذي يمثل نتيجة الدراسات وتظهر قيمة التقاطع التي توضح نتيجة أحمد في مادة الدراسات

ملاحظة مهمة:-

 في حالة تواجد أكثر من نتيجة مطابقة للدالة سوف يتم إرجاع الدالة الخطأ NUM#.
تعليقات