آموزش کار با توابع جستجو HLOOKUP‌ و INDEX و MATCH و VLOOKUP در اکسل

گروه تحریریه بی نهآخرین به روز رسانی: 09/11/1401

خواندن این مطلب 23 دقیقه زمان میبرد

ساده‌ترین روش جستجو در Excel، فشار دادن کلید ترکیبی Ctrl + F است که مثل اغلب نرم‌افزارها، پنجره‌ی Find یا سرچ را باز می‌کند و می‌توانید عبارتی را تایپ کرده و پیدا کنید. اما برای امور پیچیده‌تر و در حالتی که دیتابیس عظیمی از اطلاعات در فایل اکسل موجود باشد، این روش مناسب نیست. بهتر است از توابعی مثل VLOOKUP‌ و HLOOKUP و MATCH و INDEX استفاده کنید.

در این مقاله به معرفی چهار تابع جستجو در اکسل مجموعه‌ی آفیس مایکروسافت و روش استفاده از این توابع می‌پردازیم.

استفاده از VLOOKUP در اکسل

کار تابع VLOOKUP در نرم‌افزار Excel این است که سلولی حاوی مقدار مشخص شده را در ستونی پیدا می‌کند و مقدار دیگری که در همان ردیف قرار گرفته را برمی‌گرداند. در واقع با VLOOKUP می‌توان به این سوال پاسخ داد که: روبروی سلولی با مقداری خاص، چه مقداری درج شده است؟

اما با دو مثال ساده شیوه‌ی استفاده از تابع VLOOKUP را بررسی می‌کنیم. البته برای اطلاعات بیشتر می‌توانید مقاله‌ای که در خصوص کار با این تابع منتشر کردیم را مطالعه کنید:

آموزش استفاده از VLookup برای جستجو کردن جداول در اکسل

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

سینتکس کلی استفاده از تابع VLOOKUP به این صورت است:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])

اما توضیحی در مورد چهار آرگومان تابع VLOOKUP:

[lookup_value] یا اولین آرگومان، ویژگی‌ای است که در مورد آن اطلاع داریم و آن را جستجو می‌کنیم.
[table_array] یا دومین آرگومان، محدوده‌ای است که برای یافتن آرگومان اول جستجو می‌شود. دقت کنید که اولین ستون از محدوده‌ی وارد شده، ستونی است که داده‌ی موردنظر شما در آن قرار دارد.
[col_index_num] یا آرگومان سوم، شماره‌ی ستونی است که اطلاعات برگشتی از آن استخراج می‌شود.
[range_lookup] یا آرگومان چهارم که وارد کردن آن ضروری نیست، دو حالت 0 و 1 دارد. عدد 0 به معنی جستجوی دقیق است در حالی که عدد 1 یا وارد نکردن آرگومان چهارم، موجب جستجوی تقریبی و یافتن نزدیک‌ترین نتیجه می‌شود.

اما توضیح بیشتر در مورد آرگومان چهارم: اگر در جدول زیر و ستون امتیاز SAT، عدد ۶۵۲ را با VLOOKUP جستجو کنید و آرگومان چهارم را 0 وارد کنید، نتیجه‌ای در کار نیست چرا که این عدد وجود ندارد. اما اگر از 1 استفاده کنید یا آرمان آخر را تایپ نکنید، عدد ۶۴۶ به عنوان نتیجه‌ی جستجو در ستون SAT در نظر گرفته می‌شود.

اما مثالی دیگر: فرض کنید می‌خواهید امتیاز شخصی که نام خانوادگی وی، Winters است را پیدا کنید. آرگومان اول همین عنوان است و آرگومان دوم، سلول‌های C2 الی F101‌ است که کل جدول را تشکیل می‌دهد. آرگومان سوم یعنی شماره‌ی ستون داده‌ی مقصد، عدد 4 است چرا که اگر ستون C حاوی نام خانوادگی را ستون شماره 1 در نظر بگیرید، ستون SAT شماره‌ی 4 می‌شود. در نهایت عدد 0 را برای آرگومان آخر تایپ می‌کنیم که جستجوی دقیق انجام شود.

و فرمول نهایی در پاسخ به این سوال ساده:

=VLOOKUP(“Winters”, C2:F101, 4, 0)

نتیجه را مشاهده می‌کنید که امتیاز 651 است، امتیازی که در ردیف مربوط به شخص موردنظر و در ستون SAT موجود بوده است.

بار دیگر یادآوری می‌کنیم که در مورد آرگومان دوم که رنج یا محدوده‌ی سلول‌ها است، از ستونی شروع کنید که در آن جستجو انجام می‌شود چرا که در غیر این صورت ممکن است تابع VLOOKUP نتایج اشتباهی را گزارش کند!

نکته‌ی بعدی این است که VLOOKUP فقط یک عدد را برمی‌گرداند. اگر در ستون جستجو شده، چند سلول حاوی مقدار موردنظر باشند، تنها مقداری که روبروی اولین سلول است، برگردانده می‌شود.

استفاده از HLOOKUP در اکسل

تابع HLOOKUP عملکردی مشابه VLOOKUP دارد با این تفاوت که مقداری در یکی از سلول‌های زیر سلول موردنظر را برمی‌گرداند.

سینتکس استفاده از تابع HLOOKUP به صورت زیر است:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])

چهار آرگومان این تابع درست شبیه به تابع VLOOKUP است. بنابراین به ترتیب مقداری که باید در اولین ردیف جستجو شود، محدوده‌ی سلول‌ها، شماره‌ی ردیف سلول مقصد و اینکه آیا جستجوی تقریبی انجام شود یا خیر، چهار آرگومان این تابع خواهند بود.

و یک مثال ساده:

اگر بخواهیم در جدولی حاوی امتیاز متوسط هر شهر در سال‌های مختلف، امتیاز شهری که در ردیف ۲۴ قرار گرفته را در سال ۲۰۱۳ پیدا کنیم، دستور زیر مناسب است:

=HLOOKUP(2013, A1:P51, 24)

نتیجه نمایش امتیاز ذکر شده در ستون ۲۰۱۳ و ردیف ۲۴ است.

البته برای این مثال خاص که تعداد ردیف‌ها بسیار کم است، می‌توانید از VLOOKUP استفاده کنید که حتی ساده‌تر است چرا که نیازی به پیدا کردن ردیف مربوط به شهر موردنظر نیست. اما اگر تعداد ستون‌ها و در این مثال خاص، تعداد سال‌ها بسیار زیاد باشد، پیدا کردن ستون مربوط به سال ۲۰۱۳ وقت‌گیر می‌شود و لذا کار با HLOOKUP گزینه‌ی بهتری خواهد بود.

نکته‌ی دیگر این است که شماره‌ی سال در این مثال به صورت “2013” ذکر نشده چرا که رشته نیست بلکه عدد است.

در استفاده از HLOOKUP هم محدوده‌ی سلول‌ها می‌بایست با اولین ردیف که ردیف جستجو است، آغاز شود تا نتیجه‌ی صحیحی به دست آید. به علاوه اولین سلول از ردیفی که جستجو می‌شود، برای یافتن داده‌ی مقصد به کار می‌رود و سلول‌های بعدی که مقدار مشابهی دارند، لحاظ نمی‌شوند.

The INDEX and MATCH Functions

توابع INDEX و MATCH در اکسل نیز برای جستجو کردن در جداول عظیم داده، بسیار مفید هستند به خصوص اگر در فرمول‌نویسی، این دو تابع را به صورت ترکیبی استفاده کنید.

قبلاً با تابعی به اسم INDEX آشنا شدیم:

آموزش کار با تابع Index اکسل برای برگرداندن مقدار یک یا چند سلول

سینتکس کلی استفاده از تابع INDEX به صورت زیر است:

=INDEX([array], [row_number], [column_number])

و معنی سه آرگومان این تابع:

[array] یا آرگومان اول رنج سلول‌ها است که جستجو در آن انجام می‌شود.
[row_number] و [column_number] نیز شماره‌ی ردیف و شماره‌ی ستون است و برای محدود کردن نتایج استفاده می‌شود.

سینتکس کار با تابع MATCH مشابه است:

=MATCH([lookup_value], [lookup_array], [match_type])

و توضیح آرگومان‌های سه‌گانه‌ی MATCH:

[lookup_value] یا آرگومان اول همان مقدار و عبارتی است که موردسوال است.
[lookup_array] یا دومین آرگومان، محدوده‌ی سلول‌هایی است که جستجو می‌شود.
[match_type] یا آرگومان سوم می‌تواند 1 یا 0 یا منفی 1 باشد و البته می‌توانید آن را وارد نکنید! عدد 1 برای برگرداندن بزرگ‌ترین نتیجه که کوچک‌تر یا مساوی عبارت سرچ شده است، عدد 0 برای برگرداندن نتیجه‌ی دقیق و عدد منفی 1 برای برگرداندن کوچک‌ترین نتیجه که بزرگ‌تر یا مساوی عبارت سرچ شده است، به کار می‌رود.

دو مثال ساده از کاربرد این توابع به صورت مجزا:

همان‌طور که در تصویر زیر مشاهده می‌کنید، با استفاده از تابع MATCH، شماره‌ی ردیفی که حاوی عدد ۶۴۶ است، مشخص شده که ردیف 4 است.

و مثالی از کاربرد تابع INDEX: همان‌طور که در تصویر زیر مشاهده می‌کنید، تابع INDEX شماره‌ی ردیفی که نام شهر Anchorage در آن قرار گرفته را عدد 6 برمی‌گرداند:

اما چطور از ترکیبی این توابع استفاده کنیم و اساساً ترکیب MATCH و INDEX چه کاربردی دارد؟

با تابع MATCH عبارتی را جستجو می‌کنیم و نتیجه که شماره‌ی ردیف است را به تابع INDEX می‌دهیم تا این تابع مقدار موجود در ستونی دیگر در همان ردیف را برگرداند.

به عنوان مثال با فرمول زیر، ردیفی که کلمه‌ی Waters‌ ستون C آن موجود است، به تابع INDEX داده می‌شود و تابع ایندکس در همین ردیف و ستون F، سلولی را پیدا کرده و مقدار آن که نمره‌ی شخصی با فامیلی Waters است را برمی‌گرداند:

=INDEX(F:F, MATCH(“Waters”, C:C, 0))

در واقع این دو تابع در صورت ترکیب، کاری شبیه VLOOKUP و HLOOKUP را انجام می‌دهند.

makeuseofسیاره‌ی ‌آی‌تی

اگر به دنبال آموزش جامع 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 جدول مربوطه را که شامل هفته اول تا چهارم و اعداد درون آن هاست را انتخاب می کنیم . در این صورت 4 عدد برای نمایش وجود دارد و تابع برای هر 4 عدد اعمال شده است و نرم افزار نشان می دهد که این 4 مقدار به یکدیگر پیوسته و مربوط به یک تابع هستند بنابراین یک کادر دور آن ها مشخص می کند .

نکته : در سلول هایی که باید اطلاعات حاصل از تابع را نمایش دهد ، اگر عدد یا عنوان یا هر چیز دیگری قید شده باشد که جای کافی برای نمایش اعداد وجود نداشته باشد ، آن گاه نرم افزار 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 را در 4 سلول نمایش می دهد .اما تا اینجا ستون مورد نظر مربوط به هفته ی دلخواه پروژه را مشخص نکرده ایم که در مرحله ی بعد می بایست مشخص کرد .

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

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

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

در قسمت سوم آموزش جامع xlookup ، می خواهیم با چهارمین آرگومان تابع xlookup یعنی آموزش if not found آشنا شویم . آرگومان چهارم تابع xlookup حالت اجبار ندارد و استفاده از آن به انتخاب خودمان است . تابع xlookup شامل 6 قسمت است که 3 آرگومان اول حالت اجبار و الزام در استفاده دارند که شامل 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 می باشد . تا این مرحله باید اطلاعات مربوط به فعالیت مورد نظر را در 4 سلول برای 4 هفته نمایش دهد .

نکته : اگر فعالیت سرچ شده در جدول اول وجود نداشته باشد ، خطای 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 آشنا شدیم . همچنین می دانیم که 3 آرگومان اول حالت الزام دارند ، اما از آرگومان 4 تا 6 اختیاری و با توجه به کاربرد آن استفاده می شوند . در این مقاله قصد داریم 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- نیز نمایش می دهد . این دستور می گوید یا همان عدد را پیدا کن و اگر یافت نشد می توانی کوچک ترین عدد نزدیک به آن را جستجو کنی و داده ی مربوط به آن را نمایش دهی .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

مرحله پنجم : قسمت بعد تاریخ برگردانده شده یعنی ستون تاریخ جدول را وارد می کنیم . سپس از 3 کاما برای گذشتن از قسمت 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 می خواهیم جدول پایین که در تصویر نشان داده است را تکمیل کنیم . در ردیف اول می بایست پر سود ترین دسیپلین را مشخص کرده و تنها مبلغ آن را نشان دهیم. برای اینکار به روش زیر عمل می کنیم:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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