ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • Excel 매크로 (VBA) - 005. 시트에 있는 데이터(셀) 순회하기
      Excel/Excel 매크로 2022. 8. 17. 22:19
      728x90

      전체 목차

       - 001. 특정 행을 복사하여 삽입하기

       - 002. 선택하여 붙여 넣기 기능 정리

       - 003. 변수/Assignment/Loop/비교

       - 004. 데이터가 있는 셀의 범위 알아내기

       - 005. 데이터 시트(쉘) 순회하기

       

      포스트 목차

        - 01. 해결해야 하는 문제 

        - 02. 필요한 도구 설명

        - 03. 매크로 만들기

       

      본 포스트에서는 간단한 문제를 통해, 시트에 있는 데이터를 조회하고, 조회한 데이터를 가공하여 시트의 다른 셀에 해당 내용을 작성하는 예제를 다루고자 한다. 003에서 다룬 루프문과 비교문을 실제로 사용하는 방법을 배우도록 해보자.

      https://eggdrop.tistory.com/8

       

      엑셀 매크로 (VBA) - 003. variable/assignment/branch/loop (변수/할당/비교/순환)

      전체 목차  - 001. 특정 행을 복사하여 삽입하기  - 002. 선택하여 붙여 넣기 기능 정리  - 003. 변수/Assignment/Loop/비교 포스트 목차  - 01. VBA에서의 변수(variable)  - 02. VBA에서 할당(Assingment)..

      eggdrop.tistory.com

       

      01. 해결해야 하는 문제.

      위의 그림에서처럼 시트의  A열과 B열에는 주문번호와 상품명에 대한 정보가 각각 채워져 있다. A열과 B열에 대한 데이터만 주어진 상황에서 매크로를 실행하면 C열~G열이 자동으로 채워지는 작업을 진행하고 싶다.  

       - C열: C열은 단순히 A열과 B열에 있는 주문번호와 상품명 사이에 공백을 하나 넣어 결합한 형태로 볼 수 있다.

       - D열: 주문번호에 ABC가 포함되어 있는 경우 TRUE, 아닌 경우 FALSE를 표기한다.

       - E열: 주문번호에 GEF가 포함되어 있는 경우 TRUE, 아닌 경우 FALSE를 표기한다.

       - F열: 주문번호에 YTE가 포함되어 있는 경우 TRUE, 아닌 경우 FALSE를 표기한다.

       - G열: A열에 있는 주문번호를 중복되지 않게 표기한다.

       

      02. 필요한 도구 설명

       - & 연산자: 엑셀의 수식에서처럼 &연산자를 사용하면 두 개의 String을 하나로 합치는 것이 가능하다.

       

       - InStr() 함수:  문자열에서 특정 문자열이 처음으로 나타나는 곳의 위치를 반환한다.   *함수의 개념에 대해서는 나중에 다시 다루도록 하자

         문법: InStr ( [start], string1, string2, [compare]) 

         start와 compare는 생략가능하다.

         - string1: 탐색의 대상이 되는 문자열

         - string2: 찾아내야 하는 문자열

         - start: string1 중에서 탐색을 시작할 시작 위치를 뜻한다. 

          -compare: 문자로(vbTextCompare) 비교할지, 문자에 대응하는 코드(vbBinaryCompare)로 비교할지를 정한다.

         string1의 start 위치에서 시작하여 string2가 처음으로 발견되는 위치를 반환한다.

         

       상황에 따라 InStr 함수가 반환하는 값은 다음과 같다.

      상황 반환값
      string1이 공백인 경우 0
      string1이 Null인 경우 Null
      string2가 공백인 경우 start
      string2가 Null인 경우 Null
      string2가 string1에 포함되지 않은 경우 0
      string2가 string1에 포함된 경우 시작 위치
      start > string1 0

      사용 예)

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      Sub testInStr()
          Dim string1, string2 As String
          Dim pos As Integer
          
          
          string1 = "ABC_001"
          string2 = "BC"
          pos = InStr(string1, string2)
          
          MsgBox (pos)
      End Sub
          
      		 cs

       

      ABC_001라는 문자열에서 BC라는 문자열이 포함되어 있는지 확인한다. BC는 ABC_001의 두번째 위치에서 시작하기 때문에 2를 반환해야 한다.

       

      실행결과

      - Array: 같은 형태의 변수를 여러 개 모아놓은 것으로 생각할 수 있다. Array를 구성하는 개별적인 변수를 element라고 하고, Array를 선언할 때 사용한 변수명과 인덱스를 이용해서 개별적인 변수에 접근하는 것이 가능하다.

       (1) 선언

       Dim 변수명(크기) As 변수형

       일반적인 변수를 선언할 때와의 차이점은 (크기)가 들어간다는 것이다. ReDim 키워드를 이용하여 크기를 나중에 재정의하는 것도 가능하다.

       ReDim 변수명(크기) 

       

       스트링 배열 선언의 예

       Dim arrStrOrderId(3) As String

        

       (2) 접근

       변수명(인덱스) 로 접근이 가능하다. 선언한 크기보다 인덱스 값이 크면 에러가 발생한다.

       

       스트링 배열 접근의 예

       arrStrOrderId(3) = "돼지" 

       MsgBox (arrStrOrderId(3))

        

      - 데이터 범위 알아오기 

         https://eggdrop.tistory.com/9

       

      Excel 매크로 (VBA) - 004. 데이터가 있는 셀의 범위 알아내기

      전체 목차  - 001. 특정 행을 복사하여 삽입하기  - 002. 선택하여 붙여 넣기 기능 정리  - 003. 변수/Assignment/Loop/비교  - 004. 데이터가 있는 셀의 범위 알아내기 포스트 목차  - 01. 엑셀에서 데이터.

      eggdrop.tistory.com

       

      03 매크로 만들기 

       이제 위에서 다룬 도구들을 이용하여 매크로를 작성해보자.

        

       

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      62
      63
      64
      65
      66
      67
      68
      69
      70
      71
      72
      73
      74
      75
      76
      77
      78
      79
      80
      81
      82
      83
      84
      Sub makeResult()
          '범위 알아오기 A열을 기준으로
          
          '데이터의 범위 지정을 위한 변수 선언
          Dim rowMax As Long
          Dim rowStart As Long
          '주문번호를 저장하기 위한 배열 선언
          Dim arrStrOrderId() As String
          
          '1행은 열이름이 들어가기 때문에 실제 데이터는 2행부터 시작
          rowStart = 2
          '주문번호가 있는 1열을 기준으로 데이터의 최대행수를 계산
          rowMax = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
          
          '배열에서 실제 데이터가 몇개 들어있는지를 저장하기 위한 변수 선언
          Dim arrMax As Integer
          arrMax = 0
          
          '확인작업을 위한 이진 변수 선언 후 거짓으로 초기화
          Dim checker As Boolean
          checker = False
          
          '조사할 데이터가 있는 경우에만 작업 수행    
          If rowMax > 0 Then
              '배열의 크기를 데이터의 수만큼 선언 (실제로 사용할 데이터의 수(arrMax)는 이 값보다 작다)
              ReDim arrStrOrderId(rowMax)
              
              '행을 하나씩 돌아가며 탐색 시작
              For i = rowStart To rowMax
                  checker = False
                  '위에서 생성한 배열을 순회하며 값비교 (처음에는 배열에 값이 없음)
                  For j = 0 To arrMax '유효한 데이터가 있는 영역까지만 비교 수행
                      '탐색하는 셀의 내용과 배열의 내용을 비교하여 같은 값이 있으면 checker를 True로 설정
                      If arrStrOrderId(j) = ActiveSheet.Cells(i, 1).Value Then
                          checker = True
                      End If
                  Next j
                        
                  '배열에 같은 값이 없는 경우 주문번호를 배열에 저장한다.
                  If checker = False Then
                      arrMax = arrMax + 1
                      arrStrOrderId(arrMax) = ActiveSheet.Cells(i, 1).Value
                  End If
              Next i
          End If
          
          '행을 하나씩 증가하며 데이터 읽기 시작
          For i = rowStart To rowMax
              'D E F열에 들어갈 데이터를 미리 선언 (varient 타입)
              Dim dCellValue, eCellValue, fCellValue
              
              'A, B열 데이터를 각각의 변수에 저장        
              aCellValue = ActiveSheet.Cells(i, 1).Value
              bCellValue = ActiveSheet.Cells(i, 2).Value
              'A열과 B열의 문자열을 합쳐서 C열에 들어갈 데이터 생성
              cCellValue = aCellValue & " " & bCellValue
              'C열의 데이터 변경
              ActiveSheet.Cells(i, 3).Value = cCellValue
              'D E F 열에 들어갈 데이터를 False로 초기화
              dCellValue = False
              eCellValue = False
              fCellValue = False
              '주문번호에 일치하는 문자열이 포함된 경우에만 True로 변경
              If InStr(aCellValue, "ABC") > 0 Then
                 dCellValue = True
              End If
              If InStr(aCellValue, "GEF") > 0 Then
                 eCellValue = True
              End If
              If InStr(aCellValue, "YTE") > 0 Then
                 fCellValue = True
              End If
              'D E F 열의 데이터 변경
              ActiveSheet.Cells(i, 4).Value = dCellValue
              ActiveSheet.Cells(i, 5).Value = eCellValue
              ActiveSheet.Cells(i, 6).Value = fCellValue
          Next i
          '위에서 생성한 배열을 순회하며 G열에 데이터 
          For i = 1 To arrMax
              ActiveSheet.Cells(i + 1, 7).Value = arrStrOrderId(i)
          Next i
          
      End Sub
       
      		 cs

       

      매크로 실행 전

       

      매크로 실행 후

       

      기존에 입력되어 있는 데이터를 순회하며, 요구되는 기준에 맞게 데이터를 가공(텍스트 병합, 텍스트 포함여부 조사, 중복데이터 제거)를 수행하는 매크로를 작성했다. 중복데이터가 없는 주문번호를 수집하는 과정에서 사용한 배열(Array)은 앞으로도 많이 활용될 예정이므로, 사용법을 익혀두도록 하자.

       

      728x90

      'Excel > Excel 매크로' 카테고리의 다른 글

      Excel 매크로 (VBA) - 007. 변수에 저장되어 있는 데이터 확인하기 (디버그 기능)  (0) 2022.08.21
      Excel 매크로 (VBA) - 006. 셀 병합 하기  (0) 2022.08.18
      Excel 매크로 (VBA) - 004. 데이터가 있는 셀의 범위 알아내기  (0) 2022.08.16
      엑셀 매크로 (VBA) - 003. variable/assignment/branch/loop (변수/할당/비교/순환)  (0) 2022.08.15
      엑셀 매크로 (VBA) - 002. 선택하여 붙여 넣기 기능 정리  (0) 2022.08.13
    Designed by Tistory.

    티스토리툴바