วันพุธที่ 27 พฤษภาคม พ.ศ. 2558

การจัดการเกี่ยวกับวันที่ ใน Excel

คงต้องยอมรับกันว่า เรื่องของวันที่ใน Excel ทำให้ปวดหัวอยู่ไม่น้อยทีเดียว วันนึงผมได้ไฟล์จากที่อื่นมาเปิด เพื่อถ่ายโอนเข้าโปรแกรม Access วันที่ของเดิม เขียนว่า 1-มี.ค.-58 ทำไปทำมา กลายเป็น 1/3/1958 พอเป็นภาษาไทย ก็กลายเป็น 1/3/2501 ห่างกัน 57 ปี

อย่างนี้ก็ปวดหัวละซี

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

หลักใหญ่ของเรา คือ ต้องกำหนดรูปแบบให้ถูก โดยกำหนดให้ใช้ปีพุทธศักราช และ คลิกการป้อนให้สอดคล้องกัน การตั้งค่าดังกล่าว ให้คลิกเลือกเซลล์ที่จะป้อนวันที่ แล้วกด Ctrl +1 จะเปิดหน้าจอให้จัดรูปแบบเซลล์

ถ้าทำอย่างนี้ได้ จะหมดปัญหาเรื่องวันที่ ไม่มีเรื่องปวดหัวกวนใจ

แต่ถ้าเลือกไม่ได้ ปัญหาเข้ามาเอง ก็ต้องจัดการให้อยู่หมัด

มีสูตรเกี่ยวกับวันที่ ของ Excel ที่ช่วยแก้ไขสถานการณ์ได้ดีมาก คือ สูตร  Date() เป็นสูตรที่ยืดหยุ่นมาก

สูตรคือ Date(year, month, day)

ตัวอย่างการใช้

การเพิ่ม เดือน อีก 3 เดือน   คือ  =Date(2015, 2+3,20 จะได้ 20 พฤษภาคม 2558
เพิ่มจำนวนวัน โปรแกรมคิดเป็นเดือนให้ด้วย เช่น =DATE(2015,10,14+30) จะได้ 13 พฤศจิกายน 2558
ถ้าในกรณีปัญหาของผมข้างต้น ก็สามารถบวกปี ในเซลล์ ได้เลย เช่น =DATE(YEAR(A2)+57,MONTH(A2),DAY(A2))


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


การจัดรูปแบบข้อมูล 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





วันเสาร์ที่ 16 พฤษภาคม พ.ศ. 2558

การใช้ไฟล์เสียง mp3 ใน Blogger ร่วมกับ Google Site

การใช้เสียงใน Blogger หรือ Blogspot มี 2 ขั้นตอนใหญ่ ๆ คือ

  1. การนำไฟล์เสียงไว้บน Server ในตัวอย่างนี้จะใช้ Google site ซึ่งสามาชิกอีเมลของ Google ทุกคนสามารพถใช้ได้ฟรี
  2. การลิงค์ไฟล์เสียงในหน้าของ Blogger หรือ  Blogspot
ตัวอย่างการใช้เสียง ใน Blogger หรือ Blogspot



(เดี่ยวขลุ่ย เพลงลมพัดชายเขา ๒ ชั้น โดยครูสุวิทย์ แก้วกระมล)

การสร้าง Site บน Google Site และ upload ไฟล์

  1. ถ้ายังไม่มีอีเมลของ Google ให้สมัครอีเมลก่อน
  2. login เข้าใช้งานอีเมล
  3. ไปที่หน้า Google Site โดยพิมพ์ https://sites.google.com/
  4. จะเห็นปุ่มให้ Create ไชต์ 
  5. คลิกปุ่ม CREATE และทำตามหน้าจอ โดยตั้งชื่อ และเลือก Template แนะนำให้เลือก Blank Template จะง่ายและสะดวก
  6. เมื่อได้ Site แล้ว ที่หน้าแรก ให้คลิก Edit
  7. เลื่อนลงมาด้านล่าง จะเห็นปุ่ม ADD FILE เพื่อส่งไฟล์ขึ้น Server ให้คลิกและเลือกไฟล์ที่ต้องการ
  8. จะเห็นไฟล์ที่ส่งขึ้นไป ดังภาพ
  9. คัดลอก URL ของไฟล์นี้ไว้ สำหรับใช้ในหน้า Blog ต่อไป เช่น http://sites.google.com/site/thongjoon/Home/lompadChaiKhao2.mp3
  10. ถ้ามีการตั้งให้ Google ส่งข้อมูลไปที่อีเมล ในกรณีที่ site มีการเปลี่ยนแปลง เราสามารถหา URL ของไฟล์ที่ส่งขึ้นไปได้ ดังภาพ

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


การใช้ไฟล์เสียง ใน  Blogger
  1. สร้างหน้าใหม่ใน Blogger
  2. คลิกไปที่ HTML
  3. เพิ่มโค้ดตามตัวอย่าง
  4. ในส่วน THE_LOCATION_OF_YOUR_RECORDING ให้ใช้ URL ของไฟล์เสียง ที่ส่งขึ้นไปบน Google site
  5. เมื่อคลิกกลับมาที่หน้า เขียน จะเห็นมีแถบสำหรับเปิดเสียง และสามารถเปิดเสียงได้ ดังตัวอย่างข้างต้น

วันพฤหัสบดีที่ 7 พฤษภาคม พ.ศ. 2558

การสร้าง popup ที่มี Scroll bar ใน Word ด้วย vba

บางครั้งเราต้องการใช้ฟอร์มในโปรแกรม MS Word เพื่อแสดงข้อความบางอย่าง โดยให้แสดงเมื่อมีการกดปุ่ม ในกรณีของผม ผมใช้ Word สร้างเป็นบทเรียนปฏิสัมพันธ์ โดยกำหนดให้มีการฟังเสียงภาษาอังกฤษ และเมื่อกดปุ่มดู Script ก็จะมีหน้าจอใหม่ เป็น Popup เพื่อแสดงข้อความ พร้อมทั้งมี Scroll bar ให้สามารถเลื่อนขึ้นลงได้

วิธีการ มี 2 ขั้นตอนคือ
1. การสร้างฟอร์มใหม่ที่มี Scroll bar
2. การสร้างปุ่ม เรียกใช้งานฟอร์มที่สร้างขึ้น

การสร้างฟอร์มใหม่ที่มี Scroll bar 
  1. เปิดใช้งานเมนู นักพัฒนา หรือ Developer tab ดูวิธีการที่ http://www.thongjoon.com/2015/04/media-player-word2010.html
  2. พิมพ์ข้อความตามต้องการ
  3. กดปุ่ม Alt + F11 เพื่อเปิดหน้าจอเขียนโค้ด
  4. ไปที่  Insert > UserForm
  5. ปรับขนาดของฟอร์มให้ได้ขนาดตามต้องการ โดยใช้เมาส์ลากที่มุมขอบของฟอร์ม
  6. เลือกเครื่องมือ กล่องข้อความ หรือ Textbox ในกล่องเครื่องมือ 
  7. กำหนด Property ของฟอร์ม ให้สามารถทำงานอย่างอื่นได้ ในขณะที่ฟอร์มเปิดอยู่ โดยกำหนด showModal ให้เป็น False ทั้งนี้เพื่อให้สามารถทำงานอื่นได้ ในขณะที่เปิดฟอร์มนี้ค้างไว้ ถ้า showModal มีค่าเป็น True จะไม่สามารถทำอะไรได้ จนกว่าจะปิดฟอร์มนี้
  8. ลากวาง กล่องข้อความ หรือ  Textbox บนฟอร์มให้มีขนาดเกือบเต็ม เพื่อแสดงข้อความ
  9. ที่ขอบของ กล่องข้อความ หรือ  Textbox คลิกขวา เลือก Properties
  10. ในหน้าจอ Property ตั้งชื่อฟอร์ม เช่น myTextbox
  11. กำหนดให้ Multiline เป็น true เพื่อให้สามารถมีได้หลายบรรทัด
  12. กำหนด ลักษณะของ Scrollbar ตามต้องการ ในตัวอย่างกำหนดให้มีเฉพาะตามแนวตั้ง
  13. พิมพ์ข้อความในกล่องข้อความตามต้องการ 
การเรียกใช้ฟอร์ม

  1. กลับมาที่หน้าเดิมที่พิมพ์ข้อความไว้แล้ว
  2. ไปที่แถบ นักพัฒนา เลือกเครื่องมือ ปุ่มคำสั่ง
  3.  เมื่อคลิกที่เครื่องมือปุ่มคำสั่ง โปรแกรม Word จะสร้างปุ่มให้ ณ ที่เคอร์เซอร์
  4. สังเกตดูจะเห็นว่ากำลังอยู่ในโหมดออกแบบ
  5. คลิกขวาที่ปุ่ม เลือก คุณสมบัติ และเปลี่ยนข้อความบนปุ่มตามต้องการ
  6. ปิดหน้าจอ Properties
  7. ดับเบิ้ลคลิกที่ปุ่มที่สร้างขึ้น
  8. ใช้คำสั่งเรียกใช้ฟอร์มที่สร้างขึ้น โดยใช้ชื่อของฟอร์ม และตามด้วยคำสั่ง show 
  9. ปิดหน้าจอเขียนโค้ดและกลับมาที่หน้าหลัก
  10. คลิกโหมดออกแบบ เพื่อเข้าสู่โหมดปกติ
  11. คลิกที่ปุ่ม จะเปิดฟอร์มที่ทำขึ้น
ปัญหา
  1. เมื่อคลิกที่ฟอร์ม จะเห็นว่า เลื่อนไปบรรทัดสุดท้าย หรืออาจจะเปิดอยู่ในตำแหน่งบรรทัดสุดท้าย
  2. ผู้ใช้สามารถพิมพ์ข้อความเปลี่ยนแปลงได้
การแก้ไข
  1. คลิกโหมดออกแบบ
  2. กด Alt + F11 เพื่อไปที่หน้าจอเขียนโค้ด
  3. ถ้าไม่เห็นหน้าต่าง Project Explorer ให้ไปที่ View > Project Explorer
  4. ในหน้าต่าง Project Explorer คลิกขวาที่แบบฟอร์มที่สร้าง และเลือก View Code
  5. เลือกชื่อฟอร์ม และเลือก Activate เพื่อเขียนโค้ดสำหรับฟอร์มนี้ ในขณะที่ฟอร์มเริ่มทำงาน
  6. เขียนโค้ดสำหรับฟอร์มนี้ ตามตัวอย่างข้างล่าง เพื่อกำหนดให้แสดงบรรทัดที่ 1 และป้องกันการแก้ไข
  7. เมื่อกลับมาหน้าแรก และคลิกโหมดออกแบบ เพื่อกลับไปในโหมดปกติ และคลิกที่ปุ่ม จะแสดงหน้าจอ ตามต้องการ
  8. บันทึกเป็นไฟล์ *.docm เนื่องจากมีการใช้แมโคร หรือ vba
ดาวน์โหลดตัวอย่างไฟล์ คลิกที่นี่