함수를 이용하면 다른 PC에서도 그대로 작동하기 때문에 편리한 대신, 워크시트 함수에는 병합 셀과 관련된 함수가 없으므로
https://lsw3210.tistory.com/entry/%EB%B3%91%ED%95%A9-%EC%85%80%EC%9D%98-%EC%B2%AB%EB%B2%88%EC%A7%B8-%EC%85%80-%EA%B0%92-%EC%B0%BE%EA%B8%B0
위 글과 같이 If함수를 중첩적으로 사용해야 하는 불편함이 있습니다.
따라서, VBA로 사용자 정의 함수를 만들어보겠습니다.
사용자 정의 함수는 PC별로 존재하기 때문에 다른 PC에서 열어보면 폴더명과 함께 사용자 정의 함수명이 표시되는 불편함이 있습니다.
그래도 수식으로 작성하면 복잡한데 사용자 정의 함수를 만들면 간단하게 해결할 수 있는 장점이 있습니다.
아래 데이터를 가지고,
연도별, 지점별로 판매량과 판매액을 구하려고 합니다.
1. 논리
If함수의 경우는 조건이 맞지 않을 경우 If함수를 중첩적으로 사용했는데,
사용자 정의 함수에서는 병합셀이라면 범위의 첫 셀을 반환하도록 하면 됩니다.
2. 사용자 정의 함수 구성
VBA 작성 시 필요한 것으로는 MergeArea(병합 셀 범위)와 Offset(병합셀의 A1셀 찾기) 속성(Property)입니다.
가. 개발 도구 > Visual Basic 에디터를 열고, 모듈 삽입
개발 도구 탭을 클릭한 다음 맨 왼쪽 Visual Basic명령을 누릅니다.
엑셀 파일 명을 클릭한 후 ①삽입 메뉴에서 모듈을 누르거나, ②왼쪽 모듈 삽입 콤보상자를 누른 후 모듈을 누릅니다.
그러면 아래와 같이 모듈 아래 Module1이 추가되고, 오른쪽에 빈 에디터 창이 열립니다.
나. 사용자 정의 함수 작성
아래와 같이 사용자 정의 함수를 작성한 후
Function FirstCell(Merge_Cell As Range)
FirstCell = Merge_Cell.MergeArea.Offset(0, 0)
End Function
표준 도구 모음에서 저장 아이콘을 누르면 "VB 프로젝트가 포함돼서 매크로 제외 통합 문서에 저장할 수 없다"고 합니다.
따라서, 아니요 버튼을 누른 후 파일 형식은 'Excel 추가 기능'을 선택하고, 파일 이름으로는 '병합셀의 첫셀'이라고 입력합니다.
그러면 사용자 폴더 > AppData > Roaming > Microsoft > AddIns 폴더에 저장됩니다.
표준 도구 모음에서 엑셀 아이콘을 눌러 워크 시트로 돌아옵니다.
※ AppData 폴더는 숨겨진 셀이기 때문에 폴더를 표시하려면 탐색기에서 보기 > 표시 > 숨긴 항목에 체크해야 합니다.
다. 사용자 정의 함수 설명
- 사용자 정의 함수는 Function으로 시작해서 End Function으로 끝납니다.
- Function 다음에 함수명을 입력하고 그 다음 괄호 안에 인수를 입력합니다.
함수명은 FirstCell이라고 했고, 인수는 범위 형식의 Merge_Cell 하나만 입력했습니다.
- Function과 End Function 사이가 실행문인데,
FirstCell = Merge_Cell.MergeArea.Offset(0, 0)
한 줄입니다.
실행문에 있는 .(점)은 '~의'란 의미입니다.
엑셀 사용자 정의 함수의 경우는 Return문을 사용하지 않고, 함수명, 여기서는 FirstCell이 반환문이 되며,
Merge_Cell.MergeArea.Offset(0, 0)는 Merge_Cell의 MergeArea 다시 말해 병합셀 영역입니다(병합 셀이 아닌 경우는 단일 셀인 Merge_Cell과 같습니다).
그리고, Offset(0,0)은 (병합)셀 영역의 첫 번째 셀을 가리키며, 그 다음에 .value(값)이 생략 됐습니다.
따라서, 위 문장은 연도별로 병합된 셀의 가장 왼쪽 셀 값을 반환하는 것입니다.
- 엑셀 워크시트에서는 복잡하게 If문을 중첩해서 수식을 작성해야 하는데,
사용자 정의 함수로 하면 너무 간단하게 첫 번째 셀을 구할 수 있습니다.
다. FirstCell 사용자 정의 함수를 이용해 수식 작성
(1) FirstCell 사용자 정의 함수 적용하기
B3셀에 =fir까지만 입력해도 FirstCell이란 함수명이 표시되므로 탭 키를 눌러 함수명을 모두 입력하고,
B1셀을 마우스로 클릭한 다음 아래 줄에 수식을 복사할 때도 1행이 유지되도록 F4키를 두 번 눌러 B$1이라는 혼합 참조형식으로 변경합니다.
그리고, 괄호를 닫고 엔터 키를 누르면 2022라는 값이 구해집니다.
B3셀의 채우기 핸들을 E3셀까지 끌고, 다시 E3셀의 채우기 핸들을 E4셀까지 끌면 모든 셀에 연도가 채워지는데, C3셀에서 E4셀까지 2022 또는 2023이라는 원하는 값이 반환합니다.
(2) SumIfs함수에 FirstCell 함수 적용하기
=sumifs라고 입력하고, 더할 범위로 판매량을 입력한 다음, 원시 데이터 시트를 클릭하고 연도가 있는 A2셀부터 A13셀까지 선택한 다음 F4키를 눌러 절대 참조 형식으로 변경합니다.
그리고, 첫 번째 조건인 연도를 입력해야 하는데, fir까지 입력하면 FirstCell 함수가 표시되므로 탭 키를 눌러 함수명을 모두 입력한 다음
괄호 안에 B1셀 주소를 F4키를 두 번 눌러 B$1 형식으로 입력합니다. 그리고 괄호를 닫고,
쉼표(,)를 입력하고, 두 번째 조건인 지점명을 입력하기 위해 원시 데이터 시트를 클릭한 다음 D2셀부터 D13셀까지 선택한 다음
F4키를 눌러 절대 참조형식으로 바꾸고, 쉼표를 입력한 다음, 조건인 지점명이 있는 A3셀을 입력하는데 A열이 바뀌면 안 되므로 $A3이라고 혼합 참조 형식으로 바꾸고 괄호를 닫고 엔터키를 누르면
2002년도 A지점의 판매량 4,625가 구해집니다.
이제 B3셀의 수식을 복사한 다음 C3셀에 붙여 넣고, 판매량을 판매액으로 수정합니다. 이때 량을 지운 다음 스페이스 키를 누르면, 판매량과 판매액이 표시되므로 판매액을 마우스로 더블 클릭해서 입력해도 됩니다.
그리고, 엔터 키를 누르면 2022년 A지점의 판매액 46,250,000이 구해집니다.
이제 B3셀과 C3셀을 복사한 다음 B3셀에서 E4셀까지 선택한 다음 붙여 넣기를 하면 모든 셀에 원하는 값이 들어갑니다.
이제 엑셀 파일의 이름을 '병합셀의 첫 셀 값 찾기(완성)'이라고 변경하고 저장 버튼을 누르면
위에서와 마찬가지로 VB 프로젝트를 포함하고 있어 매크로 제외 통합 문서에 저장할 수 없다고 하는데,
위에서 VB 프로젝트를 추가 기능 파일에 저장했기 때문에 이것은 매크로 사용 통합 문서로 저장할 필요가 없습니다.
따라서, 예를 클릭합니다.
(3) VB 프로젝트 기능이 포함되지 않은 엑셀 파일(.xlsx)을 열 때
이제 엑셀을 닫고 다시 실행한 후, '병합셀의 첫 셀 값 찾기(완성).xlsx' 파일을 열면 FirstCell 함수에 에러 표시 없이 값이 보이는 것 같은데
B3셀을 선택한 다음 F2키를 눌러 편집 모드로 들어간 다음 엔터 키를 누르면 수식에 아무런 에러 표시가 없는데도 값이 0으로 표시됩니다.
따라서, 개발도구 - Excel 추가 기능명령을 실행한 후 '병합셀의 첫셀'이란 추가 기능 왼쪽에 체크하고 확인 버튼을 눌러서 추가 기능을 로드해야 합니다.
이제 F2키를 누른 후 엔터 키를 누르면 4,625란 판매량이 제대로 표시됩니다.
(4) 엑셀 추가 기능이 포함된 파일(.xlsm)을 열 때
엑셀 파일 저장 시 '병합셀의 첫 셀 값 찾기(완성).xlsm(매크로 사용 통합문서 형식)'이라고 저장하면 추가 기능 모듈이 같이 저장되기 때문에 Excel 추가 기능을 추가하지 않아도 됩니다.
(5) 다른 PC에서 사용자 정의 함수가 포함된 엑셀 파일을 열 때
이 파일을 다른 PC로 보내서 열어보면 처음에는 값이 그대로 보이는데,
F2키를 눌러 편집 모드로 들어간 다음 엔터 키를 누르면 FirstCell이란 함수가 없어서 0이란 값으로 바뀝니다.
따라서, 추가 기능 파일을 같이 보내야 하고,
그 PC에서 개발도구 - Excel 추가 기능 명령을 누른 후 '병합셀의 첫셀' 왼쪽에 체크한 후 확인 버튼을 눌러야 추가 기능이 반영돼서
F2키를 누른 후 엔터 키를 눌러도 값이 제대로 계산됩니다.
(6) 추가 기능은 한 파일(.xlam)에 모아서 관리하는 것이 편리합니다.
'Excel' 카테고리의 다른 글
| ArraytoText 함수 - 배열을 문자열로 반환하는 함수 (0) | 2023.05.12 |
|---|---|
| 참조 열은 1칸, 기록할 열은 2 칸씩 움직일 때 (2) | 2023.05.04 |
| 병합 셀의 첫번째 셀 값 찾기(1) - if와 Offset 함수 (0) | 2023.05.02 |
| Cell의 속성을 알려주는 Cell 함수 (2) (0) | 2023.05.01 |
| Cell의 속성을 알려주는 Cell 함수 (1) (0) | 2023.04.29 |