فرمول اکسل SUM و INDIANTH Dynamic Range

مایکروسافت اکسل برخی از ترفندهای خنک را دارد و با استفاده از فرمول های دامنه دامنه SUM و INDIRECT تنها دو راه برای به راحتی دستکاری داده های شما است.

SUM - بی نظیر فرمول بررسی

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

INDIRECT می تواند با تعدادی از توابع استفاده شود که مرجع سلولی را به عنوان یک استدلال مانند توابع OFFSET و SUM قبول می کند.

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

INDIRECT این کار را با اشاره به داده ها در سلول های غیر مستقیم از طریق یک محل متوسط ​​انجام می دهد.

مثال: SUM - فرم غیر مستقیم برای مجموع یک دامنه دینامیکی ارزش استفاده می شود

این مثال بر اساس داده های نشان داده شده در تصویر بالا است.

فرمول SUM - INDJECT که با استفاده از مراحل آموزش زیر ایجاد شده است:

= SUM (INDIRECT ("D" و E1 و ": D" و E2))

در این فرمول، استدلال عملکرد تضمین شده توزیع نشده حاوی ارجاعات به سلولهای E1 و E2 است. اعداد در این سلول ها، 1 و 4، در صورت همراه با بقیه استدلال INDIRECT، سلول های مرجع D1 و D4 را تشکیل می دهند.

در نتیجه، محدوده ای که با عملکرد SUM به دست می آید، داده های موجود در محدوده سلول های D1 تا D4 است که 50 است.

با تغییر اعداد موجود در سلولهای E1 و E2؛ با این حال، محدوده به طور کامل می تواند به راحتی تغییر کند.

این مثال برای اولین بار از فرمول بالا برای جمع آوری داده ها در سلول های D1: D4 و سپس محدوده جمع بندی به D3: D6 را بدون اصلاح فرمول در سلول F1 تغییر دهید.

01 از 03

ورود به فرمول - گزینه ها

گزینه های ورود به فرمول عبارتند از:

• typing فرمول بالا به طور مستقیم به سلول F1 و با فشار دادن کلید Enter بر روی صفحه کلید
• وارد کردن تابع INDIRECT به عنوان یک بحث با استفاده از کادر محاوره SUM

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

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

مراحل زیر از کادر محاوره ای SUM برای وارد کردن فرمول استفاده می کنند.

وارد کردن داده های آموزشی

داده های سلولی D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. داده های زیر را به سلول های D1 تا E2 وارد کنید

شروع فرمول SUM - INDIECT - باز کردن جعبه محاوره SUM

1. با کلیک بر روی سلول F1 - این جایی است که نتایج این مثال نمایش داده خواهد شد
2. روی برگه فرمول نوار منو کلیک کنید
3. ریاضی و ریز را از روبان انتخاب کنید تا لیست کشویی تابع باز شود
4. روی لیست SUM کلیک کنید تا کادر محاوره ای تابع باز شود

02 از 03

وارد کردن تابع غیر مستقیم - برای مشاهده تصویر بزرگتر کلیک کنید

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

در مورد توابع توزيع، اکسل اجازه باز کردن کادر محاوره ای تابع دوم را نمی دهد برای ورود به استدلال های آن.

بنابراین، تابع INDIRECT باید به صورت دستی در خط شماره 1 کادر محاوره SUM تابع وارد شود.

1. در کادر محاوره ای، بر روی Line Number1 کلیک کنید
2. تابع INDIRECT زیر را وارد کنید: INDIRECT ("D" و E1 و ": D" و E2)
3. برای تکمیل تابع و کلیک بر روی OK را کلیک کنید
4. شماره 50 باید در سلول F1 ظاهر شود، زیرا این کل برای داده های موجود در سلول های D1 تا D4 است
5. هنگامی که روی F1 سلول کلیک کنید، فرمول کامل = SUM (INDIRECT ("D" و E1 و ": D" & E2)) در نوار فرمول بالای برگه ظاهر می شود

شکستن عملکرد غیر مستقیم

برای ایجاد یک محدوده دینامیکی در ستون D با استفاده از INDIRECT، باید با استفاده از اعداد موجود در سلولهای E1 و E2، حرف D را در استدلال تابع INDIRECT ترکیب کنیم.

این توسط موارد زیر انجام می شود:

• ampersand ( & ) برای ترکیب کردن یا ترکیب اطلاعات متنی (در این صورت نامه D) با مرجع سلولی (E1 و E2)
• علاوه بر این، داده های متنی که همراه با مرجع های سلولی همپوشانی می شوند باید با علامت های زوج ( "" )
• در نهایت، نقاط انتهای محدوده با کولون (:)

بنابراین، نقطه شروع محدوده توسط شخصیت ها تعریف می شود: "D" و E1 .

مجموعه دوم شخصیت ها: ": D" و E2 کولون را با نقطه انتهایی ترکیب می کند. این کار به این دلیل انجام می شود که کولون یک شخصیت متن است و بنابراین باید داخل علامت نقل قول باشد.

امپرسان سوم در وسط مورد استفاده قرار می گیرد تا دو قسمت را به یک استدلال متصل سازد :

"D" و E1 و ": D" و E2

03 از 03

به طور پویا تغییر دامنه عملکرد SUM

نکته کل این فرمول این است که تغییر اندازه محدوده عملکرد SUM را بدون نیاز به ویرایش آرگومان تابع آسان کند.

با استفاده از عملکرد غیر مستقیم در فرمول، تغییر اعداد در سلول های E1 و E2 طیف سلول هایی را که توسط عملکرد SUM خوانده می شوند، تغییر می دهند.

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

1. بر روی سلول E1 کلیک کنید
2. شماره 3 را تایپ کنید
3. کلید Enter بر روی صفحه کلید را فشار دهید
4. بر روی سلول E2 کلیک کنید
5. شماره 6 را تایپ کنید
6. کلید Enter بر روی صفحه کلید را فشار دهید
7. پاسخ در سلول F1 باید به 90 تغییر کند - که مجموع اعداد موجود در سلول های D3 تا D6 است
8. فرمول زیر را با تغییر محتویات سلولهای B1 و B2 به هر عدد بین 1 تا 6 تست کنید

INDIRECT و #REF! مقدار خطا

#REF! اگر در مورد استدلال تابع غلط، مقدار خطا در سلول F1 ظاهر شود:

• مرجع سلولی معتبر نیست
• حاوی مرجع خارجی به یک کتاب کار متفاوتی است و کتاب کار باز نیست
• به محدوده سلولی خارج از محدوده برگه (ردیف 1،048،576 یا ستون XFD)