วันอาทิตย์ที่ 24 ตุลาคม พ.ศ. 2553

สูตร excel : การตั้งราคาขาย

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

สมมุติว่ามีสินค้าที่ต้องการกำหนดราคาขายดังแสดงในภาพที่ 1

การตั้งราคาขาย

ภาพที่ 1 ภาพตัวอย่างข้อมูลสินค้าที่ต้องการตั้งราคาขาย

การคำนวณราคาขายคำนวณได้จากสูตร

ราคาขาย = ต้นทุน + กำไร

= ต้นทุน + ต้นทุน*%กำไร

= ต้นทุน (1 + %กำไร)

ดังนั้นในเซลล์ E3 เราสามารถพิมพ์สูตร excel ได้ดังนี้ = C3*(1+D3) ในกรณีที่ต้องการปัดเศษลง เราจะคำนวณลงในคอลัมภ์ F ขณะที่หากต้องการปัดเศษขี้น เราจะคำนวณลงในคอมลัมภ์ G โดยใช้ สูตร excel : FLOOR และ CEILING ตามลำดับ โดยพิมพ์สูตรในเซลล์ F3 ได้ดังนี้

= FLOOR(E3,5) ในขณะที่ G3 สามารถพิมพ์สูตร excel ได้ดังนี้ =CEILING(E3,5)

ในขณะที่ในแถวถัดไปสามารถใช้ AutoFill จะได้ผลการตั้งราคาขายได้ดังภาพที่ 2

การตั้งราคาขาย1

ภาพที่ 2 ผลการใช้สูตร excel ตั้งราคาขาย

ท่านผู้อ่านก็จะได้ราคาขายซึ่งอยู่ในรูปแบบที่ต้องการแล้วครับ

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

สูตรการคิด%กำไร

%กำไร = (ราคาขายหลังหักส่วนลด - ราคาต้นทุน)/ราคาต้นทุน

การคิดราคาขายหลังหักส่วนลด

ราคาขายหลังหักส่วนลด = (1 - %ส่วนลด)*ราคาขาย

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

%กำไร = ((1 - %ส่วนลด)*ราคาขาย - ราคาต้นทุน)/ราคาต้นทุน

เมื่อจัดรูปแบบสมการใหม่จะได้สมการการตั้งราคาขายดังนี้

ราคาขาย = (1+%กำไร)*ราคาต้นทุน/(1-%ส่วนลด)

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

การตั้งราคาขาย2

ภาพที่ 3 การตั้งราคาแบบมีส่วนลด

ดังนั้นหากต้องการคิดราคาขายก็สามารถพิมพ์สูตร excel ลงในคอลัมภ์ F ได้ดังนี้ = (1+D3)*C3/(1-E3)

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

การตั้งราคาขาย3

ภาพที่ 4 ราคาขายหลังหักส่วนลดและคิดกำไรที่ต้องการ

หากท่านผู้อ่านต้องการตั้งราคาขายให้ลงท้ายด้วยเลข 0 หรือ 5 ก็สามารถใช้สูตร excel ดังที่ได้นำเสนอไปในตอนต้นครับ

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

จัดทำบัญชีรายรับรายจ่ายด้วย excel : Pivot Table

การจัดทำบัญชีรายรับรายจ่าย ด้วย Pivot Table เป็นการปรับปรุงการจัดทำบัญชีรายรับรายจ่ายแบบอย่างง่ายที่ได้นำเสนอไปในบทความที่ผ่านมา ในบทความนี้ผมขอยกตัวอย่างการจัดทำบัญชีรายรับรายจ่ายประเภทเงินสด โดยการจัดการด้วย Pivot Table จะทำการจัดเก็บข้อมูลในแต่ละแถวเป็นแบบฐานข้อมูล โดยแยกรายการใช้จ่ายออกเป็นประเภทของรายรับรายจ่ายและเก็บไว้ในหลักๆหนึ่ง โดยตั้งชื่อหัวข้อไว้เป็น ประเภท ดังแสดงในภาพตัวอย่างที่ 1
บัญชีรายรับรายจ่าย-Pivot-Table ภาพที่ 1 ตัวอย่างบัญชีรายรับรายจ่ายประเภทเงินสด
ท่านผู้อ่านอาจจะกำหนดให้ข้อมูลในเซลล์ D3 ให้ผู้ใช้เลือกรายการว่าจะเป็นรายรับหรือรายจ่าย ซึ่งlสามารถทำได้โดยใช้ excel ฟังก์ชัน Data Validation
จากข้อมูลที่เราทำการบันทึกลงบัญชีรายรับรายจ่ายประเภทเงินสดในแต่ละวัน เราสามารถนำข้อมูลมากำหนดเป็น Pivot Table ได้ตามขั้นตอนดังนี้
  1. คลิกเซลล์ B3
  2. เลือกคำสั่งในเมนู ข้อมูล->รายงาน PivotTable และPivotChart…
  3. จะปรากฎหน้าต่างตัวช่วยสร้าง PivotTable และPivotChart ให้คลิกปุ่ม ถัดไป สองครั้งเพื่อยอมรับค่าตั้งต้นที่ได้จาก excel
  4. ในขั้นตอนที่ 3 ของตัวช่วยสร้างให้คลิก ปุ่ม เค้าโครง… เพื่อกำหนดเค้าโครงของรายงาน
  5. ลากหัวข้อ ประเภทไปวางไว้ในตำแหน่ง คอมลัมภ์ (Column)
  6. ลากหัวข้อ วันที่ และ รายการ ไปวางไว้ในตำแหน่ง แถว (Row)
  7. ลากหัวข้อ จำนวนเงินไปวางไว้ในตำแหน้ง ข้อมูล (Data) จะได้ผลดังแสดงในภาพที่ 2
  8. กดปุ่ม ตกลง จะกลับมายังหน้าต่างของตัวช่วยสร้างให้คลิกปุ่ม ตัวเลือก… จะปรากฎหน้าต่าง ตัวเลือก PivotTable
  9. ให้ยกเลิก หัวข้อ ผลรวมทั้งหมดสำหรับแถว
  10. คลิกปุ่ม ตกลง และคลิกปุ่ม เสร็จสิ้น excel จะสร้าง worksheet ใหม่เพื่อแสดงผลการทำ Pivot Table ดังแสดงในภาพที่ 3
PivotTableWizard-1
ภาพที่ 2 หน้าต่างตัวเลือก PivotTable
PivotTable
ภาพที่ 3 ผลการทำ Pivot Table กับบัญชีรายรับรายจ่าย
หากต้องการทราบว่าในแต่ละวันมีเงินคงเหลือเท่าไหร่ก็ตั้งสูตรในหลัก E ได้ดังที่เคยนำเสนอในบทความการจัดทำบัญชีรายรับรายจ่ายประเภทเงินสด
ท่านผู้อ่านจะเห็นว่าการทำบัญชีรายรับรายจ่ายโดยใช้ Pivot Table ก็มีความยืดหยุ่นและสะดวก โดยผู้จัดทำเพียงบันทึกข้อมูลลงในบัญชีรายรับรายจ่ายดังภาพที่ 1 ในแต่ละวัน เมื่อต้องการวิเคราะห์สภาพคล่องของเงินสดก็เพียงใช้ Pivot Table ช่วยดังที่นำเสนอมา ก็หวังว่าท่านผู้อ่านจะประยุกต์ใช้ในการจัดทำบัญชีรายรับรายจ่ายประเภทที่เหลือได้ต่อไปครับ สวัสดีครับ

วันเสาร์ที่ 23 ตุลาคม พ.ศ. 2553

การใช้ excel : การจัดทำงบดุล

การใช้ excel ในบทความนี้จะนำเสนอ การจัดทำงบดุล ซึ่งเปรียบได้กับการเอกซเรย์สุขภาพการเงินของเราๆท่านๆกันครับ ในบทความสองสามบทความที่ผ่านมาผมได้นำเสนอการจัดทำบัญชีรายรับรายจ่ายประเภทต่างๆเพื่อให้ทราบสภาพคล่องทางการเงินของเรา หากเปรียบไปแล้วบัญชีรายรับรายจ่ายดังกล่าวก็เสมือนอวัยวะภายในของร่างกายเรา เช่น ตับ กระเพาะ หัวใจ ฯลฯ โดยการเงินที่ไหลผ่านไปมาเพื่อดำเนินการต่างๆเปรียบไปก็เหมือนกับเลือดที่ไหลไปเลี้ยงร่างกายของเรานั่นเอง ดังนั้นการจัดงบดุลจะทำให้เราทราบสถานะทางการเงินของเราในภาพใหญ่ได้เป็นอย่างดี โดยงบดุลจะแสดงปริมาณเงินที่สะสมอยู่ในบัญชีรายรับรายจ่ายต่างๆ ณ วันใดวันหนึ่ง ซึ่งการจัดทำงบดุลของตัวเราเองก็สามารถทำได้โดยง่ายหากเราได้ดำเนินการจัดทำบัญชีรายรับรายจ่ายแต่ละประเภทมาแล้วยิ่งสามารถจัดทำงบดุลได้โดยไม่ยากมากนัก เรามาดูตัวอย่าง งบดุล ที่ถูกจัดทำไว้โดยใช้ excel ดังแสดงในภาพที่ 1
งบดุล
ภาพที่ 1 งบดุล
จากตัวอย่างงบดุลในภาพที่ 1 เราจะเห็นว่า ตารางงบดุลจะแบ่งออกเป็น 2 ส่วนคือ ส่วนด้านซ้ายเป็นส่วนของทรัพย์สิน ส่วนด้านขวาจะประกอบด้วยส่วนหนี้สินและส่วนที่เป็นของเรา ซึ่งผลรวมของตัวเลขด้านขวา(รวมหนี้สิน + ส่วนของเรา)ต้องเท่ากับผลรวมของตัวเลขด้านซ้าย อธิบายได้ง่ายๆว่างบดุลจะช่วยบอกเราได้ว่า ตัวเรามีสินทรัพย์อะไรบ้าง และตีเป็นจำนวนเงินได้เท่าไหร่ (ด้านซ้าย) และยังสามารถบอกได้อีกว่า ทรพย์สินที่เรามีอยู่ได้รับการสนับสนุนทางการเงินจากเราเอง(ส่วนของเรา)เท่าไหร่ และกู้เจ้าหนี้มาเท่าไหร่ (รวมหนี้สิน) จากการวิเคราะห์งบดุล เราจะเห็นว่าทรัพย์สินของเราตามงบดุลอาจไม่ได้เป็นของเราจริงๆ 100% (ยกตัวอย่างเช่น รถยนต์ บ้าน ที่ดิน)แต่จะเป็นของเราเท่าใดนั้นก็ขึ้นอยู่กับว่ามีส่วนของเราคิดเป็นกี่เปอร์เซนต์นั่นเอง ดังนั้นหากจะวัดสุขภาพการเงินเราควรจะวัดส่วนที่เป็นของเรามากว่า จากตารางงบดุล ท่านจะเห็นว่ามีข้อมูลบางรายการในส่วนของสินทรัพย์และหนี้สินที่อ้างอิงได้จากบัญชีรายรับรายจ่าย คือข้อมูลเงินสดในเซลล์ B6 เราจะอ้างถึงบัญชีรายรับรายจ่ายประเภทเงินสด ข้อมูลเงินฝากออมทรัพย์ในเซลล์ B7 เราจะอ้างถึงบัญชีรายรับรายจ่ายประเภทบัญชีเงินฝากออมทรัพย์ ข้อมูลบัตรเครดิตในเซลล์ D6 เราจะอ้างถึงบัญชีรายรับรายจ่ายประเภทบัตรเครดิต
ท่านผู้อ่านจะเห็นว่าจากที่ผมได้นำเสนอบทความเกี่ยวกับบัญชีรายรับรายจ่ายจนถึงงบดุลในบทความนี้ หวังว่าท่านผู้อ่านจะนำไปประยุกต์การบริหารจัดการด้านการเงินให้เกิดประโยชน์สูงสุดครับ บทความต่อไปผมจะนำเสนอการจัดทำบัญชีรายรับรายจ่ายในรูปแบบที่ซับซ้อนขึ้นแต่จะช่วยให้ท่านวิเคราะห์พฤติกรรมการใช้เงินในรูปแบบต่างๆได้ชัดเจนมากขึ้น เพราะท้ายที่สุดจุดมุงหมายของเราก็คือการเพิ่ม ส่วนของเราใน งบดุล และการเพิ่มส่วนของทรัพย์สินในงบดุล นั่นเอง สวัสดีครับ

การใช้ excel : จัดทำบัญชีรายรับรายจ่าย (บัญชีเงินฝากออมทรัพย์)

การใช้ excel ในบทความนี้ผมจะนำท่านผู้อ่านจัดทำบัญชีรายรับรายจ่ายประเภทสุดท้าย นั่นคือ บัญชีรายรับรายจ่ายประเภทบัญชีเงินฝากออมทรัพย์ในธนาคาร คงไม่มีใครปฎิเสธว่าปัจจุบันนี้การฝากเงินในบัญชีสะสมทรัพย์ในธนาคารเป็นการบริหารสภาพคล่องได้ดีอีกวิธีหนึ่ง เพราะเราสามารถฝากถอนได้อย่างสะดวกนั่นเอง ทำให้การฝากเงินออมทรัพยืไว้กัยธนาคารจึงมีสภาคล่องใกล้เคียงกับเงินสดในมือมาก และในปัจจุบันมนุษย์เงินเดือนอย่างเราๆท่านๆโดยส่วนใหญ่จะได้รับเงินเดือนผ่านทางธนาคาร ดังนั้นรายรับก้อนใหญ่ในแต่ละเดือนของพวกเราจึงอยู่ในรูปแบบของบัญชีเงินฝากสะสมทรัพย์โดยปริยาย ดังนั้นในทำนองเดียวกันกับบัญชีรายรับรายจ่ายประเภทอื่นๆ เราสามารถจัดทำบัญชีรายรับรายจ่ายประเภทเงินฝากออมทรัพย์ได้โดยกำหนดรายการได้ดังนี้
รายรับ หมายถึง เงินที่ถูกฝากเข้าบัญชีเงินฝาก , ดอกเบี้ย เป็นต้น
รายจ่าย หมายถึง เงินที่ถูกถอนออกไปจากบัญชี , ค่าธรรมเนียมการถอน เป็นต้น
ในการจัดทำบัญชีรายรับรายจ่ายประเภทเงินฝากออมทรัพย์ไม่ได้ถูกจำกัดด้วยจำนวนบัญชีเงินฝาก ดังแสดงในภาพที่ 1
บัญชีรายรับรายจ่ายเงินฝากออมทรัพย์
ภาพที่ 1 บัญชีรายรับรายจ่ายประเภทบัญชีเงินฝากออมทรัพย์
ในการวิเคราะห์บัญชีรายรับรายจ่ายประเภทเงินฝากออมทรัพย์ก็เป็นไปในทำนองเดียวกันกับ บัญชีรายรับรายจ่ายประเภทอื่น นั่นคือต้องเพิ่มรายการจำนวนสุทธิเข้ามาเพื่อตรวจสอบสถานะสภาพคล่อง โดยการใช้สูตร excel ดังนี้
  1. ในเซลล์ F4 ให้พิมพ์สูตร excel ดังนี้ = D4-E4
  2. ในเซลล์ F5 ให้พิมพ์สูตร excel ดังนี้ = F4 + D5 – E5
  3. ทำการ AutoFill จนถึงแถวที่ 9 จะได้ผลลัพธ์ดังภาพที่ 2
  4. ในกรณีต้องการทราบผลรวมของรายรับและรายจ่ายสามารถทำได้โดยการใช้สูตร excel : AutoSum ในหลัก D และ หลัก E
บัญชีรายรับรายจ่ายเงินฝากออมทรัพย์2
ภาพที่ 2 บัญชีรายรับรายจ่ายประเภทบัญชีเงินฝากออมทรัพย์แบบสมบูรณ์
จากบทความที่ผ่านมาจนถึงบทความนี้ผมได้นำเสนอการจัดทำบัญชีรายรับรายจ่ายทั้งหมด 3 ประเภทดังนี้
  1. บัญชีรายรับรายจ่ายประเภทเงินสด
  2. บัญชีรายรับรายจ่ายประเภทบัตรเครดิต
  3. บัญชีรายรับรายจ่ายประเภทบัญชีเงินฝากออมทรัพย์
ซึ่งแสดงถึงเงินสำรองสภาพคล่องของแต่ละบุคคลหรือแต่ละครอบครัวใน 3 รูปแบบไปแล้ว เรามารู้จักสภาพคล่องสุทธิ(Net Liquidity Reserved) กันต่อเลยครับ ณ เวลาใดเวลาหนึ่ง หากเราอยากทราบว่าเรามีสภาพคล่องสุทธิอยู่เท่าไหร่ เราสามารถทำได้โดยการนำจำนวนสุทธิของบัญชีรายรับรายจ่ายทั้งสามรูปแบบมารวมกัน โดยผลรวมของเงินทั้งสามรูปแบบจะถูกเรียกว่า สภาพคล่องสุทธิ (Net PLR) ณ วันนั้นๆ การหาสภาพคล่องสุทธิโดยใช้ excel ช่วยจัดการทำได้ง่ายมาก ดังแสดงในภาพที่ 3
สภาพคล่องสุทธิ ภาพที่ 3 สภาพคล่องสุทธิ
โดยจำนวนสุทธิในแต่ละประเภทของสภาพคล่อง ผู้อ่านสามารถเชื่อมโยงจาก WorkSheets ต่างๆซึ่งเราได้ทำการแยกบัญชีรายรับรายจ่ายแต่ละประเภทไว้แล้ว ซึ่งจะทำให้สภาพคล่องสุทธิมีการปรับปรุงผลคำนวณโดยอัตโนมัติหากมีการบันทึกค่าใน WorkSheet ของบัญชีรายรับรายจ่ายแต่ละประเภท
ก่อนจบบทความนี้ขอฝากคำถามให้ท่านผู้อ่านไว้ซักหนึ่งคำถามนะครับว่า สภาพคล่องสุทธิของเราควรจะมีค่าเป็นเท่าใด และสภาพคล่องสุทธิที่มากเกินไปเป็นผลดีหรือไม่ ในทางกลับกัน สภาพคล่องสุทธิที่น้อยมากหรือติดลบ เราควรบริหารจัดการอย่างไร คำตอบมีอยู่ในนิยามของการจัดทำบัญชีรายรับรายจ่ายแต่ละประเภทแล้วหล่ะครับ สวัสดีครับ

การใช้งาน excel : จัดทำบัญชีรายรับรายจ่าย (บัญชีบัตรเครดิต)

การรใช้งาน excel ในบทความนี้ผมขอนำเสนอบัญชีรายรับรายจ่ายในรูปแบบอื่น ในบทความที่ผ่านมาผมได้นำเสนอการจัดทำบัญชีรายรับรายจ่ายในรูปของเงินสดไปแล้ว แต่ในปัจจุบันทุกท่านคงปฏิเสธไม่ได้ว่าการใช้จ่ายของเราทุกวันนี้ยังมีการใช้จ่ายหรือรายรับในรูปแบบอื่นอีก ขอแบ่งเป็นรูปแบบของการใช้จ่ายออกเป็น 3 รูปแบบดังนี้ครับ
  1. เงินสด (Cash flow)
  2. บัตรเครดิต
  3. บัญชีเงินฝากในธนาคาร
บัตรเครดิต มีทั้งประโยชน์และโทษอยู่ในตัวเอง หากรู้จักวิธีใช้ให้เกิดประโยชน์ก็ดีไป ในที่นี้ขอนิยามบัตรเครดิตไว้ดังนี้ครับ บัตรเครดิตหมายถึง วงเงินพร้อมใช้ไว้เสริมสภาพคล่อง หรือหากจะหมายถึง หนี้พร้อมก่อที่ต้องชำระคืนทุกเดือน ก็มองได้แล้วแต่ว่าจะมองในมุมไหน แต่ในการบริหารการใช้เงินเราจะนิยามบัตรเครดิตเป็นหนี้พร้อมก่อที่ต้องชำระทุกเดือน ดังนั้นการจัดทำบัญชีรายรับรายจ่าย เราจึงกำหนดช่องในการบันทึกรายการไว้ให้สอดคล้องกับการนิยามดังนี้
รายรับหมายถึง จำนวนเงินที่เราได้ชำระคืน
รายจ่ายหมายถึง จำนวนเงินที่เราใช้จ่ายผ่านบัตรเครดิต
จำนวนสุทธิหมายถึง รายรับ - รายจ่าย
ท่านผู้อ่านจะพบว่าในบัญชีรายรับรายจ่ายประเภทบัตรเครดิต เราคงไม่ต้องการจำนวนสุทธิที่ติดลบมากเกินไปหรือหากมีค่าเท่ากับศูนย์ได้ยิ่งเป็นสิ่งที่ดีเพราะนั่นหมายถึงการที่เราไม่ติดหนี้บัตรเครดิตอยู่เลย การจัดทำบัญชีรายรับรายจ่าย โดยการใช้ excel มีรูปแบบดังแสดงในภาพที่ 1
บัญชีรายรับรายจ่ายบัตรเครดิต
ภาพที่ 1 บัญชีรายรับรายจ่ายประเภทบัตรเครดิต
เพื่อให้เราทราบสถานะและบริหารการใช้จ่ายผ่านบัตรเครดิต จึงเพิ่มรายการจำนวนสุทธิในหลัก F โดยกำหนดสูตร excel ดังนี้
  1. ในเซลล์ F4 พิมพ์สูตร excel ดังนี้ = D4 – E4
  2. ในเซลล์ F5 พิมพ์สูตร excel ดังนี้ = F4 + D5 – E5
  3. ทำการ AutoFill จนถึงแถวที่ 9 จะได้จำนวนสุทธิดังแสดงในภาพที่ 2
  4. กรณีที่ต้องการทราบผลรวมของรายรับหรือรายจ่ายเราก็สามารถใช้สูตร excel : AutoSum
บัญชีรายรับรายจ่ายบัตรเครดิต2 ภาพที่ 2 บัญชีรายรับรายจ่ายประเภทบัตรเครดิตแบบสมบูรณ์
ท่านผู้อ่านคงจะเห็นแล้วว่า บัญชีรายรับรายจ่ายประเภทบัตรเครดิตจะช่วยให้เราทราบถึงสถานะทางการเงินของเราได้ดียิ่งขึ้น ยิ่งตัวเลขในรายการจำนวนสุทธิมีค่าติดลบมากขึ้นแสดงให้เห็นถึงหนี้ที่เราต้องเร่งดำเนินการชำระในเดือนถัดไปมากขึ้น ซึ่งทุกท่านคงทราบดี หนี้คงค้างในการชำระบัตรเครดิตจะโดนชาร์จดอกเบี้ยถึงร้อยละ 20 ต่อปี ดังนั้นการตรวจสอบบัญชีรายรับรายจ่ายประเภทบัตรเครดิตก็เป็นสิ่งที่เราต้องใส่ใจ
ครับบทความนี้ก็นำเสนอการใช้ excel จัดทำบัญชีรายรับรายจ่ายประเภทบัตรเครดิตไปแล้วนะครับ บทความต่อไปผมจะมาแนะนำการจัดทำบัญชีรายรับรายจ่ายประเภทบัญชีเงินฝากกันครับ สวัสดีครับ
ปล หนี้บัตรเครดิตต่างกับเงินกู้อย่างไร

การใช้ excel จัดทำบัญชีรายรับรายจ่าย(บัญชีเงินสด)

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

บัญชีรายรับรายจ่าย
ภาพที่ 1 ตัวอย่างบัญชีรายรับรายจ่าย
วัตถุประสงค์ในการจัดทำบัญชีรายรับรายจ่าย ก็เพื่อให้เราทราบสถานะทางการเงินหรือ Cash flow ของเราเอง ดังนั้นในการบันทึกรายการลงบัญชีรายรับรายจ่าย เจ้าของบัญชีต้องทราบจำนวนเงินสุทธิในบัญชี ซึ่งผู้ใช้งาน excel สามารถทำได้โดยง่ายดังนี้
  1. คลิกเซลล์ F4 พิมพ์สูตร excel ดังนี้ = D4 – E4
  2. คลิกเซลล์ F5 พิมพ์สูตร excel ดังนี้ = F4 + D5 – E5
  3. ทำการ AutoFill ลงมาถึงแถวที่ 11 จะได้ผลดังแสดงในภาพที่ 2
  4. ในกรณีที่ผู้จัดทำบัญชีรายรับรายจ่าย ต้องการทราบรายจ่ายสุทธิและรายรับสุทธิตั้งแต่วันที่ 1 ของเดือน จนถึง ปัจจุบัน ก็สามารถทำได้โดยการใช้สูตร excel : AutoSum ก็จะได้ผลดังแสดงในภาพที่ 2
บัญชีรายรับรายจ่าย2
ภาพที่ 2 บัญชีรายรับรายจ่ายแบบสมบูรณ์

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

การใช้งาน excel : จัดทำบัญชีรายรับรายจ่าย (บัญชีบัตรเครดิต)

การใช้ excel : จัดทำบัญชีรายรับรายจ่าย (บัญชีเงินฝากออมทรัพย์)

การใช้ excel : การจัดทำงบดุล

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

วันศุกร์ที่ 22 ตุลาคม พ.ศ. 2553

สูตร excel : การดำเนินการระหว่างตัวเลขและTime data

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

h.m ซึ่งผมว่ามันเข้าใจในภาษามนุษย์เราใช่มั้ยครับเช่น 4.53 ก็หมายถึง 4 ชั่วโมง 53 นาที เอาหล่ะครับหากเราต้องการแปลงตัวเลขนี้ให้เป็นข้อมูลประเภท Time จะทำอย่างไร เรามาดูกันขั้นตอนกันเลยครับ

สูตร excel

  1. ใช้สูตร excel : Floor เพื่อปัดเป็นจำนวนเต็มจะได้เป็น =FLOOR(4.53,1) ผลที่ได้คือจำนวนชั่วโมงเท่ากับ 4
  2. นำผลที่ได้จากการใช้สูตร excel : Floor ลบออกจากตัวเลขตั้งต้นและคูณด้วย 100 จะได้เศษนาทีออกมาครับ
สูตร excel

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

เขียน excel vba

  1. ใช้ฟังก์ชัน Int เพื่อแปลงตัวเลขเป็นจำนวนเต็ม เขียนได้ดังนี้ h = Int(4.53) ผลที่ได้คือ 4
  2. นำผลที่ได้จากการใช้ฟังก์ชัน Int ลบออกจากตัวเลขตั้งต้นและคูณด้วย 100 จะได้เศษนาทีออกมาครับ

จะเห็นว่าหากเราต้องการแปลงตัวเลขเหล่านี้เป็นจำนวนนาทีก็ไม่ยากใช่มั้ยครับสามารถคำนวณได้ดังนี้

time_minute = h*60 + m

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

Function NumToMinute(ByVal num As Double) As Integer
Dim h As Integer
h = Int(num)
NumToMinute = h * 60 + (num - h) * 100
End Function

ผลการเรียกใช้ฟังก์ชัน NumToMinute แสดงได้ดังภาพที่ 2

Excel Function ภาพที่ 2 การเรียกใช้สูตร excel : NumToMinute

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

ผลรวมของเวลา

Function AddTime(tm1 As Integer, tm2 As Integer) As Integer
AddTime = tm1 + tm2
End Function

ผลต่างของเวลา

Function MinusTime(tm1 As Integer, tm2 As Integer) As Integer
If tm1 >= tm2 Then
MinusTime = tm1 - tm2
Else
MinusTime = tm2 - tm1
End If
End Function

ผลการใช้สูตร Excel ทั้งสองแสดงได้ดังภาพที่ 3

Excel Function1

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

ในบางกรณีเราต้องการแยกองค์ประกอบของจำนวนนาทีให้อยู่ในรูปแบบของจำนวนชั่วโมงและจำนวนนาที (h.m) เราสามารถเขียนฟังก์ชันได้ดังนี้

Function MinuteToNum(ByVal tm As Integer) As Double
Dim h As Integer
Dim m As Integer
m = tm Mod 60
h = Int(tm / 60)
MinuteToNum = h + m / 100#
End Function

ดังนั้นหลังจากดำเนินการกับเวลาเสร็จแล้วเราสามารถแปลงจำนวนเวลาดังกล่าวกลับมาอยู่ในรูปแบบที่มนุษย์เข้าใจได้โดยง่ายโดยใช้สูตร Excel : MinuteToNum

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

วันอาทิตย์ที่ 17 ตุลาคม พ.ศ. 2553

แบบฟอร์ม kpi (kpi template )

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

วันอาทิตย์ที่ 10 ตุลาคม พ.ศ. 2553

การใช้ Excel : Import Text File ลง excel

การใช้ Excel ในบทความนี้ขอนำเสนอการ Import Text File ลง excel ครับ ข้อมูลบางอย่างที่ต้องการคำนวณอาจถูกสร้างในรูปแบบของ Text File เช่น ข้อมูลจากเครื่องรูดบัตร หรือข้อมูลจากเครื่องอ่านบาร์โค้ด เป็นต้น Text File เหล่านี้จะมีรูปแบบการจัดเก็บในลักษณะของฐานข้อมูลอยู่แล้ว บทความนี้ผมขอยกตัวอย่าง Text File ข้อมูลเครื่องรูดบัตรเข้าออกยี่ห้อ TAFF ครับซึ่งมีรูปแบบการเก็บข้อมูลในแต่ละแถวดังนี้

รหัสพนักงาน สถานะการรูด วันเดือนปี เวลารูดบัตร ตัวเลขตรวจสอบ

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

Text File

ภาพที่ 1 ตัวอย่างข้อมูลใน Text File

เริ่มต้นให้ไปที่ แฟ้ม->เปิด จะปรากฏหน้าต่างเปิดไฟล์ ให้ท่านไปยังโฟล์เดอร์ที่เก็บ Text File และให้เลือกชนิดแฟ้มในหน้าต่างเปิดไฟล์ เป็น All Files และให้เลือก Text File ที่ต้องการ Import เข้า Excel จากนั้นให้คลิก เปิด จะปรากฏหน้าต่างตัวช่วยสร้างการนำเข้าข้อความดังแสดงในภาพที่ 2 ในกรอบของชนิดข้อมูลดั้งเดิมให้เลือก มีการใช้ตัวคั่น กำหนดให้เริ่มนำเข้าในแถวที่ 1 ให้คลิกปุ่มถัดไป

Text File Wizard1

ภาพที่ 2 ตัวช่วยสร้างการ Import Text File Step 1

เมื่อคลิกปุ่มถัดไปจะปรากฏหน้าต่างตัวช่วยสร้างการ Import Text File Step 2 ดังแสดงในภาพที่ 3 ให้เลือกตัวคั่นเป็นแท็บและช่องว่าง (เนื่องจากข้อมูลใน Text File ที่กำลัง Import แยกข้อมูลในแต่ละหลักด้วยช่องว่างนั่นเอง) จากนั้นให้คลิกปุ่ม ถัดไป

Text File Wizard2

ภาพที่ 3 ตัวช่วยสร้างการ Import Text File Step 2

จะปรากฏหน้าต่างตัวช่วยสร้างการ Import Text File Step 3 ดังแสดงในภาพที่ 4 ในส่วนของหลักวันเดือนปี ให้เลือก รูปแบบข้อมูลคอมลัมน์ เป็นแบบวันที่และเลือกรูปแบบเป็น ปดว (ปีเดือนวัน) จากนั้นให้คลิกปุ่ม เสร็จสิ้น ถือเป็นการสิ้นสุดการ Import Text File ของการรูดบัตรลงใน Excel แล้วครับ ข้อมูลที่ถูกนำเข้าแสดงได้ดังภาพที่ 5

Text File Wizard3

ภาพที่ 4 ตัวช่วยสร้างการ Import Text File Step 3

Excel Data

ภาพที่ 5 ข้อมูลเวลาที่ถูก Import เข้า Excel

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

ครับมาถึงตรงนี้ท่านผู้อ่านคงเข้าใจการ Import Text File แล้วนะครับ คำถามคือหากต้องการสรุปการทำงานประจำเดือน (30 ไฟล์) พนักงานมี 500 ท่าน การเลือกเปิดไฟล์แบบที่นำเสนอไปคงไม่เหมาะ เพราะเป็นการทำซ้ำๆกัน การเขียน vba excel จะช่วยท่านได้ เดี๋ยวจะนำเสนอในโอกาสต่อไปนะครับ สวัสดีครับ

วันเสาร์ที่ 2 ตุลาคม พ.ศ. 2553

การใช้งาน excel : การจัดการ Text Value

Text Value เป็นรูปแบบที่ท่านผู้อ่าน how to excel ต่างประสบปัญหากันมาบ้างไม่มากก็น้อย ตัวผมเองก็ประสบมาด้วยตัวเองก็พอสมควรดังได้เคยนำมาเล่าในหัวข้อการค้นหาข้อมูลด้วย VLOOKUP ไปแล้วครับ วันนี้ก็เลยมีความตั้งใจที่จะเสนอแนะลักษณะงานที่เกี่ยวกับ Text value ในการใช้งาน excel เนื่องด้วยบทความนี้เป็นบทความที่ 50 พอดี ย้อนกลับไปก็ให้ฉงนเหมือนกันว่าผมเขียนไปบทความไปเกือบครึ่งร้อยซะแล้ว วันนี้เลยขอนำเสนอบทความที่สบายๆเป็นการผ่อนคลายไปในตัวหล่ะกันนะครับ

วันนี้ผมอยากนำเสนอฟังก์ชัน excel ที่ใช้ในการจัดการข้อมูลประเภท Text Value ซัก 3 ฟังก์ชัน เนื่องด้วยอาทิตย์ที่ผ่านมามีโอกาสได้ใช้จัดการวางแผนการผลิต เรามาเริ่มสูตร Excel สูตรแรกกันเลยครับ

  1. UPPER / LOWER เป็น excel function ที่ใช้ในการแปลงอักษรทั้งหมดในข้อความให้เป็นตัวพิมพ์ใหญ่(UPPER) หรือเป็นตัวพิมพ์เล็ก(LOWER) ท่านผู้อ่านใช้ excel function ในด้านใดบ้างครับ ผมเองจะใช้ในการตรวจสอบเงื่อนไขครับ เพื่อป้องกันผู้ใช้พิมพ์ตัวพิมพ์เล็กหรือพิมพ์ใหญ่ทำให้ง่ายต่อการตรวจสอบครับ โดยเรานำข้อความมาทำการแปลงก่อนนำไปเปรียบเทียบเงื่อนไขครับ ตัวอย่างเช่น หากตัวอักษร 2 ตัวแรกของข้อความที่นำเข้ามาเป็นตัวอักษร “EZ” ให้พิมพ์คำว่า yes หากมิใช่ให้พิมพ์ no ท่านผู้อ่านจะเห็นว่าความเสี่ยงที่ผู้ใช้จะพิมพ์ตัวอักษร 2 ตัวแรกในรูปแบบที่แตกต่างกันมีความเป็นไปได้ เช่น Ez , ez , eZ , EZ ดังนั้นเพื่อให้การตรวจสอบความถูกต้องสามารถทำได้ง่าย เราอาจเขียนเป็นสูตร excel ได้ดังนี้ B2 =if(Left(UPPER(A2),2) = “EZ” ,”EZ” , “NO”) จากตัวอย่างที่ผมได้นำเสนอสามารถแสดงได้ดังภาพที่ 1

Upper

ภาพที่ 1 ตัวอย่างการใช้งาน excel function ในการตรวจสอบเงื่อนไข

excel function : UPPER / LOWER ในการเขียน VBA excel จะใช้ฟังก์ชัน UCase / LCase

ท่านผู้อ่านจะเห็นว่าผมใช้ฟังก์ชัน Left เพื่อตัดเอาตัวอักษร 2 ตัวแรกด้านซ้ายมาแปลงเป็นตัวพิมพ์ใหญ่และตรวจสอบว่าเป็นตัวอักษร “EZ” หากใช่ก็กำหนดให้เท่ากับ EZ ในหลัก B หากไม่ใช่จะกำหนดให้เท่ากับ NO ในหลัก B

2. การประยุกต์ฟังก์ชัน Find และฟังก์ชัน Left Mid ในการแยกชื่อและนามสกุลออกจากกัน ในบางครั้งเราอาจมีความประสงค์ที่จะแยกชื่อและนามสกุลออกจากันเพื่อนำไปใช้งานอื่นๆ มาดูแนวคิดการแยกชื่อและนามสกุลกันเลยครับ โดยปกติชื่อและนามสกุลจะถูกคั่นด้วยช่องว่างดังนั้นขั้นตอนแรกต้องเราต้องหาตำแหน่งของช่องว่างในชื่อ-นามสกุล จากนั้นใช้ฟังก์ชั่น Left เพื่อแยกชื่อมาแสดง จากนั้นเราจะใช้ฟังก์ชัน Mid ในการแยกนามสกุล ดังแสดงเป็นสูตร Excel ในภาพที่ 2

find-left-mid

ภาพที่ 2 การแยกชื่อนามสกุลออกจากกัน

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

วันศุกร์ที่ 1 ตุลาคม พ.ศ. 2553

การใช้งาน Excel : ประยุกต์ VLOOKUP คำนวณค่าคอมมิชชั่นแบบขั้นบันได

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

  1. ยอดขายส่วน 0 – 5,000 บาทแรก จะคิดค่าคอมมิชชั่นให้ 1.5 %
  2. ยอดขายส่วน 5,000 – 10,000 บาท ต่อไปคิดค่าคอมมิชชั่นให้ 2.5 %
  3. ยอดขายส่วน 10,000 – 20,000 ต่อไปคิดค่าคอมมิชชั่นให้ 3.5 %
  4. ยอดขายส่วน 20,000 – 50,000 ต่อไปคิดค่าคอมมิชชั่นให้ 5.0 %
  5. ยอดขายส่วนที่ เกิน 50,000 คิดค่าคอมมิชชั่นให้ 7.5 %

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

ตารางที่ 1 ค่าคอมมิชชั่นสูงสุดในแต่ละขั้น

ช่วงของยอดขาย คอมมิชชั่นสูงสุดในช่วง เปอร์เซนต์คอมมิชชั่น
0 – 5,000 = 5000*0.015 1.5 %
5,000 – 10,000 = 5,000*0.025 2.5 %
10,000 – 20,000 =10,000*0.035 3.5 %
20,000 – 50,000 =30,000*0.050 5.0 %
50,000 up = ส่วนเกิน*0.075 7.5 %

หลักในการคำนวณ หากยอดขายรวมของเราอยู่ในช่วงใด ค่าคอมมิชชั่นที่ได้จะเท่ากับ ผลรวมของค่าคอมมิชชั่นสูงสุดของชั้นที่ต่ำกว่าทั้งหมด บวกด้วยผลคูณระหว่างยอดขายที่เหลือในช่วงนี้กับเปอร์เซนต์คอมมิชชั่นในช่วงนี้ ดังนั้นสามรถใช้ excel สร้างตารางเก็บค่าผลรวมคอมมิชชั่นและค่าต่ำสุดของยอดขายในแต่ละช่วงดังแสดงในภาพที่ 1 โดยสูตรการคำนวณค่าผลรวมคอมมิชชั่นในหลัก c ได้ดังนี้ สมมุติคำนวณค่าใน B3 = B2 + (A3-A2)*C2

Commission

ภาพที่ 1 ตารางใน excel ซึ่งเก็บข้อมูลการตำนวณค่าคอมมิชชั่น

จากหลักการคำนวณค่าคอมมิชชั่นจะเห็นว่าเราสามารถนำสูตร Excel : VLOOKUP มาประยุกต์ได้โดยการกำหนด option การค้นหาเป็น true เพื่อหาตำแหน่งช่วงข้อมูลค่าที่น้อยกว่ายอดขายได้จริง ตัวอย่างเช่น นาย ก มียอดขายในเดือน มค เท่ากับ 8,800 บาท การคำนวณค่าคอมมิชชั่นจะเริ่มจากหาตำแหน่งของยอดขายที่ต่ำกว่า 8,800 บาทในตารางจะพบว่ามีค่าเท่ากับ 5,000 บาท และจะพบว่าค่าคอมมิชชั่นสะสมในชั้นนี้เท่ากับ 75 บาท เมื่อรวมกับผลต่างในช่วงนี้ซึ่งมีค่าเท่ากับ 3,800*3.5% = 95 บาท รวมเป็นค่าคอมมิชชั่นที่ได้เท่ากับ 75+95= 170 บาท จากวิธีการคำนวณ สมมุติเรามีข้อมูลยอดขายรวมของพนักงานดังแสดงในภาพที่ 2

Commission-1

ภาพที่ 2 ตัวอย่างยอดขายเพื่อคำนวณค่าคอมมิชชั่น

จากแนวคิดการคำนวณสามารถเขียนเป็นสูตร Excel ได้ดังนี้ (สมมุติคำนวณค่าคอมมิชชั่นของพนักงานในแถว A11 = VLOOKUP(B11,$A$2:$C$6,2,TRUE) + (B11 -VLOOKUP(B11,$A$2:$C$6,1,TRUE))*VLOOKUP(B11,$A$2:$C$6,3,TRUE)

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

Commission-2

ภาพที่ 3 ผลการคำนวณค่าคอมมิชชั่นตามสูตร Excel

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

ปล สนใจตัวอย่างการคำนวณค่าคอมมิชชั่นในรูปแบบอื่น ดู ที่นี่ ครับ

ตัวอย่าง kpi : ค่า OEE

ตัวอย่าง kpi ในบทความนี้ผมขอนำเสนอค่า OEE ที่กล่าวได้ว่าอยู่ในระดับ World class เพื่อให้ท่านผู้อ่านได้นำไปเปรียบเทียบกับค่า oee ซึ่งคำนวณได้จริงของโรงงานท่าน จากที่ได้นำเสนอการคำนวณค่า oee ไปแล้วในบทความที่ผ่านมา ซึ่งท่านผู้อ่านก็ได้ทราบไปแล้วว่าค่า oee จะประกอบไปด้วยความพร้อมในการผลิต 3 ด้านคือ ความพร้อมด้านเครื่องจักร(Availability Rate) ความเร็วในการผลิตสินค้า (Performance Rate) คุณภาพของสินค้า (Quality Rate) โดยกล่าวได้ว่า ค่า oee ในระดับ word class จะมีเปอร์เซนต์ความพร้อมทั้งสามด้านดังแสดงในตารางที่ 1

ตารางที่ 1 แสดงองค์ประกอบของค่า oee ในระดับ world class

OEE Factor

world class

Availability Rate

90.0 %

Performance Rate

95.0 %

Quality Rate

99.9 %

Overall OEE

85.0 %

จากตารางที่นำเสนอไปจะช่วยตรวจสอบการวัดค่าและการเก็บข้อมูลสำหรับคำนวณ kpi ที่เป็นองค์ประกอบของค่า oee ได้เป็นอย่างดี อย่าลืมนะครับ ค่า oee ที่คำนวณได้ไม่น่าจะเกิน 85.0 % นะครับ หากท่านคำนวณแล้วได้ oee มากกว่า 85.0 % ให้หยุดคิดซักนิดและกลับไปทบทวนดูนะครับ เพราะท่านจะเห็นว่าการดำเนินกิจกรรมคุณภาพเพื่อให้ได้ค่า Quality Rate สูงถึง 99.9 % คงไม่ใช่เรื่องง่ายใช่หรือไม่ครับ ดังนั้นหากใครกล่าวว่า โรงงานสามารถดำเนินการผลิตโดยค่า oee มากกว่าหรือเท่ากับ 85.0 % ขอให้ช่วยสะกิดท่านผู้นั้นเบาๆและช่วยแนะนำการคำนวณค่า kpi ที่ถูกต้องดังที่ผมนำเสนอในตัวอย่าง kpi ที่กล่าวมาแล้ว การดำเนินการเพื่อให้ค่า oee ของกระบวนการผลิตหรือของโรงงานนั้นๆสูงขึ้นนั้น ในปัจจุบันได้ใช้หลักของ TPM เข้ามาบริหารจัดการครับอยากทราบว่าทำอย่างไรรอติดตามในบทความต่อๆไปนะครับ สวัสดีครับ

ตัวอย่าง kpi การผลิต

  1. oee
  2. Availability Rate
  3. Performance Rate
  4. Quality Rate
Yahoo bot last visit powered by  Ybotvisit.com