جستجوی بانک‌های اطلاعاتی با استفاده از توابع اکسل

فرض کنید اطلاعات کتابخانه محل زندگی خود را که برای هر کتاب شامل نام نویسنده، سال انتشار، تعداد صفحه، انتشارات و… می‌باشد را در یک فایل اکسل ذخیره کرده‌اید. برای اینکه وقتی کد یا نام کتاب را در یک سلول وارد می‌کنید سایر اطلاعات مربوط به آن کتاب نمایش داده شود چه کار می‌کنید؟ در ادامه مطلب با آموزش اکسل پیشرفته امروز  همراه باشید تا با نحوه جستجو در بانک‌های اطلاعاتی با استفاده از توابع موجود در اکسل آشنا شوید.

1- تابع  LOOKUP  در اکسل

این تابع دو فرم آرایه‌ای و برداری (Vector) دارد که فرم برداری آن مدنظر ما است. در اکسل به یک محدوده از سلول‌ها که تنها یک سطر یا یک ستون داشته باشد، Vector می‌گویند مثلاً محدوده‌های A1:A88 یا A1:M1 هر دو Vector هستند.

تابع LOOKUP، یک عبارت را در یک Vector جستجو می‌کند و محتوای سلول هم موقعیت با سلول پیدا شده در Vector دیگر را به عنوان خروجی به کاربر می‌دهد.

ساختار تابع LOOKUP به شکل زیر می‌باشد:

=LOOKUP (lookup_value, lookup_vector, result_vector)

آرگومان اول: وارد کردن این آرگومان اجباری است چون بیانگر عبارت مورد نظر برای جستجو می‌باشد. این آرگومان می‌تواند عدد، رشته متنی، Logical Values (شامل صفر و یک یا True و False) یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
آرگومان دوم: این آرگومان بیانگر Vector محل جستجو می‌باشد که وارد کردن آن نیز اجباری است. این آرگومان یک محدوده از اکسل شامل یک سطر یا یک ستون می‌باشد که قرار است آرگومان اول در آن جستجو شود. سلول‌های محدوده‌ی Vector هم می‌توانند حاوی اعداد یا رشته‌های متنی یا Logical Values (شامل صفر و یک یا True و False) باشند.

آرگومان سوم: یک Vector مانند آرگومان دوم و به همان اندازه می‌باشد، مثلاً اگر آرگومان دوم یک بردار افقی با 10 سلول باشد، آرگومان سوم هم باید یک بردار افقی با 10 سلول باشد. در واقع پس از یافتن عبارت مورد جستجو در lookup_vector، تابع LOOKUP محتوای سلول هم تراز با سلول حاوی عبارت جستجو در result_vector را به عنوان خروجی نمایش می‌دهد.

اگر تابع LOOKUP نتواند عبارت آرگومان اول را در vector مورد جستجو پیدا کند، آخرین (بزرگترین) مقدار در lookup_vector را که برابر یا کوچکتر از عبارت مورد جستجو می‌باشد را به عنوان نتیجه‌ی جستجو می‌پذیرد. بنابراین برای اینکه خروجی تابع، صحیح باشد، Vector باید به صورت صعودی مرتب شده باشد. در غیر اینصورت ممکن است تابع LOOKUP جواب صحیح را به ما ندهد. پس به صعودی بودن lookup_vector دقت کنید.

اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو lookup_vector باشد در خروجی تابع خطای N/A# ظاهر می‌شود.

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

دقت داشته باشید که لزومی ندارد دو محدوده lookup_vector و  result_vector مانند مثال بالا، حتماً مجاور هم باشند، بلکه صرفاً هم اندازه بودن این دو محدوده (Vector) کافیست.

2- تابع VLOOKUP:

تابع VLOOKUP یا Vertical LOOKUP (جستجوی عمودی) در excel مانند تابع LOOKUP عمل می‌کند. درواقع اگر با تابع LOOKUP آشنا باشید درک VLOOKUP برای شما آسان‌تر خواهد بود، لذا توصیه می‌شود قبل از مشاهده توضیحات تابع VLOOKUP، بخش تابع LOOKUP که در بالا به آن اشاره شده است را  مطالعه کنید.

تابع VLOOKUP می‌تواند یک عبارت را در اولین ستون یک محدوده جستجو کند و در هر یک از ستون‌های موجود در محدوده‌ مورد جستجو، محتوای سلول هم تراز (هم ردیف) با سلول پیدا شده را به عنوان خروجی اعلام نماید. در واقع کلمه Vertical در نام این تابع به جستجو در ستون اشاره می‌کند.

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

ساختار این تابع به صورت زیر است:

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

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

آرگومان اول: این آرگومان عبارتی است که کاربر می‌خواهد جستجو کند، در مثال بالا این آرگومان شماره پرسنلی شخص می‌باشد، این آرگومان همانند آنچه در تابع LOOKUP وجود داشت، می‌تواند عدد، رشته متنی، آدرس سلول و یا یک مقدار منطقی (Logical Value) باشد.

اگر محتویات ستون اول محدوده مورد جستجو از نوع متن باشد، می‌توانید از کاراکترهای جایگزین شونده استاندارد در آرگومان اول استفاده کنید. علامت? را می‌توان جایگزین یک کاراکتر و علامت * را می‌توان جایگزین چندین کاراکتر دانست.

آرگومان دوم: این آرگومان یک محدوده از اکسل می‌باشد، تمام جدول داده‌ها به عنوان این آرگومان به تابع معرفی می‌گردد، در مثال بالا محدوده‌ی A2:C10 نشانگر آرگومان دوم می‌باشد، همینطور می‌توان نام محدوده را به عنوان آرگومان دوم درج کرد

نکته 1) عملیات جستجوی آرگومان اول تنها در ستون اول محدوده‌ی معرفی شده به عنوان آرگومان دوم انجام می‌شود. بنابراین مهم نیست که محدوده‌ی وارد شده دارای چند ستون باشد.

آرگومان سوم: این آرگومانیک عدد می‌باشد و شماره ستون داده‌ی مورد نظر برای استخراج از جدول است، ستون شماره 1 همان ستون یا Vector جستجو شده و ستون شماره 2 ستون مجاور می‌باشد و به همین ترتیب. در مثال بالا، این آرگومان عدد 3 می‌باشد، زیرا ستون حاوی نام شخص ستون سوم از جدول است.

نکته 2) اگر آرگومان سوم تابع VLOOKUP کمتر از یک باشد خروجی تابع خطای!VALUE# و اگر این عدد بزرگتر از تعداد کل ستون‌ها باشد خروجی تابع خطای!REF# خواهد بود.

آرگومان چهارم: اگرچه وارد کردن این آرگومان، اختیاری است اما بسیار مهم می‌باشد. این آرگومان می‌تواند True یا False باشد.

اگر این آرگومان True باشد یا نادیده گرفته شود، در اینصورت تابع VLOOKUP رفتار زیر را انجام می‌دهد:

• اولاً، داده‌های محدوده مورد جستجو (ستون اول) باید همانند آنچه در تابع LOOKUP وجود داشت به صورت صعودی مرتب شده باشند تا مطمئن باشیم که خروجی تابع قابل اعتماد است.
• دوماً، در صورت نیافتن عبارت مورد جستجو در ستون اول، دقیقاً مشابه تابع LOOKUP، تابع VLOOKUP نیز بزرگترین مقدار کوچکتر از عبارت مورد جستجو را به عنوان پاسخ می‌پذیرد.
• سوماً، مجدداً مشابه تابع LOOKUP، اگر تابع VLOOKUP نتواند عبارت مورد جستجو را در ستون اول پیدا کند، آخرین (بزرگترین) مقدار در آن Vector که برابر یا کوچکتر از عبارت مورد جستجو می‌باشد را به عنوان نتیجه‌ی جستجو می‌پذیرد (به صعودی بودن داده‌ها دقت کنید).

حال اگر آرگومان چهارم False باشد، False برای تابع به معنی Exact Match است یعنی در این حالت تابع تنها داده‌ای را به عنوان پاسخ می‌پذیرد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.

در صورتی که آرگومان چهارم False باشد نیازی به چینش صعودی داده‌های ستون مورد جستجو نیست در صورت وجود داشتن چند جواب، اولین مورد پیدا شده به عنوان جواب پذیرفته می‌شود.

آرگومان چهارم هرچه که باشد (True یا False)، مانند تابع LOOKUP، اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو ستون مورد جستجو (Vector) باشد خروجی تابع خطای N/A# است.

3- تابع HLOOKUP:

تابع HLOOKUP یا Horizontal LOOKUPn (جستجوی افقی) در اکسل، در ساختار و طریقه عملکرد هیچ تفاوتی با تابع VLOOKUP ندارد، تنها تفاوت این دو تابع در افقی و عمودی بودن داده‌ها است، تابع HLOOKUP برای جدول‌های افقی کاربرد دارد و سطر اول داده‌ها را جستجو می‌کند. با یادگیری و فهم توابع LOOKUP و VLOOKUP مشکلی در کار با تابع HLOOKUP نخواهید داشت.