~M*E*H*D*I~
2013/10/06, 09:59
7 دلیل برای استفاده از فرمول 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)
در این ترکیب شما به دادن شماره سطر و ستون به یک مقدار یا رفرنس در یک محدوده خاص دست خواهید یافت
در این مثال ما از داده هایی به شرح جدول زیر با نام List استفاده خواهیم کرد
http://chakane.persiangig.com/image/1.png
دلیل اول : بدست آوردن n امین داده
این فرمول بهترین و ساده ترین فرمول برای این کار است کافیست بنویسیم:
=index(list;n)
دلیل دوم : بدست آوردن مقدار حاصل از تقاطع یم ردیف ویک ستون با دادن شماره های ردیف و ستون
در صورتی که بخواهید به داده ای که n امین ردیف و m امین ستون قرار دارد برسید کافیست فرمول زیر را بنویسید:
=index(list;n;m)
دلیل سوم: دریافت کل یا ستونی از یک جدول
گاهی شما می خواهید اطلاعاتی از کل یک جدول یا ستونی از یک جدول بدست آورید مثلا متوسط سن افراد شما می توانید از فرمول زیر استفاده کنید:
=average(age column)
همچنین می توانید از فرمول زیر نیز استفاده کنید سن افراد در ستون 5 جدول قرار دارد می توان نوشت:
=average(index(list; ;5))
نکته: میتوان بجای ردیف از جای خالی یا 0 استفاده کنید همچنین در مورد ستون هم می توان بکار برد.
دلیل چهارم: جستجو به سمت چپ
می دانیم که استفاده از فرمول vlookup امکان جستجو در خلاف جهت را به ما نمی دهد اما با ترکیب فرمول های index&match می توان به این مشکل فائق آمد ، در مثال فوق می خواهیم بدانیم کدام فرد سنگین ترین وزن را دارد.
=INDEX(List[F-Name];MATCH(MAX(List[weight]);List[weight];0);1)
دلیل پنجم: ایجاد محدوده داینامیک
تا کنون با برخی از توانایی های تابع 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 استفاده مکنیم
http://chakane.persiangig.com/image/10-6-2013%2002-21-49%20%D8%A8.jpg
=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))
همانطور که دیدید تابع 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)
در این ترکیب شما به دادن شماره سطر و ستون به یک مقدار یا رفرنس در یک محدوده خاص دست خواهید یافت
در این مثال ما از داده هایی به شرح جدول زیر با نام List استفاده خواهیم کرد
http://chakane.persiangig.com/image/1.png
دلیل اول : بدست آوردن n امین داده
این فرمول بهترین و ساده ترین فرمول برای این کار است کافیست بنویسیم:
=index(list;n)
دلیل دوم : بدست آوردن مقدار حاصل از تقاطع یم ردیف ویک ستون با دادن شماره های ردیف و ستون
در صورتی که بخواهید به داده ای که n امین ردیف و m امین ستون قرار دارد برسید کافیست فرمول زیر را بنویسید:
=index(list;n;m)
دلیل سوم: دریافت کل یا ستونی از یک جدول
گاهی شما می خواهید اطلاعاتی از کل یک جدول یا ستونی از یک جدول بدست آورید مثلا متوسط سن افراد شما می توانید از فرمول زیر استفاده کنید:
=average(age column)
همچنین می توانید از فرمول زیر نیز استفاده کنید سن افراد در ستون 5 جدول قرار دارد می توان نوشت:
=average(index(list; ;5))
نکته: میتوان بجای ردیف از جای خالی یا 0 استفاده کنید همچنین در مورد ستون هم می توان بکار برد.
دلیل چهارم: جستجو به سمت چپ
می دانیم که استفاده از فرمول vlookup امکان جستجو در خلاف جهت را به ما نمی دهد اما با ترکیب فرمول های index&match می توان به این مشکل فائق آمد ، در مثال فوق می خواهیم بدانیم کدام فرد سنگین ترین وزن را دارد.
=INDEX(List[F-Name];MATCH(MAX(List[weight]);List[weight];0);1)
دلیل پنجم: ایجاد محدوده داینامیک
تا کنون با برخی از توانایی های تابع 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 استفاده مکنیم
http://chakane.persiangig.com/image/10-6-2013%2002-21-49%20%D8%A8.jpg
=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))
همانطور که دیدید تابع index کاربرد های متفاوت و سودمندی دارد ، ممکن است شما از این تابع به نحو دیگری استفاده کرده باشید که برای غنای این مطلب لطفا مطرح کنید.