آموزش جامع تابع xlookup + ویدیو

اگر به دنبال آموزش جامع xlookup هستید در این مقاله می توانید پاسخ تمامی سوالاتی که در مورد تابع  xlookup  در ذهن شما وجود دارد یا حین کار به آن بر می خورید را پیدا کنید. همچنین تفاوت تابع xlookup  را با دو تابع vlookup و hlookup بیان می کنیم .به علاوه تکنیک هایی به شما می آموزیم که در حین کار می تواند برای شما بسیار مفید باشد. پس با ما همراه باشید.

تابع xlookup چیست؟کاربرد xlookup چیست؟

تابع xlookup تابع جدیدی است که در office 2021 اضافه شده است . البته لازم به ذکر است این تابع و چندین فرمول دیگر از Dynamic array پشتیبانی می کند و در اکسل جدید هستند . پیشنهاد ما این است که از ورژن جدید اکسل استفاده کنید چون قابلیت های این توابع جدید ، بسیار جذاب و کارا می باشند ؛ که در ادامه آموزش جامع xlookup را خواهیم داشت.

تفاوت xlookup و vlookup

در تابع vlookup که قبلا آموزش داده ایم ، برای پیدا کردن یک عنوان در جدول و نشان دادن اطلاعات مربوط به آن ، می بایست پس از وارد کردن عنوان ، کل جدول را select کنیم تا نرم افزار با استفاده از تابع vlookup در کل جدول به دنبال آن بگردد ؛ آن گاه داده ی مورد نظر مثلا مربوط به هفته اول ، هفته دوم یا غیره را نشان بدهد .

تفاوت xlookup و vlookup در این است که با توجه به توضیحات گفته شده راجع به تابع vlookup ، باید بدانیم که تابع xlookup به این شکل نیست و برای وارد کردن اطلاعات جدول در فرمول ، تنها داده های ستون مورد نظر را انتخاب می کنیم ، که در ادامه شرح خواهیم داد .

 

آموزش جامع xlookup به کمک تابع vlookup

در تصویر زیر مشاهده می کنید که اطلاعات یک پروژه در جدولی آورده شده است که داده ها برای عناوین متفاوت در هفته های متفاوت نمایش داده شده است .

در این قسمت می خواهیم تابع xlookup را با توجه به طرز کار تابع vlookup مقایسه و توضیح دهیم . برای استفاده ی تابع xlookup با توجه به آموزش جامع xlookup که در ادامه داریم ، عمل می کنیم .

مرحله اول : در سلول مورد نظر ، تابع xlookup را وارد می کنیم . سپس نشان می دهیم که سلول مورد نظر برای نمایش نتیجه ی تابع ، کدام است . که در این مثال سلول D12 را وارد می کنیم .

مرحله دوم : در مرحله دوم آموزش جامع xlookup می بایست ستون مورد نظر برای جستجو را مشخص کنیم . ستونی که قرار است عنوان مربوطه درون آن جستجو شود . سپس در این مثال در ستون C ردیف 4 تا 8 را انتخاب می کنیم . تفاوت xlookup و vlookup نیز در همین قسمت است . چون تابع xlookup از جنس Array است و فقط اطلاعات مربوط به یک ستون با یک ردیف را می پذیرد .

مرحله سوم : در این قسمت بازه ی مربوط به جستجو برای اطلاعات جدول را مشخص می کنیم ، به این معنا که در مورد عنوان مربوطه ؛ در کدام ستون جستجو کنیم تا اطلاعات بدست آوریم . همچنین در این مثال ستون هفته ی دوم مدنظر است ، پس در ستون E سلول های E4 تا E8 را انتخاب می کنیم . آن گاه داده ی مربوط به Fitup در هفته دوم یعنی عدد 111 را نشان می دهد .

تفاوت xlookup و hlookup

در این قسمت می خواهیم تفاوت xlookup و hlookup را توضیح دهیم . مانند حالت قبل یعنی استفاده از طرز کار تابع vlookup و تفاوت آن با xlookup ، این بار تابع hlookup مدنظر است . در تابع hlookup نیز به جای انتخاب و وارد کردن تمام اطلاعات جدول ؛ شامل ستون ها و ردیف ها ، می بایست تنها از ردیف مورد نظر استفاده کرد .

آموزش جامع xlookup با استفاده از ایده ی تابع hlookup

مرحله اول : سلول مورد نظر را برای نمایش اطلاعات مشخص می کنیم که در این مثال سلول D11 می باشد . آن گاه اطلاعات ورودی جدول را مشخص کرده که ردیف 3 از سلول D3 تا G3 می باشد . توجه داشته باشید که تفاوت xlookup و hlookup در این بخش نمایان می شود ، چون تنها ردیف مورد نظر را انتخاب می کنیم .

مرحله دوم : در مرحله بعدی آموزش جامع xlookup ردیف برای جستجو را مشخص می کنیم که ردیف 6 یعنی مربوط به Welding می باشد . به این منظور که در ردیف Welding می بایست جستجو کند . آن گاه عدد مورد نظر را که در این مثال عدد 104 می باشد نمایش داده می شود .

تفاوت تابع xlookup با vlookup , hlookup

در تابع vlookup و hlookup نمی توانیم رو به عقب search کنیم یعنی هرگاه ردیف یا ستون مورد نظر را انتخاب کردیم ، نمی توانیم در ردیف بالا تر یا ستون قبل تر جستجو کنیم . این بحث در تابع xlookup مطرح نیست و ما می توانیم اطلاعات مربوط به ردیف های بالا تر و یا ستون های قبل را جستجو کرده و نمایش دهیم . در ادامه آموزش جامع xlookup در رابطه با این موضوع را خواهیم داشت

کاربرد تابع xlookup برای ردیف های بالا تر

مانند قبل یعنی جستجو با تابع xlookup عمل می کنیم . منتها برای انتخاب ردیف مربوطه ی مثال ، ردیف 2 یعنی بالا تر از عناوین را انتخاب می کنیم . مشاهده خواهیم کرد جواب مورد نظر که از جستجوی ما بین اطلاعات جدول بدست آمد را نشان می دهد . پس هم برای ردیف های پایین و هم ردیف های بالا ممکن است . همچنین این مورد تفاوت xlookup و vlookup محسوب می شود .

کاربرد تابع xlookup برای ستون های قبل تر

در این قسمت نیز مانند قسمت قبل یعنی جستجو بین ردیف های قبلی ، این بار جستجو در ستون های قبل تر اتفاق می افتد . به این صورت که ستون مورد نظر برای جستجو را ستون قبل از عناوین  ، مشخص می کنیم که در این مثال ستون B می باشد . آن گاه پاسخ مورد نظر نمایش داده می شود که تفاوت xlookup و hlookup می باشد .

آموزش جامع xlookup و پشتیبانی از Dynamic Array

تابع xlookup از Dynamic Array پشتیبانی می کند . برای فهم بهتر در نرم افزار نمایش می دهیم . فرض کنید می خواهیم اطلاعات مربوط به welding را در هفته اول ، دوم ، سوم و چهارم نمایش دهیم، آن گاه می توانیم در فرمول xlookup جدول مربوطه را که شامل هفته اول تا چهارم و اعداد درون آن هاست را انتخاب می کنیم . در این صورت ۴ عدد برای نمایش وجود دارد و تابع برای هر ۴ عدد اعمال شده است و نرم افزار نشان می دهد که این ۴ مقدار به یکدیگر پیوسته و مربوط به یک تابع هستند بنابراین یک کادر دور آن ها مشخص می کند .

نکته : در سلول هایی که باید اطلاعات حاصل از تابع را نمایش دهد ، اگر عدد یا عنوان یا هر چیز دیگری قید شده باشد که جای کافی برای نمایش اعداد وجود نداشته باشد ، آن گاه نرم افزار error می دهد و به صورت SPILL نمایش می دهد . به این معناست که باید داده های اضافی را در آن ردیف حذف یا جابجا کنیم تا محل قرار گیری اطلاعات این تابع کافی باشد .

کاربرد تابع xlookup برای جستجوی دو شرطی

در قسمت دوم از آموزش جامع xlookup می خواهیم جستجوی دو شرطی با Xlookup را آموزش دهیم و در نتیجه بجای استفاده از توابع Index match می توانید، برای جستجوی دو شرطی از تابع xlookup ، استفاده خواهیم کرد .

جستجوی دو شرطی با تابع Xlookup

در نرم افزار اکسل پروژه ای داریم که اطلاعات مربوط به آن در جدولی آمده است . این جدول شامل ردیف های متفاوت و ستون های متفاوت برای هفته های کاری پروژه می باشد ، که در تصویر زیر نشان داده شده است . به عنوان مثال می خواهیم عدد مربوط به welding در هفته ی سوم را نمایش دهد. قبل از این ، برای نشان دادن چنین دستوری از توابع Index match استفاده می کردیم اما اکنون می خواهیم به کمک آموزش تابع xlookup این دستور را با تابع xlookup نمایش دهد .

استفاده از xlookup برای جستجوی دو شرطی در اکسل

مرحله اول : در سلول مورد نظر که برای نمایش نتیجه ی فرمول انتخاب کرده ایم ، تابع xlookup را وارد می کنیم . به آن دستور می دهیم که قصد داریم welding را در جدول search کند ، بنابر این سلول welding را که در این مثال C12 است را وارد می کنیم .

مرحله دوم : مرحله بعد باید مشخص کنیم که welding را در کدام ستون جستجو کند ، بنابر این B4 تا B8 را انتخاب می کنیم .

مرحله سوم : در قسمت سوم آموزش جامع xlookup می بایست به تابع دستور دهیم که اطلاعات مربوط به welding را در جدول مورد نظر یعنی از بین داده های سلول C4 تا F8 جستجو کند .

پس از این مرحله اطلاعات مربوط به ردیف welding را در ۴ سلول نمایش می دهد .اما تا اینجا ستون مورد نظر مربوط به هفته ی دلخواه پروژه را مشخص نکرده ایم که در مرحله ی بعد می بایست مشخص کرد .

مرحله چهارم : در این مرحله باید مشخص کنیم که کدام هفته مد نظر ما است تا فقط یک عدد برای هفته مورد نظر را نشان بدهد . بنابر این در این قسمت بجای آرگومان سوم که return array هست ، xlookup وارد می کنیم . درون تابع xlookup مشخص می کنیم که در کدام ردیف جستجو کند ، که ردیف C3 تا F3 را وارد می کنیم .

مرحله پنجم : در مرحله آخر ، قسمت چهارم فرمول که جدول داده هاست را وارد می کنیم ؛ و در نهایت پرانتز فرمول را می بندیم. در نتیجه ی این عمل ، تنها یک عدد مربوط به ردیف دلخواه و ستون دلخواه نمایش داده می شود که در این قسمت آموزش xlookup ، جستجوی دو شرطی با Xlookup  را بجای استفاده از توابع Index match فرا گرفتیم .

آموزش if not found در تابع xlookup

در قسمت سوم آموزش جامع xlookup ، می خواهیم با چهارمین آرگومان تابع xlookup یعنی آموزش if not found آشنا شویم . آرگومان چهارم تابع xlookup حالت اجبار ندارد و استفاده از آن به انتخاب خودمان است . تابع xlookup شامل ۶ قسمت است که ۳ آرگومان اول حالت اجبار و الزام در استفاده دارند که شامل lookup value ، lookup array و return array می باشد . پس از آن آرگومان ها انتخابی هستند که می خواهیم در این بخش با آرگومان چهارم یعنی آموزش if not found آشنا شویم

آموزش if not found

در آموزش جامع xlookup در قسمت if not found تابع ، مشخص می کنیم که فعالیت مورد نظر که search خواهد شد ، در صورتی که در جدول وجود نداشت ، آن گاه چه دستوری انجام دهد؟ مثلا به تابع دستور می دهیم که اگر عنوان فعالیت را پیدا نکرد ، در جدول دوم جستجو کند یا دستور دلخواه دیگر ، که در ادامه با نحوه ی استفاده از این تابع در آموزش if not found آشنا خواهیم شد .

آموزش if not found و استفاده از آن در اکسل

در این بخش از آموزش جامع xlookup ، به آموزش آرگومان بعدی یعنی if not found می پردازیم که طبق روال زیر می باشد :

مرحله اول : در سلول مورد نظر برای نمایش نتیجه ی تابع ، از فرمول xlookup استفاده می کنیم . و وارد می کنیم که کدام سلول را باید در جدول جستجو کند . آن گاه ستون مورد نظر برای فضای جستجو را مشخص می کنیم ؛ که در این مثال سلول B4 تا B8 می باشد .

مرحله دوم : مرحله بعد جدول داده ها را برای جستجو مشخص می کنیم که از C4 تا F8 می باشد . تا این مرحله باید اطلاعات مربوط به فعالیت مورد نظر را در ۴ سلول برای ۴ هفته نمایش دهد .

نکته : اگر فعالیت سرچ شده در جدول اول وجود نداشته باشد ، خطای N/A می دهد . به این معنا که فعالیت مورد نظر یافت نشد . برای رفع این مشکل ، آرگومان چهارم یعنی if not found به کار می آید.

مرحله سوم : برای رفع خطای N/A می بایست یک xlookup دیگر تعریف کنیم که به آن دستور دهیم اگر فعالیت را در جدول اول پیدا نکردی ؛ در جدول دوم جستجو کن . بنابر این مجددا تابع xlookup را وارد می کنیم . درون این تابع بجای اطلاعات جدول اول مانند سری قبل ، اطلاعات جدول دوم را انتخاب می کنیم

در قسمت سوم از آموزش جامع xlookup در می یابیم که هر گاه فعالیتی را وارد تابع xlookup می کنیم ، تابع در بین جدول وارد شده  جستجو می کند و در صورت وجود آن فعالیت اطلاعاتش را نمایش می دهد . اما در صورتی که فعالیت در آن جدول وجود نداشت ،  به سراغ جدول دوم می رویم و به جستجو می پردازیم تا اطلاعات مربوط را دریافت کنیم .

کاربرد تابع xlookup و آرگومان match mode در اکسل

در این بخش از آموزش جامع xlookup می خواهیم شما را با آرگومان پنجم xlookup یعنی آموزش match mode در اکسل آشنا کنیم . در قسمت های قبل با دیگر آرگومان های تابع xlookup که عبارتند از lookup value , lookup array , return array و if not found آشنا شدیم . همچنین می دانیم که ۳ آرگومان اول حالت الزام دارند ، اما از آرگومان ۴ تا ۶ اختیاری و با توجه به کاربرد آن استفاده می شوند . در این مقاله قصد داریم match mode را آموزش دهیم .

آموزش جامع xlookup و آرگومان match mode

تصور کنید در پروژه ای ، اسامی افراد مختلف در دسترس است که میزان سود این افراد از پروژه را به دست آورده ایم . اکنون می خواهیم با توجه به سود آن ها ، پاداش برایشان در نظر بگیریم . پس اسامی افراد و میزان سود دریافتی آن ها در یک جدول آورده شده است و پاداش مربوط به بازه های متفاوت در جدولی دیگر نمایش داده شده است .

می بایست با آموزش جامع xlookup به نرم افزار دستوری بدهیم تا با توجه به میزان سود هر فرد ، که بین کدام بازه قرار گرفته است ؛ پاداش مربوط به آن بازه را به فرد اختصاص دهد . در شکل زیر چنین تصویری از پروژه را مشاهده می کنید .

در این تصویر نشان می دهد که اگر میزان سود هر فرد بین 40000 تا 50000 باشد ، به او پاداش 2000 تعلق می گیرد . اگر میزان سود بین 30000 تا 40000 باشد ، میزان پاداش 1000 تعلق می گیرد و به همین ترتیب ادامه خواهد داشت . در ادامه ی بحث می خواهیم با استفاده از تابع xlookup این موضوع را مشخص کنیم .

استفاده از match mode در اکسل ، در تابع xlookup

در این قسمت از آموزش جامع xlookup با ما همراه باشید تا کاربرد تابع xlookup و match mode را یاد بگیریم .

مرحله اول : در ستون پاداش جدول اول ، تابع xlookup را تعریف می کنیم . مرحله اول باید تعریف کنیم که سلول مورد نظر میزان سود را سرچ کند ، بنابر این سلول C3 را وارد می کنیم .

مرحله دوم : در قسمت lookup array مشخص می کنیم که این میزان سود را در کدام ستون جستجو کند . بنابر این ستون شروع بازه را باید مشخص کرد که سلول های F3 تا F7 می باشد . سپس با استفاده از دکمه $ یا دکمه f4 داده ها را fix می کنیم که برای ردیف های بعدی هم اعمال کنیم .

مرحله سوم : در این مرحله مشخص می کنیم که کدام ستون را برگرداند که باید ستون میزان پاداش جدول دوم را وارد کنیم . بنابر این سلول H3 تا H7 می باشد و سپس داده ها را fix می کنیم . قسمت بعد تابع xlookup ، قسمت if not found می باشد ، که در محتوای قبلی آموزش جامع xlookup یاد گرفته ایم و در این قسمت خالی می گذاریم و با یک کامای دیگر به مرحله بعد می رویم .

مرحله چهارم : قسمت پنجم تابع xlookup ، آرگومان match mode می باشد . قسمت match mode در اکسل ، خود شامل چند بخش است که بخش اول آن exact match مشخص می کند دقیقا چیزی که پیدا کردی ، روبرویش هر اطلاعاتی بود نمایش بده . در غیر این صورت اگر مورد جستجو را پیدا نکرد هم خطای N/A می دهد .

گزینه بعد exact match or next smaller item می باشد که با علامت 1- نیز نمایش می دهد . این دستور می گوید یا همان عدد را پیدا کن و اگر یافت نشد می توانی کوچک ترین عدد نزدیک به آن را جستجو کنی و داده ی مربوط به آن را نمایش دهی .

گزینه بعد که عکس گزینه قبلی می باشد ، با علامت +۱ نمایش می دهد و می گوید یا همان عدد را نمایش بده و یا عدد بزرگترش را ، سپس عدد مقابل آن را برای نمایش انتخاب کن . گزینه چهارم را در آموزش های بعدی خواهیم داشت .

ما در مرحله چهارم می بایست گزینه ی دوم که عدد _۱ می باشد را انتخاب کنیم که با دبل کلیک بر روی آن گزینه و یا تایپ _۱ ممکن می شود . سپس پرانتز بسته می شود و فرمول کامل خواهد شد

منطق match mode در تابع xlookup

در این بخش از آموزش جامع xlookup آموختیم که برای هر فرد ، میزان سود مربوطه را بخواند سپس جستجو کند که بین کدام بازه قرار دارد . آن گاه با توجه به همان بازه پاداش مربوطه ، به آن فرد تعلق بگیرد .

مثلا برای شخص روزبه کران زاده که اولین فرد جدول می باشد، میزان سود 45000 در جدول دوم سرچ می شود و چون 45000 بین بازه 40000 تا 50000 قرار دارد ، بنابر این میزان پاداش 2000 را نمایش می دهد .

به همین ترتیب برای بقیه افراد نیز جستجو ممکن خواهد شد ، به این صورت که  فرمول نوشته شده ی ردیف اول را تا ردیف دوم آخر جدول select می کنیم . نتیجه ی کار را در شکل زیر مشاهده می کنید .

آموزش search mode در تابع xlookup

در این قسمت از آموزش تابع xlookup قصد داریم شما را با ششمین آرگومان تابع یعنی search mode در تابع xlookup و کارایی آن آشنا کنیم . تا قبل از این در قسمت های قبل با آرگومان های قبلی تابع آشنا شده اید ؛ در این بخش تابع که search mode می باشد نیز مانند ۲ آرگومان قبلی ، با توجه به کاربرد ؛ دلخواه و به انتخاب شما می باشد . در ادامه می خواهیم نحوه ی استفاده از آن را آموزش دهیم.

آموزش تابع xlookup و آرگومان search mode

در این بخش با مثال کاربردی آموزش تابع xlookup ، و استفاده از search mode در تابع xlookup و کاربرد تابع xlookup را خواهیم داشت. فرض کنید پروژه ای در اختیار داریم که به صورت روزانه  فعالیت هایی که در پروژه انجام شده را ثبت کرده است ؛ که چه فعالیتی ، چه دسیپلین و در چه تاریخی با چه حجمی انجام شده است . می خواهیم در این پروژه با استفاده از تابع xlookup ، یک فعالیت را جستجو کنیم و دسیپلین ، تاریخ شروع و پایان آن را مشخص کنیم .

آموزش تابع xlookup در اکسل

در شکل بالا مشاهده کردید که یک جدول با یک ردیف برای جستجو و نوشتن اطلاعات مورد نظر در پایین جدول داده های پروژه ترسیم شده است . در قسمت اول که نام فعالیت را وارد می کنیم و فرمول استفاده از تابع xlookup را باید در قسمت دوم وارد کنیم که به شرح زیر می باشد :

مرحله اول : در سلول مربوط به دسیپلین ، تابع xlookup را وارد می کنیم ، مانند آموزش تابع xlookup در قسمت های قبل ، می دانیم که باید سلول مورد نظر برای جستجو را وارد کنیم . که در این مثال سلول B18 می باشد که نام فعالیت است .

مرحله دوم : در دومین مرحله آموزش تابع xlookup  مشخص می کنیم که این فعالیت را در کدام ستون جستجو کند که B3 تا B15 را را وارد می کنیم.

مرحله سوم : در قسمت بعد برای دسیپلین و تاریخ شروع ، داده های مربوط به سلول C3 تا D15 را وارد می کنیم که همزمان دسیپلین و تاریخ شروع را به ترتیب و در جدول پایین نمایش می دهد .

نکته مهم : در قسمت آخر جدول می خواهیم تاریخ پایان فعالیت را نشان دهیم که با استفاده از آموخته های قبلی ممکن نیست . چون تمامی تاریخ ها در یک ستون داده شده اند که نمی توان تاریخ شروع و پایان را تشخیص داد مگر با استفاده از search mode در تابع xlookup که در ادامه ی آموزش تابع xlookup خواهیم داشت .

مرحله چهارم : پس در مرحله چهارم می بایست از آرگومان ششم استفاده کنیم . باید به نرم افزار دستور دهیم که برود آخرین تاریخ مربوط به فعالیت مورد نظر را پیدا کند . بنابر این در سلول تاریخ پایان از یک تابع دیگر xlookup استفاده می کنیم . تابع را وارد کرده مجددا فعالیت مورد نظر و سپس لیست کلی فعالیت ها را وارد می کنیم.

مرحله پنجم : قسمت بعد تاریخ برگردانده شده یعنی ستون تاریخ جدول را وارد می کنیم . سپس از ۳ کاما برای گذشتن از قسمت if not found و match mode میزنیم تا به سراغ قسمت search mode برویم .

مرحله ششم : مرحله آخر ، استفاده از search mode است که شامل چند گزینه می باشد . گزینه اول search first to last که به ترتیب از اول نشان می دهد که برای استفاده ی ما در این پروژه مناسب نیست .

گزینه ی بعد search last to first می باشد که گزینه آخر را پیدا کرده و نشان میدهد . پس همین گزینه را با تایپ کردن 1- وارد می کنیم . که تاریخ پایان را نشان می دهد ، در واقع تاریخ آخرین مورد که فعالیت Welding می باشد را نمایش می دهد .

بنابر این در نتیجه ی کار آموزش تابع xlookup و استفاده از search mode تاریخ پایان هر فعالیت را هم می توانیم به این صورت پیدا کرده و نمایش دهیم .

آموزش تابع xlookup برای تهیه داشبورد های مدیریتی

در قسمت آخر آموزی تابع xlookup در اکسل ، می خواهیم شما را با کاربرد تابع xlookup در تهیه و طراحی داشبورد های مدیریتی آشنا کنیم . تصور کنید یک سری داده ای از پروژه استخراج شده است در اختیار دارید، که به تفکیک شماره صورت وضعیت ها در ستون Invoices ، مقادیر هزینه ی دسیپلین های متفاوت را نشان داده است و در مجموع حاصل جمع مقادیر را در ستون total مشخص کرده است . می خواهیم برای هر صورت وضعیت ، مبلغ پر سودترین دسیپلین و نام پر سودترین دسیپلین، همچنین تعداد دسیپلین های بالای ۵۰ میلیون را مشخص کنیم.

داشبورد های مدیریتی و آموزش توابع در اکسل

در آموزش تابع xlookup می خواهیم جدول پایین که در تصویر نشان داده است را تکمیل کنیم . در ردیف اول می بایست پر سود ترین دسیپلین را مشخص کرده و تنها مبلغ آن را نشان دهیم. برای اینکار به روش زیر عمل می کنیم:

مرحله ۱ : در سلول اول تابع xlookup را وارد می کنیم. سپس سلول C11 را بعنوان داده اولیه وارد می کنیم. آن گاه مشخص می کنیم که این عنوان را مابین ستون B جستجو کند. بنابر این B3 تا B9 را وارد می کنیم.

مرحله ۲ : در مرحله دوم مشخص می کنیم که اطلاعات تابع را از کدام جدول بگیرد. که در این مثال، جدول C3 تا I9 می باشد. سپس اطلاعات ورودی را با $ حتما fix می کنیم.

تا این مرحله تمام داده ها را بصورت Dynamic در یک ردیف نشان می دهد. اما این داده ها مربوط به بیشترین سود هر Inv نیست. برای اینکار باید قبل از تابع xlookup از یک max استفاده کنیم که در مرحله بعد انجام می دهیم.

مرحله ۳ : قبل از تابع xlookup ، از یک max برای نشان دادن ماکزیمم اطلاعات هر ردیف از جدول مشخص می کنیم. سپس فرمول سلول اول را برای همه سلول های همان ردیف امتداد می دهیم. مطابق با تصویر زیر مشاهده می کنید که ماکزیمم هر Inv در ستون مربوط به خودش نوشته شده است.

کاربرد تابع xlookup برای نمایش پرسودترین دسیپلین

در این بخش از آموزش تابع xlookup از آموزش توابع در اکسل می خواهیم ردیف دوم جدول که مربوط به پرسودترین دسیپلین است را تکمیل کنیم. پس با توجه به ردیف قبل که مبلغ پرسودترین را مشخص کردیم، اکنون می بایست نام فعالیت را مشخص کنیم.

مرحله ۱ : در سلول اول این بخش از جدول ؛ تابع xlookup را وارد می کنیم. سپس سلول C12 را برای داده ی اولیه وارد می کنیم. آن گاه ردیف مربوط به آن که C3 تا I3 می باشد را وارد تابع می کنیم.

مرحله ۲ : سپس نام فعالیت را باید وارد کنیم که در ردیف بالای جدول قرار دارد. بنابراین C2 تا I2 را وارد می کنیم و داده ها را fix می کنیم.

مرحله ۳ : برای اینکه فرمول برای تمام Inv ها اعمال شود ، لازم است که بجای وارد کردن C12 بصورت مستقیم، فرمولی که برای سلول C12 نوشتیم را کپی کنیم. پس در این مرحله فرمول را کپی کرده و بجای C12 در فرمول جدید کپی می کنیم.

مرحله ۴ : در آرگومان دوم فرمول، باید مشخص کنیم هرگاه Inv عوض شد، همان ردیف مربوط به آن را در فرمول وارد کند. بنابراین بجای این آرگومان، مجددا از xlookup استفاده می کنیم ؛ که همچنین به آن مسیرِ مربوط به جدول را می دهیم. که در تصویر زیر مشاهده می کنید.

بنابر مراحل قبل نام فعالیت ها برای هر دسیپلین در Inv های متفاوت نمایش داده می شود که در شکل زیر نمایش داده شده است.

تهیه گزارش جدید در آموزش تابع xlookup

در قسمت بعدی از آموزش تابع xlookup می خواهیم گزارش تهیه کنیم که در آن تعداد فعالیت هایی که در هر Inv ، سود بیشتر از ۵۰ میلیون دارند را نمایش دهد. پس به صورت زیر عمل می کنیم :

مرحله ۱ : در سلول اول، تابع count if را وارد می کنیم. در آرگومان اول که range می باشد، تابع xlookup را وارد می کنیم. پس یکی دیگر از کاربرد توابع xlookup در تابع count if می باشد.

مرحله ۲ : در آرگومان تابع داده های مربوط به تابع xlookup را وارد می کنیم که در شکل زیر نمایش می دهد.

مرحله ۳ : مرحله سوم باید آرگومان دوم که criteria نام دارد را مشخص کنیم که عبارت بزرگتر از 50000000 را وارد می کنیم.

سپس این فرمول را برای تمام ستون ها اعمال می کنیم، مشاهده می کنید که تعداد دسیپلین های بالای ۵۰ میلیون را نمایش می دهد.

پس با استفاده از آموزش توابع xlookup می توانید انواع و اقسام گزارشات را استخراج کنید و در تهیه ی داشبورد های مدیریتی از آن ها استفاده کنید.