چگونگی ایجاد کادر جستجوی اختصاصی در اکسل

این آموزش همراه با مثال به شما چگونگی ایجاد یک کادر جستجوی اختصاصی خودتان را نشان می دهد. فایل اکسل این مثال در انتهای آموزش برای دانلود در اختیار شما می باشد.

تصویر زیر نتیجه کار را نشان می دهد. اگر شما مقداری را در سلول B2 وارد کرده و جستجو کنید، اکسل در ستون E جستجو را انجام می دهد و نتایج جستجو را در ستون B به شما نمایش می دهد.

برای ایجاد این کادر جستجو مراحل زیر را انجام بدهید.

➊ سلول D4 را انتخاب کنید و تابع SEARCH را مشابه آنچیزی که در تصویر می بینید، وارد نمایید. یک ارجاع قطعی (absolute reference) به سلول B2 داشته باشید.

➋ بر گوشه سمت راست سلول D4 دوبار کلیک کنید تا به سرعت تابع را به سایر سلولها کپی نمایید.

توضیح: تابع SEARCH موقعیت یک متن را در متنی دیگر می یابد. تابع SEARCH بین حروف کوچک و بزرگ تمایز قائل می شود، اصطلاحاً case-insensitive می باشد. در مورد Tunisia متن uni در موقعیت 2 یافت شده است. در مورد United States متن uni در موقعیت 1 یافت شده است. هر چقدر این عدد کوچکتر باشد، در نتایج جستجو باید در رتبه بالاتری قرار بگیرد.

➌ هر دو متن United States و United Kingdom مقدار 1 را بر می گردانند. برای اینکه هر نتیجه جستجو موقعیت یکتایی (unique) را برگرداند، تا در رتبه بندی نتایج جستجو توسط تابع RANK به ما کمک کند، فرمول موجود در سلول D4 را اندکی تنظیم می کنیم.

➍ دوباره بر روی گوشه سمت راست سلول D4 دوبار کلیک کنید تا فرمول به سرعت به سلولهای دیگر کپی شود.

توضیح: تابع ROW شماره ردیف هر سلول را بر می گرداند. اگر ما شماره هر ردیف را بر یک عدد بزرگ تقسیم کنیم و آن را به نتایج تابع SEARCH اضافه کنیم، همواره اعداد یکتایی (unique) را خواهیم داشت. با این وجود، این افزایش های کوچک در رتبه بندی نتایج جستجو تاثیری نخواهند گذاشت. متن United States مقدار 1.00006 را دارد و متن United Kingdom مقدار 1.00009 را دارد. ما همچنین تابع IFERROR را نیز اضافه کرده ایم. اگر سلولی دارای خطا (error) باشد - اگر متن جستجو یافت نشود - یک متن خالی ("") نمایش داده می شود.

➎ سلول C4 را انتخاب کنید و تابع RANK را مشابه تصویر زیر به آن اضافه کنید.

➏ بر روی گوشه پایین و سمت راست سلول C4 دوبار کلیک کنید تا فرمول موجود در آن به سرعت به سایر سلولها کپی شود.

توضیح: تابع RANK رتبه یک عدد را در لیستی از اعداد بر می گرداند. اگر سومین آرگومان ارسالی به این تابع 1 باشد، اکسل کوچکترین عدد را ابتدای لیست نمایش می دهد، دومین عدد کوچک را در رتبه دوم قرار می دهد و غیره. از آنجا که ما از تابع ROW استفاده کرده ایم تمامی مقادیر موجود در ستون D یکتا (unique) هستند. در نتیجه، رتبه بندی های موجود در ستون C نیز یکتا (unique) خواهند بود.

➐ تقریباً به آخرش رسیدیم. ما از تابع VLOOKUP برای بازگرداندن کشورهای یافت شده استفاده خواهیم کرد (کوچکترین رتبه در اول لیست، دومین رتبه در دوم لیست و ...). سلول B4 را انتخاب کنید و تابع VLOOKUP را مشابه تصویر زیر در آن وارد کنید.

➑ بر روی گوشه پایین و سمت راست سلول B4 دوبار کلیک کنید تا فرمول به سرعت به سایر سلولها کپی شود.

➒ رنگ متن (text color) اعداد موجود در ستون A را به رنگ سفید در بیاورید و ستونها C و D را مخفی (hide) کنید.

نتیجه: شما کادر جستجوی اختصاصی خودتان را در اکسل خواهید داشت.