Snasui Excel Forum

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

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

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

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

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

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

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

ค่ะ ขอถามปัญหาเรื่องนับ 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)

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

B35 คีย์

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

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

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

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

จากสูตร

=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 จะได้เข้าใจเพิ่มขึ้นครับ

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