المحتويات
نبدأ اليوم سلسلة من المقالات التي تصف إحدى أكثر الميزات المفيدة في Excel - VPR (VLOOKUP). هذه الوظيفة ، في نفس الوقت ، هي واحدة من أكثر الوظائف تعقيدًا وأقلها فهمًا.
في هذا البرنامج التعليمي على VPR سأحاول وضع الأساسيات ببساطة قدر الإمكان من أجل جعل عملية التعلم واضحة قدر الإمكان للمستخدمين عديمي الخبرة. بالإضافة إلى ذلك ، سوف ندرس العديد من الأمثلة باستخدام صيغ Excel التي ستوضح حالات الاستخدام الأكثر شيوعًا للوظيفة VPR.
وظيفة VLOOKUP في Excel - الوصف العام وبناء الجملة
إذا ما هو VPR؟ حسنًا ، أولاً وقبل كل شيء ، إنها وظيفة Excel. ماذا تفعل؟ يبحث عن القيمة التي تحددها ويعيد القيمة المقابلة من العمود الآخر. من الناحية التقنية، VPR يبحث عن القيمة في العمود الأول من النطاق المحدد ويعيد النتيجة من عمود آخر في نفس الصف.
في التطبيق الأكثر شيوعًا ، الوظيفة VPR يبحث في قاعدة البيانات عن معرف فريد معين ويستخرج بعض المعلومات المتعلقة به من قاعدة البيانات.
الحرف الأول في اسم الوظيفة VPR (VLOOKUP) تعني Вعمودي (Vعمودي). بواسطته يمكنك التمييز VPR تبدأ من GPR (HLOOKUP) ، الذي يبحث عن قيمة في الصف العلوي من النطاق - Гعرضي (Hأفقيا).
المسمى الوظيفي VPR متوفر في Excel 2013 و Excel 2010 و Excel 2007 و Excel 2003 و Excel XP و Excel 2000.
بناء جملة الدالة VLOOKUP
المسمى الوظيفي VPR (VLOOKUP) له الصيغة التالية:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
كما ترى ، وظيفة VPR في Microsoft Excel لديه 4 خيارات (أو وسيطات). الثلاثة الأولى إلزامية ، والأخيرة اختيارية.
- ابحث عن القيمة (lookup_value) - القيمة التي يجب البحث عنها. يمكن أن يكون هذا قيمة (رقم ، تاريخ ، نص) أو مرجع خلية (يحتوي على قيمة البحث) ، أو قيمة يتم إرجاعها بواسطة بعض وظائف Excel الأخرى. على سبيل المثال ، ستبحث هذه الصيغة عن القيمة 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
إذا كانت قيمة البحث أقل من أصغر قيمة في العمود الأول من النطاق الذي يتم البحث عنه ، فإن الدالة VPR سيبلغ عن خطأ #في (# N / A).
- table_array (جدول) - عمودان أو أكثر من أعمدة البيانات. تذكر ، الوظيفة VPR يبحث دائمًا عن القيمة في العمود الأول من النطاق المعطى في الوسيطة table_array (الطاولة). يمكن أن يحتوي النطاق القابل للعرض على بيانات متنوعة ، مثل النصوص والتواريخ والأرقام والمنطق المنطقي. الوظيفة غير حساسة لحالة الأحرف ، مما يعني أن الأحرف الكبيرة والصغيرة تعتبر متشابهة. إذن ، ستبحث الصيغة عن القيمة 40 في الخلايا من A2 إلى A15، لأن A هو العمود الأول من النطاق A2: B15 المعطى في الوسيطة table_array (الطاولة):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (العمود رقم) هو رقم العمود في النطاق المحدد الذي سيتم عرض القيمة في الصف الذي تم العثور عليه منه. العمود الموجود في أقصى اليسار في النطاق المحدد هو 1، العمود الثاني هو 2، العمود الثالث هو 3 وهلم جرا. الآن يمكنك قراءة الصيغة كاملة:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)صيغة تبحث عن قيمة 40 في النطاق أكسنومك: أكسنومك وإرجاع القيمة المقابلة من العمود B (لأن B هو العمود الثاني في النطاق A2: B15).
إذا كانت قيمة الوسيطة col_index_num (العمود رقم) أقل من 1then VPR سيبلغ عن خطأ #القيمة! (#القيمة!). وإذا كان أكثر من عدد الأعمدة في النطاق table_array (الجدول) ، سترجع الدالة خطأ #REF! (#حلقة الوصل!).
- مجموعة البحث (range_lookup) - يحدد ما الذي تبحث عنه:
- مطابقة تامة ، يجب أن تكون الوسيطة متساوية خاطئة (خاطئة)؛
- تطابق تقريبي ، وسيطة تساوي الكود الحقيقي (صواب) أو لم يتم تحديدها على الإطلاق.
هذه المعلمة اختيارية ، لكنها مهمة جدًا. في وقت لاحق من هذا البرنامج التعليمي على VPR سأعرض لك بعض الأمثلة لشرح كيفية كتابة الصيغ للعثور على التطابقات الدقيقة والتقريبية.
أمثلة على VLOOKUP
آمل الوظيفة VPR تصبح أكثر وضوحا قليلا بالنسبة لك. الآن دعونا نلقي نظرة على بعض حالات الاستخدام VPR في الصيغ مع البيانات الحقيقية.
كيفية استخدام VLOOKUP للبحث في ورقة Excel أخرى
في الممارسة العملية ، الصيغ مع وظيفة VPR نادرًا ما تُستخدم للبحث عن البيانات في نفس ورقة العمل. في أغلب الأحيان ، ستبحث عن القيم المقابلة وتسترجعها من ورقة أخرى.
لكي تستخدم VPR، ابحث في ورقة Microsoft Excel أخرى ، يجب عليك في الوسيطة table_array (جدول) حدد اسم الورقة بعلامة تعجب متبوعة بمجموعة من الخلايا. على سبيل المثال ، توضح الصيغة التالية النطاق أ 2: ب 15 موجود على ورقة مسماة Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
بالطبع ، لا يلزم إدخال اسم الورقة يدويًا. ما عليك سوى البدء في كتابة الصيغة ، وعندما يتعلق الأمر بالوسيطة table_array (الجدول) ، قم بالتبديل إلى الورقة المطلوبة وحدد نطاق الخلايا المطلوب باستخدام الماوس.
تبحث الصيغة الموضحة في لقطة الشاشة أدناه عن نص "المنتج 1" في العمود A (وهو العمود الأول من النطاق A1: B2) في ورقة العمل الأسعار.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
يرجى تذكر أنه عند البحث عن قيمة نصية ، يجب عليك تضمينها بين علامتي اقتباس ("") ، كما يحدث عادةً في صيغ Excel.
للحجة table_array (جدول) من المستحسن استخدام المراجع المطلقة دائمًا (بعلامة $). في هذه الحالة ، سيظل نطاق البحث بدون تغيير عند نسخ الصيغة إلى خلايا أخرى.
ابحث في مصنف آخر باستخدام VLOOKUP
لتعمل VPR عملت بين اثنين من مصنفات Excel ، فأنت بحاجة إلى تحديد اسم المصنف بين قوسين مربعين قبل اسم الورقة.
على سبيل المثال ، يوجد أدناه صيغة تبحث عن القيمة 40 على الورقة Sheet2 في كتاب Numbers.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
إليك أسهل طريقة لإنشاء صيغة في Excel باستخدام VPRالذي يرتبط بمصنف آخر:
- افتح كلا الكتابين. هذا ليس مطلوبًا ، ولكن من الأسهل إنشاء صيغة بهذه الطريقة. لا تريد إدخال اسم المصنف يدويًا ، أليس كذلك؟ بالإضافة إلى ذلك ، سوف يحميك من الأخطاء المطبعية العرضية.
- ابدأ بكتابة وظيفة VPRوعندما يتعلق الأمر بالحجة table_array (جدول) ، قم بالتبديل إلى مصنف آخر وحدد نطاق البحث المطلوب فيه.
تُظهر لقطة الشاشة أدناه الصيغة مع مجموعة البحث إلى نطاق في المصنف قائمة الأسعار.xlsx على الورقة الأسعار.
المسمى الوظيفي VPR سيعمل حتى عند إغلاق المصنف الذي تم البحث عنه ويظهر المسار الكامل لملف المصنف في شريط الصيغة ، كما هو موضح أدناه:
إذا كان اسم المصنف أو الورقة يحتوي على مسافات ، فيجب إرفاقها بفواصل عليا:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
كيفية استخدام نطاق أو جدول مسمى في الصيغ باستخدام VLOOKUP
إذا كنت تخطط لاستخدام نفس نطاق البحث في وظائف متعددة VPR، يمكنك إنشاء نطاق مسمى وإدخال اسمه في الصيغة كوسيطة table_array (الطاولة).
لإنشاء نطاق مسمى ، ما عليك سوى تحديد الخلايا وإدخال اسم مناسب في الحقل الإسم الأول، على يسار شريط الصيغة.
يمكنك الآن كتابة الصيغة التالية لإيجاد سعر المنتج المنتج شنومكس:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
تعمل معظم أسماء النطاقات لمصنف Excel بأكمله ، لذلك ليست هناك حاجة لتحديد اسم الورقة للوسيطة table_array (جدول) ، حتى لو كانت الصيغة ونطاق البحث في أوراق عمل مختلفة. إذا كانوا في مصنفات مختلفة ، فقبل اسم النطاق ، تحتاج إلى تحديد اسم المصنف ، على سبيل المثال ، مثل هذا:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
لذا فإن الصيغة تبدو أكثر وضوحا ، توافق؟ أيضًا ، يعد استخدام النطاقات المسماة بديلاً جيدًا للمراجع المطلقة لأن النطاق المسمى لا يتغير عند نسخ الصيغة إلى خلايا أخرى. هذا يعني أنه يمكنك التأكد من أن نطاق البحث في الصيغة سيظل دائمًا صحيحًا.
إذا قمت بتحويل نطاق من الخلايا إلى جدول بيانات Excel كامل باستخدام الأمر طاولات ومكاتب (جدول) علامة التبويب إدخال (إدراج) ، عند تحديد نطاق بالماوس ، سيقوم Microsoft Excel تلقائيًا بإضافة أسماء الأعمدة (أو اسم الجدول إذا حددت الجدول بأكمله) إلى الصيغة.
ستبدو الصيغة النهائية كما يلي:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
أو ربما مثل هذا:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
عند استخدام النطاقات المسماة ، ستشير الروابط إلى نفس الخلايا بغض النظر عن مكان نسخ الوظيفة VPR داخل المصنف.
استخدام أحرف البدل في صيغ VLOOKUP
كما هو الحال مع العديد من الوظائف الأخرى ، VPR يمكنك استخدام أحرف البدل التالية:
- علامة الاستفهام (؟) - تحل محل أي حرف واحد.
- النجمة (*) - يستبدل أي تسلسل من الأحرف.
استخدام أحرف البدل في الوظائف VPR يمكن أن تكون مفيدة في كثير من الحالات ، على سبيل المثال:
- عندما لا تتذكر بالضبط النص الذي تريد البحث عنه.
- عندما تريد البحث عن بعض الكلمات التي هي جزء من محتوى الخلية. إعلم أن VPR يبحث بمحتويات الخلية ككل ، كما لو تم تمكين الخيار تطابق محتوى الخلية بالكامل (خلية كاملة) في بحث Excel القياسي.
- عندما تحتوي الخلية على مسافات إضافية في بداية المحتوى أو نهايته. في مثل هذه الحالة ، يمكنك إرهاق عقلك لفترة طويلة ، في محاولة لمعرفة سبب عدم نجاح الصيغة.
مثال 1: البحث عن نص يبدأ أو ينتهي بأحرف معينة
لنفترض أنك تريد البحث عن عميل معين في قاعدة البيانات الموضحة أدناه. أنت لا تتذكر اسمه الأخير ، لكنك تعلم أنه يبدأ بـ "ack". إليك صيغة ستؤدي المهمة على ما يرام:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
الآن بعد أن تأكدت من العثور على الاسم الصحيح ، يمكنك استخدام نفس الصيغة للعثور على المبلغ الذي دفعه هذا العميل. للقيام بذلك ، ما عليك سوى تغيير الوسيطة الثالثة للدالة VPR إلى رقم العمود المطلوب. في حالتنا ، هذا هو العمود C (الثالث في النطاق):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
فيما يلي بعض الأمثلة الأخرى مع أحرف البدل:
~ ابحث عن اسم ينتهي بـ "man":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ ابحث عن اسم يبدأ بكلمة "إعلان" وينتهي بكلمة "ابن":
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ نجد الاسم الأول في القائمة ويتكون من 5 أحرف:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
لتعمل VPR مع أحرف البدل التي تعمل بشكل صحيح ، كالوسيطة الرابعة التي يجب عليك استخدامها دائمًا خاطئة (خاطئة). إذا كان نطاق البحث يحتوي على أكثر من قيمة واحدة تتطابق مع مصطلحات البحث مع أحرف البدل ، فسيتم إرجاع القيمة الأولى التي تم العثور عليها.
مثال 2: الجمع بين أحرف البدل ومراجع الخلايا في صيغ VLOOKUP
الآن دعنا نلقي نظرة على مثال أكثر تعقيدًا قليلاً عن كيفية البحث باستخدام الوظيفة VPR حسب القيمة في الخلية. تخيل أن العمود A عبارة عن قائمة بمفاتيح الترخيص ، والعمود B عبارة عن قائمة بالأسماء التي تمتلك ترخيصًا. بالإضافة إلى ذلك ، لديك جزء (عدة أحرف) من نوع ما من مفتاح الترخيص في الخلية C1 ، وتريد العثور على اسم المالك.
يمكن القيام بذلك باستخدام الصيغة التالية:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
تبحث هذه الصيغة عن القيمة من الخلية C1 في النطاق المحدد وترجع القيمة المقابلة من العمود B. لاحظ أنه في الوسيطة الأولى ، نستخدم حرف علامة العطف (&) قبل وبعد مرجع الخلية لربط السلسلة النصية.
كما ترى في الشكل أدناه ، الوظيفة VPR إرجاع "Jeremy Hill" لأن مفتاح الترخيص الخاص به يحتوي على تسلسل الأحرف من الخلية C1.
لاحظ أن الحجة table_array (الجدول) في لقطة الشاشة أعلاه يحتوي على اسم الجدول (Table7) بدلاً من تحديد نطاق من الخلايا. هذا ما فعلناه في المثال السابق.
تطابق تام أو تقريبي في وظيفة VLOOKUP
وأخيرًا ، دعنا نلقي نظرة فاحصة على الوسيطة الأخيرة المحددة للدالة VPR - مجموعة البحث (interal_view). كما ذكرنا في بداية الدرس ، فإن هذه الحجة مهمة للغاية. يمكنك الحصول على نتائج مختلفة تمامًا بنفس الصيغة بقيمتها الكود الحقيقي (صحيح أو خاطئة (خاطئة).
أولاً ، دعنا نتعرف على ما يعنيه Microsoft Excel بالمطابقات الدقيقة والتقريبية.
- إذا كانت الحجة مجموعة البحث (range_lookup) يساوي خاطئة (FALSE) ، فإن الصيغة تبحث عن تطابق تام ، أي بالضبط نفس القيمة الواردة في الوسيطة ابحث عن القيمة (ابحث عن القيمة). إذا كان في العمود الأول من النطاق tقادر_أري (جدول) يصادف قيمتين أو أكثر تتطابق مع الوسيطة ابحث عن القيمة (search_value) ، ثم سيتم اختيار أول واحد. إذا لم يتم العثور على مطابقات ، ستقوم الوظيفة بالإبلاغ عن خطأ #في (# N / A). على سبيل المثال ، ستقوم الصيغة التالية بالإبلاغ عن خطأ #في (# N / A) إذا لم تكن هناك قيمة في النطاق A2: A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - إذا كانت الحجة مجموعة البحث (range_lookup) يساوي الكود الحقيقي (TRUE) ، تبحث الصيغة عن تطابق تقريبي. بتعبير أدق ، أولا الوظيفة VPR يبحث عن تطابق تام ، وإذا لم يتم العثور على أي منها ، يتم تحديد تطابق تقريبي. المطابقة التقريبية هي أكبر قيمة لا تتجاوز القيمة المحددة في الوسيطة. ابحث عن القيمة (ابحث عن القيمة).
إذا كانت الحجة مجموعة البحث (range_lookup) يساوي الكود الحقيقي (TRUE) أو لم يتم تحديدها ، فيجب فرز القيم الموجودة في العمود الأول من النطاق بترتيب تصاعدي ، أي من الأصغر إلى الأكبر. خلاف ذلك ، فإن الوظيفة VPR قد ترجع نتيجة خاطئة.
لفهم أهمية الاختيار بشكل أفضل الكود الحقيقي (الحقيقة) أو خاطئة (خطأ) ، دعنا نلقي نظرة على بعض الصيغ مع الوظيفة VPR وإلقاء نظرة على النتائج.
مثال 1: البحث عن تطابق تام مع VLOOKUP
كما تتذكر ، للبحث عن المطابقة التامة ، الوسيطة الرابعة للدالة VPR يجب أن يهم خاطئة (خاطئة).
دعنا نعود إلى الجدول من المثال الأول ونكتشف أي حيوان يمكنه التحرك بسرعة 50 ميل في الساعة. أعتقد أن هذه الصيغة لن تسبب لك أي صعوبات:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
لاحظ أن نطاق البحث (العمود A) يحتوي على قيمتين 50 - في الخلايا A5 и A6. تُرجع الصيغة قيمة من الخلية B5. لماذا ا؟ لأنه عند البحث عن تطابق تام ، فإن الوظيفة VPR يستخدم القيمة الأولى التي تم العثور عليها والتي تطابق القيمة التي يتم البحث عنها.
مثال 2: استخدام VLOOKUP للبحث عن تطابق تقريبي
عند استخدام الوظيفة VPR للبحث عن تطابق تقريبي ، أي عند الحجة مجموعة البحث (range_lookup) يساوي الكود الحقيقي (صواب) أو تم حذفه ، فإن أول شيء عليك القيام به هو فرز النطاق حسب العمود الأول بترتيب تصاعدي.
هذا مهم جدا لأن الوظيفة VPR تُرجع القيمة الأكبر التالية بعد القيمة المحددة ، ثم يتوقف البحث. إذا أهملت الترتيب الصحيح ، فسينتهي بك الأمر بنتائج غريبة جدًا أو رسالة خطأ. #في (# N / A).
يمكنك الآن استخدام إحدى الصيغ التالية:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
كما ترون ، أريد معرفة أي الحيوانات لديها السرعة الأقرب 69 ميلا في الساعة. وها هي النتيجة التي أعيدت الوظيفة إلي VPR:
كما ترى ، أعادت الصيغة نتيجة بقر الوحش (الظباء) سرعتها 61 ميل في الساعة ، على الرغم من أن القائمة تشمل أيضًا الفهد (فهد) الذي يجري بسرعة 70 ميل في الساعة ، و 70 أقرب إلى 69 من 61 ، أليس كذلك؟ لماذا يحدث هذا؟ لأن الوظيفة VPR عند البحث عن تطابق تقريبي ، يتم إرجاع أكبر قيمة لا تزيد عن القيمة التي يتم البحث عنها.
آمل أن تلقي هذه الأمثلة بعض الضوء على العمل مع الوظيفة VPR في Excel ، ولم تعد تنظر إليها على أنها غريبة. الآن لا يضر أن نكرر بإيجاز النقاط الرئيسية للمادة التي درسناها من أجل إصلاحها بشكل أفضل في الذاكرة.
VLOOKUP في Excel - عليك أن تتذكر ذلك!
- المسمى الوظيفي VPR لا يمكن أن ينظر Excel إلى اليسار. يبحث دائمًا عن القيمة الموجودة في العمود الموجود في أقصى اليسار من النطاق المعطى بواسطة الوسيطة table_array (الطاولة).
- فى مهمة VPR جميع القيم غير حساسة لحالة الأحرف ، أي أن الأحرف الصغيرة والكبيرة متكافئة.
- إذا كانت القيمة التي تبحث عنها أقل من الحد الأدنى للقيمة في العمود الأول من النطاق الذي يتم البحث عنه ، فإن الوظيفة VPR سيبلغ عن خطأ #في (# N / A).
- إذا الوسيطة الثالثة col_index_num (العمود رقم) أقل من 1وظيفة VPR سيبلغ عن خطأ #القيمة! (#القيمة!). إذا كان أكبر من عدد الأعمدة في النطاق table_array (الجدول) ، ستقوم الوظيفة بالإبلاغ عن خطأ #REF! (#حلقة الوصل!).
- استخدم مراجع الخلية المطلقة في الوسيطة table_array (جدول) بحيث يتم الاحتفاظ بنطاق البحث الصحيح عند نسخ الصيغة. حاول استخدام نطاقات أو جداول مسماة في Excel كبديل.
- عند إجراء بحث مطابق تقريبي ، تذكر أنه يجب فرز العمود الأول في النطاق الذي تبحث عنه بترتيب تصاعدي.
- أخيرًا ، تذكر أهمية الحجة الرابعة. استخدم القيم الكود الحقيقي (الحقيقة) أو خاطئة (خطأ) متعمد وسوف تتخلص من الكثير من الصداع.
في المقالات التالية من البرنامج التعليمي الخاص بالوظيفة VPR في Excel ، سنتعلم المزيد من الأمثلة المتقدمة ، مثل إجراء عمليات حسابية مختلفة باستخدام VPRواستخراج القيم من عدة أعمدة والمزيد. شكرًا لك على قراءة هذا البرنامج التعليمي وآمل أن أراك مرة أخرى الأسبوع المقبل!