ข้ามไปที่เนื้อหาหลัก

การจัดรูปแบบข้อมูล Excel ด้วย VBA และนำเข้าไปยัง Access

การถ่ายโอนข้อมูล จาก Excel 2010 ไปยัง Access 2010 สามารถทำได้หลายวิธี เช่น การใช้เมนู ข้อมูลภายนอก หรือ External Data แต่ปัญหาอยู่ที่การจัดโครงสร้างของข้อมูล ถ้าโครงสร้างระหว่าง Excel และ Access ไม่ตรงกัน การถ่ายโอนก็จะทำได้ค่อนข้างลำบาก ทั้งนี้เพราะ การถ่ายโอนข้อมูลจาก Excel ไปยัง Access นั้น หัวตารางของ Excel จะต้องเหมือนกับชื่อ ฟิลด์ในตารางที่จะนำข้อมูลเข้า หรือมิฉะนั้นก็นำเข้าเป็นตารางใหม่ แล้วค่อยจัดการภายหลัง

ในตัวอย่างนี้จะเป็นการถ่ายโอนเข้าตารางที่มีอยู่แล้ว

ตัวอย่าง
ต้องการถ่ายโอนข้อมูล ใน Excel ลงใน ตาราง ของ Access ต่อไปนี้

โครงสร้างของตาราง Access ที่ต้องการถ่ายโอนข้อมูลเข้ามา มีดังนี้
จะเห็นว่า โครงสร้างของข้อมูลใน Excel ไม่เหมือนกับใน Access ซึ่งเป็นเรื่องธรรมดา การถ่ายโอนข้อมูล จาก Excel จึงจำเป็นต้องจัดรูปแบบของข้อมูลใน Excel ให้เหมือนในตาราง ของ Access โดยถ้าจะให้ดี ก็ต้องเพิ่มชื่อหัวตาราง ให้เหมือนกับชื่อฟิลด์ในตารางของ Access ก็จะทำให้การถ่ายโอน สะดวก และง่ายได้ รูปแบบที่ต้องการ คือ

ฟิลด์ ID เป็น AutoNumber จึงไม่ต้องนำเข้าจาก Excel

วิธีการจัดรูปแบบตามที่ต้องการที่ง่ายที่สุด คือ การลากทีละเซลล์ มาวางในตำแหน่งที่ต้องการ และพิมพ์เพิ่มหัวตาราง

วิธีข้างต้น เป็นวิธีง่าย แต่ทำยาก โดยเฉพาะถ้ามีข้อมูลจำนวนมาก ๆ อาจจะต้องใช้เวลาหลายวัน และมีข้อผิดพลาดได้ง่าย

วิธีที่ดี คือการใช้ VBA คัดลอกข้อมูล และนำมาวางในตำแหน่งที่ต้องการ ซี่งมีโค้ด ดังนี้

Sub formatForAccess()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim thisName As String
Dim thisDate As Date

k = 5  'กำหนด แถวที่ 5 เป็นจุดเริ่มต้นการ Past
For i = 2 To 4 'จำนวนแถวที่มีข้อมูลที่ต้องการคัดลอก คือ ตั้งแต่แถวที่ 2 ถึงแถวที่ 4
        For j = 1 To 5 + 1 'จำนวนคอลัมน์ที่มีข้อมูล ตั้งแต่คอลัมน์ที่ 1 ถึงที่ 5 บวกไปเพื่อทดแทนกับที่มีการข้าม loop
            Sheets("Sheet1").Select
                If j = 1 Then 'ขึ้นแถวใหม่ หาชื่อคน
                    thisName = Cells(i, j).Value
                    j = j + 1  'ข้ามไป เพราะจะไม่พิมพ์ชื่อคนในตำแหน่งนี้
                End If
                Cells(i, j).Select
                Selection.Copy
                If Selection.Value <> "" Then  'ถ้าเป็นเซลล์ที่ไม่มีข้อมูล จะไม่คัดลอก
                         Sheets("Sheet2").Select
                            Cells(k, 2).Value = thisName  ' พิมพ์ชื่อคน ในคอลัมน์ที่ 2 ของ Sheet2
                         Cells(k, 3).Select
                         If j <> 1 Then
                            ActiveSheet.Paste
                         End If
                         Sheets("Sheet1").Select
                         Cells(1, j).Select  'แถวบนสุด คือว้นที่ คัดลอกมาในตำแหน่งที่ตรงกับข้อมูลในแถวนั้น ๆ
                         Selection.Copy
                         Sheets("Sheet2").Select
                         Cells(k, 4) = Format(Date, "yyyy-mm-dd") 'จัดรูปแบบของเซลล์ให้เป็นวันที่ short date
                         Cells(k, 4).Select
                         ActiveSheet.Paste
                         k = k + 1  ' เพิ่มค่า เพื่อให้เลื่อนลงมาอีกแถว ใน Sheet2 เพื่อเตรียม Paste
                End If
        Next j
Next i

Sheets("Sheet1").Select
Cells(1, 1).Select
Application.CutCopyMode = False  'ยกเลิกการคัดลอก (เส้นประ)


End Sub

หลักการ

  1. เป็นการจัดโครงสร้างรูปแบบข้อมูลใน Sheet1 โดยนำไปไว้ที่ Sheet2 เพื่อให้สามารถนำเข้า Access ได้
  2. ใช้วิธีการวน 2 ชั้น คือ วนไปตามคอลัมน์ และเมื่อจบแถวที่ 1 ซึ่งเป็นข้อมูลของคนที่ 1 แล้ว จึงเลื่อนมายังแถวที่ 2 ซึ่งเป็นข้อมูลของคนที่ 2  ทั้งนี้ โดยใช้ตัวแปร เพื่อกำหนดตำแหน่งของ คอลัมน์และแถว เพื่อให้สะดวกในการวน และเป็นการจำตำแหน่งเดิมไว้ด้วย
  3. เนื่องจากข้อมูลของแต่ละคนไม่เท่ากัน ดังนั้น เมื่อไปถึงแต่ละเซลล์ จึงต้องมีการตรวจสอบว่า มีข้อมูลหรือไม่ ถ้าไม่มีข้อมูล ก็ไม่ต้องทำอะไร
  4. คำสั่งหลักที่ใช้ในการระบุตำแหน่ง คือ Cells(Row, Column) เช่น Cells(2, 4) คือ แถวที่ 2 คอลัมน์ที่ 4


วิธีการใช้งาน

  1. เรียกใช้ Developer tab หรือ นักพัฒนา
  2. กดปุ่ม Alt + F11 เพื่อเปิดหน้าต่างเขียนโค้ด คลิก Insert > Module และวางโค้ดที่นี่
  3. สร้าง ปุ่ม บน Sheet1 ซึ่งมีข้อมูลที่ต้องการจัดรูปแบบ 
  4. เมื่อสร้างปุ่มครั้งแรก โปรแกรมจะมีหน้าจอให้เขียนโค้ด ให้เลือก สร้าง
  5. พิมพ์ชื่อ Sub ที่คัดลอกมาวางไว้
  6.  เมื่อคลิกปุ่ม โปรแกรมจะทำงานทันที
  7. เมื่อเสร็จแล้ว ให้ไปที่ Sheet2 และพิมพ์หัวตารางให้เหมือนฟิลด์ ในตารางของ Access ตามภาพข้างบน
  8. บันทึกเป็นไฟล์ xlsm เนื่องจากมีการใช้โค้ด VBA
ดาวน์โหลดไฟล์ Excel คลิกที่นี่



วิธีการนำเข้า Access

  1. เปิดโปรแกรม Access ที่มีตารางข้อมูล ที่จะนำข้อมูลนี้เข้าไปเพิ่มเติม
  2. ไปที่ ข้อมูลภายนอก เลือก Excel
  3. เลือกผนวกไฟล์ และเลือกไฟล์ Excel ที่จัดข้อมูลแล้ว (ถ้าใช้โค้ดข่างบนจัดโครงสร้างใหม่ ข้อมูลจะอยู่ใน Sheet2)
  4. คลิกตกลง
  5. คลิกเลือก Sheet2 เพื่อเลือกข้อมูลที่จัดรูปแบบแล้ว
  6. คลิก ตกลง
  7. ข้อมูลจะถูกนำไปต่อท้ายข้อมูลเดิมที่มีอยู่แล้ว
  8. เป็นอันเสร็จการถ่ายโอนข้อมูล จาก Excel ไปยัง Acess





ความคิดเห็น

โพสต์ยอดนิยมจากบล็อกนี้

อุปมา อุปไมย สำนวนการเปรียบเทียบ ของไทย

การเตรียมสอบ ก.พ. ภาค ก. เพื่อสอบบรรจุเข้ารับราชการ มีการทดสอบความสามารถทั่วไป มักจะมี
ข้อสอบที่เกี่ยวกับอุปมาอุปไมย  ข้อสอบมีลักษณะ ให้หาตัวเลือกที่มีความหมาย ความสัมพันธ์คล้ายคลึง หรือเหมือนกับที่โจทย์กำหนดให้มา  หรือเติมข้อความที่มีความหมายสอดคล้องกับคำอุปมาอุปไมยที่ยกมาให้ เป็นต้น ดังนั้น การเข้าใจความหมายของคำอุปมาอุปไมย จึงช่วยให้ทำข้อสอบได้ดียิ่งขึ้น

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

คนดุ หากต้องการให้ความหมายชัดเจน น่าฟัง และเกิดภาพพจน์ชัดเจนก็ต้องอุปมาอุปไมยว่า “ดุ เหมือน เสือ”
ขรุขระมาก การสื่อความยังไม่ชัดเจนไม่เห็นภาพ ต้องอุปมาอุปไมยว่า “ขรุขระเหมือนผิวมะกรูด” หรือ “ขรุขระเหมือนผิวพระจันทร์” ก็จะทำให้เข้าใจ ความหมายในรูปธรรมชัดเจนมากยิ่งขึ้น

คำอุปมาอุปไมยที่ควรรู้จัก (พิมพ์คำ/ข้อความ แล้วกดปุ่ม "ค้นหา")

แนวข้อสอบ เงื่อนไขสัญลักษณ์

ครั้งที่แล้ว ได้แนะนำหลักการทำ ข้อสอบ ก.พ. ภาค ก. ความสามารถทั่วไป เงื่อนไขสัญลักษณ์ มา แล้ว ถ้าใครยังไม่ได้อ่าน ก็คลิกกลับไปอ่านได้
ความจริง ข้อสอบเงื่อนไขสัญลักษณ์ เป็นข้อสอบไม่ยาก ถ้าเข้าใจหลักการ และมีทักษะความชำนาญ ใจเย็น ๆ อย่าตื่นเต้น โดยเฉพาะการดูเครื่องหมายต่าง ๆ อย่าดูผิด เช่น เครื่องหมายมากกว่า (>) น้อยกว่า (<) เป็นต้น เพราะการแก้ปัญหาโจทย์เงื่อนไขสัญลักษณ์ หรือ inequality ก็คล้ายกับการแก้ปัญหาสมการโดยทั่วไป นั่นเอง คือ สามารถบวก ลบ คูณ หาร ด้วยจำนวนที่เท่ากัน ทั้งสองข้างของเครื่องหมายได้ กลับเศษเป็นส่วนได้ แต่ก็มีบางเรื่อง บางรายละเอียดที่แตกต่างกันบ้าง ซึ่งอ่านได้จาก ข้อสอบ ก.พ. ภาค ก. ความสามารถทั่วไป เงื่อนไขสัญลักษณ์ นะครับ ครั้งนี้ จึงเป็นการนำแนวข้อสอบ เงื่อนไขสัญลักษณ์ เพื่อนำมาฝึกทำให้เกิดทักษะความชำนาญ เพื่อจะได้ทำข้อสอบได้รวดเร็วขึ้น เพราะในห้องสอบ เวลาจัดได้ว่ามีค่ามาก ยิ่งทำเร็วและถูกต้อง ยิ่งดี คำสั่ง

เลือกตอบข้อ 1. ถ้าข้อสรุปทั้งสอง ถูกด้องหรือเป็นจริง ตามเงื่อนไข
เลือกตอบข้อ 2. ถ้าข้อสรุปทั้งลอง ผิดหรือไม่เป็นจริง ตามเงื่อนไข
เลือกตอบข้อ 3. ถ้าข้อ…

เทคนิคการทำ ข้อสอบ อนุกรม ของ ก.พ.

|ประเภทของอนุกรม เทคนิคการทำโจทย์เลข อนุกรม ข้อแนะนำเพิ่มเติม |


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


ประเภทของอนุกรม รูปแบบความสัมพันธ์ของตัวเลขอนุกรมเท่าที่พบบ่อย ๆ มีหลายประเภท เช่น

ก. อนุกรมเชิงเดี่ยว 

ได้แก่ชุดตัวเลขที่เป็นอนุกรมเพียงชุดเดียว เช่น
ค่าของตัวเลขเพิ่มขึ้นต่อเนื่องอย่างเป็นระบบ โดยการบวก หรือ คูณ ตัวเลขก่อนหน้า เช่น บวกด้วยตัวเลขที่เป็นค่าคงที่ เช่น    5   10   15   20   ...?...
บวกด้วยตัวเลขที่มีระบบ เช่น     1    2    5    10   ...?...
คูณด้วยค่าคงที่ เช่น   1   3   9   27   ...?...
มีทั้ง บวก ลบ คูณ หรือหาร สลับกัน เช่น บวกแล้วคูณด้วยค่าคงที่สลับกัน ดังตัวอย่าง  5   7    14   16  32   ...... มีการ บวก ลบ คูณ หรือ หาร ร่วมกัน เช่น  15   31   63   127   255  ...?...
ในตัวอย่างนี้ จะเห็นว่า ตัวเลขตัวแรกคูณด้วย 2 และบวกด้วย 1 จะได้ตัวเลขตัวถัดไป คูณด้วยค่าคงที่ที่เป็นเศษส่วน ให้สังเกตความสัมพันธ์ว่า ตัวเลขก่อนหน้า …