فهرست
اخیرا مقاله ای را به یکی از کاربردی ترین توابع اکسل به نام اختصاص داده ایم VPR و نشان داد که چگونه می توان از آن برای استخراج اطلاعات مورد نیاز از یک پایگاه داده در یک سلول کاربرگ استفاده کرد. همچنین اشاره کردیم که دو مورد استفاده برای عملکرد وجود دارد VPR و تنها یکی از آنها با پرس و جوهای پایگاه داده سروکار دارد. در این مقاله روش دیگری را که کمتر شناخته شده است برای استفاده از تابع یاد خواهید گرفت VPR در اکسل
اگر هنوز این کار را انجام نداده اید، حتما آخرین مقاله در مورد عملکرد را بخوانید VPR، زیرا تمام اطلاعات زیر فرض می کند که شما قبلاً با اصول توضیح داده شده در مقاله اول آشنا هستید.
هنگام کار با پایگاه های داده، توابع VPR یک شناسه منحصربفرد ارسال می شود که برای شناسایی اطلاعاتی که می خواهیم پیدا کنیم (مثلاً کد محصول یا شماره شناسایی مشتری) استفاده می شود. این کد منحصر به فرد باید در پایگاه داده وجود داشته باشد، در غیر این صورت VPR یک خطا گزارش خواهد کرد. در این مقاله به این روش استفاده از تابع خواهیم پرداخت VPRزمانی که id اصلا در پایگاه داده وجود ندارد. مثل اینکه تابع VPR به حالت تقریبی تغییر می کند، و انتخاب می کند که چه داده هایی به ما ارائه دهد وقتی می خواهیم چیزی پیدا کنیم. در شرایط خاص، این دقیقا همان چیزی است که مورد نیاز است.
نمونه ای از زندگی ما تعیین تکلیف کردیم
بیایید این مقاله را با یک مثال واقعی - محاسبه کمیسیون بر اساس طیف گسترده‌ای از معیارهای فروش - توضیح دهیم. ما با یک گزینه بسیار ساده شروع می کنیم و سپس به تدریج آن را پیچیده می کنیم تا زمانی که تنها راه حل منطقی مشکل استفاده از تابع باشد. VPR. سناریوی اولیه برای کار ساختگی ما به شرح زیر است: اگر فروشنده ای بیش از 30000 دلار در یک سال فروش داشته باشد، کمیسیون او 30٪ است. در غیر این صورت، کمیسیون فقط 20٪ است. بیایید آن را در قالب یک جدول قرار دهیم:
فروشنده اطلاعات فروش خود را در سلول B1 وارد می کند و فرمول موجود در سلول B2 نرخ کمیسیون درستی را که فروشنده می تواند انتظار داشته باشد را تعیین می کند. به نوبه خود، نرخ حاصل در سلول B3 برای محاسبه کل کمیسیونی که فروشنده باید دریافت کند استفاده می شود (به سادگی سلول های B1 و B2 را ضرب می کنیم).
جالب ترین بخش جدول در سلول B2 موجود است - این فرمول برای تعیین نرخ کمیسیون است. این فرمول حاوی یک تابع اکسل به نام است IF (اگر). برای آن دسته از خوانندگانی که با این تابع آشنا نیستند، نحوه کارکرد آن را توضیح خواهم داد:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
وضعیت آرگومان تابعی است که مقدار هر کدام را می گیرد کد واقعی (درست)، یا نادرست (کاذب). در مثال بالا عبارت B1
آیا درست است که B1 کمتر از B5 است؟
یا می توانید طور دیگری بگویید:
آیا این درست است که مجموع فروش سال کمتر از مقدار آستانه است؟
اگر به این سوال پاسخ دهیم بله (TRUE)، سپس تابع برمی گردد ارزش اگر درست باشد (مقدار اگر TRUE باشد). در مورد ما، این مقدار سلول B6 خواهد بود، یعنی نرخ کمیسیون زمانی که کل فروش کمتر از آستانه باشد. اگر به سوال پاسخ دهیم نه (FALSE) سپس برمی گردد ارزش اگر نادرست باشد (مقدار اگر FALSE باشد). در مورد ما، این مقدار سلول B7 است، یعنی نرخ کمیسیون زمانی که کل فروش بالاتر از آستانه باشد.
همانطور که می بینید، اگر کل فروش 20000 دلار را در نظر بگیریم، نرخ کمیسیون 2% در سلول B20 دریافت می کنیم. اگر مقدار 40000 دلار را وارد کنیم، نرخ کمیسیون 30٪ تغییر می کند:
جدول ما اینگونه کار می کند.
ما کار را پیچیده می کنیم
بیایید کار را کمی سخت تر کنیم. بیایید یک آستانه دیگر تعیین کنیم: اگر فروشنده بیش از 40000 دلار درآمد داشته باشد، نرخ کمیسیون به 40٪ افزایش می یابد:
به نظر می رسد همه چیز ساده و واضح است، اما فرمول ما در سلول B2 به طور قابل توجهی پیچیده تر می شود. اگر به فرمول دقت کنید، آرگومان سوم تابع را خواهید دید IF (IF) به یکی دیگر از عملکردهای کامل تبدیل شد IF (اگر). این ساختار را تودرتو کردن توابع در یکدیگر می نامند. اکسل با خوشحالی به این ساختارها اجازه می دهد، و حتی کار می کنند، اما خواندن و درک آنها بسیار سخت تر است.
ما به جزئیات فنی نمی پردازیم - چرا و چگونه کار می کند، و ما به جزئیات نوشتن توابع تو در تو نمی پردازیم. پس از همه، این یک مقاله اختصاص داده شده به عملکرد است VPR، یک راهنمای کامل برای اکسل نیست.
در هر صورت، فرمول پیچیده تر می شود! اگر برای فروشندگانی که بیش از 50 دلار فروش دارند، گزینه دیگری را برای نرخ کمیسیون 50000٪ معرفی کنیم. و اگر شخصی بیش از 60000 دلار فروخته باشد آیا 60% پورسانت می پردازد؟
حالا فرمول سلول B2، حتی اگر بدون خطا نوشته شده باشد، کاملاً ناخوانا شده است. فکر می کنم تعداد کمی هستند که بخواهند از فرمول هایی با 4 سطح تودرتو در پروژه های خود استفاده کنند. باید راه ساده تری هم باشد؟!
و چنین راهی وجود دارد! تابع به ما کمک خواهد کرد VPR.
ما تابع VLOOKUP را برای حل مشکل اعمال می کنیم
بیایید طرح میزمان را کمی تغییر دهیم. ما همه فیلدها و داده های یکسان را حفظ می کنیم، اما آنها را به روشی جدید و فشرده تر ترتیب می دهیم:
یک لحظه وقت بگذارید و از جدول جدید مطمئن شوید جدول امتیازات شامل داده های مشابه جدول آستانه قبلی است.
ایده اصلی استفاده از تابع است VPR برای تعیین نرخ تعرفه مورد نظر طبق جدول جدول امتیازات بسته به حجم فروش لطفا توجه داشته باشید که فروشنده می تواند کالا را به مبلغی بفروشد که با یکی از پنج آستانه جدول برابری نمی کند. به عنوان مثال، او می تواند به قیمت 34988 دلار بفروشد، اما چنین مبلغی وجود ندارد. بیایید ببینیم عملکرد چگونه است VPR می تواند با چنین شرایطی مقابله کند.
درج یک تابع VLOOKUP
سلول B2 (جایی که می خواهیم فرمول خود را درج کنیم) را انتخاب کرده و پیدا کنید VLOOKUP (VLOOKUP) در کتابخانه توابع اکسل: فرمول (فرمول ها) > کتابخانه تابع (کتابخانه تابع) > جستجو و مرجع (مرجع و آرایه ها).
یک کادر محاوره ای ظاهر می شود استدلال های عملکرد (آگومان های تابع). مقادیر آرگومان ها را یکی یکی پر می کنیم و شروع می کنیم Lookup_value (Lookup_value). در این مثال، این مقدار کل فروش از سلول B1 است. مکان نما را در فیلد قرار دهید Lookup_value (Lookup_value) و سلول B1 را انتخاب کنید.
در مرحله بعد، باید توابع را مشخص کنید VPRکجا به دنبال داده بگردیم در مثال ما، این یک جدول است جدول امتیازات. مکان نما را در فیلد قرار دهید آرایه جدولی (جدول) و کل جدول را انتخاب کنید جدول امتیازاتبه جز هدرها
در مرحله بعد، باید مشخص کنیم که با استفاده از فرمول، داده ها را از کدام ستون استخراج کنیم. ما به نرخ کمیسیون علاقه مندیم که در ستون دوم جدول است. بنابراین، برای استدلال Col_index_num (Column_number) مقدار 2 را وارد کنید.
و در نهایت آخرین استدلال را معرفی می کنیم - Range_lookup (Interval_lookup).
مهم: استفاده از این آرگومان است که بین دو روش اعمال تابع تفاوت ایجاد می کند VPR. هنگام کار با پایگاه های داده، آرگومان Range_lookup (range_lookup) همیشه باید یک مقدار داشته باشد نادرست (FALSE) برای جستجوی یک تطابق دقیق. در استفاده ما از تابع VPR، باید این قسمت را خالی بگذاریم یا مقداری را وارد کنیم کد واقعی (درست است، واقعی). انتخاب صحیح این گزینه بسیار مهم است.
برای روشن تر شدن مطلب به معرفی می پردازیم کد واقعی (درست) در میدان Range_lookup (Interval_lookup). اگر چه، اگر فیلد را خالی بگذارید، این یک خطا نخواهد بود، زیرا کد واقعی مقدار پیش فرض آن است:
ما تمام پارامترها را پر کرده ایم. حالا فشار می دهیم OKو اکسل با یک تابع برای ما فرمولی ایجاد می کند VPR.
اگر چندین مقدار مختلف را برای مقدار کل فروش آزمایش کنیم، مطمئن خواهیم شد که فرمول به درستی کار می کند.
نتیجه
زمانی که تابع VPR با پایگاه های داده، آرگومان کار می کند Range_lookup (range_lookup) باید بپذیرد نادرست (کاذب). و مقدار به صورت وارد شده است Lookup_value (Lookup_value) باید در پایگاه داده وجود داشته باشد. به عبارت دیگر، به دنبال یک تطابق دقیق است.
در مثالی که در این مقاله به آن نگاه کردیم، نیازی به تطبیق دقیق نیست. این مورد زمانی است که تابع VPR باید به حالت تقریبی تبدیل شود تا نتیجه دلخواه را بازگرداند.
مثلا: ما می خواهیم تعیین کنیم که برای یک فروشنده با حجم فروش 34988 دلار از چه نرخی در محاسبه کمیسیون استفاده کنیم. عملکرد VPR مقدار 30% را به ما برمی گرداند که کاملاً صحیح است. اما چرا فرمول ردیفی را انتخاب کرد که دقیقاً 30٪ باشد و نه 20٪ یا 40٪؟ منظور از جستجوی تقریبی چیست؟ بیایید شفاف باشیم.
وقتی استدلال Range_lookup (interval_lookup) یک مقدار دارد کد واقعی (TRUE) یا حذف شده، تابع VPR در ستون اول تکرار می شود و بزرگترین مقداری را انتخاب می کند که از مقدار جستجو تجاوز نمی کند.
نکته مهم: برای اینکه این طرح کار کند، ستون اول جدول باید به ترتیب صعودی مرتب شود.