چگونه از تابع TREND در اکسل استفاده کنیم؟ + آموزش رسم خط روند در اکسل

تابع TREND در اکسل جهت پیش‌بینی مقادیر بر مبنای یک روند خطی (رگرسیون خطی) به‌کار می‌رود. این تابع، با استفاده از داده‌های موجود که دارای یک الگوی مشخص هستند، قادر است پیش‌بینی‌هایی برای داده‌های آینده (یا داده‌هایی که هنوز جمع‌آوری نشده‌اند) ارائه دهد و نتایج را در اختیار شما بگذارد.

فرمول ترند در اکسل به‌صورت زیر است:

در ادامه هر بخش را توضیح می‌دهیم:

• known_y’s (الزامی): متغیرهای وابسته‌ای هستند که شما از قبل دارید و برای محاسبه‌ی خط روند (Trend Line) استفاده می‌شوند. بدون این داده‌ها، اکسل نمی‌تواند روند را محاسبه کند و با خطا مواجه خواهید شد. این مقادیر معمولاً روی محور عمودی (y) نمودار قرار می‌گیرند.
• known_x’s (اختیاری): این‌ها متغیرهای مستقل هستند که روی محور افقی (x) نمودار قرار می‌گیرند و باید طول آن‌ها برابر با طول known_y’s باشد.
• new_x’s (اختیاری): این‌ها مقادیر جدیدی هستند که شما می‌خواهید اکسل برایشان روند را محاسبه کند. این بخش مشخص می‌کند که تعداد جدیدی از مقادیر y باید برای داده‌های شما محاسبه شود.
• const (اختیاری): یک مقدار منطقی است که مقدار عرض از مبدا (b در معادله y=mx+b) را تعیین می‌کند. اگر مقدار FALSE باشد، خط روند مجبور می‌شود که از نقطه‌ی صفر در محور x عبور کند، در حالی‌که اگر مقدار TRUE را وارد کنید یا این قسمت را خالی بگذارید (که به معنای TRUE است)، روند معمولاً از نقطه‌ی تقاطع یا intercept عبور می‌کند.

حالا با مثال‌های کاربردی، بهتر یاد می‌گیریم که چگونه می‌توانیم از تابع TREND در اکسل استفاده کنیم.

مثال اول: پیش بینی مصرف برق در ماه‌های آتی

فرض کنید مدیر یک دیتاسنتر هستید و در حال مانیتورینگ مصرف برق سرورها در چند ماه گذشته بوده‌اید. داده‌ها به این صورت است:

هدف‌: پیش‌بینی کنید مصرف برق در ماه‌های ۵ و ۶ چقدر خواهد بود.

راه حل:

۱- ابتدا داده‌ها را وارد شیت اکسل کنید.

• ستون A: ماه‌ها (۱، ۲، ۳، ۴)
• ستون B: مصرف برق (۱۲۰۰، ۱۳۵۰، ۱۵۰۰، ۱۷۰۰)

۲- از فرمول برای پیش‌بینی. استفاده کنید. در یک سلول (مثال ما B5) این فرمول را بنویسید و اینتر بزنید:

۳- با انجام مراحل بالا اکسل مقادیر زیر را پیش‌بینی می‌کند:

• ماه پنجم: ۱۸۵۰ کیلووات ساعت
• ماه ۶ام: ۲۰۱۵ کیلووات ساعت

مثال دوم: پیش بینی تعداد کاربران فعال در دکل های مخابراتی

داده‌ها: فرض کنیم اطلاعات زیر را داریم:

• تعداد دکل‌های مخابراتی: X=[۱۰,۲۰,۳۰,۴۰]
• تعداد کاربران فعال در شبکه: Y=[۵۰۰,۱۰۰۰,۱۵۰۰,۲۰۰۰]

هدف: می‌خواهیم پیش‌بینی کنیم اگر تعداد دکل‌های مخابراتی به ۵۰ افزایش پیدا کند، تعداد کاربران فعال چه مقدار خواهد بود.

راه حل با استفاده از تابع TREND:

تابع TREND در اکسل می‌تواند رابطه‌ی خطی میان متغیر مستقل (X) و وابسته (Y) را بیابد و پیش‌بینی کند.

• در ستون اول شیت اکسل، مقادیر X (تعداد دکل‌ها) و در ستون دوم، مقادیر Y (تعداد کاربران) را وارد کنید. از تابع زیر استفاده کنید:

برای مثال فرمول زیر را در سلول B6 وارد کردیم و دکمه‌ی اینتر را فشار دادیم:

این تابع پیش‌بینی می‌کند که اگر تعداد دکل‌ها به ۵۰ عدد برسد، تعداد کاربران فعال ۲۵۰۰ خواهد بود. از این تحلیل می‌توان برای تصمیم‌گیری درباره‌ی گسترش زیرساخت‌های مخابراتی و ظرفیت شبکه استفاده کرد.

مثال سوم: پیش‌ بینی ظرفیت شبکه در یک شرکت مخابراتی

فرض کنید شما مدیر شبکه یک شرکت مخابراتی هستید و می‌خواهید مصرف پهنای باند کاربران را در ماه‌های آینده پیش‌بینی کنید. داده‌های مصرف ماهانه برای کاربران در چند ماه گذشته از قرار زیر است:

هدف:

• پیش‌بینی مصرف پهنای باند برای ماه‌های ۵ و ۶ با تعداد کاربران فعال ۳۰۰۰ و ۳۵۰۰.
• استفاده از دو متغیر مستقل (ماه و تعداد کاربران) برای پیش‌بینی دقیق‌تر.

تحلیل:

در این مثال، مصرف پهنای باند هم به زمان (x) و هم به تعداد کاربران (z) بستگی دارد. از تابع TREND برای پیش‌بینی با چند متغیر مستقل استفاده می‌کنیم.

راه حل:

داده‌ها را وارد شیت کنید:

• ستون A: ماه‌ها (x) (۱، ۲، ۳، ۴)
• ستون B: تعداد کاربران فعال (z) (۱۰۰۰، ۱۵۰۰، ۲۰۰۰، ۲۵۰۰)
• ستون C: مصرف پهنای باند (y) (۵۰۰، ۷۵۰، ۱۱۰۰، ۱۴۰۰)

• پیش‌بینی با استفاده از ماه و تعداد کاربران: در سلول C6 از این فرمول استفاده کنید و دکمه‌ی اینتر را فشار دهید.

نکته: ستون B4:A1 شامل دو متغیر مستقل (ماه و تعداد کاربران) است و ستون B6:A5 مقادیر جدیدی است که می‌خواهید برای آن‌ها پیش‌بینی کنید.

اکسل مقادیر زیر را پیش‌بینی می‌کند:

• ماه ۵ و ۳ هزار کاربر: مصرف پهنای باند = ۱۷۰۰ ترابایت
• ماه ۶ و ۳ هزار و ۵۰۰ کاربر: مصرف پهنای باند = ۲۰۰۵ ترابایت

اکنون که فرآیند پیش‌بینی داده‌ها را انجام داده‌ایم، می‌توانیم به رسم نمودار روند در اکسل بپردازیم.

• تمام داده‌های جدول خود را شامل عنوان‌ها به‌همراه مقادیر پیش‌بینی‌شده انتخاب کنید.
• به منوی Insert بروید و از قسمت Charts یکی از نمودارها (مانند 2D Clustered Column Chart) را انتخاب کنید.
• حالا می‌خواهیم خط روند (Trend Line) را اضافه کنیم.

• برای رسم ترند لاین در اکسل روی یکی از نقاط داده کلیک‌راست کنید و گزینه‌ی Add Trendline را انتخاب کنید.
• نوع خطی (Linear) را انتخاب کنید و گزینه‌ی Display Equation on chart را فعال کنید تا خط روند در نمودار اکسل ظاهر شود.

گزینه‌ی Display Equation on Chart در اکسل به شما امکان رسم خط روند در اکسل را می‌دهد و کمک می‌کند تا معادله‌ی خط روند (Trendline) را مستقیماً روی نمودار نمایش دهید. این معادله رابطه‌ی بین متغیرهای مستقل (x) و وابسته (y) را نشان می‌دهد و معمولاً به صورت یک معادله‌ی خطی یا چندجمله‌ای (بسته به نوع خط روند) نمایش داده می‌شود.

این روش‌ها به شما کمک می‌کند روند داده‌ها را در اکسل به‌صورت گرافیکی و قابل‌درک نمایش دهید.

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