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

همه اینها ممکن است با یک مثال ساده تر درک شود. با این حال، برای اینکه قادر به نوشتن فرمول، حتی در ساده ترین شکل آن، شما باید از استدلال های عملکرد مطلع شوید، درست است؟ بنابراین، بیایید ابتدا یک نگاه سریع به نحو اکسل 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 به مقدار در سلول 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 نتیجه همان نتیجه را نشان می دهد. آیا شما قبلا فهمید چرا؟ 

 =INDIRECT(C1)

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

 =INDIRECT(C3,FALSE)

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

 =INDIRECT(C5,FALSE)

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

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

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

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

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

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

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

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

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

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

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

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

=SUM(INDIRECT(G1))

=AVERAGE(INDIRECT(G1))

=MAX(INDIRECT(G1))

=MIN(INDIRECT(G1))

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

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

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

همانطور که می دانید، روش معمول برای ارجاع به یک صفحه دیگر در اکسل، نوشتن نام شیت است و علامت تعجب و یک مرجع سلولی / محدوده مانند SheetName Range می باشد. از آنجا که یک نام برگ اغلب شامل یک فضا(ها) است، شما بهتر است آن را (نام، نه یک فضای:) در نقل قول به منظور جلوگیری از یک خطا، به عنوان مثال "صفحه من!" $ A $ 1 را محصور کنید.

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

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), "")

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

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

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

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

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

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

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

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

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

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

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

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

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

فرض کنید شما می خواهید مقادیر را در سلول A2: A5 جمع کنید و می توانید این کار را با استفاده از تابع SUM انجام دهید:

=SUM(A2:A5)

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

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

از آنجایی که اکسل "A1: A5" را به عنوان یک رشته متن ساده به جای یک مرجع محدوده درک می کند، هنگامی که یک ردیف را وارد یا حذف می کنید، تغییری نخواهد کرد.

به غیر از 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 اعمال کنید و فرمول آرایه شما همواره درست باقی خواهد ماند، صرف نظر از اینکه چند ردیف درج شده یا حذف شوند:

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

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

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

=INDIRECT(ADDRESS(1,3))

برای ساختن منوهای پیچیده تر 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