كيف تُجري عملية VLOOKUP ثم تضرب الناتج في جداول Excel؟
في سياقات العمل اليومية أو سيناريوهات تحليل البيانات، من الشائع مواجهة حالات تتطلب استخراج معلومات من جداول منظمة ثم إجراء عمليات حسابية إضافية بناءً على نتائج البحث. فعلى سبيل المثال، قد يكون لديك جدول يُحدد نسب أرباح مختلفة وفقًا لحجم مبيعات كل منتج، إلى جانب جداول أخرى تسجّل أحجام المبيعات الفعلية لكلٍ منها. ويتمثل التحدي هنا في تحديد نسبة الربح المناسبة لكل منتج استنادًا إلى مبيعاته الفعلية، ثم حساب الربح النهائي بضرب قيمة المبيعات في النسبة الملائمة. ويُعد هذا النهج بالغ الأهمية—خصوصًا عندما تعتمد استراتيجيات التسعير أو الحوافز على عتبات أو قواعد متغيرة—مما يجعله أداةً لا غنى عنها في مجالات المبيعات والمالية وإدارة المخزون.
البحث ثم الضرب بناءً على معايير
البحث ثم الضرب بناءً على معايير
لإنجاز هذه المهمة، يمكنك استخدام دالة VLOOKUP في Excel مع دالة MATCH، أو الاعتماد على دالة INDEX للحصول على نتائج مشابهة. يتميّز كل حل بحالات الاستخدام الخاصة به وفروقه الدقيقة، ما يمكّنك من اختيار الطريقة الأنسب لهيكل بياناتك وتفضيلاتك. وتساعد هاتان الطريقتان في استرجاع نسبة الربح المرتبطة بمزيج المنتج وحجم المبيعات، ثم حساب مبلغ الربح الفعلي بكفاءة.
اختر الخلية التي تقع مباشرةً على يمين قيمة المبيعات الفعلية للمنتج، ثم أدخل الصيغة التالية لاستخراج نسبة الربح المقابلة:
=VLOOKUP(B3,$A$14:$E$16,MATCH(C3,$A$13:$E$13),0)بعد كتابة الصيغة، اضغطEnter لعرض نسبة الربح التي تتوافق مع كلٍ من المنتج وحجم مبيعاته. ولحساب نسب الأرباح تلقائيًا لجميع المنتجات، استخدم مقبض التعبئة (المربع الصغير في الزاوية السفلية اليمنى للخلية) واسحب الصيغة لأسفل عبر جميع الصفوف ذات الصلة في قائمتك.
في هذه الصيغة:
- B3: اسم المنتج أو المعرّف المراد البحث عنه (المعيار 1)
- C3: قيمة المبيعات الفعلية أو الفئة (المعيار 2)
- $A$14:$E$16: النطاق الذي يحتوي جدول القواعد (المنتجات ونسب الأرباح المقابلة لها)
- $A$13:$E$13: صف العناوين الذي يحتوي نطاقات المبيعات أو الفئات
من المهم التأكد من ضبط نطاقات البحث ($A$14:$E$16 و$A$13:$E$13) كمراجع مطلقة باستخدام علامة الدولار ($)، وذلك لتجنب حدوث أخطاء عند نسخ الصيغ إلى صفوف أخرى. وإذا كانت عناوين الجداول أو أسماء المنتجات تحتوي على مسافات زائدة أو تنسيق غير متسق، فاستخدم دالة TRIM أو CLEAN لتوحيد بياناتك قبل تطبيق الصيغ. كما يجب الانتباه إلى أنواع البيانات، إذ قد يؤدي عدم التطابق بين القيم الفئوية والعددية في خلايا المعايير (B3، C3) وأعمدة العناوين إلى أخطاء في البحث أو ظهور نتائج غير مقصودة.
بمجرد استرجاع نسب الأرباح، احسب ربح كل منتج بضرب نسبة الربح المستخرجة في قيمة المبيعات الفعلية المرتبطة به. وفي العمود المجاور التالي، أدخل الصيغة التالية:
=D3*C3هنا، تحتوي الخليةD3 على نسبة الربح المحسوبة أعلاه، بينما تمثّل الخليةC3 قيمة المبيعات الفعلية. اضغطEnter، ثم استخدم مقبض التعبئة لسحب الصيغة لأسفل وتطبيقها على جميع المنتجات الأخرى، واحسب ربح كلٍّ منها بسهولة.
تلميح:إذا كنت تعمل مع مجموعة بيانات كبيرة أو تحتاج إلى نهج أكثر مرونة، يمكنك أيضًا استخدام تركيبة دالتي INDEX وMATCH للبحث. ويكون هذا مفيدًا بشكل خاص إذا لم يكن جدول البحث منظمًا بحيث يكون عمود البحث في أقصى اليسار كما تشترط دالة VLOOKUP، أو إذا كنت تفضل الإشارة إلى الأعمدة والصفوف بشكل منفصل. على سبيل المثال، أدخل الصيغة التالية للحصول على نسبة الربح استنادًا إلى كلٍ من المنتج والمبيعات:
=INDEX($B$14:$E$16,MATCH(B3,$A$14:$A$16,0),MATCH(C3,$B$13:$E$13))في هذه الصيغة:
- $B$14:$E$16: النطاق الذي يحتوي على نسب الأرباح فقط.
- MATCH(B3,$A$14:$A$16,0): يعثر على رقم الصف الذي يطابق المنتج المستهدف.
- MATCH(C3,$B$13:$E$13): يعثر على رقم العمود المقابل لمبلغ المبيعات أو الفئة.
بعد إدخال الصيغة، اضغطEnter، ثم استخدم مقبض التعبئة لتطبيقها على باقي الجدول حسب الحاجة. وعادةً ما تكون دالتا INDEX وMATCH أقل عرضة للأخطاء عندما يكون هيكل جدول البحث أكثر تعقيدًا أو يخضع لتغييرات مستقبلية، مما يوفّر مرونة أكبر.
إذا واجهت أخطاء مثل #N/A أو #REF!، فتحقق مجددًا من كتابة وسيطات البحث بشكل صحيح، وأن النطاقات تتطابق مع التصميم الفعلي لبياناتك، وأن قيم فئات المبيعات متسقة بين الجداول. ومن الأفضل توحيد قيم المعايير وتجنب المسافات المخفية أو التنسيقات غير المتوقعة في مجموعات البيانات الخاصة بك.
يمكنك أيضًا التفكير في استخدام صيغ بديلة مثل XLOOKUP أو SUMPRODUCT، خاصةً إذا كنت تعمل بإصدارات أحدث من Excel أو تحتاج إلى مطابقة متقدمة عبر مصفوفات أو شروط متعددة. فعلى سبيل المثال، توفر لك دالة XLOOKUP تركيبًا أكثر سهولة وتعاملاً مع الأخطاء بطريقة أكثر بديهية مقارنةً بـ VLOOKUP.
بعد الانتهاء من حسابات الأرباح، يُوصى بالتحقق يدويًّا من بعض النتائج للتأكد من تطبيق قواعد البحث والحسابات كما هو متوقع، مما يساعد في اكتشاف التناقضات الناتجة عن هيكل الجدول أو أخطاء الإدخال. ويسهم تحديث نطاقات جداول البحث مع تغيُّر البيانات في الحفاظ على الدقة مع مرور الوقت.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل