ฟังก์ชันทางสถิติของ Excel: บทช่วยสอนพร้อมตัวอย่าง ตอนที่ 3

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

ในบทความนี้ เราจะเจาะลึกลงไปในฟังก์ชันทางสถิติของ Excel สำหรับการคำนวณฟังก์ชันเส้นแนวโน้ม

โปรดทราบว่าฟังก์ชันทางสถิติบางอย่างถูกนำมาใช้ใน Excel เวอร์ชันล่าสุด ดังนั้นจึงไม่มีในเวอร์ชันเก่า

เวลาในการอ่านโดยประมาณ: 12 นาที

ฟังก์ชันเส้นแนวโน้ม

Forecast

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

วากยสัมพันธ์

= FORECAST( x, known_y's, known_x's )

วิชา

• x: ค่า x ตัวเลขที่คุณต้องการทำนายค่า y ใหม่
• known_y's: อาร์เรย์ของค่า y ที่ทราบ
• known_x's: อาร์เรย์ของค่า x ที่ทราบ

โปรดทราบว่าความยาวของอาร์เรย์ของ known_x จะต้องเหมือนกับของ known_y และความแปรปรวนของ known_x ไม่จำเป็นต้องเป็นศูนย์

ตัวอย่าง

ในสเปรดชีตต่อไปนี้ ฟังก์ชัน FORECAST Excel ใช้เพื่อทำนายจุดเพิ่มเติมตามเส้นตรงที่เหมาะสมที่สุดผ่านชุดค่า x และ y ที่ทราบ (เก็บไว้ในเซลล์ F2:F7 และ G2:G7)

ดังที่แสดงในเซลล์ F7 ของสเปรดชีต ฟังก์ชันในการคำนวณค่า y ที่คาดหวังที่ x=7 คือ :=FORECAST( 7, G2:G7, F2:F7 )

สิ่งนี้ให้ผลลัพธ์ 32.666667 .

Intercept

ในบรรดาฟังก์ชันการคาดการณ์ของ Excel เราพบ Intercept. ฟังก์ชัน Intercept ของ Excel จะคำนวณค่าตัดแกน (ค่าที่จุดตัดของแกน y) ของเส้นการถดถอยเชิงเส้นข้ามชุดค่า x และ y ที่กำหนด

วากยสัมพันธ์

= INTERCEPT( known_y's, known_x's )

วิชา

• known_y's: อาร์เรย์ของค่า y ที่ทราบ
• known_x's: อาร์เรย์ของค่า x ที่ทราบ

โปรดทราบว่าความยาวของอาร์เรย์ของ known_x จะต้องเหมือนกับของ known_y และความแปรปรวนของ known_x ไม่จำเป็นต้องเป็นศูนย์

ตัวอย่าง

สเปรดชีตต่อไปนี้แสดงตัวอย่างของฟังก์ชัน Intercept ของ Excel ใช้ในการคำนวณจุดที่เส้นการถดถอยเชิงเส้นผ่าน known_x และ known_y (แสดงอยู่ในเซลล์ F2:F7 และ G2:G7) จะตัดแกน y

Le known_x และ known_y ถูกลงจุดบนกราฟในสเปรดชีต

ดังที่แสดงในเซลล์ F9 ของสเปรดชีต สูตรสำหรับฟังก์ชัน Intercept คือ :=INTERCEPT( G2:G7, F2:F7 )

ซึ่งให้ผลลัพธ์ 2.4 .

Slope

ฟังก์ชั่นการทำนายที่น่าสนใจอีกอย่างหนึ่งคือความชัน (Slope) Excel จะคำนวณความชันของเส้นการถดถอยเชิงเส้นผ่านชุดค่า x และ y ที่กำหนด

ไวยากรณ์ของฟังก์ชันคือ:

วากยสัมพันธ์

= SLOPE( known_y's, known_x's )

วิชา

• known_y's: อาร์เรย์ของค่า y ที่ทราบ
• known_x's: อาร์เรย์ของค่า x ที่ทราบ

โปรดทราบว่าความยาวของอาร์เรย์ของ known_x จะต้องเหมือนกับของ known_y และความแปรปรวนของ known_x ไม่จำเป็นต้องเป็นศูนย์

ตัวอย่าง

สเปรดชีตต่อไปนี้แสดงตัวอย่างของฟังก์ชัน Slope (ความชัน) ของ Excel ที่ใช้ในการคำนวณความชันของเส้นถดถอยเชิงเส้นผ่าน known_x และ known_yในเซลล์ F2:F7 และ G2:G7

Le known_x และ known_y ถูกลงจุดบนกราฟในสเปรดชีต

ดังที่แสดงในเซลล์ F9 ของสเปรดชีต สูตรสำหรับฟังก์ชัน Intercept คือ :=SLOPE( G2:G7, F2:F7 )

ซึ่งให้ผลลัพธ์ 4.628571429.

Trend

ฟังก์ชันพยากรณ์ Excel ที่น่าสนใจมากคือ แนวโน้ม Excel (เทรนด์) คำนวณเส้นแนวโน้มเชิงเส้นผ่านชุดค่า y ที่กำหนดและ (เป็นทางเลือก) ชุดค่า x ที่กำหนด

จากนั้นฟังก์ชันจะขยายเส้นแนวโน้มเชิงเส้นเพื่อคำนวณค่า y เพิ่มเติมสำหรับชุดค่า x ใหม่เพิ่มเติม

ไวยากรณ์ของฟังก์ชันคือ:

วากยสัมพันธ์

= TREND( known_y's, [known_x's], [new_x's], [const] )

วิชา

• known_y's: อาร์เรย์ของค่า y ที่ทราบ
• [known_x's]: อาร์เรย์หนึ่งหรือหลายค่าของค่า x ที่ทราบ นี่เป็นอาร์กิวเมนต์ทางเลือกที่หากระบุไว้ ควรมีความยาวเท่ากับชุดของ known_y's. หากละเว้น เซตของ [known_x's] รับค่า {1, 2, 3, …}
• [ของใหม่_x]: อาร์กิวเมนต์ที่เป็นทางเลือก โดยระบุอาร์เรย์ของค่าตัวเลขตั้งแต่หนึ่งอาร์เรย์ขึ้นไปซึ่งแสดงถึงชุดของค่า x ใหม่ ซึ่งคุณต้องการคำนวณค่า y ใหม่ที่สอดคล้องกัน แต่ละอาร์เรย์ของ [นิว_เอ็กซ์] ควรตรงกับอาร์เรย์ของ [known_x's] ถ้าข้อโต้แย้ง [นิว_เอ็กซ์] ถูกละไว้ จะถูกตั้งค่าให้เท่ากับ [known_x's] .
• [ค่าใช้จ่าย]: อาร์กิวเมนต์ตรรกะทางเลือกที่ระบุว่าค่าคงที่ 'b' ในสมการเชิงเส้นหรือไม่ y = ม. x + b จะต้องถูกบังคับให้เท่ากับศูนย์ ตัวเอง [ราคา] เป็น TRUE (หรือถ้าละเว้นอาร์กิวเมนต์นี้) ค่าคงที่ b จะได้รับการปฏิบัติตามปกติ
• ตัวเอง [ราคา] เป็น FALSE ค่าคงที่ b ถูกตั้งค่าเป็น 0 และสมการเส้นตรงจะกลายเป็น y = ม .

ตัวอย่าง

ในสเปรดชีตต่อไปนี้ ฟังก์ชัน Excel Trend ใช้เพื่อขยายชุดของค่า x และ y ที่อยู่บนเส้นตรง y = 2x + 10 ค่า x และ y ที่ทราบจะถูกเก็บไว้ในเซลล์ A2-B5 ของ สเปรดชีตและยังแสดงในกราฟสเปรดชีตอีกด้วย

โปรดทราบว่าจุดที่กำหนดไม่จำเป็นจะต้องพอดีกับเส้นตรงทุกประการ y = 2x + 10 (แม้ว่าในตัวอย่างนี้จะต้องเป็นเช่นนั้นก็ตาม) ฟังก์ชัน Trend ของ Excel จะค้นหาบรรทัดที่เหมาะสมที่สุดสำหรับชุดค่าใดๆ ที่คุณระบุ

ฟังก์ชัน Trend ใช้วิธีการกำลังสองน้อยที่สุดเพื่อค้นหาเส้นที่พอดีที่สุด จากนั้นใช้เพื่อคำนวณค่า y ใหม่สำหรับค่า x ใหม่ที่ระบุ

ในตัวอย่างนี้ ค่าของ [นิว_เอ็กซ์] จะถูกเก็บไว้ในเซลล์ A8-A10 และมีการใช้ฟังก์ชัน Trend ของ Excel ในเซลล์ B8-B10 เพื่อค้นหาค่า y ใหม่ที่สอดคล้องกัน ดังที่แสดงในแถบสูตร สูตรคือ := แนวโน้ม( B2:B5, A2:A5, A8:A10 )

คุณจะเห็นว่าฟังก์ชัน Trend ในแถบสูตรอยู่ในวงเล็บปีกกา { } นี่แสดงว่าฟังก์ชันถูกป้อนเป็น สูตรอาร์เรย์ .

Growth

ในบรรดาฟังก์ชันการคาดการณ์ของ Excel เราพบ Growth. ฟังก์ชั่น Growth Excel คำนวณเส้นโค้งการเติบโตแบบเอ็กซ์โปเนนเชียลผ่านชุดค่า y ที่กำหนดและ (ไม่บังคับ) ชุดค่า x หนึ่งชุดขึ้นไป จากนั้นฟังก์ชันจะขยายเส้นโค้งเพื่อคำนวณค่า y เพิ่มเติมสำหรับชุดค่า x ใหม่เพิ่มเติม

ไวยากรณ์ของฟังก์ชันคือ:

วากยสัมพันธ์

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

วิชา

• known_y's: อาร์เรย์ของค่า y ที่ทราบ
• [known_x's]: อาร์เรย์หนึ่งหรือหลายค่าของค่า x ที่ทราบ นี่เป็นอาร์กิวเมนต์ทางเลือกที่หากระบุไว้ ควรมีความยาวเท่ากับชุดของ known_y's. หากละเว้น เซตของ [known_x's] รับค่า {1, 2, 3, …}
• [ของใหม่_x]: ชุดของค่า x ใหม่ ซึ่งฟังก์ชันจะคำนวณค่า y ใหม่ที่สอดคล้องกัน หากละไว้ จะถือว่าเซตของ [นิว_เอ็กซ์] เท่ากับของ [known_x's] และฟังก์ชันจะส่งกลับค่า y ที่อยู่บนกราฟการเติบโตแบบเอ็กซ์โปเนนเชียลที่คำนวณได้
• [ค่าใช้จ่าย]: อาร์กิวเมนต์ตรรกะทางเลือกที่ระบุว่าค่าคงที่ 'b' ในสมการเชิงเส้นหรือไม่ y = b * m^x จะต้องถูกบังคับให้เท่ากับ 1 ถ้า [ราคา] เป็น TRUE (หรือถ้าละเว้นอาร์กิวเมนต์นี้) ค่าคงที่ b จะได้รับการปฏิบัติตามปกติ ตัวเอง [ราคา] เป็น FALSE ค่าคงที่ b ถูกตั้งค่าเป็น 1 และสมการเส้นตรงจะกลายเป็น y = mx .

ตัวอย่าง

ในสเปรดชีตต่อไปนี้ ฟังก์ชันการเติบโตของ Excel ใช้เพื่อขยายชุดของค่า x และ y ที่อยู่บนเส้นโค้งการเติบโตแบบเอ็กซ์โพเนนเชียล y = 5 * 2^x สิ่งเหล่านี้จะถูกจัดเก็บไว้ในเซลล์ A2-B5 ของสเปรดชีต และยังปรากฏในแผนภูมิสเปรดชีตด้วย

ฟังก์ชันการเติบโตจะคำนวณเส้นโค้งการเติบโตแบบเอ็กซ์โปเนนเชียลที่เหมาะกับค่า x และ y ที่ทราบที่ให้ไว้มากที่สุด ในตัวอย่างง่ายๆ นี้ เส้นโค้งที่เหมาะสมที่สุดคือเส้นโค้งเอ็กซ์โปเนนเชียล y = 5 * 2^x

เมื่อ Excel คำนวณสมการเส้นโค้งการเติบโตแบบเอ็กซ์โปเนนเชียล ก็จะสามารถใช้เพื่อคำนวณค่า y ใหม่สำหรับค่า x ใหม่ที่ให้ไว้ในเซลล์ A8-A10

ในตัวอย่างนี้ ค่าของ [new_x's] จะถูกเก็บไว้ในเซลล์ A8-A10 และฟังก์ชัน Growth ของ Excel ถูกแทรกลงในเซลล์ B8-B10 ดังที่แสดงในแถบสูตร สูตรสำหรับสิ่งนี้คือ:=Growth( B2:B5, A2:A5, A8:A10 )

คุณจะเห็นว่าฟังก์ชันการเติบโตในแถบสูตรอยู่ในวงเล็บปีกกา { } นี่แสดงว่าฟังก์ชันถูกป้อนเป็น สูตรอาร์เรย์ .

โปรดทราบว่าแม้ว่าจุดในตัวอย่างข้างต้นจะพอดีกับเส้นโค้ง y = 5 * 2^x แต่ก็ไม่จำเป็น ฟังก์ชั่น Growth Excel จะค้นหาเส้นโค้งที่เหมาะสมที่สุดสำหรับชุดค่าใดๆ ที่คุณระบุ

ฟังก์ชั่นทางการเงิน

ผล

ฟังก์ชั่น Effect Excel ส่งกลับอัตราดอกเบี้ยรายปีที่แท้จริงสำหรับอัตราดอกเบี้ยที่กำหนดและจำนวนงวดการทบต้นที่กำหนดต่อปี

อัตราดอกเบี้ยรายปีที่มีผลบังคับใช้

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

อัตราดอกเบี้ยรายปีที่แท้จริงคำนวณโดยใช้สมการต่อไปนี้:

นกพิราบ nominal_rate คืออัตราดอกเบี้ยที่กำหนด e npery คือจำนวนงวดทบต้นต่อปี

ไวยากรณ์ของฟังก์ชันคือ:

วากยสัมพันธ์

= EFFECT( nominal_rate, npery )

วิชา

• nominal_rate: อัตราดอกเบี้ยที่กำหนด (ต้องเป็นค่าตัวเลขระหว่าง 0 ถึง 1)
• npery: จำนวนงวดประนอมต่อปี (ต้องเป็นจำนวนเต็มบวก)

ตัวอย่าง

สเปรดชีตต่อไปนี้แสดงสามตัวอย่างของฟังก์ชันเอฟเฟกต์ Excel:

ถ้าผลของฟังก์ชัน Effect แสดงเป็นทศนิยมหรือแสดง 0% ปัญหาทั้งสองนี้น่าจะเกิดจากการจัดรูปแบบของเซลล์ที่มีฟังก์ชัน Effect.

ดังนั้นปัญหาสามารถแก้ไขได้โดยการจัดรูปแบบเซลล์เป็นเปอร์เซ็นต์โดยมีตำแหน่งทศนิยม

เพื่อทำสิ่งนี้:

1. เลือกเซลล์ที่จะจัดรูปแบบเป็นเปอร์เซ็นต์
2. เปิดกล่องโต้ตอบ "จัดรูปแบบเซลล์" โดยใช้วิธีใดวิธีหนึ่งต่อไปนี้:
   • คลิกขวาที่เซลล์หรือช่วงที่เลือกแล้วเลือกตัวเลือก จัดรูปแบบเซลล์... จากเมนูบริบท
   • คลิกตัวเปิดใช้กล่องโต้ตอบในการจัดกลุ่มตัวเลขบนแท็บ หน้าแรก ริบบิ้น Excel;
   • ใช้แป้นพิมพ์ลัด CTRL-1 (เช่น เลือกปุ่ม CTRL และกดค้างไว้ เลือกปุ่ม “1” (หนึ่ง))
3. ในกล่องโต้ตอบ "จัดรูปแบบเซลล์":
   • ตรวจสอบให้แน่ใจบัตร Numero ที่ด้านบนของกล่องโต้ตอบถูกเลือก
   • เลือก เปอร์เซ็นต์ จากรายการ หมวดหมู่ ทางด้านซ้ายของกล่องโต้ตอบ .จะแสดงตัวเลือกเพิ่มเติมทางด้านขวาของช่องทำเครื่องหมาย ให้คุณเลือกจำนวนตำแหน่งทศนิยมที่ต้องการให้ปรากฏได้
   • เมื่อคุณเลือกจำนวนตำแหน่งทศนิยมที่คุณต้องการแสดงแล้ว ให้คลิก OK .

Nominal

ฟังก์ชั่น Nominal Excel จะส่งกลับอัตราดอกเบี้ยที่ระบุสำหรับอัตราดอกเบี้ยที่แท้จริงที่กำหนดและจำนวนงวดการทบต้นที่กำหนดต่อปี

ไวยากรณ์ของฟังก์ชันคือ:

วากยสัมพันธ์

= NOMINAL( effect_rate, npery )

วิชา

• effect_rate: อัตราดอกเบี้ยที่แท้จริง (ค่าตัวเลขระหว่าง 0 ถึง 1)
• npery: จำนวนงวดประนอมต่อปี (ต้องเป็นจำนวนเต็มบวก)

ตัวอย่าง

ในสเปรดชีตต่อไปนี้ ฟังก์ชัน Nominal ของ Excel ใช้ในการคำนวณอัตราดอกเบี้ยที่กำหนดของสินเชื่อสามรายการที่มีเงื่อนไขต่างกัน

Ercole Palmeri