VLOOKUP مع SUM في Excel | كيفية استخدام VLOOKUP مع وظيفة SUM؟
Vlookup هي وظيفة متعددة الاستخدامات للغاية يمكن دمجها مع وظائف أخرى للحصول على بعض النتائج المرجوة ، أحد هذه الحالات هو حساب مجموع البيانات (بالأرقام) بناءً على القيم المطابقة ، في مثل هذه الحالات يمكننا دمج دالة الجمع مع vlookup الوظيفة ، الطريقة هي كما يلي = SUM (Vlookup (القيمة المرجعية ، مجموعة الجدول ، رقم الفهرس ، المطابقة).
Vlookup مع وظيفة SUM
VLOOKUP هي إحدى الميزات المتقدمة لبرنامج Excel. يتم استخدامه كوظيفة قاعدة البيانات لإجراء العمليات الحسابية على الجداول المستوردة من قاعدة البيانات. في هذه المقالة ، الاستخدام المشترك لوظيفة VLOOKUP و SUM في Excel لتحديد المعايير المحددة والعثور على مجموع القيم المعروضة في جميع VLOOKUPs.
شرح VLOOKUP مع Sum في Excel
نحتاج إلى فهم المصطلحات المختلفة المرتبطة باستخدام VLOOKUP و SUM في التفوق للعثور على إجمالي القيم التي تفي بالمعايير. يجب مراعاة أربعة عناصر بشكل أساسي داخل وظيفة البحث بما في ذلك قيمة البحث ونطاق البحث وفهارس الأعمدة والقيمة المنطقية.
يجب إدخال الصيغة كـ = SUM (VLOOKUP (قيمة البحث ، نطاق البحث ، فهرس العمود ، والقيمة المنطقية))
- قيمة البحث - هذه هي القيمة التي نبحث عنها لتحديد المجموع المطابق تمامًا. إنه يغير بالتأكيد قيمة البحث لتحديد مجموع الأعمدة المختلفة باستخدام معايير مختلفة.
- نطاق البحث - هو نطاق الخلايا المفيد للبحث عن البيانات باستخدام المعايير المحددة. بشكل عام ، سيكون جدول بيانات تم إنشاؤه من مصادر مختلفة.
- فهارس الأعمدة - للعثور على مجموع مصفوفة الفهارس يجب إدخالها. يمكن للمرء إدخال جميع فهارس الأعمدة وعدد قليل من فهارس الأعمدة بناءً على المتطلبات. هذه مفيدة في تحديد الأعمدة بما في ذلك باختصار.
- القيمة المنطقية - قيمة منطقية مناسبة إما 0 و 1 أو صواب أو خطأ لتحديد القيم المطابقة أو التقريبية
ملاحظة: يتم تطبيق دالة Sum أيضًا على صفوف قيم المطابقة باستخدام SUMPRODUCT في Excel و SUMIF وظيفة في Excel.
أهم أمثلة VLOOKUP مع وظيفة SUM
فيما يلي بعض الأمثلة على VLOOKUP مع وظيفة SUM.
يمكنك تنزيل Vlookup هذا مع نموذج Sum Excel هنا - Vlookup مع قالب Sum Excelمثال 1
استخدام دالة الجمع و vlookup البسيطة
يتم تحديد مبيعات الكمبيوتر المحمول باستخدام المجموع والبحث. ولكن ، يمكن أن يكون هذا ببساطة باستخدام صيغة الجمع أيضًا. سبب استخدام vlookup مع sum هو أن الجدوى من تغيير قيم البحث للخلية G3 لتحديد مبيعات عناصر أخرى مثل DVD والهاتف. من خلال تغيير قيمة البحث إلى الهاتف ، يتم إنشاء مبيعات الهاتف كما هو موضح في لقطة الشاشة أدناه.
في السيناريو الحالي ، استبعدت الدالة VLOOKUP الحساب الفردي لمجموع المبيعات المنتجة.
المثال رقم 2
تحديد مجموع المبيعات الناتجة في ورقتي عمل مختلفتين باستخدام Excel VLOOKUP و SUM. تعتبر البيانات التالية لشرح هذا المثال.
يتم إنشاء ثلاث أوراق عمل بما في ذلك يناير وفبراير وملخص لتحديد إجمالي المبيعات التي تم إنشاؤها بواسطة العميل. في هذا ، تمت إضافة وظيفتين vlookup لأوراق عمل يناير وفبراير لتحديد إجمالي المبيعات كما هو موضح في الشكل المذكور أدناه.
لتحديد المبيعات التي تم إنشاؤها بواسطة عملاء آخرين ، يتم سحب الصيغة إلى صفوف أخرى عن طريق إضافة مرجع خلية مطلق إلى نطاق البيانات. سوف ينتج عنه نتائج دقيقة في استخدام مجموع و vlookup.
المثال رقم 3
جمع القيم المعروضة في أعمدة بديلة
من الممكن جمع القيم المعروضة في أعمدة بديلة ومحددة. يعتبر الجدول التالي لتوضيح المثال الحالي.
إذا أردنا تحديد مجموع القيم في أعمدة بديلة ، بما في ذلك يناير ومارس ومايو ويوليو وسبتمبر ونوفمبر. للقيام بذلك ، يجب النظر في فهارس هذه الأعمدة فقط بدلاً من جميع فهارس الأعمدة كما هو موضح في لقطة الشاشة أدناه.
للحصول على مبيعات من المنتجات الأخرى ، ببساطة تغيير القيمة في الخلية B14 يساعد في الحصول على النتيجة المرجوة. هذه هي أفضل ميزة يقدمها برنامج Excel VLOOKUP.
كيفية استخدام VLOOKUP مع وظيفة SUM؟
يمكن أيضًا استخدام VLOOKUP و SUM للقيام بما يلي -
- تحديد مجموع القيم المطابقة في الأعمدة
- تحديد مجموع القيم المطابقة في الصفوف
للفهم ، تم إنشاء الجدول كما هو موضح في الشكل أدناه.
# 1 - تحديد مجموع القيم المطابقة في الأعمدة
إذا أردنا تحديد إجمالي مبيعات المبرد من يناير إلى ديسمبر ، فيجب إدخال المعايير المحددة في الخلية الفارغة
في هذا ، يتم تقديم قيمة البحث في الخلية المسماة A15 و A2: M11 هي نطاق البيانات ، والأرقام من 2 إلى 13 هي فهارس الأعمدة. من خلال تطبيق هذه في صيغة excel vlookup and sum ، سيتم الحصول على القيمة الإجمالية لمبرد المبيعات. بعد إدخال صيغة ، CTRL و SHIFT و ENTER في كل مرة للحصول على النتيجة وإلا يتم عرض قيمة الخلية الأولى فقط بدون كل القيم. هذه العملية قابلة للتطبيق على المنتجات المتبقية للعثور على مجموع القيم في أعمدة مختلفة.
# 2 - تحديد مجموع القيم المطابقة في الصفوف
تعرض الورقة المذكورة أدناه مبيعات المبرد في الصفوف المختلفة. يتم جمعها باستخدام الدالة SUMPRODUCT.
في هذا ، يتم تقديم قيمة البحث في الخلية A15 التي تعتبر معايير لتحديد مجموع المبيعات. يمكننا تغيير هذه القيمة إلى منتجات أخرى لمعرفة المبيعات. ولكن ، في هذه العملية ، يؤدي الضغط ببساطة على الإدخال بعد كتابة الصيغة إلى نتائج أفضل.
أشياء يجب تذكرها حول Excel Vlookup with Sum
يحتاج إلى تذكر الأشياء التالية أثناء استخدام وظيفة VLOOKUP من خلال تطوير بناء الجملة المناسب:
- القيمة التي نرغب في البحث عنها تسمى قيمة البحث
- يجب إنشاء الاسم لنطاق من الخلايا أو الصفيف لاستخدامه مع VLOOKUP. يحتاج إلى التأكد من وضع قيمة البحث في العمود الأول للعمل المناسب لـ VLookup. على سبيل المثال ، يجب أن يبدأ نطاق الخلايا بالحرف B عند تقديم قيمة البحث في الخلية التي تحتوي على العنوان C2.
- شيء واحد يجب تذكره هو أنه يجب إعطاء أرقام الفهرس للأعمدة لاستخدامها بسهولة. يُشار إلى عمود قيمة البحث بالرمز 1 ، والعمود التالي بالرقم 2 ، وهكذا.
- يجب إدخال القيمة المنطقية المناسبة إما صواب أو خطأ لمطابقة تقريبية أو تامة لقيمة البحث ورقم الفهرس ونطاق الخلايا. خلاف ذلك ، سوف تحدث أخطاء.
