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

تابع Trend در اکسل چیست؟

در تعریف کلی، تابع Trend بر اساس داده‌های موجود با الگوی مشخصی از تغییرات، علاوه بر ساده‌سازی بررسی روند فعلی، امکان پیش‌بینی داده‌های آینده را نیز فراهم می‌کند. اما طبق مفاهیم ریاضی و فرمول‌نویسی، از این تابع برای انجام تحلیل از طریق یک «خط روند» (Trendline) با توجه به مجموعه مشخصی از مقادیر وابسته «y» و یک یا چند سری داده مستقل «x» استفاده می‌شود. به این شکل که تابع Trend مقادیر متناظر روی خط روند را باز می‌گرداند و مقادیر «y» جدیدی را برای مجموعه‌ای از مقادیر جدید «x» پیش‌بینی می‌کند.

این مفهوم ممکن است در ابتدا کمی پیچیده به‌نظر برسد، اما در ادامه مطلب بعد از بررسی فرمول تابع Trend و چند مثال تصویری کاملا روشن خواهد شد. در هر حال لازم است توجه کنیم برای درک بهتر تابع Trend، آشنایی با اصول فرمول‌‌نویسی اکسل و اصطلاحات آن ضروری است. بنابراین قبل از ورود به بحث اصلی پیشنهاد می‌کنیم فیلم آموزش توابع و فرمول‌‌نویسی در اکسل در فرادرس را مشاهده کنید.

ساختار کلی فرمول تابع Trend در اکسل

برای فرمول‌نویسی تابع Trend در اکسل از فرمول زیر استفاده می‌کنیم.

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

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

1. آرگومان اجباری known_y’sمربوط به مقادیر وابسته «y»
2. آرگومان اختیاری known_x’s مربوط به مقادیر مستقل «x»
3. آرگومان اختیاری new_x’s مربوط به مقادیر جدید «x»
4. آرگومان اختیاری و منطقی const

آرگومان اجباری تابع Trend

اولین آرگومان تابع Trend در فرمول به‌نام known_y’s مجموعه‌ای از مقادیر و داده‌هایی هستند که از قبل در اختیار داریم. این داده‌ها معمولا روی محور عمودی «y» در نمودار قرار دارند. تحلیل روند بدون مشخص کردن این دسته داده‌ها انجام نمی‌گیرد و حین عملیات با پیام خطای اکسل روبرو می‌شویم.

آرگومان‌های اختیاری تابع Trend

تابع Trend سه آرگومان اختیاری دارد.

۱. آرگومان known_x’s

یک یا چند مجموعه از داده‌های مستقل که روی محور افقی «x» قرار می‌گیرند.

نکات مهم در تعریف آرگومان known_x’s

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

• درصورتی‌که فقط یک مجموعه متغیر مستقل روی محور «x» داشته باشیم، مقادیر known_y’s و known_x’s می‌توانند به شکل محدوده‌ اعداد یک ستون یا یک ردیف از جدول اکسل اما با تعداد داده‌های یکسان تعریف شوند.
• اگر چند مقدار مستقل روی محور «x» داشته باشیم، مقادیر known_y’s باید فقط در یک ردیف یا فقط در یک ستون باشند.
• زمانی‌که مقدار مشخصی برای known_x’s تعریف نکنیم، اکسل به‌صورت پیش‌فرض آرایه‌ای از اعداد متوالی را در نظر می‌گیرد.

۲. آرگومان new_x’s

یک یا چند مجموعه از مقادیر مستقل جدید که می‌خواهیم اکسل روند را برای آن‌ها محاسبه کند.

نکات مهم در تعریف آرگومان new_x’s

در تعریف این آرگومان لازم است به نکات زیر توجه کنیم.

• تعداد ستون یا ردیف در این آرگومان با تعداد داده‌ها در آرگومان known_x’s برابر است.
• اگر مقداری برای این آرگومان تعریف نشود، اکسل به‌صورت پیش‌فرض آن را با آرگومان known_x’s یکسان در نظر می‌گیرد.

۳. آرگومان const

مقداری منطقی است که مشخص می‌کند آیا مقدار ثابت «a» یا همان عرض از مبدا در معادله خطی y = bx + a محاسبه شود یا خیر.

نکات مهم در تعریف آرگومان const

هنگام تعریف این آرگومان لازم است به نکات زیر توجه کنیم.

• با تعریف مقدار «TRUE» در این آرگومان، یا در صورت حذف آن، مقدار «a» توسط اکسل به‌صورت خودکار و بر اساس داده‌ها تعیین می‌شود. به‌عبارتی، اکسل این مقدار را طوری در نظر می‌گیرد که خط رسم شده بیشترین تطابق را با داده‌های موجود داشته باشد.
• اگر این آرگومان را در فرمول به شکل «False» تعریف کنیم، اکسل آن را معادل عدد صفر در نظر می‌گیرد. به این شکل با حذف مقدار «a»، فقط کافی است در معادله خطی y = bx مقدار «b» را تعیین کنیم تا پیش‌بینی مقادیر، مطابق فرمول انجام گیرد.

روش محاسباتی رسم خط روند توسط تابع Trend

تابع Trend در اکسل برای پیدا کردن مناسب‌ترین خط روند با بیشترین تطابق با داده‌ها، از «روش حداقل مربعات» (Least Square Method) استفاده می‌کند. در این روش، خطی انتخاب می‌شود که جمع مربعات فاصله عمودی نقاط داده تا خط، کمترین مقدار ممکن باشد.

اگر فقط یک مجموعه از مقادیر «x» را داشته باشیم معادله خط حاصل به شکل y = bx + a خواهد بود. در غیر این صورت، با وجود چند مجموعه متغیر مستقل «x» در جدول داده‌ها، معادله به شکل y = b1x1 + b2x2 + … + bnxn + a در می‌آید. تعریف هر یک از متغیرهای معادله به شکل زیر است.

• «y»: متغیر وابسته‌ای که قصد محاسبه مقدار آن را داریم.
• «x»:‌متغیر یا متغیرهای مستقلی که بر اساس آن مقدار «y» را محاسبه می‌کنیم.
• «a»: مقدار عرض از مبدا یا نقطه‌ای که خط روند، محور «y» را قطع می‌کند. در این حالت مقدار «x» در معادل برابر صفر است.
• «b»: شیب خط که نشان‌دهنده سرعت تغییر مقدار «y» با افزایش «x» است.

معادله دوم برای پیدا کردن بهترین خط متناسب با داده‌ها در تحلیل روند با تابع LINEST در اکسل نیز کاربرد دارد.

فرمول‌نویسی آرایه‌ای با تابع Trend در اکسل

اگر بخواهیم محاسبات مربوط به پیش‌بینی مقادیر را به‌طور هم‌زمان برای چند مقدار جدید «y» انجام دهیم، تابع Trend را در قالب فرمول آرایه‌ای استفاده می‌کنیم. بنابراین در صورت ناآشنا بودن با این نوع فرمول‌نویسی پیشنهاد می‌کنیم فیلم آموزش رایگان فرمول‌نویسی آرایه‌ای در اکسل از فرادرس را مشاهده کنید.

اما فرمول‌نویسی آرایه‌ای برای تابع Trend در سه مرحله زیر انجام می‌گیرد.

1. ابتدا همه سلول‌هایی که می‌خواهیم نتایج در آن‌ها نمایش داده شود را انتخاب می‌کنیم.
2. فرمول تابع Trend را می‌نویسیم.
3. در انتها برای اجرای فرمول به‌جای دکمه «Enter» از کلیدهای ترکیبی «Ctrl + Shift + Enter» استفاده می‌کنیم.

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

یادگیری حرفه‌ای فرمول‌نویسی با توابع اکسل در فرادرس

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

همچنین در دو مجموعه آموزش زیر قابلیت انتخاب بیشتری بر حسب نیاز برای علاقه‌مندان وجود دارد.

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

کار با تابع Trend ممکن است در ابتدا مشکل به‌نظر برسد. اما با آموزش حرفه‌ای فرمول‌نویسی و یادگیری ترفندهای اکسل همراه با مثال درک بهتری از این موضوع پیدا خواهیم کرد.

استفاده از تابع Trend برای تحلیل روند سری‌های زمانی

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

مثال

فرض می‌کنیم مجموعه‌ای از داده‌های فروش یک کسب و کار در بازه زمانی ماهیانه مشخصی را در اختیار داریم. رسم نمودار روند بر اساس این داده‌ها کمک می‌کند تا ضمن بررسی تغییرات فروش در این بازه، میزان فروش در آینده را نیز پیش‌بینی کنیم. به‌عنوان مثال در جدول تصویر زیر متغیر مستقل «x» یا همان ماه فروش در سلول‌های A2 تا A13 و متغیر وابسته «y» یا میزان فروش در سلول‌های B2 تا B13 قرار دارند.

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

1. ستون C با عنوان روند «Trend» را در جدول ایجاد می‌کنیم.
2. سلول‌های C2 تا C13 را انتخاب می‌کنیم.
3. فرمول =TREND(B2:B13,A2:A13) را در نوار اکسل می‌نویسیم و کلید ترکیبی «Ctrl + Shift + Enter» را فشار می‌دهیم. به این شکل اعداد مربوط به تحلیل روند کلی فروش در ستون C نوشته می‌شوند.
4. برای رسم نمودار روند ابتدا داده‌های مربوط به فروش و روند یعنی سلول‌های B1 تا C13 را انتخاب می‌کنیم. سپس با رفتن به زبانه «Insert» و بخش «Chart» روی گزینه «Line or Chart Area» کلیک می‌کنیم.
5. در نهایت نمایش گرافیکی روند سری‌ زمانی فروش رسم می‌شود.

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

پیش‌بینی روند آینده

تحلیل و پیش‌بینی روند آینده یکی از مباحث پرکاربرد فروش محصولات در کسب و کارها محسوب می‌شود. اما در این حالت چند دسته متغیر مستقل «x» در جدول داده‌های اکسل داریم. مراحل انجام کار با کمی تغییرات در فرمول‌نویسی تابع Trend همانند قبل است.

مثال

در داده‌های فروش جدول قبل، اگر بخواهیم پیش‌بینی تقریبی برای فروش در دو ماه آینده را داشته باشیم، فرمول به‌صورت =TREND(B2:B13,A2:A13,A14:A17) تغییر می‌کند. در این حالت داده‌های فروش در ستون B2 تا B13 مربوط به آرگومان known_y’s ، داده‌های مربوط به ستون A2 تا A13 مربوط به آرگومان known_x’s و داده‌های ستون A4 تا A17 مربوط به آرگومان new_x’s هستند.

بعد از وارد کردن فرمول و فشار دادن کلید ترکیبی «Ctrl + Shift + Enter» همانند تصویر زیر، خط روند جدیدی در نمودار بر اساس داده‌های موجود در B1 تا C17 رسم می‌شود. در این حالت می‌توان بر حسب ماه مورد نظر، عدد تقریبی فروش را پیش‌بینی کرد.

فرمول‌نویسی با تابع Trend در اکسل با چند مجموعه از مقادیر مستقل x

برخی اوقات برای انجام تحلیل روند، دو یا چند مجموعه از مقادیر مستقل «x» را در اختیار داریم. در این حالت برای رسم خط روند با استفاده از تابع Trend ابتدا هر یک از این مجموعه مقادیر را به‌صورت جداگانه در ستون‌های مختلف به‌عنوان آرگومان known_x’s وارد می‌کنیم.

مثال

در جدول زیر مقادیر مربوط به آرگومان known_x1 در سلول‌های B2 تا B13 اولین سری از مجموعه متغیر مستقل «x» و داده‌های مربوط به known_x2  در سلول‌های C2 تا C13 دومین سری از متغیرهای مستقل هستند. همچنین متغیر وابسته فرمول نیز در سلول‌های D2 تا D13 قرار دارند. در این حالت فرمول تابع Trend برای این مجموعه اعداد به شکل =TREND(D2:D13,B2:C13) در می‌آید.

به همین ترتیب برای پیش‌بینی روند آینده، می‌توانیم مقادیر جدید متغیر مستقل «x» یعنی new_x1 و new_x2 را در ادامه ستون‌های B14 تا B17 و C14 تا C17 وارد کنیم. در این حالت برای پیش‌بینی داده‌های فروش بر اساس مقادیر جدید «x» فرمول به شکل =TREND(D2:D13,B2:C13,B14:C17) تغییر می‌کند. نتایج نمودار روند تغییرات فروش به شکل زیر خواهد بود.

سایر روش‌های تحلیل روند در اکسل

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

استفاده از تابع FORECAST

توابع Trend و Forecast تفاوت‌های کمی از نظر مفهوم و نوع فرمول‌نویسی به شرح زیر با یکدیگر دارند.

• تمرکز تابع Trend روی تحلیل روند فعلی و گذشته است. به‌عنوان مثال با تحلیل داده‌های فروش یک کسب و کار می‌توان روند فعلی را بررسی کرد. هر چند همان‌طور که در مثال‌های مختلف تا این بخش دیدیم، از این تابع برای تخمین روند آینده داده‌ها نیز استفاده می‌شود. اما تمرکز تابع Forecast فقط روی پیش‌بینی روند آینده بر اساس داده‌های موجود است.
•  در تابع Forecast، فقط یک مقدار جدید از متغیر وابسته «y» بازای یک مقدار جدید از متغیر مستقل «x» بازمی‌گردد. برای این‌کار یک فرمول‌نویسی معمولی کافی است. اما در تابع Trend اگر بخواهیم دو یا چند مجموعه از متغیر «y» بازای چند متغیر مستقل «x» را محاسبه کنیم، نیاز به فرمول‌نویسی آرایه‌ای داریم.

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

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

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

رسم خط روند برای بررسی الگوی تغییر داده‌ها

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

۱. بعد از انتخاب نمودار روی آن راست کلیک می‌کنیم.

۲. روی گزینه «Add Trendline» کلیک می‌کنیم. بعد از باز شدن پنجره «Format Trendline» نوع خط روند از بخش مربوط قابل تغییر است.

۳. در صورت نیاز به پیش‌بینی روند آینده داده‌ها در بخش «Forward» تعداد دوره‌های مورد نظر را وارد می‌کنیم. همچنین می‌توانیم برای مشاهده تقریبی روندهای گذشته و گسترش داده‌ها به آنچه قبل از وضعیت موجود اتفاق افتاده است، در بخش «Backward» مقدار مورد نظر را وارد کنیم.

همان‌طور که در تصویر مشخص است، نتایج پیش‌بینی داده‌ها با خط روند، تابع Trend و تابع Forecast با یکدیگر هیچ تفاوتی ندارند.

هموارسازی داده‌ها با روش میانگین‌سازی متحرک

روش ساده دیگر برای نمایش تصویری روند داده‌ها، استفاده از «میانیگن متحرک» (Moving Average) است. در این تکنیک که به نام میانگین چرخشی یا میانگین جاری نیز شناخته می‌شود، الگوها یا روندهای بلندمدت از طریق حذف نوسانات داده یا هموارسازی داده‌ها در یک سری زمانی شفاف‌تر می‌شوند. تحلیل میانگین متحرک ابزاری رایج برای نمایش عمومی قیمت‌ها و سیگنال‌های خرید و فروش در بازارهای مالی و تحلیل تکنیکال بورس است. ابزاری که در صورت تمایل به درک بهتر آن در این حوزه خاص می‌توانید فیلم آموزش میانگین متحرک در بازارهای مالی در فرادرس را مشاهده کنید.

اما در حالت کلی، فرمول‌های دستی مختلفی برای محاسبه میانگین متحرک وجود دارد. با این حال در نرم‌افزار اکسل می‌توانیم خط روند را به‌صورت خودکار بر اساس مفهوم میانگین متحرک رسم کنیم و تحلیل مربوط به داده‌های خود را با توجه به این روش انجام دهیم. این کار در دو مرحله زیر انجام می‌گیرد.

۱. روی سری داده‌های نمودار، راست کلیک می‌کنیم و به بخش «Add Trendline» می‌رویم.

۲. در پنجره «Format Trendline» گزینه «Moving Average» را فعال می‌کنیم. سپس تعداد دوره‌های زمانی مورد نظر را در بخش «Period» می‌نویسیم.

در مطلب زیر به شکل دقیق‌تری مفاهیم پایه و تحلیل با این روش در بازارهای مالی را تشریح کرده‌ایم.

جمع‌بندی پایانی

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