วิธีการสร้างแผนภูมิฮิสโตแกรมโดยใช้สูตร Excel : Frequency (Method to create histogram chart with excel formula)

การใช้ excel ขอนำเสนอวิธีการสร้างแผนภูมิฮิสโตแกรมโดยใช้สูตร Excel : Frequency จากที่ได้นำเสนอการสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram ใน Add -Ins Data Analysis ไปแล้ว ซึ่งพบว่าวิธีการดังกล่าวอาจจะไม่เหมาะสมหากข้อมูลที่ต้องการวิเคราะห์มีการเปลี่ยนแปลงจำนวนอยู่ตลอดเวลา โดยวิธีการเดิมนั้นจะต้องทำการเรียกคำสั่ง  Histogram ทุกครั้งเมื่อมีการปรับเพิ่มหรือลดลงของข้อมูล ดังนั้นเพื่อเพิ่มประสิทธิภาพในการวิเคราะห์ข้อมูล จึงขอนำเสนอ วิธีการสร้างแผนภูมิฮิสโตแกรมโดยใช้สูตร Excel : Frequency ซึ่งจะทำให้แผนภูมิฮิสโตแกรมที่สร้างด้วยวิธีนี้มีการอัพเดต รูปร่างอย่างอัตโนมัติเมื่อมีการอัพเดตข้อมูล โดยข้อมูลสำหรับสร้างฮิสโตแกรมผมจะขออ้างอิงตัวอย่างเดิมจากบทความการสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram มานำเสนอ เริ่มจาก

1. ปรับปรุงการเก็บข้อมูลโดยย้ายข้อมูลทั้งหมดถูกเก็บอยู่ในหลัก A เพียงหลักเดียว

2. ใช้สูตร excel แสดงค่า max. ค่า min. จำนวนข้อมูล  Range of data จำนวนชั้นข้อมูล และความกว้างของชั้นข้อมูล ตามที่ได้นำเสนอในบทความ การสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram 

3. สร้างข้อมูลขอบเขตข้อมูล โดยใช้สูตร excel ตามที่นำเสนอในบทความการสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram 

4. หลังปรับรูปแบบการเก็บและแสดงข้อมูล จะได้รูปแบบใน excel ก่อนหาความถี่ของข้อมูลแต่ละชั้นดังภาพที่ 1

5. ขั้นตอนการคำนวณความถี่ของข้อมูลแต่ละชั้นด้วยสูตร excel : FREQUENCY ทำได้ดังนี้
    5.1 พิมพ์สูตร excel ใน D11 ดังนี้ D11 = FREQUENCY(A:A,C11:C18)
    5.2 เลือก CELL D11:D18
    5.3 กดปุ่ม F2
    5.4 กดปุ่ม Shift + Ctrl + Enter เพื่อคำนวณความถี่ของชั้นข้อมูลที่เหลือ (เป็นการบอกให้ excel คำนวณสูตร excel แบบเวกเตอร์)
6. สร้างแผนภูมิฮิสโตรแกรมโดยใช้แผนภูมิแบบ Scatter ดังที่ได้นำเสนอในบทความ การใช้ excel ปรับแต่งกราฟแท่งในแผนภูมิฮิสโตแกรม
7. แผนภูมิฮิสโตแกรมแสดงได้ดังภาพที่ 2

ภาพที่ 1 รูปแบบการแสดงข้อมูลใน excel

ภาพที่ 2 แผนภูมิฮิสโตแกรม

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

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

การใช้ excel พยากรณ์ยอดขายด้วยเทคนิค Monte Carlo Simulation







การติดตั้ง Analysis Toolpack ใน excel เพื่อช่วยวิเคราะห์ข้อมูล

บทความการใช้ excel ขอนำเสนอการติดตั้งเครื่องมือใน Analysis Toolpack เป็นที่ทราบกันดีว่าโปรแกรม excel  ได้เตรียมเครื่องมือเพื่อช่วยในการคำนวณหรือวิเคราะห์ข้อมูลให้กับผู้ใช้ ที่ผ่านมาผมได้นำเสนอการใช้ Histogram , t-test ในคำสั่ง Data Analysis ซึ่งเป็นคำสั่งประเภทหนึ่งใน Analysis Toolpack นอกจากนี้ยังมี Add-Ins ตัวอื่นๆที่เคยนำเสนอไปแล้วใน blog นี้เช่น Solver ซึ่งช่วยในการคำนวณในปัญหาการหาค่าที่เหมาะสมที่สุด (Optimization Method)

โดยปกติเครื่องมือเหล่านี้จะไม่ถูกแสดงในเมนูหรือ Ribbon (excel ตั้งแต่ 2007 ขึ้นไป) ดังนั้นท่านผู้อ่านจะต้องทำการติดตั้งเพื่อให้คำสั่งเหล่านี้แสดงในเมนูหรือ Ribbon โดยผมขอนำเสนอการติดตั้งใน excel 2007 นะครับ การติดตั้งเริ่มจาก

1. คลิกปุ่ม  Office button ซึ่งอยู่มุมบนซ้ายของโปรแกรม excel

2. คลิกปุ่ม Excel Options... จะปรากฎหน้าต่าง Excel Options ให้เลือก Add-Ins ในรายการด้านซ้าย ดังแสดงในภาพที่ 1

3. รายการด้านขวาจะแสดงรายการ Add-Ins ต่างๆ โดยจะแบ่งออกเป็นสองส่วนคือ แถวด้านบนจะแสดงรายการ Add-Ins ที่เราได้ติดตั้งลง excel แล้ว จะปรากฎในส่วนของ Active Application Add-Ins ส่วนถัดไปเป็นรายการ Add-Ins ที่ยังไม่ได้ถูกติดตั้งลง excel จะเรียนว่า Inactive Application Add-Ins 

4. จากภาพที่ 1 จะเห็นว่า Analysis Toolpack ได้ถูกติดตั้งใน excel ในคอมพิวเตอร์เครื่องนี้แล้ว

5. หากต้องการติดตั้ง Add-Ins  Solver Add-In (อยู่แถวสุดท้ายของ Inactive Application Add-Ins) สามารถทำได้โดย คลิกเลือก Solver Add-In

6. คลิกปุ่ม Go จะปรากฎหน้าต่าง Add-Ins ดังแสดงในภาพที่ 2 ให้คลิกเลือก Solver Add-In และกดปุ่ม OK 

7. โปรแกรม excel จะทำการติดตั้ง ซึ่งบางครั้งอาจจะให้เรา Insert แผ่น CD โปรแกรม  Microsoft Office ลงไปด้วย ซึ่งแล้วแต่การเลือกข้อมูลการติดตั้งโปรแกรมตอนเริ่มต้น

8. เมื่อติดตั้งเสร็จแล้วหน้าต่างการติดตั้งจะปิดเองโดยอัตโนมัติ

9. ให้ท่านผู้อ่านคลิกเมนู Data จะปรากฎคำสั่ง Solver เพิ่มขึ้นมาในแถวถัดจาก Data Analysis ใน Ribbon Analysis ดังแสงในภาพที่ 3

ภาพที่ 1 หน้าต่าง Excel Options

ภาพที่ 2 หน้าต่าง Add-Ins

ภาพที่ 3 คำสั่ง Data Analysis และ Solver ใน Ribbon Analysis

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

การใช้ excel ปรับแต่งกราฟแท่งในแผนภูมิฮิสโตแกรม(Flexible Histogram Chart)

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

1. การปรับแต่งกราฟแท่งในแผนภูมิฮิสโตแกรม

แผนภูมิฮิสโตแกรมที่นำเสนอในบทความที่ผ่านมาแสดงได้ดังภาพที่ 1

ภาพที่ 1 แผนภูมิฮิสโตแกรมก่อนปรับแต่ง

เราจะลดช่องว่างระหว่างแท่งกราฟเพื่อให้กราฟแท่งชิดกันมากขึ้นเพื่อให้เห็นรูปร่างของฮิสโตแกรมชัดเจนมากขี้น โดยดำเนินการดังนี้

1. คลิกแท่งกราฟในแผนภูมิฮิสโตแกรมและกดเมาส์ปุ่มขวาจะปรากกฎ Pop up menu ขึ้นมา

2. เลือก Format Data Series... จะปรากฎหน้าต่าง Format Data Series

3. เลือก Series Option ใน List รายการด้านซ้าย

4. เลือกปรับ Gap Width โดยการลาก Slide bar มาด้าน No Gap ดังภาพที่ 2

5. เลือก Border Color ใน List รายการด้านซ้าย
6. เลือก Option Solid line เลือกสีของเส้นขอบของกราฟแท่งเป็นสีดำ
7. กดปุ่ม Close เพื่อออกจากการปรับแต่งแท่งกราฟจะได้แผนภูมิฮิสโตแกรมที่มีความชัดเจนของรูปร่างมากขึ้น
8. แก้ไขชื่อแกนนอนและแกนตั้ง (X และ Y) เพื่อให้สื่อความหมายโดยแก้ไขข้อมูลที่ Cell G18 และ H18 เป็น จำนวน Finish Goods และ จำนวนพนักงาน (คน)
9. แก้ไขชื่อแกนนอนและแกนตั้งโดยคลิกเมาส์ขวาที่ตำแหน่งของชื่อแกนในแผนภูมิและให้เลือก Edit Text
10. ทำการลบและแก้ไขชื่อแกนตามข้อที่ 8 จะได้แผนภูมิที่ถูกปรับแต่งให้มีความชัดเจนดังแสดงในภาพที่ 3

.

ภาพที่ 2 การปรับแต่งระยะห่างระหว่างแท่งกราฟด้วย excel

ภาพที่ 3 แผนภูมิฮิสโตแกรมหลังการปรับปรุงด้วย excel

2. การสร้าง Histogram โดยการใช้แผนภูมิแบบ scatter plot

การสร้างแผนภูมิฮิสโตแกรมด้วยกราฟแท่งอาจพบเจอปัญหาป้ายกำกับในแนวนอนวางซ้อนทับกันจนไม่สามารถสื่อสารข้อมูลได้ ซึ่งจะพบในกรณีที่จำนวนชั้นของข้อมูลมีจำนวนมาก การหลีกเลี่ยงปัญหาดังกล่าวทำได้โดยการสร้างแผนภูมิฮิสโตแกรมด้วยแผนภูมิแบบ scatter plot  และใช้ Error bar ในแกนตั้งเป็นแท่งแสดงความถี่ ก็จะช่วยแก้ปัญหาได้ เรามาดูวิธีการสร้างแผนภูมิแบบ scatter plot  กันครับ

1. เลือกข้อมูลขอบเขตชั้นและความถี่ที่เกิดขึ้น G19:H26

2. เลือกเมนู Insert คลิกเลือก Scatter ดังภาพที่ 4 เลือก Scatter with Straight Lines

3. จะปรากฎแผนภูมิแบบ Scatter ขึ้นดังภาพที่ 5

4. คลิกเลือกเส้นกราฟแผนภูมิ เลือกเมนู Layout เลือก Error Bar เลือก More Error Bars Option... จะปรากฏหน้าต่าง Format Error Bars ดังภาพที่ 6

5. เลือก Vertical Error Bars ใน List รายการด้านซ้าย

6. เลือก Direction เป็น Minus

7. เลือก Error Amount เป็น Percentage และกำหนดค่าเท่ากับ 100

8. เลือก Line Stye ใน List รายการด้านซ้าย

9. กำหนด Width เป็น 20

10. เลือก Line Color ใน List รายการด้านซ้าย

11. เลือก Solid Line กำหนดสีตามต้องการ
12. กดปุ่ม Close จะได้แผนภูมิฮิสโตแกรมดังภาพที่ 7
13. สามารถเพิ่มป้ายกำกับแกนตั้งและแกนนอนได้โดยคลิกแผนภูมิ เลือกเมนู Layout เลือก Axis Titles สามารถศึกษาได้จากบทความ การใช้ excel สร้างแผนภูมิพาเรโต

ภาพที่ 4 เมนูสร้างกราฟ Scatter

ภาพที่ 5 กราฟ Scatter จาก excel

ภาพที่ 6 การปรับแต่งรูปแบบ Error Bars

ภาพที่ 7 แผนภูมิฮิสโตแกรมจากกราฟ Scatter ของ Excel

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

หัวข้อที่น่าสนใจ
การใช้ excel พยากรณ์ยอดขายด้วยเทคนิค Monte Carlo Simulation

การใช้ excel สร้างแผนภูมิฮิสโตแกรม 1 (How to make histogram chart?)

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

โปรแกรม excel มีวิธีสร้างแผนภูมิฮิสโตแกรมอยู่ 2 วิธีดังนี้

1. การสร้างโดยใช้เครื่องมือสำเร็จรูป Analysis Tool-Pack (ซึ่งจะนำเสนอในบทความนี้)

2. การสร้างโดยใช้สูตร excel : Frequency

การสร้างโดยใช้เครื่องมือสำเร็จรูป Analysis Tool-Pack 

ก่อนที่จะสร้างแผนภูมิฮิสโตแกรมด้วย Analysis Tool-Pack จะต้องเตรียมข้อมูลให้พร้อมก่อน โดยในบทความนี้ขอนำเสนอข้อมูลจำนวนชิ้นงาน FG ของคนงาน 100 คนจากการศึกษาผลผลิตของคนงานในช่วง 2 สัปดาห์ โดยบันทึกลงในช่วง A1:J10 ผมขอแบ่งขั้นตอนการสร้างแผนภูมิฮิสโตแกรมออกเป็น 2 ขั้นตอนดังนี้

ขั้นตอนการเตรียมข้อมูล

1. หาค่าสูงสุดและต่ำสุดของช่วงข้อมูลโดยแสดงผลที่ B13 และ B14 โดยเขียนสูตร excel ดังนี้

• Max(A1:J10)
• Min(A1:J10)

 2. คำนวณพิสัยของช่วงข้อมูลและแสดงผลที่ B15 โดยเขียนสูตร excel ดังนี้

• B15 = B13 - B14

 3. คำนวณหาจำนวนชั้นของข้อมูลและแสดงผลที่ B16 โดยเขียนสูตร excel ดังนี้

• B16=ROUNDUP(1+3.3*LOG10(B15),0) 

 4. คำนวณค่าความกว้างของชั้นข้อมูลและแสดงผลที่ B17 โดยเขียนสูตร excel ดังนี้

• B17=ROUNDUP(B15/B16,0)

 5. คำนวณขอบเขตบนของข้อมูลของชั้นแรก โดยแสดงผลที่ D18 โดยเขียนสูตร excel ดังนี้

• D18 = $B$14 + $B$17

6.  คำนวณขอบเขตบนของข้อมูลของชั้นที่ 2 โดยแสดงผลที่ D19 โดยเขียนสูตร excel ดังนี้

• D19 = D18 + $B$17

 7. สำเนาสูตร excel ที่ cell D19 ลงไปที่ D20 ถึง D25

จะได้ข้อมูลที่พร้อมนำไปสร้างแผนภูมิฮิสโตแกรมดังแสดงในภาพที่ 1

ภาพที่ 1 ข้อมูลสำหรับสร้างแผนภูมิฮิสโตแกรมใน excel

ขั้นตอนการสร้างแผนภูมิฮิสโตแกรมโดยใช้เครื่องมือสำเร็จรูป Analysis Tool-Pack 

ให้ท่านผู้อ่านไปเลือกเมนู ข้อมูล (Data) เลือกเมนู Data Analysis จะปรากฎหน้าต่าง Data Analysis ขึ้นมาให้ท่านเลือก Histogram ใน List รายการAnalysis Tools กดปุ่ม OK จะปรากฎหน้าต่าง Histogram ขึ้นมาให้ดำเนินการดังนี้

1. ในช่อง Input Range ให้เลือกช่วงข้อมูลที่ต้องการสร้างแผนภูมิฮิสโตแกรม โดยในตัวอย่างเลือก A1:J10

2. ในช่อง Bin Range ให้เลือกช่วงขอบเขตบนของชั้นข้อมูล ในตัวอย่างนี้เลือก D18:D25

3. ในช่อง Output Range ให้เลือกตำแหน่งในการแสดงผลเป็น G18 (ท่านสามารถทดลองใช้ Option อื่นในการแสดงผลได้ครับ)

4. เลือก Chart Output เพื่อแสดงแผนภูมิฮิสโตแกรม ท่านผู้อ่านสามารถเลือกผลการคำนวณเป็น แผนภูมิพาเรโตหรือความถี่สะสมได้

5. คลิกปุ่ม OK จะได้แผนภูมิฮิสโตแกรมดังภาพที่ 3

ภาพที่ 2 การกำหนดข้อมูลเพื่อสร้างแผนภูมิฮิสโตแกรม

ภาพที่ 3 แผนภูมิฮิสโตแกรม

เสร็จเรียบร้อยแล้วครับขั้นตอนการสร้างแผนภูมิฮิสโตแกรมด้วยเครื่องมือสำเร็จรูป Analysis Tool-Pack  วิธีการนี้มีข้อเสียคือหากมีจำนวนข้อมูลเพิ่มขึ้นท่านผู้อ่านต้องดำเนินการทำขั้นตอนการสร้างแผนภูมิฮิสโตแกรมใหม่อีกครั้ง แต่การสร้างแผนภูมิฮิสโตแกรมแบบใช้สูตร excel : Frequency ที่จะนำเสนอต่อไปจะมีความยืดหยุ่นมากขึ้นโดยสามารถอัพเดตรูปร่างของแผนภูมิฮิสโตแกรมให้โดยอัตโนมัติ ลองติดตามดูครับ ส่งท้ายท่านผู้อ่านจะพบว่าแผนภูมิฮิสโตแกรมที่สร้างขึ้นจากเครื่องมือสำเร็จรูป Analysis Tool-Pack ยังต้องปรับแต่งรูปร่างฮิสโตแกรมอีกเล็กน้อยเพื่อให้มีความสวยงามครับ โดยผมจะขอนำเสนอในบทความต่อไป สวัสดีครับ

ปล

หาก Excel ของท่านผู้อ่านไม่มีคำสั่ง Data Analysis ท่านจะต้องติดตั้งเครื่องมือสำเร็จรูป Analysis Tool-Pack  เสียก่อนครับ

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

การใช้ excel พยากรณ์ยอดขายด้วยเทคนิค Monte Carlo Simulation

ปรับขนาดไฟล์ภาพทีละมากๆทำอย่างไร (How to resize the picture)

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

1. เปิดโปรแกรม Microsoft Office Picture Manager
2. เลือกเมนู File เลือก Add Picture Shortcut...
3. จะปรากฎไดอะล็อก Add Picture Shortcut
4. เลือกโฟล์เดอร์ซึ่งเก็บไฟล์ภาพที่ต้องการปรับขนาด.
5. คลิกปุ่ม Add
6. โปรแกรมจะโหลดรูปภาพในโฟล์เดอร์ที่เลือกไว้มาแสดง
7. ด้านขวามือของโปรแกรมจะปรากฎตัวเลือกในการปรับแต่งภาพ
8. ให้กด Ctrl+A เพื่อเลือกปรับขนาดไฟล์ภาพทั้งหมด
9. เลือก Edit Picture
10. จะปรากฎตัวเลือกสำหรับปรับขนาดภาพ
11. เลือก Resize เพื่อปรับขนาด
12. เลือก ปรับขนาดเป็น Percentage เพื่อสะดวกในการปรับขนาดคืน
13. คลิกปุ่ม OK เพื่อยืนยันการปรับขนาดภาพ
14. เลือกเมนู File เลือก Save All เพื่อเป็นการบันทึกทั้งหมด

เท่านี้ท่านผู้อ่านก็สามารถปรับลดขนาดไฟล์ภาพได้ครั้งละมากๆได้แล้วครับ ลองนำไปใช้ดูนะครับ ผมว่ามันสามารถช่วยท่านผู้อ่านหลายๆท่านได้เหมือนที่ผมประสบมา สวัสดีครับ

หน้าจอโปรแกรมMicrosoft Office Picture Manager

ตัวเลือกการปรับลดขนาดไฟล์ภาพ

ตัวเลือกปรับขนาดไฟล์ภาพเป็น Percentage 

การใช้ excel คำนวณดอกเบี้ยเงินกู้หรือบัตรเครดิตแบบรายวัน

การใช้ excel คำนวณดอกเบี้ยเงินกู้หรือบัตรเครดิตแบบรายวัน เป็นวิธีการหนึ่งที่ช่วยให้ท่านผู้อ่านสามารถคิดดอกเบี้ยเงินกู้หรือบัตรเครดิตได้อย่างต่อเนื่อง ตามได้นำเสนอไปแล้วในการคำนวณหนี้เงินกู้ เราจะพบว่าหนี้เงินกู้ประเภทสินเชื่อเงินสด และหนี้จากบัตรเครดิต เจ้าหนี้จะคำนวณดอกเบี้ยเป็นรายวัน หากท่านผู้อ่านสังเกตตัวอย่างการคำนวณด้านหลังของใบแจ้งหนี้บัตรเครดิตหรือใบแจ้งหนี้บัตรเงินสด เราจะพบตัวอย่างการคำนวณดอกเบี้ยให้เราทราบ เอาหล่ะครับมีกฎข้อหนึ่งที่ต้องจำคือดอกเบี้ยจากบัตรเครดิตจะคิดก็ต่อเมื่อเราชำระหนี้ต่ำกว่ายอดที่เรียกเก็บภายในกำหนดเวลา ในขณะที่บัตรกดเงินสดจะคิดดอกเบี้ยเงินกู้ทันทีที่เรากดใช้เงินสด ดังนั้นตัวแปรที่เราต้องทราบในการคำนวณดอกเบี้ยหนี้บัตรเครดิตหรือบัตรกดเงินสดคือ วันที่ต้องการคำนวณดอกเบี้ย อัตราดอกเบี้ย และวันที่เราเบิกถอนเงินสด หรือวันที่เรารูดบัตรเครดิตซื้อของ (ในกรณีที่เราติดค้างหนี้บัตรเครดิตอยู่) มีสูตร excel ที่ใช้คำนวณระยะห่างระหว่างวันได้โดยง่าย เพียงกำหนดรูปแบบของ cell ให้เป็นแบบ date (d/m/yyyy) เราสามารถคำนวณดอกเบี้ยตั้งแต่วันถอนหรือรูดบัตรถึงวันที่ต้องการคำนวณดอกเบี้ยได้ตามสูตรดังนี้

INT = PV*อัตราดอกเบี้ย(%)*day/365   (สมการที่ 1)

เมื่อ

• INT คือดอกเบี้ยที่เกิดขึ้นจากเงินต้นก้อนนั้น
• PV คือเงินต้น
• อัตราดอกเบี้ย ให้คิดรวมค่าธรรมเนียมด้วย โดยปกติแหล่งเงินกู้ที่เป็นคนไทยจะมีอัตราดอกเบี้ยรวมค่าธรรมเนียมรายปีต่ำกว่าแหล่งเงินกู้ต่างประเทศ อย่างเช่น UOB HSBC นี่เป็นสาเหตุหนึ่งที่ทำให้ธนาคารเหล่านี้อนุมัติบัตรเครดิตหรือบัตรเงินสดในวงเงินที่สุงกว่าและง่ายกว่า
• day คือจำนวนวันตั้งแต่รูดบัตรถึงวันที่คำนวณดอกเบี้ย

เราจะออกแบบการคำนวณในโปรแกรม excel โดยให้ท่านดำเนินการสร้างแบบฟอร์มดังภาพที่ 1

ภาพที่ 1 แบบฟอร์มการคำนวณดอกเบี้ย



กำหนดรูปแบบของเซลล์ต่างๆดังนี้

• C1 กำหนดรูปแบบเป็นแบบ Date(d/m/yyyy)
• C2 ป้อนอัตราดอกเบี้ย
• กำหนดรูปแบบของ A5:A18 เป็นแบบ Date(d/m/yyyy)
• กำหนดรูปแบบของ D5:D18 เป็นแบบ General โดยกำหนดสูตร excel เพื่อคำนวณจำนวนวันได้ดังนี้
• สมมุติสูตร excel ของ D5 = IF(ISBLANK(A5),"",($C$1-A5)+1)
• ก็อปปี้สูตร excel ไปในช่วง D6:D18
• กำหนดรูปแบบของ E5:E18 เป็นแบบ Number แสดงทศนิยม 2 ตำแหน่ง และกำหนดเครื่องหมายคอมม่าเพื่อแยกหลักพันของตัวเลข  โดยกำหนดสูตร excel เพื่อคำนวณดอกเบี้ยตามสมการที่ 1 ได้ดังนี้
• สมมุติสูตร excel ของ E5 = IF(ISBLANK(A5),"",C5*D5*$C$2/365)
• ก็อปปี้สูตร excel ไปในช่วง E6:E18
• กำหนดรูปแบบของ F5:F18 เป็นแบบ Number แสดงทศนิยม 2 ตำแหน่ง และกำหนดเครื่องหมายคอมม่าเพื่อแยกหลักพันของตัวเลข โดยกำหนดสูตร excel เพื่อคำนวณเงินต้นบวกดอกเบี้ย ได้ดังนี้
• สมมุติสูตร excel ของ F5 = IF(ISBLANK(A5),"",C5+F5)
• ก็อปปี้สูตร excel ไปในช่วง F6:F18
• กำหนดรูปแบบของ E19:F19 เป็นแบบ Number แสดงทศนิยม 2 ตำแหน่ง และกำหนดเครื่องหมายคอมม่าเพื่อแยกหลักพันของตัวเลข โดยกำหนดสูตร excel เพื่อคำนวณผลรวมเงินต้นและดอกเบี้ยของทุกรายการได้ดังนี้
• สูตร excel ของ F19 = SUM(F5:F18)
• สูตร excel ของ E19 = SUM(E5:E18)

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

ภาพที่ 2 ตัวอย่างการคิดดอกเบี้ย