자유 그리고 경험
[Excel]매크로 기록 활용 예제(with 상대참조로 ) 본문
다른 매체들에서 매크로 관련 글들을 쓰면서, 자동화를 위한 VBA 초보자들이나, VBA를 당장 어려운 분들은 반복 작업을 위해 매크로 녹화를 활용할 수 있다고 많이 소개드려왔습니다. 또, 매크로 녹화를 한 후 기록된 매크로 코드를 뜯어보면서 필요한 기능의 코드를 알 수도 있습니다.
글을 쓸 때마다 예제 만들기가 어려웠는데, 오늘은 좋은 예제가 있어 같이 만들어보며 감을 잡아보려고 합니다.
자료는 다음과 같습니다.
위의 자료 형태로 60개정도 자료가 있는데, 이 자료를 아래와 같이 바꿔서 정리하고 싶습니다.
한눈에 생각했을 때, 줄 사이사이마다 새로운 행을 삽입해주고, 일일히 '셀병합'을 해주고, 어떤 부분은 잘라내서 다른 부분에 붙여넣기 까지 하는 등 복잡해 보입니다.
하지만 여기서 중요한 것은 이 복잡한 작업들이 '반복' 되는 '반복작업'이라는 것이 겁이 난다는 점입니다.
어떤 작업이 조금 복잡하더라도 일정한 규칙으로 반복만 된다면, 얼마든지 코드를 짜서 자동화할 수 있습니다. 코드를 못짜신다구요? 걱정하지 마세요. '매크로 기록' 기능을 쓰면 컴퓨터가 알아서 코드를 짜줍니다.
이렇게 하면 작업을 '녹화' 해놨다가 '재생' 하는 것처럼 반복시킬 수 있습니다.
이제 어떻게 하는지 알아봅시다.
1.기본동작 알려주기
반복할 기본동작을 컴퓨터에 알려줘야 합니다. 그래야 그 기본값을 컴퓨터가 적어놨다가 앞으로도 써먹을 수 있습니다.
먼저 1A Cell 을 선택합니다. 매우 중요합니다. 동작을 시작할 위치를 알려주는 작업이거든요.
두번째로, [개발도구] 에서 [상대 참조로 기록]을 클릭해줍니다.
잠깐만, 개발도구가 없다구요?
긴 설명할 시간 없습니다. 위 영상 보세요ㅎㅎ
상대 참조까지 되셨다면, [매크로 기록]을 눌러주세요.
위와 같은 창이 나오는데, 바로 가기 키 옆에 빈칸에 단축키를 지정해주세요. 저는 k를 썼습니다. 이렇게 하면 기록이 완료된 동작을 'ctrl+k' 를 눌러 반복할 수 있습니다.
확인을 누르셨으면 [매크로 기록] 이라고 쓰여 있던 부분이 [기록 중지] 로 바뀌게 됩니다. 기록중이라는 뜻입니다. 이제부터 실행하는 동작들을 엑셀이 코드로 기록해줍니다.
저는 첫번째 열에 대해서 해야 하는 일들을 다 해주었습니다. A3를 선택해서 다음열까지 선택해주고 [기록 중지] 버튼을 눌러 입력이 끝났음을 컴퓨터에 알려줍니다.
2.테스트 해보기
기록이 진짜 됐는지, 단축키는 제대로 작동할지 ctrl+k를 한번 입력해볼까요?
위와 같이 아까 첫번째 열에서 열심히 했던 작업이 순식간에 두번째 자료에 적용된 것을 볼 수 있습니다. 동작이 완료되고 나서는 A6 번 셀이 선택되었네요. 이 상태에서 ctrl+k를 누르면 네번째 자료에 매크로가 적용됩니다. 세번째 자료를 선택하고 했으면 세번째 자료에 적용이 됐을 거라는 걸 유추할 수 있죠?
지금까지 한 번 기록한 매크로를 다른 비슷한 곳에 쓰는 법이 궁금하셨던 분들, 이것이 '상대참조로 기록'의 힘입니다.
연속으로 적용하기 위해서는 ctrl+k 를 입력한 뒤 화살표 윗방향 (↑)을 눌러 한 칸 올리면서 작업해야겠네요.
씐나게 작업해보겠습니다.
3.씐나게 반복하기
ctrl+k+↑ X 10 !!!
4. 부록. 코드 구경하기
아까 이 동작을 컴퓨터가 '기록한다' 고 말씀드렸는데, 혹시나 이 기록이 어디에 있는지 궁금한 분들은 'Alt+F11'을 눌러보세요.
왼쪽 창에서 Module1 이라는 걸 선택하면 '매크로2' (한번에 성공했다면 매크로 1) 이라는 이름으로 바로가기 키도 설명이 되어있고, 복잡한 코드가 쓰여있는 게 보입니다.
우리가 ctrl+k 를 누를 때마다 저 코드들이 실행됩니다. 상대 참조를 위해 ActiveCell 이라는 걸 사용한 것이 눈에 띄는데, 코드는 지금은 몰라도 됩니다. 패스!
복잡한 동작을 매크로 기록을 통해 해결해 보았습니다.
주의사항을 정리해보겠습니다.
- 시작점 설정을 잘 해주셔야 합니다.
- 반복하는 곳의 시작점도 잘 설정해주셔야 합니다.
- 반복하는 곳의 시작점을 엉뚱한 곳 ( D10 등) 에 놓고 단축키를 누르면 에러가 나는 것을 알 수 있습니다. 매크로는 중간에 에러가 나면 ctrl+z 등의 되돌리기 기능도 제한될 때가 있고 복잡하므로 되도록 에러가 안 나게 합시다.
- 단축키 설정시 덮어씌워지므로 평소에 자주쓰는 단축키는 피해서 씁시다. (ctrl+z, ctrl+s 등을 쓰면 구찮아져요).
- 참고로 대부분의 ctrl 조합은 단축키가 이미 지정되어 있고 j,l,m,q 정도가 비어있습니다.
- 본문에서 쓴 ctrl+k (하이퍼 링크 삽입) 정도 하찮은 단축키는 괜찮다고 생각합니다. 지금까지도 안 써왔잖아요.
사실 상대 참조를 해도 여전히 예상치 못한 곳에서 에러가 나는 등 귀찮은 점은 많지만(중간 중간 저장 필수!), 이번 예제처럼 적당히 복잡한 반복작업에서는 빨리 작업하기 편한 기능이라고 생각합니다. 더욱 발전된 자동화를 원하시는 분은 VBA라는 언어를 배워보시는 걸 추천드립니다! 조만간 기회가 닿으면 다뤄보겠습니다.
'기술' 카테고리의 다른 글
[Google spreadsheet]자동화하기_윗줄 식 아랫줄에 복사_2편 (0) | 2021.11.14 |
---|---|
[Google spreadsheet]자동화하기_윗줄 식 아랫줄에 복사_1편 (0) | 2021.11.13 |
[Excel]월 자동으로 채우기, 연도에서 연도 빼기 (2) | 2021.11.09 |
[Excel]월별 입고, 출고 갯수 세기. (feat.텍스트나누기.피벗테이블) (2) | 2021.11.05 |
엑셀 자동으로 숫자 채우기, 규칙 암기 없이 배워봅시다.(스프레드 시트도 끼워주랑) (1) | 2021.10.24 |