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

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

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

ชุดคำสั่ง : SendData

image018

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

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

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

  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 ผิดพลาดตามไปด้วย

Related Articles

© Copyright 1999

สงวนลิขสิทธิ์ตามกฎหมาย

ห้ามนำข้อความหรือส่วนหนึ่งส่วนใดของบทความหรือวิดีโอหรือรูปภาพไปใช้เพื่อการค้าขาย หรือเพื่อประโยชน์ส่วนตัว

อนญาตให้นำไปใช้เพื่อสาธารณประโยชน์โดยขอให้ระบุที่มาและชื่อผู้เขียนกำกับไว้ด้วยเสมอ

ลิงก์เว็บ Excel Expert Training

เว็บสำหรับ เรียนออนไลน์

เว็บสำหรับ เรียนแบบกลุ่ม-ส่วนตัว

ติดตามข่าวสารได้จาก facebook

ถามปัญหาได้ที่ กลุ่มคนรัก Excel

และไลน์กลุ่ม Excel Expert Group

ที่อยู่และการติดต่อ

สมเกียรติ ฟุ้งเกียรติ 7/1 รามคำแหง ซอย 35 หัวหมาก บางกะปิ กทม 10240 โทร 097-140-5555, 02-718-9331

Excel@ExcelExpertTraining.com

sfk234x234