توابع MATCH و INDEX در اکسل

توابع MATCH و INDEX اغلب همراه با هم برای انجام عملیات جست وجو استفاده می شوند. تابع MATCH، موقعیت نسبی یک سلول در دامنه که با یک مقدار مشخص تطبیق دارد را برمی گرداند. ساختار دستوری تابع MATCH به صورت زیر است:

MATCH ( lookup_value, lookup_array, match_type )

آرگومان های تابع MATCH به شرح زیر است:

:lookup_valueمقداری که می خواهید در lookup_array به دنبال آن بگردید. اگر مقدار match_type برابر صفر و مقدار lookup_value یک عبارت متنی باشد، این آرگومان می تواند شامل کاراکترهای *و ؟ باشد.

:lookup_arrayدامنه ای که جست وجو در آنجا انجام می شود.

:match_typeعدد صحیح (1-، صفر یا 1 ) که مشخص می کند عملیات تطبیق چگونه اجرا می شود.

اگر مقدار match_typeبرابر 1 باشد تابع MATCH، بزرگ ترین مقداری که کمتر یا مساوی با lookup_valueباشد را پیدا می کند و lookup_array  باید به صورت صعودی مرتب شده باشد.

اگر مقدار match_typeبرابر با صفر باشد تابع MATCH، اولین مقداری که دقیقا مساوی با lookup_value باشد را پیدا می کند.

اگر مقدار match_type برابر 1- باشد تابع MATCH، کوچک ترین مقداری که بزرگ تر یا مساوی lookup_value باشد را پیدا می کند و lookup_array باید به صورت نزولی مرتب شده باشد.

اگر آرگومان match_type  را به کار نگرفته باشید، مقدار آن در تابع برابر 1 در نظر گرفته می شود.

تابع INDEX، یک سلول از دامنه را به عنوان پاسخ برمی گرداند. ساختار دستوری این تابع به صورت زیر است:

INDEX ( array, row_num, column_num )

آرگومان های تابع INDEX به شرح زیر هستند:

 :arrayدامنه‌ی جست وجو.

 :row_numشماره ی ردیف در .array

 :col_numشماره ی ستون در  array

اگر آرایه تنها یک ردیف یا ستون داشته باشد، می توانید آرگومان row_num یا col_num را درج نکنید.

تصویر زیر،کاربرگی را نشان می دهد که در ستون های D،Eو F به ترتیب تاریخ (Date)، نام روز (WeekDay) و مقدار (Amount) ذکر شده اند. وقتی تاریخی را در سلول B1 درج می کنید فرمول زیر که در سلول B2 قرار دارد در بین تاریخ های ستون D به جست وجو می پردازد و مقادیر متناظر با آن تاریخ را در ستون F به عنوان نتیجه درج می کند. فرمول سلول B2 به شرح زیر است:

=INDEX ( F2 : F21, MATCH ( B1 , D2 : D21 , 0 ) )

فرمول های تخصصی جست وجو

 

برای درک چگونگی عملکرد این فرمول، کار را با تابع MATCH شروع می کنیم.

این تابع در دامنه ی D2:D21به دنبال تاریخ درج شده در سلول B1  می گردد. هرجا این تاریخ را پیدا کرد، شماره ی ردیف مربوطه را برمی گرداند که از آن به عنوان آرگومان دوم در تابع INDEX استفاده می شود. نتیجه، مقدار متناظر در دامنه‌ی F2: F21 است.

سلول های تهی و عملیات جست وجو

توابع جست وجوی اکسل، سلول های تهی در دامنه ی نتیجه را "صفر" در نظر می گیرندبرای مثال در سلول B2، فرمول زیر نوشته شده که با کمک نام درج شده در سلول B1، مقدار معادل (Amount) را از ستون E پیدا می کند و نمایش می دهد:

=VLOOKUP (B1 , D2 : E8 , 2)

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

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

وظیفه تابع IF، این است که کنترل کند آیا طول مقدار برگشتی از ستون Amount برابر صفر است یا خیر. وقتی معادل نام جست وجو در قسمتAmount، تهی باشد (یعنی مقداری برای آن درج نشده باشد)، طول مقدار برگشتی برابر صفر خواهد بود.

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

=IF (LEN (VLOOKUP (B1 , D2 : E8 , 2) ) =0, “” , (VLOOKUP (B1 , D2 : E8 , 2) ) )

البته می توانید با کمک فرمول زیر، به دنبال رشته های خالی در ستون E بگردید:

=IF (VLOOKUP (B1 , D2 : E8 , 2) =” “ , “ “ , (VLOOKUP (B1 , D2 : E8 , 2) ) )


نویسنده : --

تگ ها :
ثبت نظر