تابع INDIRECT

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

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

آرگومان های تابع

تابع INDIRECT تنها دو آرگومان دارد، اولی اجباری است، در حالی که دوم اختیاری است:

INDIRECT (ref_text، [a1])

ref_text - یک مرجع سلولی یا یک مرجع به یک سلول در قالب یک رشته متن یا محدوده نامیده می شود.

a1 - یک مقدار منطقی است که مشخص میکند چه نوع مرجع در argument argument ref_text وجود دارد:

• اگر TRUE یا حذف شود، ref_text به عنوان یک مرجع سلولی A1 تفسیر می شود.
• اگر FALSE، ref_text به عنوان مرجع R1C1 رفتار شود.

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

استفاده عمومی تابع INDIRECT

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

را در یک سلول دیگر قرار دهید و ببینید چه اتفاقی می افتد:

• تابع INDIRECT به مقدار در سلول C1 اشاره می کند که A1 است.
• این تابع به سلول A1 منتقل می شود که مقدار آن را برای بازگشت به ارمغان می آورد، که شماره 3 است.

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

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

نحوه استفاده از INDIRECT در اکسل - نمونه فرمول

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

در هنگام کار با داده های واقعی، تابع NETIRECT می تواند هر رشته متنی را به مرجع تبدیل کند که از جمله رشته های بسیار پیچیده ای است که شما با استفاده از مقادیر سلول های دیگر و نتایج های دیگر فرمول های اکسل به دست می آورید. اما اجازه دهید سبد خرید را قبل از اسب سوار نکنیم!  و از طریق چندین فرمول اکسل غیر مستقیم، یک بار در یک زمان اجرا کنیم.

ایجاد منابع غیر مستقیم از ارزش سلولی

 

همانطور که شما به یاد می آورید، عملکرد اکسل INDIRECT اجازه می دهد تا برای سبک های مرجع A1 و R1C1 انتخاب کنید. معمولا، شما نمی توانید از هر دو سبک در یک ورق تنها در یک زمان استفاده کنید، فقط می توانید بین دو نوع مرجع را از طریق کادر انتخاب File> Options> Formulas > R1C1 تغییر دهید . به همین دلیل است که کاربران اکسل به ندرت به استفاده از R1C1 به عنوان روش ارجاع جایگزین در نظر می گیرند.

در فرمول غیر مستقیم، اگر شما بخواهید، می توانید از هر نوع مرجع در همان ورق استفاده کنید. قبل از اینکه بیشتر جلو بروید، ممکن است بخواهید تفاوت بین سبک های مرجع A1 و R1C1 را بدانید.

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

سبک R1C1 نوع مرجع مخالف است - ردیف ها به دنبال ستون ها، که زمان زیادی برای استفاده می شود:) به عنوان مثال، R4C1 به سلول A4 اشاره دارد که در ستون 1 در یک ردیف 4 قرار دارد. اگر شماره ای بعد از این نامه نباشد، پس شما به همان ردیف یا ستون اشاره می کنید.

و اکنون، بیایید ببینیم که چگونه عملکرد بی نظیر A1 و R1C1 مراجع: 

همانطور که در تصویر بالا مشاهده می کنید، سه فرمول INDIRECT نتیجه همان نتیجه را نشان می دهد. آیا شما قبلا فهمید چرا؟ 

فرمول در سلول D1:

 =INDIRECT(C1)

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

فرمول در سلول D3:

 =INDIRECT(C3,FALSE)

FALSE در استدلال 2 نشان می دهد که مقدار ارجاع شده (C3) باید مانند یک مرجع سلولی R1C1، یعنی شماره ردیف و شماره ستون، رفتار شود. بنابراین، فرمول INDIRECT ما ارزش سلول C3 (R2C1)

را به عنوان مرجع به سلول در پیوند ردیف 2 و ستون 1 که سلول A2 است، تفسیر می کند.

فرمول در سلول D5:

 =INDIRECT(C5,FALSE)

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

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

ایجاد منابع غیر مستقیم از مقادیر و متن سلول

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

 در مثال زیر فرمول: = INDIRECT ("B" & C2)

یک مقدار از سلول B2 را بر اساس زنجیره منطقی زیر باز می گرداند:

تابع INDIRECTعناصر را در  ref_text ترکیب می کند - متن B و مقدار در سلول C2) مقدار در سلول C2 شماره 2 است که باعث می شود که مرجع سلول B2بشود) فرمول به سلول B2 برسد و مقدار آن را برمی گرداند که شماره 10 است. 

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

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

فرض کنید شما دارای محدوده های نامگذاری شده در صفحه خود هستید:

• سیب - B2: B6
• موز - C2: C6
• لیمو - D2: D6

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

=INDIRECT(G1)

و اکنون می توانید یک گام بیشتر بکشید و این فرمول INDIRECT را در سایر توابع اکسل برای محاسبه مجموع و میانگین مقادیر در یک محدوده داده شده نامیش دهید یا حداکثر / حداقل مقدار را پیدا کنید:

=SUM(INDIRECT(G1))

=AVERAGE(INDIRECT(G1))

=MAX(INDIRECT(G1))

=MIN(INDIRECT(G1))

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

فرمول غیر مستقیم به صورت پویا با ارجاع به یک برگه دیگر 

مفید بودن عملکرد اکسل INDIRECT محدود به ساختن مراجع سلولی "پویا" نیست. شما همچنین می توانید آن را برای اشاره به سلول ها در برگه های دیگر استفاده کنید.

فرض کنید شما بعضی از داده های مهم را در جدول 1 دارید و می خواهید این داده ها را در صفحه 2 بکشید. تصویر زیر نشان می دهد که چگونه یک فرمول اکسل غیر مستقیم می تواند این کار را انجام دهد:  بیایید فرمول را که در تصویر مشاهده میکنیم  تفسیر و درک کنیم.

همانطور که می دانید، روش معمول برای ارجاع به یک صفحه دیگر در اکسل، نوشتن نام شیت است و علامت تعجب و یک مرجع سلولی / محدوده مانند SheetName Range می باشد. از آنجا که یک نام برگ اغلب شامل یک فضا(ها) است، شما بهتر است آن را (نام، نه یک فضای:) در نقل قول به منظور جلوگیری از یک خطا، به عنوان مثال "صفحه من!" $ 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), "")

ایجاد مرجع پویا اکسل به کتابچه های دیگر

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

برای اینکه همه چیز ساده تر شود، ابتدا با مراجعه به کتاب دیگری با روش معمول شروع کنیم (در صورتی که کتاب و / یا نام کتاب شما حاوی فضاها هستند، آپوستروف اضافه می شود): '[Book_name.xlsx]Sheet_name'!Range

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

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

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

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

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

توجه داشته باشید. کتاب کار ارجاع داده شده در فرمول شما باید همیشه باز باشد، در غیر این صورت عملکرد NETIRECT خطای #REF را پر می کند. به طور معمول، عملکرد IFERROR می تواند به شما کمک کند از آن اجتناب کنید:

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

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

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

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

1. هر عدد در هر سلول را وارد کنید، مثلا شماره 20 در سلول A1.
2. به A1 از دو سلول دیگر به روش های مختلف به A1 مراجعه کنید:

 =A1 and =INDIRECT("A1")

1. یک ردیف جدید در ردیف 1 قرار دهید.

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

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

فرض کنید شما می خواهید مقادیر را در سلول A2: A5 جمع کنید و می توانید این کار را با استفاده از تابع 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 در Excel برای بازگشت یک آرایه از مقادیر استفاده می شود. به عنوان مثال، شما می توانید از فرمول آرایه زیر استفاده کنید (به یاد داشته باشید که با فشار دادن Ctrl + Shift + Enter می توانید میانگین 3 عدد کوچکتر در محدوده A1: A10 را بدست آورید):

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

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

برای جلوگیری از این اتفاق می افتد، مستقیما در عملکرد ROW اعمال کنید و فرمول آرایه شما همواره درست باقی خواهد ماند، صرف نظر از اینکه چند ردیف درج شده یا حذف شوند:

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

مثال 2. توابع INDIRECT و ADDRESS

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

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

 رشته $ C $ 1 را بازگردانی می کند از آنجا که C1 یک سلول در تقاطع ردیف 1 و ستون 3 است.

برای ایجاد مرجع سلولی غیر مستقیم، شما به سادگی تابع ADDRESS را به یک فرم INDIRECT مانند این اضافه کنید: 

=INDIRECT(ADDRESS(1,3))

 استفاده از INDIRECT با Data Validation در اکسل شما می توانید با استفاده از تابع اکسل INDIRECT با اعتبار سنجی داده ها برای ایجاد لیست کشویی مرتب که گزینه های مختلفی را انتخاب می کنند بسته به اینکه چه مقدار کاربر در اولین کشویی انتخاب شده است، استفاده کنید.
لیست کشویی وابسته ساده بسیار آسان است. همه چیزهایی که چندین بار نامیده می شود، برای ذخیره آیتم های کشویی و یک فرم ساده ساده  =INDIRECT(A2)  که در آن A2 یک سلول است که اولین لیست کشویی شما را نمایش می دهد. 

برای ساختن منوهای پیچیده تر 3 سطوح یا کشویی با ورودی های چند کلمه ای، شما باید یک فرم ترکیبی بی نظیر پیچیده تر با یک عمل SUBSTITUT تو در تو داشته باشید.

عملکرد اکسل غیر مستقیم - خطاهای احتمالی و مسائل

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

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

#REF! error

اغلب تابع NETIRECT یک #REF را باز می گرداند! خطا در سه مورد:

1. ref_text یک مرجع سلولی معتبر نیست اگر پارامتر ref_text در فرم غیر مستقیم شما یک مرجع سلولی معتبر نیست، فرمول به #REF تبدیل خواهد شد! 
2. حد محدوده بیش از حد است . اگر استدلال ref_text فرم غیر مستقیم خود را به یک سلول از فراتر از حد ردیف 1،048،576 یا محدودیت ستون 16،384 اشاره می کند، شما همچنین خطای #REF را در اکسل 2007، 2010 و اکسل 2013 دریافت خواهید کرد.
3. ورق یا کتاب مورد نظر بسته شده است. اگر فرمول نامطلوب شما به یک کتابچه یا یک برگه دیگر اکسل اشاره دارد، آن کتاب / صفحه گسترده دیگر باید باز باشد، در غیر اینصورت #REF را برمیگرداند. 

#REF! error

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