การนับเวลา การเข้า-ออก งาน

[liveday]

เรียนอาจารย์

รบกวนสูตรการนับเวลาการเข้างาน(สาย) ออกงาน(ก่อนเวลา)ด้วยครับ

ทั้งนี้ขอให้พร อันประเสริฐ ในโลกนี้ ส่งผลดี ให้แก่ท่าน ในวันหน้า คิดสิ่งใด หวังสิ่งใด จงได้มา สมปรารถนา
ดั่งใจหวัง ยั่งยืนนาน ในวาระดิถีขึ้นปีใหม่ ขออวยชัยให้ท่านจงสุขสันต์ สุขเกษมเปรมปรีดิ์ทุกคืนวัน หฤหรรปรีดากันทุกคน

ขอมอบให้อาจารย์ Snasui, อาจารย์ทุกท่าน และทุกคน ที่อยู่ในบอร์ดนี้ด้วยครับ

ปล.คำอวยพรอาจจะช้าไปนิดนะครับ ^_^

[snasui]

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

ที่เซลล์ AF22 คีย์สูตร

=COUNTIFS($D$5:$BM$5,"เข้า",INDEX($D$6:$BM$17,MATCH(AF$21,$AF$21:$AQ$21,0),0),">10:30")

Enter > Copy ไปทางขวา

สำหรับ F23, F24 ลองปรับสูตรเฉพาะช่วงที่ผมระบายสีไว้ดู ติดตรงไหนแล้วค่อยถามกันต่อครับ

[liveday]

เรียนอาจารย์

ช่วงเวลาเข้างานได้ตามต้องการแล้วครับ ตอนนี้เหลือช่วงเวลาออกงานครับ ซึ่งจะแบ่งเป็น 2 ช่วงเวลาครับ วันจันทร์-พฤหัสบดี เวลาเลิกงานคือ 19:30 ถ้าออกงานก่อน 19:15 - 19:30, และต่ำกว่า 19:15 ลงไป ส่วนอีกช่วงเวลาเลิกงาน วันศุกร์ - อาทิตย์ เวลาเลิกงานคือ 20:00 ถ้าออกงานก่อน 19:45 - 20:00, และต่ำกว่า 19:45 ลงไป ขอคำแนะนำอาจาร์ยด้วยครับ

[snasui]

ข้อมูลการทำงานควรทำมาให้เป็น Database ไม่ใช่ทำเป็นรายงาน การนำรายงานมาสรุปข้อมูลจะยุ่งยากลำบาก

เช่น วันที่เดียวแยกเป็น 2 คอลัมน์ คือ เข้า และ ออก หากต้องการให้ง่ายขึ้นวันที่จะต้องใส่ทั้งสองคอลัมน์ ไม่ใช่ใส่คอลัมน์เดียวแล้วใช้ร่วมกัน

ลองแนบไฟล์ที่เติมวันที่ให้เต็มแล้วค่อย ๆ ถามตอบกันไปครับ

[liveday]

อาจารย์ดูฟอร์มอีกทีนึงครับ พอดีลองใช้สูตรอาจารย์มาปรับ ยังใช้ไม่ได้กับฟอร์มนี้ รบกวนอาจาร์ยอีกครั้งครับ

[snasui]

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

1. เซลล์ R36 คีย์ 2014 (ปีที่แสดงรายงาน)
2. เซลล์ R37:AC37 คีย์ มกราคม-ธันวาคม
3. เซลล์ R39 ตัวอย่างสูตรการเข้าสายหลังเวลา 10:30
   =COUNTIFS(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0),0),">10:30")
   Enter > Copy ไปด้านขวา
4. เซลล์ R41 ตัวอย่างการกลับก่อนเวลา 19:15 วันจันทร์ถึงพฤหัสฯ
   =COUNT(IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<"19:15"+0,IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<>"",IF(WEEKDAY($D$5:$AH$5&R37&$R$36,2)>=1,IF(WEEKDAY($D$5:$AH$5&R37&$R$36,2)<5,1)))))
   Ctrl+Shift+Enter > Copy ไปด้านขวา
5. เซลล์ R4- ตัวอย่างการกลับก่อนเวลา 19:45 วันศุกร์ถึงอาทิตย์
   =COUNT(IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<"19:45"+0,IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<>"",IF(WEEKDAY($D$5:$AH$5&R37&$R$36,2)>4,1))))
   Ctrl+Shift+Enter > Copy ไปด้านขวา

[liveday]

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

[liveday]

อาจาร์ยครับสูตรนี้คือถ้ามาเกินเวลา 10:30 เท่ากับ 1 *=COUNTIFS(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0),0),">10:30")
ถ้าต้องการปรับสูตรว่าถ้ามาเกินเวลา 10:30 - 10:45 เท่ากับ 1 ต้องแก้ไขสูตรหรือเพิ่มสูตรอย่างไรดีครับ
ขอบคุณครับ

[snasui]

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

=COUNTIFS(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0),0),">10:30",INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0),0),"<10:45")

สำหรับเครื่องหมาย >, >=, <, <= จะต้องทราบว่ามีความหมายอย่างไร เพื่อจะได้ปรับให้ได้ค่าตามที่ต้องการได้ด้วยตัวเอง

[liveday]

เรียน อาจารย์

ช่วงเวลาออกยังติดอยู่นิดนึงครับ เนื่องจากสูตรยังคำนวนวันซ้ำกัน ระหว่างช่วงเวลา 19:15 ลงมา กับช่วงเวลา 19:15 - 19:30 ทำให้ไม่สามารถคำนวนหักเงินเป็นวันออกมาได้ครับ
ขอบคุณครับ

[snasui]

ลองปรับมาก่อนครับ

ตัวอย่างที่ให้ไปไม่มีการหาจากช่วงเวลา ลองประยุกต์เองดูว่าถ้าหาจากช่วงเวลาจะเพิ่มเงื่อนไขเข้าไปอย่างไร

[liveday]

ลองปรับมาก่อนครับ

ตัวอย่างที่ให้ไปไม่มีการหาจากช่วงเวลา ลองประยุกต์เองดูว่าถ้าหาจากช่วงเวลาจะเพิ่มเงื่อนไขเข้าไปอย่างไร

ผมลองปรับตามนี้แต่ผลลัพท์ไม่ขึ้นครับ
{=COUNT(IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<"19:30"+0,IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0),">19:15",IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<>"",IF(WEEKDAY($D$5:$AH$5&R37&$R$36,2)>=1,IF(WEEKDAY($D$5:$AH$5&R37&$R$36,2)<5,1))))))}
เงื่อนไขคือ กลับก่อนเวลาน้อยกว่า 19:30 และมากกว่า 19:15 ในวัน จันทร์ - พฤหัสบดี ให้มีค่าเท่ากับ 1 แต่สูตรยังเเสดงไม่ตรงเงื่อนไขครับ

[snasui]

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

=COUNT(IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<"19:30"+0,IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)>"19:15"+0,IF(INDEX($D$6:$AH$29,MATCH(R$37,$B$6:$B$29,0)+1,0)<>"",IF(WEEKDAY($D$5:$AH$5&R$37&$R$36,2)>=1,IF(WEEKDAY($D$5:$AH$5&R$37&$R$36,2)<5,1))))))

[liveday]

ขอบคุณอาจาร์ยมากครับ ได้ตามต้องการแล้วครับ ตอนนี้กำลังลอง Lookup อยู่ ถ้าติดตรงไหนจะรบกวนถามอาจาร์ยเป็นข้อๆไปครับ แต่จะลองทำด้วยตัวเองก่อนครับ ถึงตอนนี้ถึงจะไม่เข้าใจถึงเเก่นแท้ แต่ก็พอจะอ่านความหมายแต่ละสูตรได้ขึ้นมาบ้างจากคนไม่เคยเรียน Excel มาเลยและไม่จบ ม.3 ด้วย เพราะความขนขวายถึงแม้จะโดนบางบอร์ดว่าพื้นฐานไม่มีก็ไม่ควรทำ จนมาเจออาจาร์ย คนควน ในที่แห่งนี้ Snasui.com ต้องขอบคุณอาจาร์ยมากๆครับ ที่ไม่เหนื่อยตอบหรือรำคาญกับคำถามที่มาจากคำคนที่ไม่ค่อยรู้ Excel มาก่อน อาจาร์ยก็สามารถประติดประต่อทำให้งานทุกอย่างออกมาได้ตามต้องการทุกครั้ง ต้องขอขอบคุณอาจาร์ยอีกครั้งครับ ขอบคุณครับ

[liveday]

เรียนอาจารย์
ผมทำ Vlookup ได้ช่องเดียวครับ ที่ช่อง D6 กับ D7 จะลากมาทางขวาแต่ไม่ได้ ไม่ทราบว่าต้องเพิ่มสูตรเพิ่มเติมอย่างไรบ้างครับ
ขอบคุณครับ

[snasui]

ช่วยอธิบายว่าต้องการจะทำอะไร นำค่าใดจากไหนมาใช้ ด้วยเงื่อนไขใด จะได้เข้าใจตรงกันครับ

[liveday]

เรียนอาจาร์ย
ผมปรับฟอร์มใหม่แล้วครับ ต้องการดึงข้อมูลแต่ละเดือน ที่ Sheet เดือน มาแสดงที่ Sheet SCDC ครับ โดยที่สามารถ ใส่รหัสพนักงานแล้วนำเวลามาแสดงแต่ละคนครับ
ขอบคุณครับ

[snasui]

ข้อมูลทุกเดือนทำให้อยุ่ในชีทเดียวกัน สามารถเพิ่มคอลัมน์เดือนเข้ามาเพื่อระบุว่าข้อมูลใดเป็นของเดือนใด

จากไฟล์ที่แนบมาเซลล์ D6 คีย์

Code: Select all

=IFERROR(INDEX(INDEX(มกราคม!$E$2:$F$49,0,MATCH($C6,{"เข้า","ออก"},0)),SMALL(IF(มกราคม!$B$2:$B$49=$D$2,IF(TEXT(มกราคม!$C$2:$C$49,"mmmm")=LOOKUP(CHAR(255),$B$6:$B6),IF(DAY(มกราคม!$C$2:$C$49)=D$5,ROW(มกราคม!$C$2:$C$49)-ROW(มกราคม!$C$2)+1))),1)),"")

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

[liveday]

อาจาร์ยครับ
เท่าที่ลองสูตร ผมคีย์ข้อมูลที่ช่อง AN2 แล้วไม่ได้ครับ ต้องปรับอย่างไรครับ

[snasui]

ควรแนบไฟล์ทีเป็นปัญหามาด้วยและชี้ให้เห็นว่าคำตอบต้องเป็นเท่าใด จะได้ช่วยดูให้ได้ครับ