VBA Excel ตอน User Function (Excel Add-Ins)

สวัสดีท่านผู้อ่าน How to Excel ในวันสิ้นเดือนสิงหาคมนะครับ บทความนี้ขอเล่าแบบสบายๆให้เข้าบรรยากาศเงินเดือนออกซะหน่อยครับ จากบทความ Excel Series ของการจัดการ Date Data ผมได้นำเสนอวิธีการสร้างฟังก์ชันขึ้นมาใช้เองด้วยการใช้ VBA และได้แสดงวิธีการเรียกฟังก์ชันมาใช้ใน Excel ไปแล้ว โดยได้ทิ้งท้ายไว้ในเรื่องของการนำฟังก์ชันที่ได้พัฒนาแล้วให้สามารถเรียกใช้จากไฟล์ Excel อื่น ดังนั้นบทความนี้จึงขอนำเสนอวิธีการดังกล่าวครับ

เริ่มจากให้ท่านผู้อ่านสร้างไฟล์ Excel ป่าวๆมาซักหนึ่งไฟล์ครับ เอาเป็นว่าผมจะขอนำเสนอฟังก์ชันที่เปลี่ยนข้อมูลจาก Text Format เป็นข้อมูลในรูปแบบ Time Format หล่ะกันนะครับ โดยรายละเอียดเกี่ยวกับ Time Format ขอให้ท่านผู้อ่านไปติดตามได้ในบทความ Excel Series เรื่องการจัดการ Time Data

มาดูปัญหากันก่อนครับ โดยปกติผมมักจะเจอข้อมูลการรูดบัตรจากเครื่องรูดบัตรในลักษณะนี้ครับ HM เช่น 0805 เป็นต้น ทีนี้รูปแบบของ Time Data ใน excel จะเป็นดังนี้ครับ H:M ฉะนั้นหากเราต้องการนำข้อมูลที่อ่านได้จากเครื่องรูดบัตรส่งให้ Excel คำนวณต่อ เช่น เวลาทำงาน ค่าโอที หรือคำนวณประสิทธิภาพเครื่องจักร ก็ต้องแปลงข้อมูลให้อยู่ในรูปแบบที่ Excel รู้จักกันก่อน มาเริ่มกันเลยครับ จากปัญหาที่พบไม่อยากเลยใช่มั้ยครับ แค่แยกข้อมูลของจำนวนชั่วโมงและจำนวนนาทีจากข้อมูลที่ถูกส่งผ่านมา และใช้ฟังก์ชัน Date ของ VBA ทำการแปลงและคืนค่ากลับเป็นข้อมูลแบบ Time ครับ ดังนั้นข้อมูลที่ถูกส่งผ่านฟังก์ชันของเราก็คือข้อมูลของเวลาในรูปแบบ text นั่นเอง ผมขอตั้งชื่อ User Function ของผมเป็น TextToTime โดยรับค่าข้อมูลเวลาผ่านตัวแปร text_time เอาหล่ะครับมาสร้างTime Data ใน Excel กันเลย โดยการกด Alt+F11 จะปรากฎ VB EDITOR ครับให้ไปที่เมนู Insert แล้วเลือก Module ในหน้าต่าง Editor จะปรากฎส่วนที่เขียนฟังก์ชันให้เขียนโค้ดลงไปดังนี้ครับ

ภาพที่ 1 Excel User Function

ตอนนี้เราก็ได้ฟังก์ชัน TextToTime ไว้ใช้งานแล้วครับ มาลองใช้งานใน Excelกันเลยครับดังแสดงในภาพที่ 2

ภาพที่ 2 ผลการคำนวณใน Excel ผ่านการเรียกใช้ User Function

เอาหล่ะครับ ที่นี้หากเราต้องการนำฟังก์ชันนี้ไปใช้งานในไฟล์ Excel อื่นๆต้องทำอย่างไร ตามมาเลยครับ เริ่มต้นให้ท่านลบข้อมูลใน worksheet ทั้งหมดก่อนครับ จากนั้นทำการบันทึกไฟล์ Excel นี้ ในตัวอย่างนี้ผมขอตั้งชื่อไฟล์เป็น MyFunction โดยเลือกประเภทของไฟล์เป็น Microsoft Office Excel Add-In ครับ เอาหล่ะครับตอนนี้เรามีไฟล์ Excel ประเภท Add-In ที่เราสร้างขึ้นมาแล้ว เรามาดูกันครับว่าจะเรียกใช้ได้อย่างไร

เริ่มจากสร้างไฟล์ Excel ใหม่ขึ้นมาไฟล์หนึ่งครับ หากต้องการเรียกใช้ฟังก์ชัน TextToTime ซึ่งได้สร้างเตรียมไว้ ในมีไฟล์ Excel ประเภท Add-In ให้ไปที่ เมนู เครื่องมือ เลือก Add-Ins… จะปรากฎหน้าต่าง Add-In ท่านจะเห็นว่ายังไม่มี Add-In ที่เราสร้างไว้ปรากฏอยู่ในรายการ ให้คลิกปุ่ม เรียกดู… จะปรากฏหน้าต่างค้นหาไฟล์ Excel Add-In ให้เราเลือกไฟล์ excel Add-Ins ที่ได้สร้างขึ้น และคลิกปุ่มตกลง จะกลับมาที่หน้าต่าง Add-In ในรายการ Add-In จะปรากฏรายการ Add-In ชื่อ MyFunction ให้คลิกเลือก และคลิกปุ่มตกลงเพื่อออกจากหน้าต่าง Add-In ตอนนีไฟล์ Excel ของเราก้สามารถเรียกใช้งานฟังก์ที่อยู่ในไฟล์ Add-In ชื่อ MyFunction ได้แล้ว แน่นอนว่าตอนนี้มีเพียงแค่ฟังก์ชัน TextToTime เพียงฟังก์ชันเดียว

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

VBA Excel ตอน เขียน vba แก้ปัญหาสูตร Datedif (จบ)

มาถึงตอนจบของ VBA Excel Series ของการแก้ปัญหา Datedif กันแล้วนะครับ ก่อนไปว่ากันในเนื้อหาของบทความตอนจบ ขอเล่านอกเรื่องซักหน่อยครับ วันที่เขียนบทความนี้ (29/8/2010) มีโอกาสไปดูหนังไทยเรื่องหนึ่ง เงา ครับ ไม่อยากบอกว่าหนังไทยเรื่องนี้เป็นหนังไทยที่ทำให้ผมผิดหวังอีกครั้งหนึ่ง ด้วยเนื้อหาที่พยายามจะป้อนให้ผู้ชมในเวลา 90 นาที กับเรื่องสั้นจำนวนสี่เรื่องที่ผมเองมองว่ามากเกินไป ทำให้ผมรู้สึกว่าถูกบีบบังคับให้ดูเรื่องย่อจริงๆ เห้อ ว่าแล้วขอไว้อาลัยกับหนังลักษณะนี้เลยครับ เทียบกับ 5 แพร่ง 4 แพร่งซึ่งมีเรื่องย่อยจำนวนพอกัน เทียบไม่ได้เลย ขอบ่นกันนิดหนึ่งครับ มาเข้าเรื่อง VBA Excel ของเรากันดีกว่าครับ จากที่ได้แสดงแนวการคิดและการใช้สูตรในชุด Date ของ Excel เข้ามาประยุกต์ใช้เพื่อหาค่า Year Diff , Mont Diff และ Day Diff ซึ่งจะพบว่ามีความยุ่งยากพอสมควรในการใช้งานกับ Excel ในบทความนี้เรามาเขียนฟังก์ชันสำหรับการคำนวณดังกล่าวด้วยเทคนิค VBA ของ Excel กันครับ มาดูกันเลยครับ เริ่มจาก กด Alt + F11 เพื่อเข้าสู่หน้าต่างของ Microsoft Visual Basic ในหน้าต่าง Editor ให้สร้างฟังก์ชันชื่อ MyDateDiff โดยมีรูปแบบดังนี้

Function MyDateDiff(Start_Date as Date,End_Date As Date , form as String) As integer

End Function

โดยมีคัวแปรที่ถูกส่งผ่านดังนี้

Start_Date คือวันเริ่มต้น เป็นตัวแปรชนิด Date

End_Date คือวันสิ้นสุด เป็นตัวแปรชนิด Date

form เป็น รูปแบบที่ต้องการให้ฟังก์ชันคำนวณ โดยในการสร้างฟังก์ชันนี้ผมขอยกตัวอย่างการคำนวณ Year Diff (ผลต่างจำนวนปี), Month Diff(จำนวนเดือนหลังคิดจำนวนปีแล้ว และ Day Diff (จำนวนวันหลังคิดผลของปีและเดือนแล้ว) โดยใช้รูปแบบเป็น “Y” , “MY” , “DY”

เขียน VBA Code ในฟังก์ชัน MyDateDiff ตามวิธีการของบทความที่ผ่านมาดังนี้

Option Explicit

Function MyDateDiff(Start_Date As Date, End_Date As Date, format As String) As Integer

Dim YDiff As Integer

Dim MDiff As Integer

Dim DDiff As Integer

Dim temp As Date

format = UCase(format) 'converted to uppercase

'switch Start_Date & End_Date

If Start_Date > End_Date Then

temp = Start_Date

Start_Date = End_Date

End_Date = temp

End If

' Year Diff

YDiff = Year(End_Date) - Year(Start_Date)

If DateSerial(Year(End_Date), Month(Start_Date), Day(Start_Date)) > End_Date Then

YDiff = YDiff - 1

End If

If format = "Y" Then

MyDateDiff = YDiff

End If

'Month Diff

If Month(End_Date) > Month(Start_Date) Then

If Day(End_Date) >= Day(Start_Date) Then

MDiff = Month(End_Date) - Month(Start_Date)

Else

MDiff = Month(End_Date) - Month(Start_Date) - 1

End If

Else

If Day(End_Date) >= Day(Start_Date) Then

MDiff = Month(End_Date) - Month(Start_Date) + 12

If MDiff = 12 Then

MDiff = 0

End If

Else

MDiff = Month(End_Date) - Month(Start_Date) + 11

End If

End If

If format = "MY" Then

MyDateDiff = MDiff

End If

'Day Diff

If Day(End_Date) >= Day(Start_Date) Then

DDiff = Day(End_Date) - Day(Start_Date)

Else

DDiff = Day(DateSerial(Year(End_Date), Month(End_Date), 0)) - Day(Start_Date) + Day(End_Date)

End If

If format = "DY" Then

MyDateDiff = DDiff

End If

End Function

จากฟังก์ชัน MyDateDiff ที่เขียนขึ้นใน VBA ท่านผู้อ่านสามารถเรียกใช้ได้ใน Worksheets ของ Excel โดยสามารถพิมพ์สูตรนี้ได้ในเซลล์ดังแสดงในภาพที่ 1

ภาพที่ 1 ผลการคำนวณของ Excel จากการใช้ฟังก์ชัน MyDateDiff

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

จากการนำเสนอในบทความนี้นอกจากกการคำนวณเกี่ยวกับวันที่แล้วยังทำให้เราเห็นว่าเราสามารถสร้างฟังก์ชันการคำนวณขึ้นมาเอง (User define function) ด้วย VBA และสามารถเรียกใช้ฟังก์ชันนี้ผ่าน Excel ได้อย่างง่าย แต่ในขณะเดียวกันหากเราต้องการนำฟังก์ชันนี้ไปใช้กับไฟล์ Excel อื่นจะทำอย่างไร เดี๋ยวผมจะนำเสนอในบทความต่อไปหล่ะกันนะครับ เกี๋ยวกับการสร้างไฟล์ Add-In ใน Excel สวัสดีครับ

VBA Excel ตอน เขียน vba แก้ปัญหาสูตร Datedif (3)

มาถึงบทความรองสุดท้ายของอนุกรม vba excel เกี่ยวกับการคำนวณ Datedif มาที่ผลต่างของลำดับวันกันครับ มาดูหลักการคำนวณก่อนนำไปเขียนสูตร Excel กันครับ

4. พิจารณาเปรียบเทียบลำดับวันของวันเริ่มต้นและวันสิ้นสุด โดยมีหลักการพิจารณาดังนี้

4.1 หากลำดับวันของวันสิ้นสุดมากกว่าวันเริ่มต้น แสดงว่าลำดับวันได้ครบรอบเดือนไปแล้วดังนั้นผลต่างของลำดับวันที่เหลือจะเท่ากับ ลำดับของวันสิ้นสุดลบด้วยลำดับของวันเริ่มต้น

4.2 หากลำดับวันของวันสิ้นสุดมากกว่าวันเริ่มต้น แสดงว่ายังไม่ครบรอบเดือน หลักการในการคำนวณนั้นจะเริ่มจากหาผลต่างระหว่างลำดับวันเริ่มต้นถึงลำดับวันสิ้นเดือนของเดือนในวันสิ้นสุดและบวกด้วยลำดับของวันสิ้นสุด

หลักการคิดสามารถเขียนเป็นสูตรใน Excel ด้วยฟังก์ชัน Date ดังแสดงในภาพที่ 1

ภาพที่ 1 การคำนวณ Day Diff ด้วยสูตรใน Excel

ท่านผู้อ่านคงเห็นแล้วว่าจากบทความนี้และที่ผ่านมาในการคำนวณหา Year Diff , Month Diff และ Day Diff ด้วยสูตรฟังก์ชัน Date ของ Excel ค่อนข้างจะยุ่งยากพอสมควรเนื่องจากมีเงื่อนไขตามหลักการคิดและคำนวณที่ค่อนข้างซับซ้อน ซึ่งต้องใช้คำสั่ง IF หรือ AND ของ Excel เข้าช่วยตรวจสอบก่อน แต่อย่างไรก็ตามรูปแบบของการคำนวณก็ยังดูไม่น่าใช้งานครับ เดี๋ยวบทความสุดท้ายผมจะนำเสนอวิธีการเขียนวิธีการที่เราได้พัฒนาขึ้นมานี้จาก Excel ไปยัง VBA ซึ๋งสุดท้ายเราจะได้ฟังก์ชันที่ได้กำหนดขึ้นมาใช้งานเอง (User Function) และถูกใช้เป็นสูตรใน Excel ต่อไปครับ สวัสดีครับ

VBA Excel : ตอน เขียน vba แก้ปัญหาสูตร Datedif (2)

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

3 แนวคิดก็เริ่มจาก นำลำดับเดือนในวันสิ้นสุดลบด้วยลำดับเดือนในวันเริ่มต้น และพิจารณาผลต่างที่คำนวณได้ดังนี้

3.1 หากผลต่างมีค่ามากกว่า 0 แสดงว่าเกินรอบปีไปแล้ว และให้พิจารณาเปรียบเทียบลำดับวันของวันสิ้นสุดและวันเริ่มต้น โดยมีเงื่อนไขว่า

3.1.1 หากลำดับวันในของวันสิ้นสุดมากกว่าหรือเท่ากับวันเริ่มต้นหมายถึงว่าครบรอบเดือนใหม่แล้ว ดังนั้น ผลต่างของเดือนจะเท่ากับ ลำดับเดือนในวันสิ้นสุดลบด้วยลำดับเดือน

3.1.2 หากลำดับวันในของวันสิ้นสุดน้อยกว่าวันเริ่มต้นหมายถึงว่ายังไม่ครบรอบเดือนใหม่ ดังนั้น ผลต่างของเดือนจะเท่ากับ ลำดับเดือนในวันสิ้นสุดลบด้วยลำดับเดือนลบด้วย 1

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

3.2.1 หากลำดับวันในของวันสิ้นสุดมากกว่าหรือเท่ากับวันเริ่มต้นหมายถึงว่าครบรอบเดือนแล้ว ให้พิจารณาค่าผลต่างลำดับเดือน + 12 หาก มีค่าเท่ากับ 12 หมายถึงว่าครบรอบปีพอดี ดังนั้น ผลต่างลำดับเดือน เท่ากับ 0 หากมีค่าไม่เท่ากับ 12 ผลต่างลำดับเดือนเท่ากับ ค่าผลต่างลำดับเดือน + 12

3.2.2 หากลำดับวันในของวันสิ้นสุดมากกว่าหรือเท่ากับวันเริ่มต้นหมายถึงว่ายังไม่ครบรอบเดือนถัดไปและไม่ครบรอบปีด้วยดังนั้น ผลต่างลำดับเดือนเท่ากับ ค่าผลต่างลำดับเดือน + 11

จากที่ได้อธิบายมาข้างต้นเราสามารถใช้ฟังก์ชัน date และ ฟังก์ชันในการตรวจสอบเงื่อนไขของ Excel เข้าช่วยในการคำนวณผลต่างของเดือน แสดงได้ดังภาพที่ 1

ภาพที่ 1 การคำนวณ Month Diff ด้วยสูตร Excel

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

จากสูตร Excel ที่ได้นำเสนอในเซลล์ C9:C13 จะพบว่าเป็นการพัฒนาสูตรตามหลักการคำนวณที่เราได้คิดไว้ครับ เพราะฉะนั้น Excel จะคำนวณผลการแนวคิดของเรานั่นเองเพียงแต่ Excel มีความรวดเร็วและถูกต้องในคำนวณพื้นฐานที่ดีกว่าเราครับ เดี๋ยวบทความต่อไปจะนำเสนอการคำนวณผลต่างของวันที่เหลือจากการนับลำดับปีและลำดับเดือนแล้วครับแน่นอนยังเป็นการใช้สูตรใน Excel อยู่ครับ ท่านใดสนใจจะเห็นการเขียน vba คงต้องรอบทความสุดท้ายของอนุกรมชุดนี้ครับ สวัสดีครับ

VBA Excel ตอน เขียน vba แก้ปัญหาสูตร Datedif (1)

สูตร Datedif ใน Excel ที่ผมได้นำเสนอไปในบทความที่ผ่านมา และทิ้งท้ายให้ผู้อ่านโปรดระมัดระวังการใช้งานสูตร Datedif จากการที่ไปค้นข้อมูลจากแหล่งต่างๆก็พบคำเตือนในการใช้งานสูตรนี้ โดยใน Excel2003 และ Excel2007 ได้ถูกตัดออกไปจาก Help ของ MS Excel ไปแล้ว ซึ่งผมคิดว่าคงมีปัญหาอะไรบางอย่างที่เรายังไม่รู้แน่ๆ ดังนั้นบทความนี้จึงขอนำเสนอวิธีคิดคำนวณหาระยะห่างระหว่างวันโดยใช้ VBA ในการสร้างฟังก์ชันการคำนวณดังกล่าว โดยใช้ฟังก์ชัน Date ที่มีอยู่ในสูตรของ Excel เรามาช่วยกันคิดครับ

1 สูตรคำนวณระยะห่างระหว่างวันจะนำวันสุดท้ายมานับด้วย เช่น ระยะห่างระหว่างวันที่ 27 สิงหาคม 2010 ถึง 30 สิงหาคม 2010 จะเท่ากับ 4 วันเป็นต้น

2 แนวคิดการคำนวณเริ่มจาก จะพิจารณาปี คศ ของวันทั้งสอง (ใช้สูตร YEAR(date) ของ Excel โดยผลต่างของจำนวนปีทั้งสองจะต้องถูกนำมาตรวจสอบก่อนดังนี้

2.1 หากวันที่และเดือนของวันเริ่มต้นเกิดหลังวันที่และเดือนของวันสุดท้ายแสดงว่ายังไม่ครบรอบปี ดังนั้นผลต่างของปีที่คำนวณได้ก็ต้องถูกลบออกไป 1 ปี แต่หากเกิดก่อนวันที่และเดือนของวันสุดท้ายก็แสดงว่าเลยขวบปีมาแล้วจำนวนปีก็จะเท่ากับผลต่างที่ได้จากการคำนวณ

ตัวอย่างเช่น ผลต่างจำนวนปีของ 27/08/2008 ถึง 29/08/2010 ผลต่างที่ได้ คือ 2010 – 2008 = 2 แต่เมื่อพิจารณาตามเงื่อนไขข้อ 2.1 พบว่า 27/08 เกิดก่อน 29/08 ดังนั้นผลต่างของปีจะเท่ากับ 2 ปี ถูกมั้ยครับ

หากให้ เซลล์ B2 เป็นวันเริ่มต้น B3 เป็น วันสุดท้าย สามารถเขียนเป็นสูตร Excel ได้ภาพที่ 1 ครับ

ภาพที่ 1 Diff year

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

VBA Excel : date format กรณี สูตร Datedif

สวัสดีครับท่านผู้อ่าน ช่วงนี้พบกันค่อนข้างบ่อยหน่อยนะครับ พอดีว่ามีปัญหาการใช้งาน Excel ของผู้ใช้มาให้ผมคิด ตามที่เราได้เกริ่นไปแล้วเรื่องของสูตร Excel ที่เกี่ยวข้องกับ date และ time ของข้อมูล ล่าสุด HR ของผมส่งแบบฟอร์ม Excel มาให้กรอกข้อมูลของพนักงานในฝ่ายเพื่อเป็นข้อมูลในการนำไปประกอบกิจกรรม Walk Railly ดังแสดงเป็นตารางได้ดังนี้ครับ

ชื่อ – นามสกุล	ชื่อเล่น	อายุงาน (ปี เดือน วัน)	อายุ(ปี)
A	A	1 ปี 2 เดือน 15 วัน	32

ตารางที่ 1 ข้อมูลที่ HR ต้องการจากผม

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

=datedif(วันเริ่ม,วันสิ้นสุด,รูปแบบระยะเวลาที่ต้องการแสดงผล)

โดยรูปแบบระยะเวลาที่ต้องการแสดงผลมีดังนี้

“Y” = ระยะห่างเป็นจำนวนปี

“M” = ระยะห่างเป็นจำนวนเดือนทั้งหมด

“D” = ระยะห่างเป็นจำนวนวันทั้งหมด

“YM” = จำนวนเดือนที่เหลือหลังนับปีแล้ว

“YD” = จำนวนเดือนที่เหลือหลังจากนับปีแล้ว

“MD” = จำนวนวันที่เหลือหลังจากนับเดือนแล้ว

เรามาดูตัวอย่างกันครับ

สมมุติผมเริ่มงานวันที่ 1 ตุลาคม 2548 หากนับอายุงานผมถึงวันที่ 26 สิงหาคม 2553 ผมจะมีอายุงานเท่าไหร่น๊อ

สมมุติผมกำหนดวันเริ่มงานผมในเซลล์ B1 เป็น 1/10/2005

กำหนดวันที่ต้องการคำนวณอายุงานไว้ที่เซลล์ B2 เป็น 26/8/2010

กำหนดให้อายุงานเป็นจำนวนวันอยู่ในเซลล์ B3 จะกำหนดสูตรได้เป็น

=Datedif(B$1,B$2,”D”) ผลที่ได้จะเป็น 1,790 วัน โว้ทำงานนานเหมือนกันนะเนี่ยผม

เมื่อทดลองใช้รูปแบบการแสดงผลอื่นๆจะแสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลลัพธ์จาก Excel สำหรับการใช้รูปแบบของสูตร Datedif

คอลัมภ์ C แสดงสูตร Excel (Datedif) ที่แสดงผลลัพธ์ในคอมลัมภ์ B

ท่านผู้อ่านคงเห็นประโยชน์ของสูตร Excel สูตรนี้และคงคิดถึงการนำไปประยุกต์ใช้ในงานของท่านนะครับ ส่วนผมเองจากปัญหานี้เองทำให้นำไปใช้งานได้อีกเยอะเลยเช่น ด้าน HR ก็นำไปใช้เตือนระยะเวลาทดลองงานของพนักงานได้โดยปรับ Date ในเซลล์ B2 เป็น =Today() เท่านี้เราก็สามารถตรวจสอบระยะในการทดลองงานของพนักงานในแต่ละวันได้แล้วว่าถึงกำหนดรึยังเพื่อที่จะดำเนินการบรรจุพนักงานหรือดำเนินการประเมินผลงานของพนักงานได้อย่างเหมาะสมในช่วงเวลานั้น (ตามกฎหมายต้องไม่เกิน 119 วัน) ในด้านการซ่อมบำรุงรักษาเครื่องจักรก็สามารถนำไปใช้ในกำหนดตารางการบำรุงรักษาเครื่องจักรได้อย่างมีประสิทธิภาพ ในขณะที่บางอุตสาหกรรมผลิตภัณฑ์ที่จะนำออกสู่ตลาดต้องมีขั้นตอนการ Ageing เพื่อให้ผลิตภัณฑ์มีคุณภาพสูงที่สุด เราก็สามารถประยุกต์ใช้ฟังก์ชันนี้ในการตรวจสอบอายุของผลิตภัณฑ์ได้ หรือคนที่กำลังบันทึกผลสำหรับการประเมินผลงานด้วย KPI ก็สามารถประยุกต์ไปใช้ได้นะครับ ก็บอกแล้วครับว่ามี Excel หนึ่งโปรแกรมช่วยเราได้เยอะมากในการทำงาน เดี๋ยวบทความต่อไปผมจะนำเสนอสูตร Excel ในหมวด Date fuction อีกฟังก์ชันหนึ่ง ในการตรวจสอบระยะห่างระหว่างเวลา ท่านผู้อ่านคงอยากรู้ว่าทำไม Datedif ไม่เพียงพอเหรอ เอาเป็นว่าผมทิ้งคำถามไว้หล่ะกันครับว่า ทำไม Excel ถึงซ่อน สูตร Datedif ไว้ทำให้เราไม่สามารถเรียกใช้ได้จากเมนูบารื

ก่อนจบอย่าลืมข้อควรระวังในการคำนวณเกี่ยวกับวันที่ใน Excel นะครับ

ผู้นำ

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

จวงจื๊อเขียนถึงขงจื๊อ (มรดกจากเต๋า เพื่อเยาวชนไทย จวงจื๊อจอมปราชญ์ บุญศักดิ์ แสงระวี แปล) ตอนหนึ่งว่า วันหนึ่ง ในป่าดำริมทะเลสาบ ขงจื๊อนั่งดีดพิณร้องเพลง

ชายชราชาวประมง คิ้วขาว หนวดขาวยาวย้อยต่ำ ผมขาวกระจายคลุมไหล่ สองมือยัดใส่ในแขนเสื้อ เดินช้าๆ เข้ามาคุกเข่า มือขวา เท้าคาง นั่งฟังเสียงเพลงของขงจื๊ออย่างตั้งใจ

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

เบื้องบนรับใช้ประมุขอย่างซื่อสัตย์ เบื้องล่างกล่อมเกลาราษฎร ให้แผ่นดินนี้มีสันติ

"เช่นนั้น เขาคงเป็นศักดินาชน เป็นขุนนางผู้ใหญ่ของเจ้าผู้ครองแคว้น"

เมื่อ ศิษย์ขงจื๊อปฏิเสธว่าไม่ใช่ หยีฟู่ก็หัวเราะลั่น "เขาเหนื่อยยากปานนี้ น่ายกย่อง แต่ถ้าเขายังขืนทำเช่นนี้ต่อไป เขาจะยิ่งห่างจากมหามรรคออกไปทุกวัน"

พูดจบแล้วหยีฟู่ก็เดินจากไป ขงจื๊อฟังคำหยีฟู่จากศิษย์ ก็รู้ว่าชายชราผู้นี้มีสติปัญญาล้ำเลิศ ก็รีบลุกเดินตามไปทัน ขณะที่หยีฟู่ กำลังก้าวลงเรือ

"ถ้อยคำที่ ท่านกล่าว ดูเหมือนจะยังไม่จบ" ขงจื๊อนอบน้อม "ข้าพเจ้ารักการศึกษามาแต่เด็ก ขณะนี้อายุ 69 ปี ยังโง่เขลา ยังมิได้ฟังมหามรรค ใคร่ขอคำสั่งสอนจากท่านอีก"

"คนเรามีโรคร้าย 8 ประการ ความทุกข์ 4 ประการ" หยีฟู่วิสัชนา "จะไม่สนใจมิได้"

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

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

โรคร้ายทั้ง 8 ประการนี้ ต่อภายนอกก็ก่อกวนคนอื่น ต่อภายในก็ทำร้ายตัวเอง นี่เป็นสิ่งที่ผู้มีสติปัญญามิยอมชิดใกล้"

"ถ้าเช่นนั้น ที่ว่าความทุกข์ 4 ประการนั่นเล่า คืออย่างไร" ขงจื๊อถาม

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

มองเห็นความผิดของตน แต่ไม่ยอมแก้ไข ครั้นเมื่อได้ฟังคำตักเตือนของคนอื่น ก็กลับโมโหโกรธา นี่เรียกว่า ยโส ถ้าความเห็นนั้นตรงกับตน ก็ว่าถูก ถ้าความเห็นนั้นไม่ตรงกับตน แม้จะดีก็ว่าไม่ดี

นี่เรียกว่า ทะนง

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

หนังสือเล่มนี้ เป็นหนังสือที่พิมพ์เพื่อเยาวชนไทย จึงมีภาคการ์ตูน คำ "มหามรรค" ในภาคการ์ตูน ถูกเฉลยว่า คือการแสวงหาสติปัญญาอันเลิศ

บทสรุปคำสอน จากหยีฟู่ หากผู้นำยังติดข้อง 8 โรคร้าย จมอยู่กับ 4 ความทุกข์ โอกาสที่จะบรรลุถึงขั้น "สติปัญญาเลิศ" นำมาแก้ปัญหาให้บ้านเมือง สร้างความสงบให้แผ่นดิน ก็คงไม่มี.

ที่มา คอมลัมภ์ กิเลน ประลองเชิง หนังสือพิมพ์ไทยรัฐ ฉบับวันที่ 25 สิงหาคม 2553

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

vba excel ตอน date format และ datetime function

สวัสดีครับ บทความตอนนี้ผมขอเสนอรูปแบบและฟังก์ชันหนึ่งซึ่งมีประโยชน์และพบเจอมากที่สุด นั่นคือ date format และ datetime function จากบทความเกี่ยวกับการประมวลผลด้วย KPI ที่ได้นำเสนอแนวคิดของการประเมินผลงานด้วย KPI หรือ BalanceScoreCard มาแล้วนั้น ท่านผู้อ่านจะเห็นว่ายังไงแล้วการเขียน vba ของเราต้องเกี่ยวข้องกับข้อมูลด้านเวลาหรือรูปแบบวันที่แน่ๆ บทความนี้ผมเลยขอนำเสนอการกำหนด date format การใช้ datetime function ข้อควรระวังในการใช้งาน รวมถึงการแปลงข้อมูลจาก String to datetime format การ Convert string to datetime ยังพบปัญหาอยู่มากในการจัดการฐานข้อมูลทั้งใน excel เอง datetime ใน sql หรือ datetime ใน php เอาไว้จะเล่าให้ฟังในคราวต่อๆไปนะครับ

การกำหนด date format ให้กับ เซลล์ ใน worksheet คลิกเลือกเซลล์ที่ต้องการกำหนดรูปแบบใหม่จากนั้นก็คลิกเมาส์ปุ่มขวาแล้วเลือก จัดรูปแบบเซลล์ ก็จะปรากฎหน้าต่าง การจัดรูปแบบเซลล์ จะปรากฎหน้าต่าง การจัดรูปแบบเซลล์ ให้เลือกแท็บ ตัวเลข เลือก วันที่ ใน List box ประเภทและเลือกลักษณะของ date format ที่ต้องการ ดังแสดงในภาพที่ 1 คลิก OK ก็เสร็จสิ้นการกำหนดรูปแบบของวันแล้วครับ ง่ายดีมั้ยครับ นั่นหมายถึงว่าเราได้กำหนด date format ให้กับเซลล์ที่เราเลือกแล้วในตัวอย่างนี้หมายถึงว่า วันที่จะอยู่ในรูปแบบ dd/mm/yyyy โดย yyyy จะเป็นปี คศ นะครับอย่าลืม 24/8/2553 excel จะมองเป็นวันที่ 24 สิงหาคม คศ 2553 โว้ ต้องระวังนะครับ

ภาพที่ 1 การกำหนด date format ให้กับเซลล์ที่เลือก

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

ฟังก์ชันที่น่าสนใจเกี่ยวกับ datetime

Day จะคืนค่าวันที่ของเดือนกลับมาให้เราครับ เช่น =DAY(24/8/2010) ก็จะคืนเลข 24 กลับมา

MONTH จะคืนค่าลำดับของเดือน(1-12) กลับมาให้เราครับ เช่น =DAY(24/8/2010) ก็จะคืนเลข 8 กลับมา

YEAR จะคืนค่าปีกลับมาให้เราครับ เช่น =DAY(24/8/2010) ก็จะคืนเลข 2010 กลับมา

WEEKDAY จะคืนค่าตัวเลขตั้งแต่ 1 –7 ซึ่งเป็นวันในสัปดาห์มาให้ครับ

TODAY() จะส่งกลับวันที่ปัจจุบันมาให้ครับ ก็จะคืน 24/8/2010

หากข้อมูลในเซลล์เป็นชนิดข้อความ (Text format) เราสามารถใช้ฟังก์ชัน DateValue ทำการ Convert string to datetime โดยมีรูปแบบการใช้ datetime function ดังนี้

DateValue(date_text) เช่น =DateValue(“24/8/2010”)

หรือสมมุติให้ เซลล์ B1 เป็นข้อมูลแบบ text มีข้อมูลเป็น 24/8/53 ดังนั้นหากเรากำหนดให้เซลล์ C1 = DateValue(B1) ผลลัพธ์จะได้ว่าเซลล์ C1 มีข้อมูลเป็นแบบ Datetime format โดยมีค่าเป็นวันที่ 24 เดือนสิงหาคม คศ. 2010

ครับจากที่ได้แนะนำท่านผู้อ่านมาข้อควรระวังนะครับให้ตรวจเช็ครูปแบบข้อมูลให้ถูกต้องก่อนนะครับ รูปแบบข้อมูลที่ได้จาก excel หลากหลายต้องถูกปรับให้อยู่ในรูปแบบ Datetime format เดียวกันก่อนนะครับก่อนนำไปประมวลผลต่อ มิฉะนั้นจะเกิดความผิดพลาดอย่างร้ายแรงได้นะครับ เอาหล่ะครับมาถึงปัญหาหนึ่งที่เราพบ หากต้องการนับระยะห่างระหว่างวันสองวันจะทำอย่างไรหล่ะครับ เช่น ระยะห่างตั้งแต่วันที่ 24/8/53 ถึง 1/9/53 เราจะใช้ datetime function ตัวไหนมาจัดการ เดี่ยวรอติดตามในบทความต่อไปครับ

ประเมินผล KPI ด้วย Excel (1)

บทความนี้เป็นการนำเสนอวิธีการประเมินผลงานของพนักงานด้วย ตัวชี้วัดประสิทธิภาพ KPIs จุดมุ่งหมายในการนำเสนอก็เพื่อเป็นพื้นฐานให้กับท่านผู้อ่านที่กำลังจะถูกมอบหมายจากผู้บริหารในการประเมินผลงานของลูกน้องหรือทั้งองค์กร ทุกท่านคงรู้จัก Balance Score Card กันมาบ้างแล้วนะครับ ซึ่งเป็นวิธีการบริหารงานที่ได้รับความนิยมในปัจจุบัน การวัดประสิทธิภาพขององค์กรหรือหน่วยงานภายในจะต้องวัดกันให้ครบทุกด้าน เช่น ด้านการเงิน ด้านลูกค้า ด้านกระบวนการภายใน ด้านการเรียนรู้และการเจริญเติบโต เป็นต้น ทีนี้ในแต่ละด้านก็จะมีตัวชี้วัดที่สำคัญไว้บ่งชี้ประสิทธิภาพของหน่วยงานนั้นๆ เช่นด้านการเงิน อาจจะใช้ตัวชี้วัดเป็น ผลกำไรต่อปีของบริษัท เป็นต้น ขั้นตอนการกำหนดตัวชี้วัดเหล่านี้จะต้องถูกทำอย่างเป็นขั้นเป็นตอน ซึ่งท่านสามารถศึกษาได้ในตำราด้าน Balance Score Card หรือ KPIs ครับ ซึ่งหลังจากได้กำหนดตัวชี้วัดได้แล้ว ผู้ออกแบบตัวชี้วัด ต้องกำหนดเป้าของตัวชี้วัด ในบทความนี้ขอยกตัวอย่างเป้าหมายง่ายๆเพื่อให้เห็นแนวคิดการประเมินผล KPIs สมมุติว่าตำแหน่งงานของผมมี KPIs ทั้งหมด 3 ตัว โดย KPIs แต่ละตัวมีเป้าดังแสดงในตารางที่ 1

ตารางที่ 1 KPIs และเป้าหมายที่กำหนด

ชื่อตัวชี้วัด	เป้าหมาย
A	ต้องมากกว่า 80
B	ต้องมากกว่า 90
C	ต้องมากกว่า 100

ในการประเมินผลงานโดยส่วนใหญ่มักจะคำนวณประสิทธิภาพออกมาเป็นเปอร์เซนต์ และ KPIs แต่ละตัวมีความสำคัญไม่เท่ากัน ดังนั้นจึงต้องมีการกระจายน้ำหนักคะแนนสำหรับประเมินผลงาน(ขั้นตอนการกระจายน้ำหนักต้องอยู่ในการประชุม KPIs เพื่อร่วมกันพิจารณา) ดังแสดงในตารางที่ 2

ตารางที่ 2 KPI เป้าหมายที่กำหนดและน้ำหนักคะแนน

ชื่อตัวชี้วัด	เป้าหมาย	น้ำหนักคะแนน
A	ต้องมากกว่า 80	0.5
B	ต้องมากกว่า 90	0.3
C	ต้องมากกว่า 100	0.2

ข้อสังเกต เนื่องจากเราต้องการประเมินผลงานในรูปแบบเปอร์เซนต์ ดังนั้นผลรวมของน้ำหนักคะแนนจึงต้องเท่ากับ 1

ทีนี้มาดูกันครับ สมมุติว่ามีการประเมินผลงานทุกๆ 1 เดือน ดังนั้นหลังสิ้นเดือนเราจะมีข้อมูลผลการชี้วัด(ผลการปฏิบัติงานของเรานั่นแหล่ะครับ) ดังแสดงในตารางที่ 3

ตารางที่ 3 ผลการชี้วัด KPIs ประจำเดือน……

ชื่อตัวชี้วัด	น้ำหนักคะแนน	เป้าหมาย	ผลการชี้วัด
A	0.5	ต้องมากกว่า 80	60
B	0.3	ต้องมากกว่า 90	50
C	0.2	ต้องมากกว่า 100	100

การประเมินผลจะเริ่มจาก การคำนวณประสิทธิภาพของเราในแต่ละตัวชี้วัด ซึ่งต่อไปเราจะเรียกว่า ประสิทธิภาพสัมพัทธ์ (Relative Peformance) ซึ่งสามารถคำนวณได้จากสูตร Relative Peformance = ผลการชี้วัด / เป้าหมาย

และสามารถคำนวณประสิทธิภาพสัมบูรณ์ (Absolute Performance) ของตัวชี้วัดได้จากสูตร Absolute Performance = น้ำหนักคะแนนของตัวชี้วัด*Relative Peformance

จากลักษณะวิธีการประเมินผลและการบันทึกข้อมูลเราสามารถใช้ Excel ในการบริหารจัดการและช่วยคำนวณได้ โดยเฉพาะในทางปฏิบัติองค์กรเราคงไม่มีตัวชี้วัดเพียงแค่ 3 ตัวนี้ ที่สำคัญการทำรายงานสรุปผล KPIs ของทั้งองค์กรให้ผู้บริหารทราบ ซึ่งในปัจจุบันแม้จะมีซอฟท์แวร์จัดการ KPIs ออกมาช่วยในจุดนี้ แต่หากเราประยุกต์ใช้ฟังก์ชันใน excel เช่น VLOOKUP INDEX and MATCH หรือใช้ vba มาช่วยในการจัดการหรือลดการทำซ้ำ ก็จะสามารถลดค่าใช้จ่ายในจุดนี้ได้ และยังเป็นการใช้งาน excel ให้เกิดประโยชน์สูงสุด ในบทความต่อๆไปของผมจะนำเสนอการใช้ประยุกต์ใช้ฟังก์ชันใน excel และ ใช้ vba มาช่วยในการจัดการ ตั้งแต่การบันทึกหัวข้อ KPIs การค้นหาเพื่อแก้ไข การประเมินผล การทำรายงานสรุปผล เดี๋ยวมาดูกันในบทความต่อๆไปครับ
ระบบ KPI ตอน การกระจาย KPI ตามโครงสร้างองค์กร

ระบบ KPI ตอน กระจายน้ำหนัก KPI ตามระดับพนักงาน

ระบบ KPI ตอน ฐานข้อมูลพนักงานใน Excel

ระบบ KPI ตอน การประเมินผล KPI ของพนักงาน (1)

VBA Excel ตอน Google chrom

วันนี้ขอนอกเรื่อง engineering with vba excel กันซักนิดครับ ที่ผ่านท่านผู้อ่านใช้บริการ เว็บเบราว์เซอร์ ตัวไหนในการท่องเว็บ ตัวผมเองใช้งานอินเตอร์เน็ตมาแล้วสิบกว่าปี เป็นสาวกของ IE มาโดยตลอด จนกระทั่งต้นปีที่ผ่านมาด้วยปัญหาความไม่เสถียรของ IE ทำให้ผมเบนความสนใจมาที่ opensource programm ที่ชื่อ Mozilla Firefox ซึ่งจากการใช้งานพบว่าขจัดปัญหาที่ผมพบเจอใน IE ได้เป็นอย่างดี ทำให้รู้ว่าในโลกอินเตอร์เน็ตของฟรีมีคุณภาพยังรอให้เราใช้อีกเยอะ และจากการที่ใช้บริการ Search Engine ของอาจารย์กู๋ (google) มาโดยตลอดพบว่า google มีบริการของฟรีให้เรามากมายจนเรียกได้ว่าเกือบครบวงจรเว็บเบราว์เซอร์ ก็เช่นกัน goolgle ได้นำเสนอ Google chrom ให้เป็นทางเลือกของผู้ที่ชอบของฟรีมีคุณภาพ ผมจึงทดลองนำมาติดตั้งและใช้งาน Google chrom ดู สิ่งแรกที่ประทับใจคือ ความสะอาดตาของตัวโปรแกรมไม่มีไอคอนให้เยอะวุ่นวาย ตามด้วยเรื่องความเร็วในการใช้งาน ส่วนเรื่องของความปลอดภัยคงต้องรอดูต่อไปครับว่าเมื่อเทียบกับ Mozilla Firefox แล้วตัวไหนดีกว่ากัน ท่านผู้อ่านสามารถเข้าไป download โปรแกรมและศึกษาคุณลักษณะของGoogle chrom ได้ที่นี่เลยครับ http://www.google.com/chrome แล้วมาแชร์ความรู้กันดูนะครับ ของฟรีมีคุณภาพยังมีอยู่ครับ

Goal seek : เทคนิคการเดาค่าเริ่มต้น

จากบทความหลายๆเรื่องที่ผ่านมาของผมได้ยกตัวอย่างการใช้ฟังก์ชัน Goal seek สำหรับการหาผลเฉลยโดยประมาณมาบ้างแล้ว โดยจะพบว่า Goal seek จะเหมาะสำหรับการค้นหาผลเฉลยของตัวแปรไม่ทราบค่าตัวแปรเดียว โดยผู้ใช้ต้องกำหนดค่าเริ่มต้นของตัวแปรนั้นๆให้กับ excel การกำหนดค่าที่ไม่เหมาะสมอาจทำให้ไม่สามารถหาผลเฉลยได้ ดังนั้นการเดาค่าเริ่มต้นให้กับ excel จึงมีความสำคัญเป็นอย่างมาก เรามาดูกันว่าการเดาค่าเริ่มต้นจะมีหลักการอย่างไร

1. ผู้ใช้ต้องทราบธรรมชาติของปัญหานั้น(Characteristic of problem) เพื่อให้มั่นใจว่าปัญหาที่กำลังแก้มีผลเฉลยอยู่จริง และความต้องการผลเฉลยที่เหมาะสม เนื่องด้วยฟังก์ชันที่แก้ปัญหาอาจมีผลเฉลยได้หลายค่าเช่นฟังก์ชันโพลิโนเมียล ซึ่งในบางกรณีเราต้องการผลเฉลยที่มีค่าเป็นบวกเท่านั้นเป็นต้น
2. จัดรูปแบบฟังก์ชันที่ต้องการแก้ปัญหาให้อยู่ในรูปแบบ f(x) = 0 ดังได้แสดงในบทความเรื่องการแก้ปัญหาในการออกแบบ
3. ใช้ฟังก์ชันใน Excel พล๊อตกราฟแสดงความสัมพันธ์ระหว่างฟังก์ชันและตัวแปรที่ไม่ทราบค่าตามความสัมพันธ์ในข้อที่ 2 เพื่อให้สามารถประมาณตำแหน่งของผลเฉลยที่ต้องการได้เหมาะสม (ผลเฉลยของฟังก์ชันคือตำแหน่งในแนวแกน x ที่ค่าของฟังก์ชันมีค่าเท่ากับศูนย์ ซึ่งเราเรียกว่าการหา root ของฟังก์ชันนั่นเอง ดังแสดงในภาพที่ 1 )เนื่องด้วยฟังก์ชันที่แก้ปัญหาอาจมีผลเฉลยได้หลายค่าดังอธิบายในข้อที่ 1 ดังนั้นการพล๊อตกราฟจึงเป็นการช่วยให้ผู้ใช้ได้ผลเฉลยที่เหมาะสมและตรงตามธรรมชาติของปัญหานั้นๆ
4. แก้ปัญหาด้วย Goal Seek โดยเดาค่าเริ่มต้นจากการพิจารณากราฟที่ได้จากการพล็อตด้วย excel ในข้อที่ 3 จากภาพตัวอย่างหากต้องการผลเฉลยที่มีค่าเป็นบวก เราจะเดาค่าเริ่มต้นด้วยจำนวน 5 เป็นต้น

กราฟความสัมพันธ์ระหว่าง f(x) และ x ซึ่งถูกพล๊อตใน excel

หวังว่าเทคนิคที่ผมได้นำเสนอในบทความนี้คงเป็นประโยชน์ต่อท่านผู้อ่านนะครับ สวัสดีครับ

VBA Excel ตอน ชนิดของข้อมูล (Data type)

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

ประเภท	ชนิดข้อมูล	ใช้พื้นที่ในหน่วยความจำ(ไบต์)	ขอบเขตข้อมูลที่ใช้
ตัวเลข	Byte	1	จำนวนเต็มระหว่าง 0 - 255
	Integer	2	จำนวนเต็มระหว่าง –32,768 – 32,767
	Long	4	จำนวนเต็มระหว่าง –2,147,483,648 ถึง 2,147,483,647
	Single	4	เลขทศนิยมระหว่าง –3.4028235E+38 ถึง –1.401298E-45 (กรณีค่าลบ) 1.401298E-45 ถึง 3.4028235E+38  (กรณีค่าบวก)
	Double	8	เลขทศนิยมระหว่าง –1.79769313486231570E+308 ถึง –4.94065645841246544E-324 (กรณีค่าลบ) 4.94065645841246544E-324 ถึง1.79769313486231570E+308 (กรณีค่าบวก)
	Currency	8	-922,337,203,685,477.5808 ถึง 922,337,203,685,477.5807
ข้อความ	String	10 + ความยาว String	เป็นข้อความมีความยาวตั้งแต่ 0 ถึง 2 พันล้านตัวอักษรซึ่งกำหนดค่าในเครื่องหมาย “ “ เช่น “Excel”
วันและเวลา	Date	8	วันระหว่าง 1 มค คศ 100 ถึง 31 ธค คศ 9999 และเวลาระหว่าง 0:00:00 ถึง 23:59:59
Logic	Boolean	2	True กับ False
ไม่กำหนดชนิด	Variant	ไม่แน่นอน	ขึ้นอยู่กับค่าที่ใช้เก็บ
กำหนดโดยผู้ใช้ Object	User-define Object	ไม่แน่นอน 4	ขึ้นอยู่กับผู้ใช้กำหนด สำหรับ Object

 

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

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

การหา root ลำดับที่ n ของจำนวนใดๆด้วยวิธีเชิงตัวเลข

หากให้ x เป็นรากที่ n ของจำนวนจริง C ใดๆ เราสามารถเขียนเป็นสมการได้ดังนี้

หากทำการยกกำลัง n ทั้งสองข้างของสมการและจัดรูปแบบสมการใหม่สามารถเขียนได้เป็น

จากสมการนี้ เราสามารถใช้ Tayler Series เพื่อประมาณค่า x ได้ดังสมการ

เมื่อ x(k+1) คือค่าประมาณการครั้งที่ k+1

x(k) คือค่าประมาณการครั้งที่ k

จากสมการนี้ เราสามารถใช้ VBA เพื่อทำการคำนวณแบบทำซ้ำเพื่อหารากที่ n ของจำนวนจริง C ใดๆได้ โดยเริ่มต้นการคำนวณครั้งแรกต้องเดาค่า x เริ่มต้นก่อน และการคำนวณจะสิ้นสุดลงเมื่อ x(k+1) มีการเปลี่ยนแปลงค่าอยู่ในช่วงที่ยอมรับได้เมื่อเทียบกับค่า x(k) วิธีการคำนวณเช่นนี้จะเรียกว่า ระเบียบวิธีการของ นิวตันราฟสัน (Newton Rapson Method) ซึ่งเคยนำเสนอในบทความต้นๆของ blog นี้มาแล้วโดยใช้ฟังก์ชัน Goal Seek

โค้ดใน VBA สามารถแสดงได้ดังภาพ

VBA Code (For…Next)

จาก VBA Code นอกจากจะแสดงวิธีการคำนวณเชิงตัวเลขเพื่อหาค่ารากที่ n ของจำนวนจริงใดๆแล้ว ยังเป็นการแสดงวิธีการใช้คำสั่ง For…Next และการออกจากลูปเมื่อเงื่อนไขที่กำหนดไว้เป้นจริง (เงื่อนไขการตรวจสอบความละเอียดของผลการคำนวณ) โดยในความเป็นจริงแล้ว การคำนวณลักษณะนี้น่าจะใช้คำสั่ง While….Wend จะเหมาะสมกว่า ลองปรับเปลี่ยนโค้ดและนำไปใช้งานดูนะครับ

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

VBA Code (While…Wend)

จากภาพเราจะเรียกใช้ฟังก์ชัน Root ผ่าน macro ชื่อ test โดยผ่านค่า C และ n จาก เซลล์ B1 , B2 และคืนค่ารากที่ n กลับมาแสดงในเซลล์ B3 ครับ โดยรากที่ n ของ C ที่คำนวณได้จะมีความละเอียดถึง 15 ตำแหน่ง และใน code ที่ผมนำเสนอได้มีการป้องกัน Excel value ที่ไม่พึงประสงค์ไว้ด้วยลองทำความเข้าใจในโค้ดดูนะครับ