การใช้งาน Excel : การอ้างถึง Worksheets ใน VBA

สวัสดีครับท่านผู้อ่าน How to excel ทุกท่านนะครับ หายไปหลายวันด้วยภาระกิจด้านอื่นๆที่ต้องสะสางครับ บทความนี้เป็นบทความต่อเนื่องจากบทความที่ผ่านมาครับ บทความก่อนหน้าผมได้นำเสนอการอ้างถึงไฟล์ Excel หรือ Workbooks ที่ถูกเปิดอยู่ เพื่อที่เราจะได้เข้าไปอ่านข้อมูลในเซลล์ หรือเข้าไปแก้ไขข้อมูลในเซลล์นั้นๆได้ ท่านผู้อ่านคงทราบกันดีว่า ในWorkbooks ก็จะประกอบไปด้วย worksheets หลายๆ worksheets และใน worksheets ก็จะประกอบไปด้วยเซลล์ต่างๆ ซึ่งการประมวลผลของ Excel จะดำเนินการระหว่างเซลล์ ซึ่งจากที่ได้กล่าวมาการประมวลผลข้อมูลระหว่างเซลล์ข้าม Workbooks หรือ Worksheets นั้นสามารถทำได้หากเราสามารถอ้างถึงตำแหน่งของเซลล์ได้ถูกต้อง(workbooks ,worksheets,row and column) เอาหล่ะครับวันนี้เรามาดูวิธีการอ้างถึง Worksheet ในแต่ละวิธีกันเลยครับ

1. การอ้างถึง Worksheets ที่กำลังถูกใช้งานอยู่ (active worksheets) เราจะใช้สมบัติ ActiveSheet ในการอ้างถึง เช่น หากต้องการทราบชื่อของ WorkSheet ที่กำลังใช้งานอยู่สามารถเขียนโค้ดได้ดังนี้

sheetsName = ActiveSheet.Name

2. การอ้างถึงผ่าน object ของ WorkSheet

ตัวอย่างเช่น

 Dim ws As Worksheet
For Each ws In Worksheets
   ws.Select 
   ListBox1.AddItem ws.Name
Next ws
ตัวอย่างนี้เป็นการแสดงชื่อของ WorkSheet ทั้งหมดใน Workbooks ที่กำลัง Active
โดยแสดงใน ListBox

3.การอ้างถึงแบบชัดแจ้ง (Explicitly)

เราสามารถใช้สมบัติของ WorkSheets ได้ในกรณีที่ทราบชื่อ WorkSheet
ตัวอย่างเช่นเราสามารถลบ WorkSheets ได้โดยผ่านสมบัติของ WorkSheets
โดยต้องระบุชื่อของWorkSheetที่ต้องการลบ ดังแสดงในโค้ด

‘workshtname คือ ชื่อของWorkSheetที่ต้องการลบ 

Worksheets(workshtname).Delete

4.การอ้างถึง WorkSheets โดยใช้ Index

การนับจำนวน WorkSheets จะใช้สูตรใน VBA เป็น

Worksheets.Count

Index หรือ ลำดับของ WorkSheet ใน Workbooks จะเริ่มตั้งแต่ 1ถึง
จำนวน WorkSheets โดย WorkSheets(1) จะหมายถึง WorkSheet แรก
ขณะที่WorkSheets(Worksheets.Count)จะหมายถึง WorkSheet สุดท้าย

เอาหล่ะครับท่านผู้อ่าน How to excel มาถึงตอนนี้ Excel Utility ที่ผมได้ปูพื้นฐาน
มาให้ท่านผู้อ่านเพื่อเตรียมพร้อมในการเข้าสู่เนื้อหาของการประเมินผลด้วย KPI 
ใกล้สมบูรณ์แล้ว บทความต่อไปผมอยากนำเสนอ การนำเสนอผลการประเมินKPI
ด้วยรูปภาพกันอีกซักบทความก่อนเข้าสู่การประเมินผล KPI กันครับ สวัสดีครับ

การใช้งาน Excel : การอ้างถึงไฟล์ Excel ใน VBA

สวัสดีครับท่านผู้อ่าน How to excel บทความนี้เกิดจากความรับผิดชอบต่อท่านผู้อ่านในบทความเรื่อง การเปิดไฟล์ Excel ใน VBA ซึ่งการทำงานใน vba ส่วนใหญ่จะมีการอ้างถึงไฟล์ Excel ที่เปิดใช้งานอยู่ ซึ่งมีหลายวิธี เรามาทำความเข้าใจในแต่ละวิธีกันครับ

1. การอ้างถึง Workbooks หรือไฟล์ Excel ที่กำลังทำงานอยู่หรือกำลัง Focus เราจะใช้

ActiveWorkbook properties

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

หลังจากการใช้งาน Excel บนWorkbooks เสร็จสิ้น หากต้องการปิด Workbooks โดยไม่บันทึก จะใช้คำสั่ง

ActiveWorkbook.Close False หมายถึงให้ปิด Workbooks ที่กำลัง Focus โดยไม่บันทึก

หากต้องการ บันทึกข้อมูลก่อนปิด จะใช้คำสั่ง

ActiveWorkbook.Close True หมายถึงให้ปิด Workbooks ที่กำลัง Focus โดยบันทึกก่อนปิดไฟล์

หากต้องการทราบชื่อหรือตำแหน่งของไฟล์ Excel หรือ Workbook ที่กำลัง Focus สามารถใช้คำสั่ง

ActiveWorkbook.Path  หมายถึงให้คืนค่าตำแหน่งที่เก็บไฟล์ Excel นี้กลับ

ActiveWorkbook.Name หมายถึงให้คืนชื่อไฟล์ Excel นี้กลับ

2.การอ้างถึง Workbooks หรือไฟล์ Excel ที่กำลังรัน โค้ด VBA เนื่องจาก โค้ด VBA ไม่ได้อยู่ใน Workbooks ที่กำลัง Focus เสมอไป เราจะใช้ ThisWorkbook property ในการอ้างถึง ตัวอย่างการใช้งาน เช่น

ThisWorkbook.Path

ThisWorkbook.Name

3.การอ้างถึง Workbooks หรือไฟล์ Excel ที่เปิดอยู่ทั้งหมดด้วยWorkbooks collection ตัวอย่างเช่น การแสดงชื่อ Workbooks ทั้งหมดที่เปิดลงใน ListBox1

Dim wb As Workbook For Each wb In Workbooks ListBox1.AddItem wb.Name Next wb

4.การอ้างถึง Workbooks หรือไฟล์ Excel แบบชัดแจ้ง (explicit name) เป็นการอ้างถึง Workbooks หรือไฟล์ Excel ด้วยชื่อ Workbooks หรือไฟล์ Excel นั่นเช่น หากต้องการปิดไฟล์ Excel ชื่อ Test.xls โดยไม่บันทึก เราจะใช้คำสั่งดังนี้

Workbooks(“Tesr.xls”).Close False
หรือหากต้องการ Focus หรือ Active ก็สามารถสั่งได้ดังนี้
Workbooks(“Tesr.xls”).Active
การอ้างถึงด้วยวิธีนี้สิ่งที่ควรระวังให้มากคือ ชื่อ Workbooks หรือไฟล์ Excel ที่อาจมีการเปลี่ยนแปลงได้ซึ่งจะทำให้เกิดความผิดพลาดในการะหว่างทำงานได้ (Run-time error)

5. การอ้างถึง Workbooks หรือไฟล์ Excel ด้วย Index Excel จะกำหนดค่า Index ให้กับ Workbooks หรือไฟล์ Excel ที่เปิด โดยกำหนดตามลำดับที่เปิดก่อนหลัง ดังนั้นหากต้องการอ้างถึง Workbooks หรือไฟล์ Excel ที่เปิดเป็นไฟล์แรก ก็จะอ้างถึงได้ดังนี้

Workbooks(1).Active

สิ่งที่ควรระวังในการอ้างถึงคือ Index ของ Workbooks หรือไฟล์ Excel ที่เปิด จะมีการเปลี่ยนแปลงหากมีการปิดไฟล์ โดยจะมีการจัดเรียง Index ใหม่ทุกครั้ง

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

VBA Excel ตอน การเปิดไฟล์ Excel ด้วย VBA

สวัสดีตอนเช้าครับท่านผู้อ่าน How to excel บทความนี้ยังเป็นการนำเสนอ Excel utility สำหรับเตรียมพร้อมในการสร้างระบบการประเมินผลด้วย KPI ที่ผมได้นำเสนอเป็น Serie ค้างไว้ เนื่องด้วยอยากปูพื้นฐานถึงที่มาที่ไปของการทำงานในระบบการประเมินผลของเรา Excel utility ในบทความนี้ขอกล่าวถึงการเปิดไฟล์ Excel ครับ เนื่องด้วยหากท่านผู้อ่านย้อนกลับยัง Serie ระบบการประเมินผลด้วย KPI ท่านจะเห็นว่า เราจำเป็นต้องเข้าไปอ่านข้อมูลการประมวลผล KPI ของ KPI ระดับต่างๆดังแสดงในภาพที่ 1

ภาพที่ 1 ลักษณะการเข้าถึงข้อมูลผลการชี้วัด KPI

สิ่งแรกที่ต้องทำในการเข้าไปอ่านข้อมูลและดึงข้อมูลมาเก็บที่ไฟล์ Excel (สรุปผล KPI.xls) ก็คือต้องทำการเปิดไฟล์ก่อนครับ เริ่มจากสร้างปุ่มกด Worksheets ของไฟล์ สรุปผล KPI.xls สำหรับเรียกใช้งาน Macro และสร้าง Macroในการเปิดไฟล์ สมมุติให้ชื่อว่า ImportData_Click ซึ่งมีรายละเอียดดังนี้ครับ

Sub ImportData_Click()
Dim cur As Integer
Dim actWBName As String
actWBName = ActiveWorkbook.Name
With Application.FileDialog(msoFileDialogOpen)
.Title = "Select File"
.AllowMultiSelect = False
.Show
Workbooks.Open (.SelectedItems(1))
cur = Workbooks.Count
Workbooks(cur).Activate

‘ Do some thing

Workbooks(cur).Close
Workbooks(actWBName).Activate
End With
End Sub

สามารอธิบายการทำงานของ VBA โค้ดได้ดังนี้ครับ

Dim cur As Integer
Dim actWBName As String

กำหนดตัวแปร cur สำหรับเก็บตำแหน่งของ workbooks ล่าสุด กำหนดตัวแปร actWBName สำหรับเก็บชื่อไฟล์ Excel ที่เรียกใช้งาน Macro

actWBName = ActiveWorkbook.Name

เก็บชื่อไฟล์ Excel ที่กำลังใช้งานอยู่ไว้ในตัวแปร actWBName

With Application.FileDialog(msoFileDialogOpen)
เรียก Dialog สำหรับเปิดไฟล์ด้วยฟังก์ชัน FileDialog และกำหนดโหมดเปิดไฟล์ด้วยตัวแปร msoFileDialogOpen

.Title = "Select File"
.AllowMultiSelect = False
.Show
กำหนดข้อความใน Title ของDialog เป็น Select File

กำหนดให้ผู้ใช้งานสมารถเลือกเปิดไฟล์ได้ไฟล์เดียวหรือหลายไฟล์ด้วย properties AllowMultiSelect ในตัวอย่างนี้กำหนดให้เลือกได้ไฟล์เดียว (False)

สั่งให้แสดงDialogด้วย properties Show

เมื่อ ถึงคำสั่งในบรรทัดนี้ Excel จะแสดง Dialog สำหรับเปิดไฟล์ ดังแสดงในภาพที่ 2

ภาพที่ 2 Dialog สำหรับเปิดไฟล์

Workbooks.Open (.SelectedItems(1))
cur = Workbooks.Count
Workbooks(cur).Activate

‘ Do some thing

Workbooks(cur).Close

เปิดไฟล์ Excel ที่เลือก (.SelectedItems(1)) ด้วยคำสั่ง Open ของ object Workbooks

เก็บตำแหน่งไฟล์ Excel ล่าสุดที่เปิดไว้ (ตำแหน่งไฟล์ Excel ที่เราเปิดนั่นแหล่ะครับ) เพื่อใช้อ้างอิง Workbooks โดยเก็บไว้ที่ตัวแปร cur

Active ไฟล์ Excel ที่เปิดล่าสุดผ่านการอ้างอิงเพื่อเตรียมเข้าไปอ่านหรือทำงานในไฟล์ Excel นั้น

ดำเนินการภายในไฟล์ Excel ที่ Active

เมื่อเสร็จแล้วปิดไฟล์ Excel ที่ Active ด้วยคำสั่ง Close

Workbooks(actWBName).Activate
End With

ทำการกำหนดให้ไฟล์ Excel ที่เรียกใช้งาน Macro นี้ Activate โดยผ่านการอ้างอิงด้วยชื่อไฟล์ ในตัวแปร actWBName

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

VBA Excel ตอน ระเบียบวิธีการทำซ้ำ (Interation)

สวัสดีครับท่านผู้อ่าน How to excel ทุกท่านครับ บทความที่อยากจะนำเสนอในวันนี้ก็คือ ระเบียบวิธีการทำซ้ำแบบหนึ่งจุด (one-point interation method) ซึ่งหากท่านผู้อ่านได้ติดตามการนำเสนอบทความของผมในกลุ่มของการคำนวณทางวิศวกรรมด้วยระเบียบวิธีเชิงตัวเลข จะเห็นว่าผมได้นำเสนอการใช้ Goal Seek ซึ่งเป็น Excel function ที่ใช้ในการหาค่าเป้าหมาย โดยผมได้นำเสนอการประยุกต์ใช้ในการหารากของสมการ การแก้ปัญหาในด้านการออกแบบ เป็นต้น โดยพบว่าหลักของการคำนวณของฟังก์ชัน Goal Seek เป็นลักษณะของการทำซ้ำ โดยเริ่มจากการกำหนดค่าเริ่มต้น และ Excel จะทำการลองผิดลองถูก จนกระทั่งได้ค่าผลลัพธ์ตามเป้าหมายที่กำหนด ระเบียบวิธีการทำซ้ำก็เป็นอีกวิธีการหนึ่งที่ใช้ในการหารากของสมการ(ค่าเป้าหมาย) โดยเป็นวิธีการที่สามารถทำได้โดยง่าย โดยหลักการของระเบียบวิธีการทำซ้ำ คือการจัดฟังก์ชัน ที่กำหนดมาให้มีค่า x อยู่ตัวเดียวโดดๆทางด้านซ้ายของสมการ เช่น

f(x) = 3x^3 – 20x^2 + 1000x + 12000 = 0

ทำการจัดสมการใหม่ได้เป็น

x = (-3x^3 +20x^2 –12000)/1000

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

x(i+1) = (-3xi^3 + 20xi^2 – 12000)/1000

หมายถึงว่าให้คำนวณค่า x(i+1) ซึ่งเป็นค่าใหม่ จากสมการด้านขวาซึ่งประกอบด้วยค่า xi เก่า

บางครั้งฟังก์ชันที่ต้องการหารากไม่มีเทอมที่ประกอบด้วยค่า x เดี่ยวๆที่สามารถแยกออกได้ง่ายเช่น

cos x - xe^x = 0

ให้ทำการบวกค่า x ใดๆลงในสมการทั้งสองข้างจะได้ว่า

x = cos x - xe^x + x

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

x(i+1) = cos xi - xie^xi + xi

เงื่อนไขการยุติการทำซ้ำจะพิจารณาการเปลี่ยนแปลงของค่า x(i+1) และ x(i) โดยกำหนดเป็นค่าความผิดพลาดโดยประมาณ เท่ากับ (x(i+1) – x(i))/x(i+1)

จากรูปแบบการทำซ้ำเราสามารถนำไปเขียนเป็นฟังก์ชันย่อยใน VBA เพื่อค้นหาค่าเป้าหมาย (รากของสมการ) โดยการทำซ้ำ สามารถเขียนโค้ดใน VBA ได้ดังนี้

Function OnePointInter(init As Double) As Double

Dim error As double

Dim xold As double

Dim xnew As double

error = 1

xold = init

Do While error > 0.01

xnew = Cos(xold) – xold*Exp(xold) + xold

error = abs((xnew – xold)/xnew)*100

xold = xnew

Loop

OnePointInter = xnew

End Function

จาก VBA code ท่านผู้อ่านจะเห็นว่า มีการทำซ้ำใน Do While…Loop โดยการทำซ้ำจะยุติลงหากค่าความผิดพลาดโดยประมาณมีค่าน้อยกว่า 0.01% น่าสนใจอยุ่ไม่น้อยนะครับว่าหากเราเดาค่าเริ่มต้นได้ไม่เหมาะสม(ดูเทคนิดการเดาค่าเริ่มต้น) เราอาจจะหารากของสมการหรือค่าเป้าหมายไม่เจอเลยก็ได้ครับ ซึ่งก็จะทำให้เกิดการทำซ้ำอยู่ใน Do While…Loop ตลอดไป ซึ่งก็จะส่งผลให้ไฟล์ excel ของเราแฮงค์ได้ครับ ลองฝากท่านผู้อ่านเป็นการบ้านหน่อยครับว่าหากเราอยากให้การทำซ้ำยุติลงในกรณีที่เกิดการลู่ออกของผลลัพธ์ จะต้องกำหนดเงื่อนไขการทำซ้ำอย่างไรดี เดี๋ยวในโอกาสต่อไปจะมานำเสนอโอกาสของการลู่ออกของระเบียบวิธีการทำซ้ำว่าจะเป็นไปในรูปแบบใดบ้าง สวัสดีครับ

Excel VLOOKUP ตอน ป้องกันการแสดง #N/A

สวัสดีครับท่านผู้อ่าน How to VBA Excel วันนี้ได้ลองย้อนกลับไปอ่านบทความเก่าๆใน Blog นี้มีบทความอยู่ 2-3 บทความที่กล่าวถึง VLOOKUP ซึ่งเป็น Excel Function ที่ผมได้นำเสนอไปในตอนแรกๆเนื่องจาก VLOOKUP เป้นฟังก์ชันที่ช่วยอำนวยความสะดวกให้กับผู้ใช้งาน Excel ที่เกี่ยวข้องกับการค้นหาข้อมูลในตารางได้ดีมาก แต่ผมเองกลับพบความบกพร่องของการนำเสนอบทความที่ประยุกต์ใชฟังก์ชัน VLOOKUP เหล่านี้ของผมเอง นั่นคือการไม่ได้กล่าวถึงข้อผิดพลาดที่ฟังก์ชัน VLOOKUP ส่งกลับมาครับ วันนี้เลยขอนำเสนอในปัญหานี้ครับ ที่มาของข้อผิดพลาดของ ฟังก์ชัน VLOOKUP ผมได้กล่าวไปแล้วในบทความเหล่านั้น ซึ่งจะเกิดจากการที่ Excel ไม่พบข้อมูลที่ต้องการค้นหาในหลักที่เรากำหนดในตารางข้อมูลนั้น ผลที่ถูกส่งกลับจาก VLOOKUP ก็คือ #N/A ซึ่งจะแสดงในเซลล์ที่เรากำหนด ทีนี้หากเราไม่ต้องการให้ excel แสดงค่า #N/A ออกมาในกรณีการใช้สูตร VLOOKUP เราจะใช้ฟังก์ชัน ISNA ในการตรวจค่าที่ถูกส่งกลับมาจาก VLOOKUP ว่าใช่ #N/A หรือไม่ หากใช่ ISNA จะคืนค่า true กลับมา หากไม่ใช่ จะคืนค่า false กลับมา ลองมาคิดกันแบบภาษามนุษย์เรานะครับ “ถ้า VLOOKUP ส่งค่า #N/A ให้แสดงข้อความว่า “หาไม่พบ” แต่หากหาพบให้แสดงค่านั้นออกมา” มาดูตัวอย่างกันเลยครับตามภาพที่ 1

ภาพที่ 1 ผลการประยุกต์ใช้ ISNA เพื่อตรวจสอบการคืนค่าจาก VLOOKUP

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

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

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 ตอน ชนิดของข้อมูล (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 ก็เพียงพอแล้วเป็นต้น

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

ค่า PI in Engineering

บทความนี้อยากจะกล่าวถึงค่าคงที่ค่าหนึ่งที่ engineer ทุกคนรู้จักและเคยนำไปใช้ในการคำนวณมาบ้างแล้วไม่มากก็น้อย นั่นคือ ค่า PI นั่นเองครับ ค่าคงที่ PI ถูกนิยามว่า เป็นอัตราส่วนระหว่างความยาวเส้นรอบวงของวงกลมต่อความยาวเส้นผ่านศูนย์กลางของวงกลมนั้น จะเห็นได้ว่าค่าคงที่ PI ถูกนิยามขึ้นจากลักษณะรูปร่างของวัตถุ ดังนั้นจึงอาจกล่าวได้ว่า หากเราพบเจอค่าคงที่ PI ในสูตรการคำนวณใดๆ แล้ว ขอให้เราวิเคราะห์ได้เบื้องต้นว่า เรากำลังแก้ปัญหาที่เกี่ยวข้องกับรูปร่างอยู่ เช่น สูตรการหาพื้นที่วงกลมก็คือ PI *r^2 สูตรการคำนวณหาแรงกดวิกฤตในแนวแกนของเสาที่มีการยึดแบบ pinned end ก็จะเท่ากับ Pcr = PI ^2*E*I/l^2 หรือค่ามุมในหน่วยเรเดียนก็ยังเกี่ยวข้องกับ PI เนื่องด้วยการนิยามค่ามุมจะเกี่ยวข้องกับความยาวส่วนโค้งที่รองรับมุมที่กวาดไปรอบจุดศูนย์กลางของวงกลม เช่น มุม 90 องศาก็จะเท่ากับ PI /2 เป็นต้น

เอาหล่ะครับทีนี้มาเข้าเรื่องกันเลยครับ แล้ว PI มีค่าเท่าไหร่ ใน Excel หรือ VBA Excel จะกำหนดค่าอย่างไร ตอนเด็กๆเราคงจำได้และท่องขึ้นใจนะครับว่า PI มีค่าเท่ากับ 22/7 หรือประมาณ 3.14 แต่ทีนี้เราจะพบว่าการใช้ค่าประมาณในการคำนวณซ้ำๆกันเช่น การคูณ การหาร หรือ ยกกำลัง จะทำให้ผลลัพธ์ของเราเกิดความผิดพลาดขึ้นมาได้ แล้ว PI มีค่าเท่าไหร่ หล่ะ ครับ ใน Excel มีวิธีการหาค่า PI สองวิธีครับคือ

1. ใช้ฟังก์ชั่นที่เรียกใช้ค่า PI ได้เลยครับ โดยเราจะเรียกใช้ผ่านฟังก์ชั่น PI() ครับ ซึ่งฟังก์ชันนี้จะคืนค่า PI มาเท่ากับ 3.14159265358979 (ความละเอียด15 หลัก)

2. หาค่าโดยอ้อมผ่านฟังก์ชัน ATAN (ค่าอาร์กแทนเจนต์) ครับ ทุกท่านคงทราบดีนะครับว่า ATAN(1) จะคืนค่ามุมในหน่วยเรเดียนกลับ ซึ่งเราทราบอยู่แล้วว่า ATAN(1) = PI /4 ดังนั้น PI = 4*ATAN(1)

ครับที่กล่าวมาเป็นการเรียกใช้ฟังก์ชันใน Excel หากต้องใช้ใน VBA เราสามารถหาค่า PI ได้ตามวิธีการที่สองเท่านั้นครับ แต่ฟังก์ชันที่คืนค่าอาร์กแทนเจนต์ใน VBA จะไม่เหมือนกับฟังก์ชันใน Excel โดยจะสามารถเขียนได้ดังนี้ PI = 4*Atn(1) เมื่อ Atn คือ ฟังก์ชันที่คืนค่าอาร์กแทนเจนต์

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

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

โดยจะเขียนอยู่ในรูปแบบของอนุกรมได้ดังนี้ครับ (นำเสนอโดยคุณ John Machin ในปี คศ 1706)

เดี๋ยวบทความต่อไปเรามาลองเขียน VBA code คำนวณอนุกรมนี้เพื่อหาค่า PI กันครับ

Determine polygon area โดยใช้เทคนิค VBA in excel (Macro excel)

เข้าสู่ฤดูฝนอย่างเต็มตัวกันแล้วนะครับท่านผู้อ่าน ฟุตบอลโลก 2010 กำลังโม่แข้งกันงวดเข้ามาทุกที เยอรมันทีมโปรดของผมยังอยู่ในเส้นทางลุ้นแชมป์ อีกประมาณ 1 สัปดาห์คงทราบผลกัน รักษาสุขภาพกันด้วยนะครับ
บทความที่ต้องการนำเสนอทุกท่านวันนี้เป็นภาคต่อจากที่เคยนำเสนอระเบียบวิธีในการคำนวณหาพื้นที่หลายเหลี่ยมไปแล้ว และได้กล่าวทิ้งท้ายถึงเทคนิคที่จะนำมาใช้กับระเบียบวิธีดังกล่าว บทความนี้จึงขอนำเสนอเทคนิคการใช้ VBA in excel เข้ามาช่วยในการแก้ปัญหานี้ ซึ่งผู้อ่านต้องเข้าใจพื้นฐานของ VBA Excel มาพอสมควร โดยขอยกตัวอย่างการหาพื้นที่รูปหลายเหลี่ยมซึ่งมีจุดยอด (vertex) ในระบบพิกัดคาร์ทีเซียน ดังนี้ (0,0) , (10,0) , (10 , 10) , (5,10) , (5,15) , (0,10) , (0,0) เมื่อลากเส้นตรงเชื่อมจุดยอดจะได้รูปหลายเหลี่ยมดังแสดงในภาพที่ 1

ภาพที่ 1 รูปหลายเหลี่ยม (Polygon)

จากภาพพบว่ารูปหลายเหลี่ยมประกอบไปด้วยจุดยอดทั้งหมด 7 จุด และเมื่อพิจารณารูปหลายเหลี่ยมจะเห็นได้ว่าเกิดจากรูปร่างอย่างง่ายสองรูปประกอบเข้าด้วยกัน ได้แก่ รูปสี่เหลี่ยมจัตุรัส(A2) และรูปสามเหลี่ยมหน้าจั่ว A1 ดังนั้นสามารถคำนวณหาพื้นที่รูปหลายเหลี่ยมได้ง่ายๆดังนี้

A (polygon) = A1 + A2 = (5*5/2) + 10*10 = 112.5 ตารางหน่วย

ทีนี้หากต้องการใช้เทคนิค VBA ช่วยในการคำนวณหาพื้นที่จะเริ่มดำเนินการอย่างไร เรามาเริ่มกันเลยดีกว่า
เริ่มจากการนำเข้าข้อมูลจุดยอดของรูปหลายเหลี่ยมมายัง Excel โดยกำหนดให้คอลัมน์ A เป็นพิกัด X คอลัมน์ B เป็นพิกัด Y ดังแสดงในภาพที่ 2

ภาพที่ 2 พิกัดจุดยอดของ Polygon ในระบบพิกัดคาร์ทีเซียน

คลิก ปุ่ม Alt + F8 เพื่อจัดการมาโครสำหรับใช้คำนวณพื้นที่ โดยกำหนดชื่อมาโครเป็น PolygonArea ดังแสดงในภาพที่ 3 คลิกปุ่ม สร้างเพื่อเข้าสู่หน้า Visual Basic Editor โดยจะพบกับ Subroutine PolygonArea ดังรูปที่ 4

ภาพที่ 3 สร้าง Macro Excel


ภาพที่ 4 Visual Basic Editor

เราจะเขียนโค้ดสำหรับคำนวณพื้นที่ของ Polygon ใน Subroutine PolygonArea ตามระเบียบวิธีที่เคยนำเสนอมากันครับ โดยมีรายละเอียดดังนี้

แนวคิด

ในการคำนวณพื้นที่รูปหลายเหลี่ยมจะคำนวณได้จากผลรวมของ Xi*Yi+1 ลบด้วยผลรวมของ Xi+1*Yi หารด้วย 2 ดังแสดงในภาพที่ 5 จากสูตรที่กล่าวมาจะพบว่า แถวสุดท้ายที่สามารถหาผลคูณได้คือแถวที่ n-1 เนื่องจากแถวถัดไปคือแถวที่ n-1+1 นั่นคือ n เนื่องจากแถวที่ n+1 ไม่มีข้อมูล

ภาพที่ 5 ระเบียบวิธีการคำนวณ


ภาพที่ 6 แสดงการคำนวณแถวที่ n-1



ภาพที่ 7 code vba และคำอธิบาย

สร้างปุ่มกด และกำหนดมาโคร PolygonArea ให้กับปุ่มกดนี้ ดังแสดงในภาพที่ 8

ภาพที่ 8 ผลการคำนวณพื้นที่รูปหลายเหลี่ยม

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