Excel [지식인 해결 시리즈] 003. 데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

 

전체 목차

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)002. 공백 셀 제거 후 정렬

 

 

포스트 목차.

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

01. 문제

문제를 해석해보면,  사진1에 있는 데이터를 한 행씩 읽고, 적입수 단위에 맞게 행을 복사하여 데이터를 분할하는 작업을 원하는 것으로 보인다. 

 

02. 문제해결 방법

 

1) 엑셀 문서 구성

편의상 품번별로 적입수가 기입되어 있는 기준데이터를 Sheet1에 저장하고,  적입수/수량에 따른 분할 결과를 Sheet2에 저장합니다.

 

Sheet1: 기준 데이터

Sheet2: 결과 데이터

 

2) 복사할 행의 수(N) 구하기

Sheet1에 저장되어 있는 수량을 적입수로 나눠서 복사할 행의 수를 구해봅시다.

 

적입수 * X + 나머지 = 수량

 

X는 수량을 적입수로 나누는 연산의 해라고 볼 수 있습니다.

나머지 수량도 한행이 필요하기 때문에 총 필요한 행의 수 N은  X+1입니다.

 

예) 수량: 150, 적입수: 80

X=1,  나머지=70

필요한 행의 수 N=X+1 = 2

 

3) 수량 구하기

2)번 과정에서 필요한 행 N을 구했다. N행 중에서  n번째행에 들어갈 수량을 구하기 위해 다시 나누기 식을 이용해 보자.

 

적입수 * X + 나머지 = 수량

 

n=N인 경우)  수량 = 나머지

n<N인 경우)   수량 = 적입수

 

4) Sub 프로시저의 흐름

 - Sheet 1에 있는 데이터의 행의 수 확인

 - Sheet 1에 있는 데이터를 행 단위로 순회하며,  Sheet 2에 작성할 행의 수(N) 를 구하고, 데이터분할 수행 

 - 데이터 분할 과정에서 수량 열에 들어갈 수량 데이터 계산하여 작성

 - Sheet 2에서 추가로 데이터가 씌여질 행의 위치 저장

 - Sheet1에 있는 다음 행으로 이동하의 위의 과정 반복 수행

 

03. 예제 및 설명

 

Sheet1 과 Sheet2를 다음과 같이 준비한다.

 

 

Sheet1에서 나누기 작업을 할 D열을 기준으로, 데이터의 갯수를 알아보자.

 

다음과 같이 Rows.Count와 End(xlUp)을 이용하여 데이터의 수를 알 수 있따.

'Sheet1의 데이터의 행 수 확인

    Dim dataRowNum As Long

    dataRowNum = Worksheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row

 

VBA에서 나누기(/) 연산을 하면 연산결과를 저장할 변수의 데이터 형에 따라 결과가 다르게 나온다. 저장할 변수가 Integer일 경우 반올림한 결과가 들어가고, 변수형 미지정의 경우는 소수값이 결과로 저장된다. 여기서는 변수형 미지정을 사용하여 처리해보자. 소수값이 나온경우 우리는 내림 연산을 해야한다. 이 때 사용할 수 있는 것이 Int() 함수이다. Int함수는 인자로 입력된 숫자를 내림연산을 하여 정수로 변경해준다. 

 

우리는 나누기 연산의 나머지에 대해서도 알아야 하는데, Mod 연산자를 이용하면 나머지를 구할 수 있다.

 

자 이제 위에서 분석한 Sub 프로시저의 흐름에 맞게 코드를 작성해보자.

Sub DivideAndPaste()
    
    'Sheet1의 데이터의 행 수 확인
    Dim dataRowNum As Long
    dataRowNum = Worksheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
      
    'Sheet2에 쓰기가 수행될 행을 지시하는 변수 선언 및 2로 초기화 (1열은 제목)
    Dim writeRowPosition As Long
    writeRowPosition = 2
          
    'Sheet1의 데이터 순회하면 Sheet2 작성
    For rowItem = 2 To dataRowNum
        '수량 추출
        itemCountA = Worksheets("Sheet1").Cells(rowItem, 3).Value
        '적입수 추출
        divider = Worksheets("Sheet1").Cells(rowItem, 4).Value
        '적입수, 수량이 숫자인 경우, 적입수가 0이 아닌경우만 데이터 분할 수행
        If IsNumeric(itemCountA) And IsNumeric(divider) And divider <> 0 Then
            '분할할 열의 수 구하기
            Sol = Int(itemCountA / divider) + 1
            '나머지값 구하기
            Remainder = itemCountA Mod divider
            '분할할 열의 수만큼 반복하며 값 채우기
            For rowItemN = 1 To Sol
               '품번과 품명은 그대로 복사
               Worksheets("Sheet2").Cells(writeRowPosition, 1) = Worksheets("Sheet1").Cells(rowItem, 1)
               Worksheets("Sheet2").Cells(writeRowPosition, 2) = Worksheets("Sheet1").Cells(rowItem, 2)
               '맨 마지막 행일 경우 나머지 값으로 수량 입력
               If rowItemN = Sol Then
                    Worksheets("Sheet2").Cells(writeRowPosition, 3) = Remainder
               '맨 마지막 행이 아닐 경우 적입수로 수량 입력
               Else
                    Worksheets("Sheet2").Cells(writeRowPosition, 3) = divider
               End If
               'Sheet2에 쓰기가 수행될 행을 지시하는 변수 1증가
               writeRowPosition = writeRowPosition + 1
            Next rowItemN
        End If
    Next rowItem
 
End Sub

위의 Sub 프로시저를 실행한 결과는 다음과 같다.