كيفية إرجاع قيمة إذا كانت هناك قيمة معينة موجودة في نطاق محدد في Excel؟
عند العمل مع البيانات في Excel، غالبًا ما يكون من الضروري تحديد ما إذا كانت قيمة محددة موجودة ضمن نطاق محدد، وإذا كانت كذلك، استرداد قيمة من الخلية المجاورة المقابلة لتلك الإدخال. على سبيل المثال، كما هو موضح في لقطة الشاشة على اليسار، إذا كنت تبحث عن الرقم 5 ضمن قائمة أو نطاق، فقد ترغب في إرجاع القيمة المجاورة المقابلة تلقائيًا - وهذا مفيد في المهام مثل البحث عن معرفات المنتجات، واسترجاع معلومات المستخدمين، أو مطابقة الأكواد مع القيم دون البحث اليدوي.
إرجاع قيمة إذا كانت هناك قيمة معينة موجودة في نطاق محدد
إرجاع قيمة إذا كانت هناك قيمة معينة موجودة في نطاق محدد باستخدام دالة VLOOKUP
للاستعلام بسرعة عن قيمة مرتبطة بإدخال معين من جدول بيانات أو نطاق في Excel، تقدم دالة VLOOKUP حلاً بسيطًا.
هذه الطريقة فعالة بشكل خاص إذا كانت عمود البحث الخاص بك (حيث تبحث عن القيمة) هو العمود الأول في النطاق الخاص بك، وتريد إرجاع البيانات من عمود إلى يمينه. تُستخدم عادةً للبحث عن الرموز والأسامي وأرقام الهوية أو أرقام المرجع واسترجاع التفاصيل ذات الصلة بسهولة.
1. اختر خلية فارغة حيث تريد أن تظهر النتيجة. أدخل الصيغة التالية في شريط الصيغة:
=VLOOKUP(E2,A2:C8,3,TRUE)اضغط على Enter لتنفيذ الصيغة. انظر لقطة الشاشة:
في هذا المثال، إذا تم العثور على الرقم 5 (في الخلية E2) ضمن النطاق المحدد للأرقام في العمود A (على سبيل المثال، بين 4 و6)، ستحلل Excel هذه القيمة وتقوم مباشرة بملء القيمة المقابلة من العمود الثالث (العمود C) من النطاق A2:C8 في الخلية التي اخترتها. في الرسم التوضيحي، يتم إرجاع "Addin 012" لأن الرقم 5 يقع ضمن النطاق 4-6.
ملاحظة: في الصيغة، E2 تشير إلى القيمة المطلوبة، A2:C8 هو نطاق البيانات الذي يحتوي على القيم المطلوبة والقيم المسترجعة، و3 تحدد أن القيمة المسترجعة يجب أن تأتي من العمود الثالث من النطاق المحدد. قم بتعديل هذه المراجع حسب ما يناسب ورقة العمل الخاصة بك.
نصائح ومخالفات:
- تأكد من أن نطاق البحث (A2:C8) يتضمن كل من عمود البحث وعمود الاسترجاع.
- عند استخدام VLOOKUP مع وسيطة TRUE، يجب أن يكون عمود البحث مرتبًا تصاعديًا، وإلا قد تحصل على نتائج غير متوقعة.
- للمطابقات الدقيقة، استخدم FALSE كوسيطة رابعة، ولكن بالنسبة للبحث في النطاقات (كما في هذا المثال)، اتركها كـ TRUE.
- إذا كانت بياناتك تتغير بشكل متكرر، تحقق من جديد من مراجعك لتجنب أخطاء عدم التوافق.
إرجاع قيمة إذا كانت هناك قيمة معينة موجودة في نطاق محدد باستخدام دوال INDEX و MATCH
الجمع بين دوال INDEX و MATCH هي طريقة مرنة لإرجاع قيمة عندما تكون هناك قيمة معينة موجودة في نطاق محدد. على عكس VLOOKUP، يمكن لـ INDEX و MATCH البحث عن قيمة في أي عمود وإرجاع نتيجة من أي عمود آخر، بغض النظر عن الترتيب. هذا مفيد بشكل خاص إذا لم يكن عمود البحث هو العمود الموجود في أقصى اليسار أو إذا كنت بحاجة إلى مرونة أكبر في بنية بياناتك.
1. اختر خلية فارغة حيث تريد أن تظهر النتيجة (على سبيل المثال، F2). أدخل الصيغة التالية في شريط الصيغة:
=INDEX(C2:C8, MATCH(E2, A2:A8,1))اضغط على Enter لتأكيد الصيغة.
- MATCH(E2, A2:A8, 1) يبحث عن موقع القيمة الأكبر ≤ E2 في العمود A. (يتطلب ذلك أن يكون العمود A مرتبًا تصاعديًا.)
- INDEX(C2:C8, ...) يعيد القيمة من العمود C عند رقم الصف الذي وجده MATCH.
تبحث هذه الصيغة عن القيمة في E2 ضمن النطاق A2:A8. إذا تم العثور على القيمة (على سبيل المثال، 5 بين 4 و6 في أحد الصفوف)، فإن دالة MATCH تعيد موقعها النسبي، ويجلب INDEX القيمة من الصف المقابل في C2:C8. 1 في MATCH يعني تطابق تقريبي، لذا تأكد من أن نطاق البحث الخاص بك مرتب بشكل صحيح.
- إذا كنت تريد تطابقًا دقيقًا، استخدم
0كوسيطة ثالثة في MATCH. - يدعم INDEX و MATCH كل من الاتجاهين العمودي والأفقي للبيانات.
- إذا لم يتم العثور على القيمة، فإن الصيغة ترجع #N/A؛ فكر في تغليفها مع
IFERRORللحصول على مخرجات أكثر ودية.
إرجاع قيمة إذا كانت هناك قيمة معينة موجودة في نطاق محدد باستخدام دالة XLOOKUP
دالة XLOOKUP هي بديل حديث للبحث عن القيم في Excel365 وExcel2019. تتجاوز XLOOKUP العديد من القيود الموجودة في VLOOKUP، مثل القيد على مواقع الأعمدة وتطابق التفسيري/التقريبي التلقائي.
1. في الخلية المطلوبة للإخراج (على سبيل المثال، F2)، اكتب الصيغة التالية:
=XLOOKUP(1, (E2>=A2:A8)*(E2<=B2:B8), C2:C8)بعد إدخال الصيغة، اضغط على Enter لرؤية النتيجة في الخلية المختارة.
- (E2>=A2:A8) تتحقق مما إذا كان E2 أكبر من أو يساوي كل قيمة في العمود A.
- (E2<=B2:B8) تتحقق مما إذا كان E2 أقل من أو يساوي كل قيمة في العمود B.
- ضرب هذين الشرطين يخلق مجموعة من 1 و0، حيث تعني 1 أن E2 بين A وB في تلك الصف.
- XLOOKUP(1, ..., C2:C8) يبحث عن أول 1 ويُرجع القيمة المقابلة من العمود C.
- تعدل XLOOKUP ديناميكيًا إذا تم إدراج أعمدة أو نقلها، على عكس VLOOKUP بأعداد الأعمدة الثابتة.
- يعمل لكل من البيانات العمودية والأفقية.
- يتطلب Excel 365 أو 2021؛ لنسخ أقدم، استخدم الأساليب الأخرى الموصوفة أعلاه.
اكتشف سحر Excel مع Kutools AI
- التنفيذ الذكي: قم بعمليات الخلية، وتحليل البيانات، وإنشاء المخططات - كل ذلك يتم من خلال أوامر بسيطة.
- الصيغ المخصصة: قم بإنشاء صيغ مصممة خصيصًا لتسهيل سير عملك.
- برمجة VBA: كتابة وتطبيق أكواد VBA بسهولة.
- تفسير الصيغ: فهم الصيغ المعقدة بسهولة.
- ترجمة النصوص: تجاوز الحواجز اللغوية داخل جداول البيانات الخاصة بك.
مقالات ذات صلة:
- كيفية البحث عن قيمة باستخدام vlookup وإرجاع صحيح أو خطأ/نعم أو لا في Excel؟
- كيفية إعادة قيمة باستخدام vlookup في الخلية المجاورة أو التالية في Excel؟
- كيفية إرجاع قيمة في خلية أخرى إذا كانت الخلية تحتوي على نص معين في Excel؟
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...
Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير
- تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
- افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!
جميع إضافات Kutools. مثبت واحد
حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.
- حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
- مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
- الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
- تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد