[기초방] VBA 100제 #96 [데이터를 PIVOT 시키기 feat. ADODB]

 

VBA로 피벗 테이블 생성: ADODB 매크로 완벽 정복 🎯

1. 코드 설명 📝

이 매크로는 엑셀의 VBA에서 ADODB를 활용해 SQL 쿼리를 사용하고, 피벗 테이블과 유사한 형태로 데이터를 집계해 셀에 출력한다. `TRANSFORM` 쿼리를 통해 여러 과목의 점수를 이름별로 집계해주는 기능을 한다.

2. 코드 전체

Option Explicit

Sub Haja_Guid_Pivot()

    Dim StrGuid$: StrGuid = "{B691E011-1797-432E-907A-4D8C69339129}" ' = ADODB 참조
    
    On Error Resume Next
        ThisWorkbook.VBProject.References.AddFromGuid StrGuid, 0, 0 ' = 최신 버전의 ADODB 참조 추가
    On Error GoTo 0
    
    Call 기초방96 ' 데이터 집계 매크로 실행

End Sub

Sub 기초방96()

    Dim Rs As New ADODB.Recordset  ' ADODB 레코드셋 객체 생성
    Dim strPath$ ' 파일 경로 저장
    Dim strSQL$, strConn$ ' SQL 쿼리와 연결 문자열 변수
    Dim strYear&
    Dim i&
    Dim rngX As Range: Set rngX = [g3] ' G3 셀을 기준으로 출력

    strPath = ThisWorkbook.FullName ' 현재 워크북의 전체 경로를 가져옴
    
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & strPath & ";" & _
              "Extended Properties=Excel 12.0;" ' 엑셀 파일과 연결

    strSQL = " TRANSFORM SUM(점수) " & _
             " SELECT 이름 " & _
             " FROM [기초방96$B3:D20] " & _
             " GROUP BY 이름 " & _
             " PIVOT 과목" ' TRANSFORM 쿼리로 이름별 과목 점수 합산

    Rs.Open strSQL, strConn ' 쿼리 실행 후 레코드셋 열기
        
    ' 필드 이름 출력 (G3부터 시작)
    For i = 0 To Rs.Fields.Count - 1
        If i > 0 Then rngX(1, i + 1) = Rs.Fields(i).Name ' 필드 이름을 오른쪽으로 출력
    Next i

    Set rngX = rngX.Offset(1) ' 출력 시작 위치를 한 행 아래로 이동
    rngX.CopyFromRecordset Rs ' 레코드셋의 데이터를 셀에 복사

    Rs.Close ' 레코드셋 닫기
    Set Rs = Nothing ' 메모리 해제

End Sub
    

3. 코드 구간별 분석 📋

3.1 ADODB 라이브러리 참조 설정

Dim StrGuid$: StrGuid = "{B691E011-1797-432E-907A-4D8C69339129}" 
ThisWorkbook.VBProject.References.AddFromGuid StrGuid, 0, 0
    

이 코드는 ADODB 라이브러리의 최신 버전을 참조하도록 설정한다. 이 GUID를 통해 Excel VBA에서 외부 데이터베이스와 연결할 수 있는 ADODB 객체를 사용할 수 있다.

3.2 SQL 쿼리를 사용한 데이터 집계

strSQL = " TRANSFORM SUM(점수) " & _
         " SELECT 이름 " & _
         " FROM [기초방96$B3:D20] " & _
         " GROUP BY 이름 " & _
         " PIVOT 과목"
    

여기서는 엑셀 시트의 B3:D20 범위를 대상으로 TRANSFORM 쿼리를 사용해 이름별로 과목 점수를 집계한다. 각 과목은 열 형태로 변환(PIVOT)된다.

3.3 레코드셋 데이터를 엑셀에 출력

For i = 0 To Rs.Fields.Count - 1
    If i > 0 Then rngX(1, i + 1) = Rs.Fields(i).Name
Next i

rngX.CopyFromRecordset Rs
    

이 부분은 G3 셀부터 시작해 쿼리 결과의 필드 이름과 데이터를 출력하는 코드다. 필드 이름은 첫 번째 행에, 각 이름별 점수는 아래로 채워진다.

4. 실행 예시 및 예상 결과 🎬

아래와 같은 데이터를 가지고 있다고 가정해보자:

이름	과목	점수
홍길동	수학	85
홍길동	영어	90
김철수	수학	80
김철수	영어	88

실행 후 G3 셀부터 다음과 같이 출력된다:

이름	수학	영어
홍길동	85	90
김철수	80	88

5. 확장 기능 및 응용 예시 🚀

• 데이터 범위를 동적으로 설정해 더 큰 범위의 데이터를 집계하기
• 여러 시트의 데이터를 하나로 합쳐서 피벗 테이블 생성
• 조건부 쿼리(SQL WHERE 절)를 추가해 특정 과목만 필터링

6. 자주 발생하는 오류와 해결법 🛠️

• 라이브러리 참조 오류: ADODB 라이브러리가 참조되지 않는 경우, 매크로 설정에서 참조를 수동으로 추가한다.
• SQL 구문 오류: 필드 이름이나 시트 이름에 공백이 있을 경우 대괄호([])로 감싼다.
• 데이터 범위 오류: 쿼리 범위가 잘못되면 올바른 셀 주소를 지정해야 한다.

 

 

365 PIVOTBY 함수 설명 및 예제

=PIVOTBY 함수는 Microsoft 365 버전에서 새롭게 제공되는 함수로, 특정 데이터 범위를 집계하여 피벗 테이블과 유사한 요약 데이터를 생성할 수 있다. 피벗 테이블과 달리 수식으로 작성할 수 있어서 동적 분석에 유용하다.

📌 PIVOTBY 함수 구문 및 매개변수 분석

=PIVOTBY(행_레이블_범위, 열_레이블_범위, 값_범위, 집계_함수, 누락된_값_대체, 행_정렬, 열_정렬, 값_정렬)

매개변수	설명
행_레이블_범위	피벗 테이블의 행을 구성할 범위
열_레이블_범위	피벗 테이블의 열을 구성할 범위
값_범위	집계할 데이터 범위
집계_함수	집계에 사용할 함수 (예: SUM, AVERAGE, COUNT)
누락된_값_대체	빈 셀 또는 누락된 값이 있을 때 사용할 기본값
행_정렬, 열_정렬, 값_정렬	행·열·값의 정렬 방식 (0: 기본, 1: 오름차순, -1: 내림차순)

📋 매개변수별 기능 설명

• 행_레이블_범위 (B4:B20): 행 레이블로 사용할 범위. 예: "상품명" 또는 "지역".
• 열_레이블_범위 (C4:C20): 열 레이블로 사용할 범위. 예: "월별 데이터".
• 값_범위 (D4:D20): 집계할 데이터 범위. 예: 매출 금액, 수량.
• 집계_함수 (SUM): 데이터 집계 방식. 이 예제에서는 SUM으로 값들을 더한다.
• 누락된_값_대체 (0): 누락된 값을 0으로 처리.
• 행_정렬, 열_정렬, 값_정렬 (0): 기본 정렬 상태를 유지.

🎬 예제 데이터와 실행 결과

데이터 예제

상품명	월	매출
사과	1월	1000
바나나	1월	1500
사과	2월	2000
바나나	2월	1000
오렌지	1월	500
사과	1월	500
바나나	3월	3000

수식

=PIVOTBY(B4:B10, C4:C10, D4:D10, SUM, 0, 0, 0, 0)

실행 결과

상품명	1월	2월	3월
사과	1500	2000	0
바나나	1500	1000	3000
오렌지	500	0	0

✅ PIVOTBY 함수의 장점

• 동적 업데이트: 데이터가 변경되면 자동으로 결과가 업데이트된다.
• 피벗 테이블 대체 가능: 수식으로 다양한 데이터 요약 가능.
• 간편한 정렬 및 집계 함수 활용: 정렬과 집계 방식을 자유롭게 조정할 수 있다.

🛠️ 오류 해결 가이드

• #VALUE! 오류: 매개변수에 잘못된 범위가 입력되었는지 확인한다.
• #N/A 오류: 해당 행·열 조합에 데이터가 없을 때 발생. 이 경우 누락된_값_대체 매개변수를 활용해 해결 가능.

🚀 활용 팁 및 응용 예시

• 여러 값 집계: 매출 외에도 수량, 비용 등을 추가로 분석.
• 조건부 정렬: 오름차순·내림차순 정렬로 주요 데이터를 빠르게 파악.
• 조건부 서식: 생성된 테이블에 조건부 서식을 적용해 분석 결과 강조.

 

기초방96.xlsm

0.02MB