snasui.com ยินดีต้อนรับ
ยินดีต้อนรับสู่กระดานถามตอบ Excel and VBA และอื่น ๆ ที่เป็นมิตรกับทุกท่าน มีไฟล์แนบมหาศาล ช่วยให้ท่านค้นหาและติดตามศึกษาได้โดยง่าย สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ ระบุ Version ของ Excel
ฟอรัมถาม-ตอบปัญหาการใช้งาน MS Excel and VBA
Forum rules
ไม่อนุญาตให้ใช้ภาษาแชทในการถามและตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
ห้ามถามโดยระบุชื่อผู้ตอบและต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
กรุณาอธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
กรุณาแนบไฟล์ตัวอย่างพร้อมแสดงคำตอบที่ถูกต้องมาในไฟล์ด้วยเพื่อให้ง่ายต่อการทำความเข้าใจและสะดวกต่อการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่นที่อาจจะถูกลบทิ้งไปโดยต้นทางในภายหลัง นอกจากนี้ไม่ควรแนบไฟล์ที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน กรุณาโพสต์ Code ให้แสดงเป็น Code คือเปิดด้วย [code]
และปิดด้วย [/code]
ตัวอย่างเช่น [code]dim r as range[/code]
เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
กรุณาแจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#1
Post
by wisit » Sun Oct 03, 2021 4:13 am
รบกวนผู้รู้ครับ
พอดีผมต้องการสูตรตัดจำนวนให้เหลือตามต้องการ ถ้าเกินให้แสดงผล ซึ่งเราจะตัดจำนวนหลายครั้ง และการตัดจำนวนจะมีการกรอกข้อมูลเข้ามาเรื่อยๆ ซึ่งผมได้แนบไฟล์แสดงผลลัพธ์ที่ต้องการมาด้วยครับ
You do not have the required permissions to view the files attached to this post.
snasui
Site Admin
Posts: 30761 Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:
#2
Post
by snasui » Sun Oct 03, 2021 5:24 am
เซลล์ G3:G4 ได้ผลลัพธ์เป็น 5 และ 2 ตามลำดับ ช่วยแจกแจงมาว่ามีวิธีคิดอย่างไรจึงได้ค่าเท่านั้น จะได้เข้าใจตรงกันครับ
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#3
Post
by wisit » Sun Oct 03, 2021 12:09 pm
ครับอาจารย์ เซลล์ G3:G4 ได้ผลลัพธ์เป็น 5 และ 2 ตามลำดับ คือ ผลรวมที่กรอกข้อมูล แล้วเราตัดยอดในครั้งที่ 1 (ไฮไลท์สีเหลือง ตัดยอดจำนวน 20) ซึ่งรหัส 10 กับ 15 มีค่าเท่ากับ 25 และ 22 เมื่อเกินค่าที่ต้องตัด 20 ก็จะได้ผลลัพธ์เท่ากับ 5 และ 2 ตามลำดับครับ
ขอบคุณมากครับ
snasui
Site Admin
Posts: 30761 Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:
#4
Post
by snasui » Sun Oct 03, 2021 1:49 pm
ตัวอย่างสูตรตามด้านล่างครับ
ที่ 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 ลงด้านล่าง
ที่ H3 คีย์
=IF(G3<>"",SUMIFS($B$2:$B$6,$A$2:$A$6,$G3)-20,"")
Enter > Copy ลงด้านล่าง
ส่วนครั้งที่ 2 และ 3 ช่วยอธิบายมาใหม่ ดูโจทย์แล้วยังไม่เข้าใจครับ
You do not have the required permissions to view the files attached to this post.
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#5
Post
by wisit » Mon Oct 04, 2021 4:44 am
ผมอาจจะอธิบายผิด คือ ผมอยากได้สูตรที่สามารถตัดจำนวน (โดยเราจะตัดยอดตามข้อมูลที่กรอก ซึ่งจำนวน Row อาจมีจำนวนไม่แน่นอน แล้วแต่ข้อมูลที่กรอก)
ผมใช้สูตร SUMIF หาผลลัพธ์ แต่ติดปัญหาตรงที่เราจะต้องนำยอดตัดมาวางในชีทพักข้อมูลตามจำนวนที่ตัดซึ่งจะได้ผลลัพธ์ (แต่เราต้องสร้างชีทเยอะมาก ซึ่งรหัสมีจำนวนมาก และเราต้องตัดยอดอย่างน้อย 4 ครั้ง แต่ไม่เกิน 10 ครั้ง) ซึ่งอาจเกิดข้อผิดพลาดหรือลืมก๊อปปี้ไปวางในชีทพักข้อมูล
ซึ่งผมได้แนบไฟล์ตัวอย่างที่ทำ โดยตัดยอดเพียงแค่ 3 ครั้ง (การตัดยอดผมจะไฮไลท์สีในชีททั้งหมด 3 สี ตามครั้งที่ตัด)
You do not have the required permissions to view the files attached to this post.
snasui
Site Admin
Posts: 30761 Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:
#6
Post
by snasui » Mon Oct 04, 2021 7:28 pm
ระบบจะทราบได้อย่างไรว่าข้อมูลไหนเป็นครั้งที่ 1 ครั้งที่ 2 ครั้งที่ 3 หรือครั้งที่ใด ๆ ครับ
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#7
Post
by wisit » Mon Oct 04, 2021 9:32 pm
เราจะเป็นคนตัดยอดเองครับอาจารย์ ซึ่งข้อมูลที่เราตัดยอดไม่แน่นอน บางช่วงอาจมีจำนวนข้อมูลที่กรอก 500 เซลล์ หรืออาจมากกว่า 500เซลล์ครับ
ซึ่งการตัดครั้งที่ 1 เราต้องนำข้อมูลมาพักไว้ในชีทพักข้อมูลครั้งที่ 1 หากตัดครั้งที่ 2 ก็นำข้อมูลมาพักไว้ในชีทพักข้อมูลครั้งที่ 2 ทำวนไปเรื่อยๆ ตามจำนวนที่ตัด
โดยการตัดข้อมูลแต่ละครั้ง เราจะต้องนำข้อมูลที่ตัดแต่ละครั้ง มาวางไว้ในชีทพักข้อมูลตามจำนวนครั้งที่ตัด ถึงจะกรอกข้อมูลต่อไปได้ เช่น หากเราตัดครั้งที่ 1 เมื่อได้ค่า ก็ก๊อปปี้ค่าที่ได้มาวางในชีทพักข้อมูลครั้งที่ 1 ถึงจะกรอกข้อมูลต่อไป การตัดยอดทุกครั้งต้องทำเช่นนี้ไปเรื่อยๆ ครับ
ซึ่งสูตรที่ผมทำ ต้อง SUMIF ชีทพักข้อมูลตามจำนวนครั้งที่ตัดตลอด ซึ่งหากมีการตัด 7 ครั้ง ก็ต้องทำการ SUMIF ทั้ง 7 ชีท
ซึ่งผมอยากได้สูตรที่สามารถตัดยอด โดยเราไม่ต้องสร้างสูตรเพิ่ม เพียงแค่เพิ่มชีทพักข้อมูลตามจำนวนครั้ง ที่ตัดยอดเท่านั้น ไม่ต้องทำสูตรเพิ่มครับ
snasui
Site Admin
Posts: 30761 Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:
#8
Post
by snasui » Mon Oct 04, 2021 10:31 pm
ที่อธิบายมานั้นผมทราบอยู่แล้วว่าตัดยอดเองด้วยคนครับ
้ถ้าหากจะให้โปรแกรมคำนวณเองก็จะต้องกำหนดเป็นครั้งที่ ถ้าไม่เช่นนั้นก็ต้องเขียนเป็น VBA เมื่อคลิกปุ่มใด ๆ ก็ให้ทำการคำนวณค่าไปพักไว้ เช่นนี้เป็นต้น กรณีเป็น VBA จำเป็นต้องเขียนมาเองก่อน ติดตรงไหนค่อยถามกันต่อครับ
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#9
Post
by wisit » Tue Oct 05, 2021 7:28 pm
ตามที่อาจารย์แนะนำ ผมได้เพิ่มอีก 1 คอลัมน์ เพื่อใส่จำนวนครั้ง แต่ผมไม่รู้จะใช้สูตรอย่างไร
รบกวนอาจารย์และผู้รู้แนะนำหน่อยครับ
ผมได้แนบไฟล์ และผลลัพธ์มาใหม่ครับ
ขอบคุณมากครับ
You do not have the required permissions to view the files attached to this post.
snasui
Site Admin
Posts: 30761 Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:
#10
Post
by snasui » Tue Oct 05, 2021 8:06 pm
ตัวอย่างสูตรในชีต
กรอกข้อมูล ตามด้านล่างครับ
ที่ 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 ลงด้านล่าง
ที่ 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 ไปทางขวาและลงด้านล่าง
You do not have the required permissions to view the files attached to this post.
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#11
Post
by wisit » Tue Oct 05, 2021 11:07 pm
ขอบคุณมากครับอาจารย์ ได้ผลตามต้องการเลยครับ
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#12
Post
by wisit » Wed Oct 06, 2021 12:13 am
รบกวนอาจารย์ครับ
พอดีผมมาเช็คดูผลลัพธ์การตัดรอบครั้งที่ 3
็H2 ต้องได้ผลลัพธ์เท่ากับ 5 แต่สูตรแสดงผลลัพธ์เท่ากับ 10
็H7 ต้องได้ผลลัพธ์เท่ากับ 0 แต่สูตรแสดงผลลัพธ์เท่ากับ 2
ขอบคุณมากครับ
snasui
Site Admin
Posts: 30761 Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:
#13
Post
by snasui » Wed Oct 06, 2021 7:12 am
ปรับสูตรที่ 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 ไปด้านขวาและลงด้านล่าง
You do not have the required permissions to view the files attached to this post.
wisit
Bronze
Posts: 344 Joined: Sun Sep 25, 2011 9:42 pm
Excel Ver: 2019
#14
Post
by wisit » Wed Oct 06, 2021 10:47 am
ขอบคุณมากครับอาจารย์ ได้ผลตามที่ต้องการเลยครับ