สร้าง RangeName ด้วยสูตร Index ได้หรือไม่

[bank9597]

สวัสดีครับ พอดีว่าผมสงสัยสูตร Index ที่มี 2 ไวยกรณ์ คือ

1. INDEX(array, row_num, [column_num])
2. INDEX( reference, row_number, [column_number], [area_number] )

ไม่ทราบว่า อย่างที่ 2 เราสามารถนำมาทำเป็น RangeName แทนสูตร Offset ที่ใช้ๆกันอยู่ได้หรือไม่

เนื่องจากผมได้สร้าง RangeName แบบ อาร์เรย์ ทำให้การทำงานของโปรแกรมช้าลงทันที ทั้งๆที่ข้อมูลมีปริมาณน้อย

แต่ก็รูู้ได้ว่าเกิดจากการอ้างอิงทั้งคอลัมน์ ($A:$A) ผมจึงเปลี่ยนการอ้างอิงใหม่ โปรแกรมจึงทำงานเร็วดั่งเดิม

แต่ทีนี้ผมอยากทราบว่า มีสูตรอื่นอีกหรือไม่ ที่สามารถนำมาทำ RangeName ได้ และเร็วกว่าการใช้สูตร Offset

ขอบคุณครับ

[snasui]

เนื่องจากผมได้สร้าง RangeName แบบ อาร์เรย์ ทำให้การทำงานของโปรแกรมช้าลงทันที ทั้งๆที่ข้อมูลมีปริมาณน้อย

ปกติ Range Name สามารถสร้างเป็น Array ได้อยู่แล้วครับ แต่เราไม่สามารถใช้สูตร Array ในการสร้าง Range Name หรือ Data Validation

ตามที่ถามมาลองปรับสูตรสำหรับการสร้าง Range Name เป็นด้านล่างครับ

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(CHAR(255),Sheet1!$A:$A))

[bank9597]

ปกติ Range Name สามารถสร้างเป็น Array ได้อยู่แล้วครับ

อ่อครับ

สูตร =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(CHAR(255),Sheet1!$A:$A)) จะอ้างอิงทั้งคอลัมน์ (ไม่ใช้ Offset ดังที่ถามไว้ข้างต้น)

ผมขออนุญาตถามต่อน่ะครับ

ผมจะปรับสูตร Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(CHAR(255),Sheet1!$A:$A)) อย่างไรให้สูตรอ้างอิงช่วงข้อมูล A12:A18 โดยมีเงื่อนไขว่า

ให้แสดงรหัสที่ย้อนหลังไป 5 วัน เช่นวันนี้วันที่ 16 ย้อนไป 5 วัน ก็คือวันที่ 11 นั่นเอง
ผมได้ไฮไลต์ไว้แล้วดังนี้ สีเหลืองคือ ช่วงข้อมูลที่ต้องการ , สีฟ้าคือ ช่วงข้อมูลที่ตรงกับเงื่อนไข

เพิ่มเติม:
เบื้องต้นผมปรับสูตรเป็น
=INDEX(Sheet1!$A:$A,MATCH(TODAY()-5,Sheet1!$B:$B)):INDEX(Sheet1!$A:$A,MATCH(CHAR(255),Sheet1!$A:$A))

ยังไม่ถูกต้องเลยครับ

[snasui]

สามารถปรับ Range Name เป็นตามด้านล่างครับ

Code: Select all

=INDEX(Sheet1!$A:$A,MATCH(CHAR(255),Sheet1!$A:$A)):INDEX(Sheet1!$A:$A,MATCH(INDEX(Sheet1!$B:$B,MATCH(CHAR(255),Sheet1!$A:$A))-5,Sheet1!$B:$B,0))

สูตร =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(CHAR(255),Sheet1!$A:$A)) จะอ้างอิงทั้งคอลัมน์ (ไม่ใช้ Offset ดังที่ถามไว้ข้างต้น)

สูตรนี้แม้จะอ้างทั้งคอลัมน์หรือทั้ง Worksheet ก็สามารถทำงานได้เร็วเพราะไม่มีสภาพเป็น Volatile ความช้าหรือเร็วอยู่ที่ฟังก์ชั่น Match ไม่ได้ขึ้นกับฟังก์ชั่น Index สำหรับฟังก์ชั่น Match ที่นำมาใช้กับสูตรนี้เป็นการ Match แบบ Binary Search ไม่ใช่เป็นแบบ Extract Match (แบบตรงตัว) จึงทำงานได้เร็วมาก

[bank9597]

ขอบคุณมากครับ กระจ่างชัดเลย

[bank9597]

สูตรนี้แม้จะอ้างทั้งคอลัมน์หรือทั้ง Worksheet ก็สามารถทำงานได้เร็วเพราะไม่มีสภาพเป็น Volatile ความช้าหรือเร็วอยู่ที่ฟังก์ชั่น Match ไม่ได้ขึ้นกับฟังก์ชั่น Index สำหรับฟังก์ชั่น Match ที่นำมาใช้กับสูตรนี้เป็นการ Match แบบ Binary Search ไม่ใช่เป็นแบบ Extract Match (แบบตรงตัว) จึงทำงานได้เร็วมาก

นำไปใช้กับไฟล์ตัวจริงแล้วครับ ทำงานได้เร็วจริงๆ ตามที่อาจารย์ได้กล่าวไว้ครับ

ปกติไฟล์ที่ใช้ทำงาน จะมี VBA ค่อนข้างเยอะ ซึ่งหากมีสูตรอาร์เรย์ หรือ Range Name ที่ต้องประมวลผลสูง จะทำให้การทำงานของ VBA นั้นช้าลงอย่างเห็นได้ชัดเจนครับ