컴퓨터를 활용하는 실무형 자격증
국가기술자격증 컴퓨터활용능력(Computer Efficiency)
이번 호에서는 지난 호에서 살펴보았던『견적서 작성하기』,『영업소 매출 현황』에 이어서 『제품별 생산 실적 현황』을 관리하는 방법을 배워보기로 하자. 제품별 생산 실적 현황 관리에 사용될 함수들은 WEEKDAY, CHOOSE, TODAY, IF, MID, LEFT, VLOOKUP, RANK, AVERAGE, MAX, MIN, SUMIF 등이 있다. 특히 IF함수와 문자열 함수(MID, LEFT)의 중첩에 대한 정확한 이해와 숙지가 요구된다. 아울러 RANK와 SUMIF함수의 중요성에 대해서도 인지해야 된다.
홍태성 drcom@dreamwiz.com
홍태성 drcom@dreamwiz.com
쉽고 재미있는 강의를 추구하는 온오프라인 인기강사이자 컴퓨터 관련 Technical Writer로 활동 중이다. 저서로는 컴퓨터활용능력과 정보처리기능사, e-Test Professional Excel 등이 있다.
실무 예제를 통해 함수식을 작성함으로써 함수에 대한 사용 용도와 기능을 정확히 익혀서 필기와 실기 시험을 대비하는데 있어 적어도 함수 때문에 고민하는 일이 없었으면 하는 바램이다. 또한 이미 지난 호에서 살펴본 함수와 중복되는 함수는 예제조건에 따라 작성된 함수식으로 그 내용을 갈음하고자 한다.
제품별 생산 실적 현황 관리
Excel For Business Practice
Preview
Topic : 제품별 생산 실적 현황 관리
■ 제품코드를 입력받아 화면의 항목을 구하고 생산률과 순위를 통해 실적 통계를 관리한다.
작성 조건
· <코드표>를 참조하여 다음 아래의 항목에 대해 작업한다.
· 작성일을 입력한다.(형식 : 2013년 1월 9일(수)) 사용함수 : Today, choose, weekday
· 제품명 : 제품코드의 첫번째 자리의 구분으로 화면에 나타낸다.
· 원산지 : 제품코드의 두번째 자리의 구분으로 화면에 나타낸다.
· 생산금액 : 생산수량과 코드표의 생산단가를 이용하여 산출한다.
· 생산률(%) : 생산수량÷생산계획수량
· 순위 : 생산금액이 높은 순으로 순위를 산출한다.
· 비고 : 생산률이 40%이하이면 “생산부족”, 그렇치 않으면 공백으로 한다.
· 합계, 평균, 최대치, 최소치 : 생산계획수량, 생산수량, 생산금액
· 한국 합계 : 원산지가 한국의 경우에 한해 생산계획수량, 생산수량, 생산금액의 합을 산출한다.
· 작성일을 입력한다.(형식 : 2013년 1월 9일(수)) 사용함수 : Today, choose, weekday
· 제품명 : 제품코드의 첫번째 자리의 구분으로 화면에 나타낸다.
· 원산지 : 제품코드의 두번째 자리의 구분으로 화면에 나타낸다.
· 생산금액 : 생산수량과 코드표의 생산단가를 이용하여 산출한다.
· 생산률(%) : 생산수량÷생산계획수량
· 순위 : 생산금액이 높은 순으로 순위를 산출한다.
· 비고 : 생산률이 40%이하이면 “생산부족”, 그렇치 않으면 공백으로 한다.
· 합계, 평균, 최대치, 최소치 : 생산계획수량, 생산수량, 생산금액
· 한국 합계 : 원산지가 한국의 경우에 한해 생산계획수량, 생산수량, 생산금액의 합을 산출한다.
WEEKDAY → 요일을 구해준다.
사전적인 의미 엑셀에서의 기능
weekday 날짜에 해당하는 요일을 표시.
1. 주일(週日) (토,일요일 이외의 날) 기본적으로 요일은 1(일요일)에서 7(토요일)까지의 정수.
weekday 날짜에 해당하는 요일을 표시.
1. 주일(週日) (토,일요일 이외의 날) 기본적으로 요일은 1(일요일)에서 7(토요일)까지의 정수.
2. 평일
=WEEKDAY(날짜,반환값)
날짜 요일을 구하려는 날짜 텍스트 (예:“2013-12-2”)
반환값 반환값에 따라 요일을 결정하는 결과가 다름
1 또는 생략 1(일), 2(월), 3(화), 4(수), 5(목), 6(금), 7(토)까지의 숫자가 결과로 나타남
2 1(월), 2(화), 3(수), 4(목), 5(금), 6(토), 7(일)까지의 숫자가 결과로 나타남
3 0(월), 1(화), 2(수), 3(목), 4(금), 5(토), 6(일)까지의 숫자가 결과로 나타남
반환값 반환값에 따라 요일을 결정하는 결과가 다름
1 또는 생략 1(일), 2(월), 3(화), 4(수), 5(목), 6(금), 7(토)까지의 숫자가 결과로 나타남
2 1(월), 2(화), 3(수), 4(목), 5(금), 6(토), 7(일)까지의 숫자가 결과로 나타남
3 0(월), 1(화), 2(수), 3(목), 4(금), 5(토), 6(일)까지의 숫자가 결과로 나타남
CHOOSE → 값을 선택한다.
사전적인 의미 엑셀에서의 기능choose 고르다, 선택하다 인덱스 순번에 해당하는 인수 목록을 구합니다
=CHOOSE(인덱스 순번,인수1,인수2,…)
인덱스 순번 선택할 인수의 순번(1부터 254까지 지정)인수1,인수2 인덱스 순번에 해당하는 인수(1부터 254개까지 지정)
Exercise
Exercise
제품별 생산 실적 현황 작성
위와 같이 제품별 생산 실적 현황 작성을 위해 필요한 함수들에 대해 살펴 보았다. 이제 본격적으로 제품별 생산 실적 현황을 작성해 보자. 01 “제품별 생산 실적 및 코드” 시트에 제품별 생산 실적 현황 관리를 위해 다음과 같이 입력하여 작성한다.
02 “제품별 생산 실적 및 코드”시트의 [C4]셀에 =TODAY()를 입력한다.
03 ③ “제품별 생산 실적 및 코드”시트의 [D4]셀에 =”(“ &CHOOSE(WEEKDAYC4,1),”일”,”월”,”화”,”수”,”목”,”금”,”토“) & “)”를 입력한다.
※ [C4]셀에 =TODAY()를 입력한 다음 셀 서식(CTRL+1)의 [표시 형식]탭의 [범주]에서 “사용자 지정” 형식을 yyyy년 m월 d일(aaa)을 이용하여 서식을 지정하여도 같은 결과가 된다.
※ (aaa)→(수), (aaaa)→(수요일), (ddd)→(Wed), (dddd)→(Wednesday)
04 “제품별 생산 실적 및 코드”시트의 [C6]셀에 =IF(LEFT(B6,1)=”A”,”에어컨”,IF(LEFT(B6,1)=”C”,”카메라“,IF(LEFT(B6,1)=“O”,“오디오”,IF(LEFT(B6)=“K”,“캠코더”,IF(LEFT(B6)=“R”,“냉장고”,IF(LEFT(B6)=“Q”,“가습기”, “전화기”))))))을 입력한다. 그 다음 [C16]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
※ IF 함수 외에 이미 배운 VLOOKUP 함수를 이용하여 결과를 산출할 수도 있다.
→ =VLOOKUP(LEFT(B6,1),$E$24:$F$31,2,0)
05 “제품별 생산 실적 및 코드”시트의 [D6]셀에 =IF(MID(B6,2,1)=“K”,“한국”,IF(MID(B6,2,1)=“I”,“이탈리
아”,IF(MID(B6,2,1)=“C”,“캐나다”,IF(MID(B6,2,1)=“E”,“중동”,IF(MID(B6,2,1)=“F”,“프랑스”,IF(MID(B6,2,1)=“U”,”우루과이”,”아프리카”))))))을 입력한다. 그 다음 [D16]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
아”,IF(MID(B6,2,1)=“C”,“캐나다”,IF(MID(B6,2,1)=“E”,“중동”,IF(MID(B6,2,1)=“F”,“프랑스”,IF(MID(B6,2,1)=“U”,”우루과이”,”아프리카”))))))을 입력한다. 그 다음 [D16]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
※ 위의 수식 역시 IF함수 외에 이미 배운 VLOOKUP 함수를 이용하여 결과를 산출할 수도 있다.
→ =VLOOKUP(MID(B6,2,1),$H$24:$I$31,2,0)
06 “제품별 생산 실적 및 코드”시트의 [G6]셀에 =VLOOKUP(C6,$F$24:$G$31,2,0)*F6을 입력한다. 그 다음 [G16]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
07 “제품별 생산 실적 및 코드”시트의 [H6]셀에 =F6/E6을 입력한다. 그 다음 [H16]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
08 “제품별 생산 실적 및 코드”시트의 [I6]셀에 =RANK(G6,$G$6:$G$16)을 입력한다. 그 다음 [I16]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
09 “제품별 생산 실적 및 코드”시트의 [J6]셀에 =IF(H6<=40%,”생산부족”,””)을 입력한다. 그 다음 [J16]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
10 “제품별 생산 실적 및 코드”시트의 [E17]셀에 =SUM(E6:E16)을 입력한다. 그 다음 [G17]셀까지 채우기핸들을 이용하여 수식을 복사한다.
11 “제품별 생산 실적 및 코드”시트의 [E18]셀에 =AVERAGE(E6:E16)을 입력한다. 그 다음 [G18]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
12 “제품별 생산 실적 및 코드”시트의 [E19]셀에 =MAX(E6:E16)을 입력한다. 그 다음 [G19]셀까지 채우기핸들을 이용하여 수식을 복사한다.
13 “제품별 생산 실적 및 코드”시트의 [E20]셀에 =MIN(E6:E16)을 입력한다. 그 다음 [G20]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
14 “제품별 생산 실적 및 코드”시트의 [D22]셀을 클릭, [데이터] 탭의 [데이터 도구]그룹-[데이터 유효성 검사]를 클릭하여 실행한 다음 [데이터 유효성] 대화상자에서 제한 대상은 “목록”, 원본에는 “=$I$25:$I$31”을 입력하고 [확인]을 클릭한다. mok.jpg 드롭다운표시 단추를 이용하여 원하는 국가명을 선택할 수 있게 된다
15 “제품별 생산 실적 및 코드”시트의 [E22]셀에 =SUMIF($D$6:$D$16,$D$22,E6:E16)을 입력한다. 그 다음 [G22]셀까지 채우기 핸들을 이용하여 수식을 복사한다.
이상과 같이 배운 함수들을 가지고 제품별 생산 실적 현황을 관리해 보았다. 간단한 함수부터 여러 다른 함수로의 응용과 중첩이 가능한 예제로서 함수의 여러 면을 살펴보는 기회가 되었으면 한다. 2013년 한 해가 시작한지 어느새 벌써 한 달이 지났다. 새로운 출발을 준비하는, 시작의 준비가 미흡한 부분을 보충해 주기에 좋은 2월이 아닐까 싶다.
