كل أسرار وظيفة Excel VLOOKUP للبحث عن البيانات في جدول واستخراجها إلى جدول آخر

2024 مؤلف: Malcolm Clapton | [email protected]. آخر تعديل: 2023-12-17 03:46

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

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

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

VLOOKUP هو اختصار لـ الخامس عمودي NS تفتيش. وبالمثل ، VLOOKUP - البحث الرأسي. يلمح اسم الوظيفة نفسه إلى أنه يبحث في صفوف الجدول (عموديًا - يتكرر فوق الصفوف ويصلح العمود) ، وليس في الأعمدة (أفقيًا - يتكرر فوق الأعمدة ويصلح الصف). تجدر الإشارة إلى أن VLOOKUP لديها أخت - بطة قبيحة لن تصبح بجعة أبدًا - هذه هي وظيفة HLOOKUP. تقوم HLOOKUP ، على عكس VLOOKUP ، بإجراء عمليات بحث أفقية ، ولكن مفهوم Excel (وفي الواقع مفهوم تنظيم البيانات) يعني أن الجداول تحتوي على عدد أقل من الأعمدة والعديد من الصفوف. هذا هو السبب في أننا نحتاج إلى البحث عن طريق الصفوف عدة مرات أكثر من البحث عن طريق الأعمدة. إذا كنت تستخدم وظيفة HLO كثيرًا في Excel ، فمن المحتمل أنك لم تفهم شيئًا ما في هذه الحياة.

بناء الجملة

تحتوي وظيفة VLOOKUP على أربع معلمات:

= VLOOKUP (;; [;])، هنا:

- القيمة المرغوبة (نادرًا) أو إشارة إلى خلية تحتوي على القيمة المطلوبة (الغالبية العظمى من الحالات) ؛

- الإشارة إلى نطاق من الخلايا (صفيف ثنائي الأبعاد) ، في العمود الأول (!) حيث سيتم البحث عن قيمة المعلمة ؛

- رقم العمود في النطاق الذي سيتم عرض القيمة منه ؛

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

أراهن أن العديد من أولئك الذين يعرفون وظيفة VLOOKUP غير مستقر ، بعد قراءة وصف المعلمة الرابعة ، قد يشعرون بعدم الارتياح ، لأنهم معتادون على رؤيتها في شكل مختلف قليلاً: عادة ما يتحدثون عن تطابق تام عندما البحث (FALSE أو 0) أو مسح النطاق (TRUE أو 1).

أنت الآن بحاجة إلى إجهاد وقراءة الفقرة التالية عدة مرات حتى تشعر بمعنى ما قيل حتى النهاية. كل كلمة مهمة هناك. ستساعدك الأمثلة على اكتشافها.

كيف تعمل صيغة VLOOKUP بالضبط؟

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

  

• الفورمولا الثاني. إذا تم تحديد المعلمة الأخيرة على أنها 0 ، فإن VLOOKUP تفحص العمود الأول من المصفوفة بالتسلسل وتوقف البحث فورًا عند العثور على أول تطابق تام مع المعلمة ، وإلا فإن رمز الخطأ # N / A (# N / A) يتم إرجاع.

  

سير عمل الصيغ

نوع VPR الأول

نوع VPR الثاني

النتائج الطبيعية للصيغ من النموذج الأول

1. يمكن استخدام الصيغ لتوزيع القيم عبر النطاقات.
2. إذا كان العمود الأول يحتوي على قيم مكررة وتم فرزها بشكل صحيح ، فسيتم إرجاع آخر الصفوف ذات القيم المكررة.
3. إذا كنت تبحث عن قيمة أكبر مما يمكن أن يحتوي عليه العمود الأول ، فيمكنك بسهولة العثور على الصف الأخير من الجدول ، والذي يمكن أن يكون ذا قيمة كبيرة.
4. ستعرض طريقة العرض هذه الخطأ # N / A فقط إذا لم تجد قيمة أقل من القيمة المطلوبة أو مساوية لها.
5. من الصعب فهم أن الصيغة ترجع القيم الخاطئة إذا لم يتم فرز الصفيف الخاص بك.

الضروريات الطبيعية للصيغ من النوع الثاني

إذا حدثت القيمة المطلوبة عدة مرات في العمود الأول من المصفوفة ، فستحدد الصيغة الصف الأول لاسترداد البيانات اللاحقة.

أداء VLOOKUP

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

• أحتاج إلى جهاز كمبيوتر أكثر قوة ؛
• أحتاج إلى صيغة أسرع ، على سبيل المثال ، يعرف الكثير من الناس عن INDEX + MATCH ، والذي يفترض أنه أسرع بنسبة 5-10٪.

وقليل من الناس يعتقدون أنه بمجرد بدء استخدام VLOOKUP من النوع I والتأكد من فرز العمود الأول بأي وسيلة ، ستزداد سرعة VLOOKUP 57 مرة. أكتب بالكلمات - سبعة وخمسون مرة! ليس 57٪ بل 5700٪. لقد تحققت من هذه الحقيقة بشكل موثوق.

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

مساوئ الصيغة

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

بعض جوانب تطبيق الصيغة في الحياة الواقعية

البحث عن المدى

التوضيح الكلاسيكي للبحث في النطاق هو مهمة تحديد الخصم حسب حجم الطلب.

ابحث عن السلاسل النصية

بالطبع ، لا يبحث VLOOKUP عن الأرقام فحسب ، بل يبحث أيضًا عن النصوص. يجب أن يؤخذ في الاعتبار أن الصيغة لا تميز بين حالة الأحرف. إذا كنت تستخدم أحرف البدل ، فيمكنك تنظيم بحث غامض. هناك نوعان من أحرف البدل: "؟" - يستبدل أي حرف واحد في سلسلة نصية ، "*" - يستبدل أي رقم من أي حرف.

مساحات القتال

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

تنسيق بيانات مختلف

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

= VLOOKUP (−− D7؛ Products! $ A $ 2: $ C $ 5؛ 3؛ 0) - إذا كان D7 يحتوي على نص وكان الجدول يحتوي على أرقام ؛

= VLOOKUP (D7 & "") ؛ المنتجات! دولار أسترالي 2: 5 دولارات كندية ؛ 3 ؛ 0) - والعكس صحيح.

بالمناسبة ، يمكنك ترجمة النص إلى رقم بعدة طرق في وقت واحد ، اختر:

• نفي مزدوج -D7.
• الضرب بواحد D7 * 1.
• إضافة صفرية D7 + 0.
• الرفع إلى القوة الأولى D7 ^ 1.

يتم تحويل رقم إلى نص من خلال التسلسل بسلسلة فارغة ، مما يفرض على Excel تحويل نوع البيانات.

كيفية قمع # N / A

هذا مناسب جدًا للقيام بوظيفة IFERROR.

على سبيل المثال: = IFERROR (VLOOKUP (D7؛ Products! $ A $ 2: $ C $ 5؛ 3؛ 0)؛ "").

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

مجموعة مصفوفة

غالبًا ما ينسون جعل مرجع المصفوفة مطلقًا ، وعند تمديد المصفوفة "يطفو". تذكر استخدام $ A $ 2: $ C $ 5 بدلاً من A2: C5.

من المستحسن وضع الصفيف المرجعي في ورقة منفصلة من المصنف. لا تحصل على الأرض ، وستكون أكثر أمانًا.

قد تكون الفكرة الأفضل هي إعلان هذه المصفوفة كنطاق مسمى.

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

حسنًا ، على وشك العبقرية - لترتيب المصفوفة بالشكل.

استخدام الدالة COLUMN لتحديد العمود المراد استخراجه

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

إنشاء مفتاح مركب باستخدام & "|" &

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

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