박봉달의 개발생활
[컴활:실기#3] 시험 전 꼭 보고 가세요! 엑셀 기출문제 계산작업-2 본문
안녕하세요, 박봉달입니다!
저번 컴활 후기를 작성한 이후, 본격적으로 공부를 시작해보려고 합니다.
엑셀과 엑세스를 시험에서 나오는 순서에 맞춰 정리하려고 합니다.
도움이 되길 바라며 (물론 저에게도..)
정리글 시작합니다.
[컴활:실기#1] 분노에 차서 요약하는 컴활 실기 정리글 / 머리글..
안녕하세요, 박봉달입니다! 제가 20년 12월 마지막 시험을 봤는데요, 뭐 하나를 크게 틀려서 실기합격에 결국 실패했습니다.. 엑세스는 통과했는데 엑셀은 틀려서요.. 결국 1월 1일에 실기시험
things-voyager.tistory.com
2-1) 표1의 도서분류, 출판일, 정가와 표3을 이용하여 [G3:G29] 영역에 포인트를 계산하여 표시하시오
- 포인트 = 정가 * 적립률
- INDEX, MATCH, YEAR 함수 사용
이번 문제는 INDEX와 MATCH를 이용하는 문제군요!
자주 나오는 문제 중 하나입니다.
포인트 = 정가 * 적립률 이라는 기준이 주어졌으니 이걸 이용하여 해결해야겠죠?
정가 * 적립률(MATCH와 INDEX 사용)으로 해결하면 될 것 같습니다.
INDEX는 표나 범위 내에서 값을 구하는 함수이며
MATCH는 배열에서 저장된 값에 일치하는 위치를 구하는 함수입니다.
INDEX에서 ARRAY는 구하고자 하는 값이 있는 범위
ROW는 가로, COLUMN은 세로의 값을 의미합니다.
MATCH에서 MATCH_TYPE은 1, 0, -1이 들어갈 수 있습니다
- 1 또는 생략 : 찾을값보다 작거나 같은값 중 가장 큰 값을 찾습니다. 범위는 반드시 오름차순으로 정렬되어야 합니다.
- 0 : 찾을값과 정확히 일치하는 값을 찾습니다. 정렬순서와 상관없이 동작합니다.
- -1 : 찾을값보다 크거나 같은값 중 가장 작은 값을 찾습니다. 범위는 반드시 내림차순으로 정렬되어야 합니다.
따라서 =$F3*INDEX($F$34:$I$40,MATCH($A3,$E$34:$E$40,0),MATCH(YEAR($D3),$F$32:$I$32,1)) 가 됩니다.
2-2) 표1의 판매량을 이용하여 [J3:J29] 영역에 다음과 같이 "★"를 표시하시오
- 판매량이 8200일 경우 : ★★★★★★★★☆☆
- 판매량이 5900일 경우 : ★★★★★☆☆☆☆☆
- CONCATENATE, REPT, QUOTIENT 함수 사용
이번 문제는 치환하는 문제입니다. 판매량을 잘 입력하고 그 수를 문자로 치환합니다.
CONCATENATE는 문자열을 이어붙이는 함수
REPT는 텍스트를 지정한 횟수만큼 반복하는 함수
QUOTIENT는 나눗셈 몫의 정수 부분을 반환하는 함수입니다.
여기서 살짜쿵 팁은 = 하고 함수를 입력할 때 바로 함수마법사로 들어가지 마시고 어떤 함수인지 한번 읽어보시면 도움이 됩니다.
Concatenate로 처음에 시작을 해야 까만 별도 흰색 별도 함께 이어붙일 수 있습니다.
REPT는 텍스트를 반복하는 함수인데 얼마나 반복하는 거냐? 판매량을 별 10개로 기준했을 때(=별 하나에 1000)
그럼 QUIOTENT는 어떻게 쓰냐하면 판매량을 1000으로 나누면 되겠죠!
그럼 흰색 별은 어떻게 하나요?
흰색 별은 만점(별 10개)에서 까만별을 뺀거니까 10을 빼주면 끝!
=CONCATENATE(REPT("★",QUOTIENT($I3,1000)),REPT("☆",10-QUOTIENT($I3,1000)))
2-3) 사용자 정의함수 'fn비고'를 작성하여 비고[K3:K29]을 계산하여 표시하시오
- 'fn비고'는 회원 리뷰수와 판매량을 인수라 받아 상품추천을 계산하는 함수
- 상품 추천은 회원리뷰수가 80 이상이고 판매량이 6000 이상이면 "추천", 회원 리뷰수만 80 이상이면 "관심도서", 판매량만 6000 이상이면 "인기도서", 그 외에는 공백으로 표시하시오
Public Function fn비고(회원리뷰수, 판매량)
End Function
사용자 정의 함수가 나왔습니다. 사용자 정의 함수는 수식에서 가장 쉬운 부분이니 정 막힌다 싶으면 이것부터 먼저 풀길 추천!! 사용자 정의 함수니까 함수를 정의하는 걸 먼저 해야겠죠?
개발도구 -> Visual Basic 을 클릭!
클릭해서 삽입 -> 모듈을 클릭!
그럼 아래와 같이 코드를 입력해서 함수를 작성하는 폼이 나옵니다.
Public Function fn비고(회원리뷰수, 판매량)
End Function
를 입력해서 함수를 정의하기 시작합니다.
함수를 정의하면 아래와 같은데요. 상품 추천은 회원리뷰수가 80 이상이고 판매량이 6000 이상이면 "추천", 회원 리뷰수만 80 이상이면 "관심도서", 판매량만 6000 이상이면 "인기도서", 그 외에는 공백으로 표시하라는 조건을 지켜야 합니다.
여기서 엑셀은 if문을 끝낼 때는 end if 를 꼭 입력해줘야한다는 점!
작성을 끝내고 엑셀로 돌아갑니다. 그리고 함수를 적용시켜야겠죠.
함수 마법사로 가서 사용자 정의 함수를 가면 선언 했던 fn비고 함수가 있는 걸 알수 있습니다.
그럼 클릭해서 각 인자를 알맞게 집어 넣으면 결과가 나오게 됩니다!
이렇게 사용자 정의 함수도 끝~
2-4) 표1의 도서분류와 회원리뷰수를 이용하여 표2의 [B33:B39] 영역에 분야별 회원리뷰수가 50 이상인 도서의 개수를 계산하여 표시하시오
- SUM, IF 함수를 사용한 배열 수식으로 작성
이제 마의 배열 수식이 나왔습니다. 배열 수식에서 기억해야할 내용은 ~이거나 이면 +, ~이고 라고 하면 *를 사용해야합니다.
그리고 배열수식을 마무리할 때는 Ctrl+Shift+Enter로 마무리!
보통 배열수식을 작성할 때는 IF 함수가 꼭 나옵니다.
IF 함수에서 논리 확인을 할 떄 배열 수식을 작성하여 조건을 맞추는 것이 중요합니다.
도서의 총 개수를 계산하는 것이기 때문에 처음 시작은 SUM으로 열어줍니다.
그리고 IF 함수로 논리 확인을 하게 됩니다.
보통 배열=A일 경우 라는 식으로 배열 수식을 작성하게 됩니다.
그리고 개수를 세는 경우에는 참일 경우 1을 넣어주어서 숫자가 누적되게 만들어줘야합니다
하지만 실제 시험에서는 이것보다 훨씬 훨씬 훨씬 어려워요...
2-5) 표1의 도서분류와 판매량을 이용하여 표2의 [C33:C39] 영역에 도서분류 판매량의 평균을 계산하여 표시하시오
- AVERAGE, IF 함수를 사용한 배열 수식으로 작성
배열 수식이 나왔습니다. 배열 수식은 두번 나와요.
다시한번, 배열 수식에서 기억해야할 내용은 ~이거나 이면 +, ~이고 라고 하면 *를 사용해야합니다.
그리고 배열수식을 마무리할 때는 Ctrl+Shift+Enter로 마무리!
사실 이것도 시험에서 나오는 난이도의 10분의 1 정도입니다..
평균을 구하는 것이기 때문에 AVERAGE로 시작을 합니다.
그리고 IF 함수를 사용하여 논리 검사를 하는데, 조건이 하나라 간단해 보입니다.
이상 엑셀 영역의 두번째 파트, 계산작업에 대해 알아보았습니다
계산 작업은 총점이 30점입니다.
이상 마치겠습니다.
읽어주셔서 감사합니다 :)
'Qualification > 컴활 1급' 카테고리의 다른 글
| [컴활:실기#5] 시험 전 꼭 보고 가세요! 엑셀 기출문제 기타 작업-4 (0) | 2021.02.07 |
|---|---|
| [컴활:실기#4] 시험 전 꼭 보고 가세요! 엑셀 기출문제 분석 작업-3 (0) | 2021.02.07 |
| [컴활:실기#2] 시험 전 꼭 보고 가세요! 엑셀 기출문제 기본작업 -1 (0) | 2021.01.30 |
| [컴활:실기#1] 분노에 차서 요약하는 컴활 실기 정리글 / 머리글.. (0) | 2021.01.29 |
