วิธีเลือกส่งข้อมูลตามลักษณะรายการเก่าใหม่

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

ชุดคำสั่ง : SendData

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

ส่งข้อมูลที่กรอกลงในเซลล์ B3:D3 ไปเก็บในตารางด้านขวา โดยพิจารณาจากรหัส Id ที่กรอกในเซลล์ B3 ถ้าเป็นรหัส Id a002 ซึ่งเป็นรหัสเก่า ให้จัดส่งข้อมูลไปบันทึกทับรายการเดิมของรหัส a002 แต่ถ้าเป็นรหัสใหม่ที่ไม่เคยมีอยู่ในตารางด้านขวา เช่น รหัส Id a004 ให้จัดส่งข้อมูลไปบันทึกต่อท้ายรายการในตารางขวา

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

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

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

Target
=OFFSET( Ref,
   IF( COUNTIF( Id, $B$3 )>=1,
        MATCH( $B$3, Id, 0 )-1,
        COUNTA( $G:$G )-1),
   0, 1, 3 )

ขอให้สังเกตเลข 0, 1, 3 ที่อยู่ด้านท้ายของสูตร Offset ก่อน

• เลข 0 กำหนดตำแหน่งของข้อมูล ให้เริ่มในแนว column เดียวกันกับ Ref
• เลข 1 คือ ความสูงของรายการข้อมูล ซึ่งต้องมีความสูงคงที่ 1 row เสมอ
• เลข 3 คือ ความกว้างของรายการ ซึ่งประกอบด้วย Id, Name, Amount จึงกำหนดให้กว้าง 3 column คงที่

ส่วนของสูตร IF( COUNTIF(Id,$B$3)>=1, MATCH($B$3,Id,0)-1, COUNTA($G:$G)-1)

• COUNTIF(Id,$B$3) ทำหน้าที่นับจำนวนรายการที่มี Id ที่กรอกใน B3
• ถ้า COUNTIF(Id,$B$3)>=1 เป็นจริง แสดงว่ามีรหัสรายการบันทึกอยู่แล้ว จึงให้ส่งข้อมูลไปบันทึกทับรายการเดิม ณ ตำแหน่งที่คำนวณจาก MATCH($B$3,Id,0)-1
• ถ้า COUNTIF(Id,$B$3)>=1 เป็นเท็จ แสดงว่าเป็นรหัส Id ของรายการใหม่ จึงให้ส่งข้อมูลไปบันทึกต่อท้ายรายการเดิม ณ ตำแหน่งที่คำนวณจาก COUNTA($G:$G)-1

โปรดสังเกตว่า สูตร IF ที่ใช้นี้เป็นสูตรที่อยู่ใน Excel จึงช่วยให้ไม่ต้องเสียเวลากลับไปแก้ไขรหัส VBA เลยแม้แต่น้อย