آموزش ۵ روش جستجو ساده، پیشرفته و حرفه‌ای در اکسل
نحوه سرچ ساده در اکسل با استفاده از توابع : جستجو در بین شیت ها و..
Excel Lookup Functions to Efficiently Search Spreadsheets
اکسل از برنامه های پر کاربرد مایکروسافت است. گاهی یک فایل آماده دارید که باید تغییراتی روی آن انجام دهید یا سلولی خاص را مشاهده کنید. اسکن سطر و ستون یا استفاده از کلید های ترکیبی Ctrl+F احتمالا اولین راهی است که به ذهن شما می رسد. اما در پروژه های بزرگ که اسامی مشابهی وجود دارند این کار سخت به نظر می رسد. همچنین ممکن است بخواهید سرچی پیشرفته تر انجام دهید.
جستجو در اکسل با تابع VLOOKUP این تابع جستجو در شیت های اکسل برای مشخص کردن یک مقدار و بازگشت آن از ستون های مختلف کاربرد دارد. به عنوان مثال شما می خواهید نام خانوادگی کارمندی را با استفاده از شماره کارمندی در لیست خود پیدا کنید یا با یک شماره تلفن، نام خانوادگی را بیابید. برای این موارد از تابع زیر استفاده می شود.
=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
- مقدار [lookup_value] قطعه ای از اطلاعات است که شما در حال حاظر دارید. مقدار [table_array] نیز به شما امکان می دهد تا سلول هایی را تعیین کنید که در آن تابع برای موارد خاسته شده سرچ می کند. هنگام جستجو مطمئن شوید که محدوده انتخابی شامل ستون اول موجود در آرایه شما باشد.
- عبارت [col_index_num] عدد ستونی را که حاوی مقادیر بازگشتی است نشان می دهد.
- [range_lookup] نیز یک استدلال اختیاری است و مقدارش ۰ یا ۱ است. اگر شما ۱ وارد کنید، این تابع دنبال مقداری که وارد کرده اید یا مقدار بعدی در صورت عدم وجود می گردد.
- در مثال زیر VLOOKUP به دنبال یک نمره ۶۵۲ می گردد که عدد ۶۴۶ را بازگشت می دهد. زیرا نزدیک ترین شماره به ۶۵۲ در لیست است و مقدار پیش فرض [range_lookup] یک است.
به عنوان مثال برای جستجو در ستون اکسل ، یک فایل اکسل داریم که شامل شماره ID، نام و نام خانوادگی، شهر، ایالت و امتیازات SAT است. حال می خواهیم امتیاز SAT را برای نام خانوادکی Winters پیدا کنیم. در اینجا با استفاده از VLOOKUP این کار آسان است.
عبارت تابع را به شکل زیر وارد می کنیم:
=VLOOKUP(“Winters”, C2:F101, 4, 0)
از آنجایی که امتیازات SAT ستون جهارم بعد از ستون نام خانوادکی است، ما از عدد ۴ برای آرگومان column index استفاده می کنیم. توجه داشته باشید زمانی که به دنبال یک متن هستید، بهتر است مقدار [range_lookup] را صفر قرار دهید.
پس از انجام تابع عدد ۶۵۱ بازگشت می شود و همانطور که مشاهده میشود، این امتیاز متعلق به شخص Kennedy Winters در لیست است که در ردیف ۹۲ قرار دارد. و این گونه این تابع برای استفاده های شما خصوصا برای انجام کار های مالیاتی مفید می باشد. در استفاده از این تابع اطمینان حاصل کنید که اولین ستون در محدوده شما همان مقدار مورد نظر شماست. این کار برای نمایش صحیح نتایج مورد نیاز است. دومین نکته ای که باید توجه داشته باشید، این تابع فقط یک مقدار را باز می گرداند، در موارد مشابه اولین مقدار شناسایی شده را نمایش می دهد. همچنین اگر قصد تبدیل ورد به اکسل را دارید، مطلب آموزش تصویری و گام به گام روش تبدیل جدول ورد به اکسل را نیز مطالعه نمایید.
جستجو در بین شیت های اکسل با تابع HLOOKUP
تابع قبلی مقادیر مربوط به ستون را پیدا می کند. اگر مقادیر مربوط در یک ردیف متفاوت را می خواهید پیدا کنید، باید از تابع HLOOKUP جهت جستجو در چند شیت اکسل استفاده نمایید. این تابع به شکل زیر نوشته می شود.
= HLOOKUP ([lookup_value], [table_array], [row_index_num], [range_lookup])
مقدار [lookup_value] همانند تابع قبلی مقداری است که آن را می دانید.
[table_array] معادل سلول هایی است که می خواهید جستجو کنید.
[row_index_num] نیز ردیفی است که مقادیر بازگشتی که از آن آمده است را مشخص می کند.
[range_lookup] نیز همانند نمونه قبل است و بهتر است خالی باشد یا مقدار ۰ وارد شود.
به عنوان مثال یک فایل شامل یک ردیف برای نام ایالت، همراه با نمره SAT ایجاد کرده ایم که شامل سال های ۲۰۰۰ تا ۲۰۱۴ می شود. برای پیدا کردن نمره میانگین در ایالت Minnesota در سال ۲۰۱۳ از این تابع استفاده می کنیم:
= HLOOKUP (2013, A1: P51, 24)
توجه داشته باشید که این ایالت در لیست در ردیف ۲۴ قرار دارد. همانطور که در تصویر خواهید دید، مقدار مورد نظر نمایش داده می شود.
این تابع نیز فقط یک مقدار را بازگشت می دهد.
استفاده از تابع های INDEX و MATCH در اکسل INDEX و MATCH
و تابع متفاوت دستور جستجو در اکسل هستند. اما زمانی که آن ها با یکدیگر مورد استفاده قرار می گیرند، می توانند صفحات گسترده ای را سریعتر جستجو کنند. هر دو تابع دارای نقاطی ضعفی هستند که با استفاده ترکیبی، این میزان کم می شود. ساختار مورد استفاده به شکل زیر است
=INDEX([array], [row_number], [column_number])
=MATCH([lookup_value], [lookup_array], [match_type])
در تابع Index، مقدار [array] آرایه ای است که آن را جستجو می کنید.
[row_number] و [column_number] را می توان برای محدود کردن جستجو استفاده کرد.
در تابع Match نیز مقدار [lookup_value] عبارت جستجو است که می تواند یک رشته یا عدد باشد. [lookup_array] نیز آرایه ای است که در آن اکسل برای عبارت سرچ، جستجو می کند.
[match_type] هم یک استدلال اختیاری است که می تواند ۰، ۱ یا -۱ باشد. ۰ مقدار دقیق را باز می گرداند، ۱ در صورت نبود مقدار معادل بزرگتر و -۱ معادل کوچکتر را بازگشت خواهند داد.
در مثالی برای Match می خواهیم مقدار ۶۴۶ در ستون F مشخص شود که این کار را انجام داده و رقم ۴ را نشان می دهد
Index نیز از سویی دیگر برعکس عمل می کند.مرجع سلولی را می گیرد و مقدار آن را می دهد. به طور مثال برای بازگردانی سلول شش از ستون City مقدار Anchorage, از ردیف ۶ را می دهد.
برای نمونه ترکیبی، به طور مثال برای یک شخص به نام خانوادگی Waters را پیدا و امتیاز او را مشاهده کنید. در اینجا از فرمول زیر استفاده می کنیم:
=INDEX(F:F, MATCH(“Waters”, C:C, 0))
همانطور که می بینید، مقدار Owen Waters با امتیاز ۱۷۲۰ نمایش داده می شود. به همین ترتیب می توانید از این توابع استفاده نمایید.
استفاده از تابع سرچ اکسل می توانید از تابع سرچ اکسل نیز برای جستجوی پیشرفته در اکسل استفاده کنید. برای این منظور از تابع زیر استفاده کنید:
SEARCH( substring, string, [start_position] )
- مقدار substring شامل زیر رشته ای است که می خواهید پیدا کنید.
- string نیز رشته برای جستجو می باشد.
- start_position هم اختیاری است و موقعیت در رشته ای که جستجو شروع می شود می باشد. موقعیت پیش فرض ۱ است.
این تابع مقداری عددی را نشان می دهد و اگر نتیجه ای پیدا نکند، عبارت #VALUE! را به عنوان خروجی می دهد. به عنوان مثال تابع =SEARCH(“bet”, A1) را اعمال میکنیم و نتیجه را می بینید.
تابع های زیادی برای استفاده در اکسل وجود دارند که تعدادی برای جستجو را به شما معرفی کردیم.اگر به طور حرفه ای با اکسل کار می کنید، حتما با تابع های کاربردی آشنا شوید