ส่วนเบี่ยงเบนมาตรฐานของ Excel ค่าเบี่ยงเบนมาตรฐานคืออะไร - การใช้ฟังก์ชันค่าเบี่ยงเบนมาตรฐานเพื่อคำนวณค่าเบี่ยงเบนมาตรฐานใน Excel

ในบทความนี้ฉันจะพูดถึง วิธีหาค่าเบี่ยงเบนมาตรฐาน. เนื้อหานี้มีความสำคัญอย่างยิ่งต่อการทำความเข้าใจคณิตศาสตร์อย่างถ่องแท้ ดังนั้นครูสอนพิเศษคณิตศาสตร์จึงควรจัดสรรบทเรียนแยกกันหรือหลายบทเรียนในการศึกษาเนื้อหานั้น ในบทความนี้ คุณจะพบลิงก์ไปยังวิดีโอสอนโดยละเอียดและเข้าใจได้ ซึ่งจะอธิบายว่าค่าเบี่ยงเบนมาตรฐานคืออะไร และจะหาได้อย่างไร

ส่วนเบี่ยงเบนมาตรฐานทำให้สามารถประเมินการแพร่กระจายของค่าที่ได้รับจากการวัดพารามิเตอร์บางตัวได้ ระบุด้วยสัญลักษณ์ (อักษรกรีก "ซิกมา")

สูตรการคำนวณค่อนข้างง่าย หากต้องการหาค่าเบี่ยงเบนมาตรฐาน คุณต้องหารากที่สองของความแปรปรวน ตอนนี้คุณต้องถามว่า “ความแปรปรวนคืออะไร”

ความแปรปรวนคืออะไร

นิยามของความแปรปรวนเป็นแบบนี้ การกระจายตัวคือค่าเฉลี่ยเลขคณิตของการเบี่ยงเบนกำลังสองของค่าจากค่าเฉลี่ย

หากต้องการค้นหาความแปรปรวน ให้ทำการคำนวณต่อไปนี้ตามลำดับ:

• กำหนดค่าเฉลี่ย (ค่าเฉลี่ยเลขคณิตอย่างง่ายของชุดค่า)
• จากนั้นลบค่าเฉลี่ยออกจากแต่ละค่าแล้วยกกำลังสองผลต่างผลลัพธ์ (คุณจะได้ ผลต่างกำลังสอง).
• ขั้นตอนต่อไปคือการคำนวณค่าเฉลี่ยเลขคณิตของผลต่างกำลังสองที่เกิดขึ้น (คุณสามารถดูสาเหตุที่แท้จริงของกำลังสองด้านล่าง)

ลองดูตัวอย่าง สมมติว่าคุณและเพื่อนๆ ตัดสินใจวัดส่วนสูงของสุนัข (หน่วยเป็นมิลลิเมตร) จากการวัด คุณจะได้รับการวัดความสูง (ที่ไหล่): 600 มม., 470 มม., 170 มม., 430 มม. และ 300 มม.

มาคำนวณค่าเฉลี่ย ความแปรปรวน และส่วนเบี่ยงเบนมาตรฐานกัน

ขั้นแรกให้หาค่าเฉลี่ยก่อน. ดังที่คุณทราบแล้วในการทำเช่นนี้คุณต้องบวกค่าที่วัดได้ทั้งหมดแล้วหารด้วยจำนวนการวัด ความคืบหน้าการคำนวณ:

เฉลี่ย มม.

ดังนั้นค่าเฉลี่ย (ค่าเฉลี่ยเลขคณิต) คือ 394 มม.

ตอนนี้เราจำเป็นต้องกำหนด ส่วนเบี่ยงเบนความสูงของสุนัขแต่ละตัวจากค่าเฉลี่ย:

ในที่สุด, เพื่อคำนวณความแปรปรวนเรายกกำลังสองผลต่างผลลัพธ์แต่ละรายการ จากนั้นค้นหาค่าเฉลี่ยเลขคณิตของผลลัพธ์ที่ได้รับ:

การกระจาย มม. 2 .

ดังนั้นการกระจายตัวคือ 21704 มม. 2

วิธีค้นหาค่าเบี่ยงเบนมาตรฐาน

แล้วเราจะคำนวณค่าเบี่ยงเบนมาตรฐานโดยรู้ความแปรปรวนได้อย่างไร? อย่างที่เราจำได้ ให้หาสแควร์รูทของมัน นั่นคือค่าเบี่ยงเบนมาตรฐานเท่ากับ:

Mm (ปัดเศษให้เป็นจำนวนเต็มที่ใกล้ที่สุดในหน่วย mm)

เมื่อใช้วิธีนี้ เราพบว่าสุนัขบางตัว (เช่น สุนัขร็อตไวเลอร์) เป็นสุนัขที่มีขนาดใหญ่มาก แต่ก็มีสุนัขตัวเล็กมากด้วย (เช่น ดัชชุนด์ แต่คุณไม่ควรบอกพวกมัน)

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

นั่นคือเมื่อใช้ค่าเบี่ยงเบนมาตรฐานเราได้รับวิธี "มาตรฐาน" ที่ช่วยให้เราสามารถค้นหาว่าค่าใดเป็นค่าปกติ (ค่าเฉลี่ยทางสถิติ) และค่าใดที่มีขนาดใหญ่เป็นพิเศษหรือในทางกลับกันมีขนาดเล็ก

ค่าเบี่ยงเบนมาตรฐานคืออะไร

แต่... ทุกอย่างจะแตกต่างออกไปเล็กน้อยหากเราวิเคราะห์ ตัวอย่างข้อมูล. ในตัวอย่างของเราเราพิจารณาแล้ว ประชากรทั่วไป.นั่นคือสุนัข 5 ตัวของเราเป็นสุนัขตัวเดียวในโลกที่สนใจเรา

แต่ถ้าข้อมูลเป็นตัวอย่าง (ค่าที่เลือกจากประชากรจำนวนมาก) การคำนวณจะต้องแตกต่างออกไป

หากมีค่าแล้ว:

การคำนวณอื่น ๆ ทั้งหมดดำเนินการในทำนองเดียวกัน รวมถึงการกำหนดค่าเฉลี่ยด้วย

ตัวอย่างเช่น หากสุนัขทั้งห้าของเราเป็นเพียงตัวอย่างจำนวนสุนัข (สุนัขทุกตัวบนโลก) เราจะต้องหารด้วย 4 ไม่ใช่ 5กล่าวคือ:

ความแปรปรวนตัวอย่าง = มม. 2

ในกรณีนี้ ค่าเบี่ยงเบนมาตรฐานสำหรับตัวอย่างจะเท่ากับ มม. (ปัดเศษให้เป็นจำนวนเต็มที่ใกล้ที่สุด)

เราสามารถพูดได้ว่าเราได้ “แก้ไข” บ้างแล้ว ในกรณีที่ค่านิยมของเราเป็นเพียงตัวอย่างเล็กๆ น้อยๆ เท่านั้น

บันทึก. เหตุใดผลต่างกำลังสองกันแน่?

แต่ทำไมเราถึงหาผลต่างกำลังสองเป๊ะๆ เมื่อคำนวณความแปรปรวน? สมมติว่าเมื่อวัดพารามิเตอร์บางตัว คุณได้รับชุดค่าต่อไปนี้: 4; 4; -4; -4. หากเราเพียงเพิ่มค่าเบี่ยงเบนสัมบูรณ์จากค่าเฉลี่ย (ความแตกต่าง) เข้าด้วยกัน... ค่าลบจะถูกยกเลิกด้วยค่าบวก:

.

ปรากฎว่าตัวเลือกนี้ไม่มีประโยชน์ ถ้าอย่างนั้นมันก็คุ้มค่าที่จะลองใช้ค่าสัมบูรณ์ของการเบี่ยงเบน (นั่นคือโมดูลของค่าเหล่านี้)

เมื่อมองแวบแรกปรากฎว่าดี (ค่าผลลัพธ์เรียกว่าค่าเฉลี่ยส่วนเบี่ยงเบนสัมบูรณ์) แต่ไม่ใช่ในทุกกรณี ลองอีกตัวอย่างหนึ่ง ให้ผลการวัดเป็นชุดค่าต่อไปนี้: 7; 1; -6; -2. จากนั้นค่าเบี่ยงเบนสัมบูรณ์เฉลี่ยคือ:

ว้าว! อีกครั้งที่เราได้รับผลลัพธ์เป็น 4 แม้ว่าความแตกต่างจะมีสเปรดที่ใหญ่กว่ามากก็ตาม

ทีนี้มาดูว่าจะเกิดอะไรขึ้นถ้าเรายกกำลังสองส่วนต่าง (แล้วหารากที่สองของผลรวมของมัน)

สำหรับตัวอย่างแรกจะเป็น:

.

สำหรับตัวอย่างที่สอง มันจะเป็น:

ตอนนี้มันเป็นเรื่องที่แตกต่างไปจากเดิมอย่างสิ้นเชิง! ยิ่งการแพร่กระจายของความแตกต่างมากขึ้น ค่าเบี่ยงเบนมาตรฐานก็จะยิ่งมากขึ้น... ซึ่งเป็นสิ่งที่เราตั้งเป้าไว้

ที่จริงแล้ว วิธีการนี้ใช้แนวคิดเดียวกันกับเมื่อคำนวณระยะห่างระหว่างจุด แต่ใช้วิธีอื่นเท่านั้น

และจากมุมมองทางคณิตศาสตร์ การใช้กำลังสองและรากที่สองให้ประโยชน์มากกว่าที่เราจะได้รับจากค่าเบี่ยงเบนสัมบูรณ์ ทำให้ค่าเบี่ยงเบนมาตรฐานใช้ได้กับปัญหาทางคณิตศาสตร์อื่นๆ

เซอร์เกย์ วาเลรีวิชบอกคุณถึงวิธีหาค่าเบี่ยงเบนมาตรฐาน

อันเดรย์ ลิโปฟ

กล่าวง่ายๆ ก็คือ ค่าเบี่ยงเบนมาตรฐานจะแสดงว่าราคาของตราสารมีความผันผวนมากน้อยเพียงใดเมื่อเวลาผ่านไป นั่นคือ ยิ่งตัวบ่งชี้นี้สูงเท่าใด ความผันผวนหรือความแปรปรวนของค่าจำนวนหนึ่งก็จะยิ่งมากขึ้นเท่านั้น

ค่าเบี่ยงเบนมาตรฐานสามารถและควรใช้ในการวิเคราะห์ชุดของค่า เนื่องจากชุดสองชุดที่มีค่าเฉลี่ยเท่ากันอาจกลายเป็นความแตกต่างอย่างสิ้นเชิงในการกระจายของค่า

ตัวอย่าง

ลองใช้ตัวเลขสองแถวกัน

ก) 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 ในโปรแกรมเวอร์ชันภาษาอังกฤษ)

คำแนะนำทีละขั้นตอนมีดังนี้:

1. คำนวณค่าเฉลี่ยสำหรับชุดตัวเลข
2. สำหรับแต่ละค่า ให้กำหนดความแตกต่างระหว่างค่าเฉลี่ยและค่านั้น
3. คำนวณผลรวมของกำลังสองของความแตกต่างเหล่านี้
4. หารผลรวมผลลัพธ์ด้วยจำนวนตัวเลขในชุดข้อมูล
5. หารากที่สองของจำนวนที่คุณได้รับในขั้นตอนสุดท้าย

เพื่อนของคุณจะพบว่าข้อมูลนี้มีประโยชน์ แบ่งปันกับพวกเขา!

เราต้องจัดการกับการคำนวณค่าต่างๆ เช่น การกระจายตัว ส่วนเบี่ยงเบนมาตรฐาน และแน่นอน ค่าสัมประสิทธิ์ของการแปรผัน เป็นการคำนวณหลังที่สมควรได้รับความสนใจเป็นพิเศษ เป็นสิ่งสำคัญมากที่ผู้เริ่มต้นทุกคนที่เพิ่งเริ่มทำงานกับโปรแกรมแก้ไขสเปรดชีตสามารถคำนวณขีดจำกัดสัมพัทธ์ของการแพร่กระจายของค่าได้อย่างรวดเร็ว

ค่าสัมประสิทธิ์ของการแปรผันคืออะไร และเหตุใดจึงต้องมี?

ดังนั้น สำหรับฉันแล้วดูเหมือนว่าการสำรวจเชิงทฤษฎีระยะสั้นและเข้าใจธรรมชาติของสัมประสิทธิ์ของการแปรผันจะเป็นประโยชน์ ตัวบ่งชี้นี้จำเป็นเพื่อสะท้อนช่วงของข้อมูลที่สัมพันธ์กับค่าเฉลี่ย กล่าวอีกนัยหนึ่ง มันแสดงอัตราส่วนของส่วนเบี่ยงเบนมาตรฐานต่อค่าเฉลี่ย โดยทั่วไปค่าสัมประสิทธิ์ของการแปรผันจะวัดเป็นเปอร์เซ็นต์และใช้เพื่อแสดงความเป็นเนื้อเดียวกันของอนุกรมเวลา

ค่าสัมประสิทธิ์ของการแปรผันจะกลายเป็นตัวช่วยที่ขาดไม่ได้เมื่อคุณต้องการคาดการณ์ตามข้อมูลจากตัวอย่างที่กำหนด ตัวบ่งชี้นี้จะเน้นชุดค่าหลักที่จะเป็นประโยชน์มากที่สุดสำหรับการคาดการณ์ในภายหลัง และจะล้างตัวอย่างปัจจัยที่ไม่สำคัญด้วย ดังนั้นหากคุณเห็นว่าค่าสัมประสิทธิ์เป็น 0% ให้ประกาศอย่างมั่นใจว่าอนุกรมนั้นเป็นเนื้อเดียวกันซึ่งหมายความว่าค่าทั้งหมดในนั้นมีค่าเท่ากัน หากค่าสัมประสิทธิ์ของการแปรผันมีค่าเกิน 33% แสดงว่าคุณกำลังเผชิญกับอนุกรมที่ต่างกันซึ่งแต่ละค่าแตกต่างอย่างมากจากค่าเฉลี่ยตัวอย่าง

จะหาค่าเบี่ยงเบนมาตรฐานได้อย่างไร?

เนื่องจากในการคำนวณดัชนีรูปแบบใน Excel เราจำเป็นต้องใช้ค่าเบี่ยงเบนมาตรฐาน จึงค่อนข้างเหมาะสมที่จะทราบว่าเราจะคำนวณพารามิเตอร์นี้ได้อย่างไร

จากหลักสูตรพีชคณิตของโรงเรียน เรารู้ว่าค่าเบี่ยงเบนมาตรฐานคือรากที่สองที่ดึงมาจากความแปรปรวน กล่าวคือ ตัวบ่งชี้นี้จะกำหนดระดับความเบี่ยงเบนของตัวบ่งชี้เฉพาะของกลุ่มตัวอย่างโดยรวมจากค่าเฉลี่ย ด้วยความช่วยเหลือนี้ เราสามารถวัดการวัดความผันผวนของคุณลักษณะที่กำลังศึกษาและตีความได้อย่างชัดเจน

การคำนวณค่าสัมประสิทธิ์ใน Excel

น่าเสียดายที่ Excel ไม่มีสูตรมาตรฐานที่ให้คุณคำนวณดัชนีรูปแบบต่างๆ ได้โดยอัตโนมัติ แต่นี่ไม่ได้หมายความว่าคุณต้องคำนวณในหัว การไม่มีเทมเพลตใน "แถบสูตร" จะไม่ส่งผลต่อความสามารถของ Excel ดังนั้นคุณจึงสามารถบังคับให้โปรแกรมทำการคำนวณที่คุณต้องการได้อย่างง่ายดายโดยการป้อนคำสั่งที่เหมาะสมด้วยตนเอง

ในการคำนวณดัชนีความผันแปรใน Excel คุณต้องจำหลักสูตรคณิตศาสตร์ระดับมัธยมศึกษาตอนปลายของคุณ และหารค่าเบี่ยงเบนมาตรฐานด้วยค่าเฉลี่ยตัวอย่าง ที่จริงแล้ว สูตรมีลักษณะดังนี้ - STANDARDEVAL(ช่วงข้อมูลที่ระบุ)/AVERAGE(ช่วงข้อมูลที่ระบุ) คุณต้องป้อนสูตรนี้ลงในเซลล์ Excel ที่คุณต้องการรับการคำนวณที่คุณต้องการ

อย่าลืมว่าเนื่องจากค่าสัมประสิทธิ์แสดงเป็นเปอร์เซ็นต์ จึงต้องจัดรูปแบบเซลล์ที่มีสูตรตามนั้น คุณสามารถทำได้ดังนี้:

1. เปิดแท็บ "หน้าแรก"
2. ค้นหาหมวดหมู่ "รูปแบบเซลล์" ในนั้นและเลือกตัวเลือกที่ต้องการ

หรือคุณสามารถกำหนดรูปแบบเปอร์เซ็นต์สำหรับเซลล์ได้โดยการคลิกขวาที่เซลล์ตารางที่เปิดใช้งาน ในเมนูบริบทที่ปรากฏขึ้น คล้ายกับอัลกอริทึมด้านบน คุณต้องเลือกหมวดหมู่ "รูปแบบเซลล์" และตั้งค่าที่ต้องการ

เลือกเปอร์เซ็นต์ และป้อนจำนวนตำแหน่งทศนิยมหากจำเป็น

บางทีอัลกอริธึมข้างต้นอาจดูซับซ้อนสำหรับบางคน ที่จริงแล้ว การคำนวณค่าสัมประสิทธิ์นั้นง่ายพอ ๆ กับการบวกตัวเลขธรรมชาติสองตัว เมื่อคุณทำงานนี้ใน Excel เสร็จแล้ว คุณจะไม่กลับไปใช้โซลูชันที่น่าเบื่อและซับซ้อนในสมุดบันทึกอีกต่อไป

ยังไม่สามารถเปรียบเทียบเชิงคุณภาพกับระดับการกระจายของข้อมูลได้ใช่หรือไม่ สับสนกับขนาดของกลุ่มตัวอย่างใช่ไหม? จากนั้นลงมือทำธุรกิจทันทีและฝึกฝนเนื้อหาทางทฤษฎีทั้งหมดที่นำเสนอข้างต้น! ให้การวิเคราะห์ทางสถิติและการพัฒนาการคาดการณ์ไม่ทำให้คุณรู้สึกกลัวและเป็นลบอีกต่อไป ประหยัดพลังงานและเวลาของคุณด้วย

สวัสดีตอนบ่าย

ในบทความนี้ ฉันตัดสินใจดูว่าค่าเบี่ยงเบนมาตรฐานทำงานอย่างไรใน Excel โดยใช้ฟังก์ชัน STANDARDEVAL ฉันไม่ได้อธิบายหรือแสดงความคิดเห็นเกี่ยวกับเรื่องนี้มาเป็นเวลานานแล้ว และเพียงเพราะว่านี่เป็นฟังก์ชันที่มีประโยชน์มากสำหรับผู้ที่เรียนคณิตศาสตร์ระดับสูง และการช่วยเหลือนักเรียนเป็นสิ่งศักดิ์สิทธิ์ ฉันรู้จากประสบการณ์ว่าการเรียนรู้เป็นเรื่องยากเพียงใด ในความเป็นจริง ฟังก์ชันค่าเบี่ยงเบนมาตรฐานสามารถใช้เพื่อกำหนดความเสถียรของผลิตภัณฑ์ที่ขาย สร้างราคา ปรับหรือจัดประเภท และการวิเคราะห์อื่นๆ ที่มีประโยชน์เท่าเทียมกันสำหรับการขายของคุณ

Excel ใช้ฟังก์ชันผลต่างนี้หลายรูปแบบ:

ทฤษฎีทางคณิตศาสตร์

ขั้นแรก อธิบายทฤษฎีเล็กน้อยว่าคุณสามารถอธิบายฟังก์ชันส่วนเบี่ยงเบนมาตรฐานในภาษาคณิตศาสตร์เพื่อใช้ใน Excel ได้อย่างไร เพื่อวิเคราะห์ เช่น ข้อมูลสถิติการขาย แต่จะอธิบายเพิ่มเติมในภายหลัง ฉันเตือนคุณทันที ฉันจะเขียนคำที่เข้าใจยากมากมาย...)))) หากมีสิ่งใดด้านล่างในข้อความ ให้มองหาการใช้งานจริงในโปรแกรมทันที

ค่าเบี่ยงเบนมาตรฐานทำหน้าที่อะไรกันแน่? โดยจะประมาณค่าเบี่ยงเบนมาตรฐานของตัวแปรสุ่ม X ที่สัมพันธ์กับการคาดการณ์ทางคณิตศาสตร์ โดยอิงจากการประมาณค่าความแปรปรวนที่เป็นกลาง เห็นด้วย ฟังดูน่าสับสน แต่ฉันคิดว่านักเรียนจะเข้าใจสิ่งที่เรากำลังพูดถึงจริงๆ!

ขั้นแรกเราต้องกำหนด "ค่าเบี่ยงเบนมาตรฐาน" เพื่อคำนวณ "ค่าเบี่ยงเบนมาตรฐาน" ในภายหลัง สูตรจะช่วยเราในเรื่องนี้: สูตรสามารถอธิบายได้ดังต่อไปนี้ โดยจะวัดในหน่วยเดียวกับการวัดตัวแปรสุ่ม และใช้ในการคำนวณค่าคลาดเคลื่อนของค่าเฉลี่ยเลขคณิตมาตรฐาน เมื่อสร้างช่วงความเชื่อมั่น เมื่อทดสอบสมมติฐานสำหรับสถิติ หรือเมื่อวิเคราะห์เชิงเส้น ความสัมพันธ์ระหว่างตัวแปรอิสระ ฟังก์ชันนี้ถูกกำหนดให้เป็นรากที่สองของความแปรปรวนของตัวแปรอิสระ

ตอนนี้เราสามารถกำหนดและ ส่วนเบี่ยงเบนมาตรฐานคือการวิเคราะห์ค่าเบี่ยงเบนมาตรฐานของตัวแปรสุ่ม X ที่สัมพันธ์กับเปอร์สเปคทีฟทางคณิตศาสตร์ โดยอิงจากการประมาณค่าความแปรปรวนที่เป็นกลาง สูตรเขียนดังนี้:
ฉันทราบว่าการประมาณการทั้งสองนั้นมีอคติ ในกรณีทั่วไป ไม่สามารถสร้างการประมาณการที่เป็นกลางได้ แต่การประมาณตามการประมาณค่าความแปรปรวนที่เป็นกลางจะสอดคล้องกัน

การนำไปปฏิบัติจริงใน Excel

ทีนี้เราเลิกใช้ทฤษฎีที่น่าเบื่อแล้วมาดูในทางปฏิบัติว่าฟังก์ชัน STANDARDEVAL ทำงานอย่างไร ฉันจะไม่พิจารณารูปแบบต่างๆ ของฟังก์ชันส่วนเบี่ยงเบนมาตรฐานทั้งหมดใน Excel อันเดียวก็เพียงพอแล้ว แต่ในตัวอย่าง ตามตัวอย่าง มาดูวิธีการกำหนดสถิติความมั่นคงด้านการขาย

ขั้นแรก ให้ดูที่การสะกดของฟังก์ชัน และอย่างที่คุณเห็น มันง่ายมาก:

ส่วนเบี่ยงเบนมาตรฐาน.Г(_number1_;_number2_; ….) โดยที่:

ตอนนี้เรามาสร้างไฟล์ตัวอย่างและพิจารณาว่าฟังก์ชันนี้ทำงานอย่างไร เนื่องจากในการคำนวณเชิงวิเคราะห์จึงจำเป็นต้องใช้ค่าอย่างน้อยสามค่า ตามหลักการแล้วในการวิเคราะห์ทางสถิติ ฉันใช้เวลา 3 ช่วงแบบมีเงื่อนไข ซึ่งอาจเป็นหนึ่งปี หนึ่งในสี่ หนึ่งเดือนหรือหนึ่งสัปดาห์ ในกรณีของฉัน - หนึ่งเดือน เพื่อความน่าเชื่อถือสูงสุด ฉันขอแนะนำให้ใช้เวลาให้มากที่สุดเท่าที่จะเป็นไปได้ แต่ต้องไม่น้อยกว่าสามช่วง ข้อมูลทั้งหมดในตารางนั้นง่ายมากเพื่อความชัดเจนของการทำงานและการทำงานของสูตร

ขั้นแรกเราต้องคำนวณค่าเฉลี่ยเป็นรายเดือน เราจะใช้ฟังก์ชัน AVERAGE และรับสูตร: = AVERAGE(C4:E4)
ที่จริงแล้ว เราสามารถหาค่าเบี่ยงเบนมาตรฐานได้โดยใช้ฟังก์ชัน STANDARDEVAL.G ซึ่งเป็นค่าที่เราต้องป้อนยอดขายของผลิตภัณฑ์ในแต่ละช่วงเวลา ผลลัพธ์จะเป็นสูตรของรูปแบบต่อไปนี้: =STANDARD DEVIATION.Г(C4;D4;E4)
เสร็จงานไปครึ่งหนึ่งแล้ว ขั้นตอนต่อไปคือการสร้าง "รูปแบบ" ซึ่งได้โดยการหารด้วยค่าเฉลี่ย ส่วนเบี่ยงเบนมาตรฐาน และแปลงผลลัพธ์เป็นเปอร์เซ็นต์ เราได้รับตารางต่อไปนี้:
การคำนวณพื้นฐานเสร็จสมบูรณ์แล้ว สิ่งที่เหลืออยู่คือการพิจารณาว่ายอดขายมีเสถียรภาพหรือไม่ ให้เราตั้งเงื่อนไขว่าค่าเบี่ยงเบน 10% ถือว่าคงที่ จาก 10 ถึง 25% ถือเป็นค่าเบี่ยงเบนเล็กน้อย แต่อะไรก็ตามที่สูงกว่า 25% จะไม่เสถียรอีกต่อไป เพื่อให้ได้ผลลัพธ์ตามเงื่อนไข เราจะใช้ตรรกะ และเพื่อให้ได้ผลลัพธ์เราจะเขียนสูตร:

ถ้า(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

ช่วงทั้งหมดใช้เพื่อความชัดเจน งานของคุณอาจมีเงื่อนไขที่แตกต่างไปจากเดิมอย่างสิ้นเชิง
เพื่อปรับปรุงการแสดงภาพข้อมูล เมื่อตารางของคุณมีตำแหน่งหลายพันตำแหน่ง คุณควรใช้โอกาสในการใช้เงื่อนไขบางอย่างที่คุณต้องการหรือใช้เพื่อเน้นตัวเลือกบางอย่างด้วยโทนสี ซึ่งจะชัดเจนมาก

ขั้นแรก เลือกอันที่คุณจะใช้การจัดรูปแบบตามเงื่อนไข ในแผงควบคุม "หน้าแรก" เลือก "การจัดรูปแบบตามเงื่อนไข" และในเมนูแบบเลื่อนลงเลือก "กฎสำหรับการเน้นเซลล์" จากนั้นคลิกรายการเมนู "ข้อความมี..." กล่องโต้ตอบปรากฏขึ้นโดยที่คุณป้อนเงื่อนไขของคุณ

หลังจากที่คุณเขียนเงื่อนไขเช่น "คงที่" - สีเขียว "ปกติ" - สีเหลืองและ "ไม่เสถียร" - สีแดง เราจะได้ตารางที่สวยงามและเข้าใจง่ายซึ่งคุณจะเห็นได้ว่าต้องใส่ใจอะไรเป็นอันดับแรก

การใช้ VBA สำหรับฟังก์ชัน STDEV.Y

ผู้ที่สนใจสามารถทำการคำนวณอัตโนมัติโดยใช้มาโครและใช้ฟังก์ชันต่อไปนี้:

ฟังก์ชั่น MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# For Each x In Arr aSum = aSum + x "คำนวณผลรวมขององค์ประกอบอาร์เรย์ aCnt = aCnt + 1 "คำนวณจำนวนองค์ประกอบ ถัดไป x aAver = aSum / aCnt "ค่าเฉลี่ยสำหรับแต่ละ x In Arr tmp = tmp + (x - aAver) ^ 2 "คำนวณผลรวมของกำลังสองของความแตกต่างระหว่างองค์ประกอบอาร์เรย์และค่าเฉลี่ย ถัดไป x MyStDevP = Sqr(tmp / aCnt ) "คำนวณ STANDARDEV.G() End Function

ฟังก์ชั่น MyStDevP(Arr) หรี่ x , aCnt & , aSum #, aAver#, tmp# สำหรับแต่ละ x ใน Arr aSum = aSum + x "คำนวณผลรวมขององค์ประกอบอาร์เรย์

การทำการวิเคราะห์ทางสถิติใดๆ จะไม่สามารถคิดได้หากไม่มีการคำนวณ ในบทความนี้ เราจะดูวิธีคำนวณความแปรปรวน ค่าเบี่ยงเบนมาตรฐาน ค่าสัมประสิทธิ์การเปลี่ยนแปลง และตัวบ่งชี้ทางสถิติอื่นๆ ใน Excel

ค่าสูงสุดและต่ำสุด

ค่าเบี่ยงเบนเชิงเส้นเฉลี่ย

ค่าเบี่ยงเบนเชิงเส้นเฉลี่ยคือค่าเฉลี่ยของการเบี่ยงเบนสัมบูรณ์ (โมดูโล) จากชุดข้อมูลที่วิเคราะห์ สูตรทางคณิตศาสตร์คือ:

ก– ค่าเบี่ยงเบนเชิงเส้นเฉลี่ย

เอ็กซ์– ตัวบ่งชี้ที่วิเคราะห์,

เอ็กซ์– ค่าเฉลี่ยของตัวบ่งชี้

n

ใน Excel ฟังก์ชันนี้เรียกว่า สรอต.

หลังจากเลือกฟังก์ชัน SROTCL แล้ว เราจะระบุช่วงข้อมูลที่ควรจะคำนวณ คลิก "ตกลง"

การกระจายตัว

(โมดูล 111)

บางทีอาจไม่ใช่ทุกคนที่รู้ว่าอะไร ดังนั้นฉันจะอธิบายว่านี่เป็นการวัดที่แสดงลักษณะการแพร่กระจายของข้อมูลตามความคาดหวังทางคณิตศาสตร์ อย่างไรก็ตาม โดยทั่วไปจะมีเพียงตัวอย่างเท่านั้น ดังนั้นจึงใช้สูตรความแปรปรวนต่อไปนี้:

ส 2– ความแปรปรวนตัวอย่างคำนวณจากข้อมูลเชิงสังเกต

เอ็กซ์– ค่านิยมส่วนบุคคล

เอ็กซ์– ค่าเฉลี่ยเลขคณิตสำหรับตัวอย่าง

n– จำนวนค่าในชุดข้อมูลที่วิเคราะห์

ฟังก์ชัน Excel ที่สอดคล้องกันคือ DISP.G. เมื่อวิเคราะห์ตัวอย่างที่มีขนาดค่อนข้างเล็ก (มากถึงประมาณ 30 การสังเกต) คุณควรใช้ ซึ่งคำนวณโดยใช้สูตรต่อไปนี้

เห็นได้ชัดว่าความแตกต่างอยู่ที่ตัวส่วนเท่านั้น Excel มีฟังก์ชันในการคำนวณความแปรปรวนที่เป็นกลางของกลุ่มตัวอย่าง DISP.B.

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

ส่วนเบี่ยงเบนมาตรฐาน

ค่าเบี่ยงเบนมาตรฐาน (RMS) คือรากของความแปรปรวน ตัวบ่งชี้นี้เรียกอีกอย่างว่าค่าเบี่ยงเบนมาตรฐานและคำนวณโดยใช้สูตร:

โดยประชากรทั่วไป

ตามตัวอย่าง

คุณสามารถหารากของความแปรปรวนได้ แต่ Excel มีฟังก์ชันสำเร็จรูปสำหรับค่าเบี่ยงเบนมาตรฐาน: STDEV.Gและ STDEV.V(สำหรับประชากรทั่วไปและประชากรตัวอย่าง ตามลำดับ)

ฉันขอย้ำว่าค่ามาตรฐานและค่าเบี่ยงเบนมาตรฐานเป็นคำพ้องความหมาย

จากนั้นระบุช่วงที่ต้องการตามปกติแล้วคลิก "ตกลง" ส่วนเบี่ยงเบนมาตรฐานมีหน่วยวัดเดียวกันกับตัวบ่งชี้ที่วิเคราะห์ ดังนั้นจึงเทียบเคียงได้กับข้อมูลต้นฉบับ เพิ่มเติมเกี่ยวกับเรื่องนี้ด้านล่าง

ค่าสัมประสิทธิ์ของการแปรผัน

ตัวบ่งชี้ทั้งหมดที่กล่าวถึงข้างต้นเชื่อมโยงกับขนาดของแหล่งข้อมูลและไม่อนุญาตให้มีแนวคิดที่เป็นรูปเป็นร่างเกี่ยวกับการเปลี่ยนแปลงของประชากรที่วิเคราะห์ หากต้องการวัดการกระจายข้อมูลแบบสัมพัทธ์ ให้ใช้ ค่าสัมประสิทธิ์ของการแปรผันซึ่งคำนวณโดยการหาร ส่วนเบี่ยงเบนมาตรฐานบน เฉลี่ย. สูตรสำหรับค่าสัมประสิทธิ์ของการแปรผันนั้นง่าย:

ไม่มีฟังก์ชันสำเร็จรูปสำหรับคำนวณค่าสัมประสิทธิ์การเปลี่ยนแปลงใน Excel ซึ่งไม่ใช่ปัญหาใหญ่ การคำนวณสามารถทำได้โดยการหารค่าเบี่ยงเบนมาตรฐานด้วยค่าเฉลี่ย เมื่อต้องการทำเช่นนี้ ให้เขียนลงในแถบสูตร:

ค่าเบี่ยงเบนมาตรฐานG()/เฉลี่ย()

ช่วงข้อมูลจะระบุอยู่ในวงเล็บ หากจำเป็น ให้ใช้ค่าเบี่ยงเบนมาตรฐานตัวอย่าง (STDEV.V)

โดยทั่วไปค่าสัมประสิทธิ์ของการแปรผันจะแสดงเป็นเปอร์เซ็นต์ ดังนั้นคุณจึงสามารถจัดเฟรมเซลล์ด้วยสูตรในรูปแบบเปอร์เซ็นต์ได้ ปุ่มที่ต้องการจะอยู่บน Ribbon บนแท็บ "หน้าแรก":

คุณยังสามารถเปลี่ยนรูปแบบได้โดยเลือกจากเมนูบริบทหลังจากไฮไลต์เซลล์ที่ต้องการแล้วคลิกขวา

ค่าสัมประสิทธิ์การแปรผันซึ่งแตกต่างจากตัวบ่งชี้การแพร่กระจายของค่าอื่น ๆ ถูกใช้เป็นตัวบ่งชี้การเปลี่ยนแปลงข้อมูลที่เป็นอิสระและให้ข้อมูลที่เป็นประโยชน์มาก ในสถิติ เป็นที่ยอมรับกันโดยทั่วไปว่าหากค่าสัมประสิทธิ์ของการแปรผันน้อยกว่า 33% ชุดข้อมูลจะเป็นเนื้อเดียวกัน หากมากกว่า 33% แสดงว่ามีความต่างกัน ข้อมูลนี้อาจเป็นประโยชน์สำหรับคำอธิบายเบื้องต้นของข้อมูลและสำหรับการระบุโอกาสในการวิเคราะห์เพิ่มเติม นอกจากนี้ ค่าสัมประสิทธิ์ของการแปรผันซึ่งวัดเป็นเปอร์เซ็นต์ ทำให้สามารถเปรียบเทียบระดับการกระจายตัวของข้อมูลต่างๆ ได้ โดยไม่คำนึงถึงขนาดและหน่วยการวัด คุณสมบัติที่เป็นประโยชน์

ค่าสัมประสิทธิ์การสั่น

ตัวบ่งชี้การกระจายตัวของข้อมูลอีกประการหนึ่งในปัจจุบันคือค่าสัมประสิทธิ์การแกว่ง นี่คืออัตราส่วนของช่วงของการแปรผัน (ความแตกต่างระหว่างค่าสูงสุดและค่าต่ำสุด) ต่อค่าเฉลี่ย ไม่มีสูตร Excel สำเร็จรูป ดังนั้นคุณต้องรวบรวมฟังก์ชันสามอย่าง: MAX, MIN, AVERAGE

ค่าสัมประสิทธิ์การสั่นจะแสดงระดับของการแปรผันที่สัมพันธ์กับค่าเฉลี่ย ซึ่งสามารถใช้เพื่อเปรียบเทียบชุดข้อมูลต่างๆ ได้ด้วย

โดยทั่วไป ด้วยความช่วยเหลือของ Excel ตัวชี้วัดทางสถิติจำนวนมากจะถูกคำนวณอย่างง่ายดาย หากมีบางอย่างไม่ชัดเจน คุณสามารถใช้ช่องค้นหาในการแทรกฟังก์ชันได้ตลอดเวลา Google พร้อมให้ความช่วยเหลือ

ตอนนี้ฉันขอแนะนำให้คุณดูวิดีโอการสอน