ลบค่าที่มากกว่าส่วนเบี่ยงเบนมาตรฐานใน Excel การคำนวณความแปรปรวน ค่าเบี่ยงเบนค่าเฉลี่ยกำลังสอง (มาตรฐาน) ค่าสัมประสิทธิ์ของการแปรผันใน Excel
คำแนะนำ
ปล่อยให้มีตัวเลขหลายตัวที่แสดงลักษณะ - หรือปริมาณที่เป็นเนื้อเดียวกัน เช่น ผลการวัด การชั่ง การสังเกตทางสถิติ เป็นต้น ปริมาณทั้งหมดที่แสดงจะต้องวัดด้วยการวัดเดียวกัน หากต้องการหาค่าเบี่ยงเบนมาตรฐาน ให้ทำดังนี้
กำหนดค่าเฉลี่ยเลขคณิตของตัวเลขทั้งหมด: บวกตัวเลขทั้งหมดและหารผลรวมด้วยจำนวนทั้งหมดของตัวเลข
กำหนดการกระจาย (กระจาย) ของตัวเลข: เพิ่มกำลังสองของการเบี่ยงเบนที่พบก่อนหน้านี้และหารผลรวมที่ได้ด้วยจำนวนของตัวเลข
มีผู้ป่วยในหอผู้ป่วย 7 ราย อุณหภูมิ 34, 35, 36, 37, 38, 39 และ 40 องศาเซลเซียส
จำเป็นต้องกำหนดส่วนเบี่ยงเบนเฉลี่ยจากค่าเฉลี่ย
สารละลาย:
"ในวอร์ด": (34+35+36+37+38+39+40)/7=37 ºС;
อุณหภูมิเบี่ยงเบนจากค่าเฉลี่ย (ในกรณีนี้คือค่าปกติ): 34-37, 35-37, 36-37, 37-37, 38-37, 39-37, 40-37 ปรากฎ: -3, -2, -1 , 0, 1, 2, 3 (ºС);
หารผลรวมของตัวเลขที่ได้รับก่อนหน้านี้ด้วยจำนวนของพวกเขา เพื่อความแม่นยำในการคำนวณควรใช้เครื่องคิดเลข ผลลัพธ์ของการหารคือค่าเฉลี่ยเลขคณิตของผลบวก
ใส่ใจกับทุกขั้นตอนของการคำนวณอย่างใกล้ชิด เนื่องจากข้อผิดพลาดในการคำนวณอย่างน้อยหนึ่งรายการจะนำไปสู่ตัวบ่งชี้สุดท้ายที่ไม่ถูกต้อง ตรวจสอบการคำนวณที่ได้รับในแต่ละขั้นตอน ค่าเฉลี่ยเลขคณิตมีมาตรเดียวกับผลบวกของตัวเลข นั่นคือ หากคุณกำหนดจำนวนผู้เข้าร่วมโดยเฉลี่ย ตัวบ่งชี้ทั้งหมดจะเป็น "บุคคล"
วิธีนี้การคำนวณจะใช้ในการคำนวณทางคณิตศาสตร์และสถิติเท่านั้น ตัวอย่างเช่น ค่าเฉลี่ยเลขคณิตในวิทยาการคอมพิวเตอร์มีอัลกอริทึมการคำนวณที่แตกต่างกัน ค่าเฉลี่ยเลขคณิตเป็นตัวบ่งชี้ที่มีเงื่อนไขมาก แสดงความน่าจะเป็นของเหตุการณ์โดยมีปัจจัยหรือตัวบ่งชี้เพียงตัวเดียว สำหรับการวิเคราะห์เชิงลึกจะต้องคำนึงถึงปัจจัยหลายประการ สำหรับสิ่งนี้ จะใช้การคำนวณปริมาณทั่วไปมากขึ้น
ค่าเฉลี่ยเลขคณิตเป็นหนึ่งในการวัดแนวโน้มเข้าสู่ส่วนกลาง ซึ่งใช้กันอย่างแพร่หลายในวิชาคณิตศาสตร์และการคำนวณทางสถิติ การหาค่าเฉลี่ยเลขคณิตของค่าต่าง ๆ นั้นง่ายมาก แต่แต่ละงานมีความแตกต่างของตัวเองซึ่งจำเป็นต้องรู้เพื่อทำการคำนวณที่ถูกต้อง
ผลเชิงปริมาณของการทดลองดังกล่าว
วิธีหาค่าเฉลี่ยเลขคณิต
การค้นหาค่าเฉลี่ยเลขคณิตสำหรับอาร์เรย์ของตัวเลขควรเริ่มต้นด้วยการหาผลรวมเชิงพีชคณิตของค่าเหล่านี้ ตัวอย่างเช่น หากอาร์เรย์ประกอบด้วยตัวเลข 23, 43, 10, 74 และ 34 ผลรวมเชิงพีชคณิตของพวกมันจะเท่ากับ 184 เมื่อเขียน ค่าเฉลี่ยเลขคณิตจะแสดงด้วยตัวอักษร μ (mu) หรือ x (x พร้อมแถบ) . ถัดไป ผลรวมเชิงพีชคณิตควรหารด้วยจำนวนตัวเลขในอาร์เรย์ ในตัวอย่างนี้ มีตัวเลขห้าตัว ดังนั้นค่าเฉลี่ยเลขคณิตจะเป็น 184/5 และจะเป็น 36.8คุณสมบัติของการทำงานกับจำนวนลบ
หากมีจำนวนลบในอาร์เรย์ ค่าเฉลี่ยเลขคณิตจะพบได้โดยใช้อัลกอริทึมที่คล้ายกัน มีความแตกต่างเฉพาะเมื่อคำนวณในสภาพแวดล้อมการเขียนโปรแกรมหรือหากงานมี ข้อกำหนดเพิ่มเติม. ในกรณีเหล่านี้ การหาค่าเฉลี่ยเลขคณิตของตัวเลขด้วย สัญญาณที่แตกต่างกันเดือดลงไปสามขั้นตอน:1. การหาค่าเฉลี่ยเลขคณิตร่วมโดยวิธีมาตรฐาน
2. การหาค่าเฉลี่ยเลขคณิตของจำนวนลบ
3. การคำนวณค่าเฉลี่ยเลขคณิตของจำนวนบวก
คำตอบของแต่ละการกระทำจะถูกเขียนคั่นด้วยเครื่องหมายจุลภาค
เศษส่วนธรรมชาติและทศนิยม
หากมีการแสดงตัวเลขแบบอาร์เรย์ ทศนิยมการแก้ปัญหาเกิดขึ้นตามวิธีการคำนวณค่าเฉลี่ยเลขคณิตของจำนวนเต็ม แต่ผลลัพธ์จะลดลงตามความต้องการของปัญหาเพื่อความถูกต้องของคำตอบเมื่อทำงานกับเศษส่วนธรรมชาติ ควรลดให้เหลือส่วนร่วมซึ่งคูณด้วยจำนวนของตัวเลขในอาร์เรย์ ตัวเศษของคำตอบจะเป็นผลรวมของตัวเศษที่กำหนดขององค์ประกอบที่เป็นเศษส่วนเดิม
สวัสดีตอนบ่าย
ในบทความ ฉันตัดสินใจที่จะพิจารณาว่าส่วนเบี่ยงเบนมาตรฐานทำงานอย่างไรใน Excel โดยใช้ฟังก์ชัน STDEV ฉันไม่ได้อธิบายหรือแสดงความคิดเห็นเป็นเวลานานมาก และเพียงเพราะนี่เป็นคุณสมบัติที่มีประโยชน์มากสำหรับผู้ที่ศึกษา คณิตศาสตร์ที่สูงขึ้น. และการช่วยเหลือนักเรียนเป็นสิ่งศักดิ์สิทธิ์ ฉันรู้จากประสบการณ์ของตัวเองว่าการจะเชี่ยวชาญนั้นยากเพียงใด ในความเป็นจริง ฟังก์ชันค่าเบี่ยงเบนมาตรฐานสามารถใช้เพื่อกำหนดความมั่นคงของผลิตภัณฑ์ที่ขาย สร้างราคา ปรับหรือสร้างการจัดประเภท และการวิเคราะห์การขายของคุณที่มีประโยชน์เท่าเทียมกันอื่นๆ
Excel ใช้ตัวแปรหลายอย่างของฟังก์ชันความแปรปรวนนี้:
ทฤษฎีทางคณิตศาสตร์
ขั้นแรก เล็กน้อยเกี่ยวกับทฤษฎีว่าฟังก์ชันสามารถอธิบายในภาษาคณิตศาสตร์ได้อย่างไร ส่วนเบี่ยงเบนมาตรฐานเพื่อนำไปใช้ใน Excel เพื่อวิเคราะห์ เช่น ข้อมูลสถิติการขาย แต่จะเพิ่มเติมในภายหลัง ฉันเตือนคุณทันทีฉันจะเขียนคำที่เข้าใจยากจำนวนมาก ... )))) หากมีข้อความด้านล่างให้ดูการใช้งานจริงในโปรแกรมทันที
ค่าเบี่ยงเบนมาตรฐานทำอะไรกันแน่? มันสร้างค่าประมาณของส่วนเบี่ยงเบนมาตรฐาน ตัวแปรสุ่ม X สัมพันธ์กับค่าเฉลี่ยตามค่าประมาณของความแปรปรวนที่เป็นกลาง เห็นด้วย ฟังดูสับสน แต่ฉันคิดว่านักเรียนจะเข้าใจว่าจริง ๆ แล้วเกี่ยวกับอะไร!
ในการเริ่มต้น เราต้องกำหนด "ส่วนเบี่ยงเบนมาตรฐาน" เพื่อที่จะคำนวณ "ส่วนเบี่ยงเบนมาตรฐาน" เพิ่มเติม สูตรจะช่วยเราในเรื่องนี้: เป็นไปได้ที่จะอธิบายสูตรดังต่อไปนี้: จะวัดในหน่วยเดียวกับการวัดตัวแปรสุ่มและจะใช้เมื่อคำนวณข้อผิดพลาดค่าเฉลี่ยเลขคณิตมาตรฐานเมื่อสร้าง ช่วงความมั่นใจเมื่อทดสอบสมมติฐานสำหรับสถิติ หรือเมื่อวิเคราะห์ความสัมพันธ์เชิงเส้นระหว่างตัวแปรอิสระ ฟังก์ชันถูกกำหนดให้เป็น รากที่สองจากการกระจายตัวของปริมาณอิสระ
ตอนนี้เราสามารถกำหนดและ ส่วนเบี่ยงเบนมาตรฐานเป็นการวิเคราะห์ค่าเบี่ยงเบนมาตรฐานของตัวแปรสุ่ม X เมื่อเปรียบเทียบกับมุมมองทางคณิตศาสตร์ตามค่าประมาณของความแปรปรวนที่เป็นกลาง สูตรเขียนดังนี้:
โปรดทราบว่าค่าประมาณทั้ง 2 รายการนั้นให้แบบลำเอียง ที่ กรณีทั่วไปเป็นไปไม่ได้ที่จะสร้างการประมาณการที่เป็นกลาง แต่การประมาณการตามค่าประมาณความแปรปรวนที่เป็นกลางจะสอดคล้องกัน
การใช้งานจริงใน Excel
ตอนนี้เรามาเปลี่ยนจากทฤษฎีที่น่าเบื่อและในทางปฏิบัติ เรามาดูกันว่าฟังก์ชัน STDEV ทำงานอย่างไร ฉันจะไม่พิจารณารูปแบบทั้งหมดของฟังก์ชันส่วนเบี่ยงเบนมาตรฐานใน Excel เพียงอันเดียวก็เพียงพอแล้ว แต่ในตัวอย่าง ตัวอย่างเช่น พิจารณาวิธีการกำหนดสถิติความมั่นคงในการขาย
ก่อนอื่น ดูที่การสะกดของฟังก์ชัน และอย่างที่คุณเห็น มันง่ายมาก:
STDEV.G(_number1_;_number2_; ....) โดยที่:
ตอนนี้เรามาสร้างไฟล์ตัวอย่างและเราจะพิจารณาการทำงานของฟังก์ชั่นนี้ เนื่องจากสำหรับการคำนวณเชิงวิเคราะห์จำเป็นต้องใช้ค่าอย่างน้อยสามค่าตามหลักการในการวิเคราะห์ทางสถิติ ฉันจึงใช้ 3 ช่วงเวลาแบบมีเงื่อนไข อาจเป็นปี ไตรมาส เดือน หรือสัปดาห์ก็ได้ ในกรณีของฉันหนึ่งเดือน เพื่อความน่าเชื่อถือสูงสุด ฉันขอแนะนำให้ใช้ให้มากที่สุด จำนวนมากงวดแต่ไม่น้อยกว่าสามงวด ข้อมูลทั้งหมดในตารางนั้นง่ายมากเพื่อความชัดเจนในการทำงานและการทำงานของสูตร
ในการเริ่มต้นเราต้องคำนวณค่าเฉลี่ยเป็นรายเดือน เราจะใช้ฟังก์ชัน AVERAGE สำหรับสิ่งนี้และรับสูตร: =AVERAGE(C4:E4)
อันที่จริงแล้ว เราสามารถหาค่าเบี่ยงเบนมาตรฐานได้โดยใช้ฟังก์ชัน STDEV.G ซึ่งเป็นค่าที่เราต้องใส่ยอดขายสินค้าในแต่ละงวดลงไป ผลลัพธ์คือสูตรของแบบฟอร์มต่อไปนี้: \u003d STDEV.G (C4; D4; E4)
นั่นคืองานเสร็จไปแล้วครึ่งหนึ่ง ในขั้นตอนถัดไป เราสร้าง "การเปลี่ยนแปลง" ซึ่งได้จากการหารด้วยค่าเฉลี่ย ส่วนเบี่ยงเบนมาตรฐาน และแปลงผลลัพธ์เป็นเปอร์เซ็นต์ เราได้ตารางต่อไปนี้:
การคำนวณหลักจบลงแล้ว ยังคงต้องพิจารณาว่ายอดขายจะคงที่หรือไม่ ให้เราใช้เป็นเงื่อนไขว่าการเบี่ยงเบน 10% ถือว่าคงที่ จาก 10 ถึง 25% สิ่งเหล่านี้เป็นการเบี่ยงเบนเล็กน้อย แต่ทุกอย่างที่สูงกว่า 25% จะไม่เสถียรอีกต่อไป เพื่อให้ได้ผลลัพธ์ตามเงื่อนไขเราจะใช้ตรรกะและเพื่อให้ได้ผลลัพธ์เราจะเขียนสูตร:
ไอเอฟ(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))
ช่วงทั้งหมดมีเงื่อนไขเพื่อความชัดเจน งานของคุณอาจมีเงื่อนไขที่แตกต่างกันโดยสิ้นเชิง
เพื่อปรับปรุงการแสดงภาพข้อมูล เมื่อตารางของคุณมีตำแหน่งหลายพันตำแหน่ง คุณควรใช้โอกาสนี้กำหนดเงื่อนไขบางอย่างที่คุณต้องการ หรือใช้เพื่อเน้นตัวเลือกบางอย่างด้วยชุดสี ซึ่งจะทำให้มองเห็นได้ชัดเจนมาก
ขั้นแรก เลือกสิ่งที่คุณต้องการใช้การจัดรูปแบบตามเงื่อนไข ในแผงควบคุม "หน้าแรก" เลือก "การจัดรูปแบบตามเงื่อนไข" และในเมนูแบบเลื่อนลง เลือกรายการ "กฎการเลือกเซลล์" จากนั้นคลิกรายการเมนู "ข้อความมี ... " กล่องโต้ตอบจะปรากฏขึ้นเพื่อให้คุณป้อนเงื่อนไขของคุณ
หลังจากเขียนเงื่อนไขเช่น "คงที่" - สีเขียว "ปกติ" - สีเหลืองและ "ไม่คงที่" - สีแดง เราได้ตารางที่สวยงามและเข้าใจได้ซึ่งคุณสามารถดูสิ่งที่ต้องให้ความสนใจก่อนอื่น
การใช้ VBA สำหรับฟังก์ชัน STDEV.H
ผู้ที่สนใจสามารถทำการคำนวณโดยอัตโนมัติโดยใช้มาโครและใช้ฟังก์ชันต่อไปนี้:
ฟังก์ชัน 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 "คำนวณผลรวมของกำลังสองของความแตกต่างระหว่างองค์ประกอบของอาร์เรย์และค่าเฉลี่ย Next x MyStDevP = Sqr(tmp / aCnt ) "คำนวณ STDEV.G() สิ้นสุดฟังก์ชัน
ฟังก์ชัน MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# สำหรับแต่ละ x ใน Arr aSum = ผลรวม + x "คำนวณผลรวมขององค์ประกอบของอาร์เรย์ |
ในการหาค่าเฉลี่ยใน Excel (ไม่ว่าจะเป็นค่าตัวเลข ข้อความ เปอร์เซ็นต์ หรือค่าอื่นๆ) มีฟังก์ชันมากมาย และแต่ละคนมีลักษณะและข้อดีของตัวเอง ท้ายที่สุดแล้ว เงื่อนไขบางอย่างสามารถตั้งค่าได้ในงานนี้
ตัวอย่างเช่น ค่าเฉลี่ยของชุดตัวเลขใน Excel จะคำนวณโดยใช้ฟังก์ชันทางสถิติ คุณยังสามารถป้อนสูตรของคุณเองได้ ลองพิจารณาตัวเลือกต่างๆ
จะหาค่าเฉลี่ยเลขคณิตของตัวเลขได้อย่างไร?
ในการหาค่าเฉลี่ยเลขคณิต คุณต้องบวกตัวเลขทั้งหมดในชุดแล้วหารผลรวมด้วยตัวเลข ตัวอย่างเช่น คะแนนของนักเรียนในวิชาวิทยาการคอมพิวเตอร์: 3, 4, 3, 5, 5 ไตรมาสละเท่าไร: 4 เราพบค่าเฉลี่ยเลขคณิตโดยใช้สูตร: \u003d (3 + 4 + 3 + 5 + 5) / 5.
ทำอย่างไรให้รวดเร็วโดยใช้ฟังก์ชัน Excel? ยกตัวอย่างชุดตัวเลขสุ่มในสตริง:
หรือ: ทำให้เซลล์ทำงานและป้อนสูตรด้วยตนเอง: =AVERAGE(A1:A8)
ทีนี้มาดูกันว่าฟังก์ชัน AVERAGE ทำอะไรได้อีกบ้าง
ค้นหาค่าเฉลี่ยเลขคณิตของตัวเลขสองตัวแรกและสามตัวหลัง สูตร: =AVERAGE(A1:B1;F1:H1) ผลลัพธ์:
เฉลี่ยตามเงื่อนไข
เงื่อนไขสำหรับการค้นหาค่าเฉลี่ยเลขคณิตอาจเป็นเกณฑ์ที่เป็นตัวเลขหรือเป็นข้อความก็ได้ เราจะใช้ฟังก์ชัน: =AVERAGEIF()
ค้นหาค่าเฉลี่ยเลขคณิตของตัวเลขที่มากกว่าหรือเท่ากับ 10
ฟังก์ชัน: =AVERAGEIF(A1:A8,">=10")
ผลลัพธ์ของการใช้ฟังก์ชัน AVERAGEIF ในเงื่อนไข ">=10":
อาร์กิวเมนต์ที่สาม - "ช่วงค่าเฉลี่ย" - ถูกละไว้ ประการแรกไม่จำเป็น ประการที่สอง ช่วงที่แยกวิเคราะห์โดยโปรแกรมมีค่าตัวเลขเท่านั้น ในเซลล์ที่ระบุในอาร์กิวเมนต์แรก การค้นหาจะดำเนินการตามเงื่อนไขที่ระบุในอาร์กิวเมนต์ที่สอง
ความสนใจ! สามารถระบุเกณฑ์การค้นหาในเซลล์ได้ และในสูตรที่ต้องทำการอ้างอิงนั้น
มาหาค่าเฉลี่ยของตัวเลขตามเกณฑ์ข้อความ ตัวอย่างเช่น ยอดขายเฉลี่ยของผลิตภัณฑ์ "ตาราง"
ฟังก์ชันจะมีลักษณะดังนี้: =AVERAGEIF($A$2:$A$12;A7;$B$2:$B$12) ช่วง - คอลัมน์ที่มีชื่อผลิตภัณฑ์ เกณฑ์การค้นหาคือลิงก์ไปยังเซลล์ที่มีคำว่า "ตาราง" (คุณสามารถแทรกคำว่า "ตาราง" แทนลิงก์ A7) ช่วงค่าเฉลี่ย - เซลล์ที่ข้อมูลจะถูกนำไปคำนวณค่าเฉลี่ย
จากการคำนวณฟังก์ชัน เราได้ค่าต่อไปนี้:
ความสนใจ! สำหรับเกณฑ์ข้อความ (เงื่อนไข) ต้องระบุช่วงค่าเฉลี่ย
วิธีการคำนวณราคาถัวเฉลี่ยถ่วงน้ำหนักใน Excel?
เราจะทราบราคาถัวเฉลี่ยถ่วงน้ำหนักได้อย่างไร?
สูตร: =SUMPRODUCT(C2:C12,B2:B12)/SUM(C2:C12)
เมื่อใช้สูตร SUMPRODUCT เราจะหารายได้รวมหลังการขายสินค้าตามจำนวนทั้งหมด และฟังก์ชัน SUM - สรุปปริมาณสินค้า เราพบราคาถัวเฉลี่ยถ่วงน้ำหนักโดยการหารรายได้จากการขายสินค้าด้วยจำนวนหน่วยสินค้าทั้งหมด ตัวบ่งชี้นี้คำนึงถึง "น้ำหนัก" ของแต่ละราคา มีส่วนร่วมในมวลรวมของมูลค่า
ส่วนเบี่ยงเบนมาตรฐาน: สูตรใน Excel
แยกความแตกต่างระหว่างค่าเบี่ยงเบนมาตรฐานสำหรับประชากรทั่วไปและกลุ่มตัวอย่าง ในกรณีแรก นี่คือรากของความแปรปรวนทั่วไป ในวินาที จากความแปรปรวนตัวอย่าง
ในการคำนวณตัวบ่งชี้ทางสถิตินี้ จะมีการรวบรวมสูตรการกระจาย รากถูกนำมาจากมัน แต่ใน Excel มีฟังก์ชันสำเร็จรูปสำหรับหาค่าเบี่ยงเบนมาตรฐาน
ส่วนเบี่ยงเบนมาตรฐานเชื่อมโยงกับขนาดของแหล่งข้อมูล นี่ยังไม่เพียงพอสำหรับการแสดงโดยเป็นรูปเป็นร่างของความแปรผันของช่วงที่วิเคราะห์ ในการรับระดับสัมพัทธ์ของการกระจายในข้อมูล จะมีการคำนวณค่าสัมประสิทธิ์ของการแปรผัน:
ส่วนเบี่ยงเบนมาตรฐาน / ค่าเฉลี่ยเลขคณิต
สูตรใน Excel มีลักษณะดังนี้:
STDEV (ช่วงของค่า) / AVERAGE (ช่วงของค่า)
ค่าสัมประสิทธิ์ของการเปลี่ยนแปลงจะคำนวณเป็นเปอร์เซ็นต์ ดังนั้นเราจึงกำหนดรูปแบบเปอร์เซ็นต์ในเซลล์
ในบทความนี้ผมจะพูดถึง วิธีหาค่าเบี่ยงเบนมาตรฐาน. เนื้อหานี้มีความสำคัญอย่างยิ่งต่อการทำความเข้าใจคณิตศาสตร์อย่างถ่องแท้ ดังนั้น ติวเตอร์คณิตศาสตร์ควรแยกบทเรียนต่างหากหรือแม้แต่หลายๆ บทเรียนเพื่อศึกษาบทเรียนนั้น ในบทความนี้ คุณจะพบลิงก์ไปยังวิดีโอแนะนำโดยละเอียดและเข้าใจได้ ซึ่งจะอธิบายว่าค่าเบี่ยงเบนมาตรฐานคืออะไรและจะค้นหาได้อย่างไร
ส่วนเบี่ยงเบนมาตรฐานทำให้สามารถประเมินการแพร่กระจายของค่าที่ได้รับจากการวัดพารามิเตอร์บางอย่าง มันแสดงด้วยสัญลักษณ์ (ตัวอักษรกรีก "sigma")
สูตรการคำนวณค่อนข้างง่าย ในการหาค่าเบี่ยงเบนมาตรฐาน คุณต้องหารากที่สองของความแปรปรวน ตอนนี้คุณต้องถามว่า "ความแปรปรวนคืออะไร"
การกระจายคืออะไร
นิยามของความแปรปรวนมีดังนี้ การกระจายตัวเป็นค่าเฉลี่ยเลขคณิตของการเบี่ยงเบนกำลังสองของค่าจากค่าเฉลี่ย
ในการค้นหาความแปรปรวน ให้ทำการคำนวณตามลำดับต่อไปนี้:
- กำหนดค่าเฉลี่ย (ค่าเฉลี่ยเลขคณิตอย่างง่ายของชุดค่าต่างๆ)
- จากนั้นลบค่าเฉลี่ยออกจากแต่ละค่าและยกกำลังสองผลต่างที่เกิดขึ้น (เราได้ ผลต่างยกกำลังสอง).
- ขั้นตอนต่อไปคือการคำนวณค่าเฉลี่ยเลขคณิตของกำลังสองของผลต่างที่ได้รับ (คุณจะพบว่าเหตุใดจึงเป็นกำลังสองที่อยู่ด้านล่าง)
ลองดูตัวอย่าง สมมติว่าคุณและเพื่อนของคุณตัดสินใจวัดความสูงของสุนัขของคุณ (หน่วยเป็นมิลลิเมตร) จากผลการวัด คุณได้รับการวัดความสูงต่อไปนี้ (ที่ไหล่): 600 มม. 470 มม. 170 มม. 430 มม. และ 300 มม.
ลองคำนวณค่าเฉลี่ย ความแปรปรวน และส่วนเบี่ยงเบนมาตรฐาน
มาหาค่าเฉลี่ยก่อน. ดังที่คุณทราบแล้ว คุณต้องเพิ่มค่าที่วัดได้ทั้งหมดและหารด้วยจำนวนการวัด ความคืบหน้าการคำนวณ:
มม. เฉลี่ย
ดังนั้น ค่าเฉลี่ย (ค่าเฉลี่ยเลขคณิต) คือ 394 มม.
ตอนนี้เราต้องกำหนด ความเบี่ยงเบนของความสูงของสุนัขแต่ละตัวจากค่าเฉลี่ย:
ในที่สุด, เพื่อคำนวณผลต่างผลต่างที่ได้แต่ละค่าจะถูกยกกำลังสอง จากนั้นเราจะหาค่าเฉลี่ยเลขคณิตของผลลัพธ์ที่ได้:
การกระจาย มม. 2 .
ดังนั้น การกระจายคือ 21704 มม. 2 .
วิธีหาส่วนเบี่ยงเบนมาตรฐาน
ทีนี้จะคำนวณค่าเบี่ยงเบนมาตรฐานโดยรู้ค่าความแปรปรวนได้อย่างไร อย่างที่เราจำได้ ให้หาค่ารากที่สองของมัน นั่นคือค่าเบี่ยงเบนมาตรฐานคือ:
มม. (ปัดเศษเป็นจำนวนเต็มที่ใกล้ที่สุดในหน่วย มม.)
เมื่อใช้วิธีนี้ เราพบว่าสุนัขบางตัว (เช่น ร็อตไวเลอร์) เป็นสุนัขที่ตัวใหญ่มาก แต่ก็มีสุนัขตัวเล็กมากเช่นกัน (เช่น ดัชชุน แต่คุณไม่ควรบอกเรื่องนี้)
สิ่งที่น่าสนใจที่สุดคือส่วนเบี่ยงเบนมาตรฐานมีข้อมูลที่เป็นประโยชน์ ตอนนี้เราสามารถแสดงผลลัพธ์การวัดการเติบโตที่ได้รับซึ่งอยู่ในช่วงที่เราได้รับหากเราแยกส่วนเบี่ยงเบนมาตรฐานออกจากค่าเฉลี่ย (ทั้งสองด้าน)
นั่นคือการใช้ค่าเบี่ยงเบนมาตรฐานเราได้วิธีการ "มาตรฐาน" ที่ช่วยให้คุณทราบว่าค่าใดที่เป็นปกติ (ค่าเฉลี่ยทางสถิติ) และค่าใดที่มีค่ามากหรือน้อยเป็นพิเศษ
ค่าเบี่ยงเบนมาตรฐานคืออะไร
แต่ ... สิ่งต่าง ๆ จะแตกต่างกันเล็กน้อยหากเราวิเคราะห์ การสุ่มตัวอย่างข้อมูล. ในตัวอย่างของเรา เราถือว่า ประชาชนทั่วไปนั่นคือสุนัขของเรา 5 ตัวเป็นสุนัขตัวเดียวในโลกที่สนใจเรา
แต่ถ้าข้อมูลเป็นตัวอย่าง (ค่าที่เลือกจากประชากรจำนวนมาก) การคำนวณจะต้องทำแตกต่างกัน
หากมีค่า ดังนั้น:
การคำนวณอื่น ๆ ทั้งหมดทำในลักษณะเดียวกัน รวมทั้งการหาค่าเฉลี่ย
ตัวอย่างเช่น หากสุนัขห้าตัวของเราเป็นเพียงตัวอย่างหนึ่งของประชากรสุนัข (สุนัขทุกตัวบนโลก) เราจะต้องหารด้วย 4 แทนที่จะเป็น 5กล่าวคือ:
ความแปรปรวนตัวอย่าง = มม. 2 .
ในกรณีนี้ ค่าเบี่ยงเบนมาตรฐานสำหรับตัวอย่างจะเท่ากับ มม. (ปัดเศษเป็นจำนวนเต็มที่ใกล้เคียงที่สุด)
เราสามารถพูดได้ว่าเราทำการ "แก้ไข" บางอย่างในกรณีที่ค่าของเราเป็นเพียงตัวอย่างเล็กๆ
บันทึก. ทำไมผลต่างกำลังสองกันแน่?
แต่ทำไมเราถึงใช้กำลังสองของความแตกต่างเมื่อคำนวณความแปรปรวน? ยอมรับการวัดพารามิเตอร์บางอย่าง คุณได้รับชุดค่าต่อไปนี้: 4; 4; -4; -4. หากเราเพิ่มค่าเบี่ยงเบนสัมบูรณ์จากค่าเฉลี่ย (ผลต่าง) เข้าด้วยกัน ... ค่าลบจะถูกลบด้วยค่าบวก:
.
ปรากฎว่าตัวเลือกนี้ไร้ประโยชน์ ถ้าอย่างนั้นก็น่าจะลองใช้ค่าสัมบูรณ์ของการเบี่ยงเบน (นั่นคือโมดูลของค่าเหล่านี้)
เมื่อมองแวบแรกก็ไม่เลว (ค่าผลลัพธ์เรียกว่าค่าเบี่ยงเบนสัมบูรณ์เฉลี่ย) แต่ไม่ใช่ในทุกกรณี ลองอีกตัวอย่างหนึ่ง ให้ผลการวัดเป็นชุดของค่าต่อไปนี้: 7; 1; -6; -2. ดังนั้นค่าเบี่ยงเบนสัมบูรณ์เฉลี่ยคือ:
ว้าว! เราได้ผลลัพธ์เป็น 4 อีกครั้ง แม้ว่าความแตกต่างจะมีสเปรดที่กว้างกว่ามาก
ทีนี้มาดูกันว่าจะเกิดอะไรขึ้นถ้าเรานำผลต่างยกกำลังสอง (แล้วหารากที่สองของผลรวมของมัน)
สำหรับตัวอย่างแรก คุณจะได้รับ:
.
สำหรับตัวอย่างที่สอง คุณจะได้รับ:
ตอนนี้มันเป็นเรื่องที่แตกต่างไปจากเดิมอย่างสิ้นเชิง! ยิ่งค่าความเบี่ยงเบนของรูต-ค่าเฉลี่ย-กำลังสองยิ่งมาก ความแตกต่างก็จะยิ่งกระจายมากขึ้น ... ซึ่งเป็นสิ่งที่เราพยายามไขว่คว้ามา
อันที่จริง วิธีนี้ใช้แนวคิดเดียวกับเมื่อคำนวณระยะห่างระหว่างจุด แต่ใช้วิธีอื่นเท่านั้น
และจากมุมมองทางคณิตศาสตร์ การใช้กำลังสองและรากที่สองมีประโยชน์มากกว่าที่เราจะได้รับจากค่าสัมบูรณ์ของส่วนเบี่ยงเบน เนื่องจากค่าเบี่ยงเบนมาตรฐานใช้ได้กับปัญหาทางคณิตศาสตร์อื่น ๆ
Sergey Valerievich บอกคุณถึงวิธีหาส่วนเบี่ยงเบนมาตรฐาน
ฟังก์ชันส่วนเบี่ยงเบนมาตรฐานนั้นมาจากหมวดหมู่ของคณิตศาสตร์ขั้นสูงที่เกี่ยวข้องกับสถิติอยู่แล้ว ใน Excel มีตัวเลือกมากมายสำหรับการใช้ฟังก์ชันค่าเบี่ยงเบนมาตรฐาน:
- ฟังก์ชัน STDEV
- ฟังก์ชัน STDEV
- ฟังก์ชัน STDEV
เราจะต้องใช้ฟังก์ชันเหล่านี้ในสถิติการขายเพื่อระบุความมั่นคงของการขาย (การวิเคราะห์ XYZ) ข้อมูลนี้สามารถใช้สำหรับการกำหนดราคาและการก่อตัว (การปรับ) ของเมทริกซ์การจัดประเภทและสำหรับการวิเคราะห์การขายที่มีประโยชน์อื่น ๆ ซึ่งฉันจะพูดถึงในบทความต่อ ๆ ไปอย่างแน่นอน
คำนำ
มาดูสูตรในภาษาคณิตศาสตร์กันก่อน จากนั้น (ด้านล่างของข้อความ) เราจะวิเคราะห์สูตรใน Excel อย่างละเอียดและวิธีนำผลลัพธ์ที่ได้ไปใช้ในการวิเคราะห์สถิติการขาย
ดังนั้น Standard Deviation คือค่าประมาณของค่าเบี่ยงเบนมาตรฐานของตัวแปรสุ่ม xเกี่ยวกับความคาดหวังทางคณิตศาสตร์จากการประมาณค่าความแปรปรวนที่เป็นกลาง)))) อย่ากลัวคำพูดที่เข้าใจยาก อดทนและคุณจะเข้าใจทุกอย่าง!
คำอธิบายของสูตร: ค่าเบี่ยงเบนมาตรฐานวัดเป็นหน่วยของตัวแปรสุ่มเอง และใช้ในการคำนวณข้อผิดพลาดมาตรฐานของค่าเฉลี่ยเลขคณิต เมื่อสร้างช่วงความเชื่อมั่น เมื่อทดสอบสมมติฐานทางสถิติ เมื่อวัดความสัมพันธ์เชิงเส้นระหว่างตัวแปรสุ่ม กำหนดเป็นรากที่สองของความแปรปรวนของตัวแปรสุ่ม
ส่วนเบี่ยงเบนมาตรฐานคือค่าประมาณของส่วนเบี่ยงเบนมาตรฐานของตัวแปรสุ่ม xในส่วนที่เกี่ยวกับความคาดหวังทางคณิตศาสตร์บนพื้นฐานของการประมาณค่าความแปรปรวนที่เป็นกลาง:
การกระจาย;
- ฉันองค์ประกอบตัวอย่าง -th;
ขนาดตัวอย่าง;
ตัวอย่างค่าเฉลี่ยเลขคณิต:
ควรสังเกตว่าการประมาณการทั้งสองมีความลำเอียง ในกรณีทั่วไป เป็นไปไม่ได้ที่จะสร้างค่าประมาณที่เป็นกลาง อย่างไรก็ตาม การประมาณการตามค่าประมาณความแปรปรวนที่เป็นกลางนั้นสอดคล้องกัน
กฎสามซิกมา() - ค่าเกือบทั้งหมดของตัวแปรสุ่มที่กระจายตามปกติอยู่ในช่วง . เคร่งครัดยิ่งขึ้น ด้วยความน่าจะเป็นประมาณ 0.9973 ค่าของตัวแปรสุ่มที่กระจายตามปกติจะอยู่ในช่วงที่ระบุ (โดยมีเงื่อนไขว่าค่าเป็นจริง และไม่ได้มาจากการประมวลผลตัวอย่าง) เราจะใช้ช่วงเวลาปัดเศษ 0.1
หากไม่ทราบค่าที่แท้จริง คุณควรใช้ not แต่ ส. ดังนั้นกฎของซิกมาสามจึงเปลี่ยนเป็นกฎสาม ส. เป็นกฎนี้ที่จะช่วยเรากำหนดความมั่นคงของการขาย แต่จะเพิ่มเติมในภายหลัง...
ตอนนี้ฟังก์ชันส่วนเบี่ยงเบนมาตรฐานใน Excel
ฉันหวังว่าฉันจะไม่ทำให้คุณคลั่งไคล้คณิตศาสตร์ อาจมีบางคนต้องการข้อมูลนี้เพื่อจุดประสงค์เชิงนามธรรมหรืออย่างอื่น ทีนี้มาดูกันว่าสูตรเหล่านี้ทำงานอย่างไรใน Excel...
เพื่อกำหนดความมั่นคงของการขาย เราไม่จำเป็นต้องเจาะลึกตัวเลือกทั้งหมดสำหรับฟังก์ชันส่วนเบี่ยงเบนมาตรฐาน เราจะใช้เพียงอย่างเดียว:
ฟังก์ชัน STDEV
สพฐ(หมายเลข 1;หมายเลข 2;... )
เบอร์1 เบอร์2..- จากอาร์กิวเมนต์ตัวเลข 1 ถึง 30 ที่สอดคล้องกับประชากรทั่วไป
ทีนี้มาดูตัวอย่างกัน:
มาสร้างหนังสือและสเปรดชีตชั่วคราวกันเถอะ คุณสามารถดาวน์โหลดตัวอย่างนี้ได้ใน Excel ที่ส่วนท้ายของบทความ
ยังมีต่อ!!!
สวัสดีอีกครั้ง. ดี!? มีนาทีฟรี ไปต่อไหม?
และเพื่อความมั่นคงในการขายด้วยความช่วยเหลือ ฟังก์ชัน STDEV
เพื่อความชัดเจน เรามาดูสินค้าชั่วคราวกัน:
ในการวิเคราะห์ ไม่ว่าจะเป็นการคาดการณ์ การวิจัย หรือสิ่งอื่นที่เกี่ยวข้องกับสถิติ จำเป็นต้องใช้สามช่วงเวลาเสมอ อาจเป็นสัปดาห์ เดือน ไตรมาส หรือปี เป็นไปได้และดีที่สุดที่จะใช้เวลามากที่สุดเท่าที่จะเป็นไปได้ แต่ไม่น้อยกว่าสาม
ฉันได้แสดงการขายที่เกินจริงโดยเฉพาะ ซึ่งคุณสามารถเห็นได้ด้วยตาเปล่าว่าสิ่งใดขายได้อย่างต่อเนื่องและสิ่งใดไม่ขาย สิ่งนี้จะทำให้เข้าใจวิธีการทำงานของสูตรได้ง่ายขึ้น
ดังนั้นเราจึงมียอดขายตอนนี้เราต้องคำนวณยอดขายเฉลี่ยตามงวด
สูตรค่าเฉลี่ย AVERAGE(ข้อมูลระยะเวลา) ในกรณีของฉัน สูตรจะมีลักษณะดังนี้ =AVERAGE(C6:E6)
เราขยายสูตรสำหรับผลิตภัณฑ์ทั้งหมด ทำได้โดยการกดค้างไว้ที่มุมขวาของเซลล์ที่เลือกแล้วลากไปที่ท้ายรายการ หรือวางเคอร์เซอร์บนคอลัมน์ที่มีผลิตภัณฑ์แล้วกดคีย์ผสมต่อไปนี้:
Ctrl + Down เลื่อนเคอร์เซอร์ไปที่ด้านล่างสุดของรายการ
Ctrl + ขวา เคอร์เซอร์จะเลื่อนไปทางด้านขวาของตาราง ไปทางขวาอีกครั้งแล้วเราจะไปที่คอลัมน์พร้อมสูตร
ตอนนี้เราหนีบ
Ctrl + Shift แล้วกดขึ้น ดังนั้นเราจึงเลือกพื้นที่ของการยืดสูตร
และการกดแป้น Ctrl + D จะขยายฟังก์ชันที่เราต้องการ
โปรดจำไว้ว่าชุดค่าผสมเหล่านี้ช่วยเพิ่มความเร็วของคุณใน Excel โดยเฉพาะอย่างยิ่งเมื่อคุณทำงานกับอาร์เรย์ขนาดใหญ่
ขั้นตอนต่อไป ฟังก์ชันส่วนเบี่ยงเบนมาตรฐานเอง อย่างที่บอก เราจะใช้เพียงอันเดียว สพฐ
เรากำหนดฟังก์ชันและในค่าฟังก์ชันเราใส่ค่าการขายของแต่ละช่วงเวลา หากคุณมียอดขายในตารางเรียงต่อกัน คุณสามารถใช้ช่วงตามสูตรของฉัน =STDEV(C6:E6) หรือแสดงรายการเซลล์ที่ต้องการด้วยเครื่องหมายอัฒภาค =STDEV(C6;D6;E6)
นี่คือการคำนวณทั้งหมดและพร้อมแล้ว แต่คุณจะรู้ได้อย่างไรว่าสิ่งใดขายได้อย่างสม่ำเสมอและสิ่งใดไม่ขาย ลองใส่การประชุม XYZ โดยที่
X มีความเสถียร
Y - มีความเบี่ยงเบนเล็กน้อย
Z - ไม่เสถียร
ในการทำเช่นนี้ เราใช้ช่วงข้อผิดพลาด หากความผันผวนเกิดขึ้นภายใน 10% เราจะถือว่ายอดขายคงที่
ถ้าอยู่ระหว่าง 10 ถึง 25 เปอร์เซ็นต์ จะเป็น Y
และถ้าค่าความแปรผันเกิน 25% แสดงว่าไม่เสถียร
หากต้องการตั้งค่าตัวอักษรสำหรับแต่ละผลิตภัณฑ์อย่างถูกต้อง เราจะใช้สูตร IF ในรายละเอียดเพิ่มเติมเกี่ยวกับ ในตารางของฉัน ฟังก์ชันนี้จะมีลักษณะดังนี้:
ไอเอฟ(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))
ดังนั้นเราจึงขยายสูตรทั้งหมดสำหรับชื่อทั้งหมด
ฉันจะพยายามตอบคำถามทันที ทำไมช่วงเวลา 10% และ 25%
ในความเป็นจริง ช่วงเวลาอาจแตกต่างกัน ทั้งหมดขึ้นอยู่กับงานเฉพาะ ฉันได้แสดงมูลค่าการขายที่เกินจริงให้คุณโดยเฉพาะ โดยที่ "ตา" มองเห็นความแตกต่างได้ เห็นได้ชัดว่าผลิตภัณฑ์ 1 ขายไม่สม่ำเสมอ แต่การเปลี่ยนแปลงแสดงให้เห็นถึงยอดขายที่เพิ่มขึ้น ทิ้งรายการนี้ไว้คนเดียว...
แต่ผลิตภัณฑ์ที่ 2 มีความไม่เสถียรบนใบหน้าอยู่แล้ว และการคำนวณของเราแสดง Z ซึ่งบอกเราเกี่ยวกับความไม่แน่นอนของการขาย รายการที่ 3 และรายการที่ 5 แสดงประสิทธิภาพคงที่ โปรดทราบว่าการเปลี่ยนแปลงนั้นอยู่ภายใน 10%
เหล่านั้น. รายการที่ 5 ที่มีคะแนน 45, 46 และ 45 แสดงการเปลี่ยนแปลง 1% ซึ่งเป็นชุดตัวเลขที่คงที่
แต่ผลิตภัณฑ์ 2 ที่มีคะแนน 10, 50 และ 5 แสดงการเปลี่ยนแปลง 93% ซึ่งไม่ใช่ชุดตัวเลขที่คงที่
หลังจากคำนวณทั้งหมดแล้ว คุณสามารถใส่ตัวกรองและกรองความเสถียรออกได้ ดังนั้นหากตารางของคุณประกอบด้วยสินค้าหลายพันรายการ คุณสามารถเลือกได้อย่างง่ายดายว่ารายการใดที่ไม่คงที่ในการขาย หรือในทางกลับกัน รายการใดที่มีเสถียรภาพ
"Y" ใช้ไม่ได้ในตารางของฉัน ฉันคิดว่าจำเป็นต้องเพิ่มชุดตัวเลขเพื่อความชัดเจน ฉันจะวาด Goods 6...
คุณจะเห็นว่าชุดตัวเลข 40, 50 และ 30 แสดงการเปลี่ยนแปลง 20% ดูเหมือนว่าไม่มีข้อผิดพลาดใหญ่ แต่การแพร่กระจายยังมีนัยสำคัญ ...
และเพื่อสรุป:
10,50,5 - Z ไม่เสถียร ความแปรปรวนมากกว่า 25%
40,50,30 - Y คุณสามารถให้ความสนใจกับผลิตภัณฑ์นี้และปรับปรุงยอดขายได้ ความแปรปรวนน้อยกว่า 25% แต่มากกว่า 10%
45,46,45 - X คือความเสถียร ยังไม่มีอะไรต้องดำเนินการกับผลิตภัณฑ์นี้ ความแปรปรวนน้อยกว่า 10%
นั่นคือทั้งหมด! ฉันหวังว่าฉันจะอธิบายทุกอย่างชัดเจน ถ้าไม่ให้ถามสิ่งที่ไม่ชัดเจน และฉันจะขอบคุณสำหรับทุกความคิดเห็นไม่ว่าจะเป็นคำชมหรือคำวิจารณ์ ดังนั้นฉันจะรู้ว่าคุณกำลังอ่านฉันและคุณซึ่งสำคัญมากและน่าสนใจ ดังนั้นบทเรียนใหม่จะปรากฏขึ้น