[엑셀 실무 예제] 일일 업무 스테이션 -1/2 (조건부서식, VLOOKUP, 매크로)

엑셀 실무 예제 - 일일 업무 스테이션

 

조건부 서식, VLOOKUP, 매크로 그리고 하이퍼 링크를 사용, 종합 스테이션과 같은 형식으로 하루를 시작할 때 띄워놓고 사용하고 있습니다.

 

 

여러 기능들이 녹아들어 가 있는데... 그림에 표시한 번호는 아래의 번호와 일치합니다.

 

 

1. 데이터 목록 / 조건부 서식 - 목록에서 데이터 가져오기, 해당항공사 선택 시 셀 색상변경

2. VLOOKUP - 현 작업번호 선택 시 데이트 불러오기 - VLOOKUP

3. 조건부 서식 - 잘못된 문서 선택시 내용 안 보이게 하기 

4. 하이퍼 링크 - 해당문서의 원하는 시트로 이동 

5. 매크로 - 원클릭 버튼 선택시 프린터 선택, 문서별 필요한 만큼 인쇄 후 PDF 파일 생성 

 

---

 1  데이터 목록 

 

 

1-1, 목록에서 리스트 가져오기 (데이터 유효성 검사)

1-2, 목록에 맞는 데이터 값 가져오기 (VLOOKUP)

예제 1

 

 

 이렇게 사용하고 있습니다.  (영상참조) 

(1) : 오늘 작업할 마스트 번호를 입력합니다.

(2) : 작업순서에 따라 마스트를 선택합니다.(목록에서 선택)

(3) : 선택한 마스트 번호에 따라 결괏값이 출력됩니다.(VLOOKUP)

이 경우 마스트 번호 타이핑 실수를 줄일 수 있는 장점이 있습니다. 예제는 3개의 마스트이지만 100개쯤이면 매번 타이핑하기에 많이 번거로울 겁니다.

목록에서 작업순서를 선택하는 방법은 화살표의 "아이콘을 클릭"하는 방법과 해당셀을 선택한 후 키보드 "Alt + 방향키 아래/위"를 선택하여도 됩니다.

 

---

 1  목록 만들기

 

"목록에서 원하는 숫자를 선택할 수 있도록 합니다."

 

예제 1의 (1) 위치에 데이트를 입력합니다. (예제의 경우 범위 G3:G5) 

예제 2의 "89720017"의 위치를 선택한 후 (예제의 경우 위치 E3)

예제 2

 

[데이터], [데이터 유효성 검사]를 선택합니다.

 

 

 

[데이터 유효성]에서 제한 대상을 "목록"(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, 매크로)

공감과 댓글은 글쓴이에게 많은 힘이 됩니다.