تبلیغات
حسابداری دانشگاه فردوسی مشهد - توابع جستجو در اکسل(LOOKUP,VLOOKUP,HLOOKUP)

slider1 slider1 slider1 slider1
.................... همایش IFRS و اقتصاد در پسا برجام ....................
.................... دیدار شورای دبیران انجمن های علمی دانشجویی دانشگاه فردوسی مشهد با استاندار خراسان رضوی ....................
.................... دیدار شورای دبیران انجمن های علمی دانشجویی دانشگاه فردوسی مشهد با معاون امور اقتصادی و توسعه منابع استانداری خراسان رضوی ....................
توابع جستجو در اکسل(LOOKUP,VLOOKUP,HLOOKUP)

توابع جستجو در اکسل                    

این دسته از توابع به منظور جستجو در بانک های اطلاعاتی مورد استفاده قرار میگیرند، منظور از جستجو صرفاً پیدا کردن یک عبارت نیست، توابع VLOOKUP,LOOKUP و HLOOKUP در اکسل در واقع به کاربران در تکمیل بانک های اطلاعاتی و استفاده از آنها بسیار کمک میکنند، با یاری گرفتن از این توابع انجام بسیاری از کارها در اکسل راحت میشود.

تابع LOOKUP در اکسل

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

LOOKUP (lookup_value, lookup_vector, result_vector)

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

نکته ی بسیار مهم اینست که محدوده جستجو (چه سطری باشد چه ستونی) حتماً باید به صورت صعودی مرتب (Sort) شده باشد از اعداد منفی به مثبت یا کوچکتر به بزرگتر یا از حرف A تا حرف Z یا False به true در غیر اینصورت ممکن است تابع LOOKUP جواب صحیح را به ما ندهد.

  • Result_vector، برای این آرگومان باید محدوده ای از سل ها به اندازه تعداد سل محدوده ای که برای جستجو انتخاب کردیم باشد و باید به صورت یک ستونی ویا یک سطری باشدو در ضمن این محدوده  میتواند در هر کجای شیت اکسل باشد(برای فهم بهتر می توانید به فایل با توضیحات تصویری رجوع کنید)وتابع LOOKUP محتوای سلول هم تراز با سلول حاوی عبارت جستجو در result_vector را به عنوان خروجی ارائه میدهد.

اگر تابع LOOKUP نتواند عبارت مورد جستجو را در lookup_vector پیدا کند، آخرین (بزرگترین) مقدار در lookup_vector را که برابر یا کوچکتر از عبارت مورد جستجو میباشد را به عنوان نتیجه ی جستجو میپذیرد (به صعودی بودن lookup_vector دقت کنید). و اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو lookup_vector باشد خروجی تابع خطای N/A# میباشد.

تذکر:دقت داشته باشید که لزومی ندارد دو محدوده lookup_vector و  result_vecto ، حتماً در جوار یکدیگر قرار داشته باشند، بلکه صرفاً هم اندازه بودن این دو محدوده (Vector) برای بدست آوردن جواب کافیست.

در ادامه با دو تابع  VLOOKUPوHLOOKUP آشنا میشوید.

تابع VLOOKUP در اکسل                            

تابع VLOOKUP یا Vertical LOOKUP مانند تابع LOOKUP عمل میکند، اگر منطق تابع LOOKUP و طریقه ی کار کردن با آن را بدانید درک VLOOKUP برای شما آسان تر خواهد بود، لذا حتماً بخش تابع LOOKUP را قبل از این قسمت بخوانید.

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

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

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

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

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

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

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

همانند تابع LOOKUP، جستجو در تابع VLOOKUP تنها در یک ستون (ستون اول داده ها)  صورت میگیرد، هر محدوده ای که به عنوان آرگومان دوم (table_array) به تابع معرفی شود، ستون اول (سمت چپ ترین ستون در چینش چپ به راست) به عنوان محل جستجو برای عبارت مورد نظر استفاده میشود.

آرگومان  سوم col_index_num، یک عدد میباشد و شماره ستون داده ی مورد نظر برای استخراج از جدول است، ستون شماره۱ همان ستون  جستجو شده و ستون شماره ۲ ستون مجاور میباشد و به همین ترتیب.

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

 

آرگومان چهارم یک آرگومان اختیاری و بسیار مهم است، این آرگومان Boolean میباشد و میتواند True یا False باشد. اگر این آرگومان True یا ۱ باشد یا نادیده گرفته شود، در اینصورت تابع VLOOKUP رفتار زیر را انجام میدهد:

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

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

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

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

 

تابع HLOOKUP در اکسل

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

 

نظرات مطلب
آخرین عناوین
گرامیداشت روز حسابدار
پنجشنبه 9 دی 1395
پیام نوروزی سال 1395
جمعه 6 فروردین 1395
شاخص فلاکت (MISERY INDEX)
سه شنبه 20 بهمن 1394
سوئیفت
سه شنبه 29 دی 1394
معرفی نرم افزار ترازگر
پنجشنبه 24 دی 1394
موضوعات
نویسندگان
آرشیو
برچسب ها
روزانه ها
پیوندها
صفحات جانبی
امکانات سایت