دو ورژن مختلف از index در اکسل وجود دارد – شکل آرایه ای و شکل مرجع. هر دو شکل این تابع را میتوان در ورژن های 2003 به بعد اکسل استفاده کرد.

شکل آرایه ای تابع index

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

=INDEX(array, row_num, column_num)

Array: محدوده ای از سلول ها، محدوده دارای نام یا جدول است.

row_num: شماره ردیف در آرایه ای است که میخواهید مقدار مورد نظر را از آن برگردانید. اگر row_num حذف شود، COL_NUM الزامی خواهد بود.

column_num: شماره ستون در آرایه ای است که میخواهید مقدار مورد نظر از آن برگردانده شود. اگر column_num حذف شود، row_num الزامی خواهد بود.

برای مثال فرمول=INDEX(A1:D6, 4, 3)مقدار موجود در تقاطع ردیف چهارم و ستون سوم را در محدوده A1:D6 برمیگرداند. که همان مقدار موجود در سلول C4 است.

برای درک بهتر تابع INDEX و نحوه کارکرد این تابع در مورد داده های واقعی، لطفاً به مثال زیر توجه فرمائید:

به جای وارد کردن شماره ردیف و ستون در فرمول، میتوانید آدرس سلول را وارد کنید تا فرمول کلی تری بدست آورید:

=INDEX($B$2:$D$6, G2, G1)

بنابراین، تابع INDEX دقیقاً مقدار موجود در تقاطع شماره محصولی که در سلول G2 قرار گرفته (شماره ردیف) و شماره هفته ای که در سلول G1 قرار گرفته (شماره ستون) را برمیگرداند.

نکته: استفاده از آدرس های مطلق(($B$2:$D$6 به جای آدرس نسبی (B2:D6) در پارامتر ARRAY، کپی کردن فرمول به دیگر سلول ها را ساده تر میکند. همچنین میتوانید یک محدوده را با استفاده از (CTRL+T) تبدیل به جدول کنید و در فرمول خود به آن جدول اشاره کنید.

شکل آرایه ای تابع INDEX

1. در صورتی که پارامتر آرایه فقط یک ستون یا ردیف داشته باشد، میتوانید شماره ستون یا ردیف را وارد کنید یا نکنید.
2. اگر پارامتر آرایه بیش از یک ردیف داشته باشد و ROW_NUMBER حذف شده باشد یا بر روی ۰ قرار داده شده باشد، تابع INDEX آرایه ای از کل ستون را برمیگرداند. همچنین اگر، آرایه بیش از یک ستون داشته باشد و COLUMN NUMBER بر روی صفر قرار داشته باشد یا خالی باشد، فرمول INDEX کل ردیف را برمیگرداند.
3. پارامترهای ROW_NUMBER و COLUMN_NUMBER، می بایست به یک سلول در درون آرایه اشاره کنند در غیر اینصورت فرمول INDEX خطای #REF! را برمیگرداند.

شکل مرجع تابع INDEX

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

=INDEX(reference, row_num, [column_num], [area_num])

Reference: یک یا چند محدوده

اگر بیش از یک محدوده را وارد کنید، محدوده ها را بوسیله کاما از هم جدا کنید و کل پارامتر reference را درون پرانتز قرار دهید. برای مثال (A1:B5, D1:F5) .

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

row_num(شماره ردیف): شماره ردیف در محدوده ای که میخواهید آدرس مورد نظر از آن برگردانده شود. این پارامتر دقیقاً شبیه شکل آرایه ای این تابع است.

column_num(شماره ستون): شماره ستونی که میخواهید یک آدرس از آنجا برگردانید. این پارامتر هم شبیه به شکل آرایه ای تابع INDEX کار میکند.

area_num: یک پارامتر اختیاری است که نشان میدهد کدام محدوده از پارامتر REFERENCE می بایست به کار گرفته شود. در صورتی که از فرمول حذف شود، تابع INDEX نتایج را بر اساس اولین محدوده در پارامتر REFERENCE برمیگرداند.

برای مثال، فرمول زیر

=INDEX((A2:D3, A5:D7), 3, 4, 2)

مقدار سلول D7 را که در تقاطع ردیف سوم و ستون چهارم در محدوده دوم (A5:D7) است، برمیگرداند.

شکل مرجع تابع INDEX

1. اگر شماره ردیف یا ستون ۰ قرار داده شود، تابع INDEX به ترتیب آدرس کل ردیف یا ستون را برمیگرداند.
2. اگر هم شماره ردیف و هم شماره ستون حذف شده باشد، تابع INDEX محدوده ای را که در AREA_NUM مشخص شده است برمیگرداند.
3. تمام پارامترهای (NUM (row_num, column_num and area_num می بایست به یک سلول در درون REFERENCE اشاره داشته باشند در غیر اینصورت تابع خطای #REF! را برمیگرداند.

هر دو فرمول INDEX که تاکنون در مورد آنها بحث کردیم بسیار ساده بودند و فقط مفهوم این تابع را نشان دادند. فرمول های واقعی بسیار پیچیده تر از این فرمول ها خواهند بود. بنابراین به بررسی چند مثال پیشرفته تر از تابع INDEX میپردازیم.

نحوه استفاده از تابع INDEX در اکسل – مثال های فرمولی

ممکن است تابع INDEX به تنهایی کاربرد های زیادی در اکسل نداشته باشد ولی این تابع در ترکیب با توابعی مثل MATCH و COUNTA میتواند بسیار قدرتمند عمل کند.

در تمام فرمول های  INDEXدر این مطلب (به جز مثال آخر) از داده های زیر استفاده خواهیم کرد. برای استفاده راحت تر، این داده ها در جدول زیر که DataSource نام دارد قرار گرفته است.

استفاده از جدول ها و محدوده های دارای نامت، فرمول ها را کمی طولانی میکند ولی این کار باعث میشود فرمول انعطاف پذیرتر و قابل خواندن تر شود. برای اصلاح هر فرمول INDEX در ورک شیت خود می بایست یک نام را تغییر دهید و این کار باعث طولانی شدن فرمول خواهد شد.

البته شما میتوانید از محدوده های معمولی در فرمول خود استفاده کنید. در این صورت، فقط کافیست نام جدول (SOURCENAME) را در فرمول با آدرس مورد نظر خود جایگزین کنید.

1. بدست آوردن آیتم Nام از یک لیست

این مورد آسانترین و ابتدایی ترین مثال فرمول INDEX است. برای برگرداندن یک آیتم مشخص از یک لیست فقط کافیست فرمول =INDEX(range, n) را وارد کنید. RANGE محدوده ای سلول ها یا یک محدوده دارای نام است و N جایگاه آیتمی است که میخواهید بدست آورید.

زمانی که با جدول های اکسل کار میکنید، میتوانید ستون را با استفاده از ماوس انتخاب کنید و اکسل نام ستون را در کنار نام جدول در فرمول قرار خواهد داد.

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

مثال بعد:

در جدول نمونه بالا، برای پیدا کردن دومین سیاره بزرگ در منظومه شمسی ، جدول را بر اساس ستون «قطر» دسته بندی کنید و از فرمول INDEX زیر استفاده کنید:

=INDEX(SourceData, 2, 3)

ARRAY: نام جدول یا آدرس محدوده است که در این مثال همان SOURCEDATA است.

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

COL_NUM 3 قرار داده شده زیرا سومین ستون در جدول است.

در صورتی که میخواهید به جای اندازه سیاره نام آن را نمایش دهید، شماره ستون را به ۱ تغییر دهید. طبیعتاً، برای فراگیر تر کردن فرمول خود میتوانید به شکلی که در تصویر زیر نشان داده شده است، به جای شماره ردیف یا ستون در فرمول از آدرس سلول استفاده کنید:

2. بدست آوردن تمام مقادیر در یک ردیف یا ستون

به جای برگرداندن یک سلول، تابع INDEX قادر است آرایه ای از سلول ها را از کل ردیف یا ستون برگرداند. برای بدست آوردن کل مقادیر یک ستون ، می بایست ROW-NUM را حذف کنید یا آن را بر روی ۰ قرار دهید. به همین صورت برای بدست آوردن کل یک ردیف، می بایست COL-NUM را خالی نگه دارید یا ۰ قرار دهید.

ار چنین فرمول هایی به سختی میتوان به تنهایی در اکسل استفاده کرد. زیرا این فرمول ها مجموعه ای از سلول ها را برمیگردانند و این مجموعه قادر نیست در یک سلول قرار گیرد به همین دلیل اکسل خطای #VALUE را برمیگرداند. اما اگر از تابع INDEX در ترکیب با دیگر توابع مثل SUM یا AVERAGE استفاده کنیم، نتایج فوق العاده ای بدست خواهید آورد.

برای مثال، میتوانید از فرمول INDEX برای بدست آوردن میانگین دمای سیاره ها در منظومه شمسی استفاده کنید.

=AVERAGE(INDEX(SourceData, ,4))

در فرمول بالا شماره ستون ۴ قرار داده شده زیرا دمای سیارات در ستون چهارم قرار دارد. همچنین پارامتر شماره ردیف حذف شده است.

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

=MAX(INDEX(SourceData, , 4))

=MIN(INDEX(SourceData, , 4))

یا جرم کل سیارات را بدست آورید (جرم در ستون دوم جدول قرار دارد)

=SUM(INDEX(SourceData, , 2))

به لحاظ عملی، توابع INDEX در فرمول های بالا زائد است و شما میتوانید با حذف این تابع و به صورت

=AVERAGE(range)

یا

=SUM(range)

همان نتایج را بدست آورید.

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

3. استفاده از INDEX در ترکیب با دیگر توابع اکسل(SUM, AVERAGE, MAX, MIN)

ممکن است با دیدن مثال های قبل تصور کنید که تابع INDEX یک مقدار را برمیگرداند در حالیکه واقعیت این است که این تابع آدرس سلول را برمیگرداند. مثال زیر درک صحیحی از تابع INDEX را ایجاد خواهد کرد.

از آنجایی که نتیجه یک فرمول INDEX یک آدرس است، میتوانیم از آن در دیگر توابع و برای ساخت یک محدوده پویا استفاده کنیم. گیج کننده است؟ فرمول زیر همه چیز را روشن خواهد کرد.

فرض کنید فرمول  =AVERAGE(A1:A10) را که میانگین سلول های A1:A10 را برمیگرداند در اختیار دارید. شما میتوانید A1 یا A10 یا هر دو را با یک فرمول  INDEXجایگزین کنید. برای مثال:

=AVERAGE(A1 : INDEX(A1:A20,10))

هر دو فرمول های بالا، یک نتیجه را برمیگردانند زیرا فرمول INDEX (شماره ردیف ۱۰ و شماره ستون خالی قرار داده شده است) در واقع آدر سلول A10 را در فرمول میانگین قرار میدهد. تفاوت این دو در این است که محدوده فرمول AVERAGE/INDEX پویا است و زمانی که شماره ردیف را در تابع INDEX تغییر دهید، محدوده ای که توسط فرمول میانگین پردازش میشود تغییر میکند و فرمول نتیجه دیگری را نشان میدهد.

مثال۱٫ بدست آوردن میانگین N آیتم اول در یک لیست

فرض کنید میانگین N سیاره بزرگ را در جدول میخواهید. برای این کار می بایست جدول را بر اساس ستون «قطر» و از بزرگ به کوچک مرتب کنید و از فرمول Average/index زیر استفاده کنید:

=AVERAGE(C5 : INDEX(SourceData[Diameter], B1))

مثال۲٫ بدست آوردن مجموع آیتم هایی که در بین دو آیتم مشخص شده قرار دارند.

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

برای مثال، فرمول زیر، مجموع مقادیر را در ستون «قطر» که در بین دو آیتم موجود در سلول های B1 و B2 قرار دارند برمیگرداند.

=SUM(INDEX(SourceData[Diameter],B1) : INDEX(SourceData[Diameter], B2))

4. استفاده از فرمول index برای ساخت محدوده های پویا و لیست

در بسیاری از موارد اتفاق می افتد که در زمان سازماندهی داده ها در یک شیت، نمیدانید که چند عنصر دارید. این مورد در جدول سیارات ما اتفاق نیافتاده ولی ممکن است دربعضی از موارد برای شما اتفاق افتد.

به هر حال، در صورتی که تعداد در حال تغییری از آیتم ها را در یک ستون دارید، برای مثال از A1 تا AN، می بایست محدوده پویایی ایجاد کنید که تمام سلول های دارای داده را در بر بگیرد. در این مورد شما می بایست محدوده ای داشته باشید که در صورت حذف یا اضافه کردن داده های جدید به صورت اتوماتیک تغییر اندازه دهد. برای مثال، اگر در حال حاضر ۱۰ آیتم دارید، محدوده شما A1:A10 است. اگر یک عنصر جدید اضافه کنید، محدوده شما به صورت اتوماتیک به A1:A11 تغییر میکند و اگر نظرتان عوض شود و داده اضافه شده را دو باره حذف کنید محدوده به صورت اتوماتیک به A1:A10 تغییر میکند.

مزیت اصلی این شیوه، این است که در هر بار تغییر در محدوده نیازی به ایجاد تغییر و اصلاح در تمام فرمول های ورک بوک نخواهد بود.

یکی از راه های تعریف یک محدوده پویا استفاده از تابع offset در اکسل است:

=OFFSET(Sheet_Name!$A$1, 0, 0, COUNTA(Sheet_Name!$A:$A), 1)

یکی دیگر از راهها استفاده از تابع index همراه با counta است:

=Sheet_Name!$A$1:INDEX(Sheet_Name!$A:$A, COUNTA(Sheet_Name!$A:$A))

در هر دو فرمول، A1 سلولی است که آیتم اول لیست در آن قرار گرفته و محدوده پویایی که توسط هر دو فرمول ایجاد میشود یکسان است.

تفاوت فقط در شیوه این دو فرمول است. در حالی که فرمول offset از نقطه شروع به تعداد مشخصی ردیف و ستون جلو میرود، index سلولی را که در محل تقاطع یک ستون و ردیف مشخص است پیدا میکند. تابع counta نیز، که در هر دو فرمول مورد استفاده قرار گرفته، در ستون مورد نظر تعداد سلول های غیر خالی را بدست می آورد.

در این مثال، ۹ سلول غیر خالی در ستون A وجود دارد در نتیجه counta، ۹  را برمیگرداند. در نتیجه، تابع index $A$9 را که آخرین سلول استفاده شده در ستون A است برمیگرداند (فرمول index معمولاً یک مقدار را برمیگرداند ولی در اینجا، عملگر آدرس(:) این تابع را مجبور به برگرداندن یک آدرس میکند). و از آنجایی که $A$1 نقطه شروع ماست نتیجه نهایی فرمول ما محدوده $A$1:$A$9 است.

تصویر زیر نحوه استفاده از تابع index برای ساخت لیست dropdown را نشان میدهد.

نکته: ساده ترین راه برای ساخت یک لیست پویا و به روز شونده در اکسل ایجاد یک لیست دارای نام بر اساس یک جدول است. در این صورت، به هیچ فرمول پیچیده ای نیاز نخواهید داشت زیرا جدول های اکسل فی نفسه پویا هستند.

همچنین میتوانید با استفاده از تابع index نیز لیست های وابسته را ایجاد کنید.

5. vlookup  قوی همراه با index/match

انجام جستجوی عمودی در اکسل – این همان چیزی است که تابع index در انجام آن بسیار قدرتمند است. بنابراین اگر در انجام جستجوی عمودی از تابع vlookup خسته شده اید یا محدودیت های بسیار زیاد این تابع مثل عدم امکان برگرداندن مقادیر از ستون های قرار گرفته در سمت چپ lookup column، یا محدودیت ۲۵۵ کاراکتر برای مقدار مورد جستجو  شما را به ستوه آورده است میتوانید از تابع index به جای آن استفاده کنید.

• هیچ مشکلی در جستجوی مقادیر سمت چپ وجود ندارد
• هیچ محدودیتی در اندازه مقدار مورد جستجو وجود ندارد
• نیاز به هیچ دسته بندی وجود ندارد (تابع vlookup به مطابقت جزئی نیاز به دسته بندی مقادیر به صورت صعودی دارد)
• شما در حذف یا اضافه ستون ها آزاد هستید بدون اینکه نیاز به تغییری در فرمول ها باشد
• و آخرین مورد اینکه توابع index match مثل vlookup سرعت سیستم شما را پائین نمی آورند.

از index match به شیوه زیر استفاده میشود:

=INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))

برای مثال، اگر Sourcetable را عکس کنیم به صورتی که نام سیاره در ستون سمت راست قرار گیرد، فرمول index match هنوزه مقدار مورد نظر را از ستون سمت چپ انتخاب میکند بدون اینکه نیازی به انجام هیچ کاری باشد.

6. فرمول index برای بدست آوردن یک محدوده از بین مجموعه ای از محدوده ها

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

فرض کنید، چندین لیست دارید و در هرکدام چندین آیتم وجود دارد. شاید باورتان نشود ولی میتوانید میانگین یا مجموع مقادیر را در هر محدوده ای که انتخاب کنید بوسیله یک فرمول بدست آورید.

ابتدا، یک محدوده دارای نام را برای هرکدام از لیست ها ایجاد کنید. برای مثال در اینجا PlanetsD و MoonsD را انتخاب میکنیم:

امیدوارم که تصویر بالا دلیل استفاده از محدوده های دارای نام را برای شما روشن کرده باشد. همچنین باید اشاره کرد که لیستی که از قمر ها در اینجا آورده شده است کامل نیست و ما در منظومه شمسی ۱۷۶ قمر شناخته شده داریم و من فقط به صورت تصادفی ۱۱ مورد را در این جدول آورده ام. البته زیاد هم تصادفی نیست و ۱۱ مورد از زیباترین ها انتخاب شده است.

از اینکه از بحث منحرف شدیم عذرخواهی میکنم و به بحث اصلی خود برمیگردیم. با فرض اینکه PlanetsD محدوده ۱ شماست و MoonsD محدوده ۲ شماست و شماره محدوده را در سلول B1 قرار میدهید، میتوانید با استفاده از فرمول زیر میانگین را در محدوده دارای نام مورد نظر خود محاسبه کنید:

=AVERAGE(INDEX((PlanetsD, MoonsD), , , B1))

لطفا به یاد داشته باشید که در حال حاضر از شکل آرایه ای تابع index استفاده میکنیم و شماره موجود در پارامتر آخر (area_num) محدوده مورد نظر را به فرمول میدهد.

در تصویر زیر،  area-nam(سلول B1)،۲ قرار داده شده است در نتیجه فرمول مقدار میانگین قطر قمر ها (moons) را بدست می آورد. زیرا محدوده MoonsD درقسمت دوم  پارامتر reference  قرار میگیرد.

اکر از لیست های متعدد استفاده می کنید و نمیخواهید اعداد مربوط به هر لیست را به خاطر داشته باشید میتوانید از تابع if تو در تو برای انجام این کار استفاده کنید:

=AVERAGE(INDEX((PlanetsD, MoonsD), , , IF(B1="planets", 1, IF(B1="moons", 2))))

در تابع if، از نام های آسان و قابل یادآوری برای لیست ها که میخواهید کاربران به جای عدد در سلول b1 قرار دهند استفاده میکنید. به یاد داشته باشید که برای اینکه فرمول درست کار کند متنی که در سلول b1 قرار میگیرد می بایست دقیقا شبیه به متن درون فرمول باشد( عدم حساس به حروف بزرگ و کوچک) در غیر اینصورت تابع index خطای #value! را برمیگرداند.

برای آسانتر کردن فرمول برای کاربران، میتوانید با استفاده از data validation اکسل یک لیست با نام های از پیش تعیین شده برای کاربران خود ایجاد کنید تا ازبوجود آمدن خطای املایی یا نوشتاری جلوگیری شود.

در آخر، برای اینکه فرمول index خود را از هر نظر بی نقص کرده باشید، میتوانید آن را در درون تابع iferror قرار دهید . این کار کاربر را مجبور میکند که اگر گزینه ای را از لیست انتخاب نکرده باشد، یکی را انتخاب کند:

=IFERROR(AVERAGE(INDEX((PlanetsD, MoonsD), , ,IF(B1="planet",1,IF(B1="moon", 2))),"Please select the list!")

نحوه استفاده از فرمول های index در اکسل توضیح داده شد. امیدواریم که این مطلب شما را در استفاده از پتانسیل های تابع index در اکسل یاری کند.

نحوه استفاده از فرمول های index در اکسل توضیح داده شد. امیدواریم که این مطلب شما را در استفاده از پتانسیل های تابع index در اکسل یاری کند.