การใช้ excel : นำเสนอข้อมูลด้วยกราฟ Log ตอนที่ 1 (How to present data by logarithm scale by excel-1)

ในบทความนี้ขอนำเสนอเทคนิคการนำเสนอข้อมูลด้วย Excel  โดยใช้เทคนิคของ logarithm graph ซึ่งเป็นเทคนิคที่ใช้แก้ปัญหาข้อมูลที่นำเสนอมีค่าที่แตกต่างกันอย่างมาก เช่น มีค่าตั้งแต่ 100 – 1,000,000 ในกราฟเดียวกัน หรือยอดขายของเซลล์แต่ละคน ยอดขายสินค้า by item ในแต่ละเดือนเป็นต้น  ก่อนอื่นขอเริ่มต้นให้ท่านผู้อ่านรู้จัก scaling ก่อนครับ Scaling เป็นการเพิ่มหรือลดขนาดของตัวเลขในกลุ่มข้อมูลด้วยค่าคงที่ค่าหนึ่งๆ โดยส่วนใหญ่เราจะรู้จัก scaling แบบ linear (linear scale) ซึ่งสามารถเขียนสมการแสดงความสัมพันธ์ได้ดังนี้

y = ax  

เมื่อ y คือค่าหลังทำการ scale

x คือค่าก่อนการ scale

a คือค่าคงที่ในการ scale  มีค่ามากกว่า 0

จากสมการจะพบว่าความสัมพันธ์ระหว่าง x และ y เป็นแบบ linear  โดยหาก a มีค่ามากกว่า 1 จะเป็นการขยายค่า หรือ scale up และหาก x น้อยกว่า 1 จะเป็นการ scale down ตัวอย่างการทำ linear scale มีมากมายที่จะประยุกต์ใช้ ยกตัวอย่างเช่น คุณสมชายมีข้อมูลยอดขายสินค้า 5 item ซึ่งมีข้อมูลดังนี้

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

Y = (1/1,000,000)*x

ซึ่ง y เป็นยอดขายหลังทำการ scale แล้ว หน่วยเป็น ล้านบาท

x เป็นยอดขายที่แท้จริง หน่วยเป็นล้านบาท

a = 1/1,000,000

จากสมการ linear scale  ท่านผู้อ่านจะเห็นว่า a น้อยกว่า 1 ดังนั้นจึงเป็นการ scale down โดยหลังการ scaling แล้วจะได้ ยอดขายใหม่ในหน่วย ล้านบาท และเมื่อใช้สูตร excel ปรับรูปแบบตัวเลขเป็นทศนิยม 2 ตำแหน่ง ข้อมูลการนำเสนอยอดขายจะแสดงได้ดังตาราง

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

เอาหล่ะครับมาถึงขณะนี้สมมุติว่า คุณสมชายมีสินค้าใหม่เพิ่มขึ้นมาอีก 1 item ยอดขาย 80,000 บาท และต้องการนำเสนอผู้บริหารด้วยเนื่องจากเป็นสินค้าที่กำลังมาแรงหรือมี potential ในตลาดขณะนี้  เรามาดูกราฟหลังการเพิ่มสินค้า item นี้กันครับ

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

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

การใช้ excel สร้างกราฟแบบไดนามิกส์ (How to create dynamics chart by excel)

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

ภาพที่ 1 ตัวอย่างข้อมูลยอดขาย

ขั้นตอนการสร้างกราฟแบบไดนามิกส์จะเริ่มดังนี้ โดยสมมุติให้มีผลกับ Worksheet ชื่อ Sheet1 เท่านั้น

1. กำหนดชื่อข้อมูลแบบพลวัตรให้กับข้อมูลแกน x โดยใช้ชื่อ xValue  (ใช้เมนู สูตร เลือก กำหนดชื่อและเลือกขอบเขตเป็น Sheet1 )

สูตร excel : OFFSET($B$3,1,0,COUNTA($B:$B)-1,1) 

2. กำหนดชื่อข้อมูลแบบพลวัตรให้กับข้อมูลแกน y โดยใช้ชื่อ yValue  (ใช้เมนู สูตร เลือก กำหนดชื่อและเลือกขอบเขตเป็น Sheet1 )

สูตร excel : OFFSET($B$3,1,1,COUNTA($B:$B)-1,1) 

3. สร้างกราฟแท่ง โดยไปที่เมนู แทรก เลือกแผนภูมิคอลัมน์แบบกลุ่ม จะปรากฎพื้นที่พล๊อตกราฟใน Sheet1 

4. ทำการคลิกขวาในพื้นทีีแผนภูมิและเลือก เลือกข้อมูล... จะปรากฎหน้าต่างเลือกแหล่งข้อมูล

5. คลิกปุ่มเพิ่มในหน้าต่างดังกล่าวเพื่อ เพิ่มข้อมูลแกน y จะปรากฎหน้าต่าง แก้ไขชุดข้อมูล ให้พิมพ์สูตร excel ลงในช่องของค่าชุดข้อมูล ดังแสดงในภาพที่ 2 และคลิกปุ่ม ตกลง เพื่อปิดหน้าต่างดังกล่าว

6. คลิกปุ่ม แก้ไข ในช่องรายการ ป้ายชื่อแกน (ประเภท) แนวนอน จะปรากฎหน้าต่าง ป้ายชื่อแกน ให้พิมพ์สูตร excel ลงในช่อง ช่วงป้ายชื่อแกน ดังแสดงในภาพที่ 3 และคลิกปุ่ม ตกลง เพื่อปิดหน้าต่าง

7. คลิกปุ่ม ตกลง เพื่อปิดหน้าต่าง เลือกแหล่งข้อมูล จะได้กราฟแท่ง แสดงยอดขาย 3 เดือนแรก ดังภาพที่ 4

ภาพที่ 2 การกำหนดสูตร excel แสดงค่า y

ภาพที่ 3 การกำหนดสูตร excel แสดงป้ายชื่อแกน x

ภาพที่ 4 กราฟแท่งแสดงยอดขาย 3 เดือน

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

ภาพที่ 5 กราฟหลังจากมีการเพิ่มเติมข้อมูล

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

การใช้ excel สร้างกราฟเส้นตรง (Linear graph)

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

Ax + By + C = 0

เราสามารถเขียนรูปแบบสมการเส้นตรงใหม่เพื่อให้ง่ายต่อการเข้าใจเป็น

y = mx + c ; เมื่อ m คือความชันของกราฟเส้นตรง c คือจุดตัดบนแกน y (x = 0)

สรุปได้ว่า กราฟเส้นตรงจะประกอบไปด้วย ค่าความชันและค่าจุดตัดบนแกน y โดยจะมีกรณีพิเศษเช่น
หาก c = 0 (m ไม่เท่ากับ 0) เราจะได้กราฟเส้นตรงที่ลากผ่านจุดกำเนิด (0,0)

หาก m = 0 (c ไม่เท่ากับ 0) เราจะได้กราฟเส้นตรงที่ขนานกับแกน x ตัดแกน y ที่จุด c

หาก m เป็น infinity เราจะได้กราฟเส้นตรงที่ขนานกับแกน y ตัดแกน x ที่จุด C

นอกจากนี้เรายังสามารถคำนวณมุมระหว่างกราฟเส้นตรงและแกน x ได้จาก มุม = atan(m)

เรามาดูว่าจะใช้ excel กับกราฟเส้นตรงหรือสมการเส้นตรงได้ในกรณีใดบ้าง

1. อยากทราบรูปแบบของสมการเส้นตรงเมื่อมีพิกัดบนกราฟเส้นตรงใดๆ

จากสมการเส้นตรงที่ผมนำเสนอไป จะพบว่าค่าคงที่ m และ c ของสมการเส้นตรงหาได้ โดยหากเราทราบพิกัดใดๆบนกราฟเส้นตรงนั้นจำนวน 2 จุด ดังนั้นหากเรามีพิกัด (x , y) 2 จุด เราสามารถใช้ excel ช่วยคำนวณหาความชัน (Slope) และจุดตัดบนแกน y ของเส้นตรงนั้นได้ มาดูตัวอย่างกันครับ

สมมุติเรามีพิกัด 2 จุด ซึ่งอยู่บนกราฟเส้นตรงใดๆ แสดงอยู่ในเซลล์ C3:D4

1. ความชันของเส้นตรงจะแสดงที่เซลล์ C5 สามารถหาได้จากสูตร excel  C5 = SLOPE(D3:D4,C3:C4)
2. จุดตัดบนแกน y แสดงที่เซลล์ C6 หาได้จากสูตร excel  C6 = INTERCEPT(D3:D4,C3:C4)
3. เมื่อได้ค่าคงที่ของสมการเส้นตรงแล้ว เราสามารถคำนวณตัวแปรตาม y ซึ่งขึ้นกับ ตัวแปรอิสระ x ใดๆ(C8) ได้ในเซลล์ D8 โดยเขียนสูตร excel : D8 = $C$5*C8 + $C$6

ผลการรันสูตร excel แสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการรันสูตร excel



2. หากมีข้อมูลจำนวนมาก อยากเห็นรูปร่างการกระจายข้อมูลและเส้นแนวโน้มของ กราฟเส้นตรง

การใช้ excel สร้างกราฟเส้นตรง เราจะเริ่มจากการมีข้อมูลพิกัด x , y หลายๆจุด สมมุติให้ x และ y อยู่ในหลัก B และ C ให้ทำการพล็อตจุดข้อมูลเหล่านั้นโดยใช้การพล็อตกราฟแบบกระจาย (Scatter) ดังภาพที่ 2 เพื่อวิเคราะห์ลักษณะแนวโน้มของข้อมูล

ภาพที่ 2 การกระจายของข้อมูล

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

1. คลิกขวาที่จุดข้อมูล และให้เลือก เพิ่มเส้นแนวโน้ม....(Add Trend Line...)
2. จะปรากฎหน้าต่าง จัดรูปแบบเส้นแนวโน้ม ให้คลิกเลือก ตัวเลือกเส้นแนวโน้มในรายการด้านซ้าย ในรายการด้านขวาให้เลือก เชิงเส้น เป็นชนิดของแนวโน้ม/การถดถอย เลือกให้แสดงสมการเส้นบนแผนภูมิและแสดงค่า R-Squared บนแผนภูมิ ดังแสดงในภาพที่ 3

ท่านผู้อ่านสามารถปรับรูปแบบสีเส้นและลักษณะเส้นแนวโน้มได้โดยเลือกรายการด้านซ้าย คลิกปุ่ม ปิดหน้าต่าง จะปรากฎเส้นแนวโน้มเป็นกราฟเส้นตรงเพิ่มขึ้นในพื้นที่พล็อตกราฟ รวมถึงรูปแบบของสมการเส้นตรงและค่า R-Squared ดังแสดงในภาพที่ 4

ภาพที่ 3 การปรับเลือกเส้นแนวโน้มใน excel

ภาพที่ 4 กราฟเส้นตรงที่ได้จากการประมาณของข้อมูล

จากภาพที่ 4 เราจะเห็นว่าสมการเส้นตรงที่ได้ มีความชันเท่ากับ 0.067 และตัดแกน y ที่ y = 1.423 ส่วนค่า R-Squared เป็นสิ่งที่นักวิเคราะห์ต้องคำนึงถึง โดยหากค่า R มีค่าเข้าใกล้ 1 มากๆจะหมายถึงว่าความสัมพันธ์ระหว่าง y และ x เป็นแบบเชิงเส้นมาก นั้นหมายถึงว่าเราสามารถใช้ สมการเส้นตรงที่ได้จากกราฟเส้นตรงนี้พยากรณ์ตัวแปร y ได้ หากเราทราบตัวแปรอิสระ x โดยทั่วไป R-Squared ควรอยู่ประมาณ 0.9 - 1 ครับ 

3. เมื่อทราบว่าพิกัดข้อมูลที่มีอยู่มีแนวโน้มเป็นเส้นตรง จะคำนวณค่าตัวแปร y จากตัวแปร x ที่กำหนดให้

กรณีนี้สมมุติเรามีข้อมูล x , y ในหลัก Bและ C และเมื่อใช้ Scatter plot การกระจายแล้วพบว่ามีแนวโน้มเป็นเส้นตรง ดังภาพที่ 2 เราสามารถคำนวณหาค่า y เมื่อกำหนดตัวแปร x ได้ โดยใช้ สูตร excel : TREND โดยสูตร excel นี้มีรูปแบบดังนี้

TREND(y_known,x_known,x_new,const) โดยที่

y_known คือชุดข้อมูลพิกัดแกน y ที่ทราบค่า

x_known คือชุดข้อมูลพิกัดแกน x ที่ทราบค่า

x_new คือค่าตัวแปร x ที่ต้องการคำนวณค่า y

const คือตัวแปรเงื่อนไข หากกำหนดเป็น FALSE จะกำหนดให้ สมการเส้นตรงเป็น y = mx หากกำหนดเป็น TRUE จะกำหนดให้สมการเส้นตรงเป็น y = mx+c

สมมุติว่าเราต้องการทราบค่า y ที่ x = 11.5 จากการกระจายตัวแบบเส้นตรงของกลุ่มข้อมูลในหลัก B และ C เราสามารถเขียนสูตร excel ได้ดังนี้

= TREND(C5:C24,B5:B24,11,TRUE)  จะได้ค่า y ที่ x = 11 เป็น 2.162 เป็นต้น

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

บทความที่เกี่ยวข้อง

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

การใช้สูตร excel : linest สำหรับวิเคราะห์ความถดถอยของสมการเส้นตรง







การใช้ 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

การสร้างกราฟหรือแผนภูมิแบบเรดาร์ด้วย excel (How to make radar chart)

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

ภาพที่ 1 ข้อมูลใน Sheet Excel

ขั้นตอนการสร้างกราฟเรดาห์โดยใช้ excel ทำได้ดังนี้

1.เลือกข้อมูล cell A1:B6

2.คลิกเลือกเมนู insert

3.เลือกทูลบาร์ Other charts

4.เลือก chart type เป็น radar ชนิด Radar with Markers

จะได้กราฟหรือแผนภูมิแบบเรดาร์ดังแสดงในภาพที่ 2

ภาพที่ 2 กราฟแบบเรดาร์จากโปรแกรม excel

ในกรณีที่คะแนนสูงสุดของการประเมินในทุกหัวข้อเท่ากันท่านผู้อ่านสามารถปรับสเกลของกราฟได้ สมมุติให้ทุกหัวข้อการประเมินมีคะแนนเต็ม 6 คะแนน ให้ท่านผู้อ่านเลือกแกนกราฟและคลิกเมาส์ปุ่มขวา เลือก Format axis... โปรแกรม excel จะแสดงหน้าต่าง Format axis ให้ท่านผู้อ่านเลือกหัวข้อ Axis Options ด้านขวา ในพื้นที่ด้านซ้าย หัวข้อ Maximum คลิกเลือก  Fixed ป้อนค่าคะแนนเต็ม 6 และกดปุ่ม Close จะได้กราฟแบบเรดาร์ดังแสดงในภาพที่ 3

ภาพที่ 3 กราฟแบบเรดาร์หลังปรับคะแนนเต็มของหัวข้อประเมิน

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

ภาพที่ 4 ข้อมูลการประเมินผลแบบสอบถาม

ให้ท่านผู้อ่านดำเนินการตามขั้นตอน 1-4 ของวิธีการสร้างกราฟด้วย excel โดยในขั้นตอนที่ 1 ให้เลือกข้อมูลจาก cell A1:C6

ภาพที่ 5 แผนภูมิเรดาร์หลังการปรับปรุง

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

การใช้ excel สร้างแผนภูมิพาเรโต (How to make a Pareto Chart)

บทความฉบับนี้ขอนำเสนอการใช้ excel สร้างแผนภูมิพาเรโต (Pareto chart) แผนภูมิพาเรโตเป็นแผนภูมิที่ใช้สำหรับแสดงปัญหาหรือหัวข้อต่างๆ ที่เกิดขึ้นกับสิ่งที่กำลังสนใจ โดยส่วนใหญ่จะพิจารณาไปกับสิ่งที่กำลังจะก่อให้เกิดปัญหา หรือใช้เพื่อช่วยตัดสินใจแก้ปัญหาต่างๆ โดยหลักการสร้างแผนภูมิพาเรโตจะเริ่มจากการเรียงลำดับปัญหาหรือหัวข้อต่างๆเหล่านั้นตามความถี่ที่พบจากมากไปหาน้อยและแสดงขนาดความถี่มากน้อยด้วยแผนภูมิแท่งควบคู่ไปกับการแสดงค่าสะสมของความถี่ด้วยแผนภูมิเส้น ซึ่งแกนนอนของแผนภูมิเป็นประเภทของปัญหาและแกนตั้งเป็นค่าร้อยละของปัญหาที่พบ จากหลักการดังกล่าว จะพบว่าเราสามารถใช้ excel ช่วยสร้างแผนภูมิพาเรโตได้โดยไม่ยากนัก โดยต้องกำหนดให้แผนภูมิดังกล่าวประกอบด้วยแกนตั้ง 2 แกน (Primary and secondary axis) ผมขอยกตัวอย่างการสร้างแผนภูมิพาเรโตด้วย excel ดังต่อไปนี้ครับ

สมมุติว่าเราได้ทำการวิเคราะห์แยกสาเหตุของการทำของเสียในกระบวนการผลิตทั้งหมด 6 ข้อ สมมุติให้เป็นสาเหตุ A , B, C, D, E, F โดยจำนวนของเสียเมื่อแยกตามสาเหตุต่างๆแล้วสามารถบันทึกลงในโปรแกรม excel ได้ดังภาพที่ 1

ภาพที่ 1 ข้อมูลของเสียจากการผลิต

ให้ท่านผู้อ่านทำการเรียงข้อมูลจำนวนของเสียจากมากไปหาน้อย (เรียงตามข้อมูลในหลัก C ) จากนั้นให้ท่านผู้อ่านสร้างหลักข้อมูลเพิ่มขึ้นอีกสองหลัก (หลัก D and หลัก E) โดยกำหนดสูตร excel เพื่อคำนวณจำนวนของเสียสะสมในหลัก D ดังนี้

D6 = C6

สำหรับ D7 เขียนสูตร excel เพื่อคำนวณจำนวนของเสียสะสม

D7 = D6+C7

ท่านผู้อ่านสามารถ drag เพื่อสำเนาสูตร excel ไปยัง D8 to D12

กำหนดสูตร excel เพื่อคำนวณจำนวนเปอร์เซนต์ของเสียสะสมในหลัก E ได้ดังนี้

E6 = D6/$D$12

ท่านผู้อ่านสามารถ drag เพื่อสำเนาสูตร excel ไปยัง E8 to E12

กำหนดรูปแบบของเซลล์ E6 to E12 เป็นแบบ %

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

ภาพที่ 2 ข้อมูลของเสียหลังจากปรับแต่งเพื่อสร้างแผนภูมิพาเรโต



เอาหล่ะครับเรามาสร้างแผนภูมิพาเรโตโดยการใช้ excel กันเลยครับ (อ้างอิงการใช้ excel 2010) เริ่มจาก การสร้างแผนภูมิแท่ง มีรายละเอียดดังนี้

1. เลือกข้อมูล B5:C12,E5:E12 (ทำได้โดยการเลือก B5:C12 และกดปุ่ม ctrl ขณะเลือก E5:E12)

2. เลือกเมนู Insert - 2D Column เลือกชนิดของแผนภูมิแท่งเป็นแบบ Clustered Column (คอลัมน์แบบกลุ่ม) คลิกปุ่ม OK

จะได้แผนภูมิแท่งดังแสดงในภาพที่ 3





ภาพที่ 3 แผนภูมิแท่งแสดงสาเหตุ

3. ใช้เมาส์เลือกแท่งข้อมูล เปอร์เซนต์ของเสียสะสม(แท่งสีแดงต่ำๆ)และคลิกเมาส์ขวา เลือก Change Series Chart Type... (เปลี่ยนชนิดแผนภูมิชุดข้อมูล...) จะปรากฎหน้าต่าง Change Chart Type ให้เลือกแผนภูมิประเภท X Y (Scatter) และเลือกชนิด Scatter with Straight Lines and Markers คลิกปุ่ม OK

4. ทำการย้าย legend ของแผนภูมิไปยังด้านล่างของแผนภูมิ โดยการคลิกเมาส์เลือกแผนภูมิ และเลือกเมนู Layout เลือก Legend เลือก Show Legend at bottom

5. เพิ่มป้ายชื่อแกนแนวนอนและแนวตั้งของแผนภูมิพาเรโต โดยการคลิกเมาส์เลือกแผนภูมิ และเลือกเมนู Layout เลือก Axis Titles เพิ่มป้ายชื่อแกนตั้งและแกนนอน

โดยการเลือก Primary Horizontal Axis Titles สำหรับป้ายแกนแนวนอน (สาเหตุการเกิดของเสีย)

โดยการเลือก Primary Vertical Axis Titles สำหรับป้ายแกนตั้งด้านซ้าย(จำนวนของเสีย (ชิ้น))

โดยการเลือก Secondary Vertical Axis Titles สำหรับป้ายแกนตั้งด้านขวา (เปอร์เซนต์ของเสียสะสม)
6. ปรับรูปแบบแกน เปอร์เซนต์ของเสียสะสมให้แสดงค่าสูงสุดเท่ากับ 100% โดยการคลิกเมาส์ขวาที่แกนตั้งด้านขวา เลือก จัดรูปแบบแกน... จะปรากฎ หน้าต่าง จัดรูปแบบแกน ใน List ด้านซ้ายให้ท่านเลือก ตัวเลือกแกน หน้าต่างด้านขวาจะปรากฎ ตัวเลือกแกนขึ้น ให้เลือก ค่ามากที่สุด เป็น ค่าคงที่ และปรับค่าเป็น 1.0 ดังแสดงในภาพที่ 4

ภาพที่ 4 หน้าต่างการปรับรูปแบบแกน เปอร์เซนต์ของเสียสะสม

ท่านจะได้แผนภูมิพาเรโตดังแสดงในภาพที่ 5

ภาพที่ 5 แผนภูมิพาเรโตจากการใช้ excel 



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

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

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