آموزش تابع INDIRECT در اکسل (به زبان ساده)

از تابع INDIRECT در اکسل برای ارجاع غیر مستقیم استفاده می شود. این تابع از یک آدرس، یک رشته متنی یا ترکیبی از هر دو برای برگشت مقدار از یک سلول یا یک محدوده دیگر استفاده می کند. چون به طور غیرمستقیم به سلول ها ارجاع می دهد، آن را INDIRECT به معنای “غیر مستقیم” نامگذاری کرده اند. این تابع در گروه توابع Lookup/Reference در اکسل قرار دارد و بیشتر زمانی مورد استفاده قرار می گیرد که باید یک رشته متنی به یک آدرس معتبر تبدیل شود.

در این آموزش شیوه استفاده از تابع INDIRECT را همراه با مثال های مختلف بررسی خواهیم کرد.

آموزش ویدیویی

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

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

تابع INDIRECT در اکسل

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

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

ساختار تابع:

INDIRECT(ref_text, [a1])

تابع INDIRECT حداکثر دو آرگومان دارد:

ref_text: تنها آرگومان الزامی تابع است. آدرس سلولی است که می تواند شامل یک ارجاع به سبک A1، یک ارجاع به سبک R1C1، مرجع نامگذاری شده و یا ارجاع به یک سلول به عنوان یک رشته متنی باشد.

A1: یک آرگومان اختیاری است. یک مقدار منطقی (TRUE یا FALSE) است که مشخص می کند چه نوع مرجعی در آرگومان ref_text استفاده شده است:

• اگر TRUE باشد یا مقدار نداشته باشد، سبک آدرس دهی A1 می باشد.
• اگر FALSE باشد، سبک آدرس دهی R1C1 می باشد.

نکته: اگر ref_text مرجع معتبری نباشد، تابع خطای #REF! را برمی گرداند!

نکته: اگر آرگومان ref_text به کتاب کار دیگری مراجعه کند، آن نیز همزمان باید باز باشد. در غیر اینصورت، خطای !REF# را نشان می دهد.

احتمالا گیج شدید؟ بهترین راه برای درک عملکرد تابع INDIRECT مشاهده و بررسی مثال های مختلف است. بنابراین در ادامه چندین مثال را توضیح خواهیم داد.

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

مثال ۱: تابع INDIRECT ساده

این یک مثال ساده از تابع INDIRECT می باشد:

= INDIRECT(D2)

این فرمول مقدار ‘۶۰۰’ در سلول G2 را در سلول A2 قرار می دهد. این کار را به طور غیرمستقیم انجام می دهد، ابتدا به سلول D2 مراجعه می کند و چون سلول D2 حاوی آدرس سلول G2 است به سلول G2 رفته و در نهایت مقدار آن را در سلول A2 قرار می دهد.

مثال ۲: تابع INDIRECT همراه با تابع SUM

در این مثال، داده هایی وجود دارد که مقادیر فروش را بر اساس نام منطقه (نام محدوده ها) برای ده نماینده فروش مختلف (سطرهای ۴ تا ۱۳) نشان می دهد. هدف این است که با نوشتن نام منطقه در سلول G4، مقدار کل فروش آن منطقه در سلول H4 نشان داده شود.

برای این کار می توان تابع SUM را با تابع INDIRECT ترکیب کرد. برای سریعتر و آسان تر شدن فرآیند، هر کدام از محدوده ها با یک اسم منطقه نامگذاری شده است (North, South, East, West)

مراحل زیر را دنبال کنید:

۱- روی سلول H4 کلیک کنید.

۲- فرمول زیر را در سلول وارد کنید،

=SUM(INDIRECT(G4))

این فرمول به داده سلول G4 ارجاع می دهد که در اینجا مقدار آن برابر با “North” است، سپس مجموع مقادیر محدوده سلول ها با نام “North” را محاسبه می کند.

۳- منطقه در سلول G4 به “West” تغییر می کند.

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

آموزش جامع و تصویری کار با توابع و فرمول نویسی در اکسل

مثال ۳: تابع INDIRECT با ارجاع به فرمت R1C1

در این مثال از تابع INDIRECT و ارجاع به سبک R1C1 برای دریافت آخرین مقدار در یک جدول استفاده شده است حتی اگر جدول توسعه یابد و بزرگتر شود.

آدرس دهی R1C1: ارجاع یا آدرس دهی R1C1 یک روش جایگزین برای ارجاع سلول در صفحه گسترده است. به احتمال زیاد هنگام کار در اکسل به طور پیشفرض از سبک آدرس دهی A1 استفاده می کنید. در این سبک، سلول ها با استفاده از حرف ستون و شماره سطر ارجاع داده می شوند، مثل B2، C10، D12 و …. آدرس دهی R1C1 مخفف Row و Column است و به جای استفاده از حرف برای ارجاع به ستون ها از عدد استفاده می شود،. به طور مثال ستون A معادل عدد ۱، ستون B معادل عدد ۲ و …. بنابراین آدرس R1C1 همان A1 می باشد یعنی سطر۱ و ستون۱٫

هدف در اینجا دستیابی به آخرین آمار فروش ماهانه (در حال حاضر ماه جولای در محدوده Jul) و قرار دادن آن در سلول J4 است. توجه داشته باشید که آمار ماه بعد (ماه آگوست Aug) به ستون I اضافه می شود. بنابراین باید اطمینان حاصل کنیم که فرمول مطابق با داده های جدید به روزرسانی می شود.

۱- روی سلول J4 کلیک کنید.

۲- فرمول زیر را در سلول وارد کنید:

=INDIRECT(“R15C”&COUNTA(15:15),FALSE)

این فرمول از آدرس دهی R1C1 برای یافتن کل (TOTAL) در ستون آخر استفاده می کند، در اینجا مقدار کل (TOTAL) در سطر ۱۵ است، تابع COUNTA، تعداد ستون های موجود در سطر ۱۵ را به دست می آورد. از این روش استفاده شده است تا در صورت توسعه جدول و افزودن داده ها در محدوده بعدی، فرمول همچنان به درستی کار کند. آرگومان FALSE به اکسل می گوید فرمول در حال استفاده از ارجاع به سبک R1C1 می باشد در حالیکه اگر این آرکومان TRUE باشد، نشان دهنده ارجاع از نوع A1 می باشد.

در صورتیکه داده ها به ستون I اضافه شوند، فرمول INDIRECT در سلول J4 به درستی به روزرسانی می شود و نتیجه صحیح را برمی گرداند.

مثال ۴: تابع INDIRECT و Data Validation

در این مثال، چند کشور وجود دارد که در آنها تور برگزار می شود و از هر کشور به بقیه کشور ها تور وجود دارد.

از تابع INDIRECT برای ایجاد دو لیست کشویی اعتبارسنجی داده های پویا استفاده می شود. یک لیست کشویی در سلول B14 ایجاد می شود که لیست کشورها (Countries) را شامل می شود و یک لیست کشویی در سلول B15 که تورهای موجود در کشور انتخاب شده در سلول B14 را نشان می دهد.

محدوده های زیر روی ستون ها نامگذاری شده اند:

‘Countries’, ‘Bali’, ‘India’, ‘Thailand’, ‘Australia’

۱- روی سلول B14 کلیک کنید.

۲- به تب Data در نوار ریبون بروید.

۳- از گروه Data Tools روی گزینه Data Validation کلیک کنید.

۴- List  را از لیست کشویی Allow انتخاب کنید.

۵- در کادر Source، = Countries را بنویسید.

۶- روی OK را کلیک کنید.

اکنون یک لیست کشویی در قسمت Country اضافه شده است.

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

۷- روی سلول B15 کلیک کنید.

۸- به تب Data در نوار ریبون بروید.

۹- از گروه Data Tools روی گزینه Data Validation کلیک کنید.

۱۰- List را از لیست کشویی Allow انتخاب کنید.

۱۱-در کادر Source، فرمول زیر را وارد کنید:

=INDIRECT($B$14)

فرمول INDIRECT به کشوری که در سلول B14 انتخاب شده ارجاع می دهد و آن را با محدوده نامگذاری شده با همان نام تطبیق می دهد تا لیست کشویی به درستی ساخته شود.

نتیجه گیری

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

لیست ۵۰ تا از کاربردی ترین فرمول های اکسل (توابع مهم)