112회 ITQ 엑셀 기출문제 풀이 – 표 작성 후 함수 이용해서 계산값 넣기, 3부

112회 ITQ 엑셀 기출문제 풀이 – 표 작성 후 함수 이용해서 계산값 넣기, 3부

 

환경: Microsoft Excel 2010

 

내용이 길어서 나누어 설명하겠습니다. 먼저 조건에 설명되어 있는 부분을 참고해서 출력형태를 만들어 보겠습니다. 필요한 부분만 만드는 과정을 설명하겠습니다.

 

¤ 문제

 

 

≪조건≫

○ 모든 데이터의 서식에는 글꼴(맑은고딕, 11pt), 맞춤은 ≪출력형태≫를 참조하시오.

○ 제 목 ⇒ 모서리가 둥근 직사각형과 바깥쪽 그림자 스타일(오프셋 오른쪽)을 이용하여 작성하고“신발나라 온라인 판매 현황”을 입력한 후 다음 서식을 적용하시오

(글꼴-맑은고딕, 24pt, 검정, 굵게, 채우기-노랑).

○ 임의의 셀에 결재란을 작성하여 카메라 또는 그림복사 기능을 이용하여 붙이기 하시오(단, 원본 삭제).

○ 「B4:J4, G14, I14」영역은 ‘주황’으로 채우기 하시오.

○ 유효성 검사를 이용하여「H14」셀에 상품명(「C5:C12」영역)이 선택 표시되도록 하시오.

○ 셀 서식 ⇒「G5:H12」영역에 셀 서식을 이용하여 숫자 뒤에 ‘EA’를 표시하시오. (예 : 1,270 → 1,270EA).

○ 「G5:G12」영역에 대해 ‘전월판매량’으로 이름정의를 하시오.

 

☞ ⑴~⑹ 셀은 반드시 주어진 함수를 이용하여 값을 구하시오(결과값을 직접 입력하면 해당 셀은 0점 처리됨).

⑴ 판매량 순위 ⇒ 당월 판매량의 내림차순 순위를 구한 결과값에 ‘위’를 붙이시오. (RANK 함수, & 연산자)(예 : 1위).

⑵ 포인트 ⇒「회원가×환산점수」로 구하되, 환산점수는 상품코드 2번째 자리 숫자가 1이면 0.05, 2이면 0.07, 3이면 0.09, 4이면 0.1로 계산하시오(CHOOSE, MID 함수).

⑶ 최소 전월 판매량 ⇒ 정의된 이름(전월판매량)을 이용하여 구하시오(MIN 함수).

⑷ 워커 상품 수 ⇒ (COUNTIF 함수)

⑸ 보고일 ⇒「I13」셀의 요일을 ‘월요일, 화요일…’의 형태로 구하시오(CHOOSE, WEEKDAY 함수).

⑹ 회원가 ⇒「H14」셀에서 선택한 상품명에 대한 회원가를 표시하시오(VLOOKUP 함수).

⑺ 조건부 서식을 이용하여 당월 판매량 셀에 데이터 막대 스타일(빨강)을 최소값 및 최대값으로 적용하시오.

 

¤ 해설

 

(1) 판매량 순위를 구하는 함수를 이용하는 것입니다. RANK 함수와 문자열을 합치는 & 연산자를 사용해서 “위” 를 붙이라고 하네요. 수식 편집기에 RANK 를 입력하고 fx 를 눌러 인수 대화상자를 엽니다.

 

● Number : 순위를 매길 셀을 선택합니다. 해당 셀이 전체 셀에서 몇 위 하는지 결정됩니다. 수식을 적용한 셀을 드래그 해서 내리면 셀 위치값도 따라서 바뀌겠죠.

● Ref : 비교 영역을 입력합니다. 영역을 지정한후 F4 를 눌러 절대주소로 바꿉니다.

● Order : 0 이나 빈값이면 오름차순으로 지정합니다. 값이 제일 높으면 1위가 되는 것이죠. 값을 1 로 하시면 그 반대로 내림차순이 됩니다.

 

▼ RANK 로 구한 순위값에 “위” 를 붙이기 위해 연산자 & 사용했습니다. 완성이 되었으면 다른 셀에도 적용하기 위해 드래그 해서 내립니다.

 

=RANK(H5,$H$5:$H$12,0) & " 위"

 

⑵ 포인트 ⇒「회원가×환산점수」로 구하되, 환산점수는 상품코드 2번째 자리 숫자가 1이면 0.05, 2이면 0.07, 3이면 0.09, 4이면 0.1로 계산하시오(CHOOSE, MID 함수).

 

▼ 먼저 환산점수는 계산해야 겠네요. 상품 코드값의 두번째 자리수를 MID 함수로 가져오겠습니다. 가져온 값에 따라 환산점수가 다르기 때문입니다. 상품코드값을 가져오기 위해 수식편집기에 MID 라고 입력한뒤 fx 를 클릭해서 함수 인수 대화상자를 띄웁니다.

 

● Text : 추출할 문자열

● Start_num : 첫번째 문자열의 위치, 두번째 코드값을 가져와야 하므로 2

● Num_chars : 가져올 문자개수, 두번째 코드값 하나 이므로 1

 

▼ “B1-06” 문자열에서 MID 를 이용해 두번째 코드값인 1 을 추출했습니다. 거기에 1일 때 0.05 이므로 CHOOSE 함수를 이용해서 구해야 합니다. CHOOSE 함수는 Index_num 에서 추출된 값에 따라 인수 목록중에 하나를 고를수 있습니다. 두번째 인수부터 1,2,3.. 이며 255 까지 입력이 가능합니다. 그러니까 Index_num 값이 1 이면 Value1 의 값을 리턴하게 되는 것입니다.

 

=CHOOSE(MID(B5,2,1),0.05,0.07,0.09,0.1)

 

▼ CHOOSE 와 MID 를 이용해서 상품 코드값에 따라 환산점수를 가져올수 있었습니다. 이제 회원가와 곱해주면 포인트 점수가 됩니다. 만약 소수점 2자리가 나온다면 Ctrl + 1 을 눌러 셀 서식 대화상자를 띄운뒤 소수 자릿수를 0 으로 만들거나 형식을 일반으로 바꿔줍니다.

 

=CHOOSE(MID(B5,2,1),0.05,0.07,0.09,0.1) * F5

 

⑶ 최소 전월 판매량 ⇒ 정의된 이름(전월판매량)을 이용하여 구하시오(MIN 함수).

 

▼ 조건 만들 때 “전월 판매량” 영역을 이름정의해서 추가했었죠. 그걸 이번에 사용할 것입니다. 이름 정의에 전월판매량이 있는지 확인해 보겠습니다. 수식탭 > 이름 관리자를 클릭하시면 이름 관리자 리스트에 “전월 판매량” 을 볼수 있죠.

 

▼ 문제에서 처럼 전월판매량중 최소값을 구하기 위해 MIN 함수를 사용해야 합니다. 함수의 인수로 전월판매량 셀 영역을 넣어야 하는데 정의된 이름이 있다면 그럴 필요가 없습니다. 아래 그림처럼 정의된 이름인 “전월판매량” 을 입력하시면 됩니다. 

 

⑷ 워커 상품 수 ⇒ (COUNTIF 함수)

 

▼ 상품 구분에서 “워커” 에 해당하는 제품이 몇 개 있는지 체크하는 문제 입니다. COUNTIF 를 사용하면 되는데 첫번째 인수에 범위를 넣고, 두번째 인수로 값을 입력합니다. Criteria 에 “등산화” 를 넣으면 값이 2가 되겠죠.

 

⑸ 보고일 ⇒「I13」셀의 요일을 ‘월요일, 화요일…’의 형태로 구하시오(CHOOSE, WEEKDAY 함수).

 

▼ I13 에 셀이 날짜로 지정이 되어 있습니다. 2014-11-29 일이 무슨 요일인지 알아내서 (5) 에 채워넣는 문제 입니다. 요일을 알아내는 함수는 WEEKDAY 입니다. 그러니까 WEEKDAY가 “월요일”, “화요일” 같은 요일명을 리턴하는 것이 아니라 숫자를 리턴합니다. 두가지 인수중 첫번째 Serial_number 에는 날짜 데이터를 넣고 두번째 인수는 요일을 결정하는 유형, 1,2,3 중에 하나를 입력합니다. 2 는 월요일이 1 이 되고 일요일이 7 입니다.

 

▼ WEEKDAY 함수로 리턴받은 값을 CHOOSE 함수의 첫번째 인수로 넣습니다. CHOOSE 함수는 첫번째 인수 값이 1 이면 두번째 인수 값인 Value1 을 선택하고 2 이면 다음 인수값을 선택합니다. 이렇게 255개까지 지정이 가능합니다. 요일은 일요일까지 7개만 지정하면 되겠죠. 이렇게 WEEKDAY 로 요일에 해당하는 숫자값을 리턴받아 CHOOSE 함수로 숫자가 아닌 텍스트를 선택해서 셋팅할수 있도록 함수를 만들어 보았습니다.

 

=CHOOSE(WEEKDAY(I13,2),"월요일","화요일","수요일","목요일","금요일","토요일","일요일")

 

⑹ 회원가 ⇒「H14」셀에서 선택한 상품명에 대한 회원가를 표시하시오(VLOOKUP 함수).

 

▼ 다음은 H14 에 있는 값을 상품명에서 찾아 해당하는 회원가 정보를 (6) 에 셋팅하는 것입니다. VLOOKUP 함수는 해당 영역에 값이 있을 때 동일한 행에 위치하는 다른 값을 지정해서 가져올수 있습니다.

 

▼ 화면을 보면서 다시 설명하자면 상품명에서 “코브라 209” 를 찾았다고 합시다. 그럼 회원가는 상품명이 있는 열을 기준으로 했을 때 4번째 위치하고 있죠. 4 를 인수로 넘기면 찾아진 행에 4번째에 있는 회원가 정보를 가져올수 있는 것입니다.

 

¤ Lookup_value : 상품명의 위치

¤ Table_array : 찾고자 하는 영역. 상품명 영역을 시작으로 회원가 전체를 말하죠.

¤ Col_index_num : 위에서 말했듯이 상품명을 기준으로 회원가의 열 위치를 말합니다.

¤ Range_lookup : 값을 정확하게 찾고자 할 때 FALSE 로 지정합니다.

 

⑺ 조건부 서식을 이용하여 당월 판매량 셀에 데이터 막대 스타일(빨강)을 최소값 및 최대값으로 적용하시오.

 

▼ 조건부 서식을 이용해서 아래 그림처럼 값을 간단하게 그래프화 하는 것입니다. 숫자 보다는 간단한 셀 그래프로 표현하면 좀더 명확하고 가독성이 있습니다.

 

▼ 상단에 있는 홈탭 > [조건부 서식] 리본메뉴를 클릭해서 규칙 관리자를 띄웁니다. 규칙 관리자 하단에 보시면 [새 규칙] 버튼이 있습니다. 클릭해서 새로운 규칙을 만들어 봅시다.

 

▼ 셀에 있는 값을 간단한 그래프로 표현하기 위해 1번부터 4번까지 값을 따라서 지정합니다. [셀값을 기준으로 모든 셀의 서식지정] 을 선택한 뒤 [서식 스타일]을 데이터 막대로 지정합니다. 그리고 채우기는 그라데이션을 주고 색상을 선택합니다.

 

▼ 확인을 누르시고 규칙관리자 창으로 이동합니다. 마지막으로 해야 될 일이 규칙을 적용할 영역을 지정하는 것입니다. 적용대상에 당월 판매량을 드래그 해서 지정하시면 됩니다.