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

پیشنهاد مطالعه: 

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

تابع xlookup در اکسل (تایع جدید در اکسل 2021)

قبل از شروع بحث می بایست اشاره کنیم که رفتار اکسل در این زمینه  بر اساس دو موضوع تغییر میکند: اول اینکه آیا شما به دنبال جستجوی یک مقدار دقیقا مطابق (exact match) هستید یا مطابقت جزیی(approximate match) و دوم اینکه آیا داده ها مرتب سازی(Sort) شده اند یا خیر.

پیشنهاد مطالعه: 

با توجه به پیچیدگی مطلب پیش رو و ارتباط زیاد این مطلب با تابع Vlookup پشنهاد میکنیم در صورتی که به خوبی با تابع Vlookup  آشنا نیستید مطلب زیر را مطالعه کنید:

تابع VLOOKUP در اکسل | مثال ها، کاربردها و فیلم آموزشی

اگر موضوع هنوزه برای شما مبهم است به دقت به مثال زیر دقت کنید:

فرض کنید میخواهیم از تابع Vlookup برای برگرداندن قیمت آیتم Green در داده ها زیر استفاده کنیم.به نظر شما با این کار کدام قیمت برگردانده میشود؟

 حالت exact match برای برگرداندن اولین مقدار

زمانی که از حالت exact match در پارامتر سوم تابع Vlookup استفاده میکنید(false)، همیشه اولین مقداری که یافت میشود، برگردانده میشود( حتی اگر داده ها مرتب سازی نشده باشد). در تصویر زیر، مقدار مورد جستجو(lookup value) در سلول e5 مقدار “red” است و همانطور که مشاهده میکنید اولین قیمت مربوط به red که 11 می باشد، برگردانده شده است:

=VLOOKUP(E5,data,2,FALSE)

 

حالت approximate match برای برگرداندن آخرین مقدار

اگر از حالت approximate match یا مطابقت تقریبی در تابع Vlookup استفاده میکنید و داده های خود را بر اساس ستون lookup value مرتب سازی کرده اید، آخرین مقدار مطابق برگردانده خواهد شد. چرا؟ زیرا زمانی که از approximate match استفاده می کنید، اکسل در بین داده ها جستجو میکند تا زمانیکه مقداری بزرگتر از lookup value یافت شود(یعنی تا انتهای ستون)، سپس مقدار قبل از آن(آخرین مقدار) را برمیگرداند.

در تصویر زیر، از حالت approximate match در تابع Vlookup استفاده شده است و داده ها بر اساس ستون color مرتب سازی شده است. همانطور که مشاهده میکنید، vlookup قیمت مربوط به آخرین مقدار green را برگردانده است.

=VLOOKUP(E5,data,2,TRUE)

 

توجه داشته باشید که پارامتر سوم در تابع vlookup بالا روی حالت true قرار گرفته است(approximate match)

حالت approximate match در زمانیکه داده ها مرتب سازی نشده اند = خطررررر

حالت مطابقت جزیی(approximate match) زمانی استاندارد است که داده های شما مرتب سازی شده باشد. اما زمانی که داده های خود را سورت نکرده اید و از این حالت استفاده میکنید ممکن است نتایجی کاملا اشتباه برگردانده شود. این حالت در تابع vlookup بسیار رایج است زیرا حالت پیش فرض این تابع بر روی حالت Approximate قرار گرفته و در صورتی که پارامتر چهارم را خالی بگذارید، اکسل به صورت اتوماتیک این گزینه را انتخاب خواهد کرد.

برای درک بهتر مسئله، نمونه زیر را در نظر بگیرید. داده ها مرتب سازی نشده اند و پارامتر چهارم تابه vlookup نیز قرار داده نشده است(حالت Approximate match). همانطور که در زیر مشاهده میکنید، پاسخ کاملا اشتباهی برگردانده شده است، زیرا ما هیچ مقدار”red”  با قیمت 17  در بین داده های خود نداریم.

=VLOOKUP(E5,data,2)

 

به دلیل گفته شده در بالا، همیشه پیشنهاد میکنیم پارامتر آخر تابع vlookup را به روشنی مشخص کنید:           TRUE = approximate match و  FALSE = exact match

در ادامه نحوه حل مشکل داده های مرتب سازی نشده و برگرداندن آخرین مقدار مطابق در تابع vlookup را نشان خواهیم داد.

پیشنهاد مطالعه: 

آموزش کامل مرتب سازی داده ها (Sort) در اکسل

مثال: حالت Approximate match در تابع Vlookup برای یافتن آخرین مقدار

زمانی که داده های شما مرتب سازی شده باشد میتوانید به سادگی از تابع حالت approximate برای برگردان آخرین مقدار استفاده کنید . فرض کنید محصولات زیر را در اختیار دارید و میخواهید آخرین قیمت مربوط به کالای sandals را برگردانید. همانطور که مشاهده میکنید داده ها بر اساس ستون Item  و سپس Date مرتب سازی شده اند؛ در نتیجه آخرین قیمت برای یک محصول در آخر نمایش داده میشود.

=VLOOKUP(F5,data,3,TRUE)

 

ترکیب توابع index و match

یکی از شیوه هایی که میتوان با استفاده از آن آخرین مقدار مطابق را برگرداند، ترکیب توابع index/match به جای استفاده از تابع vlookup است. توجه داشته باشید که در فرمول استفاده شده در اینجا تابع match در حالت تقریبی (approximate) قرار داده شده است(پارامتر سوم 1 قرار داده شده است) و آیتم ها به صورت صعودی مرتب شده اند.

=INDEX(price,MATCH(F5,item,1))

 

برای مطالعه بیشتر در زمینه ترکیب توابع index و match در اکسل میتوانید مطلب زیر را مطالعه کنید:

ترکیب تابع index و match در اکسل

تابع lookup

در این مورد میتوان از تابع Vlookup نیز استفاده کرد. تابع lookup همیشه به حالت Approximate استفاده می کند در نتیجه، برای برگرداندن آخرین نتیجه به خوبی عمل میکند. فرمول lookup به شکل زیر خواهد بود:

=LOOKUP(F5,item,price)

 

برگرداندن آخرین مقدار با داده های مرتب نشده

حالت هایی که گفته شد مربوط به زمانی بود که داده های شما سورت شده باشند. اما زمانی که داده ها مرتب نشده باشند و بخواهیم آخرین مقدار را برگردانیم، چه باید کرد؟ در این شرایط بهترین گزینه ای که میتوان از آن استفاده کرد تابع Lookup است. زیرا این تابع بدون استفاده از control + shift + enter به خوبی به صورت آرایه ای کار میکند. به این معنی که میتوان به صورت پویا، یک آرایه جستجو ایجاد کرد و با استفاده از عبارت های منطقی ساده، داده های مورد نظر خود را پیدا کنیم.

برای مثال، به فرمول زیر دقت کنید:

 

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

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

item=F5

نتیجه کار یک آرایه از مقادیر true و false خواهد بود. در واقع در این آرایه مقدار true مربوط به آیتم های sandals و false مربوط به آیتم های دیگر است.

{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

 

سپس 1 را تقسیم به آرایه بدست آمده میکنیم. با استفاده از این کار، مقدار true تبدیل به 1  و false تبدیل به 0 میشود. بنابراین قسمت میانی فرمول به شکل زیر خواهد بود:

1/{0;1;0;1;0;0;1;0}

 

1 تقسیم بر 1 برابر با 1 و 1 تقسیم بر 0 مساوی با #DIV/0 خواهد بود. بنابراین آرایه به صورت زیر میشود:

{#DIV/0!;1;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!}

 

شاید تاکنون بعضی از شما دقت کرده باشید که مقدار مورد جستجو(lookup value) مقدار 2 است. اما تابع lookup چگونه می بایست مقار 2 را در آرایه ای که شامل مقادیر خطا و 1 است، پیدا کند؟ در واقع تابع lookup قرار نیست مقدار 2 را یافت کند. این مقدار با این منظور استفاده میشود که تابع lookup با هدف یافتن آن، تا انتهای مقادیر را جستجو کند.

تابع lookup به صورت اتوماتیک خطاها را نادیده می گیرد در نتیجه تنها مقادیر باقیمانده 1 ها هستند. این تابع با بررسی 1 ها به دنبال 2 می گردد ولی آن را پیدا نمیکند. زمانی که به انتهای آرایه می رسد، یک گام به عقب برمیگردد و آخرین مقدار معتبر(آخرین 1) را که مربوط به آخرین مقدار مطابق بر اساس شرط مشخص شده  است برمیگرداند.

نسخه آرایه ای Index/Match

زیبایی تابع lookup این بود که به صورت درونی آرایه ای بود و نیازی به وارد کردن یک فرمول آرایه ای و استفاده از کلیدهای Ctrl+shift+Enter نیست. در اینجا از نسخه آرایه ای تابع Index/match همراه با کلیدهای Ctrl+shift+Enter استفاده میکنیم:

{=INDEX(price,MATCH(2,1/(item=F5),1))}

 

پیدا کردن آخرین سلول غیر خالی

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

=LOOKUP(2,1/(B:B<>""),B:B)

 

ترکیب توابع index، Max، If، Row و Min

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

{=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1))}

 

توضیحات

برای بدست آوردن محل آخرین مقدار مطابق با مقدار مورد جستجو، میتوانید از فرمول بالا که به صورت آرایه ای و ترکیبی از Max، If، Row و Min است، استفاده کنید. در تصویر بالا، در سلول H6 فرمول زیر وارد شده است:

{=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1))}

 

Names در فرمول بالا، محدوده C4:C11 است.

توجه داشته باشید که این فرمول به صورت آرایه ای است و می بایست پس از وارد کردن از کلیدهای control + shift + enter استفاده کنید.

این فرمول چگونه کار میکند؟

نکته این فرمول این است که در آن، برای یک محدوده مشخص لیستی از شماره ردیف ها را ایجاد کردیم و سپس با استفاده از تابع max بزرگترین شماره ردیف را برگرداندیم. در اینجا از محدوده دارای نام names صرفا برای راحتی کار استفاده شده است.

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

ROW(names)-MIN(ROW(names))+1

 

نتیجه استفاده از عبارت بالا، آرایه ای از اعداد به شکل زیر است:

{1;2;3;4;5;6;7;8}

 

توجه کنید که 8 عدد مربوط به 8 ردیف جدول است. بنا به هدفی که در این فرمول داریم، ما صرفا شماره ردیف هایی را میخواهیم که مربوط به مقدارهای مورد نظر باشد. در نتیجه از تابع IF برای فیلتر کردن مقادیر به صورت زیر استفاده میکنیم:

IF(names=H5,ROW(names)-MIN(ROW(names))+1)

 

نتیجه این قسمت آرایه ای به شکل زیر خواهد بود:

{1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE}

 

دقت کنید که آرایه هنوزه شامل 8 آیتم می باشد. با اینحال، فقط شماره ردیف هایی که مقدار مربوط به آنها در محدوده دارای نام “names” برابر با “amy” بود، باقی ماندند( یعنی 1،4،7)

در نهایت تابع IF فرمول را به تابع max تحویل میدهد. این تابع بزرگترین مقدار آرایه یعنی 7 را که متعلق به آخرین تکرار از amy می باشد برمیگرداند. حال که محل آخرین مقدار مطابق را میدانیم میتوانیم با استفاده از تابع index مقدار قرار گرفته در آن محل را برگردانیم.

=VLOOKUP(1,CHOOSE({1,2},(A2:A9=D2)*(B2:B9=11),B2:B9),2,FALSE)