دستور VLOOKUP اکسل و کاربرد آن در ساخت پایگاه داده

یکی از قدرتمندترین دستورات اکسل که در ساخت یک پایگاه داده کاربرد فراوانی دارد VLOOKUP است. با استفاده از این دستور می توان در یک جدول داده ها به جستجو پرداخت و داده های متناظر با یک ستون مشخص جدول را استخراج کرد. برای مثال فرض کنید جدولی از اندازه های استاندارد پیچ به صورت زیر در اختیار داریم:

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

برای ایجاد پایگاه داده به کمک این دستور، ابتدا یک فهرست گشودنی (dropbox) از نمادهای استاندارد پیچ (ستون اول جدول) می سازیم. جهت این کار ابتدا در یک خانه مناسب اکسل (مانند D19) کلیک کرده و از منوی نرم افزار اکسل دستور زیر را اجرا کنید:

Data > Validation

در بخش Settings کادر زیر Allow را باز کرده گزینه List را انتخاب کنید. روی تکمه انتهای کادر Source کلیک کرده و ستون اول جدول را با نگهداشتن تکمه چپ ماوس و کشیدن آن روی تمام خانه ها انتخاب کنید. برای این منظور می توانید از صفحه کلید نیز استفاده کنید. دقت کنید که In-cell dropdown حتما انتخاب شده باشد.

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

در خانه D21 جلوی عبارت Pitch: دستور زیر را وارد کنید:

=VLOOKUP(D19;C4:J17;2;FALSE)

بخش های مختلف این دستور بشرح زیر است:

پارامتر اول خانه ای را مشخص می کند که به عنوان داده ورودی بکار می رود. در این جا مقدار انتخاب شده در خانه D19 که مطابق داده های ستون اول جدول اصلی است وارد شده است.

پارامتر دوم جدول تحت جستجو را مشخص می کند که در اینجا کل جدول داده ها است.

پارامتر سوم شماره ستونی را مشخص می کند که داده جستجو از آن باید برداشته شود. برای مثال در اینجا ستون 2 یعنی مقدار گام یا Pitch متناظر با استاندارد پیچ از جدول استخراج شود.

مقدار FALSE برای آخرین پارامتر یعنی می خواهیم مقدار داده ورودی (D19) دقیقا با یکی از سطرهای جدول همخوانی داشته باشد. اگر پارامتر آخر TRUE انتخاب شود، اکسل نزدیکترین داده به مقدار ورودی را از ستون اول انتخاب می کند و دیگر همخوانی کامل با داده ورودی نخواهیم داشت.

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

D23  =VLOOKUP(D19;C4:J17;3;FALSE)

D24  =VLOOKUP(D19;C4:J17;4;FALSE)

D26  =VLOOKUP(D19;C4:J17;5;FALSE)

D27  =VLOOKUP(D19;C4:J17;6;FALSE)

D29  =VLOOKUP(D19;C4:J17;7;FALSE)

D30  =VLOOKUP(D19;C4:J17;8;FALSE)

با انتخاب هر کدام از اندازه های استاندارد پیچ در خانه D19 مقادیر قطر ماکزیم، گام و قطر می نیمم به شکل زیر به نمایش در می آید:

فایل اکسل این مثال را از لینک زیر می توانید بارگذاری کنید.

---

برای آموزش اکسل پیشرفته و تخصصی با شماره زیر تماس بگیرید یا پیام واتس آپ بفرستید:

09360771981

آموزش اکسل پیشرفته متناسب با زمینه کاری شما با بهترین کیفیت و کمترین قیمت

آموزش اکسل برای سازمانها و موسسات گوناگون

---