پنج شنبه , ۳۰ دی ۱۳۹۵
صفحه نخست / توسعه مهارتهای اکسل / فرمولهای پیشرفته و پرکاربرد / چگونه Vlookup می تواند به چپ حرکت کند؟

چگونه Vlookup می تواند به چپ حرکت کند؟

فرمول vlookup  با تمام کاربردهای غبر قابل انکاری که دارد محدودیتهایی را نیز دارد. یکی از این مجدودیتها این است که فرمول vlookup نمیتواند به سمت چپ خود حرکت کند! چرا؟ اولین آرگومان در فرمول Vlookup  یعنی lookup_value همواره باید اولین ستون Table_array باشد. پس vlookup هیچگاه نمی تواند به محدوده پشت lookup_value برود.

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

فرض کنید جدولی مانند زیر داشته باشید و بخواهید بدانید فروش ۲۰۰ واحدی و یا تعداد مشتری ۱۴ نفر متعلق به کدام ویزیتور می باشد؟

حرکت vlookup به چپ-www.iskills.ir

 

نکته: می دانیم فرمول Vlookup بدنبال اولین عدد ۲۰۰ میگردد پس اگر چندین فروش ۲۰۰ واحدی داشته باشید با فرمول Vlookup همیشه اولین مورد را خواهد داشت.فرمول به اینصورت خواهد بود:

=VLOOKUP(200;CHOOSE({1,2};$C$2:$C$5;$A$2:$A$5);2;0)

اما این فرمول چطور کار میکند؟ یادتان هست که گفتیم همیشه باید lookup_array اولین ستون جدول مورد جستجو باشد اما در اینجا عدد ۲۰۰ در جدول مورد نظر ما آخرین ستون است در نتیجه Vlookup نمی تواند به چپ حرکت کند. برای اینکه بتوانیم اینکار را انجام بدهیم از معجزه ای بنام تابع CHOOSE و روش فرمولنویسی آرایه ای کمک میگیریم.

Lookup_Value: 200

Table_array: CHOOSE({1,2};$C$2:$C$5;$A$2:$A$5)

Col_Num:2

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

در اینجا به جدولی نیاز داریم که دو محدوده ستون C و ستون A در آن بعنوان ستون اول  و ستون دوم معرفی شوند تا VLOOKUP بتواند کار خود را انجام دهد. پس با تابع CHOOSE ستون C را که در اینجا ستون سوم محسوب میشود بعنوان اولین ستون جدول و ستون A را بعنوان دومین ستون جدول فرضی مان به اکسل می شناسانیم. با اینکار انگار اکسل را گول زده ایم. در واقع با فرمول CHOOSE برای خودمان یک Table ساخته ایم و اولین ستون آنرا ستون فروش معرفی کرده و دومین ستون آنرا ستون ویزیتورها.

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

در انتها فرموش نکنید بجای Enter باید Ctrl+Shift+Enter بزنید.

حرکت vlookup به چپ-www.iskills.ir

دانلود فایل اکسل حرکت VLOOKUP به چپ

 

 

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

18 + 12 =

Time limit is exhausted. Please reload CAPTCHA.