جستجوي بانك‌هاي اطلاعاتي با استفاده از توابع اكسل

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

 ۱- تابع  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 مانند آرگومان دوم و به همان اندازه مي‌باشد، مثلاً اگر آرگومان دوم يك بردار افقي با ۱۰ سلول باشد، آرگومان سوم هم بايد يك بردار افقي با ۱۰ سلول باشد. در واقع پس از يافتن عبارت مورد جستجو در lookup_vector، تابع LOOKUP محتواي سلول هم تراز با سلول حاوي عبارت جستجو در result_vector را به عنوان خروجي نمايش مي‌دهد.

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

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

به مثال زير دقت كنيد:

 

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

۲- تابع 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 نشانگر آرگومان دوم مي‌باشد، همينطور مي‌توان نام محدوده را به عنوان آرگومان دوم درج كرد

نكته ۱) عمليات جستجوي آرگومان اول تنها در ستون اول محدوده‌ي معرفي شده به عنوان آرگومان دوم انجام مي‌شود. بنابراين مهم نيست كه محدوده‌ي وارد شده داراي چند ستون باشد.

آرگومان سوم: اين آرگومانيك عدد مي‌باشد و شماره ستون داده‌ي مورد نظر براي استخراج از جدول است، ستون شماره ۱ همان ستون يا Vector جستجو شده و ستون شماره ۲ ستون مجاور مي‌باشد و به همين ترتيب. در مثال بالا، اين آرگومان عدد ۳ مي‌باشد، زيرا ستون حاوي نام شخص ستون سوم از جدول است.

نكته ۲) اگر آرگومان سوم تابع 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# است.

۳- تابع HLOOKUP:

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