استفاده از فرمول indexدر اکسل
در بین فرمول های موجود در اکسل فرمول index به واقع یکی از 5 فرمول برتر آن است، این فرمول همه کاره، قوی و هوشمند است گرچه در ظاهر ساده به نظر می رسد. این فرمول قادر است تغییر عمده ای در روش آنالیز داده ها و محاسبه اعداد به وجود آورد.
مفهوم فرمول:
به بیان ساده فرمول index فرمولی است که به شما ارزش یا رفرنس یک داده را در یک جدول یا محدوده باز می گرداند شاید در ظاهر این موضوع جزئی به نظر برسد اما اگر به طور کامل با کاربرد های آن آشنا شوید شگفت زده خواهید شد.
چند کابرد ساده از فرمول در اکسل:
فرض کنید شما لیستی 8 تایی از اسامی دارید حال میخواهید بدانید هشتمین آیتم از این لیست چه نامی است، کافی است فرمول زیر را بنویسید
=INDE
list, 8)
فرض کنید در ستون سوم از این لیست شماره تلفن وارد شده باشد می خواهید شماره تلفن هشتمین نفر از لیست را بدانید
=INDE list, 8,3)
ترکیب های مختلف فرمول indexدر اکسل
فرمول index با دو ترکیب بکار می رود
INDE range or table, row number, column number)
در این ترکیب با دادن شماره سطر و ستون یک ناحیه می توان به رفرنس یا مقدار دست یافت
INDE 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 می توان به این مشکل فائق آمد، در مثال فوق می خواهیم بدانیم کدام فرد سنگین ترین وزن را دارد.
=INDE List[F-Name];MATCH(MA 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:INDE 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(INDE (list1;list2;list3);;;D2))
(list1;list2;list3) نشان دهنده کل محدوه ها و d2 نشانگر شماره محدوده مورد نظر است.
دلیل هفتم: تابع index می تواند آرایه ها را پردازش کند
ماهیت تابع index به گونه ای است که بدون استفاده از CTRL+SHIFT+ENTER داده های آرایه ای را پردازش کند، به عنوان مثال شما می توانید متوسط سنی افرادی که نام آنها با حرف F شروع شده بیایبد
=SUM(INDE ((LEFT(List[F-Name];1))="F")*(List[Age]);0))
مفهوم فرمول:
به بیان ساده فرمول index فرمولی است که به شما ارزش یا رفرنس یک داده را در یک جدول یا محدوده باز می گرداند شاید در ظاهر این موضوع جزئی به نظر برسد اما اگر به طور کامل با کاربرد های آن آشنا شوید شگفت زده خواهید شد.
چند کابرد ساده از فرمول در اکسل:
فرض کنید شما لیستی 8 تایی از اسامی دارید حال میخواهید بدانید هشتمین آیتم از این لیست چه نامی است، کافی است فرمول زیر را بنویسید
=INDE
list, 8)فرض کنید در ستون سوم از این لیست شماره تلفن وارد شده باشد می خواهید شماره تلفن هشتمین نفر از لیست را بدانید
=INDE
list, 8,3)ترکیب های مختلف فرمول indexدر اکسل
فرمول index با دو ترکیب بکار می رود
INDE
range or table, row number, column number)در این ترکیب با دادن شماره سطر و ستون یک ناحیه می توان به رفرنس یا مقدار دست یافت
INDE
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 می توان به این مشکل فائق آمد، در مثال فوق می خواهیم بدانیم کدام فرد سنگین ترین وزن را دارد.
=INDE
List[F-Name];MATCH(MA 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:INDE
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(INDE
(list1;list2;list3);;;D2))(list1;list2;list3) نشان دهنده کل محدوه ها و d2 نشانگر شماره محدوده مورد نظر است.
دلیل هفتم: تابع index می تواند آرایه ها را پردازش کند
ماهیت تابع index به گونه ای است که بدون استفاده از CTRL+SHIFT+ENTER داده های آرایه ای را پردازش کند، به عنوان مثال شما می توانید متوسط سنی افرادی که نام آنها با حرف F شروع شده بیایبد
=SUM(INDE
((LEFT(List[F-Name];1))="F")*(List[Age]);0))
- لینک منبع
تاریخ: شنبه , 04 آذر 1402 (19:26)
- گزارش تخلف مطلب