دوره حضوری برنامه نویسی اکسل | استاد : فرشید میدانی | شروع از سه شنبه 5 تیر ماه 1397

ثبت نام و کسب اطلاعات بیشتر شماره 41867  داخلی 412

آموزش تابع VLOOKUP در اکسل - قدم به قدم

بهتر است که کار آموزش تابع VLOOKUP را از این نقطه آغاز کنیم که چرا این تابع این همه مهم است؟ و چرا باید هر کاربر اکسل آنرا در حد تسلط یاد بگیرد؟

من در کلاس های اکسل همواره این را می گویم که :
اگر کسی vlookup  ، Sumifs و Pivot را بلد نیست، اکسل را نمی­‌داند.

پاسخ در توجیه اهمیت VLOOKUP ساده است.

تابع VLOOKUP برای ادغام داده‌­ها در اکسل بکار می­رود.

 

همیشه در اکسل داده­‌های ما در یک شیت متمرکز نیست. داده‌­ها در شیت ها و فایل های مختلف پراکنده­اند و همواره ما به گزارش و تحلیلی نیاز داریم که لازمه آن ادغام این اطلاعات پراکنده در یک شیت می­‌باشد.

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

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

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

 

فایل اکسل آموزش VLOOKUP این مقاله را از انتهای همین صفحه دانلود کنید.

 

درک رفتار تابع VLOOKUP

کارنامه ساده زیر را در نظر بگیرید و سپس به این سوالات پاسخ دهید؟

جدول اکسل برای تابع VLOOKUP

 

الف) در جلوی اسم بهار در سومین ستون جدول چه مقداری نوشته شده است؟

ب) در جلوی کد دانش آموزی 2000 در دومین ستون جدول چه مقداری نوشته شده است؟

 

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

بنابراین تابع VLOOKUP یک مقداری (که به آن معلوم می­گوییم) را در یک جدول پیدا می کند و می گوید که جلوی آن مقدار چه مقداری تایپ شده است. دقیقا همان کاری که شما برای پاسخ به سوالات الف و ب  انجام دادید.

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

لطفا 10 بار جمله زیر را هر 8 ساعت یکبار با صدای بلند برای خودتان تکرار کنید:
VLOOKUP  یک چیزی را پیدا می کند و می گوید که جلوی آن چیست. فقط همین.

 

تفسیر واژه «جدول» در VLOOKUP

در سوالات الف و ب ما با ابهامی مواجه هستیم. سومین ستون جدول کجاست؟

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

تعریف جدول و شماره گذاری آن در جمله زیر دقیقا مشخص است:
ستون معلوم ها، اولین ستون جدول است.

بگذارید به سوال های پرسیده شده با این تعریف ارائه شده پاسخ دهیم.

الف) در جلوی اسم بهار در سومین ستون جدول چه مقداری نوشته شده است؟

برای ما در این سوال نام دانشجو مشخص شده، بنابراین ستون معلوم های ما، ستون «نام دانش آموز» است و بنابراین اولین ستون جدول در اینجا  «نام دانش آموز» خواهد بود و دومین ستون جدول در «فارسی» و سومین ستون جدول «قرآن» می باشد. پس پاسخ این سوال عدد 14.5 خواهد بود.

 

شماره ستون های جدول اکسل در تابع VLOOKUP

 

ب) در جلوی کد دانش آموزی 2000 در دومین ستون جدول چه مقداری نوشته شده است؟

برای ما در این سوال کد دانش آموز مشخص شده، بنابراین ستون معلوم های ما، ستون «کد دانش آموز» است و بنابراین اولین ستون جدول در اینجا «کد دانش آموز» خواهد بود و دومین ستون جدول «نام دانش آموز» و سومین ستون جدول «فارسی» می باشد. پس پاسخ این سوال عدد «علی» خواهد بود.

 

شماره ستون معلوم ها در تابع VLOOKUP

 

نکته: بسیاری از کاربران به اشتباه تصور می کنند که اولین ستون باید ستون A در اکسل باشد.

 

آموزش فرمول VLOOKUP 

تابع VLOOKUP از شما 4 ورودی می­خواهد و سپس به شما پاسخ را خواهد داد. بگذارید با حل قدم به قدم پرسش الف شروع کنیم.

 در جلوی اسم بهار در سومین ستون جدول چه مقداری نوشته شده است؟

 

جدول اکسل برای تابع VLOOKUP

 

ورودی 1)

 در ابتدا VLOOKUP از شما مقدار معلوم را می­خواهد، پس خواهیم نوشت:

=VLOOKUP("BAHAR"

یادآوری: هر گاه در فرمولی یک متن نوشتیم باید آن متن در داخل علامت (دبل کوت) یعنی " " قرار گیرد.

 

ورودی 2)

 سپس VLOOKUP از شما می­خواهد که جدولی را برای جستجو مشخص کنید. (در واقع کاملا حق هم دارد، از کجا بدانید که در کدام شیت و یا ستون­ها باید جستجو را انجام دهد) . دقت داشته باشید که حتما باید این جدول دو شرط زیر را داشته باشد:

الف) اولین ستون جدول، ستونی باشد که مقادیر معلوم در آن تایپ شده است.

با توجه به تصویر چون نام دانش آموزان در ستون C وارد شده است، باید جدول ما از ستون C شروع شود.

ب) جدول شما شامل ستون مجهول ها هم باشد.

چون سومین ستون جدول (یعنی نمره قرآن) را باید پیدا می­کردیم، حتما باید جدول ما حداقل تا ستون E باشد. بنابراین ادامه تابع ما می­شود

=VLOOKUP("BAHAR" , C:E

یادآوری: علامت «:» در اکسل به معنای «تا» است. بنابراین C:E را باید بخوانیم ستون C تا E.

نکته: اگر جدول را بیشتر انتخاب کردیم مثلا نوشتیم C:Z، هیچ اشکالی ندارد.

 

ورودی 3)

 خوب حالا که معلوم و جدول را مشخص کردیم، نوبت می­رسد که به VLOOKUP بگوییم که مجهول ما کجاست. کافی است که جای ستون مجهول را به آن بگوییم. یعنی بگوییم در این جدول (یعنی C:E) سومین ستون حاوی مقداری است که ما آنرا لازم داریم. بنابراین باید بنویسیم:

=VLOOKUP("BAHAR" , C:E , 3

توجه 1: حتما باید سومین ورودی یک عدد باشد. در واقع شما نمی توانید نام ستون مجهول را بدهید.

یادآوری: مجدد تاکید می­شود که شمارش ستون ها باید نسبت به «جدول» باشد.

 

ورودی 4)

این ورودی را فعلا طوطی وارد عدد 0 بگذارید. دلیلش را فعلا نپرسید. در یک مقاله دیگر (اگر همت و فرصتی شد) توضیح خواهیم داد.

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

=VLOOKUP("BAHAR" , C:E , 3 , 0)

و در پاسخ عدد 14.5 را خواهید داشت.

 

حل مساله ب)

سوال) در جلوی کد دانش آموزی 2000 در دومین ستون جدول چه مقداری نوشته شده است؟

فرمول شما به شکل زیر خواهد بود:

=VLOOKUP(2000 , B:F , 2 , 0)

حالا بگذارید من چند تا سوال بپرسم و مثلا شما پاسخ بدهید:

1) چرا 2000 را داخل علامت " "  قرار ندادید؟

پاسخ شما : چون یک عدد است و فقط متن ها را داخل علامت " "  می گذاریم.

 

2) چرا از ستون B، جدول را شروع کردید؟

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

 

3) چرا جدول را B:C ننوشتید؟

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

 

4) عدد 2 در فرمول بالا چیست؟

پاسخ شما: دومین ستون منطقه B:F است و مجهول های ما در این ستون قرار دارند.

 

5) عدد 0 در فرمول بالا چیست؟

پاسخ شما: استاد گفته 0 بگذارید بعدا توضیح می دهم.

 

نکات نهایی تابع VLOOKUP

نکته 1: حروف بزرگ و کوچک انگلیسی متفاوت نیستند. یعنی BAHAR = baHaR است.

نکته 2: عدد و متن متفاوتند. یعنی 2 = "2" نیست. (در یک مقاله دیگر باید اینرا دقیق توضیح دهیم اما به صورت خلاصه مربوط می شود به Format Cell Text .)

نکته 3: همواره فرض ما این است که یک مقدار معلوم وجود دارد. اگر مقادیر معلوم ها تکراری بودند، تابع vlookup اولین مقدار را می­یابد.

نکته 4: تابع vlookup از wildcard ها پشتیبانی می کند یعنی می­توانید به جای "bahar" بنویسید "*aha*" و یعنی سلولی که در آن aha وجود دارد.

نکته 5: توجه داشته باشید که دو حرف "ی" و "ک" در عربی و فارسی متفاوت هستند. (بازهم در مقاله دیگری باید به موضوع چرا VLOOKUP یک مقدار را نمی یابد، بپردازیم)

نکته 6: تابع vlookup فقط می تواند رو به جلو جستجو کند. به همین دلیل است که همواره ستون معلوم ها را در ابتدای یک لیست می نویسیم. (برای یافتن مقادیر پشت سر مقدار معلوم باید از ترکیب دو تابع index  و match استفاده کنیم که در یک مقاله دیگر باید به آن پرداخت).

نکته 7: لطفا شما در قسمت نظرات سایر نکات، تجربیات را برای ما بنویسید.

نویسنده: فرشید میدانی

دیدگاه‌ها

به این مطلب پاسخ دهید

حسنی در تاریخ 13 آبان 1396 نوشته:

مثل همیشه عالی بود

علي رضا در تاریخ 22 آذر 1396 نوشته:

واقعاً عالی بود . بنده از نحوه بیان مطالب خیلی خوشم آمد و بسیار استفاده بردم. با تشکر

محمد گودرزی در تاریخ 12 بهمن 1396 نوشته:

ممنون از توضیح کاملتون. لذت بردم از روش تدریستون.

مصطفی در تاریخ 14 بهمن 1396 نوشته:

سلام
واقعا ساده و روان بود. ممنون

محمد2202 در تاریخ 5 اسفند 1396 نوشته:

سلام
الان اگر بخواهیم که کد کسی که نمره فارسی را 20 گرفته است بدست آوریم به صورت زیر می شود
vlookup(20,b:d,1,0)

فرشید میدانی در تاریخ 7 اسفند 1396 نوشته:

تابع Vlookup فقط جلوی خودش را می یابد و نمی تواند مقداری که پشت سرش وارد شده را پیدا کند. برای اینکار دو تابع index / match معمولا بکار می رود. فرمول مورد نظر شما می شود:

=INDEX(C:C,MATCH(20,D:D,0))

سهیلا امیرسیافی در تاریخ 2 فروردين 1397 نوشته:

با سپاس

1) فرمول index و match هم همین کار را انجام میدهد. اما شنیده ام که فرمول choose هم همین کار را میکند اما حجم کمتری دارد. آیا ممکن است که درباره آن هم توضیح دهید.

2) به نظر من در استفاده از فرمول vlookup علاوه بر فرمول، موارد استفاده از فرمول خیلی مهم است.

مثلاً در یک جدول که پیوت از آن گرفته شده روی اطلاعات یک ستون بررسی هایی را انجام داده ایم. مثلاً از بین 1200 نفر که در جدول اصلی در یکی از ستونها هر کدام بین 1 تا 15 بار تکرار شده اند کسانی که وضعیت خاصی دارند را به صورت دستی انتخاب کرده ایم (خصوصیتی که در جدول اصلی نیست). مثلاً کسانی را انتخاب میکنیم که قصد داریم در یک پروژه کاری مشغول شوند.
برای اینکه در جدول اصلی این افراد را مشخص کنیم به لیست انتخابی یک نام میدهیم و در جدول اصلی با انتخاب ستون 1 از این لیست (در واقع یک ستون بیشتر نداریم) این افراد را در یک ستون مشخص میکنیم.
هم چنین میتونیم در همان حال با ترکیب این فرمول با فرمول IF، به جای نام افراد یک عبارت مثلاً نام پروژه مورد نظر یا یک کد را اختصاص بدهیم تا در پیوت بتوانیم آنها را فیلتر کنیم.
به جای فرمول IF میتوان بعد از فراخوانی اسامی کل اسامی را فیلتر کنیم و بعد نامشان یک یک جا عوض کنیم.

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

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

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

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

فرضاً واحد ۱ به صورت های زیر در جداول آورده شده است.

واحد1
واحد یک
واحدیک
واحد اول
واحداول
واحد-یک
واحد-اول
اولین واحد
...

میبینم که یک منظور به 8 مدل نوشته شده است. میتوان برای یک بار یک کلید تعریف کنیم و هر بار که جداول را دریافت کردیم این اطلاعات را برایمان به همان صورت که خودمان میخواهیم (که میتواند کلاً متفاوت از این کلمات باشد) برایمان تغییر دهد.
در کارم به مواردی برخورد کرده ام که یک عنوان به بیش از 7 صورت مختلف نوشته شده است که عملاً گزارشگیری را غیرممکن میکند و یکسان کردن آنها در هر بار بیش از ۲ ساعت وقت نیاز دارد ضمن اینکه کار بسیار خسته کننده ای است. اما با تعریف این کلید کار به کمتر از یک دقیقه کاهش میابد ضمن اینکه با این روش چنانچه صورتهای دیگری هم برایمان ارسال شد تنها کافی است که مورد جدید را به کلید اضافه کنیم.

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

با تشکر فراوان از استاد میدانی که فرصت یادگیری و ارتقاء را برایمان فراهم کرده اند. به قول حافظ :

خداش خیردهاد آن که این عمارت کرد

.

فرشید میدانی در تاریخ 10 فروردين 1397 نوشته:

1) فرمول index و match هم همین کار را انجام میدهد. اما شنیده ام که فرمول choose هم همین کار را میکند اما حجم کمتری دارد. آیا ممکن است که درباره آن هم توضیح دهید.

 

بله با Index / Match هم می توان اینکار را انجام داد اما وقتی از آنها استفاده می کنیم که بخواهیم Vlookup رو به عقب (برعکس) انجام دهید. یعنی وقتی که بخواهید چیزی را پیدا کنید و سپس مقداری که پشت سر آن (نه جلوی آن) وارد شده است، را برگردانید.

در خصوص Choose اصلا!!! اینطوری که شما تصور می کنید نیست. در واقعا اصلا Vlookup نمی کند. بلکه بسیار ساده تر است. یعنی فصل کنید که می خواهید یک عدد را به یک مقدار تبدیل کنید مثلا عدد 1 معادل فصل bahar و الی آخر ، بنابراین می نویسید:

=choose(A1 , "bahar","tabestan","paeez","zemestan")

یعنی در سلول A1 یک مقدار بین 1 تا 4 وارد می کنید و تابع choose به شما نام آن فصل را می دهد.

بنابراین تابع choose وقتی کاربرد دارد که بخواهید در یک فهرست / لیست مرتب (مانند فصل های سال / ماه های سال) یک مقدار را بیابید.

ابوذر محمدی پیراهی در تاریخ 5 فروردين 1397 نوشته:

درود بر شما
چقدر کاربردی و زیبا یاد دادید
وجود همانندهای شما مایه ی سربلندی میهن است

محمد فاضل غرشی در تاریخ 9 فروردين 1397 نوشته:

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

فرشید میدانی در تاریخ 10 فروردين 1397 نوشته:

دقیقا از همین تکنیک استفاده می شود و شما برای آوردن هر قلم اطلاعاتی مانند نام، نمره درس ریاضی ، باید یک Vlookupجداگانه بنویسید.

محمد فاضل غرشی در تاریخ 10 فروردين 1397 نوشته:

ببخشید یه سوال دیگه میشه تابع IF رو با تابع Vlookup ترکیب کرد ؟؟؟
یعنی مثلا ما بتونیم اطلاعاتی که در شیت 1 وارد کردیم را با نوشتن 1 عدد یا یک کد مانند 2000 که در شیت 1 این کد را داریم را در شیت 2 باز خوانی کنیم.
امکان این کار با ترکیب این 2 تابع آیا وجود دارد یا خیر ؟
ممنون از پاسخ گویی شما

فرشید میدانی در تاریخ 10 فروردين 1397 نوشته:

لازم به تابع جدیدی نیست . این کار دقیقا با Vlookup انجام می شود. مقاله را دقیق مطالعه کنید.