รบกวนสอบถามเรื่องการทำวัน ลา มาสาย การคิดสูตร

[kai44444]

1.การแปลงTextไฟล์ ให้อยู่ในรูปแบบของเวลา เช่น 83126(Text)==>08:31:26(Time) เพื่อใช้เก็บข้อมูล และสามารถนำไปคำนวณเวลาสายได้
2.การกรองข้อมูลจำนวนมากๆ โดยกรองจากข้อมูลเวลาที่มีการเข้า และออก ของพนักงาน เช่น นาย ก. เข้า 08:30:00 ออก 17:00:00 แต่ระหว่างวันมีการเข้า ออกมากกว่านั้น ซึ่งเราไม่ต้องการ จะมีวิธีการใดช่วยให้ง่ายและไว มากกว่าการเรียงข้อมูลแล้วตัดออกเองไหมค่ะ
3.ขอสูตรให้คำนวณตามไฟล์แนบค่ะ (ได้แนบตารางมาให้ดู อันนี้คิดเองเพื่อสามารถสรุปประจำเดือน และปีได้ ถ้าการออกแบบตารางไม่ถูกต้องยังไง รบกวนขอคำแนะนำด้วยค่ะ)

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

[snasui]

สำหรับข้อ 1. เรื่องการแปลงวันที่และเวลาผมแทรกคอลัมน์ C:D จากนั้น

1. ที่ C3 คีย์สูตร

=TEXT(A3,"0000\/00\/00")+0

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

2. ที่ D3 คีย์สูตร

=TEXT(B3,"00\:00\:00")+0

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

ส่วนข้อ 2 ยังไม่กระจ่างครับ

จาก 2. การกรองข้อมูลเอาเฉพาะเวลาที่ In ครั้งแรก และ Out ครั้งสุดท้าย ทำอย่างไรค่ะ

การดู In, Out ต้องดูชื่อคนประกอบด้วยใช่หรือไม่ครับ หากใช่จะต้องใช้สูตรมาช่วยในการค้นหา และต้องมีเซลล์ที่ระบุเงื่อนไขไว้ด้วยครับ การกรองของ Excel ไม่มี Option การกรองตัวแรก ตัวสุดท้ายมาให้ครับ

และสำหรับการคิดเวลานั้น จำเป็นจะต้องทราบเงื่อนไขทั้งหมด สำหรับการมาสาย การคิด OT จึงจะสามารถตอบได้ครับ

ดูไฟล์แนบประกอบครับ

[kai44444]

ขอบคุณมากค่ะ สำหรับการแปลง Text เป็น วัน และ เวลา
คำถาม
1.ถ้าจะนำไปคำนวณเวลาสายสามารถนำไปวางที่เวลาเข้างานใน Sheet Database แล้วจะขอสูตรการคำนวณในแต่ละ Cell ของไฟล์ที่แนบด้วยค่ะ

อธิบายเงื่อนไขการคิด วันลา มาสาย (เรื่องโอทีตัดทิ้งได้เลยค่ะ T T )
1. เข้างาน 08.30 - 17.00 น.
2. การเข้าสายมี 3 เงื่อนไขดังนี้
2.1 08.31 - 09.00 น. ถือว่า สายระดับ1 หากมาสายในช่วงเวลานี้ 3 ครั้งใน 1 เดือน(ตัดทุกวันที่25) ถือว่า ขาดงาน 1/2 วัน กรณีสาย 2ครั้งใน 1เดือน อนุโลมไม่คิดสาย
2.2 09.01 - 10.00 น. ถือว่า สายระดับ2 หัก 1/2 วัน
2.3 10.01 ขึ้นไป ถือว่า สายระดับ3 หัก 1 วัน
3. ประเภทการลา ลากิจ ลาป่วย ขาดงาน พักร้อน ลาคลอด ลืมตอกบัตร ลาอุปสมบท ลารับราชการทหาร
ทุกคนมีสิทธิลา ปีละ 6 ครั้ง (1 ก.ค. - 30 มิ.ย.) 6 เดือน ใช้สิทธิได้ 3 วัน เกินจาก 3 วันใน6เดือน หักเงิน 1 วัน

4. รบกวนขอสูตรคำนวณในช่องที่มีไฮไลท์สีชมพู ด้วยค่ะ

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

[snasui]

ลองดูการดึงข้อมูลการเข้างานและการออกงานในเซลล์ F4:G9 ตามไฟล์แนบครับ

ผมเขียนสูตรการคิดการเข้างานสายในช่วง G4:H9, L4:N9

ลองทดสอบเปลี่ยนแปลงค่าและดูการเปลี่ยนแปลง

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

[kai44444]

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

1.ช่องเวลาสาย หากเข้าสายกว่า 08.30 ต้องการให้คำนวณเป็นนาที เช่น นาย ก. มาเวลา 08.31 ก็คำนวณ 1 นาที

2.ดูในไฟล์ประกอบที่มีลูกศรชี้ค่ะ ต้องการให้คำนวณจากเงื่อนไขใน Sheet Database

3.จากตารางที่ส่งให้ดู มันสามารถแตกเป็นรายงานตามชีทที่ส่งให้ดูได้ไหมค่ะ ต้องการลดกระบวนการทำด้วยการคีย์ลงไปเอง เป็นการรวบรวมข้อมูลแล้ว Link ไปที่หน้ารายงานต่างๆได้ (ข้อนี้แค่ถามว่ามันสามารถมีโอกาศเป็นไปได้ไหมค่ะ)

4.ถ้าการออกแบบตารางที่เก็บข้อมูลมันไม่ถูกต้อง ขอคำชี้แนะค่ะ จะพยายามออกแบบให้สอดคล้องกับการออกหน้ารายงานค่ะ

รบกวนด้วยนะคะ

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

[snasui]

ข้อ ๑ และ ๒ ผมเขียนสูตรให้แล้ว สำหรับการเลือกเฉพาะ In และ Out ดูที่ชีท Text คอลัมน์ P สามารถกรองข้อมูลเฉพาะที่มีค่า True ครับ

คำถามข้อ ๓ และ ๔ นั้น ข้อมูลเป็นกึ่ง Database กึ่งรายงาน หากจะนำไปใช้ในชีทต่าง ๆ ก็พอทำได้ครับ

[kai44444]

ขอบคุณมากค่ะ ที่สละเวลาคิด และตอบคำถามให้ ขอบคุณค่ะ

[kai44444]

ขอถามต่อจากไฟล์ ที่ท่านอาจารย์ได้ทำให้ TIME_EMPLOYEE-1

สูตรกรองข้อมูล ในส่วนของ IN นั้นไม่มีปัญหาค่ะ ถูกต้อง เพราะว่าเลือกข้อมูลแรกจากข้อมูลที่มีการซ้ำกัน
แต่ในส่วนของ OUT นั้นมีปัญหาค่ะ เพราะว่า ต้องการค่าสุดท้ายจากข้อมูลที่ซ้ำกัน

จึงขอรบกวนถามในส่วนของการหาค่าสุดท้ายของข้อมูลที่มีซ้ำกัน (OUT คือ เวลาออกจากงาน จึงต้องเอาค่าสุดท้ายหรือเวลาสุดท้ายที่มีการสแกนบัตรออกค่ะ)

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

[snasui]

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

ที่ชีท Text เซลล์ P3 คีย์

=$D3=IF($O3="in",MIN(IF($G3=$G$3:$G$295,IF($C3=$C$3:$C$295,$D$3:$D$295))),MAX(IF($G3=$G$3:$G$295,IF($C3=$C$3:$C$295,$D$3:$D$295))))

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

สังเกตการกดแป้นให้รับสูตร หรือกรณีแก้ไขสูตร จะต้องกด Ctrl+Shift ค้างไว้ก่อนแล้วกด Enter เนื่องจากเป็นสูตร Array และหากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ ดูไฟล์แนบประกอบครับ

[kai44444]

ขอบคุณมากค่ะ
รบกวนดูไฟล์อันใหม่ค่ะ ซึ่งดูจากกระทู้เก่าๆ ก็เลยทำเรื่องการใช้ Offset ได้บางนิดหน่อยแบบไม่เข้าใจ แต่ก็พอทำได้

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

[snasui]

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

[kai44444]

เราเน้นที่เดือนเป็นหลักค่ะ ก็คือ กรกฎาคม 2553 ชีท Jul ค่ะ

[snasui]

ไม่เข้าใจครับ จากด้านล่าง

....

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

เราเน้นที่เดือนเป็นหลักค่ะ ก็คือ กรกฎาคม 2553 ชีท Jul ค่ะ

สรุปหาจากหลายชีทหรือชีทเดียวครับ

[kai44444]

เรียบเรียงคำถามใหม่นะคะ

การเก็บข้อมูลการสาย เก็บแยกเป็นรายเดือน จึงมี 12 ชีท ตามชื่อเดือน

มีแบบฟอร์มสรุปให้เป็นรายคน ในชีทForm ซึ่ง แต่ละเดือนมันต้องเอาข้อมูลจาก 12 ชีทเดือน

อยากให้มันหาง่ายๆ เลยสร้างให้ใส่รหัสพนักงานขึ้นมา แล้วข้อมูลก็ลงตามช่องเองอ่ะคะ


สรุปต้องหาจากหลายชีท ไปลงที่ชีท Form ค่ะ

[snasui]

ครับ จากอธิบายมานั้นยังไม่พบว่าได้ตอบคำถามนี้ครับ

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

[kai44444]

ฮ่า งั้นทำไฟล์เองใหม่ดีกว่า ได้ลองทำสูตรให้มันลิ้งค์หากัน แล้ว แต่การใช้ Vlookup บางครั้งก็พบว่า ข้อมูลที่ส่งมาไม่ตรง ถามว่า จากไฟล์ที่ทำมานี้ มันจะมีโอกาสผิดพลาดของข้อมูลไหมค่ะ ถ้าเราใช้วิธีนี้

[snasui]

ถ้ามั่นใจว่าข้อมูลในชีท Jul คอลัมน์ B มีการเีรียงจากน้อยไปหามากเสมอ สามารถใช้ Vlookup ตามที่เขียนมาได้ครับ แต่หากไม่เรียงควรจะใช้แบบตรงตัว นั้นคือต้องมีส่วนประกอบสุดท้ายเป็น 0 หรือ False เสมอ ไม่เช่นนั้นข้อมูลที่ได้มาจะไม่ตรงกับที่ต้องการ ดูเพิ่มเติมที่นี่ครับ http://snasui.blogspot.com/2009/12/vlookup.html

[kai44444]

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