• تابع VLOOKUP در اکسل

    تابع vlookup از کاربردی ترین توابع اکسل است. تابع vlookup یکی از توابع اکسل جهت جستجو و بازیابی اطلاعات از یک ستون مشخص از یک جدول می­باشد

    تابع «VLOOKUP» در «مایکروسافت اکسل» (Microsoft Excel) یکی از پرکاربردترین توابع موجود است و اتفاقا یکی از توابعی است که کمتر کسی درک درستی از عملکرد آن دارد. در این مطلب می‌خواهیم با استفاده از مثال‌های واقعی، تابع «VLOOKUP» را توضیح دهیم. در طی این آموزش یک قالب فاکتورگیری برای یک شرکت فرضی ایجاد خواهیم کرد.

    همانطور که گفته شد، «VLOOKUP» یک «تابع» است. در طی این مطلب فرض را بر این می‌گیریم که شما درک کافی از توابع در اکسل دارید و با توابع اولیه نظیر «SUM»، ‏«AVERAGE» و «TODAY» آشنا هستید.

    در ساده‌ترین حالت استفاده، «VLOOKUP» یک تابع مربوط به پایگاه داده یا «database» است، به این معنی که این تابع با جداول پایگاه داده سروکار خواهد داشت یا به زبانی ساده‌تر، با لیستی از محتوا در کاربرگ‌های اکسل کار می‌کند. این محتوا می‌توانند لیستی از کارمندان، محصولات، مشتری‌ها یا هر چیز دیگری باشند. در تصویر زیر یک مثال از لیست (یا دیتابیس) را مشاهده می‌کنید. در این مثال یک لیست از محصولات یک شرکت فرضی قرار گرفته است:

     

    آشنایی با تابع Vlookup در اکسل

    این گونه لیست‌ها معمولا برای هر آیتم یک شناسه‌ی منحصر به فرد دارند. در این لیست، شناسه منحصر به فرد ما ستون «Item Code» است. توجه داشته باشید که تابع «VLOOKUP» تنها در زمانی کار می‌کند که لیست یا پایگاه داده شما یک ستون شناسه منحصر به فرد (که از آن به عنوان «کلید» یا «ID» هم یاد می‌شود) داشته باشد. همچنین این ستون حتما باید اولین ستون موجود در جدول شما باشد. مثالی که در بالا مشاهده می‌کنید این شرایط را دارد.

    سخت‌ترین بخش استفاده از تابع «VLOOKUP» درک دقیق کاربرد آن است. این تابع را به شکل زیر تعریف می‌کنیم:

    VLOOKUP تابعی است که بر اساس مقدار شناسه منحصر به فرد ارائه شده، اطلاعات را از یک پایگاه داده یا لیست دریافت می‌کند.

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

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

     

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

    آشنایی با تابع Vlookup در اکسل

    عملکرد این کاربرگ این گونه خواهد بود که کاربر تعدادی کد آیتم را در ستون A وارد می‌کند و سیستم توضیحات و قیمت هر آیتم را از پایگاه داده دریافت می‌کند. در نهایت از این داده‌ها برای محاسبه مبلغ کل استفاده می‌شود. همچنین برای سادگی کار، دیتابیس محصولات را در یک کاربرگ مجزا در همان کاربرگ یا «Workbook» قرار می‌دهیم.

    آشنایی با تابع Vlookup در اکسل

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

    همانطور که در بالا مشاهده کردید، پایگاه داده محصولات ما به شکل زیر است:

    آشنایی با تابع Vlookup در اکسل

    برای امتحان کردن فرمول VLOOKUP، ابتدا یک کد آیتم صحیح در سلول A11 فاکتور خود می‌نویسیم.

    آشنایی با تابع Vlookup در اکسل

    سپس سلولی که می‌خواهیم توضیحات محصول در آن نمایش داده شود را انتخاب می‌کنیم. جالب است بدانید این گام همان بخشی است که اکثر کاربران به اشتباه متوجه آن می‌شوند. در این گام می‌خواهیم که توضیحات مربوط به کد آیتم موجود در سلول A11 را دریافت کرده و نمایش دهیم. این توضیحات باید در سلول B11 نمایش داده شوند، در نتیجه فرمول VLOOKUP را نیز باید در سلول B11 بنویسیم.

    آشنایی با تابع Vlookup در اکسل

    حالا نیاز به پیدا کردن تابع VLOOKUP در لیست توابع اکسل داریم تا بتوانیم به کمک اکسل این تابع را پیاده‌سازی کنیم. برای این کار باید از داخل زبانه «Formulas» گزینه‌ی «Insert Fucntion» را بزنید:

    آشنایی با تابع Vlookup در اکسل

    یک کادر باز خواهد شد که امکان انتخاب تمام توابع موجود در اکسل را به ما می‌دهد.

    آشنایی با تابع Vlookup در اکسل

    برای پیدا کردن تابع مورد نظر خود، می‌توانیم عبارت «lookup» را جست‌وجو کنیم. سیستم یک لیست از تمام توابع مربوط به «lookup» را به ما نمایش می‌دهد. VLOOKUP دومین گزینه در این بخش خواهد بود. آن را انتخاب کرده و بر روی OK کلیک کنید.

    آشنایی با تابع Vlookup در اکسل

    کادر «Function Arguments» باز خواهد شد و لیست تمام آرگومان‌هایی (یا پارامترهایی) که برای پیاده‌سازی فرمول VLOOKUP نیاز داریم را به ما نمایش می‌دهد. در واقع این کادر سوال‌های زیر را از ما می‌پرسد:

    1. شناسه منحصر به فردی که به دنبال آن هستید چیست؟
    2. دیتابیس شما در کجا قرار دارد؟
    3. کدام اطلاعات مربوط به شناسه منحصر به فرد خود را می‌خواهید از دیتابیس به دست بیاورید؟

    سه آرگومان اول با رنگ پر رنگ‌تر نمایش داده شده‌اند؛ این مساله به این خاطر است که پر کردن این موارد اجباری است. آرگومان چهارم که کم رنگ است اختیاری بوده و می‌توانید آن را وارد نکنید:

    آشنایی با تابع Vlookup در اکسل

    ما این آرگومان‌ها را به ترتیب از بالا به پایین پر می‌کنیم.

    اولین آرگومانی که باید پر کنیم، آرگومان «Lookup_value» است. تابع ما نیاز دارد تا بداند باید به دنبال کدام شناسه منحصر به فرد بگردد (که در مثال ما همان کد آیتم است) که بتواند توضیحات آن را برگرداند. در این بخش باید کد آیتمی که قبلتر وارد کرده بودیم را بنویسیم (کدی که در A11 نوشتیم).

     

    بر روی آیکون موجود در سمت راست آرگومان اول کلیک کنید:

    آشنایی با تابع Vlookup در اکسل

    سپس یکبار بر روی سلولی که شامل کد آیتم ما می‌شود (سلول A11) کلیک کرده و کلید «Enter» را فشار دهید:

    آشنایی با تابع Vlookup در اکسل

    مقدار «A11» در آرگومان اول قرار می‌گیرد.

    حال باید مقدار آرگومان «Table_array» را وارد کنیم. به عبارتی دیگر، باید به تابع VLOOKUP بگوییم که در کدام لیست یا دیتابیس به دنبال مقدار مورد نظر ما بگردد. بر روی آیکون موجود در سمت راست آرگومان دوم کلیک کنید:

    آشنایی با تابع Vlookup در اکسل

    حال دیتابیس یا لیستی که می‌خواهید را پیدا کرده و تمام آن را انتخاب نمایید (به غیر از سربرگ ستون‌ها). در مثال ما، دیتابیس در یک کاربرگ مجزا قرار دارد، در نتیجه ابتدا آن را باز می‌کنیم:

    آشنایی با تابع Vlookup در اکسل

    سپس تمام دیتابیس خود را به غیر از سربرگ آن انتخاب می‌نماییم:

    آشنایی با تابع Vlookup در اکسل

    در نهایت کلید «Enter» را فشار می‌دهیم. محدوده سلول‌هایی که دیتابیس ما را مشخص می‌کنند به طور خودکار در آرگومان دوم قرار خواهند گرفت (در مثال ما این مقدار «Product Database’!A2:‎D7» است).

    حال به سراغ آرگومان سوم، یعنی «Col_index_num» می‌رویم. از این آرگومان برای مشخص کردن اطلاعاتی که می‌خواهیم استفاده می‌کنیم. در این مثال، می‌خواهیم مقدار ستون «Description» را به دست بیاوریم. اگر به کاربرگ دیتابیس نگاهی بیندازید مشاهده خواهید کرد که ستون «Description» دومین ستون دیتابیس ما است. پس ما هم مقدار «2» را در آرگومان «Col_index_num» وارد می‌کنیم:

    آشنایی با تابع Vlookup در اکسل

    نکته مهمی که باید به آن توجه کنید این است که دلیل وارد کردن عدد «2» این نیست که ستون مورد نظر ما در ستون «B» قرار دارد. اگر دیتابیس ما از ستون «K» شروع می‌شد، باز هم ما عدد 2 را در این بخش وارد می‌کردیم، چراکه ستون «Description» ستون دومی است که در لیست مجموعه ستون‌های وارد شده در آرگومان «Table_array» قرار دارد.

    در نهایت باید تصمیم بگیریم که آیا برای آرگومان آخر نیز مقداری می‌خواهیم وارد کنیم یا خیر. این آرگومان نیازمند یک مقدار «True» یا «False» به معنای صحیح یا غلط است. زمانی که از VLOOKUP در هنگام کار با دیتابیس استفاده می‌کنید، باید بر اساس توضیحات زیر تصمیم به مقداردهی این آرگومان بگیرید:

    اگر ستون اول دیتابیس شما (ستونی که به عنوان شناسه منحصر به فرد استفاده شده است) به ترتیب حروف الفبا یا عدد و به صورت صعودی مرتب شده است، می‌توانید مقدار «true» را در این بخش وارد نموده یا آن را خالی رها کنید.

    اگر ستون اول دیتابیس مرتب نشده باشد یا به صورت نزولی مرتب شده باشد، باید مقدار «false» را برای این آرگومان استفاده نمایید.

    از آنجایی که ستون اول دیتابیس ما مرتب نشده است، مقدار «false» را در آن وارد می‌کنیم.

    آشنایی با تابع Vlookup در اکسل

    فرمول ما آماده است. تمامی اطلاعات مورد نیاز برای به دست آوردن اطلاعات مورد نیاز خود را در تابع VLOOKUP وارد کردیم. بر روی گزینه‌ی «OK» کلیک کنید. مشاهده می‌کنید که توضیحات مربوط به آیتم «R992545» در سلول B11 به نمایش در آمده است.

    آشنایی با تابع Vlookup در اکسل

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

    آشنایی با تابع Vlookup در اکسل

    اگر یک کد آیتم دیگر را در سلول A11 وارد کنیم، می‌توانیم قدرت تابع VLOOKUP را مشاهده نماییم. توضیحات ما تغییر کرده و به توضیحات آیتم جدید تبدیل خواهد شد.

    آشنایی با تابع Vlookup در اکسل

    همچنین می‌توانیم گام‌هایی مشابه را برای نمایش مبلغ در ستون «E11» دنبال کنیم. توجه داشته باشید که فرمول جدید ما باید در سلول E11 ساخته شود. نتیجه‌ی آن به شکل زیر خواهد بود:

    آشنایی با تابع Vlookup در اکسل

    فرمول جدید ما نیز همانند زیر است:

    آشنایی با تابع Vlookup در اکسل

    توجه کنید که تنها تفاوتی که در بین این دو فرمول وجود دارد این است که مقدار آرگومان «Col_index_num» را از 2 به 3 تغییر داده‌ایم، چراکه می‌خواستیم مقدار ستون سوم دیتابیس را نمایش دهیم.

    اگر قصد خرید دو عدد از این آیتم را داشته باشیم، مقدار 2 را در سلول «D11» وارد می‌کنیم، سپس فرمول زیر را در سلول «F11» می‌نویسیم تا قیمت کل به دست بیاید:

    نتیجه به شکل زیر خواهد بود:

    آشنایی با تابع Vlookup در اکسل

    تکمیل قالب فاکتور

    تا اینجای کار اطلاعات زیادی راجع به VLOOKUP به دست آورده‌ایم. نکته‌ی مهمی که باید به یاد داشته باشید این است که VLOOKUP در شرایط دیگری به غیر از کار با دیتابیس‌ها نیز کاربرد دارد.

    با این حال قالب فاکتور ما هنوز کامل نیست و برای تکمیل آن باید کارهای زیر را انجام دهیم:

    1. مقدارهای آزمایشی را از سلول‌های A11 و D11 حذف می‌کنیم. با این کار فرمول VLOOKUP یک خطا به ما نمایش می‌دهد:
      آشنایی با تابع Vlookup در اکسل
      این مساله را با استفاده از توابع «IF» و «ISBLANK» حل می‌کنیم. در واقع فرمول خود را از: به فرمول زیر تغییر می‌دهیم:
    2. این فرمول را به سلول‌های B11، ‏E11،‏ F11 و ستون‌های مشابه در سایر سطرهای فاکتور کپی می‌کنیم. توجه داشته باشید که زمانی که این کار را انجام می‌دهیم، دیگر فرمول‌های ما به جدول دیتابیس صحیح اشاره نخواهند کرد. برای رفع این مشکل می‌توانیم از ارجاع‌های مطلق استفاده کنیم. همچنین می‌توانیم یک نام برای محدوده‌ی دیتابیس خود در نظر گرفته و از آن برای ارجاع استفاده نماییم. با این کار فرمول ما از: به فرمول زیر تغییر می‌کند: حالا می‌توانیم فرمول را به سایر سطرهای خود نیز کپی کنیم.
    3. می‌توانیم سلول‌های خود را قفل کنیم تا کسی به طور اشتباه فرمول‌های ما را در هنگام پر کردن فاکتور تغییر ندهد.
    4. این فایل را به عنوان یک قالب ذخیره می‌کنیم تا هر کسی در شرکت بتواند از آن استفاده کند.
    نظرات ارسال شده ارسال نظر جدید
    برای تبادل نظر، می بایست در سایت وارد شوید

    ورود به سایت
تماس سبد خرید بالا