سبد خرید 0

وبلاگ

تابع VLOOKUP در اکسل و کاربرد آن در فاکتور فروش

تابع VLOOKUP در اکسل به عنوان یکی از مهمترین و شاخص ترین توابع اکسل، پرکاربردترین تابع جست و جو در میان سایر توابع جست و جو موجود در اکسل می باشد که می توان با ارجاع به شیت دیگر یا شیت جاری مقداری را جست و جو کرد.توابع دیگری مثل MATCH,INDEX,HLOOKUP,CHOOSE و … نیز وجود دارد که با ترکیب آنها می شود این کار را انجام داد.اما چیزی که این تابع را با سایر توابع هم رده خود متمایز میکند کاربردی و ساده بودن استفاده از آن می باشد.

کاربرد تابع VLOOKUP در طراحی فاکتور فروش:

شما فرض کنید در یک شیت دیگر لیستی از کالاهای خود را ذخیره کرده اید و در یک شیت دیگر یک فرم فاکتور فروش دارید که میخواهید با زدن کد کالا سایر مشخصات کالا در فرم فاکتور فروش فراخوانی شده و در جایگاه خودشون قرار بگیره.

فرم خام فاکتور فروش
شکل شماره 1 – فرم اولیه فاکتور فروش در sheet1

قرار است ما با وارد کردن کد کالا در شکل شماره 1 ، نام کالا و قیمت واحد آن از شیت دیگر (شکل شماره 2) فراخوانی شده و درشکل شماره 1 در ستون مربوط به خودشان قرار بگیرند.

ثبت کالا در فاکتور فروش
شکل شماره 2 – لیست کالاها در sheet2

معرفی آرگومان های این تابع :

قبل از شروع کار بهتر است ساختار و آرگومان های تابع vlookup در اکسل را بشناسیم.

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

lookup_value : مقداری که تابع قرار است داخل یک لیست به دنبال آن بگردد.

table_array : محدوده ای که قرار است مقدار وارد شده در lookup_value در آنجا جست و جو شود

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

[range_lookup] : این یک آرگومان اختیاری می باشد و نوع حساسیت به مقدار جست و جو رو مشخص میکنه و دو مقدار (0)False یا (1)True دریافت می کند.اگر مقدار 0 یا همان False را وارد کنید به تابع جست و جو تعیین می کنید که دقیقا دنبال همان چیزی بگرده که ما در lookup_value وارد کرده ایم.و اگر مقدار 1 یا همان True را وارد کنید تعیین می کنید که اگر مقداری برابر با آن پیدا نکرد مشابه آن یا اولین مقدار کوچکتر و نزدیک به آن را پیدا کند.


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

نکته :

استفاده از مقدار 1 یا همان True نکات ریز بسیاری دارد و توصیه میکنم اگر با الگوی رفتاری این مقدار آشنایی کامل ندارید همیشه از مقدار 0 یا همان False استفاده کنید.

فرمول نویسی فاکتور فروش:

خب برگردیم به مثال خودمون.

من می خواهم با زدن کد کالا، مشخصات کالا از شیت دیگر فراخوانی بشه.پس مقداری که من میخواهم جست و جو کنم کد کالا هست پس :
lookup_value = کد کالا
ما می توانیم هم مستقیم کد کالا را مثلا 103 وارد کنیم یا اینکه آدرس یک سلول را بنویسیم تا برود از آن سلول مقدار را دریافت کند.یعنی ما اینجا به سلول B4 ارجاع میدیم تا وقتی کد کالا را در سلول B4 وارد کردیم نام کالا را فراخوانی کند.

خب الان این کد کالا قرار است در کدوم لیست جست و جو شود…؟ مشخص است در sheet2 و در محدوده A3:C7 . پس میرویم از sheet2 آن محدوده رو انتخاب میکنیم.
sheet2!$A$3:$C$7 = table_array

خب دوستان ما میخواهیم نام کالا فراخوانی بشه.الان نام کالا در لیست کالاها چندمین ستون محدوده انتخابی ما می باشد…؟
مسلما دومین ستون محدوده انتخابی می باشد پس:
2 = col_index_num

آرگومان آخر رو هم همان 0 یا false وارد میکنیم چون با مشابه ها کاری نداریم.

0 = [range_lookup]

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

(VLOOKUP(B4,$A$3:$C$7,2,0

حال فرمول بالا را در sheet2 و در سلول C4 وارد کرده و به سمت پایین درگ کنید.مطابق شکل شماره 3

استفاده از تابع Vlookup در فاکتور فروش برای جست و جوی نام کالا در شیت دیگر
شکل شماره 3 – استفاده از تابع Vlookup در فاکتور فروش برای جست و جوی نام کالا

اکنون شما با وارد کردن کد کالا مشاهده خواهید کرد که نام کالا در ستون “شرح کالا” فراخوانی می شود.

خب حالا نوبت فراخوانی بهای واحد مربوط به کد کالا می باشد.این کار بسیار آسان است.کافیست کد قبلی را کپی کرده و در سلول E4 قرار دهیم.فقط باید مقدار col_index_num را به 3 تغییر دهیم.زیرا بهای واحد در سومین ستون از محدوده انتخابی ما قرار دارد.یعنی فرمول نهایی به شکل زیر می شود:

(VLOOKUP(B4,sheet2!$A$3:$C$7,3,0

نتیجه نهایی تابع VLOOKUP برای فراخوانی اطلاعات از شیت دیگر
شکل شماره 4 – نتیجه نهایی تابع VLOOKUP برای فراخوانی اطلاعات

چند نکته درمورد تابع VLOOKUP :

تابع vlookup فقط یک آیتم را فراخوانی می کند.

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

مقداری که قصد جست و جوی

آنرا داریم حتما باید در اولین ستون محدوده انتخابی ما باشد.

فرض کنید در فرم فاکتور در sheet1 به جای کد کالا می خواستیم با انتخاب نام کالا ، بهای واحد آن فراخوانی شود.اینجا دیگر ما نمی توانیم محدوده sheet2!$A$3:$C$7 را انتخاب کنیم.زیرا آیتمی که ما دنبال آن هستیم در اولین ستون قرار ندارد.در این حالت باید محدوده sheet2!$B$3:$C$7 را انتخاب کنیم.

شماره ستون در آرگومان col_index_num بر اساس محدوده انتخابی تعیین می شود و هیچ ارتباطی به شماره ستون شیت ندارد.

به مثال گزینه 2 در بالا دقت کنید.محدوده جست و جوی تابع vlookup تغییر پیدا کرده است.در این حالت در sheet2 ، “نام کالا” ستون 1 محدوده و “قیمت” ستون 2 محدوده انتخابی محسوب می شود.یعنی اگر بخواهیم در sheet1 با انتخاب نام کالا، قیمت واحد آن فراخوانی شود باید در سلول E4 فرمول را بصورت زیر بنویسیم:
(VLOOKUP($C$4,sheet2!$B$3:$C$7,2,0=

زمانی که تابع vlookup مقداری را پیدا نکند خطای N/A# را نشان می دهد.

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

(“”,(IFERROR(VLOOKUP(C4,sheet2!$B$3:$C$7,2,0=



منابع:

support.office.com

exceljet.net

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

6 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • با سلام و ارزوی بهترین ها
    با استفاده از تابع ویلوکاپ چگونه میشود تابع 3شرطی را جستجو کرد

      • سلام
        یه سوال
        آیا کانکاتنیت رو میشه به صورت فالس و ترو نوشت؟
        مثلاً بنویسیم
        ایف
        کانکاتنیت آ ۱ و بی ۱ مساوی است با یکی از کانکاتنیت سی ۱ تا سی ۲۰ و دی ۱ تا دی ۲۰؟
        به دلیل سنگین شدن برنامه نمی‌خوام از آرایه استفاده کنم
        به دلیل زیاد بودن دیتا (صد هزار به بالا) نمی‌خوام از ستون کمکی استفاده کنم
        ببین اکسل در countifs این کار رو انجام داده مگه میشه در لوکاپ انجام نداده باشه؟
        لطفا فرمول ترکیبی پیشنهادی خودتون رو بنویسید.
        با سپاس

  • سلام خسته نباشيد
    يه جدول كه فقط خريد يك كالا داخلش ثبت بشه به اسم فروشندگان مختلف، فاكتور خريدش چجوري باشه كه بفهمم هر فروشنده چقدر از اون كالا به من فروخته؟

دیدگاهتان را بنویسید