Excel Forum

*รบกวนหน่อยนะครับกำหนดไห้แสดงรายชื่อผู้ที่ถูกปรับ อัตโนมัติอย่างไร และไม่แสดงรายชื่อเมื่อชำระเงินแล้ว อย่างไร ครับท่านอาจารย์

ช่วยแจ้งรายละเอียดด้วยครับว่าต้องการคำตอบในชีทไหน เซลล์ไหน ตัวอย่างคำตอบที่ถูกต้องคืออะไร จะได้เข้าถึงข้อมูลโดยไวและเข้าใจตรงกันครับ

ให้แสดงรายชื่อตั้งแต่เ ซลล์ B128 ลงไป
และจำนวนเงินที่ถูกปรับ ตั้งแต่เ ซลล์ D128 ลงไป

snasui wrote: ช่วยแจ้งรายละเอียดด้วยครับว่าต้องการคำตอบในชีทไหน เซลล์ไหน ตัวอย่างคำตอบที่ถูกต้องคืออะไร จะได้เข้าถึงข้อมูลโดยไวและเข้าใจตรงกันครับ

ช่วยแนบไฟล์มาใหม่พร้อมตัวอย่างคำตอบที่ต้องการมาด้วยครับ

ผมลองทำตัวอย่างมาให้ดูครับว่าใช่ที่ต้องการไหม
1.ทำการลบแถวที่เป็นหัวคอลัมภ์ออกให้หมดให้เหลือเพียงด้านบนแถวเดียวแล้วเรียงข้อมูลต่อกันลงมาด้านล่าง
2.ที่ D106 ใส่สูตรสำหรับแสดงจำนวนของบุคคลที่ค้างชำระว่ามีจำนวนเท่าไร
=SUMPRODUCT(--(H3:H68<>""))
3. ที่ A107 ใส่สูตรเพื่อแสดงลำดับที่ของบุคคลที่ค้นพบ
=IF(ROWS(A107:A$107)<=$A$106,ROWS(A107:A$107),"") Enter >Copy ลงล่าง
4. ที่ B107 ใส่สูตรเพื่อแสดงรายชื่อบุคคลที่ถูกปรับตามจำนวนที่ค้นพบ
=IF(N($D107),INDEX(B$3:B$68,SMALL(IF(INDEX($H$3:$H$68,0,MATCH($D$106,$H$2,0))=$D107,ROW($B$3:$B$68)-ROW($B$3)+1),COUNTIF($D$107:$D107,$D107))),"")
กด Ctrl+Shift+Enter พร้อมกัน Copy ลงล่าง
5. ที่ D106 คีย์ หัวข้อ “ ปรับ”
6.ที่ D107 คีย์สูตรเพื่อแสดงจำนวนเงินที่ถูกปรับ
=IF(ROWS(D$107:D107)>$A$106,"",LARGE(INDEX($H$3:$H$68,0,MATCH($D$106,$H$2,0)),ROWS(D$107:D107))) Enter >Copy ลงล่าง

ขอบคุณมากๆครับ คุณJoo ตอบปัญหาตรงประเด็นผมเลย

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

สูตรเดิมที่ให้ไปสามารถนำมาปรับใช้ให้ตรงเงื่อนไขที่ต้องการได้ครับ เช่น จากสูตรนี้ =SUMPRODUCT(--(H3:H68<>"")) ก็เพิ่มเงื่อนไขไม่ให้นับช่วงข้อมูลที่มีคำว่า "ปรับ" สูตรก็จะเป็น =SUMPRODUCT(--(H3:H89<>""),--(H3:H89<>"ปรับ")) นอกนั้นก็เหมือนเดิมเพียงแต่ปรับ Rangeให้ตรงค่าที่แสดงออกมาก็จะได้ตามที่ต้องการ สังเกตุการอ้างอิงในสูตร ลองทำตามที่แนะนำดูก่อนครับ หากได้ค่าที่ไม่ถูกต้องสามารถแนบไฟล์ตัวอย่างมาถามกันได้ครับ

อีกนิดครับท่าน joo สงสัยผมจะใส่ค่าไม่ถูกต้อง กรุณาต่อยอดหน่อยนะครับ

ลองตามนี้ครับ

1. ที่ A18 คีย์สูตร
   =SUMPRODUCT(--(ISNUMBER(H3:H89)))
   Enter
2. ที่ B129 คีย์สูตร
   =IF(N($A129),INDEX(B$3:B$89,SMALL(IF(ISNUMBER($H$3:$H$89),ROW($B$3:$B$89)-ROW($B$3)+1),ROWS(B$129:B129))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. ที่ C129 คีย์สูตร
   =IF(N($A129),INDEX(H$3:H$89,SMALL(IF(ISNUMBER($H$3:$H$89),ROW($B$3:$B$89)-ROW($B$3)+1),ROWS(B$129:B129))),"")
   Ctrl+Shift+Enter

Note: Ctrl+Shift+Enter หมายถึงกดแป้นให้รับสูตรด้วยการกดแป้น Ctrl+Shift ค้างไว้แล้วตามด้วย Enter เนื่องจากเป็นสูตร Array หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร เช่น {=Formula(...)} หากยังไม่เห็นเครื่องหมายปีกกาครอบสูตรแสดงว่ากดแป้นไม่ถูกต้องครับ

ขอบคุณมากครับ ทำได้แล้วเสร็จทันสินเดือนพอดี เย้....

เรียนถามท่านอาจารย์เพิ่มเติมครับ

การใส่สูตรให้โชว์ เครื่องหมาย ขีด(-) แทนในช่องว่าง
ตั้งแต่ D ไปจนถึง คอลัมน์ J นะครับ

การใส่สูตร ใส่ฟังก์ชั่น ควรเป็นเรื่องที่เกี่ยวกับการคำนวณ กรณีที่ถามมานี้ไม่ได้เกี่ยวกับกการคำนวณใด ๆ เพียงแต่ต้องการเติมเซลล์ว่างให้เห็นเป็นเครื่องหมาย - เท่านั้น สามารถทำได้ดังนี้ครับ

1. คลุมพื้นที่ที่ต้องการจะปรับเซลล์ว่างให้เห็นเป็นเครื่องหมาย -
2. กดแป้น F5 > Special > Blanks > OK
3. คีย์เลข 0 ลงในเซลล์ใด ๆ ที่ถูกเลือก > กดแป้น Ctrl+Enter จะเป็นการเติม 0 พร้อม ๆ กันทุกเซลล์ที่เป็นเซลล์ว่าง
4. คลุมพื้นที่ที่ต้องการใหม่อีกครั้งเพื่อจัดรูปแบบ จากนั้นทำตามด้านล่าง
   1. คลิกขวาในเซลล์ใด ๆ ที่ถูกคลุมแล้วเลือก Format Cells...
   2. ที่แถบ Number เลือก Custom > ที่ช่อง Type: คีย์ #,##0;-#,##0;- แล้วคลิก OK

ขอบคุณครับท่านอาจารย์
ติดอยู่นิดหนึ่งครับ ตรงคอลัมน์ ปรับ ใส่ค่า (-) แทน 0 แต่0มาแสดงตรงรายชื่อปรับอัตโนมัติจะแก้ไขปรับค่าขึ้นตรงรายชื่อผู้ถูกปรับอัตโนมัตอย่างไร

อ่านแล้วไม่เข้าใจครับ ลองอธิบายมาใหม่ครับ

แนบไฟล์ให้ดูเลยครับ
คือ ตรงรายชื่อผู้ถูกปรับอัตโนมัติแสดงผู้ถูกปรับจำนวนเงิน 0 บาทอยู่ ผมต้องการให้แสดงอัตโนมัติตั้งแต่ 1 บาทขึ้นไบ

ลองตามนี้ครับ

1. ที่ A117 ปรับสูตรเป็น
   =SUMPRODUCT(--ISNUMBER(J3:J89),--(J3:J89>0))
   Enter
2. ที่เซลล์ D118 ปรับสูตรเป็น
   =IF(N($A118),INDEX(J$3:J$89,SMALL(IF(ISNUMBER($J$3:$J$89),IF($J$3:$J$89>0,ROW($B$3:$B$89)-ROW($B$3)+1)),ROWS(B$118:B118))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

ขอบคุณครับ
ไม่ให้แสดงชื่อคนที่ (-)หรือไม่ถูกปรับได้แล้วครับ (j)
แต่ กลับ B 118 ลงไปรายชื่อผู้ถูกปรับอัตโนมัติแสดงไม่ถูกต้องครับ...


เกี่ยวไหมครับ ตรง คลัมน์ J ตั้งแต่ j3 ถึง j88 ผม format cell
ทำ 0 ให้เป็น - กำหนด _-* #,###0_-;-*0_-;_-*"-"??_-;_-@_- แทน #,##0;-#,##0;-

แนบไฟล์อีกทีครับ

komd4 wrote: เกี่ยวไหมครับ ตรง คลัมน์ J ตั้งแต่ j3 ถึง j88 ผม format cell
ทำ 0 ให้เป็น - กำหนด _-* #,###0_-;-*0_-;_-*"-"??_-;_-@_- แทน #,##0;-#,##0;-

ไม่เกี่ยวครับ

ที่ B118 ปรับสูตรเป็น

=IF(N($A118),INDEX(B$3:B$89,SMALL(IF(ISNUMBER($J$3:$J$89),IF($J$3:$J$89>0,ROW($B$3:$B$89)-ROW($B$3)+1)),ROWS(B$118:B118))),"")

Ctrl+Shift+Enter > ลองสังเกตที่ผมระบายสีแดงไว้ว่าต่างกันอย่างไรกับสูตรก่อนหน้าครับ

ขอบมากๆคุณครับ สมประสงค์เลยละครับ..