كيفية استخدام وظيفة VLOOKUP في Microsoft Excel [+ فيديو تعليمي]

يعد تنسيق كمية هائلة من البيانات في Microsoft Excel بمثابة صداع يستغرق وقتًا طويلاً. لحسن الحظ، ليس عليك القيام بذلك. يمكن أن تساعدك وظيفة VLOOKUP في أتمتة هذه المهمة وتوفير الكثير من الوقت.

ماذا تفعل VLOOKUP بالضبط؟ إليك الشرح البسيط: تبحث وظيفة VLOOKUP عن قيمة محددة في بياناتك، وبمجرد تحديد هذه القيمة، يمكنها العثور على - وعرض - بعض المعلومات الأخرى المرتبطة بهذه القيمة.

تعد وظيفة VLOOKUP الخاصة بـ Microsoft Excel أسهل في الاستخدام مما تعتقد. علاوة على ذلك، فهي قوية بشكل لا يصدق، وهي بالتأكيد شيء تريد أن تمتلكه في ترسانتك من الأسلحة التحليلية.

انتقل إلى:

كيف تعمل VLOOKUP؟

يرمز VLOOKUP إلى "البحث العمودي". في Excel، يعني هذا عملية البحث عن البيانات عموديًا عبر جدول بيانات، باستخدام أعمدة جدول البيانات - ومعرف فريد داخل تلك الأعمدة - كأساس لبحثك. عندما تبحث عن بياناتك، يجب أن يتم إدراجها عموديًا أينما توجد تلك البيانات.

صيغة VLOOKUP Excel

تصف Microsoft صيغة أو وظيفة VLOOKUP كما يلي:

=VLOOKUP(قيمة البحث، النطاق الذي يحتوي على قيمة البحث، رقم العمود في النطاق الذي يحتوي على القيمة المرجعة، المطابقة التقريبية (TRUE) أو المطابقة التامة (FALSE)).

يساعد على تنظيم بياناتك بطريقة تجعل القيمة التي تريد البحث عنها موجودة على يسار القيمة المرجعة التي تريد العثور عليها.

تبحث الصيغة دائمًا إلى اليمين.

عند إجراء VLOOKUP في Excel، فإنك تبحث بشكل أساسي عن بيانات جديدة في جدول بيانات مختلف مرتبط بالبيانات القديمة في جدولك الحالي. عندما تقوم VLOOKUP بتشغيل هذا البحث، فإنها تبحث دائمًا عن البيانات الجديدة الموجودة على يمين بياناتك الحالية.

على سبيل المثال، إذا كان أحد جداول البيانات يحتوي على قائمة عمودية من الأسماء، وكان جدول بيانات آخر يحتوي على قائمة غير منظمة بهذه الأسماء وعناوين بريدهم الإلكتروني ، فيمكنك استخدام VLOOKUP لاسترداد عناوين البريد الإلكتروني هذه بالترتيب الذي وضعتها به في جدول بياناتك الأول. يجب إدراج عناوين البريد الإلكتروني هذه في العمود الموجود على يسار الأسماء في جدول البيانات الثاني، وإلا فلن يتمكن Excel من العثور عليها. (إذهب واستنتج … )

تحتاج الصيغة إلى معرف فريد لاسترداد البيانات.

سر كيفية عمل VLOOKUP؟ معرفات فريدة.

المعرف الفريد هو جزء من المعلومات التي يتشاركها كلا مصدري البيانات، و- كما يوحي اسمه - فهو فريد (أي أن المعرف يرتبط فقط بسجل واحد في قاعدة البيانات الخاصة بك). تتضمن المعرفات الفريدة رموز المنتج ووحدات حفظ المخزون (SKU) وجهات اتصال العملاء.

حسنًا، شرح كافٍ: دعونا نرى مثالاً آخر لـ VLOOKUP أثناء العمل!

مثال VLOOKUP إكسل

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

ملاحظة المؤلف: هناك العديد من الإصدارات المختلفة لبرنامج Excel، لذا فإن ما تراه في الفيديو أعلاه قد لا يتطابق دائمًا تمامًا مع ما ستراه في نسختك. ولهذا السبب نشجعك على اتباع التعليمات المكتوبة أدناه.

كيفية استخدام فلوكوب في إكسيل

1. حدد عمودًا من الخلايا التي ترغب في ملؤها بالبيانات الجديدة.
2. حدد "Function" (Fx) > VLOOKUP وأدخل هذه الصيغة في الخلية المميزة.
3. أدخل قيمة البحث التي تريد استرداد البيانات الجديدة لها.
4. أدخل مصفوفة الجدول الخاصة بجدول البيانات حيث توجد البيانات المطلوبة.
5. أدخل رقم عمود البيانات التي تريد أن يقوم Excel بإرجاعها.
6. أدخل نطاق البحث الخاص بك للعثور على تطابق تام أو تقريبي لقيمة البحث الخاصة بك.
7. انقر فوق "تم" (أو "أدخل") واملأ العمود الجديد.

كمرجع لك، إليك ما يبدو عليه بناء جملة دالة VLOOKUP:

VLOOKUP(lookup_value، table_array، col_index_num، range_lookup)

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

1. حدد عمودًا من الخلايا التي ترغب في ملؤها بالبيانات الجديدة.

تذكر أنك تريد استرداد البيانات من ورقة أخرى وإيداعها في هذه الورقة. مع أخذ ذلك في الاعتبار، قم بتسمية عمود بجوار الخلايا التي تريد المزيد من المعلومات عنها باستخدام عنوان مناسب في الخلية العلوية، مثل "MRR"، للحصول على إيرادات شهرية متكررة. هذا العمود الجديد هو المكان الذي ستنتقل إليه البيانات التي تجلبها.

2. حدد "Function" (Fx) > VLOOKUP وأدخل هذه الصيغة في الخلية المميزة.

على يسار شريط النص أعلى جدول البيانات، سترى رمز وظيفة صغير يشبه البرنامج النصي: Fx . انقر فوق الخلية الفارغة الأولى أسفل عنوان العمود الخاص بك، ثم انقر فوق رمز الوظيفة هذا. سيظهر مربع بعنوان منشئ الصيغة أو وظيفة الإدراج على يمين شاشتك (اعتمادًا على إصدار Excel لديك).

ابحث عن "VLOOKUP" وحدده من قائمة الخيارات المضمنة في Formula Builder. ثم حدد موافق أو إدراج دالة لبدء إنشاء VLOOKUP الخاص بك. يجب أن تبدو الخلية التي حددتها حاليًا في جدول البيانات كما يلي: " =VLOOKUP() "

يمكنك أيضًا إدخال هذه الصيغة في مكالمة يدويًا عن طريق إدخال النص الغامق أعلاه تمامًا في الخلية المطلوبة.

بعد إدخال النص =VLOOKUP في الخلية الأولى، حان الوقت لملء الصيغة بأربعة معايير مختلفة. ستساعد هذه المعايير برنامج Excel في تضييق نطاق البيانات التي تريدها بالضبط وما الذي تبحث عنه.

3. أدخل قيمة البحث التي تريد استرداد البيانات الجديدة لها.

المعيار الأول هو قيمة البحث — وهي قيمة جدول البيانات الذي يحتوي على بيانات مقترنة به، والتي تريد أن يبحث عنها Excel ويعيدها إليك. لإدخالها، انقر فوق الخلية التي تحتوي على القيمة التي تحاول العثور على تطابق لها. في مثالنا، الموضح أعلاه، يوجد في الخلية A2. ستبدأ في ترحيل بياناتك الجديدة إلى D2، نظرًا لأن هذه الخلية تمثل MRR لاسم العميل المدرج في A2.

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

4. أدخل مصفوفة الجدول الخاصة بجدول البيانات حيث توجد البيانات المطلوبة.

بجوار حقل "مصفوفة الجدول"، أدخل نطاق الخلايا التي ترغب في البحث فيها والورقة التي توجد بها هذه الخلايا، باستخدام التنسيق الموضح في لقطة الشاشة أعلاه. الإدخال أعلاه يعني أن البيانات التي نبحث عنها موجودة في جدول بيانات بعنوان "الصفحات" ويمكن العثور عليها في أي مكان بين العمود B والعمود K.

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

على سبيل المثال، إذا كانت بياناتك موجودة في "الورقة2" بين الخلايا C7 وL18، فسيكون إدخال صفيف الجدول الخاص بك هو "الورقة2!C7:L18".

5. أدخل رقم عمود البيانات التي تريد أن يقوم Excel بإرجاعها.

أسفل حقل مصفوفة الجدول، ستدخل "رقم فهرس العمود" لمصفوفة الجدول التي تبحث فيها. على سبيل المثال، إذا كنت تركز على الأعمدة من B إلى K (يتم الإشارة إلى "B:K" عند إدخالها في حقل "مصفوفة الجدول")، ولكن القيم المحددة التي تريدها موجودة في العمود K، فسوف تقوم بإدخال "10" في حقل "رقم فهرس العمود"، حيث أن العمود K هو العمود العاشر من اليسار.

6. أدخل نطاق البحث الخاص بك للعثور على تطابق تام أو تقريبي لقيمة البحث الخاصة بك.

في حالات مثل حالتنا، والتي تتعلق بالإيرادات الشهرية، فأنت تريد العثور على التطابقات التامة من الجدول الذي تبحث فيه. للقيام بذلك، أدخل "FALSE" في حقل "البحث عن النطاق". يخبر هذا Excel أنك تريد العثور فقط على الإيرادات المحددة المرتبطة بكل جهة اتصال مبيعات.

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

عند تعيين VLOOKUP لمطابقة تقريبية، فإنها تبحث عن البيانات الأكثر تشابهًا مع قيمة البحث الخاصة بك، بدلاً من البيانات المطابقة لتلك القيمة. إذا كنت تبحث عن بيانات مرتبطة بقائمة روابط مواقع الويب، على سبيل المثال، وكانت بعض روابطك تحتوي على "https://" في البداية، فقد يتعين عليك العثور على تطابق تقريبي فقط في حالة وجود روابط ليس لديك علامة "https://" هذه. بهذه الطريقة، يمكن أن يتطابق باقي الارتباط بدون علامة النص الأولية هذه مما يتسبب في قيام صيغة VLOOKUP بإرجاع خطأ إذا لم يتمكن Excel من العثور عليه.

7. انقر فوق "تم" (أو "أدخل") واملأ العمود الجديد.

لإحضار القيم التي تريدها رسميًا إلى العمود الجديد من الخطوة 1، انقر فوق "تم" (أو "إدخال"، اعتمادًا على إصدار Excel الخاص بك) بعد ملء حقل "البحث عن النطاق". سيؤدي هذا إلى ملء خليتك الأولى. يمكنك اغتنام هذه الفرصة للبحث في جدول البيانات الآخر للتأكد من أن هذه هي القيمة الصحيحة.

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

دروس VLOOKUP

هل واجهت مشكلة بعد محاولة إجراء VLOOKUP الخاص بك باستخدام الخطوات المذكورة أعلاه؟ تحقق من هذا البرنامج التعليمي المفيد من Microsoft الذي يحتوي على برنامج تعليمي مفيد سيرشدك خلال استخدام الوظيفة بشكل صحيح.

مصدر الصورة

إذا اتبعت الخطوات المذكورة أعلاه وما زال VLOOKUP الخاص بك لا يعمل، فإما أن تكون هناك مشكلة في:

• بناء الجملة (أي كيفية تنظيم الصيغة)
• القيم (أي ما إذا كانت البيانات التي يبحث عنها جيدة ومنسقة بشكل صحيح)

استكشاف أخطاء بناء جملة VLOOKUP وإصلاحها

ابدأ بالنظر إلى صيغة VLOOKUP التي كتبتها في الخلية المخصصة.

• هل يشير إلى قيمة البحث الصحيحة لمعرف المفتاح الخاص به؟
• هل يحدد نطاق صفيف الجدول الصحيح للقيم التي يحتاج إلى استردادها
• هل تحدد الورقة الصحيحة للنطاق؟
• هل تلك الورقة مكتوبة بشكل صحيح؟
• هل يستخدم بناء الجملة الصحيح للإشارة إلى الورقة؟ (على سبيل المثال الصفحات!B:K أو "الورقة 1"!B:K)
• هل تم تحديد رقم العمود الصحيح؟ (على سبيل المثال A هو 1، B هو 2، وهكذا)
• هل صحيح أم خطأ هو المسار الصحيح لكيفية إعداد الورقة الخاصة بك؟

استكشاف أخطاء قيم VLOOKUP وإصلاحها

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

• هل تم تنسيق القيم عموديًا ومن اليمين إلى اليسار؟
• هل تتطابق القيم مع كيفية الإشارة إليها؟

على سبيل المثال، إذا كنت تبحث عن بيانات عنوان URL، فيجب أن يكون كل عنوان URL عبارة عن صف يحتوي على البيانات المقابلة له على يساره في نفس الصف. إذا كانت لديك عناوين URL كرؤوس أعمدة مع تحرك البيانات عموديًا، فلن تعمل VLOOKUP.

مع الالتزام بهذا المثال، يجب أن تتطابق عناوين URL في التنسيق في كلا الورقتين. إذا كانت لديك ورقة واحدة تتضمن "https://" في القيمة بينما تحذف الورقة الأخرى "https://"، فلن تتمكن VLOOKUP من مطابقة القيم.

VLOOKUPs كأداة تسويقية قوية

يتعين على المسوقين تحليل البيانات من مجموعة متنوعة من المصادر للحصول على صورة كاملة لتوليد العملاء المحتملين (والمزيد). يعد Microsoft Excel الأداة المثالية للقيام بذلك بدقة وعلى نطاق واسع، خاصة مع وظيفة VLOOKUP.

ملاحظة المحرر: تم نشر هذا المنشور في الأصل في مارس 2019 وتم تحديثه من أجل الشمول.