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

پس تابع indirect چیست و چه کاری انجام میدهد؟ این سؤال بسیار سؤال مهمی است و امیدواریم در پایان این مطلب پاسخ صحیحی بدست آورید.

• تابع indirect در اکسل – ترکیب و کاربردهای اولیه
• مثال های فرمولی تابع indirect
  • ایجاد آدرس های غیر مستقیم از طریق مقادیر سلولی
  • تابع indirect همراه با محدوده های دارای نام
  • ایجاد یک آدرس پویا مربوط به یک شیت دیگر
  • ایجاد یک آدرس پویا مربوط به یک ورک بوک دیگر
  • جستجوی یک آدرس سلول
  • تابع indirect همراه با دیگر توابع اکسل(ROW, ADDRESS, VLOOKUP)
  • تابع indirect و data validation
• خطاهای احتمالی و مشکلات فرمول های indirect

تابع indirect در اکسل –  ترکیب و کاربردهای اولیه

همانطور که از نام این تابع مشخص است، تابع indirect به منظور آدرس دهی سلول ها ، محدوده ها و دیگر شیت ها یا کاربرگ ها به کار گرفته میشود. به عبارت دیگر، تابع indirect به شما اجازه می دهد به جای وارد کردن آدرس ها درون فرمول، یک آدرس سلول پویا ایجاد کنید. در نتیجه، میتوانید یک آدرس سلول درون فرمول را  تغییر دهید بدون اینکه نیاز باشد فرمول را تغییر دهید. همچنین، این آدرس indirect ، در صورت حذف یا اضافه کردن ردیف و ستون های جدید نیز دچار تغییر نمیشود.

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

پارامترهای تابع indirect

تابع indirect فقط دو پارامتر دارد، اولین پارامتر ضروری است و پارامتر دوم اختیاری است.

INDIRECT(ref_text, [a1])

Ref_text: یک آدرس سلول، یا ارجاع به یک سلول به صورت رشته متنی یا محدوده دارای نام.

A1: یک مقدار منطقی است که نشان میدهد چه نوع آدرسی در پارامتر اول وجود دارد.

• اگر true باشد یا خالی ، ref_text، بعنوان یک آدرس سلولی به سبک A1 در نظر گرفته میشود.
• اگر false باشد، ref_text بعنوان یک آدرس به سبک R1C1 درنظر گرفته میشود.

اگرچع نوع R1C1 در بعضی از حالات مفید است ولی شما احتمالاً در بسیاری از موارد مایل به استفاده از نوع A1 هستید. به هر حال، تقریباً تمام مثال های این مطلب از نوع A1 استفاده می کنند. در نتیجه ما پارامتر دوم را حذف میکنیم.

کاربرد های تابع indirect

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

فرض کنید، یک عدد 3 در سلول A1 دارید و در سلول C1 نیز رشته A1 قرار دارد. فرمول =INDIRECT(C1) را در یک سلول دیگر قرار دهید و ببینید چه اتفاقی می افتد.

• تابع INDIRECT به مقدار موجود در سلول c1 اشاره میکند.
• مقدار موجود در این سلول را میگیرد (A1) و مقدار موجود در سلول A1 را برمیگرداند.

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

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

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

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

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

ایجاد آدرس های غیرمستقیم از مقادیر سلولی

همانطور که به یاد دارید، پارامتر دوم تابع INDIRECT به دو صورت A1 و R1C1 بود. معمولاً شما نمیتوانید از هر دو استایل در یک شیت استفاده کنید و فقط میتوانید با دستور  File > Options > Formulas > R1C1 check box بین دو استایل جابجا شوید. و به همین دلیل کاربران اکسل به ندرت از روش R1C1 استفاده میکنند.

در یک فرمول INDIRECT میتوانید از هر کدام از این دو استایل در یک شیت استفاده کنید. پیش از اینکه جلوتر برویم، میبایست تفاوت بین A1 و R1C1 گفته شود.

استایل A1: نوع معمول آدرس در اکسل است که ابتدا به نام ستون و بعد به شماره ردیف اشاره میکند. برای مثال B2 به سلولی اشاره میکند که در تقاطع ستون B و ردیف 2 قرار دارد.

استایل R1C1: نوع آدرس دهی برعکس مورد قبل است. ابتدا ردیف ذکر میشود سپس ستون. که البته مقداری زمان میبرد تا به آن عادت کنید. برای مثال R4C1 به سلول A4 اشاره دارد که در ردیف 4 و ستون 1 است. اگر بعد از حرف هیچ عددی ذکر نشود، شماره ستون و ردیف یکسان است.

حال ببینیم تابع INDIRECT چگونه با دو نوع استایل ذکر شده کار میکند.

همانطور که در تصویر بالا می بینید، سه فرمول متفاوت INDIRECT یک نتیجه یکسان را برمیگردانند. متوجه دلیل این موضوع شده اید؟ شرط میبندم که متوجه شده اید : )

فرمول در سلول D1: =INDIRECT(C1)

این ساده ترین شکل ممکن است و فرمول به سلول C1 اشاره میکند، مقدار آن را که رشته متنی A2 است میگیرد و آن را تبدیل به آدرس سلول میکند به سمت سلول A2 میرود و مقدار آن را برمیگرداند.

فرمول موجود در سلول D3: =INDIRECT(C3,FALSE)

مقدار FALSE در پارامتر دوم نشان میدهد که می بایست از استایل R1C1 استفاده کرد. در نتیجه فرمول INDIRECT ما مقدار موجود در سلول C3 را، سلول موجود در ردیف 2 و ستون 1 تفسیر میکند که همان سلول A2 است.

فرمول موجود در سلول D5 :  =INDIRECT(C5,FALSE)

شبیه به مثال قبل، این فرمول INDIRECT نیز در پارامتر دوم خود مقدار FALSE دارد.به این معنی که از شیوه R1C1 استفاده میکند. در نتیجه فرمول مقدار موجود در سلول C5 یعنی R3C را میگیرد و آن را تبدیل به آدرس C3 میکند(ردیف 3 و ستون 3).

همچنین اگر به جای R3C از R3C3 استفاده میشد نتیجه یکسان بود و مقدار موجود در سلول C3(R2C1) برگردانده میشد. صادقانه بگویم که نتوانستم توضیح خاصی برای این اتفاق بدست آورم شاید این نیز یکی از خصوصیات تابع INDIRECT است.

ایجاد آدرس های غیر مستقیم از طریق مقادیر سلولی و متن

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

در مثال بعد، فرمول  =INDIRECT("B"&C2) بر اساس زنجیره منطقی زیر یک مقدار را از سلول B2 برمیگرداند:

تابع INDIRECT عناصر موجود در پارامتر REF_TEXT را بهم متصل میکند متن B و مقدار موجود در سلول C2 ->مقدار موجود در سلول C2 عدد 2 است، که در نتیجه آدرس B2 ساخته میشود -> فرمول به سلول B2 میرود و مقدار آنرا برمیگرداند که عدد 10 است.

استفاده از تابع INDIRECT همراه با محدوده های دارای نام

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

فرض کنید محدوده های زیر را در شیت خود در اختیار دارید:

• Apples – B2:B6
• Bananas – C2:C6
• Lemons – D2:D6

برای ساخت یک آدرس پویا برای هرکدام از محدوده های بالا، فقط کافیست نام آن محدوده را در یک سلول وارد کنید و با استفاده از یک فرمول indirect به آن سلول اشاره کنید =INDIRECT(G1).

حال میتوانید فراتر روید و این تابع indirect را درون دیگر توابع اکسل قرار دهید تا مجموع و میانگین این محدوده را بدست آورید یا مقدار حداکثر و حداقل را در محدوده مورد نظر بدست آورید.

=SUM(INDIRECT(G1))

=AVERAGE(INDIRECT(G1))

=MAX(INDIRECT(G1))

=MIN(INDIRECT(G1))

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

ایجاد یک آدرس پویا مربوط به یک شیت دیگر

کاربردهای تابع INDIRECT محدود به ایجاد آدرس های پویا نیست. شما میتوانید با استفاده از این فرمول به سلول های دیگر شیت ها اشاره کنید. در زیر نحوه این کار توضیح داده شده است:

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

برای درک بهتر فرمولی که در تصویر استفاده شده است آن را به اجزای کوچکتر تقسیم میکنیم:

همانطور که میدانید، راه معمول برای ارجاع به یک شیت دیگر در اکسل،  نوشتن نام شیت سپس علامت تعجب و در پایان آدرس سلول مورد نظر است. از آنجایی که نام شیت معمولاً دارای فاصله است، بهتر است آن را در درون علامت ‘ ‘ قرار دهید تا از بروز خطا جلوگیری شود. برای مثال ‘My Sheet!’$A$1

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

با توجه به مطالب بالا به الگوی زیر می رسیم:

=INDIRECT("'" & Sheet's name & "'!" & Cell to pull data from)

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

=INDIRECT("'" & $A$1 & "'!" & B1)

همچنین به یاد داشته باشید که اگر فرمول را به دیگر سلول ها کپی میکنید، می بایست با استفاده از آدرس های مطلق ، آدرس را به نام شیت، قفل کنید برای مثال $A$1

نکته:

• اگر هر کدام از سلول هایی که شامل نام شیت دوم و آدرس سلول (A1 و B1 در فرمول بالا) میشوند، خالی باشند، فرمول شما یک خطا را برمیگرداند. برای جلوگیری از این خطا، میتوانید فرمول indirect را درون تابع if قرار دهید:
• IF(OR($A$1="",B1=""), "", INDIRECT("'" & $A$1 & "'!" & B1))
• برای اینکه فرمول indirect که به یک شیت دیگر اشاره میکند، به درستی کار کند می بایست شیت مورد اشاره باز باشد. در غیر اینصورت فرمول خطای #ref! را برمیگرداند. برای اینکه این خطا برگردانده نشود، میتوانید از تابع iferror استفاده کنید که در صورت وقوع خطا یک رشته خالی را برمیگرداند.

=IFERROR(INDIRECT("'" & $A$1 & "'!" &B1), "")

ایجاد یک آدرس پویا در اکسل از یک ورک بوک دیگر

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

برای آسان تر کردن کار، به ساخت آدرس یک ورک بوک دیگر به طریق معمول میپردازیم (در صورتی که نام شیت یا ورک بوک شما شامل فاصله باشد از علامت ‘ استفاده میکنیم):

'[Book_name.xlsx]Sheet_name'!Range

با فرض اینکه نام ورک بوک در سلول A2 قرار دارد، نام شیت در سلول B2 و آدرس سلول در سلول C2 باشد، فرمول زیر بدست می آید:

=INDIRECT("'[" & $A$2 & "]" & $B$2 & "'!" & C2)

از آنجایی که نمیخواهید در زمان کپی کردن آدرس به سلول های دیگر نام شیت و ورک بوک تغییر کند، می بایست با استفاده از آدرس دهی مطلق، آن را ثابت کنید.(به ترتیب $A$2 و $B$2)

حال، میتوانید آدرس پویای خود را برای یک ورک بوک دیگر با استفاده از الگوی زیر بنویسید:

=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)

نکته:

ورک بوکی که فرمول شما به آن اشاره میکند می بایست همیشه باز باشد، در غیر اینصورت، فرمول indirect خطای #ref! را برمیگرداند. مثل همیشه برای جلوگیری از بروز این اتفاق میتوانید از تابع iferror استفاده کنید:

=IFERROR(INDIRECT("'[" & A2 & "]" & $A$1 & "'!" & B1), "")

استفاده از تابع indirect اکسل برای قفل کردن یک آدرس سلول

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

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

1. یک مقدار را در یک سلول وارد کنید. برای مثال ، عدد 20 در سلول A1
2. در دو سلول دیگر به دو شکل متفاوت به سلول A1 اشاره کنید:   =A1 و  =INDIRECT("A1")
3. یک ردیف جدی بالای ردیف 1 اضافه کنید.

مشاهده کردید که چه اتفاق افتاد؟ سلولی که با علامت مساوی اشاره کرده بود هنوزه 20 باقی ماند، زیرا فرمول آن به صورت اتوماتیک به =A2 تغییر یافت. سلولی که دارای فرمول indirect بود حال مقدار 0 را برمیگرداند. زیرا با اضافه کردن ردیف جدید فرمول تغییری نکرد و هنوزه مقدار سلول A1 را برمیگرداند که در حال حاضر خالی است:

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

فرض کنید، مقدار مجموع سلول های A2:A4 را میخواهید.و میتوانید به راحتی این کار را با استفاده از تابع sum انجام دهید:

=SUM(A2:A5)

با اینحال به فرمولی نیاز دارید که در صورت اضافه یا حذف ردیف هیچ تغییری نکند. واضح ترین راه حل این است که از آدرس های ثابت استفاده کنیم. ولی این کار مشکل را حل نمیکند. برای اطمینان از این موضوع فرمول  =SUM($A$2:$A$5) را در یک سلول وارد کنید، یک ردیف جدید اضافه کنید، برای مثال در ردیف 3. می بینید که فرمول تبدیل به =SUM($A$2:$A$6) میشود.

البته این ویژگی اکسل در بسیاری از موارد کمک کننده است. با این وجود، شرایطی وجود دارد که شما مایل نیستید فرمولتان در موقعیت های مختلف تغییر کند.راه حل استفاده از تابع indirect است. مثل:

=SUM(INDIRECT("A2:A5"))

از آنجایی که اکسل  “A1:A5” را یک رشته متن میداند و نه آدرس سلول. در زمانی که ردیف هایی را حذف یا اضافه میکنید ، آن را هیچ تغییری نمیدهد.

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

علاوه بر sum، تابع indirect با بسیاری از دیگر توابع اکسل مثل row، column، address، vlookup، sumif نیز ترکیب میشود.

مثال1. توابع indirect و row

اغلب، از تابع row برای برگرداندن آرایه ای از مقادیر در اکسل استفاده میشود. برای مثال، میتوانید از فرمول آرایه زیر برای برگرداندن میانگین سه عدد کوچکتر در محدوده  A1:A10 استفاده کنید:

=AVERAGE(SMALL(A1:A10,ROW(1:3)))

به یاد داشته باشید که در فرمول های آرایه ای می بایست از کلید های ترکیبی Ctrl + Shift + Enter استفاده کرد.

با اینحال، اگر یک ردیف جدید در هر جایی بین ردیف 1 و 3  به ورک شیت خود اضافه کنید، محدوده موجود در فرمول به ROW(1:4) تغییر خواهد کرد و فرمول به جای سه عدد کوچکتر میانگین چهار عدد کوچکتر را بدست خواهد آورد.

برای جلوگیری از بروز این خطا، تابع indirect را درون row قرار دهید. در این صورت هر تعداد ردیف که اضافه یا حذف شود فرمول هیچ تغییری نخواهد کرد:

=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:3"))))

اگر میخواهید تابع vlookup را بشناسید،کلیک کنید: تابع VLOOKUP در اکسل | مثال ها و کاربردها

مثال2. تابع indirect همراه با تابع address

شما میتوانید با ترکیب تابع indirect و address مقدار یک سلول در حال تغییر را بدست آورید.

اگر به یاد داشته باشید، از فرمول address برای بدست آوردن آدرس یک سلول با استفاده از شماره ردیف و ستون استفاده میشد. برای مثال فرمول، =ADDRESS(1,3) رشته $C$1  را برمیگرداند زیرا C1 سلولی است که در تقاطع ردیف اول و ستون سوم قرار دارد.

استفاده از تابع indirect همراه با data validation در اکسل

شمامیتوانید با ترکیب تابع indirect و خاصیت data validation اکسل، لیست های زنجیره ای بسازید که مقادیر را بر اساس مقدار انتخاب شده توسط کاربر در اولین لیست نشان میدهند.

ساخت یک لیست کشویی وابسته ساده بسیار آسان است. فقط کافیست چند محدوده دارای نام ایجاد کنید و عناصری که می بایست در لیست قرار بگیرد را در آن بگنجانید. و یک فرمول ساده  =INDIRECT(A2) بنویسید که در آن A2 سلولی است که لیست کشویی اول شما در آن قرار دارد.

برای ساخت لیست های کشویی پیچیده تر و چند کلمه ای و یا حتی دارای 3 سطح یا بیشتر، نیاز به فرمول های پیچیده تر و ترکیب indirect و Substitute است.

تابع indirect در اکسل – خطاهای احتمالی و مشکلات

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

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

خطای #ref در تابع indirect اکسل

1. Ref_text یک آدرس سلول معتبر نیست: اگر این پارامتردر فرمول indirect شما یک آدرس معتبر نباشد، خطای #ref! برگردانده میشود. برای حل این مشکل لطفا پارامتر های تابع indirect خود را بررسی کنید.
2. محدودیت در محدوده: اگر پارامتر ref_text تابع indirect شما محدوده ای را اشاره کند که در حدود 1048576 سطر و 16384 ستون قرار نگیرد، تابع در ورژن های 2007، 2010 و 2013خطای ref را برمیگرداند. در ورژن های قبلتر، تابع خطا را برنمیگرداند ولی مقداری برگردانده میشود که به احتمال زیاد مقدار مورد نظر شما نیست.
3. شیت یا ورک بوک مورد نظر بسته است: اگر فرمول indirect شما به شیت یا بوک دیگری اشاره میکند می بایست، آن ورک بوک یا ورک شیت باز باشد در غیر اینصورت خطای #ref! برگردانده میشود/

خطای #name? در تابع indirect

این خطا بسیار واضح است. به این معنی که در نام تابع یک اشتباه وجود دارد.

استفاده از تابع indirect در مناطق غیر انگلیسی زبان

ممکن است برایتان جالب باشد که معادل تابع indirect به 14 زبان دنیا وجود دارد. از جمله:

یکی از مشکلات غیر انگلیسی زبان ها نام تابع indirect نیست بلکه ، تنظیمات منطقه ای برای جدا کننده های لیست است. در تنظیمات استاندارد ویندوز برای آمریکای شمالی و بعضی دیگر از کشورها،  جداکننده لیست، یک کاما است. در حالی که برای کشورهای اروپایی جداکننده نقطه ویرگول است.

در نتیجه، وقتی یک فرمول را در دو نقطه جغرافیایی کپی میکنید، با خطای “We found a problem with this formula…” مواجه میشوید. زیرا جداکننده فرمول با چیزی که در کامپیوتر شماست متفاوت است. در صورتی که زمان کپی کردن فرمول های این مطلب با چنین خطایی مواجه شدید، فقط کافیست کاما(,) را با نقطه ویرگول(;) جایگزین کنید.

برای بررسی اینکه سیستم شما از کدام جداکننده استفاده میکند، از مسیر Control Panel> Region and Language > Additional Settings. استفاده کنید.

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

پس تابع indirect چیست و چه کاری انجام میدهد؟ این سؤال بسیار سؤال مهمی است و امیدواریم در پایان این مطلب پاسخ صحیحی بدست آورید.

• تابع indirect در اکسل – ترکیب و کاربردهای اولیه
• مثال های فرمولی تابع indirect
  • ایجاد آدرس های غیر مستقیم از طریق مقادیر سلولی
  • تابع indirect همراه با محدوده های دارای نام
  • ایجاد یک آدرس پویا مربوط به یک شیت دیگر
  • ایجاد یک آدرس پویا مربوط به یک ورک بوک دیگر
  • جستجوی یک آدرس سلول
  • تابع indirect همراه با دیگر توابع اکسل(ROW, ADDRESS, VLOOKUP)
  • تابع indirect و data validation
• خطاهای احتمالی و مشکلات فرمول های indirect

تابع indirect در اکسل –  ترکیب و کاربردهای اولیه

همانطور که از نام این تابع مشخص است، تابع indirect به منظور آدرس دهی سلول ها ، محدوده ها و دیگر شیت ها یا کاربرگ ها به کار گرفته میشود. به عبارت دیگر، تابع indirect به شما اجازه می دهد به جای وارد کردن آدرس ها درون فرمول، یک آدرس سلول پویا ایجاد کنید. در نتیجه، میتوانید یک آدرس سلول درون فرمول را  تغییر دهید بدون اینکه نیاز باشد فرمول را تغییر دهید. همچنین، این آدرس indirect ، در صورت حذف یا اضافه کردن ردیف و ستون های جدید نیز دچار تغییر نمیشود.

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

پارامترهای تابع indirect

تابع indirect فقط دو پارامتر دارد، اولین پارامتر ضروری است و پارامتر دوم اختیاری است.

INDIRECT(ref_text, [a1])

Ref_text: یک آدرس سلول، یا ارجاع به یک سلول به صورت رشته متنی یا محدوده دارای نام.

A1: یک مقدار منطقی است که نشان میدهد چه نوع آدرسی در پارامتر اول وجود دارد.

• اگر true باشد یا خالی ، ref_text، بعنوان یک آدرس سلولی به سبک A1 در نظر گرفته میشود.
• اگر false باشد، ref_text بعنوان یک آدرس به سبک R1C1 درنظر گرفته میشود.

اگرچع نوع R1C1 در بعضی از حالات مفید است ولی شما احتمالاً در بسیاری از موارد مایل به استفاده از نوع A1 هستید. به هر حال، تقریباً تمام مثال های این مطلب از نوع A1 استفاده می کنند. در نتیجه ما پارامتر دوم را حذف میکنیم.

کاربرد های تابع indirect

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

فرض کنید، یک عدد 3 در سلول A1 دارید و در سلول C1 نیز رشته A1 قرار دارد. فرمول =INDIRECT(C1) را در یک سلول دیگر قرار دهید و ببینید چه اتفاقی می افتد.

• تابع INDIRECT به مقدار موجود در سلول c1 اشاره میکند.
• مقدار موجود در این سلول را میگیرد (A1) و مقدار موجود در سلول A1 را برمیگرداند.

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

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

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

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

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

ایجاد آدرس های غیرمستقیم از مقادیر سلولی

همانطور که به یاد دارید، پارامتر دوم تابع INDIRECT به دو صورت A1 و R1C1 بود. معمولاً شما نمیتوانید از هر دو استایل در یک شیت استفاده کنید و فقط میتوانید با دستور  File > Options > Formulas > R1C1 check box بین دو استایل جابجا شوید. و به همین دلیل کاربران اکسل به ندرت از روش R1C1 استفاده میکنند.

در یک فرمول INDIRECT میتوانید از هر کدام از این دو استایل در یک شیت استفاده کنید. پیش از اینکه جلوتر برویم، میبایست تفاوت بین A1 و R1C1 گفته شود.

استایل A1: نوع معمول آدرس در اکسل است که ابتدا به نام ستون و بعد به شماره ردیف اشاره میکند. برای مثال B2 به سلولی اشاره میکند که در تقاطع ستون B و ردیف 2 قرار دارد.

استایل R1C1: نوع آدرس دهی برعکس مورد قبل است. ابتدا ردیف ذکر میشود سپس ستون. که البته مقداری زمان میبرد تا به آن عادت کنید. برای مثال R4C1 به سلول A4 اشاره دارد که در ردیف 4 و ستون 1 است. اگر بعد از حرف هیچ عددی ذکر نشود، شماره ستون و ردیف یکسان است.

حال ببینیم تابع INDIRECT چگونه با دو نوع استایل ذکر شده کار میکند.

همانطور که در تصویر بالا می بینید، سه فرمول متفاوت INDIRECT یک نتیجه یکسان را برمیگردانند. متوجه دلیل این موضوع شده اید؟ شرط میبندم که متوجه شده اید : )

فرمول در سلول D1: =INDIRECT(C1)

این ساده ترین شکل ممکن است و فرمول به سلول C1 اشاره میکند، مقدار آن را که رشته متنی A2 است میگیرد و آن را تبدیل به آدرس سلول میکند به سمت سلول A2 میرود و مقدار آن را برمیگرداند.

فرمول موجود در سلول D3: =INDIRECT(C3,FALSE)

مقدار FALSE در پارامتر دوم نشان میدهد که می بایست از استایل R1C1 استفاده کرد. در نتیجه فرمول INDIRECT ما مقدار موجود در سلول C3 را، سلول موجود در ردیف 2 و ستون 1 تفسیر میکند که همان سلول A2 است.

فرمول موجود در سلول D5 :  =INDIRECT(C5,FALSE)

شبیه به مثال قبل، این فرمول INDIRECT نیز در پارامتر دوم خود مقدار FALSE دارد.به این معنی که از شیوه R1C1 استفاده میکند. در نتیجه فرمول مقدار موجود در سلول C5 یعنی R3C را میگیرد و آن را تبدیل به آدرس C3 میکند(ردیف 3 و ستون 3).

همچنین اگر به جای R3C از R3C3 استفاده میشد نتیجه یکسان بود و مقدار موجود در سلول C3(R2C1) برگردانده میشد. صادقانه بگویم که نتوانستم توضیح خاصی برای این اتفاق بدست آورم شاید این نیز یکی از خصوصیات تابع INDIRECT است.

ایجاد آدرس های غیر مستقیم از طریق مقادیر سلولی و متن

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

در مثال بعد، فرمول  =INDIRECT("B"&C2) بر اساس زنجیره منطقی زیر یک مقدار را از سلول B2 برمیگرداند:

تابع INDIRECT عناصر موجود در پارامتر REF_TEXT را بهم متصل میکند متن B و مقدار موجود در سلول C2 ->مقدار موجود در سلول C2 عدد 2 است، که در نتیجه آدرس B2 ساخته میشود -> فرمول به سلول B2 میرود و مقدار آنرا برمیگرداند که عدد 10 است.

استفاده از تابع INDIRECT همراه با محدوده های دارای نام

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

فرض کنید محدوده های زیر را در شیت خود در اختیار دارید:

• Apples – B2:B6
• Bananas – C2:C6
• Lemons – D2:D6

برای ساخت یک آدرس پویا برای هرکدام از محدوده های بالا، فقط کافیست نام آن محدوده را در یک سلول وارد کنید و با استفاده از یک فرمول indirect به آن سلول اشاره کنید =INDIRECT(G1).

حال میتوانید فراتر روید و این تابع indirect را درون دیگر توابع اکسل قرار دهید تا مجموع و میانگین این محدوده را بدست آورید یا مقدار حداکثر و حداقل را در محدوده مورد نظر بدست آورید.

=SUM(INDIRECT(G1))

=AVERAGE(INDIRECT(G1))

=MAX(INDIRECT(G1))

=MIN(INDIRECT(G1))

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

ایجاد یک آدرس پویا مربوط به یک شیت دیگر

کاربردهای تابع INDIRECT محدود به ایجاد آدرس های پویا نیست. شما میتوانید با استفاده از این فرمول به سلول های دیگر شیت ها اشاره کنید. در زیر نحوه این کار توضیح داده شده است:

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

برای درک بهتر فرمولی که در تصویر استفاده شده است آن را به اجزای کوچکتر تقسیم میکنیم:

همانطور که میدانید، راه معمول برای ارجاع به یک شیت دیگر در اکسل،  نوشتن نام شیت سپس علامت تعجب و در پایان آدرس سلول مورد نظر است. از آنجایی که نام شیت معمولاً دارای فاصله است، بهتر است آن را در درون علامت ‘ ‘ قرار دهید تا از بروز خطا جلوگیری شود. برای مثال ‘My Sheet!’$A$1

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

با توجه به مطالب بالا به الگوی زیر می رسیم:

=INDIRECT("'" & Sheet's name & "'!" & Cell to pull data from)

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

=INDIRECT("'" & $A$1 & "'!" & B1)

همچنین به یاد داشته باشید که اگر فرمول را به دیگر سلول ها کپی میکنید، می بایست با استفاده از آدرس های مطلق ، آدرس را به نام شیت، قفل کنید برای مثال $A$1

نکته:

• اگر هر کدام از سلول هایی که شامل نام شیت دوم و آدرس سلول (A1 و B1 در فرمول بالا) میشوند، خالی باشند، فرمول شما یک خطا را برمیگرداند. برای جلوگیری از این خطا، میتوانید فرمول indirect را درون تابع if قرار دهید:
• IF(OR($A$1="",B1=""), "", INDIRECT("'" & $A$1 & "'!" & B1))
• برای اینکه فرمول indirect که به یک شیت دیگر اشاره میکند، به درستی کار کند می بایست شیت مورد اشاره باز باشد. در غیر اینصورت فرمول خطای #ref! را برمیگرداند. برای اینکه این خطا برگردانده نشود، میتوانید از تابع iferror استفاده کنید که در صورت وقوع خطا یک رشته خالی را برمیگرداند.

=IFERROR(INDIRECT("'" & $A$1 & "'!" &B1), "")

ایجاد یک آدرس پویا در اکسل از یک ورک بوک دیگر

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

برای آسان تر کردن کار، به ساخت آدرس یک ورک بوک دیگر به طریق معمول میپردازیم (در صورتی که نام شیت یا ورک بوک شما شامل فاصله باشد از علامت ‘ استفاده میکنیم):

'[Book_name.xlsx]Sheet_name'!Range

با فرض اینکه نام ورک بوک در سلول A2 قرار دارد، نام شیت در سلول B2 و آدرس سلول در سلول C2 باشد، فرمول زیر بدست می آید:

=INDIRECT("'[" & $A$2 & "]" & $B$2 & "'!" & C2)

از آنجایی که نمیخواهید در زمان کپی کردن آدرس به سلول های دیگر نام شیت و ورک بوک تغییر کند، می بایست با استفاده از آدرس دهی مطلق، آن را ثابت کنید.(به ترتیب $A$2 و $B$2)

حال، میتوانید آدرس پویای خود را برای یک ورک بوک دیگر با استفاده از الگوی زیر بنویسید:

=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)

نکته:

ورک بوکی که فرمول شما به آن اشاره میکند می بایست همیشه باز باشد، در غیر اینصورت، فرمول indirect خطای #ref! را برمیگرداند. مثل همیشه برای جلوگیری از بروز این اتفاق میتوانید از تابع iferror استفاده کنید:

=IFERROR(INDIRECT("'[" & A2 & "]" & $A$1 & "'!" & B1), "")

استفاده از تابع indirect اکسل برای قفل کردن یک آدرس سلول

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

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

1. یک مقدار را در یک سلول وارد کنید. برای مثال ، عدد 20 در سلول A1
2. در دو سلول دیگر به دو شکل متفاوت به سلول A1 اشاره کنید:   =A1 و  =INDIRECT("A1")
3. یک ردیف جدی بالای ردیف 1 اضافه کنید.

مشاهده کردید که چه اتفاق افتاد؟ سلولی که با علامت مساوی اشاره کرده بود هنوزه 20 باقی ماند، زیرا فرمول آن به صورت اتوماتیک به =A2 تغییر یافت. سلولی که دارای فرمول indirect بود حال مقدار 0 را برمیگرداند. زیرا با اضافه کردن ردیف جدید فرمول تغییری نکرد و هنوزه مقدار سلول A1 را برمیگرداند که در حال حاضر خالی است:

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

فرض کنید، مقدار مجموع سلول های A2:A4 را میخواهید.و میتوانید به راحتی این کار را با استفاده از تابع sum انجام دهید:

=SUM(A2:A5)

با اینحال به فرمولی نیاز دارید که در صورت اضافه یا حذف ردیف هیچ تغییری نکند. واضح ترین راه حل این است که از آدرس های ثابت استفاده کنیم. ولی این کار مشکل را حل نمیکند. برای اطمینان از این موضوع فرمول  =SUM($A$2:$A$5) را در یک سلول وارد کنید، یک ردیف جدید اضافه کنید، برای مثال در ردیف 3. می بینید که فرمول تبدیل به =SUM($A$2:$A$6) میشود.

البته این ویژگی اکسل در بسیاری از موارد کمک کننده است. با این وجود، شرایطی وجود دارد که شما مایل نیستید فرمولتان در موقعیت های مختلف تغییر کند.راه حل استفاده از تابع indirect است. مثل:

=SUM(INDIRECT("A2:A5"))

از آنجایی که اکسل  “A1:A5” را یک رشته متن میداند و نه آدرس سلول. در زمانی که ردیف هایی را حذف یا اضافه میکنید ، آن را هیچ تغییری نمیدهد.

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

علاوه بر sum، تابع indirect با بسیاری از دیگر توابع اکسل مثل row، column، address، vlookup، sumif نیز ترکیب میشود.

مثال1. توابع indirect و row

اغلب، از تابع row برای برگرداندن آرایه ای از مقادیر در اکسل استفاده میشود. برای مثال، میتوانید از فرمول آرایه زیر برای برگرداندن میانگین سه عدد کوچکتر در محدوده  A1:A10 استفاده کنید:

=AVERAGE(SMALL(A1:A10,ROW(1:3)))

به یاد داشته باشید که در فرمول های آرایه ای می بایست از کلید های ترکیبی Ctrl + Shift + Enter استفاده کرد.

با اینحال، اگر یک ردیف جدید در هر جایی بین ردیف 1 و 3  به ورک شیت خود اضافه کنید، محدوده موجود در فرمول به ROW(1:4) تغییر خواهد کرد و فرمول به جای سه عدد کوچکتر میانگین چهار عدد کوچکتر را بدست خواهد آورد.

برای جلوگیری از بروز این خطا، تابع indirect را درون row قرار دهید. در این صورت هر تعداد ردیف که اضافه یا حذف شود فرمول هیچ تغییری نخواهد کرد:

=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:3"))))

اگر میخواهید تابع vlookup را بشناسید،کلیک کنید: تابع VLOOKUP در اکسل | مثال ها و کاربردها

مثال2. تابع indirect همراه با تابع address

شما میتوانید با ترکیب تابع indirect و address مقدار یک سلول در حال تغییر را بدست آورید.

اگر به یاد داشته باشید، از فرمول address برای بدست آوردن آدرس یک سلول با استفاده از شماره ردیف و ستون استفاده میشد. برای مثال فرمول، =ADDRESS(1,3) رشته $C$1  را برمیگرداند زیرا C1 سلولی است که در تقاطع ردیف اول و ستون سوم قرار دارد.

استفاده از تابع indirect همراه با data validation در اکسل

شمامیتوانید با ترکیب تابع indirect و خاصیت data validation اکسل، لیست های زنجیره ای بسازید که مقادیر را بر اساس مقدار انتخاب شده توسط کاربر در اولین لیست نشان میدهند.

ساخت یک لیست کشویی وابسته ساده بسیار آسان است. فقط کافیست چند محدوده دارای نام ایجاد کنید و عناصری که می بایست در لیست قرار بگیرد را در آن بگنجانید. و یک فرمول ساده  =INDIRECT(A2) بنویسید که در آن A2 سلولی است که لیست کشویی اول شما در آن قرار دارد.

برای ساخت لیست های کشویی پیچیده تر و چند کلمه ای و یا حتی دارای 3 سطح یا بیشتر، نیاز به فرمول های پیچیده تر و ترکیب indirect و Substitute است.

تابع indirect در اکسل – خطاهای احتمالی و مشکلات

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

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

خطای #ref در تابع indirect اکسل

1. Ref_text یک آدرس سلول معتبر نیست: اگر این پارامتردر فرمول indirect شما یک آدرس معتبر نباشد، خطای #ref! برگردانده میشود. برای حل این مشکل لطفا پارامتر های تابع indirect خود را بررسی کنید.
2. محدودیت در محدوده: اگر پارامتر ref_text تابع indirect شما محدوده ای را اشاره کند که در حدود 1048576 سطر و 16384 ستون قرار نگیرد، تابع در ورژن های 2007، 2010 و 2013خطای ref را برمیگرداند. در ورژن های قبلتر، تابع خطا را برنمیگرداند ولی مقداری برگردانده میشود که به احتمال زیاد مقدار مورد نظر شما نیست.
3. شیت یا ورک بوک مورد نظر بسته است: اگر فرمول indirect شما به شیت یا بوک دیگری اشاره میکند می بایست، آن ورک بوک یا ورک شیت باز باشد در غیر اینصورت خطای #ref! برگردانده میشود/

خطای #name? در تابع indirect

این خطا بسیار واضح است. به این معنی که در نام تابع یک اشتباه وجود دارد.

استفاده از تابع indirect در مناطق غیر انگلیسی زبان

ممکن است برایتان جالب باشد که معادل تابع indirect به 14 زبان دنیا وجود دارد. از جمله:

یکی از مشکلات غیر انگلیسی زبان ها نام تابع indirect نیست بلکه ، تنظیمات منطقه ای برای جدا کننده های لیست است. در تنظیمات استاندارد ویندوز برای آمریکای شمالی و بعضی دیگر از کشورها،  جداکننده لیست، یک کاما است. در حالی که برای کشورهای اروپایی جداکننده نقطه ویرگول است.

در نتیجه، وقتی یک فرمول را در دو نقطه جغرافیایی کپی میکنید، با خطای “We found a problem with this formula…” مواجه میشوید. زیرا جداکننده فرمول با چیزی که در کامپیوتر شماست متفاوت است. در صورتی که زمان کپی کردن فرمول های این مطلب با چنین خطایی مواجه شدید، فقط کافیست کاما(,) را با نقطه ویرگول(;) جایگزین کنید.

برای بررسی اینکه سیستم شما از کدام جداکننده استفاده میکند، از مسیر Control Panel> Region and Language > Additional Settings. استفاده کنید.

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