10월 2018 - 엑셀 노하우 Who?

2018-10-30

다중 조건 함수, SWITCH 조건식이 특정한 값을 가지는 경우

This entry is part [part not set] of 5 in the series 논리 함수

다중 조건 함수, SWITCH

이전글에서 IF문을 중첩하여 사용하여 다중 조건의 참, 거짓을 판단하는 함수 IFS 를 배웠습니다.
이번에는 조건문이 특정한 값을 가질 때 사용할 수 있는 함수 SWITCH 를 배워보겠습니다.

SWITCH 함수는 엑셀 2019 버전, 오피스 365 엑셀 2016 이상에서 사용 가능합니다.

이전 글에서 다중 비교 함수 IFS 를 살펴봤습니다.
IFS 함수를 사용하면 IF 함수를 여러번 중첩해서 사용하지 않아도 되는 것을 확인습니다.
IFS 함수를 IF 함수 중첩을 완전히 대체할 수는 없지만, 많은 경우에 유용하게 사용할 수 있었습니다.

SWITCH 함수는 조건식이 특정한 값을 가지는 경우에 IFS 함수를 효율적으로 대체할 수 있습니다.

아래의 그림을 보신 후 설명을 읽어보시기 바랍니다.

다중 조건 함수, IFS 함수로 문제 해결 시도

이전에 많이 사용했던 예제를 그대로 사용하겠습니다.
이전 예제에서 추가된 부분은 J 열에 추가된 가점 항목입니다.
가점은, 등급이 “수” 인 경우 10점, 등급이 “우” 인 경우 5점, 등급이 “미” 인 경우 3점, 그 외에는 0점 입니다.

등급에 따라 가점을 부여하기 위해 함수 IFS 함수를 사용해 보겠습니다.
J3 셀에 IFS 함수를 사용해서 문제를 해결해보겠습니다.

J3 셀에 함수 수식 =IFS(I3=”수”,10,I3=”우”,5,I3=”미”,3) 을 입력합니다.

그림의 결과에서 보여지듯이 위의 IFS 함수 수식 으로는 문제를 완벽히 해결할 수 없습니다.
등깁이 “수”, “우”, “미” 인 경우는 정상적으로 출력되지만, 나머지 경우는 “#NA” 오류가 출력됩니다.
J3 셀에 입력된 함수 수식은 텍스트로 주어진 등급별 가점 조건에 따라 작성되었지만, 등급이 “양”, “가” 인 경우에 대한 점수가 정의되지 않아 오류가 발생한 것 입니다.
IFS 함수를 사용해서 “양”, “가” 인 경우에 가점 0점을 주기 위해서는 아래와 같이 조건과 조건이 참일 때의 값을 추가로 정의해줘야 합니다.

오류를 수정하기 위해 J3 셀에 입력된 함수 수식을 아래와 같이 수정해 보겠습니다.

J3 셀에 함수 수식 =IFS(I3=”수”,10,I3=”우”,5,I3=”미”,3,I3=”양”,0,I3=”가”,0) 을 입력합니다.

이제야 모든 학생의 가점이 올바르게 출력되고 있습니다.
즉, IFS 함수를 사용하는 경우 주어진 조건에 해당하지 않는 값에 대한 조건식과 결과값도 모두 정의해줘야 올바른 결과를 출력할 수 있는 것 입니다.
이번 예제와 같이 두 개의 조건에 대해서만 추가로 정의하는 경우는 문제가 어렵지 않게 해결됩니다.
하지만, 주어진 조건에 해당되지 않는 경우가 무수히 많은 경우에는 그것을 일일히 지정하기가 쉽지 않습니다.

다중 조건 함수, SWITCH 조건식이 특정한 값을 가지는 경우 사용 방법

SWITCH 함수를 사용하면 위의 경우와 같은 예제를 간단하게 해결할 수 있습니다.

단, 모든 버전의 엑셀에서 SWITCH 함수를 지원하는 것은 아니기 때문에, 모든 분에게 해결책이 될 수는 없습니다.

SWITCH 함수를 사용한 해결 방법을 아래의 그림을 통해 확인해 보겠습니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(SWITCH)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =SWITCH( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (expression, value1, result1, [default_or_value2, result2], …) 의 의미는 아래와 같습니다.

expression : 조건식을 첫 번째 인수로 입력해야 합니다. (필수 입력)
value1 : 조건식과 비교할 첫 번째 값을 두 번째 인수를 입력해야 합니다. (필수 입력)
result1 : 조건식과 value1 과 일치할 경우 출력할 값을 세 번째 인수를 입력해야 합니다. (필수 입력)
default_or_value2 : 더이상 비교할 값이 없는 경우 출력할 값 또는 조건식과 비교할 두 번째 값을 네 번째 인수로 입력해야 합니다. (선택 입력)
result2 : value2 가 입력되었다면 조건식이 value2 와 일치할 경우 출력할 값을 다섯 번째 인수를 입력해야 합니다. (선택 입력)
… : 조건식과 비교할 값과 일치할 경우 출력할 내용을 쌍으로 계속해서 입력할 수 있습니다. (선택 입력)

J3 셀에 =SWITCH(I3,”수”,10,”우”,5,”미”,3,0) 라고 함수 수식을 입력합니다.
그러면 결과값 10 이 출력됩니다.
나머지 학생의 가점 셀에도 함수를 복사하여 붙여넣습니다.
모든 학생의 가점이 정확하게 출력되고 있는 것을 확인할 수 있습니다.
조건식과 비교할 값은 “수”, “우”, “미” 세 개의 값 뿐 이지만, default 값을 지정함으로써 그 외의 경우는 0점 으로 출력하도록 할 수 있습니다.
사용된 함수 수식이 무척 간단해진 것을 알 수 있습니다.

IFS 함수 VS SWITCH 함수

두 함수에 사용된 함수 수식을 비교해 보겠습니다.

=IFS(I3=”수”,10,I3=”우”,5,I3=”미”,3,I3=”양”,0,I3=”가”,0)
=SWITCH(I3,”수”,10,”우”,5,”미”,3,0)

눈으로 대충 봐도 함수 수식이 무척 간단해 진 것이 보입니다.
이런 결과가 가능해 진 것은 바로 SWITCH 함수의 default 값 때문 입니다.

IFS 에서는 default 와 같은 기능을 할 수 있는 고정적인 방법이 없습니다.
문제의 경우에 따라 그에 맞는 조건식과 출력값을 적당히 추가할 수 밖에 없습니다.

SWITCH 함수가 만능은 아닙니다. 조건식과 비교하는 value 에는 값만 입력할 수 있습니다.
value 에 범위(range)를 입력할 수 없기 때문에 조건식이 특정한 값을 가지는 경우를 비교할 때에 제 기능을 발휘할 수 있습니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-29

IFS 함수 VS IF 함수 중첩 사용, 사용 가능한 예제

This entry is part [part not set] of 5 in the series 논리 함수

IFS 함수 VS IF 함수 중첩

이전글에서 입력된 조건식의 참, 거짓을 판단하는 함수 IF를 배웠습니다.
이번에는 IF문을 중첩하여 사용하여 다중 조건의 참, 거짓을 판단하는 함수 IFS를 배워보겠습니다.

IFS 함수는 엑셀 2016 버전 이상에서 사용 가능합니다.

다중 비교 함수 IFS 를 설명하기 위해 다중 조건 비교가 필요한 예제를 살펴보겠습니다.
아래의 그림을 보신 후 설명을 읽어보시기 바랍니다.

이전에 많이 사용했던 예제를 그대로 사용하겠습니다.
이전 예제에서 추가된 부분은 I 열에 추가된 등급 항목입니다.
등급은, 평균 값이 90점 이상인 경우 “수”, 80점 이상인 경우 “우”, 70점 이상인 경우 “미”, 60점 이상인 경우 “양”, 60점 미만인 경우 “가” 입니다.
학생의 수가 여섯 명 이라면 위의 그림 처럼 수, 우, 미, 양, 가 등급을 직접 입력하는 것이 빠를겁니다.
하지만, 이렇게 입력하는 것은 엑셀을 사용한다고 보기 어려운 방식입니다. (워드를 사용하는 것과 다름 없습니다.)

조건에 따라 등급을 부여하기 위해 함수 IF를 사용해야 합니다.
I3 셀에 IF 함수를 사용해서 문제를 해결해보겠습니다.

I3 셀에 함수 수식 =IF(H3>=90,”수”,”우”) 을 입력합니다. (단일 IF 함수 사용)

위의 IF 함수 수식 으로는 문제를 완벽히 해결할 수 없습니다.
평균 점수가 90점 이상인 경우에 “수” 는 정상적으로 출력되지만, 나머지(조건식이 거짓인) 경우는 모두 “우” 가 출력됩니다.
우연하게도 80점 이상인 이다혜 학생의 경우 “우” 로 출력되었지만, 정상적으로 얻은 결과는 아닙니다.

IF 함수는 한 번에 하나의 조건식에 대한 비교를 하고, 참과 거짓인 경우에 출력할 값을 인수로 갖고 있습니다.
하지만, 위의 예제는 조건식이 5개 필요합니다. 즉, 하나의 IF 함수를 사용해서 해결할 수 없는 문제인 것 입니다.

IF 함수 중첩 1단계

IF 함수로 여러개의 조건식을 비교하기 위해서는 함수 중첩을 해야 합니다.
함수를 중첩해서 사용하는 것은 말 그대로 입니다.

=함수(…함수(…함수(…))) 주의할 점은 여는 괄호”(” 와 닫는 괄호 “)” 의 개수가 같아야 한다는 것 입니다.

I3 셀에 IF 함수 중첩 사용해서 문제를 해결해보겠습니다.

I3 셀에 함수 수식 =IF(H3>=90,”수”,IF(H3>=80,”우”,”미”)) 을 입력합니다. (중첩 IF 함수 사용)

H3>=90 조건식이 참(TRUE)인 경우는 “수” 를 출력하지만, 거짓(FALSE)인 경우는 다시 IF 함수를 사용하여 새로운 조건식의 참, 거짓을 판단합니다.
즉, IF 함수의 거짓일 때 출력할 값의 인수에 다시 IF 함수를 입력하여 조건식의 참과 거짓을 판단하게 하는 것 입니다. (처음 접할시 개념이 복잡하게 느껴질 수 있습니다.)
수식 자체만 보면 오히려 어려울 수 있습니다.
수식의 내용을 우리말로 풀어내서 읽어보면 좀 더 쉽게 이해할 수 있습니다.

“평균 점수가 90점 이상인 경우(조건식) / 참(TRUE) 이면 “수” 를 출력하고 / 거짓(FALSE)이면 다시 평균 점수가 80점 이상인지 확인(조건식)해서 참(TRUE) 이면 “우” 를 출력하고 거짓(FALSE)이면 “미” 를 출력한다.”

이 부분을 충분히 이해해야 두 개 이상의 함수의 중첩도 이해할 수 있게 됩니다.

하지만, 위의 IF 함수 수식 으로는 문제를 완벽히 해결할 수 없습니다.
IF 함수를 두 번 중첩 사용했고, 두 개의 조건식에 대한 처리만 가능한 함수 수식 입니다.
평균 점수가 80점 미만인 유지태, 김민정, 박정아, 서경준 학생의 등급은 정상적으로 출력되지 않고 있습니다. (김민정, 박정아 학생의 등급 “미” 는 우연히 맞게 출력된 것일 뿐!)

IF 함수 중첩 2단계

I3 셀에 IF 함수 중첩 단계를 더 사용해서 문제를 해결해보겠습니다.

I3 셀에 함수 수식 =IF(H3>=90,”수”,IF(H3>=80,”우”,IF(H3>=70,”미”,”양”))) 을 입력합니다. (중첩 IF 함수 사용)

위의 IF 함수 수식 으로는 문제를 완벽히 해결할 수 없습니다.
IF 함수를 세 번 중첩 사용했고, 세 개의 조건식에 대한 처리만 가능한 함수 수식 입니다.

예제로 주어진 학생 중 점수가 60점 미만인 학생이 없기 때문에, 결과로 출력된 것들에 문제는 없습니다.
하지만, 아직 60점 미만에 대한 등급을 처리하는 조건식은 없기 때문입니다.

IF 함수 중첩 3단계

I3 셀에 IF 함수 중첩 단계를 더 사용해서 문제를 해결해보겠습니다.

I3 셀에 함수 수식 =IF(H3>=90,”수”,IF(H3>=80,”우”,IF(H3>=70,”미”,IF(H3>=60,”양”,”가”)))) 을 입력합니다. (중첩 IF 함수 사용)

드디어 IF 함수 수식 으로 문제를 완벽히 해결했습니다.
IF 함수를 네 번 중첩 사용했고, 네 개의 조건식에 대한 처리만 가능한 함수 수식 입니다.

함수의 중첩 사용을 통해 복잡한 문제를 해결했습니다.
하지만 I3 셀에 입력된 함수 수식은 점점 더 복잡한 형태로 변해가는 것을 확인할 수 있었습니다.
함수를 중첩하여 사용하면 하나의 함수 수식을 통해 다양한 결과값을 처리할 수 있습니다.
하지만 함수 수식 작성이 쉽지 않다는 단점이 있습니다.
함수의 인수로 다시 함수 수식이 들어가는 것은 엑셀에 익숙한 사용자에게도 쉽지 않은 일 입니다.
수식작성기를 통해 조금 더 쉽게 작성할 수는 있지만 그것도 쉬운 것은 아닙니다.

IFS 함수

복잡한 함수의 중첩을 해결해주는 함수들이 있습니다.
주로 원래의 함수명에 S 가 덧붙여져 사용됩니다. (COUNTIFS, SUMIFS, AVERAGEIFS)
위의 문제를 해결하는데 사용할 함수는 IFS 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(IFS)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =IFS( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (logical_test1, value_if_true1, …) 의 의미는 아래와 같습니다.

logical_test1 : 참과 거짓을 판단하려는 조건식을 첫 번째 인수로 입력해야 합니다. (필수 입력)
value_if_true1 : 조건식이 참인 경우 출력할 내용을 두 번째 인수를 입력해야 합니다. (필수 입력)
… : 조건식과 조건식이 참인 경우 출력할 내용을 쌍으로 계속해서 입력할 수 있습니다. (선택 입력)

I4 셀에 =IFS(H4>=90,”수”,H4>=80,”우”,H4>=70,”미”,H4>=60,”양”,H4<60,”가”) 라고 함수 수식을 입력합니다.
그러면 결과값 “우” 가 출력됩니다.
IFS 함수의 첫 번째 조건식 부터 비교를 시작합니다.
첫 번째 조건식 H4>=90 이 거짓(FALSE) 이므로, 두 번째 조건식 H4>=80 을 비교합니다.
이다혜 학생의 평균 점수 H4 셀의 값이 80 이고 IFS 함수의 두 번째 조건식 비교시 참(TRUE) 이 되므로 “우” 가 출력되는 것 입니다.

IF 함수 중첩, IFS 함수 사용시 왼쪽의 조건식 부터 참(TRUE)/거짓(FALSE) 를 비교하고 참이 되는 순간 오른쪽에 남은 조건식은 비교하지 않고 무시합니다.

따라서 두 번째 조건식이 참인 경우에 출력되는 “우” 가 출력된 것 입니다.

IF 함수 중첩 VS IFS 함수 비교

IF 함수 중첩 사용과 IFS 함수 사용의 차이점을 살펴보겠습니다.

IF 함수 중첩은 총 네 개의 IF 함수가 중첩 사용 되었습니다. VS IFS 함수는 한 개의 IFS 함수만 사용 되었습니다.
IF 함수 중첩 함수 수식을 입력하기 위해 여는 괄호 “(” 네 번, 닫는 괄호 “)” 네 번 쌍으로 사용 되었습니다. VS IFS 함수는 한 개의 여는 괄호 “(“, 닫는 괄호 “)” 가 사용 되었습니다.
IF 함수 중첩은 마지막 IF 함수 에서 모든 조건식이 거짓(FALSE) 인 경우 값 처리가 가능합니다. VS IFS 함수는 각 조건식이 참인 경우에 대해서만 값 처리가 가능합니다.

IFS 함수가 IF 함수를 중첩해서 사용하는 대부분의 경우에 대해 대체를 할 수 있을 것 입니다.
하지만 모든 경우에 대체가 가능한 것은 아닙니다.
위의 차이점을 잘 읽어보면 어떤 경우에 대체가 불가능할지 알 수 있습니다.

IFS 함수는 모든 조건식이 거짓인 경우에 출력한 값을 설정하는데 문제가 있을 수 있습니다. 경험을 통해 알게 되는 부분 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-26

여러 조건에 맞는 셀 평균 함수, AVERAGEIFS 다중 조건 평균 함수

This entry is part [part not set] of 6 in the series 집계 합계 함수

여러 조건에 맞는 셀 평균 함수, AVERAGEIFS

이전에 SUMIFS 함수에 대해 배웠습니다.
SUMIFS 함수는 여러 개의 조건에 맞는 셀 값의 합계를 구하는 함수 입니다.
AVERAGEIFS 함수는 여러 조건에 맞는 셀 값의 평균을 구하는 함수 입니다.

즉, 참조 범위에서 여러 조건에 맞는 셀 값의 평균을 구한 후 결과값을 출력하는 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(AVERAGEIFS)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =AVERAGEIFS( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) 의 의미는 아래와 같습니다.

average_range : 조건에 맞는 평균을 계산할 셀 참조 범위 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
criteria_range1 : 조건에 맞는지 비교할 셀 참조 범위 값을 두 번째 인수로 입력해야 합니다. (필수 입력)
criteria1 : 적용할 조건을 비교할 셀 참조 범위에 적용할 조건 문자열 값을 세 번째 인수로 입력해야 합니다. (필수 입력)
[criteria_range2] : 조건에 맞는지 비교할 셀 참조 범위 값을 네 번째 인수로 입력해야 합니다. (선택 입력)
[criteria2] : 적용할 조건을 비교할 셀 참조 범위에 적용할 조건 문자열 값을 다섯 번째 인수로 입력해야 합니다. (선택 입력)
… : 이전의 선택 입력 인수를 참조 범위와 조건의 쌍으로 계속 추가할 수 있습니다. (선택 입력)

국어 점수가 80점 이상, 영어 점수도 80점 이상인 학생의 합계 점수 평균을 계산해 보겠습니다.
G10 셀에 =AVERAGEIFS($G$3:$G$8,$D$3:$D$8,”>=80″,$E$3:$E$8,”>=80″) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “250” 값이 출력됩니다.
참조 범위의 숫자 값 중 국어 점수 >=80, 영어 점수 >=80 에 맞는 학생은 위에서부터 순서대로 최민준, 박정아 학생 입니다.
국어 점수 80점 이상, 영어 점수 80점 이상인 두 명의 학생의 합계 점수를 더해보면 270+230=500 이며, 2로 나눠 평균을 계산하면 250, 출력된 결과와 일치합니다.
AVERAGEIFS 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

여러 조건에 맞는 셀 평균 함수, AVERAGEIFS 연습

이번엔 조건을 조금 변경해 보겠습니다.
영어 점수가 80점 이상, 수학 점수도 80점 이상인 학생의 평균 점수 평균을 계산해 보겠습니다.

여러 조건에 맞는 셀 평균 함수 AVERAGEIFS 다중 조건 평균 함수

G10 셀에 =AVERAGEIFS($H$3:$H$8,$E$3:$E$8,”>=80″,$F$3:$F$8,”>=80″) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “85” 값이 출력됩니다.
참조 범위의 숫자 값 중 영어 점수 >=80, 수학 점수 >=80 에 맞는 학생은 위에서부터 순서대로 최민준, 이다혜 학생 입니다.
영어 점수 80점 이상, 수학 점수 80점 이상인 두 명의 학생의 평균 점수를 더해보면 90+80=170 이며, 2로 나눠 평균을 계산하면 85, 출력된 결과와 일치합니다.
AVERAGEIFS 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

AVERAGEIF VS AVERAGEIFS

AVERAGEIF 함수와 AVERAGEIFS 함수의 차이점은 어떤 것이 있을까요?

AVERAGEIF 함수는 average_range 가 선택 입력 값,SUMIFS 함수는average_range 가 필수 입력 값 이며 첫 번째 인수로 온다.
AVERAGEIF 함수는 단일 조건을 만족하는 셀 값의 평균,SUMIFS 함수는 다중 조건을 만족하는 셀 값의 평균이다.

오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-26

조건에 맞는 셀 평균 함수, AVERAGEIF 함수 쉽게 실습 해보기

This entry is part [part not set] of 6 in the series 집계 합계 함수

조건에 맞는 셀 평균 함수, AVERAGEIF

이전에 SUMIF 함수에 대해 배웠습니다.
SUMIF 함수는 조건에 맞는 셀의 개수가 아닌 셀 값의 합계를 구하는 함수 입니다.
AVERAGEIF 함수는 조건에 맞는 셀의 합계가 아닌 셀 값의 평균을 구하는 함수 입니다.

즉, 참조 범위에서 조건에 맞는 셀의 값의 평균을 구한 후 결과값을 출력하는 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(AVERAGEIF)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =AVERAGEIF( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (range, criteria, [average_range]) 의 의미는 아래와 같습니다.

range : 조건을 비교할 셀 참조 범위 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
criteria : 적용할 조건을 비교할 셀 참조 범위에 적용할 조건 문자열 값을 두 번째 인수로 입력해야 합니다. (필수 입력)
[average_range] : 평균을 계산할 셀 참조 범위 값을 세 번째 인수로 입력해야 합니다. 생략 가능하며, 생략시 첫 번째 인수 range 값의 합계를 출력합니다. (선택 입력)

평균 점수가 80점 이상인 학생의 국어 점수 평균을 계산해 보겠습니다.
G10 셀에 =AVERAGEIF($H$3:$H$8,”>=80″,D3:D8) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “75” 값이 출력됩니다.
참조 범위의 숫자 값 중 평균 점수 >=80 에 맞는 학생은 위에서부터 순서대로 최민준, 이다혜 학생 입니다.
평균 점수 80점 이상인 두 명의 학생의 국어 점수를 더해보면 80+70=150 이며, 2로 나눠 평균을 계산하면 75, 출력된 결과와 일치합니다.
AVERAGEIF 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

조건에 맞는 셀 평균 함수, AVERAGEIF 연습

이번엔 조건을 조금 변경해 보겠습니다.
영어 점수가 80점 이상인 학생의 수학 점수 평균을 계산해 보겠습니다.

G10 셀에 =AVERAGEIF($E$3:$E$8,”>=80″,$F$3:$F$8) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “77.5” 값이 출력됩니다.
참조 범위의 숫자 값 중 영어 점수 >=80 에 맞는 학생은 위에서부터 순서대로 최민준, 이다혜, 박정아, 서경준 학생 입니다.
영어 점수 80점 이상인 네 명의 학생의 수학 점수를 더해보면 100+90+70+50=310 이며, 4로 나눠 평균을 계산하면 77.5, 출력된 결과와 일치합니다.
AVERAGEIF 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

만약 세 번째 인수를 생략하면 어떻게 될까요?
위에서 AVERAGEIF 함수의 인수에 대한 설명을 자세히 보신 분은 답을 알고 계실겁니다.
잘 모르시겠다고요? 그러면 Just Do It!

오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-25

여러 조건에 맞는 셀 합계 함수, SUMIFS 다중 조건 합계 함수

This entry is part [part not set] of 6 in the series 집계 합계 함수

여러 조건에 맞는 셀 합계 함수, SUMIFS 다중 조건 합계 함수

이전에 SUMIF 함수에 대해 배웠습니다.
SUMIF 함수는 한 개의 조건에 맞는 셀 값의 합계를 구하는 함수 입니다.
SUMIFS 함수는 여러 조건에 맞는 셀 값의 합계를 구하는 함수 입니다.

즉, 참조 범위에서 여러 조건에 맞는 셀의 값을 더한 후 결과값을 출력하는 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(SUMIFS)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =SUMIFS( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) 의 의미는 아래와 같습니다.

sum_range : 조건에 맞는 합계를 계산할 셀 참조 범위 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
criteria_range1 : 조건에 맞는지 비교할 셀 참조 범위 값을 두 번째 인수로 입력해야 합니다. (필수 입력)
criteria1 : 적용할 조건을 비교할 셀 참조 범위에 적용할 조건 문자열 값을 세 번째 인수로 입력해야 합니다. (필수 입력)
[criteria_range2] : 조건에 맞는지 비교할 셀 참조 범위 값을 네 번째 인수로 입력해야 합니다. (선택 입력)
[criteria2] : 적용할 조건을 비교할 셀 참조 범위에 적용할 조건 문자열 값을 다섯 번째 인수로 입력해야 합니다. (선택 입력)
… : 이전의 선택 입력 인수를 참조 범위와 조건의 쌍으로 계속 추가할 수 있습니다. (선택 입력)

국어 점수가 80점 이상, 영어 점수도 80점 이상인 학생의 합계 점수 합계를 계산해 보겠습니다.
G10 셀에 =SUMIFS($G$3:$G$8,$D$3:$D$8,”>=80″,$E$3:$E$8,”>=80″) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “500” 값이 출력됩니다.
참조 범위의 숫자 값 중 국어 점수 >=80, 영어 점수 >=80 에 맞는 학생은 위에서부터 순서대로 최민준, 박정아 학생 입니다.
국어 점수 80점 이상, 영어 점수 80점 이상인 두 명의 학생의 합계 점수를 더해보면 270+230=500 이며, 출력된 결과와 일치합니다.
SUMIFS 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

여러 조건에 맞는 셀 합계 함수, SUMIFS 연습

이번엔 조건을 조금 변경해 보겠습니다.
영어 점수가 80점 이상, 수학 점수도 80점 이상인 학생의 합계 점수 합계를 계산해 보겠습니다.

G10 셀에 =SUMIFS($D$3:$D$8,$E$3:$E$8,”>=80″,$F$3:$F$8,”>=80″) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “150” 값이 출력됩니다.
참조 범위의 숫자 값 중 영어 점수 >=80, 수학 점수 >=80 에 맞는 학생은 위에서부터 순서대로 최민준, 이다혜 학생 입니다.
영어 점수 80점 이상, 수학 점수 80점 이상인 두 명의 학생의 국어 점수를 더해보면 80+70=150 이며, 출력된 결과와 일치합니다.
SUMIFS 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

SUMIF VS SUMIFS

SUMIF 함수와 SUMIFS 함수의 차이점은 어떤 것이 있을까요?

SUMIF 함수는 sum_range 가 선택 입력 값, SUMIFS 함수는 sum_range 가 필수 입력 값 이며 첫 번째 인수로 온다.
SUMIF 함수는 단일 조건을 만족하는 셀 값의 합계, SUMIFS 함수는 다중 조건을 만족하는 셀 값의 합계이다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-25

조건에 맞는 셀 합계 함수, SUMIF 함수 쉽게 실습 해보기

This entry is part [part not set] of 6 in the series 집계 합계 함수

조건에 맞는 셀 합계 함수, SUMIF

이전에 COUNTIF 함수에 대해 배웠습니다.
COUNTIF 함수는 조건에 맞는 셀 개수 세기 함수 입니다.
SUMIF 함수는 조건에 맞는 셀의 개수가 아닌 셀 값의 합계를 구하는 함수 입니다.

즉, 참조 범위에서 조건에 맞는 셀의 값을 더한 후 결과값을 출력하는 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(SUMIF)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =SUMIF( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (range, criteria, [sum_range]) 의 의미는 아래와 같습니다.

range : 조건을 비교할 셀 참조 범위 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
criteria : 적용할 조건을 비교할 셀 참조 범위에 적용할 조건 문자열 값을 두 번째 인수로 입력해야 합니다. (필수 입력)
[sum_range] : 합게를 계산할 셀 참조 범위 값을 세 번째 인수로 입력해야 합니다. 생략 가능하며, 생략시 첫 번째 인수 range 값의 합계를 출력합니다. (선택 입력)

평균 점수가 70점 이상인 학생의 국어 점수 합계를 계산해 보겠습니다.
G10 셀에 =SUMIF($H$3:$H$8,”>=70″,$D$3:$D$8) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “300” 값이 출력됩니다.
참조 범위의 숫자 값 중 평균 점수 >=70 에 맞는 학생은 위에서부터 순서대로 최민준, 이다혜, 김민정, 박정아 학생 입니다.
평균 점수 70점 이상인 네 명의 학생의 국어 점수를 더해보면 80+70+70+80=300 이며, 출력된 결과와 일치합니다.
SUMIF 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

조건에 맞는 셀 합계 함수, SUMIF 연습

이번엔 조건을 조금 변경해 보겠습니다.
평균 점수가 70점 이상인 학생의 합계 점수 합계를 계산해 보겠습니다.

G10 셀에 =SUMIF($H$3:$H$8,”>=70″,$G$3:$G$8) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

G10 셀에 “950” 값이 출력됩니다.
참조 범위의 숫자 값 중 평균 점수 >=70 에 맞는 학생은 위에서부터 순서대로 최민준, 이다혜, 김민정, 박정아 학생 입니다.
평균 점수 70점 이상인 네 명의 학생의 합계 점수를 더해보면 270+240+210+230=950 이며, 출력된 결과와 일치합니다.
SUMIF 함수가 정상적인 결과값을 출력했음을 확인할 수 있습니다.

이전의 예와 다른 부분은 SUMIF 함수의 세 번째 인수 입니다.
필수적인 인수는 아니지만, SUMIF 함수를 쓸 때 거의 매번 사용되는 인수 입니다.
두 예제 모두, 조건의 참조 범위는 평균 점수로 동일하지만, 합계를 구할 참조 범위는 서로 다릅니다.

만약 세 번째 인수를 생략하면 어떻게 될까요?
위에서 SUMIF 함수의 인수에 대한 설명을 자세히 보신 분은 답을 알고 계실겁니다.
잘 모르시겠다고요? 그러면 Just Do It!

오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-24

다중 조건 건수 세기 함수, COUNTIFS 사용법

This entry is part [part not set] of 6 in the series 집계 합계 함수

다중 조건 건수 세기 함수, COUNTIFS 사용법

이전글에서 COUNTIF 함수에 대해 배웠습니다.
COUNTIF 함수는 인수로 주어진 참조 값 또는 참조 범위에서 숫자의 개수를 세는 함수 입니다.
COUNTIFS 함수는 COUNTIF 함수를 확장하여 다중 조건에 맞는 건수를 세는 함수 입니다.

즉, 참조 범위에서 두개 이상의 조건에 맞는 셀 개수 세기 를 수행한 후 결과값을 출력하는 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(COUNTIFS)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =COUNTIFS( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (criteria_range1, criteria1, …) 의 의미는 아래와 같습니다.

criteria_range1 : 개수를 셀 참조 범위1의 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
criteria1 : 개수를 셀 참조 범위1에 적용할 조건 문자열 값을 두 번째 인수로 입력해야 합니다. (필수 입력)
… : criteria_range1, criteria1 참조 범위와 조건이 쌍을 이룬 형태로 세 번째, 네 번재 인수, 그리고 계속적으로 입력될 수 있습니다. (선택 입력)

다중 조건 건수 세기를 할 내용은 “국어 >= 70, 평균 >= 80” 입니다.

단순히 조건을 텍스트로 풀어서 표현한 것으로 조건의 참조 주소로 사용되지는 못합니다.

F10 셀에 =COUNTIF($D$3:$D$8,”>=70″,$H$3:$H$8,”>=80″) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.
국어 >= 70 : $D$3:$D$8,”>=70″ 으로 해석하여 인수 입력.
평균 >= 80 : $H$3:$H$8,”>=80″ 으로 해석하여 인수 입력.

F10 셀에 “2” 값이 출력됩니다.
참조 범위의 숫자 값 중 국어(D열) 점수가 =>70, 평균(H열) 점수가 >=80 에 맞는 값은 위에서부터 순서대로 최민준, 이다혜 두 명 입니다.
개수를 세어보면 2개로 출력된 결과와 일치합니다.

다중 조건 건수 세기 함수, COUNTIFS 예

이번엔 조건을 변경하여 다중 조건 건수 세기 함수 COUNTIFS 를 수정해 보겠습니다.
다중 조건 건수 세기를 할 내용은 “영어 >= 70, 합계 >= 230” 입니다.

단순히 조건을 텍스트로 풀어서 표현한 것으로 조건의 참조 주소로 사용되지는 못합니다.

F10 셀에 =COUNTIF($E$3:$E$8,”>=80″,$G$3:$G$8,”>=230″) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.
영어 >= 80 : $E$3:$E$8,”>=80″ 으로 해석하여 인수 입력.
합계 >= 230 : $G$3:$G$8,”>=80″ 으로 해석하여 인수 입력.

F10 셀에 “3” 값이 출력됩니다.
참조 범위의 숫자 값 중 영어(E열) 점수가 =>80, 합계(G열) 점수가 >=230 에 맞는 값은 위에서부터 순서대로 최민준, 이다혜, 박정아 세 명 입니다.
개수를 세어보면 3개로 출력된 결과와 일치합니다.

조건에 따라 함수 수식을 변경하는 것을 연습해 보시기 바랍니다.

COUNTIF VS COUNTIFS

COUNTIF 함수와 COUNTIFS 함수의 차이점을 바로 이해하셨나요?
COUNTIFS, 함수명 끝에 S 하나가 더 붙은 형태로 보면 단수, 복수와 연관이 있을 것 같습니다.

맞습니다. COUNTIF 함수가 단일 조건에 대한 건수를 세는 함수라면, COUNTIFS 함수는 복수 조건에 대한 건수를 세는 함수 입니다.
COUNTIF 함수를 사용해도 되는 곳에 COUNTIFS 함수를 사용할 필요는 없습니다.

COUNTIF 함수를 COUNTIFS 함수로 변경할 경우가 생긴다고 해도 함수명에 S를 더 붙이고 참조 범위와 조건 인수를 쌍의 형태로 추가하면 됩니다.

오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-24

조건에 맞는 셀 개수 세기 함수, COUNTIF 건수 세기 함수

This entry is part [part not set] of 6 in the series 집계 합계 함수

조건에 맞는 셀 개수 세기 함수, COUNTIF 건수 세기 함수

이전에 COUNT 함수에 대해 배웠습니다.
COUNT 함수는 인수로 주어진 참조 값 또는 참조 범위에서 숫자의 개수를 세는 함수 입니다.
COUNTIF 함수는 COUNT 함수에 IF(조건)이 추가된 함수 입니다.

즉, 참조 범위에서 조건에 맞는 셀 개수 세기 를 수행한 후 결과값을 출력하는 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(COUNTIF)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =COUNTIF( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (range, criteria) 의 의미는 아래와 같습니다.

range : 개수를 셀 참조 범위 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
criteria : 개수를 셀 참조 범위에 적용할 조건 문자열 값을 두 번째 인수로 입력해야 합니다. (필수 입력)

E3 셀에 =COUNTIF($B$3:$B$14,”>50″) 함수 수식을 입력합니다.

참조 범위 선택시 마우스로 범위를 선택하여 입력한 후 F4(펑션키)를 눌러 절대참조 주소로 변환했습니다.

E3 셀에 “5” 값이 출력됩니다.
참조 범위의 숫자 값 중 조건 >50 에 맞는 값은 위에서부터 순서대로 321,63, 212, 53, 62 입니다.
개수를 세어보면 5개로 출력된 결과와 일치합니다.

조건에 참조 주소를 입력하여 건수 세기

위의 예에서는 두 번째 인수인 조건을 “>50” 텍스트로 직접 입력했습니다.
이번엔 조건을 텍스트가 아닌 참조 주소로 입력해 보겠습니다.

E2 셀에 조건 텍스트인 “>50” 을 입력합니다.
E3 셀에 =COUNTIF($B$3:$B$14,E2) 함수 수식을 입력합니다.

COUNTIF 함수의 두 번째 인수인 criteria 에 입력되어 있는 조건 텍스트 “>50” 을 참조 주소인 E2로 변경하여 수식을 완성합니다.

E3 셀에 “5” 값이 출력됩니다.
조건 텍스트 대신 참조 주소를 입력해도 동일하게 동작합니다.
조건 텍스트 대신 참조 주소에 조건 텍스트 값을 입력해서 COUNTIF 함수를 사용시 어떤 장점이 있을까요?

조건을 자유롭게 변경하여 입력하여 건수 세기

만약, 30보다 큰 수의 개수를 세려면 어떻게 해야 할까요?

조건에 텍스트를 사용해서 작성된 =COUNTIF($B$3:$B$14,”>50″) 함수 수식 이라면, 수식을 직접 수정해야 할 것입니다.
조건에 참조 주소를 사용해서 작성된 =COUNTIF($B$3:$B$14,E2) 함수 수식 이라면, 참조 주소의 값만 수정하면 됩니다.

위의 그림에서 볼 수 있듯, 조건 참조 주소 E2 셀의 값을 변경하면, E3 셀의 함수 수식을 변경하지 않고도 원하는 조건에 맞는 숫자의 개수를 셀 수 있습니다.
“>30”, “>100” 으로 조건을 변경하여 확인해 본 예제 입니다.
30보다 큰 셀의 개수는 10개, 100보다 큰 셀의 개수는 2개, 정확한 개수가 출력되었습니다.

오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-23

논리 함수, AND OR 논리곱 논리합 한눈에 보기

This entry is part [part not set] of 5 in the series 논리 함수

논리 함수, AND OR 논리곱 논리합 한눈에 보기

엑셀에서 논리 값은 무엇인가요? 참(TRUE), 거짓(FALSE) 값을 가지는 것을 논리 값이라 합니다.
엑셀은 컴퓨터에서 실행되는 응용프로그램 입니다.
컴퓨터는 실제 화면에 보이는 값이 아니라, 내부적으로 모든 값을 0과 1의 2진수로 처리합니다.
0은 거짓(FALSE), 1은 참(TRUE) 입니다.
대부분의 프로그래밍 언어에서는 이와는 조금 다르게 처리됩니다. 0은 거짓(FALSE), 0이 아닌 수는 참(TRUE)으로 처리됩니다.
엑셀에서도 대부분의 프로그래밍 언어와 동일하게 0은 거짓(FALSE), 0이 아닌 수는 참(TRUE)으로 처리됩니다.
TRUE, FALSE 로 논리 값을 입력하는 것은 사람이 보기에 편하기 위해서가 주요한 이유 입니다.

논리 함수, AND

영단어 “AND” 의 사전적 의미는 “그리고” 입니다.
“나는 사과 그리고 배를 가지고 있습니다.” 는 사과와 배 둘 다 가지고 있다는 뜻 입니다.
즉, 사과도 있고 배도 있을 때만 참(TRUE)이 됩니다.
둘 중 하나가 없다면 위의 문장은 거짓(FALSE)이 됩니다.
논리 함수 AND 도 위의 문장을 해석하는 것과 동일하게 해석할 수 있습니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(AND)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =AND( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (logical1, [logical2], …) 의 의미는 아래와 같습니다.

logical1 : AND 연산에 사용할 논리 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
[logical2] : AND 연산에 사용할 논리 값을 두 번째 인수로 입력해야 합니다. (선택 입력)
… : 앞의 인수와 동일한 형태의 값을 계속해서 입력할 수 있다는 의미 입니다.. (선택 입력)

D3 셀에 =AND(B3,C3) 함수 수식을 입력합니다.
D3 셀에 “FALSE” 값이 출력됩니다.
AND 함수는 인수로 입력 받은 모든 값이 참(TRUE) 일 때 참(TRUE)을 출력합니다.
따라서 D3 셀의 값은 =AND(FALSE, FALSE) 의 결과인 FALSE 가 출력되는 것이 맞습니다.
D3 셀의 함수 수식을 D4:D6 범위에 복사하여 붙여넣습니다.
D4:D6 셀에 함수 수식의 결과값이 출력되었습니다.
참(TRUE)인 경우는 D6 셀 뿐 입니다.
AND 함수에 모두 참(TRUE) 인수가 입력된 경우는 D6 셀이 유일하기 때문에 당연한 결과입니다.

논리 함수, OR

영단어 “OR” 의 사전적 의미는 “또는” 입니다.
“나는 사과 또는 배를 가지고 있습니다.” 는 사과와 배 둘 중 적어도 하나는 가지고 있다는 뜻 입니다.
즉, 사과만 있을 때 참, 배만 있을 때, 사과와 배 둘 다 있을 때 모두 (TRUE)이 됩니다.
단, 둘 모두가 없다면 위의 문장은 거짓(FALSE)이 됩니다.
논리 함수 OR 도 위의 문장을 해석하는 것과 동일하게 해석할 수 있습니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(OR)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =OR( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (logical1, [logical2], …) 의 의미는 아래와 같습니다.

logical1 : OR 연산에 사용할 논리 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
[logical2] : OR 연산에 사용할 논리 값을 두 번째 인수로 입력해야 합니다. (선택 입력)
… : 앞의 인수와 동일한 형태의 값을 계속해서 입력할 수 있다는 의미 입니다.. (선택 입력)

E3 셀에 =OR(B3,C3) 함수 수식을 입력합니다.
E3 셀에 “FALSE” 값이 출력됩니다.
OR 함수는 인수로 입력 받은 값 중 적어도 하나의 값이 참(TRUE) 일 때 참(TRUE)을 출력합니다.
따라서 E3 셀의 값은 =OR(FALSE, FALSE) 의 결과인 FALSE 가 출력되는 것이 맞습니다.
E3 셀의 함수 수식을 E4:E6 범위에 복사하여 붙여넣습니다.
E4:E6 셀에 함수 수식의 결과값이 출력되었습니다.
참(TRUE)인 경우는 E4, E5, E6 셀 입니다.
거짓(FALSE)인 겨우는 E3 셀 하나 입니다.
OR 함수에 모두 거짓(FALSE) 인수가 입력된 경우는 E3 셀이 유일하기 때문에 당연한 결과입니다.

논리 함수, AND 곱셈 연산

논리 함수 AND 는 곱셈 연산을 한다고 표현합니다.
이 말의 의미를 확인해 보겠습니다.

F3 셀에 =B3*C3 수식을 입력합니다.
F3 셀에 “0” 값이 출력됩니다.
FALSE(0)*FALSE(0)=0(FALSE) 의 계산이 되어 0이 출력된 것 입니다.
F3 셀의 함수 수식을 F4:F6 범위에 복사하여 붙여넣습니다.
F4:F6 셀에 함수 수식의 결과값이 출력되었습니다.
1(TRUE)인 경우는 F6 셀 뿐 입니다.
논리 값 곱셈의 결과는 둘 다 참(TRUE) 인 값이 곱해진 F6 셀이 유일하기 때문에 당연한 결과입니다.

논리 함수, OR 덧셈 연산

논리 함수 OR 는 덧셈 연산을 한다고 표현합니다.
이 말의 의미를 확인해 보겠습니다.

G3 셀에 =B3+C3 수식을 입력합니다.
G3 셀에 “0” 값이 출력됩니다.
FALSE(0)+FALSE(0)=0(FALSE) 의 계산이 되어 0이 출력된 것 입니다.
G3 셀의 함수 수식을 G4:G6 범위에 복사하여 붙여넣습니다.
G4:G6 셀에 함수 수식의 결과값이 출력되었습니다.
0(FALSE)인 경우는 G3 셀 뿐 입니다.
논리 값 덧셈의 결과는 둘중 적어도 하나는 1(TRUE) 인 값이 더해진 G4, G5, G6 셀이 되는 당연한 결과입니다.

논리 함수, AND OR 곱셈 덧셈 연산 결과 확인

논리 값의 곱셈, 덧셈 연산 결과인 숫자에 대해 AND, OR 논리 함수 연산을 해보는 예제 입니다.
논리곱(AND)의 경우 곱셈(F) 열과 덧셈(G) 열 모두의 값이 0(FALSE)이 아닌 경우 TRUE(참) 가 출력되고 있습니다.
논리합(OR)의 경우 곱셈(F) 열과 덧셈(G) 열 값 중 적어도 하나만 1(TRUE)인 경우 TRUE(참) 가 출력되고 있습니다.

논리값1, 논리값2 의 값을 숫자로 표현한 것으로 비교하지 않아서 정확한 비교는 아니지만,
참(TRUE / 0이 아닌 수) 거짓(FALSE / 0) 논리 값을 AND, OR 논리 함수를 통해 다루는 방법을 연습하는데는 문제가 없습니다.

오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기

2018-10-23

텍스트 주소 참조 함수, INDIRECT 간접 주소 참조 함수

This entry is part [part not set] of 6 in the series 참조 함수

텍스트 주소 참조 함수, INDIRECT

이전글에서 VLOOKUP 함수를 대체할 수 있는 INDEX, MATCH 함수를 배웠습니다.
두 가지 방식 모두 찾을 값이 포함된 참조 범위를 절대참조 방법으로 지정해야 한다고 했습니다.
예제에서 상대참조 방식을 사용한 경우도 있었는데, 이는 아주 특이한 경우에만 그렇습니다.
대부분의 경우는 절대참조 방식을 사용해야 합니다.
이번 글에서는 참조 범위를 지정할 때 절대참조 방식에 비해 좀 더 유연하게 지정할 수 있는 텍스트 주소 참조 함수 INDEIRCT 함수에 대해 배워보겠습니다.

엑셀에서 값을 참조하기 위해 가장 많이 사용하는 함수는 바로 VLOOKUP 입니다.
보통 =VLOOKUP(E3,$B$3:$C$8,2,FALSE) 형태로 함수 수식을 작성합니다.
여기서 주목할 부분은 두 번째 인수인 table_array $B$3:$C$8 입니다.
절대참조 방식으로 참조 범위가 지정된 것을 보실 수 있습니다.
절대참조로 지정된 참조 범위는 수식을 복사할 경우에도 그 값이 자동으로 변하지 않습니다.
VLOOKUP 함수를 사용하는 대부분의 경우는, 변하지 않는 참조 범위에서 찾는 값을 검색하여 그 행의 다른 열에 있는 값을 참조하는 것 입니다.
학생의 이름을 찾는 값으로 하여 생년월일 값을 참조하는 예에서 이를 확인했습니다.

만약 VLOOKUP 함수의 참조 범위가 유동적이 되어야 한다면, 기존에 작성된 함수 수식에 어떤 변화가 나타날까요?
$B$3:$C$8 절대참조 참조 범위를 수정하고 적용된 전체 셀에 복사해서 붙여넣기 해야합니다.
데이터 건수가 적은 경우에는 간단하지만, 건수가 늘어나면 쉽지 않습니다.
이때 활용할 수 있는 것이 텍스트 주소 참조 함수 INDIRECT 입니다.
간단한 예제를 통해 확인해 보겠습니다.

VLOOKUP 함수 상대참조 방식의 문제

이전 글에서 VLOOKUP 함수의 참조 범위 지정시 절대참조 방식을 사용하는 것을 권장한다고 했습니다.
상대참조 방식을 사용하면 어떤 문제가 발생하기 때문인지에 대한 설명은 자세히 하지 않았습니다.
위의 그림을 보고 확인해 보겠습니다.

F3 셀에 입력된 VLOOKUP 함수 수식의 두 번째 인수 table_array 는 상대참조 방식 B3:C8 으로 작성되어 있습니다.
다른 학생의 이름으로 생년월일 찾는 부분을 하나 더 만들기 위해 E3:F3 범위를 복사하여 E4:F4 범위에 붙여넣기 했습니다.
그러자 F4 셀에 “#N/A” 오류가 발생했습니다.
오류의 원인을 확인하기 위해 F4 셀에서 펑션키F2 를 눌렀더니, 참조 범위가 이전과 달라진 것이 보입니다.
상대참조 방식으로 작성했기 때문에 복사 붙여넣기 작업시 수식이 자동으로 변경 적용되었기 때문입니다.
그 결과 김민준 학생이 포함되지 않은 참조 범위가 VLOOKUP 함수에 입력 되었고, 오류가 발생했습니다.
이 문제를 해결하기 위한 방법은 바로 참조 범위를 절대참조 방식으로 지정하는 것 입니다.

F3 셀의 함수 수식을 =VLOOKUP(E3,$B$3:$C$8,2,FALSE) 으로 변경합니다.
두 번째 인수를 상대참조 방식 에서 절대참조 방식으로 변경한 것 입니다.
이후 F3 셀을 복사하여 F4 셀에 붙여넣기 합니다.
이제 결과가 정상적으로 출력되고 있습니다.

F4 셀의 함수 수식을 확인해보면 참조 범위가 학생 전체 범위에 대해 올바르게 복사되어 지정되었음을 알 수 있습니다.
이러한 이유로 VLOOKUP 함수의 참조 범위는 절대참조를 사용해야 하는 것 입니다.

간접 주소 참조 함수, INDIRECT

절대참조 방식으로 참조 범위를 지정하여 수식 복사하여 붙여넣기 시에도 참조 범위가 변경되지 않도록 하는 것을 봤습니다.
하지만 이 방식은 참조 범위가 고정되어 있기 때문에 유연한 방식이라고 할 수 없습니다.
만약 참조 범위가 변경된다면, 해당 절대참조 범위가 적용된 모든 함수 수식에 변경이 필요하기 때문입니다.
이러한 문제에 도움을 줄 수 있는 함수가 바로 INDIRECT 함수 입니다.

함수는 함수명과 인자로 구성되어 있습니다.
= 를 입력하고 함수명(INDIRECT)를 입력한 후, ( 괄호를 여는 순간 함수 인자를 입력하기 위한 도움말이 나타납니다.

위의 그림에서 =INDIRECT( 함수의 사용을 도와주는 도움말이 나타납니다.
함수에 어떤 인자를 입력해야 하는지 알려주는데요, (ref_text, [a1]) 의 의미는 아래와 같습니다.

ref_text : 참조 범위를 나타내는 텍스트 값을 첫 번째 인수로 입력해야 합니다. (필수 입력)
[a1] : TRUE 또는 생략시 기본적으로 사용되는 A1 형태, FALSE 의 경우 R1C1 형태의 주소체계를 사용합니다. 이 값을 두 번째 인수로 입력해야 합니다. (선택 입력)

F3 셀에 =VLOOKUP(E3,INDIRECT(“B3:C8”),2,FALSE) 함수 수식을 입력합니다.
F3 셀에 김민준 학생의 생년월일 값이 올바르게 출력됩니다.
F3 셀을 복사하여 F4 셀에 붙여넣기 합니다.
F4 셀에 김민준 학생의 생년월일 값이 올바르게 출력됩니다.
절대참조 방식이 아닌 INDIRECT 함수를 사용해서 동일한 결과를 얻었습니다.
INDIRECT 함수의 인수인 “B3:C8” 은 참조 범위를 텍스트로 입력한 것 이므로, 복사 붙여넣기 작업을 하더라도 자동으로 변경되지 않았기 때문입니다.

INDIRECT 함수를 사용했지만 절대참조를 사용한 경우와 달라진 것이 별로 없습니다.
여전히 참조 범위가 변경되면 INDIRECT 함수의 인수 값을 변경하고 다른 셀에 복사하여 붙여넣기를 해야하기 때문 입니다.
이런 상태라면 굳이 INDIRECT 함수를 사용할 필요가 없습니다.

간접 주소 참조 함수, INDIRECT 활용 방법

INIDRECT 함수에는 텍스트로 작성된 참조 범위 인수가 입력됩니다.
그 인수에 텍스트를 직접 입력하는 것이 아니라 텍스트가 입력된 셀의 참조를 입력해도 된다면 상황이 많이 달라집니다.
바로 이것이 가능하기 때문에 INDIRECT 함수가 빛을 발하게 됩니다.

텍스트 주소 참조 함수 INDIRECT 함수간접 주소 참조 함수 활용 방법

F6 셀에 INDIRECT 함수의 첫 번째 인수 ref_text 에 입력될 참조 범위를 텍스트로 입력합니다.
F3 셀의 수식도 =VLOOKUP(E3,INDIRECT($F$6),2,FALSE) 형태로 변경합니다.
INDIRECT 함수의 첫 번째 인수로 F6 셀을 절대참조 방식으로 입력했습니다.
F3 셀의 값이 정상적으로 출력되고 있음을 확인할 수 있습니다.
F3 셀을 복사하여 F4 셀에 붙여넣기 한 후 값을 확인해 보겠습니다.
F4 셀의 값이 정상적으로 출력되고 있음을 확인할 수 있습니다.

이 상태에서 만약 참조 범위가 변경된다면 어떻게 하면 될까요?
이제 딱 하나만 바꿔주면 됩니다.

바로 F6 셀에 입력된 참조 범위의 텍스트 값만 변경된 참조 범위에 맞게 수정하면 됩니다.
기존에 입력된 VLOOKUP 수식은 변경할 필요가 없어졌습니다.

INDIRECT 함수를 사용해서 유연하게 대응할 수 있도록 VLOOKUP 함수 수식이 변경되었습니다.
INDIRECT 함수는 다양하게 활용 가능합니다.
이후의 글에서 여러가지 활용방법을 확인해 보겠습니다.

오류가 발생되었을 때 당황하지 말고, 함수의 인수들을 천천히 살펴보시기 바랍니다.
그것이 엑셀 함수 수식의 오류를 해결하는 가장 빠른 방법 입니다.

각자 간단한 예제를 만들어 사용법을 익히기 바랍니다.

엑셀에 관한 궁금증은 Microsoft Office 도움말 에서 해결할 수 있습니다.
Microsoft Office 도움말 바로가기