تابع VLOOKUP در اکسل و کاربرد آن در فاکتور فروش
تابع VLOOKUP در اکسل به عنوان یکی از مهمترین و شاخص ترین توابع اکسل، پرکاربردترین تابع جست و جو در میان سایر توابع جست و جو موجود در اکسل می باشد که می توان با ارجاع به شیت دیگر یا شیت جاری مقداری را جست و جو کرد.توابع دیگری مثل MATCH,INDEX,HLOOKUP,CHOOSE و … نیز وجود دارد که با ترکیب آنها می شود این کار را انجام داد.اما چیزی که این تابع را با سایر توابع هم رده خود متمایز میکند کاربردی و ساده بودن استفاده از آن می باشد.
کاربرد تابع VLOOKUP در طراحی فاکتور فروش:
شما فرض کنید در یک شیت دیگر لیستی از کالاهای خود را ذخیره کرده اید و در یک شیت دیگر یک فرم فاکتور فروش دارید که میخواهید با زدن کد کالا سایر مشخصات کالا در فرم فاکتور فروش فراخوانی شده و در جایگاه خودشون قرار بگیره.
قرار است ما با وارد کردن کد کالا در شکل شماره ۱ ، نام کالا و قیمت واحد آن از شیت دیگر (شکل شماره ۲) فراخوانی شده و درشکل شماره ۱ در ستون مربوط به خودشان قرار بگیرند.
معرفی آرگومان های این تابع :
قبل از شروع کار بهتر است ساختار و آرگومان های تابع vlookup در اکسل را بشناسیم.
([VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup
lookup_value : مقداری که تابع قرار است داخل یک لیست به دنبال آن بگردد.
table_array : محدوده ای که قرار است مقدار وارد شده در lookup_value در آنجا جست و جو شود
col_index_num : شماره ستونی که مقدار موردنظر ما در آن ستون قرار دارد.این شماره ستون ارتباطی به شماره شیت اکسل ندارد.
[range_lookup] : این یک آرگومان اختیاری می باشد و نوع حساسیت به مقدار جست و جو رو مشخص میکنه و دو مقدار (۰)False یا (۱)True دریافت می کند.اگر مقدار ۰ یا همان False را وارد کنید به تابع جست و جو تعیین می کنید که دقیقا دنبال همان چیزی بگرده که ما در lookup_value وارد کرده ایم.و اگر مقدار ۱ یا همان True را وارد کنید تعیین می کنید که اگر مقداری برابر با آن پیدا نکرد مشابه آن یا اولین مقدار کوچکتر و نزدیک به آن را پیدا کند.
مثلا اگر شما در لیست اسم علیرضا را داشته باشید و مقدار False را الگوی جست و جو تعیین کنید و دنبال علی بودید، درصورت پیدا نکردن با خطا مواجه می شوید ولی اگر علی را پیدا کرد مقدار را برای شما نشان خواهد داد.
اما اگر مقدار ۱ یا همان True را معیار جست و جو تعیین کنید اگر علی را پیدا نکرد مقدار علیرضا را نشان خواهد داد.چون گفتیم که اگر دقیقا همان مقدار را پیدا نکرد مشابه آن اسم و کوچکترین مقدار نزدیک به آنرا برای ما نشان دهد.
نکته :
استفاده از مقدار ۱ یا همان True نکات ریز بسیاری دارد و توصیه میکنم اگر با الگوی رفتاری این مقدار آشنایی کامل ندارید همیشه از مقدار ۰ یا همان False استفاده کنید.
فرمول نویسی فاکتور فروش:
خب برگردیم به مثال خودمون.
من می خواهم با زدن کد کالا، مشخصات کالا از شیت دیگر فراخوانی بشه.پس مقداری که من میخواهم جست و جو کنم کد کالا هست پس :
lookup_value = کد کالا
ما می توانیم هم مستقیم کد کالا را مثلا ۱۰۳ وارد کنیم یا اینکه آدرس یک سلول را بنویسیم تا برود از آن سلول مقدار را دریافت کند.یعنی ما اینجا به سلول B4 ارجاع میدیم تا وقتی کد کالا را در سلول B4 وارد کردیم نام کالا را فراخوانی کند.
خب الان این کد کالا قرار است در کدوم لیست جست و جو شود…؟ مشخص است در sheet2 و در محدوده A3:C7 . پس میرویم از sheet2 آن محدوده رو انتخاب میکنیم.
sheet2!$A$3:$C$7 = table_array
خب دوستان ما میخواهیم نام کالا فراخوانی بشه.الان نام کالا در لیست کالاها چندمین ستون محدوده انتخابی ما می باشد…؟
مسلما دومین ستون محدوده انتخابی می باشد پس:
۲ = col_index_num
آرگومان آخر رو هم همان ۰ یا false وارد میکنیم چون با مشابه ها کاری نداریم.
۰ = [range_lookup]
فرمول نهایی تابع vlookup به شکل زیر خواهد بود :
(VLOOKUP(B4,$A$3:$C$7,2,0
حال فرمول بالا را در sheet2 و در سلول C4 وارد کرده و به سمت پایین درگ کنید.مطابق شکل شماره ۳
اکنون شما با وارد کردن کد کالا مشاهده خواهید کرد که نام کالا در ستون “شرح کالا” فراخوانی می شود.
خب حالا نوبت فراخوانی بهای واحد مربوط به کد کالا می باشد.این کار بسیار آسان است.کافیست کد قبلی را کپی کرده و در سلول E4 قرار دهیم.فقط باید مقدار col_index_num را به ۳ تغییر دهیم.زیرا بهای واحد در سومین ستون از محدوده انتخابی ما قرار دارد.یعنی فرمول نهایی به شکل زیر می شود:
(VLOOKUP(B4,sheet2!$A$3:$C$7,3,0
چند نکته درمورد تابع VLOOKUP :
تابع vlookup فقط یک آیتم را فراخوانی می کند.
مثلا فرض کنید شما چندین فاکتور برای یک شخص ثبت کرده اید.حالا با این تابع می خواهید با زدن نام مشتری تمام فاکتورهای صادر شده برای این مشتری را فراخوانی کند.متاسفانه این تابع جست و جو چنین قابلیتی ندارد.یعنی زمانی که اولین فاکتور صادر شده را پیدا کند آنرا فراخوانی می کند و با بقیه فاکتورهای صادر شده کاری ندارد.
مقداری که قصد جست و جوی آنرا داریم حتما باید در اولین ستون محدوده انتخابی ما باشد.
فرض کنید در فرم فاکتور در sheet1 به جای کد کالا می خواستیم با انتخاب نام کالا ، بهای واحد آن فراخوانی شود.اینجا دیگر ما نمی توانیم محدوده sheet2!$A$3:$C$7 را انتخاب کنیم.زیرا آیتمی که ما دنبال آن هستیم در اولین ستون قرار ندارد.در این حالت باید محدوده sheet2!$B$3:$C$7 را انتخاب کنیم.
شماره ستون در آرگومان col_index_num بر اساس محدوده انتخابی تعیین می شود و هیچ ارتباطی به شماره ستون شیت ندارد.
به مثال گزینه ۲ در بالا دقت کنید.محدوده جست و جوی تابع vlookup تغییر پیدا کرده است.در این حالت در sheet2 ، “نام کالا” ستون ۱ محدوده و “قیمت” ستون ۲ محدوده انتخابی محسوب می شود.یعنی اگر بخواهیم در 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=
مشاهده ویدئویی این آموزش:
[aparat id=”pVzPY”]
منابع:
درباره جاوید
عاشق اکسل هستم و بیش از 12 ساله دارم اکسل کار می کنم. در این مسیر آموزش ها و فایل های حرفه ای ارائه دادم و همچنان در این مسیر با علاقه پیش میرم چون امروزه اکسل به یک مهارت انکار نشدنی تبدیل شده
سایر نوشته های جاویدمطالب زیر را حتما مطالعه کنید
فایل اکسل محاسبه مالیات حقوق سال ۱۴۰۱ + آموزش ویدئویی فرمولنویسی مالیات
راست چین در اکسل و نحوه راست به چپ کردن شیت اکسل
برای چاپ اطلاعات داخل فرم از قبل پرینت شده در اکسل چکار کنیم؟
فقط با ۳ سوت فرم لاگین در اکسل طراحی کن
آموزش طراحی UserForm گرافیکی و جذاب در اکسل
تبدیل عدد به حروف در اکسل + دانلود افزونه ABH
4 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام روزتون بخیر. ممنون از آموزش خوبتون. من میخواستم از همین جدول شما استفاده کنم به اضافه یک ستون اضافه تر که بشه ستون خروج که بعد از وارد کردن عدد همون کالا تو شیت ورودی ها تعدادش کم بشه/ ممنون میشم کمکم کنید/
سلام. وقت بخیر
کنترل موجودی انبار رو بهتره تو شیت مجزا انجام بدید.
میتونید دوره جامع آموزش طراحی فرم انبارداری رو مشاهده کنید.
سلام
ممکنه ما اسم کالا رو بنویسیم و اون قیمتش رو بزنه یعنی لازم نباشه کد کالا رو وارد کنیم
سلام. وقت بخیر
بله. براساس هر آیتمی میتونید فراخوانی کنید.