เชี่ยวชาญ Trend-Adjusted Exponential Smoothing ใน Excel: คู่มือทีละขั้นตอน

เราทราบว่าการใช้สูตรน้ำหนักเฉพาะที่ให้ความสำคัญกับช่วงเวลาที่เกี่ยวข้องมากที่สุด Exponential Smoothing (การทำให้เรียบเนียนแบบเอ็กซ์โพเนนเชียล) คำนวณสมการแนวโน้ม (Trend Equation) ผ่านข้อมูล ด้วย Microsoft Excel เราสามารถทำงานได้อย่างสมบูรณ์แบบขณะคำนวณ Exponential Smoothing Excel มีเครื่องมือและ Add-ins ที่น่าทึ่งบางอย่าง ในบทความนี้ เราจะสาธิตขั้นตอนการคำนวณ Trend Adjusted Exponential Smoothing ใน Excel

คุณสามารถดาวน์โหลดสมุดงานและฝึกปฏิบัติด้วยพวกเขาได้

Exponential Smoothing ใน Excel คืออะไร?

Excel มีแนวทางการทำให้เรียบเนียนแบบรวมอยู่แล้ว ซึ่งคือ Exponential Smoothing เราใช้สิ่งนี้สำหรับการฉายแนวโน้ม (Trend Projection), การทำให้ข้อมูลเรียบเนียน (Data Smoothing), และการพยากรณ์ (Forecasting) เพื่อวัตถุประสงค์ในการตัดสินใจที่มีข้อมูลสนับสนุน Exponential Smoothing ถูกใช้เพื่อคาดการณ์ปริมาณธุรกิจ วิธีนี้ 'Smooths (ทำให้เรียบ)' ข้อมูลโดยการลดจำนวนตัวแปรอธิบาย

เราจะได้ Exponential Smoothing ใน Excel ที่ไหน?

เพื่อเข้าถึงเครื่องมือ Exponential Smoothing เราต้องเปิดใช้งานแท็บ Data Analysis (การวิเคราะห์ข้อมูล) บนริบบอน Excel ฟีเจอร์ Data Analysis ให้ความสามารถในการคิดอย่างมีวิจารณญาณเกี่ยวกับข้อมูลในภาษาธรรมดา แทนที่จะต้องสร้างอัลกอริทึมที่费力 เพื่อเปิดใช้งานแท็บ Data Analysis เราต้องทำตามคำแนะนำด้านล่าง

ขั้นตอน:

• ขั้นแรก ไปที่แท็บ File (ไฟล์) จากริบบอน

• สิ่งนี้จะพาคุณไปที่เมนู Backstage ของ แอปพลิเคชัน Excel
• ขั้นที่สอง เลื่อนลงเล็กน้อย และคลิกที่เมนู Options (ตัวเลือก)

• กล่องโต้ตอบ Excel Options จะปรากฏ
• ต่อไป ไปที่ Add-ins (ส่วนเสริม) และเลือก Analysis ToolPak ในกลุ่ม Add-ins
• เพิ่มเติม เลือกตัวเลือก Excel Add-ins จากเมนูแบบเลื่อนลง Manage (จัดการ)
• จากนั้น คลิกที่ปุ่ม Go (ไป)

• ดังนั้น กล่องโต้ตอบ Add-ins จะแสดงขึ้น
• ผลลัพธ์ ติ๊กเช็คบ็อกซ์ Analysis ToolPak
• และ จากนั้น คลิกที่ OK เพื่อเสร็จสิ้นขั้นตอน

• สุดท้าย เมื่อคุณไปที่ริบบอน Excel อีกครั้งและไปที่แท็บ Data (ข้อมูล) คุณจะเห็นฟีเจอร์ Data Analysis เปิดใช้งานแล้วในหมวด Analysis (การวิเคราะห์)

ขั้นตอนการคำนวณ Trend Adjusted Exponential Smoothing ใน Excel แบบทีละขั้น

Adjusted Exponential Smoothing (การทำให้เรียบเนียนแบบปรับแล้ว) เป็นเทคนิคที่สร้างการพยากรณ์จากตัวอย่างข้อมูลในอดีตที่วัดได้ โดยการคำนวณค่าเฉลี่ยน้ำหนักของค่าเดิมและการพยากรณ์สำหรับช่วงเวลาล่าสุด และเพิ่มการปรับแนวโน้ม (Trend Adjustment) มาทำตามคำแนะนำเพื่อคำนวณ Trend Adjusted Exponential Smoothing ใน Excel

ขั้นตอนที่ 1: ใส่ข้อมูลสำคัญใน Excel

ในเบื้องต้น เราต้องใส่ข้อมูลที่จำเป็นทั้งหมด

• ขั้นแรก เราวางช่วงเวลา (Periods) ในคอลัมน์ B และเราใส่ 10 ช่วงเวลาเพื่อคำนวณ Exponential Smoothing
• ขั้นที่สอง ใส่ความต้องการ (Demand) สำหรับแต่ละช่วงเวลา
• ขั้นที่สาม ใส่ Smoothing Constant (ค่าคงที่การทำให้เรียบ) ซึ่งคือ Alpha (α) ในกรณีของเรา ค่า Alpha คือ 20% หรือ 0.2
• ขั้นที่สี่ วาง Trend Factor (ปัจจัยแนวโน้ม) ซึ่งคือ Beta (β) ในกรณีนี้ ค่า Beta คือ 30% หรือ 0.3

ขั้นตอนที่ 2: คำนวณ Exponential Smoothing Forecast (พยากรณ์แบบทำให้เรียบเนียน)

ฟีเจอร์ Exponential Smoothing ของ Excel ค่อนข้างเข้าใจง่ายและตรงไปตรงมา เราสามารถทำได้ง่ายด้วยเครื่องมือ และด้วยสมการหรือสูตร ขั้นแรก เราจะเห็นการหา Exponential Smoothing ด้วยเครื่องมือ Data Analysis ของ Excel

• เพื่อเริ่มต้น ไปที่แท็บ Data (ข้อมูล) จากริบบอน
• จากนั้น คลิกที่ฟีเจอร์ Data Analysis ภายใต้หมวด Analysis (การวิเคราะห์)

• ผลลัพธ์ จะแสดงกล่องโต้ตอบ Data Analysis
• เลื่อนลงเล็กน้อย และเลือก Exponential Smoothing
• หลังจากนั้น คลิก OK

• ดังนั้น กล่องโต้ตอบ Exponential Smoothing จะเปิดขึ้น
• ตอนนี้ วาง Input Range (ช่วงข้อมูลนำเข้า) และ Damping factor (ตัวคูณลดทอน) ในส่วน Input (นำเข้า) ในกรณีของเรา เราเลือกช่วง Demand (ความต้องการ) ($C$5:$C$14) และวาง 0.2 เป็นตัวคูณลดทอนของเรา
• เพิ่มเติม วาง Output Range (ช่วงข้อมูลส่งออก) ใน Output options (ตัวเลือกส่งออก) ที่เราต้องการดูผลลัพธ์ Exponential Smoothing ของเรา
• เพิ่มเติม หากคุณต้องการดูผลลัพธ์ในแผนภูมิด้วย ให้ติ๊กเช็คบ็อกซ์ Chart Output (ผลลัพธ์แผนภูมิ)
• สุดท้าย คลิกที่ปุ่ม OK เพื่อเสร็จสิ้นกลยุทธ์

• ดังนั้น เราสามารถเห็นได้ว่า ผลลัพธ์ Exponential Smoothing แสดงในช่วงส่งออกที่เราเลือก และแผนภูมิมองเห็นได้
• แต่ มีปัญหาในการใช้สิ่งนี้ เนื่องจาก Exponential Smoothing ช่วงแรกแสดงข้อผิดพลาด #N/A ดังนั้น ขณะคำนวณแนวโน้ม เราจะได้ข้อผิดพลาด #N/A ในค่าแนวโน้มทั้งหมด

ตามลำดับ ดีกว่าที่จะใช้สูตรของ Exponential Smoothing แต่มีปัญหาเล็กน้อยอีกว่า เราไม่ได้รับผลลัพธ์ Exponential Smoothing ที่แม่นยำ แต่ผลลัพธ์จะค่อนข้างคล้ายกับที่แม่นยำ มาดูคำแนะนำในการคำนวณ Exponential Smoothing ด้วยสูตร

• ขั้นแรก เราใช้ค่าความต้องการ (Demand) เป็น Exponential Smoothing ช่วงแรก ดังนั้น เราเลือกเซลล์ D5 และใส่สูตรง่ายๆ ลงในเซลล์นั้น

=C5

• กด Enter

• ขั้นที่สอง เลือกเซลล์ที่สอง D6 และใส่สูตรลงในเซลล์ที่เลือก

=C5*$D$16+(1-$D$16)*D5

• กดปุ่ม Enter เพื่อเสร็จสิ้นกระบวนการ

• ตอนนี้ ลาก Fill Handle (จุดจับเติม) ลงเพื่อคัดลอกสูตรลงในช่วง หรือ AutoFill (เติมอัตโนมัติ) ช่วงโดยดับเบิลคลิกที่เครื่องหมายบวก (+)

• สุดท้าย คุณสามารถเห็นผลลัพธ์ของ Exponential Smoothing

• เพิ่มเติม เราต้องการลดทศนิยม สำหรับสิ่งนี้ ไปที่แท็บ Home (หน้าแรก) บนริบบอน
• จากนั้น คลิกที่ Decrease Decimal (ลดทศนิยม) ในกลุ่ม Number (ตัวเลข)

• เสร็จแล้ว! เราสามารถเห็นผลลัพธ์ Exponential Smoothing

ขั้นตอนที่ 3: คำนวณ Trend (แนวโน้ม)

ตอนนี้ เราจะคำนวณ Trend (แนวโน้ม) Trend คือโครงสร้างในข้อมูลที่แสดงว่าลำดับเคลื่อนที่ผ่านช่วงเวลาไปสู่ค่าที่ค่อนข้างมากกว่าเดิมตามปกติ

• เลือกเซลล์ E6 และแทรกสูตรเพื่อคำนวณแนวโน้มในเซลล์นั้น

=$D$17*(D6-D5)+(1-$D$17)*E5

• กดปุ่ม Enter บนคีย์บอร์ดของคุณ
• ผลลัพธ์จะแสดงในเซลล์ที่เลือก พร้อมกับสูตรในแถบสูตร

• เพิ่มเติม เพื่อคัดลอกสูตรลงในช่วง ให้ลาก Fill Handle ลง หรือ ดับเบิลคลิก ที่เครื่องหมายบวก (+) เพื่อ AutoFill ช่วง

• เราวาง 0 ใน Trend ช่วงแรก และช่วงเวลาอื่นๆ ของ Trend ผลลัพธ์จากการใช้สูตร

ขั้นตอนที่ 4: หา Adjusted Exponential Smoothing (การทำให้เรียบเนียนแบบปรับแล้ว)

ตอนนี้ เราจะคำนวณ Adjusted Exponential Smoothing โดยการบวก Exponential Smoothing และ Trend

• ในเบื้องต้น เลือกเซลล์ที่คุณต้องการดูผลลัพธ์ เราทิ้งเซลล์ Adjusted Exponential Smoothing ช่วงแรกว่างไว้ ดังนั้นเราเลือกเซลล์ช่วงที่สอง
• ใส่สูตรการบวกลงในเซลล์ F6

=D6+E6

• จากนั้น กดปุ่ม Enter บนคีย์บอร์ดของคุณ
• ตอนนี้ คุณจะสามารถเห็นผลลัพธ์ในเซลล์ที่เลือก และสูตรจะปรากฏในแถบสูตร

• เพิ่มเติม เพื่อคัดลอกสูตรลงในช่วง ให้ลาก Fill Handle ลง หรือ ดับเบิลคลิก ที่เครื่องหมายบวก (+) เพื่อ AutoFill ช่วง

• สุดท้าย เราวางตัวเลขเท่ากับความต้องการและ Exponential Smoothing ในช่วงแรกของ Adjusted Exponential Smoothing ของเรา
• และ นั่นคือเสร็จสิ้น! โดยเพียงปฏิบัติตามขั้นตอนด้านบน เราสามารถคำนวณ Adjusted Exponential Smoothing ใน Excel ได้

ขั้นตอนที่ 5: แทรกแผนภูมิ (Chart)

สุดท้าย เพื่อจินตทัศน์ (Visualize) ค่าใช้จ่ายบ่อยครั้ง เราสามารถแทรก Chart (แผนภูมิ) ได้ Charts เป็นเครื่องมือยอดนิยมสำหรับการส่องสว่างความเชื่อมโยงของข้อมูลแบบกราฟิก เราใช้แผนภูมิเพื่อสื่อสารข้อมูลที่มากเกินไปหรือซับซ้อนเกินไปที่จะแสดงในข้อความอย่างเต็มที่ ในขณะที่ใช้พื้นที่น้อยกว่า

• ขั้นแรก เลือกข้อมูลที่เราต้องการขณะแทรกแผนภูมิ ในกรณีของเรา เราเลือก Period (ช่วงเวลา), Demand (ความต้องการ), Exponential Smoothing, และ Adjusted Exponential Smoothing ด้วย

• ขั้นที่สอง ไปที่แท็บ Insert (แท็บแทรก) จากริบบอน
• หลังจากนั้น คลิกที่เมนูแบบเลื่อนลง Insert Scatter (X, Y) or Bubble Chart (แทรกแผนภูมิกระจาย หรือฟอง) ภายใต้กลุ่ม Charts (แผนภูมิ)
• เลือก Scatter with Straight Lines and Markers (กระจายพร้อมเส้นตรงและเครื่องหมาย) ซึ่งเป็นตัวเลือกแถวที่สองตัวแรกของ Scatter

• สิ่งนี้จะสร้างแผนภูมิเพื่อจินตทัศน์การคำนวณ Trend-Adjusted Exponential Smoothing บ่อยครั้ง

ผลลัพธ์สุดท้ายของ Trend Adjusted Exponential Smoothing

นี่คือผลลัพธ์สุดท้ายของ Trend-Adjusted Exponential Smoothing พร้อมแผนภูมิ

สิ่งที่ควรจำ

• ยอดสูงและยอดต่ำในข้อมูลจะถูกทำให้เรียบเนียนเมื่อตัวคูณลดทอน (Damping Factor) เพิ่มขึ้น
• Excel Exponential Smoothing เป็นแนวทางที่ยืดหยุ่นและคำนวณง่ายมาก
• ตัวคูณลดทอนมากกว่า ค่า Alpha น้อยกว่า ผลลัพธ์ ยอดสูงและยอดต่ำถูกทำให้เรียบเนียนมากที่สุด
• ตัวคูณลดทอนน้อยกว่า และค่า Alpha มากกว่า ผลลัพธ์การทำให้เรียบเนียนจึงคล้ายกับจุดตัวอย่างจริงมากกว่า

สรุป

ขั้นตอนด้านบนจะช่วยให้คุณ คำนวณ Trend Adjusted Exponential Smoothing ใน Excel หวังว่าจะช่วยคุณได้ โปรดแจ้งให้เราทราบในส่วนความคิดเห็น หากคุณมีคำถาม ข้อเสนอแนะ หรือข้อป้อนกลับ หรือคุณสามารถอ่านบทความอื่นๆ ของเราในบล็อก ExcelDemy.com ได้!