سرچ در اکسل – آموزش تصویری و کامل روش‌ های مختلف

کار با اکسل برای بسیاری از افراد کمی ترسناک است. حقیقتا هم مواجهه با صدها و هزاران سلول که با انواع مختلف داده پر شده‌اند، می‌تواند ترسناک و خسته‌کننده باشد. مثلا چطور می‌شود سرچ در اکسل را در بین این حجم بسیار زیاد داده انجام داد؟ روش‌های مختلفی برای این کار وجود دارد که مایکروسافت برای راحتی کاربران، آن‌ها را به این برنامه افزوده است.

اگر بدانید در اکسل دنبال چه چیزی هستید، استفاده از ابزارهای جستجوی اکسل، یافتن هر عبارت و سلولی را بسیار آسان می‌کنند. به همین دلیل در ادامه این مطلب به بررسی و توضیح این روش‌ها پرداخته‌ایم. کار با اکسل، آن‌طور که به‌نظر می‌رسد سخت نیست، می‌توانید همین حالا آموزش‌های ساده و کاربردی مجله فراردس درباره اکسل را با مقاله «چگونه اکسل یاد بگیریم؟» دنبال کنید تا متوجه این قضیه بشوید.

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

در گوشه سمت راست بالای پنجره اکسل، کادری قرار دارد که روی آن نوشته شده است: Search Sheet. کافی است داخل این کادر کلیک و کلمه مورد نظر را وارد کنید، سپس با فشردن دکمه Enter روی کیبورد، روند سرچ در اکسل شروع خواهد شد. بیشتر برای جستجوی متن در اکسل از این روش استفاده می‌شود.

همچنین می‌توانید به کمک میانبر Ctrl+F در ویندوز و Command+F در مک، به نوار جستجو دسترسی داشته باشید. ابتدا این دو دکمه را روی کیبورد فشار دهید تا کادر جستجو باز شود. سپس عبارت موردنظر را بنویسید و دکمه Enter را بزنید. در نهایت می‌توانید با انتخاب گزینه Finde next، بین نتایج مختلف در اکسل جابجا شوید.

سرچ در اکسل به کمک ویژگی Find

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

• در صفحه اکسل زبانه Home را باز کنید. سپس در بخش Editing‌ ابتدا گزینه Find & Select و بعد گزینه Find را برگزینید.

سپس پنجره Find and Replace را خواهید دید، اما با زدن دکمه Options، گزینه‌های پیشرفته‌تری نشان داده می‌شوند.

با استفاده از گزینه‌های زیر می‌توانید سرچ خود را در اکسل انجام دهید:

فیلم آموزش اکسل Microsoft Office Excel 2016

کلیک کنید

• Find what: عدد یا متن مورد نظر برای سرچ را در این قسمت وارد کنید.
• Format: این قسمت را در شرایطی انتخاب کنید که به دنبال متنی با فرمت خاص هستید. فرمتی که در پنجره Find Format تعریف شده است.
• Within: برای انجام عمل سرچ در صفحه جاری یا کل Workbook کاری، گزینه Sheet یا WorkSheet را در این قسمت انتخاب کنید.
• Search: در این بخش با انتخاب یکی از گزینه‌های By Rows یا By Columns، تصمیم می‌گیرید که سرچ هر بار در یک سطر یا یک ستون انجام شود‌ (و وقتی نتیجه پیدا شد در آن سطر یا ستون توقف خواهد کرد).
• Look In: می‌توانید با انتخاب این گزینه به سرچ خود عمق ببخشید و به اکسل بگویید که در کدام منطقه از شیت جستجو کند، فرمول‌ها، مقادیر، یادداشت‌ها یا نظرات.
• Match case: این گزینه، سرچ در اکسل را با حساسیت بر کوچک و بزرگ بودن حروف انگلیسی انجام می‌دهد.
• Match entire cell contents: با انتخاب این مورد، اکسل فقط به دنبال عبارتی که وارد کرده‌اید می‌گردد و باقی موارد مشابه به آن را نادیده خواهد گرفت.

با زدن گزینه Find Next، خواهید دید هر مکانی که حاوی متن مورد جستجو بوده، برجسته (Highlight) می‌شود. برای رفتن به نتیجه بعدی می‌توانید دوباره همین دکمه را بزنید. اما برای دیدن تمام نتایج باید گزینه Find All را انتخاب کنید.

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

سرچ عملگرهای عام در اکسل

هنگام استفاده از گزینه Find what باید بدانید که عملگرهای عام چطور کار می‌کنند. به این ترتیب می‌توانید سرچ پیشرفته را هر چه بیشتر شخصی سازی کنید و دقیق‌تر به آنچه می‌خواهید برسید. پس در ادامه با انواع عملگرهای عام که در این زمینه کمک کننده هستند، بیشتر آشنا خواهیم شد:

• علامت سوال (؟): می‌تواند جایگزین یک کاراکتر یا عدد شود. مثلا اگر بنویسید «C?t»، تمام کلماتی که با C شروع و به t ختم می‌شوند، مانند Cat ،Cut یا Cot جستجو خواهند شد.
• علامت ستاره (*): این علامت جایگزین بخشی از یک کلمه می‌شود. مثلا اگر بنویسید «C?t»، تمام کلماتی که با C شروع و به t ختم می‌شوند، با هر تعداد حروفی که باشند، در نتایج جای خواهند داشت. مثلا کلمات Cart ،Cast یا Count.
• علامت تیلدا (~): این علامت را زمانی استفاده کنید که می‌خواهید خود علامت‌های قبلی را بیابید. مثلا اگر به دنبال عبارت «?user» (شامل علامت سوال) هستید، باید آن را به شکل «?~user» وارد کنید. به این ترتیب، خود علامت سوال نیز همراه با کل عبارت، جستجو خواهد شد.

سرچ در اکسل به کمک گزینه Replace

در همان پنجره Find & Reolace که در روش‌های قبلی باز کردیم، گزینه Replace نیز دیده می‌شود. به کمک این گزینه نه‌تنها می‌توانید عبارت‌های مختلف را در شیت‌های اکسل جستجو کنید، بلکه امکان جایگزین کردن آن‌ها با هر متن دیگری را نیز خواهید داشت.

برای یافتن این گزینه همچنین می‌توانید زبانه Home را باز کنید و سپس در بخش Editing‌، گزینه Find & Replace را بزنید. در این قسمت با انتخاب گزینه Replace وارد تنظیمات یافتن و جای‌گزینی خواهید شد. همچنین با زدن دکمه Options، گزینه‌های پیشرفته‌تر، نشان داده می‌شوند.

تمام گزینه‌های پیشرفته این قسمت نیز دقیقا مانند زبانه Find کار می‌کنند. تنها تفاوت این است که در این قسمت، گزینه‌ای به نام Replace With وجود دارد.

در این بخش باید متنی را تایپ کنید که می‌خواهید با نتایج پیدا شده در گزینه Find With، جایگزین شود. سپس مراحل زیر را برای یافتن و جای‌گزینی نتایج، انجام دهید:

• ابتدا گزینه Find Next را بزنید تا مکان اولین نتیجه جستجوی متن یا عدد، برجسته و مشخص شود.
• سپس برای جایگزین کردن آن، دکمه Replace را لرگزینید. اگر خواستید سلول نتیجه‌ای را بدون تغییر بگذارید، کافی است گزینه Find Next را بزنید.
• روند Find Next و Replace را تا جایی ادامه دهید که تمام نمونه‌های مورد نظر به درستی تغییر کرده باشند.

همچنین این امکان وجود دارد که با زدن گزینه Replace All، تمام نتایج را به صورت یکجا جایگزین کنید. به این ترتیب نیازی نیست یکی‌یکی نمونه‌ها را ببینید و آن‌ها را تغییر دهید. البته اگر عبارتی که به دنبال آن هستید، بخشی از یک سلول است که نمی‌خواهید کل متن آن جایگزین شود، باید ریسک از دست دادن چنین داده‌ای را به جان بخرید.

استفاده از توابع برای سرچ در اکسل

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

در ادامه چند نمونه از بهترین توابع سرچ در اکسل را با هم می‌بینیم که کار ما را بسیار ساده‌تر کرده‌اند.

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

کلیک کنید

تابع VLookup در اکسل چیست؟

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

 استفاده از VLOOKUP روی بازه ای از مقادیر در اکسل با فرمول زیر انجام می‌شود:

1=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])

به عنوان مثال، در جدول زیر، چند حرف انگلیسی و تعداد تکرار آن‌ها در چند متن را داریم. می‌خواهیم ببینیم حرف «e» چندین بار تکرار شده است. برای این کار تابع VLookup را به شکل زیر می‌نویسیم:

1=VLOOKUP("e",A1:B7,2,FALSE)

آرگومان اول این فرمول، مقدار مورد جستجو است. آرگومان دوم، محدوده‌ای است که می‌خواهیم جستجو را انجام دهیم. عدد 2، یعنی مقدار متناظر با سلول مورد جستجو را در ستون دوم، پیدا کند و نشان دهد. آرگومان آخر نیز برای این است که سرچ را با تطابق کامل انجام دهد و فقط به دنبال حرف «e» باشد. نتیجه این مثال را در تصویر بالا می‌بینید.

نکاتی درباره تابع Vlookup برای سرچ در اکسل

خوب است هنگام کار با تابع Vlookup چند نکته را مد نظر داشته باشید. حتما مطمئن شوید که اولین ستون Range یا محدوده، مقدار Vlookup که دنبال آن هستید را در خود داشته باشد. اگر این مقدار در ستون اول نباشد، تابع نتیجه نادرستی را نشان خواهد داد. البته اگر ستون‌ها به درستی در کنار یکدیگر قرار گرفته باشند، مشکلی پیش نمی‌آید.

همچنین توجه داشته باشید که Vlookup همیشه فقط یک مقدار را در خروجی نشان می‌دهد. مثلا اگر در جدول بالا، بیشتر از یک حرف «e» داشتیم، نتیجه تغییری نمی‌کرد.

تابع Hlookup برای سرچ در اکسل

در حالی که تابع Vlookup مقدار متناظر در ستون دیگر را پیدا می‌کند، تابع Hlookup، مقدار متناظر در یک ردیف دیگر را در خروجی نشان می‌دهدو برای این کار، نام ستون‌ها را بررسی می‌کند. مشخصا بررسی عناوین ستون‌ها برای یافتن عنوان درست، از فیلتر کردن ساده‌تر است. به همین دلیل، بهترین استفاده از Hlookup زمانی خواهد بود که داده‌هایی با حجم بسیار زیاد داشته باشید یا با داده‌هایی کار کنید که بر اساس زمان مرتب شده‌اند.

• مطالب پیشنهادی برای مطالعه: 
  • استفاده از توابع منطقی (Logical Functions) در اکسل (+ دانلود فیلم آموزش گام به گام)
  • تابع IF در اکسل — به زبان ساده
  • مرتب سازی در اکسل — راهنمای کاربردی
  • تابع XOR در اکسل – آموزش تصویری کاربرد به زبان ساده
  • توابع شمارشی و جمع بندی پیشرفته در اکسل (+ دانلود فیلم آموزش گام به گام)

تابع Hlookup مانند فرمول زیر نوشته می‌شود:

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

فرض کنید لیستی از نام دانش‌آموزان را در ستون ‌A و معدل ‌آن‌ها را در ستون B داریم. در ادامه به کمک تابع Hlookup، معدل «دانش‌آموز 4» را پیدا می‌کنیم.

برای این کار، باید از فرمول زیر استفاده کنیم:

1=Hlookup("B",A1:B6,5)

آرگومان اول این تابع، «B» است، چون معدل‌ها در ستون «B» هستند. آرگومان دوم، کل جدول را از A1 تا B6 در نظر می‌گیرد، چون در این تابع، نام ستون‌ها را نیاز داریم. آرگومان سوم نیز برای مشخص کردن چهارمین دانش‌آموز است و از آن‌جایی که سر تیتر نام ستون‌ها نیز در شمارش حساب می‌شود، پس «دانش‌آموز 4» در پنجمین سلول قرار دارد.

نکات مهم درباره تابع Hlookup

در این تابع نیز درست مانند Vlookup، مقداری که به دنبالش هستیم، باید در ستون اول موجود باشد. البته چون در Hlookup از نام ستون برای سرچ در اکسل استفاده می‌شود، این مورد مشکل چندانی ایجاد نخواهد کرد. همچنین Hlookup نیز فقط یک مقدار را در خروجی نشان می‌هد.

تابع Index-Match در اکسل چیست؟

این تابع همانند Vlookup عمل می‌کند، با این تفاوت که نیازی نیست داده‌های مورد جستجو، در سمت چپ ستونی باشند که انتخاب می‌کنیم. فرمول آن، ترکیبی از دو تابع ‌Index و Match در اکسل است. تابع Index، مقدار یک سلول جدول را بر اساس شماره ستون و ردیف، نشان می‌دهد. در حالی که تابع ‌Match برای یافتن و نشان دادن موقعیت یک سلول در یک سطر یا ستون است. تابع Index مانند زیر نوشته می‌شود.

1=INDEX([array], [row_number], [column_number])

برای درک بهتر این تابع ترکیبی، بهتر است ابتدا ببینیم هر کدام از این دو تابع به صورت جداگانه چطور عمل می‌کنند. تابع Match یک کلمه برای جستجو دریافت می‌کند و مرجع سلول حاوی آن را در خروجی نمایش می‌دهد.

در مثال زیر جدولی از چند حرف و تعداد تکرار آن‌ها در یک جمله داریم. می‌خواهیم به کمک تابع Match، موقعیت حرف «k» را در جدول پیدا کنیم. برای این کار، فرمول تابع را به شکل زیر می‌نویسیم:

1=MATCH("k",A:A)

در این فرمول، "k" عبارتی است که می‌خواهیم موقعیت آن را پیدا کنیم. A:A نیز محدوده‌ای است که عبارت مورد جستجو در آن واقع شده است. این فرمول، ستون A را برای یافتن حرف «k» جستجو می‌کند و در نهایت موقعیت این سلول را مانند تصویر زیر، نشان می‌دهد.

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

1=INDEX(A:A,4)

در این فرمول، A:A ستونی است که سلول مورد نظر، در آن قرار دارد. 4 نیز عدد مربوط به ردیفی است که می‌خواهیم مقدار آن را ببینیم. در نتیجه، مقدار موجود در سلول چهارم از ستون A، مانند تصویر زیر، در خروجی نشان داده می‌شود.

در ادامه با استفاده از ترکیب این دو تابع، به سرچ در اکسل می‌پردازیم. در واقع ابتدا تابع Match، موقعیت یک سلول را پیدا می‌کند، و سپس تابع Index به کمک آن موقعیت، مقدار مورد نظر را جستجو می‌کند.

فرض کنید می‌خواهیم تعداد دفعات تکرار حرف «s» را به کمک تابع Index-match پیدا کنیم. برای این کار، فرمول زیر را می‌نویسیم:

1=INDEX(B:B,MATCH("s",A:A,0))

همان‌طور که مشخص است، در این فرمول ابتدا تابع MATCH، موقعیت سلول حاوی حرف «s» را پیدا می‌کند و سپس تابع INDEX، مقدار آن را مانند تصویر زیر، در خروجی نمایش می‌دهد.

تابع Find برای سرچ در اکسل

مقاله سرچ در اکسل بدون وجود تابع Find کامل نمی‌شود. اما این تابع، آن‌طور که فکر می‌کنید، نیست. از تابع FIND برای یافتن موقعیت یک رشته متنی درون یک رشته متنی دیگر و جداسازی متن در اکسل استفاده می‌شود. مثلا می‌شود برای جدا کردن عدد از متن در اکسل از این تابع کمک گرفت.

به عنوان مثال، در ادامه می‌خواهیم موقعیت اولین حرف «x» را در جمله «The brown fox jumped over the fence» پیدا کنیم. فرمول ما باید به شکل زیر باشد:

1=FIND("x", "The brown fox jumped over the fence")

به این ترتیب، تابع Find، موقعیت اولین حرف «x» را درون این جمله پیدا می‌کند. توجه داشته باشید که در این تابع، فاصله‌ها نیز به عنوان یک حرف شمرده می‌شوند. پس نتیجه مانند تصویر می‌شود:

نکاتی درباره تابع Find

مانند Vlookup ،Hlookup و سایر توابع این‌چنینی، تابع Find نیز، فقط اولین مقدار مورد نظر در رشته را پیدا می‌کند. مثلا در این مثال، موقعیت اولین x در خروجی نشان داده شده است. همچنین باید بدانید که این تابع، نسبت به بزرگی و کوچکی حروف انگلیسی حساس است. می‌توانید برای یافتن چندین حرف از آن استفاده کنید و امکان جستجوی اعداد را نیز دارد.

ممکن است فکر کنید تابع Find به تنهایی چندان کاربردی نیست، اما اگر در ترکیب با سایر توابع از آن استفاده کنید، می‌تواند بسیار مفید باشد. به عنوان مثال، می‌شود برای جدا کردن نام و نام خانوادگی در اکسل این تابع را با سایر توابع مرتبط به کار برد.

تابع Find در مقابل تابع Serach در اکسل

نمی‌شود نام تابع Find را برد و از تابع Search در کنار آن حرف نزد. در واقع این دو تابع یکسان هستند، با این تفاوت که Search نسبت به بزرگی و کوچکی حروف حساس نیست و از عملگرهای عام نیز پشتیبانی می‌کند. به این ترتیب به کمک تابع Search می‌توانید به دنبال مقادیری بگردید که دقیق نیستند. اکسل از سه علامت عام پشتیبانی می‌کند که شامل علامت سوال (؟)، ستاره (*) و علامت تیلدا (~) است.

تابع Xlookup در اکسل چیست و چگونه نوشته می‌شود؟

تابع Xlookup به تازگی طراحی شده است تا جایگزین Vlookup باشد. این تابع نیز مانند تابع Vlookup برای یافتن مقداری خاص در یک جدول یا محدوده کاربرد دارد. با این تفاوت که Xlookup این امکان را فراهم می‌کند تا در ستون‌های سمت راست یا چپ مقدار درخواستی جستجو کنید. این درحالی است با Vlookup فقط می‌شود داده‌ها را در سمت راست ستون مورد نظر، پیدا کرد.

فرمول تابع Xlookup به شکل زیر است:

1=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

آرگومان‌های این فرمول به شکل زیر هستند:

فیلم آموزش محاسبات آماری در اکسل Excel

کلیک کنید

• lookup_value : مقداری است که به دنبال یافتنش هستیم.
• lookup_array : محدوده یا آرایه‌ای است که می‌خواهیم در آن‌جا جستجو کنیم.
• return_array : این آرگومان، محدوده یا آرایه‌ای است که در خروجی نشان داده می‌شود و اولین تفاوت این تابع با Vlookup است.
• [if_not_found] : آرگومان اختیاری است و در صورتی که مقدار مورد نظر یافت نشود، پیامی که شما وارد کرده‌اید را در خروجی نشان می‌دهد.
• [match_mode] : این آرگومان نیز اختیاری است و کمک می‌کند تطبیق دقیق (با 0)، مورد کوچک‌تر بعدی (با 1)، مورد بزرگ‌تر بعدی (با 1-) یا تطابق به کمک علامت‌های عام (با 2) را پیدا کنید. کافی است عدد مورد نظر را در فرمول، جایگزین این آرگومان کنید.
• [search_mode] : این آرگومان اختیاری این امکان را به شما می‌دهد که ترتیب جستجو را کنترل کنید. پیش فرض آن 1 است که جستجو را از اولین مورد شروع می‌کند. همچنین می‌توانید با 1-، سرچ را از آخرین مورد شروع کنید. به علاوه، می‌توانید یک جستجوی دودویی انجام دهید که به مرتب بودن lookup_array بستگی دارد و برای این کار، عدد 2 به ترتیب صعودی و 2- به ترتیب نزولی کار می‌کنند.

مثال استفاده از تابع Xlookup

برای مثال، جدول زیر را در نظر بگیرید. در این جدول، ستون A حاوی چند حرف است. ستون B تعداد تکرار این حروف در جمله 1 و ستون C تعداد تکرار این حروف در جمله 2 را نشان می‌دهند. می‌خواهیم ببینیم حرفی که در جمله اول 10 بار تکرار شده، در جمله 2 چند بار تکرار شده است. برای این کار از فرمول زیر استفاده می‌کنیم:‌

1=XLOOKUP(D1,B1:B9,C1:C9,,,-1)

آرگومان اول، D1 است، یعنی جایی که عدد 10 را در آن نوشته‌ایم. در واقع به سلول حاوی عبارت مورد جستجو، ارجاع داده‌ایم. B1:B9 محدوده‌ای است که عبارت مورد جستجو (یعنی 10)، در آن‌جا قرار گرفته است. C1:C9 نیز محدوده‌ای است که می‌خواهیم عدد متناظر با 10 را در آن‌جا پیدا کنیم.

در قسمت بعدی، دو آرگومان خالی داریم که [if_not_found] و [match_mode] هستند. از آن‌جایی که نمی‌خواستیم از آن‌ها استفاده کنیم، جای آن‌ها را خالی گذاشته‌ایم. در نهایت عدد 1- یعنی جستجو از آخرین مورد شروع شود، چون عبارت مورد جستجوی ما در انتهای جدول قرار گرفته است. نتیجه این فرمول را در تصویر زیر می‌بینید:

تابع Offset برای سرچ در اکسل

تابع Offset مقداری را از یک سلول برمی‌گرداند که می‌تواند در هر فاصله و هر جهتی از سلول مرجع قرار داشته باشد. البته این تابع هیچ سلولی را جابجا نمی‌کند یا تغییر نمی‌دهد. به کمک این تابع می‌توانید مقداری که چند سلول پایین‌تر، بالاتر یا در سمت چپ یا راست سلول مرجع است را پیدا کنید. فرمول این تابع به شکل زیر نوشته می‌شود:

1=OFFSET(reference, rows, cols, [height], [width])

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

یافتن مقداری خاص در سلولی پایین‌تر از سلول مرجع با تابع Offset

تابع Offset می‌تواند مقداری که در سلولی پایین‌تر از سلول مرجع قرار دارد را پیدا کند و در خروجی نمایش دهد. در مثال زیر، سلول مرجع یا نقطه شروع ما، سلول A1 است. می‌خواهیم حرف «m» که سه سلول پایین‌تر از آن است را پیدا کنیم. فرمولی که می‌نویسیم به شکل زیر است:

1=OFFSET(A1,3,0)

در این فرمول، سلول A1 را به عنوان مرجع و نقطه شروع قرار داده‌ایم. سپس عدد 3 را به معنی سه ردیف پایین‌تر، نوشته‌ایم و 0 را به عنوان تعداد ستون‌ها، وارد کرده‌ایم. از آنجایی که ستون، 0 است، تابع فقط در طول ردیف پیش می‌رود و سومین ردیف پایین‌تر از سلول A1 را مانند زیر در خروجی نشان می‌دهد:

یافتن مقداری خاص در سلول پایین‌تر و سمت راست سلول مرجع

در ادامه، می‌خواهیم یک سلول را مرجع قرار دهیم و سپس مقدار موجود در سلولی که پایین‌تر در سمت راست آن قرار دارد را پیدا کنیم. در این مثال، سلول A1 مرجع و نقطه شروع است. می‌خواهیم سه ردیف پایین‌تر و دو سلول به سمت راست این مرجع را پیدا کنیم. برای این کار، فرمول زیر را در سلول D1 می‌نویسیم:

1=OFFSET(A1,3,2)

همان‌طور که می‌بینید، عدد 3 را برای یافتن 3 ردیف پایین‌تر و عدد 2 را برای 2 ستون به سمت راست نسبت به مرجع، وارد کرده‌ایم. نتیجه مانند تصویر زیر خواهد شد:

یافتن مقداری خاص در سلول بالاتر و سمت چپ سلول مرجع

در ادامه مقداری را در چند سلول بالاتر و در سمت چپ سلول مرجع، پیدا می‌کنیم. در این مثال، مرجع، سلول C5 است. می‌خواهیم مقدار موجود در سلولی که سه ردیف بالاتر و دو سلول در سمت چپ آن قرار دارد را پیدا کنیم. برای این کار، فرمول به شکل زیر خواهد بود:

1=OFFSET(C5,-3,-2)

همان‌طور که در فرمول بالا می‌بینید، برای اشاره به بالا و سمت چپ از اعداد منفی استفاده کرده‌ایم. در این مورد، تابع Offset، سه سلول بالاتر و دو سلول به سمت چپ می‌رود تا به نتیجه برسد.

تابع Offset را می‌توانید در ترکیب با سایر توابع نیز استفاده کنید. مثلا اگر تابع Match را با آن ترکیب کنید، می‌توانید جستجوی پیشرفته‌ای در اکسل انجام دهید.

استفاده از فیلترها برای جستجو در اکسل

به کمک فیلتر در اکسل، می‌توانیم داده‌هایی که نمی‌خواهیم را مخفی کنیم. به عنوان مثال برای اینکه فقط سلول‌های مرتبط به عبارت «R» در جدول نشان داده شوند، باید مراحل زیر را پیش بگیرید:

ابتدا ستون‌هایی که می‌خواهید فیلتر شوند را به کمک ماوس انتخاب کنید و در زبانه Home و بخش Editing، گزینه Filter را مانند زیر برگزینید.

بعد از انجام این کار، خواهید دید که یک فلش به سر تیترهای هر ستون اضافه شده است. روی فلش ستون A کلیک کنید. در منوی باز شده، تیک تمام گزینه‌ها به غیر از «R» را بردارید و دکمه OK را بزنید. در نتیجه، خواهید دید که فقط سلول‌هایی نشان داده می‌شوند که حاوی عبارت «R» هستند.

سخن نهایی

پیدا کردن مقداری خاص در اکسل کار ساده‌ای است اما می‌تواند پیچیده هم باشد. مثلا می‌توانید به کمک نوار جستجو، هر متن یا عددی را در اکسل بیابید. اما اگر بخواهید مقداری متناظر با یک رشته را پیدا کنید یا ندانید موقعیت یک سلول در شیت کجاست، چطور؟

در چنین مواقعی باید از روش‌های پیچیده‌تر مانند توابع استفاده کرد. توابعی که در این مقاله معرفی کرده‌ایم، هر کدام به شیوه‌ای، عملیات سرچ در اکسل را انجام می‌دهند. مثلا تابع Index آدرس سلول را دریافت می‌کند و مقدار موجود در آن را نشان می‌دهد. در مقابل، تابع Match، مقدار یک سلول را می‌گیرد و موقعیت آن را نشان می‌دهد.

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