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

 

환경: Microsoft Excel 2010

 

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

 

¤ 문제

 

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

 

≪조건≫

○ 모든 데이터의 서식에는 글꼴(맑은고딕, 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 로 하시면 그 반대로 내림차순이 됩니다.

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

 

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

 

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

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

 

⑵ 포인트「회원가×환산점수」로 구하되, 환산점수는 상품코드 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

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

 

“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)

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

 

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

 

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

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

 

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

 

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

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

 

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

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

 

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

 

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

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

 

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

 

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

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

 

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

 

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

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

 

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

 

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

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

 

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

 

¤ Lookup_value : 상품명의 위치

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

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

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

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

 

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

 

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

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

 

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

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

 

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

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

 

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

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

 

Posted by 녹두장군