Page 1 of 1
ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 9:03 am
by Totem
เรียน อาจารย์และเพื่อนสมาชิกทุกคน
วิธีหาค่าร้อยละในช่อง C4 : C8 แล้วปัดเศษจุดทศนิยม 2 ตำแหน่ง และให้ผลรวมในช่อง C9 เป็น 100% เสมอ
เพราะบางครั้งเกิน100% บางครั้งไม่ถึง100% จะมีวิธีอย่างไรให้ถูกต้องครับ
ขอบคุณครับ
รวม.xlsx
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 10:41 am
by menem
ปกติการคำนวณแบบนี้ (มีการใช้ Round ) ผลรวมจะเกิด Diff ครับ
ซึ่งโดยมาก เราอาจจะใช้วิธีปัดเศษเข้าไปในค่าใดค่าหนึ่งตายตัว
หรือใช้วิธีวางไว้ที่ตัวที่มีค่ามากที่สุดก็ได้
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 10:59 am
by Totem
menem wrote:ปกติการคำนวณแบบนี้ (มีการใช้ Round ) ผลรวมจะเกิด Diff ครับ
ซึ่งโดยมาก เราอาจจะใช้วิธีปัดเศษเข้าไปในค่าใดค่าหนึ่งตายตัว
หรือใช้วิธีวางไว้ที่ตัวที่มีค่ามากที่สุดก็ได้
ถ้าช่อง C4:C8 ไม่มีใช้ Round ก็ได้ ให้รวมค่าเป็น 100 จะมีวิธีไหนครับ เพราะหากข้อมูลในช่องจำนวนมีค่ามากสุดเท่ากันก็จะให้สูตรลักษณะนี้ไม่ได้ เช่น ช่อง B5 = 61 รวมเท่ากับ 100.01 สูตรจะไม่ครอบคลุมปัญหานี้
หรือใช้วิธีปัดเศษเข้าไปในค่าใดค่าหนึ่งตายตัว หากมีค่าอื่นที่จำนวนเท่ากันกับค่าหนึ่งที่ปัดเศษตายตัว จะทำให้
ค่าร้อยละที่แสดงออกมาทั้ง 2 รายการไม่เท่ากันครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 11:16 am
by menem
ในกรณีที่ใช้สูตรแบบไม่มี Round มากำกับ
ผลรวมจะเป็น 100% ครับ แต่เวลาเราเอามาดู
แล้วรวมโดยใช้เครื่องคิดเลขข้างนอก อาจจะ
ไม่เท่ากับ 100% เพราะการแสดงผล กับค่าที่
Excel คำนวณได้จริงมันต่างกันอยู่ครับ
กรณี Fix การปัดเศษเข้าไปที่ตัวใดตัวหนึ่งโดยตรงสูตร
จะเป็น =100-sum( ผลการคำนวณของบรรทัดอื่น ๆ )
วิธีนี้จะดีในแง่ที่ว่าเรารู้เสมอว่าการปัดเศษจะกระทำกับ
รายการไหน (และไม่มีโอกาสปัดซ้ำ เหมือนกับวิธีการ
วางในค่า Max หากค่า Max มีมากกว่าหนึ่งรายการ)
แต่ข้อเสียที่อาจจะมีขึ้นคือ กรณีรายการที่ Fixed การปัดเศษ
มีค่าเป็น 0 มันก็จะไม่สมเหตุสมผลที่จะปัดเข้าไปในรายการนั้น ๆ ครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 11:28 am
by Totem
menem wrote:ในกรณีที่ใช้สูตรแบบไม่มี Round มากำกับ
ผลรวมจะเป็น 100% ครับ แต่เวลาเราเอามาดู
แล้วรวมโดยใช้เครื่องคิดเลขข้างนอก อาจจะ
ไม่เท่ากับ 100% เพราะการแสดงผล กับค่าที่
Excel คำนวณได้จริงมันต่างกันอยู่ครับ
กรณี Fix การปัดเศษเข้าไปที่ตัวใดตัวหนึ่งโดยตรงสูตร
จะเป็น =100-sum( ผลการคำนวณของบรรทัดอื่น ๆ )
วิธีนี้จะดีในแง่ที่ว่าเรารู้เสมอว่าการปัดเศษจะกระทำกับ
รายการไหน (และไม่มีโอกาสปัดซ้ำ เหมือนกับวิธีการ
วางในค่า Max หากค่า Max มีมากกว่าหนึ่งรายการ)
แต่ข้อเสียที่อาจจะมีขึ้นคือ กรณีรายการที่ Fixed การปัดเศษ
มีค่าเป็น 0 มันก็จะไม่สมเหตุสมผลที่จะปัดเข้าไปในรายการนั้น ๆ ครับ
ใช่ครับ ในกรณีที่ใช้สูตรแบบไม่มี Round มากำกับ
ผลรวมจะเป็น 100% แล้วกรณี Fix ก็ยังมีการแก้ไขปัญหายังไม่ครอบคลุม ผมลองทำหลายแบบแล้ว ยังไม่สำเร็จครับ
จะมีวิธีไหนหรือไม่...
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 11:50 am
by menem
งั้นลองแบบนี้ดูครับ
- คำนวณหาว่ามี Diff เท่าไหร่
- ตรวจสอบการ Allocate ค่า Diff
- การวางจะวางครั้งเดียว กับค่าที่มากที่สุดของช่วง
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 12:42 pm
by Totem
menem wrote:งั้นลองแบบนี้ดูครับ
- คำนวณหาว่ามี Diff เท่าไหร่
- ตรวจสอบการ Allocate ค่า Diff
- การวางจะวางครั้งเดียว กับค่าที่มากที่สุดของช่วง
หากค่าที่มากที่สุดของช่วงนั้นเท่ากัน เช่น B4 = 61 , B5 = 61 ที่ช่อง C9 รวมได้ 100 แต่ ร้อยละในช่อง C4 = 44.21 ,
C5 = 44.20 เกิดปัญหาว่าไม่ตรงกันครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 1:09 pm
by XDoll
C4:C8 ให้เอา Round ออกครับ
ส่วน C9 ให้เปลี่ยนเป็น =ROUND(SUMPRODUCT(C4:C8),2)
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 1:30 pm
by Totem
XDoll wrote: C4:C8 ให้เอา Round ออกครับ
ส่วน C9 ให้เปลี่ยนเป็น =ROUND(SUMPRODUCT(C4:C8),2)
หากเอา Round ออกเช่นนี้ C4:C8 ปรับให้เป็นจุดทศนิยม 2 ตำแหน่ง จะได้ 99.99 เมื่อใช้สูตร=ROUND(SUMPRODUCT(C4:C8),2)ทำให้ C9 = 100 แต่จำนวน C4:C8 จะยังไปถูกต้องครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 3:07 pm
by Totem
Totem wrote:XDoll wrote: C4:C8 ให้เอา Round ออกครับ
ส่วน C9 ให้เปลี่ยนเป็น =ROUND(SUMPRODUCT(C4:C8),2)
หากเอา Round ออกเช่นนี้ C4:C8 ปรับให้เป็นจุดทศนิยม 2 ตำแหน่ง จะได้ 99.99 เมื่อใช้สูตร=ROUND(SUMPRODUCT(C4:C8),2)ทำให้ C9 = 100 แต่จำนวน C4:C8 จะยังไม่ถูกต้องครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 3:39 pm
by DhitiBank
ก่อนอื่น ขอถามก่อนว่าทศนิยมหลักที่ 2 สำคัญมากหรือไม่ครับ เพราะหากไม่สำคัญอะไรมาก แค่อยากให้ตรงกันเฉยๆ ผมมักจะใช้วิธีนี้คือ
1. C4:C7 ใช้สูตรตามปกติ (มี Round)
2. C8 คีย์
=IF(ROUND(B8/$B$9*100,2)+SUM($D$4:$D$7)=100,ROUND(B8/$B$9*100,2),100-SUM($D$4:$D$7))
มันก็จะตรงกันครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 4:25 pm
by Totem
DhitiBank wrote:ก่อนอื่น ขอถามก่อนว่าทศนิยมหลักที่ 2 สำคัญมากหรือไม่ครับ เพราะหากไม่สำคัญอะไรมาก แค่อยากให้ตรงกันเฉยๆ ผมมักจะใช้วิธีนี้คือ
1. C4:C7 ใช้สูตรตามปกติ (มี Round)
2. C8 คีย์
=IF(ROUND(B8/$B$9*100,2)+SUM($D$4:$D$7)=100,ROUND(B8/$B$9*100,2),100-SUM($D$4:$D$7))
มันก็จะตรงกันครับ
สรุปได้ว่า
ช่อง C8 ใช้สูตรข้างต้น = 15.38
ช่อง C4 = 67.03
ช่อง C5 = 15.38
ช่อง C6 = 2.20
ช่อง C7 = 0.00
รวม ช่อง C9 = 99.99
ก็ยังไม่ตรงครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 4:27 pm
by DhitiBank
ลองดูในไฟล์แนบครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 5:47 pm
by Totem
ทศนิยม 2 ตำแหน่งจะมีความสำคัญ เมื่อมีจำนวนเท่ากันในช่อง B2:B8 ในตัวอย่างนี้ คำตอบ C2=15.38 และ C8=15.38
จะต้องเท่ากันครับ ถ้าว่าปัดเศษที่จำนวนไม่เท่ากัน ก็ไม่ถือว่าสำคัญ ปัดเศษได้เลย ให้ได้ รวมคือ 100 ครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 6:04 pm
by DhitiBank
Totem wrote:ทศนิยม 2 ตำแหน่งจะมีความสำคัญ เมื่อมีจำนวนเท่ากันในช่อง B2:B8 ในตัวอย่างนี้ คำตอบ C2=15.38 และ C8=15.38
จะต้องเท่ากันครับ ถ้าว่าปัดเศษที่จำนวนไม่เท่ากัน ก็ไม่ถือว่าสำคัญ ปัดเศษได้เลย ให้ได้ รวมคือ 100 ครับ
จริงด้วยครับ
ผมลืมคิดไป ถ้าอย่างนั้น ลองแบบนี้ครับ
D3 คีย์ Find
D4 คีย์
=IF($C$9=100,0,IF(SUM(D$3:D3)=1,0,IF(COUNTIF($C$4:$C$8,C4)=1,1,0)))
enter --> คัดลอกลงถึง D8
E4 คีย์
=IF($C$9<>100,IF(D4=0,C4,100-SUMPRODUCT(--($D$4:$D$8=0),$C$4:$C$8))) สูตรนี้ไม่เอาครับ ขอปรับเป็นด้านล่าง
enter --> คัดลอกลงถึง E8
E9 ลองหาผลรวมดูครับ
แก้ไขครับ: แต่ในไฟล์ไม่ได้แก้
E4 คีย์
=IF(D4=0,C4,100-SUMPRODUCT(--($D$4:$D$8=0),$C$4:$C$8))
Re: ทำให้ร้อยละรวมได้100
Posted: Wed Jan 20, 2016 9:36 pm
by menem
ลองดูอีกทีครับ
Re: ทำให้ร้อยละรวมได้100
Posted: Thu Jan 21, 2016 9:47 am
by Totem
DhitiBank wrote:Totem wrote:ทศนิยม 2 ตำแหน่งจะมีความสำคัญ เมื่อมีจำนวนเท่ากันในช่อง B2:B8 ในตัวอย่างนี้ คำตอบ C2=15.38 และ C8=15.38
จะต้องเท่ากันครับ ถ้าว่าปัดเศษที่จำนวนไม่เท่ากัน ก็ไม่ถือว่าสำคัญ ปัดเศษได้เลย ให้ได้ รวมคือ 100 ครับ
จริงด้วยครับ
ผมลืมคิดไป ถ้าอย่างนั้น ลองแบบนี้ครับ
D3 คีย์ Find
D4 คีย์
=IF($C$9=100,0,IF(SUM(D$3:D3)=1,0,IF(COUNTIF($C$4:$C$8,C4)=1,1,0)))
enter --> คัดลอกลงถึง D8
E4 คีย์
=IF($C$9<>100,IF(D4=0,C4,100-SUMPRODUCT(--($D$4:$D$8=0),$C$4:$C$8))) สูตรนี้ไม่เอาครับ ขอปรับเป็นด้านล่าง
enter --> คัดลอกลงถึง E8
E9 ลองหาผลรวมดูครับ
แก้ไขครับ: แต่ในไฟล์ไม่ได้แก้
E4 คีย์
=IF(D4=0,C4,100-SUMPRODUCT(--($D$4:$D$8=0),$C$4:$C$8))
menem wrote:ลองดูอีกทีครับ
ลองสูตรทั้งสองแบบและได้ปรับอีกเล็กน้อย นำไปใช้ได้ตามต้องการครับ
ขอบคุณ คุณDhitiBankและคุณmenem ครับ