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

ชุดคำสั่ง : SendData

image021

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

ส่งข้อมูลที่กรอกลงในเซลล์ 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 เลยแม้แต่น้อย  

Author: สมเกียรติ ฟุ้งเกียรติEmail: This email address is being protected from spambots. You need JavaScript enabled to view it.

E-Learning

Go to top