การใช้ 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 พยากรณ์ยอดขายด้วยเทคนิด Monte Carlo Simulation 1

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

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

กำไร(Y) = รายได้ - รายจ่าย

โดยรายได้เกิดจาก 

จำนวนการสั่งซื้อ (S) * กำไรต่อหน่วย (P) 

และจำนวนการสั่งซื้อสามารถคำนวณได้จากจำนวนลูกค้าเป้าหมายที่บริษัทต้องไปนำเสนอ (L) *โอกาสที่ลูกค้าจะสั่งซื้อสินค้าดังกล่าว (R)

ดังนั้นรายได้สามารถคำนวณได้จาก L*R*P

รายจ่ายคำนวณได้จาก

รายจ่ายคงที่(H) + รายจ่ายในการนำเสนอลูกค้าทั้งหมด

รายจ่ายในการนำเสนอลูกค้าทั้งหมดจะเท่ากับ รายจ่ายจากการไปนำเสนอลูกค้าต่อราย(C) *L

ดังนั้น รายจ่ายคำนวณได้จาก H + L*C

ดังนั้น    Y = L*R*P - (H+L*C)

จากแบบจำลองผลกำไรจะพบว่า ผลกำไรจะขึ้นอยู่กับตัวแปรอิสระ L, R, P, C, H โดยเราพบว่าตัวแปรบางตัวมีความผันผวนตลอดเวลา เรามาดูความผันผวนของแต่ละตัวแปรกันครับ

1. กำไรต่อหน่วยของสินค้า (P) อาจมีการผันผวนเนื่องจากต้นทุนการผลิต ค่าธรรมเนียมต่างๆ หรือแม้แต่ราคาตั้งต้นในการขาย สมมุติให้มีการผันผวน ระหว่าง 1,400 - 1,600 บาทต่อหน่วย

2. โอกาสที่ลูกค้าจะสั่งซื้อสินค้าดังกล่าว (R) อยู่ระหว่าง 5% - 20% จากกลุ่มลูกค้าเป้าหมาย

3. จำนวนลูกค้าเป้าหมายที่ไปนำเสนอ(L) ผันผวนอยู่ที่ 1,300 - 1,700 ราย

4. รายจ่ายจากการไปนำเสนอลูกค้าต่อราย(C) ผันผวนอยู่ระหว่าง 10 - 25 บาท

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

2. การจำลองตัวแปรอิสระให้อยู่ในช่วงผกผันในแต่ละครั้ง จะใช้สูตร excel : RAND()  ซึ่ง จะคืนค่าตัวเลขที่สุ่มได้ระหว่าง มากกว่าหรือเท่ากับ 0 ถึงน้อยกว่า 1 โดยค่าที่ถูกสุ่มมีโอกาสเท่าๆกัน เราสามารถสั่งให้ excel สุ่มตัวเลขใหม่ได้โดยการกดปุ่ม F9 มาดูรูปแบบของ Worksheet ใน excel ก่อนทำการจำลองตัวแปรอิสระ ดังแสดงในภาพที่ 1

ภาพที่ 1 ค่าผกผันของตัวแปรอิสระ

3. สูตร excel สำหรับจำลองตัวแปรอิสระแต่ละตัวกำหนดได้ดังนี้ (เริ่มที่การจำลองครั้งที่ 1 แถวที่ 10)

  R : A10 = $B$2 + RAND()*($C$2 - $B$2)

  L : B10 = $B$3 + RAND()*($C$3 - $B$3)

  P : C10 = $B$4 + RAND()*($C$4 - $B$4)

  C : D10 = $B$5 + RAND()*($C$5 - $B$5)

4. สูตร excel สำหรับคำนวณผลกำไรจากการจำลองครั้งที่ 1

  F10 = $A10*$B10*$C10 - ($B$6 + $B10*$D10)

6. ผลการจำลองแสดงได้ดังภาพที่ 2

ภาพที่ 2 การจำลองผลกำไรจากการขายสินค้าครั้งที่ 1

7. ทำการสำเนาสูตร excel ในแถวที่ 10 ไปอีก 4,999 แถว (แถวที่ 5009 ) เพื่อจำลองผลกำไรให้ครบ 5,000 ครั้ง 

8. สังเกตุดูผลกำไรจะเปลี่ยนไปทุกครั้งที่มีการคำนวณใหม่ เนื่องจากสูตร excel: RAND (กดปุ่ม F9)

9. ดำเนินการเตรียมข้อมูลผลกำไรในช่วงข้อมูล F10:F5009 เพื่อเตรียมสร้างแผนภูมิฮิสโตแกรม ตามบทความ การใช้ excel สร้างแผนภูมิฮิสโตแกรม โดยใช้สูตร excel: FREQUENCY ได้รูปแบบของข้อมูลดังแสดงในภาพที่ 3

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

10. ฮิสโตแกรมผลกำไรที่คาดว่าจะเกิดขึ้น ดังภาพที่ 4

ภาพที่ 4 ฮิสโตแกรมผลกำไรจากการพยากรณ์ยอดขายสินค้าใหม่

11. หากกดปุ่ม F9 รูปร่างฮิสโตแกรมจะเปลี่ยนไป

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

การใช้ excel สร้างแผนภูมิฮิสโตแกรมแบบ dot plot

การใช้ excel ในบทความนี้ขอเสนอการสร้างแผนภูมิฮิสโตแกรมแบบ dot plot โดยสามารถประยุกต์ใช้กับแผนภูมิพาเรโตก็ได้ จุดเด่นของแผนภูมิแบบ dot plot คือไม่ต้องใช้ chart object ใน excel สำหรับการสร้างแผนภูมิดังกล่าว เหมาะสำหรับใช้วิเคราะห์ข้อมูลที่มีจำนวนไม่มากนักหรือต้องการทราบรูปร่างการกระจายของข้อมูล ขอยกตัวอย่างข้อมูลเดิมในการสร้างแผนภูมิฮิสโตแกรมนะครับ ผมขอข้ามขั้นตอนของการสร้างชั้นข้อมูลและการใช้สูตร excel :  FREQUENCY ไปเลย หากต้องการทราบรายละเอียดสามารถศึกษาได้จากบทความ การสร้างแผนภูมิฮิสโตแกรมด้วยสูตร excel : FREQUENCY ครับ รูปแบบใน worksheet หลังจากเตรียมข้อมูลเรียบร้อยแล้วเป็นไปตามภาพที่ 1 

ภาพที่ 1 รูปแบบข้อมูลใน worksheet

จากภาพที่ 1 เราจะแทนที่จำนวนความถี่ของข้อมูล (D11:D18) ด้วยจำนวนของสัญลักษณ์ * แทนโดยให้เรียงตัวตามแนวนอนในเซลล์ F เราจะใช้สูตร excel : REPT เพื่อสร้างสัญลักษณ์ * ตามความถี่ของแต่ละชั้นข้อมูล โดยเขียนสูตร excel ได้ดังนี้ครับ F11 = REPT("*",D11) เมื่อทำการสำเนาสูตร excel และนำไปวางในเซลล์ F12:F18 จะได้แผนภูมิแบบ dot plot ดังภาพที่ 2 (ผมได้ซ่อนหลัก D ไว้แล้ว)

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

ในทำนองเดียวกันหากเราต้องการทำแผนภูมิแบบแท่งก็สามารถทำได้เช่นกัน โดยเริ่มจากจัดรูปแบบของข้อมูล Bin และ Frequency ใหม่ดังภาพที่ 3 ซึ่งใช้วิธีการสำเนาและวางแบบพิเศษเพื่อสลับข้อมูลในแนวนอนและแนวดิ่ง (G20:N21)

ภาพที่ 3 รูปแบบข้อมูล

ในเซลล์แสดงผลให้ท่านผู้อ่านเขียนสูตร excel ดังนี้ G19 = REPT("*",G21) และทำการสำเนาสุตร excel ไปยังเซลล์ H19:N19 จะได้แผนภูมิฮิสโตแกรมแบบ dot plot ดังภาพที่ 5

เทคนิคที่สำคัญของการนำเสนอเป็นแบบแท่งคือการวางแนวของตัวอักษรในเซลล์ G19:N19 เป็นมุม 90 องศา ดังภาพที่ 4

ภาพที่ 4 การจัดรูปแบบเซลล์

ภาพที่ 5 ฮิสโตแกรมแบบแท่ง

จากที่นำเสนอวิธีการสร้างฮิสโตแกรมมาหลายวิธี หวังว่าท่านผู้อ่านจะนำวิธีการสร้างฮิสโตแกรมแต่ละวิธีไปใช้ให้เหมาะสมกับรูปแบบข้อมูล