توابع کلیدی اکسل - تابع INDEX

تابع INDEX مقداری را از یک محدوده سلولی بر اساس شماره سطر و ستونی که مشخص می‌کنید ، باز می‌گرداند. می‌توانید از INDEX برای بازیابی مقادیر تکی یا کل ردیف‌ها و ستون‌ها استفاده کنید. تابع MATCH اغلب همراه با INDEX برای ارائه شماره ردیف‌ها و ستون‌ها استفاده می‌شود. 

     از کجا شروع کنم      

هدف تابع

دریافت یک مقدار از یک لیست یا جدول بر اساس موقعیت سلول مربوطه

خروجی تابع

مقدار یک سلول مشخص 

ترکیب عمومی تابع

=  INDEX ( array , row_num , [ col_num ] , [ area_num] )

ورودی تابع (مولفه‌ها‌)

Array  یا  cell reference– محدوده سلول‌هایی که می‌خواهید یک مقدار از آن محدوده بدست آورید.
row_num – شماره سطر مقداری که می‌خواهید دریافت کنید . اگر مقدار آن صفر باشد و به صورت یک فرمول آرایه‌ای وارد کنید ، تابع تمام مقادیر را بر می‌گرداند.
col_num – [اختیاری] شماره سطر مقداری که می‌خواهید دریافت کنید. اگر مقدار آن صفر باشد و به صورت یک فرمول آرایه‌ای وارد کنید ، تابع تمام مقادیر را بر می‌گرداند.
area_num – [اختیاری] شماره محدوده انتخاب شده در اولین مولفه ( وقتی تابع بصورت آدرس سلول آدرس دهی شود).

نحوه استفاده از تابع

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

INDEX اغلب همراه با تابع MATCH استفاده می‌شود. در این سناریو ، تابع MATCH یک مقدار را پیدا می‌کند و موقعیت عددی را به تابع INDEX می‌دهد و INDEX مقدار را از آن موقعیت بر می‌گرداند.

در رایج‌ترین ترکیب مورد استفاده ، تابع INDEX سه مولفه می‌گیرد: array، row_num و col_num . مولفه Array محدوده‌ای از سلول‌ها است که مقادیر از آن بازیابی می‌شوند. یک آرایه بیش از یک سلول را شامل می شود.

Row_num شماره سطری است که مقدار از آن بازیابی می‌شود و col_num شماره ستونی است که مقدار از آن بازیابی می‌شود. Col_num اختیاری است و وقتی آرایه یک بعدی است، نیازی به آن نیست.

در مثال بالا، هدف بدست آوردن قطر سیاره مشتری است. از آنجا که مشتری پنجمین سیاره در لیست است و قطر سیاره‌ها ستون سوم است، فرمول G3 به صورت زیر است:

= INDEX ( B3:E11 , 5 , 3 )   //   قطر مشتری

فرمول بالا ارزش محدودی دارد زیرا شماره سطر و ستون به صورت عددی وارد شده‌ است. معمولا از تابع MATCH درون تابع INDEX برای ارائه این اعداد استفاده می‌شود. 

کاربرد اولیه

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

در اکسل یک آرایه می‌تواند یک بعدی یا دو بعدی باشد. آرایه‌های یک بعدی در سلول‌های یک سطر و یا یک ستون قرار می‌گیرند و در آرایه‌های دو بعدی , سلول‌های اکسل به صورت مستطیلی پر می‌شوند.
وقتی محدوده یک بعدی است، فقط باید شماره سطر را وارد کنید. وقتی محدوده دو بعدی است، باید هم شماره سطر و هم شماره ستون محدوده را وارد کنید. به عنوان مثال ، برای دریافت سومین مورد از محدوده یک بعدی A1:A5 فرمول به صورت زیر خواهد بود:

= INDEX ( A1:A5 , 3 )   //  را برمی‌گرداند A3 مقدار سلول 

فرمول‌های زیر نشان می‌دهند که چگونه می‌توان از تابع INDEX برای بدست آوردن مقداری از یک محدوده دوبعدی استفاده کرد:

= INDEX ( A1:B5 , 2 , 2 )   //  را برمی‌گرداند B2 مقدار سلول
= INDEX ( A1:B5 , 3 , 1 )   //  را برمی‌گرداند A3 مقدار سلول

INDEX و MATCH

در مثال‌های بالا، موقعیت‌ها (شماره سطر و شماره ستون) به صورت عددی مقدار دهی شده است. معمولاً از تابع MATCH برای یافتن موقعیت‌های INDEX استفاده می‌شود. برای مثال، در برگه زیر، از تابع MATCH برای یافتن «مریخ» (G2) در سطر 4 و ارسال موقعیت آن به INDEX استفاده می‌شود. فرمول G3 به شرح زیر است:

= INDEX ( B3:E11 , MATCH ( G2 , B3:B11 , 0 ) , 3 )

تابع MATCH شماره سطر (4) را به INDEX ارائه می‌دهد. شماره ستون همچنان به صورت عددی 3 است.

INDEX و MATCH با جدول افقی

در برگه زیر، جدول بالا به صورت افقی جابجا شده است. تابع MATCH شماره ستون (4) را بر می‌گرداند و شماره سطر به صورت ثابت 2 وارد شده است. فرمول C10 به شرح زیر است:

= INDEX ( C4:K6 , 2 , MATCH ( C9 , C4:K4 , 0 ) )

ترکیب‌های تابع INDEX

تابع INDEX دو ترکیب دارد: array و reference. هر دو ترکیب رفتار یکسانی دارند - INDEX آدرس در یک آرایه را بر اساس موقعیت سطر و ستون مشخص بر می‌گرداند. تفاوت این است که فرم reference تابع INDEX اجازه می‌دهد بیش از یک آرایه، همراه با یک مولفه اختیاری برای انتخاب آرایه مورد استفاده، وجود داشته باشد. اکثر فرمول‌ها از شکل آرایه‌ای INDEX استفاده می‌کنند، هر دو ترکیب در ادامه مورد بحث قرار می‌گیرد.

فرم (array) آرایه‌ای

در شکل آرایه‌ای INDEX، اولین مولفه یک آرایه است که به عنوان محدوده‌ای از سلول‌ها یا یک ثابت آرایه ارائه می‌شود. ترکیب فرم آرایه‌ای تابع INDEX به شرح زیر است:

INDEX ( array , row_num , [ col_num ] )

اگر هر دو مولفه row_num و col_num وارد شوند، تابع INDEX مقدار موجود در سلول تقاطع row_num و col_num را بر می‌گرداند.

اگر row_num روی صفر تنظیم شود، تابع INDEX آرایه‌ای از مقادیر را برای کل یک ستون برمی‌گرداند. برای استفاده از این مقادیر آرایه‌ای، می‌توانید تابع INDEX را به عنوان یک فرمول آرایه‌ای در یک محدوده افقی وارد کنید، یا آرایه را به تابع دیگری بدهید.

اگر col_num روی صفر تنظیم شود، تابع INDEX آرایه‌ای از مقادیر را برای کل یک ردیف برمی‌گرداند. برای استفاده از این مقادیر آرایه‌ای، می‌توانید تابع INDEX را به عنوان یک فرمول آرایه‌ای در یک محدوده عمودی وارد کنید، یا آرایه را به تابع دیگری بدهید.

نکته :کلید تابعی F9 محدوده انتخاب شده را به آرایه تبدیل می کند.

روی یک سلول خالی دوبار کلیک کنید (سلول B10). علامت = را تایپ کنید. محدوده سلول‌های مورد نظر را با موس انتخاب و یا آدرس محدوده را تایپ کنید ( B3: D5 ).کلید تابعی F9 را از صفحه کلید فشار دهید. نتیجه را در فرمول‌بار مشاهده می‌کنید. آرایه حاصل را می‌توانید در فرمول استفاده کنید.

برای جدا کردن ستون‌ها از Comma (،) و برای جدا کردن سطر‌ها از semicolon (؛) استفاده شده است. در نسخه انگلیسی اکسل از Comma و semicolon استفاده می‌شود ، در نسخه‌های دیگر زبان اکسل ممکن است از دیگر نمادها استفاده شود.

 فرم reference

در فرم reference تابع INDEX، مولفه اول آدرس یک یا چند محدوده است و مولفه اختیاری چهارم، area_num، برای انتخاب محدوده مناسب ارائه شده است. ترکیب فرم reference تابع INDEX به صورت زیر است:

INDEX ( reference , row_num , [ col_num ] , [ area_num ] )

درست مانند فرم آرایه‌ای ، فرم reference آدرس سلول تقاطع row_num و col_num را بر می‌گرداند. تفاوت این است که مولفه اول شامل بیش از یک محدوده است و area_num انتخاب می‌کند که کدام محدوده باید استفاده شود. مولفه area_num به عنوان یک عدد ارائه می‌شود که مانند یک اندیس عددی عمل می‌کند. اولین آرایه داخل مولفه اول ۱، آرایه دوم ۲ و ... است.

به عنوان مثال، مولفه اول فرمول زیر دارای دو محدوده است و در مولفه area_num مقدار ۲ (محدوده دوم) انتخاب شده که به محدوده A7:C10 اشاره دارد:

= INDEX ( ( A1:C5 , A7:C10 ) , 1 , 3 , 2 )

در فرمول بالا، تابع INDEX مقدار سلول واقع در تقاطع سطر ۱ و ستون ۳ از محدوده A7:C10 را برمی‌گرداند.

محدوده‌های چندگانه در مولفه اول با کاما از هم جدا شده و داخل پرانتز قرار می‌گیرند.

همه محدوده‌ها باید در یک برگه اکسل باشند، در غیر این صورت تابع INDEX خطای VALUE # را بر می‌گرداند. می‌توانید از تابع CHOOSE به عنوان راه حل استفاده کنید.

کل سطر/ستون

از INDEX می‌توان برای برگرداندن کل ستون‌ها یا سطرها با استفاده از فرمولی به صورت زیر استفاده کرد:

= INDEX ( range , 0 , n )   //   کل ستون
= INDEX ( range , n , 0 )   //   کل سطر

که در آن n نشان دهنده تعداد ستون یا سطری است که باید برگردانده شود و range محدوده سلول‌های مشخص شده است. 

آدرس سلول به عنوان نتیجه تابع

لازم به ذکر است که تابع INDEX یک آدرس سلول به عنوان نتیجه بر می‌گرداند. برای مثال، در فرمول زیر، INDEX مقدار A2 را بر می‌گرداند:

= INDEX (A1:A5 , 2 )   //  را برمی‌گرداند A2

در یک فرمول معمولی، مقدار موجود در سلول A2 را به عنوان نتیجه خواهید دید، بنابراین مشخص نیست که INDEX یک آدرس را بر می‌گرداند. با این حال، این یک ویژگی مفید در فرمول‌هایی است که از INDEX برای ایجاد یک محدوده نامگذاری شده پویا استفاده می‌کند. می‌توانید از تابع CELL برای گزارش آدرس برگردانده شده توسط INDEX استفاده کنید.

لطفا دیدگاه خود را در کادر پایین با ما به اشتراک بگذارید.

آیا می‌خواهید:

• برنامه‌های قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
• فرصت های شغلی جدید کسب کنید.

کتاب‌های معرفی شده ، راهنمای گام به گام جامع و عملی برای افرادی است که (مانند شما) می‌خواهند به این اهداف برسند.