ایجاد لیست کشویی با قابلیت جستجو در اکسل
به توکل نام اعظمت بسم الله الرحمن الرحیم. سلام عرض میکنم خدمت یکایک دنبال کنندگان محترم سایت آموزشی اکسل لرن. در این پست آموزشی قصد دارم نحوه ایجاد لیست کشویی با قابلیت جستجو در اکسل را به شما عزیزان آموزش دهم لذا تا انتهای این پست آموزشی ما را همراهی کنید.
خروجی نهایی این آموزش را در قسمت زیر مشاهده می کنید:
قدم اول – جستجو در دیتابیس و تعیین جایگاه
با فرض شبه جدول داده های زیر:
قصد دارم ابتدا داده هایی که شامل کارکتر مورد جستجو در سلول G2 هستند را توسط تابع Search مشخص کنم، این عمل جستجو توسط تابع Find نیز امکان پذیر است با این تفاوت که تابع Search نسبت به بزرگ و کوچک بودن کارکترهای مورد جستجو حساسیت ندارد درحالیکه در تابع Find این حساسیت وجود دارد. (آموزش توابع Find و Search در اکسل)
در ادامه به منظور تکمیل عملیات جستجو نیاز به یک ستون کمکی همانند تصویر زیر و تعریف تابع Search خواهیم داشت:
از آنجاییکه قصد داریم این عملیات جستجو در یکایک سلول های ستون A صورت گیرد لذا نیاز به مطلق کردن سلول مورد جستجو ($G$2) همانند تصویر زیر خواهیم داشت تا در تعمیم دادن به سایر سلول ها اختلالی ایجاد نشود:
در ادامه نتیجه فرمول نویسی فوق را امتحان خواهیم نمود:
همانطور که در تصویر فوق نیز مشاهده می کنید، سلول هایی که دارای مقدار جستجو هستند توسط اعداد که بیانگر جایگاه کارکتر مورد جستجو در محتوای آنان است، نمایش داده شده و سلول هایی که فاقد محتوای مورد جستجو هستند با خطا روبرو شدند.
حال نیاز به یک فرمول داریم تا سلول های دارای محتوای مورد جستجو را برای ما مرتب سازی کند تا فراخوانی آنها امکان پذیر باشد. من در این قسمت از ترکیب توابع IF,MAX و ISNUMBER استفاده خواهم نمود. به تصویر زیر دقت کنید:
فرمولی که شما در تصویر فوق مشاهده می کنید به شرح زیر می باشد:
=IF(ISNUMBER(SEARCH($G$1,A2)),MAX($B$1:B1)+1,0)
در فرمول فوق تابع ISNUMBER ابتدا نتیجه تابع SEARCH را بررسی میکند، درصورتیکه مقدار مورد جستجو در محتوای سلول باشد، خروجی تابع SEARCH قطعا یک عدد خواهد بود لذا خروجی تابع ISNUMBER نیز برابر با TRUE خواهد بود.
در این شرایط تابع IF از تابع MAX می خواهد که بیشترین مقدار عددی محدوده $B$1:B1 را مشخص و به آن یک واحد اضافه کند تا یک مرتب سازی بصورت کاملا موقت صورت گیرد. حال اگر خروجی تابع SEARCH برابر با خطا باشد، یعنی مقدار مورد جستجو در محتوای سلول وجود ندارد که در این حالت خروجی تابع ISNUMBER برابر با FALSE و در پایان مقدار آرگومان دوم تابع IF یعنی عدد 0 فراخوانی و نمایش داده خواهد شد.
نکته ای که در این قسمت وجود دارد این است که شما می بایست تنها سلول اول محدوده $B$1:B1 را مطلق کنید زیرا با تعمیم دادن، بزرگترین عدد از ابتدا تا هرسلول مورد بررسی استخراج خواهد شد، نه نسبت به کل محدوده ! به منظور درک بهتر این موضوع به فرمول تصویر زیر دقت کنید:
در فرمول فوق، تابع MAX بیشترین مقدار عددی بین محدوده $B$1 تا سلول انتخاب شده(B12) را محاسبه و نتیجه آن را برمی گرداند.
حال ستون کمکی را انتخاب با نگاه داشتن Shift آن را به ابتدای شبه جدول منتقل می کنیم تا حکم شماره ردیف را برای شبه جدول پیدا کند. خروجی ما تا به این لحظه همانند تصویر زیر خواهد بود:
قدم دوم – مرتب سازی داده های مورد جستجو
تا به اینجای کار آموختیم که چگونه محتوایی را مورد جستجو و جایگاه آن را توسط ترکیب توابع ISNUMBER,IF و MAX تعیین کنیم. در ادامه قصد داریم با استفاده از توابع VLOOKUP و ROWS جایگاه به وجود آمده را مرتب سازی کنیم.
به تصویر زیر دقت کنید:
در فرمول فوق ما با استفاده از تابع ROWS آخرین ردیف مورد بررسی را در ستون اول محدوده A1:B15 مورد جستجو قرار دادیم (آموزش توابع ROW و ROWS در اکسل) که در صورت وجود سلول نظیر آن در ستون دوم (Name) فراخوانی خواهد شد. به خروجی زیر دقت کنید:
همانطور که در تصویر فوق نیز مشاهده می کنید، هردو سلولی که محتوای “adam” را شامل بودند، به ترتیب پشت یکدیگر قرار گرفتند. اما شاید هنوز برای شما سوال باشد که چگونه این عمل رخ داد! به منظور درک بهتر به فرمول تصویر زیر دقت کنید:
در فرمول فوق که در سلول C3 درج گردیده است، ردیف و یا به عبارتی عدد 2 مورد جستجو قرار می گیرد. چرا ؟ چون تابع ROWS در این سلول مقدار عددی 2 را بر می گرداند. در ادامه عدد 2 مورد جستجو در ستون اول محدوده $A$1:$B$15 قرار می گیرد که نتیجه این جستجو سلول نظیر آن در ستون دوم محدوده فوق خواهد بود.
حال برای اینکه از نمایش خطای #N/A که نتیجه عدم یافتن مقدار جستجو است، جلوگیری کنیم می بایست فرمول فوق را فی مابین یک IFERROR ساده قرار دهیم. به تصویر زیر دقت کنید:
همانطور که در تصویر فوق نیز مشاهده می کنید، از نمایش خطا توسط تابع IFERROR جلوگیری کردیم.
قدم سوم – تعیین محدوده داده های لیست
تا به اینجای کار ما مقدار مورد جستجو را در یکایک داده های خود جستجو و سپس آنها را مرتب نمودیم. حال قبول دارید در این قسمت ما نمی توانیم این محدوده مرتب شده را به عنوان محدوده ثابت لیست هنگام جستجو درنظر بگیریم؟ چرا ؟ چون خروجی جستجو به هیچ عنوان یکسان نیست لذا تعریف کردن یک محدوده ثابت برای لیست Data Validation عملا زیبایی کار ما را نابود می کند. حال این مشکل را ما با استفاده از ترکیب توابع OFFSET و COUNTIF برطرف نمودیم، به تصویر زیر دقت کنید:
در تصویر فوق ما محدوده ای جدیدی را با استفاده از تابع OFFSET که شروع این محدوده از سلول ثابت $C$2 به ارتفاع متغیر که توسط تابع COUNTIF براساس سلول های پر استخراج می شود، ایجاد نمودیم. حال شاید برای شما سوال باشد که منظور از ?* در شرط تابع COUNTIF چیست؟ این عبارات به معنای “حداقل با یک کارکتر” است.
قدم چهارم و پایانی – ایجاد لیست در Data Validation
تقریبا به انتهای کار نزدیک شده ایم، تنها کافی است محدوده متغیری که در قدم سوم توسط ترکیب توابع OFFSET و COUNTIF ایجاد نمودیم را با یک نام ذخیره و در لیست Data Validation فراخوانی کنیم. به منظور تعریف نام برای محدوده متغیر طبق تصویر زیر عمل کنید:
با کلیک کردن برروی OK این محدوده متغیر با نام Validation_List ثبت خواهد شد. حال زمان آن رسیده است تا لیست خودمان را مطابق با تصویر زیر و محتوای آن را با توجه به محدوده Validation_List فراخوانی کنیم:
اجازه بدین نتیجه را تا به این لحظه بررسی کنیم:
همانطور که مشاهده می کنید، به هنگام جستجوی یک عبارت با خطای Data Validation روبرو خواهیم شد. مشکل کجاست؟ بطور خلاصه ابزار لیست ورود اطلاعات را محدود به لیست می کند لذا اگر داده ی خارج از لیست درج گردد، اجازه ورود نخواهد داشت. حال برای حل این مشکل نیز تنها کافی است مجدد به ابزار Data Validation برگردیم و تیک invalid data را همانند تصویر زیر حذف کنیم:
با کلیک کردن برروی OK نتیجه را مجدد با هم بررسی خواهیم نمود:
همانطور که در تصویر فوق نیز مشاهده می کنید اینبار به درستی عملیات جستجو صورت گرفت.
همچنین اضافه می گردد که برای ایجاد لیست کشویی جستجوگر حتما نیاز به فرمول نویسی نیست. با ترفند بسیار ساده و استفاده از Pivot Table نیز قادر به ایجاد لیست کشویی جستجوگر در اکسل خواهید بود. به منظور درک بهتر شما عزیزان یک ویدیو آموزشی کوتاه آماده نمودیم که می توانید در ادامه آن را مشاهده نمایید:
در پایان امیدواریم پست آموزشی ایجاد لیست کشویی با قابلیت جستجو در اکسل مورد رضایت شما عزیزان قرار گرفته باشد لذا در صورت تمایل می توانید از سایت آموزشی اکسل لرن به منظور تولید محتوای بیشتر حمایت کنید (اختیاری) و همچنین تشکر میکنم از همراهی شما عزیزان. موفق و پیروز باشید.