كيفية استخدام وظيفة vpr في Excel. كيف تعمل وظيفة vpr

VLOOKUP هي إحدى وظائف Excel التي تتيح لك البحث في عمود معين باستخدام بيانات من عمود آخر. تُستخدم وظيفة VLOOKUP في Excel أيضًا لنقل البيانات من جدول إلى آخر. هناك ثلاثة شروط:

1. يجب أن تكون الجداول في نفس مصنف Excel.
2. يمكنك البحث فقط بين البيانات الثابتة (وليس الصيغ).
3. يجب أن يظهر مصطلح البحث في العمود الأول من البيانات المستخدمة.

صيغة VLOOKUP في Excel

بناء جملة VLOOKUP في برنامج Excel سكانها ينالون الجنسية الروسية هو:

VLOOKUP (معيار البحث ؛ نطاق البيانات ؛ رقم عمود النتيجة ؛ شرط البحث)

الوسيطات بين قوسين مطلوبة للعثور على النتيجة النهائية.

معايير البحث

عنوان الخلية ورقة عمل Excel، مما يشير إلى بيانات إجراء البحث في الجدول.

نطاق البيانات

جميع العناوين التي يتم البحث من بينها. يجب أن يكون العمود الأول هو العمود الذي يوجد به مصطلح البحث.

رقم العمود في المجموع

رقم العمود الذي سيتم أخذ القيمة منه عند العثور على تطابق.

حالة البحث

قيمة منطقية (صواب / 1 أو خطأ / 0) تشير إلى تطابق تقريبي للبحث عن (1) أو تطابق تام (0).

VLOOKUP في Excel: أمثلة على الوظائف

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

لنجد عدد المنتجات التي تم إصدارها بالفعل حسب اسم الشهر.
يتم عرض النتيجة على يمين الجدول. في الخلية التي تحتوي على العنوان H3 ، سنقوم بإدخال القيمة المطلوبة. في المثال ، سيكون هذا هو اسم الشهر.
في الخلية H4 ، أدخل الوظيفة نفسها. يمكن القيام بذلك يدويًا ، أو يمكنك استخدام المعالج. للاتصال ، ضع المؤشر فوق الخلية H4 وانقر فوق رمز Fx بجوار شريط الصيغة.


تفتح نافذة معالج وظيفة Excel. من الضروري أن تجد VLOOKUP فيه. حدد "قائمة أبجدية كاملة" من القائمة المنسدلة وابدأ في كتابة VLOOKUP. قم بتمييز الوظيفة التي تم العثور عليها واضغط على "موافق".


تظهر نافذة VLOOKUP لجدول بيانات Excel.


لتحديد الوسيطة الأولى (المعيار) ، ضع المؤشر على السطر الأول وانقر على الخلية H3. سيظهر عنوانه على السطر. لتحديد نطاق ، ضع المؤشر على السطر الثاني وابدأ التحديد بالماوس. ستنهار النافذة إلى صف واحد. يتم ذلك حتى لا تتداخل النافذة مع رؤية النطاق بالكامل ولا تتداخل مع تنفيذ الإجراءات.


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


حرك المؤشر إلى السطر الثالث واقرأ من أي عمود سيتم أخذ البيانات عند العثور على تطابق. في مثالنا ، هذا هو 3.


اترك السطر الأخير فارغًا. بشكل افتراضي ، ستكون القيمة مساوية لـ 1 ، دعنا نرى القيمة التي تعرضها وظيفتنا. انقر فوق موافق.


والنتيجة محبطة. "N / A" تعني بيانات غير صالحة للوظيفة. لم نحدد قيمة في الخلية H3 ، وتبحث الدالة عن قيمة فارغة.


دعنا ندخل اسم الشهر وستتغير القيمة.


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


انقر فوق موافق. كما ترى ، تغيرت النتيجة.


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


انقر فوق موافق". كما ترى ، تبين أن نتيجة البحث هي 0 ، على الرغم من أن الجدول يحتوي على قيمة 85٪.


VLOOKUP في Excel "يفهم" القيم الثابتة فقط.

مقارنة بيانات جدولين Excel

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

على ورقتين ، لدينا نفس الجداول ببيانات مختلفة.

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

دعنا ندخل وظيفة VLOOKUP في الخلية B2. كوسيطة أولى ، سنشير إلى الخلية التي تحتوي على الشهر على الورقة الحالية ، وسنختار النطاق من ورقة "Shop1". لمنع النطاق من التحول عند النسخ ، اضغط على F4 بعد تحديد النطاق. هذا سيجعل الارتباط مطلقًا.


قم بتمديد الصيغة لملء العمود بأكمله.

وبالمثل ، أدخل الصيغة في العمود التالي ، حدد فقط النطاق في ورقة "Shop2".


بعد النسخ ، ستتلقى تقريرًا موجزًا ​​من ورقتين.

استبدال البيانات من جدول Excel إلى جدول آخر

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


وفي الخلية G3 ضع وظيفة VLOOKUP. نأخذ النطاق مرة أخرى من الورقة المجاورة.


نتيجة لذلك ، سيتم نسخ عمود الجدول الثاني إلى الأول.


هذه هي كل المعلومات عن غير الواضحة ، لكن وظيفة مفيدة VLOOKUP في Excel للدمى. نأمل أن تساعدك في حل المشاكل.

أتمنى لك يوما عظيما!

أمثلة على استخدام ملفات صيغ Excelتتيح لك وظيفة VLOOKUP معرفة مبدأ التشغيل وتجنب الأخطاء:

1. تلتقط الوظيفة ضبط المعلمات، باستخدام المعيار من الجدول الأول ، يشير إلى النطاق الثاني ، حيث سيتم "سحب" القيم.
2. من أعلى خلية في عمود المعايير ، تبحث في جميع القيم.
3. بعد العثور على تطابق مع المعايير المحددة ، يقوم بحساب عدد الأعمدة المحدد إلى اليمين وينتهي به الأمر في الخلية حيث توجد القيمة المطلوبة ، والتي يتم "سحبها" إلى الخلية حيث تم تحديد الصيغة.

مثال على استخدام وظيفة VLOOKUP

في المثال المعطى ، تريد معرفة تكلفة البضائع المباعة. لحسابها ، يجب أن تجد منتج الكمية والأسعار (توجد أعمدة بها بيانات في أعمدة مجاورة). في عمود فارغ بجانبه ، يكتبون معادلة منتج خليتين ويمددونها لأسفل حتى نهاية قائمة البضائع.

يمكن احتواء المعلومات الأصلية في نطاقات مختلفة وبترتيب مختلف. الجدول الأول يوضح كمية البضائع المباعة:

والثاني للأسعار:

إذا تطابقت البضائع في كلا الجدولين ، باستخدام تركيبة المفاتيح Ctrl + C و Ctrl + V ، يمكن بسهولة استبدال مؤشرات السعر بالكمية. لكن الجداول لها ترتيب مختلف للمواضع.

المعلومات المتعلقة بالعديد من المنتجات لا تتوافق مع المؤشرات المجاورة. في هذه الحالة ، يتم استبعاد إمكانية كتابة معادلة الضرب و "السحب" للأسفل لجميع المواضع.

كيفية استبدال الأسعار من الجدول الثاني بمؤشرات الكمية المقابلة من الأول ، أي سعر الموضع A إلى كمية المركز A والسعر B إلى الكمية B.

عند استخدام وظيفة VLOOKUP ، يتم "سحب" الأسعار من الجدول الثاني إلى الكمية من الجدول الأول ، وفقًا لأسماء البضائع.

إجراء:

في الجدول الأول ، تحتاج إلى إضافة عمود جديد حيث سيتم تحديد الأسعار باستخدام صيغة VLOOKUP.

يتم استدعاء وظيفة VLOOKUP من خلال معالج الوظائف أو تتم كتابتها يدويًا.

يتمثل استدعاء الخيار من خلال المعالج في تنشيط الخلية حيث سيتم الإشارة إلى الصيغة ، والنقر فوق الزر f (x) في بداية شريط الصيغة. في مربع الحوار الظاهر في المعالج ، تحتاج إلى تحديد VLOOKUP من القائمة المقترحة.

تتطلب صيغة VLOOKUP في Excel for Dummies التعبئة الصحيحة للحقول في مربع حوار معالج الوظائف:

1. يسمح لك العمود الأول "قيمة البحث" بتعيين معايير الخلية حيث ستتم كتابة الصيغة. في هذا المثال ، تحتوي الخلية على العنصر "أ".
2. السطر التالي"طاولة". عند إدخال نطاق من البيانات ، سيسمح لك بالعثور على القيم المطلوبة. على سبيل المثال ، تم استخدام الجدول الثاني مع الأسعار. لأن الأسعار "ترتفع" إلى الكمية. من المهم مراعاة الحاجة إلى احتواء العمود الموجود في أقصى اليسار (أول يسار) من النطاق المحدد على معايير بحث مماثلة. في المثال ، هذا هو العمود الذي يحتوي على اسم المنتج. ثم يتم تمييز الجدول إلى اليمين وصولاً إلى العمود الذي يحتوي على البيانات المطلوبة (الأسعار). يمكنك تمديد التحديد إلى اليمين ، لكن هذا لن يؤثر على أي شيء ، حيث سيتم تحديد العمود الذي يحتوي على المؤشرات المطلوبة بشكل فريد المعلمة التالية... من المهم أن تبدأ الجداول المحددة بعمود معيار وأن تلتقط عمود البيانات محل الاهتمام.
3. "رقم العمود" - الأرقام التي يتم من خلالها فصل الأعمدة التي تحتوي على البيانات المطلوبة (الأسعار) عن العمود بالمعايير (اسم المنتج). يبدأ العد التنازلي من عمود المعايير نفسه. إذا كان كلا العمودين في الجدول الثاني يقعان جنبًا إلى جنب ، فيجب أن تشير إلى الرقم 2 (الأول هو المعايير ، والثاني هو الأسعار). من الممكن وضع البيانات فيما يتعلق بمعايير 10 أو 20 عمودًا. لا يهم ، سيقوم Excel بإجراء الحسابات الصحيحة.
4. العمود الأخير هو "عرض الفاصل الزمني" ، حيث تتم الإشارة إلى خيارات البحث: مطابقة تامة (0) أو تقريبية (1) للمعايير. يجب عليك الآن تحديد 0 (أو FALSE).

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

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

خيار بديل هو كتابة صيغة VLOOKUP في خلية عن طريق إضافة علامة "؛" بين المعلمات.

يُسمح بوصف اسم "vpr" باستخدام أحرف صغيرة ، ولا يهم السجل.

كيف أستخدم لصق خاص؟

نتيجة لاستخدام الدالة VLOOKUP ، يظل الجدولان مرتبطين. عند إجراء تغييرات على قائمة الأسعار ، ستتغير أيضًا تكلفة البضائع المستلمة في المستودع. لتجنب هذا الموقف ، يجب عليك استخدام "لصق خاص".

• حدد العمود بالأسعار المحددة.
• انقر بزر الماوس الأيمن - "نسخ".
• بدون إزالة التحديد ، انقر بزر الماوس الأيمن - "لصق خاص".
• اضبط مربع الاختيار مقابل "القيم". نعم.

ستبقى القيم فقط في الخلايا ، وسيتم إلغاء الصيغة.

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

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

إجراء:

في قائمة الأسعار القديمة ، تحتاج إلى إنشاء عمود "أسعار جديدة".

حدد الخلية الأولى وحدد صيغة VLOOKUP. مجموعة الحجج (انظر أعلاه). على سبيل المثال:

توضح الصيغة المحددة الحاجة إلى أخذ اسم المنتج من النطاق A2: A15 ، وعرضه في "السعر الجديد" باستخدام العمود A. ثم استخدم البيانات من العمود الثاني من قائمة الأسعار الجديدة (الأسعار الجديدة) وأدخلها في الخلية C2.

يتيح لك اختيار طريقة عرض البيانات مقارنتها وتحديد الفرق في الأرقام والنسب المئوية.

صيغة VLOOKUP في Excel مع عدد من الشروط

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

للبحث عن السعر الذي تم به إحضار اللوح المضلع من JSC "Vostok" ، يجب عليك إنشاء شرطين: حسب اسم المادة والموردين.

تكمن الصعوبة الرئيسية في وجود العديد من أسماء المنتجات من مورد واحد.

تتضمن الإرشادات خطوة بخطوة عددًا من الإجراءات:

إضافة العمود الموجود في أقصى اليسار إلى الجدول (مهم!) ، والجمع بين "الموردين" و "المواد".

الجمع بين المعايير المطلوبة عن طريق القياس:

وضع المؤشر في المكان المناسب وتعيين وسيطات الصيغة:

يبحث Excel عن السعر المناسب.

دراسة مفصلة للصيغة:

1. ما الذي تبحث عنه.
2. أين تبحث.
3. ما هي البيانات التي يجب أخذها.

صيغ VLOOKUP والقوائم المنسدلة

دع بعض البيانات يتم إدخالها في شكل قائمة منسدلة. في هذا المثال ، هذه هي المواد. من المهم تكوين الوظيفة بحيث يتم عرض السعر عند تحديد اسم.

لإنشاء قائمة منسدلة ، يجب عليك تنفيذ عدد من الإجراءات:

ضع المؤشر في الخلية E8 ، حيث تخطط لوضع القائمة.

افتح علامة التبويب "البيانات". تحقق من قائمة البيانات.

حدد نوع البيانات - "القوائم". المصادر - مجموعة بأسماء المواد.

عند النقر فوق "موافق" ، سيتم إنشاء قائمة منسدلة.

يبقى تعيين وظيفة تسمح ، عند اختيار المواد ، بعرض القيم المقابلة في عمود السعر. ضع المؤشر في الخلية E9 (حيث سيتم عرض السعر).

افتح "معالج الوظائف" وحدد VLOOKUP.

الوسيطة الأولى هي "قيمة البحث" - الخلايا ذات القوائم المنسدلة. الجدول - نطاق مع اسم المادة والأسعار. العمود - 2. سيتم عرض الوظيفة بالتنسيق التالي:

يبقى الضغط على ENTER والتمتع بالنتيجة.

عندما تقوم بتغيير المادة ، يتغير السعر:

هذه هي الطريقة التي تعمل بها القائمة المنسدلة في Excel باستخدام وظيفة VLOOKUP. كل شيء يتم في الوضع التلقائي ، في غضون ثوان.

ميزات العمل باستخدام صيغة VLOOKUP

قبل استخدام وظيفة VLOOKUP في Excel ، يجب أن تتعرف على ميزاتها:

1. عند استخدام الخيار لعدد من الخلايا عن طريق تحديد صيغة في إحداها والنسخ إلى الباقي ، من المهم التحكم في نسبية المراجع وحتميتها. في VLOOKUP ، يجب أن تحتوي المعايير (الحقول الأولى) على مراجع نسبية (بدون $) ، والتي يتم تحديدها من خلال وجود معاييرها الخاصة لكل خلية. يجب أن تحتوي النطاقات على مراجع مطلقة (يشار إلى عناوين النطاق بعلامة $). خلاف ذلك ، عند نسخ الصيغ ، فإن النطاق "يطفو" لأسفل ولن يتم عرض العديد من المؤشرات في البحث ، حيث لن يكون هناك مكان للبحث.
2. يجب حساب أرقام الأعمدة المحددة في حقل "رقم العمود" الثالث عند استخدام معالج الوظيفة من عمود المعايير.
3. في حالة عدم وجود معايير في الجداول حيث يتم البحث في البيانات ، يظهر الخطأ # N / A ، مما يتسبب في صعوبات في حساب الإجماليات (المجموع ، المتوسط ​​، إلخ). لحل المشكلة ، يمكنك استخدام وظيفة SUMIF (بدلاً من VLOOKUP) أو ESLIOSHIBKA (وضعها قبل VLOOKUP).
4. عند استخدام القيم الرقمية بدلاً من المعايير (الرموز وأرقام المقالات) ، زادت صيغة VLOOKUP من الحساسية لتنسيقات الخلايا. إذا كانت هناك معايير بتنسيق رقمي في أحد الجداول ، وفي تنسيق نصي في جدول آخر ، فعندئذ إذا كانت المؤشرات متطابقة تمامًا ، فسيتم عرض الخطأ # N / A. يكفي التحقق مما إذا كان تنسيق الحقل يطابق المعايير وجعلها متطابقة ، أو استخدام وظيفة SUMIF (لا يهم التنسيق بالنسبة لها).
5. لا يوصى باستخدام معايير طويلة لتقليل احتمالية وجود اختلافات "عشوائية". على سبيل المثال ، سيؤدي وجود مسافة إضافية بين الكلمات أو حرف خاطئ واحد إلى عدم إمكانية المقارنة بين قيم المعايير نفسها. لا بأس بأرقام العناصر أو الرموز الشريطية للمنتج ، ولكن لا يوصى باستخدام الأسماء متعددة الكلمات للمعايير.
6. وظائف VLOOKUP من الجدول بالبيانات المطلوبة تعطي المؤشر الأول من الأعلى. إذا كان في الجدول الثاني ، حيث يتم "سحب" البيانات ، يوجد عدد من الخلايا بنفس المعايير ، فسيتم التقاط المؤشر الأول من الأعلى ضمن نطاق VLOOKUP المحدد. هذا مهم للنظر فيه. على سبيل المثال ، إذا كان من الضروري تشديد الكمية من نطاق آخر إلى سعر البضائع ، وهناك يتكرر هذا المنتج في عدة أعمدة ، فإن المؤشرات الأولى (الكمية) من الأعلى ستظهر على السعر ، وستبقى أخرى تجاهله.
7. مطلوب وجود المعلمة الأخيرة في شكل الرقم 0 (صفر). خلاف ذلك ، تعمل الصيغة بشكل ملتوي.
8. بعد استخدام وظيفة VLOOKUP ، يوصى بحذف الصيغة على الفور ، مع ترك النتائج التي تم الحصول عليها فقط. يتطلب الإجراء إبراز النطاق مع النتائج التي تم الحصول عليها ، باستخدام زر "نسخ" وتعيين القيم إلى هذا المكان باستخدام ملحق خاص... عند وضع الجداول في مصنفات Excel مختلفة ، سيكون الفصل أكثر ملاءمة علاقات خارجية(مع ترك البيانات) باستخدام الأداة ، ضمن البيانات → تحرير الروابط.

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

سيؤدي هذا إلى إزالة جميع الروابط الخارجية مرة واحدة.

1. خيار VLOOKUP البديل هو خيار HLOOKUP. الفرق هو العرض الأفقي لقائمة البيانات.

بالتأكيد كثير المستخدمين النشطين محرر جداول البياناتكان على Excel بشكل دوري التعامل مع المواقف التي أصبح من الضروري فيها استبدال القيم من جدول إلى آخر. فقط تخيل أن منتجًا معينًا قد دخل إلى المستودع الخاص بك. لدينا ملفان تحت تصرفنا: أحدهما يحتوي على قائمة بأسماء البضائع المستلمة ، والثاني - قائمة أسعار هذا المنتج بالذات. بعد فتح قائمة الأسعار ، وجدنا أن هناك المزيد من المراكز فيها ولا توجد في نفس التسلسل كما في الملف مع قائمة الأسماء. بالكاد سيحب أي منا فكرة فحص كلا الملفين ونقل الأسعار من مستند إلى آخر يدويًا. بالطبع ، في الحالة التي نتحدث فيها عن 5-10 مواضع ، يكون إدخال البيانات ميكانيكيًا ممكنًا تمامًا ، ولكن ماذا لو تجاوز عدد العناصر 1000؟ في هذه الحالة ، سيساعدنا Excel ووظيفة VLOOKUP السحرية (أو vlookup ، إذا كنا نتحدث عن النسخة الإنجليزية من البرنامج) على التعامل مع العمل الرتيب.

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

لنفترض أننا بحاجة إلى ملء "جدول الطلبات" ببيانات من "قائمة الأسعار"

لذا فإننا نواجه مهمة نقل أسعار البضائع الموجودة إلى جدول بأسمائها وحساب التكلفة الإجمالية لكل منتج. للقيام بذلك ، يتعين علينا تنفيذ الخوارزمية التالية:

1. أولا ، أحضر جداول البيانات إكسلبالشكل الذي تحتاجه. أضف عمودين باسم "السعر" و "التكلفة" إلى مصفوفة البيانات المعدة. حدد تنسيق العملة للخلايا في نطاق الأعمدة المكون حديثًا.
2. الآن قم بتنشيط الخلية الأولى في كتلة "السعر" واستدعاء "معالج الوظائف". يمكن القيام بذلك عن طريق النقر فوق الزر "fx" الموجود أمام شريط الصيغة ، أو عن طريق الضغط باستمرار على مجموعة المفاتيح "Shift + F3". في مربع الحوار الذي يفتح ، ابحث عن فئة "المراجع والصفائف". نحن هنا لسنا مهتمين بأي شيء آخر غير وظيفة VLOOKUP. حدده وانقر على "موافق". بالمناسبة ، يجب أن يقال أنه يمكن استدعاء وظيفة VLOOKUP من خلال علامة التبويب الصيغ ، والتي تحتوي القائمة المنسدلة أيضًا على فئة المراجع والمصفوفات.
3. بعد تنشيط VLOOKUP ، ستفتح أمامك نافذة بها قائمة بوسائط الوظيفة التي حددتها. في حقل "قيمة البحث" ، ستحتاج إلى إدخال نطاق البيانات الموجود في العمود الأول من الجدول مع قائمة البضائع المستلمة وكميتها. أي أنك تحتاج إلى إخبار Excel بما يجب أن يجد بالضبط في الجدول الثاني ونقله إلى الأول.
4. بعد الإشارة إلى الوسيطة الأولى ، يمكنك الانتقال إلى الثانية. في حالتنا ، يمثل الجدول الذي يحتوي على قائمة أسعار الوسيطة الثانية. ضع مؤشر الماوس في حقل الوسيطة وانتقل إلى الورقة مع قائمة الأسعار. حدد يدويًا نطاقًا به خلايا موجودة في منطقة الأعمدة بأسماء المنتجات التجارية وأسعارها. أخبر Excel بالقيم المراد تعيينها لوظيفة VLOOKUP.
5. حتى لا يتم الخلط بين Excel والإشارة إلى البيانات التي تحتاجها ، من المهم إصلاح الرابط المعطى له. للقيام بذلك ، حدد القيم المطلوبة في حقل "الجدول" واضغط على المفتاح F4. إذا تم كل شيء بشكل صحيح ، يجب أن تظهر علامة $ على الشاشة.
6. ننتقل الآن إلى حقل الوسيطة "رقم الصفحة" ونعطيه القيمة "2". تحتوي هذه الكتلة على جميع البيانات التي يجب إرسالها إلى ورقة العمل الخاصة بنا ، وبالتالي من المهم تعيين قيمة خاطئة إلى "عرض الفاصل الزمني" (اضبط الموضع على "FALSE"). يعد ذلك ضروريًا حتى تعمل وظيفة VLOOKUP فقط مع القيم الدقيقة ولا يتم تقريبها.

الآن وقد تم الانتهاء من جميع الإجراءات اللازمة ، علينا فقط تأكيدها بالضغط على الزر "موافق". بمجرد أن تتغير البيانات الموجودة في الخلية الأولى ، سنحتاج إلى تطبيق وظيفة VLOOKUP على مستند Excel بأكمله. للقيام بذلك ، يكفي ضرب VLOOKUP في عمود "السعر" بأكمله. يمكن القيام بذلك عن طريق سحب الزاوية اليمنى السفلية للخلية بالقيمة المتغيرة إلى أسفل العمود. إذا نجح كل شيء ، وتغيرت البيانات كما نحتاج ، يمكننا البدء في حساب التكلفة الإجمالية لبضائعنا. لتنفيذ هذا الإجراء ، نحتاج إلى إيجاد حاصل ضرب عمودين - "الكميات" و "الأسعار". نظرًا لأن Excel يحتوي على جميع الصيغ الرياضية ، يمكن توفير الحساب إلى "شريط الصيغة" باستخدام رمز "fx" المألوف بالفعل.

نقطة مهمة

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

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

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

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

كيف تقارن بين جدولين في إكسيل؟

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

القدرة على العمل بشروط متعددة

ميزة أخرى لا شك فيها لوظيفة VLOOKUP هي قدرتها على العمل مع العديد من المعلمات المتأصلة في منتجك. للعثور على منتج بخاصيتين أو أكثر ، يجب عليك:

1. قم بإنشاء اثنين (أو ، إذا لزم الأمر ، أكثر) من مصطلحات البحث.
2. أضف عمودًا جديدًا ، يتم من خلاله ، أثناء تشغيل الوظيفة ، إضافة جميع الأعمدة الأخرى ، والتي يتم من خلالها البحث عن المنتج.
3. في العمود الناتج ، وفقًا للخوارزمية أعلاه ، أدخلنا الصيغة المألوفة بالفعل لوظيفة VLOOKUP.

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

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

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

مثال 1

على سبيل المثال ، تُظهر الصورة أدناه قائمة من 10 ألقاب ، كل لقب له رقمه الخاص. مطلوب استخراج اللقب على الرقم المحدد.

استخدام الوظيفة VLOOKUPمن السهل جدًا القيام بذلك:

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

مثال 2

لنأخذ مثالاً آخر. تُظهر الصورة أدناه نفس 10 أسماء كما كانت من قبل ، فقط الأرقام تأتي مع تصاريح.

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

النقطة هي أن الوظيفة VLOOKUPيحتوي أيضًا على وسيطة رابعة ، والتي تسمح لك بتعيين ما يسمى بفحص الفاصل الزمني. يمكن أن يكون لها معنيين: TRUE و FALSE. علاوة على ذلك ، إذا تم حذف الحجة ، فهذا يعادل الحقيقة.

في الحالة التي تكون فيها الوسيطة الرابعة هي TRUE ، تبحث الدالة أولاً عن تطابق تام ، وإذا لم يكن هناك تطابق من هذا القبيل ، فإن أقرب واحد هو أقل من المحدد. هذا هو سبب الوظيفة VLOOKUPعاد اللقب "بانتشينكو". إذا قدمنا ​​"008" ، لكانت الصيغة قد أعادت "Panchenko" أيضًا.

في حالة عندما تكون الوسيطة الرابعة للدالة VLOOKUPلها القيمة المنطقية FALSE ، تبحث الدالة عن تطابق تام. على سبيل المثال ، في الشكل أدناه ، ستعرض الصيغة خطأً لأنه لم يتم العثور على تطابق تام.

إذا كانت الوسيطة الرابعة للدالة VLOOKUPتساوي TRUE أو تم حذفها ، يجب فرز العمود الموجود في أقصى اليسار بترتيب تصاعدي. إذا لم يتم ذلك ، فإن الوظيفة VLOOKUPقد تعيد النتيجة الخاطئة.

بالنسبة لأولئك الذين يحبون إنشاء جداول غير رأسية ، ولكن أفقية ، هناك نظير في Excel VLOOKUPولكن للبحث الأفقي.

أفقي VLOOKUP في Excel

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

إذا قمت بتمثيل المثال أعلاه بشكل أفقي ، فستبدو الصيغة كما يلي:

كما ترى ، كل شيء بسيط للغاية!

بهذا نختتم درسنا. اليوم التقينا ، على الأرجح ، بأداة Microsoft Excel الأكثر شيوعًا - وظيفة VLOOKUPوحللت قدراتها لعدة أمثلة بسيطة... آمل أن يكون هذا البرنامج التعليمي مفيدًا. كل التوفيق والنجاح في تعلم Excel.

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

تعد وظيفة VLOOKUP () واحدة من أكثر الوظائف المستخدمة في EXCEL ، لذلك دعونا نلقي نظرة عليها بالتفصيل.

في هذه المقالة ، تم اتباع نهج غير قياسي: التركيز ليس على الوظيفة نفسها ، ولكن على تلك المهام التي يمكن حلها بمساعدتها.

بناء جملة الوظيفة

VLOOKUP (lookup_value ؛ جدول ؛ عمود رقم ؛ نطاق_عرض)

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

طاولة -مرجع إلى نطاق من الخلايا. يتم البحث عن العمود الأيسر من الجدول ابحث عن القيمة، ومن الأعمدة الموجودة على اليمين ، يتم عرض النتيجة المقابلة (على الرغم من أنه ، من حيث المبدأ ، يمكنك إخراج القيمة من العمود الأيسر (في هذه الحالة ستكون ابحث عن القيمة)). غالبًا ما يتم استدعاء العمود الأيسر مفتاح... إذا كان العمود الأول لا يحتوي على ابحث عن القيمة , # N / أ.

رقم العمود- رقم العمود الجداولمن خلالها إخراج النتيجة. يحتوي العمود (مفتاح) الموجود في أقصى اليسار على الرقم 1 (يتم البحث عنه).

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

تتناول المقالة أدناه المشكلات الشائعة التي يمكن حلها باستخدام وظيفة VLOOKUP ().

مهمة 1. دليل البضائع

دع الجدول الأصلي معطى (انظر. مثال مرجع ورقة الملف).

المهمة هي تحديد المطلوب رمز البائعالمنتج ، اسحبه اسمو السعر.

ملحوظة... هذه مشكلة "كلاسيكية" لاستخدام VLOOKUP () (راجع المقالة).

للانسحاب الأسماءاستخدم الصيغة = VLOOKUP (E9 دولارات أمريكية ، 13 دولارًا أستراليًا: 19،2 دولارًا كنديًا ، خطأ)أو = VLOOKUP (E9 دولارات أمريكية ؛ 13 دولارًا أستراليًا: 19 دولارًا كنديًا ؛ 2 ؛ صحيح)أو = VLOOKUP ($ E9؛ $ A $ 13: $ C $ 19؛ 2) (أي قيمة المعلمة Interval_viewيمكن ضبطها على FALSE أو TRUE ، أو حذفها تمامًا). قيمة المعلمة العمودتحتاج إلى ضبط = 2 ، لأن رقم العمود اسميساوي 2 (عمود المفتاح هو رقم 1 دائمًا).

للانسحاب الأسعاراستخدم صيغة مماثلة = VLOOKUP (E9 دولارات أمريكية ؛ 13 دولارًا أستراليًا: 19 دولارًا كنديًا ؛ 3 ؛ خطأ)(قيمة المعلمة العموديجب تعيين = 3).

يحتوي عمود المفتاح في حالتنا على أرقام ويجب ضمان احتوائه على القيمة المطلوبة (حالة المشكلة). إذا كان العمود الأول لا يحتوي على العنصر الذي تبحث عنه , ثم تقوم الدالة بإرجاع قيمة الخطأ # N / أ.يمكن أن يحدث هذا ، على سبيل المثال ، مع وجود خطأ إملائي عند إدخال رقم المقالة. من أجل عدم ارتكاب خطأ عند إدخال المقالة المطلوبة ، يمكنك استخدام (انظر الخلية ه 9 ).

من الواضح أن عمود المفتاح في مهمتنا يجب ألا يحتوي على نسخ مكررة (هذا هو معنى المقال الذي يحدد المنتج بشكل فريد). خلاف ذلك ، سيتم عرض أعلى قيمة.

عند حل مثل هذه المشكلات ، يكون عمود المفتاح أفضل مسبقًا (وهذا سيساعد أيضًا في القيام به قائمة منسدلةاكثر وضوحا). أيضًا ، في حالة وجود قائمة لم يتم فرزها ، VLOOKUP () مع المعلمة Interval_viewلن تعمل TRUE (أو تم حذفها).

أيضًا ، يتم النظر في الصيغ البديلة (سنحصل على نفس النتيجة) باستخدام وظائف INDEX () و SEARCH () و LOOKUP (). إذا لم يكن عمود المفتاح (العمود الذي يحتوي على وحدات SKU) في أقصى اليسار في الجدول ، فإن وظيفة VLOOKUP () غير قابلة للتطبيق. في هذه الحالة ، تحتاج إلى استخدام صيغ بديلة. مجموعة من الوظائف INDEX () ، SEARCH () تشكل ما يسمى ب "حق VLOOKUP": = فهرس (B13: B19 ؛ بحث (9 دولارات أمريكية ؛ 13 دولارًا أستراليًا: 19 دولارًا أستراليًا) ؛ 1)

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

ملحوظة... لا تستخدم أبدًا VLOOKUP () مع المعلمة Interval_view TRUE (أو تم حذفها) إذا لم يتم فرز عمود المفتاح بترتيب تصاعدي. نتيجة الصيغة غير متوقعة (إذا وجدت الدالة VLOOKUP () قيمة أكبر من القيمة المطلوبة ، فإنها تُخرج القيمة التي تقع فوقها بسطر واحد).

المهمة 2. إيجاد أقرب رقم

لنفترض أنك تريد العثور على منتج يكون سعره مساويًا أو أقرب إلى السعر المطلوب.

لاستخدام وظيفة VLOOKUP () لحل هذه المشكلة ، يجب أن تفي بعدة شروط:

1. يجب أن يكون عمود المفتاح المراد البحث فيه هو العمود الموجود في أقصى اليسار في الجدول ؛
2. يجب فرز عمود المفتاح بترتيب تصاعدي ؛
3. قيمة المعلمة Interval_viewيجب تعيينها على TRUE أو حذفها تمامًا.

للانسحاب اسم العنصراستخدم الصيغة = VLOOKUP ($ A7 ؛ $ A $ 11: $ B $ 17 ؛ 2 ؛ TRUE)

لعرض السعر الذي تم العثور عليه (لن يتطابق بالضرورة مع السعر المحدد) استخدم الصيغة: = VLOOKUP ($ A7، $ A $ 11: B $ 17،1، TRUE)

كما ترى من الصورة أعلاه ، تم العثور على VLOOKUP () أعلى سعر، وهو أقل من أو يساوي المعطى (انظر. مثال على ورقة ملف "ابحث عن أقرب رقم"). هذا بسبب الطريقة التي تبحث بها الوظيفة: إذا وجدت الدالة VLOOKUP () قيمة أكبر من القيمة المطلوبة ، فإنها تُخرج القيمة التي تقع فوقها بسطر واحد. نتيجة لذلك ، إذا كانت القيمة المرغوبة أقل من الحد الأدنى في عمود المفتاح ، فستقوم الوظيفة بإرجاع خطأ # N / أ.

قد لا تكون القيمة التي تم العثور عليها هي الأقرب. على سبيل المثال ، إذا حاولت العثور على أقرب سعر لـ 199 ، ستُرجع الدالة 150 (على الرغم من أن أقرب سعر هو 200). هذا مرة أخرى نتيجة لحقيقة أن الوظيفة تجد أكبر رقم أقل من أو يساوي الرقم المحدد.

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

ملحوظة... للراحة ، يتم تمييز صف الجدول الذي يحتوي على الحل الذي تم العثور عليه. يمكن القيام بذلك باستخدام الصيغة = SEARCH ($ A $ 7 ؛ $ A $ 11: $ A $ 17 ؛ 1) = LINE () - LINE ($ 10 دولارات أمريكية).

ملحوظة: إذا كان عمود المفتاح يحتوي على قيمة تطابق القيمة المطلوبة ، فإن الوظيفة مع المعلمة Interval_view= FALSE ستعيد أول قيمة تم العثور عليها تساوي القيمة المطلوبة ، ومع المعلمة = TRUE - القيمة الأخيرة (انظر الصورة أدناه).

إذا لم يكن العمود الذي يتم البحث عنه في أقصى اليسار ، فلن يساعدك VLOOKUP (). في هذه الحالة ، تحتاج إلى استخدام الدالتين SEARCH () + INDEX () أو VIEW ().