سيحل برنامج XLOOKUP الجديد من Excel محل VLOOKUP ، مما يوفر بديلاً قويًا لواحدة من أكثر وظائف Excel شيوعًا. هذه الميزة الجديدة تحل بعض قيود VLOOKUP ولها وظائف إضافية. إليك ما تحتاج إلى معرفته.
ما هو XLOOKUP؟
تحتوي وظيفة XLOOKUP الجديدة على حلول لبعض أكبر قيود VLOOKUP. كما أنه يحل محل HLOOKUP. على سبيل المثال ، يمكن أن يبدو XLOOKUP يسارًا ، وله تطابق تام افتراضيًا ، ويتيح لك تحديد نطاق من الخلايا بدلاً من رقم العمود. VLOOKUP ليس سهل الاستخدام أو متعدد الاستخدامات. نريك كيف يعمل كل شيء.
حاليًا ، يتوفر XLOOKUP فقط للمستخدمين في برنامج Insiders. يمكن لأي شخص الانضمام إلى برنامج Insiders للوصول إلى أحدث ميزات Excel بمجرد توفرها. ستبدأ Microsoft قريبًا في طرحه لجميع مستخدمي Office 365.
كيفية استخدام وظيفة XLOOKUP
دعنا نتعمق في مثال XLOOKUP أثناء العمل. خذ المثال أدناه. نريد إرجاع القسم من العمود F لكل معرّف في العمود A.
هذا مثال كلاسيكي على المطابقة التامة. تتطلب وظيفة XLOOKUP ثلاث قطع من المعلومات فقط.
تُظهر الصورة أدناه XLOOKUP بستة وسيطات ، لكن الثلاثة الأولى فقط ضرورية لمطابقة تامة. لذلك دعونا نركز عليهم:
Lookup_value: ما الذي تبحث عنه. Lookup_array: أين تبحث. Return_array: النطاق الذي يحتوي على القيمة المراد إرجاعها.
تعمل الصيغة التالية مع هذا المثال: = XLOOKUP (A2، $ E $ 2: $ E $ 8، $ F $ 2: $ F $ 8)
الآن دعنا نستكشف بعض المزايا التي يتمتع بها XLOOKUP على VLOOKUP هنا.
لا مزيد من أرقام فهرس الأعمدة
كانت الوسيطة الثالثة سيئة السمعة لـ VLOOKUP هي تحديد رقم عمود المعلومات التي سيتم إرجاعها من مصفوفة جدول. لم تعد هذه مشكلة لأن XLOOKUP يسمح لك بتحديد النطاق الذي تريد الرجوع منه (العمود F في هذا المثال).
ولا تنس أن XLOOKUP يمكنه رؤية البيانات على يسار الخلية المحددة ، على عكس VLOOKUP. المزيد عن هذا أدناه.
لم تعد لديك مشكلة الصيغة المقطوعة عند إدراج أعمدة جديدة. إذا حدث ذلك في جدول البيانات ، فسيتم تعديل الفاصل الزمني للعودة تلقائيًا.
المطابقة التامة هي الافتراضية
كان الأمر دائمًا محيرًا عند تعلم VLOOKUP لماذا اضطررت إلى إدخال مطابقة تامة.
لحسن الحظ ، يتم تعيين XLOOKUP افتراضيًا على المطابقة التامة – وهو السبب الأكثر شيوعًا لاستخدام صيغة البحث). هذا يقلل من الحاجة إلى الإجابة على الوسيطة الخامسة ويضمن أخطاء أقل من قبل المستخدمين الجدد في الصيغة.
باختصار ، يطرح XLOOKUP أسئلة أقل من VLOOKUP ، وهو أكثر سهولة في الاستخدام ، كما أنه أكثر متانة.
يمكن أن ينظر XLOOKUP إلى اليسار
تجعل القدرة على تحديد نطاق بحث من XLOOKUP أكثر تنوعًا من VLOOKUP. مع XLOOKUP ، لا يهم ترتيب أعمدة الجدول.
تم تقييد VLOOKUP بالبحث في العمود الموجود في أقصى اليسار من الجدول ثم العودة من عدد محدد من الأعمدة إلى اليمين.
في المثال أدناه ، نحتاج إلى البحث عن معرف (العمود E) وإرجاع اسم الشخص (العمود D).
يمكن أن تحقق الصيغة التالية هذا: = XLOOKUP (A2، $ E $ 2: $ E $ 8، $ D $ 2: $ D $ 8)
ماذا تفعل إذا لم يتم العثور عليه
مستخدمو وظائف البحث على دراية كبيرة برسالة الخطأ # N / A التي ترحب بهم عندما يتعذر على VLOOKUP أو وظيفة MATCH الخاصة بهم العثور على ما يحتاجون إليه. وغالبًا ما يكون هناك سبب منطقي لذلك.
لذلك ، يبحث المستخدمون بسرعة عن كيفية إخفاء هذا الخطأ لأنه غير صحيح أو مفيد. وبالطبع هناك طرق للقيام بذلك.
يأتي XLOOKUP مع وسيطته المضمنة “إذا لم يتم العثور عليها” لمعالجة مثل هذه الأخطاء. دعنا نراها في العمل مع المثال السابق ، ولكن مع معرف بها خطأ إملائي.
تعرض الصيغة التالية النص “معرّف غير صحيح” بدلاً من رسالة الخطأ: = XLOOKUP (A2، $ E $ 2: $ E $ 8، $ D $ 2: $ D $ 8، “Incorrect ID”)
استخدام XLOOKUP للبحث عن نطاق
على الرغم من أنه ليس شائعًا مثل المطابقة التامة ، إلا أن الاستخدام الفعال جدًا لصيغة البحث هو البحث عن قيمة في النطاقات. خذ المثال التالي. نريد استرداد الخصم بناءً على المبلغ الذي تم إنفاقه.
هذه المرة نحن لا نبحث عن قيمة محددة. نحتاج إلى معرفة مكان تواجد القيم الموجودة في العمود B ضمن النطاقات الموجودة في العمود E. وهذا سيحدد الخصم المكتسب.
يحتوي XLOOKUP على وسيطة خامسة اختيارية (تذكر أنه يتم تعيينها افتراضيًا على المطابقة التامة) تسمى وضع المطابقة.
يمكنك أن ترى أن XLOOKUP أكثر قدرة على المطابقات التقريبية من VLOOKUP.
من الممكن العثور على أقرب تطابق أقل من (-1) أو أقرب إلى أكبر من (1) القيمة التي تم البحث عنها. يوجد أيضًا خيار لاستخدام أحرف البدل (2) مثل؟ أو ال *. هذا الإعداد ليس قيد التشغيل افتراضيًا كما كان مع VLOOKUP.
تُرجع الصيغة في هذا المثال أقرب قيمة أقل من التي تم البحث عنها إذا لم يتم العثور على تطابق تام: = XLOOKUP (B2، $ E $ 3: $ E $ 7، $ F $ 3: $ F $ 7 ،، – 1)
ومع ذلك ، يوجد خطأ في الخلية C7 حيث تم إرجاع الخطأ # N / A (لم يتم استخدام الوسيطة “إذا لم يتم العثور”). كان من المفترض أن يؤدي هذا إلى إرجاع خصم بنسبة 0٪ لأن النفقات 64 لا تفي بمعايير أي خصم.
ميزة أخرى لوظيفة XLOOKUP هي أنها لا تتطلب أن يكون نطاق البحث بترتيب تصاعدي كما تتطلب VLOOKUP.
أدخل صفًا جديدًا أسفل جدول البحث ، ثم افتح الصيغة. قم بتوسيع المساحة المستخدمة عن طريق النقر على الزوايا وسحبها.
الصيغة تصحح الخطأ على الفور. ليست مشكلة أن يكون لديك “0” في الجزء السفلي من النطاق.
أنا شخصياً ما زلت أفرز الجدول حسب عمود البحث. وجود “0” في الجزء السفلي قد يدفعني إلى الجنون. لكن حقيقة أن الصيغة لم تنكسر هي حقيقة رائعة.
XLOOKUP يستبدل أيضًا وظيفة HLOOKUP
كما ذكرنا ، فإن وظيفة XLOOKUP موجودة هنا أيضًا لاستبدال HLOOKUP. ميزة واحدة لتحل محل اثنين. ممتاز!
وظيفة HLOOKUP هي البحث الأفقي ، وتستخدم للبحث على طول الصفوف.
ليس مشهورًا مثل شقيقه VLOOKUP ، ولكنه مفيد لأمثلة مثل المثال أدناه حيث توجد الرؤوس في العمود A والبيانات على طول الصفين 4 و 5.
يمكن أن يبحث XLOOKUP في كلا الاتجاهين – أعمدة لأسفل وأيضًا بطول الصفوف. لم نعد بحاجة إلى وظيفتين مختلفتين.
يستخدم هذا المثال الصيغة لإرجاع قيمة المبيعات للاسم الموجود في الخلية A2. يبحث على طول الصف 4 للعثور على الاسم وإرجاع القيمة من الصف 5: = XLOOKUP (A2، B4: E4، B5: E5)
يمكن أن ينظر XLOOKUP من الأسفل إلى الأعلى
عادة ما يتعين عليك البحث في قائمة للعثور على أول ظهور (غالبًا فقط) لقيمة. يحتوي XLOOKUP على وسيطة سادسة تسمى وضع البحث. يسمح لنا هذا بتبديل البحث للبدء من الأسفل والبحث عن قائمة للعثور على أحدث تكرارات للقيمة بدلاً من ذلك.
في المثال أدناه ، نريد إيجاد مستوى المخزون لكل منتج في العمود أ.
يتم ترتيب جدول البحث حسب التاريخ وهناك العديد من عمليات فحص المخزون لكل منتج. نريد إرجاع مستوى المخزون من آخر مرة تم فحصه فيها (آخر ظهور لمعرف المنتج).
توفر الوسيطة السادسة للدالة XLOOKUP أربعة خيارات. نحن مهتمون باستخدام خيار “البحث من آخر إلى أول”.
تظهر الصيغة النهائية هنا: = XLOOKUP (A2، $ E $ 2: $ E $ 9، $ F $ 2: $ F $ 9 ،،، – 1)
في هذه الصيغة ، تم تجاهل الوسيطتين الرابعة والخامسة. إنه اختياري وأردنا مطابقة تامة بشكل افتراضي.
جمع الشمل
دالة XLOOKUP هي الوريثة التي طال انتظارها لكل من دالتي LOOKUP و DELETE.
تم استخدام مجموعة متنوعة من الأمثلة في هذه المقالة لتوضيح فوائد XLOOKUP. أحدها هو أنه يمكن استخدام XLOOKUP عبر الأوراق والمصنفات وحتى مع الجداول. تم الحفاظ على الأمثلة بسيطة في المقالة لمساعدتنا على الفهم.
بسبب المصفوفات الديناميكية التي يتم تقديمها في Excel قريبًا ، يمكن أيضًا إرجاع مجموعة من القيم. هذا بالتأكيد شيء يستحق المزيد من الاستكشاف.
أيام VLOOKUP مرقمة. XLOOKUP هنا وسيصبح قريبًا صيغة البحث الفعلية.
