ทำให้ร้อยละรวมได้100

[Totem]

เรียน อาจารย์และเพื่อนสมาชิกทุกคน

วิธีหาค่าร้อยละในช่อง C4 : C8 แล้วปัดเศษจุดทศนิยม 2 ตำแหน่ง และให้ผลรวมในช่อง C9 เป็น 100% เสมอ

เพราะบางครั้งเกิน100% บางครั้งไม่ถึง100% จะมีวิธีอย่างไรให้ถูกต้องครับ

ขอบคุณครับ

รวม.xlsx

[menem]

ปกติการคำนวณแบบนี้ (มีการใช้ Round ) ผลรวมจะเกิด Diff ครับ
ซึ่งโดยมาก เราอาจจะใช้วิธีปัดเศษเข้าไปในค่าใดค่าหนึ่งตายตัว
หรือใช้วิธีวางไว้ที่ตัวที่มีค่ามากที่สุดก็ได้

[Totem]

ปกติการคำนวณแบบนี้ (มีการใช้ Round ) ผลรวมจะเกิด Diff ครับ
ซึ่งโดยมาก เราอาจจะใช้วิธีปัดเศษเข้าไปในค่าใดค่าหนึ่งตายตัว
หรือใช้วิธีวางไว้ที่ตัวที่มีค่ามากที่สุดก็ได้

ถ้าช่อง C4:C8 ไม่มีใช้ Round ก็ได้ ให้รวมค่าเป็น 100 จะมีวิธีไหนครับ เพราะหากข้อมูลในช่องจำนวนมีค่ามากสุดเท่ากันก็จะให้สูตรลักษณะนี้ไม่ได้ เช่น ช่อง B5 = 61 รวมเท่ากับ 100.01 สูตรจะไม่ครอบคลุมปัญหานี้
หรือใช้วิธีปัดเศษเข้าไปในค่าใดค่าหนึ่งตายตัว หากมีค่าอื่นที่จำนวนเท่ากันกับค่าหนึ่งที่ปัดเศษตายตัว จะทำให้
ค่าร้อยละที่แสดงออกมาทั้ง 2 รายการไม่เท่ากันครับ

[menem]

ในกรณีที่ใช้สูตรแบบไม่มี Round มากำกับ
ผลรวมจะเป็น 100% ครับ แต่เวลาเราเอามาดู
แล้วรวมโดยใช้เครื่องคิดเลขข้างนอก อาจจะ
ไม่เท่ากับ 100% เพราะการแสดงผล กับค่าที่
Excel คำนวณได้จริงมันต่างกันอยู่ครับ

กรณี Fix การปัดเศษเข้าไปที่ตัวใดตัวหนึ่งโดยตรงสูตร
จะเป็น =100-sum( ผลการคำนวณของบรรทัดอื่น ๆ )
วิธีนี้จะดีในแง่ที่ว่าเรารู้เสมอว่าการปัดเศษจะกระทำกับ
รายการไหน (และไม่มีโอกาสปัดซ้ำ เหมือนกับวิธีการ
วางในค่า Max หากค่า Max มีมากกว่าหนึ่งรายการ)
แต่ข้อเสียที่อาจจะมีขึ้นคือ กรณีรายการที่ Fixed การปัดเศษ
มีค่าเป็น 0 มันก็จะไม่สมเหตุสมผลที่จะปัดเข้าไปในรายการนั้น ๆ ครับ

[Totem]

ในกรณีที่ใช้สูตรแบบไม่มี Round มากำกับ
ผลรวมจะเป็น 100% ครับ แต่เวลาเราเอามาดู
แล้วรวมโดยใช้เครื่องคิดเลขข้างนอก อาจจะ
ไม่เท่ากับ 100% เพราะการแสดงผล กับค่าที่
Excel คำนวณได้จริงมันต่างกันอยู่ครับ

กรณี Fix การปัดเศษเข้าไปที่ตัวใดตัวหนึ่งโดยตรงสูตร
จะเป็น =100-sum( ผลการคำนวณของบรรทัดอื่น ๆ )
วิธีนี้จะดีในแง่ที่ว่าเรารู้เสมอว่าการปัดเศษจะกระทำกับ
รายการไหน (และไม่มีโอกาสปัดซ้ำ เหมือนกับวิธีการ
วางในค่า Max หากค่า Max มีมากกว่าหนึ่งรายการ)
แต่ข้อเสียที่อาจจะมีขึ้นคือ กรณีรายการที่ Fixed การปัดเศษ
มีค่าเป็น 0 มันก็จะไม่สมเหตุสมผลที่จะปัดเข้าไปในรายการนั้น ๆ ครับ

ใช่ครับ ในกรณีที่ใช้สูตรแบบไม่มี Round มากำกับ
ผลรวมจะเป็น 100% แล้วกรณี Fix ก็ยังมีการแก้ไขปัญหายังไม่ครอบคลุม ผมลองทำหลายแบบแล้ว ยังไม่สำเร็จครับ
จะมีวิธีไหนหรือไม่...

[menem]

งั้นลองแบบนี้ดูครับ
- คำนวณหาว่ามี Diff เท่าไหร่
- ตรวจสอบการ Allocate ค่า Diff
- การวางจะวางครั้งเดียว กับค่าที่มากที่สุดของช่วง

[Totem]

งั้นลองแบบนี้ดูครับ
- คำนวณหาว่ามี Diff เท่าไหร่
- ตรวจสอบการ Allocate ค่า Diff
- การวางจะวางครั้งเดียว กับค่าที่มากที่สุดของช่วง

หากค่าที่มากที่สุดของช่วงนั้นเท่ากัน เช่น B4 = 61 , B5 = 61 ที่ช่อง C9 รวมได้ 100 แต่ ร้อยละในช่อง C4 = 44.21 ,
C5 = 44.20 เกิดปัญหาว่าไม่ตรงกันครับ

[XDoll]

C4:C8 ให้เอา Round ออกครับ
ส่วน C9 ให้เปลี่ยนเป็น =ROUND(SUMPRODUCT(C4:C8),2)

[Totem]

C4:C8 ให้เอา Round ออกครับ
ส่วน C9 ให้เปลี่ยนเป็น =ROUND(SUMPRODUCT(C4:C8),2)

หากเอา Round ออกเช่นนี้ C4:C8 ปรับให้เป็นจุดทศนิยม 2 ตำแหน่ง จะได้ 99.99 เมื่อใช้สูตร=ROUND(SUMPRODUCT(C4:C8),2)ทำให้ C9 = 100 แต่จำนวน C4:C8 จะยังไปถูกต้องครับ

[Totem]

C4:C8 ให้เอา Round ออกครับ
ส่วน C9 ให้เปลี่ยนเป็น =ROUND(SUMPRODUCT(C4:C8),2)

หากเอา Round ออกเช่นนี้ C4:C8 ปรับให้เป็นจุดทศนิยม 2 ตำแหน่ง จะได้ 99.99 เมื่อใช้สูตร=ROUND(SUMPRODUCT(C4:C8),2)ทำให้ C9 = 100 แต่จำนวน C4:C8 จะยังไม่ถูกต้องครับ

[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))

มันก็จะตรงกันครับ

[Totem]

ก่อนอื่น ขอถามก่อนว่าทศนิยมหลักที่ 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

ก็ยังไม่ตรงครับ

[DhitiBank]

ลองดูในไฟล์แนบครับ

[Totem]

ทศนิยม 2 ตำแหน่งจะมีความสำคัญ เมื่อมีจำนวนเท่ากันในช่อง B2:B8 ในตัวอย่างนี้ คำตอบ C2=15.38 และ C8=15.38
จะต้องเท่ากันครับ ถ้าว่าปัดเศษที่จำนวนไม่เท่ากัน ก็ไม่ถือว่าสำคัญ ปัดเศษได้เลย ให้ได้ รวมคือ 100 ครับ

[DhitiBank]

ทศนิยม 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]

ลองดูอีกทีครับ

[Totem]

ทศนิยม 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))

ลองดูอีกทีครับ

ลองสูตรทั้งสองแบบและได้ปรับอีกเล็กน้อย นำไปใช้ได้ตามต้องการครับ

ขอบคุณ คุณDhitiBankและคุณmenem ครับ