استخدام الدالة VLOOKUP في جداول بيانات جوجل

تعد وظيفة الدالة (VLOOKUP) واحدة من أكثر الميزات المفيدة التي توفرها جداول بيانات جوجل (Google Sheets)، إنها أيضًا الأكثر شيوعًا عندما يتعلق الأمر بالبحث عن المعلومات في مجموعة بيانات ضخمة.

ما هي الدالة VLOOKUP

في جداول بيانات جوجل (Google Sheets) تُعد (VLOOKUP) طريقة سريعة وسهلة للبحث في قسمين من البيانات وتوحيدهما معًا في جدول البيانات بقيمة واحدة، حيث تمكّنك من العثور على المعلومات عندما يتم تنظيم البيانات في أعمدة، يرمز الحرف (V) في (VLOOKUP) إلى الوضع الرأسي (vertical)، لذلك يقوم بإجراء بحث عمودي، يبحث عن البيانات عموديًا في العمود الأول من نطاق الإدخال استنادًا إلى قيمة مفتاح مقترنة ويقوم بإرجاع قيمة في نفس الصف من عمود آخر.

قد تبدو الدالة (VLOOKUP) مُحيّرة ولكنها بسيطة جدًا بمُجرّد فهم كيفية عملها، تحتوي الصيغة التي تستخدم الدالة (VLOOKUP) على أربع وسيطات بحيث تكون الصيغة العامة للدالة (VLOOKUP) تكون كالتالي:

VLOOKUP (search_key، range، index، [is_sorted])=

• (search_key): هو قيمة مفتاح البحث التي تبحث عنها، على سبيل المثال يمكنك البحث عن الرقم (42) أو كلمة (Cats) أو القيمة الموجودة في الخلية (B4)، عمليات بحث (VLOOKUP) غير حساسة لحالة الأحرف.

• النطاق (range): هو نطاق الخلايا الذي تبحث عنه على سبيل المثال (A1 إلى D10)، يجب أن يتكون النطاق من عمودين أو أكثر من أعمدة البيانات للبحث العمودي، تبحث الوظيفة دائمًا عن المفتاح في العمود الأول (أقصى اليسار) من هذا النطاق.

• الفهرس (index): هو رقم العمود في النطاق الذي سيتم عرض قيمة مطابقة منه، تم ترقيم العمود الأول في النطاق بـ (1) ويجب أن يكون الفهرس بين (1) وعدد الأعمدة الموجودة في النطاق.

• (is_sorted): (اختيارية) مهمة فقط إذا كنت تبحث عن أقرب تطابق لقيمة مفتاح البحث،إذا كنت تفضل إرجاع التطابقات التامة لمفتاح البحث فقم بتعيينها على (FALSE)، إنها (TRUE) افتراضيًا ولكن يجب تعيينها على (FALSE) عندما لا يكون النطاق الخاص بك مرتبًا.

متى نستخدم الدالة VLOOKUP لأغراض التسويق

• الجمع بين مجموعات البيانات المختلفة لعمليات التدقيق.

• سحب بيانات الكلمات الرئيسية من مصادر مختلفة.

• إحالة أي بيانات مع مقاييس أُخرى.

كيفية استخدام الدالة VLOOKUP في جداول بيانات جوجل

1. إذا كان ناتج (is_sorted) هو (FALSE): تعمل الدالة (VLOOKUP) على إرجاع تطابق تام، إذا تواجد في العمود الأول من النطاق قيمتين أو أكثر تتطابق تمامًا مع (search_key)، فستكون النتيجة القيمة الأولى التي تم العثور عليها، إذا لم يكن هناك تطابق تام فستتلقى (FALSE) في معظم الحالات، هذا هو السلوك المطلوب.

2. إذا كانت قيمة (is_sorted) هي (TRUE): وهي قيمتها الافتراضية ترجع الدالة تطابقًا تقريبيًا، في هذه الحالة يجب فرز العمود الأول من النطاق بترتيب تصاعدي من الأصغر إلى الأكبر أو أبجديًا، تبحث الصيغة عن المطابقة التامة أولاً وإذا لم يتم العثور عليها فإنّها تبدأ في البحث عن أقرب تطابق أقل من أو يساوي (search_key)، إذا كانت كل قيمة في عمود البحث أكبر من مفتاح البحث فسيتم إرجاع الخطأ (#N/A).

أمثلة على استخدام الدالة VLOOKUP

سنشرح هنا مثال لكيفية استخدام (VLOOKUP)، من المُمكن أن يحتوي جدول بيانات الشركة على ورقتين في جدول بيانات جوجل، واحدة منها تحتوي على قائمة بالمنتجات (كل منها برقم معرّف وسعر) والورقة الثانية تحتوي على الطلبات، تستطيع أن تستخدم الـ (id) كقيمة بحث (search_key) لـ (VLOOKUP) للبحث عن أسعار المنتجات.

شيء واحد يجب ملاحظته هو أن (VLOOKUP) لا يُمكنه البحث في المعلومات المتواجدة على يسار رقم الفهرس، غالبًا ام يتوجّب عليك إمّا تجاهل البيانات الموجودة في الأعمدة الموجودة على يسار مفتاح البحث أو وضع بيانات مفتاح البحث في العمود الأول.

استخدام VLOOKUP على ورقة واحدة

في هذا المثال لنفترض أن لديك جدولين يحتويان على بيانات في ورقة واحدة، الجدول الأول هو قائمة بأسماء الموظفين وأرقام الهوية وأعياد الميلاد، كما هو موضح في الصورة التالية:

في الجدول الثاني يمكنك استخدام (VLOOKUP) للبحث عن البيانات التي تستخدم أيًا من المعايير من الجدول الأول (الاسم أو رقم المعرف أو تاريخ الميلاد)، في هذا المثال سنستخدم (VLOOKUP) لتقديم تاريخ الميلاد لرقم معرف موظف معين، ستكون صيغة (VLOOKUP) المناسبة لذلك كما هو موضح في الصورة التالية:

لشرح الصيغة الموضحة في الصورة أعلاه: يستخدم (VLOOKUP) الخلية (F4) كمفتاح البحث ويقوم بالبحث في الخلايا (A3:D9) بالكامل، تقوم بإرجاع البيانات من العمود رقم (4) في هذا النطاق (العمود D وهو عيد الميلاد) وحيث أننا نريد مطابقة تامة فإن الوسيطة النهائية هي (FALSE).

في هذه الحالة بالنسبة لرقم المعرف (123) تُرجع (VLOOKUP) تاريخ ميلاد (19/12/1971) باستخدام تنسيق (DD / MM / YY)، سنقوم بتوسيع هذا المثال بشكل أكبر عن طريق إضافة عمود إلى الجدول (B) لأسماء العائلة ممّا يجعله يربط تواريخ أعياد الميلاد بالأشخاص الفعليين، هذا لا يتطلب سوى تغيير بسيط في الصيغة، في مثالنا في الخلية (H4)، يبحث (VLOOKUP (F4، A3: D9، 3، FALSE)=) عن اللقب الذي يطابق رقم المعرف (123).

يتم هنا الحصول على البيانات الموجودة في العمود رقم (3) وهي (Surname) والتي تطابق قيمة الـ (ID) الموجودة في العمود رقم (1).

استخدام VLOOKUP مع أوراق متعددة

استخدمنا في المثال السابق بيانات مجتمعه في ورقة واحدة من جدول بيانات جوجل، يمكن أيضًا استخدام صيغة (VLOOKUP) للبحث في بيانات موجودة في العديد من الأوراق، في هذا المثال المعلومات من الجدول (A) موجودة الآن على ورقة تسمى (Employees)، بينما الجدول (B) موجود الآن على ورقة تسمى (Birthdays).

هنا لن نستخدم نطاق خلايا نموذجي للبحث فيه عن البيانات كما في المثال السابق بل يمكن النقر فوق خلية فارغة ثم كتابة الصيغة الموضحة في الصورة التالية:

قمنا بإضافة اسم الورقة التي نريد البحث خلالها وهي (Employees) إلى نطاق الخلايا حتى تتمكن صيغة (VLOOKUP) من استخدام البيانات من ورقة منفصلة في بحثها.

استخدام أحرف البدل مع VLOOKUP

استخدمت الأمثلة أعلاه قيم مفتاح البحث الدقيقة لتحديد موقع البيانات المطابقة تماماً، إذا لم يكن لديك قيمة مفتاح بحث دقيقة فيمكنك أيضًا استخدام أحرف البدل (wildcards) مثل علامة الاستفهام أو علامة النجمة مع (VLOOKUP).

في هذا المثال سنستخدم نفس مجموعة البيانات التي قمنا باستخدامها في الأمثلة أعلاه ولكن إذا نقلنا عمود الاسم الأول إلى العمود (A) فيمكننا استخدام الاسم الأول الجزئي وحرف البدل بعلامة النجمة للبحث في ألقاب الموظفين، صيغة (VLOOKUP) للبحث عن الألقاب باستخدام الاسم الأول الجزئي هي كالتالي: (VLOOKUP (B12، A3: D9، 2، FALSE)=) تذهب قيمة مفتاح البحث في الخلية (B12)، في المثال الموضح في الصورة أدناه يتطابق (Chr *) في الخلية (B12) مع اللقب (Geek) في جدول بحث العينة: