ตัดยอดหลายครั้งให้เหลือตามจำนวนที่ต้องการ ถ้าเกินให้แสดงผล

[wisit]

รบกวนผู้รู้ครับ
พอดีผมต้องการสูตรตัดจำนวนให้เหลือตามต้องการ ถ้าเกินให้แสดงผล ซึ่งเราจะตัดจำนวนหลายครั้ง และการตัดจำนวนจะมีการกรอกข้อมูลเข้ามาเรื่อยๆ ซึ่งผมได้แนบไฟล์แสดงผลลัพธ์ที่ต้องการมาด้วยครับ

[snasui]

เซลล์ G3:G4 ได้ผลลัพธ์เป็น 5 และ 2 ตามลำดับ ช่วยแจกแจงมาว่ามีวิธีคิดอย่างไรจึงได้ค่าเท่านั้น จะได้เข้าใจตรงกันครับ

[wisit]

ครับอาจารย์ เซลล์ G3:G4 ได้ผลลัพธ์เป็น 5 และ 2 ตามลำดับ คือ ผลรวมที่กรอกข้อมูล แล้วเราตัดยอดในครั้งที่ 1 (ไฮไลท์สีเหลือง ตัดยอดจำนวน 20) ซึ่งรหัส 10 กับ 15 มีค่าเท่ากับ 25 และ 22 เมื่อเกินค่าที่ต้องตัด 20 ก็จะได้ผลลัพธ์เท่ากับ 5 และ 2 ตามลำดับครับ
ขอบคุณมากครับ

[snasui]

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ G3 คีย์
   =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$6)/((0<FREQUENCY(MATCH($A$2:$A$6,$A$2:$A$6,0),ROW($A$2:$A$6)-ROW($A$2)+1))*(SUMIFS($B$2:$B$6,$A$2:$A$6,$A$2:$A$6)>20)),ROWS(G$3:G3))),"")
   Enter > Copy ลงด้านล่าง
2. ที่ H3 คีย์
   =IF(G3<>"",SUMIFS($B$2:$B$6,$A$2:$A$6,$G3)-20,"")
   Enter > Copy ลงด้านล่าง

ส่วนครั้งที่ 2 และ 3 ช่วยอธิบายมาใหม่ ดูโจทย์แล้วยังไม่เข้าใจครับ

[wisit]

ผมอาจจะอธิบายผิด คือ ผมอยากได้สูตรที่สามารถตัดจำนวน (โดยเราจะตัดยอดตามข้อมูลที่กรอก ซึ่งจำนวน Row อาจมีจำนวนไม่แน่นอน แล้วแต่ข้อมูลที่กรอก)
ผมใช้สูตร SUMIF หาผลลัพธ์ แต่ติดปัญหาตรงที่เราจะต้องนำยอดตัดมาวางในชีทพักข้อมูลตามจำนวนที่ตัดซึ่งจะได้ผลลัพธ์ (แต่เราต้องสร้างชีทเยอะมาก ซึ่งรหัสมีจำนวนมาก และเราต้องตัดยอดอย่างน้อย 4 ครั้ง แต่ไม่เกิน 10 ครั้ง) ซึ่งอาจเกิดข้อผิดพลาดหรือลืมก๊อปปี้ไปวางในชีทพักข้อมูล
ซึ่งผมได้แนบไฟล์ตัวอย่างที่ทำ โดยตัดยอดเพียงแค่ 3 ครั้ง (การตัดยอดผมจะไฮไลท์สีในชีททั้งหมด 3 สี ตามครั้งที่ตัด)

[snasui]

ระบบจะทราบได้อย่างไรว่าข้อมูลไหนเป็นครั้งที่ 1 ครั้งที่ 2 ครั้งที่ 3 หรือครั้งที่ใด ๆ ครับ

[wisit]

เราจะเป็นคนตัดยอดเองครับอาจารย์ ซึ่งข้อมูลที่เราตัดยอดไม่แน่นอน บางช่วงอาจมีจำนวนข้อมูลที่กรอก 500 เซลล์ หรืออาจมากกว่า 500เซลล์ครับ
ซึ่งการตัดครั้งที่ 1 เราต้องนำข้อมูลมาพักไว้ในชีทพักข้อมูลครั้งที่ 1 หากตัดครั้งที่ 2 ก็นำข้อมูลมาพักไว้ในชีทพักข้อมูลครั้งที่ 2 ทำวนไปเรื่อยๆ ตามจำนวนที่ตัด
โดยการตัดข้อมูลแต่ละครั้ง เราจะต้องนำข้อมูลที่ตัดแต่ละครั้ง มาวางไว้ในชีทพักข้อมูลตามจำนวนครั้งที่ตัด ถึงจะกรอกข้อมูลต่อไปได้ เช่น หากเราตัดครั้งที่ 1 เมื่อได้ค่า ก็ก๊อปปี้ค่าที่ได้มาวางในชีทพักข้อมูลครั้งที่ 1 ถึงจะกรอกข้อมูลต่อไป การตัดยอดทุกครั้งต้องทำเช่นนี้ไปเรื่อยๆ ครับ
ซึ่งสูตรที่ผมทำ ต้อง SUMIF ชีทพักข้อมูลตามจำนวนครั้งที่ตัดตลอด ซึ่งหากมีการตัด 7 ครั้ง ก็ต้องทำการ SUMIF ทั้ง 7 ชีท
ซึ่งผมอยากได้สูตรที่สามารถตัดยอด โดยเราไม่ต้องสร้างสูตรเพิ่ม เพียงแค่เพิ่มชีทพักข้อมูลตามจำนวนครั้ง ที่ตัดยอดเท่านั้น ไม่ต้องทำสูตรเพิ่มครับ

[snasui]

ที่อธิบายมานั้นผมทราบอยู่แล้วว่าตัดยอดเองด้วยคนครับ

้ถ้าหากจะให้โปรแกรมคำนวณเองก็จะต้องกำหนดเป็นครั้งที่ ถ้าไม่เช่นนั้นก็ต้องเขียนเป็น VBA เมื่อคลิกปุ่มใด ๆ ก็ให้ทำการคำนวณค่าไปพักไว้ เช่นนี้เป็นต้น กรณีเป็น VBA จำเป็นต้องเขียนมาเองก่อน ติดตรงไหนค่อยถามกันต่อครับ

[wisit]

ตามที่อาจารย์แนะนำ ผมได้เพิ่มอีก 1 คอลัมน์ เพื่อใส่จำนวนครั้ง แต่ผมไม่รู้จะใช้สูตรอย่างไร
รบกวนอาจารย์และผู้รู้แนะนำหน่อยครับ
ผมได้แนบไฟล์ และผลลัพธ์มาใหม่ครับ
ขอบคุณมากครับ

[snasui]

ตัวอย่างสูตรในชีตกรอกข้อมูลตามด้านล่างครับ

1. ที่ E2 คีย์
   =IFERROR(AGGREGATE(15,6,$A$2:$A$20/(($A$2:$A$20<>"")*(0<FREQUENCY(MATCH("~"&$A$2:$A$20,$A$2:$A$20&"",0),ROW($A$2:$A$20)-ROW($A$2)+1))),ROWS(E$2:E2)),"")
   Enter > Copy ลงด้านล่าง
2. ที่ F2 คีย์
   =IF(F$1>MAX($C$2:$C$20),"",IF($E2="","",MAX(0,SUMIFS($B$2:$B$20,$A$2:$A$20,$E2,$C$2:$C$20,"<="&F$1)-20-((F$1>1)*E2))))
   Enter > Copy ไปทางขวาและลงด้านล่าง

[wisit]

ขอบคุณมากครับอาจารย์ ได้ผลตามต้องการเลยครับ

[wisit]

รบกวนอาจารย์ครับ
พอดีผมมาเช็คดูผลลัพธ์การตัดรอบครั้งที่ 3
็H2 ต้องได้ผลลัพธ์เท่ากับ 5 แต่สูตรแสดงผลลัพธ์เท่ากับ 10
็H7 ต้องได้ผลลัพธ์เท่ากับ 0 แต่สูตรแสดงผลลัพธ์เท่ากับ 2
ขอบคุณมากครับ

[snasui]

ปรับสูตรที่ F2 เป็นตามด้านล่างครับ

=IF(F$1>MAX($C$2:$C$20),"",IF($E2="","",MAX(0,SUMIFS($B$2:$B$20,$A$2:$A$20,$E2,$C$2:$C$20,"<="&F$1)-SUM($E2:E2)+$E2-20)))

Enter > Copy ไปด้านขวาและลงด้านล่าง

[wisit]

ขอบคุณมากครับอาจารย์ ได้ผลตามที่ต้องการเลยครับ