엑셀 실무 예제 - 일일 업무 스테이션
조건부 서식, VLOOKUP, 매크로 그리고 하이퍼 링크를 사용, 종합 스테이션과 같은 형식으로 하루를 시작할 때 띄워놓고 사용하고 있습니다.
여러 기능들이 녹아들어 가 있는데... 그림에 표시한 번호는 아래의 번호와 일치합니다.
1. 데이터 목록 / 조건부 서식 - 목록에서 데이터 가져오기, 해당항공사 선택 시 셀 색상변경
2. VLOOKUP - 현 작업번호 선택 시 데이트 불러오기 - VLOOKUP
3. 조건부 서식 - 잘못된 문서 선택시 내용 안 보이게 하기
4. 하이퍼 링크 - 해당문서의 원하는 시트로 이동
5. 매크로 - 원클릭 버튼 선택시 프린터 선택, 문서별 필요한 만큼 인쇄 후 PDF 파일 생성
1 데이터 목록
1-1, 목록에서 리스트 가져오기 (데이터 유효성 검사)
1-2, 목록에 맞는 데이터 값 가져오기 (VLOOKUP)
이렇게 사용하고 있습니다. (영상참조)
(1) : 오늘 작업할 마스트 번호를 입력합니다.
(2) : 작업순서에 따라 마스트를 선택합니다.(목록에서 선택)
(3) : 선택한 마스트 번호에 따라 결괏값이 출력됩니다.(VLOOKUP)
이 경우 마스트 번호 타이핑 실수를 줄일 수 있는 장점이 있습니다. 예제는 3개의 마스트이지만 100개쯤이면 매번 타이핑하기에 많이 번거로울 겁니다.
목록에서 작업순서를 선택하는 방법은 화살표의 "아이콘을 클릭"하는 방법과 해당셀을 선택한 후 키보드 "Alt + 방향키 아래/위"를 선택하여도 됩니다.
1 목록 만들기
"목록에서 원하는 숫자를 선택할 수 있도록 합니다."
예제 1의 (1) 위치에 데이트를 입력합니다. (예제의 경우 범위 G3:G5)
예제 2의 "89720017"의 위치를 선택한 후 (예제의 경우 위치 E3)
[데이터], [데이터 유효성 검사]를 선택합니다.
[데이터 유효성]에서 제한 대상을 "목록"(1)으로 선택한 후 "원본"(2)는 테이터가 입력된 범위를 마우스로 드래그 하여 범위를 지정한 후 "확인" 버튼을 눌러줍니다.
2 조건부 서식
선택에 따라 지정범위 색상 변하게 하기
D3:D4(MAWB NO, 출항일)를 선택 후 [조건부 서식], [새규칙], [수식을 사용하여 서식을 지정할 셀 결정] 을 선택합니다.
[다음 수식이 참인 값의 서식 지정]에 아래의 수식을 입력 후 [서식]을 선택합니다.
=$D$2="AIR 1 (NRT)"
D2의 값이 AIR 1 (NRT) 이면 빨강으로 채우기가 되는 조건부 서식입니다.
참고로 아니면을 사용할땐 <> 사용합니다.
[셀 서식]에서 [채우기],[빨강]을 선택 후 [확인]을 눌러줍니다.
2 VLOOKUP
데이터에서 원하는 값 찾아오기.
목록에서 임의의 숫자를 선택 시 해당 data에 맞는 정보를 가져옵니다. [EX : AIR 2 (ICN) ]
VLOOKUP 예제
D2에 "=VLOOKUP(D5, D5:F9,2,0)"이라고 입력하였습니다. ["가"를 찾은 후 2번째 행의 값을 가져와라] 결과 값은 "a" 이군요, 하나씩 분해해서 설명해 보겠습니다.
- VLOOKUP 찾아라 D5를 D5는 "가"입니다.
- D5:F9의 범위에
- 2의 의미는 2번째 행 즉 "a"입니다. 3을 입력하면 "1" 이 됩니다.
- 0은 정확히 일치하는 값을 의미합니다.
실무에서는 이렇게 사용하였습니다.
=IFERROR(VLOOKUP(E3, 'MAWB LIST_KYLE.xlsm'! MAWB_LIST_KYLE,9,0), "")&" ("&VLOOKUP(E3, 'MAWB LIST_KYLE.xlsm'! MAWB_LIST_KYLE,10,0)&")"
VLOOKUP의 4가지 인수를 2번 사용하였습니다. 연속하여 사용할 때는 &를 이용합니다.
해석하자면 E3의 값 "89720017"을 찾아서 9번째 행의 값을 가져온 결과는 AIR 2가 됩니다.
& 를 이용 한번 더 반복하고 10번째 행 ICN과 합치면 결괏값은 AIR 2 (ICN)이 됩니다.
()와 같은 문자를 사용할 땐 "를 이용합니다. (규칙입니다.) 하여 ") " 와 같은 이상한 문자가 들어가 있는 겁니다.
여기서 해결되지 않는 문제 IFERROR 그리고 Table_arry 값 'MAWB LIST_KYLE.xlsm'! MAWB_LIST_KYLE는 무엇인지 설명드리겠습니다.
- IFERROR
ERROR가 발생하면 원하는 문자를 출력하는 함수입니다.
예제에서는 원하는 문자를 ""로 하였습니다. 문자를 사용할 땐 "를 사용한다고 앞에서 설명드렸습니다. "와 " 사이에 아무것도 없으니 ERROR가 발생하면 빈칸으로 두어라가 됩니다.
- 'MAWB LIST_KYLE.xlsm'! MAWB_LIST_KYLE
테이블 범위를 입력받는 곳에 있는 녀석입니다.
"=VLOOKUP(D5, D5:F9,2,0)"
위의 예제와 같이 D5:F9라고 범위를 지정하여 사용하면 간단? 하겠지만 매번 DATA 범위가 변하는 경우가 더 많을 겁니다. 새롭게 DATA가 추가될 때마다 범위를 다시 지정할 수 없겠지요?
이럴 때 필요한 것이 동적 DATA 범위입니다. DATA 범위가 변해도(늘어나도/줄어들어도) 해당 범위를 자동으로 가져오게 합니다.
'MAWB LIST_KYLExlsm'은 파일 이름입니다. 참조할 data가 들어가 있는 파일 이름입니다.
파일이름을 가져올 때 사용하는 규칙은 '입니다. 같은 형식으로 사용합니다.
! MAWB_LIST_KYLE는 동적 DATA가 지정된 이름입니다. 1, 데이트 목록 예제 편에서 설명한 목록을 만들어도 자동으로 이름 (표1) 이 만들어집니다.
이름 관리자는 Ctrl + F3 으로 불러올 수 있습니다.
동적 DATA 범위가 지정된 영역은 =OFFSET(data!$C$2,0,0, COUNTA(data!$C:$C),12)입니다.
DATA 범위를 불러올 때마다 =OFFSET(data!$C$2,0,0, COUNTA(data!$C:$C),12) 대신에 MAWB_LIST_KYLE를 사용할 수 있도록 합니다. 마치 사원증과 같은 역할입니다.
=OFFSET(data!$C$2,0,0, COUNTA(data!$C:$C),12)에서 생소한 OFFSET과 COUNTA는 어떻게 사용하는지 설명하겠습니다.
- OFFSET
시작점에서 지정한 거리만큼 이동한 위치의 셀 또는 범위를 반환하는 함수
( DATA로 사용할 범위를 지정합니다.)
1. data!$C$2 : 시작지점 (현재 C2의 값 37500227)
2. 0 : 아래로 이동할 칸 수 (아래로 이동안함)
3. 0 : 오른쪽으로 이동할 칸 수 (오른쪽으로 이동안함)
4. COUNTA(data!$C:$C) : 출력할 범위를 아래로 확장 (C열의 DATA 개수 : 300)
5. 12 : 출력할 범위를 오른쪽으로 확장 (DATA 범위는 오른쪽으로 12칸 "N"까지)
- COUNTA
범위에서 비어 있지 않은 셀의 개수를 구합니다.
COUNTA(data!$C:$C) : 시트 data의 C열의 data 개수를 구합니다. 300
data가 추가 또는 감소할 때마다 개수를 구해줍니다.
1 이름 관리자
Ctrl + F3 을 눌러 이름 관리자를 불러온 후 [새로 만들기]를 선택합니다.
원하는 이름을 입력하고, 참조 대상에 해당 함수를 입력한 후 [확인]을 눌러줍니다.
=OFFSET(data!$C$2,0,0, COUNTA(data!$C:$C),12)
3 조건부 서식
잘 못된 문서 선택 시 내용 안 보이게 하기
조건에 맞는 파일은 정상출력되지만 잘못된 시트를 선택 출력 시 내용 안 나오게 하기
AK1 위치에 VLOOKUP 함수를 사용하여 해당 항공사 정보를 가져옵니다.
시트 전체를 선택한 후 [조건부 서식], [새규칙], [수식을 사용하여 서식을 지정할 셀 결정] 을 선택합니다.
[다음 수식이 참인 값의 서식 지정]에 아래의 수식을 입력 후[서식]을 선택합니다.
=$AK$1<>"AIR1 (ICN)"
AK1의 값이 AIR1 (ICN)이 아닐 경우 지정되는 서식을 만들어 줍니다.
예제의 경우 [글꼴], [색],[흰색]을 선택하였습니다. 결과적으로 잘못된 시트를 선택 프린터 하면 내용이 없는 백지의 용지가 나오게 됩니다.
4 하이퍼 링크
선택 시 원하는 파일의 원하는 시트로 이동할 수 있습니다.
마우스 우 클릭후 [링크], 원하는 파일 선택 후 [책갈피], 원하는 시트 선택
5 매크로
내용이 방대할 것으로 기대되어 다음 편
엑셀 실무 예제, 일일 업무 스테이션 -1/2 (조건부서식, VLOOKUP, 매크로) 에서 이어서 설명토록 하겠습니다.
2023.12.07 - [엑셀 실무 예제] - 엑셀로 단순화한 업무 예제, 일일 업무 스테이션 -2/2 (조건부서식, VLOOKUP, 매크로)
공감과 댓글은 글쓴이에게 많은 힘이 됩니다.
'엑셀 실무 예제' 카테고리의 다른 글
| 프린터 이름 찾는 법 (0) | 2023.12.14 |
|---|---|
| [엑셀 실무 예제] 파일 위치 확인하는 방법 (0) | 2023.12.14 |
| [엑셀 실무 예제] 일일 업무 스테이션 -2/2 (조건부서식, VLOOKUP, 매크로) (0) | 2023.12.13 |
| [엑셀 실무 예제] 매크로 기록 및 실행 사용 (2) | 2023.12.13 |
| 엑셀 매크로 사용 설정하는 방법 (2) | 2023.12.07 |
