การกำหนดวันที่ไม่ให้ตรงกับวันที่กำหนดไว้ในholiday

[kitman]

ผมลองใช้countif แต่ไม่เป็นตามที่ต้องการครับ
ผิดตรงไหนชี้แนะด้วยครับ

[DhitiBank]

ช่วย​บอก​เงื่อน​ไข​การ​นับ​ด้วย​ได้​ไหม​ครับ​ ว่า​วัน​เริ่ม​อยู่​เซลล์​ไหน​ ต้อง​นับ​กี่​วัน​สังเกต​ตรง​ไหน​​ อาจารย์​หรือ​เพื่อน​ๆ ที่​เข้ามา​ช่วย​จะ​ได้​เข้าใจ​ตรง​กัน​ครับ​

[menem]

ผมลองสร้างสูตรสำหรับคำนวณช่อง P และ R ให้ใหม่ (อยู่ด้านท้าย)
ลองตรวจสอบดูครับว่าตรงกับที่ต้องการหรือไม่

หลักพื้นฐานคือ การสร้างวันที่ที่ไม่ใช่วันหยุดเอาไว้อีกคอลัมน์หนึ่ง
แล้วใช้การ Match วันที่ที่คำนวณได้ อ้างอิงจากวันหยุด ถ้ามีตรงกัน
ก็แปลว่าให้เอาวันที่ที่ไม่ใช่วันหยุดมาใช้
แต่ถ้าไม่เจอ ก็แปลว่าวันดังกล่าวสามารถใช้งานได้เลย ไม่ต้องแปลงค่าอีก

หมายเหตุ : ผมไม่ได้ใช้ CountIF นะครับ

[puriwutpokin]

อีกแบบใช้ if และ match มาเช็คครับ ตามไฟล์แนบครับ

[kitman]

ผมลองสร้างสูตรสำหรับคำนวณช่อง P และ R ให้ใหม่ (อยู่ด้านท้าย)
ลองตรวจสอบดูครับว่าตรงกับที่ต้องการหรือไม่

หลักพื้นฐานคือ การสร้างวันที่ที่ไม่ใช่วันหยุดเอาไว้อีกคอลัมน์หนึ่ง
แล้วใช้การ Match วันที่ที่คำนวณได้ อ้างอิงจากวันหยุด ถ้ามีตรงกัน
ก็แปลว่าให้เอาวันที่ที่ไม่ใช่วันหยุดมาใช้
แต่ถ้าไม่เจอ ก็แปลว่าวันดังกล่าวสามารถใช้งานได้เลย ไม่ต้องแปลงค่าอีก

หมายเหตุ : ผมไม่ได้ใช้ CountIF นะครับ

ตรงกับใจเป๊ะเลยครับ ขอบคุณมากๆครับ

[kitman]

อีกแบบใช้ if และ match มาเช็คครับ ตามไฟล์แนบครับ

ขอบคุณมากๆครับ ผมเอาวิธีนี้ไปใช้ต่อได้อีกเยอะเลยครับ

[kitman]

ผมลองสร้างสูตรสำหรับคำนวณช่อง P และ R ให้ใหม่ (อยู่ด้านท้าย)
ลองตรวจสอบดูครับว่าตรงกับที่ต้องการหรือไม่

หลักพื้นฐานคือ การสร้างวันที่ที่ไม่ใช่วันหยุดเอาไว้อีกคอลัมน์หนึ่ง
แล้วใช้การ Match วันที่ที่คำนวณได้ อ้างอิงจากวันหยุด ถ้ามีตรงกัน
ก็แปลว่าให้เอาวันที่ที่ไม่ใช่วันหยุดมาใช้
แต่ถ้าไม่เจอ ก็แปลว่าวันดังกล่าวสามารถใช้งานได้เลย ไม่ต้องแปลงค่าอีก

หมายเหตุ : ผมไม่ได้ใช้ CountIF นะครับ

ผมสงสัยว่าทำไมช่องY9 Y10 ถึงยังเป็นวันที่ในholiday ช่องA อยู่ครับ

[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 อยู่ เลยทำให้ผลการคำนวณออกมาไม่ตรงกันกับที่ควรจะเป็นน่ะครับ

[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

[kitman]

ลองแก้ไขสูตร 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ไปด้วย

[kitman]

countif.xlsx

ลืมแนบไฟล์ครับ

[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 อยู่ เลยทำให้ผลการคำนวณออกมาไม่ตรงกันกับที่ควรจะเป็นน่ะครับ

แต่จำนวนวันทำงานมันไม่ได้ครับ เหมือนมันนับรวมวันholidayไปด้วย

การใช้สูตร Round ตรงนี้ ต้องขึ้นกับวัตถุประสงค์ในการใช้ครับว่า
ต้องการ "วันที่มากกว่า" หรือ "วันที่น้อยกว่า" เพราะเท่าที่ประเมิน
น่าจะเป็นวันที่ต้องใช้เพื่อเตรียมขั้นตอนการผลิตใช่ไหมครับ ?

ถ้าเป็นเช่นนั้น ก็แปลว่าต้องการจำนวนวันที่มากกว่า (กรณีคำนวณออกมาแล้ว
เป็นเศษของวัน) ดังนั้นอาจจะปัดตามสูตรเดิมที่ผมใช้ไม่ได้ เพราะ
อิงการปัดตาม 0.5 ลองเปลี่ยนจาก ROUND เป็น ROUNDDOWN แทนครับ

หมายเหตุ : ถ้าคำนวณแล้วไม่ตรง ขอช่วยระบุวันที่คิดว่าถูกต้อง พร้อมหลักการคิดมาด้วยครับ

[menem]

ดูจากในคำอธิบายในไฟล์ที่แนบมาใหม่ แปลว่าผมเข้าใจผิดไปไกลเลยครับ

ลองดูไฟล์นี้อีกทีครับว่าใช่อย่างที่ต้องการหรือไม่

หมายเหตุ : ผมคำนวนต่างจากที่ระบุไป 1 วันนะครับ
แปลว่า วันเริ่มผลิต จนถึงวันที่ส่งมอบ จะต้องมีมากกว่า จำนวนวันที่ใช้ในการผลิตจริงใช่ไหมครับ ?
เพราะเท่าที่ดู เหมือนว่าจะเป็น วันเริ่มผลิต + วันที่ใช้ในการผลิต (นับรวมวันเริ่มผลิต) จะต้องเสร็จ
ก่อนวันส่งมอบ 1 วัน

หมายเหตุ 2 :
สูตร Array นี้ผมได้มาจาก
https://exceljet.net/formula/get-nth-ma ... ndex-match
(ผมไม่ค่อยรู้เรื่องเกี่ยวกับ Array Formula เท่าไหร่นะครับ ถ้าจะให้อธิบาย
อาจจะต้องรบกวนท่านอื่นมาช่วยอธิบายอีกที)

[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"}