آموزش تابع index در اکسل: تابع ایندکس در اکسل با مثال

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

آموزش تابع index در اکسل

تابع index را می توان در دسته توابع جستجو و مرجع (Lookup & Reference) جای داد. این تابع، قابل استفاده با تابع MATCH خواهد بود. بدین شکل که پیدا کردن موقعیت یک مقدار برای تابع ایندکس، بر عهده تابع MATCH بوده و سپس تابع index اقدام به برگرداندن مقدار مورد نظر می کند.

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

1. قالب آرایه (Array Format)
2. قالب مرجع (Reference Format)

قالب	فرمول	توضیحات
قالب آرایه (Array Format)	INDEX(array, row_num, [column_num])	برای پیدا کردن مقدار یک سلول در محدوده مشخص
قالب مرجع (Reference Format)	INDEX(reference, row_num, [column_num], [area_num])	برای جستجو در چند محدوده مشخص‌شده

پیش از ادامه مطلب پیشنهاد می کنیم که اگر علاقمند به یادگیری مهارت های اکسل هستید از مطلب تابع VLOOKUP در اکسل نیز دیدن فرمائید.

از قالب آرایه چه هنگام استفاده می شود؟

قالب آرایه (Array Format) که یکی از قالب های متعلق به تابع index است، به منظور پیدا کردن مقدار یک سلول در محدوده ای مشخص استفاده می گردد تابع index با قالب آرایه دارای این فرمول می باشد:

INDEX(array, row_num, [column,_num])

• Array مربوط به محدوده مورد نظر می باشد.
• row_num مربوط به شماره ردیف است.
• در column,_num نیز شماره ستون مورد نظر درج می گردد.

آرگومان	توضیحات
Array	محدوده‌ای که مقدار مورد نظر در آن جستجو می‌شود
Row_num	شماره ردیف مورد نظر
Column_num	(اختیاری) شماره ستون مورد نظر

آرگومان های اول و دوم یعنی Array و row_num باید حتما وارد شوند و این امر، ضروری است. در خصوص آرگومان سوم، ضرورتی وجود نداشته و نیازی به وارد کردن حتمی آن نیست.

قبل از اینکه به سراغ ادامه مطلب برویم، قصد داریم به شما پیشنهاد کنیم که از مقاله فارسی کردن اعداد در اکسل نیز دیدن فرمائید که توسط سایت ۲۴ وب لرن گردآوری شده است.

قالب مرجع در ایندکس چه کاربردی دارد؟

قالب دیگری که برای تابع index می توان برشمرد، قالب مرجع (Reference Format) است. زمانی که بخواهیم در چند محدوده مشخص شده انتخابی، مقدار یک سلول را پیدا کنیم، از این قالب بهره می گیریم. فرمولی که می توان برای این قالب تعریف کرد، بدین شرح می باشد:

INDEX(reference, row_num, [column_num], [area_num])

• در Reference باید محدوده های مورد نظر را درج نمود. علامت کاما اقدام به جداسازی این محدوده ها نموده و از علامت پرانتز نیز برای محاصره آنها استفاده می گردد.
• Row_num جایی است که شماره ردیف در آن درج می شود.
• شماره ستون در Column_num وارد می گردد.
• Area_num یک محدوده را انتخاب می کند، حتی اگر در Refrence چند محدوده انتخاب شده باشند. به ترتیبی که انتخاب محدوده ها صورت گرفته، به همان شکل نیز شماره گذاری انجام می شود. در صورت تایپ نکردن عبارت در این قسمت، تابع اولین محدوده را در نظر می گیرد.

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

مثالی درباره قالب آرایه در تابع index

فرض کنید شما یک مشاور تحصیلی هستید که می خواهید در یک فایل اکسل، میزان مطالعه دانش آموزان در طول روز و معدل آنها را وارد کنید:

۱	نام و نام خانوادگی	میزان مطالعه (در روز)	معدل کل
۲	پریسا نوروزی	۴	۱۷.۳۲
۳	زهرا صفایی	۲	۱۵.۶۰
۴	فاطمه عابد	۷	۱۹.۷۰
۵	آزیتا بردبار	۵	۱۸
۶	سارا رحیمی	۶	۱۸.۵
۷	معصومه پورجعفر	۱	۱۳
۸	هلیا رضایی	۳	۱۶

اگر هدفتان پیدا کردن معدل آزیتا بردبار است، باید برای شروع کار، اقدام به کلیک بر روی سلول نتیجه E2 نمایید. حالا به نوار فرمول رفته و با کلیک بر روی Fx به کار خود ادامه دهید. (به جای اینکار می توانید کلیدهای ترکیبی  Shift+F3 را فشار دهید.) پس از آن، پنجره Insert Function را خواهید داشت. در این پنجره، دسته توابع جستجو و مرجع (Lookup & Reference) را می بینید که می بایست از طریق آن اقدام به پیدا کردن تابع index نمایید.

پس از اینکه تابع را پیدا کردید، برای تایید، ok را فشار دهید. پس از آن با پنجره پنجره Select Arguments روبرو خواهید بود. در این قسمت باید گزینه نخست یعنی قالب آرایه array, row, num.column_num را برگزینید. در ادامه ok را فشار دهید.

بعد از آن پنجره Function Argumentes را خواهید داشت که می بایست با کلیک در کادر Array و انتخاب محدوده مورد نظر (A2:C8) ادامه دهید. حالا با کلیک در کادر Row_num و انتخاب عدد ۴، به ادامه کار بپردازید. سپس کادر Column_num را خواهید داشت که باید بر روی آن نیز کلیک کرده و عدد ۳ را انتخاب کنید و ok را بزنید. همینجا کار شما پایان یافته و معدل آزیتا بردبار به دست خواهد آمد.

آموزش تابع index در اکسل: قالب مرجع و مثالی در رابطه با آن

در این قسمت از آموزش تابع index در اکسل، به بیان مثالی در خصوص قالب مرجع در تابع ایندکس خواهیم پرداخت که در واقع شکل دیگری از مثال قبلی است:

مثال:

اگر می خواهید معدل آزیتا بردبار را در ازای انتخاب چند محدوده به دست آورید، با قالب مرجع سروکار خواهید داشت. با کلیک بر روی سلول نتیجه (E2) کار خود را آغاز کنید. حالا بر روی Fx که در نوار فرمول وجود دارد، کلیک کرده و یا از کلیدهای ترکیبی Shift+F3 کمک بگیرید. با این اقدام، پنجره Insert Function باز می شود. تابع ایندکس را از دسته توابع جستجو و مرجع که در این پنجره مشاهده می کنید، انتخاب نمایید. حتما برای تایید، روی ok بزنید.

در پنجره بعدی یعنی پنجره Select Arguments، قالب مرجع که گزینه دوم می باشد را انتخاب کنید. این بار هم روی ok بزنید. پنجره سوم، پنجره Function Argumentes است که در آن کادر Refrence را خواهید دید. بر روی آن کلیک کرده و محدوده های انتخابی را در آن درج کنید. با علامت کاما، محدوده ها را از هم جدا نموده و با علامت پرانتز، آنها را محاصره کنید (A2:C3,A5:C8). با کلیک در کادر Row_num و تایپ عدد ۱ به کارتان ادامه دهید.

کادر Column_num را می بینید؟ بر روی آن کلیک کرده و عدد ۳ را تایپ کنید. پس از آن با کادر Area_num روبرو خواهید بود که می توانید شروع به تایپ عدد ۲ در آن نمایید. در آخر بر روی کلید ok بزنید تا تغییرات مورد نظر اعمال شوند.

آموزش تابع index در اکسل: مثالی در رابطه با ترکیب توابع index و Match

در این بخش از آموزش تابع index در اکسل می خواهیم با ذکر مثالی درباره ترکیب توابع index و Match در اکسل، مفاهیم را به طور کامل در اختیارتان قرار دهیم:

مثال:

فرض کنید یک فایل اکسل دارید که نام کشورها در ستون A و رتبه جهانی پیشرفت آنها در ردیف B درج شده است:

۱	نام کشور	رتبه جهانی پیشرفت
۲	سوئیس	۱
۳	آمریکا	۲
۴	دانمارک	۷
۵	کره جنوبی	۸
۶	انگلیس	۱۱
۷	نروژ	۱۵
۸	گرجستان	۲۰
۹	سودان	۳۸

اگر هدفتان پیدا کردن رتبه جهانی نروژ در حوزه پیشرفته می باشد، باید این فرمول را در ردیف D2 درج کنید:

INDEX(B2:B9,MATCH((“نروژ”, A2:A9,0))

در این فرمول، تابع MATCH وارد عمل شده و موقعیت یک سلول بر حسب شماره ستون و ردیف را فراخوانی می کند. در ادامه تابع index شروع به فراخوانی مقدار سلول در یک جدول بر حسب شماره ستون و ردیف خواهد کرد. در آخر رتبه کشور نروژ مشخص خواهد شد.

سایت پشتیبانی مایکروسافت درباره استفاده از توابع Match و Index اینگونه می گوید:

The INDEX function actually uses the result of the MATCH function as its argument. The combination of the INDEX and MATCH functions are used twice in each formula – first, to return the invoice number, and then to return the date

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

نکاتی که باید بدانید

در بحث آموزش تابع index در اکسل، نکاتی وجود دارند که دانستن آنها ضروری به نظر می رسد. به این نکات در قسمت زیر اشاره کرده ایم:

1. اگر آرگومان‌های row_num ، column_num و area_num حاوی مقادیر غیر عددی باشند، تابع ایندکس با خطای #NAME? روبرو خواهد شد.
2. خطای #REF! در تابع ایندکس، زمانی رخ می دهد که تعداد ردیف‌های موجود در محدوده معین از مقدار آرگومان row_num، کمتر باشند.
3. حتی در صورت کمتر بودن تعداد ستون‌های موجود در محدوده معین از مقدار آرگومان Column_num این خطا رخ می دهد.
4. ناگفته نماند اگر تعداد محدوده‌های انتخاب شده از مقدار آرگومان Area_num کمتر باشند، باز هم با این خطا روبرو خواهید شد.
5. همچنین خطای #REF! در تابع ایندکس زمانی رخ می دهد که مقدار آرگومان‌های Row_num و Column_num، اشاره ای به سلول های موجود در محدوده نداشته باشند.
6. وارد کردن مقدار در قسمت Row_num، در صورتی که با یک ردیف از محدوده مواجه باشید، الزامی نیست.
7. وارد کردن مقدار در در قسمت Column_num نیز در صورت وجود یک ستون در محدوده، ضروری نخواهد بود.
8. در صورت حذف row_num، column_num الزامیست و برعکس این قضیه نیز وجود دارد.

خطا	علت
#NAME?	مقدار row_num, column_num, یا area_num غیر عددی است
#REF!	مقدار row_num یا column_num از محدوده خارج است

پرورش مهارت های جدید همچون اکسل، دروازه های جدیدی را به رویتان می گشاید

تا به اینجا با آموزش تابع index در اکسل آشنا شدید. اگر خواستار آشنایی با موضوعات بیشتری از نرم افزار اکسل هستید، دوره آموزشی ۲۴ وب لرن را به شما توصیه می کنیم. این دوره آموزش اکسل مقدماتی که توسط اساتید خبره همچون جناب آقای تقی زاده آموزش داده می شود، سرشار از نکات و مفاهیم کاربردی اکسل است که قطعا در شغل فعلی خود و یا در مشاغلی که در آینده خواهید داشت، به کارتان می آیند.

 

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

کلام پایانی

آموزش تابع index در اکسل یکی از مهمترین مفاهیم آموزشی است که خواهید آموخت. با ذکر مثال هایی درباره هر کدام از قالب های تابع ایندکس، سعی کردیم تا آموزش مفید و راهبردی ای را در اختیارتان قرار دهیم. برای دریافت مفاهیم بیشتر و آموزش های کامل تر می توانید دوره آموزشی ۲۴ وب لرن را تهیه نموده و خود را آماده دریافت موضوعات و آموزش های کلیدی نمایید.

سوالات متداول

تابع index در اکسل چیست و چه کاربردی دارد؟

جهت دستیابی به پاسخ این سوال، پیشنهاد می کنیم از ابتدا تا انتهای متن را دنبال کنید.

آیا می توان مقادیر غیر عددی را در تابع index به کار برد؟

خیر. در آموزش تابع index در اکسل و نکات مربوط به آن متوجه شدیم که در صورت ورود مقادیر غیر عددی، تابع با خطای #NAME? مواجه خواهد شد.

آموزش تصویری تابع index در اکسل را از کجا تهیه کنیم؟

دوره آموزشی اکسل ۲۴ وب لرن، آموزش های تصویری و کاملی را در خصوص تابع ایندکس در اختیارتان خواهد گذاشت.