EXCELNI YAQINLASHTIRISH

Prognozlashning turli usullari orasida taxminiylikni farqlashning iloji yo'q. Uning yordami bilan siz taxminiy hisob-kitoblarni amalga oshirishingiz va rejalashtirilgan ko'rsatkichlarni asl ob'ektlarni oddiyroq narsalar bilan almashtirish orqali hisoblashingiz mumkin. Excelda bu usuli taxmin va tahlil qilish uchun foydalanish imkoniyati mavjud. Keling, ushbu usulni o'rnatilgan dasturga qanday o'rnatilganligini ko'rib chiqamiz.

Approximatsiyani bajarish

Ushbu uslubning nomi "yaqin" so'zining lotin tilidagi "proxima" so'zidan kelib chiqqan bo'lib, u ma'lum ko'rsatkichlarni soddalashtirish va yumshatish, ularni trendga aylantirish va uning asosidir. Lekin bu usul faqat prognozlash uchun emas, balki mavjud natijalarni o'rganish uchun ham qo'llanilishi mumkin. Axir, qarama-qarshilik aslida asl ma'lumotlarning soddalashtirilishi va soddalashtirilgan versiyani o'rganishga osonroqdir.

Moslashuvchan Excelda amalga oshiriladigan asosiy vosita trend yo'nalishini qurishdir. Pastki chiziq, mavjud indikatorlarga asoslangan holda, vazifaning jadvali kelgusi davrlar uchun bajariladi. Taqqoslash yo'nalishining asosiy maqsadi, taxmin qilish qiyin emas, prognoz qilish yoki umumiy tendentsiyalarni aniqlashdir.

Ammo u besh turdagi yaqinlashuvlardan biri yordamida qurilishi mumkin:

Lineer;
Ustel;
Logaritmik;
Polinom;
Quvvat.

Har bir variantni alohida-alohida ko'rib chiqing.

Kurs: Excelda trend chizig'ini yaratish

Boshqaruv 1: Lineer tekislash

Avvalo, eng oddiy yondashishni, ya'ni lineer funktsiyani qo'llashni ko'rib chiqaylik. Biz bu haqda batafsilroq ma'lumotga ega bo'lamiz, chunki biz boshqa usullarni o'z ichiga olgan umumiy fikrlarni, ya'ni rejalashtirishni va boshqa variantlarni ko'rib chiqishda to'xtamasligimiz kerak bo'lgan ayrim boshqa nuanslarni belgilab beramiz.

Avvalo, biz yumshatuvchi jarayonni amalga oshirishimiz mumkin bo'lgan grafik tuzamiz. Grafikni yaratish uchun biz korxonada ishlab chiqarilgan mahsulotning birlik qiymatini va ma'lum bir davrda tegishli daromadlarni har oyda ko'rsatib beradigan jadvalni olamiz. Biz yaratgan grafik funktsiyadagi daromadlar o'sishining ishlab chiqarish xarajatlarining pasayishiga bog'liqligini aks ettiradi.

Grafikni yaratish uchun, birinchi navbatda, ustunlarni tanlang "Ishlab chiqarishning birlamchi qiymati" va "Qor". Keyin tabga o'tish "Qo'shish". Keyinchalik "Diagrammalar" asboblar qutisidagi lentada tugmani bosing "Spot". Ochilgan ro'yxatda ismni tanlang "Yumaloq egri va markerlar bilan nuqta". Bu trend chizig'i bilan ishlaydigan va shuning uchun Excelda taxminiy usulni qo'llash uchun eng mos bo'lgan bunday grafikalar.

Jadval qurildi.

Trenaj qatorini qo'shish uchun sichqonchani o'ng tugmasini bosing. Kontekst menyusi paydo bo'ladi. Unda biror elementni tanlang "Tizim chizig'ini qo'shish ...".
Buni kiritish uchun yana bir variant bor. Ipdagi yorliqlarning qo'shimcha guruhida "Charts bilan ishlash" tabga o'ting "Layout". Keyingi asboblar qutisida "Tahlil" tugmasini bosing "Trend yo'nalishi". Ro'yxat ochiladi. Chiziqli yondashuvni qo'llashimiz kerakligi sababli tanlangan pozitsiyalardan tanlaymiz "Lineer yondashuv".

Biroq, kontekst menyusi orqali qo'shimcha qilish bilan birinchi operatsiyani bajarish tanlangan bo'lsa, formatlash oynasi ochiladi.
Parametr blokida "Tizim chizig'ini qurish (yaqinlashtirish va yumshatish)" kalitni joyiga qo'ying "Lineer".
Agar so'ralsa, siz manzilning yaqinidagi belgini belgilashingiz mumkin "Diagrammada tenglikni ko'rsatish". Shundan keyin diagrammada yumshatuvchi funktsiya tenglamasi aks ettiriladi.
Bizning holatimizda, turli xil taxminiy variantlarni taqqoslash uchun qutini belgilash muhimdir "Grafika ishonchli yondoshuv (R ^ 2) qiymatini qo'ying". Ushbu ko'rsatkich har xil bo'lishi mumkin 0 qadar 1. U qanchalik yuqori bo'lsa, yaxshiroq taxminan (ishonchli). Bu ko'rsatkichning qiymatini hisobga olganda ishonamiz 0,85 va yuqori yumshatilish ishonchli hisoblanishi mumkin va agar bu raqam past bo'lsa, u holda - yo'q.
Yuqoridagi barcha sozlamalardan so'ng. Biz tugmani bosamiz "Yopish"oynaning pastki qismiga joylashtirilgan.

Ko'rib turganingizdek, trend yo'nalishi grafigida tasvirlangan. Lineer yaqinlashganda, u qora to'g'ri chiziq bilan ifodalanadi. Ushbu yumshatilishning tipi ma'lumotlar juda tez o'zgartirilganda va funktsional qiymatning argumentga bog'liqligiga ishonch hosil qilishning eng oddiy holatlarida qo'llanilishi mumkin.

Bu holda ishlatiladigan yumshatuvchi quyidagi formula bilan tavsiflanadi:

y = ax + b

Bizning maxsus holimizda, formula quyidagi shaklni oladi:

y = -0.1156x + 72.255

Yondashuvning aniqligi bizga tengdir 0,9418, bu juda maqbul natijadir, bu yumshatilishni ishonchli deb ta'riflaydi.

2-usul: Ustel yaqinlik

Keling, Excelda eksponentlik turini ko'rib chiqaylik.

Trenaj satrini o'zgartirish uchun, sichqonchani o'ng tugmasini bosib uni tanlang va ochilgan menyuda elementni tanlang "Trend yo'nalish formati ...".

Shundan so'ng bizga tanish bo'lgan formatli oyna ishga tushirildi. Yakunlanish turini tanlash uchun blokda kalitni tanlang "Exponential". Qolgan sozlamalar birinchi holatda bo'lgani kabi bir xil bo'ladi. Tugmasini bosing "Yopish".

Shundan so'ng, trend chizig'i tuziladi. Ko'rib turganingizdek, bu usuldan foydalanganda uning egri chiziqli shakli bor. Ishonch darajasi 0,9592, bu chiziqli yaqinlashtirilganda nisbatan yuqori. Eksponentsial usul qadriyatlar dastlab tez o'zgarganda va muvozanatli shaklni olishda eng yaxshi usul hisoblanadi.

Yumshatilish funksiyasining umumiy ko'rinishi quyidagicha:

y = x ^ x

qaerda e - bu tabiiy logaritma asosidir.

Xususiy holatda, formula quyidagi shaklni oldi:

y = 6282.7 * e ^ (- 0.012 * x)

Boshqaruv 3: Log tekislash

Endi logaritmik yondashuv usuli hisobga olinadi.

Avvalgi vaqtda bo'lgani kabi kontekst menyusi orqali trend yo'nalishining formatini oching. Kaliti joyga qo'ying "Logaritmik" tugmasini bosing "Yopish".

Logaritmik yondoshuv bilan trend chizig'ini qurish tartibi mavjud. Avvalgi holatda bo'lgani kabi, ushbu parametr dastlabki ma'lumotlar tezda o'zgarganda va muvozanat bilan qarashda foydalanish uchun yaxshiroqdir. Ko'rib turganingizdek, ishonch darajasi 0,946. Bu chiziqli usuldan foydalangandan yuqori, ammo eksponentlar bilan yumshatuvchi trend chizig'ining sifatidan past bo'ladi.

Umuman, yumshatuvchi formulaga quyidagilar kiradi:

y = a * ln (x) + b

qaerda Mn tabiiy logaritmning kattaligi. Shuning uchun usulning nomi.

Bizning holatda, formula quyidagi shaklni oladi:

y = -62,81ln (x) +404,96

Boshqaruv 4: polinomli yumshatish

Polinomning yumshatilish uslubini hisoblash vaqti keldi.

Tren chizig'i formatidagi oynaga o'ting, chunki siz allaqachon bir marta bajargansiz. Blokda "Bir trend yo'nalishini yaratish" kalitni joyiga qo'ying "Polinom". Ushbu moddaning o'ng tomonida maydon bor "Degree". Tanlashda "Polinom" faol bo'ladi. Bu erda har qanday quvvat qiymatini tanlashingiz mumkin 2 (sukut bo'yicha o'rnatilgan) 6. Bu ko'rsatkich funktsiyaning maksimal va minimal sonini aniqlaydi. Ikkinchi darajali polinom o'rnatilganda, faqat bitta maksimum tavsiflanadi va oltinchi darajali polinom o'rnatilganda, eng ko'p beshta maksimum aniqlanishi mumkin. Boshlash uchun biz standart sozlamalarni qoldiramiz, ya'ni ikkinchi darajani belgilaymiz. Qolgan sozlamalar avvalgi usullarda o'rnatilgandek qolaveradi. Biz tugmani bosamiz "Yopish".

Ushbu usul yordamida trend satr qurildi. Ko'rib turganingizdek, eksponentsial yaqinlashtirilishdan ko'ra ko'proq kavisli bo'ladi. Ishonch darajasi yuqorida keltirilgan usullarning har qandayiga qaraganda yuqori 0,9724.
Ma'lumotlar doimo o'zgartirilsa, bu usul eng muvaffaqiyatli tarzda qo'llanilishi mumkin. Ushbu turdagi yumshatilishni ta'riflovchi funksiya quyidagicha:
y = a1 + a1 * x + a2 * x ^ 2 + ... + a * x ^ n
Bizning holatda, formula quyidagi shaklni oldi:
y = 0.0015 * x ^ 2-1.7202 * x + 507.01
Keling, polinomlar darajasini o'zgartirsak natijalar boshqacha bo'ladimi? Formatlash oynasiga qaytamiz. Approximatsiya turi polinom bo'lib qoldi, ammo uning oldida deraza oynasida maksimal mumkin bo'lgan qiymat - 6.

Ko'rib turganingizdek, bundan keyin trend yo'nalishimiz balandlikning soni oltmish oltita aniq egri shaklini oldi. Ishonch darajasi yanada oshdi, natijada 0,9844.

Ushbu turdagi tekislashni tasvirlaydigan formula quyidagi shaklni oldi:

y = 8E-08x ^ 6-0,0003x ^ 5 + 0.3725x ^ 4-269.33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09

Boshqaruv 5: Quvvatni yumshatish

Xulosa qilib, Excelda quvvatni yaqinlashtirish usulini ko'rib chiqing.

Oynaga siljiting "Trend Line formati". Düzleştirme ko'rinishi holatini o'rniga o'rnating "Quvvat". Tenglik va ishonch darajasini ko'rsatish, har doimgidek, uni qoldiring. Biz tugmani bosamiz "Yopish".

Dastur bir trend chizig'ini tashkil etadi. Ko'rib turganingizdek, bizning holimizda bu engil burilishli chiziq. Ishonch darajasi 0,9618Bu juda yuqori ko'rsatkichdir. Yuqorida tavsiflangan barcha usullardan ishonch darajasi faqat polinom uslubidan foydalanganda yuqori bo'ldi.

Ushbu usul funksiya ma'lumotlarida jadal o'zgarishlar sodir bo'lganda samarali qo'llaniladi. Ushbu parametr faqat funktsiya va argument salbiy yoki nol qiymatlarni qabul qilmasa, amal qilishi mumkin.

Ushbu uslubni tavsiflovchi umumiy formula quyidagicha:

y = bx ^ n

Bizning maxsus holimizda shunday ko'rinadi:

y = 6E + 18x ^ (- 6.512)

Ko'rib turganimizdek, misol uchun foydalanadigan ma'lumotlardan foydalanganda oltinchi darajadagi polinom bilan polinom yaqinlashuv usuli (0,9844), lineer usulda ishonchning eng past darajasi (0,9418). Ammo, bu boshqa misollarni qo'llashda ham xuddi shunday tendentsiya bo'lishini anglatmaydi. Yo'q, yuqorida ko'rsatilgan usullarning samaradorligi trend yo'nalishini qurish kerak bo'lgan funktsiyaning o'ziga xos turiga qarab farq qilishi mumkin. Shuning uchun, tanlangan usul bu funktsiya uchun eng samarali bo'lsa, bu boshqa holatda ham u maqbul bo'ladi degani emas.

Agar yuqorida keltirilgan tavsiyalarga asoslanib, darhol aniqlay olmasangiz, qaysi turdagi yondashuv sizning holatlaringizga mos keladi, keyin barcha usullarni sinab ko'rish mantiqan. Bir trend chizig'ini qurib, uning ishonch darajasini ko'rganingizdan so'ng, eng yaxshi variantni tanlashingiz mumkin.