کاربرد فازی لوکاپ و برتری آن به Vlookup
افزونه Fuzzy Lookup
شاید برای شما هم سوال شده باشد که Fuzzy Lookup چیست و کاربرد فازی لوکاپ (افزونه)چه می باشد ؟
Fuzzy Lookup چیست ؟
Fuzzy Lookup یک افزونه اکسل می باشد که توسط مایکروسافت ارائه شده و به صورت رایگان در دسترس همه می باشد.
این افزونه طبق ورودی که برایش تعریف کنیم بهترین تطابق را جستجو کرده و بهترین تطابق همراه با شباهت را نتیجه می دهد
در ابتدا مثالی برای شما تهیه نموده ایم تا ویژگی این افزونه را به خوبی متوجه شوید
همانگونه که ملاحظه میکنید دو جدول داریم که می‌خواهیم مبالغ دریافتی و پرداختی این دو جدول را کنار هم بگذاریم تا بتوانیم خالص پرداختی به هر شخص را پیدا کنیم.
برتری فازی لوکاپ به تابع vlookup
اما همانطور که در تصاویر فوق ملاحظه می‌نمایید مشکلی در اینجا وجود دارد آن هم این است که اسامی در این دو جدول اختلاف‌های جزئی دارند.
به عنوان مثال یک اسم در جدول دوم ابتدا اسم و سپس نام خانوادگی نوشته شده و در جدول اول بالعکس می باشد.
و یا اینکه در جدول اول پسوند یک اسم وارد نشده ولی در جدول دوم اسم به همراه پسوند وارد شده است.
حال اگر بخواهیم با تابع VLOOKUP این کار را انجام دهیم به چه صورت می باشد؟
در سلولی که می‌خواهیم خروجی در آن قسمت نشان داده شود فرمول را به صورت نمایش داده شده در شکل زیر مینویسیم :
همانطور که در شکل زیر میبینید مواردی که اسم در دو جدول یکسان هستند داده ها استخراج شده و فرمول جواب داده است
ولی برای مابقی موارد فرمول VLOOKUP جواب صحیح را به ما نداده و نمیتوان جواب گرفت و از آنجایی که در این فرمول راهکاری برای این موارد نداریم تا به جواب برسیم پس این راه حل کنار گداشته می شود.
آیا در بحث آموزش اکسل راه حل دیگری هست !!!
نصب افزونه فازی لوکاپ
در حال حاضر تنها راه حل موجود که می تواند در اینگونه موارد باشد استفاده از افزونه Fuzzy Lookup هست.
ابتدا برای اینکه این افزونه را نصب کنیم باید در گوگل کلمه Fuzzy Lookup را سرچ کنیم.
همانطور که در شکل زیر نمایش داده شده اولین لینک مربوط به سایت مایکروسافت می باشد.
این لینک را باز می کنیم و در صفحه باز شده به دنبال گزینه دانلود می گردیم و با زدن این گزینه، افزونه دانلود خواهد شد
و پس از نصب افزونه یک تب به نام Fuzzy Lookup که فقط هم یک گزینه‌ی Fuzzy Lookup دارد به اکسل شما اضافه خواهد شد.
پس از نصب این افزونه می خواهیم ببینیم که این افزونه قدرتمند چطور در حل این مشکل به ما کمک می کند.
ابتدا باید محدوده ی دو جدول را به تیبل تبدیل کنید که میتوانید:
ازتبHOME قسمت Format as Table یکی از این فرمتها را انتخاب کنید ودر پنجره باز شده همانطور که در تصویر زیر مشاهده میکنید
ازشما میپرسد که جدولتانHeader دارد یا خیر. که چون جدول ما دارد OK را میزنیم .
وقتی که جدول را تبدیل به Table کردیم
می توانیم در قسمت مشخص شده در تصویر زیر نامش را تغییر دهیم که ما در اینجا نام جدول اول خود را به نام Daryafti و دومین جدول را پس از تبذیل شدن به Table
که اینجا می توانیم از CTRL+T نیز استفاده کنیم به نام Pardakhti تغییر می دهیم تا بعدا بتوانیم جداول را راحت تر تشخیص دهیم .
حال در Tab، Fuzzy Lookup گزینه‌ی Fuzzy Lookup را می زنیم.
در پنجره ای که درقسمت Fuzzy Lookup در سمت راست دیده می شود در بخش Left Table و Right Table خودش جداول را در لیست کشویی به ما میدهد( شکل پایین-شماره 1)
فرقی ندارد که سمت چپ و راست کدام یک از جدول ها را انتخاب کنیم مهم این است که این دوجدول را از این لیست انتخاب کنیم.
در قسمت بعدی می بایست مشخص کنیم که فیلد مشترکمان چیست؟
در بسیاری از مواقع خود سیستم فیلد مشترک را تشخیص داده و در قسمت Match Columns قرار می دهد که در اینجا نام و نام خانوادگی از دو جدول جزء فیلد مشترکمان هست
اما اگر سیستم نتواند فیلد مشترک را تشخیص دهد می بایست خودمان فیلدها را انتخاب کرده و گزینه‌ی Match که در مابین دو جدول Left Columns و Right Columns قرار دارد را بزنیم.(شکل پایین شماره2)
در قسمت Ouput Columns میگوید بعد از اینکه این مقایسه انجام شده و مشترکها مشخص شدند، می خواهید چه ستونهایی را برایتان برگردانم و نمایش دهم.
ما در اینجا می خواهیم که نام و نام خانوادگی در ستون دریافتی ، مبلغ دریافتی در جدول دریافتی، نام و نام خانوادگی در جدول پرداختی و مبلغ پرداختی در جدول پرداختی را برایمان نمایش دهد .
در قسمت پایین همین جدول هم میگوید که مقدار مشابهتش را هم می خواهید نشان دهم که چقدر با هم شبیه هستند و ما این گزینه را نیز انتخاب میکنیم تا بتوانیم کنترل داشته باشیم.(شکل پایین – شماره 3)
قسمت Number Of Matches هیچگونه تغییری نمی دهیم (شکل پایین – شماره 4 )
و در قسمت Similarity Threshold میزان مشابهت را میتوانیم کم ویا زیاد کنیم که فرضاّ اگر میبینید داده هایتان اختلاف زیادی دارند باید کمترش کنید که پیش فرض خودش روی حدود نیم می باشد،
ما در اینجا کمترش میکنیم تا بتواند همه موارد را پیدا کند.(شکل پایین – شماره 5)
بعد باید محل مقصدتان رو انتخاب کنید که ما در اینجا A18 را انتخاب کرده و سپس فقط کافیستGo را بزنیم.(شکل پایین – شماره6)
منتظر می مانیم تا نتیجه کار در محل مشخص شده به ما نمایش داده شود.
نتیجه کار در شکل پایین نشان داده شد و همانطور که می بینید مثلا در جدول 1 جعفر رضوی داشتیم و در جدول 2 جعفر رضوی مقدم که داده هایش قرار گرفته و همه داده ها به درستی کنار هم قرار گرفته اند.
در ستون Similarity هم مقدار همبستگی دو داده ای که مقایسه کرده را نشان می دهد.
مثلاً شما این جدول را فیلتر میکنید که با کلید میانبر Ctrl+Shift+L نیز انجام می شود.
مواردی که خیلی بهم شبیه نبوده یکم ریسکش بالاست.
این موارد را فیلتر می کنید ببینید که درست عمل کرده است یا خیر.
نکته طلایی :
در استفاده از این روش نکاتی که می بایست به آن توجه کرد اینست که:
1-در تعداد سطرهای جدول محدودیتی نداریم فقط چنانچه سطرها زیاد باشند برای گرفتن خروجی می بایست کمی بیشتر منتظر بمانیم
2- نکته دیگر اینست که همیشه تعداد سطرهای جدولی که در قسمت Left Table از شکل بالا قرار میگیرد برای مقایسه لحاظ میگردد.
به طور مثال اگر جدول شماره 1 دارای 8 سطر باشد و در قسمت Left Table قرار دهیم و جدول شماره 2 دارای 12 سطر باشد و در قسمت Right Table قرار دهیم
با انجام فرآیند Fuzzy Lookup تنها 8 سطر بررسی و در خروجی به ما نمایش داده می شود.
جهت مشاهده آموزش ویدئویی فازی لوکاپ بر روی لینک زیر کیلیک کرده و آن را داخل پیج ایسنتاگرام مطالب کمیاب در اکسل و حسابداری ( softwaretrain ) مشاهده فرمایید .
دیدگاهتان را بنویسید