فهرست
تبریک می گویم! شما به روز پایانی ماراتن رسیدید 30 عملکرد اکسل در 30 روز. این یک سفر طولانی و جالب بوده است که در طی آن چیزهای مفید زیادی در مورد توابع اکسل یاد گرفته اید.
در روز پانزدهم ماراتن، مطالعه عملکرد را اختصاص خواهیم داد غیر مستقیم (INDIRECT)، که پیوند مشخص شده توسط رشته متن را برمی گرداند. با این تابع می توانید لیست های کشویی وابسته ایجاد کنید. به عنوان مثال، هنگام انتخاب یک کشور از یک لیست کشویی، مشخص می شود که کدام گزینه در لیست کشویی شهر ظاهر می شود.
بنابراین، اجازه دهید نگاهی دقیق تر به بخش نظری تابع بیاندازیم غیر مستقیم (غیر مستقیم) و نمونه های عملی کاربرد آن را بررسی کنید. اگر اطلاعات اضافی یا نمونه هایی دارید، لطفا در نظرات به اشتراک بگذارید.
تابع 30: غیر مستقیم
عملکرد غیر مستقیم (INDIRECT) پیوند مشخص شده توسط رشته متن را برمی گرداند.
چگونه می توان از تابع INDIRECT استفاده کرد؟
از آنجایی که تابع غیر مستقیم (INDIRECT) یک پیوند داده شده توسط یک رشته متن را برمی گرداند، می توانید از آن برای موارد زیر استفاده کنید:
- یک پیوند اولیه بدون تغییر ایجاد کنید.
- یک مرجع به یک محدوده استاتیک با نام ایجاد کنید.
- با استفاده از اطلاعات برگه، ردیف و ستون پیوند ایجاد کنید.
- یک آرایه اعداد بدون تغییر ایجاد کنید.
نحو INDIRECT (INDIRECT)
عملکرد غیر مستقیم (INDIRECT) دارای نحو زیر است:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) متن پیوند است.
- a1 – اگر برابر با TRUE (TRUE) باشد یا مشخص نشده باشد، از سبک پیوند استفاده خواهد شد A1; و اگر FALSE (FALSE)، سپس سبک R1C1.
تله INDIRECT (INDIRECT)
- عملکرد غیر مستقیم (INDIRECT) هر زمان که مقادیر موجود در کاربرگ اکسل تغییر کند دوباره محاسبه می شود. اگر این تابع در بسیاری از فرمول‌ها استفاده شود، می‌تواند تا حد زیادی سرعت کتاب کار شما را کاهش دهد.
- اگر تابع غیر مستقیم (INDIRECT) پیوندی به یک کتاب کار اکسل دیگر ایجاد می کند، آن کتاب کار باید باز باشد یا فرمول خطا را گزارش می کند. #مرجع! (#ارتباط دادن!).
- اگر تابع غیر مستقیم (INDIRECT) به محدوده ای اشاره می کند که از حد ردیف و ستون بیشتر است، فرمول یک خطا را گزارش می دهد #مرجع! (#ارتباط دادن!).
- عملکرد غیر مستقیم (INDIRECT) نمی تواند به یک محدوده با نام پویا ارجاع دهد.
مثال 1: یک پیوند اولیه بدون تغییر ایجاد کنید
در مثال اول، ستون های C و E حاوی اعداد یکسانی هستند که مجموع آنها با استفاده از تابع محاسبه می شود مبلغ (SUM) نیز یکسان هستند. با این حال، فرمول ها کمی متفاوت است. در سلول C8، فرمول به صورت زیر است:
=SUM(C2:C7)
=СУММ(C2:C7)
در سلول E8، تابع غیر مستقیم (INDIRECT) پیوندی به سلول شروع E2 ایجاد می کند:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
اگر یک ردیف در بالای برگه وارد کنید و مقدار ژانویه (ژانویه) را اضافه کنید، مقدار در ستون C تغییر نخواهد کرد. در واکنش به افزودن یک خط، فرمول تغییر خواهد کرد:
=SUM(C3:C8)
=СУММ(C3:C8)
با این حال، تابع غیر مستقیم (INDIRECT) E2 را به عنوان سلول شروع ثابت می کند، بنابراین ژانویه به طور خودکار در محاسبه کل ستون E لحاظ می شود. سلول پایان تغییر کرده است، اما سلول شروع تحت تأثیر قرار نگرفته است.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
مثال 2: پیوند به یک محدوده استاتیک با نام
عملکرد غیر مستقیم (INDIRECT) می تواند یک مرجع به یک محدوده نامگذاری شده ایجاد کند. در این مثال، سلول های آبی محدوده را تشکیل می دهند NumList. علاوه بر این، یک محدوده پویا نیز از مقادیر ستون B ایجاد می شود NumListDynبسته به تعداد اعداد این ستون.
مجموع هر دو محدوده را می توان با دادن نام آن به عنوان آرگومان برای تابع محاسبه کرد مبلغ (SUM)، همانطور که در سلول های E3 و E4 می بینید.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
به جای تایپ نام محدوده در یک تابع مبلغ (SUM)، می توانید به نام نوشته شده در یکی از سلول های کاربرگ مراجعه کنید. به عنوان مثال، اگر نام NumList در سلول D7 نوشته شده است، سپس فرمول در سلول E7 به این صورت خواهد بود:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
متاسفانه عملکرد غیر مستقیم (INDIRECT) نمی تواند مرجع دامنه پویا ایجاد کند، بنابراین وقتی این فرمول را در سلول E8 کپی می کنید، با خطا مواجه می شوید. #مرجع! (#ارتباط دادن!).
مثال 3: با استفاده از اطلاعات صفحه، سطر و ستون یک پیوند ایجاد کنید
شما به راحتی می توانید پیوندی را بر اساس اعداد سطر و ستون و همچنین با استفاده از مقدار FALSE (FALSE) برای آرگومان تابع دوم ایجاد کنید. غیر مستقیم (غیر مستقیم). به این ترتیب پیوند سبک ایجاد می شود R1C1. در این مثال، نام برگه را به پیوند اضافه کردیم - "MyLinks"!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
مثال 4: یک آرایه اعداد بدون تغییر ایجاد کنید
گاهی اوقات لازم است از آرایه ای از اعداد در فرمول های اکسل استفاده کنید. در مثال زیر، می‌خواهیم 3 عدد بزرگ را در ستون B میانگین بگیریم. اعداد را می‌توان در یک فرمول وارد کرد، همانطور که در سلول D4 انجام می‌شود:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
اگر به یک آرایه بزرگتر نیاز دارید، بعید است که بخواهید همه اعداد را در فرمول وارد کنید. گزینه دوم استفاده از تابع است ROW (ROW)، همانطور که در فرمول آرایه وارد شده در سلول D5 انجام می شود:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
گزینه سوم استفاده از تابع است ROW (STRING) همراه با غیر مستقیم (INDIRECT)، همانطور که با فرمول آرایه در سلول D6 انجام می شود:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
نتیجه برای هر 3 فرمول یکسان خواهد بود:
با این حال، اگر ردیف‌هایی در بالای صفحه درج شود، فرمول دوم نتیجه نادرستی را نشان می‌دهد، زیرا مراجع در فرمول همراه با تغییر ردیف تغییر می‌کنند. حالا به جای میانگین سه عدد بزرگ، فرمول میانگین اعداد 3، 4 و 5 بزرگ را برمی گرداند.
با استفاده از توابع غیر مستقیم (INDIRECT)، فرمول سوم مراجع ردیف صحیح را حفظ می کند و به نشان دادن نتیجه صحیح ادامه می دهد.