Snasui Excel Forum

สวัสดีครับ

ผมรบกวนขอปรึกษาการคำนวนหาค่าเฉลี่ยย้อนหลัง 10 ปี ตามไฟล์ที่แนบมานี้ครับ

จากไฟล์ที่แนบมานี้ ผมได้ใช้สูตร AVERAGE ในการคำนวนหาราคาค่า กำไร/หุ้น ย้อนหลัง 10 ปี โดย Array ของผมคือ I2:ค่าสุดท้ายทางขวาสุดของ ROW 2 ครับ

ในการกำหนดให้ excel ดึงค่าสุดท้ายของ ROW2 มาใช้ ต้องกราบขอบคุณอาจารย์สันติพงษ์ที่ชี้แนะจนสามารถทำได้ นั่นก็คือ คำสั่ง
=average(I$2:index(2:2,,match(9.99999999999999E307,2:2))) ครับ

ผมมีคำถามเพิ่มเติมครับ ปกติเวลาที่ผมหาค่าเฉลี่ยในแถว ผมมักจะคำนวนหาค่าเฉลี่ยย้อนหลัง 5 ปี และ 10 ปี แต่ปัญหาคือถ้าหากผมมีการอัพเดทข้อมูล EPS (กำไร/หุ้น) เพิ่มลงไปที่ S2, T2,.... แต่ Array ตั้งต้นยังคงประจำอยู่ที่ I2 ซึ่งถ้าหากผมระบุตัวเลข EPS ในเซลล์ S2 จะทำให้ค่าเฉลี่ยย้อนหลัง 10 ปีของผมกลายเป็นเฉลี่ย 11 ปี แทนที่จะเป็น 10 ปี

จะมีคำสั่งอะไรบ้างครับที่สามารถทำให้ Array ตั้งต้นขยับจาก I2 ตามค่าสุดท้ายทางขวาสุดออกไปเรื่อยๆ ได้ เพื่อให้การคำนวนหาค่าเฉลี่ยที่ได้นั้นเป็น 10 ปีเท่าเดิมไปตลอดครับ

กราบขอบพระคุณครับ

ลองแบบนี้ครับ

ฺB3
=AVERAGE(I$2:INDEX(2:2,,MIN(MATCH(I$1,1:1)+9,MATCH(9.99999999999999E+307,2:2))))
Enter ครับ

ลองดูแล้วครับ เอาไปใส่ที่เซลล์ B3 จากนั้นผมลองเพิ่มข้อมูล EPS ที่ช่อง S2 ออกไป

ค่าเริ่มต้นใน Array ก็ยังคงดึง I2 มาใช้เหมือนเดิมครับ

DhitiBank wrote:ลองแบบนี้ครับ

ฺB3
=AVERAGE(I$2:INDEX(2:2,,MIN(MATCH(I$1,1:1)+9,MATCH(9.99999999999999E+307,2:2))))
Enter ครับ

ผมทำได้แล้วครับ!!!

ที่ช่อง B3 ผมใส่สูตรนี้ครับ
=AVERAGE(INDEX(2:2,,MATCH(9.99999999999999E+307,2:2)-9):INDEX(2:2,,MATCH(9.99999999999999E+307,2:2)))

ก็คือผมลอกเอาสูตรที่กำหนดค่าของ ARRAY ฝั่งขวามาแล้วลบออก 9 cell ครับ

ตามไฟล์แนบนี้เลยครับ
ขอบคุณมากครับ

ขออภัยครับ ผมเข้าใจคำถามผิดครับ นึกว่าให้เริ่มตายตัวที่เซลล์ I2

=AVERAGE(OFFSET(INDEX(2:2,MATCH(9.99999999E+307,2:2)),,-9,1,10))

ไม่เป็นไรครับ กราบขอบคุณอีกครั้งสำหรับสูตรที่ให้มาใหม่นี้นะครับ

DhitiBank wrote:ขออภัยครับ ผมเข้าใจคำถามผิดครับ นึกว่าให้เริ่มตายตัวที่เซลล์ I2

=AVERAGE(OFFSET(INDEX(2:2,MATCH(9.99999999E+307,2:2)),,-9,1,10))