search ข้อมูลด้วย VLOOKUP (by Spreadsheet software)

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

การค้นหาข้อมูลหากใช้ MS Access คงจะสะดวกมากในการค้นหาเนื่องจากในมีคำสั่ง SQL ช่วยในการค้นหา หากท่านเก็บข้อมูลไว้ใน Spreadsheet software เช่น MS Excel หล่ะ เราจะค้นหาข้อมูลเหล่านั้นได้อย่างไร สมมุติว่าเราได้เก็บข้อมูลส่วนผสมของสารเคมีสำหรับการผสมยาชนิดต่างๆไว้ใน Sheet1 ดังรูป


โดยหลัก A จะเก็บชื่อสูตรยา หลัก B เป็นปริมาณร้อยละของสาร X หลัก C เป็นปริมาณร้อยละของสาร Y และ หลัก D เป็นปริมาณร้อยละของสาร Z ข้อมูลในแต่ละแถวคือสูตรยาต่างๆ โดยจากตัวอย่างประกอบไปด้วยห้าสูตร ดังนี้ สูตร 1 , สูตร 2,สูตร 3,สูตร 4 และ สูตร 5
สมมุติว่าเราต้องการคำนวณปริมาณสารเคมีที่ใช้ในการผสมยาสูตรต่างๆ สิ่งแรกที่ต้องทราบคือชื่อสูตรยาที่ต้องการผสม และปริมาณยาที่ต้องการผสม M ดังนั้นปริมาณสารเคมี X ,Y, Z จะคำนวณได้จากสูตร M*X , M*Y , M*Z ตามลำดับ ปริมาณร้อยละของสารเคมีจะขึ้นอยู่กับสูตรยางที่้ี่เราเลือก จากรายละเอียดดังกล่าว เราสามารถใช้ฟังก์ชัน VLOOKUP ช่วยค้นหาข้อมูลที่ต้องการได้ โดยสมมุติให้เราเลือก Sheet2 เป็น Sheet สำหรับการคำนวณ กำหนดให้ A1 = "สูตรยา" , B1 = "ปริมาณยาที่ต้องการ(kg)" ,C1 = "ปริมาณสารเคมี X ที่ต้องใช้(kg)",D1 = "ปริมาณสารเคมี Y ที่ต้องใช้(kg)",E1 = "ปริมาณสารเคมี Z ที่ต้องใช้(kg)"

แนวคิด ผู้ใช้ป้อนชื่อสูตรยางในเซลล์ A2 และป้อนปริมาณยาที่ต้องการในเซลล์ B2 MS Excel จะค้นหาและคำนวณปริมารสารเคมีที่ต้องใช้ให้
วิธีการ คำนวณหาปริมาณสาร X ที่ต้องใช้ได้ โดยมีแนวคิดดังนี้ ปริมาณสาร X = M*X จากสูตรดังกล่าว M จะมีค่าเท่ากับค่าในเซลล์ B2 ขณะที่ X ต้องค้นหาจากข้อมูลในSheet1 โดยเริ่มจากค้นหาชื่อสูตรยางตามแนวดิ่งที่เท่ากับค่าในเซลล์ A2 เมื่อพบแล้วให้เลือกสมาชิกที่อยู่ในแถวเดียวกันในหลักที่ 2 นับจากชื่อสูตรยาง การค้นหาในลักษณะนี้จะใช้ฟังก์ชัน VLOOKUP ซึ่งมีรูปแบบดังนี้

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value คือค่าที่จะค้นหาในฟิลด์แรกของตารางอาเรย์ ในที่ตัวอย่างนี้คือสูตรยางใน Sheet2 เซลล์ $A2
table_array คือตารางข้อมูลอย่างน้อยต้องมีฟิลด์เท่ากับสอง โดยค่าในฟิลด์แรกคือค่าที่ถูกค้นหาด้วย lookup_value ในตัวอย่างนี้คือข้อมูลใน Sheet1 ในช่วง $A2:$D6
col_index_num คือ หมายเลขฟิลด์ในตารางข้อมูล table_array ซึ่งค่าภายในฟิลด์จะถูกส่งกลับมาในตัวอย่างนี้จะเท่ากับ 2
range_lookup คือค่าตรรกะในการค้นหาในตัวอย่างนี้เราต้องการค้นหาค่าที่ตรงกันเท่านั้นดังนั้นจึงกำหนดให้เท่ากับ 0

ดังนั้นจากตัวอย่างนี้เราสามารถเขียนสูตร X = M*X ได้ดังนี้

C2 = $B2*VLOOKUP($A2,Sheet1!$A2:$D6,2,0)/100

ดังนั้นสำหรับสารเคมี Y และ Z จะสามารถคำนวณได้ในทำนองเดียวกัน

D2 = $B2*VLOOKUP($A2,Sheet1!$A2:$D6,3,0)/100
E2 = $B2*VLOOKUP($A2,Sheet1!$A2:$D6,4,0)/100

จากตัวอย่างหากเราต้องการคำนวณปริมาณสารเคมีของสูตรยางหลายสูตรๆก็สามารถทำได้โดยง่ายโดยเพิ่มแถวของสูตรยาและปริมาณยาที่ต้องการในหลัก A และ B

หากมีข้อมูลของสูตรยาเพิ่มขึ้นเราสามารถเพิ่มแถวใน Sheet1 และทำการแก้ไขช่วงของ table array ในฟังก์ชัน VLOOKUP
ในกรณีที่มีการเพิ่มชนิดของสารเคมีในสูตรยาก็สามารถทำในทำนองเดียวกัน โดยต้องคำนึงถึง col_index_num ด้วย

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

พระสีวลี พระอรหันต์ผู้เป็นเลิศด้านโชคลาภ

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

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

คาถาบูชาพระสีวลี (โดยหลวงพ่อเกษม เขมโก แห่งสุสานไตรลักษณ์ จังหวัดลำปาง)

ตั้งนะโมสามจบ
สีวะลีมะหาเถรัง วันทามิหัง(๓ จบ)
มะหาสีวะลี เถโร มะหาลาโภ โหติ มะหา สีวะลี เถโร ลาภัง เม เทถะ

Goal Seek กับ Fatigue Analysis ด้วย Soderberg line

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

มาต่อกันเรื่องการใช้ Goal Seek Function อีกตัวอย่างหนึ่งครับ ในการออกแบบเพลาซึ่งเป็นวัตถุที่รองรับโหลดแบบวงรอบ(Cyclic load) ในบางครั้งก็มีโหลดแบบไม่เป็นรูปแบบมากระทำทำให้ความเค้นเฉลี่ยมีค่าไม่เท่ากับศูนย์ เราเรียกความเค้นที่ไม่เป็นรูปแบบนี้และมีผลให้ความเค้นเฉลี่ยไม่เท่ากับศูนย์ว่า Fluctuating stress ในการออกแบบรูปร่างและขนาดของเพลาสำหรับวัสดุเหนียวสามารถใช้ทฤษฎีการวิเคราะห์ได้หลายรูปแบบ แต่ในบทความนี้ผมขอยกตัวอย่างการวิเคราะห์ความล้าด้วย Soderberg line โดยมีรูปแบบของสมการดังนี้

(Kf*sigma a/Se) + (sigma m/Sy) = 1/n (1)
เมื่อ Se คือ modified endurance limit ,Pa
Sy คือ yield strength , Pa
Kf คือ fatigue stress concentration factor
sigma a คือ alternating stress , Pa
sigma m คือ mean stress , Pa
n คือ safety factor

ในกรณีการออกแบบเพลาโดยใช้ทฤษฎีความเสียหาย Maximum shear -stress จะได้ความสัมพันธ์ระหว่างขนาดเส้นผ่านศูนย์กลางและsafety factoc ดังนี้

(pi/32)*d^3 = n[(Mm/Sy + Kf*Ma/Se)^2 + (Tm/Sy + Kfs*Ta/Se)^2]^0.5 (2)

สมมุติว่าในปัญหานี้เราต้องการทราบค่าโมเมนต์ดัดเฉลี่ยซึ่งเพลาจะสามารถรับได้ โดยทราบขนาดเส้นผ่านศูนย์กลางของเพลาและตัวแปรอื่นๆในสมการ จะเห็นว่าการแก้สมการ(2) ค่อนข้างยุ่งยากเนื่องด้วยสมการเ็ป็นแบบไม่เชิงเส้น แต่จากบทความที่แล้วเราได้ศึกษาการใช้ฟังก์ชั่น Goal Seek ของ MS Excel มาบ้างแล้ว ในปัญหานี้เราจะประยุกต์ใช้เพื่อแก้สมการ โดยเริ่มจากการกำหนดค่าตัวแปรต่างๆที่ทราบค่าลงในเซลล์ใดๆเพื่อเป็นข้อมูล Input จากนั้นเลือกเซลล์เพื่อกำหนดสูตรในการหาค่า ด้านซ้ายและด้านขวาของสมการ
สมมุติว่ากำหนดให้ เซลล์ A12 เป็นค่าด้านซ้ายของสมการเราสามารถกำหนดได้เป็น

= pi()* E20^3/32 (สมมุติว่าค่า d ถูกกำหนดไว้ที่เซลล์ E20)
กำหนดให้เซลล์ B12 เป็นค่าด้านขวาของสมการ
เลือกเซลล์ A13 เป็นเซลล์ที่คำนวณผลต่างระหว่างค่าด้านซ้ายและค่าด้านขวาของสมการ โดยสามารถกำหนดได้ดังนี้
= A12 - B12

จากนั้นเราจะใช้ Goal Seek ใน MS Excel เพื่อหาค่า โมเมนต์ดัดเฉลี่ย Mm โดยคำตอบที่ได้จะถูกต้องก็ต่อเมื่อผลต่างของค่าด้านซ้ายและด้านขวาของสมการมีค่าเท่ากับศูนย์
ดังนั้นในไดอะล็อคของ Goal Seek จะกำหนดค่าดังนี้

ตั้งค่าในเซลล์ : ฺB13
ให้เป็นค่า : 0
โดยการเปลี่ยนเซลล์ : E12 (สมมุติว่าค่า Mm ถูกเดาไว้ที่เซลล์ E12)

คลิก OK MS Excel จะช่วยค้นหาค่า Mm ที่ทำให้ผลต่างของค่าด้านซ้ายและด้านขวาของสมการมีค่าเท่ากับศูนย์

จากตัวอย่างนี้ ในทางกลับกันหากเราทราบค่า Mm และต้องการหาค่า เส้นผ่านศูนย์กลางของเพลา ก็สามารถทำได้โดยง่ายเพียงเปลี่ยนค่าใน By changing cell เป็น E20

ดังนั้นจากที่นำเสนอการใช้ฟังก์ชัน Goal Seek ทั้งสองตัวอย่างจะพบว่าหากปัญหาที่ต้องการแก้เป็นปัญหาหนึ่งตัวแปร ท่านสามารถใช้ฟังก์ชัน Goal Seek แก้ปัญหาได้อย่างง่ายและรวดเร็ว
ในกรณีที่ต้องการหาคำตอบมากกว่าหนึ่งตัวแปร เราคงต้องหันไปใช้ฟังก์ชัน Solver เป็นคำตอบสุดท้ายครับ

บทความเกี่ยวกับ Goal seek
การใช้ Excel : Goal Seek แก้ปัญาหาปริมาตรของทรงกลม

การประยุกต์ใช้ Goal Seek แก้ปัญหาการออกแบบ 1

ในการออกแบบเครื่องกลบ่อยครั้งต้องแก้ปัญหาของสมการแบบไม่เชิงเส้น ตัวอย่างเช่น การหาค่าความเค้นหลักที่จุดใดๆในวัตถุ (Principal stress at a point) สามารถหาได้จากสมการ characteristic equation ดังเช่น

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

ค่า x ที่ทำให้ f(x) = 0 จะถูกเรียกว่า รากของสมการ รากของสมการ characteristic equation ในปัญหานี้คือค่าความเค้นหลักที่จุดใดๆในวัตถุนั่นเอง จากรูปแบบของสมการจะพบว่า รากของสมการจะมีทั้งหมดสามค่าและเป็นจำนวนจริงทั้งหมด (รายละเอียดผมจะเล่าคราวหลัง) การแก้สมการเพื่อหารากสมการนี้สามารถทำได้โดยการลองผิดลองถูก (trial and error) โดยการทดลองเดาค่า x จนกว่าจะทำให้ค่าด้านซ้ายมือของสมการเข้าใกล้ศูนย์มากที่สุด การทดลองเดาค่า x มีวิธีการอยู่สองวิธีคือ วิธีการ False Position และวิธีการของนิวตันราฟสัน (Newton-Raphson) โดยปกติวิธีการของนิวตันราฟสันจะมีอัตราการลู่เข้าสู่คำตอบได้เร็วกว่าวิธีการ False Position อย่างไรก็ตามฟังก์ชันบางประเภทก็ไม่สามารถแก้ปัญหาด้วยวิธีการของนิวตันราฟสันได้ดีกว่าวิธีการ False Position

บทความนี้ผมจะนำเสนอเทคนิคการหารากของสมการโดยการใช้ฟังก์ชัน Goal Seek ซึ่งเป็นฟังก์ชันในSpreadsheet software โดยในบทความนี้จะกล่าวถึงโปรแกรม Microsoft Excel ซึ่งใช้วิธีการของนิวตันราฟสันในการหารากของสมการ (ในโปรแกรม Spreadsheet ของ Open office ก็มีฟังก์ชั่นนี้เช่นกัน) เรามาดูวิธีการกันครับ สมมุติเราต้องการหารากของสมการข้างต้น

1. ขั้นตอนแรกให้ท่านเปิดโปรแกรม Microsoft Excel แล้วป้อนค่าในเซลล์ต่างๆของ sheet1 ดังนี้

เซลล์ A1 : ให้ป้อนตัวอักษรเพื่อเป็นป้ายชื่อเป็น X =

เซลล์ B1 :ให้ป้อนค่าที่ต้องการเดาเริ่มต้นในที่นี้ให้ป้อนเป็น 0

เซลล์ A3 : ให้ป้อนตัวอักษรเพื่อเป็นป้ายชื่อเป็น f(x) =

เซลล์ B3 : ให้ป้อนสูตรดังนี้ =3*B1^3 – 20*B1^2 + 1000*B1 + 12000

2. หลังจากนั้นให้ไปที่เมนู Tools เลือก Goal Seek… จะปรากฏไดอะล๊อกของ Goal Seek ขึ้นมา

ให้ป้อนข้อมูลลงในไดอะล๊อกของ Goal Seek ดังนี้

ในช่อง Set cell ให้เลือกเซลล์ที่คำนวณค่าของสมการที่ต้องการหารากในตัวอย่างนี้คือ เซลล์ B3

ในช่อง To Value กำหนดค่าให้เท่ากับ 0

ในช่อง changing cell ให้เลือกเซลล์ B1

จากการป้อนข้อมูลในไดอะล๊อกของ Goal Seek สามารถอธิบายได้ดังนี้ เป็นการกำหนดให้ Microsoft Excel เปลี่ยนแปลงค่าในเซลล์ B1 จนกว่าค่าฟังก์ชันในเซลล์เท่ากับหรือเข้าใกล้ศูนย์

3. กดปุ่ม OK

4. หากสมการนี้สามารถหารากได้ Microsoft Excel จะแจ้งสถานะให้ทราบ และเราจะเห็นรากของสมการในเซลล์ A1

สิ่งที่ต้องคำนึงในการหารากของสมการด้วย Goal Seek คือ รากที่ทำให้ฟังก์ชัน f(x) = 0 จะขึ้นอยู่กับค่าเริ่มต้นของการเดา บางครั้ง Microsoft Excel อาจแจ้งไม่พบรากของสมการในสถานะของการค้นหา ผู้ใช้ต้องลองปรับค่าเริ่มต้นการเดาใหม่ซึ่งอาจทำให้หารากของสมการได้

เมื่อพิจารณาค่าปัจจุบันในเซลล์ B3 จะพบว่ามีค่าไม่เท่ากับศูนย์ ซึ่งเกิดจากกระบวนการทำซ้ำ(iteration process) แต่อย่างไรก็ตามหากอยู่ในค่าคลาดเคลื่อนที่ยอมรับได้ เราก็ไม่คำนึงถึงค่าไม่เท่ากับศูนย์นี้

สรุปแล้วการหารากของสมการด้วย Goal Seek จะขึ้นอยู่กับค่าการเดาเริ่มต้นที่เหมาะสม และก่อนการแก้ปัญหาด้วย Goal Seek ควรมั่นใจด้วยว่าสมการนี้มีรากของสมการแน่ๆ