엑셀 기초 강좌, 납품 수량 관리 시트 만들기#2 (엑셀 IF, ADDRESS 함수, 조건부 서식 사용)

납품 수량 관리 시트 만들기의 2번째 포스팅입니다.

첫 번째 포스팅에선 시트에서 사용되는 사용되는 각각의 기능에 대해 설명드렸었는데, 첫 포스팅은 이런 문서의 용어에 익숙하신 분이라면 읽지 않으시고 두 번째 포스팅을 바로 보셔도 될 것이라 생각됩니다

단, 시트의 용어가 이해가 안가신다면, 앞의 포스팅을 보신 후 감을 잡으신 후 읽으시는게 좋으시리라 생각됩니다.

  ▶ 엑셀 교육, 납품 수량 관리 시트 만들기#1 (시트 기능 설명)

▼ ADDRESS 함수와 IF 함수만을 필요로 하시는 경우 아래 관련 포스팅을 읽어주세요.

  ▶ 엑셀 IF 함수 사용법

  ▶ 엑셀 INDIRECT 함수, ADDRESS 함수 사용법 및 활용법

▼ 서식틀을 잡아 놓은 연습용 엑셀 파일 (함수는 아래 내용을 보시고, 기입해보시면서 연습해보세요~)

납품 수량 관리_이월수량 추가_최종본#3___.xlsx

셀에 조건부 서식 지정하기

 

클릭시 확대 가능(해당 포스팅 모든 사진)

이렇게 한 달 이상의 장기 계획을 수립하는 시트는 오늘 일자를 구분하다 실수하는 경우가 생기기 마련인데요. 실수를 최대한 방지하기 위해 오늘 날짜에 조건부 서식을 지정해 색상이 표시되도록 구성했습니다.

적용 대상 : 1일 ~ 31일(마지막 일자)

조건부 서식 규칙 : =Today()-E2

이렇게 작성하면 위 조건에 해당되는 값에 대해선 지정한 셀 서식이 반영되는 형식입니다.

 

납품 수량 관리 시트를 만들기 위해 사용된 함수 정리

 

위의 시트는 이번 포스팅을 통해 완성되는 최종 시트를 캡처한 사진인데(장기 계획 제외), 앞전 포스팅에서 사용된 양식보다 조금 더 보기 좋도록 색상을 제 스타일로 수정해봤습니다.

그리고 응용된 함수를 설명드리기 전에 위에서 사용된 대표적인 함수를 제외한 ADDRESS, MATCH, TODAY 함수에 대해서 간략히 설명하고 진해하는 것이 좋을 것 같아 간략한 설명을 작성했습니다

① ADDRESS는 ADDRESS(행 값, 열 값, 옵션)으로 이루워 저 있으며, 주어진 값을 셀 주소로 표현해주는 함수입니다. 예를 들면 ADDRESS(3,3,1)일 경우 C3이란 값을 표시하게 됩니다.

 

② MATCH는 MATCH(찾을 대상, 찾을 범위, 옵션)으로, 찾을 범위에 찾을 대상이 있을 경우 범위 내에서 몇 번째 순서인지를 숫자로 표현해주는 함수입니다.

 

③ LEFT는 LEFT(대상, 가져올 글자 수)로, 대상 셀 내용 중 왼쪽으로부터 가져올 글자 수만큼의 글자만 표시해줍니다

 

엑셀 교육, 납품 수량 관리 시트 만들기 - 날짜 위치값 만들기 위한 LEFT, ADDRESS, MATCH, TODAY 함수 응용

 

첫 번째 포스팅을 읽고 오셨다는 가정하에 약간의 설명만 드리면서 진행하도록 하겠습니다

오늘을 3/10일이라고 가정할 때 위 그림에서 보시면 N열이 10일로 당일이고, O열이 11일로 다음 날 이란 것을 아실 수 있는데요. 계획 대비 실제 실적 사항이 반영된 01 ~ 10일 까지를 나누는 기준이 N열이고, 예상 납품 실적이 반영된 11 ~ 31일 까지를 나누는 기준이 O열을 구하는 함수를 만들어 보겠습니다.

▼ 먼저 계획 대비 실적 수량을 나누기 위한 N열을 구하는 함수를 만들어보도록 하겠습니다. 

예제 : LEFT(ADDRESS(8, MATCH(TODAY(), $E$2:$AI$2, 0) + 4, 1), 2)

결과 : $N (19/03/10일이 당일이란 기준)

이제 함수를 설명해드리겠습니다

① TODAY() 함수를 사용해 오늘 일자인 19-03-10일을 가져옵니다

 

② MATCH 함수를 사용해 E2 : AI2 범위에서 19-03-10 과 일치하는 값을 찾아, 범위 내의 몇 번째 순위인지를 계산 후 순위 값을 가져옵니다. (결과 : 10)

 

③ MATCH 함수 내의 +4 값이 남아 있기 때문에, MATCH(10 + 4)를 진행합니다. (결과 : 14)

 

④ ADDRESS(8행, 14열, 옵션 0)으로 계산되면 14는 A부터 시작할 때 N이 되기 때문에, ADDRESS(N8)란 결과가 나옵니다. 하지만 옵션 0은 행, 열에 대한 절대주소를 상징하기 때문에 N8에 절대주소를 뜻하는 $를 붙여줍니다 (결과 : $N$8). 그리고 행 값을 8로 기입해둔 이유는 아무런 의미가 없습니다. 실제 사용하지 않을 값이기 때문에 1을 입력하셔도 되고, 2를 입력하셔도 문제가 없습니다.

 

⑤ LEFT($N$8, 2)는 왼쪽 기준으로 2글자만 가져오면, 행 값을 삭제한 열 값인 $N 값만 가져오게 됩니다. (결과 : $N) 

위와 같은 순서로 함수가 계산되면, 오늘 셀 주소를 알려주는 $N 열이란 값 주소값을 만들어 낼 수 있습니다. 다음 날 열 주소값도 위와 동일한 방법으로 진행하면 되는데, + 4, 1), 2) 부분 중 +4를 +5으로 바꿔주기만 하면 됩니다.

계획 대비 실적 수식 작성하기 (TODAY, SUM, IF) 함수 응용

 

계획 대비 실적 항목은 고객사 요청 수량 대비 생산처에서 실제 출하한 수량을 계산해, 납품 수량이 부족했는지, 아니면 납품하고 재고가 많이 남아있는지를 확인하는 용도로 사용됩니다.

그리고 계산 방법은 (생산처 출하 - 고객사 요청)으로 계산되는데, 이렇게 계산하면 고객 요청 수량 대비 부족수량은 얼마인지, 그리고 즉시 납품 가능한 재고 수량은 얼마나 있는지를 확인하는데 유용합니다.

▼ 계획 대비 실적 수량에 대한 예제 함수를 보도록 하겠습니다.

예제 : IF(E$2<=TODAY(),SUM($D5:E5) - SUM($D3:E3),0)

결과 : 위 사진 참조

이제 응용된 함수를 설명드리도록 하겠습니다.

① TODAY() 함수를 통해 오늘 일자를 가져오며, 오늘 일자를 19/03/10이라 가정합니다 (결과 : 19-03-10)

 

② IF문을 이용해 조건"E2 <= 19-03-10" 라는 공식이 성립되는지를 파악하는데, 과거 ~ 오늘까지에 대한 결과는 실제 계산해야 되기 때문에 TRUE으로, 내일부터는 계산 없이 기본 값 0을 표기해야 되기 때문에 False를 표시합니다. 

 

③ IF문의 조건이 False(거짓)일 경우 0을 표시합니다. 여기서 0을 표시하는 이유는 연산하지 않았을 경우 0을 기본 값으로 지정하도록 하기 위해 사용됐습니다.

 

④ IF문의 조건이 TRUE(참)일 경우 SUM($D5:E5) - SUM($D3:E3)을 연산하는데, SUM(0,0) - SUM(4,000 + 1,200)  →  0 - 5,200  →  -5,200 이란 결과를 표시하게 됩니다. 

여기서 $D5와 $D3의 열 값에 절대값을 지정한 이유는, 위 예제 기준 D5와 D3열 기준으로 드래그해 수식을 복사하는 경우가 대부분일 것인데, 이때 첫 시작 값을 D5와 D3으로 고정하기 위해 열 고정을 진행했습니다.

▼ 납품 수량 관리 시트 만들기 3번째 포스팅

2019/03/17 - [엑셀 응용] - 엑셀 기초 강좌, 납품 수량 관리 시트 만들기#3 (엑셀 INDIRECT 함수, IF 함수 사용)

다음 포스팅에선 예상 납품 실적 예제 작성 방법과 3개월간의 1분기 고객 요구 수량을 반영한 시트를 만들어보도록 하겠습니다.