جستجو در اکسل و یا آشنایی با 4 نوع تابع بسیار کاربردی برای هر نوع جست و جویی در صفحات اکسل موضوع این مقاله از سایت است. اگر با نرم افزار اکسل کار می کنید پیدا کردن یک عدد، نام، عبارت و هر نوع داده ای کاری است که بار ها با ان سر و کار خواهید داشت. حتما می دانید که اغلب مواقع پیدا کردن یک سلول در فایل اکسل کار ساده ای است؛ اگر نتوانستید بین ردیف ها و ستون ها حرکت کنید و مطلب مورد نظرتان را پیدا کنید کافی است CTRL+F را فشار دهید. اما اگر فایل صفحه گسترده تان خیلی حجیم باشد می توانید از این چهار تابعی که به شما کاربران آموزش می دهیم بهره مند شوید تا در وقت و انرژی صرفه جویی کرده باشید. با استفاده از این چهار تابع فارغ از حجم فایل اکسل، بهره وری تان بالا تر خواهد رفت. پس با ما همراه باشید.

تابع VLOOKUP در اکسل برای جست و جو

این قابلیت به شما اجازه می دهد ستون و مقداری را مشخص کنید و مقداری را از ردیف مربوط به یک ستون دیگر باز خواهد گرداند (اگر مطلب را متوجه نشدید نگران نباشید در ادامه بیشتر توضیح خواهیم داد). دو مثال می زنیم:

اگر دنبال نام خانوادگی کارمندتان هستید می توانید آن را از روی شماره تلفن و اگر دنبال شماره تلفن کارمند هستید می توانید آن از روی نام خانوادگی پیدا کنید. ساختار این ترفند به شرح زیر است :

 

 

[lookup_value] تکه اطلاعاتی است که در اختیار دارید؛ مثلا اگر بخواهید فلان شهر در کدام ایالت/ استان قرار دارد،  [lookup_value] نام شهر خواهید بود. [table_array] به شما اجازه می دهد سلول هایی را مشخص کنید که در آنها، تابع به دنبال جست و جو می گردد و مقادیر را بر می گرداند.

وقتی دارید رِنج خود را انتخاب می کنید حتما مطمئن شوید که ستون اولی که در آرایه تان وجود دارد، ستونی است که مقدار جست و جو را شامل می شود.این نکته ی مهمی است. [col_index_num] عدد ستونی است که حاوی مقدار بازگشت است.

[range_lookup]  یک نشانه اختیاری است و مقدار 0 یا 1 می گیرد. اگر 1 را وارد کنید یا آن را حذف کنید، این تابع به دنبال مقداری می گردد که شما وارده کرده اید و یا به دنبال عدد بعدی که کمترین مقدار را دارد می گردد. بنابراین در تصویر زیر، VLOOKUP که به دنبال نمره 652 امتحان SAT است 646 را به دست می دهد و این عدد نزدیک ترین عدد در لیست است که کمتر از 652 است و [range_lookup] به صورت پیش فرض 1 می شود.

بیایید ببینیم چگونه می توان از این تابع استفاده کرد. در فایل اکسل فرضی ما کد ملی، نمره امتحان ، شهر، استان و نام و نام خانوادگی وجود دارد. فرض کنید می خواهیم نمره امتحان شخصی با نام خانوادگی وینترز را پیدا کنیم. VLOOKUP کار را برای ما ساده می کند. بدین شرح:

(VLOOKUP("Winters", C2:F101, 4, 0=

از آنجا که نمره های امتحان در چهارمین ستون بعد از نام خانوادگی هستند، ما برای نشانه شاخص ستون از عدد 4 استفاده کرده ایم. توجه داشته باشید که وقتی دارید به دنبال متنی می گردید، قرار دادن  [range_lookup]  روی 0 ایده ی خوبی است چرا که در غیر این صورت نتایج بدی به دست می آورید. در زیر، چیزی که مایکروسافت اکسل به دست ما می دهد نمایان است:

ملاحظه می کنید که اکسل، 651 یعنی نمره امتحانی را که متعلق به دانش آموزی به نام کندی وینترز است و در ردیف 92 قرار دارد را به دست داده است. اگر می خواستیم به دنبال نام او بگردیم زمان زیادی تلف می شد.

نکاتی در باب تابع VLOOKUP

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

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

تابع HLOOKUP در اکسل

تابع قبلی به دنبال مقادیر مربوطه در ستون دیگر می گشت، اما تابع HLOOKUP مقادیر مربوطه را در ردیف دیگر پیدا می کند. از آنجا که مرور سرستون ها تا پیدا کردن ستون مناسب و استفاده از یک فیلتر برابی پیدا کردن آنچه به دنبالش هسیتم کار آسانی است، بهتر است زمانی از این تابع استفاده شود که حجم فایل اکسل بسیار زیاد باشد و زمانی که مقادیر بر حسب زمان مرتب شده اند از این تابه استفاده شود. ساختار این تابع به شرح زیر است :

([HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup]=

در اینجا هم [lookup_value] مقداری است که شما می دانید و می خواهید مقدار مربوط به آن را پیدا کنید. [table_array] سلول هایی است که می خواهید در آنها جست و جو کنید. [row_index_num] ردیفی را مشخص می کند که مقدار از آن به دست خواهد آمد. و [range_lookup]ماند بالاست؛ آنها را خالی بگذارید تا هر وقت امکان داشت نزدیک ترین مقدار را به دست بیاورید و یا 0 را وارد کنید تا تنها به دنبال مطابقت های دقیق باشید.

نکاتی در باب HLOOKUP

همانطور که در VLOKUP دیدیم، مقدار جست و جو باید در ردیف اول آرایه ی جدولتان باشد؛ این در HLOOKUP به ندرت مساله است چرا که معمولا از عنوان ستون برای مقدار جست و جو استفاده خواهید کرد. HLOOKUP نیز تنها یک مقدار واحد را به دست خواهد داد.

توابع INDEX و MATCH در اکسل

دو تابع INDEX و MATCH دو تابع متفاوت هستند اما وقتی با هم استفاده شوند جست و جو در فایل اکسل حجیم و بزرگ بسیار سریع تر انجام می شود. هر دو تابع معایبی دارند اما با ترکیبشان می توان از نقاط قوت آنها استفاده کرد. اول، ساختارشان به شرح زیر است:

([INDEX([array], [row_number], [column_number=
([MATCH([lookup_value], [lookup_array], [match_type=

در تابع [INDEX، [array  آرایه ای است که در آن، به جست و جو خواهید پرداخت. [row_number] و [column_number]  را می توان برای محدود کردن جست و جو استفاده نمود؛ در ادامه به این مساله خواهیم پرداخت.

[MATCH’s [lookup_value یک عبارت جست و جو است که می تواند یک رشته یا عدد باشد؛ [lookup_array] آرایه ای است که در آن، مایکروسافت اکسل به دنبال عبارت جست و جو می پردازد.[match_type]  یک نشانه اختیاری است که می تواند 1 ، 0 یا -1 باشد؛ 1 بزرگترین مقداری را به دست خواهد داد که از عبارت جست و جو کمتر است یا برابر با آن است؛ و -1 کوچک ترین مقداری را به دست خواهد داد که از عبارت جست و جو بزرگتر است یا برابر با آن است.

ممکن است استفاده همزمان از آن دو تابع برای شما روشن نباشد برای همین در ادامه به شما آموزش می دهیم. MATCH عبارت جست و جویی را می گیرد و یک مرجع سلول به دست می دهد. در تصوی زیر، می توانید ببینید که در یک جست و جو برای مقدار 646 در ستون F ، MATCH عدد 4 را به دست می دهد.  از سوی دیگر تابع INDEX عکس این عمل می کند: یک مرجع سلول می گیرد و مقدار ر ادر آن به دست می دهد.

کاری که قرار است بکنیم این است که این دو تابع را با هم ترکیب کنیم تا MATCH مرجع سلول را به دست دهد و INDEX از آن سلول مرجع برای جست و جوی مقدار در یک سلول لستفاده کند. فرض کنیم دانش آموزی را به یاد دارید که نان خانوادگی اش واترز بود؛ و می خواهید ببینید نمره امتحان این دانش آموز چند است. فرمول آن به شرح زیر است:

((INDEX(F:F, MATCH("Waters", C:C, 0=

می بینید که نوع تطبیق در اینجا 0 است، و وقتی که به دنبال یک رشته می گردید از این 0 استفاده می کنید. در زیر، نتیجه اجرای آن تابع را ملاحظه می کنید:

همانطور که می بینید، واترز نمره 1720 گرفته است و این عددی است که وقتی تابع را اجرا می کنیم به دست می آید. وقتی می خواهید تنها چند ستون را بالا و پایین کنید و به جست و جو بپردازید این تابع ممکن است چندان سودمند به نظر نیاید اگر تصور کنید که اگر قرار بود این کار را 50 بار در یک فایل اکسل حجیم و بزرگ که چند صد ستون داشت انجام می دادید چقدر باید وقت و انرژی صرف می کردید!

مقالات مرتبط: