การใช้ excel : เทคนิคการคำนวณค่าเฉลี่ย KPI

การใช้ excel ในบทความนี้จะนำเสนอเทคนิคการหาค่าเฉลี่ยข้อมูล ในปัจจุบันการประเมินผลงานด้วย KPI ถูกนำมาใช้อย่างแพร่หลายในองค์กรต่างๆไม่ว่าจะของรัฐ ของเอกชน หากท่านได้รับมอบหมายให้ทำการหาค่าเฉลี่ยประสิทธิภาพของ KPI ใน 5 อันดับสูงสุด จาก KPI ของคนเป็นร้อยๆคน จะทำอย่างไร วิธีที่ง่ายที่สุดสำหรับผู้ใช้ excel คือใช้คำสั่งเรียงลำดับข้อมูลตามผลของ KPI จากนั้นก็ใช้สูตร excel : average และเลือกข้อมูล KPI 5 อันดับแรก ก็จะได้ค่าเฉลี่ยค่า KPI  5 อันดับสูงสุดแล้ว แต่ลองพิจารณาดูให้ดี หากมีหัวข้อ KPI ที่ต้องประเมินมากๆ และท่านผู้บริหารต้องการค่าเฉลี่ย KPI ใน 5 อันดับสูงสุดของ KPI แต่ละตัว การทำวิธีการนี้คงยุ่งยากพอควร เรามาดูวิธีการประยุกต์สูตร Excel เพื่อแก้ปัญหานี้กันครับ

เราจะใช้สูตร excel : LARGE() เข้ามาช่วยสูตร excel : AVERAGE() 

แนวคิดคือเราจะใช้ สูตร excel : LARGE() ในการค้นหาค่ามากสุด 5 ลำดับแรก และให้ส่งกลับทั้ง 5 ค่ามาที่สูตร excel : AVERAGE() เพื่อหาค่าเฉลี่ยต่อไป

สมุติมีข้อมูล KPI อยู่ 10 ตัว วางอยู่ในหลัก A ดังรูป เราสามารถเขียนสูตร excel ได้ดังนี้

= AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))

ผลการคำนวณค่าเฉลี่ย KPI ที่มีค่าสูงสุด 5 ลำดับแรก

จากสูตรนี้เราก็สามารถนำไปใช้คำนวณค่าเฉลี่ย KPI ตัวอื่นๆได้อย่างรวดเร็วครับ

ก่อนจบบทความนี้มีคำถามเพิ่มเติมก่อนจะมาเฉลยต่อไปว่า หากเราจะทำให้สูตรหาค่าเฉลี่ย KPI ตัวนี้มีความยืดหยุ่นมากยิ่งขึ้น เช่น หากข้อมูล KPI มีเพิ่มขึ้นหรือลดลงจาก 10 ค่า, หากต้องการหาค่าเฉลี่ย KPI ที่มีค่าสูงสุด n ลำดับแรก หรือหากต้องการหาค่าเฉลี่ย KPI ที่มีค่าน้อยสุด n ลำดับแรก  เราจะต้องปรับสูตร excel อย่างไร แนะนำให้ไปดู การสร้างรายการแบบไดนามิค 

ท่านสามารถนำเทคนิคนี้ไปประยุกต์กับการหาค่าเฉลี่ยของข้อมูลอื่นๆได้อีกครับ สวัสดีครับ

มาดูเพิ่มเติมจากคำถามเรื่องการปรับสูตรให้ยืดหยุ่นกันต่อครับ วันนี้มาตอบคำถาม 2 เรื่อง

เรื่องแรก หากข้อมูล KPI มีเพิ่มขึ้นหรือลดลงจาก 10 ค่า เราจะใช้การสร้างรายการแบบไดนามิคในส่วนของช่วงข้อมูล จะได้สูตร excel ดังนี้

AVERAGE(LARGE(OFFSET(A:A,0,0,COUNT(A:A),1),{1,2,3,4,5}))

และหากต้องการหาค่าเฉลี่ย KPI ที่มีค่าน้อยสุด 5 ลำดับแรก เราจะใช้ สูตร excel: SMALL แทน LARGE 

AVERAGE(SMALL(OFFSET(A:A,0,0,COUNT(A:A),1),{1,2,3,4,5}))

พิจารณาจากสูตร excel ท่านจะพบว่ามีความยาวพอสมควร ท่านผู้อ่านสามารถใช้วิธีการสร้าง ตัวแปรแบบไดนามิค และนำเข้ามาใช้ในสูตร excel ดังกล่าวได้ (แทนที่ OFFSET(A:A,0,0,COUNT(A:A),1) ด้วยตัวแปรไดนามิค)

ผลจากการปรับสูตร excel ให้ยืดหยุ่นขึ้น

คำถามเพิ่มเติม หากข้อมูลมีน้อยกว่า 5 เราจะปรับสูตร excel อย่างไร สวัสดีครับ

การคำนวณดอกเบี้ยกู้ซื้อบ้านเพื่อวางแผนลดดอกเบี้ยซื้อบ้าน(Home loan) ตอนที่ 2

ในบทความที่ผ่านมาได้นำเสนอแผนการลดดอกเบี้ยซื้อบ้านด้วยวิธีการชำระค่างวดเพิ่มจาก ค่างวดรายเดือน ซึ่งพบว่าทำให้ระยะเวลาในการผ่อนชำระลดลง รวมถึงดอกเบี้ยซื้อบ้านก็ลดลงไปด้วย ในบทความนี้จะขอการสร้างแบบจำลองการคำนวณดอกเบี้ยกู้ซื้อบ้าน เพื่อช่วยในการจำลองการผ่อนชำระสินเชื่อบ้าน โดยอาศัยการใช้โปรแกรม excel และสูตร excel PMT และ PPMT  แนวคิดการจำลองการคำนวณดอกเบี้ยกู้ซื้อบ้าน จะเริ่มจาก การแยกเงินต้นและดอกเบี้ยที่ชำระในแต่ละงวด และการปรับเปลี่ยนอัตราดอกเบี้ย กู้ซื้อบ้านตามโปรโมชั่นของสถาบันการเงิน ผลที่ได้จากการจำลองจะทำให้เราสามารถเลือกโปรโมชั่นที่เหมาะสมกับเราได้ โดยจะขอสมมุติการกู้ซื้อบ้านตามตัวอย่างในบทความที่ผ่านมา โดยแบบจำลองที่ 1 จะนำเสนอการคำนวณการชำระค่างวดตามที่คำนวณได้จากสูตร excel : PMT และคำนวณเงินต้นจากสูตร excel : PPMT รูปแบบการคำนวณดอกเบี้ยกู้ซื้อบ้าน จะกำหนดให้ หลัก A เป็นลำดับงวดชำระ หลัก B เป็นอัตาดอกเบี้ย หลัก C เป็นเงินค่างวดที่ต้องชำระ หลัก D เป็นเงินต้นที่ชำระในแต่ละงวด หลัก E เป็นดอกเบี้ยซื้อบ้านในแต่ละงวด และ หลัก F เป็นเงินต้นคงเหลือ สมมุติให้เริ่มต้นงวดที่ 1 ตรงแถว 14 โดยเขียนสูตร excel ได้ดังนี้

A14 = 1

B14 = 8

C14 = $E$8

D14 = PPMT(B14/(100*12),A14,$E$7,$E$4,0,0)

E14 = C14-D14

F14 = E4+D14

Copy สูตร excel ในแถวที่ 14 และวางลงแถวที่ 15 และทำการแก้ไขสูตร excel ใหม่บาง CELLS ดังนี้

A15 = A14+1

F15 = F14 + D15

ผลการคำนวณดอกเบี้ยกู้ซื้อบ้านแสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการจำลองสินเชื่อบ้าน

ให้ drag สูตรใน cells A15:F15 ลงไปอีก 358 แถว เพื่อให้ครบ 360 เดือน (แถวที่ 373) และทำการคำนวณดอกเบี้ยกู้ซื้อบ้านรวม เงินต้นกู้ซื้อบ้าน และ เงินทั้งหมดที่ต้องจ่ายในการกู้ซื้อบ้าน โดยใช้สูตร excel ดังนี้

E375 = SUM(E14:E373)

D375 = SUM(D14:D373)

C375 = SUM(C14:C373)

ผลการจำลองสินเชื่อบ้านแสดงได้ดังภาพที่ 2

ภาพที่ 2 ผลรวมดอกเบี้ยกู้ซื้อบ้าน

เอาหล่ะครับ เรามาจำลองแผนลดดอกเบี้ยซื้อบ้านแบบที่ 2 กันครับ โดยเราจะชำระค่างวดในเดือนสุดท้ายของปี (เดือนที่ 12 ) เป็นเงินอีกหนึ่งเท่าตัว โดยจะขอยกตัวอย่างการจ่ายค่างวด 40,000 บาท ในเดือนสุดท้ายของปี  ก่อนอื่นขอให้ท่าน Copy สูตร excel ใน A14: F14 ไปวางเริ่มต้นที่ H14 และทำการแก้ไขสูตร excel ใหม่ ดังนี้

J14 = 20000            กำหนดยอดเงินที่ต้องการชำระแต่ละงวด

K14 = J14-L14       คำนวณยอดเงินต้นที่ชำระ

L14 = (30*I14/(365*100))*L4  เป็นสูตรคำนวณดอกเบี้ยซื้อบ้านรายเดือนจากยอดเงินต้น

M14 = L4-K14        คำนวณยอดเงินต้นคงเหลือ

ทำการ Copy สูตร excel ในแถวที่ 14 และวางลงแถวที่ 15 และทำการแก้ไขสูตร excel ใหม่บาง CELLS ดังนี้

H15 = H14+1

L15 = (30*I14/(365*100))*M14

M15 = M14-K15

ดังแสดงในภาพที่ 3

ภาพที่ 3 ผลการคำนวณดอกเบี้ยกู้บ้าน แบบที่ 2

ให้ drag สูตรใน cells H15:M15 ลงไปอีก 358 แถว เพื่อให้ครบ 360 เดือน (แถวที่ 373) ตามแผนการลดดอกเบี้ยซื้อบ้านแบบที่ 2 ให้ทำการแก้ไข เงินค่างวด ในหลัก J งวด 12 , 24,36 ,48 , .....360 จาก 20,000 เป็น 40,000 บาท การตรวจสอบผลการจำลอง ให้พิจารณายอดเงินต้นคงเหลือที่เป็นลบงวดแรก ซึ่งจะเป็นจำนวนงวดสุดท้ายที่เราต้องชำระ โดยจากตัวอย่างพบว่า การเพิ่มเงินค่างวดในเดือนสุดท้ายของปีอีกหนึ่งเท่า ทำให้เราต้องผ่อนชำระ 194 งวด (แถวที่ 207) ดังแสดงในภาพที่ 4 ซึ่งในงวดที่ 194 เราจะต้องชำระเงินเท่ากับ M206+L207 = 6,813.44+44.80 = 6,858.24 บาท

ภาพที่ 4 แสดงแถวที่ยอดเงินกู้บ้านคงเหลือเป็นลบ

ทำการคำนวณดอกเบี้ยกู้ซื้อบ้านรวม เงินต้นกู้ซื้อบ้าน และ เงินทั้งหมดที่ต้องจ่ายในการกู้ซื้อบ้าน โดยใช้สูตร excel ดังนี้

L375 = SUM(L14:L207)     คำนวณดอกเบี้ยซื้อบ้าน

K375 = SUM(K14:K206) + M206   คำนวณเงินต้นทั้งหมด

J375 = SUM(J14:J206) + M206 + L207   คำนวณเงินที่ชำระสินเชื่อทั้งหมด

ดังแสดงในภาพที่ 5

ภาพที่ 5 ดอกเบี้ยกู้ซื้อบ้านตามแบบที่ 2

ซึ่งจะพบว่าแผนการลดดอกเบี้ยแบบที่ 2 มีประสิทธิภาพกว่าแผนการลดดอกเบี้ยซื้อบ้านแบบที่ 1 ครับโดยสามารถลดดอกเบี้ยได้มากกว่าถึง 463,271 บาท และยังลดเวลาการผ่อนชำระลงได้มากกว่าด้วย แต่ทั้งนี้ก็อยู่ที่ท่านผู้อ่านจะนำไปวางแผนกันหล่ะครับว่า 40,000 บาทมากไปหรือไม่ ซึ่งท่านสามารถทดลองปรับเปลี่ยนได้ในแบบจำลองครับ ในบทความต่อไปจะนำเสนอการคำนวณตามโปรโมชั่นของสถาบันการงาน จำพวก 3 ปี แรก ดอกเบี้ยคงที่ หลังจากนั้นลอยตัว หรือ 0% ใน 1 ปี แรก จากนั้นลอยตัว เพื่อให้ท่านผู้อ่านนำไปจำลองดอกเบี้ยกู้ซื้อบ้านของท่านต่อไป

การคำนวณดอกเบี้ยกู้ซื้อบ้านเพื่อวางแผนลดดอกเบี้ยซื้อบ้าน(Home loan) ตอนที่ 1

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

สมมุติว่า ต้องการซื้อทาวน์เฮาส์ ราคา 2,360,000 บาท ก่อนที่จะวางแผนลดดอกเบี้ยซื้อบ้าน เพื่อให้เสียค่าดอกเบี้ยกู้ซื้อบ้านน้อยที่สุดแล้ว เราจะเริ่มจากคำนวณค่างวดผ่อนชำระรายเดือน ในกรณีที่ อัตราดอกเบี้ยสินเชื่อบ้านคงที่ และ ในระยะเวลากู้ยืมที่มากที่สุดที่เราจะได้ โดยอัตราดอกเบี้ยสินเชื่อบ้าน ขออ้างอิง อัตราดอกเบี้ย MLR+1 และระยะเวลากู้ยืมเป็น 360 เดือน (30 ปี) โดยเงินดาวน์เท่ากับ 0 บาท เราสามารถคำนวณใน Excel ได้ดังภาพที่ 1

ภาพที่ 1 การคำนวนค่างวดกู้ซื้อบ้าน

จากภาพที่ 1 ท่านจะเห็นว่า ท่านจะต้องผ่อนชำระเงินกู้ซื้อบ้านงวดละ 17,316.84 บาท โดยหากท่านผ่อนบ้านด้วยค่างวดดังกล่าวเป็นระยะเวลา 30 ปี ดอกเบี้ยกู้ซื้อบ้านที่เราต้องเสียให้กับสถาบันการเงินจะเท่ากับ 3,874,063.82 บาท  การวางแผนลดดอกเบี้ยซื้อบ้านวิธีแรกที่จะนำเสนอคือ ให้ท่านดำเนินการจ่ายค่างวดมากกว่าค่างวดที่คำนวณได้ สมมุติว่ามีความสามารถจ่ายค่างวดได้ที่เดือนละ 20,000 บาท แบบสบายๆ ตามที่ได้กล่าวไว้แล้วว่า การคำนวณดอกเบี้ยกู้ซื้อบ้านเป็นแบบ ต้นลดดอกลด สมมุติว่าสถาบันการเงินยอมให้เราจ่ายค่างวดกู้ซื้อบ้านเกินกว่าที่กำหนดไว้ได้ตั้งแต่งวดแรก เราจะใช้ฟังก์ชัน Goal Seek ช่วยคำนวณหาระยะเวลาการผ่อนชำระที่ลดลง กรณีที่จ่ายค่างวดเพิ่มขึ้น โดยไปที่เมนู Data เลือก What-If Analysis เลือก Goal Seek ดังภาพที่ 2 จะปรากฏหน้าต่าง Goal Seek ดังภาพที่ 3 โดยกำหนดให้ Set Cell เป็น ค่างวดรายเดือน K7 ให้เป็นค่า (To Cell) -20000 โดยการปรับเปลี่ยนระยะเวลาผ่อนชำระ K6 กด OK Excel จะคำนวณระยะเวลาการผ่อนชำระที่เหมาะสมกับการผ่อนชำระ 20,000 บาท เป็นระยะเวลา 233 เดือน ดอกเบี้ยกู้ซื้อบ้านลดลงเหลือ 2,290,130.06 บาท ซึ่งก็จะช่วยลดดอกเบี้ยซื้อบ้านให้ท่านได้มากพอควรเลยครับ

ภาพที่ 2 การเลือก Goal Seek

ภาพที่ 3 การใช้สูตร Excel

ภาพที่ 4 ผลการคำนวณดอกเบี้ยกู้ซื้อบ้าน กรณีเพิ่มเงินค่างวด

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

หัวข้อที่น่าสนใจ

การคำนวณดอกเบี้ยกู้ซื้อบ้านเพื่อวางแผนลดดอกเบี้ยซื้อบ้าน(Home loan) ตอนที่ 2

การใช้ excel : ประยุกต์การสร้างกราฟเส้นตรงกับกราฟแน้วโน้มการเพิ่มสู่จุดอิ่มตัว

การใช้ excel ในบทความนี้ขอเพิ่มเติมการประยุกต์การสร้างกราฟเส้นตรงเข้ากับข้อมูลที่มีลักษณะการกระจายที่มีอัตราการเพิ่มเข้าสู่จุดอิ่มตัว (Saturation-growth rate) การนำไปใช้ประโยชน์ เช่นการวิเคราะห์หาจุดอิ่มตัวของปริมาณใดๆที่เราสนใจจะวิเคราะห์เพื่อกำหนดแผนการดำเนินการต่อไปเช่น จุดอิ่มตัวหรืออัตราการเติบโตของตลาด (Market Saturate)  จุดอิ่มตัวและอัตราการเติบโตของผลิตภัณฑ์ จุดอิ่มตัวและอัตราการเติบโตทางเศรษฐกิจ (economic growth rate) เป็นต้น

ภาพที่ 1 เส้นกราฟสีน้ำเงินแสดงขนาดของตลาดสินค้าชนิดหนึ่งที่มีลักษณะเข้าสู่จุดอิ่มตัว

Ref. http://en.wikipedia.org/wiki/Market_saturation

หลังจากที่ได้อธิบาย การประยุกต์กราฟเส้นตรงกับข้อมูลที่กระจายแบบกำลังไปแล้ว แนวคิดการแก้ปัญหายังเหมือนเดิม เราจะเริ่มจาก สมการที่ใช้อธิบาย ลักษณะการกระจายข้อมูลแบบที่มีอัตราการเพิ่มเข้าสู่จุดอิ่มตัว เราจะเรียกสมการนี้ว่า Saturation-growth rate Equation โดยมีรูปแบบดังนี้



จากรูปแบบสมการหากทำการคูณไขว้ และจัดรูปสมการใหม่จะได้ดังสมการที่ 1

หากพิจารณาสมการที่ 1 เทียบกับรูปแบบของสมการเชิงเส้น y = mx + c เราจะได้ว่า

สมการแรก หากเราพล็อตกราฟ แกนตั้งเป็นค่า 1/y แกนนอนเป็นค่า 1/x ความชันของชุดข้อมูล m = b/a จุดตัดแกนตั้ง c = 1/a ดังนั้น a = 1/c  ขณะที่ b = m*a

 

การเตรียมข้อมูลใน excel เพื่อคำนวณหาค่าคงที่ a และ b ในสมการการเพิ่มเข้าสุ่จุดอิ่มตัว

สมมุติว่าเรามีข้อมูล x และ y จากข้อมูลจริงและได้บันทึกข้อมูลลง excel ในหลัก A และ B ดังแสดงในภาพที่ 2

 

ภาพที่ 2 ตัวอย่างข้อมูลที่กระจายแบบเพิ่มเข้าสู่จุดอิ่มตัว

 

จากลักษณะของสมการเพิ่มเข้าสุ่จุดอิ่มตัวที่เปลี่ยนเป็นสมการเส้นตรงตามสมการที่ 1 จะต้องแปลงค่า y ให้เป็นค่า 1/y และเก็บค่าไว้ในหลัก D เขียนสูตร excel ได้เป็น D4= 1/B4 และแปลงค่า x ให้เป็นค่า 1/x และเก็บค่าไว้ในหลัก C  เขียนสูตร excel ได้เป็น C4= 1/A4

 

ใช้สูตร excel : SLOPE เพื่อหาความชันและจุดตัดของข้อมูล (1/x , 1/y) ใน D2 และ E2 ได้ดังนี้

สูตร excel

=SLOPE($D$4:$D$13,$C$4:$C$13)

=INTERCEPT($D$4:$D$13,$C$4:$C$13)

เราสามารถคำนวณค่าคงที่ a และ b ใน F2 และ G2 โดยเขียนสูตร excel ได้ดังนี้

= 1/E2

= F2*D2

เราสามารถคำนวณค่า y ของฟังก์ชันยกกำลังได้โดยใช้ค่าคงที่ a และ b ที่คำนวณได้ โดยคำนวณและเก็บไว้ในหลัก D ดังนี้

สูตร excel : D4 = $F$2*$A4/($G$2+$A4) (copy สูตร excel ไปยังแถวถัดไปได้)

 

 

ภาพที่ 3 เปรียบเทียบเส้นแนวโน้มและข้อมูลจริง

 

สูตร excel สำหรับคำนวณค่าตัวแปรตาม (y) ในสมการการเพิ่มเข้าสู่จุดอิ่มตัว

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

 

เมื่อพิจารณาสมการการเพิ่มเข้าสู่จุดอิ่มตัวซึ่งถูกเปลี่ยนอยู่ในรูปสมการเชิงเส้นไปแล้วนั้นจะพบว่า หากใช้สูตร excel : TREND กับข้อมูลดังกล่าว สูตร excel : TREND จะคืนค่า 1/y กลับมา ดังนั้นหากต้องการทราบค่า y ในสมการการเพิ่มเข้าสู่จุดอิ่มตัวจะต้องเขียนสูตร excel ดังนี้ สมมุติให้ เซลล์ C1 เป็นจุด x ที่ต้องการทราบค่า y และค่า y จะถูกคำนวณและแสดงในเซลล์ C2 เราจะเขียนสูตร excel ได้ดังนี้

C2 = 1/(TREND($D$4:$D$13,$C$4:$C$13,1/C1))

ผลคำนวณแสดงได้ดังภาพที่ 4

 

 

ภาพที่ 4 การใช้สูตร excel : TREND

 

ข้อควรระวังสำหรับการประยุกต์สมการเส้นตรงกับ สมการการเพิ่มเข้าสู่จุดอิ่มตัว คือค่า x และ y ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า 1/0 ได้ และจากสมการที่ 1 เราจะพบว่าหากป้อนค่า x = 0 เข้าไป เราจะคำนวณค่า y ได้เท่ากับ 0 ฉะนั้นจึงควรเขียนสูตร excel เพื่อป้องกันการแสดงความผิดพลาดในการคำนวณสูตร excel :TREND ในเซลล์ C2 โดยปรับสูตรใหม่ได้ดังนี้

C2 = IF(C1=0,0,1/(TREND($D$4:$D$13,$C$4:$C$13,1/$C$1))

 

เพิ่มเติม

เราสามารถคำนวณอัตราการเพิ่มหรือเติบโต (growth-rate) ได้โดยการ differential สมการที่ 1 เทียบกับ x จะได้สมการคำนวณอัตราการเติบโตได้ดังสมการ

 

สมมุติกำหนดให้ growth-rate ถูกคำนวณไว้ในเซลล์ H2 โดยสามารถเขียนสูตร excel ได้ดังนี้

 

H2 = $F$2*$G$2/($G$2+$C$1)^2

 

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

 



 

 

 

 

 

 

 

การใช้ excel คำนวณดอกเบี้ยทบต้น (Compound interest)

สวัสดีครับ blog การใช้ excel ขอนำเสนอวิธีการคำนวณดอกเบี้ยทบต้น หรือที่เรียกว่า Compound interest เพื่อให้ท่านผู้อ่านที่กำลังเป็นนักลงทุนหรือกำลังจะกู้เงินได้เป็นแนวทางในการตัดสินใจครับ ก่อนอื่นมารู้จัก ดอกเบี้ยทบต้นก่อน ดอกเบี้ยทบต้นคือ การคำนวณดอกเบี้ย โดยคิดจากจำนวนเงินต้นที่สูงขึ้นเรื่อยๆ จากการนำดอกเบี้ยในแต่ละงวดที่คิดดอกเบี้ยมารวมเป็นเงินต้นในงวดต่อๆไป ดังนั้นดอกเบี้ยในงวดถัดๆไปจะเพิ่มขึ้นเรื่อยๆเนื่องจาก เงินต้นถูกเพิ่มขึ้นเรื่อยๆจะดอกเบี้ยสะสมในแต่ละงวด เรามาเปรียบเทียบการคิดดอกเบี้ยในเงื่อนไขคาบเวลาการคิดดอกเบี้ยที่แตกต่างกัน โดยสมมุติให้ เงินกู้จำนวนหนึ่งคิดดอกเบี้ยเป็นรายปีในอัตราร้อยละ 6 ในขณะที่เงินกู้จำนวนเดียวกันคิดดอกเบี้ยทุกๆ 4 เดือนในอัตราร้อยละ 6 ต่อปี เราจะสร้างตารางคำนวณใน excel   โดยกำหนดให้ หลัก B เป็นอัตราดอกเบี้ยต่อปี ในหลัก C ของเงินกู้ 2 กำหนดรายการคาบเวลาการคิดดอกเบี้ยให้เลือก มีรายการดังนี้ รายปี ทุกครึ่งปี ทุก 4 เดือน ทุก 3 เดือน ทุก 2 เดือน และรายเดือน โดยใช้สูตร excel if เพื่อแสดงจำนวนครั้งในการคิดดอกเบี้ยต่อปีให้เป็น 1, 2, 3, 4,6 และ 12 เดือนในเซลล์ D3 ตามลำดับ ในเซลล์ E3 คำนวณดอกเบี้ยที่แท้จริงต่อปี โดยใช้สูตร excel เป็น E3 =(1+B3/D3)^D3 - 1 ดังแสดงในภาพที่ 1

 

 

ภาพที่ 1 ตารางคำนวณดอกเบี้ยทบต้นใน excel

 

ให้ท่านทดลองปรับเปลี่ยน อัตราดอกเบี้ยหรือ ลักษณะการคิดดอกเบี้ยในตาราง excel ท่านจะพบว่า การลงทุนหรือการกู้เงินที่มีการคิดดอกเบี้ยถี่ๆ อัตราดอกเบี้ยที่แท้จริงต่อปีจะสูงกว่า ซึ่งเราคงต้องการวิธีคิดดอกเบี้ยถี่ๆสำหรับการลงทุน แต่คงไม่ต้องการสำหรับการกู้เงินหรือการเป็นหนี้ ตัวอย่างที่ชัดเจนที่สุดคือ ดอกเบี้ยบัตรเครดิต ซึ่งคิดเป็นรายวันนั่นเอง หากเพิ่มความถี่ในการคิดดอกเบี้ยมากขึ้นเรื่อยๆ ดอกเบี้ยที่แท้จริงจะเข้าใกล้ค่าๆหนึ่ง เราจะเรียกการคิดดอกเบี้ยลักษณะนี้ว่า ดอกเบี้ยทบต้นต่อเนื่อง จากตัวอย่างอัตราดอกเบี้ยร้อยละ 6 ต่อปี หากกำหนดการคิดดอกเบี้ยเป็นรายวันจะพบว่า อัตราดอกเบี้ยที่แท้จริงจะเท่ากับร้อยละ 6.18 หากเพิ่มความถี่เป็น 10,000 จะพบว่า อัตราดอกเบี้ยที่แท้จริงจะเท่ากับร้อยละ 6.18 เช่นกัน ท่านสามารถเพิ่มรายการคิดดอกเบี้ยรายวันลงในรายการได้ โดยกำหนดจำนวนครั้งในการคิดต่อปีเท่ากับ 365 ดังนั้นจึงกล่าวได้ว่าดอกเบี้ยทบต้นต่อเนื่องมีค่าเท่ากับร้อยละ 6.18 กรณีดอกเบี้ยบัตรเครดิต ซึ่งตามกฏหมายกำหนดให้ไม่เกินร้อยละ 28 ต่อปี จะพบว่าอัตราดอกเบี้ยที่แท้จริงต่อปีจะเท่ากับ ร้อยละ 32.30 ครับ น่าเป็นห่วงคนเป็นหนี้บัตรเครดิต หรือคนคิดจะกู้เงินบัตรเครดิตจริงๆ ก็หวังว่าตาราง excel และสูตร excel ที่พัมนาขึ้นคงช่วยท่านผู้อ่านประกอบการตัดสินใจในการลงทุนหรือกู้เงินได้ครับ

 

การใช้ excel ช่วยคำนวณดอกเบี้ยเงินฝากประจำ

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

ดอกเบี้ยที่ได้รับในแต่ละเดือน = เงินต้น x อัตราดอกเบี้ย(ร้อยละต่อปี)xจำนวนวันที่คิดดอกเบี้ย/365

โดยส่วนใหญ่เมื่อครบกำหนดฝากธนาคารจะคิดโบนัสดอกเบี้ยอีกร้อยละ 2.5 ของดอกเบี้ยที่ได้รับ 

เงินต้นที่ใช้คำนวณในแต่ละเดือนจะเพิ่มขึ้นตามเงินฝากที่สะสมในแต่ละเดือน ตาราง excel สำหรับคำนวณดอกเบี้ยแสดงได้ดังภาพที่ 1 โดยสมมุติให้เป็นเงินฝากแบบประจำ 12 เดือน อัตราดอกเบี้ยร้อยละ 3.5 โดยเริ่มเปิดบัญชีเงินฝากวันที่ 1 มค 2555

ภาพที่ 1 ตาราง excel คำนวณดอกเบี้ยเงินฝากประจำ

จากภาพที่ 1 จะขออธิบายสูตร excel และการบันทึกข้อมูลในแต่ละเซลล์ดังนี้

• ข้อมูลจำนวนงวดเงินฝากในหลัก A และ วันเดือนปีที่ฝากเงินในแต่ละเดือน ผู้ใช้ excel ต้องบันทึกทุกเดือน
• การคำนวณเงินต้นสะสมในงวดที่ 2 ถึง 12 จะเขียนสูตร excel โดยอ้างอิงเงินต้นสะสมในเซลล์ C2
• สูตร excel เงินต้นสะสม C3 = $C$2*A3 (copy สูตร excel ลงไปในแถวด้านล่างต่อไป)
• ข้อมูลอัตราดอกเบี้ยเงินฝาก ผู้ใช้ excel ต้องบันทึกทุกเดือน (ในกรณีที่ดอกเบี้ยเงินฝากในแต่ละเดือนไม่คงที่)
• จำนวนสำหรับคำนวณดอกเบี้ยในแต่ละเดือน จะคำนวณระยะห่างจากวันฝากเงินเดือนนี้และวันฝากเงินเดือนถัดไป เราสามารถเขียนสูตร excel ได้ดังนี้ E2 = B3-B4
• ดอกเบี้ยแต่ละเดือน เขียนสูตร excel ได้ดังนี้ F2 = C2*(E2/365)*E2
• ในเซลล์ B14 จะเป็นวันเดือนปีที่ครบกำหนดการฝากประจำ
• ในเซลล์ F14 เป็นดอกเบี้ยรวมทั้ง 12 เดือน เขียนสูตร excel ได้เป็น F14=SUM(F2:F13)
• ในเซลล์ F15 คำนวณโบนัสดอกเบี้ยร้อยละ 2.5 เขียนสูตร excel ได้เป็น F15 = F14*0.025
• ในเซลล์ F16 คำนวณดอกเบี้ย  เขียนสูตร excel ได้เป็น F16 = F15+F14
• ในเซลล์ F17 คำนวณเงินต้นและดอกเบี้ยที่ได้จาการฝากประจำ เขียนสูตร excel ได้เป็น F17 = F13+F16

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

การใช้ excel : ประยุกต์การสร้างกราฟเส้นตรงกับกราฟแนวโน้มแบบยกกำลัง (1)

การใช้ excel ในบทความนี้ขอนำคำถามจากคุณ kv ซึ่งฝากไว้ที่บทความเรื่อง การใช้ excel สร้างกราฟเส้นตรง (Linear graph) เกี่ยวกับการวิเคราะห์ข้อมูลที่มีแนวโน้มแบบยกกำลังหรือแบบ exponential โดยผมจะนำเสนอการประยุกต์การวิเคราะห์ข้อมูลแบบกราฟเส้นตรงที่ได้นำเสนอมาแล้วในบทความดังกล่าว โดยเริ่มต้นจากการแนะนำรูปแบบฟังก์ชันยกกำลังที่นิยมนำมาใช้วิเคราะห์แนวโน้มข้อมูล ซึ่งในบทความนี้ขอนำเสนอ 3 ฟังก์ชันดังนี้ครับ

แนวคิด

เราจะเปลี่ยนฟังก์ชันยกกำลังเหล่านี้ให้อยู่ในรูปของฟังก์ชันเชิงเส้น y = mx + c และประยุกต์ใช้สูตร excel ที่เกี่ยวกับกราฟเส้นตรงซึ่งได้อธิบายไปแล้วในบทความ การใช้ excel สร้างกราฟเส้นตรง คำนวณหาความชันและจุดตัดแกน y และย้อนกลับไปคำนวณหาค่าคงที่ a , b ในฟังก์ชันยกกำลังดังกล่าว และสุดท้ายจะประยุกต์ใช้สูตร excel : TREND ซึ่งใช้สำหรับสมการเส้นตรง ช่วยคำนวณค่า y ในฟังก์ชันยกกำลังดังกล่าว

การเปลี่ยนฟังก์ชันยกกำลังให้เป็นพังก์ชันเชิงเส้น

ทำการ Take ค่า ln กับฟังก์ชันยกกำลังทั้งสองข้างและจัดรูปแบบสมการใหม่ได้ดังนี้ครับ

เมื่อพิจารณาฟังก์ชันที่ถูกแปลงใหม่กับรูปแบบของสมการเส้นตรง y = mx + c เราจะพบว่า

สมการแรก หากเราพล็อตกราฟ แกนตั้งเป็นค่า ln y แกนนอนเป็นค่า x  ความชันของชุดข้อมูล m จะเท่ากับ ln b จุดตัดแกนตั้ง c = ln a ดังนั้น a = exponential(c)  ขณะที่ b = exponential(m)

สมการที่สอง หากเราพล็อตกราฟ แกนตั้งเป็นค่า ln y แกนนอนเป็นค่า x  ความชันของเส้นตรง m จะเท่ากับ b จุดตัดแกนตั้ง c = ln a ดังนั้น a = exponential(c)  ขณะที่ b = m

สมการที่สาม หากเราพล็อตกราฟ แกนตั้งเป็นค่า ln y แกนนอนเป็นค่า ln x  ความชันของเส้นตรง m จะเท่ากับ b จุดตัดแกนตั้ง c = ln a ดังนั้น a = exponential(c)  ขณะที่ b = m 

การเตรียมข้อมูลใน excel เพื่อคำนวณหาค่าคงที่ในฟังก์ชันยกกำลัง

ผมจะยกตัวอย่างการหาวิเคราะห์เส้นแนวโน้มของสมการที่ 1 สมมุติว่าเรามีข้อมูล x และ y จากข้อมูลจริงและได้บันทึกข้อมูลลง excel ในหลัก A และ B ดังแสดงในภาพที่ 1

ภาพที่ 1 ลักษณะข้อมูล

จากลักษณะของฟังก์ชันยกกำลังที่เปลี่ยนเป็นฟังก์ชันเส้นตรงของสมการที่ 1 จะต้องแปลงค่า y ให้เป็นค่า ln y โดยเก็บไว้ในหลัก C เขียนสูตร excel ได้เป็น C4= ln(B4) 

ใช้สูตร excel : SLOPE เพื่อหาความชันและจุดตัดของข้อมูล (x , lny) ใน D2 และ E2 ได้ดังนี้

สูตร excel 

=SLOPE($C$4:$C$13,$A$4:$A$13)

=INTERCEPT($C$4:$C$13,$A$4:$A$13)

เราสามารถคำนวณค่าคงที่ a และ b ใน F2 และ G2 โดยเขียนสูตร excel ได้ดังนี้

=exp(E2)

=exp(D2)

เราสามารถคำนวณค่า y ของฟังก์ชันยกกำลังได้โดยใช้ค่าคงที่ a และ b ที่คำนวณได้ โดยคำนวณและเก็บไว้ในหลัก D ดังนี้

สูตร excel : D4 = $F$2*$G$2^$A4 (copy สูตร excel ไปยังแถวถัดไปได้)

ภาพที่ 2 ผลการคำนวณหาเส้นแนวโน้ม

สูตร excel สำหรับคำนวณค่าตัวแปรตาม (y) ในฟังก์ชันยกกำลัง

ในบทความ การใช้ excel สร้างกราฟเส้นตรง เราใช้สูตร excel : TREND สำหรับคำนวณค่าตัวแปรตามในสมการเส้นตรง เมื่อกำหนดค่าตัวแปรอิสระ x เราสามารถประยุกต์ สูตร excel : TREND กับฟังก์ชันยกกำลังได้ โดยมีหลักการดังนี้ 

เมื่อพิจารณาฟังก์ชันยกกำลังซึ่งถูกเปลี่ยนอยู่ในรูปสมการเชิงเส้นไปแล้วนั้นจะพบว่า หากใช้สูตร excel : TREND กับข้อมูลดังกล่าว สูตร excel : TREND จะคืนค่า ln y กลับมา ดังนั้นหากต้องการทราบค่า y ในฟังก์ชันยกกำลังจะต้องเขียนสูตร excel ดังนี้ สมมุติให้ เซลล์ C1 เป็นจุด x ที่ต้องการทราบค่า y และค่า y จะถูกคำนวณและแสดงในเซลล์ C2 เราจะเขียนสูตร excel ได้ดังนี้

C2 = exp(TREND($C$4:$C$13,$A$4:$A$13,$C$1))

ในกรณีสมการที่ 3 สูตร excel จะเปลี่ยนเป็น

C2 = exp(TREND($C$4:$C$13,$A$4:$A$13,ln($C$1)))

 

ผลคำนวณแสดงได้ดังภาพที่ 3

ภาพที่ 3 ผลการประยุกต์ใช้สูตร excel :TREND

จากที่ผมยกตัวอย่างมาเป็นการวิเคราะห์แนวโน้มตามลักษณะของสมการที่ 1 โดยท่านผู้อ่านสามารถนำไปรูปแบบการคำนวณใน excel ไปใช้กับสมการที่ 2 ได้ทันที 

โดยปรับสูตร excel ใน G2 จาก G2 = exp(D2) เป็น G2 = D2 และ

ปรับสูตร excel ใน D4-D13 เป็น D4 = $F$2*exp($G$2^$A4) (copy สูตร excel ไปยังแถวถัดไปได้)

ในขณะที่รูปแบบการคำนวณใน excel สำหรับสมการที่ 3 นั้นจะปรับเปลี่ยนการคำนวณดังนี้

คำนวณ ln x ในหลัก C

คำนวณ ln y ในหลัก D

คำนวณค่า slope ใน D2 ด้วยสูตร excel : D2=SLOPE($D$4:$D$13,$C$4:$C$13)

คำนวณค่า Intercept ใน E2 ด้วยสูตร excel : E2=SLOPE($D$4:$D$13,$C$4:$C$13)

คำนวณค่า a ใน F2 ด้วยสูตร excel : F2=exp(E2)

คำนวณค่า a ใน G2 ด้วยสูตร excel : G2=D2

ข้อควรระวังสำหรับการประยุกต์สมการเส้นตรงกับ สมการที่ 1-3 คือค่า y ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า ln 0 ได้ ในขณะที่สมการที่ 3 ค่า x ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า ln 0 ได้ 

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

การใช้ excel สร้างกราฟสำหรับบันทึกผลการลดน้ำหนัก

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

ภาพที่ 1 แบบฟอร์มการบันทึกผลการลดน้ำหนักใน excel

การสร้างรูปแบบและสูตร excel ในแบบฟอร์มมีขั้นตอนดังนี้

• กำหนดชื่อข้อมูลความสูงเป็นแบบคงที่ชื่อ High ให้กับ CELL B1 ไปแล้ว
• กำหนดรูปแบบของวันเดือนปีที่บันทึกในหลัก A เป็น d/m/yyyy
• กำหนดความละเอียดของค่าน้ำหนัก(หลัก C) เป็นทศนิยม 1 ตำแหน่ง
• กำหนดความละเอียดของค่า BMI และ อัตราน้ำหนัก (หลัก D และ E) เป็นทศนิยม 2 ตำแหน่ง
• กำหนดพื้นหลังของหลัก A และ C เป็นสีส้ม

การใช้สูตร excel คำนวณลำดับวัน

ใช้สูตร excel : DATEDIF ตั้งแต่ Cell B4 ลงไป เพื่อคำนวณลำดับวันของการชั่งน้ำหนักเมื่่อนับจากวันแรกที่บันทึกข้อมูล โดยเขียนสูตร excel ได้ดังนี้

(สมมุติเป็น cell B4) = IFERROR(DATEDIF($A$4,$A4+1,"D"),"")

การใช้สูตร excel คำนวณค่า BMI

เขียนสูตร excel ที่ cell D4 ได้ดังนี้ = IFERROR((High^2/$C4)^-1,"")

การใช้สูตร excel คำนวณค่าอัตราน้ำหนัก

เขียนสูตร excel ที่ cell E4 ได้ดังนี้ = IFERROR((($B3-$B4)/($C$3-$C4))^-1,"")

สำเนาสูตร excel ข้างต้นลงไปตามแถวด้านล่างประมาณแถวที่ 200 เพื่อให้สูตร excel สามารถคำนวณโดยอัตโนมัติเมื่อมีการบันทึกข้อมูลในหลัก A และ C

สร้างชื่อข้อมูลแบบพลวัตรให้กับข้อมูลหลัก B และ D เพื่อให้กราฟแสดงค่า BMI สามารถอัพเดตข้อมูลแบบอัตโนมัติทุกครั้งที่มีการบันทึกข้อมูล

การใช้งานแบบฟอร์มบันทึกการลดน้ำหนักเริ่มจาก

บันทึกวันเดือนปีและน้ำหนักที่ชั่งได้ในแต่ละวัน กราฟแสดงค่า BMI แสดงได้ดังภาพที่ 2

ภาพที่ 2 ผลการใช้งานแบบฟอร์มบันทึกผลการลดน้ำหนัก

กรณีที่มีการบันทึกการลดน้ำหนักเป็นเวลานาน จะพบปัญหาจำนวนวันมากขึ้นอาจจะปรับสเกลจำนวนวันจาก Linear เป็นสเกล Log เพื่อทำให้อ่านผลการลดน้ำหนักได้ง่ายขึ้นครับ download Excel ไฟล์บันทึกการลดน้ำหนักไปใช้กันได้ครับ

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

Excel Tips: การแสดงผลรวมเวลาที่มากกว่า 24 ชั่วโมง

Excel Tips วันนี้ขอนำเสนอ วิธีการแสดงผลเวลาที่มากกว่า 24 ชั่วโมง Excel Tips นี้จะเป็นประโยชน์กับผู้ที่กำลังใช้ Excel ในงานสำนักงานหลายๆด้านๆ เช่น ผู้ที่กำลังจะประมวลผล KPI ผู้ที่กำลังใช้ excel คำนวณชั่วโมงรวม OT ของพนักงาน หรือ ชั่วโมงรวมการเข้าอบรมของพนักงานเป็นต้น ในบทความที่ผ่านๆมาของผม ได้นำเสนอ ตัวอย่าง KPI บางหัวข้อที่เกี่ยวข้องกับเวลา เช่น lead KPI ค่า MTTR เป็นต้น ตามปกติหากต้องการหาผลรวมของค่าชั่วโมงใน excel จะพบว่า หากผลรวมเวลามีค่าเกิน 24 ชั่วโมง excel จะละเว้นไม่นำค่าที่เกินมาแสดงเนื่องด้วย excel ทราบว่า 1 วันมี 24 ชั่วโมง ดังนั้นความผิดพลาดจากการคำนวณก็จะเกิดขึ้นซึ่งมีผลต่อความถูกต้องของผลลัพธ์ในลำดับถัดไปได้ เรามาดูตัวอย่างการหาผลรวมจำนวนชั่วโมงซึ่งใช้สูตร excel : SUM ให้ผลลัพธ์ที่ผิดออกมา ดังภาพที่ 1  โดยผลลัพธ์ที่ถูกต้องควรเป็น 30 ชั่วโมง

ภาพที่ 1 สูตร excel : SUM ให้ผลรวมเวลาผิด

ผลรวมเวลาที่ถูกต้องสามารถหาได้โดยการแก้ไขรูปแบบการแสดงผลของ CELL C11 ใหม่จากเดิม h:mm เป็น [h]:mm หรือหากต้องการแสดงค่าวินาทีด้วยก็สามารถแก้รูปแบบแสดงผลเป็น [h]:mm:ss ผลลัพธ์จากสูตร excel แสดงดังภาพที่ 2

ภาพที่ 2 สูตร excel สำหรับรวมเวลาหลังแก้ไขรูปแบบการแสดงผล