ตารางการคำนวณเวลามาสายของพนักงาน

[whatsis]

สวัสดีครับ

รบกวนขอความรู้และความช่วยเหลือจากทุกคนในห้องนี้ด้วยนะครับ

ผมต้องรับผิดชอบทำรายงานข้อมูลการมาสายของพนักงานในแผนก โดยอาศัยข้อมูลดิบจากเครื่องบันทึกเวลาเข้า-ออกงานของพนักงงานทั้งโรงงาน

พยายามจะลองใช้ Vlookup แยกพนักงานในแผนกออกจากพนักงานทั้งหมดก็พบว่าหมายเลขพนักงานจะไปซ้ำกับหมายเลขของเขาเองที่ตอกบัตรเข้า-ออกในวันอื่นๆ เช่น
WX29840 2012.03.30 8:33
WX29840 2012.03.30 19:24
WX30368 2012.03.30 8:33
WX30368 2012.03.30 20:14
WX34305 2012.03.30 18:39
WX39293 2012.03.30 7:48
WX29840 2012.04.02 8:54
WX29840 2012.04.02 18:56

รวมทั้งพยายามใช้ Offset ดึงข้อมูลเวลาออกงานของพนักงานคนนั้นๆที่มาสายว่าได้มีการทำงานชดเชยเวลาที่ตัวเองมาสายหรือไม่ก็ปรากฎว่าบางครั้งเครื่องบันทึกเวลาไม่ได้เรียงลำดับเวลาเข้า-ออกงานติดกันแต่กลับมีข้อมูลพนังงานคนอื่นแทรกจึงใช้ =Offset(A-1,0,0) ไม่ได้ เช่น
WX29840 2012.03.30 8:33
WX29840 2012.03.30 19:24
WX30368 2012.03.30 8:33
WX34305 2012.03.30 18:39
WX30368 2012.03.30 20:14
WX39293 2012.03.30 7:48
WX39293 2012.03.30 18:02

เบื้องต้นใช้วิธีการค้นหาหมายเลขพนักงานเฉพาะพนักงานในแผนก หลังจากนั้นมาดูบันทึกเวลาเข้างานด้วยตัวเอง

ทั้งนี้ผมได้แนบไฟล์ตัวอย่างมาให้ดูด้วยครับ โดยสมมุติให้ทั้งโรงงานมีพนังงาน 16 คน ส่วนแผนกที่ต้องการมีพนักงาน 6 คน ในแต่ละเดือนจะได้ข้อมูลจากเครื่องบันทึกเวลามาดังชีทที่ชื่อว่า "Raw" เพื่อมาทำรายงานในชีท "Sammary"

ขอขอบพระคุณทุกคนล่วงหน้าครับ

[bank9597]

ลองทำตัวอย่างคำตอบที่ต้องการมาให้ดูอีกนิดได้ไหมครับ ผมยังไม่ค่อยเข้าใจมากนัก

[whatsis]

ขออภัยด้วยครับที่อาจจะเขียนไม่ค่อยเข้าใจ ผลลัพท์ที่ต้องการได้คือแบบในชีท "Summary" เลยครับ เพียงแค่ต้องการจัดระเบียบข้อมูลเรียงเป็นบันทึกการเข้าออกงานของพนักงานแต่ละท่านตามด้านล่างนี้เลยครับ

Employee NO. Date Swipetime Late
WX21030 2012.04.02 8:27 0:27
WX21030 2012.04.02 17:47

ชีท "Summary" นี้ผมใช้วิธีค้นหาจากหมายเลขพนักงานที่ละท่านทีละท่านจึงต้องใช้เวลาอย่างมาก ปัญหาหลักของผมคือจะค้นหาเฉพาะพนักงานแผนกตัวเองยังไงให้ได้แบบชีท "Summary" จากข้อมูลดิบที่ได้จากเครื่องบันทึกเวลาเข้าออก (ชีท "Raw")

ขอบพระคุณล่วงหน้าครับ

[whatsis]

ผมลองใช้ Vlookup ตาม http://snasui.blogspot.com/2009/12/vlookup.html กับกรณีอื่นก็ได้ผลดี แต่คราวนี้พอใช้ Vlookup มันก็จะไปเจอข้อมูลซ้ำๆเพราะเป็นหมายเลขพนังการเดียวกัน หรือว่าผมเลือกใช้ฟังชั่นไม่เหมาะสม รบกวนขอคำชี้แนะด้วยครับ

[snasui]

ลองทำตัวอย่างข้อมูลโดยอ้างอิงมาให้ด้วยว่าค่าที่ได้มาจากเซลล์ไหนของ Raw Data สักสี่ห้าบรรทัด เพื่อความเข้าใจที่ตรงกัน

สำหรับการ Lookup ข้อมูลที่เป็นค่าเดียวกันแล้วประกอบด้วยหลาย ๆ บรรทัดและต้องการจะดึงมาทั้งหมด จะไม่สามารถใช้ Vlookup มาช่วยได้ครับ จำเป็นจะต้องใช้สูตร Array ยกตัวอย่างเช่นที่ C2 ชีท Summary คีย์

=INDEX(Raw!$C$2:$C$575,SMALL(IF(A2=Raw!$A$2:$A$575,IF(B2=Raw!$B$2:$B$575,ROW(Raw!$C$2:$C$575)-ROW(Raw!$C$2)+1)),COUNTIF(A$2:A2,A2)))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

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

[whatsis]

ผมมีปัญหาเล็กๆกับสูตรที่อาจารย์ให้มาครับ คือว่ามาใส่สูตรแล้วกด Ctrl+Shift+Enter มันไม่มี {} ตามที่อาจารย์บอกก็คือผมกดไม่ถูกต้อง แต่ก็ได้ลองพยายามหลายครั้งแล้ว เบื้องต้นปัญหาที่ผมพบเมื่อพยายามแก้สูตรก็คือในสูตรที่ผมพยายามปรับชื่อเซลล์มันไม่ได้เป็น C6,B3,F6 อะไรแบบนี้อ่ะครับ แต่มันกลับเป็น R[3],R[-5],R[7] อะไรแบบนี้แทน ไม่แน่ใจว่าผมไปตั้งค่าอะไรผิดหรือไม่?

ทั้งนี้ทั้งนั้นต้องขอบพระคุณอาจารย์มากเลยครับที่ช่วยไขข้อข้องใช้เรื่อง Lookup ให้ ระหว่างนี้ผมจะพยายามไปศึกษาการใช้ Array ด้วยตนเองควบคู่กันไปด้วยครับ

ขอบคุณครับ

[snasui]

แสดงว่าตอนนี้หัวคอลัมนเป็นตัวเลขแทนที่จะเป็นตัวหนังสือ ให้เข้าเมนู Tools > Options... แล้วปลดเครื่องหมายตรง R1C1 reference style ตามภาพด้านล่างครับ

[whatsis]

ขอบพระคุณอาจารย์มากเลยครับ

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

[snasui]

ลองเปลี่ยนสูตรเดิมเป็น

=INDEX(Raw!$C$2:$C$575,SMALL(IF(A2=Raw!$A$2:$A$575,IF(B2=Raw!$B$2:$B$575,ROW(Raw!$C$2:$C$575)-ROW(Raw!$C$2)+1)),SUM(IF(A2=Raw!$A$2:$A$575,IF(B2=Raw!$B$2:$B$575,1)))))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ที่เน้นการ Copy ลงด้านล่างเพราะตามไฟล์แนบเป็นการ Copy ที่ผิดครับ Copy ไปแล้วไม่ต้องตามไปแก้ใด ๆ สูตรจะถูกต้องของมันเอง

[whatsis]

พอใช้สูตรใหม่ แล้ว Ctrl+Shift+Enter แล้ว Copy ลงด้านล่างแล้ว Ctrl+Shift+Enter ไปเรื่อยๆปรากฎว่าผลออกมาเป็นเวลาออกงาน 17:47 ทั้งหมดเลยอ่ะครับ

[snasui]

พอใช้สูตรใหม่ แล้ว Ctrl+Shift+Enter แล้ว Copy ลงด้านล่างแล้ว Ctrl+Shift+Enter ไปเรื่อยๆ

การทำแบบนี้ไม่ใช่การ Copy ลงด้านล่างตามที่ผมแนะนำครับ ขั้นตอนที่ถูกต้องคือ เมื่อคีย์เสร็จในเซลล์แรกและ Ctrl+Shift+Enter เรียบร้อยแล้ว ให้คลิกขวาที่เซลล์นั้น แล้วเลือก Copy จากนั้นคลุมเซลล์ด้านล่างทั้งหมดที่เหลือเพื่อจะวางข้อมูล หลังจากคลุมแล้วให้คลิกขวาแล้วเลือก Paste

[whatsis]

เปิ่นจังผมนี่! ขอบคุณมากครับอาจารย์ไขซะกระจ่างเลย แต่ผมก็ยังมีปัญหาเล็กๆตรงที่วันแรกของพนักงานแต่ละคนมันจะไม่ขึ้นครับ แล้วก็เวลาเข้า-ออกในแต่ละวันก็แสดงผลออกมาเป็นเวลาเดี่ยวกันอีก ไม่แน่ใจว่าผมทำอะไรผิดพลาดอีกหรือป่าว

ขอบคุณครับ

[snasui]

สังเกตสูตรที่ผมเขียนให้ไปตามที่ผมระบายสีไว้ตามด้านล่างว่าตรงกับสูตรในไฟล์ที่แนบมาหรือไม่ครับ

=INDEX(Raw!$C$2:$C$575,SMALL(IF(A2=Raw!$A$2:$A$575,IF(B2=Raw!$B$2:$B$575,ROW(Raw!$C$2:$C$575)-ROW(Raw!$C$2)+1)),SUM(IF(A2=Raw!$A$2:$A$575,IF(B2=Raw!$B$2:$B$575,1)))))

[whatsis]

ขอบคุณมากๆเลยครับอาจารย์ ผมจะนำสูตรนี้ไปประยุกต์ใช้กับงานอื่นๆ แล้วก็ศึกษาหาข้อมูลด้วยตัวเองประกอบไปด้วย แต่ถ้าเหลือบ่ากว่าแรงยังไงต้องขออนุญาตกลับมารบกวนอาจารย์อีกนะครับ ขอบคุณมากครับ