چرا تابع Vlookup درست کار نمیکنه؟

[S.Cheraghi]

چرا تابع Vlookup درست کار نمیکنه؟

این سوالیه که خیلی وقت ها از من پرسیده شده. مشکل اینجاست که فرد داره داده مورد نظرشو توی داده ها می بینه، اما تابع vlookup , hlookup, match و … اونو نمیتونه پیدا کنه و خطای N/AA# رو نشون میده یا اشتباها داده دیگه ای رو بر میگردونه و از نظر ما درست نیست. حالا میخوایم بررسی کنیم ببینیم مشکل تابع Vlookup از کجاست. این مشکل میتونه دو تا علت داشته باشه:

• خروجی فرمول درست نیست و در واقع داده غیر مرتبط رو نشون میده.

این به دلیل اینه که آرگومان آخر توابع vlookup , Hlookup, match که جستجوی دقیق و تخمینی رو تعیین میکنه فراموش میشه و صفر گذاشته نمیشه.

• خروجی فرمول خطای N/A# است.

وقتی خروجی فرمول خطای N/A# است باید دو مورد زیر رو بررسی کنیم:

مرحله اول: بررسی کنیم ببینیم آیا واقعا دو داده با هم برابرند یا نه؟ برای این کار از = استفاده میکنیم.

اگر خروجی True بود یعنی دقیقا با هم برابرند و اینجا باید چک کنیم ببینیم موردی که در Lookup استفاده شده درست هست یا نه؟ همچنین Match Type رو بررسی کنیم که صفر گذاشته شده باشه.

نکته:
این موضوع در خصوص حروف و کلمات فارسی که “ی” و “ک” دارند هم زیاد اتفاق میفته. اینجور مواقع Lookup value رو حتما از سلول بگیریم که دچار این اشتباه نشیم.

شکل ۱-مشکل تابع Vlookup – بررسی مساوی بودن دو داده مورد نظر

اگه خروجی False بود باید رو نکته زیر رو بررسی کنیم:

علت اول:

احتمالا کاراکترهایی که قابل مشاهده نیستند (مثل فاصله Space) در یکی از سلول ها (یا در سلول Lookup value یا در محدوده جستجو table Array) وجود داره.

حتما بخوانید: قواعد فرمول نویسی حرفه ای در اکسل | قسمت سوم

راه حل:

از تابع Trim استفاده میکنیم. این تابع Space های اضافی (فاصله اول و آخر یک سلول) رو حذف میکنه. برای اینکار مراحل رو طبق تصویر زیر انجام میدیم:

نکته:
تابع Trim فقط فاصله (Space) رو حذف میکنه. اگر کاراکترهای غیرقابل مشاهده دیگه ای در سلول وجود داشته باشه باید از تابع Clean استفاده کنید. در واقع همه کارهایی که برای تابع Trim کردیم رو در مورد تابع Clean هم انجام میدیم.


علت دوم:

داده هایی که از نظر ما یکسان هستند، ممکنه نوع داده (Data Type) متفاوتی داشته باشند. مثلا یکی به عنوان متن ذخیره شده باشه و یکی بصورت عدد. در اینصورت علیرغم تساوی ظاهری، با هم برابر نیستند. یک راه ساده برای اینکه تشخیص بدیم داده بصورت عدد ذخیره شده یا متن، اینه که از تابع IsText یا IsNumber استفاده کنیم.

شکل ۲- مشکل تابع Vlookup – بررسی نوع داده ذخیره شده

همونطور که در شکل ۲ می بینید سلول ۲A بصورت متنی ذخیره شده ولی سلول D3 بصورت عددی هست. پس علیرغم ظاهر مشابه، با هم تفاوت دارند. حالا باید نوع داده ها رو یکسان کنیم. یا متنی ها رو به عدد تبدیل کنیم یا عددی ها رو به متن.

منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
چرا تابع Vlookup درست کار نمیکنه؟

 

[S.Cheraghi]

چرا باید با پیوت تیبل (Pivot Table) کار کنیم؟
جداول پاشنه‏ ای (Pivot Table) در اکسل، ابزاری بسیار قدرتمند در تحلیل داده‏ ها بشمار می‏رود. شما می‏توانید با صرف چند دقیقه و بدون هیچ تجربه قبلی و فرمول نویسی‏های پیچیده، گزارشی زیبا و جذاب از خلاصه‏ای از هزاران داده خام تهیه کنید.

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

در شکل 1 اطلاعات مربوط به میزان فروش فروشنده ها در شعب مختلف رو می بینید.

شکل 1- بانک اطلاعاتی موجود جهت گزارشگیری​

با 2 تا 3 کلیک، میتونیم مثلا دو گزارش زیر رو تهیه کنیم:

شکل 2- میزان فروش به تفکیک فروشنده

شکل 3- میزان فروش هر فروشنده به تفکیک کد محصول

شکل 4- گزارش فروش هر محصول به تفکیک تاریخ و نام فروشنده​

خب می بینید که گزارش های خیلی کاربردی رو میتونیم با پیوت تیبل (Pivot Table) تهیه کنیم، جالب اینجاست که این گزارش ها فقط با چند کلیک و بدون حتی یک فرمول نویسی تهیه میشه و این تیپ گزارش ها ساده ترین و پیش پا افتاده ترین انواع گزارش های پیوت تیبل هستند.

یکی از ویژگی های بسیار عالی Pivot Table اینه که شما میتونید داده های مرتبط با نتیجه هر گزارش رو مشاهده کنید. مثلا در شکل 2 میخواید جزئیات فروش آقای سمیعی که 40500 تومان هست رو ببینید. برای این کار کافیه روی عدد 40500 دو بار کلیک کنید. با این کار یک شیت جدید ایجاد میشه و ریز اطلاعاتی که مربوط به فروشنده (آقای سمیعی) هست فراخوانی میشه. در شکل 5، جمع ستون E برابر است با 40500 تومان.

حتما بخوانید: چهار روش تبدیل متن به عدد

شکل 5- مشاهده جزئیات یکی از آیتم های محاسبه شده​

خب تا اینجا با این موضوع که ماهیت کار پیوت تیبل چی هست و بصورت کلی چه کاری انجام میده، آشنا شدیم. حالا اینکه چطور پیوت تیبل (Pivot Table) رو ایجاد کنیم و گزار شهای مورد نظر خودمون رو بسازیم رو در مراحل بعد میبینیم.

مرحله 2: چطور پیوت تیبل (Pivot Table) رو ایجاد کنیم؟


منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
پیوت تیبل (Pivot Table) چیست؟