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 format : To Calculte Pi (Machin’s series)

This article aims to present The command to check conditions. Under the command to reproduce While I, for example, to calculate the PI using a case study For a description of this command. Finally, provides a set text format in VBA in order to display the calculation.

From previous posts. We have presented methods to calculate the PI using the function PI () in excel or using trigonometric relationships ATAN function through the end of the article. I leave recipes for your Machin the PI consider the formula for the PI in the form of infinite series. As shown in Equation 1.

Figure 1 Machin Formular

From the equation, we see that the PI can be determined by the sum of each value of k until the resolution of the PI to the totals for repeated this Easily through computer programming a few lines. Let's see the code written in VBA and description below.

subrutene series

Sub series()
Dim p As Double
Dim q As Double
Dim tol As Double
Dim bfinish As Boolean
p = 0
q = 0
bfinish = True
tol = 0.000000000000001
Dim k As Integer
Dim j As Integer
Dim i As Integer
k = 0
j = 0
i = 0

While bfinish
k = (-1) ^ i
j = 2 * i + 1
p = p + (16 * k) / (j * (5 ^ j))
q = q + (4 * k) / (j * (239 ^ j))
'check condition 15 digit

If Abs(16*k)/(j*(5^j)) <tol And Abs(4*k)/(j * (239 ^ j))<tol Then
bfinish = False
End If
i = i + 1 ' increase i
Wend
Range("A1").Select

‘define format cell (text format)
ActiveCell.NumberFormat = "0.000000000000000"

ActiveCell.Value = p - q
End Sub

From the vba code has shown that Calculated the series for the PI command only two major commands that are used in a while ... wend reproduce and use the IF .. THEN check the terms of the resolution, while the PI of. Details of cell to use the command NumberFormat which enabled simple observations in code of the calculated PI I picked up the calculation in the variable temp. To facilitate the reading code and prevent errors in calculations.

แท็กของ Technorati: {กลุ่มแท็ก}VBA Excel,PI

ค่า 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 กันครับ

Solve Moody Chart with Goal Seek

สวัสดีท่านผู้อ่านครับ ห่างหายไปจากการเขียนบทความไปเกือบสองอาทิตย์เนื่องด้วยตาแดงครับทำให้ต้องลดเวลากับการอยู่หน้าคอมพิวเตอร์ลงครับ ตอนนี้อาการดีขึ้นแล้ว(จากการไปหาหมอมาครับ) ช่วงนี้งานประจำก็ค่อนข้างเยอะมีการปรับความเร็วในการทำงานหรือจะเรียกให้ทันสมัยหน่อยก็คือ ถูกกระชับเวลาครับ แต่ก็ยังหาโอกาสมานำเสนอบทความที่คิดว่าน่าจะช่วยชี้แนะท่านผู้อ่านได้บ้างครับ
"Everyone lives by selling same thing" (Robert Louis Stevenson) แปลเป็นไทยก็คือ เราต่างมีชีวิตอยู่ด้วยการขายอะไรบางอย่างด้วยกันทั้งนั้น
มาเข้าสู่เนื้อหาบทความนี้กันครับ จากการที่ได้นำเสนอการใช้ Goal Seek ช่วยในการแก้ปัญหาการออกแบบไปแล้ว บทความนี้จะนำเสนอการนำไปใช้แก้ปัญหา Pipe Flow ขอยกตัวอย่างการคำนวณหาขนาดของปั๊มที่เหมาะสม ในการส่งน้ำผ่านท่อขนาดเส้นผ่านศูนย์กลาง D เป็นระยะทางเท่ากับ L วิศวกรจำเป็นต้องคำนวณหาค่าพลังงานสูญเสียภายในท่อจากสมการ

โดย hl แทนพลังงานสูญเสียภายในท่อ
V แทนค่าความเร็วเฉลี่ยของน้ำที่ไหลในท่อ
f แทนค่าตัวประกอบแรงเสียดทาน (Friction factor)
ซึ่งหากภายในท่อมีผิวขรุขระ ค่าตัวประกอบแรงเสียดทานสามารถคำนวณได้จาก Colebrook Equation

โดย e แทนค่าความขรุขระสัมพัทธ์ภายในท่อ
Re แทนค่าตัวเลขเรย์โนลด์
หากนำผลเฉลยของ Colebrook Equation นี้ไปพล๊อตกราฟแสดงความสัมพันธ์ระหว่างค่าตัวประกอบแรงเสียดทาน ตัวเลขเรย์โนลด์ และความขรุขระ จะได้กราฟที่ชื่อว่า Moody Chart ดังแสดงในภาพ

Moody Chart

สมมุติว่าปัญหานี้กำหนดให้ e = 0.01 และ Re = 100,000

ในการคำนวณหาค่าตัวประกอบแรงเสียดทานสามารถทำได้ในทำนองเดียวกับการแก้ปัญหาการออกแบบในบทความที่ผ่านมา โดยเราจะใช้ฟังก์ชัน Goal Seek เพื่อหาค่า f โดยเริ่มจากเดาค่า f ในเซลล์ B1 เท่ากับ 0.1
และกำหนดค่า e และ Re เท่ากับ 0.01 และ 100,000 ลงในเซลล์ B2 และ B3 ตามลำดับ
กำหนดให้เซลล์ B4 เท่ากับค่าด้านซ้ายของสมการ โดยให้เท่ากับ = 1/SQRT(B1)
กำหนดให้เซลล์ B5 เท่ากับค่าด้านขวาของสมการ
โดยกำหนดสูตรได้ =-2*LOG((B2/3.7 + (2.51/(B3*SQRT(B1)))),10)
กำหนดให้ B6 เป็นผลต่างของ B4 และ B5 ดังแสดงในภาพ

เมื่อกำหนดค่าครบทุกเซลล์แล้วให้คลิกที่เมนู Tool>Goal Seek...
จะปรากฎหน้าต่างสำหรับกำหนดค่าเริ่มต้นดังแสดงในภาพ

โดยกำหนดให้ ตั้งค่าในเซลล์เป็น B6 ให้เป็นค่า 0 โดยการเปลี่ยนค่าในเซลล์ B1 จากนั้นให้กดปุ่ม ตกลง
Excel จะทำการคำนวณเพื่อหาค่า f ที่เหมาะสม เมื่อพบคำตอบแล้วจะแสดงหน้าต่างดังแสดงในภาพ

เมื่อคลิกตกลงค่าในเซลล์ B1 จะแสดงถึง ค่า f ที่เหมาะสม ขณะที่ค่าในเซลล์ B6 จะแสดงค่าผลต่างของค่าด้านซ้ายและด้านขวาของสมการ

หากผลต่างที่ออกมายังอยู่ในค่าที่ไม่ยอมรับ ท่านผู้อ่านก็สามารถเดาค่า f ในเซลล์ B1 ใหม่แล้วดำเนินการโดยใช้ Goal Seek อีกครั้ง

จากตัวอย่างนี้ท่านผู้อ่านคงเห็นวิธีการหาคำตอบที่เหมาะสมในงานวิศวกรรมแล้วนะครับ เพราะหลายครั้งที่เราแก้ปัญหาในการออกแบบขั้นตอนสุดท้ายจะจบลงที่การ ลองผิดลองถูก (Trial and error) ซึ่ง Goal Seek สามารถช่วยท่านได้