تابع INDEX یکی از توابع جستجو و مرجع (Lookup & Reference) اکسل می باشد. در این آموزش پیشرفته اکسل با قسمت های تشکیل دهنده و کاربرد این تابع آشنا می شویم.
فهرست مطالب
اشکال مختلف تابع INDEX
تابع INDEX یک مقدار یا یک مرجع به یک مقدار را که در یک جدول یا محدوده قرار گرفته است برمی گرداند. دو شکل مختلف برای استفاده از تابع INDEX وجود دارد:
- شکل آرایه (Array form) : در این شکل تابع یک مقدار را از عناصر یک جدول، آرایه یا محدوده برمی گرداند.
- شکل مرجع (Reference form) : در این شکل تابع یک مرجع یا ارجاع سلولی را از یک جدول، آرایه یا محدوده برمی گرداند.
شکل آرایه تابع INDEX اکسل
ورودی (آرگومان) ها
آرگومان های تابع INDEX اکسل در شکل آرایه در پنجره Function Arguments این تابع در تصویر زیر نمایش داده شده است.
تابع INDEX در شکل آرایه سه آرگومان به نام های Row_num، Array و Column_num دارد.
کارکرد
کارکرد تابع INDEX در شکل آرایه با توجه به موقعیت قرار گرفتن یک مقدار در یک جدول یا محدوده و یا یک آرایه ثابت آن را انتخاب می نماید.
INDEX در انگلیسی به معنای شاخص می باشد. تابع INDEX با استفاده از شاخص های سطر و ستون یک آرایه (که می تواند نام یک جدول یا یک محدوده نیز باشد) یکی از عناصر آرایه را برمی گرداند.
شکل فرمولی
=INDEX(Array, Row_num, [Column_num])
- Array : اجباری؛ یک محدوده سلولی یا یک آرایه ثابت.
- اگر Array فقط یک سطر یا یک ستون داشته باشد، آرگومان Row_num یا Column_num متناظر اختیاری خواهد بود.
- اگر Array بیشتر از یک سطر و یک ستون داشته باشد و فقط Row_num یا Column_num استفاده شده باشد، تابع فقط یک آرایه شامل کل سطر یا ستون Array را برمی گرداند.
- Row_num : اجباری، مگر اینکه Column_num بکار گرفته شود؛ شماره ردیفی از Array است که مقدار انتخابی در آن ردیف قرار دارد. اگر نادیده گرفته شود تکمیل Column_num اجباری می شود.
- Column_num : اختیاری؛ شماره ستونی از Array است که مقدار انتخابی در آن ستون قرار دارد. اگر نادیده گرفته شود تکمیل Row_num اجباری می شود.
ملاحظات
- اگر هر دو آرگومان Row_num و Column_num استفاده شوند، تابع INDEX مقدار سلولی که در محل تقاطع Row_num و Column_num قرار دارد را بر می گرداند.
- Row_num و Column_num باید به یک سلول که درون Array قرار می گیرد، اشاره داشته باشند؛ در غیر اینصورت تابع خطای
#REF!را برمی گرداند. - اگر مقدار Row_num یا Column_num را صفر قرار دهید، تابع INDEX یک آرایه از تمام مقادیر به ترتیب ستون یا ردیف را ایجاد می نماید.
- در مواردی که مقدار بازگشتی تابع یک آرایه باشد، اکسل آرایه را نمی تواند در سلول نمایش دهد و خطای
#VALUE!را نمایش خواهد داد. در این موارد برای نمایش مقدار بازگشتی در سلول باید تابع را بصورت فرمول آرایه در سلول وارد نمایید.
مثال
در این مثال کارکرد تابع INDEX را در شکل آرایه بررسی می نماییم.
داده ها
| شماره ردیف اکسل | A | B |
|---|---|---|
| 3 | سیب | لیمو |
| 4 | موز | هلو |
فرمول ها
| فرمول | توضیح | نتیجه |
|---|---|---|
=INDEX(A3:B4,2,2) | مقدار سلولی را که در تقاطع سطر 2 و ستون 2 محدوده A2:B3 قرار گرفته است را برمی گرداند. | هلو |
=INDEX(A3:B4,2,1) | مقدار سلولی را که در تقاطع سطر 2 و ستون 1 محدوده A2:B3 قرار گرفته است را برمی گرداند. | موز |
=INDEX({1,2;3,4},0,2) | داده های ستون دوم آرایه ثابت {1,2;3,4} را برمی گرداند. مقدار بازگشتی تابع آرایه {2;4} می باشد و فقط عنصر اول آرایه در سلول نمایش داده می شود. | 2 |
=SUM(INDEX({1,2;3,4},0,2)) | برای آزمایش داده های مثال قبل آن را در یک تابع SUM قرار دادیم. | 6 |
شکل مرجع تابع INDEX اکسل
ورودی (آرگومان) ها
آرگومان های تابع INDEX اکسل در پنجره Function Arguments این تابع در تصویر زیر نمایش داده شده است.
تابع INDEX در شکل مرجع چهار آرگومان به نام های Row_num، Reference، Column_num و Area_num دارد.
کارکرد
کارکرد تابع INDEX در شکل مرجع شبیه حالت آرایه می باشد و فقط دو تفاوت دارد که یک، مقدار بازگشتی تابع در این حالت یک مرجع سلولی است و می تواند بعنوان بخشی از آرگومان سایر توابع بکار رود و دو اینکه در حالت مرجع می توانید از بین چند محدوده سلولی غیر مجاور یکی را برای انتخاب سلول نهایی از آن انتخاب کنید.
شکل فرمولی
=INDEX(Reference, Row_num, [Column_num], [Area_num])
- Reference : اجباری؛ یک محدوده سلولی است که می تواند یک یا چند سلول داشته باشد.
- اگر برای Reference یک یا چند محدوده غیرمجاور وارد می نمایید باید Reference را داخل پرانتز قرار دهید. (بعنوان مثال
(A1:C2, D3:E4)). در این حالت همه محدوده ها باید در یک صفحه () واقع شده باشند و در غیر اینصورت خطای#VALUE!رخ خواهد داد. - اگر هر یک از محدوده های Reference فقط یک سطر یا یک ستون داشته باشد، آرگومان Row_num یا Column_num به ترتیب اختیاری خواهند بود.
- اگر برای Reference یک یا چند محدوده غیرمجاور وارد می نمایید باید Reference را داخل پرانتز قرار دهید. (بعنوان مثال
- Row_num : اجباری؛ شماره ردیفی از Reference که مرجع انتخابی در آن ردیف قرار دارد.
- Column_num : اختیاری؛ شماره ستونی از Reference که مرجع انتخابی در آن ستون قرار دارد.
- Area_num : اختیاری؛ وقتی که در Reference چند محدوده وارد شده باشد، یکی از آنها را انتخاب می نماید. اولین محدوده 1، دومی 2 و به همین ترتیب. اگر نادیده گرفته شود تابع از محدوده اول استفاده می کند.
بعنوان مثال اگر Reference شامل محدوده های(A1:B4,D1:E4,G1:H4)باشد، Area_num اگر 1 باشد محدوده A1:B4، اگر 2 باشد محدوده D1:E4 و اگر 3 باشد محدوده G1:H4 انتخاب خواهد شد.
ملاحظات
- آرگومان های Reference و Area_num یک محدوده خاص را انتخاب می نمایند و وقتی که هر دو آرگومان Row_num و Column_num استفاده شوند، این دو آرگومان یک سلول را از آن محدوده انتخاب می کنند. تابع INDEX سلولی را که در محل تقاطع Row_num و Column_num قرار دارد بر می گرداند.
- Row_num و Column_num باید به یک سلول که درون Array قرار می گیرد، اشاره داشته باشند؛ در غیر اینصورت تابع خطای
#REF!را برمی گرداند. اگر هر دو آرگومان Row_num و Column_num نادیده گرفته شوند، تابع کل محدوده ای را که توسط Area_num تعیین شده باشد برمی گرداند. در این حالت تکمیل Area_num اجباری می باشد. - اگر مقدار Row_num یا Column_num را صفر قرار دهید، تابع INDEX یک مرجع سلولی از تمام سلول های به ترتیب ستون یا ردیف ایجاد می نماید.
- نتیجه تابع INDEX یک مرجع است و توسط سایر فرمول ها اینگونه تفسیر می شود. بسته به نوع فرمول، مقدار بازگشتی تابع INDEX ممکن است بعنوان یک مقدار یا یک مرجع بکار گرفته شود.
برای مثال فرمولCELL("width",INDEX(A1:B2,1,2))معادل فرمولCELL("width",B1)می باشد. تابع CELL مقدار بازگشتی تابع INDEX را بعنوان یک مرجع استفاده می نماید. از سوی دیگر فرمول2*INDEX(A1:B2,1,2)مقدار بازگشتی تابع INDEX را به عددی که در سلول B1 قرار گرفته است ترجمه می کند.
مثال
در این مثال کارکرد تابع INDEX را در شکل مرجع بررسی می نماییم.
داده ها
| A | B | C | |
|---|---|---|---|
| 1 | میوه | قیمت | تعداد |
| 2 | سیب | 0.69 | 40 |
| 3 | موز | 0.34 | 38 |
| 4 | لیمو | 0.55 | 15 |
| 5 | پرتقال | 0.25 | 25 |
| 6 | گلابی | 0.59 | 40 |
| 7 | بادام | 2.80 | 10 |
| 8 | بادام هندی | 3.55 | 16 |
| 9 | بادام زمینی | 1.25 | 20 |
| 10 | گردو | 1.75 | 12 |
فرمول ها
| فرمول | توضیحات | نتیجه |
|---|---|---|
=INDEX(A2:C6, 2, 3) | سلول C3 را که در تقاطع سطر دوم و ستون سوم محدوده A2:C6 قرار دارد را برمی گرداند. | 38 |
=INDEX((A1:C6, A8:C10), 2, 2, 2) | سلول B9 را که در تقاطع سطر دوم و ستون دوم محدوده دوم یعنی A8:C10 قرار دارد را برمی گرداند. | 1.25 |
=SUM(INDEX(A2:C10, 0, 3, 1)) | تابع INDEX یک مرجع سلولی از ستون سوم محدوده A2:C10 یعنی C2:C10 را برمی گرداند و فرمول نهایی SUM(C2:C10) می شود. | 216 |
=SUM(B2:INDEX(A2:C6, 5, 2)) | در این فرمول شکل مرجع تابع INDEX را برای تکمیل آرگومان تابع SUM استفاده کرده ایم. مقدار بازگشتی تابع INDEX سلول سطر پنجم و ستون دوم محدوده A2:C6 یعنی سلول B6 می باشد. بنابراین فرمول نهایی SUM(B2:B6) می شود. | 2.42 |
مطالب تصادفی
بیشتر بخوانید
تابع Concat اکسل | جمع کردن کلمات و رشته ها در اکسل
تابع CODE اکسل | پیدا کردن کد یک نویسه (کاراکتر) در اکسل
تابع CHAR اکسل | تبدیل کد عددی نویسه (کاراکتر کد) به نویسه در اکسل
تابع ADDRESS اکسل | تبدیل شماره سطر و ستون یک سلول به آدرس در اکسل
تابع INDEX اکسل | پیدا کردن داده ها در یک جدول با استفاده از شماره سطر و ستون