엑셀기타강좌 3강
매크로
매크로에 대해 설명한 강의입니다.
▶ 매크로 - 자주 반복적으로 사용하는 작업을 단축키나 도형 등에 지정해놓고 쉽게 사용할 수 있는 기능
▶ 매크로 사용설정
1. 개발도구 탭 표시
2. 파일을 매크로 포함 파일로 저장(*.xlsm)
3. 보안센터 - '디지털 서명된 매크로만 포함', '모든 매크로 포함' 중에 하나 설정
▶ 매크로 이름 - 첫 글자 문자, 공백, 기호, 구두점 포함 안됨
아래의 연습파일을 다운로드해서 연습하세요.
아래 동영상 강의에 위의 내용이 자세하고 쉽게 설명이 되어있습니다.
| [무료 동영상 강의] 엑셀기타#2 - 차트 (3) | 2019.02.04 |
|---|---|
| [무료 동영상 강의] 엑셀기타#1 - 조건부 서식 (1) | 2019.01.25 |
방명록에 어떤 분이 수많은 품목이 엑셀에 저장되어 있는데 거래명세서에서 품목을 입력할 때 품목의 일부만 입력해도 그 일부가 포함된 품목의 목록이 나타나서 클릭만으로 쉽게 입력할 수 있는 방법을 물어보셨다.
생각해보니 꼭 거래명세서나 품목이 아니더라도 활용도가 많은 경우라고 생각해서 그 방법을 모색해보니 고급필터, 데이터유효성검사, 매크로의 3가지 방법을 모두 사용해야 가능해진다.
아래와 같이 '품목'에 '딸기'를 입력하면 그 글자가 포함된 모든 품목의 목록이 '딸기'를 입력한 셀에 나타나 간단하게 원하는 품목을 선택할 수 있는 예제를 만들어보겠다.
어찌보면 네이버의 연관 검색어와 비슷한 면이 있는듯해서 포스팅 제목을 '엑셀 연관 검색어 실습'이라고 지어봤다.
이 파일은 '거래명세표' 시트와 '품목 목록' 시트의 2개의 시트가 있으며, '품목 목록' 시트에는 품목 데이터가 입력되어 있다. 실습용이라 그 목록의 개수는 적지만, 몇만 개의 데이터가 입력되어 있더라도 결과는 같다.
예제 파일이 첨부되어 있으니 다운받아서 같이 따라해보면 될듯~~~
실습 시작
1. Q9 셀에 고급 필터의 조건을 미리 입력해보자.
="*'&D16&"*" 식은 '*'을 이용하여
문자의 일부가 포함된 모든 데이터를 검색할 때 사용되던
'*문자열*'를 식을 이용하여 작성한 것이다.
예를 들어 '*딸기*'는 '딸기'가 포함된 모든 데이터란 의미이다.
어찌 보면 이번 실습에서 가장 중요한 부분이라고 볼 수 있을듯..
고급 필터가 궁금하다면 참조~!! 고급필터로 데이터 추출하기 (고급필터 조건 작성법) by Y
2. 식을 입력하고 엔터를 치면 D16셀이
비어 있으므로 *만 두개 표시될 것이다.
3. 하지만, D16셀에 품목의 일부분을 입력하면
당장 조건이 바뀌는 것을 볼 수 있다.
4. 고급 필터의 조건이 준비되었다면 고급 필터를 실행하고 그 결과를
데이터 유효성 검사로 품목 셀에 표시하는 일련의 과정은 매크로로 작성한다.
[개발 도구]-[매크로 기록] 명령을 클릭한다.
매크로가 궁금하다면 참조~!! 엑셀 매크로 실습 (데이터를 원하는 위치로 이동하기) by Y
5. 매크로 이름과 단축키를 지정하고 [확인] 버튼을 클릭한다.
6. 고급 필터는 조건과 결과를 표시할 시트에서 시작해야 하므로
'거래명세표' 시트의 임의의 셀을 클릭한 상태에서
[데이터]-[고급 필터] 명령을 클릭한다.
7. '다른 장소에 복사'를 지정하고 '목록 범위'는 '품목 목록' 시트 탭을 눌러
목록 전체를 범위로 지정한다.
'조건 범위'를 클릭하면 자동으로 '거래명세표' 시트로 화면이 이동한다.
8. 나머지 조건 범위와 복사 위치를 지정한 후 [확인] 버튼을 클릭한다.
9. 고급 필터가 실행되어 '소스'가 포함된 모든 품목이 필터링된다.
10. 이제 고급 필터의 결과를 '품목' 셀에 목록으로 표시하기 위해
D16셀을 클릭한 후 [데이터]-[데이터 유효성 검사] 명령을 클릭한다.
데이터 유효성 검사가 궁금하다면 참조~!!
엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y
11. '제한 대상'을 목록으로 선택한 후 '원본'에 고급 필터의 결과를 선택한다.
이때, 결과가 많을 경우를 대비하여 결과보다 훨씬 많은 부분을 선택한다.
어짜피 글자가 있는 부분만 표시되므로 상관 없다.
12. 자 이제 모든 작업이 끝났으므로 잊지 말고 기록을 중지하자.
13. 이제 잘 되는지 실습해보기에 앞서
앞에서 작성한 데이터 유효성 검사를 지우기 위해
D16셀이 선택된 상태에서 [데이터]-[데이터 유효성 검사] 명령을
클릭한 후 [모두 지우기] 버튼을 클릭한다.
**내용 추가**
생각해보니 위의 과정을 매크로 시작시 가장 처음에 실행하면
D16셀에 있던 유효성검사를 지울 수 있어 번거롭게 따로 지울 필요가 없다.
14. 이제 품목에 '딸기'를 입력하고 엔터를 친 후
Ctrl+q 키를 누르면 매크로가 실행되어 딸기가 포함된 데이터가 표시된다.
이때 원하는 품목을 클릭하면 품목이 입력된다.
마치, 네이버의 연관 검색어와 비슷하지 않남? ㅎㅎ
만약 다른 셀에서 또 다른 데이터를 입력할 경우
목록이 표시되는 셀도 이동되도록 매크로를 수정해야 한다.
| 엑셀 머리글/바닥글 삽입과 편집 by Y (4) | 2013.03.25 |
|---|---|
| 외부 데이터 가져오기 1 (웹 데이터 엑셀로 가져오기) by Y (4) | 2013.03.19 |
| 엑셀 차트 만들기 (데이터 유효성 검사, VLOOKUP 함수) by Y (2) | 2013.03.18 |
| 엑셀 시트의 일부분만 보호하기 (잠금, 시트보호, 시트보호해제) by Y (0) | 2013.03.13 |
| 피벗테이블로 데이터 분석하기 (자동서식 적용, 피벗차트 만들기) by Y (2) | 2013.03.07 |
| 엑셀 부분합 (자동 서식 적용, 결과 복사) by Y (0) | 2013.02.26 |
| 엑셀 시트 관리 (이름 바꾸기, 복사, 이동, 생성, 삭제) by Y (0) | 2013.02.25 |
| 엑셀 정렬 (오름차순, 내림차순, 사용자 지정) by Y (6) | 2013.02.21 |
| 엑셀 시트그룹지정 (여러 시트에 같은 내용 입력하기) by Y (0) | 2013.02.18 |
| 엑셀 데이터 자동 검색 실습 3 (옵션단추, 고급필터, 매크로) by Y (7) | 2013.02.15 |
매크로 실습 자료.xlsm 매크로 작성 완료.xlsm 작업 완료.xlsm
엑셀에서 매크로를 모르면 간단하게 해결할 일도 수많은 수작업을 해야하는 경우가 많다. 매크로는 단순 작업이 수없이 반복될수록 더 효율적인 작업이 가능하게 하는 기능으로 단축키에 저장해놓는다면 쉽게 작업이 가능하다.
그림에서처럼 왼쪽처럼 입력된 데이터를 오른쪽처럼 한글과 영어가 한 행에 보기 좋게 배치되도록 매크로를 작성해보자.(단, 처음 데이터와 마지막 데이터가 결과 시트에 반대로 입력된다.)
먼저 개발 도구에서 매크로 작업을 해야 하는데 자기 엑셀 리본 메뉴에 개발 도구 탭이 없다면 다음 글을 참조해보자.
매크로 작업시 유의할 점
1. 단순한 작업(자료의 복사, 이동, 꾸미기, 인쇄 등)이 반복될 경우 사용하는 것이 매크로이므로 매크로를 작성할 경우에는 그 반복되는 최소 단위를 캐치하여 만드는 것이 중요하다. 예를 들어 복사가 반복된다면 한번의 복사를 매크로로 저장해야 한다.
2. 1000개의 자료를 다른 곳에 복사할 경우 1개의 자료를 복사한 후 다음 매크로에서 두 번째 자료를 복사할 수 있도록하는 것이 중요하다. 즉, 다음 매크로가 실행될 경우를 염두에 두고 매크로를 작성해야 한다.
작업을 위한 예제 파일을 첨부하였으니 같이 따라서 작업해보자. 매크로 기록을 시작하기 위해 [개발 도구] 탭의 [매크로 기록] 명령을 클릭한다.
[매크로 기록] 대화 상자가 나타나면 '매크로 이름'과 '바로 가기 키'를 지정한다. 여기에서는 '매크로 이름'은 그대로 놔두고 '바로 가기 키'만 'w'로 지정했다. [확인] 버튼을 누른 다음부터는 모든 키보드 행동과 마우스 행동이 기록되기 때문에 조심 또 조심해서 작업해야 한다.
[A1] 셀을 복사하자. [Ctrl]+[C] 키를 눌러도 되고, 마우스 오른쪽 메뉴의 [복사] 메뉴를 클릭해도 된다.
'결과' 시트의 [A1] 셀에 붙여넣는다. [Ctrl]+[V] 키를 눌러도 되고, 마우스 오른쪽 메뉴의 [붙여넣기] 메뉴를 클릭해도 된다.
같은 방법으로 '원본' 시트의 [A3] 셀을 '결과' 시트의 [B1] 셀에 복사한다.
한 문장이 모두 복사됐으면 다음 매크로 작업 때는 다음 문장이 [A1] 셀에 위치해야 한다. 그래야 항상 [A1] 셀을 복사할 수 있게 된다. 이렇게 매크로를 만들 경우 다음 매크로 때에도 정상적으로 작업이 되도록 하는 것이 가장 중요하다.
그래서 '원본' 시트의 [1:3] 행을 선택한 후 행을 삭제한다.
마찬가지로 '결과' 시트에도 항상 [A1] 셀에 붙여넣기가 되므로 먼저 복사한 내용이 [A1]에 있으면 다음 작업시 먼저 내용을 덮어버리므로 [A1] 셀을 비워놔야 한다.
그래서 '결과' 시트의 [1] 행을 선택한 후 행을 삽입한다.
그리고 '결과' 시트의 [A1] 셀을 선택해 놓는다. 그래야 다음 매크로 작업시 [A1] 셀에 한글이 붙여넣기 된다.
이젠 다시 '원본' 시트를 선택한 후 [B2] 셀을 선택해 놓는다.
이제 한 번의 최소 반복 작업이 끝났으니 [기록 중지] 명령을 클릭한다. 여기까지 작업한 결과 파일을 첨부하였으니 한 번 확인해보면 되겠다.
이제 매크로가 제대로 실행되는지 확인하려면 단축키 [Ctrl]+[W] 키를 누르면 된다. '결과' 시트로 한글과 영문이 제대로 복사되고 '원본' 시트에서 사라졌다면 제대로 실행되는 것이고, 아니라면 다시 작업하기 위해 [매크로] 명령을 눌러 앞에서 작성한 매크로를 삭제하고 다시 매크로 기록 작업을 해야 한다.
만약 매크로가 제대로 실행이 안된다면 다음 글을 참조해보자.
여러번 [Ctrl]+[W]를 눌러 매크로를 여러 번 실행한 모습이다.
| 엑셀 함수 11 (SUMIFS, AVERAGEIFS) by Y (2) | 2013.01.17 |
|---|---|
| 엑셀 함수 10 (개수 구하기) by Y (0) | 2013.01.17 |
| 엑셀 오류 메시지 by Y (1) | 2013.01.16 |
| 엑셀 조건부 서식 1 ('표시 형식'의 '사용자 지정'으로 작성) by Y (0) | 2013.01.14 |
| 엑셀 리본메뉴 최소화/최대화 by Y (0) | 2013.01.11 |
| 엑셀 서식의 '맞춤'에서 '균등 분할(들여쓰기)' 기능 by Y (0) | 2013.01.07 |
| 엑셀 '표시 형식' by Y (0) | 2013.01.07 |
| 엑셀 '표시 형식'의 '사용자 지정' 알아보기 by Y (4) | 2013.01.03 |
| 엑셀 함수 9 (배열수식) by Y (0) | 2013.01.02 |
| 엑셀 함수 8 (데이터베이스 함수) by Y (0) | 2012.12.27 |
|
Tistory Cumulus Flash tag cloud by requires Flash Player 9 or better.
매크로.xlsx
