Snasui Excel Forum

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

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

สำหรับข้อ 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 จึงจะสามารถตอบได้ครับ

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

ขอบคุณมากค่ะ สำหรับการแปลง 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. รบกวนขอสูตรคำนวณในช่องที่มีไฮไลท์สีชมพู ด้วยค่ะ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ที่ชีท 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 และหากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ ดูไฟล์แนบประกอบครับ

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

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

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

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

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

kai44444 wrote:....

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

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

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

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

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

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

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


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

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

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

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

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

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