إذا كنت قد عملت مع Excelربما واجهتَ الحاجة للبحث عن البيانات أو الرجوع إليها بكفاءة وسرعة. على الرغم من أن الكثيرين يقتصرون على استخدام الوظائف الأساسية، وظائف البحث والمرجع في برنامج إكسل، تُعدّ الصيغ أدوات فعّالة تُحسّن بشكل كبير طريقة عملك وتحليلك للمعلومات. إن الفهم الجيد لكيفية عمل هذه الصيغ يُمكّنك من توفير الوقت، وتجنّب الأخطاء، واتخاذ قرارات أكثر وعيًا.
في هذه المقالة ، سوف نستكشف في عمق كل وظائف البحث الرئيسية والمرجعية في هذا الدرس، سنتعرف على الدوال الموجودة في Excel، ونكتشف كيفية استخدامها، وميزاتها، ونصائح عملية لمساعدتك على تحقيق أقصى استفادة منها. بالإضافة إلى مراجعة الأمثلة والقواعد النحوية، سنوضح غرض كل دالة ونقدم لك نصائح حول كيفية دمجها في جداول بياناتك، سواء كنت مبتدئًا أو لديك خبرة سابقة وترغب في التحسين.
ما هي وظائف البحث والمرجع في Excel؟
في عالم وظائف Excel، مجموعة البحث والمرجع تتميز هذه الوظيفة بتمكيننا من تحديد موقع معلومات محددة ضمن نطاق أو جدول أو مجموعة مصفوفات، وإرجاع النتائج بناءً على التطابقات أو المواضع النسبية. صُممت هذه الوظائف لأتمتة استرجاع البيانات ذات الصلة، وتسهيل التحليلات المعقدة، وربط المعلومات بين الجداول، وإنشاء تقارير ديناميكية، وغير ذلك الكثير.
وظائف البحث والمرجع الرئيسية في Excel
يتضمن برنامج Excel مجموعة واسعة من الوظائف من هذا النوع، ولكل منها استخدام محدد. بعضها معروف على نطاق واسع، مثل VLOOKUP o هلوكوب، في حين أن آخرين مثل البحث o الفهرس اكتسبت هذه الميزات أهميةً متزايدةً مع أحدث الإصدارات. إليك وصفٌ لأهمّ الميزات:
- VLOOKUP:يبحث عن قيمة في العمود الأول من المصفوفة ويعيد قيمة من نفس الصف في عمود آخر تحدده.
- هلوكوب:يشبه VLOOKUP، لكنه يقوم بالبحث في الصف الأول من المصفوفة، ويعيد القيم من الصفوف الأخرى.
- بحث:يسمح لك بالبحث في صف أو عمود واحد والعثور على قيمة في نفس الموضع في صف أو عمود آخر.
- البحث:النسخة المحسنة من الإصدارات السابقة، والتي تسمح بالبحث الأفقي أو الرأسي، وخيارات المطابقة المتقدمة، وهي أكثر مرونة بكثير.
- الفهرس:ترجع قيمة خلية محددة في المصفوفة، مع الأخذ في الاعتبار موضع الصف والعمود.
- COINCIDIR:ترجع الموضع النسبي لقيمة ضمن نطاق أو مصفوفة.
- غير مباشر:ينشئ مرجعًا استنادًا إلى قيمة نصية، وهو أمر مفيد للمراجع الديناميكية.
- عوض:ترجع إزاحة مرجعية من خلية بداية، وهي مفيدة جدًا لمراجع المتغيرات.
- يختار:يسمح لك باختيار قيمة من قائمة الوسائط استنادًا إلى الفهرس الذي تحدده.
- المناطق:إرجاع عدد المناطق في مرجع.
- عمود y فيلا:إرجاع رقم العمود أو الصف لمرجع معين.
- قوائم الانتظار y الأعمدة:إرجاع العدد الإجمالي للصفوف أو الأعمدة في مرجع.
- رابط تشعبي:إنشاء وصول سريع إلى مستند أو موقع ويب آخر أو موقع داخل المصنف نفسه.
- نقل:تبديل الصفوف والأعمدة في المصفوفة.
- يونيكوس:إرجاع قائمة من القيم الفريدة من مصفوفة أو نطاق.
- فلتر:يستخرج البيانات من نطاق بناءً على المعايير التي تحددها.
كيف تعمل وظائف البحث الشائعة
دعونا نستعرض وظائف البحث الأكثر شيوعًا، ونشرح تركيبها، ونرى كيف تعمل في المواقف الشائعة. سيساعدك هذا على فهم الاختلافات وتطبيق كل منها بالشكل المناسب.
وظيفة VLOOKUP
الوظيفة VLOOKUP (أو VLOOKUP باللغة الإنجليزية) ربما تكون الأكثر استخدامًا عندما يتعلق الأمر بالبحث عن المعلومات داخل جدول عموديًا.
بناء الجملة: BUSCARV(valor_buscado, matriz_buscar_en, indicador_columnas, )
- ابحث عن القيمة:البيانات التي تريد العثور عليها.
- مصفوفة البحث في:نطاق الخلايا التي يتم إجراء البحث فيها، حيث يحتوي العمود الأول على القيم التي تم البحث عنها.
- أعمدة المؤشرات:رقم العمود الذي سيتم أخذ القيمة الناتجة منه، بدءًا من اليسار.
- أنيق:اختياري. صحيح (افتراضي) للمطابقة التقريبية أو خطأ للمطابقة الدقيقة.
على سبيل المثال، إذا كان لديك جدول منتج وتريد البحث عن سعر مرجع معين، VLOOKUP يمكنك إرجاع هذه القيمة تلقائيًا دون الحاجة إلى البحث عنها يدويًا.
دالة HLOOKUP
أخت السابقة هلوكوبيبحث في الصف العلوي من النطاق ويعيد البيانات من العمود المحدد.
بناء الجملة: BUSCARH(valor_buscado, matriz_buscar_en, indicador_filas, )
وهو مثالي للجداول التي يتم فيها ترتيب المعلومات الرئيسية بشكل أفقي.
وظيفة البحث
الوظيفة بحث يُفيد هذا عند البحث في صف أو عمود واحد وإرجاع قيمة في نفس الموضع من نطاق آخر. يُمكن استخدامه بطريقتين: كمتجه أو كمصفوفة.
- شكل المتجه:يسمح لك بالبحث في نطاق صف أو عمود واحد (متجه) والحصول على قيمة من متجه آخر في نفس الموضع.
- شكل المصفوفة:يبحث في الصف أو العمود الأول من المصفوفة ويعيد القيمة من الصف أو العمود الأخير المقابل.
بناء الجملة (المتجه): BUSCAR(valor_buscado; vector_de_comparación; )
بناء الجملة (المصفوفة): BUSCAR(valor_buscado; matriz)
في كلتا الحالتين، إذا لم يتم العثور على القيمة المطلوبة بدقة، تُرجع الدالة أقرب قيمة أصغر من أو تساوي القيمة المطلوبة. إذا كانت القيمة المطلوبة أصغر من أي قيمة في المصفوفة أو المتجه، تُرجع الدالة #N/A.
دالة XLOOKUP
واحدة من أحدث الإضافات، البحث تُحسّن أداة (XLOOKUP) عمليات البحث وتُبسّطها بشكل كبير. فهي تتيح لك البحث أفقيًا ورأسيًا، وتدعم التطابقات التقريبية والدقيقة، والبحث العكسي، وتُعيّن الإعدادات الافتراضية في حال عدم العثور على تطابق، وغيرها الكثير. لمعرفة المزيد عن مزاياها، تفضل بزيارة موقعنا.
تعتبر هذه الوظيفة أكثر مرونة وتتجنب العديد من القيود الموجودة في VLOOKUP وHLOOKUP، على سبيل المثال، لا تتطلب أن تكون قيمة البحث في العمود أو الصف الأول من المصفوفة.
وظائف INDEX وMATCH
مزيج من الفهرس y COINCIDIR يتيح إجراء عمليات بحث متقدمة تتجاوز بكثير ما يمكن لـ VLOOKUP القيام به. الفهرس يتيح لك استخراج قيمة من مجموعة بناءً على موضعها. COINCIDIR العثور على موضع قيمة ضمن نطاق، ويمكنك معًا البحث عن أي قيمة تريدها في أي صف أو عمود في جدول بغض النظر عن الترتيب.
بناء الجملة INDEX: ÍNDICE(matriz, núm_fila, )
بناء الجملة MATCH: COINCIDIR(valor_buscado, rango_buscado, )
يتيح لك هذا معًا البحث بشكل ديناميكي عن القيم وإنشاء عمليات بحث معقدة.
وظائف مرجعية مفيدة أخرى
- عوض يتيح لك الحصول على إزاحة النطاق من خلية البداية، وهو أمر أساسي لإنشاء نطاقات ديناميكية ومعرفة المزيد في كيفية إنشاء تحليلات شرطية في Excel.
- غير مباشر يعد هذا مفيدًا جدًا إذا كنت بحاجة إلى إنشاء مراجع باستخدام النص، مما يساعدك على العمل مع صيغ متغيرة.
- عمود y فيلا إنها تشير إلى رقم العمود أو الصف للمرجع، وهي عملية جدًا للحسابات التلقائية.
- نقل يسمح لك بتبديل الصفوف بالأعمدة، في حالة وجود الجدول الخاص بك في الاتجاه المعاكس لما تحتاجه.
وظائف البحث والمراجع الحديثة والمتقدمة
مع مرور الوقت، قام Excel بدمج ميزات جديدة تعمل على تعزيز قدرات التحليل والأتمتة الخاصة به:
- فلتر:يسمح لك بالحصول على جدول مفلتر وفقًا لشرط أو مجموعة من الشروط التي تحددها.
- يونيكوس:استخراج كافة القيم الفريدة من نطاق، وإزالة القيم المكررة.
- ترتيب y فرز حسب:تسهل ترتيب البيانات داخل مصفوفة أو بناءً على نطاق آخر.
- يأخذ, إسقاط, توكول, تورو:وظائف مختلفة تسمح لك بالتعامل مع المصفوفات، أو إرجاع صفوف أو أعمدة معينة فقط، أو تحويل النطاقات إلى أعمدة/صفوف مفردة.
- الرابكولز y يلتف:أعد تنظيم المصفوفات عن طريق تقسيمها وفقًا لعدد العناصر التي تحددها.
تتيح لك هذه الوظائف إنشاء جداول وتحليلات أكثر ديناميكية، أتمتة العديد من المهام دون الحاجة إلى موارد خارجية أخرى.
مثال عملي: البحث في المتجهات والمصفوفات
تخيل أن لديك عمودًا يحتوي على قيم رقمية وآخر يحتوي على ألوان مرتبطة بها. تريد معرفة اللون الذي يقابل القيمة 4,19.
- ضع بياناتك في عمودين، على سبيل المثال، A وB، مثل هذا:
تردد لون 4,14 أحمر 4,19 البرتقالي 5,17 أصفر 5,77 الأخضر 6,39 أزرق - الآن، في خلية أخرى يمكنك كتابة الصيغة:
=BUSCAR(4,19; A2:A6; B2:B6)
سيبحث Excel في العمود A عن القيمة 4,19 وسيرجع اللون الموجود في نفس الصف في العمود B: البرتقالي.
إذا بحثتَ عن قيمة غير موجودة في الجدول، فسيتم إرجاع اللون ذي الرقم الأقرب. إذا كانت قيمة البحث أقل من الحد الأدنى للعمود، فسيتم إرجاع #N/A.
يعد هذا النوع من البحث ضروريًا في تحليل البيانات والمخزون ومراقبة المخزون وأي موقف تحتاج فيه إلى مقارنة المعلومات.
نصائح رئيسية لإتقان وظائف البحث والمرجع
- في الإصدارات الأحدث من Excel، راهن على SEARCHX كلما أمكن، إذ يُعالج العديد من قيود دالة VLOOKUP وHLOOKUP. لمعرفة كيفية استخدامه، نوصي بزيارة موقعنا.
- عندما تعمل مع مصفوفات ديناميكية أو بيانات تتغير بشكل متكرر، يجمع بين INDEX وMATCH للحصول على نتائج قوية ومرنة.
- لا تنسى الاستفادة من الميزات مثل فلتر, يونيكوس ووظائف مصفوفة جديدة لأتمتة التقارير.
- إذا كنت بحاجة إلى الإشارة إلى النطاقات التي تتغير في الحجم، استخدم OFFSET أو INDIRECT للحفاظ على صيغك محدثة.
- لتسهيل قراءة الصيغ الخاصة بك، استخدم أسماء النطاقات ووصف ما تفعله كل وظيفة بشكل واضح، خاصة إذا كنت تشارك الملف مع الآخرين.
تطبيقات عملية وأمثلة من الحياة الواقعية
تُعدّ وظائف البحث والمراجع أساسًا لأتمتة المهام اليومية في Excel، بدءًا من دمج البيانات من جداول بيانات مختلفة وصولًا إلى إنشاء تقارير مخصصة ولوحات معلومات. من بين التطبيقات العملية:
- قم بمقارنة بيانات المبيعات والعملاء للحصول على المعلومات ذات الصلة بسرعة.
- إنشاء قوالب المخزون التي يتم تحديثها تلقائيًا استنادًا إلى إدخال المنتج.
- إنشاء تقارير ديناميكية تعرض القيم استنادًا إلى التحديدات المخصصة.
- مقارنة قوائم الموظفين أو الطلاب أو المنتجات، واكتشاف التكرارات والاختلافات.
- قم بتصفية كميات كبيرة من المعلومات للحصول فقط على السجلات التي تلبي معايير معينة.
الأخطاء الشائعة وكيفية تجنبها
من الشائع ارتكاب بعض الأخطاء عند استخدام هذه الوظائف، خاصة إذا لم تنتبه إلى بناء الجملة والنطاقات:
- أخطاء النوع #N/A:تحدث هذه المشكلة عندما لا يتم العثور على القيمة التي تم البحث عنها، أو عندما لا يكون نطاق البحث كافياً.
- مشاكل مع المراجع النسبية والمطلقة:كن حذرًا عند سحب الصيغ؛ استخدم الرمز $ لربط الأعمدة أو الصفوف.
- عدم تطابق في عدد الصفوف/الأعمدة:عند استخدام LOOKUP أو INDEX/MATCH، تأكد من تطابق النطاقات في الحجم.
- عمليات البحث الحساسة لحالة الأحرف:هذه الوظائف غير حساسة لحالة الأحرف بشكل عام، لذا فإن "John" و"john" سيكونان متكافئين.
- دائما تذكر قم بفرز البيانات إذا كنت ستقوم بعمليات بحث تقريبية، حيث أن عدم وجود النظام يمكن أن يؤدي إلى نتائج غير متوقعة.
نصائح لتحسين إنتاجيتك باستخدام هذه الميزات
- التدرب على جداول الأمثلةبهذه الطريقة سوف تتعلم كيفية التعامل مع الأخطاء وفهم سلوك كل وظيفة.
- استخدم معالجات الصيغ الجديدة في Excel لبناء وظائف معقدة خطوة بخطوة.
- فكر دائمًا في كيفية أتمتة العمليات المتكررة باستخدام الصيغ المرجعية، مما يوفر الوقت ويتجنب الأخطاء البشرية.
- استفد من مساعدة في Excel والوثائق الرسمية لمعرفة المتغيرات والإمكانيات المتقدمة لكل وظيفة.
إتقان وظائف البحث والمرجع إكسل مهارة تُحدث الفرق بين مجرد ملء جداول البيانات وبين أن تصبح خبيرًا حقيقيًا في تحليل البيانات. إذا استفدت من هذه الأدوات وتدربت عليها بأمثلة مختلفة، فستُؤتمت مهامك، وتُقلل الأخطاء، وتزيد إنتاجيتك، لتتمكن من التركيز على ما هو أهم: اتخاذ قرارات أفضل وتسهيل عملك اليومي. جرّبها وستلاحظ التغيير في لمح البصر! شارك الدليل حتى يتمكن المزيد من الأشخاص من معرفة كيفية القيام بذلك..