เปลี่ยนวันที่เองโดยใช้ marco

[March201711]

คือต้องเข้ามาปลี่ยนข้อมูลใน module code vba เองทุกครั้งถ้าติดวันหยุด มีวิธีปรับสูตรอย่างไรให้ vba รู้ได้เองโดยไม่ต้องเข้าไปแก้ใน module ถ้าติดวันหยุด หรือวันหยุดทางราชการตามปีปฏิทิน ต้องกำหนดตัวแปรอย่างไร อย่างเอกสารที่แนบเป็นการเปลี่ยนข้อมูลเมื่อวานมาเป็นข้อมูลปัจจุบัน ลบวันหยุด
เช่นถ้าเป็นวันจันทร์ ต้องปรับเปลี่ยนเองโดย ลบ 2 เอง เพราะใน code vba เป็นข้อมูลวันศุกร์อยู่ พอถึงวันอังคารต้องเข้าไปแก้ code vba เอง โดยเอาลบ 2ออกคะ

[snasui]

แนบไฟล์ที่มี Macro มาด้วยจะได้ช่วยทดสอบได้ครับ

[March201711]

ตามเอกสารแนบค่ะ

[snasui]

งานลักษณะนี้ใช้สูตรมาช่วยได้ ตัวอย่างสูตรที่ D15 ครับ

=C15-LOOKUP(WEEKDAY(TEXT(C15,"0000\/00\/00"),2),{0,6,7},{0,1,2})

Enter > Copy ลงด้านล่าง

แต่ดูเหมือนจะยังไม่เพียงพอ เพราะไม่มีข้อมูลของวันหยุดนักขัตฤกษ์หรืออื่น ๆ ซึ่งจำเป็นจะต้อง List รายการแยกเอาไว้ต่างหาก จากนั้นค่อยนำมาใช้ประกอบกับสูตรนี้ครับ

[March201711]

ได้สร้าง sheet เพิ่มเป็น Calendar สีแดงเป็นวันหยุดเสาร์อาทิตย์และวันหยุดตามปีปฏิทินคะ อยากให้ vba จับข้อมูลตามวันยุดแล้วแสดงวันให้ทราบได้เลย

[snasui]

สีแดงเป็นวันหยุดเสาร์อาทิตย์และวันหยุดตามปีปฏิทินคะ

ที่ถูกคือ

สีแดงเป็นวันหยุดเสาร์อาทิตย์และวันหยุดตามปีปฏิทินค่ะ

กรณีของวันหยุดให้ List มาเฉพาะวันหยุดเรียงลงด้านล่างต่อเนื่องกันไป ไม่เรียงเป็นรายเดือนไปด้านขวา จะเดือนใดก็แล้วแต่ให้เรียงลงด้านล่างทั้งหมดครับ

[March201711]

ตามเอกสารแนบคะ

[snasui]

ตามเอกสารแนบคะ

ช่วยโพสต์มาใหม่แล้วแก้คำลงท้ายมาเป็น "ค่ะ" ด้วยครับ

[March201711]

ตามเอกสารแนบค่ะ

[snasui]

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ D15 คีย์สูตรเพื่อเป็นคอลัมน์ช่วย
   =TEXT(C15,"0000\/00\/00")+0
   Enter > Copy ลงด้านล่าง
2. ที่ E15 คีย์สูตรเพื่อเป็นคอลัมน์ช่วย
   =MAX(IF(ISNA(MATCH(ROW(INDIRECT(D15-7&":"&D15)),Calendar!$A$4:$A$25,0)),ROW(INDIRECT(D15-7&":"&D15))))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. ที่ F15 คีย์สูตรเพื่อเป็นคำตอบ
   =TEXT(E15-LOOKUP(WEEKDAY(E15,2),{0,6,7},{0,1,2}),"yyyymmdd")
   Enter > Copy ลงด้านล่าง

!

Note: Ctrl+Shift+Enter เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

[March201711]

ทำตามที่อาจารย์บอกแล้ว ยังไม่ได้ค่ะ

[snasui]

คีย์สูตรในเซลล์ E15 ไม่ถูกต้อง ทบทวนการเขียนสูตรใหม่ทุกอักขระครับ

นอกจากนี้ในชีต Calendar จะต้องรวมวันหยุดเสาร์ อาทิตย์เอาไว้ด้วยครับ

[March201711]

อาจารย์ช่วยอธิบายสูตรที่มีเครื่องหมาย \/ หมายถึงอะไรคะ เช่น
TEXT(C15,"0000\/00\/00")+0
และ Isna กับ iferror ต่างกันอย่างไรคะ
=MAX(IF(ISNA(MATCH(ROW(INDIRECT(D15-7&":"&D15)),Calendar!$A$4:$A$25,0)),ROW(INDIRECT(D15-7&":"&D15)))

[snasui]

อาจารย์ช่วยอธิบายสูตรที่มีเครื่องหมาย \/ หมายถึงอะไรคะ เช่น
TEXT(C15,"0000\/00\/00")+0

เครื่องหมาย \ ในฟังก์ชั่น Text ในส่วนประกอบที่ใช้กำหนดรูปแบบหรือในการกำหนดรูปแบบให้กับค่าในเซลล์มีค่าเทียบเท่ากัน หมายถึง ให้แสดงอักขระที่ตามหลังเครื่องหมาย \

การคีย์ "0000\/00\/00" หมายถึง ให้แสดงผลเป็น 0000/00/00 เป็นการบังคับให้แสดงเครื่องหมาย / วัตถุประสงค์หลักมีเท่านี้ เมื่อแปลงออกมาแล้วจะเป็นวันเดือนปีที่ Excel รู้จักและสามารถนำไปใช้ต่อได้ครับ

ส่วนการบวก 0 ก็เพื่อแปลง Text ที่ได้จากการกำหนดค่าด้วยฟังก์ชั่น Text ให้กับมาเป็น Number

และ Isna กับ iferror ต่างกันอย่างไรคะ

Isna เป็นการตรวจสอบว่าค่าที่ได้เป็นค่าผิดพลาดเป็น #N/A หรือไม่ เช็คเฉพาะ #N/A เท่านั้นหากเป็นค่า #N/A ผลลัพธ์จะเป็น True ไม่เช่นนั้นผลลัพธ์จะเป็น False

Iferror เป็นฟังก์ชั่นสำหรับแปลงค่า Error ทุกชนิดเป็นค่าใด ๆ ตามต้องการ หากไม่ Error ให้แสดงค่าผลัพธ์ที่ได้นั้นตามปกติครับ

[March201711]

ขอบคุณอาจารย์ที่อธิบายให้หายสงสัยค่ะ