การแปลง box-cox ใน excel (ทีละขั้นตอน)

การแปลงแบบ box-cox เป็นวิธีการที่ใช้กันทั่วไปในการแปลงชุดข้อมูลที่ไม่กระจายแบบปกติให้เป็นชุด แบบกระจายแบบปกติ มากขึ้น

แนวคิดพื้นฐานคือการหาค่าของ γ เพื่อให้ข้อมูลที่แปลงแล้วใกล้เคียงกับการแจกแจงแบบปกติมากที่สุด โดยใช้สูตรต่อไปนี้:

• y(แลมบ์ดา) = (y ^แลม – 1) / แลม ถ้า y ≠ 0
• y(แลมบ์ดา) = log(y) ถ้า y = 0

ตัวอย่างทีละขั้นตอนต่อไปนี้แสดงวิธีดำเนินการแปลง box-cox บนชุดข้อมูลใน Excel

ขั้นตอนที่ 1: ป้อนข้อมูล

เริ่มต้นด้วยการป้อนค่าจากชุดข้อมูล:

ขั้นตอนที่ 2: จัดเรียงข้อมูล

จากนั้นสร้างคอลัมน์ดัชนีและคอลัมน์ข้อมูลที่เรียงลำดับ:

ขั้นตอนที่ 3: เลือกค่าที่กำหนดเองสำหรับ Lambda

ต่อไป เราจะเลือกค่าที่กำหนดเองเป็น 1 สำหรับ lambda และใช้การแปลง box-cox ชั่วคราวกับข้อมูล:

ขั้นตอนที่ 4: คำนวณคะแนน Z

ต่อไป เราจะคำนวณคะแนน z สำหรับแต่ละค่าดัชนี:

จากนั้นเราจะคำนวณความสัมพันธ์ระหว่างค่าที่แปลงแล้วของ box-cox และคะแนน z:

ขั้นตอนที่ 5: ค้นหาค่า Lambda ที่เหมาะสมที่สุด

ต่อไป เราจะใช้ Goal Seek เพื่อค้นหาค่า lambda ที่เหมาะสมที่สุดเพื่อใช้ในการแปลง box-cox

โดยคลิกที่แท็บ ข้อมูล บริเวณริบบิ้นด้านบน จากนั้นคลิก What-If-Analysis ในกลุ่ม การคาดการณ์

ในเมนูแบบเลื่อนลง คลิก การค้นหาเป้าหมาย และกรอกค่าต่อไปนี้:

เมื่อคุณคลิก ตกลง Goal Seek จะค้นหาค่าแลมบ์ดาที่เหมาะสมที่สุดซึ่งก็คือ -0.5225 โดยอัตโนมัติ

ขั้นตอนที่ 6: ทำการแปลง Box-Cox

สุดท้ายนี้ เราจะใช้การแปลง box-cox กับข้อมูลต้นฉบับ โดยใช้ค่าแลมบ์ดาที่ -0.5225:

โบนัส: เราสามารถยืนยันได้ว่าข้อมูลที่แปลงแล้วมีการกระจายตามปกติโดยทำการ ทดสอบ Jarque-Bera ใน Excel

แหล่งข้อมูลเพิ่มเติม

วิธีแปลงข้อมูลใน Excel (บันทึก, สแควร์รูท, รูทคิวบ์)
วิธีการคำนวณคะแนน Z ใน Excel