Page 1 of 1
หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 2:13 pm
by Pongsakovic
สวัสดีครับ ขอคำชี้แนะจากทุกท่านเกี่ยวกับการหาค่าเฉลี่ยตัวเลข โดยที่ต้องการ ตัดค่าสูงสุด และค่าต่ำสุด ออก ก่อนน้ำมาหาค่าเฉลี่ย
แต่ปัญหาที่พบ คือ คำสั่งที่เขียนโดยตัดค่าสูงสุดและค่าต่ำสุดออกแล้ว นำมาหาค่าเฉลี่ย ตามคอลัมน์ O5:O9
กรณีที่ 1 ตัวเลขครบ 12 เดือน สูตรใช้งานได้ดี (column A5)
กรณีที่ 2 ตัวเลขมีแค่ 2 เดือน สูตรจะตัดค่า max, min ทิ้งทำให้หาค่าไม่ได้ (column A6) กรณีมี 2 ค่า ให้เลือกแสดงค่าน้อยสุด ค่าเดียว
กรณีที่ 3 ตัวเลขมีแค่ 1 เดือน แต่สูตร เลือกค่านั้นมาให้เลย อันนี้โอเค
กรณีที่ 4 เมื่อแทนค่าช่องว่าง (blank) ด้วยเลขศูนย์ หรือ เครื่องหมาย "-" สูตรจะนับเป็นจำนวนทำให้ค่า min เปลี่ยนไปและนำมาคำนวณค่าเฉลี่ย
และสุดท้าย ต้องการให้ตัวเลขเปรียบเทียบข้อมูลระหว่างปี 2018 vs 2019 ดูว่ายอดขาย โตขึ้น หรือ ลดลง ต้องเขียนคำสั่งยังไงครับ
ตัวอย่างไฟล์ผมตัดมาแค่อธิบายแต่ละเคส แต่ข้อมูลจริงมันรวมกันเป็นจำนวนมาก ปัญหาหลักๆเท่าที่เจอมีแค่นี้ครับ
ขอคำแนะนำการเขียนคำสั่งให้ครอบคลุมที่ติดปัญหา และผมยังเขียนคำสั่งไม่ผ่านด้วยครับ
ขอบคุณทุกคำชี้แนะคร้บ
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 2:26 pm
by logic
น่าจะแบบนี้ครับ
O5 =IF(COUNT(B5:M5)=1,SUM(B5:M5),IF(COUNT(B5:M5)=2,LOOKUP(9.99999999999999E+307,B5:M5),(SUM(B5:M5)-MIN(IF(B5:M5>0,B5:M5))-MAX(IF(B5:M5>0,B5:M5)))/(COUNTIF(B5:M5,">0")-2))) Ctrl+Shift+Enter คัดลอกสูตรลง
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 2:49 pm
by Pongsakovic
logic wrote: Fri Sep 27, 2019 2:26 pm
น่าจะแบบนี้ครับ
O5 =IF(COUNT(B5:M5)=1,SUM(B5:M5),IF(COUNT(B5:M5)=2,LOOKUP(9.99999999999999E+307,B5:M5),(SUM(B5:M5)-MIN(IF(B5:M5>0,B5:M5))-MAX(IF(B5:M5>0,B5:M5)))/(COUNTIF(B5:M5,">0")-2))) Ctrl+Shift+Enter คัดลอกสูตรลง
==================================================================================================
ขอบคุณมากครับ ไร้ที่ติเลยครับ ทดสอบผ่านทุกเงื่อนไข
สอบถามเพิ่มเติมนิดนึงครับ
LOOKUP(9.99999999999999E+307 <<<<< อันนี้หมายความว่ายังไงครับ
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 3:01 pm
by logic
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 3:11 pm
by menem
O5
=IFERROR((SUMPRODUCT(B5:M5,--(B5:M5<>0))-MAX(B5:M5)-SMALL(B5:M5,COUNTIF(B5:M5,0)+1))/IF(COUNTIF(B5:M5,">0")>2,COUNTIF(B5:M5,">0")-2,0),SMALL(B5:M5,COUNTIF(B5:M5,0)+1))
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 3:35 pm
by Pongsakovic
menem wrote: Fri Sep 27, 2019 3:11 pm
O5
=IFERROR((SUMPRODUCT(B5:M5,--(B5:M5<>0))-MAX(B5:M5)-SMALL(B5:M5,COUNTIF(B5:M5,0)+1))/IF(COUNTIF(B5:M5,">0")>2,COUNTIF(B5:M5,">0")-2,0),SMALL(B5:M5,COUNTIF(B5:M5,0)+1))
===========================================================================================================
โอ้วว ขอบคุณมากครับผม สูตรนี้ แม้จะลองคีย์เลข 0 ลงในช่องว่าง ค่าไม่เปลี่ยนและคำตอบตรงเลยครับ ขอบคุณอีกครั้งครับ
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 3:36 pm
by Pongsakovic
============================================================
ขอบคุณมากครับ อ่านแล้วเข้าใจขึ้นเยอะเลยครับ ความรู้ใหม่จริงๆ
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 3:41 pm
by Pongsakovic
logic wrote: Fri Sep 27, 2019 2:26 pm
น่าจะแบบนี้ครับ
O5 =IF(COUNT(B5:M5)=1,SUM(B5:M5),IF(COUNT(B5:M5)=2,LOOKUP(9.99999999999999E+307,B5:M5),(SUM(B5:M5)-MIN(IF(B5:M5>0,B5:M5))-MAX(IF(B5:M5>0,B5:M5)))/(COUNTIF(B5:M5,">0")-2))) Ctrl+Shift+Enter คัดลอกสูตรลง
====================================================================================
ลองทดสอบละเอียดอีกครั้ง ในแถวที่มีช่องว่าง (blank) เมื่อกรอกเลข 0 หรือ - ลงไป กรณีมีตัวเลขแค่ 2-3 ตัว ค่าเฉลี่ยจะกลายเป็น #DIV/0! ครับพี่ มีวิธีแก้มั้ยครับ
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 3:54 pm
by logic
ช่วยแนบไฟล์มาด้วยครับ
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 4:58 pm
by Pongsakovic
logic wrote: Fri Sep 27, 2019 3:54 pm
ช่วยแนบไฟล์มาด้วยครับ
===========================================================================================
เดิม J6, K7 (blank)
พอเติม 0 ค่าเฉลี่ยที่ได้เป็น #DIV/0! ครับ ส่วนเคสอื่นๆ สูตรทำงานได้ดีเลยครับ
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Fri Sep 27, 2019 10:30 pm
by snasui
ตัวอย่างการปรับสูตรครับ
เซลล์ P5 คีย์
=IF(COUNTIF(B5:M5,">0")=1,SUM(B5:M5),IF(COUNTIF(B5:M5,">0")=2,LOOKUP(2,1/(B5:M5>0),B5:B5),(SUM(B5:M5)-MIN(IF(B5:M5>0,B5:M5))-MAX(IF(B5:M5>0,B5:M5)))/(COUNTIF(B5:M5,">0")-2)))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
Re: หาค่าเฉลี่ยโดยการตัดค่าสูงสุดและต่ำสุดออก และไม่นับเลขศูนย์
Posted: Sat Sep 28, 2019 11:00 am
by Bo_ry
Q5
=AVERAGE(INDEX(AGGREGATE(15,6,1/(1/B5:M5),ROW(INDEX(A:A,MAX(1,MIN(COUNTIF(B5:M5,">0")-1,2))):INDEX(A:A,MAX(COUNTIF(B5:M5,">0")-1,1)))),))