นับวันตามเดือน

[March201711]

อยากทราบว่าถ้าจะนับจำนวนวันที่ไม่มีสี (high light สีส้มและสีเทา) และให้นับถึงสิ้นเดือนนั้นๆ สามารถใช้สูตรอะไรคะ

เช่น เดือน มกราคม มี 31 วัน นับได้ 22 วัน โดยไม่นับสีส้มและสีเทา
เดือน กุมภาพันธ์ มี 28 วัน นับได้ 19 วัน โดยไม่นับสีส้มและสีเทา

[snasui]

Excel ไม่มีฟังก์ชั่นที่ใช้นับเซลล์ที่มีสีโดยตรง หากจะทำต้องอาศัย Excel 4 Macro Function หรือ VBA ครับ

[March201711]

ทำการrecord marcoแล้ว ไม่ได้ตามที่ต้องการน่ะค่ะ ไม่มีคำสั่งให้ไม่เลือกนับเป็นสีให้หรือเปล่าคะ

[snasui]

ถ้าจะบันทึก Macro ต้องประกอบด้วยเทคนิคพวกนี้เป็นอย่างน้อยครับ

1. Filter ข้อมูลตามสีที่จะนับ
2. เขียนฟังก์ชั่น Subtotal(3,Range) เพื่อนับบรรทัดที่ได้หลังจากการ Filter ค่านี้คือผลลัพธ์ที่ต้องการ

Range หมายถึงช่วงข้อมูลที่จะนับ

[March201711]

ค่ะ ขอถามปัญหาเรื่องนับ avg ถ้าจะคำนวณที่ row35 โดยนับเดือนของวันแรกถึงวันสุดท้ายของเดือน ต้องปรับอย่างไร เพราะต้องมาเปลี่ยนที่ cell แต่ละเดือนทุกเดือนเลยค่ะ
เช่น เดือน Jan-19 มี31 วัน ค่า Average ต้องนับถึงวันที่ 31 คือ average=(b3:b33)
เดือน Feb-19 มี28 วัน ค่า Average ต้องนับถึงวันที่ 28 คือ average=(c3:c30)
เดือน Mar-19 มี31 วัน ค่า Average ต้องนับถึงวันที่ 31 คือ average=(d3:d33)

[snasui]

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

B35 คีย์

=AVERAGE(B$3:INDEX(B$3:B$33,EOMONTH(B$2,0)-B$2+1))

Enter > Copy ไปด้านขวา

[March201711]

ได้แล้วค่ะ ช่วยอธิบายสูตรได้ไหม จะได้ปรับไปใช้งานอื่นค่ะ

[snasui]

คำอธิบายตามด้านล่างครับ

จากสูตร

=AVERAGE(B$3:INDEX(B$3:B$33,EOMONTH(B$2,0)-B$2+1))

หมายถึงให้หาค่าเฉลี่ยของช่วง B$3:INDEX(B$3:B$33,EOMONTH(B$2,0)-B$2+1)

INDEX(B$3:B$33,EOMONTH(B$2,0)-B$2+1) จะให้ผลลัพธ์เป็นตำแหน่งเซลล์ใดเซลล์หนึ่งในช่วง B$3:B$33 ส่วนจะเป็นตำแหน่งไหนก็ขึ้นอยู่กับผลลัพธ์ของ EOMONTH(B$2,0)-B$2+1

EOMONTH(B$2,0)-B$2+1 หมายถึงให้หาค่าวันที่สุดท้ายของเดือนตาม B2 แล้วลบด้วยวันที่ตาม B2 แล้วบวกด้วย 1 เพื่อให้นับวันแรกเข้าไปด้วย

เลข 0 คือนับไป 0 เดือนจากวันที่ใน B2 จึงหมายถึงวันที่สุดท้ายของเดือนตาม B2 ผลลัพธ์จะเป็นวันที่

จากนั้นนำวันที่ใน B2 มาหักออกจากค่าที่ได้ ผลลัพธ์จะเป็นจำนวนที่ต่างกันของวันที่สุดท้ายของเดือนนั้นกับวันที่ 1 ของเดือนนั้น

บวกด้วย 1 เพื่อให้รวมวันแรกด้วย ไม่เช่นนั้นจะขาดไป 1 วัน ผลลัพธ์ก็จะเป็นจำนวนวันทั้งหมดของเดือนนั้น ๆ กรณีเป็นเดือน ม.ค. ผลลัพธ์คือ 31 ภาพรวมสูตรจะกลายเป็น

=AVERAGE(B$3:INDEX(B$3:B$33,31))

จากสูตร INDEX(B$3:B$33,31) หมายถึงในช่วง B$3:B$33 ให้นำเซลล์ลำดับที่ 31 มาแสดง ผลลัพธ์จะได้ B33 ภาพรวมสูตรจะได้เป็น

=AVERAGE(B$3:B$33)

หมายถึงให้หาค่าเฉลี่ยของ B3:B33

พยายามฝึกแปลสูตรด้วยการคลุมช่วงที่เราต้องการทราบผลลัพธ์ในสูตรแล้วกดแป้น F9 จะได้เข้าใจเพิ่มขึ้นครับ

[March201711]

ขอบคุณมากค่ะอาจารย์ อาจารย์มีหลักการคิดที่เป็นระบบ อยากเก่งเหมือนๆกันทุกท่านจังคะ จะฝึกหลักการคิดคำนวณต่อไปค่ะ ขอบคุณค่ะ