توابع Lookup & Reference
در این بخش به بررسی توابع Vlookup, Match, Index می‌پردازیم.
مثال 1(
فرض کنید یک لیست A از کلمات کلیدی داریم که کارفرما بر طبق اولویتی که به آنها می‌داده، نمره‌ای را به هریک از آنها اختصاص داده است. بعد با استفاده از یکسری ابزارهای تحقیق کلمات کلیدی، از این لیست، لیست دیگری به نام لیست B ساخته‌ایم. مثلاً اگر روی لیست A، یک کلمه کلیدی را با Auto Filterانتخاب کنیم، یک تعدادی کلمه در لیست B متناظر با آن وجود خواهند داشت، همچنین هر کدام از این کلمات توسط کارفرما، نمره‌ای گرفته‌اند.
حالا می‌خواهیم نمره نهایی هر کلمه کلیدی را محاسبه کنیم. بدین صورت که نمره لیست A در نمره لیست B ضرب شود.
ابتدا باید در یک ستون جدید، نمره هر کلمه کلیدی در لیست A هم محاسبه و درج شود. باید هر کلمه کلیدی در لیست A جستجو شده، نمره متناظر آن یافت شود و به ازای تمام کلمات کلیدی موجود در این لیست در جدول جدید، نمره درج شود. جهت انجام این کار از تابع Vlookup استفاده می‌کنیم. این تابع، عبارت کلیدی را که به عنوان آرگومان ورودی به آن پاس می‌دهیم، را دریافت نموده و در لیست A جستجو می‌کند و مقدار ستون متناظر مربوط به آن را بر می‌گرداند.
=VLOOKUP(A2,'List A'!A2:B18,2,0)
در اینجا آرگومان ورودی اول، عبارتی است که می‌خواهیم در لیست A جستجو شود .آرگومان ورودی دوم، نام آن شیت اکسلی است که لیست A و بازه سلولی که جدول مورد نظر در آن قرار گرفته است. آرگومان سوم، شماره ایندکس ستونی از جدول است که می‌خواهیم بازگردانده شود و آرگومان چهارم، یک مقدار منطقی است که Range Lookup را مشخص می‌کند. در این مثال، چون هدف یافتن عبارت دقیق یا Exact Match است، از مقدار 0 استفاده کردیم.
در این فرایند احتمال بروز یکسری از خطاها وجود دارد:
1- عدم وجود عبارت کلیدی مورد جستجو در لیست .A حتی اگر یک کاراکتر فاصله هم بعد از کلمه کلیدی وجود داشته باشد با خطای #N/A(Not Available) مواجه خواهیم شد.
2- تکمیل سایر سلول‌های ستون نمره لیست A از طریق کپی همین فرمول: این خطا به دلیل مطلق نبودن دامنه Table Array رخ می‌دهد، که باعث می‌شود جستجو در جایی غیر از دامنه هدف صورت پذیرد. اگر فرمول را بدین صورت تغییر دهیم، این مشکل حل می‌شود:
=VLOOKUP(A2,'List A'!$A$2:$B$18,2,0)
3- استفاده از مقدار 1 یا نامعلوم برای Range Lookup، در این صورت Approximate Match انجام می‌شود و نزدیک‌ترین عبارت کلیدی به عبارتی که قصد جستجوی آن را دارید، یافته می‌شود و نمره مربوط به آن بازگردانده می‌شود.
مثال 2) فرض کنید که جدولی از اطلاعات کارکنان شامل کد پرسنلی، نام و نام خانوادگی، وضعیت تأهل، تاریخ شروع به‌کار، مدرک تحصیلی، رتبه، واحد سازمانی و حقوق پایه داریم و قصد داریم محاسبات زیر را انجام دهیم.
یافتن واحد سازمانی یک فرد با داشتن کد پرسنلی:
=VLOOKUP(N4,A4:J29,9,0)
در اینجا سلول N4 حاوی کد پرسنلی است که قصد جستجوی آن را داریم. بازه سلولی A4 تا J29 مشخص کننده جدول اطلاعات کارکنان است و عدد 9، ستون واحد سازمانی که باید به عنوان نتیجه بازگردانده شود و عدد 0، Exact Match را مشخص می‌کند. اما این فرمول با خطای #N/A مواجه می‌شود. مشکلی که در اینجا وجود دارد این است که، Table Array باید به نحوی مشخص شود که ستونی که قرار است جستجو در آن صورت گیرد، در آن جدول، ستون اول باشد. همچنین باید ایندکس ستون را جهت برگرداندن ستون نتیجه، متناظر با Table Array پاس داده شده، مشخص کنیم نه جدول اصلی. بنابراین فرمول را به صورت زیر تغییر می‌دهیم:
=VLOOKUP(N4,B4:J29,8,0)
نکته) در شمارش ستون‌ها، به راست‌چین و چپ‌چین بودن Sheet هم دقت کنید.
توابع Match و :Index ترکیب این دو تابع، همان کارکرد Vlookup را دارد.
تابع Match: موقعیت مرتبط با آیتم مورد جستجو را در یک دامنه باز می‌گرداند.
یافتن جایگاه رکورد مربوط به یک فرد با داشتن کد پرسنلی:
=MATCH(N4,B3:B29,0)
در اینجا، سلول N4 حاوی کد پرسنلی است که قصد جستجوی آن را داریم. بازه سلولی B3 تا B29 مشخص کننده دامنه‌ای است که قصد جستجو در آن را داریم و 0 نشانگر Exact Match است.
نتیجه‌ای که بازگردانده می‌شود، جایگاه آیتم را دقیقاً در دامنه آرگومان دوم نشان می‌دهد نه در کل جدول.
تابع Index: یک آرایه تک بُعدی یا دو بُعدی را به عنوان ورودی دریافت می‌کند و محتوای یک سلول را بر اساس موقعیتش، باز می‌گرداند.
بازنویسی فرمول یافتن واحد سازمانی یک فرد با داشتن کد پرسنلی:
=MATCH(N4,B3:B29,0)
O5 à 3
=INDEX(I3:I29,O5)
در اینجا بازه سلولی I3 تا I29 مشخص کننده ستون حاوی واحد سازمانی کارکنان و سلول O5، حاوی خروجی تابع Match است که در مرحله قبل محاسبه نمودیم.
نکته) توجه داشته باشید که شماره شروع و پایان هر دو بازه در هر دو تابع باید یکسان باشد.
نکته) می‌توانید فرمول را به این صورت هم بنویسید:
=INDEX(I3:I29,MATCH(N4,B3:B29,0))
کاربرد اصلی این دو تابع، زمانی است که اطلاعات ستون‌های مورد نیاز شما در جدول قبل از ستون مورد جستجو قرار گرفته‌اند. بنابراین امکان استفاده از تابع Vlookup را روی چنین ستونی که ستون اول Table Array نیست، ندارید.
یافتن نام خانوادگی یک فرد با داشتن کد پرسنلی:
=INDEX(D:D,MATCH(Q4,E:E,0))
در اینجا ستون D حاوی نام خانوادگی کارکنان است، سلول Q4 کد پرسنلی است که قصد جستجوی آن را داریم و ستون E شامل کد پرسنلی کارکنان است.
سایر توابع این دسته در بخش LookUp & Reference از منوی Formulas قرار گرفته‌اند.
تابع Hlookup : عملکردی مشابه Vlookup اما به صورت افقی و در سطرهای جدول دارد.البته اگر دیتابیس را به درستی طراحی کرده باشیم، به ندرت اتفاق می‌افتد که نیاز به استفاده از این تابع باشد.
تابع :Hyperlink تبدیل رشته ورودی به یک لینک قابل کلیک
A1à C:\Users
=HYPERLINK(A1,"open")
که در اینجا سلول A1 حاوی رشته متنی است که می‌خواهیم تبدیل به لینک شود و "open" یک متن جایگزین است که به صورت عنوان لینک نمایش می‌یابد.
این آدرس می‌تواند شامل آدرس کامل یک فایل هم باشد، که با کلیک روی open باز شود.
تابع Column : آدرس ستونی در آن قرار دارید، برمی‌گرداند. کاربرد آن در فرمول‌های آرایه‌ای است.
تابع Row : آدرس سطری در آن قرار دارید، برمی‌گرداند.
تابع Rows : تعداد سطرهای فضای انتخاب شده را برمی‌گرداند.
تابع Columns: تعداد ستون‌های فضای انتخاب شده را برمی‌گرداند.
تابع Transpose: برای تغییر نمایش از عمودی به افقی و بالعکس یک آرایه یا بازه‌ای از سلول‌ها. از آنجایی که ورودی این تابع باید به شکلی آرایه وارد شود، پس از مشخص کردن بازه مورد نظر کلیدهای ترکیبی Ctrl+Shift+Enter را بزنید.
{= TRANSPOSE(C11:C15)}