لیست قابل جستجو در اکسل
ایجاد لیست قابل جستجو در اکسل
تا حالا نحوه ایجاد لیست در اکسل و نحوه ایجاد لیست های به هم وابسته در اکسل رو یاد گرفتیم. حالا میخوایم لیستی تهیه کنیم که قابل جستجو باشه. در واقع تعداد زیادی داده در لیست فروریز داریم که میخوایم قسمتی از عبارت مورد نظر رو که تایپ کردیم، لیست مورد نظر، کوتاه بشه و فقط آیتم هایی رو نشون بده که شامل اون عبارت هستن، در واقع قصد داریم لیست فروریز قابل جست و جو در اکسل ایجاد کنیم.
فرض کنید لیستی داریم از اسامی استان های ایران و میخواهیم با تایپ قسمتی از نام یک استان، لیست محدودتری برای انتخاب داشته باشیم.
منطق کلی کار این هست که باید لیستی از سلول هایی که شامل عبارت مورد نظر ما هستن رو ایجاد کنیم و به عنوان ورودی لیست دیتاولیدیشن قرار بدیم. برای انجام این کار، طبق مراحل زیر پیش میریم:
گام اول: پیدا کردن عبارت مورد نظر برای ایجاد لیست قابل جست و جو
تابعی که میتونه جستجو کنه ببینه یک عبارت در یک سلول وجود داره یا نه تابع Find/Search هست. این دو تابع در مورد سرچ فارسی عینا مشابه عمل میکنن. در صورت پیدا کردن عبارت مورد نظر، خروجی عدد و در غیر اینصورت خطای #Value! خواهد بود.
پس با فرض اینکه لیست مورد نظر قراره در سلول D1 ایجاد بشه، فرمول زیر رو مینویسیم. (شکل ۱)
=FIND ($D$1 , A2)
شکل ۱- پیدا کردن عبارت مورد نظر در سلول های لیست برای ایجاد لیست قابل جست و جو
همونطور که میبینیم، در صورتی که عبارت مورد نظر پیدا بشه، خروجی عدد خواهد بود و در غیر اینصورت خطا.
گام دوم: شماره گذاری موارد پیدا شده
حالا برای اینکه خروجی جستجو رو به گونه ای تغییر بدیم که بتونیم سلول های پیدا شده رو پشت سر هم لیست کنیم، از تابع زیر استفاده میکنیم و سلول های پیدا شده رو شماره ردیف میزنیم.(برای درک بهتر این موضوع مقاله مربوط به شماره ردیف خودکار رو مطالعه کنید)
=IF ( ISNUMBER ( FIND ($D$1,A2) ) ,MAX ($B$1:B1) +1 ,”” )
در واقع تابع Isnumber چک میکنه که آیا خروجی تابع Find عدد هست یا نه. اگه عدد بود، ماکزیمم محدوده بالای سرشو باضافه ۱ میکنه، اگر هم عدد نبود (خطا بود) خالی میذاره. همونطور که در شکل ۲ میبینیم، مقابل دو استان که شامل عبارت “رد” هستن، یعنی اردبیل و کردستان، به ترتیب شماره ۱ و ۲ نمایش داده می شه.
شکل ۲- ایجاد شماره پشت سر هم برای سلول هایی که شامل عبارت مورد نظر هستند
گام سوم: لیست کردن موارد پیدا شده
حالا کافیه که سلول های مشخص شده رو پشت سر هم لیست کنیم. برای این کار میتونیم از Backward Vlookup استفاده کنیم. یا اینکه از ترکیب Match و Index استفاده کنیم:
=IFERROR ( INDEX ($A$2:$A$32, MATCH ( ROW(A1) , $B$2:$B$32, 0)) , “”)
شکل ۳- فراخوانی استان هایی که شامل عبارت مورد جستجو هستن
این فرمول شماره های ایجاد شده (که به ترتیب هستن) رو فراخوانی میکنه و محتوای موجود در سلول روبروی اونها رو نمایش میده. که در واقع خواسته ما هم همینه و میخوایم لیست استانهایی که شامل عبارت مورد جستججو هستن رو پشت سر هم داشته باشیم.
گام چهارم: نام گذاری پویا برای محدوده لیست
مرحله بعد ایجاد یک محوده نامگذاری پویا هست که بتونیم به دیتاولیدیشن اختصاص بدیم.
از تب Formula گزینه Name Manager رو کلیک میکنیم و یک نام به عنوان List ایجاد میکنیم و فرمول زیر رو در اون مینویسیم. مطابق شکل ۴.
=OFFSET ( Sheet2!$C$2,0,0, COUNTIF ( Sheet2!$C$2:$C$32 , “?*” ) ,1)
برای درک بهتر محدوده های نامگذاری داینامیک، مقاله مربوط به Offset رو مطالعه کنید.
تابع COUNTIF( Sheet2!$C$2:$C$32 , “?*” ) تعداد سلول های پر که داده نمایش میدن رو شمارش میکنه و کاری به سلول هایی که با فرمول پر شدن ولی خالی نمایش داده میشن نداره.
شکل ۴- لیست فروریز قابل جست و جو – نامگذاری محدوده بصورت داینامیک
گام پنجم: ایجاد لیست کشویی
کافیه که نام ایجاد شده رو به Data Validation اختصاص بدیم. برای این کار روی سلول D1 کلیک کرده و از تب Data گزینه Data Validation رو انتخاب میکنیم. از تب Settings گزینه List رو انتخاب میکنیم و نام تعیین شده که در گام چهارم فرمول نویسی کردیم رو تخصیص میدیم. مطابق شکل ۵
شکل ۵- تخصیص محدوده نامگذاری شده به دیتا ولیدیشن
قبل از اینکه Ok رو بزنیم، به تب Error Alert رفته و تیک گزینه اخطار رو برمیداریم و بعد Ok رو میزنیم. مطابق شکل ۶
شکل ۶- لیست فروریز قابل جست و جو – برداشتن تیک خطا دهی
حالا کافیه عبارت مورد نظر رو در سلول D1 تایپ کنیم و بعد لیست فروریز رو باز کنیم. می بینیم که فقط سلول هایی که شامل اون عبارت هستن در لیست نمایش داده میشه. به تصویر زیر دقت کنید:
با این ۶ گام و با استفاده سلول های کمکی و بدون نیاز به کد VBA تونستیم یک لیست فروریز قابل جستجو تهیه کنیم که میتونه در ورود داده خیلی به ما کمک کنه. حالا میتونیم داده های مرجع رو در یک شیت قرار بدیم و لیست رو در شیت های دیگه که سلول های کمکی استفاده شده هم دیده نشه.
دانلود فایل اکسل لیست قابل جست و جو
برای دانلود فایل لیست کشویی در اکسل روی دکمه زیر کلیک کنید:
سلام و وقت بخیر؛
سوالم تکراری هست، توی دیگاهها برای تعمیم جستجو گفتید از این دستور INDIRECT(CELL(“address”)) استفاده کنیم. ولی من نتونستم نتیجه بگیرم. امکانش هست در دستور زیر بجای سلول ۴$D$ ستون D:D را با دستوری که گفتید جاگذاری کنم
IF(ISNUMBER(SEARCH(Sheet!$D$4,B4)),MAX($A$2:A3)+1,0)
درود بر شما
خب این فمرول قراره چکار یانجام بده؟
اون فرمول که ارائه شده برای پیدا کردن آدرس سلول انتخاب شده در لحظه است
باسلام و احترام کاربرد apply name در define name رو میشه توضیح بدید
درود بر شما
وقتی شما فرمول نویسی رو انجم دادید
بعد نامگذاری کردید
سلول حاوی فرمول رو انتخاب میکنید و apply name رو میزنید
اگر در اون فمرول ،محدوده ای مطابق با نامگذار یهای تعیین شده وجود داشت
جایگزین میشه
سلام فایل رو بیزحمت ارسال کنید دانلود نمیشه
درود بر شما
لینک مجدد چک شد
مشکلی نداره
Spam رو چک کنید
احتمالا ایمیل های سازمانی هم مشکل دار باشه
به هر حال اینم لینک مستقیم برای دانلود خدمت شما
ممنونم عالی بود
شماراهی میدونید که بشه در تیبل سلول هارو مرج کرد
درود بر شما
نمیشه
جزو اصوله
باسلام برای اینکه در سلول های پایینی لیست کشویی هم بخوایم از لیست کلی سرچ کنه باید در قسمت ابتدای کار ینی تابع find هم از تایع indirect و cell استفاده کنیم ؟و ب چه نحو باید استفاده بشه چون من هرچی میزنم ارور میده
درود بر شما
کافیه هر جا D1 استفاده شده، بجاش بذارید indirect(Cell(“Address”))
فقط اولش یک حطای circular میگیرید که منطقش درسته چون روی خودشه
اما لیست ها درست کار میکنه…. پس اون خطا ر کاری نداشته باشید، بعد از اصلاح فرمول شروع کنید به استفاده از لیست
ضمنا یادتون ننره که ولیدیشن رو بانتقال بدید در محدوده دلخواه!
سلام وقت شما بخیر
میشه لطف کنین فرمول کامل رو از if برای وقتی که بخابیم برای چند سلول از این دیتاولیدیشن استفاده کنیم رو بنویسید ؛ indirect و cell رو که میزنم با خطا مواجه میشم
با سپاس
درود بر شما
if نمیخواد
کافیه هر جا D1 استفاده شده، بجاش بذارید indirect(Cell(“Address”))
فقط اولش یک حطای circular میگیرید که منطقش درسته چون روی خودشه
اما لیست ها درست کار میکنه
سلام
ممنون از راهنمایی تون ولی وقتی indirect(Cell(“Address”)) رو استفاده میکنم در فرمول و یک بازه رو انتخاب میکنم باز هم تنها یک سلول (d1) رو بهم پاسخ میده
وقتی سلول بعد رو میزنم باز هم پیشنهاد های سلول d1 رو بهم میده
درود
دقت کنید این تابع روی سلول فعال کار میکنه، یعنی لازمه حتما توی سلول جدید یک حرکتی انجام بشه تا به عنوان سل فعال شناخته بشه
با سلام
من یک لیست قیمت دارم که دادهای تکراری هم در آن هست می خواهم از بین آنها هر کدام از دادها را به انتخاب کمترین قیمت را به من بدهد؟؟؟؟؟؟؟؟؟؟؟؟؟؟؟؟؟؟؟
درود بر شما
اگر ۲۰۱۹ دارید تابع minifs اگر ندارید DMin اینکار رو میتونه انجام بده
سلام
چندتا محصول دارم که اطلاعات هر محصول در شیت مربوط به خودش هست.
یک شیت هم بعنوان صفحه آغازین دارم که قراره محصولم را از اونجا انتخاب کنم و و با انتخابش، به شیت مربوط به اون محصول برم.
میشه به داده های لیست کشویی لینک داد؟
سوال دومم اینه که میتونم توی اکسل و با لیست کشویی ، نمودار سه ماهه و شش ماهه و… را تعیین کنم و بهم نمودار را نمایش بده؟
ممنون میشم راهنمایی کنید.
مورد اول با hyperlink شدنی هست
https://excelpedia.net/hyperlink-function/
بعدی هم با پیوت تیبل و pivotchart و اسلایسر میشه
یا اینکه نمودارهای پویا
https://excelpedia.net/dynamic-chart/