Page 1 of 1
การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Tue Feb 28, 2017 4:42 pm
by kitman
ผมลองใช้countif แต่ไม่เป็นตามที่ต้องการครับ
ผิดตรงไหนชี้แนะด้วยครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Tue Feb 28, 2017 4:59 pm
by DhitiBank
ช่วยบอกเงื่อนไขการนับด้วยได้ไหมครับ ว่าวันเริ่มอยู่เซลล์ไหน ต้องนับกี่วันสังเกตตรงไหน อาจารย์หรือเพื่อนๆ ที่เข้ามาช่วยจะได้เข้าใจตรงกันครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Tue Feb 28, 2017 7:09 pm
by menem
ผมลองสร้างสูตรสำหรับคำนวณช่อง P และ R ให้ใหม่ (อยู่ด้านท้าย)
ลองตรวจสอบดูครับว่าตรงกับที่ต้องการหรือไม่
หลักพื้นฐานคือ การสร้างวันที่ที่ไม่ใช่วันหยุดเอาไว้อีกคอลัมน์หนึ่ง
แล้วใช้การ Match วันที่ที่คำนวณได้ อ้างอิงจากวันหยุด ถ้ามีตรงกัน
ก็แปลว่าให้เอาวันที่ที่ไม่ใช่วันหยุดมาใช้
แต่ถ้าไม่เจอ ก็แปลว่าวันดังกล่าวสามารถใช้งานได้เลย ไม่ต้องแปลงค่าอีก
หมายเหตุ : ผมไม่ได้ใช้ CountIF นะครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Tue Feb 28, 2017 7:51 pm
by puriwutpokin
อีกแบบใช้ if และ match มาเช็คครับ ตามไฟล์แนบครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Tue Feb 28, 2017 10:16 pm
by kitman
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Tue Feb 28, 2017 10:18 pm
by kitman
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Tue Feb 28, 2017 10:32 pm
by kitman
menem wrote:ผมลองสร้างสูตรสำหรับคำนวณช่อง P และ R ให้ใหม่ (อยู่ด้านท้าย)
ลองตรวจสอบดูครับว่าตรงกับที่ต้องการหรือไม่
หลักพื้นฐานคือ การสร้างวันที่ที่ไม่ใช่วันหยุดเอาไว้อีกคอลัมน์หนึ่ง
แล้วใช้การ Match วันที่ที่คำนวณได้ อ้างอิงจากวันหยุด ถ้ามีตรงกัน
ก็แปลว่าให้เอาวันที่ที่ไม่ใช่วันหยุดมาใช้
แต่ถ้าไม่เจอ ก็แปลว่าวันดังกล่าวสามารถใช้งานได้เลย ไม่ต้องแปลงค่าอีก
หมายเหตุ : ผมไม่ได้ใช้ CountIF นะครับ
ผมสงสัยว่าทำไมช่องY9 Y10 ถึงยังเป็นวันที่ในholiday ช่องA อยู่ครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Wed Mar 01, 2017 8:45 am
by menem
ลองแก้ไขสูตร Y5 เป็น
=IFERROR(INDEX(holiday!B:B,MATCH(ROUND((R5-((N5/S5)/150))-3,0),holiday!A:A,0)),ROUND((R5-((N5/S5)/150))-3,0))
แล้ว Copy ลงล่างครับ , เพราะเดิมผมใช้ Round เพื่อปัดค่าตามหลัก 0.5 ซึ่งในตำแหน่งนั้นผลการคำนวณน่าจะเป็นวันที่ 17
แต่พอเอาค่าที่ใช้แบบไม่ปัด จะยังแสดงเป็นวันที่ 16 อยู่ เลยทำให้ผลการคำนวณออกมาไม่ตรงกันกับที่ควรจะเป็นน่ะครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Wed Mar 01, 2017 5:01 pm
by DhitiBank
อีกแบบหนึ่งครับ ไม่รู้ว่าตรงตามต้องการหรือเปล่า
P5
=MAX(IF(ISNA(MATCH(ROW(INDIRECT((R5-ROUNDUP((N5/S5)/150,0)-30)&":"&(R5-ROUNDUP((N5/S5)/150,0)-3-COUNTIFS(holiday!$A$1:$A$24,">="&(R5-ROUNDUP((N5/S5)/150,0)-3),holiday!$A$1:$A$24,"<="&'4'!R5)))),holiday!$A$1:$A$24,0)),ROW(INDIRECT((R5-ROUNDUP((N5/S5)/150,0)-30)&":"&(R5-ROUNDUP((N5/S5)/150,0)-3-COUNTIFS(holiday!$A$1:$A$24,">="&(R5-ROUNDUP((N5/S5)/150,0)-3),holiday!$A$1:$A$24,"<="&'4'!R5))))))
Ctrl+Shift+Enter
R5
=MAX(IF(ISNA(MATCH(ROW(INDIRECT((U5-M5-30)&":"&(U5-M5-3-COUNTIFS(holiday!$A$1:$A$24,">="&(U5-M5-3),holiday!$A$1:$A$24,"<="&U5)))),holiday!$A$1:$A$24,0)),ROW(INDIRECT((U5-M5-30)&":"&(U5-M5-3-COUNTIFS(holiday!$A$1:$A$24,">="&(U5-M5-3),holiday!$A$1:$A$24,"<="&U5))))))
Ctrl+Shift+Enter
คัดลอกทั้งสองสูตรลงล่างครับ
2017-03-01 16_57_48-Microsoft Excel - countif.xlsx.png
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Thu Mar 02, 2017 4:41 pm
by kitman
menem wrote:ลองแก้ไขสูตร Y5 เป็น
=IFERROR(INDEX(holiday!B:B,MATCH(ROUND((R5-((N5/S5)/150))-3,0),holiday!A:A,0)),ROUND((R5-((N5/S5)/150))-3,0))
แล้ว Copy ลงล่างครับ , เพราะเดิมผมใช้ Round เพื่อปัดค่าตามหลัก 0.5 ซึ่งในตำแหน่งนั้นผลการคำนวณน่าจะเป็นวันที่ 17
แต่พอเอาค่าที่ใช้แบบไม่ปัด จะยังแสดงเป็นวันที่ 16 อยู่ เลยทำให้ผลการคำนวณออกมาไม่ตรงกันกับที่ควรจะเป็นน่ะครับ
แต่จำนวนวันทำงานมันไม่ได้ครับ เหมือนมันนับรวมวันholidayไปด้วย
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Thu Mar 02, 2017 5:06 pm
by kitman
countif.xlsx
ลืมแนบไฟล์ครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Fri Mar 03, 2017 9:36 am
by menem
kitman wrote:menem wrote:ลองแก้ไขสูตร Y5 เป็น
=IFERROR(INDEX(holiday!B:B,MATCH(ROUND((R5-((N5/S5)/150))-3,0),holiday!A:A,0)),ROUND((R5-((N5/S5)/150))-3,0))
แล้ว Copy ลงล่างครับ , เพราะเดิมผมใช้ Round เพื่อปัดค่าตามหลัก 0.5 ซึ่งในตำแหน่งนั้นผลการคำนวณน่าจะเป็นวันที่ 17
แต่พอเอาค่าที่ใช้แบบไม่ปัด จะยังแสดงเป็นวันที่ 16 อยู่ เลยทำให้ผลการคำนวณออกมาไม่ตรงกันกับที่ควรจะเป็นน่ะครับ
แต่จำนวนวันทำงานมันไม่ได้ครับ เหมือนมันนับรวมวันholidayไปด้วย
การใช้สูตร Round ตรงนี้ ต้องขึ้นกับวัตถุประสงค์ในการใช้ครับว่า
ต้องการ "วันที่มากกว่า" หรือ "วันที่น้อยกว่า" เพราะเท่าที่ประเมิน
น่าจะเป็นวันที่ต้องใช้เพื่อเตรียมขั้นตอนการผลิตใช่ไหมครับ ?
ถ้าเป็นเช่นนั้น ก็แปลว่าต้องการจำนวนวันที่มากกว่า (กรณีคำนวณออกมาแล้ว
เป็นเศษของวัน) ดังนั้นอาจจะปัดตามสูตรเดิมที่ผมใช้ไม่ได้ เพราะ
อิงการปัดตาม 0.5 ลองเปลี่ยนจาก ROUND เป็น ROUNDDOWN แทนครับ
หมายเหตุ : ถ้าคำนวณแล้วไม่ตรง ขอช่วยระบุวันที่คิดว่าถูกต้อง พร้อมหลักการคิดมาด้วยครับ
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Fri Mar 03, 2017 10:27 am
by menem
ดูจากในคำอธิบายในไฟล์ที่แนบมาใหม่ แปลว่าผมเข้าใจผิดไปไกลเลยครับ
ลองดูไฟล์นี้อีกทีครับว่าใช่อย่างที่ต้องการหรือไม่
หมายเหตุ : ผมคำนวนต่างจากที่ระบุไป 1 วันนะครับ
แปลว่า วันเริ่มผลิต จนถึงวันที่ส่งมอบ จะต้องมีมากกว่า จำนวนวันที่ใช้ในการผลิตจริงใช่ไหมครับ ?
เพราะเท่าที่ดู เหมือนว่าจะเป็น วันเริ่มผลิต + วันที่ใช้ในการผลิต (นับรวมวันเริ่มผลิต) จะต้องเสร็จ
ก่อนวันส่งมอบ 1 วัน
หมายเหตุ 2 :
สูตร Array นี้ผมได้มาจาก
https://exceljet.net/formula/get-nth-ma ... ndex-match
(ผมไม่ค่อยรู้เรื่องเกี่ยวกับ Array Formula เท่าไหร่นะครับ ถ้าจะให้อธิบาย
อาจจะต้องรบกวนท่านอื่นมาช่วยอธิบายอีกที)
Re: การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday
Posted: Fri Mar 03, 2017 2:54 pm
by DhitiBank
2017-03-03 14_48_02-Microsoft Excel - countif.xlsx.png
หลักการคิดแบบนี้นี่เอง ถ้าอย่างนั้นแนะนำอีกสูตรครับ (ได้แนวคิดจากลิ้งค์ที่คุณ menem ให้มาด้วยครับ ขอบคุณครับ)
R5
=LARGE(IF(ISNA(MATCH(ROW(INDIRECT(U5-30&":"&U5-1)),holiday!$A$2:$A$19,0)),ROW(INDIRECT(U5-30&":"&U5-1))),11)
กด
Ctrl+Shift ค้างไว้แล้วกด
Enter >> คัดลอกลงล่าง
หากตามสูตรนี้ เมื่อกำหนดวันส่งของ 25/4/2017 สูตรจะคำนวณได้วันเริ่มผลิตคือ 7/4/2017 ซึ่งหากนับจริงๆ ก็น่าจะเป็นวันนี้ไม่ใช่เหรอครับ ทำไมถึงเป็นวันที่ 5/4/2017 (ด้านล่างนี้เป็นวันที่ที่ได้จากการคำนวณครับ วันไหนตรงกับวันหยุดจะขึ้นว่า False และเมื่อนับย้อนมา 11 วันจากวันที่ 25 ก็น่าจะเป็นวันที่ 7)
{"26/3/2017";"27/3/2017";"28/3/2017";"29/3/2017";"30/3/2017";"31/3/2017";"1/4/2017";"2/4/2017";"3/4/2017";"4/4/2017";"5/4/2017";"6/4/2017";
"7/4/2017";"8/4/2017";"9/4/2017";"FALSE";"FALSE";"FALSE";"FALSE";"FALSE";"FALSE";"FALSE";
"17/4/2017";"18/4/2017";"19/4/2017";"20/4/2017";"21/4/2017";"22/4/2017";"23/4/2017";"24/4/2017"}