نوشتن برنامه جستجوی ساده در اکسل

جستجو و استخراج نتایج دلخواه از داده های ورودی یکی از کاربردی ترین تکنیک های اکسل است. شاید شما قصد داشته باشید گزارشی از اقلام خروجی انبار در هفته گذشته را تهیه کنید و یا شاید بخواهید که اسامی افرادی از لیست چند صد نفری را استخراج کنید. برای این منظور کافی است تمام یا قسمتی از حروف مورد نظر را در یک سلول وارد کرده و از اکسل بخواهید تمام اسامی که دارای کلمه مورد نظر هستند را برای شما در یک لیست نمایش دهد.

برای انجام چنین کاری به چند تابع و ترکیب آنها نیاز داریم که به صورت خلاصه به شرح آنها می پردازیم و سپس با ذکر یک مثال و استفاده از این توابع روش انجام کار را توضیح می دهیم.

1-      FIND

2-      IF

3-      ISNUMBER

4-      MAX

5-      ROW

6-      VLOOKUP

7-      IFERROR

تابع FIND

این تابع برای جستجوی محل رشته ای که به دنبال آن هستیم در یک محدوده اطلاعات به کار می رود

تذکر: این تابع به حروف کوچک و بزرگ حس است (در جملات فارسی فرقی بین حروف کوچک و بزرگ نیست)

ساختار این تابع به شرح زیر می باشد

FIND (find_text, within_text,[strat_num])

Find_text رشته ای است که قصد داریم به دنبال آن بگردیم

Within_text محدوده اطلاعاتی است که می خواهیم کل آن را جستجو کنیم

Start_num محل شروع جستجو را مشخص می کند. این آرگومان اختیاری می باشد و می توانیم آن را ننویسیم و اگر نوشته نشود مقدار آن 1 در نظر گرفته می شود.

تذکر: به طور کلی آرگومان (ورودی) که میان علائم [      ]  قرار دارند نوشتن آنها اختیاری هستند.

توجه: این تابع فقط محل رشته مورد جستجو را مشخص می کند. مثلاً فرض کنید به دنبال حرف “ع” میگردید و محدوده مورد جستجو دارای دو اسم به نام های علی و مسعود می باشد. در مقابل نام “علی” عدد 1 نوشته می شود چون حرف “ع” اولین حرف اسم می باشد و در مقابل اسم مسعود عدد عدد 3 درج میگردد. چون محل حرف “ع” در کلمه مسعود سومین حرف می باشد.

تابع IF

تابع IF ساختاری به شرح زیر دارد.

IF(logical_test,[value_if_true],[value_if_false])

Logical_test این عبارتی که میخواهیم ارزش آن را بسنجیم

Value_if_true اگر ارزش عبارت مورد سنجش درست باشد این مقدار نمایش داده می شود

Value_if_false اگر ارزش عبارت مورد سنجش نادرست باشد این مقدار نمایش داده می شود

برای مطالعه تابع IF بیشتر به آموزش تابع IF در اکسل – آموزش توابع شرطی و منطقی مراجعه فرمایید.

تابع Isnumber

ساختار این تابع به شرح زیر است

ISNUMBER(Value)

Value مقداری است که می خواهیم آن را تست کنیم. اگر این مقدار عدد باشد عبارت True و اگر غیر عددی باشد عبارت False برگردانده می شود.

تابع MAX

این تابع دارای ساختار زیر است

MAX(number1,[number2],…)

Number 1 و Number2 و … Number255  عدد هستند و این تابع بزرگترین مقدار محدوده را نمایش می دهد

تابع ROW

ساختار کلی تابع به شرح زیر است

ROW([reference])

این تابع شماره ردیف reference را برمیگرداند.

عبارت reference سلولی است که قصد داریم شماره ردیف آن را بدست آوریم

اگر آدرس reference را مشخص نکنیم شماره ردیف جاری نمایش داده می شود.

تایع vlookup

ساختار کلی تابع به شرح زیر است

Vlookup(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value  مقداری است که باید جستجو شود

table_array محدوده ای که مقدار جستجو شده درون آن قرار دارد

col_index_num شماره ستونی که با مقدار جستجو مطابقت دارد

[range_lookup] مقدار 0 یا 1 و یا True و False

تابع Vlookup یکی از مهمترین و پرکاربردترین توابع اکسل است . برای آشنایی با تابع vlookup به تابع VLOOKUP  مراجعه نمایید.

تابع IFERROR

ساختار کلی تابع به شرح زیر است

IFERROR(value,value_if_error)

اگر نتیجه تابع و عبارت نوشته شده یک خطا باشد با استفاده از این تابع  می توانیم خروجی را عوض کنیم و عبارت دلخواه خود را به جای مقدار خطا نمایش دهیم.

مثلاً می توانیم مشخص کنیم اگر خروجی تابع خطای #VALUE! بود آنگاه مقدار تهی “” ،صفر یا هر عبارت دلخواه دیگری را نمایش بده

اکنون پس از شرح مختصر توابع موردنیاز برای انجام جستجو با ذکر یک مثال و با استفاده از ترکیب توابع فوق یک مورد جستجو را مورد بحث قرار می دهیم.

در این مثال در محدوده B2:B15 یک سری اسامی وارد کرده ایم و در سلول E2 تمام یا قسمتی از عبارت مورد جستجو را وارد می کنیم. نتیجه جستجو در ستون G به نمایش درمی آید.

گام اول:

در سلول A2 مقابل اولین اسم فرمول را به این شکل می نویسیم.

FIND($E$2,B2:B15)

سلول E2 را با استفاده از علامت $  به صورت مطلق تعریف می کنیم تا در هنگام کپی فرمول این سلول ثابت بماند.

برای آشنایی با توابع مطلق و نسبی به مقاله آدرسهای نسبی و مطلق در اکسل مراجعه فرمایید.

نتیجه مطابق تصویر می باشد.

جستجو

مطابق تصویر عبارت #value! به معنای پیدا نشدن عبارت مورد جستجو در سلول مقابل فرمول، عدد 1 یعنی اینکه عبارت مورد جستجو در اولین کلمه اسم وجو دارد و عدد 3 مقابل نام مهسا یعنی عبارت جستجو شده سومین حرف اسم می باشد.

گام دوم:

در اینجا قصد داریم از نمایش خطا جلوگیری کرده و درصورتی که حاصل فرمول یک عدد باشد عبارت True و در صورت غیر عددی بودن عبارت False نمایش داده شود.

فرمول نوشته شده در گام اول را با تابع ISNUMBER ترکیب می کنیم و به شکل زیر می نویسیم

ISNUMBER(FIND($E$2,B2:B15))

جواب این تابع در صورتی که عبارت مورد جستجو وجود داشته باشد True و در غیر این صورت False خواهد بود

جستجو

گام سوم:

در این مرحله فصد داریم به اولین مورد پیدا شده شماره 1 و به دومین مورد شماره 2 و به n امین مورد شماره n را اختصاص دهیم.

به این منظور فرمول نوشته شده را با توابع IF و max ترکیب می کنیم.

IF(ISNUMBER(FIND($E$2,B2:B15)),MAX($A$1:A1)+1,””)

در این مرحله از تابع IF استفاده کرده ایم. همانطور که قبلاً توضیح داده شد تابع IF از سه قسمت تشکیل شده. قسمت اول شرط، قسمت دوم عبارت دلخواه اگر شرط درست باشد و قسمت سوم عبارت دلخواه زمانی که شرط درست نباشد.

در اینجا شرط همان عبارت ISNUMBER(FIND($E$2,B2:B15) که یا True است و یا False . حال اگر جواب True باشد عبارت MAX($A$1:A1)+1 محاسبه می شود و اگر جواب شرط False باشد قسمت دوم شرط یعنی ” ” که به معنی تهی بودن و یا خالی گذاشتن سلول می باشد.

در اینجا لازم است در مورد فرمول MAX($A$1:A1)+1 توضیح مختصری داده شود.

این فرمول ابتدا سلول A1 را با A1 چک می کند و بیشترین مقدار را پیدا کرده و با عدد 1 جمع می کند. چون A1 خالی می باشد پس بزرگترین مقدار صفر است و چون با عدد 1 جمع می شود حاصل عدد 1 خواهد بود.

وقتی که فرمول را به سمت پایین کپی می کنیم سلول $A$1 ثابت باقی می ماند و قسمت دوم فرمول یعنی A1 به A2 تبدیل می شود. و اگر در سلول A1 مقداری وجود داشته باشد، دوباره با عدد 1 جمع شده و عدد 2 بدست می آید و به همین ترتیب در صورتی که جواب تابع IF مقدار True باشد در مقابل آنها اعداد ترتیبی ایجاد شده و به مسئله مورد نظر می رسیم. یعنی برای اولین مورد پیدا شده عدد 1 برای دوین مورد عدد 2 و به همین ترتیب برای nامین مقدار پیدا شده عدد n اختصاص داده می شود.

نتیجه را در شکل زیر ببینید

جستجو

گام چهارم

کار پیدا کردن در این مرحله تمام می شود و اکنون باید فرمولی بنویسیم تا نتیجه به دست آمده را در یک لیست و بدون فاصله برای ما نمایش دهد.

ما اکنون یک جدول دو ستونی در محدوده A2:B15 از اسامی و شماره ردیف آنها داریم . در این مرحله با استفاده از تابع vlookup نتایج را نمایش خواهیم داد.

در ستون G2 فرمول را به شرح زیر می نویسیم

VLOOKUP(ROW(A1),$A$2:$B$15,2,0)

همانطور که در شرح تابع vlookup  ذکر شد این تابع مقداری را در یک ناحیه جستجو کرده و محتوای ستون مشخص شده را برمیگرداند.

مقدارمورد جستجو در اینجا عبارت ROW(A1) است. جواب این تابع عدد 1 است و با کپی کردن فرمول به سمت پایین اعداد 2 و3 و … بدست می آیند.

یعنی اگر اعداد 1 و 2 و3 و در ناحیه $A$2:$B$1 پیدا شوند آنگاه ستون دوم جدول یعنی اسامی مقابل شماره ها نمایش داده می شوند

ردیف های خالی مانند A2 و A3 در فرمول

VLOOKUP(ROW(A1),$A$2:$B$15,2,0)

دارای مقدار خطای #N/A خواهند بود که با استفاده از تابع IFERROR و اضافه کردن این تابع به فرمول فوق به جای مقادیر خطا یک رشته خالی نمایش داده می شود.

پس فرمول فوق را جهت خالی گذاشتن مقادیر سلول های دارای #N/A به شکل زیر اصلاح می کنیم.

IFERROR(VLOOKUP(ROW(A1),$A$2:$B$15,2,0),””)

نتیجه را در شکل زیر ببینید

جستجو

لینک دانلود فایل این مقاله