آموزش کار با تابع ترند در اکسل و رسم معادله خط

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

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

کاربرد تابع ترند در Excel

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

تابع ترند برای تحلیل کردن روند داده‌ها کاربرد دارد و به عبارت دیگر کاربرد آن درست مثل ترند لاین در اکسل است. لذا ۳ کاربرد مهم تابع Trend در اکسل، موارد زیر است:

• محاسبه معادله خط بین دو مجموعه داده x و y
• پیش‌بینی و محاسبه y بر اساس داده‌ی جدید به عنوان x
• ایجاد ترند لاین در اکسل برای پیش‌بینی داده‌های آتی

ترند لاین و تابع Trend در اکسل چطور خط رسم می‌کند؟

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

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

در این فرمول کلی، y متغیر وابسته یا خروجی است و x متغیر مستقل یا ورودی. m شیب خط و b عرض از مبدأ یا محل برخورد خط با محور y است.

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

سینتکس کلی تابع ترند در اکسل به صورت زیر است که در این رابطه، آرگومان اول ضروری است و ۳ آرگومان بعدی اختیاری است.

Now that you understand what a trendline is and how it's calculated, let's look at the Excel formula.

در این رابطه:

• آرگومان اول یا y، داده‌هایی است که به عنوان متغیر وابسته در اختیار داریم.
• آرگومان دوم x یا متغیر مستقل و به زبان دیگر داده‌های ورودی است که طبعاً تعداد داده‌ها باید با تعداد داده‌هایی که به عنوان آرگومان اول تعریف شده، برابر باشد.
• آرگومان سوم داده‌ای به عنوان x جدید است که با استفاده از آن می‌خواهیم y جدید را بر اساس ترند لاین محاسبه کنیم.
• آرگومان چهارم یک عدد به عنوان عرض از مبدأ است. به عبارت دیگر آرگومان چهارم، عدد ثابت b در معادله y = mx + b است. اگر به جای آرگومان چهارم از False استفاده کنیم نیز خط از مبدأ گذر خواهد کرد و اگر از True استفاده کنیم، لزومی برای عبور از مبدأ وجود نخواهد داشت که حالت پیش‌فرض نیز حالت True است.

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

استفاده از تابع Trend در اکسل با مثال ساده

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

اکنون برای محاسبه کردن ترند یا روند داده‌ها، از فرمول زیر استفاده می‌کنیم. دقت کنید که در این مثال عرض از مبدأ صفر نیست.

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

برای پیش‌بینی کردن فروش در ماه‌های آتی، کافی است آرگومان سوم تابع ترند را تعریف کنیم که تاریخ ماه‌های بعدی است و در سلول‌های A15 الی A18 وارد شده است. لذا آرگومان سوم را A15:A18 وارد می‌کنیم.

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

استفاده از تابع ترند و رسم نمودار ترند در اکسل

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

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

می‌توانید تنظیمات نمودار را تغییر بدهید تا روند فروش به شکل خطی ساده رسم شود. برای این کار روی نمودار کلیک کنید و از تب Chart Design در بالای صفحه، گزینه‌ی Change Chart Type را انتخاب کنید.

در میان انواع نمودارها، حالت Combo یا نمودار ترکیبی را انتخاب کنید. سپس برای سری داده‌هایی که به عنوان ترند توسط تابع Trend محاسبه شده، حالت Line یا نمودار خطی را انتخاب کنید. در نهایت روی OK کلیک کنید.

نتیجه را در تصویر زیر مشاهده می‌کنید.

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

howtogeekسیاره‌ی آی‌تی