تابع INDEX در اکسل – آموزش پیدا کردن و استخراج داده ها + مثال

تابع INDEX یکی از توابع جستجو در اکسل است که با آن یک سلول، یک سطر یا ستون مشخص از جدول را با استفاده از شماره سطر و ستون آن پیدا می‌کنیم. ترکیب تابع INDEX با تابع MATCH یکی از ابزارهای خودکار و قدرتمند جستجو است که با آن می‌توانیم مقدار مورد نظر را بدون وارد کردن شماره سطر و ستون آن در جدول پیدا کنیم. این ترکیب به‌دلیل قابلیت‌های بیشتر خود مانند امکان جستجوی داده از سمت چپ، جایگزین مناسبی برای تابع VLOOKUP است. برای آشنایی عملی با کاربرد تابع INDEX در اکسل، در این مطلب از مجله فرادرس فرمول‌نویسی ساده و ترکیب آن با تابع MATCH را همراه با مثال به شکل تصویری یاد می‌گیریم.

فرمول کلی تابع INDEX در اکسل

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

=INDEX(array, row_num, [column_num])

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

• array (اجباری): محدوده‌ای از جدول که داده مورد نظر برای جستجو در آن قرار دارد.
• row_num (اجباری): شماره ردیفی که داده مورد نظر در آن قرار دارد.
• column_num (اختیاری): شماره ستونی که داده مورد نظر در آن قرار دارد.

ترکیب تابع INDEX با سایر توابع اکسل مانند تابع MATCH کاربرد بسیاری در گزارش‌گیری حرفه‌ای و سریع‌تر کردن تحلیل داده‌ها دارد. ضمن اینکه آشنایی با تفاوت‌های این تابع و سایر توابع جستجوی اکسل نیز به استفاده مفید از هر یک کمک می‌کند. بنابراین پیشنهاد می‌کنیم در کنار مطالعه ادامه بحث، فیلم آموزش استفاده از توابع و فرمول‌نویسی اکسل در فرادرس را مشاهده کنید.

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

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

مثال ۱: استخراج داده‌ یک سلول از یک سطر یا یک ستون

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

به‌عنوان مثال فرض می‌کنیم جدولی از اطلاعات غذاهای مختلف یک رستوران را مانند تصویر زیر داریم.

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

• در نظر گرفتن محدوده به شکل یک ستون A1:A6 که سلول A4 در چهارمین سلول این ستون قرار دارد.

=INDEX(A1:A6,4)

• در نظر گرفتن محدوده به شکل یک ردیف A4:D4 که سلول A4 در اولین سلول از این ردیف قرار دارد.

=INDEX(A4:D4,1)

خروجی فرمول در هر دو حالت مانند تصویر زیر است.

مثال ۲: استخراج داده‌ یک سلول از کل محدوده جدول

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

به‌عنوان مثال می‌خواهیم قیمت غذای «همبرگر کلاسیک» را استخراج کنیم. این قیمت در ردیف سوم و ستون چهارم از جدول قرار دارد.

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

=INDEX(A1:D6,3,4)

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

مثال ۳: استخراج کل داده‌های یک سطر

در این مثال می‌خواهیم از جدول قبلی همه اطلاعات «تاکو گوشت» یعنی نام کشور، تعداد و قیمت آن را استخراج کنیم. برای این کار لازم است کل جدول را انتخاب کنیم. بنابراین محدوده انتخابی، دو بعدی است و نیاز داریم هم سطر و هم ستون را در نظر بگیریم. علاوه بر این، «تاکوی گوشت» در ردیف چهارم از محدوده انتخاب شده قرار دارد.

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

=INDEX(A1:D6,4,0)

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

مثال ۴: استخراج کل داده‌های یک ستون

این بار می‌خواهیم فقط نام انواع غذاهای موجود را استخراج کنیم. در این حالت هم کل اطلاعات جدول را در فرمول می‌نویسیم. نام غذاها در ستون اول از محدوده انتخابی قرار دارند.

بنابراین در سلول دلخواه مانند F2 فرمول را به شکل زیر می‌نویسیم.

=INDEX(A2:D6,0,1)

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

یادگیری کامل فرمول نویسی با توابع اکسل همراه فرادرس

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

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

پیشنهاد اول برای شروع مسیر یادگیری، مشاهده فیلم‌های آموزشی منتخب زیر است.

• فیلم آموزش استفاده از توابع و فرمول‌نویسی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش پیش‌بینی در اکسل با تابع فورکست همراه گواهینامه در فرادرس

همچنین علاقه‌مندان می‌توانند موضوع دلخواه خود را از مجموعه فیلم آموزش زیر نیز انتخاب کنند.

• مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل مقدماتی تا پیشرفته در فرادرس

فرمول نویسی ترکیبی تابع INDEX با تابع MATCH

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

فیلم آموزش توابع و فرمول‌نویسی در Excel در فرادرس

کلیک کنید

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

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), MATCH(lookup_value, lookup_array, [match_type]))

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

• array (اجباری): محدوده‌ جدول که می‌خواهیم نتیجه نهایی فرمول را از آن استخراج کنیم.
• lookup_value (اجباری): مقدار یا عبارتی که می‌خواهیم در محدوده جدول پیدا کنیم.
• lookup_array (اجباری): محدوده‌ای که می‌خواهیم مقدار مورد نظر را در آن جستجو کنیم.
• match_type (اختیاری): معیار تطبیق جستجو برای پیدا کردن مقدار در محدوده است. در این آرگومان عدد صفر به معنی تطبیق کامل و اعداد «۱» و «۱-» تطبیق تقریبی است. اگر عدد«۱» را می‌نویسیم، محدوده را باید به شکل صعودی مرتب کنیم.

کاربردهای فرمول نویسی ترکیبی تابع INDEX و تابع MATCH

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

1. جستجو بر حسب یک متن
2. جستجو به سمت چپ
3. جستجوی چند شرطی
4. جستجو بین دو کاربرگ

در ادامه مطلب هر یک از این موارد را همراه مثال توضیح می‌دهیم.

جستجو بر حسب یک متن

این حالت ساده‌ترین کاربرد ترکیبی دو تابع است. بار دیگر به مثال غذاهای رستوران برمی‌گردیم. همان‌طور که گفتیم در این رستوران فرضی جدولی مانند تصویر زیر داریم.

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

برای این مورد، فرمول را به شکل زیر می‌نویسیم.

=INDEX(D2:D6, MATCH("همبرگر کلاسیک", A2:A6, 0))

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

در این فرمول ابتدا تابع MATCH در ستون A2:A6 که نام غذاها قرار دارد، موقعیت عبارت «همبرگر کلاسیک» را با تطبیق دقیق آن با اسامی جدول، پیدا می‌کند. نتیجه جستجو عدد «۲» است. سپس تابع INDEX دومین محدوده قیمت را از محدوده D2:D6 برمی‌گرداند.

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

جستجو به سمت چپ

با فرمول‌نویسی ترکیبی تابع INDEX و تابع MATCH می‌توانیم جستجو را به سمت چپ سلول مورد نظر هم انجام دهیم. این حالت جایگزین مناسبی برای تابع VLOOKUP است که از جستجوی دو طرفه پشتیبانی نمی‌کند.

به‌عنوان مثال در همان جدول غذاها می‌خواهیم نام غذای مربوط به کشور «مکزیک» را پیدا کنیم. در این حالت نام غذاها در سمت چپ نام کشورها قرار دارد. بنابراین فرمول زیر را می‌نویسیم.

=INDEX(A2:A6, MATCH("مکزیک", B2:B6, 0))

نتیجه نهایی نیز به شکل زیر درمی‌آید.

جستجوی چند شرطی

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

به‌عنوان مثال در جدول فرضی منوی رستوران می‌خواهیم نام غذایی را پیدا کنیم که کشور آن «هند» و تعداد آن «۳» است. برای این کار فرمول زیر را می‌نویسیم.

=INDEX(A2:A6,MATCH(1,(B2:B6="هند")*(C2:C6=3),0))

نتیجه به شکل زیر درمی‌آید.

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

• ابتدا در ستون B2:B6 شرط مربوط به کشور بررسی می‌شود. به این شکل که نتیجه تطابق شرط مقدار منطقی «True» یا «FALSE» است. در صورت برقراری شرط عدد «۱» خروجی اولین فرمول است.
• در مرحله بعد فرمول، شرط دوم یعنی تعداد غذا در ستون C2:C6 را بررسی می‌کند. خروجی فرمول در صورت تطابق با عدد «۳» مقدار «۱» است.
• در مرحله سوم عملگر * نتیجه شرط‌ها را در یکدیگر ضرب می‌کند. بنابراین وقتی هر دو شرط برقرار باشد، خروجی نهایی برابر «۱» است.
• تابع MATCH مطابق خروجی مرحله سوم، موقعیت عدد «۱» را در جدول استخراج می‌کند. نتیجه آن عدد «۵» است.
• در مرحله آخر نیز تابع INDEX نام غذایی که در موقعیت «۵» از محدوده A2:A6 قرار دارد را به‌عنوان خروجی نشان می‌دهد.

جستجو بین چند کاربرگ مختلف

ترکیب تابع INDEX و MATCH برای حالتی که همه داده‌ها در یک کاربرگ نیستند نیز کاربرد دارد.

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

حال می‌خواهیم قیمت «همبرگر کلاسیک» را از کاربرگ «منوی رستوران» پیدا کنیم و در کاربرگ «سفارش مشتری» بنویسیم. در این حالت فرمول زیر را در سلول B2 از کاربرگ اول می‌نویسیم.

=INDEX('منوی رستوران'!$D$2:$D$6,MATCH(A2,'منوی رستوران'!$A$2:$A$6,0))

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

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

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

مطلب پیشنهادی:

تابع XLookup در اکسل – آموزش کامل با مثال

شروع مطالعه

خطاهای رایج در فرمول‌نویسی با تابع INDEX در اکسل

بعضی مواقع به‌دلیل رعایت نکردن ترفندهای فرمول‌نویسی، با پیام خطا روبرو می‌شویم. برای تابع INDEX در اکسل دو خطای رایج ممکن است اتفاق بیفتد.

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

کلیک کنید

خطای !VALUE#

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

خطای !REF#

این خطا به دلایل زیر اتفاق می‌افتد.

• مقدار آرگومان row_num بیشتر از تعداد ردیف‌های محدوده تعیین شده باشد.
• مقدار آرگومان column_num بیشتر از تعداد ستون‌های محدوده انتخابی باشد.

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

جمع بندی پایانی

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