오랜만에 포스팅합니다 ㅎㅎ
저도 취준생이라 요즘 바쁘네요.. ㅎㅎ..
오늘은 엑셀의 계산작업 중 IF, IFERROR, ISERROR, CHOOSE 함수에 대해 알아보겠습니다.
[문제]
표-1의 각 필드를 이용하여 표-1의
1. 3월 성적과 6월 성적의 평균이 전체 3월 성적과 6월 성적의 평균보다 크다면,
"이수", 그렇지 않다면 "미이수"로 H3:H14에 표시. (단, IF함수와 AVERAGE함수를 이용)
2. 결석 + 태도를 I3:I14에 표시. 단, 결석이 "없음"인 경우 태도+10으로 표시
(IFERROR함수를 이용)
3. 결석 + 태도를 J3:J14에 표시. 단, 결석이 "없음"인 경우 태도+10으로 표시
(ISERROR함수와 IF함수를 사용)
4. 반필드가 짝수인 경우 "짝수반", 홀수인 경우 "홀수반"으로 K3:K14에 표시
(CHOOSE, MOD, SUM, AVERAGE 함수 중 옳은 함수를 사용)
문제 풀이에 들어가기 앞서!
계산작업 문제 주의사항
이 부분에 대해 먼저 언급하고 싶습니다.
두 문제의 차이점을 찾으셨나요?
~함수와 ~함수를 사용
~함수, ~함수, ~함수 중사용
계산문제를 풀이하다 보시면 자주는 아니더라고
직면할 수 있는 상황입니다.
사실, 전자든 후자든,
문제에서 주어진 함수로 풀이가 가능합니다.
전자의 경우,
주어진 함수를 모두 사용하라. 라는 의미이며,
주어진 함수를 모두 사용할 시, 추가적인 함수를 사용해도 감점되지 않습니다.
(보통 추가적인 함수를 사용하는 경우는 흔치 않습니다. 다만, &와 ""의 활용이 추가되는 정도입니다.)
후자의 경우,
주어진 함수 중 사용할 함수를 본인이 선택해야 합니다.
시간적 여유가 있다면, 모든 함수를 다 사용하면서 점검을 할 수 있지만,
시간이 없을 경우, 본인의 판단력이 중요하기 때문에 많은 문제를 풀어보시는 것을 권장드립니다.
이제 문제를 풀어볼게요 ㅎㅎ
1. 3월 성적과 6월 성적의 평균이 전체 3월 성적과 6월 성적의 평균보다 크다면,
"이수", 그렇지 않다면 "미이수"로 H3:H14에 표시. (단, IF함수와 AVERAGE함수를 이용)
AVERAGE함수의 경우 지난 시간에 말씀드렸기 때문에 다들 아시겠...죠?
IF의 경우,
=IF ( 수식, 수식이 옳으면 표시할 값, 수식이 옳지 않다면 표시할 값) 입니다.
함수의 경우 직접 수식란에 입력하실 수도 있지만,
보통의 함수 마법사를 사용합니다.
그림의 1번처럼 수식란 왼쪽 Fx(함수 삽입 or 함수마법사)를 클릭하시면 함수를 검색하실 수 있어요.
(아마 상공회의소 엑셀은 버전이 낮아서, 함수를 완전하게 입력해야 할 겁니다.)
그래서 if와 average를 사용해서 옳은 식을 작성하면,
이런 식이 되구요.
=if(average(d3:e3) > average($d$3:#$e$14), "이수", "미이수")
식의 작성을 완성하고
식이 작성된 셀의 우측하단에 드래그 채우기 버튼이 있습니다. (초록색 네모)
이를 클릭하여 문제의 범위까지 드래그까지 하셔야
감정 대상에서 제외됩니다.
그리고 드디어 ㅎㅎ
절대값($)의 예시를 제대로 사용한 것 같습니다 ㅠㅠ
문제와 같이 드래그를 하게 되면 범위도 이동하게 됩니다.
그렇기 때문에 처음 D3:E3에는 절대값을 사용하지 않은 것입니다.
어차피 드래그로 내릴 것이기 때문에 자동으로 각 행에 맞춰서 식이 작성됩니다.
하지만 전체 평균의 경우,
드래그를 사용할 시 전체 평균의 범위 또한 내려가게 됩니다.
그러면 전체의 평균의 제대로 된 값을 구할 수 없겠죠?
그렇기 때문에 이를 방지하기 위해서 전체의 평균은 절대값을 사용한 것입니다.
[2번, 3번의 문제의 경우, 억지로 오류값을 만들어야 해서 문제가 허접합니다...]
2. 결석 + 태도를 I3:I14에 표시. 단, 결석이 "없음"인 경우 태도+10으로 표시
(IFERROR함수를 이용)
1번 그림은 iferror 함수를 사용하지 않아 오류값이 발생한 것을 보여주기 위해 첨가했습니다.
1과 9를 더하면 10이 되겠지만,
"없음"과 10을 더하면 값이 나올 수 없겠죠?
값 대신 오류값이 나옵니다.
이 오류값은 변형 시키기 위한 것이 iferror함수 입니다.
=iferror(식, 식이 오류일 시 표시할 값)
=iferror(f3+g3, g3+10)
그래서 그림 2번과 같이 오류값이 나오는 식을 포함하여 함수를 작성하시고,
문제의 요구대로 태도+10을 해줍니다.
함수 작성을 완료하시면,
문제 1번의 마지막처럼 드래그해서 마무리해줍니다.
3. 결석 + 태도를 J3:J14에 표시. 단, 결석이 "없음"인 경우 태도+10으로 표시
(ISERROR함수와 IF함수를 사용)
먼저,
IF ERROR와
IS ERROR는 다른 함수입니다. (그래서 문제를 잘 보셔야 해요..)
IFERROR는 만약 본인이 작성한 식이 "오류값"이라면, "~~~"로 표시해줄게. 라는 함수이고
ISERROR는 본인이 작성한 식이 "오류값"이면 TRUE로 표시하고, 오류값이 아니라면 FALSE로 표시할게. 라는 함수입니다.
한 글자 차이지만,
의미가 정말 다르기때문에 문제를 꼼꼼히 읽어보시는 것을 권장드립니다.
3번문제가 2번문제와 다른 점은,
2번문제는 IFERROR를 썼기 때문에 원하는 값을 바로 표시할 수 있지만,
3번의 ISERROR는 TRUE나 FALSE로 표시되기 때문에 추가적인 함수를 더 사용합니다.
문제의 경우 IF함수를 사용하여
IF(ISERROR= FALSE라면, 태도+결석, 그렇지 않다면 태도+10)라는 식으로 표시했습니다.
반대의 경우도 가능합니다.
IF(ISERROR=TRUE라면, 태도+10, 그렇지 않다면 태도+결석)도 가능합니다.
모든 수식을 완료하시면, 드래그해서 마무리해줍니다.
4. 반필드가 짝수인 경우 "짝수반", 홀수인 경우 "홀수반"으로 K3:K14에 표시
(CHOOSE, MOD, SUM, AVERAGE 함수 중 옳은 함수를 사용)
저는 위 함수 중 choose와 mod함수를 사용하겠습니다.
choose함수는 값, 식의 결과가
1일 경우 A로 표시
2일 경우 B로 표시
3일 경우 C로 표시 등등등 254까지 변환시켜주는 함수입니다.
mod함수는 나머지를 구하는 함수입니다.
=mod(10,5) ▶ 0
=mod(10,2) ▶ 0
=mod(10,3) ▶ 1
이처럼 앞의 수를 뒤의 수로 나눠서 나오는 나머지의 값을 표시합니다.
(몫을 구하는 함수는 QUOTIENT입니다. 사용 방법은 mod함수와 동일)
mod함수는 해당 셀이 짝수냐 홀수냐를 묻는 문제에 자주 사용됩니다.
(물론 iseven과 isodd를 사용하면 더 편합니다. 하지만 컴활은 여러분이 불편하길 원해서...)
바로 해당 셀을 "2"로 나눠어 산출된 나머지로 홀짝을 구분합니다.
10을 2로 나누면 나머지가 0이지요?
나머지가 0이 나오면 짝수입니다.
반대로 9를 2로 나누면 나머지가 1이지요?
나머지가 1이면 홀수입니다.
위와 동일한 방법으로 해당 문제의 홀짝을 구분해주시고,
mod함수를 choose함수로 옮겨줍니다.
편의상 작성된 mod함수에서 "="를 제외하고 잘라내기 하는 것을 권장드립니다.
나중에 match나 vlookup함수를 다룰 때
다시 작성하는 것이 너무 번거로워 복붙하는게 가장 편합니다.
하지만 여기서!
그대로 옮기면 오류값이 나옵니다.
왜why?
앞에서 구한 mod함수는 0과 1로만 구성된 결과가 나오기 때문입니다.
그리고 choose함수는 0을 구할 수 없는 함수입니다.
두 함수가 모순되죠?
그래서 mod함수의 값에 강제로 +1을 해줍니다.
그러면 mod함수의 결과는 1과 2로 구성될테고,
chooose함수도 1부터 값이 나와 오류값을 피할 수 있습니다.
하지만 그러면 결과의 값이 달라지겠죠?
강제로 1을 더했으니까 mod의 결과가 달라지겠죠.
즉 +1을 한 mod의 값이 1이라고 해서 홀수가 아니라는 것입니다.
1을 더해줬으니까 그 1은 사실 짝수가 되는 것이지요.
그렇기 때문에 choose함수의
value1에는 "짝수반"이라고 작성합니다.
=choose(mod(b3,2)+1, "짝수반", "홀수반")
이 방법이 좀 복잡하긴 하지만,
많은 문제를 풀어보시면 그렇게 어려운 부분은 아닙니다.
모든 식을 작성하셨다면,
드래그해서 마무리해줍니다.
새애애애앵각보다 길어졌네요...
하나의 함수만 설명하고 싶었는데
같이 연계된 함수들이 너무 많아서
추리고 추려서
if함수 먼저 포스팅하게 되었습니다.
긴 글 읽어주셔서 감사합니다.
공감, 댓글, 피드백 언제나 환영입니다.
'일단 해봅시다 > 컴활 1급 실기' 카테고리의 다른 글
| #6-1. 컴활1급실기 계산작업1 (hlookup, vlookup, match함수) (0) | 2020.05.01 |
|---|---|
| #5. 컴활1급실기 계산작업1 (date, year, month, day, eomonth, days360함수) (0) | 2020.05.01 |
| #3. 컴활1급실기 기본작업 3 (조건부서식) (0) | 2020.05.01 |
| #2. 컴활1급실기 기본작업 1-1문제 추가자료 (0) | 2020.02.29 |
| #1. 컴활1급실기 기본작업 1-1번 문제 (0) | 2020.02.29 |