[기초방] VBA 100제 #97 [시트 요약하기]

 

📊 VBA와 ADODB로 동적 데이터 계산 자동화

이 포스팅에서는 VBA와 ADODB를 활용해 엑셀 시트의 데이터를 SQL 쿼리로 처리하고, 과목별 평균 점수와 응시자 수를 계산하는 방법을 소개한다.

 

Option Explicit
Sub 기초방97()
    
    Dim strConn As String
    Dim Rs As New ADODB.Recordset
    Dim ws As Worksheet
    Dim 월, 과목, 결과
    Dim i&, j&
    Dim strSQL$, strPath$
    Dim 테이블범위$, 시트명$
    Dim 평균값@, 응시자수&
    
    
    ' 현재 워크북의 전체 경로를 가져옴
    strPath = ThisWorkbook.FullName
    
    ' 통합 시트에서 월과 과목 목록을 배열로 가져오기
    월 = Sheets("통합").[e4:h4]
    과목 = Sheets("통합").[d5:d8]
    
    ' 결과 배열 초기화
    ReDim 결과(LBound(과목, 1) To UBound(과목, 1), LBound(월, 2) To UBound(월, 2))
    
    ' ADODB 연결 문자열 설정
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & strPath & ";" & _
              "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"
    
    ' 각 과목과 월에 대해 반복
    For i = LBound(과목, 1) To UBound(과목, 1)
        For j = LBound(월, 2) To UBound(월, 2)
            ' 시트명 설정
            시트명 = 월(1, j) & "$"
            
            ' SQL 쿼리 작성
            strSQL = "SELECT AVG([점수]) AS 평균값, COUNT([과목]) AS 응시자수 " & _
                     "FROM [" & 시트명 & "B2:E120] " & _
                     "WHERE [과목] = '" & 과목(i, 1) & "'"
            
            ' Recordset 열기
            Rs.Open strSQL, strConn
            
            ' 데이터 처리
            If Not Rs.EOF Then
                평균값 = Rs.Fields("평균값").Value
                응시자수 = Rs.Fields("응시자수").Value
            Else
                평균값 = 0
                응시자수 = 0
            End If
            
            ' 결과 저장
            결과(i, j) = Format(평균값, "0.0") & " (" & 응시자수 & ")"
            
            ' Recordset 닫기
            Rs.Close
        Next j
    Next i
    
    ' 결과 출력
    Sheets("통합").[e5].Resize(UBound(과목, 1), UBound(월, 2)).Value = 결과
    
 
    Set Rs = Nothing
    

End Sub

1️⃣ 변수 선언 및 초기화

먼저, VBA 코드에서 필요한 변수들을 선언하고 초기화한다. 이 과정에서 SQL 쿼리 결과를 담을 Recordset 객체와 경로, 시트명 등의 문자열 변수도 정의한다.

Option Explicit  
' 명시적 변수 선언을 강제해 실수를 방지함

Dim strConn As String                  ' ADODB 연결 문자열
Dim Rs As New ADODB.Recordset          ' SQL 쿼리 결과를 담는 Recordset
Dim ws As Worksheet                    ' 엑셀 워크시트 참조
Dim 월, 과목, 결과                     ' 월과 과목 배열 및 결과 저장 변수
Dim i&, j&                             ' 반복문 인덱스 변수
Dim strSQL$, strPath$                  ' SQL 쿼리 및 파일 경로
Dim 테이블범위$, 시트명$               ' 참조할 테이블 범위와 시트명
Dim 평균값@, 응시자수&                 ' 평균값과 응시자 수 변수

2️⃣ 현재 파일 경로와 데이터 불러오기

현재 워크북의 경로를 가져와 ADODB 연결에 사용하고, 통합 시트에서 월과 과목 목록을 배열로 불러온다. 이 배열을 이용해 각 시트에서 데이터를 분석한다.

' 현재 파일의 전체 경로를 가져옴
strPath = ThisWorkbook.FullName

' 통합 시트에서 월과 과목 목록을 배열로 가져옴
월 = Sheets("통합").[e4:h4]
과목 = Sheets("통합").[d5:d8]

3️⃣ 결과 배열 초기화

과목과 월의 수에 맞게 결과 배열을 초기화한다. 이 배열은 각 과목별 월별 계산된 평균 점수와 응시자 수를 저장하는 데 사용된다.

ReDim 결과(LBound(과목, 1) To UBound(과목, 1), _
           LBound(월, 2) To UBound(월, 2))

4️⃣ ADODB 연결 문자열 설정

엑셀 파일을 데이터베이스처럼 사용하기 위해 ADODB 연결 문자열을 설정한다. IMEX=1로 설정해 데이터를 텍스트 형식으로 처리한다.

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & strPath & ";" & _
          "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"

5️⃣ 반복문을 통해 SQL 쿼리 실행

모든 과목과 월을 순회하며 SQL 쿼리를 실행한다. 이 과정에서 월별 시트에서 해당 과목의 평균 점수와 응시자 수를 계산한다.

For i = LBound(과목, 1) To UBound(과목, 1)
    For j = LBound(월, 2) To UBound(월, 2)

        ' 시트명 설정
        시트명 = 월(1, j) & "$"

        ' SQL 쿼리 작성
        strSQL = "SELECT AVG([점수]) AS 평균값, " & _
                 "COUNT([과목]) AS 응시자수 " & _
                 "FROM [" & 시트명 & "B2:E120] " & _
                 "WHERE [과목] = '" & 과목(i, 1) & "'"

        ' SQL 쿼리 실행 및 Recordset에 저장
        Rs.Open strSQL, strConn

        ' 데이터 처리 및 배열에 저장
        If Not Rs.EOF Then
            평균값 = Rs.Fields("평균값").Value
            응시자수 = Rs.Fields("응시자수").Value
        Else
            평균값 = 0
            응시자수 = 0
        End If

        결과(i, j) = Format(평균값, "0.0") & " (" & 응시자수 & ")"

        ' Recordset 닫기
        Rs.Close
    Next j
Next i

6️⃣ 결과 출력 및 객체 해제

계산된 결과를 통합 시트에 출력한 후, 사용한 객체를 해제해 메모리 누수를 방지한다.

' 결과를 통합 시트에 출력
Sheets("통합").[e5].Resize(UBound(과목, 1), UBound(월, 2)).Value = 결과

' Recordset 객체 해제
Set Rs = Nothing

📝 실행 결과

이 코드를 실행하면 통합 시트에 다음과 같은 결과가 표시된다.

과목 \ 월	1월	2월	3월	4월
수학	85.0 (10)	87.5 (8)	90.0 (12)	92.0 (15)
과학	78.5 (9)	82.0 (7)	85.5 (6)	88.0 (10)

 

 

 

 

🎨 엑셀 LET와 MAKEARRAY로 동적 데이터 요약 자동화

엑셀의 LET 함수와 MAKEARRAY 함수를 활용해 여러 시트의 데이터를 동적으로 참조하고, 과목별 평균값과 데이터 개수를 계산해보자

📋 전체코드 소개

=LET(
  → 월, "처리할 월 목록 범위",
    월, K4:N4,
  → 과목, "처리할 과목 목록 범위",
    과목, J5:J8,                                

    MAKEARRAY(
        → 배열생성, "과목의 행 수와 월의 열 수만큼 배열 생성",
        ROWS(과목), COLUMNS(월),                
      
        → 테이블계산, "각 과목과 월에 맞게 동적 계산 수행",
        LAMBDA(R, C,                            
            LET(
              → 시트명, "월의 이름을 바탕으로 참조할 시트명 생성",
                시트명, "'" & INDEX(월, C) & "'!",    
                
              → 테이블범위, "시트 내 B3:E120 범위 참조  ",
                테이블범위, INDIRECT(시트명 & "$B$3:$E$120"), 
                
              → 과목범위, "테이블에서 3열(과목) 데이터 추출",
                과목범위, INDEX(테이블범위, , 3),    
                
              → 값범위, "테이블에서 4열(점수) 데이터 추출",
                값범위, INDEX(테이블범위, , 4),      

              → 필터된데이터, "해당 과목의 데이터만 필터링",
                필터된데이터, FILTER(값범위, 과목범위 = INDEX(과목, R)), 
                

              → 결과, "필터링된 데이터의 평균값과 개수를 형식화해 반환",
                결과, TEXT(AVERAGE(필터된데이터), "00.0") & " (" & COUNTA(필터된데이터) & ")",
                

                결과                               
            )
        )
    )
)

🔍 코드 분석 및 설명

• 월과 과목 범위 설정: K4:N4 범위에서 월별 시트명을, J5:J8 범위에서 과목명을 가져온다.
• MAKEARRAY로 배열 생성: 과목의 행 수와 월의 열 수만큼 배열을 생성하고, 각 셀 위치에 맞는 데이터를 계산한다.
• 동적 시트 참조: 각 월에 해당하는 시트명을 생성한 후 B3:E120 범위를 참조한다.
• 데이터 필터링과 계산: FILTER 함수로 선택한 과목의 데이터만 필터링하고, AVERAGE와 COUNTA로 평균값과 데이터 개수를 계산한다.
• 최종 결과 반환: 계산된 평균값과 데이터 개수를 형식화해 반환한다. 예: "85.0 (5)"

📝 실행 결과

이 코드를 실행하면 월별로 각 과목의 평균 점수와 데이터 개수가 다음과 같이 출력된다.

과목 \ 월	1월	2월	3월	4월
수학	85.0 (5)	90.5 (6)	88.0 (4)	92.0 (5)
과학	78.5 (4)	83.0 (5)	85.0 (3)	80.5 (4)
영어	92.0 (5)	89.5 (6)	90.0 (5)	87.0 (4)
사회	88.5 (3)	91.0 (4)	87.5 (5)	90.0 (4)

🚀 확장 및 응용

• 다양한 조건 추가: 학년이나 반과 같은 조건을 추가해 더 정교한 필터링이 가능하다.
• 동적 범위 설정: 데이터 범위를 고정 대신 동적으로 설정해 더 많은 데이터를 처리할 수 있다.
• 데이터 시각화: 계산된 데이터를 차트로 시각화해 더 직관적인 분석이 가능하다.

 

기초방97.xlsm

0.05MB