تابع INDEX

تابع INDEX یکی از مهمترین و پر‌کاربرد‌ترین توابع اکسل بوده و در دسته توابع جستجو و مرجع (Lookup & Reference) قرار دارد و یک روش جایگزین برای استفاده از تابع VLOOKUP می باشد . این تابع را در دوحالت می توان نوشت.

مساوی INDEX را که بنویسید و پرانتز را باز کنید، دو حالت در کادر به شرح زیر نمایش داده می شود:

حالت اول ( قالب آرایه )

حالت اول تابع index شامل سه آرگومان است

index(array;Row_num;[column_num])=

آرگومان‌های این قالب عبارتند از:
Array :محدوده موردنظر را در این قسمت وارد کنید.
Row_num :شماره ردیف‌ مورد نظر را در این قسمت وارد کنید.
Column_num :شماره ستون‌ موردنظر را در این قسمت وارد کنید.

در قسمت اول،Array می باشد و ما باید محدوده‌ی داده‌های مورد نظر را انتخاب کنیم.

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

همانطور که در مباحث آموزش اکسل ذکر شده است ،جداکننده‌ی سیستم در کادر زیر تابع قابل مشاهده است و مطابق آنچه در شکل زیر ملاحظه می کنید بعد از Array ویرگول نقطه یا همان سمیکالن ( ; ) نوشته شده است، پس جداکننده در اینجا ( ; ) می باشد ولی در برخی سیستم ها ممکن است کاما ( , ) باشد.

خوب در ادامه فرمول از ما می‌پرسد سطر چندم این محدوده را می خواهید؟ که به عنوان مثال می نویسیم سطر دوم را می خواهیم، سپس جداکننده را می گذاریم:

حالا از ما می پرسد ستون چندم را می خواهید؟ که مثلاً می نویسیم ستون دوم را می خواهیم:

در حقیقت در اینجا خواستیم بدانیم که در محدوده انتخاب شده،در سطر دوم، ستون دوم چه داده‌ای قرار دارد؟ آن ‌را نمایش بدهد که نمایش داد:

حالت دوم(قالب مرجع)

در این حالت وقتی که تابع index را نوشتید در قسمت Reference شما می‌توانید چند محدوده را انتخاب کنید و این محدوده‌ها را باید داخل یک پرانتز جداگانه قرار دهید. یعنی پرانتز دوم را باز می‌کنیم به‌طور مثال محدوده اول ماه ژانویه باشد و محدوده دوم ماه می و محدوده سوم فرضاً ماه اکتبر باشد، وقتی پرانتز را ببندید قسمت Reference را که شامل چند محدوده بود را وارد کرده‌اید.

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

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

جدا کننده را می‌گذاریم، می‌پرسد سطر چندم این محدوده را می خواهید؟ فرضاً سطر سوم را می‌خواهیم، می نویسیم 3 و کاما را می‌گذاریم. می‌پرسد ستون چندم را می خواهید؟

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

کاما را می‌گذاریم، چون چند محدوده داشتیم، باید مشخص کنیم محدوده چندم را می خواهیم. به‌طور مثال محدوده سوم را انتخاب می‌کنیم و پرانتز را می‌بندیم.

در حقیقت سوال ما با استفاده از این تابع این بود که در محدوده سوم، سطر سوم، ستون اول چه داده‌ای وجود دارد؟

مثال کاربردی اول

در ادامه قصد داریم با استفاده از این تابع مهم، یک کار کاربردی‌ را انجام دهیم. اگر تابع را به این صورت بنویسیم که مساوی، INDEX پرانتز باز محدوده مورد نظر را انتخاب می کنیم (B3:M5)، کاما سطر چندم؟ فرضاً سطر دوم، کاما، ستون چندم؟، در این قسمت اگر برای قسمت شماره ستون صفر را وارد کنیم یعنی به عبارتی گفته‌ایم کل سطر را می خواهیم، پرانتز را می بندیم:

ولی نمی‌تواند کل آن سطر را در یک سلول قرار دهد و اگر بخواهید نتیجه این تابع را ببینید باید کل تابع را انتخاب کرده و کلید F9 را بزنید.

در این حالت همانگونه که در شکل زیر ملاحظه می‌کنید می توانید ببینید که کل مقادیر سطر دوم را به ما نشان داده است. فرمول با زدن

Ctrl + Z به حالت قبل باز می‌گردد.

در این حالت با داده های فوق چه کاری می‌توانیم بکنیم؟ می توانیم جمع داده ها را با استفاده از تابع SUM به ترتیب زیر بدست آورید:

نتیجه کار جمع کل سطر دوم است. حال اگر بجای سطر دوم، سطر سوم را قرار می‌دادیم، کل مقادیر سطر سوم را جمع می‌کند. اگر بار دیگر بجای سطر، صفر قرار می‌دادیم و بجای ستون، مثلاً ستون دوم را منظور می‌نمودیم، به معنای آن بود که جمع کل مقادیر ستون دوم را محاسبه و نمایش دهد.

اگر بجای صفر هیچ داده‌ای وارد نکنید دقیقاً همین کار را انجام می‌دهد یعنی صفر را نمی‌گذاریم ولی کاماها باید گذاشته شود. اینجا دقیقاً جمع کل ستون دوم را نشان می‌دهد که برابر 1،388،800 است.

مثال کاربردی دوم

حالت دیگری که می‌توان از تابع INDEX استفاده کرد این است که به عنوان یک Reference از آن استفاده کنیم.

برای این کار بعد از مساوی اولین سلول محدوده مورد نظر را انتخاب می کنیم (B3) بعد از (:) که به معنای “تا” می باشد، آدرس سلولی را که می‌خواهیم تا آن سلول را در این قسمت منظور نماییم را می توانیم از تابع INDEX استفاده کنیم ولی به چه ترتیبی؟

بر این اساس که کل محدوده را INDEX کند (B3:M5)، حال اگر بخواهیم تا آخرین داده را داشته باشیم باید بنویسیم سطر سوم ستون دوازدهم (آخرین سلول) و بدلیل اینکه قبل از INDEX یک آدرس داشته ایم (B3:) نتیجه یک آدرس است که به معنای از B3 تا آخرین داده‌ی این محدوده می باشد.

که حتماً باید محدوده مذکور را داخل یک تابع دیگر بگذاریم که به عنوان مثال و با استفاده از تابع SUM می توانیم به یک نتیجه کاربردی برسیم و با این کار در حقیقت جمع سلول B3 تا آخرین سلول را خواهیم داشت.

در نتیجه می‌بینیم مجموع کل داده‌ها با این سلول برابر است.استفاده‌ کاربردی که از این تابع کرده ایم برای محاسبه Month To Date است یعنی با کمک تابع INDEX جمع داده‌ها از اول سال تا ماهی که مد نظرمان می‌باشد را داشته باشیم.

از این تابع می توانیم استفاده‌های متعدد دیگری داشته باشیم. قبلاً تابعی معرفی شده بود که با استفاده از آن اگر ماه را به صورت میلادی و متنی نوشته باشیم تبدیل به عدد می‌کند.

روش نوشتن تابع مذکور بدین ترتیب بود که بعد از مساوی و درج تابع MONTH، پرانتز باز عدد یک و “&” و سپس با انتخاب سلولی که در آن ماه میلادی را به صورت متنی نوشته‌ایم (N1) شماره ماه را نمایش می دهد که در این مثال برای ماه JUN عدد 6 را به ترتیب زیر نمایش می دهد:

خوب حالا به چه ترتیبی از تابع INDEX استفاده کاربردی کنیم؟ برای این کار می نویسیم مساوی، SUM، پرانتز باز، از کدام سلول؟ از اولین سلول، یعنی اولین داده‌ی اولین ماه (B3) تا جایی که با تابع INDEX مشخص می‌کنیم.

INDEX پرانتز باز. محدوده‌اش کجاست؟ محدوده ما کل سطر اول است (B3:M3)، کاما، سطر چندم؟ چون یک سطر است می‌نویسیم سطر اول کاما، ستون چندم؟ با استفاده از تابع MONTH محتوای سلول N1 که حاوی ماه‌های میلادی است (JUN) را به عدد (6) تبدیل می‌کنیم .

برای اینکه نتیجه این قسمت از فرمول (MONTH) را مشاهده کنیم در هنگام تایپ فرمول تابع MONTH را انتخاب کرده و F9 را می‌زنیم و در این حالت عدد مربوط به ماه میلادی (JUN) را داخل فرمول می بینید.

و در ادامه با استفاده از کلید میانبر Ctrl + Z به حالت قبل بر می گردیم.

بدین ترتیب با استفاده از این فرمول حاصل جمع مقدار مندرج در سلول B3 را تا ششمین ستون در همین ردیف بدست آورده و درج می‌نماییم.حال اگر پرانتز به تعداد کافی بسته نشود و Enter را بزنیم با پیغام زیر روبرو می‌شویم و با انتخاب”Yes” به سیستم اجازه می‌دهیم که فرمول را اصلاح کند.

در نتیجه جمع ماه‌های ژانویه تا ژوئن را در سطر اول را که برابر با 2،853،500 می‌شود را نمایش می‌دهد.

اگر بخواهیم فرمول را برای سلولهای پایین‌تر بسط دهیم حتماً باید سلول دارای ماه میلادی (N1) را با زدن کلید F4 ثابت کنیم و Enter.

حالا می‌توانیم فرمول را به پایین بسط دهیم.

با تغییر سلول N1 و تایپ نام سایر ماه‌های میلادی می‌توانیم نتایج مختلف را مشاهده کنیم که بطور مثال اگر جمع مقادیر از ژانویه تا فوریه را بخواهیم با تایپ آن نتیجه زیر نمایش داده می شود: