Page 1 of 1
การใช้สูตรหาค่า
Posted: Tue Feb 19, 2019 10:05 am
by Leng
สวัสดีครับ
พอดีผมยากทำ excel ให้คำนวญวันหยุดเช่น
นาย A มี ลาพักร้อน ลากิจ ลาป่วย อย่างละ 30 วัน
โดยผมได้ทำ column เป็น 3 column โดยแยกหัวข้อเป็น ลาพักร้อน ลากิจ ลาป่วย และทำวันที่ไว้
ผมต้องการตัดจำนวนลาพักร้อน ลากิจ ลาป่วยโดยใช้สูตรในการคำนวณเอง
สิ่งที่ต้องการคือ
ถ้านาย A ลาวันที่ 1 ใช้เป็นลาป่วยก็ให้ตัด Auto จากยอดที่ตั้งไว้(สูตรที่ผมใช้ตอนนี้ =SUM(COUNTIF(H4:AL4,"ป่วย")-G4) ) สามารถทำงานได้
แต่สิ่งที่ต้องการเพิ่มคือ ต้องการให้หักเป็น 0.5 ได้ด้วย เช่น
พิมพ์ คำว่า ป่วย ให้ตัดเป็น 1
พิมพ์ คำว่า ป่วย0.5 ให้ตัดเป็น 0.5
Re: การใช้สูตรหาค่า
Posted: Tue Feb 19, 2019 10:49 am
by snasui
ตัวอย่างสูตรครับ
AP4 คีย์
=SUM(IF(ISNUMBER(SUBSTITUTE($I4:$AM4,"พักร้อน","")+0),SUBSTITUTE($I4:$AM4,"พักร้อน","")+0))+COUNTIF(I4:AM4,"พักร้อน")-E4
Ctrl+Shift+Enter
Re: การใช้สูตรหาค่า
Posted: Tue Feb 19, 2019 11:47 am
by Bo_ry
อีกแบบ
AN4 กด Ctrl+Shift+Enter ลากไป AP4
=SUM(IFERROR(--MID($I4:$AM4&1,FIND(SUBSTITUTE(AN3,"ลา",""),$I4:$AM4)+LEN(SUBSTITUTE(AN3,"ลา","")),3),))-E4
Re: การใช้สูตรหาค่า
Posted: Tue Feb 19, 2019 12:24 pm
by puriwutpokin
อีกตัวอย่างครับที่
AN4=SUMPRODUCT((($I4:$AM4)&1=SUBSTITUTE(AN$3,"ลา","")&1)+(($I4:$AM4)=SUBSTITUTE(AN$3,"ลา","")&0.5)/2)-E4 คัดลอกไปทางขวาครับ
Re: การใช้สูตรหาค่า
Posted: Tue Feb 19, 2019 2:32 pm
by Leng
=SUM(IF(ISNUMBER(SUBSTITUTE($I4:$AM4,"พักร้อน","")+0),SUBSTITUTE($I4:$AM4,"พักร้อน","")+0))+COUNTIF(I4:AM4,"พักร้อน")-E4
จากสูตรนี้ครับ พอดีสามารถทำได้แล้ว แต่ถ้าเราไม่ต้องกด Ctrl+Shift+Enter ได้ไหม (รบกวนช่วยอธิบายสูตรหน่อยครับจะได้นำไปปรับใช้ได้ถูกต้องครับ)
Re: การใช้สูตรหาค่า
Posted: Tue Feb 19, 2019 3:44 pm
by 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 ที่ครอบอยู่นอกสุดครับ
Re: การใช้สูตรหาค่า
Posted: Wed Feb 20, 2019 8:31 am
by Leng
ขอบพระคุณมากครับ