۶ کاربرد فوق العاده از تابع Index

چند کاربرد از تابع Index

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

قبل از مطالعه این ۶ کاربرد، نحوه کار با تابع Index و آشنایی با آرگومان های این تابع رو در مقاله ” Index تابع قدرتمند اکسل” رو مطالعه کنید.

کاربرد تابع Index: فراخوانی N امین سلول در یک محدوده

اولین و ساده ترین حالت کاربرد تابع Index این هست که برای فراخوانی Nامین داده از یک محدوده استفاده میشه. فرض کنید یک کمبوباکس داریم مطابق شکل ۱. همونطور که میدونیم خروجی لیست فروریز (کمبوباکس) یک عدد هست که در واقع میگه چندمین داده از لیست نتخاب شده. حالا برای اینکه با هر انتخاب، عنوان آیتم انتخاب شده رو فراخوانی کنیم (یعنی با انتخاب تابستان از لیست، بجای عدد ۲، مقدار تابستان رو در یک سلول داشته باشیم)، میتونیم از تابع Index استفاده کنیم.

=Index (A2:A5 ; A1 )

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

کاربرد تابع Index: فراخوانی همه داده ها در یک ردیف یا ستون

تابع Index علاوه بر اینکه یک سلول حاصل تقاطع یک شماره ردیف و ستون رو بر میگردونه، میتونه عملیاتی رو روی یک ردیف یا یک ستون مشخص هم انجام بده. به شکل ۲ توجه کنید. وقتی آرگومان Row_Num رو خالی یا ۰ میذاریم، چون ردیفی رو مشخص نمیکنیم، پس همه ردیف ها (یعنی یک ستون) در نظر گرفته میشه. در واقع داریم به فرمول میگیم که از محدوده 6B1:C1 کل ستون اول رو در نظر بگیر و میانگین روی این محدوده حساب کن.

شکل ۲- کاربرد تابع Index -تعیین محدوده با استفاده از تابع Index

در مورد آرگومان Column_Num هم همین اصل برقرار هست. یعنی اگر این آرگومان رو ۰ یا خالی بذاریم، کل ردیف تعیین شده در نظر گرفته میشه.

در واقع بجای اینکه مستقیم بنویسیم  Average(B2:B16) این محدوده رو با تابع Index تعیین کردیم. این نوع تعیین محدوده به دلیل انعطاف پذیری بالا، در ترکیب فرمول ها و تهیه نرم افزارها کاربرد زیادی دارن.

کاربرد تابع Index: ترکیب Index با سایر توابع

با توجه به مثال های قبلی، برداشتمون اینه که تابع Index محتوای سلول رو به ما میده. اما واقعیت اینه که این تابع به یک Reference اشاره میکنه که داخلش مقدار وجود داره. در این مثال ماهیت واقعی تابع Index رو میبینیم و با توجه به Reference بودن خروجی تابع Index، میتونیم ازش در تعیین محدوده های پویا استفاده کنیم.

مثال اول: فرض کنید میخواهیم میانگین یک محدوده رو مطابق Average(A1:A10) حساب کنیم. این محدوده رو میتونیم به این شکل هم تعیین کنیم:

=Average ( A1 : Index  (A1:A10,10) )

در ادامه به ارائه کاربرد این روش استفاده از Index می پردازیم:

فرض کنید میخواهیم میانگین وزن پنج نفر اول رو حساب کنیم. برای این کار اول ستون وزن رو از بزرگ به کوچک Sort میکنیم. سپس فرمول زیر رو مینویسیم. (مطابق شکل ۳)

=Average ( C2 : Index (C2:C16 ; E1) )

شکل ۳- کاربرد تابع Index -ایجاد محدوده انعطاف پذیر با تابع Index

مثال دوم: فرض کنید میخوایم یک محدوده مشخص از بین داده ها رو انتخاب کنیم و جمع محاسبه کنیم. برای این مسئله هم از ترفند بالا استفاده میکنیم و محدوده مورد نظر رو میسازیم. در واقع ما با استفاده از تابع Index ابتدا و انتهای بازه رو میسازیم. سپس با استفاده از : بازه رو تشکیل میدیم.

=Sum ( Index (B2:B13;E2) : Index (B2:B13;E3) )

شکل ۴- کاربرد تابع Index -ایجاد محدوده انعطاف پذیر با تابع Index

همونطور که در تصویر زیر میبینید، تابع Index ابتدا و انتهای بازه رو میسازه و در نهایت فرمول به Sum(B3:B9) تبدیل میشه

کاربرد تابع: محدوده پویا و داینامیک با Index

ایجاد محدوده های داینامیک و پویا از سری مباحث بسیار پر اهمیت در فرمول نویسی حرفه ای، داشبورد، ایجاد نمودارهای داینامیک و … هست. یکی از راه های ایجاد محدوده پویا تابع Offset هست که در مقاله نمودارهای پویا به آن پرداختیم. در این مقاله میخوایم با استفاده از تابع Index این کار رو انجام بدیم. فرض کنید لیستی از محصولات داریم که هر بار مواردی حذف/اضافه میشه. میخوایم لیستی داشته باشیم از نام محصولات که مطابق با تغییرات، محتوای اون لیست هم تغییر کنه. برای این کار مطابق شکل ۵ یک Name ایجاد میکنیم و فرمول زیر رو به اون نام اختصاص میدیم.

=A2 : Index ( A:A ; Counta (A:A) )

شکل ۵- کاربرد تابع Index -فرمول نویسی در Name Manger

 سپس نام مورد نظر رو در Data Validation قسمت List وارد میکنیم (شکل ۶).

شکل ۶- کاربرد تابع Index -تخصیص نام مورد نظر در دیتاولیدیشن

در نهایت لیستی داریم مطابق شکل ۷ که با کم و زیاد شدن آیتم ها، موارد داخل لیست هم آپدیت میشه.

شکل ۷- کاربرد تابع Index -ایجاد لیست داینامیک و پویا

۵- جستجو (Vlookup) با ترکیب Index و Match

استفاده از ترکیب توابع Index و Match بجای Vlookup میتونه مزیت های زیر رو داشته باشه:

• مسئله رو به جلو بودن جستجو مطرح نیست و میشه جستجوی عقبگرد انجام داد. (در خصوص Backward Vlookup این مقاله رو مطالعه کنید)
• محدودیت کاراکتر برای سرچ وجود نداره
• نیازی به مرتب بودن داده ها نیست. (در Vlookup با جستجوی تخمینی، داده ها باید مرتب باشن)
• حذف و اضافه ردیف و ستون به مراتب راحت تره
• این ترکیب به مراتب سریع تر از Vlookup چندگانه است و موجب کند شدن فایل نمیشه.

مثلا فرض کنید میخوایم ببینیم فروش ماه اردیبهشت چقدر بوده است؟ بجای Vlookup از ترکیب Index و Match استفاده میکنیم.

=INDEX ( B2:B13 ; MATCH(D2;A2:A13;0) )

در واقع تابع Match برای ما تعیین میکنه که ماه اردیبهشت چندمین سلول  در محدوده B2:B13 هست (در این مثال میشه ۲) و خروجی آن در آرگومان Row_num تابع Index قرار میگیره. یعنی :

=INDEX ( B2:B13 ; 2 )

شکل ۸- کاربرد تابع Index -جستجو با استفاده از Index و Match

۶- فراخوانی یک محدوده از لیستی از محدوده ها

همونطور که تا الان متوجه شدید، تابع Index دو حالت داره: حالت Array و حالت  .Referenceتا الان با حالت Array کار کردیم و آشنا شدیم. در ادامه یک مثال برای حالت Reference ارائه میدم:

تابع Index در حالت Reference میتونه محدوده های مختلفی رو بگیره و جستجو رو انجام بده. مثلا دو محدوده داریم و هر بار میخوایم بین یکی از اینها داده مورد نظر رو پیدا کنیم. به شکل ۹ دقت کنید:

=Index ( (A1:B5;D1:E5;G1:H5;J1:K5) ; 3 ; 2 ; 2 )

شکل ۹- انتخاب محدوده جستجو با تابع Index (Reference)

در آرگومان اول محدوده های مختلف رو انتخاب میکنیم و با جداکننده فرمول از هم جدا میکنیم. آرگومان دوم و سوم که همون شماره ردیف و ستون مورد نظر هست.

رگومان آخر هم مشخص میکنه که از بین محدوده های انتخاب شده، جستجو بین چندمین محدوده انجام بشه.

در این فرمول می بینید که آرگومان Area_Num ، ۲ تعیین شده که در .اقع به این معنی هست که دومین محدوده در نظر گرفته بشه.پس خروجی این تابع، سلول حاصل تقاطع ردیف ۳ و ستون ۲ در محدوده دوم یعنی D1:E5 خواهد بود.

حالا اگه بخوایم طوری فرمول نویسی کنیم با انتخاب فصل مورد نظر، خودش شماره محدوده رو تعیین کنه به روش زیر عمل میکنیم:

برای این کار یک لیست (Data Validation) درست میکنیم در سلول A9 که شامل عنوان چهار فصل هست، سپس فرمول زیر رو ثبت میکنیم:

=INDEX( (A1:B5;D1:E5;G1:H5;J1:K5) ; 3 ; 2 ; MATCH (A9 ; {“بهار”;”تابستان”;”پاییز”;”زمستان”} ;۰))

در واقع با استفاده از تابع Match شماره فصل رو استخراج میکنیم که بتونیم در آرگومان اخر تابع Index در حالت Reference ازش استفاده کنیم. با تغییر نام فصل خروجی تابع Match و در نهایت خروجی تابع Index تغییر میکنه.

در مثالهای بالا، استفاده از تابع Match میتونه خیلی کاربردی باشه. فکر میکنید در کدوم مثال و چطور میشه از این تابع در جهت بهبود فرمول های ارائه شده استفاده کرد؟ همچنین توابعی که میتونه جایگزین تابع Match بشه چی هست؟

پاسخ های خودتون رو در قالب کامنت و در ادامه همین پست ثبت کنید.

با مطالعه این مقاله مشاهده کردید که تابع Index چه توانایی های فوق العاده ای داره، مخصوصا زمانی که با توابع دیگه ترکیب میشه. پس سعی کنید خیلی خوب به این مبحث مسلط بشید. برای اطلاعات بیشتر میتونید مقاله ویژگی بسیار جالب تابع Index رو بخونید.

دانلود فایل کاربرد تابع Index در اکسل

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