एक्सेल जब विशाल डेटासेट से निपटने की बात आती है तो यह सबसे व्यापक रूप से उपयोग किया जाने वाला टूल है। हम Excel . में अनेक आयामों के असंख्य कार्य कर सकते हैं . इस लेख में, मैं समझाऊंगा कि होल्ट-विंटर्स एक्सपोनेंशियल स्मूथिंग . को कैसे लागू किया जाए एक्सेल . में . यह विधि पूर्वानुमान के लिए उपयोगी है।
कार्यपुस्तिका डाउनलोड करें
इस लेख को पढ़ते हुए इस कार्यपुस्तिका को डाउनलोड करें और अभ्यास करें।
होल्ट-विंटर्स एक्सपोनेंशियल स्मूथिंग का परिचय
होल्ट-विंटर्स मूल्यों की भविष्यवाणी करने के लिए विधि एक उन्नत विधि है। यह पूर्वानुमान की भविष्यवाणी करते समय मौसमी और प्रवृत्ति प्रभावों पर विचार करता है। यही कारण है कि मान कुछ यादृच्छिकता को छोड़कर वास्तविक मूल्यों का प्रतिनिधित्व करते हैं।
होल्ट-विंटर्स एक्सपोनेंशियल स्मूथिंग . का उपयोग करके पूर्वानुमान मान की गणना करने का सूत्र एक्सेल . में है
Ft+k =(Lt+k*Tt)*St-m+k
कहां, F =पूर्वानुमानित मूल्य
एल =स्तर
टी =प्रवृत्ति
एम =4 तिमाही अवधि के लिए, 12 मासिक अवधि के लिए
एस =मौसमी सूचकांक
एक्सेल में होल्ट विंटर एक्सपोनेंशियल स्मूथिंग करने के लिए 11 आसान चरण
यह आज के लेख के लिए डेटासेट है। हमारे पास तिमाही बिक्री है 2022 तक।
हम 2023 के लिए पूर्वानुमानित मानों की गणना करेंगे।
चरण 1:रैंडम अल्फा, बीटा और गामा मान असाइन करें
पहला कार्य स्थिरांक के लिए कुछ यादृच्छिक मान निर्दिष्ट करना है अल्फ़ा , बीटा, और गामा ।
हम बाद में इन मानों को अनुकूलित करेंगे।
चरण 2:प्रारंभिक मौसमी अनुक्रमणिका की गणना करें
उसके बाद, हम आरंभिक मौसमी अनुक्रमणिका . निर्धारित करेंगे पहले 4 . के लिए क्वार्टर हम प्रत्येक तिमाही की बिक्री को औसत बिक्री से विभाजित करके प्रारंभिक मौसमी अनुक्रमणिका निर्धारित करेंगे पहले 4 . में से क्वार्टर . हम औसत फ़ंक्शन . का उपयोग करेंगे ऐसा करने के लिए।
- F11 पर जाएं और निम्न सूत्र लिखिए।
=C11/AVERAGE($C$11:$C$14)
- अब, ENTER दबाएं . एक्सेल आउटपुट लौटाएगा।
- उसके बाद, हैंडल भरें . का उपयोग करें करने के लिए स्वतः भरण F14 . तक ।
चरण 3:प्रारंभिक स्तर और रुझान निर्धारित करें
डेटासेट के शुरुआती स्तर और रुझान की गणना करने का समय आ गया है।
प्रारंभिक स्तर स्तर . है पांचवीं तिमाही . के लिए चूंकि 4 . हैं एक साल में तिमाही।
प्रारंभिक स्तर का सूत्र है,
L5 =Y5/S1
कहां, Y5 =5वीं तिमाही के लिए बिक्री।
S1 =पहली तिमाही के लिए मौसमी सूचकांक।
इसे निर्धारित करने के लिए,
- D15 पर जाएं और सूत्र लिखिए
=C15/F11
- अब, ENTER दबाएं . एक्सेल आउटपुट लौटाएगा।
इस बार मैं शुरुआती रुझान की गणना करूंगा (जो कि 5वें . के लिए भी है) त्रिमास)। प्रारंभिक प्रवृत्ति का सूत्र है,
T5 =L5-Y4/S4
कहां, L5 =5वीं तिमाही के लिए स्तर।
Y4 =चौथी तिमाही के लिए बिक्री।
S4 =चौथी तिमाही के लिए मौसमी सूचकांक।
इसकी गणना करने के लिए,
- E15 पर जाएं और सूत्र लिखिए,
=D15-C14/F14
- ENTER दबाएं आउटपुट प्राप्त करने के लिए।
चरण 4:अगले सीज़नल इंडेक्स की गणना करें
अब, हम अपने सामान्य सूत्र का उपयोग करके अगले मौसमी सूचकांकों की गणना करेंगे। मौसमी सूचकांक की गणना करने का सामान्य सूत्र है,
सेंट =ɣ(Yt/Lt)+(1-ɣ)St-m
कहां
एल =स्तर।
टी =प्रवृत्ति।
एम =4 तिमाही अवधि के लिए, 12 मासिक अवधि के लिए।
एस =मौसमी सूचकांक।
Ɣ = गुणांक।
मौसमी सूचकांक की गणना करने के लिए,
- F15 पर जाएं और निम्न सूत्र लिखिए
=$C$6*(C15/D15)+(1-$C$6)*F11
- ENTER दबाएं ।
- स्वतः भरण F22 . तक ।
नोट: थोड़ी देर के लिए त्रुटि पर ध्यान न दें, अगले स्तरों . को मापने के बाद यह ठीक रहेगा और अगला रुझान ।
चरण 5:अगले स्तर निर्धारित करें
अब मैं दिखाऊंगा कि सूत्र का उपयोग करके अगले स्तरों को कैसे निर्धारित किया जाए,
Lt =α(Yt/St-m)+(1-α)(Lt-1+Tt-1)
कहां
एल =स्तर
टी =प्रवृत्ति
एम =4 तिमाही अवधि के लिए, 12 मासिक अवधि के लिए
एस =मौसमी सूचकांक
α = गुणांक
- D16 पर जाएं और निम्न सूत्र लिखिए
=$C$4*(C16/F12)+(1-$C$4)*(D15+E15)
- ENTER दबाएं ।
- स्वतः भरण D22 . तक ।
चरण 6:अगले रुझानों को मापें
आइए अब प्रवृत्ति प्रभाव का विचार प्राप्त करें। सूत्र है
Tt=β(Lt-Lt-1)+(1-β)Tt-1
कहां
एल =स्तर
टी =प्रवृत्ति
एम =4 तिमाही अवधि के लिए, 12 मासिक अवधि के लिए
एस =मौसमी सूचकांक
β= गुणांक
प्रवृत्ति प्रभाव की गणना करने के लिए,
- E6 पर जाएं और निम्न सूत्र लिखिए
=$C$5*(D16-D15)+(1-$C$5)*E15
- दबाएं ENTER जारी रखने के लिए।
- स्वतः भरण E22 . तक ।
चरण 7:वास्तविक बिक्री के साथ तुलना करने के लिए पूर्वानुमानित मान खोजें
अब, हम वास्तविक बिक्री के साथ तुलना करने के लिए अनुमानित मूल्यों की गणना करेंगे। पहला तिमाही 6 के लिए होगा। पूर्वानुमानित मानों (तुलना के लिए) की गणना करने का सूत्र है,
एफ टी =(एल t-1 + टी t-1 )* एस टी-एम
तो चलिए करते हैं।
- G16 पर जाएं और निम्न सूत्र लिखिए
=(D16+E16)*F12
- दबाएं ENTER जाने के लिए।
- स्वतः भरण G22 . तक ।
चरण 8:पूर्वानुमान त्रुटियों की गणना करें
अब, हम वास्तविक बिक्री से अनुमानित मूल्य घटाकर पूर्वानुमान त्रुटि की गणना करेंगे।
- H16 पर जाएं और सूत्र लिखिए
=C16-G16
- फिर ENTER दबाएं ।
- स्वतः भरण H22 . तक ।
चरण 9:पूर्वानुमानित तिमाही के लिए K मान निर्दिष्ट करें
पूर्वानुमान की गणना करने का समय आ गया है। लेकिन उससे पहले, हमें सह-कुशल k . को समझना चाहिए . यह पूर्वानुमान के लिए भविष्य के समय का प्रतिनिधित्व करता है। हमारे मामले में, हम 4 . के पूर्वानुमान की गणना करेंगे 2023 की तिमाहियों। और हमारे पास 2022 के लिए डेटा उपलब्ध है।
तो, 2023 की पहली तिमाही के लिए, k का मान 1 होगा, दूसरी तिमाही के लिए यह 2 होगा, इत्यादि।
चरण 10:पूर्वानुमानित मान की गणना करें
अब, हम अपने पूर्वानुमानित मूल्यों की गणना करने के लिए तैयार हैं। हम उनकी गणना करने के लिए अंतिम उपलब्ध स्तर, प्रवृत्ति और मौसमी का उपयोग करेंगे।
- G23 पर जाएं और निम्न सूत्र लिखिए
=($D$22+F23*$E$22)*F19
- फिर ENTER दबाएं आउटपुट प्राप्त करने के लिए।
- अब, स्वतः भरण G25 . तक ।
चरण 11:अल्फ़ा, बीटा और गामा को अनुकूलित करें
अब, त्रुटि को कम करने के लिए, हम अल्फ़ा . के मान को अनुकूलित करेंगे , बीटा , और गामा . हम एक एक्सेल सॉल्वर . की मदद लेंगे ऐसा करने के लिए।
- सबसे पहले, हमें मूल माध्य वर्ग त्रुटि की गणना करने की आवश्यकता है। ऐसा करने के लिए, C7 . पर जाएं और निम्न सूत्र लिखिए
=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21))
फॉर्मूला ब्रेकडाउन:
- COUNT(H15:H21) → कक्षों की संख्या गिनें।
- आउटपुट → 7
- SUMSQ(H15:H21) → H5:H11 . के वर्गों के योग की गणना करें .
- आउटपुट → 463493653301
- =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → RMSE . की गणना करता है
- =SQRT(992.463493653301/7)
- =SQRT(141.780499093329)
- आउटपुट → 9072
- फिर, ENTER दबाएं ।
- अब, डेटा पर जाएं टैब>> सॉल्वर select चुनें ।
- सॉल्वर पैरामीटर्स विंडो पॉप अप हो जाएगी। चूंकि हम त्रुटि को कम करना चाहते हैं, हमारा उद्देश्य RMSE . सेट करना है गुणांक . के मान बदलकर न्यूनतम होना ।
- उसके बाद, बाधाओं को जोड़ने के लिए, जोड़ें . क्लिक करें ।
<मजबूत> 
- जोड़ें बाधा विंडो दिखाई देगी। बाधाएं हैं 0<=α,4,ß<=1 . तो पहली बाधा जोड़ने के लिए, सेल संदर्भ . सेट करें और मान . (छवि देखें)
<मजबूत> 
- इसी तरह, दूसरी बाधा जोड़ने के बाद, आपका आउटपुट इस तरह होगा। फिर समाधान . क्लिक करें ।
- एक्सेल अल्फ़ा . को ऑप्टिमाइज़ करके त्रुटि को कम करेगा , बीटा, और गामा ।
याद रखने वाली बातें
- आपको सॉल्वर ऐड-इन को सक्रिय करना होगा ।
- हम k मान के बारे में चिंतित नहीं हैं उन पूर्वानुमानों की गणना के लिए जिनकी हम उपलब्ध वास्तविक बिक्री से तुलना करने जा रहे हैं।
निष्कर्ष
इस लेख में, मैंने समझाया है कि होल्ट-विंटर्स एक्सपोनेंशियल स्मूथिंग . को कैसे लागू किया जाए एक्सेल . में . मुझे आशा है कि यह सभी की मदद करता है। यदि आपके कोई प्रश्न हैं तो कृपया टिप्पणी करें।