نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 53

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال ۱۳۹۲ به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، طراحی گرافیک و برنامه نویسی NET. فعالیت دارم.

  • تابع VLOOKUP

تابع VLOOKUP یکی از پرکاربردترین توابع اکسل می باشد که آشنایی و استفاده درست از آن باعث سهولت در بسیاری از کارها می شود. با استفاده از تابع VLOOKUP میتوان یک مقدار را در یک ستون جستجو کرده و مقدار متناظر آن را در ستون های بعدی بدست آورد.

بیشتر اوقات ممکن است بخواهیم از یک بانک اطلاعاتی داده ای را جستجو کرده و فراخوانی کنیم. مثلا از یک بانک اطلاعاتی که شامل اطلاعات کارکنان یک سازمان میباشد میخواهیم با وارد کردن شماره پرسنلی هر فرد اطلاعات مربوط به آن فرد را فراخوانی کنیم. یکی از راه های مناسب برای انجام اینکار استفاده از تابع VLOOKUP می باشد.

بطور کلی تابع VLOOLUP از یک قاعده کلی پیروی می کند.

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

فرض کنیم در ورک بوک خود دو ورک شیت به نام های داده ها و جستجو داریم.

ورک شیت داده ها

تابع VLOOKUP − w3blog.irتصویر ۱ − ورک شیت داده ها

ورک شیت جستجو

تابع VLOOKUP − w3blog.irتصویر ۲ − ورک شیت جستجو

میخواهیم در ورک شیت جستجو با وارد کردن شماره پرسنلی در سلول B1 داده هایی نظیر نام و نام خانوادگی، وضعیت، واحد سازمانی، تاریخ استخدام و مبلغ دریافتی را در ورک شیت داده ها جستجو کرده و در سلول های B3 تا B7 در ورک شیت جستجو نمایش دهیم.

پارامتر lookup_value

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

پارامتر table_array

در این پارامتر بازه ای که قرار است جستجو در آن انجام شود را تعریف می کنیم. این بازه باید از ستونی که قرار است جستجو در آن را انجام دهیم شروع شده و به ستون هایی که دنبال نتایج متناظر جستجو در آن هستیم ادامه پیدا کند. بنابراین میخواهیم شماره پرسنلی هر فرد در ستون A مربوط به ورک شیت داده ها را جستجو کرده و مقدار متناظرش در آن سطر را برگردانیم.

پارامتر col_inex_num

این پارامتر تعیین میکند که بعد از پیدا کردن مقدار مورد نظر نتیجه تابع از چه ستون متناظری در محدوده جستجو باید استخراج شود. در این مثال میخواهیم جستجو در ستون A که مربوط به شماره پرسنلی بوده را انجام دهیم، فرض کنیم هدف رسیدن به نام و نام خانوادگی مربوط به شماره پرسنلی مورد نظر می باشد، پس باید شماره ستون مربوط به نام و نام خانوادگی در ورک شیت داده ها را تعیین کنیم. اینکار باعث می شود هنگامی که شماره پرسنلی مورد نظر در ستون اول پیدا شد، نتیجه تابع از ستون دوم که نشان دهنده نام و نام خانوادگی فرد است استخراج شود.

پارامتر range_lookup

این پارامتر در تابع VLOOKUP اختیاری است. اگر این مقدار را برابر True قرار دهیم بدین معنی می باشد که اگر مقدار مورد نظر برای جستجو در محدوده جستجو وجود نداشت، بزرگترین مقدار موجود در محدوده جستجو که کوچکتر از مقدار مورد نظر برای جستجو است به عنوان نتیجه تابع انتخاب شود. اگر این مقدار را برابر False قرار دهیم بدین معنی می باشد که اگر مقدار مورد نظر برای جستجو در محدوده جستجو وجود نداشت، نتیجه تابع با خطا مواجه می شود. این پارامتر بطور پیش فرض مقدار True را در نظر می گیرد، در نتیجه اگر به دنبال مقدار تقریبی در جستجوی خود باشیم میتوان از این پارامتر صرف نظر کرد.

سلول B3 در ورک شیت جستجو را انتخاب کرده و با کمک پنجره Function Arguments شروع به تعریف تابع VLOOKUP در ورک شیت جستجو می کنیم.

۱lookup_valueاین پارامتر را برابر با مرجع مطلق سلول B1 در ورک شیت جستجو قرار میدهیم.
۲table_arrayاین پارامتر را برابر با محدوده ستون های A تا H از ورک شیت داده ها قرار میدهیم.
۳col_index_numاین پارامتر را برابر شماره ستونی که نام و نام خانوادگی فرد در آن وجود دارد قرار می دهیم.
۴range_lookupاین پارامتر را برابر با مقدار False قرار می دهیم.

تابع VLOOKUP − w3blog.irتصویر ۳ − تعریف تابع VLOOKUP

سپس بروی OK کلیک کرده تا عملیات انجام شده ثبت گردد. در سلول B1 شماره پرسنلی فردی که قصد جستجوی آن را داریم را وارد می کنیم. این تابع نام و نام خانوادگی فردی که شماره پرسنلی اش ۱۰۰۱ بوده را برمیگرداند.

=VLOOKUP($B$1,'داده ها'!A:H,2,FALSE)

در ادامه کار سلول B3 را انتخاب کرده و آنرا تا سلول B7 درگ کرده تا تابع VLOOKUP برای سلول های B4 تا B7 کوپی شود. با انجام اینکار نتیجه تمام سلول های B3 تا B7 نام و نام خانوادگی شخص مورد نظر خواهد بود. برای تغییر در آنها به صورت زیر عمل می کنیم.

۱وضعیتمقدار پارامتر col_index_num را برابر با شماره ستون وضعیت در ورک شیت داده ها قرار می دهیم.
۲واحد سازمانیمقدار پارامتر col_index_num را برابر با شماره ستون واحد سازمانی در ورک شیت داده ها قرار می دهیم.
۳تاریخ استخداممقدار پارامتر col_index_num را برابر با شماره ستون تاریخ استخدام در ورک شیت داده ها قرار می دهیم.
۴مبلغ دریافتیمقدار پارامتر col_index_num را برابر با شماره ستون مبلغ دریافتی در ورک شیت داده ها قرار می دهیم.

تابع VLOOKUP − w3blog.irتصویر ۴ − جستجو بر حسب شماره سازمانی توسط تابع VLOOKUP

داده ای که مورد جستجو قرار می گیرد، همیشه باید در اولین ستون از table_array وجود داشته باشد. وقتی میخواهیم اطلاعات مربوط به هر شخص در یک سازمان را برحسب شماره پرسنلی فراخوانی کنیم، محدوده جستجو و یا همان table_array حتمان باید با ستون شماره پرسنلی شروع شود.

این تابع نام و نام خانوادگی، وضعیت، واحد سازمانی، تاریخ استخدام و مبلغ دریافتی کارمندی که شماره پرسنلی اش ۱۰۰۱ بوده را جستجو کرده و نتیجه آن را در سلول های B3 تا B7 بر می گرداند.