VBA กับการจัดการฐานข้อมูล

โครงสร้างตารางสำหรับเก็บข้อมูล

ตารางฐานข้อมูลที่ดีต้องมีลักษณะโครงสร้าง ตามกฎ 3 ข้อดังนี้

1. หัวตารางต้องใช้พื้นที่เซลล์ 1 row เท่านั้น จากภาพคือพื้นที่เซลล์ B2, C2, D2 แต่ถ้าอยากทำให้หัวตารางมีคำอธิบายเพิ่มเติมในอีกบรรทัด ให้ใช้วิธีกดปุ่ม Alt+Enter เพื่อขึ้นบรรทัดใหม่ในเซลล์เดิมต่อท้ายข้อความเดิม
2. ห้ามเว้นว่าง row ทั้ง row ของทั้งรายการ เช่น ห้ามเว้นเซลล์ B3:D3 หรือ B5:D5 เป็นต้น และไม่ควรเว้นว่างเซลล์ใดเซลล์หนึ่งโดยไม่จำเป็น โดยเฉพาะเซลล์ที่เก็บข้อมูลที่เป็นรหัสหรือชื่อสินค้า ซึ่งใช้เป็นข้อมูลสำหรับใช้ค้นหาข้อมูลอื่นในแนวรายการเดียวกัน
3. ห้ามนำตารางเก็บข้อมูลไปติดกับเซลล์ข้อมูลอื่น ทั้งด้านบน ด้านล่าง ด้านซ้าย และด้านขวาของตารางฐานข้อมูล ต้องเว้นว่างไว้รอบข้างอย่างน้อย 1 เซลล์โดยตลอด เพื่อทำให้ Excel รู้จักว่าพื้นที่เซลล์ที่ติดต่อกันนี้เป็นตารางเดียวกัน

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

ให้เริ่มจากคลิกเซลล์ใดเซลล์หนึ่งในพื้นที่ตารางเก็บข้อมูล แล้วเมื่อคลิกเลือกคำสั่งบนเมนู Data จะพบว่า Excel จะเลือกพื้นที่เฉพาะส่วนที่ต้องการนำมาใช้งานกับเมนูนั้นๆต่อให้เอง เช่น ถ้าสั่ง Data > Sort จะเลือกพื้นที่ B3:D7 เฉพาะส่วนที่เป็นข้อมูลรายการ หรือถ้าสั่ง Data > Filter > AutoFilter จะเกิดปุ่ม Filter ขึ้นเฉพาะหัวตารางด้านบน B2:D2

สูตรสำเร็จรูปที่ใช้จัดการฐานข้อมูล

หลังจากจัดเก็บข้อมูลไว้ในตารางที่มีโครงสร้างถูกต้องตามที่กำหนดไว้ข้างต้นแล้ว เราสามารถเลือกใช้สูตรต่อไปนี้เพื่อค้นหาข้อมูลหรือคำตอบที่ต้องการต่อไป

โครงสร้างสูตร IF
ใช้หาคำตอบตามเงื่อนไข

=IF(เงื่อนไข, ผลหากเงื่อนไขเป็นจริง, ผลหากเงื่อนไขเป็นเท็จ)

ตัวอย่าง ให้หาคำตอบถ้ายอดรวมของ Amount ทั้งหมด มีค่ารวมแล้วมีค่ามากกว่าหรือเท่ากับ 100 ให้ตอบว่า OK แต่ถ้่ายอดรวมไม่ถึง 100 ให้ตอบว่า No จะต้องสร้างสูตรต่อไปนี้

=IF( Sum(D3:D7)>=100, "OK", "No")
  คำตอบ คือ OK

โครงสร้างสูตร VLookup
ใช้หาค่าในตาราง

=VLookup(รหัสที่ใช้หา, ตารางเก็บข้อมูล, เลข column ของคำตอบเรื่องที่ต้องการ, 0)

ตัวอย่าง ต้องการหาว่า รหัส Id a003 มีชื่ออะไร และมียอดเป็นเท่าใด ให้ใช้สูตรต่อไปนี้ตามลำดับ

=VLookup("a003", B3:D7 , 2, 0)
   คำตอบ คือ C

=VLookup("a003", B3:D7 , 3, 0)
   คำตอบ คือ 30

เลข 2 และ 3 ที่ใส่ไว้ในสูตร คือ เลขของ column ของ Name และ Amount ซึ่งอยู่ใน column ที่ 2 และ 3 ตามลำดับในพื้นที่ตาราง B2:D7

เลข 0 ที่ใส่ไว้ด้านหลังสุดในสูตร เป็นการกำหนดให้ใช้สูตร VLookup หาค่าแบบ Exact Match โดยจะหาค่าเฉพาะเมื่อมีค่าตรงกับรหัสที่ใช้หาเท่านั้น แต่ถ้าไม่ใส่เลข 0 จะทำให้ VLookup ทำงานแบบ Approaching Match โดยจะหาค่ามาให้เสมอ แม้ว่าไม่มีรหัสตรงกันก็ตาม ซึ่งโดยทั่วไปถ้าใช้รหัสเป็นค่าที่ใช้ค้นหาคำตอบ ต้องใช้แบบ Exact Match

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

โครงสร้างสูตร Match
ใช้หาตำแหน่งของค่าในตาราง

=Match(รหัสที่ใช้หา, พื้นที่แนวตั้งของรหัสทั้งหมด, 0)

ตัวอย่าง ต้องการหาว่ารหัส a003 เป็นรายการที่เท่าใด ให้ใช้สูตรดังนี้

=Match("a003", B3:B7, 0)
   คำตอบ คือ 3

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

โครงสร้างสูตร Index
ใช้หาค่า ที่อยู่ตามตำแหน่งของค่าในตาราง

=Index(ตารางเก็บข้อมูล, เลขที่ Row, เลขที่ Column)

ตัวอย่าง ต้องการหาค่าจากพื้นที่ตาราง B3:D7 ณ ตำแหน่ง row ที่ 2 ตัดกับ column ที่ 3 ให้ใช้สูตรดังนี้

=Index(B3:D7, 2, 3)
   คำตอบ คือ 20

โครงสร้างสูตร Offset แบบสั้น
ใช้หาค่า ที่อยู่ตามตำแหน่งถัดไปจากตำแหน่งเซลล์อ้างอิงที่กำหนด

=Offset(เซลล์อ้างอิง, จำนวน row ถัดไป,
จำนวน column ถัดไป)

ตัวอย่าง ต้องการหาค่า ที่อยู่ถัดจากเซลล์ B3 ลงไป 1 row และถัดไปจากเซลล์ B3 ไปด้านขวา 2 column ให้ใช้สูตรดังนี้

=Offset(B3, 1, 2)
   คำตอบ คือ 20

โครงสร้างสูตร Offset แบบเต็ม
ใช้หาค่าหลายค่า เสมือนเป็นพื้นที่ตาราง

=Offset(เซลล์อ้างอิง, จำนวน row ถัดไป,
จำนวน column ถัดไป, ความสูงของตาราง,
ความกว้างของตาราง)

ตัวอย่าง ต้องการใช้สูตรกำหนดพื้นที่ของตัวเลข Amount ซึ่งจะเห็นได้ว่ามีเลข 10, 20, 30, 40, 50 เก็บไว้ ให้ใช้สูตรดังนี้

=Offset(B3, 0, 2, 5, 1)
   คำตอบ คือ {10;20;30;40;50}

สาเหตุที่ใส่เลข 0,2,5,1 นั้น เนื่องจากตารางข้อมูล Amount D3:D7 มีตำแหน่ง D3 อยู่ในแนว row เดียวกันกับ B3 จึงมีจำนวน row ถัดไปเท่ากับ 0 และตำแหน่ง D3 อยู่ถัดไปจาก B3 อีก 2 column โดย D3:D7 มีพื้นที่ความสูง 5 row และกว้าง 1 column

สูตรที่สร้างขึ้นนี้ อาจไม่เห็นคำตอบครบตามต้องการ ขอให้กดปุ่ม F2 แล้วตามด้วย F9 จะพบตัวเลข {10;20;30;40;50} และสังเกตว่า ระหว่างตัวเลขนั้น มีเครื่องหมาย semi-colon ; คั่น ซึ่งเครื่องหมาย ; นี้แสดงว่า ตัวเลขเป็นแนวตั้ง (ถ้าคั่นด้วยเครื่องหมาย comma , จะแสดงว่าเป็นแนวนอน) เมื่อเห็นตัวเลขแล้ว ให้กดปุ่ม Esc เพื่อกลับไปเป็นสูตรตามเดิม

โดยทั่วไปจะไม่ค่อยพบสูตร Offset แบบเต็มใช้ในเซลล์ใด แต่จะนำสูตร Offset แบบเต็ม ไปสร้างต่อเป็น Formula Name ผ่านเมนู Insert > Name > Define เพื่อตั้งเป็นชื่อ Formula Name นำมาใช้แทน Range Name

Offset ทำหน้าที่กำหนดขนาดตารางได้เอง (Dynamic Range)

สูตรช่วยในการกำหนดพื้นที่ตาราง ที่ขยายหรือหดได้ตามจำนวนข้อมูล เรียกว่า Dynamic Range เช่น หากต้องการทำให้พื้นที่ของ Amount ซึ่งเดิมมาจากเซลล์ D3:D7 นั้น สามารถขยายหรือหดพื้นที่ได้ตามข้อมูลที่เพิ่มหรือลดได้เอง ให้สั่ง Insert > Name > Define ตั้งชื่อ Formula Name ว่า Amount และกำหนดสูตรต่อไปนี้ลงไปในช่อง Refers to

=Offset($B$3, 0, 2, CountA($D:$D)-1, 1)

สังเกตว่า ตำแหน่งของเซลล์ที่อ้างอิงในสูตรเป็น Absolute Reference โดยมีเครื่องหมาย $ กำกับตำแหน่งทั้ง row และ column ทั้งนี้เป็นการสร้างตามหลักของ Formula Name เพื่อทำให้ Amount มีตำแหน่งถูกต้องและมีตำแหน่งแน่นอน

สูตร CountA($D:$D)-1 ทำหน้าที่คำนวณหาความสูง โดยนับจำนวนรายการข้อมูลทั้งหมดจาก column D แต่เนื่องจากสูตร CountA นับหัวตารางเซลล์ D2 เกินเข้ามา 1 เซลล์ จึงต้องลบจำนวนที่นับได้ทิ้งไป 1 เซลล์

โครงสร้างสูตร CountIF
ใช้นับจำนวนเซลล์ เท่าที่มีค่าตามเงื่อนไข

=CountIF(พื้นที่ตาราง, "เงื่อนไข")

ตัวอย่าง ต้องการนับจำนวนรหัส a003 ว่ามีกี่เซลล์ ให้ใช้สูตรดังนี้

=CountIF(B3:B7, "a003")
   คำตอบ คือ 1

หลักการใช้ VBA จัดการฐานข้อมูล

1. อย่าใช้ VBA ถ้าเราสามารถใช้เมนูหรือสูตรหาคำตอบที่ต้องการได้อยู่แล้ว นอกจากช่วยประหยัดเวลาของเรา ไม่ต้องหาทางสร้างรหัส VBA ขึ้นเอง การใช้เมนูหรือสูตร ยังทำให้ Excel ทำงานเร็วกว่าการใช้ VBA อย่างมาก (คำสั่งบนเมนูและสูตรที่ Microsoft สร้างไว้ให้นั้น จะทำงานได้เร็วมาก เพราะไม่ต้องเสียเวลาแปลรหัสเป็นภาษาเครื่อง ต่างจาก VBA ที่เราเขียนเอง ซึ่งยากจะเขียนรหัสที่มีประสิทธิภาพได้เทียบเท่า Microsoft)
2. ต้องออกแบบตารางให้ถูกต้อง แล้วทดลองใช้คำสั่งบนเมนู และใช้สูตร จนได้ขั้นตอนที่ลัดที่สุดที่ทำได้
3. ใช้ Macro Recorder บันทึกขั้นตอนการใช้คำสั่งบนเมนู แทนการเขียนรหัสเองทั้งหมด
4. รหัส VBA ที่สร้างไว้นั้น ต้องสร้างแบบยืดหยุ่น สามารถใช้รหัสเดิมทำงานต่อไปได้ตลอด ไม่ต้องเสียเวลาย้อนกลับมาแก้ไขรหัสอีกในภายหลัง ไม่ว่าโครงสร้างตาราง ชื่อชีท ชื่อแฟ้ม ชื่อโฟลเดอร์จะต่างไปจากเดิมหรือไม่อย่างไร
5. ค่าคงที่หรือตัวแปรทั้งหมดที่ใช้ในรหัส VBA ให้ link ต่อมาจาก Excel โดยใช้ Range Name หรือ Formula Name เป็นสื่อกลาง
6. ถ้าจำเป็นต้องแก้ไข ให้แก้ไขเฉพาะส่วนที่อยู่ใน Excel เนื่องจากเป็นสิ่งที่เราคุ้นเคย ใช้งานทุกวัน สามารถแก้ไขได้ง่ายและสะดวกกว่าเข้าไปแก้รหัสใน VBE
7. หลีกเลี่ยงการลบรายการที่เลิกใช้งานทิ้ง เพราะการลบข้อมูลทิ้ง ย่อมเสียข้อมูลเก่านั้นไป แต่ให้ใช้วิธีเขียนกำกับรายการที่เลิกใช้งาน เช่น ใช้เซลล์ที่มีเลข 99 กำกับรายการใดเพื่อแสดงว่า รายการนั้นเลิกใช้ไปแล้ว
8. หลีกเลี่ยงการแทรกรายการใหม่เข้าไประหว่างรายการเก่า แต่ให้บันทึกข้อมูลรายการใหม่ ต่อท้ายรายการเก่าทั้งหมดต่อกันไปเรื่อยๆ แล้วจึงนำข้อมูลไปจัดเรียงในพื้นที่อื่น เป็นตารางใหม่ที่จัดเรียงตามต้องการ
9. ควรเลือกใช้ VBA ลดขั้นตอนที่จำเป็นเท่านั้น อย่าพยายามเขียน VBA เพื่อหาทางทำให้งานทุกขั้นตอนทำงานเองโดยอัตโนมัติ เพราะรหัสจะมีความซ้บซ้อนและยากขึ้นมาก ลองนึกเผื่อไว้ด้วยว่า คนอื่นรุ่นหลังจะสามารถแกะรหัสเดิม มาแก้ไขหรือพัฒนาต่อไหวหรือไม่