ابحث عن أسعار المنتجات في جداول بيانات Google باستخدام وظائف Vlookup وMatch

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

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

في مثالنا الخاص بـ Starbuck Coffee، يتم تخزين أسعار القهوة في النطاق B2

. يتم تخزين اسم مشروب العميل (Caffè Mocha في هذا المثال) في الخلية G3. ما يلي MATCH ستعيد الوظيفة الموضع النسبي للمشروب المحدد من قائمة المشروبات.

=MATCH(G3, $B$2:$B$11, 0)

تم تعيين المعلمة الثالثة لوظيفة MATCH على 0 لأننا نريد المطابقة التامة ولم يتم فرز قائمة الأسعار لدينا.

وبالمثل، في اليوم التالي MATCH ستعيد الوظيفة الموضع النسبي للعمود الذي يحتوي على سعر المشروب بناءً على الكمية المحددة. يتم تخزين أحجام الأكواب في النطاق C2

. يتم تخزين حجم الكوب المحدد في الخلية H3.

=MATCH(H3, $B$2:$E$2, 0)

الآن بعد أن عرفنا موضع الصف والعمود النسبي لقيمة السعر التي نبحث عنها، يمكننا استخدام الدالة INDEX وظيفة للعثور على السعر الفعلي من الجدول.

=INDEX($B$2:$E$11, H5, H7)

استخدم Vlookup مع ArrayFormula وMatch

في المثال التالي، لدينا طلب عميل يحتوي على مشروبات متعددة، واحد لكل صف. نريد إيجاد سعر كل مشروب والسعر الإجمالي للطلب. ستكون صيغ المصفوفة مناسبة تمامًا هنا لأننا نريد توسيع نفس الصيغة لتشمل جميع صفوف جدول البيانات.

ومع ذلك، سيتعين علينا إعادة النظر في نهجنا منذ ذلك الحين INDEX لا يمكن استخدام الدالة المستخدمة في المثال السابق مع صيغ الصفيف لأنها لا تستطيع إرجاع قيم متعددة. سوف نستبدل INDEX مع مماثلة VLOOKUP وظيفة ودمجها مع MATCH وظيفة لإجراء بحث ثنائي الاتجاه (ابحث عن المشروب بالاسم ثم ابحث عن حجم الكوب المحدد).

بناء جملة دالة VLOOKUP، باللغة الإنجليزية البسيطة، هو:

=VLOOKUP(
  What you want to look for (beverage name),
  Where you want to look for it (price table range),
  The column number containing the matching value (chosen cup size),
  Return an approximate or exact match (True or False)
)

ستبحث الوظيفة عن اسم المشروب في النطاق السعري المحدد (B2

) ومن الصف المطابق، قم بإرجاع قيمة الخلية الموجودة في العمود الذي يتوافق مع حجم الكوب المحدد.

لم يتم فرز النطاق السعري لذا سنضع FALSE للمعلمة الرابعة.

ال MATCH ستعيد الدالة الموضع النسبي للعمود الذي يحتوي على سعر الكمية المحددة من المشروب المطابق:

=MATCH(
  What are you looking for (cup size),
  Where are you looking for it (cup size header range),
  0 if you want to find the exact value (default is 1)
)

إذا كان الصف لا يحتوي على اسم المشروب، فسيتم إرجاع الصيغة #N/A وبالتالي نختتم القيمة IFNA لمنع الصيغة من إرجاع أية أخطاء.

وبالتالي ستبدو صيغتنا النهائية كما يلي:

=ARRAYFORMULA(IFNA(VLOOKUP(B14:B, $B$2:$E$11, MATCH(C14:C, $B$2:$E$2, 0), FALSE)))

قم بتنزيل ملف Excel – ورقة البحث عن الأسعار