استفاده از فرمول indexدر اكسل

در بين فرمول هاي موجود در اكسل فرمول index به واقع يكي از 5 فرمول برتر آن است ، اين فرمول همه كاره ، قوي و هوشمند است گرچه در ظاهر ساده به نظر مي رسد. اين فرمول قادر است تغيير عمده اي در روش آناليز داده ها و محاسبه اعداد به وجود آورد.

مفهوم فرمول:
به بيان ساده فرمول index فرمولي است كه به شما ارزش يا رفرنس يك داده را در يك جدول يا محدوده باز مي گرداند شايد در ظاهر اين موضوع جزئي به نظر برسد اما اگر به طور كامل با كاربرد هاي آن آشنا شويد شگفت زده خواهيد شد.

چند كابرد ساده از فرمول در اكسل :
فرض كنيد شما ليستي 8 تايي از اسامي داريد حال ميخواهيد بدانيد هشتمين آيتم از اين ليست چه نامي است ، كافي است فرمول زير را بنويسيد

=INDEX(list, 8)
فرض كنيد در ستون سوم از اين ليست شماره تلفن وارد شده باشد مي خواهيد شماره تلفن هشتمين نفر از ليست را بدانيد
=INDEX(list, 8,3)
تركيب هاي مختلف فرمول indexدر  اكسل
فرمول index با دو تركيب بكار مي رود
INDEX(range or table, row number, column number)
در اين تركيب با دادن شماره سطر و ستون يك ناحيه مي توان به رفرنس يا مقدار دست يافت
INDEX(range, row number, column number, area number)
در اين تركيب شما به دادن شماره سطر و ستون به يك مقدار يا رفرنس در يك محدوده خاص دست خواهيد يافت
دليل اول : بدست آوردن n امين داده
اين فرمول بهترين و ساده ترين فرمول براي اين كار است كافيست بنويسيم:

=index(list;n)

دليل دوم : بدست آوردن مقدار حاصل از تقاطع يم رديف ويك ستون با دادن شماره هاي رديف و ستون
در صورتي كه بخواهيد به داده اي كه n امين رديف و m امين ستون قرار دارد برسيد كافيست فرمول زير را بنويسيد:

=index(list;n;m)
دليل سوم: دريافت كل يا ستوني از يك جدول در  excel
گاهي شما مي خواهيد اطلاعاتي از كل يك جدول يا ستوني از يك جدول بدست آوريد مثلا متوسط سن افراد شما مي توانيد از فرمول زير استفاده كنيد:
=average(age column)
همچنين مي توانيد از فرمول زير نيز استفاده كنيد سن افراد در ستون 5 جدول قرار دارد مي توان نوشت:


=average(index(list; ;5))


نكته: ميتوان بجاي رديف از جاي خالي يا 0 استفاده كنيد همچنين در مورد ستون هم مي توان بكار برد.

دليل چهارم: جستجو به سمت چپ
مي دانيم كه استفاده از فرمول vlookup امكان جستجو در خلاف جهت را به ما نمي دهد اما با تركيب فرمول هاي index&match مي توان به اين مشكل فائق آمد ، در مثال فوق مي خواهيم بدانيم كدام فرد سنگين ترين وزن را دارد.
=INDEX(List[F-Name];MATCH(MAX(List[weight]);List[weight];0);1)

دليل پنجم: ايجاد محدوده دايناميك در Excel
تا كنون با برخي از توانايي هاي تابع index آشنا شده ايد كه به نظر ساده بوده است ، حال به برخي كاربرد ها پيچيده تر اين تابع مي پردازيم ، توانايي واقعي index در ماهيت آن نهفته است شما در ظاهر مي بينيد كه index مقداري را نمايش مي دهد اما در واقع اين تابع شما را به سلولي كه حاوي آن مقدار است ارجاع مي دهد به عنوان مثال وقتي مي نويسد index(list;8) شما 8 امين مقدار از ليست را مي بينيد اما در واقع شما به سلول حاوي هشتمين مقدار ارجاع داده شده ايد پس حاصل اين تابع يك ارجاع است و هرگاه شما نياز به ارجاعي داشته باشيد مي توانيد از تابع استفاده كنيد .
كمي گيج كننده است ، با مثال هايي به شرح موضوع مي پردازيم :
شما وقتي مي خواهيد ناحيه اي مانند A1:A10 را جمع بزنيد از sum(A1:A10) استفاده ميكنيد در اين فرمول a1 , a10 رفرنس هستند حال به اين فرمول توجه كنيد :
=sum(A1:index(A1:A50;10))

جواب ها در هر دو يكسان است اما در دومي index از محدوده a1 تا a50 شما را به 10 خانه اول ارجاع مي دهد.
مثال1 : متوسط قد X نفر اول :
فرض كنيد مي خواهيد متوسط قد x نفر اول افراد ليست را بيابيد ، x عددي متغير است در اين صورت فرمول زير را خواهيم داشت:

=AVERAGEA(G4:INDEX(List[length];K7))

مثال 2 : آموزش اكسل متوسط قد افراد در يك ليست دايناميك
گاهي شما ميخواهيد متوسط قد همه افراد ليست را داشته باشيد اما نميدانيد اين افراد چند نفر هستند در اين صورت هر بار كه داده اي اضافه مي شود بايد فرمول ها را بروز كنيد اما چگونه مي توان از فرمول هاي خودكار استفاده كرد، يكي از راه ها استفاده از فرمول offset است :

=offset(A1;0;0;counta(A:A);1)
اما به جاي فرمول فوق مي توان از فرمول زير هم استفاده كرد:

=A1:index(A:A;counta(A:A))
دليل ششم : ارجاع به محدوده خاصي از چندين محدوده
سه ليست يا محدوده مجزا داريد list1,list2,list3 ميخواهيم متوسط داده هاي اين سه ليست را بدست آوريم لذا از تركيب دوم تايع index استفاده مكنيم

=AVERAGE(INDEX((list1;list2;list3);;;D2))
(list1;list2;list3) نشان دهنده كل محدوه ها و d2 نشانگر شماره محدوده مورد نظر است.

دليل هفتم : تابع index مي تواند آرايه ها را پردازش كند
ماهيت تابع index به گونه اي است كه بدون استفاده از CTRL+SHIFT+ENTER داده هاي آرايه اي را پردازش كند ، به عنوان مثال شما مي توانيد متوسط سني افرادي كه نام آنها با حرف F شروع شده بيايبد

=SUM(INDEX(((LEFT(List[F-Name];1))="F")*(List[Age]);0))