Snasui Excel Forum

ข้อมูลที่มี
1 ตารางเวร
2 เวลา Shift
3 ข้อมูลการสแกนนิ้วจากเครื่องสแกนนิ้ว ซึ่งบางครั้งพนักงานสแกนนิ้วหลายครั้งในรอบเดียว ทำให้มีข้อมูลขาเข้า หรือขาออกซ้ำๆ หลายบรรทัด แต่ต้องการข้อมูลขาเข้าบรรทัดแรก และข้อมูลขาออกบรรทัดสุดท้าย ในแต่ละครั้งของการแสกนเป็นข้อมูลที่นำมาใช้งาน

สิ่งที่ต้องการ ใน Sheet ตารางเวร
1 ข้อมูล Actual
1.1 กรณีเข้าทำงานตรงตาม Plan แสดงผลในช่อง F10 ต้องการให้แสดงตัวย่อของกะนั้นๆ
1.1.1 สายหรือไม่ แสดงผลในช่อง F11 ต้องการให้แสดงเวลาที่สายเป็นจำนวน ชั่วโมง:นาที
1.1.2 กลับก่อนเวลาหรือไม่ แสดงผลในช่อง F12 ต้องการให้แสดงเวลาที่กลับก่อนเป็นจำนวน ชั่วโมง:นาที
1.2 กรณีเข้าทำงานไม่ตรงตามกะที่ Plan ไว้
1.2.1 mapping ว่าเข้ากะใด โดยใช้ข้อมูล Finger Scan อ้างอิงกับเวลาตาม Shift ที่กำหนดไว้ แสดงผลในช่อง F13 ต้องการให้แสดงตัวย่อของกะที่เข้าจริง และใส่สีเซลเป็นสีแดง
1.2.2 สายหรือไม่ แสดงผลในช่อง F14 ต้องการให้แสดงเวลาที่สาย เป็นจำนวน ชั่วโมง:นาที
1.2.3 กลับก่อนเวลาหรือไม่ แสดงผลในช่อง F15 ต้องการให้แสดงเวลาที่กลับก่อนเป็นจำนวน ชั่วโมง:นาที
1.3 พนักงานลืมสแกนนิ้วเข้าทำงาน แสดงผลในช่อง F16 ต้องการให้ใส่สีเซลเป็นสีฟ้า
1.4 พนักงานลืมสแกนนิ้วเลิกงาน แสดงผลในช่อง F17 ต้องการให้ใส่สีเซลเป็นสีน้ำเงิน

ขอบพระคุณมากค่ะ

กรุณากรอกตัวอย่างข้อมูลที่ถูกต้องด้วยมือลงในไฟล์ตัวอย่างด้วย จะได้สะดวกต่อการทำความเข้าใจครับ

ใส่ข้อมูลตัวอย่างแล้วนะคะอาจารย์
(มีแก้ไขช่องที่ต้องการให้แสดงผลนิดหน่อยนะคะ เพราะลองกรอกข้อมูลแล้ว อยู่ในคอลัมน์เดียวกันไม่ได้ค่ะ)

ขอบพระคุณค่ะ

ค่อย ๆ ถามตอบกันไปนะครับ

May Chu wrote: Mon Nov 05, 2018 1:15 pm 1 ข้อมูล Actual
1.1 กรณีเข้าทำงานตรงตาม Plan แสดงผลในช่อง F10 ต้องการให้แสดงตัวย่อของกะนั้นๆ
1.1.1 สายหรือไม่ แสดงผลในช่อง F11 ต้องการให้แสดงเวลาที่สายเป็นจำนวน ชั่วโมง:นาที
1.1.2 กลับก่อนเวลาหรือไม่ แสดงผลในช่อง F12 ต้องการให้แสดงเวลาที่กลับก่อนเป็นจำนวน ชั่วโมง:นาที

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

ขออภัยอย่างสูงนะคะอาจารย์ หายไปหลายวันเลยค่ะ
ขอแก้ไขข้อมูลนิดนึงนะคะ

1 ข้อมูล Actual
1.1 กรณีเข้าทำงานตรงตาม Plan แสดงผลในช่อง F10 ต้องการให้แสดงตัวย่อของกะนั้นๆ

กรณีที่เข้าทำงานตรงตามกะที่ plan ไว้ใน cell F9 ให้แสดงผลใน F10 เหมือน F9

1.ดูจาก Plan ใน F9 โดยเทียบเวลาจาก ชีท "Shift" ซึ่งกำหนดให้ กะ M เข้างานเวลา 08:00 น. (Cell B2) และ เลิกงานเวลา 17:00 น. (Cell C2)
2. รายงานการสแกนนิ้วของพนักงาน ชื่อ AS ในชีท "Finger Scan" ใน cell D2 แสดงวันเวลาเข้า เป็น 1/10/2018 7:26:51 และ E2 แสดงผล C/In คือสแกนนิ้วเข้างาน
3. รายงานการสแกนนิ้วของพนักงาน ชื่อ AS ในชีท "Finger Scan" ใน cell D3 แสดงวันเวลาออก เป็น 1/10/2018 17:43:42 และ E3 แสดงผล C/Out คือสแกนนิ้วเข้างาน


1.1.1 สายหรือไม่ แสดงผลในช่อง F11 (แก้ไขเป็น G11) ต้องการให้แสดงเวลาที่สายเป็นจำนวน ชั่วโมง:นาที

สาย 31 นาที

1.ดูจาก Plan ใน G9 โดยเทียบเวลาจาก ชีท "Shift" ซึ่งกำหนดให้ กะ M เข้างานเวลา 08:00 น. (Cell B2)
2. แต่รายงานการสแกนนิ้วของพนักงาน ชื่อ AS ในชีท "Finger Scan" ใน cell D4 แสดงวันเวลาเข้า เป็น 2/10/2018 8:31:19 น. และ E4 แสดงผล C/In คือสแกนนิ้วเข้างาน


1.1.2 กลับก่อนเวลาหรือไม่ แสดงผลในช่อง F12 (แก้ไขเป็น H12) ต้องการให้แสดงเวลาที่กลับก่อนเป็นจำนวน ชั่วโมง:นาที

ออกก่อนเวลา 2 ชั่วโมง 30 นาที แต่ยังอยู่ในกะที่ Plan ไว้

1.ดูจาก Plan ใน H9 โดยเทียบเวลาจาก ชีท "Shift" ซึ่งกำหนดให้ กะ M เลิกงานเวลา 17:00 น. (Cell C2)
2. แต่รายงานการสแกนนิ้วออกพนักงาน ชื่อ AS ใน Sheet "Finger Scan" ใน cell D7 แสดงวันเวลาเข้า เป็น 3/10/2018 14:30:56 และ E7 แสดงผล C/Out คือสแกนนิ้วเข้างาน

แนบไฟล์มาด้วยแล้วนะคะ ขอบคุณค่ะอาจารย์

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

กรุณาช่วยตรวจสอบคอลัมน์ C และ D ว่าตรงกับที่ต้องการแล้วหรือไม่ หากไม่ตรง ช่วยอธิบายว่าเซลล์ใดต้องได้ค่าใดมาด้วยครับ

พร้อมทั้งช่วยกรอกข้อมูลที่ถูกต้องในคอลัมน์ J ของ Sheet1 แล้วอธิบายมาสักสองสามเซลล์ว่าพิจารณาอย่างไรจึงได้คำตอบเป็นเท่านั้น เช่น J3 ใน Sheet1 มีค่าเป็น M เหมือนที่ทำมาใน G10 ของตารางเวรหรือไม่ พร้อมอธิบายเหตุผลประกอบมาด้วย เช่นหากเป็น M เหตุใดจึงเป็น M พิจารณาเวลาเข้า เวลาออกให้ครอบคลุมทุกแง่มุม อย่าลืมพิจารณาเซลล์อื่น ๆ อันอาจไม่สอดคล้องกับสิ่งที่อธิบายครับ

นอกจากนี้ แผนของแต่ละคน (M, M1, N ฯลฯ) จะต้องมีตารางแยกออกมาต่างหาก จะได้ทราบว่าใครอยู่แผนใด โดยชีตตารางเวรควรเป็นผลลัพธ์สุดท้ายที่นำข้อมูลแผนนั้นมาใช้

ขออภัยอาจารย์อีกรอบนะคะ
ช่วงนี้พอดีช่วงนี้ไม่อยู่เลย ออกไซต์งานตลอดเลยค่ะ
เดี๋ยวกลับเข้าออฟฟิตจะรีบทำส่งให้อาจารย์นะคะ

ขอบพระคุณค่ะอาจารย์

กรุณาช่วยตรวจสอบคอลัมน์ C และ D ว่าตรงกับที่ต้องการแล้วหรือไม่ หากไม่ตรง ช่วยอธิบายว่าเซลล์ใดต้องได้ค่าใดมาด้วยครับ
- ตรงตามที่ต้องการแล้วค่ะอาจารย์

พร้อมทั้งช่วยกรอกข้อมูลที่ถูกต้องในคอลัมน์ J ของ Sheet1 แล้วอธิบายมาสักสองสามเซลล์ว่าพิจารณาอย่างไรจึงได้คำตอบเป็นเท่านั้น เช่น J3 ใน Sheet1 มีค่าเป็น M เหมือนที่ทำมาใน G10 ของตารางเวรหรือไม่ พร้อมอธิบายเหตุผลประกอบมาด้วย เช่นหากเป็น M เหตุใดจึงเป็น M พิจารณาเวลาเข้า เวลาออกให้ครอบคลุมทุกแง่มุม อย่าลืมพิจารณาเซลล์อื่น ๆ อันอาจไม่สอดคล้องกับสิ่งที่อธิบายครับ
- กรอกข้อมูลแล้วค่ะ แต่แอบติดนิดนึงตรงที่ กะ M และ M1 เวลาใกล้เคียงกันมาก เวลาการแสกนนิ้วก็อาจทำให้ผิดได้ หนูอาจจะต้อง manual รึเปล่าคะอาจารย์

นอกจากนี้ แผนของแต่ละคน (M, M1, N ฯลฯ) จะต้องมีตารางแยกออกมาต่างหาก จะได้ทราบว่าใครอยู่แผนใด โดยชีตตารางเวรควรเป็นผลลัพธ์สุดท้ายที่นำข้อมูลแผนนั้นมาใช้
- ได้เพิ่ม Sheet "ตารางเวร(2)" แล้วนะคะไม่แน่ใจว่าถูกต้องตามที่อาจารย์บอกหรือไม่ค่ะ

ขอบพระคุณค่ะอาจารย์ และขออภัยอย่างสูงที่ล่าช้านะคะ

May Chu wrote: Fri Nov 16, 2018 3:21 pm - กรอกข้อมูลแล้วค่ะ แต่แอบติดนิดนึงตรงที่ กะ M และ M1 เวลาใกล้เคียงกันมาก เวลาการแสกนนิ้วก็อาจทำให้ผิดได้ หนูอาจจะต้อง manual รึเปล่าคะอาจารย์

หากไม่สามารถอธิบายหลักการคิดได้ก็คงจะต้องกรอกมาด้วยมือครับ

ช่วยอธิบาย K3 กับ L4 ว่าหามาได้อย่างไร เหตุใดจึงกรอกคนละรูปแบบ คือตัวเลขปกติและตัวเลขที่เป็นเวลาครับ

สวัสดีค่ะอาจารย์
ใส่คอมเมนต์ในไฟล์ที่อาจารย์แจ้งแล้วนะคะ
ขอบคุณค่ะ

จากข้อมูลในคอมเมนต์ของ M5 คือ

ดูเทียบเวลาตามแผน(G5 และ H5) กับเวลาที่พนักงานสแกนนิ้วเข้าจริง Time_In (E5) และ สแกนนิ้วออกจริง Time_Out (F5) ซึ่งกำหนดให้ กะ M เข้างานเวลา 08:00 น. (H5) และ เลิกงานเวลา 17:00 น. (I5)
แต่รายงานการสแกนนิ้วของพนักงาน ชื่อ AS แสดงเวลาเข้า เป็น 8:45:59 (E5) และ แสดงเวลาออก เป็น 21:15:45 (F5) ซึ่งไม่ตรงกับแผนที่กำหนด แต่ไปตรงกับกะอื่น (M2) จึงต้องการให้แสดงกะที่เข้าจริง

ในชีต Shift เวลาเข้าของ M2 คือ 9:00 และ ออก คือ 20:30 มีหลักการพิจารณาอย่างไรว่าการเข้า 8:45:59 หมายถึงเป็นการเข้ากะของ M2 ครับ การจะเข้ากะใดควรที่จะอยู่ในช่วงเวลาของกะนั้น ถ้ายังไม่ถึงก็ไม่ควรจะถูกนับเข้าไปครับ

ส่วนสูตรสำหรับเข้าสายและกลับก่อนคือด้านล่าง

1. ที่ K2 คีย์
   =IFERROR(MAX(0,E2-H2),0)
   Enter > Copy ลงด้านล่าง
2. ที่ L2 คีย์
   =IFERROR(MAX(0,I2-F2),0)
   Enter > Copy ลงด้านล่าง

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

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

ขอบพระคุณอาจารย์อย่างสูงค่ะ

ใช้ Isna ครับ

M2 =IF(ISNA(E2),"P","")

คัดลอกไปที่ N2 แล้วคัดลอกลงไปจนสุดข้อมูล

ขอบพระคุณมากค่ะ ใส่สูตรตามที่แนะนำแล้ว และเกือบเสร็จหมดแล้ว ติดอีกเรื่องเดียวจริงๆ แล้วค่ะ
ตอนนี้มีปัญหาของกะ N ตอนดึงข้อมูลเนื่องจากเป็นการสแกนนิ้วเข้า และออกคนละวัน (เข้าดึก ออกเช้าวันถัดไป ทำให้วันที่เป็นคนละวัน แต่ตอนนี้ข้อมูลที่ดึงมาใน SHEET 1 เป็นข้อมูลวันเดียวกันค่ะ)

อ่านแล้วไม่เข้าใจครับ

เซลล์ D40 ใน Sheet1 ที่คีย์มาด้วยมือ มาจากเซลล์ไหนในชีต Finger Scan ครับ

มาจาก D75 ค่ะ

อธิบายเพิ่มเติมนะคะอาจารย์
กะ N คือ กะดึก ซึ่งต้องสแกนนิ้วเข้าทำงานตอนกลางคืน และสแกนนิ้วออกกะในเช้าวันถัดไปค่ะ
ยกตัวอย่าง กะดึก (N) วันที่ 2 จะมีการสแกนนิ้วเข้าวันที่ 2 และจะสแกนนิ้วออกในวันที่ 3 ค่ะ
ตัวอย่าง
row 74 SM NK 26 02/10/2018 21:46 C/In
row 75 SM NK 26 03/10/2018 8:34 C/Out

แต่ข้อมูลที่ดึงมาอยู่ในแถวเดียวกันของ Sheet1 ตอนนี้
กลายเป็นดึงเวลาในวันเดียวกันมาทำให้เวลาสลับกันระหว่างเวลาเข้าและออกค่ะ

row 72 SM NK 26 02/10/2018 8:03 C/Out
row 74 SM NK 26 02/10/2018 21:46 C/In

ลองดูว่าได้คำตอบที่ต้องการหรือไม่ครับ

Shee1 เซลล์ D2 ปรับสูตรเป็น

=LOOKUP(2,1/((INT('Finger Scan'!$D$2:$D$352)=$B2+(G2="N"))*('Finger Scan'!$B$2:$B$352=$A2)*('Finger Scan'!$E$2:$E$352=D$1)),'Finger Scan'!$D$2:$D$352)

Enter > Copy ลงด้านล่าง

เสร็จแล้วค่ะอาจารย์ (เย้ๆๆๆ)
กราบขอบพระคุณมากๆ เลยนะคะ
ลดเวลางานไปได้เยอะมากๆ เลยค่ะ

กราบขอบพระคุณอาจารย์ และทุกท่านที่ช่วยเหลืออีกครั้งนะคะ