تابع Index:آموزش کامل بهمراه مثال های کاربردی و عملی|آکادمي استاد آموز

تابع Index ، در نرم افزار Excel مقدار موجود در شماره سطر و ستون مشخص شده را برمی گرداند .

ساختار تابع Index

 در دو ساختار می توان از تابع Index در اکسل استفاده کرد  در متن زیر دو مورد بررسی می شود .

1. index(array;Row_num;[column_num])=
2. index(reference;Row_num;[Column_num];[area_num])=

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

تابع Index ، بهمراه تابع Match برای جستجوی مقادیر موجود در سلول ها استفاده می شود و محدودیت های تابع جستجوی Vlookup و Hlookup را ندارد . 

براي يادگيري کاربرد پيشرفته تابع Index با ديگر توابع و کاربرد آن در محسبات ليست حقوق و دستمزد مي توانيد از بسته کاربرد اکسل در حسابداري کمک بگيريد.

آرگومان های تابع Index نوع اول

array:

• این آرگومان اجباری است.
• این آرگومان شامل محدوده داده است.

Row_num:

• شماره ردیفی که در محدوده آرگومان اول است را می نویسیم.
• اگر شماره ردیف در محدوده آرگومان اول نباشد خطای! REF# می دهد .
  
• این آرگومان می تواند مقدار صفر یا خالی بگیرد .
  

column_num:

• شامل شماره ستونی که در محدوده آرگومان اول،(array) می باشد.
• اگر شماره ستون در محدوده آرگومان اول نباشد خطای! REF# می دهد .
• این آرگومان اختیاری است و می تواند خالی و یا صفر باشد.

برای مثال : لیستی از نمرات افراد داریم که با تابع Index  می خواهیم مقدار موجود در سطر  سوم و ستون دوم این محدوده را بدست آوریم

روش کار با تابع Index

ابتدا سلولی مانند D7 را انتخاب می کنیم.

عملگر مساوی را تایپ و عبارت Index را می نویسیم .

پرانتز را باز و اطلاعات آرگومان های تابع Index نوع اول را مطابق آرگومان های تابع در متن زیر پر می کنیم .

در آخر از کی بورد روی دکمه اینتر کلیک می کنیم .

array:

• این آرگومان شامل محدوده داده است.
• و این محدوده  از A3 تا C7 می باشد .(شامل ستون نام و نمرات)
• سپس جداکننده “; ” را وارد می کنیم .

Row_num:

• طبق مثال بالا شماره ردیفی مورد جستجو ، ردیف سوم است پس عدد 3 را  در این قسمت می نویسیم  .
• دقت کنید ردیف سوم شیت را تابع برنمی گرداند . ردیف سوم در محدوده A3 تا C7 را تابع جستجو می کند .
• ردیف سوم در این محدوده شامل   مینا است.

column_num:

• طبق مثال بالا شماره ستون 2 را در این قسمت  وارد می کنیم.
• ستون دوم  محدوده A3 تا C7 ،ستون نمره یک است.
  

بعد از فشردن اینتر از کی بورد تابع مقدار 13 را که در ردیف سوم و ستون دوم محدوده A3 تا C7 است را نمایش می دهد  .

درپایان فرمول مثال بالا بصورت زیر می شود .

(INDEX(A3:C7;3;2=

نکته1 :آرگومان دوم یا  سوم تابع Index ، می تواند حذف شود .در صورت حذف آرگومان دوم یا سوم یا هردو آرگومان ،تابع Index باید با توابع دیگر ترکیب شود زیرا یک محدوده برگشت داده می شود نه مقدار یک سلول،اگر با توابع دیگر ترکیب نشود خطای #VALUE برمی گرداند

نکته2 : اگر مقدار آرگومان دوم (Row_num) تابع حذف شود کل مقادیر ستون تعیین شده برگشت داده می شود .

نکته3:اگر مقدار آرگومان سوم (column_num) تابع حذف شود کل مقادیر  ردیف مشخص شده  برگشت داده می شود.

ترکیب تابع Index با توابع دیگر و حل مثال های کاربردی

برای مثال میانگین چهار نمره اول ستون های نمره 1 و نمره 2 را می خواهیم محاسبه کنیم

تابع index را می نویسیم و در قسمت array ، محدوده B4 تا C8 را با درگ انتخاب می کنیم .

در قسمت  Row_column ، بدلیل اینکه 4 نمره اول قرار است انتخاب شود عدد 4 را وارد می کنیم.

و در Column_num ، مقدار خالی یا صفر وارد می کنیم .

در این حالت طبق نکته 3 تمام آدرس خانه های ردیف چهارم که در محدوده آرگومان اول می باشد انتخاب می شوند

یعنی نمرات حسین که شامل 13 و 19 که به ترتیب در آدرس های B7 و C7  هستند خروجی این تابع می شوند.

INDEX(B4:C8;D7;)

حال در نوار فرمول کلیک کرده قبل از تابع Index ، تابع میانگین Average را می نویسیم و پرانتز را باز کرده و آدرس اولین مقدار ستون نمره B4 را می نوسیم سپس علامت : را نوشته و آدرس سلول دوم ازخروجی تابع Index  بدست می آید.

جواب پایانی فرمول فوق 13.90625 می باشدکه از میانگین اعداد زیر بدست آمده

8/(10+15.5+14+13+13.5+12.25+14+19)

فرمول تابع فوق به شکل زیر است .

AVERAGE(B4:INDEX(B4:C8;4;))=

مثال : مجموع دو نمره اول ستون نمره یک را با کمک تابع Sum و Index بدست آورید.

در قسمت array:

• و این محدوده  از B3 تا B7 می باشد .
• سپس جداکننده “; ” را وارد می کنیم .

در قسمت Row_num:

• چون مجموع دو نمره اول را می خواهیم شماره ردیف را عدد 2 می زنیم
  

column_num:

• مقدار آرگومان بعدی را خالی و یا صفر وارد می کنیم تا تمام مقادیر، تا ردیف دوم محدوده آرگومان اول(Array) یعنی اعداد 10 و 15.5 انتخاب شوند .
  
• بعد از فشردن اینتر از کی بورد ،خروجی تابع Index عدد 15.5 می شود که در آدرس B4  قرار دارد .

INDEX(B3:B7;2;)=

در نوار فرمول قبل از تابع Index ، تابع Sum را وارد می کنیم .

درپایان فرمول مثال بالا بصورت زیر می شود و مقدار 25.5 که از جمع دو 10 و 15.5 بدست آمده را برمی گرداند   .

SUM(B4:INDEX(B4:B8;2;))=

مثال : تعداد کل خانه های پر دو ستون اول (نام و نمره یک )را محاسبه نمایید

خانه ای را انتخاب و تابع Index را در آن می نویسیم سپس در هر آرگومان ورودی به ترتیب مقادیر زیر را پر می کنیم .

array:

محدوده داده را از خانه A3 تا C7 انتخاب می کنیم.

Row_num:

مقدار این آرگومان را صفر یا خالی وارد می کنیم .تا بتوانیم تعداد کل ستون های نام و نمره یک را طبق نکات بالا محاسبه کنیم .

Column_num:

چون می خواهیم تعداد کل خانه های پر دو ستون اول محاسبه شود عدد 2 را در این قسمت وارد می کنیم .

خروجی تابع Index، کل مقادیر ستون نام و ستون نمره یک می شود . و شامل مقادیر مهدی ،علی ، مینا، حسین، رضا،10، 15.5 ،14 ،13 و 10   است .

به دلیل اینکه قرار است کل تعداد خانه های پر محاسبه شود نمی توانیم از تابع Count  استفاده کنیم پس تابع CountA را بکار می بریم و قبل از تابع Index می نویسیم .

و با فشردن دکمه اینتر از کی بورد تابع مقدار 10  را که شامل خانه های پر ستون نام و نمره 1 است را نمایش می دهد.

COUNTA(A3:INDEX(A3:C7;0;2))=

نکته 4 :اگر  محدوده انتخابی تابع Index ، شامل فقط یک سطر یا یک ستون بود می توانیم شماره سطر یا ستون را ننویسیم

برای مثال در شکل بالا اگر محدوده (array) از خانه A3 تا C3 ( فقط شامل یک ردیف باشد )می توانیم آرگومان دوم که شامل شماره ردیف است ننویسیم.

و با نوشتن عدد 3 برای آرگومان سوم ، مقدار موجود در ستون سوم این محدوده که عدد 13.5 می باشد نمایش داده می شود.

INDEX(A3:C3;0;3)=

و اگر محدوده آرگومان اول تابع (Array)، از خانه A3 تا A7 (فقط شامل یک ستون )باشد می توانیم آرگومان سوم (Column_num)، را حذف کنیم و مقدار موجود در سطر چهارم محدوده آرگومان اول (َArray)که نام حسین می باشد را نمایش می دهد.

INDEX(A3:A7;4;0)=

بدست آوردن xامین عدد در یک لیست

برای مثال 4امین نمره بالا از بین نمرات ستون نمره یک را محاسبه نماییم.

ابتدا ستون نمره را از تب Home گروه Editing از گزینه Sort&Filter زیر منوی  Sort largest to smallest بصورت صعودی مرتب می کنیم .

مقدار آرگومان اول (َArray):کل ستون نمره از خانه B3 تا B7 می باشد .

مقدار Row_num: چون چهارمین نمره بالا را نیاز داریم پس عدد 4 را وارد می کنیم .

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

SUM(B3:INDEX(B3:B7;4;0))=

ساختار تابع Index از نوع دوم

وقتی از تابع Index نوع دوم استفاده می کنیم که چندین محدوده داده داشته باشیم و از بین این محدوده ها امکان انتخاب یک محدوده را داشته باشیم .

1. index(reference;Row_num;[Column_num];[area_num])=

آرگومان های تابع Index نوع دوم

reference:

بازه مربوط به محدوده های مختلف تابع Index  نوع دوم  است.

در این نوع تابع Index می توانیم چندین محدوده را انتخاب کنیم .

Row_num:

شماره ردیف در محدوده ای که انتخاب کردیم می باشد.

Column_num:

شماره ستون در محدوده انتخابی است .

Area_num:

در این نوع تابع Index می توانیم چندین محدوده را انتخاب کنیم .

شماره محدوده  که میخواهیم مقدار سطر و ستون آنرا برگردانیم را می نویسیم.

مثال از تابع Index نوع دوم :لیستی از نمرات  دانش آموزان در 3 کلاس مختلف داریم و می خواهیم با دادن شماره سطر و ستون محدوده و شماره محدوده ، مقدار سلول نماش داده شود.

کار با تابع Index نوع دوم

• در خانه های شماره های ردیف ، شماره ستون ، شماره محدوده به ترتیب مقادیر4 و 3 و 3 وارد می کنیم .
• در سلول A14 ، کلیک می کنیم نام تابع Index ، را تایپ می کنیم .
• پرانتز را باز می کنیم .
• چون سه  محدوده داریم ، پرانتز دیگری باز می کنیم و آدرس سه محدوده را در داخل پرانتز می نویسم .
• محدوده اول مربوط به کلاس 101 از خانه A4 تا C8 می باشد.سپس علامت جداکننده “;” را می نویسیم .
• محدوده دوم ، مربوط به کلاس 102 از خانه F4 تا H8 می باشد. علامت جداکننده “;” را می نویسیم .
• محدوده سوم ، کلاس 103 از خانه D13 تا F17 است .
• پرانتز را بسته

در قسمت Row_num:

1. آدرس سلول A11 را که شمار ردیف در آن درج شده را انتخاب می کنیم .

در قسمت column_num:

1. آدرس سلول  A12 که شماره ستون مورد جستجو است را انتخاب می کنیم .

در قسمت Area_num:

• از بین سه محدوده ، محدوده سوم که در آدرس خانه A13 قرار گرفته است را انتخاب می کنیم.
• در پایان پرانتز مربوط تابع index را می بندیم و دکمه Enter را می فشاریم
• و تابع مقدار 14.5 که مربوط به محدوده سوم ( کلاس103 ) که در  ردیف چهارم و در ستون سوم قرار دارد را برمی گرداند .
  

در آخر فرمول مثال فوق بشکل زیر می شود.

INDEX((A4:C8;F4:H8;D13:F17);A11;A12;A13)=