توابع آماری اکسل: آموزش همراه با مثال، قسمت سوم

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

در این مقاله به توابع آماری اکسل برای محاسبه توابع خط روند عمیق تر خواهیم پرداخت.

لطفاً توجه داشته باشید که برخی از توابع آماری در نسخه‌های اخیر اکسل معرفی شده‌اند و بنابراین در نسخه‌های قدیمی‌تر در دسترس نیستند.

زمان تخمینی مطالعه: 12 minuti

توابع خط روند

Forecast

تابع Forecast اکسل یک نقطه آینده را در خط روند خطی متناسب با مجموعه ای از مقادیر x و y پیش بینی می کند.

نحو

= FORECAST( x, known_y's, known_x's )

استدلال ها

• x: یک مقدار x عددی که می خواهید یک مقدار y جدید برای آن پیش بینی کنید.
• known_y's: آرایه ای از مقادیر y شناخته شده
• known_x's: آرایه ای از مقادیر x شناخته شده

توجه داشته باشید که طول آرایه از known_x باید همان باشد known_y و واریانس known_x لازم نیست که صفر باشد.

مثال

در صفحه گسترده زیر، تابع FORECAST اکسل برای پیش بینی یک نقطه اضافی در امتداد بهترین خط مستقیم از طریق یک سری مقادیر شناخته شده x و y (ذخیره شده در سلول های F2:F7 و G2:G7) استفاده می شود.

همانطور که در سلول F7 صفحه گسترده نشان داده شده است، تابع محاسبه مقدار y مورد انتظار در x=7 است :=FORECAST( 7, G2:G7, F2:F7 )

این نتیجه را می دهد 32.666667 .

Intercept

در میان توابع پیش‌بینی اکسل، ما می‌یابیم Intercept. تابع Intercept اکسل وقفه (مقدار در تقاطع محور y) خط رگرسیون خطی را در مجموعه ای از مقادیر x و y محاسبه می کند.

نحو

= INTERCEPT( known_y's, known_x's )

استدلال ها

• known_y's: آرایه ای از مقادیر y شناخته شده
• known_x's: آرایه ای از مقادیر x شناخته شده

توجه داشته باشید که طول آرایه از known_x باید همان باشد known_y و واریانس known_x لازم نیست که صفر باشد.

مثال

صفحه گسترده زیر نمونه ای از تابع را نشان می دهد Intercept از اکسل برای محاسبه نقطه ای که خط رگرسیون خطی از آن عبور می کند استفاده می شود known_x و known_y (در سلول های F2:F7 و G2:G7 ذکر شده است) محور y را قطع می کند.

Le known_x و known_y بر روی نمودار در صفحه گسترده رسم می شوند.

همانطور که در سلول F9 صفحه گسترده نشان داده شده است، فرمول تابع Intercept است :=INTERCEPT( G2:G7, F2:F7 )

که نتیجه را می دهد 2.4 .

Slope

تابع پیش بینی بسیار جالب دیگر شیب (Slope) اکسل شیب خط رگرسیون خطی را از طریق مجموعه ای از مقادیر x و y محاسبه می کند.

سینتکس تابع به صورت زیر است:

نحو

= SLOPE( known_y's, known_x's )

استدلال ها

• known_y's: آرایه ای از مقادیر y شناخته شده
• known_x's: آرایه ای از مقادیر x شناخته شده

توجه داشته باشید که طول آرایه از known_x باید همان باشد known_y و واریانس known_x لازم نیست که صفر باشد.

مثال

صفحه گسترده زیر نمونه ای از تابع را نشان می دهد Slope (شیب) اکسل برای محاسبه شیب خط رگرسیون خطی از طریق known_x و known_y، در سلول های F2:F7 و G2:G7.

Le known_x و known_y بر روی نمودار در صفحه گسترده رسم می شوند.

همانطور که در سلول F9 صفحه گسترده نشان داده شده است، فرمول تابع Intercept است :=SLOPE( G2:G7, F2:F7 )

که نتیجه را می دهد 4.628571429.

Trend

یک تابع پیش بینی اکسل بسیار جالب است TREND Excel (Trend) خط روند خطی را از طریق مجموعه ای از مقادیر y و (اختیاری)، مجموعه ای از مقادیر x را محاسبه می کند.

سپس این تابع خط روند خطی را گسترش می دهد تا مقادیر y اضافی را برای مجموعه اضافی از مقادیر x جدید محاسبه کند.

سینتکس تابع به صورت زیر است:

نحو

= TREND( known_y's, [known_x's], [new_x's], [const] )

استدلال ها

• known_y's: آرایه ای از مقادیر y شناخته شده
• [known_x's]: یک یا چند آرایه از مقادیر x شناخته شده. این یک آرگومان اختیاری است که در صورت ارائه، باید همان طول مجموعه باشد known_y's. اگر حذف شود، مجموعه [known_x's] مقدار {1، 2، 3، …} را می گیرد.
• [new_x's]: یک آرگومان اختیاری، ارائه یک یا چند آرایه از مقادیر عددی که مجموعه ای از مقادیر x جدید را نشان می دهد، که می خواهید مقادیر y جدید مربوطه را برای آن محاسبه کنید. هر آرایه از [new_x ها] باید با آرایه ای از [ مطابقت داشته باشدknown_x's]. اگر استدلال [new_x ها] حذف شده است، برابر با [known_x's] .
• [هزینه]: یک آرگومان منطقی اختیاری که مشخص می کند آیا ثابت 'b' در معادله خطی وجود دارد یا خیر y = m x + b ، باید به اجبار برابر با صفر باشد. خود [هزینه] درست است (یا اگر این آرگومان حذف شود) ثابت b به طور معمول در نظر گرفته می شود.
• خود [هزینه] FALSE است ثابت b روی 0 تنظیم می شود و معادله خط مستقیم می شود y = mx .

مثال

در صفحه گسترده زیر، تابع Excel Trend برای گسترش یک سری مقادیر x و y که روی خط مستقیم y = 2x + 10 قرار دارند استفاده می شود. مقادیر x و y شناخته شده در سلول های A2-B5 ذخیره می شوند. صفحه گسترده و همچنین در نمودار صفحه گسترده نمایش داده می شوند.

توجه داشته باشید که ضروری نیست که نقاط داده شده دقیقاً در امتداد خط مستقیم y = 2x + 10 قرار گیرند (اگرچه در این مثال اینطور است). تابع Trend اکسل بهترین خط را برای هر مجموعه ای از مقادیری که ارائه می کنید پیدا می کند.

تابع Trend از روش حداقل مربعات برای یافتن بهترین خط مناسب استفاده می کند و سپس از آن برای محاسبه مقادیر y جدید برای مقادیر x جدید ارائه شده استفاده می کند.

در این مثال، مقادیر [new_x ها] در سلول‌های A8-A10 ذخیره می‌شوند و تابع Trend اکسل در سلول‌های B8-B10 برای یافتن مقادیر y متناظر جدید استفاده شده است. همانطور که در نوار فرمول نشان داده شده است، فرمول این است := TREND(B2:B5، A2:A5، A8:A10)

مشاهده می کنید که تابع Trend در نوار فرمول در پرانتزهای { } محصور شده است. این نشان می دهد که تابع به عنوان وارد شده است فرمول آرایه .

Growth

در میان توابع پیش‌بینی اکسل، ما می‌یابیم Growth. کارکرد Growth اکسل منحنی رشد نمایی را از طریق مجموعه ای از مقادیر y و (اختیاری)، یک یا چند مجموعه از مقادیر x محاسبه می کند. سپس این تابع منحنی را گسترش می دهد تا مقادیر y اضافی را برای مجموعه ای اضافی از مقادیر x جدید محاسبه کند.

سینتکس تابع به صورت زیر است:

نحو

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

استدلال ها

• known_y's: آرایه ای از مقادیر y شناخته شده
• [known_x's]: یک یا چند آرایه از مقادیر x شناخته شده. این یک آرگومان اختیاری است که در صورت ارائه، باید همان طول مجموعه باشد known_y's. اگر حذف شود، مجموعه [known_x's] مقدار {1، 2، 3، …} را می گیرد.
• [new_x's]: مجموعه ای از مقادیر x جدید که تابع مقادیر y جدید مربوطه را برای آن محاسبه می کند. اگر حذف شود، فرض بر این است که مجموعه [new_x ها] برابر است با [known_x's] و تابع مقادیر y را برمی گرداند که روی منحنی رشد نمایی محاسبه شده قرار دارند.
• [هزینه]: یک آرگومان منطقی اختیاری که مشخص می کند آیا ثابت 'b' در معادله خطی وجود دارد یا خیر y = b * m^x ، باید مجبور شود برابر با 1 باشد. اگر [هزینه] درست است (یا اگر این آرگومان حذف شود) ثابت b به طور معمول در نظر گرفته می شود. خود [هزینه] FALSE است ثابت b روی 1 تنظیم می شود و معادله خط مستقیم می شود y = mx .

مثال

در صفحه گسترده زیر، تابع رشد اکسل برای گسترش یک سری مقادیر x و y که روی منحنی رشد نمایی y = 5 * 2^x قرار دارند، استفاده می شود. این ها در سلول های A2-B5 صفحه گسترده ذخیره می شوند و همچنین در نمودار صفحه گسترده ظاهر می شوند.

تابع Growth منحنی رشد نمایی را محاسبه می کند که به بهترین وجه با مقادیر شناخته شده x و y ارائه شده مطابقت دارد. در این مثال ساده، بهترین منحنی برازش، منحنی نمایی y = 5 * 2^x است.

هنگامی که اکسل معادله منحنی رشد نمایی را محاسبه می کند، می تواند از آن برای محاسبه مقادیر y جدید برای مقادیر x جدید ارائه شده در سلول های A8-A10 استفاده کند.

در این مثال، مقادیر [new_x's] در سلول های A8-A10 و تابع ذخیره می شوند Growth اکسل در سلول های B8-B10 درج شده است. همانطور که در نوار فرمول نشان داده شده است، فرمول این است:=Growth(B2:B5، A2:A5، A8:A10)

می بینید که تابع Growth در نوار فرمول در پرانتزهای { } محصور شده است. این نشان می دهد که تابع به عنوان وارد شده است فرمول آرایه .

توجه داشته باشید که اگرچه نقاط در مثال بالا دقیقاً در امتداد منحنی y = 5 * 2^x قرار می گیرند، اما این ضروری نیست. کارکرد Growth اکسل بهترین منحنی را برای هر مجموعه ای از مقادیری که ارائه می کنید پیدا می کند.

توابع مالی

اثر

کارکرد Effect Excel نرخ بهره موثر سالانه را برای یک نرخ بهره اسمی معین و تعداد معینی از دوره های ترکیبی در سال برمی گرداند.

نرخ بهره سالانه موثر

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

نرخ بهره موثر سالانه با استفاده از رابطه زیر محاسبه می شود:

فاخته nominal_rate نرخ بهره اسمی است npery تعداد دوره های مرکب در سال است.

سینتکس تابع به صورت زیر است:

نحو

= EFFECT( nominal_rate, npery )

استدلال ها

• nominal_rate: نرخ بهره اسمی (باید یک مقدار عددی بین 0 و 1 باشد)
• npery: تعداد دوره های ترکیب در سال (باید یک عدد صحیح مثبت باشد).

مثال

صفحه گسترده زیر سه نمونه از تابع Excel Effect را نشان می دهد:

اگر نتیجه تابع Effect به صورت اعشاری نمایش داده می شود یا 0٪ را نشان می دهد، هر دوی این مشکلات احتمالاً به دلیل قالب بندی سلول حاوی تابع است. Effect.

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

برای انجام این:

1. سلول های مورد نظر را به صورت درصد انتخاب کنید.
2. با استفاده از یکی از روش های زیر کادر محاوره ای Format Cells را باز کنید:
   • روی سلول یا محدوده انتخاب شده کلیک راست کرده و گزینه را انتخاب کنید قالب‌بندی سلول‌ها… از منوی زمینه؛
   • روی Dialog Box Launcher در گروه‌بندی Number در برگه کلیک کنید صفحه اصلی روبان اکسل؛
   • از میانبر صفحه کلید استفاده کنید CTRL-1 (یعنی کلید CTRL را انتخاب کنید و با نگه داشتن آن، کلید "1" (یک) را انتخاب کنید).
3. در کادر محاوره ای "Format Cells":
   • از کارت مطمئن شوید Numero در بالای کادر محاوره ای انتخاب شده است.
   • انتخاب کنید درصد از لیست دسته در سمت چپ کادر محاوره ای .با این کار گزینه‌های اضافی در سمت راست کادر انتخاب ظاهر می‌شود و به شما امکان می‌دهد تعداد ارقام اعشاری را که می‌خواهید نمایش داده شود، انتخاب کنید.
   • پس از انتخاب تعداد ارقام اعشاری که می خواهید نمایش داده شود، کلیک کنید OK .

Nominal

کارکرد Nominal اکسل نرخ بهره اسمی را برای یک نرخ بهره موثر معین و تعداد معینی از دوره های ترکیبی در سال برمی گرداند.

سینتکس تابع به صورت زیر است:

نحو

= NOMINAL( effect_rate, npery )

استدلال ها

• effect_rate: نرخ بهره موثر (مقدار عددی بین 0 و 1).
• npery: تعداد دوره های ترکیب در سال (باید یک عدد صحیح مثبت باشد).

مثال

در صفحه گسترده زیر، تابع Nominal از Excel برای محاسبه نرخ سود اسمی سه وام با شرایط مختلف استفاده می شود.

Ercole Palmeri