엑셀 기초 강좌, 납품 수량 관리 시트 만들기#3 (엑셀 INDIRECT 함수, IF 함수 사용)

엑셀 기초 강좌, 납품 수량 관리 시트 만들기 3번째(마지막) 포스팅입니다. 만약 앞전의 포스팅을 읽지 않으시고 오신 분이시라면 첫 번째 포스팅부터 진행하시는 게 좋으시리라 생각됩니다.

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

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

     # 함수를 제외한 연습용 서식 파일은 시트 만들기#2 포스팅에서 첨부했습니다.

▼ INDIRECT, ADDRESS, IF 문에 대해서만 확인을 하고 싶으신 경우 아래 포스팅을 확인 해주세요.

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

  ▶엑셀 IF 함수 사용법

다른 두 포스팅에 대해 간략히 설명드리면, 첫 번째 포스팅은 전체적인 시트가 어떻게 구성되었는지를 설명하고 있고, 두 번째 포스팅은 계획 대비 실적 및 일자를 구문하기 위한 함수를 알려주고 있습니다

앞의 두 포스팅을 읽으신 후 연속적으로 읽으신다는 가정하에 작성할 것인데, 혹시라도 내용이 이해 안 되시는 분들은 앞전 포스팅을 읽어보시거나 질문을 남겨주시면 답변드리도록 하겠습니다.

 

질문의 경우 제가 본업이 있는 관계로 즉시 답변을 해드리지 못할 수도 있으니 양해 부탁드립니다

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

아래 함수들 중 다소 생소하거나 조금 어렵게 느껴질 수 있는 INDIRECT 함수, IF 함수에 대해서만 간략히 설명하고 진행하도록 하겠습니다

① INDIRECT 함수는 문자 형식의 주소값과 동일한 실제 주소값의 결과를 가져오는 함수입니다. 예를 보시면 INDIRECT("A1", TRUE) 라고 입력할 경우 A1셀에 입력돼있는 값을 가져오는 형식입니다.

 

그리고 TRUE라는 항목은 TRUE, FALSE 두 가지 값 중 선택 가능한데요. TRUE는 우리가 잘 알고 있는 A1, B1 형식의 주소 형식이고, FALSE는 R1C1 형식을 사용할 때 지정하는 옵션인데, 만약 옵션을 지정하지 않을 경우 기본값이 TRUE(A1, B1) 형식을 지정됩니다.

② IF 함수는 조건 결과가 TRUE(참), FALSE(거짓) 이냐에 따라, 실행 결과를 다르게 하고 싶을 경우 사용됩니다.

 

예를 들어보면 IF(12 > 0 , "옮음", "틀림") 이란 수식의 IF 문이 있을 경우, 12는 0보다 크기 때문에 결과는 TRUE 항목인 "옮음"을 표시하게 됩니다. 만약 12<0 이라는 형식으로 되어있었다면, FALSE 항목인 "틀림'을 선택하게 표시하게 됩니다.

납품 수량 관리 시트의 예상 납품 실적 엑셀 함수 응용하기

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

이 번에는 예상 납품 실적을 표시하기 위해 함수를 작성할 것인데요. 여기서 말하는 예상 납품 실적이란 미래에 고객 요청 수량을 문제없이 납품 가능한지, 불가능 한지를 가늠하기 위해 사용됩니다.

 

계산 방법을 보면 계획 대비 실적 + 생산처 계획 - 고객사 요청 수량으로 계산될 수 있고, 계획 대비 실적은 마지막의 실제 납품 실적 기준으로 작성하기 위해 오늘 기준 계산됩니다

예시 : IF(O$2>TODAY(), INDIRECT($L$30 & 6) + SUM(INDIRECT($L$31 & 4):O$4) - SUM(INDIRECT($L$31 & 3):O$3), 0)

결과 : -6,150

▼ 함수가 사용되는 과정을 설명해 드리도록 하겠습니다.

 

① 가장 먼저 IF 문을 사용해 O2 > 오늘 일자보다 큰지(지나간 날자인지)를 확인한 후 일자가 클 경우 TRUE를, 일자가 작을 경우 FALSE 항목의 결과를 연산하도록 합니다. 이 방법을 사용하는 이유는 과거에 대해선 기본 값 0으로 고정을 하고, 오늘 이후의 미래 값에 대해서 연산을 하기 위해서입니다

 

② IF 결과 FALSE(거짓)일 경우 기본 값으로 0을 표시하는데, 이는 연산을 하지 않는다는 의미로 사용된 것이라 "없음"이라고 표시해도 무방합니다

 

③ IF 결과 TRUE(참)일 경우 INDIRECT($L$30 & 6) + SUM(INDIRECT($L$31 & 4):O$4) - SUM(INDIRECT($L$31 & 3):O$3) 값을 연산합니다

 

④ INDIRECT($L$30 & 6)은 위 예제 기준 $N6 라는 셀 값의 결과를 가져오며, 이는 오늘자 계획 대비 실적으로 가져오는 결과는 -5,150 입니다.

 

⑤ INDIRECT($L$31 & 4)는 위 예제 기준 $O4 라는 셀 값의 결과를 가져오며, 이는 다음날 (위 예제에선 3/11)의 생산처 계획 수량 값을 기준으로 잡습니다. 이 값을 토대로 SUM($O4:O4)를 하게 되면 0 값을 가져오고, 드래그를 통한 수식 복사를 위해 $O에만 절대주소를 부여했습니다.

 

⑥ INDIRECT($L$31 & 3)는 위 예제 기준 $O3 라는 셀 값의 결과를 가져오며, 이는 다음날의 고객사 요청 수량 값을 기준으로 잡습니다. 이 값을 기준으로 SUM($O3:O3)를 계산하게 되면 0이란 값을 가져오고, 드래그를 통한 수식 복사를 위해 $O에만 절대주소를 부여했습니다.

 

⑦ 위 결과를 토대로 -5,150 + 0 + 0 = - 5,150 이란 결과를 최종 결과로 표시합니다.  

3개월 장기 생산 계획 수립하기

위 표의 우측으로 다음 달 5일간의 계획, 합계, 3개월 예상 납품 실적(계획) 항목이 존재하는데요.

앞전에도 설명드렸지만 다음 달 5일간의 내용은 약간 뷰어 용도 성향이 강함으로, 사용하지 않으시는 경우 작성을 안 하시거나 숨겨두셔도 무방한 부분입니다

 

합계 부분은 이 번달 고객사 요청, 생산처 계획, 생산처 출하 수량의 총합을 계산하는데, 5일간의 계획은 뷰어이기 때문에 합산에서 제외합니다

 

▼ 이제 제가 설명드릴 핵심 항목인데요.

4월 예시 : IF(OR($AI7>0, $AI7<0),$AI7,$AI6)+$B7-AP3

결과 : - 4,550

5월 예시 :  (AP7+B7)-AQ3

결과 : - 5,200

6월 예시 : (AQ7+B7)-AR3

결과 : - 6,200

▼ 4월 예시에 대해 간략히 설명드리도록 하겠습니다.

 

① IF(OR($AI7>0, $AI7<0)은 예상 납품 실적이 0을 초과하거나, 0 미만인 경우 TRUE(참) 값을, 아닐 경우 FALSE(거짓) 결과를 표시합니다. OR을 사용해 두 가지 조건을 사용한 이유는 0 값을 제외한 모든 값에 대해 TRUE 값을 적용하기 위함입니다 

 

② 위 예시 기준으로 IF 문 조건 값은 TRUE이기 때문에, $AI7 값을 가져옵니다 (결과 : -3,050)

 

③ 이제 덧셈과 뺄셈을 진행하면 되는데요. IF 문의 결과 -3,050을 사용해, -3,050 + 4,500 - 6,000을 진행합니다 (결과 : -4,550)

▼ 5월 6월은 특별한 계산은 없지만 이 부분도 간략히 설명드리겠습니다

 

① 5월의 경우 4월에 계산된 -4,550 값에, 월간 생산 CAPA를 더한 후 고객 오더 수량을 빼주면 되는데요. 위 예제 기준 (-4,550 + 4,500) - 5,150  →  -5,200으로 표시됩니다 (결과 : -5,200) 

총 3강의 포스팅으로 진행된 엑셀 기초 강좌, 납품 수량 관리 시트 만들기를 완료했습니다. 이 3강의 포스팅을 작성하기 위해 다른 리뷰 포스팅보다 더 많은 시간을 들였고, 글의 수정 사항도 더욱 많았는데요.

아무래도 제가 생각해서 만들 엑셀 문서이기도 하고, INDIRECT처럼 사용 빈도가 높지 않은 함수들도 사용해 이해하시는데 어려움을 느끼시는 분들도 계시리라 생각되는데요.

지금 제가 작성해놓은 수준만 사용할 줄 알더라도 실무에선 꾀 높은 수준의 엑셀 함수 응용이 가능하리라 생각되는데요. 조금 어렵더라도 포기하지 마시고 하시다 보면, 높아진 자신의 실력을 보시면서 기뻐할 날이 오지 않을까 생각됩니다.

마지막까지 포스팅을 읽어주시느라 고생 많으셨습니다.