วิธีหาผลรวมของแถวและดึงข้อมูลจากคอลัมล์มาไว้เป็นแถวในอีกหน้า

[NUNAM]

รบกวนอาจารย์และเพื่อนสมาชิกในบอร์ดด้วยนะค่ะ คือ
1.ต้องการนำข้อมูลที่อยู่ใน Sheet1 เป็นข้อมูลในรูปแบบของคอลัมล์ (H) ไปไว้ที่ แถว ใน Sheet2 โดยมีเงื่อนไขว่า ให้ลงตามวันที่เดียวกัน และ ชื่อเดียวกัน ถ้ามีช่องว่างให้เว้นว่างไว้ (อย่างเช่นวันนี้ไม่มาป่วยให้ลงว่า่ป่วยค่ะแต่ถ้าไม่มาไม่มีข้อมูลให้ลงว่าไม่มาค่ะ)
2. เมื่อนำมาวางในแถวของ Sheet2 ให้ รวมแถวที่มีตัวเลขที่เราดึงมาว่าได้เท่าไรค่ะ (ไม่เอาตัวเลขมารวมนะค่ะต้องการนับว่าคน1คนมากี่วันค่ะ)
รูปแบบตามเอกสารที่ได้แนบมาค่ะ

ขอบคุณค่ะ

[bank9597]

รบกวลอาจารย์และเพื่อนสมาชิกใบบอร์ดด้วยนะค่ะ คือ
1.ต้องการนำข้อมูลที่อยู่ใน Sheet1 เป็นข้อมูลในรูปแบบของคอลัมล์ (H) ไปไว้ที่ แถว ใน Sheet2 โดยมีเงื่อนไขว่า ให้ลงตามวันที่เดียวกัน และ ชื่อเดียวกัน ถ้ามีช่องว่างให้เว้นว่างไว้ (อย่างเช่นวันนี้ไม่มาป่วยให้ลงว่า่ป่วยค่ะแต่ถ้าไม่มาไม่มีข้อมูลให้ลงว่าไม่มาค่ะ)
2. เมื่อนำมาวางในแถวของ Sheet2 ให้ รวมแถวที่มีตัวเลขที่เราดึงมาว่าได้เท่าไรค่ะ (ไม่เอาตัวเลขมารวมนะค่ะต้องการนับว่าคน1คนมากี่วันค่ะ)
รูปแบบตามเอกสารที่ได้แนบมาค่ะ

ขอบคุณค่ะ

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

[NUNAM]

เช่นนะค่ะ
นาย ก ข้อมูลใน Sheet1 มีข้อมูลดังนี้ค่ะ
วันที่ เวลา สรุป
11/9/56 12.30 20.50 15.5 2.5
12/9/56 ไม่มา
13/9/56 12.30 20.50 15.5 1

นาย ก ข้อมูลใน Sheet2 มีข้อมูลที่จะต้องมาลงดังนี้ค่ะ
ชื่อ วันที่ 11 12 13 รวม
นาย ก 2.5 ไม่มา 1 2 วัน


ต้องดึงจากคอลัมล์ มาไว้เป็นแถวค่ะดังตัวหนังสือสีแดง และ หาผลรวม ดัง ตัวหนังสือสีชมพูค่ะ

และ อยากให้ล้ากคุมได้หมดเลยค่ะคือไม่ได้มีแค่คนเดียวค่ะเลยอยากให้คุมหมดเลยโดยที่ไม่ต้องนั้งทำทีละคนค่ะ


ขอบคุณค่ะ

[bank9597]

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

ที่ C2 คีย์ =DATE(2556,9,$D$1) คัดลอกลงมา

ที่ D2 คีย์

Code: Select all

=IF(ISNA(INDEX(Sheet1!$H$2:$H$51,MATCH(1,IF(Sheet1!$A$2:$A$51=$A2,IF((Sheet1!$C$2:$C$51+0)=DATE(YEAR($C2),MONTH($C2),D$1),1)),0))),"ไม่มา",INDEX(Sheet1!$H$2:$H$51,MATCH(1,IF(Sheet1!$A$2:$A$51=$A2,IF((Sheet1!$C$2:$C$51+0)=DATE(YEAR($C2),MONTH($C2),D$1),1)),0)))

กด Ctrl+Shift+Enter คัดลอกไปทางขวา แล้วลงล่างพร้อมกัน

ที่ S2 คีย์ =COUNT(D2:R2) คัดลอกลงมา

หากใช้ Excel 2007 ขึ้นไป สามารถปรับสูตรให้สั้นลงได้โดยการแทนสูตรเดิมเป็น

Code: Select all

=IFERROR(INDEX(Sheet1!$H$2:$H$51,MATCH(1,IF(Sheet1!$A$2:$A$51=$A2,IF((Sheet1!$C$2:$C$51+0)=DATE(YEAR($C2),MONTH($C2),D$1),1)),0)),"ไม่มา")

[NUNAM]

ขอบคุณมากค่ะ คุณ bank9597 ขอกลับไปทำก่อนนะค่ะแล้วจะรีบมาแจ้งผลทันทีเลยค่ะ ดีใจมากค่ะ

[bank9597]

หากใช้ Excel 2007 ขึ้นไป สามารถปรับสูตรให้สั้นลงได้โดยการแทนสูตรเดิมเป็น

Code: Select all

=IFERROR(INDEX(Sheet1!$H$2:$H$51,MATCH(1,IF(Sheet1!$A$2:$A$51=$A2,IF((Sheet1!$C$2:$C$51+0)=DATE(YEAR($C2),MONTH($C2),D$1),1)),0)),"ไม่มา")

[snasui]

เช่นนะค่ะ
นาย ก ข้อมูลใน Sheet1 มีข้อมูลดังนี้ค่ะ
วันที่ เวลา สรุป
11/9/56 12.30 20.50 15.5 2.5
12/9/56 ไม่มา
13/9/56 12.30 20.50 15.5 1

นาย ก ข้อมูลใน Sheet2 มีข้อมูลที่จะต้องมาลงดังนี้ค่ะ
ชื่อ วันที่ 11 12 13 รวม
นาย ก 2.5 ไม่มา 1 2 วัน


ต้องดึงจากคอลัมล์ มาไว้เป็นแถวค่ะดังตัวหนังสือสีแดง และ หาผลรวม ดัง ตัวหนังสือสีชมพูค่ะ

และ อยากให้ล้ากคุมได้หมดเลยค่ะคือไม่ได้มีแค่คนเดียวค่ะเลยอยากให้คุมหมดเลยโดยที่ไม่ต้องนั้งทำทีละคนค่ะ


ขอบคุณค่ะ

ควรทำตัวอย่างคำตอบมาในไฟล์จะได้เข้าใจตรงกันครับ

[NUNAM]

ขอบคุณสำหรับคำแนะนำนะค่ะ คุณ snasui เพิ่งเข้ามาบอร์ดนี้วันแรกก็ประทับใจแล้วค่ะ

[NUNAM]

รบกวลถามต่อเลยได้ไมค่ะ คือ ถ้านู้อยากนับเวลาด้วยค่ะ เช่น ถ้าเวลาเกิน 8.00-9.00 ให้เอานาทีมาบวกกันค่ะ ถ้าเกิน9.00 ให้หัก 0.5 แต่ถ้าไม่เกิน 9.00 ไม่ต้องหักแต่เอามาเวลาที่เกินมาบวกกันแทนค่ะ เช่น
วันที่11 8.02
วันที่12 8.05
วันที่13 8.00

ผลรวม คือ7นาทีค่ะ

และถ้าอยากให้ใสสีด้วย เช่น ถ้าเกิน 8.00 ให้ใส่สีแดงค่ะ

ขอบคุณค่ะ

[NUNAM]

นู๋ลองเอาสูตรที่ คุณ bank9597 นี้ไปวานทับ =IFERROR(INDEX(Sheet1!$H$2:$H$51,MATCH(1,IF(Sheet1!$A$2:$A$51=$A2,IF((Sheet1!$C$2:$C$51+0)=DATE(YEAR($C2),MONTH($C2),D$1),1)),0)),"ไม่มา")

ผลลับออกมาว่า
ไม่มา
คำเดียวทั้งหมดเลยค่ะ งงค่ะ

[bank9597]

นู๋ลองเอาสูตรที่ คุณ bank9597 นี้ไปวานทับ =IFERROR(INDEX(Sheet1!$H$2:$H$51,MATCH(1,IF(Sheet1!$A$2:$A$51=$A2,IF((Sheet1!$C$2:$C$51+0)=DATE(YEAR($C2),MONTH($C2),D$1),1)),0)),"ไม่มา")

ผลลับออกมาว่า
ไม่มา
คำเดียวทั้งหมดเลยค่ะ งงค่ะ

วางสูตรลงไปแล้ว กด Ctrl+Shift+Enter แทนการ Enter เพียงอย่างเดียว

ถ้านู้อยากนับเวลาด้วยค่ะ

แก้คำว่า "นู๋" เป็น "หนู"
แก้คำว่า "ผลลับ" เป็น "ผลลัพธ์"
แก้ "รบกวลถามต่อเลยได้ไม" แก้เป็น รบกวนถามต่อเลยได้ไหม

ส่วนที่ถามมาใหม่ ให้ลองยกตัวอย่างคำตอบมาดูว่า ให้แสดงค่าตรงไหน อย่างไร

[NUNAM]

นู๋ลองเอาสูตรที่ คุณ bank9597 นี้ไปวานทับ =IFERROR(INDEX(Sheet1!$H$2:$H$51,MATCH(1,IF(Sheet1!$A$2:$A$51=$A2,IF((Sheet1!$C$2:$C$51+0)=DATE(YEAR($C2),MONTH($C2),D$1),1)),0)),"ไม่มา")

ผลลับออกมาว่า
ไม่มา
คำเดียวทั้งหมดเลยค่ะ งงค่ะ

วางสูตรลงไปแล้ว กด Ctrl+Shift+Enter แทนการ Enter เพียงอย่างเดียว

ถ้านู้อยากนับเวลาด้วยค่ะ

แก้คำว่า "นู๋" เป็น "หนู"
แก้คำว่า "ผลลับ" เป็น "ผลลัพธ์"
แก้ "รบกวลถามต่อเลยได้ไม" แก้เป็น รบกวนถามต่อเลยได้ไหม

ส่วนที่ถามมาใหม่ ให้ลองยกตัวอย่างคำตอบมาดูว่า ให้แสดงค่าตรงไหน อย่างไร

ต้องขอโทษนะค่ะตกภาษาไทยค่ะ จะพยามไม่ให้ผิดอีกนะค่ะ

คือ 1.หนูเอาศูตรมาวางแล้วค่ะและได้ผลดังฃืด และถ้า ใน1 คนมีเดือนอื่นเพื่มขึ้นมามากกว่า 1 เดือนดัง Sheet1 ที่ได้ระบายสีเหลืองไว้ เช่น 30/09/2559 01/10/2556 จะเช็คอยางไงค่ะ
2.ถ้าอยากให้เช็คด้วยว่า ถ้าเวลา 8.00-9.00 ถ้าเกิน9.00 ให้ หัก 0.5 และ ถ้าอยู่ในช่วงเวลา ให้เอาส่วนนาทีมาบวกกัน เช่น 8.05 8.16 ผลที่ได้คือเอา 5+16 จะได้ 21 นาทีค่ะ
3. ถ้าเป็นเวลาที่เกินมาเช่น 8.15 ไม่เกิน 9.00 อยู่ในช่วง 8.00 ให้ช่องนั้นเป็นสีแดงค่ะ

หนูได้ทำและแนบมาด้วยแล้วค่ะ

ขอบคุณค่ะ

[bank9597]

ยังไม่เห็นไฟล์แนบครับ ลองแนบมาอีกครั้งครับ


ซีด แก้เป็น ชีท
ศูตร แก้เป็น สูตร

[NUNAM]

ยังไม่เห็นไฟล์แนบครับ ลองแนบมาอีกครั้งครับ


ซีด แก้เป็น ชีท
ศูตร แก้เป็น สูตร

ขอโทษค่ะแนบไม่ไปในรอบแรกค่ะ

ไฟล์มีขนาดใหญ่เกินไป, ขนาดใหญ่สุดคือ 300 KiB. เลยส่งไม่ไปค่ะ เลยต้องแนบแบบนี้แทนค่ะ

ขอบคุณค่ะ

[snasui]

ดูไฟล์แนบแล้วไม่เข้าใจครับ

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

[bank9597]

ถามเพิ่มครับ

1. คน 1 คน แต่มี 2 เดือน จะต้องแสดงคำตอบอย่างไรครับ

2. เวลาที่เกิน 8.00-9.00 จะให้ใส่ตรงไหน

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

[NUNAM]

ดูไฟล์แนบแล้วไม่เข้าใจครับ

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

ขอบคุณค่ะจะปรับปรุงนะค่ะ

[NUNAM]

ถามเพิ่มครับ

1. คน 1 คน แต่มี 2 เดือน จะต้องแสดงคำตอบอย่างไรครับ

2. เวลาที่เกิน 8.00-9.00 จะให้ใส่ตรงไหน

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

ให้แสดงคำตอบ เหมือนเดิมค่ะ คือ

วันที่ 29 30 1 2
1 คือ วันที่1 เดือน และ 2556 ค่ะ

หนูได้ปรับแก้ไฟล์ให้ชัดเจนตาที่ คุณsnasui และ bank9597 แล้วนะค่ะ

จากไฟล์แนบ sheet1 ต้องการ ให้ คิดเวลาว่า ใน 1 วัน 1 คน ช่วงเวลา 8.00-9.00 เกินไม ในแถวแรก ค่ะ
และนำมา ใส่ฃีด 2 ให้ เป็นสีแดง และ รวมว่า ใน 1 คน มีที่เป็นสีแดงกี่ วันค่ะ


หนูได้แนบไฟล์มาและรายละเอียดเพิ่มเติมมาแล้วนะค่ะ

ขอบคุณค่ะ

[bank9597]

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

ที่ H2 คีย์

Code: Select all

=IF(ISNA(INDEX(Sheet1!$D$2:$D$16,MATCH(1,IF($A2=Sheet1!$A$2:$A$16,IF(DATE(YEAR($C2),MONTH($C2),H$1)=Sheet1!$C$2:$C$16+0,1)),0))),"ไม่มา",INDEX(Sheet1!$D$2:$D$16,MATCH(1,IF($A2=Sheet1!$A$2:$A$16,IF(DATE(YEAR($C2),MONTH($C2),H$1)=Sheet1!$C$2:$C$16+0,1)),0)))

กด ctrl+shift+enter คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

ที่ N2 คีย์ =SUMPRODUCT(--($H2:$L2>"08:00"+0),--($H2:$L2<="09:00"+0)) คัดลอกลงมา
ที่ O2 คีย์ =SUMPRODUCT(--($H2:$L2>"08:00"+0),--($H2:$L2<="09:00"+0),$H2:$L2)-"08:00"*$N2 คัดลอกลงมา

ส่วนที่ระบุว่า เกิน 9.00 น. ให้หัก 0.5 นั้น ตรงนี้ไม่เข้าใจครับ ให้หักจากอะไร หรือ ให้คิดเป็น 0.5 ลองอธิบายมาเพิ่มเติมครับ

ส่วนการใส่สี ต้องทำ Condition formatting วิธีการดังนี้ (เวอร์ชั่น 2007-2010)

คลุมทึบที่ H2:L5 แล้วไปที่เมนู Condition formatting เลือก Highlight Cells Rules เลือก Between จากนั้นให้ใส่ค่าตั้งแต่ 08:00 ถึง 09:00 แล้วเลือกสีเซลล์ที่ต้องการ กด OK

[NUNAM]

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

ที่ H2 คีย์

Code: Select all

=IF(ISNA(INDEX(Sheet1!$D$2:$D$16,MATCH(1,IF($A2=Sheet1!$A$2:$A$16,IF(DATE(YEAR($C2),MONTH($C2),H$1)=Sheet1!$C$2:$C$16+0,1)),0))),"ไม่มา",INDEX(Sheet1!$D$2:$D$16,MATCH(1,IF($A2=Sheet1!$A$2:$A$16,IF(DATE(YEAR($C2),MONTH($C2),H$1)=Sheet1!$C$2:$C$16+0,1)),0)))

กด ctrl+shift+enter คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

ที่ N2 คีย์ =SUMPRODUCT(--($H2:$L2>"08:00"+0),--($H2:$L2<="09:00"+0)) คัดลอกลงมา
ที่ O2 คีย์ =SUMPRODUCT(--($H2:$L2>"08:00"+0),--($H2:$L2<="09:00"+0),$H2:$L2)-"08:00"*$N2 คัดลอกลงมา

ส่วนที่ระบุว่า เกิน 9.00 น. ให้หัก 0.5 นั้น ตรงนี้ไม่เข้าใจครับ ให้หักจากอะไร หรือ ให้คิดเป็น 0.5 ลองอธิบายมาเพิ่มเติมครับ

ส่วนการใส่สี ต้องทำ Condition formatting วิธีการดังนี้ (เวอร์ชั่น 2007-2010)

คลุมทึบที่ H2:L5 แล้วไปที่เมนู Condition formatting เลือก Highlight Cells Rules เลือก Between จากนั้นให้ใส่ค่าตั้งแต่ 08:00 ถึง 09:00 แล้วเลือกสีเซลล์ที่ต้องการ กด OK

ขอบคุณมากๆๆค่ะหนูของลองทำก่อนนะค่ะ แล้วจะรีบมาแจ้งผลนะค่ะ แล้วที่เป็นวันที่ ให้ดึงมาวางเหมือนดึงวันที่ 26 มาวาง ละค่ะ คนละเดือนเลยไม่มาให้