วิธีคำนวณส่วนเบี่ยงเบนมาตรฐานใน Excel ความแปรปรวนและส่วนเบี่ยงเบนมาตรฐานใน MS EXCEL วิดีโอเพื่อช่วย
บ้าน
อันเดรย์ ลิโปฟ ถ้าเราคุยกันในภาษาง่ายๆ
สามารถและควรใช้ในการวิเคราะห์ชุดของค่า เนื่องจากสองชุดที่มีค่าเฉลี่ยเท่ากันอาจกลายเป็นความแตกต่างอย่างสิ้นเชิงในการแพร่กระจายของค่า
ตัวอย่าง
ลองใช้ตัวเลขสองแถวกันก) 1,2,3,4,5,6,7,8,9
- เฉลี่ย - 5. เซนต์ ส่วนเบี่ยงเบน = 2.7386ข) 20,1,7,1,15,-1,-20,4,18,5
- เฉลี่ย - 5. เซนต์ ส่วนเบี่ยงเบน = 12.2066
หากคุณไม่เก็บตัวเลขทั้งชุดไว้ต่อหน้าต่อตา ตัวบ่งชี้ค่าเบี่ยงเบนมาตรฐานจะแสดงว่าในกรณี "b" ค่าจะกระจัดกระจายไปตามค่าเฉลี่ยมากกว่ามาก
พูดโดยประมาณในซีรีส์ "b" ค่าคือ 5 บวกหรือลบ 12 (โดยเฉลี่ย) - ไม่แน่นอน แต่เผยให้เห็นความหมาย
วิธีการคำนวณค่าเบี่ยงเบนมาตรฐาน
ในการคำนวณค่าเบี่ยงเบนมาตรฐาน คุณสามารถใช้สูตรที่ยืมมาจากการคำนวณค่าเบี่ยงเบนมาตรฐานของผลตอบแทนของกองทุนรวม:
โดยที่ N คือจำนวนปริมาณ
DOHaverage - ค่าเฉลี่ยของค่าทั้งหมด
ระยะเวลา DOH - ค่า N
ใน Excel ฟังก์ชันที่เกี่ยวข้องเรียกว่า STANDARDEVAL (หรือ STDEV ในโปรแกรมเวอร์ชันภาษาอังกฤษ)
- คำแนะนำทีละขั้นตอนมีดังนี้:
- คำนวณค่าเฉลี่ยสำหรับชุดตัวเลข
- สำหรับแต่ละค่า ให้กำหนดความแตกต่างระหว่างค่าเฉลี่ยและค่านั้น
- คำนวณผลรวมของกำลังสองของความแตกต่างเหล่านี้
- หารผลรวมผลลัพธ์ด้วยจำนวนตัวเลขในชุดข้อมูล เอารากที่สอง
จากจำนวนที่ได้รับในย่อหน้าสุดท้าย
เพื่อนของคุณจะพบว่าข้อมูลนี้มีประโยชน์ แบ่งปันกับพวกเขา!
ค่าเบี่ยงเบนมาตรฐานเป็นหนึ่งในคำศัพท์ทางสถิติในโลกธุรกิจที่ให้ความน่าเชื่อถือแก่ผู้ที่จัดการเพื่อดึงมันออกมาได้ดีในการสนทนาหรือการนำเสนอ ขณะเดียวกันก็ทิ้งความรู้สึกสับสนที่คลุมเครือในหมู่ผู้ที่ไม่รู้ว่ามันคืออะไรแต่ก็เช่นกัน อายที่จะถาม ในความเป็นจริง ผู้จัดการส่วนใหญ่ไม่เข้าใจแนวคิดเรื่องค่าเบี่ยงเบนมาตรฐาน และหากคุณเป็นหนึ่งในนั้น ก็ถึงเวลาที่คุณต้องหยุดใช้ชีวิตแบบโกหก ในบทความวันนี้ ฉันจะบอกคุณว่าการวัดทางสถิติที่ประเมินค่าต่ำเกินไปนี้สามารถช่วยให้คุณเข้าใจข้อมูลที่คุณกำลังทำงานด้วยได้ดีขึ้นได้อย่างไร
ลองนึกภาพว่าคุณเป็นเจ้าของร้านค้าสองแห่ง และเพื่อหลีกเลี่ยงการขาดทุน การควบคุมยอดคงเหลือในสต๊อกให้ชัดเจนเป็นสิ่งสำคัญ ด้วยความพยายามที่จะค้นหาว่าผู้จัดการคนใดจัดการสินค้าคงคลังได้ดีกว่า คุณจึงตัดสินใจวิเคราะห์สินค้าคงคลังในช่วง 6 สัปดาห์ที่ผ่านมา ต้นทุนสต็อกโดยเฉลี่ยรายสัปดาห์สำหรับร้านค้าทั้งสองแห่งอยู่ที่ประมาณเท่ากันและอยู่ที่ประมาณ 32 หน่วยธรรมดา- เมื่อมองแวบแรก ปริมาณการไหลบ่าโดยเฉลี่ยแสดงให้เห็นว่าผู้จัดการทั้งสองมีการปฏิบัติงานที่คล้ายคลึงกัน
แต่ถ้าคุณพิจารณากิจกรรมของร้านที่สองให้ละเอียดยิ่งขึ้น คุณจะมั่นใจได้ว่าแม้ว่าค่าเฉลี่ยจะถูกต้อง แต่ความแปรปรวนของหุ้นก็สูงมาก (ตั้งแต่ 10 ถึง 58 USD) ดังนั้นเราจึงสรุปได้ว่าค่าเฉลี่ยไม่ได้ประเมินข้อมูลอย่างถูกต้องเสมอไป นี่คือที่มาของค่าเบี่ยงเบนมาตรฐาน
ค่าเบี่ยงเบนมาตรฐานจะแสดงวิธีการกระจายค่าสัมพันธ์กับค่าเฉลี่ยในของเรา กล่าวอีกนัยหนึ่ง คุณสามารถเข้าใจได้ว่าการแพร่กระจายของน้ำท่าในแต่ละสัปดาห์มีขนาดใหญ่เพียงใด
ในตัวอย่างของเรา เราใช้ฟังก์ชัน STANDARDDEVAL ของ Excel เพื่อคำนวณค่าเบี่ยงเบนมาตรฐานพร้อมกับค่าเฉลี่ย
ในกรณีของผู้จัดการคนแรก ค่าเบี่ยงเบนมาตรฐานคือ 2 ซึ่งบอกเราว่าโดยเฉลี่ยแต่ละค่าในกลุ่มตัวอย่างเบี่ยงเบนไปจากค่าเฉลี่ย 2 เรื่องนี้ดีมั้ย? ลองดูคำถามจากมุมที่ต่างออกไป ค่าเบี่ยงเบนมาตรฐานเป็น 0 บอกเราว่าแต่ละค่าในกลุ่มตัวอย่างเท่ากับค่าเฉลี่ย (ในกรณีของเรา 32.2) ดังนั้นค่าเบี่ยงเบนมาตรฐานของ 2 จึงไม่แตกต่างจาก 0 มากนัก แสดงว่าค่าส่วนใหญ่ใกล้เคียงกับค่าเฉลี่ย ยิ่งค่าเบี่ยงเบนมาตรฐานเข้าใกล้ 0 มากเท่าใด ค่าเฉลี่ยก็ยิ่งน่าเชื่อถือมากขึ้นเท่านั้น นอกจากนี้ ค่าเบี่ยงเบนมาตรฐานใกล้กับ 0 บ่งชี้ถึงความแปรปรวนเล็กน้อยในข้อมูล นั่นคือค่าการไหลบ่าที่มีค่าเบี่ยงเบนมาตรฐานเป็น 2 บ่งบอกถึงความสอดคล้องที่น่าทึ่งของผู้จัดการคนแรก
ในกรณีร้านที่สอง ค่าเบี่ยงเบนมาตรฐานคือ 18.9 นั่นคือต้นทุนของน้ำที่ไหลบ่าโดยเฉลี่ยเบี่ยงเบนไป 18.9 จากค่าเฉลี่ยในแต่ละสัปดาห์ แพร่กระจายบ้า! ยิ่งค่าเบี่ยงเบนมาตรฐานอยู่ห่างจาก 0 มากเท่าใด ค่าเฉลี่ยก็ยิ่งมีความแม่นยำน้อยลงเท่านั้น ในกรณีของเรา ตัวเลข 18.9 บ่งชี้ว่าค่าเฉลี่ย (32.8 USD ต่อสัปดาห์) ไม่สามารถเชื่อถือได้ นอกจากนี้ยังบอกเราว่าการไหลบ่ารายสัปดาห์มีความผันแปรสูง
นี่คือแนวคิดเรื่องค่าเบี่ยงเบนมาตรฐานโดยสรุป แม้ว่าจะไม่ได้ให้ข้อมูลเชิงลึกเกี่ยวกับการวัดทางสถิติที่สำคัญอื่นๆ (โหมด, ค่ามัธยฐาน...) แต่จริงๆ แล้ว ค่าเบี่ยงเบนมาตรฐานมีบทบาทสำคัญในการคำนวณทางสถิติส่วนใหญ่ การทำความเข้าใจหลักการของค่าเบี่ยงเบนมาตรฐานจะช่วยให้กระบวนการทางธุรกิจหลายอย่างของคุณกระจ่างขึ้น
จะคำนวณค่าเบี่ยงเบนมาตรฐานได้อย่างไร?
ทีนี้เรารู้แล้วว่าเลขเบี่ยงเบนมาตรฐานบอกอะไร ลองคิดดูว่ามันคำนวณอย่างไร
ลองดูชุดข้อมูลตั้งแต่ 10 ถึง 70 โดยเพิ่มขั้นละ 10 ดังที่คุณเห็น เราได้คำนวณค่าเบี่ยงเบนมาตรฐานสำหรับข้อมูลเหล่านั้นแล้วโดยใช้ฟังก์ชัน STANDARDEV ในเซลล์ H2 (สีส้ม)
ด้านล่างนี้เป็นขั้นตอนที่ Excel ใช้เพื่อไปถึงเวอร์ชัน 21.6
โปรดทราบว่าการคำนวณทั้งหมดจะแสดงเป็นภาพเพื่อความเข้าใจที่ดีขึ้น ในความเป็นจริง ใน Excel การคำนวณจะเกิดขึ้นทันที โดยละขั้นตอนทั้งหมดไว้เบื้องหลัง
ขั้นแรก Excel จะค้นหาค่าเฉลี่ยตัวอย่าง ในกรณีของเรา ค่าเฉลี่ยกลายเป็น 40 ซึ่งในขั้นตอนถัดไปจะถูกลบออกจากค่าตัวอย่างแต่ละค่า ความแตกต่างแต่ละอย่างที่ได้รับจะถูกยกกำลังสองและสรุปผล เรามีผลรวมเท่ากับ 2800 ซึ่งต้องหารด้วยจำนวนองค์ประกอบตัวอย่างลบ 1 เนื่องจากเรามี 7 องค์ประกอบ ปรากฎว่าเราต้องหาร 2800 ด้วย 6 จากผลลัพธ์ที่ได้ เราพบรากที่สอง นี่ ตัวเลขจะเป็นค่าเบี่ยงเบนมาตรฐาน
สำหรับผู้ที่ไม่ชัดเจนเกี่ยวกับหลักการคำนวณค่าเบี่ยงเบนมาตรฐานโดยใช้การแสดงภาพ ฉันจะให้การตีความทางคณิตศาสตร์เพื่อค้นหาค่านี้
ฟังก์ชั่นสำหรับคำนวณค่าเบี่ยงเบนมาตรฐานใน Excel
Excel มีสูตรค่าเบี่ยงเบนมาตรฐานหลายประเภท สิ่งที่คุณต้องทำคือพิมพ์ =STDEV แล้วคุณจะเห็นเอง
เป็นที่น่าสังเกตว่าฟังก์ชัน STDEV.V และ STDEV.G (ฟังก์ชันแรกและฟังก์ชันที่สองในรายการ) ซ้ำกับฟังก์ชัน STDEV และ STDEV (ฟังก์ชันที่ห้าและหกในรายการ) ตามลำดับ ซึ่งยังคงอยู่เพื่อให้เข้ากันได้กับฟังก์ชันก่อนหน้า เวอร์ชันของ Excel
โดยทั่วไป ความแตกต่างในการลงท้ายของฟังก์ชัน .B และ .G บ่งบอกถึงหลักการคำนวณค่าเบี่ยงเบนมาตรฐานของกลุ่มตัวอย่างหรือประชากร ฉันได้อธิบายความแตกต่างระหว่างอาร์เรย์ทั้งสองนี้ไปแล้วในอันก่อนหน้า
คุณสมบัติพิเศษของฟังก์ชัน STANDARDEV และ STANDARDEV (ฟังก์ชันที่สามและสี่ในรายการ) คือเมื่อคำนวณค่าเบี่ยงเบนมาตรฐานของอาร์เรย์ ค่าตรรกะและข้อความจะถูกนำมาพิจารณาด้วย ข้อความและเป็นความจริง ค่าบูลีนคือ 1 และบูลีนเท็จคือ 0 ฉันจินตนาการไม่ออกว่าจะต้องใช้ฟังก์ชันทั้งสองนี้ในสถานการณ์ใด ดังนั้นฉันคิดว่าสามารถละเลยฟังก์ชันเหล่านี้ได้
ค่าเบี่ยงเบนมาตรฐานเป็นตัวบ่งชี้คลาสสิกของความแปรปรวนจากสถิติเชิงพรรณนา
ส่วนเบี่ยงเบนมาตรฐาน, ส่วนเบี่ยงเบนมาตรฐาน, ส่วนเบี่ยงเบนมาตรฐาน, ส่วนเบี่ยงเบนมาตรฐานตัวอย่าง (อังกฤษ ส่วนเบี่ยงเบนมาตรฐาน, STD, STDev) - ตัวบ่งชี้การกระจายตัวทั่วไปในสถิติเชิงพรรณนา แต่เพราะว่า การวิเคราะห์ทางเทคนิคก็เหมือนกับสถิติ ตัวบ่งชี้นี้สามารถ (และควร) ใช้ในการวิเคราะห์ทางเทคนิคเพื่อตรวจจับระดับการกระจายตัวของราคาของเครื่องมือที่วิเคราะห์เมื่อเวลาผ่านไป แสดงด้วยสัญลักษณ์กรีกซิกมา "σ"
ขอบคุณคาร์ล เกาส์และเพียร์สันที่ให้เราใช้ค่าเบี่ยงเบนมาตรฐาน
โดยใช้ ส่วนเบี่ยงเบนมาตรฐานในการวิเคราะห์ทางเทคนิคเราหมุนสิ่งนี้ "ดัชนีการกระจายตัว""วี “ตัวชี้วัดความผันผวน“คงความหมายแต่เปลี่ยนเงื่อนไข
ค่าเบี่ยงเบนมาตรฐานคืออะไร
แต่นอกเหนือจากการคำนวณเสริมระดับกลางแล้ว ค่าเบี่ยงเบนมาตรฐานค่อนข้างยอมรับได้สำหรับการคำนวณแบบอิสระและการประยุกต์ในการวิเคราะห์ทางเทคนิค ในฐานะผู้อ่านนิตยสารหญ้าเจ้าชู้ของเรากล่าวว่า “ ฉันยังไม่เข้าใจว่าเหตุใดค่าเบี่ยงเบนมาตรฐานจึงไม่รวมอยู่ในชุดตัวบ่งชี้มาตรฐานของศูนย์ซื้อขายในประเทศ«.
จริงหรือ, ค่าเบี่ยงเบนมาตรฐานสามารถวัดความแปรปรวนของเครื่องมือด้วยวิธีคลาสสิกและ "บริสุทธิ์"- แต่น่าเสียดายที่ตัวบ่งชี้นี้ไม่ได้พบเห็นได้ทั่วไปในการวิเคราะห์หลักทรัพย์
การใช้ค่าเบี่ยงเบนมาตรฐาน
การคำนวณค่าเบี่ยงเบนมาตรฐานด้วยตนเองนั้นไม่น่าสนใจนักแต่มีประโยชน์สำหรับประสบการณ์ ค่าเบี่ยงเบนมาตรฐานสามารถแสดงได้สูตร STD=√[(∑(x-x ) 2)/n] ซึ่งฟังดูเหมือนรากของผลรวมของผลต่างกำลังสองระหว่างองค์ประกอบของตัวอย่างและค่าเฉลี่ย หารด้วยจำนวนองค์ประกอบในตัวอย่าง
หากจำนวนองค์ประกอบในกลุ่มตัวอย่างเกิน 30 ตัวส่วนของเศษส่วนที่อยู่ใต้รากจะใช้ค่า n-1 มิฉะนั้นจะใช้ n
ทีละขั้นตอน การคำนวณค่าเบี่ยงเบนมาตรฐาน:
- คำนวณค่าเฉลี่ยเลขคณิตของตัวอย่างข้อมูล
- ลบค่าเฉลี่ยนี้ออกจากแต่ละองค์ประกอบตัวอย่าง
- เรายกกำลังสองผลต่างผลลัพธ์ทั้งหมด
- รวมผลกำลังสองทั้งหมด
- หารจำนวนผลลัพธ์ด้วยจำนวนองค์ประกอบในกลุ่มตัวอย่าง (หรือด้วย n-1 ถ้า n>30)
- คำนวณรากที่สองของผลหารผลลัพธ์ (เรียกว่า การกระจายตัว)
การแทรกแซงของฝ่ายบริหารเป็นสิ่งจำเป็นเพื่อระบุสาเหตุของการเบี่ยงเบน
ในการสร้างแผนภูมิควบคุม ฉันใช้ข้อมูลดิบ ค่าเฉลี่ย (μ) และส่วนเบี่ยงเบนมาตรฐาน (σ) ใน Excel: μ = AVERAGE($F$3:$F$15), σ = STANDARDDEVAL($F$3:$F$15)
แผนภูมิควบคุมประกอบด้วย: ข้อมูลดิบ ค่าเฉลี่ย (μ) ขีดจำกัดการควบคุมล่าง (μ – 2σ) และขีดจำกัดการควบคุมด้านบน (μ + 2σ):
ดาวน์โหลดบันทึกย่อในรูปแบบ ตัวอย่างในรูปแบบ
เมื่อดูแผนที่ที่นำเสนอ ฉันสังเกตเห็นว่าข้อมูลต้นฉบับแสดงแนวโน้มเชิงเส้นที่ชัดเจนอย่างมากต่อส่วนแบ่งต้นทุนค่าโสหุ้ยที่ลดลง:
หากต้องการเพิ่มเส้นแนวโน้ม ให้เลือกแถวที่มีข้อมูลบนแผนภูมิ (ในตัวอย่างของเรา จุดสีเขียว) คลิกขวาแล้วเลือกตัวเลือก "เพิ่มเส้นแนวโน้ม" ในหน้าต่างรูปแบบเส้นแนวโน้มที่เปิดขึ้น ให้ทดลองใช้ตัวเลือกต่างๆ ฉันตัดสินตามแนวโน้มเชิงเส้น
หากข้อมูลต้นฉบับไม่กระจัดกระจายรอบๆ ค่าเฉลี่ย การอธิบายด้วยพารามิเตอร์ μ และ σ จะไม่ถูกต้องทั้งหมด สำหรับคำอธิบาย แทนที่จะเป็นค่าเฉลี่ย เส้นแนวโน้มเชิงเส้นและขอบเขตการควบคุมที่อยู่ห่างจากเส้นแนวโน้มนี้เหมาะสมกว่า
Excel ช่วยให้คุณสร้างเส้นแนวโน้มโดยใช้ฟังก์ชัน FORECAST เราต้องการแถว A3:A15 เพิ่มเติมถึง ค่านิยมที่ทราบเอ็กซ์เป็นอนุกรมต่อเนื่อง (หมายเลขบล็อกไม่ได้สร้างอนุกรมต่อเนื่องเช่นนั้น) แทนที่จะใช้ค่าเฉลี่ยในคอลัมน์ H เราขอแนะนำฟังก์ชัน FORECAST:
ค่าเบี่ยงเบนมาตรฐาน σ (ฟังก์ชัน STDEVAL ใน Excel) คำนวณโดยใช้สูตร:
น่าเสียดายที่ฉันไม่พบฟังก์ชันใน Excel ที่จะกำหนดค่าส่วนเบี่ยงเบนมาตรฐาน (เทียบกับแนวโน้ม) ในลักษณะนี้ ปัญหาสามารถแก้ไขได้โดยใช้สูตรอาร์เรย์ สำหรับผู้ที่ไม่คุ้นเคยกับสูตรอาร์เรย์ผมแนะนำให้อ่านก่อน
สูตรอาร์เรย์สามารถส่งคืนค่าเดียวหรืออาร์เรย์ได้ ในกรณีของเรา สูตรอาร์เรย์จะส่งคืนค่าหนึ่งค่า:
มาดูวิธีการทำงานของสูตรอาร์เรย์ในเซลล์ G3 กันดีกว่า
SUM(($F$3:$F$15-$H$3:$H$15)^2) เป็นตัวกำหนดผลรวมของผลต่างกำลังสอง ในความเป็นจริง สูตรคำนวณผลรวมต่อไปนี้ = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2
COUNTA($F$3:$F$15) – จำนวนค่าในช่วง F3:F15
SQRT(SUM(($F$3:$F$15-$H$3:$H$15)^2)/(COUNTA($F$3:$F$15)-1)) = σ
ค่า 6.2% คือจุดของขีดจำกัดการควบคุมล่าง = 8.3% – 2 σ
เครื่องหมายคำพูดแบบโค้งที่ด้านใดด้านหนึ่งของสูตรระบุว่าเป็นสูตรอาร์เรย์ หากต้องการสร้างสูตรอาร์เรย์ หลังจากป้อนสูตรในเซลล์ G3 แล้ว ให้ทำดังนี้
H4 – 2*รูท(SUM(($F$3:$F$15-$H$3:$H$15)^2)/(COUNT($F$3:$F$15)-1))
คุณต้องกดไม่ Enter แต่กด Ctrl + Shift + Enter อย่าพยายามใส่เครื่องหมายปีกกาจากแป้นพิมพ์ เพราะสูตรอาร์เรย์จะไม่ทำงาน หากคุณต้องการแก้ไขสูตรอาร์เรย์ ให้ทำในลักษณะเดียวกับสูตรทั่วไป แต่อีกครั้ง เมื่อแก้ไขเสร็จแล้ว ให้กด Ctrl + Shift + Enter แทน Enter
สูตรอาร์เรย์ที่ส่งคืนค่าเดียวสามารถ "ลาก" ได้เหมือนกับสูตรทั่วไป
เป็นผลให้เราได้รับแผนภูมิควบคุมที่สร้างขึ้นสำหรับข้อมูลที่มีแนวโน้มที่จะลดลง
ป.ล. หลังจากเขียนบันทึกย่อแล้ว ฉันสามารถปรับแต่งสูตรที่ใช้ในการคำนวณค่าเบี่ยงเบนมาตรฐานสำหรับข้อมูลที่กำลังมาแรงได้ คุณสามารถดูได้ในไฟล์ Excel