How to use the new Excel Lookup function

قليلاً من العمود A ، والقليل من العمود B: XLOOKUP هي الطريقة الجديدة والأسهل لاسترداد المعلومات عبر جداول بيانات Excel.

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

ولكن إذا قمت بذلك مرة واحدة فقط في العام ، فربما يتعين عليك البحث عن كيفية استخدام وظيفة VLOOKUP في Excel.

بالنسبة للوظيفة الثالثة الأكثر استخدامًا في Excel (بعد SUM و AVERAGE) ، فإن VLOOKUP معقدة. بشكل افتراضي ، يجد التطابقات التقريبية ، على الرغم من أن هذا نادرًا ما تريده ، لذلك عليك أن تتذكر تعيين FALSE في الوظيفة. وهو ليس مرنًا: إذا قمت بإضافة عمود أو حذفه في الورقة حيث تبحث عن الأرقام ، فلن يتم تحديث الصيغة بالطريقة التي تعمل بها لوظائف مثل SUM ، لذلك عليك تغيير صيغة VLOOKUP يدويًا. يجب عليك ترتيب البيانات المصدر بحيث يكون عمود الفهرس ، مثل التاريخ ، على اليسار والقيم التي تريدها ، مثل المعدل ، على اليمين ؛ لا يمكنك العثور على اليوم الذي كان فيه التبادل هو الأسوأ أو الأفضل دون الحصول على نسخة ثانية من البيانات مع البيانات بترتيب مختلف. لا يمكنك تغيير ترتيب البحث ، وإذا كنت بحاجة إلى البحث عن المعلومات الموجودة في صفوف بدلاً من الأعمدة ، فيجب عليك استخدام وظيفة مختلفة ، HLOOKUP ، بدلاً من ذلك.

يمكن أن يكون VLOOKUP أيضًا نتيجة أداء لأنه ينشئ مصفوفة تغطي عمود الفهرس والعمود بالبيانات التي تحتاجها وأي أعمدة أخرى بينهما – على الرغم من أنك لا تهتم بالبيانات الموجودة بينهما.

excel-xlookup.jpg

الصورة: ماري برانسكومب / TechRepublic

تعمل وظيفة XLOOKUP الجديدة على إصلاح كل هذه المشكلات ، لأنها أبسط وأكثر مرونة ولن تؤدي إلى إبطاء جداول البيانات الخاصة بك بنفس الطريقة. يمكنك البحث عن النتائج من الصفوف أو الأعمدة ، ولا يلزم أن يكون عمود البيانات على يمين عمود الفهرس. يمكنك توجيه XLOOKUP إلى أعمدة متعددة واسترداد أكثر من معلومة – يمكنك البحث عن اسم الموظف والقسم الذي يعمل فيه ، على سبيل المثال. يمكنك تضمين خطأ مخصص لملئه (مثل “الاسم غير موجود”) إذا لم يتم العثور على تطابق بدلاً من الحصول على الافتراضي # N / A. يمكنك تخصيص كل من ترتيب البحث وكيفية عمل المطابقة. والنتيجة التي تحصل عليها هي مرجع ، وليس قيمة ، مما يعني أنه يمكنك تمريرها إلى صيغة أخرى.

يحتاج XLOOKUP إلى ثلاث معاملات على الأقل: المصطلح الذي تستخدمه لإجراء البحث ، ومكان البحث عن المصطلح والبيانات التي تحتاج إلى استردادها ، ومكان وضع البيانات التي يتم البحث عنها.

يمكن أن يكون المصطلح الأول ، lookup_value ، خلية بالقيمة التي تبحث عنها ، أو قيمة تكتبها في الصيغة مثل الاسم ، أو صيغة أخرى مثل الحساب. هذا هو نفسه مع VLOOKUP ، ولكن يمكن أن يكون أيضًا سلسلة من الخلايا المتعددة في مصفوفة – يبحث B1 و B2 و B3 عن القيمة التي تتطابق فيها جميع الخلايا الثلاث ، على سبيل المثال – بدلاً من مجرد خلية واحدة للمطابقة عليها.

يتعلم أكثر: أسعار وميزات Office 365 Consumer

ولكن بدلاً من مصفوفة مثل A2: D400 تخبر VLOOKUP بالبحث في الخلايا من A2 إلى A400 عن مصطلح البحث ثم استرداد القيمة من نفس الصف في العمود D ، يستخدم XLOOKUP معلمتين – lookup_array و return_array. لنفس البحث ، سيكون هذا A2: A400 و D2: D400. بدلاً من إنشاء مصفوفة تحتوي على ما يقرب من 1600 خلية ، يتعين على XLOOKUP التعامل مع أقل من 800 خلية فقط. وكلما زاد حجم جدول البيانات الذي تبحث عنه ، زاد فرق الأداء الذي يحدث.

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

إذا كنت تريد إلقاء نظرة على عدة أعمدة للقيمة المطابقة ، فقم بربطها باستخدام & – C2: C200 & E2: E200.

excel-xlookup-any-direction.jpg

يمكنك البحث عن البيانات في أي اتجاه باستخدام XLOOKUP.

الصورة: ماري برانسكومب / TechRepublic

إذا كنت بحاجة إلى معرفة الموقع الذي حقق أعلى مبيعات ، أو من يستخدم معظم حصة القرص الخاصة به أو استخدم أقل عدد من أيام الإجازة ، فاستخدم معادلة MAX أو MIN في lookup_value. هذا أكثر مرونة مما كان عليه مع VLOOKUP لأنك لست مضطرًا إلى إعادة ترتيب البيانات بحيث تكون القيمة التي تستخدمها للبحث عن المعلومات دائمًا في العمود الموجود في أقصى اليسار ؛ يمكن أن ينظر XLOOKUP إلى اليسار أو اليمين ، لأعلى أو لأسفل – ما عليك سوى تحديد الأعمدة أو الصفوف التي تريد البحث فيها.

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

إذا كنت تريد البحث عن أجزاء متعددة من المعلومات بنفس البحث ، فلا يزال بإمكانك استخدام مصفوفة ، ولكن يمكنك استرداد كل شيء ببحث واحد بدلاً من الحاجة إلى واحد لكل معلومة تريد استعادتها. لذا للبحث عن اسم الموظف في العمود C وقسمه في العمود D ، يجب عليك ملء return_array كـ C2: D400.

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

Excel-xlookup-match-mode-and-if-not-found.jpg

اجمع بين المعلمة match_mode والمعلمة if_not_found للعثور على المجموعة التي يقع فيها الرقم وشرح أي نتائج لا تتطابق مع أي مجموعة.

الصورة: ماري برانسكومب / TechRepublic

إذا كنت تريد خطأ مخصصًا مثل “الاسم غير موجود” أو “لا يوجد سعر صرف لهذا التاريخ” أو شيء يشرح بوضوح أكثر من # N / A أنه لم يكن هناك تطابق لإعادته ، ضع ذلك بين علامتي الاقتباس باعتباره الرابع المعلمة (وهو ما يسمى [if_not_found]).

عادةً ما تبحث عن الإجابة الدقيقة – اسم أو سعر الصرف ليوم معين ، على سبيل المثال. ولكن يمكنك أيضًا البحث عن مكان سقوط الرقم على المقياس ، مثل معرفة شريحة الضرائب التي يقع فيها الراتب. في هذه الحالة ، قد لا تتمكن من الحصول على تطابق تام. اضبط المعلمة الخامسة [match_mode] إلى 1 وإذا لم يكن هناك تطابق تام ، فستحصل على أكبر نتيجة تالية ؛ -1 يجلب العنصر الأصغر التالي. إذا كنت تتطابق مع راتب يقع داخل نطاق ضريبي ، فأنت تريد مطابقة النتيجة الأكبر التالية لأن نطاقات الضرائب تنطبق على الحد الأقصى للرقم. لذا يجب أن يبحث الراتب الذي يبلغ 30 ألف جنيه إسترليني عن معدل الضريبة البالغ 20٪ والذي ينطبق حتى 50 ألف جنيه إسترليني: المطابقة في النتيجة التالية الأكبر ستحصل على ذلك.

انظر: كيف تصبح مطورًا: ورقة الغش (TechRepublic)

اضبط match_mode على 2 ويمكنك استخدام برنامج Excel المعتاد؟ و * أحرف البدل لتحديد ما تريد المطابقة عليه. بهذه الطريقة يمكنك البحث عن الجنوب والجنوب الشرقي باستخدام “الجنوب *” أو الشمال الغربي والجنوب الغربي بـ “* الغرب”.

البحث الافتراضي عن XLOOKUP هو من الأعلى إلى الأسفل: اضبط المعامل السادس [search_mode] إلى -1 إذا كنت تريد البحث من أسفل القائمة حتى تجد التطابق الأول.

وظيفة لتعمل

يمكنك دمج وظائف XLOOKUP لسحب خلية من جدول بالبحث عن التسميات في الصف العلوي والعمود الأيسر: استخدم XLOOKUP الأول للعثور على التسمية الأولى وتعبئة دالة XLOOKUP أخرى كعائد_مصفوفة للعثور على التسمية الثانية و ستكون النتيجة محتويات الخلية حيث تتقاطع. (هذا مثل استخدام دالتي INDEX و MATCH لكنك لست بحاجة إلى تعلم وظيفتين أخريين.) أسهل طريقة للقيام بذلك هي أن يكون لديك نفس التسميات أعلى وبجانب الخلية حيث تريد أن ترى النتيجة واستخدام تلك تسميات للمطابقة عليها.

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

لا يزال XLOOKUP حاليًا في مرحلة تجريبية ؛ يمكنك الحصول عليه فقط إذا كنت مسجلاً في برنامج Office Insiders. قامت Microsoft برحلات XLOOKUP إلى كل المطلعين منذ أوائل سبتمبر، لكنها ربما لم تصل إلى الجميع حتى الآن ، لذا استمر في التحقق إذا كنت لا تراها. إذا كنت تستخدمه بالفعل ، فاعلم أنه قد يتغير قبل أن يتم شحنه كميزة نهائية. منذ طرح XLOOKUP لأول مرة ، تم إصدار ملف [not_found] تمت إضافة المعامل (كان في الأصل المعامل السادس ولكن بعد ذلك تم نقله ليكون المعامل الرابع ، لذلك لم يكن عليك وضع معلمات فارغة لاستخدامه).

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

انظر أيضا