Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
ระบุ Version ของ Excel 
โปรดทราบ สมัครสมาชิก
Login ผ่าน Facebook Reset รหัสผ่าน ติดต่อผู้ดูแลระบบ ตั้งค่าส่วนตัว วิธีการตั้งและตอบกระทู้ จัดรูปแบบตัวอักษร กำหนดขนาดตัวอักษรใน Browser[code] และปิดด้วย [/code] ตัวอย่างเช่น [code]dim r as range[/code] เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
นับวันสายแบบมีเงื่อนไข
ตัวอย่างสูตรที่เซลล์ AJ8 ครับขอบคุณมากครับ ตรงตามที่ต้องการเลยsnasui wrote: ↑Sat Jan 28, 2023 5:42 am
=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 มารวมกันครับ
หาผลของเดือนใด ๆ ข้อมูลก็ต้องเป็นของเดือนนั้น ๆ ไม่ทราบว่ามีประเด็นตรงไหน อย่างไร ข่วยอธิบายพร้อมแนบตัวอย่างที่ชี้ให้เห็นว่าต้องการข้อมูลที่เซลล์ไหนเป็นค่าเท่าใด ด้วยเงื่อนไขใดจะได้เข้าใจตรงกันครับ ในไฟล์นั้นไม่มีสูตรที่เคยตอบไป กรุณาแนบไฟล์พร้อมสูตรมาใหม่พร้อมชี้ให้เห็นว่าผิดพลาดที่เซลล์ไหน อย่างไร กรณีไม่มีเงื่อนไขใดเพิ่มเติม สูตรเดิมที่เคยให้ไปควรจะต้องให้คำตอบได้ครับ เนื่องจากชื่อของวันที่เริ่มของแต่ละเดือนไม่เหมือนกันจึงทำให้ผลการคำนวณคลาดเคลื่อน สามารถปรับสูตรที่ AJ8 เป็นด้านล่างครับใช้งานได้ตามที่ต้องการเลยครับผม ขอบคุณมากครับอาจารย์snasui wrote: ↑Tue Jan 31, 2023 7:03 pm
=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)
เริ่มคลุมจากเซลล์ E8 จากนั้นกำหนด Conditional Formatting ด้วยสูตรตามด้านล่างครับอาจารย์ครับ ผมคลุมพื้นที่ทั้งหมดโดยเริ่มตั้งแต่ E8snasui wrote: ↑Wed Feb 01, 2023 8:04 pm
=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)
กรุณาแนบไฟล์ล่าสุดมาด้วยพร้อมอธิบายว่าได้กำหนดให้แสดงแบบใดจะได้เข้าถึงปัญหาโดยไวครับจากไฟล์งาน แถว A17 เวรวันพุธและพฤหัส ผมได้ทำแถบสีเขียวไว้คือวันที่สมาชิกท่านนี้มาสายครับ
ดูตัวอย่างจากไฟล์แนบ ผมระบายเป็นสีทึบไว้ให้ ลองไปเปลี่ยนเป็นสีหรือรูปแบบอื่นใดดูตามต้องการครับ ตัวอย่างการปรับสูตรครับ
