تابع INDEX اکسل | پیدا کردن داده ها در یک جدول با استفاده از شماره سطر و ستون

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

پیش نیازهای آموزشی این بخش
چگونه در یک سلول اکسل فرمول بنویسم؟
تکمیل فرمول ها و آرگومان توابع با استفاده از ماوس

اشکال مختلف تابع INDEX

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

  • شکل آرایه (Array form) : در این شکل تابع یک مقدار را از عناصر یک جدول، آرایه یا محدوده برمی گرداند.
  • شکل مرجع (Reference form) : در این شکل تابع یک مرجع یا ارجاع سلولی را از یک جدول، آرایه یا محدوده برمی گرداند.

شکل آرایه تابع INDEX اکسل

ورودی (آرگومان) ها

آرگومان های تابع INDEX اکسل در شکل آرایه در پنجره Function Arguments این تابع در تصویر زیر نمایش داده شده است.

ورودی های تابع INDEX اکسل در شکل آرایه

تابع 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 را در شکل آرایه بررسی می نماییم.

داده ها

شماره ردیف اکسلAB
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 اکسل در شکل مرجع

تابع 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 به ترتیب اختیاری خواهند بود.
  • 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 در وب سایت مایکروسافت درباره اختیاری بودن آرگومان Column_num در حالت مرجع تابع داده شده است، هنگام در بکارگیری تابع اگر یکی از آرگومان های Row_num یا Column_num را خالی بگذارید تابع نتیجه درستی را نمایش نمی دهد. برای رفع این مشکل آرگومان خالی را مقدار صفر قرار دهید.
بعنوان مثال فرمول =SUM(INDEX(A1:C2,1)) خطای #REF! را ایجاد می نماید، اما اگر این فرمول را بصورت =SUM(INDEX(A1:C2,1,0)) بنویسید مشکل حل و مقادیر محدوده A1 تا C1 جمع زده خواهند شد.

مثال

در این مثال کارکرد تابع INDEX را در شکل مرجع بررسی می نماییم.

داده ها

ABC
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

مطالب تصادفی

دیدگاهتان را بنویسید