วิธีส่งข้อมูลจากตารางที่มีขนาดไม่แน่นอน

ตัวอย่างที่ผ่านมาเป็นการรับส่งข้อมูลเพียงเซลล์เดียว ซึ่งสามารถเลือกใช้สูตร Index หรือ Offset ก็ได้ แต่ถ้าต้องการรับส่งข้อมูลจากตารางที่มีขนาดตั้งแต่ 2 เซลล์ขึ้นไป ต้องเลือกใช้สูตร Offset แบบเต็ม เพราะสูตร Offset นี้เท่านั้นที่สามารถกำหนดส่วนของความสูงและความกว้างของตาราง

เนื่องจากตารางข้อมูลโดยทั่วไป มีความกว้างคงที่เท่ากับจำนวน Field ซึ่งแยกเป็น column เก็บข้อมูลแต่ละเรื่อง แต่ความสูงจะเพิ่มหรือลดตามจำนวนรายการ จึงทำให้เฉพาะความสูงของตารางเท่านั้นที่มีขนาดเปลี่ยนแปลง จึงขอให้สังเกตว่า เราจะใช้สูตร CountA เพื่อคำนวณหาความสูงให้กับสูตร Offset

ชุดคำสั่ง : SendData

สิ่งที่ต้องการ

ส่งข้อมูลเท่าที่มีอยู่จากตารางด้านซ้ายไปเก็บไว้ในตารางด้านขวา ซึ่งจำนวนรายการข้อมูลที่มีนั้นอาจเพิ่มหรือลดก็ได้

ขั้นตอนการสร้างงาน

1. สร้าง Range Name ชื่อ Ref1 ให้กับเซลล์ B3
2. สร้าง Range Name ชื่อ Ref2 ให้กับเซลล์ G3
3. สร้าง Formula Name ชื่อ Source ให้เป็นสูตร
   =OFFSET( Ref1, 0, 0, COUNTA($B:$B)-1, 3 )
4. สร้าง Formula Name ชื่อ Target ให้เป็นสูตร
   =OFFSET( Ref2, 0, 0, COUNTA($B:$B)-1, 3 )

วิเคราะห์สูตร

Source
=OFFSET( Ref1, 0, 0, COUNTA($B:$B)-1, 3 )

Target
=OFFSET( Ref2, 0, 0, COUNTA($B:$B)-1, 3 )

เนื่องจากตำแหน่งเซลล์หัวมุมของตารางทั้งคู่ อยู่ตรงกับตำแหน่งของ Ref1 และ Ref2 ดังนั้นจึงกำหนดตำแหน่ง row และ column ถัดไป เท่ากับ 0

สูตรทั้งคู่กำหนดค่าความสูงเท่ากับ COUNTA($B:$B)-1 โดยใช้ข้อมูลใน column ของ Id เป็นหลักในการนับ แต่เนื่องจากมีเซลล์คำว่า Id อยู่ใน column นี้ด้วย จึงต้องลบ 1 ออกจาก COUNTA($B:$B) ส่วนความกว้างของตาราง กำหนดให้มีขนาดคงที่เท่ากับ 3 column

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

นอกจากนั้นข้อมูล Id ต้องบันทึกในเซลล์ติดต่อกันไป (ห้ามเว้นเซลล์ว่างไว้) และห้ามบันทึกค่าอื่นในเซลล์ใดๆบน column B ที่ไม่เกี่ยวข้องกับรหัส เพราะจะทำให้สูตร CountA นับจำนวนรายการผิด ส่งผลให้ค่าความสูงที่ส่งต่อไปใช้ในสูตร Offset ผิดพลาดตามไปด้วย