การใช้สูตร Convert ใน Excel

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

Convert(number , from_unit,to_unit) โดย

number คือ ตัวเลขที่ต้องการแปลงหน่วย

from_unit คือ หน่วยของตัวเลขปัจจุบัน

to_unit คือหน่วยที่ต้องการแปลง

สิ่งสำคัญคือหน่วยที่ต้องแปลงต้องสอดคล้องกัน ดังนั้นผู้ใช้ Excel ต้องจดจำหน่วยที่ Excel รองรับ ในแต่ละมิติ ตัวอย่างการใช้สูตร Excel : Convert แปลงหน่วยอุณหภูมิ ดังภาพที่ 1

ภาพที่ 1 การแปลงหน่วยอุณภูมิ

ตย หน่วยที่รองรับใน Excel

ความยาว  :  m , mi(ไมล์) ,in (นิ้ว) , ft (ฟุต)

ดวามดัน   : Pa , PSI , mmHg

แรง           : N , lbf , pound

กำลัง        : HP , W

ความเร็ว   : kn(knot) , m/s , mph

mass         : g , sg(slug) , lbm(poundmass) , ton

 

การประยุกต์ใช้สูตร Convert สำหรับแปลงหน่วยที่ซับซ้อนขึ้นจะแสดงในบทความต่อๆไปครับ

Excel Tips : การใช้ Data Validation ตรวจสอบงบหรือบัญชี

ในบทความนี้จะขอยกตัวอย่างการประยุกต์ใช้ Data Validation ของ Excel ในอีกมิติหนึ่งครับ จากที่เคยนำเสนอบทความเรื่องการใช้ Excel การจัดทำบัญชีรายรับรายจ่าย(บัญชีเงินสด)  การใช้ Excel การจัดทำบัญชีรายรับรายจ่าย(บัญชีบัตรเครดิต)  การใช้ Excel การจัดทำบัญชีรายรับรายจ่าย(บัญชีเงินฝากออมทรัพย์) และการใช้ Excel จัดทำงบดุล ไปแล้ว ล่าสุดได้แนะนำการประยุกต์ใช้ Pivot table ในการวิเคราะห์รายรับรายจ่าย  ซึ่งผมแนะนำว่าในการบริหารบัญชีต่างๆที่ได้กล่าวมาจะต้องมีขอบเขตที่เหมาะสม เช่น รายจ่ายตลอดทั้งเดือนต้องไม่เกินเท่าไหร่ รูดบัตรเครดิตแต่ละเดือนไม่เกินเท่าไหร่เป็นต้น ดังนั้นในขณะที่เราบันทึกข้อมูลลงในบัญชีเหล่านี้ ในแต่ละเดือนหากท่านผู้อ่านใช้ Data validation ช่วยตรวจสอบรายการต่างๆในแต่ละบัญชี  ก็จะช่วยเตือนสติของท่านได้ เรามาดูกันเลยครับ 

จากตัวอย่าง การจัดทำบัญชีรายรับรายจ่าย(บัญชีเงินสด) ให้ท่านผู้อ่านเลือก Cell E4 ถึง E12 เพื่อเป็น Cell ที่คำนวณผลรวมและตรวจสอบความถูกต้องของรายการที่ลงบัญชี ดังภาพที่ 1 จากนั้นให้คลิกคำสั่งใช้งาน Data Validation และกำหนดให้ตรวจสอบผลรวมของรายจ่ายตั้งแต่ E4 - E12 โดยใช้คำสั่ง SUM และกำหนดเงื่อนไขให้ผลรวมของรายจ่ายต้องไม่เกิน Budget ที่เรากำหนดไว้ เช่น รายจ่ายเงินสดของเดือนนี้ต้องไม่เกิน 15,000 บาท เราจะเขียนสูตร Excel ในช่อง Formula ได้ดังนี้ SUM(E$4:E12) <= 15000 ดังแสดงในภาพที่ 1 โดยกำหนดให้ประเภทของการแจ้งเตือนข้อผิดพลาดเป็นคำเตือน ซึ่งจะทำให้เราสามารถกำหนดรายการที่เกินงบได้ แต่จะมีคำเตือนให้ทราบว่าขณะนี้ ผลรวมรายจ่ายของเราเดือนนี้เกิน Budget ที่ตั้งไว้แล้ว

ภาพที่ 1 การกำหนด Data Validation ใน Excel

ในการใช้งาน เมื่อมีการบันทึกรายจ่ายเพิ่ม ให้ท่านแทรกแถวใหม่ลงใน Worksheet ก่อนแถว 12 และบันทึกรายรับรายจ่ายลงตามปกติ สูตร Excel : SUM ที่กำหนดไว้ใน Data Validation ก็จะปรับเปลี่ยนตำแหน่งของรายจ่ายที่ต้องรวมให้ใหม่โดยอัตโนมัติ มาดูตัวอย่างกันครับสมมุติผมเพิ่มรายการอีก 1 รายการเป็นค่าซื้อของใช้ประจำเดือนเป็นเงิน 3,500 บาท ผลของการตรวจสอบจะเป็นดังภาพที่ 2 โดยจะมีหน้าต่างคำเตือนแจ้งให้ทราบว่า รายจ่ายรวมตอนนี้ของท่านเกิน Budget ที่ตั้งไว้ หากท่านต้องการลงรายการก็ให้กด ใช้ เพื่อรับทราบ ท่านก็สามารถบันทึกค่าใช้จ่ายรายการนี้ลงบัญชีรายรับรายจ่ายได้ดังภาพที่ 3 ซึ่งหลังจากรายจ่ายรายการนี้ไปถ้าท่านบันทึกลงบัญชีรายรับรายจ่ายท่านจะพบคำเตือนนี้แจ้งให้ท่านทุกครั้ง ก็เป็นการเตือนสติท่านอยู่เสมอว่า Over Budget แล้วนะเดือนนี้ 

ภาพที่ 2 คำเตือนหลังจากที่มีการบันทึกรายจ่ายที่ผลรวมรายจ่ายเกิน Budget

ภาพที่ 3 แม้จะเกิน Budget รายจ่าย หากจะทำรายการต่อก็สามารถบันทึกลงรายการได้

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

Excel Tips : การใช้ Data Validation ป้องกันการบันทึกวันเวลาผิดพลาด

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

 

ก่อนอื่นตรวจสอบวันเดือนปีที่กรอกใน CELL ต้องไม่ใช่วันเสาร์อาทิตย์ โดยเราจะใช้สูตร Excel : WEEKDAY ซึ่งสูตร Excel จะคืนลำดับของวันใน 1 สัปดาห์กลับมา โดย เรียงลำดับดังนี้

 1 Sunday  2 Monday  ......  7 Saturday ครับ 

จากค่าที่ส่งคืนกลับ เราจะตรวจสอบ เงื่อนไขว่า ค่าวันที่คืนกลับมาต้องไม่เป็ย 1 และ 7 ซึ่งเป็นวันอาทิตย์ และ เสาร์ โดยเงื่อนไขการตรวจสอบต้องไม่ใช่ทั้ง 2 เงื่อนไข นั่นคือ ทั้งวันอาทิตย์และวันเสาร์ ดังนั้นเราจะเขียนเงื่อนไขตรวจสอบทั้ง 2 ได้โดยใช้สูตร AND  มาดูสูตร Excel กันเลยครับ

สมมุติ ต้องการตรวจสอบวันเดือนปีที่ Cell B4 เราจะเขียนสูตร Excel ได้ดังนี้

AND(WEEKDAY(B4) <> 1 , WEEKDAY(B4) <> 7)

จากสูตร Excel ดังกล่าว หากวันเดือนปีที่ตรวจสอบไม่ใช่วันอาทิตย์และเสาร์ จะคืนค่า TRUE มาให้ครับ ดังแสดงในภาพที่ 1 

ภาพที่ 1 ผลการทดลอง สูตร Excel สำหรับตรวจสอบความถูกต้องของวันเดือนปี

มาถึงจุดนี้ให้ท่านผู้อ่านลองแก้ไข วันเดือน ปี ใน Cell B4 และดูผลลัพธ์ที่เกิดขึ้นดูครับ 

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

เอาหล่ะครับ มาถึงตอนนี้ เราจะ Copy สูตร Excel ใน D4 ไปใช้ใน Data Validation ซึ่งได้แสดงวิธีการกำหนดใน บทความ การใช้ Data Validation เพื่อป้องกันความผิดพลาดไปแล้ว  ให้ท่านผู้อ่านเลือก Cell B4:B10 ก่อนเรียกหน้าต่าง การตรวจสอบความถูกต้องของข้อมูล ดังแสดงในภาพที่ 2 

ภาพที่ 2 การกำหนดสูตร Excel ใน Data Validation

ผลลัพธ์ของการตรวจสอบวันเดือนปีที่บันทึกไม่ถูกต้องแสดงได้ดังภาพที่ 3

ภาพที่ 3 ผลลัพธ์กรณีวันเดือนปีที่บันทึกเป็นวัน เสาร์ อาทิตย์

เอาหล่ะครับ มาถึงตรงนี้ท่านผู้อ่านคงได้เห็นประโยชน์ของการใช้ Data Validation ในอีก Application หนึ่ง ซึ่งที่ผมได้ยกตัวอย่างมาเป็นเพียงการประยุกต์ใช้เล็กๆน้อยๆ ซึ่ง Excel ยังมีความสามารถมากกว่านี้อีกมาก เชื่อว่า มาถึงตรงนี้ ผู้ใช้ Excel อยู่คงมีความมั่นใจในความถูกต้องของ Data ที่บันทึกลงไฟล์ Excel มากขึ้นนะครับ  

Excel Tips : การใช้ Data Validation เพื่อป้องกันการกรอกข้อมูลผิดรูปแบบ-1

สวัสดีครับท่านผู้อ่าน เรื่องของ Data Validation ผมได้นำเสนอมาหลากหลายทั้ง การป้องกันการบันทึกรายการข้อมูลซ้ำกันเหมาะสำหรับพวกการเก็บ Invoice หรือข้อมูลที่ต้องไม่ซ้ำกัน หรือวิธีการสร้างรายการข้อมูล(List) และการสร้างรายการที่มีรายการย่อย เป็นต้น ในบทนี้จะขอนำเสนอการใช้ Data Validation อีกตัวอย่างหนึ่งใน Excel นั่นคือ การป้องกันการกรอกข้อมูลผิดรูปแบบ ตัวอย่างคือ Code ต่างๆที่มีรูปแบบแน่นอน  สมมุติว่า เป็น Code วัตถุดิบที่ต้องนำเข้า โดยรูปแบบของ Code จะต้องขึ้นต้นด้วยตัวอักษร R และต่อท้ายด้วยตัวเลข  4 หลัก เช่น R0125 เป็นต้น ดังนั้น เราต้องใช้ Data Validation ใน Excel ช่วยป้องกันการกรอก Code ผิดรูปแบบ มาดูแนวคิดกันครับ การทำงานของ Excel ต้องตรวจสอบเงื่อนไข ดังนี้ครับ

อักขระตัวแรกต้องเป็นตัว R และ ต้องมีความยาวของโค้ดรวม 5 ตัว และ 4 ตัวหลังต้องเป็นตัวเลข

สมมุติว่า Code วัตถุดิบถูกกรอกที่ Cell B2 

ให้ท่านทดลองเขียนสูตร Excel ในCell D3 ดังนี้ครับ AND(LEFT(B2) = "R",LEN(B2) = 5 , ISNUMBER(VALUE(RIGHT(B2,4)))

ขออธิบายสูตร Excel นี้นะครับ

ในสูตร AND จะมีเงื่อนไขให้ตรวจสอบ 3 เงื่อนไข โดยคืนค่า TRUE หรือ 1 ก็ต่อเมื่อ ทั้งสามเงื่อนไขเป็นจริง นอกนั้นจะคืนค่า FALSE หรือ 0 ครับ

เงื่อนไขแรก จะตรวจสอบอักขระตัวแรกว่าเป็น R หรือไม่ โดยใช้สูตร LEFT(B2) = "R" เป็นการบังคับให้ผู้กรอกต้องพิมพ์ R เท่านั้น

เงื่อนไขต่อมา จะตรวจสอบว่าอักขระที่กรอกลงไปมีทั้งหมด 5 ตัวหรือไม่ โดยใช้สูตร LEN(B2) = 5 เป็นการบังคับให้ต้องกรอกอักขระ 5 ตัวเท่านั้น

เงื่อนไขสุดท้าย จะเริ่มจากตัดอักขระ 4 ตัวจากด้านขวา โดยใช้สูตร RIGHT(B2,4) จากนั้นจะแปลงอักขระเป็นตัวเลขโดยใช้ สูตร VALUE สุดท้ายจะตรวจสอบว่าอักขระที่แปลงเป็นตัวใช้หรือไม่โดยใช้สูตร ISNUMBER 

ผลแสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการตรวจสอบเงื่อนไข

เอาหล่ะครับ ถึงตอนนี้ท่านผู้อ่านทดลองกรอก Code ในช่อง B2 และสังเกตุผลลัพธ์ใน Cell D2 ดูนะครับ จากนั้นเราจะ Copy สูตร Excel ใน Cell D2 ไปใช้ใน Data Validation มาต่อกันเลยครับ 

1. คลิกเมาส์เลือก Cell B2:B10 เลือก เมนู Data และเลือกทูลบาร์ Data Validation จะปรากฎไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

2. คลิก Tab การตั้งค่า

3. ในหัวข้อ อนุญาตให้ เลือก กำหนดเอง

4. ในหัวข้อ Formula ให้วางสูตร Excel ที่ Copy จาก D2 ลงไป ดังแสดงในภาพที่ 2

ภาพที่ 2 การกำหนดเงื่อนไขใน Formula

5. คลิก Tab ข้อความที่ใส่ เพื่อกำหนดการแสดงข้อความเมื่อ Cell B2:B10 ถูกเลือก เป็นการช่วยลดความผิดพลาดลงไปได้อีก ดังแสดงในภาพที่ 3 

ภาพที่ 3 การกำหนดข้อความแจ้งเตือนขณะกรอก Code

6. คลิก Tab การแจ้งเตือนข้อผิดพลาด เพื่อกำหนดข้อความแจ้งเตือนเมื่อมีการกรอก Code ผิดรูปแบบที่กำหนด ดังแสดงในภาพที่ 4

7. คลิกปุ่ม ตกลง เพื่อปิดไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

ภาพที่ 4 การกำหนดการแจ้งเตือนข้อผิดพลาดที่เกิดขึ้น

ภาพที่ 5 ผลลัพธ์จากการกรอก Code วัตถุดิบผิดรูปแบบ

เอาหล่ะครับ มาถึงตรงนี้เราก็กำหนดเงื่อนไขเพื่อตรวจสอบความถูกต้องของ Code วัตถุดิบ Cell B2 ถึง B10 เสร็จเรียบร้อย หากท่านกรอก Code วัตถุดิบผิดรูปแบบ Excel ก็จะแจ้งเตือนความผิดพลาดท่านด้วย Dialog ที่เราได้กำหนดไว้ ดังแสดงในภาพที่ 5 เท่านี้เราก็มั่นใจได้แล้วว่าข้อมูลที่เราบันทึกเข้ามาเก็บใน Cell ดังกล่าวมีความถูกต้องแน่นอน หวังว่าจะนำไปประยุกต์ใช้กันต่อได้นะครับ 

บทความที่ใกล้เคียงกัน

การใช้ Data Validation ป้องกันการบันทึกวันเวลาผิดพลาด

มาใช้ Excel ช่วยดำเนินการกับเมตริกซ์กัน (How to use MS Excel to operate with matrix)

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

การหา Determinant ของ Matrix

Determinant ของเมตริกซ์เป็นค่าที่แสดงคุณลักษณะเฉพาะของเมตริกซ์ใดๆ โดยเมตริกซ์ที่จะหา Determinant ได้ต้องเป็นเมตริกซ์ที่มีจำนวนแถวเท่ากับจำนวนหลัก (เมตริกซ์จัตตุรัส) ใน Excel จะใช้สูตร MDETERM โดยมีรูปแบบการใช้สูตร Excel ดังนี้

MDETERM(array) โดย Excel จะคืนค่า #VALUE! เมื่อมีการกำหนด array ไม่ถูกต้อง 2 รูปแบบดังนี้

1. สมาชิกใน array อย่างน้อยหนึ่งตัวเป็นค่าว่าง หรือเป็นข้อความ

2. array หรือ matrix ไม่เป็นเมตริกซ์จัตตุรัส

การใช้สูตร ขอยกตัวอย่าง 2 รูปแบบ สมมุติต้องการหา Determinant ของเมตริกซ์ 3x3 ทีมีสมาชิกดังนี้

4    -4    0

-1    4   -2

0    -2    4

1. สมมุติต้องการแสดงค่า Determinant ที่ เซลล์ B3 เราสามารถเขียนสูตร Excel ได้ดังนี้

B3 = MDETERM({4,-4,0;-1,4,-2;0,-2,4})

2 . กำหนดสมาชิกของเมตริกซ์ลงในเซลล์และใช้การเลือกเซลล์เมตริกซ์ที่ต้องการคำนวณ เช่น กำหนดสมาชิกเมตริกซ์ลงในเซลล์ D2:F4 ดังแสดงในภาพที่ 1 เราสามารถเขียนสูตร Excel ใน B3 ได้ดังนี้

B3 = MDETERM(D2:F4)

ผลลัพธ์แสดงได้ดังภาพที่ 1

ภาพที่ 1 ใช้สูตร Excel คำนวณ Determinant ของเมตริกซ์

เพิ่มเติม

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

2. ก่อนที่จะดำเนินการใดๆกับเมตริกซ์ ควรหา Determinant ของเมตริกซ์นั้นๆก่อนเป็นลำดับแรกเพื่อตรวจสอบคุณลักษณะของเมตริกซ์นั้น

หัวข้อที่เกี่ยวข้อง
1. การใช้ Excel คำนวณผลคูณระหว่างเมตริกซ์
2. การใช้ Excel ช่วยหาเมตริกซ์ผกผัน
3. การใช้ Excel แก้ระบบสมการเชิงเส้น

การประยุกต์ใช้สูตร excel สำหรับวิเคราะห์การถดถอยแบบเชิงเส้น

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

Y = mX + c 

โดยที่ m คือ ความชันของเส้นตรง c คือ จุดตัดของเส้นตรงบนแกน Y เมื่อ X ตัวแปรอิสระ

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

ภาพที่ 1 ชุดข้อมูล

รูปแบบการใช้สูตร Linest มีดังนี้

LINEST(known_y's, [known_x's], [const], [stats])   สูตรนี้จะคืนค่ากลับเป็น Array

เมื่อ

known_y's คือข้อมูลตัวแปรตามที่ทราบ

[known_x's] คือ ข้อมูลอาเรย์ x ตัวแปรอิสระ หากไม่ใส่ Excel จะกำหนดให้เป็นค่า 1,2,3,… จนเท่ากับจำนวนของตัวแปรตาม

[const] คือ ตรรกะที่ใช้ระบุให้ Excel คำนวณค่าจุดตัดแกน (c )หรือไม่ หากเป็น TRUE จะคำนวณตามปกติ หากเป็น FALSE จะกำหนดให้ c = 0

[stats] คือ ตรรกะที่ใช้ระบุให้ Excel ส่งกลับผลคำนวณค่าทางสถิติหรือไม่ โดย หากเป็น TRUE  จะส่งกลับหากเป็น FALSE จะไม่ส่งกลับ

เนื่องจากสูตร Excel : Linest จะคืนค่าผลการคำนวณในรูปแบบ Array ดังนั้นจะต้องใช้สูตร Excel:Index ช่วยเพื่อตำแหน่งของตัวแปรที่ต้องการแสดงผล

ให้พิมพ์ข้อความใน E2 E3 และ E4 ดังนี้ m , c , r^2

พิมพ์สูตร excel ใน F2 เป็น  =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),1,1)  จากนั้นกดปุ่ม Ctrl + Shift+ENTER  เพื่อเป็นการคำนวณแบบ Array

พิมพ์สูตร excel ใน F3 เป็น  =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),1,2)  จากนั้นกดปุ่ม Ctrl + Shift+ENTER  เพื่อเป็นการคำนวณแบบ Array

พิมพ์สูตร excel ใน F4 เป็น  =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),3,1)  จากนั้นกดปุ่ม Ctrl + Shift+ENTER  เพื่อเป็นการคำนวณแบบ Array

จากผลการใช้สูตร Excel :Linest จะได้แบบจำลองสมการเชิงเส้นที่มี m = 2.037, c = -0.096, r^2 = 0.99 ค่า r^2 เป็นค่าทางสถิติที่ได้จากการวิเคราะห์การถดถอยของ Excel โดยพบว่าเข้าใกล้ 1 มากๆ แสดงถึงแนวโน้มความสัมพันธ์ระหว่างตัวแปรตาม y และตัวแปรอิสระ x ของข้อมูลชุดนี้มีความเป็นเชิงเส้นมาก ดังแสดงในภาพที่ 2 จากแบบจำลองสมการเชิงเส้นที่วิเคราะห์ได้ทำให้ผู้ใช้สามารถประมาณค่าภายในช่วง (Interpolation) ของชุดข้อมูลดังกล่าวได้เมื่อทราบตัวแปรอิสระ

ภาพที่ 2 ผลการใช้สูตร Excel

ซึ่งจากผลการประยุกต์ใช้สูตร Excel : Linest จะทำให้ผู้ใช้มีความมั่นใจที่จะเลือกใช้แบบจำลองสมการเชิงเส้นสำหรับอธิบายความสัมพันธ์ของข้อมูลที่มีอยู่ ในขณะเดียวกันหากค่า r^2 ของแบบจำลองสมการเชิงเส้น มีค่าน้อยกว่า 1 มากๆ จะแสดงให้ทราบว่าความสัมพันธ์ของชุดข้อมูลดังกล่าวไม่เป็นเชิงเส้น อาจจะเป็นแบบพหุนาม หรือ exponential ก็เป็นได้ ในทำนองเดียวกันเราสามารถประยุกต์ใช้สูตร Excel : Linest กับแบบจำลองเชิงเส้นแบบหลายเชิง แบบจำลองพหุนาม (โพลิโนเมียล) รวมถึง สมการแบบยกกำลังและแบบ log ได้ดังจะนำเสนอในครั้งต่อๆไป

การใช้ excel : มาใช้ pivot table ช่วยเก็บข้อมูลการลาของพนักงานกัน

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

1. ลากิจ

2. ลาป่วย

3. ลาพักร้อน

4. ขาดงาน

เรามาออกแบบฟอร์มการเก็บข้อมูลการลาของพนักงานก่อนครับ ในแบบฟอร์มการบันทึกข้อมูล ฐานข้อมูลจะแบ่งออกเป็นฟิลด์ต่างๆดังนี้ครับ

1. วันเดือนปีที่ลา column B

2. รายชื่อพนักงาน column C

3. ประเภทการลา column D

4. เวลาในการลา ในที่นี้จะกำหนดให้เป็น 1 วันหรือ 0.5 วัน เท่านั้น column E

ฐานข้อมูลพนักงานเราจะเก็บไว้ใน worksheet ชื่อ emp ใน column B โดยกำหนดข้อมูลเป็น Dynamic Data ชื่อ empy ดังแสดงในภาพที่ 1 การกำหนดข้อมูลเป็นแบบ Dynamic Data ทำให้หากมีพนักงานเพิ่มข้อมูลพนักงานก็จะอัพเดตอัตโนมัติ 

ในขณะเดียวกันเราจะสร้างข้อมูลประเภทการลาทั้ง 4 ไว้ใน worksheet ชื่อ type 

โดยกำหนดชื่อข้อมูลเป็น typ

ในแบบฟอร์มการเก็บข้อมูลการลา เราจะกำหนดข้อมูลใน cell B3 และ C3 เป็นแบบรายการโดยกำหนดให้เท่ากับ empy และ typ ดังแสดงในภาพที่ 2

ทำการ copy รูปแบบข้อมูลใน B3 และ C3 ลงไปในแถวด้านล่างตามที่ต้องการ เท่านี้เราก็สามารถใช้งานฟอร์มบันทึกการลาได้แล้ว โดยสามารถเลือกรายชื่อพนักงาน และประเภทการลาได้ ดังแสดงในภาพ

ในการแสดงรายงานเพื่อสรุปการลาของพนักงานเราจะใช้ความสามารถของ PivotTable ช่วยในการออกแบบ Report โดย เราจะกำหนดขอบเขตของฐานข้อมูลในแบบฟอร์มการเก็บข้อมูลการลาเป็นแบบ Dynamic Data โดยกำหนดชื่อเป็น tb_data และใช้เป็นช่วงข้อมูลใน PivotTable เพื่อให้เมื่อมีการเพิ่มข้อมูลในแบบฟอร์มการลาแล้ว เมื่อคลิกปุ่ม Refresh จะทำให้ PivotTable มีการอัพเดตข้อมูลแบบอัตโนมัติ

เราจะสร้าง Pivot Table สำหรับสรุปข้อมูลการลา 2 รูปแบบดังนี้

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

ท่านสามารถ download ไฟล์ excel เพื่อนำไปใช้งานได้ที่นี่ครับ 

ในการประยุกต์เพิ่มเติมฟิลด์ข้อมูลในการเก็บหรือสรุปรายงานจาก Pivot Table ท่านสามารถทดลองปรับดูได้ในไฟล์ดังกล่าว

การใช้ excel : สูตรการปัดเศษตัวเลขใน excel (round number in excel)

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

1. Round เป็นสูตรที่ใช้ในการปัดเศษตัวเลขให้เป็นจำนวนทศนิยมที่ต้องการตามปกติ หรือเป็นจำนวนเต็มที่หารด้วย 10 เช่น 150 1500 เป็นต้น

กรณีปัดเศษทศนิยม

รูปแบบสูตร excel  : ROUND(ตัวเลขที่ต้องการปัดเศษ,จำนวนจุดทศนิยม)

เช่น Round(1.2578,2) = 1.26 

       Round(1.2548,2) = 1.25

กรณีปัดเป็นเลขจำนวนเต็มที่หารด้วยสิบลงตัว

รูปแบบสูตร excel : ROUND(ตัวเลขที่ต้องการปัดเศษ,-จำนวนหลักด้านซ้ายของจุดทศนิยม)

เช่น Round(124.78,-1) = 120 

       Round(12548,-1) = 130

       Round(124.78,-2) = 100  //ปัดเศษหลักร้อย

       Round(151.25,-2) = 200
มาดูการปัดตัวเลขให้เข้าใกล้จำนวนเต็ม สูตร Excel : Round(ตัวเลขที่ต้องการปัดเศษ,0) ตรงนี้ท่านผู้อ่านก็นำไปใช้งานได้ในกรณีต้องการปัดเป็นจำนวนเต็มเพื่อคิดคำนวณง่ายๆเป็นต้น
เช่น Round(124.78,0) = 125
       Round(124.38,0) = 124

หากเรามีตัวเลขค่าเกินกว่าหลักล้านและต้องการปัดตัวเลขในหน่วยล้าน เช้น ล้านบาท ล้านคน ในทศนิยม 2 ตำแหน่ง เราจะประยุกต์ใช้สูตร Excel ดังนี้
เช่น มีเงิน 123,545,234.25 บาท ต้องการปัดตัวเงินเป็นหลักล้านบาทและทศนิยม 2 ตำแหน่งเป็น 123.55 ล้านบาท เราจะใช้สูตร excel ดังนี้
Round(123545234.25/1e6,2) = 123.55 ล้านบาท

การใช้ 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 อย่างไร สวัสดีครับ