문제 상황
- 데이터: 물품 정보를 정리한 엑셀 표
- 작업 내용: 데이터 정렬 및 포맷팅, 수량 계산 후 정리
- 사용자 특징: 엑셀을 처음 사용해 봄
우선 데이터 양이 적지 않았다. 엑셀 고급 필터 등 기능을 활용해도 15분 이상 걸리는 작업이다. 그런데 대부분 엑셀을 모르는 상태에서 작업을 하기 때문에 시간이 매우 오래 걸렸다. 작업 중 실수도 종종 있었다. 그래서 데이터만 입력하면 자동으로 계산하는 매크로가 필요했다.
자세한 순서는 아래와 같다.
- 수량이 0인 항목 삭제
- 카테고리 기준 오름차순 정렬
- 카테고리가 같다면 최대 수량이 큰 쪽에 몰아주기. 단, 최대 수량을 넘지 않는 범위에서 몰아준다.
예시 (전):
| 고유번호 | 카테고리 | 최대 수량 | 보유 수량 |
| 001 | A | 50 | 30 |
| 002 | A | 40 | 40 |
예시 (후):
| 고유번호 | 카테고리 | 최대 수량 | 보유 수량 |
| 001 | A | 50 | 50 |
| 002 | A | 40 | 20 |
예시를 보면 A 물품은 총 70(30+40)개가 있다. 001은 최대 수량이 50개이므로 001에 50개를 준다. 그리고 남은 수량 20개는 002로 준다.
아이디어
핵심은 카테고리와 최대 수량이므로 문제 조건에 맞게 정렬한다. 1번 조건은 카테고리 기준 오름차순 정렬이고, 2번은 최대 수량 기준 내림차순 정렬이다.
그다음은 카테고리를 찾는다.
첫 행을 startPointer로 두고 한 행씩 확인하며 다른 카테고리가 나올 때까지 움직이다. 이때 카테고리의 총 보유 수량을 구하기 위해 보유수량을 partialSum에 더하며 누적한다.
다른 카테고리가 나오면 이전 행을 endPointer로 둔다. 이렇게 하면 같은 카테고리가 있는 범위와 총 보유 수량을 알게 된다.
이제 startPointer ~ endPointer를 돌며 총 개수(partialSum)를 나눠준다. 분배 방법은 아래 슈도 코드를 참고하자.
partialSum // 카테고리 총 수량
maxNum // 최대 수량
for startRow to endRow {
if partialSum == 0 {
// 이미 다 나누어줬을 때
Cells(row, col).Value = 0
} else if partialSum > maxNum {
// 총 수량이 최대 값을 넘을 때
Cells(row, col).Value = maxNum
partialSum -= maxNum
} else {
// 총 수량보다 최대 값이 크거나 같을 때
Cells(row, col).Value = partialSum
partialSum = 0
}
}이 동작을 반복하면 카테고리 별로 수량을 파악해 분배할 수 있다.
VBS 구현
데이터는 아래와 같다.
| 순번 | 고유번호 | 카테고리 | 최대 보유 수량 | 현 보유 수량 |
| B2:B32 | C3:C32 | D3:C32 | E3:E32 | F3:F32 |
VBS를 만들기 전 한셀이라는 점을 기억해야 한다. 한셀은 엑셀과 다르게 VBS 코드에서 일부 문법을 지원하지 않는다. 그 외 여러 제약이 있기 때문에 코드 작성에 주의해야 한다. 이러한 문제로 인해 중간에 strCol이라는 함수가 등장하는데, 이 함수는 열 번호를 알파벳으로 바꿔주는 사용자 정의 함수이다. 예를 들어, 입력이 1이면 'A', 3이면 'C'와 같은 식이다.
Function strCol(col) as String
'ASCII 코드를 이용해 숫자를 문자로 변환
'A == 65
strCol = Chr(col + 64)
End Function본격적으로 데이터를 옮기기 전, 옮길 자리를 청소해 준다.
Function clear(sheet, range)
Sheets(sheet).Range(range).ClearContents
End Function현 보유 수량이 0인 데이터를 제외하고 복사해 준다. 보유 수량이 0인 데이터는 최종 결과에 포함되지 않기 때문에 미리 지워준다.
Function copyFiltered(startRow, endRow, startCol, endCol, criterion)
Dim num as integer
index = startRow
For row = startRow To endRow
num = Sheets("Before").Range(strCol(criterion) & row)
If num > 0 Then
'0보다 큰 값만 복사
Sheets("After").Range(strCol(startCol) & index, strCol(endCol) & index).Value = _
Sheets("Before").Range(strCol(startCol) & row, strCol(endCol) & row).Value
index = index + 1
End If
Next
End Function데이터가 준비됐으면 카테고리를 오름차순, 최대 보유 수량은 내림차순으로 정렬해 준다.
Range(tableRange).Sort key1:=Range(strCol(labelCol)&startRow), order1:=xlAscending, _
key2:=Range(strCol(numCol)&startRow), order2:=xlDecending이제 본격적으로 For로 반복문을 돌며 같은 카테고리 범위를 찾고 reDistribute를 통해 값을 분배한다. 위에서 설명한 알고리즘을 그대로 코드로 옮겼다. 주의할 점은 중간에 currentLabel이 비어있는지 확인하는 부분이다. 이는 VBS 정렬의 특성을 활용한 것이다. 정렬을 한 뒤 값이 비어있는 열은 표 아래에 붙게 된다. 따라서 데이터가 비어있는 열이 등장하면 그 뒤로 데이터가 없다는 뜻이다. 시간 단축을 위해 해당 조건을 만나면 조기 종료한다.
'첫 행에 대한 정보
startPointer = startRow
prevLabel = Cells(startRow, labelCol).Value
partialSum = Cells(startRow, numCol).Value
For row = startRow+1 To endRow+1
currentLabel = Cells(row, labelCol)
If prevLabel <> currentLabel Then
'카테고리가 다를 때
endPointer = row - 1
Call reDistribute(startPointer, endPointer, partialSum, maxNumCol, numCol)
startPointer = row
partialSum = 0
End If
If currentLabel = "" Then
'데이터가 없으면
Exit For
End If
prevLabel = currentLabel
num = Cells(row, numCol).Value
partialSum = partialSum + num
NextreDistribute도 위에서 설명한 알고리즘과 완전히 동일하다. 위에서 봤던 슈도 코드를 이해했다면 바로 알아차릴 수 있을 것이다.
Function reDistribute(startPointer, endPointer, partialSum, criterion, dst)
Dim maxNum as integer
For pointer = startPointer To endPointer
maxNum = Cells(pointer, criterion).Value
If partialSum = 0 Then
Cells(pointer, dst).Value = 0
elseif partialSum > maxNum Then
Cells(pointer, dst).Value = maxNum
partialSum = partialSum - maxNum
else
Cells(pointer, dst).Value = partialSum
partialSum = 0
End If
Next
End Function그 외에도 데이터에 인덱스 번호를 붙이고 필요 없는 열을 숨기는 등 포맷을 다듬는 코드도 제작했다. 전체 코드는 Github/archive에서 확인할 수 있다.
매크로를 제작하며
실생활 문제를 해결하고 팀의 업무 효율을 높일 수 있어서 뿌듯했다. 엑셀을 전혀 모르는 팀원들이 쉽게 사용할 수 있도록 UX적인 부분을 조금 더 고민한 뒤 배포할 예정이다. 하지만 코드에 대해서는 약간 답답한 부분이 있었다.
VBS/VBA를 배운 적이 없어 기본 문법이 적힌 자료를 보며 한땀 한땀 만들었다. 분명 예제에는 가능하다고 하는데 한셀에서는 안 되는 기능이 있었다. 이러한 단점 때문에 더 깔끔하게 만들 수 있을 텐데 하는 아쉬움이 남는다. 또 VBS를 처음부터 차근차근 공부한 게 아니라 필요한 내용만 찾아가며 만들다 보니 '더 효율적인 방법이 있을까?', '더 VBS스러운 방법이 있을까?' 하는 의문도 남는다. 만약 외부 프로그래밍 언어를 사용할 수 있다면 차라리 CSV 파일로 변환해 Go나 Python으로 처리하는 게 낫지 않을까하는 생각도 든다.
전체 코드 보기: https://github.com/denev6/archive/blob/main/vbs/main.vbs