การใช้สูตรหาค่า

[Leng]

สวัสดีครับ
พอดีผมยากทำ excel ให้คำนวญวันหยุดเช่น
นาย A มี ลาพักร้อน ลากิจ ลาป่วย อย่างละ 30 วัน
โดยผมได้ทำ column เป็น 3 column โดยแยกหัวข้อเป็น ลาพักร้อน ลากิจ ลาป่วย และทำวันที่ไว้
ผมต้องการตัดจำนวนลาพักร้อน ลากิจ ลาป่วยโดยใช้สูตรในการคำนวณเอง
สิ่งที่ต้องการคือ
ถ้านาย A ลาวันที่ 1 ใช้เป็นลาป่วยก็ให้ตัด Auto จากยอดที่ตั้งไว้(สูตรที่ผมใช้ตอนนี้ =SUM(COUNTIF(H4:AL4,"ป่วย")-G4) ) สามารถทำงานได้
แต่สิ่งที่ต้องการเพิ่มคือ ต้องการให้หักเป็น 0.5 ได้ด้วย เช่น
พิมพ์ คำว่า ป่วย ให้ตัดเป็น 1
พิมพ์ คำว่า ป่วย0.5 ให้ตัดเป็น 0.5

[snasui]

ตัวอย่างสูตรครับ

AP4 คีย์

=SUM(IF(ISNUMBER(SUBSTITUTE($I4:$AM4,"พักร้อน","")+0),SUBSTITUTE($I4:$AM4,"พักร้อน","")+0))+COUNTIF(I4:AM4,"พักร้อน")-E4

Ctrl+Shift+Enter

[Bo_ry]

อีกแบบ
AN4 กด Ctrl+Shift+Enter ลากไป AP4
=SUM(IFERROR(--MID($I4:$AM4&1,FIND(SUBSTITUTE(AN3,"ลา",""),$I4:$AM4)+LEN(SUBSTITUTE(AN3,"ลา","")),3),))-E4

[puriwutpokin]

อีกตัวอย่างครับที่
AN4=SUMPRODUCT((($I4:$AM4)&1=SUBSTITUTE(AN$3,"ลา","")&1)+(($I4:$AM4)=SUBSTITUTE(AN$3,"ลา","")&0.5)/2)-E4 คัดลอกไปทางขวาครับ

[Leng]

=SUM(IF(ISNUMBER(SUBSTITUTE($I4:$AM4,"พักร้อน","")+0),SUBSTITUTE($I4:$AM4,"พักร้อน","")+0))+COUNTIF(I4:AM4,"พักร้อน")-E4
จากสูตรนี้ครับ พอดีสามารถทำได้แล้ว แต่ถ้าเราไม่ต้องกด Ctrl+Shift+Enter ได้ไหม (รบกวนช่วยอธิบายสูตรหน่อยครับจะได้นำไปปรับใช้ได้ถูกต้องครับ)

[snasui]

กรณีต้องการ Enter อย่างเดียวใช้ฟังก์ชั่น Aggregate มาช่วยได้ครับ

=AGGREGATE(15,6,SUBSTITUTE($I4:$AM4,"พักร้อน","")+0,ROW(INDIRECT("1:31")))+COUNTIF(I4:AM4,"พักร้อน")-E4

ส่วนคำอธิบายสูตร =SUM(IF(ISNUMBER(SUBSTITUTE($I4:$AM4,"พักร้อน","")+0),SUBSTITUTE($I4:$AM4,"พักร้อน","")+0))+COUNTIF(I4:AM4,"พักร้อน")-E4

หมายถึง ถ้า SUBSTITUTE($I4:$AM4,"พักร้อน","")+0 มีค่าเป็นตัวเลข ให้รวมยอดของ SUBSTITUTE($I4:$AM4,"พักร้อน","")+0

จาก SUBSTITUTE($I4:$AM4,"พักร้อน","") หมายถึง ให้เปลี่ยนคำว่า "พักร้อน" ของแต่ละเซลล์ในช่วง $I4:$AM4 ให้เป็นค่าว่าง หากคำนั้นมีคำว่า "พักร้อน" ก็จะเหลือแค่ตัวเลขที่ต่อท้ายเข้ามา แต่หากไม่มีคำว่า "พักร้อน" ก็จะยังคงเป็นค่าเดิม

จากนั้นนำผลลัพธ์ของ SUBSTITUTE($I4:$AM4,"พักร้อน","") มาบวกด้วย 0 เพื่อแปลงแต่ละค่าให้กลับเป็นตัวเลข หลังจากนั้นใช้ Insumber เข้าไปครอบอีกทีเพื่อตรวจสอบว่าแต่ละค่าที่ได้เป็นตัวเลขหรือไม่ ถ้าเป็นตัวเลขจะมีผลลัพธ์เป็น True ถ้าไม่ใช่จะมีผลลัพธ์เป็น False

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

[Leng]

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