สร้างกราฟอัตโนมัติใน Excel ด้วย VBA

ได้ช่วยเพื่อนเขียนโค้ด VBA ใน MS Excel เพื่อสร้างกราฟอัตโนมัติ ขอบันทึกเตือนความจำแบบสั้นๆเก็บไว้

ข้อมูลที่ต้องการสร้างกราฟนั้นเป็นชุดข้อมูลที่เหมือนกัน สามารถประยุกต์ใช้ให้เหมาะสมกับงานอื่นๆได้ แกน X เป็นปีที่เก็บข้อมูล เช่น ปี 1990-2017 ส่วนแกน Y เป็นชุดข้อมูลที่แตกต่างกันตามสนใจ อาจจะมีเป็นร้อยชุดข้อมูลเลยก็ได้ ถ้าจะมานั่งทำทีละกราฟ ทีละชุดข้อมูล และกราฟแต่ละอันยังมีรูปแบบเหมือนกัน จะค่อนข้างเสียเวลามาก โค้ด VBA จึงพอจะช่วยลดเวลาในการทำงานลงได้บ้าง

ตัวอย่างกราฟและชุดข้อมูลตัวอย่าง

จะอธิบายตามโค้ดที่เขียนเลย

-เปิด VB editor ของ Excel ขึ้นมา แล้ว insert module ใน sheet ที่มีชุดข้อมูลที่ต้องการสร้างกราฟแล้ว จะจัดให้ข้อมูลวางแนว Row หรือ Column ก็ได้ แล้วค่อยไปกำหนดเองในโค้ด VBA

Option Explicit

Sub WRYChart()

'ประกาศชนิดของตัวแปร
Dim parameterNum As Integer
Dim co As ChartObject
Dim ct As Chart
Dim sc1 As SeriesCollection
Dim ser1 As Series
Dim LC As Long

'ให้สามารถสร้างกราฟตามชุดข้อมูลที่สนใจได้ จึงกำหนดหมายเลขกำกับแล้วอิงจากตัวเลขนั้นเพื่อสร้างกราฟ
parameterNum = InputBox("What parameter would you like to chart?")

'กำหนดหมายเลขของชุดข้อมูลไว้ เท่าไหร่ก็ได้ต้องครอบคลุมจำนวนชุดข้อมูลที่มี เช่น อันนี้มี 100 กราฟที่ต้องสร้าง
If parameterNum > 0 And parameterNum < 100 Then

'ตำแหน่ง(A10) cells ใน excel ที่อยากจะสร้างและวางกราฟลงไป ชื่อและขนาดของกราฟ ในที่นี้มีหลายกราฟ จึงเลือก column ท้ายสุดของข้อมูล และ(offset)เลื่อนลง ตามลำดับชุดข้อมูล 
Set co = Sheet3.ChartObjects.Add(Range("A10").Offset(parameterNum, 1).Left, Range("A10").Offset(parameterNum, 1).Top, 450, 200) 'Chart location
co.Name = "parameter number" & parameterNum & "Chart"

'ใส่รายละเอียดของกราฟที่อยากได้ ชื่อกราฟ รายละเอียดของแกน x,y
Set ct = co.Chart
With ct
.HasLegend = True
.HasTitle = True
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Jahr" 'กำหนดป้ายของแกน x โดยเขียนเองเป็นข้อความ
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Range("F3").Offset(parameterNum, 0).Value 'กำหนดป้ายของแกน y กำหนดให้เปลี่ยนตามข้อมูลใน cells ที่กำหนดไว้
.Axes(xlCategory).CategoryType = xlTimeScale 'ชนิดของข้อมูล
.Axes(xlCategory).BaseUnitIsAuto = True
.Axes(xlCategory).MajorUnit = 2 'กำหนดการแบ่งหน่วย
.Axes(xlCategory).TickLabels.Orientation = xlTickLabelOrientationUpward 'กำหนดการวางตัวป้าย

.ChartTitle.Text = Range("G3").Offset(parameterNum, 0).Value 'กำหนดชื่อ ให้เปลี่ยนตามข้อมูลใน cells (เริ่มที่ G3 เลื่อนตามหมายเลขเลือก) ที่กำหนดไว้
Set sc1 = .SeriesCollection 
Set ser1 = sc1.NewSeries

'รายละเอียดข้อมูลของกราฟที่จะสร้าง
With ser1
.Name = Range("G3").Offset(parameterNum, 0).Value 'ชื่อของข้อมูล
.XValues = Range(Range("G3").Offset(0, 1), Range("G3").End(xlToRight)) 'ชุดข้อมูลของแกน x (เลือกที่ตำแหน่ง G3 จนถึงตำแหน่งขาวสุด)
.Values = Range(Range("H3").Offset(parameterNum, 0), Range("L3").Offset(parameterNum, 0)) 'ชุดข้อมูลของแกน x (เลือกที่ตำแหน่ง H3 จนถึง L3)
.ChartType = xlXYScatterSmoothNoMarkers 'ชนิดของกราฟ
.Trendlines.Add(Type:=xlLinear, DisplayRSquared:=True).Select 'เพิ่มเติม การใส่ Trendline และค่า R Square ของเส้น

End With

End With

MsgBox ("That's Perfect!") 'แจ้งเตือนเมื่อกราฟสร้างเสร็จ

Else: MsgBox ("You must enter a parameter number between 1 and 100") 'แจ้งเตือนเมื่อใส่ตัวเลขผิดพลาด
End If

End Sub

ปล. กำหนดชุดข้อมูลของ x, y สามารถกำหนดในรูปแบบนี้ได้เช่นกัน

.Values = Range(Range("G3").Offset(parameterNum, 1), Range("G3").Offset(parameterNum, 1).End(xlToRight))

ข้อดีคือ สามารถเพิ่มชุดข้อมูลต่อไปได้เรื่อยๆ เพราะ End(xlToRight) จะวิ่งคลุมถึงตัวสุดท้ายของข้อมูล
ข้อเสียคือ ถ้าชุดข้อมูลไม่ต่อเนื่องมีขาดหรือหายไปในบาง cells มันจะไม่สามารถดึงข้อมูลทั้งหมดมาได้ ถ้าหากเป็นแบบนี้ต้องใช้การกำหนดระยะของขุดจ้อมูลเองดังตัวอย่างด้านบน

.Values = Range(Range("H3").Offset(parameterNum, 0), Range("AL3").Offset(parameterNum, 0))

ภาพประกอบอื่นๆ

การเรียกใช้ VB และ Macro ใน Excel
ตัวอย่างการใช้งาน
ตัวอย่างการใช้งาน

วิธีเปลี่ยนรูปแบบการอ้างอิงเซลล์แบบ R1C1 เป็น A1 ใน Excel

ในสัปดาห์นี้ได้มีโอกาสได้ใช้ Excel เยอะพอสมควร แต่ใช้ Office for Mac น่าตาการใช้งานเป็นแบบ Ribbon ก็จริงแต่มันไม่คุ้นเคยเหมือนอยู่ใน Windows ต้องพยายามใช้อยู่หลายวันค่อยหาเครื่องมือที่ใช้ประจำเจอ โดยเฉพาะวันนี้ได้ความรู้ใหม่เพิ่มขึ้นมาอีกอย่างซึ่งต้องขอบคุณเพื่อนใน Twitter ที่หลายครั้งๆที่เราบ่นออกไป มักจะมีคนเก่งๆมาช่วยเราเสมอ ความรู้ใหม่ครั้งนี้ขอขอบคุณ @ds2kGTS

เรื่องมีอยู่ว่าใน Excel ใน Mac ค่าเริ่มต้นของรูปแบบการอ้างอิงตำแหน่งจะเป็น R1C1 คือ มันจะอ้างอิงเซลล์โดยอิงจากเซลล์ที่เรากำลังทำงานอยู่อีกที โดยจะให้ตำแหน่งที่เราอยู่เป็น R[0]C[0] จุดที่เราจะอ้างถึงต่ำลงไปค่า R จะเป็นบวก ถ้าสูงขึ้นจะเป็นลบ ส่วนค่า C ไปทางขวาจะเป็นบวก ทางซ้ายจะเป็นลบ เอาง่ายๆเลยเหมือนการระบุตำแหน่งแบบ x,y สี่ช่อง แต่ให้ค่า y กลับกันให้บวกอยู่ล่าง ดูรูปแล้วจะเข้าใจ (แต่เวลาใช้งงมาก!)

การอ้างอิงเซลล์แบบ R1C1

ตอนแรกคิดว่าเป็นการอ้างอิงเฉพาะใน Excel for Mac เลยใช้ไปเรื่อยๆ จนเมื่อต้องมาเริ่มมีการใส่สูตร วิเคราะห์ข้อมูล มันเริ่มงง และดูไม่รู้ว่าเอาข้อมูลตรงไหนมาคิด แต่ก็มารู้ภายหลังว่ามันเป็นแค่รูปแบบหนึ่งของการตั้งค่าการอ้างอิงเซลล์(Reference Style)ของโปรแกรมพวก Spreadsheet เราสามารถที่จะเลือกให้เป็นแบบ A1 อย่างที่ตัวเองใช้ประจำ ซึ่งจะระบุตำแหน่งแบบโดยรวมไม่ได้อ้างอิงตามจุดที่ทำงานอย่างเช่น R1C1

จุดสังเกตง่ายๆ ถ้าตั้งเป็นแบบ R1C1 ชื่อของคอมลัมน์จะเป็นตัวเลข ส่วนแบบ A1 จะเป็นตัวอักษร

การตั้งค่ารูปแบบเซลล์ R1C1 และ A1 (คลิกดูรูปขนาดใหญ่)

แต่ทั้งสองรูปแบบสามารถตั้งค่าได้ว่าจะใช้งานแบบไหน วิธีการสลับรูปแบบระหว่าง R1C1 กับ A1 มีวิธีดังนี้ครับ

วิธีการตั้งค่ารูปแบบการอ้างอิงเซลล์ใน Excel 2010 for Mac

1. คลิก Excel >>Preference

Preferences

2. เลือก General

คลิก General

3. ในหน้าของการตั้งค่า ให้เอาเครื่องหมายติ๊กหน้าคำว่า Use R1C1 reference style ออก ถ้าต้องการใช้งานแบบ A1 ถ้าต้องการใช้ R1C1 ก็ติ๊กเลือก

ส่วนการตั้งค่า

4. ในที่นี้ ผมต้องการใช้แบบ A1 เลยเอาเครื่องหมายออก แล้วคลิก OK ไป

เลือกใช้งานแบบ A1

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

สำหรับใน Windows ก็มีขั้นตอนการเปลี่ยนรูปแบบคล้ายๆกัน ดังนี้ครับ

วิธีการตั้งค่ารูปแบบการอ้างอิงเซลล์ใน Excel 2007

คลิกปุ่ม Excel  เลือก Excel Options >>Formulas tab เลื่อนไปในส่วนของ Working with formulas

R1C1 reference style

เอาเครื่องหมายออก ก็ใช้งาน A1 style ได้แล้วครับ

ความจริงบล็อกนี้จะเขียนแบบสั้นๆก็ได้นะ แค่อธิบายว่าคลิกตรงไหน เลือกตรงไหน แต่ตอนที่ผมค้นหาวิธีการตั้งค่าแบบนี้ ไม่รู้ทำไมหายากจัง ส่วนใหญ่บอกใน Windows ซึ่งมันจะเข้าไปตั้งค่าใน Excel Options ใน Mac จะเรียกว่า Preferences เลยงมหาไม่เจอสักที ก็เลยเก็บกดเขียนให้มันละเอียดแบบนี้เลยได้ไหม (ผมโง่ครับ!) คนอื่นๆจะได้ทำตามได้ง่ายๆ หวังว่าจะมีประโยชน์นะครับ

ข้อมูลจาก https://www.lytebyte.com