آموزش فرمول index در اکسل

یکی از هوشمندترین و انعطاف پذیرترین توابع جستجو، فرمول Index در اکسل است. در این مطلب به طور کامل با این فرمول آشنا خواهید شد.
و اما فرمول Index چه کاری انجام می دهد؟
این فرمول می تواند از یک محدوده یا جدول مقدار موجود در یک موقعیت مشخص (شماره سطر یا ستون) را فراخوانی کند. به عبارت دیگر، زمانی از Index استفاده می شود که موقعیت یک مقدار را در محدوده می دانیم (یا می توانیم آن را محاسبه کنیم) ولی خود مقدار برای ما مجهول است و می خواهیم آن را به دست آوریم.
فرمول Index را می توان از گروه توابع Lookup/Reference فراخواند.
هنگام فراخوانی فرمول Index متوجه می شوید که دو نوع از این فرمول ارائه می شود. بسته به نیاز خود باید آرگومان ها را مطابق با فرم صحیح وارد کنید.

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

فرم آرایه ای فرمول Index در اکسل

فرمت آرایه ای فرمول Index زمانی استفاده می شود که بخواهیم مقادیر موجود در یک یا چند سلول را تنها در یک محدوده (یا یک بازه) جستجو کنیم.
ترکیب این فرمول به صورت زیر است:

=INDEX(array, row_num, [column_num])

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

فرمول Index را فرا می خوانیم و شروع به وارد کردن آرگومان ها به شرح زیر می کنیم:

آرگومان اول (array)

این آرگومان یک آرایه یا یک محدوده ای از سلول هایی است که می‌خواهیم مورد جستجو قرار گیرد. در این نمونه می توانیم کل جدول را انتخاب کنیم؛ یعنی به صورت A:D و یا محدوده داده ها را به صورت A2:D8 انتخاب کنیم.

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

آرگومان دوم (row_num)

این پارامتر تعیین کننده شماره ردیفی است که می خواهیم خروجی فرمول از آنجا صورت گیرد.
همانطور که کاملا مشخص است شماره ردیف مقدار مورد نظر ما ۵ است.

دقت کنید فرمول ردیف پنجم شیت را برنمی گرداند. در واقع ردیف پنجم در محدوده A2 تا D8 را جستجو می کند.

آرگومان سوم (column_num)

این مورد نشان دهنده شماره ستون در آرایه ای است که می خواهید مقدار مورد نظر از آن برگردانده شود. در این نمونه شماره ستون مقدار مورد نظر ۳ است.

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

شماتیک کلی فرمول index در حالت اول یعنی حالتی که تنها یک محدوده میپذیرد به شکل زیر می باشد:

فرم مرجع فرمول Index در اکسل

به تصویر زیر توجه کنید. مشاهده می کنید که فرمول Index برای دو مقدار مجهول خواسته شده در ردیف ۲،ستون۳ دو مقدار متفاوت را برگردانده است. چگونه این اتفاق افتاده است؟

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

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

آرگومان اول (Reference)

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

=INDEX((A3:D7; A9:D13)

آرگومان دوم (row_num)

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

=INDEX((A3:D7; A9:D13);2

آرگومان سوم (column_num)

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

=INDEX((A3:D7; A9:D13);2;3

آرگومان چهارم (area_num)

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

برای بررسی محدوده اول عدد ۱ را به فرمول می دهیم و برای جستجو در محدوده دوم عدد ۲ را به فرمول می دهیم.

=INDEX((A3:D7; A9:D13);2;3;1)

=INDEX((A3:D7; A9:D13);2;3;2)

نکاتی که باید در مورد فرمول Index بدانید

در فرم آرایه، row_num و column_num باید به سلول های درون آرایه اشاره کنند. وگرنه خطای !REF# را برمی گرداند.
در فرم مرجع تمام پارامتر ها باید به سلول های در درون Reference اشاره داشته باشند. در غیر اینصورت فرمول خطای !REF# را برمی گرداند.
اگر row_num حذف شود، column_num الزامی خواهد بود. و اگر column_num حذف شود، row_num الزامی خواهد بود.

در این مطلب با فرمول Index در اکسل آشنا شدید.

با ما همراه باشید تا با ترکیب فرمول Index با سایر توابع از جمله فرمول Match آشنا شویم.