Excel Forum

snasui wrote: Sat Jan 28, 2023 5:42 am ตัวอย่างสูตรที่เซลล์ AJ8 ครับ

=LET(day,LEFT($E$7:$AI$7,FIND(".",$E$7:$AI$7)),tran,IF(ISNUMBER(LEFT($E8:$AI8)+0),LEFT($E8:$AI8,5)),dth,INDEX(day,{2,3,4,5,6}),fdty,INDEX(วันเวร!$C$2:$G$113,MATCH("*"&$C8,TRIM(วันเวร!$B$2:$B$113),0),0),daydty,ISNUMBER(MATCH(day,IF(ISTEXT(fdty),dth),0)),rsc,SUM(IFERROR(daydty/(tran+0>="7:00"+0),0)),nrsc,SUM(IFERROR((1-daydty)/(tran+0>="7:15"+0),0)),rsc+nrsc)

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

• ชื่อพนักงานทั้งสองชีตเขียนไว้ไม่เหมือนกัน
• หัวตารางที่แสดงชื่อวันของทั้งสองชีตไม่เหมือนกัน
• เก็บข้อมูล 2 บรรทัดในเซลล์เดียว

snasui wrote: Sat Jan 28, 2023 11:56 am เนื่องจากข้อมูลจัดเก็บในลักษณะที่เป็นอุปสรรคต่อการหาคำตอบ ยกตัวอย่างเช่น

• ชื่อพนักงานทั้งสองชีตเขียนไว้ไม่เหมือนกัน
• หัวตารางที่แสดงชื่อวันของทั้งสองชีตไม่เหมือนกัน
• เก็บข้อมูล 2 บรรทัดในเซลล์เดียว

ข้างบนนี้ต้องมีการจัดการด้วยสูตรเช้าไปช่วย ยิ่งสร้างอุปสรรคไว้มากก็จะต้องใช้สูตรที่ยุ่งยากมากขึ้น

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

ไวยากรณ์ของ Let คือ

=let(name1,name_value1,calculation_or_name2,name_value2,...)

name1 คือชื่อของสูตร
name_value1 คือสูตรของ name1

จะใช้เป็นชุด ๆ ไปเช่นนี้จนครบทุกชุดที่ต้องการ โดยชุดหลังสุดคือคำตอบที่ต้องการแสดงในเซลล์

จากสูตร

=LET(day,LEFT($E$7:$AI$7,FIND(".",$E$7:$AI$7)),tran,IF(ISNUMBER(LEFT($E8:$AI8)+0),LEFT($E8:$AI8,5)),dth,INDEX(day,{2,3,4,5,6}),fdty,INDEX(วันเวร!$C$2:$G$113,MATCH("*"&$C8,TRIM(วันเวร!$B$2:$B$113),0),0),daydty,ISNUMBER(MATCH(day,IF(ISTEXT(fdty),dth),0)),rsc,SUM(IFERROR(daydty/(tran+0>="7:00"+0),0)),nrsc,SUM(IFERROR((1-daydty)/(tran+0>="7:15"+0),0)),rsc+nrsc)

ชุดที่ 1 คือ day,LEFT($E$7:$AI$7,FIND(".",$E$7:$AI$7)) เป็นการตัดชื่อวันมาแสดง สามารถทดสอบโดยการเปลี่ยนชุดสุดท้ายในสูตรด้านบนคือ rsc+nrsc เป็น day แล้วสังเกตดูผล

ชุดที่ 2 คือ tran,IF(ISNUMBER(LEFT($E8:$AI8)+0),LEFT($E8:$AI8,5)) เป็นการตัดเอาเวลาเข้างานในแต่ละบรรทัดมาแสดง สามารถทดสอบโดยการเปลี่ยนชุดสุดท้ายในสูตรด้านบนคือ rsc+nrsc เป็น tran แล้วสังเกตดูผล

ชุดที่ 3 คือ dth,INDEX(day,{2,3,4,5,6}) เป็นการสร้างหัวคอลัมน์ให้รองรับข้อมูลของชีต วันเวร สามารถทดสอบโดยการเปลี่ยนชุดสุดท้ายในสูตรด้านบนคือ rsc+nrsc เป็น dth แล้วสังเกตดูผล

ชุดที่ 4 คือ fdty,INDEX(วันเวร!$C$2:$G$113,MATCH("*"&$C8,TRIM(วันเวร!$B$2:$B$113),0),0) เป็นการหาว่ารายชื่อพนักงานในบรรทัดนั้น ๆ หยุดในวันใดบ้าง สามารถทดสอบโดยการเปลี่ยนชุดสุดท้ายในสูตรด้านบนคือ rsc+nrsc เป็น fdty แล้วสังเกตดูผล

ชุดที่ 5 คือ daydty,ISNUMBER(MATCH(day,IF(ISTEXT(fdty),dth),0)) เป็นการหาว่าพนักงานในบรรทัดนั้น ๆ หยุดวันไหนบ้าง สามารถทดสอบโดยการเปลี่ยนชุดสุดท้ายในสูตรด้านบนคือ rsc+nrsc เป็น daydty แล้วสังเกตดูผล

ชุดที่ 6 คือ rsc,SUM(IFERROR(daydty/(tran+0>="7:00"+0),0)) เป็นการหาว่าพนักงานในบรรทัดนั้น ๆ ที่มีวันเวรเข้าสายกี่วัน สามารถทดสอบโดยการเปลี่ยนชุดสุดท้ายในสูตรด้านบนคือ rsc+nrsc เป็น rsc แล้วสังเกตดูผล

ชุดที่ 7 คือ nrsc,SUM(IFERROR((1-daydty)/(tran+0>="7:15"+0),0)) เป็นการหาว่าพนักงานในบรรทัดนั้น ๆ ที่ไม่มีวันเวรเข้าสายกี่วัน สามารถทดสอบโดยการเปลี่ยนชุดสุดท้ายในสูตรด้านบนคือ rsc+nrsc เป็น nrsc แล้วสังเกตดูผล

ตัวสุดท้ายคือ rsc+nrsc เป็นผลลัพธ์ เป็นการนำผลลัพธ์ของ rsc และ nrsc มารวมกันครับ

snasui wrote: Tue Jan 31, 2023 7:03 pm เนื่องจากชื่อของวันที่เริ่มของแต่ละเดือนไม่เหมือนกันจึงทำให้ผลการคำนวณคลาดเคลื่อน สามารถปรับสูตรที่ AJ8 เป็นด้านล่างครับ

=LET(day,LEFT($E$7:$AI$7,FIND(".",$E$7:$AI$7)),tran,IF(ISNUMBER(LEFT($E8:$AI8)+0),LEFT($E8:$AI8,5)),dth,{"จ.","อ.","พ.","พฤ.","ศ."},fdty,INDEX(วันเวร!$C$2:$G$113,MATCH("*"&$C8,TRIM(วันเวร!$B$2:$B$113),0),0),daydty,ISNUMBER(MATCH(day,IF(ISTEXT(fdty),dth),0)),rsc,SUM(IFERROR(daydty/(tran+0>="7:00"+0),0)),nrsc,SUM(IFERROR((1-daydty)/(tran+0>="7:15"+0),0)),rsc+nrsc)

snasui wrote: Wed Feb 01, 2023 8:04 pm เริ่มคลุมจากเซลล์ E8 จากนั้นกำหนด Conditional Formatting ด้วยสูตรตามด้านล่างครับ

=IFERROR(AND(INDEX(วันเวร!$C$2:$G$113,MATCH("*"&$C8,TRIM(วันเวร!$B$2:$B$113),0),MATCH(LEFT(E$7,FIND(".",E$7)),TRIM(LEFT(SUBSTITUTE(OFFSET($E$7,0,MATCH("จ.*",$E$7:$AI$7,0)-1,1,5),CHAR(10),REPT(" ",5)),5)),0))="เวร",LEFT(E8,5)+0>=7/24),FALSE)

snasui wrote: Wed Feb 01, 2023 9:15 pm กรุณาแนบไฟล์ล่าสุดมาด้วยพร้อมอธิบายว่าได้กำหนดให้แสดงแบบใดจะได้เข้าถึงปัญหาโดยไวครับ

snasui wrote: Wed Feb 01, 2023 10:45 pm ดูตัวอย่างจากไฟล์แนบ ผมระบายเป็นสีทึบไว้ให้ ลองไปเปลี่ยนเป็นสีหรือรูปแบบอื่นใดดูตามต้องการครับ