[엑셀연동]엑셀 데이터로 차트를 만들어 성적표 일괄 만들기

 

 

일단 파워포인트의 표(테이블) 데이터는 차트로 반영할 수 없기 때문에

엑셀에서 엑셀 데이터로 차트를 만드는 것이 효율적입니다.

​

그런데 엑셀 표는 파워포인트 표처럼 배경에 그라디언트를 주거나 다양한 효과를 적용하는 것이 어려운 것이 단점입니다.

​

두 가지를 합칠려면 엑셀파일을 따로 만들어서 개체로 파워포인트에 삽입하는 방법이 있겠으나

각 데이터의 차트를 일일이 만들어줘야 합니다.

​

이 때 VBA를 이용해서

파워포인트에서 엑셀의 데이터를 가져와서 표양식에 채우고 차트를 생성할 수 있습니다.

​

아래와 같은 샘플이 방향을 제시해줄 수 있겠습니다.

​

엑셀상에 만들어지는 엑셀 차트의 데이터 소스를 변경하고 실시간으로 반영된 차트를 그냥 복사해서

슬라이드에 붙여넣을 수 있습니다.

​

핵심을 예시로 보여드리겠습니다.

차트의 데이터소스를 변경하는 것은 매크로 녹화를 해보시면 알 수 있습니다.

​

 

예를 들어 엑셀에서 위와 같은 데이터를 이용해 아래 차트로 만들어 놓습니다.

 

매크로를 녹화해보면 

name1 학생의 데이터 소스는 계열이름이 Sheet1!$B$2, 값이 Sheet1!$C$2:$H$2 였는데

name2 학생의 데이터 소스는 계열이름이 Sheet1!$B$3, 값이 Sheet1!$C$3:$H$3 으로 바뀌게 됩니다.

  

결국 VBA로 2,3,4,...로 차트의 소스 범위의 숫자를 바꿔주면 차트가 바뀌게 됩니다.

 

이 변경된 차트를 복사해서 파워포인트 슬라이드에 붙이면 됩니다.

​

차트 막대의 색상을 바꾸거나 데이터 라벨을 추가하고 속성을 수정하는 것도

엑셀에서 매크로를 녹화해보면 코드를 알아낼 수 있습니다.

​

파워포인트에서는 기준 슬라이드를 만들어야 합니다.

 

이름이 들어갈 텍스트 상자의 이름은 Name 2 로 하고

성적이 들어갈 표의 이름은 Table 1 이고

5개과목과 평균까지 6개의 데이터를 엑셀에서 복사해와야 합니다.

엑셀에서 데이터와 차트를 가져오기 전에

기준 슬라이드를 복제해서 하나 더 만들고 나서

작업을 해야 합니다.

 

이름, 성적, 차트를 붙여넣는 작업을 학생1부터 학생10까지 순환하면서 처리하면 아래와 같이 슬라이드가 생성됩니다.

​

​

실제 코드는 아래와 같습니다.

 

도구 > 참조에서 Microsoft Excel x.0 Library 에 체크해야 합니다.

Option Explicit

Sub 매크로1()
 
    Dim xApp As New Excel.Application
    Dim xBook As Excel.Workbook
    Dim xSht As Excel.Worksheet
    Dim xShp As Excel.Shape
    Dim xCht As Excel.Chart
    Dim xRng As Excel.Range
    
    Dim prs As Presentation
    Dim sld As Slide, lastSld As Slide
    Dim shp As Shape
    Dim tbl As Table
    Dim r As Long, c As Integer
    Dim SW!, SH!
    
    Set prs = ActivePresentation
    On Error Resume Next
    Set xBook = xApp.Workbooks.Open(prs.Path & "\통합 문서1.xlsx")
    Set xSht = xBook.Worksheets(1)
    Set xShp = xSht.Shapes("Chart 1")
    Set xCht = xShp.Chart
    On Error GoTo 0
    If xCht Is Nothing Then MsgBox "엑셀파일 첫번째 시트에 차트가 없습니다.": Exit Sub
    
    With prs.PageSetup
        SW = .SlideWidth: SH = .SlideHeight
    End With
    
    '마지막 기준 슬라이드
    Set lastSld = prs.Slides(prs.Slides.Count)
    lastSld.SlideShowTransition.Hidden = msoTrue
    
    '2행부터 11행까지 즉, 1번학생부터 10번까지
    For r = 2 To 11
        
        '엑셀 차트 변경 후 복사
        xCht.FullSeriesCollection(1).Name = "=Sheet1!$B$" & r
        xCht.FullSeriesCollection(1).Values = "=Sheet1!$C$" & r & ":$H$" & r
        '마지막 막대차트 색상 변경
        With xCht.FullSeriesCollection(1).Points(6).Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent4
            '.ForeColor.RGB = rgbYellow
        End With
        xCht.SetElement 211  '(msoElementDataLabelCallout)
        With xCht.FullSeriesCollection(1).DataLabels
            .ShowCategoryName = False
            .Format.Fill.ForeColor.RGB = RGB(217, 217, 217)
            .Format.Fill.Transparency = 0.3
        End With
        xShp.Copy
        DoEvents
        
        '마지막 기준슬라이드 복제
        lastSld.Duplicate(1).MoveTo prs.Slides.Count
        Set sld = prs.Slides(prs.Slides.Count - 1)
        sld.SlideShowTransition.Hidden = msoFalse
        
        '이름
        sld.Shapes("Name 2").TextFrame.TextRange.Text = xSht.Range("B" & r).Text
        
        '과목 점수
        For c = 1 To 6
            sld.Shapes("Table 1").Table.Cell(2, c).Shape.TextFrame.TextRange.Text = _
                xSht.Cells(r, c + 2).Text
        Next c
        
        '차트
        Set shp = sld.Shapes.Paste(1)
        shp.LockAspectRatio = msoFalse
        shp.Height = SH / 2 - 20
        shp.Width = SW * (3 / 5)
        shp.Left = SW / 2 - shp.Width / 2
        shp.Top = SH / 2 + 10
        shp.Name = "Chart_" & xSht.Range("B" & r).Text
        
    Next r
    'xBook.Close
    'xApp.Quit
    'Set xApp = Nothing
End Sub

 

첨부한 엑셀파일과 pptm 매크로 파일 참고하세요.

​

​

통합 문서1.xlsx

0.02MB

 

ReportCard1.pptm

0.06MB

 

 

참고)

​

아래의 경우 엑셀 데이터로 직접 파워포인트에 차트를 생성하고 있습니다.

https://konahn.tistory.com/entry/xls2PPTChart

엑셀 데이터로 파워포인트 차트 일괄 생성

 

또한 파워포인트 표와 차트의 데이터를 연동시키는 VBA도 있습니다.

https://konahn.tistory.com/entry/UpdateChart

슬라이드의 테이블(표)과 차트의 데이터 연동시키기

 

 

관련: 지식인