단순 반복작업 자동화: 엑셀 매크로 활용 방법과 팁 #6/8 (엑셀 함수와 매크로 동시 사용)

엑셀 매크로 프로그래밍을 효율적으로 활용하기 위해서는 기본적인 함수와 매크로를 함께 사용하는 것이 필요합니다. 이번 편에서는 엑셀에서 제공하는 함수와 매크로를 함께 활용하는 방법을 실제 예제를 통해 자세히 알아보겠습니다.

단순 반복작업 자동화: 엑셀 매크로 활용 방법과 팁 #6/8 (엑셀 함수와 매크로 동시 사용)

엑셀 매크로의 장점은 엑셀상에서 동작하는 VBA 언어라는 것입니다. 이 말은 곧 엑셀의 파워풀한 함수를 그대로 사용하면서 필요한 부분만 매크로를 통해 자동화하면 된다는 것을 의미합니다.

하나에서 열까지 모든 것을 매크로를 통해 해결할 필요가 없습니다. 이미 있는 것으로 쉽게 해결될 것을 매크로로 해결하겠다고 고집 부리면 안됩니다.

나도 엑셀 매크로 프로그램을 개발할 때 최대한 엑셀에서 제공하는 함수나 피벗과 같은 기능을 사용하고 부족한 부분만을 매크로를 통해 해결하는 방법을 쓰고 있습니다.

목차

1. 엑셀 피벗과 매크로

2. 엑셀 함수와 매크로

엑셀 피벗과 매크로

아래의 그림은 엑셀 함수와 매크로를 동시에 사용하는 예를 보여주기 위해서 첫편에서 처음에 “매크로는 언제 쓸까?” 에서 사용했던 데이터에 아래와 같이 “월”과 “처리여부” Column을 추가한 후 피벗을 사용하여 월별 매출액을 볼 수 있도록 변경한 예제입니다.

매월 매출 및 원가/판관비 데이터가 계속 추가된다고 가정할 때 사용자는 매월 신규로 생성된 데이터를 마지막 행에 복사 붙여넣기 하고 매크로를 실행하여 데이터를 가공한 후, 피벗을 “새로 고침” 해야 월별 매출/원가/판관비에 대한 집계를 볼 수 있습니다.

(여러분이 잘 알다시피 피벗은 “새로 고침”을 하지 않는 한 추가로 반영된 데이터가 반영되지 않습니다.)

그렇다면 매크로 프로그램에서 피벗의 “새로 고침”까지 해 줄 수 있다면 내가 굳이 마우스를 움직여 피벗을 “새로 고침”할 필요가 없지 않을까? 라는 생각이 들지 않나요? 이렇게 하면 사용자가 별도로 피벗을 “새로 고침”할 필요가 없기 때문에 편리할 뿐 아니라, 피벗을 “새로 고침” 하지 않아 발생할 수 있는 데이터 오류도 제거할 수 있을 것입니다. (나도 엑셀을 사용하여 데이터를 처리하면서 깜박하고 피벗을 “새로 고침” 하지 않는 경우가 종종 있습니다.)

피벗을 매크로에서 자동으로 “새로 고침” 하기 위해서는 먼저 피벗의 이름을 알아야 합니다. 이를 위해서는 아래와 같이 피벗 위에서 마우스 오른쪽 키를 누른 후 나오는 메뉴에서 “피벗 테이블 옵션”을 누릅니다.

아래 그림은 “피벗 테이블 옵션”을 누른 후 나오는 창인데 여기서 피벗 이름을 확인하고 수정할 수 있습니다.

나는 피벗 이름을 “월별집계”로 수정하여 사용하도록 하겠습니다.

이제 피벗을 자동으로 “새로 고침”할 모든 준비가 끝났습니다. 아래는 피벗을 자동으로 새로 고침하는 코드를 추가한 소스 코드이다.

☞ 매크로 실행 전

☞ 매크로 실행 후

매크로를 사용하지 않고 위와 동일한 결과를 얻기 위해서는 사용자가 수작업으로 ① 프로젝트 명을 변경하고, ② 마이너스로 된 금액을 플러스로 수정한 후, ③ 프로젝트명에 해외가 포함된 용역매출이 있을 경우 서비스명에 “해외서비스”를 추가해야 했습니다. ④ 그렇게 신규로 추가된 11건의 2월 데이터를 모두 수정한 후 ⑤ 피벗을 “새로 고침”해야만 위의 결과를 얻을 수 있었습니다.

반면 매크로를 사용할 경우 버튼 클릭 한번으로 이 모든 것을 순식간에 처리할 수 있으니, 매크로의 위력이 느껴지실 겁니다.

평소 엑셀 작업을 하면서 불편하게 느꼈던 부분이 많은 분들이라면 매크로를 통해서 상당 부분 해결할 수 있을 것입니다.

엑셀 함수와 매크로

앞의 예제에서는 매크로와 피벗만을 사용하여 문제를 해결하였는데 함수를 사용해서 조금 변경해 보도록 하겠습니다.

피벗과 매크로에서 제공한 소스 중에서 가장 복잡한 부분은 아마도 아래 부분일 것입니다.

이 부분을 엑셀 함수를 사용해서 동일하게 처리하려면 G열(서비스명)에 아래와 같이 입력하면 됩니다.

이 둘 중 어느 것이 덜 복잡하고 편리할까요? 선택은 여러분이 하면 되지만 여기서는 위의 엑셀 수식을 사용하는 것으로 매크로 소스를 수정해 보도록 하겠습니다.

위의 엑셀 수식을 사용하는 방법은 세가지 방법이 있다.

①수식을 직접 월말결산 시트의 G열에 모두 복사하는 방법

②수식을 다른 시트의 G열에 정의해 놓고 매크로에서 해당 셀을 복사 붙여넣기 하는 방법

③매크로에 직접 해당 수식을 문자열로 정의한 후 셀에 넣어주는 방법

이중에서 ①번은 엑셀을 사용하는 분들이라면 설명이 필요 없을 것 같아, ②과 ③번에 대해서만 설명하도록 하겠습니다.

위의 그림을 보면 월말결산 시트 옆에 적용수식이라는 시트가 있고, 적용수식 시트의 G2에 엑셀 함수를 사용한 수식이 들어가 있습니다. 적용수식 시트의 G2 셀을 월말결산 시트의 G열에 복사해 주면 ②번을 구현할 수 있으며, 아래 소스의 빨간색 박스 부분을 참고하면 됩니다.

③번의 경우에는 위의 빨간색 테두리 부분을 아래와 같이 수정하면 됩니다.

생각했던 것보다 조금 복잡하다고 느낄 수도 있을 것 같습니다. ②번은 1줄이었는데 ③번은 4줄이 되었으니 말입니다.

코드를 한줄씩 분석해 보도록 하겠습니다.

우선 첫째줄을 보면 원본 엑셀 수식을 그대로 사용한 것이 아니라, “ ”(큰 따옴표)를 모두 ‘ ‘(작은 따옴표)”로 바꾸었습니다. 왜 그렇게 했을까요? 이유는 단순합니다.

엑셀에서 문자열은 “ “와 같이 큰 따옴표로 묶어야 합니다. 그런데 문자열 안에 “매출”과 같은 큰 따옴표가 또 있다면 제대로 인식을 하지 못하고 에러가 발생합니다. 그래서 문자열 안에 큰 따옴표를 써야 한다면 “”와 같이 두개를 연달아 사용해야 합니다.

즉, fstr = “=IFERROR(IF(AND(B2=””매출””, C2=””용역매출””, D2=””C팀”” ~~ 와 같이 큰 따옴표를 사용해야 하기 때문에 복잡해질 수밖에 없습니다. 그래서 문자열 내에 있는 큰 따옴표를 작은 따옴표로 바꾸고 Replace 함수를 사용해서 작은 따옴표를 큰 따옴표로 한번에 바꾸어 주면 쉽고 간단하게 해결할 수 있습니다.

그런데 코드를 보면 “”” 이 아니라 Chr(34)로 되어 있습니다. 이건 또 왜 그럴까요? 엑셀에서 “””를 쓰면 중간의 큰 따옴표를 인식하지 못하고 에러가 발생합니다. 그래서 큰 따옴표의 아스키 코드인 34를 Chr 함수의 인수로 넣어주면 해당하는 문자를 반환해 주기 때문에 해결 가능합니다. (컴퓨터는 문자를 숫자로 인식하는데 각 문자에 대응하는 숫자를 아스키 코드(ASCII code) 라고 하며, ‘A’는 아스키코드로 65입니다. 그래서 Chr(65)를 호출하면 ‘A’가 반환됩니다.)

다음으로 fstr = Replace(fstr, "2", irow) 은 왜 필요할까요? 우리는 지금 행을 바꿔가면서 작업을 하고 있습니다. 그런데 만약 B2, C2 등 셀의 행을 그대로 두면 잘못된 수식이 적용될 수밖에 없습니다. 그래서 행이 바뀔 때마다 “2”를 현재 작업하고 있는 행으로 바꾸어 주어야 합니다.

프로그래밍은 내가 가지고 있는 한정된 지식을 가지고 어떻게 문제를 해결하느냐, 즉 지식보다는 창의성이 매우 중요하다는 것을 여기서도 느낄 수 있습니다.

오늘은 엑셀함수와 매크로를 함께 활용하는 방법을 알아보았습니다. 이전 편에서도 얘기했듯이 이해가 될 때까지 몇번이고 읽어 보시길 권장드립니다.

다음편 계속 읽기