초코바

@ NOT : 논리식의 결과 부정하기

NOT 함수는 논리식의 결과를 부정하는 함수입니다.

논리식의 결과가 참(TRUE)이면 거짓(FALSE)을 반환하고, 거짓(FALSE)이면 참(TRUE)을 반환하는 함수입니다.

▶ NOT 함수는 단독으로 사용하는 경우는 별로 없습니다.

형식) NOT(논리식) :논리식 결과의 반대 값을 반환합니다.

실습1)

NOT함수를 사용하면, X의 결과값을 반대로 출력하게 됩니다.

C3에 =NOT(B3) 입력하면 결과가 반대로 나타납니다.

B3의 값을 참조해서 결과값을 나타낸것과, 직접 입력한 경우는

1) =NOT(FALSE) 의 결과값은 : TRUE

2) =NOT(TRUE) 의 결과값은 : FALSE

3) 0은 거짓이며, 0 외의 값은 모두 참으로 처리 되는것을 알 수 있습니다.

1 : 참(TRUE)

0 : 거짓(FALSE)

=NOT(0) 의 결과는 : TRUE

=NOT(1) 의 결과는 : FALSE

=NOT(2) 의 결과는 : FALSE

=NOT(-1) 의 결과는 : FALSE

※ NOT 게이트와 같은 결과를 나타냅니다

엑셀 수식 끝에 { } 중괄호 표시가 있는 수식, 이것을 배열 수식이라고 합니다.

수식에는 일반적인 연산자나 함수, 셀에 입력하는 다른 함수식과 동일하게 반영되지만 수식 끝에 중괄호{ }가 표시되는 것이 다릅니다.

배열(Arrary)이란 여러값의 집합을 말하며 엑셀에서는 중괄호({}) 안에 값들이 상호 대응하여 계산한 다음 결과 값을 셀에 또는 셀 범위에 표시합니다.

아래 파일을 다운받아서 따라하면서 연습해 보세요.

실습1)

'단가 x 수량' 을 계산하는 식을 배열 수식으로 입력하여 계산하는 연습을 해봅시다.

1. ‘단가 X 수량‘을 계산하는 식을 배열 수식으로 입력하면 먼저 가격이 표시될 [D4:D8] 셀 범위를 선택한 후 ’=B4:B8*C4:C8’을 입력하고 {Ctrl}+{Shift}+{Enter}키를 누릅니다.

D4={B4:B8*C4:C8}

☺ 배열 수식은 다음과 같은 과정을 거쳐서 계산됩니다.

실습2)

sum 함수로 예제

남자의 금액합계를 계산하기 위해서 sum함수를 사용해서 연산을 진행할 때,

Enter키를 칠때와 Ctrl+Shift+Enter키를 칠때의 값입니다.

남자 금액 합계를 구하시오. (Ctrl+Shift+Enter를 이용해서 구해보시오)

정답)

=SUM((B2:B6="남")*C2:C6) 형식이라서 Enter를 치면 안됩니다.

Ctrl+Shift+Enter를 입력해야 됩니다.

☞ 15000+10000+17000 = 42000

추가 설명 정리)

● Ctrl+Enter :선택한 셀 범위를 현재 입력한 값으로 범위영역에 모두 채워지게 됩니다.
● Ctrl+Shift+Enter : 수식을 배열 수식으로 처리하게 됩니다.
    Ctrl+Shift 를 누른 상태에서 Enter를 쳐야 합니다.

1. 배열수식은 함수가 아니라 수식입니다.
2. 배열수식을 표현(입력)하는 방법은 { } (중괄호)를 사용합니다.
3. Ctrl + Shift + Enter 키를 사용합니다.
4. 배열에는 행(가로)과 열(세로)이 있으며, 행 혹은 열의 수가 서로 맞아야 합니다.
5. 배열의 연산은 논리연산의 원리가 적용됩니다.


※ 배열수식
배열 수식은 배열 인수라는 2개 이상의 값을 가지고 처리하게 됩니다.
-배열 수식은 하나 이상의 값 집합에 대해 여러 가지 계산을 수행하고 하나 또는 여러 개의 결과를 반환합니다.
-배열 수식에 사용되는 배열 인수 각각은 동일한 개수의 행과 열을 가져야 합니다.
-배열 수식은 수식을 입력할 때 CTRL + SHIFT + ENTER를 누르는 것 외에는 다른 수식을 만들 때와 같습니다.
-CTRL + SHIFT + ENTER를 누르면 수식의 앞뒤에 중괄호 ({})가 자동으로 입력됩니다.
-수식 입력줄이 활성화 되면 배열 수식의 {}는 나타나지 않습니다.
-여러 셀에 수식을 입력할 때는 입력셀을 범위로 설정한 후 수식을 입력합니다. 
-여러 셀을 범위로 지정하고, 배열 수식을 입력하면 지정된 범위에 모두 같은 수식이 입력되며, 이렇게 입력된 배열 수식은 일부 셀만 선택하여 수정할 수 없습니다.

※ 배열상수
-배열 수식에 사용되는 배열 인수를 배열 상수라고 하며, 배열 상수로는 숫자, 텍스트, TRUE나 FALSE등의 논리값, #N/A와 같은 오류 값을 사용할 수 있습니다.
-같은 배열 상수에 다른 종류의 값을 사용할 수 있습니다.
-배열 상수에 정수, 실수, 5E+3 같은 지수형 숫자를 사용할 수 있습니다.
-$, 괄호, %, 길이가 다른 행이나 열, 셀 참조는 배열 상수로 사용될 수 없습니다.
-배열 상수 값은 수식이 아닌 상수이어야 합니다.
-배열 상수를 입력할 때 열의 구분은 쉼표(,)로 행의 구분은 세미콜론(;)으로 합니다.
-배열 상수로 사용할 범위에서 빈 칸은 0으로 취급됩니다.

@ IFERROR : 수식의 결과가 오류일 경우 “오류” 표시하는 함수 입니다.

IFERROR 함수는 인수로 지정한 수식에서 "오류"가 발생할 경우 사용자가 지정한 오류 값을 반환하고, 그렇지 않으면 수식의 결과를 반환합니다.

형식) IFERROR(인수, 오류 시 표시할 값) : ‘인수’로 지정한 수식이나 셀에서 오류가 발생했으면 ‘오류 시 표시할 값’을 반환하고, 그렇지 않으면 결과값을 반환합니다.

실습1)

직접 타이핑해서 표를 만들어서 F3에 수식을 입력해보세요.

조건 : 평균(축구공+농구공+탁구공) / 학생수

F3 에 =SUM(B3:D3)/E3 입력을 하면 #DIV/0! 가 표시된다.

#DIV/0! : 어떤 숫자를 0으로 나누려고 할 때 발생하는 오류 입니다.

오류표시를 사용자가 표시하고자 하는 것으로 반환할 때 IFERROR 함수를 사용합니다.

F8 에 =IFERROR(SUM(B8:D8)/E8,"계산오류") 입력하면, 에러 내용을 사용자가 원하는 메세지로 반환하게 됩니다.

실습2)

컴활 기출문제 입니다.

파일은 다운로드해서 실습해 보세요.

학과코드와 전공2를 이용하여 [I3:I6] 영역에 학과코드별 전공2의 점수가 90 이상인 학생들의 전공2 평균을 계산하시오.

▷ 해당 학과 학생이 없는 경우에는 “없음”표시

▷ IF, AVERAGE, IFERROR 함수를 이용한 배열 수식 사용

정답 : [I3] 셀에 다음의 수식을 입력하고 [Ctrl]+[Shift]+[Enter]를 누르세요

=IFERROR(AVERAGE(IF(($C$3:$C$12=H3)*($F$3:$F$12>=90),$F$3:$F$12)),"없음")

수식설명)

AVERAGE(IF(($C$3:$C$12=H3)*($F$3:$F$12>=90),$F$3:$F$12))에서 오류가 발생하면, “없음”을 표시하고, 그렇지 않으면 결과값을 표시한다.

▷ AVERAGE와 IF를 이용한 평균구하기 배열 수식의 일반식 – 조건이 2개

AVERAGE( IF( (조건1)*(조건2), 평균을 구할 범위) )

AVERAGE( IF (C3:C12=H3) * (F3:F12>=90), F3:F12) )

조건 1 : C3:C12=H3

조건 2 : F3:F12>=90

평균을 구할 범위 : F3:F12

결과값 93을 “AVERAGE(IF(($C$3:$C$12=H3)*($F$3:$F$12>=90),$F$3:$F$12” 이곳에 대입하면

=IFERROR(93,“없음”) : 93은 오류가 아니므로 93이 [I3] 셀에 입력됩니다.

★ [Ctrl]+[Shift]+[Enter] 키의 기능 ★

엑셀 수식 끝에 { } 중괄호 표시가 되는 수식을 배열 수식이라고 합니다.

이 수식에는 일반적인 연산자나 함수 등 셀에 입력하는 다른 함수식과 동일하게 반영되지만 수식 끝에 중괄호{ }가 표시되는 것이 다릅니다.

배열(Arrary)이란 여러값의 집합을 말하며 엑셀에서는 중괄호({}) 안에 값들이 상호 대응하여 계산한 다음 결과 값을 셀에 또는 셀 범위에 표시합니다.

[수식]탭-[수식분석]그룹-수식계산-계산을 누르면 결과들을 볼 수 있습니다.

배열로 그려놓고 하면 더 쉽게 이해할 수 있습니다.

즉, 배열로 계산하면 쉽게 이해할 수 있습니다.

@ OR 함수는 여러 개의 논리식 결과 중 하나라도 참(TRUE)이면 참(TRUE)을 반환하는 함수입니다.

예를 들어 임의의 값 X가 100보다 크거나 50보다 작은 조건에 맞는지를 판별할 때는 ‘=OR(X>100, X<50)’와 같이 함수식을 입력하면 됩니다.

X가 50~100 사이에 있을 때만 모두 거짓이 되어 FALSE를 반환합니다.

OR 함수 안에 표시되는 조건은 1개에서 255개까지 사용할 수 있습니다.

★ 논리합(OR) 입니다.

논리합의 소자는 보통 두개 이상의 입력단자와 하나의 출력단자를 가지며, 입력중에 1이 하나만 있어도 출력이 1이 되는 논리소자 입니다. 이 원리와 같은게 바로 OR 함수 입니다.

A + B = C

0 : 거짓

1 : 참

조건 많을때, 하나만 참이고 모두 거짓이여도 결과는 참이 나온다.

형식) OR(조건1, 조건2, …) : 인수 중 하나라도 참이면 참을 반환합니다.

실습1)

정답) D3 : =OR(B3:C3)

실습2) 컴활 기출문제

파일을 다운 받아서 실습해보세요.

문제)

결제와 포인트를 이용하여 할인액[H3:H9]을 구하시오.

▷ 할인액 = 정가 * 수량 * 할인율

▷ 할인율은 결제가 ‘제휴’ 이거나 ‘현금’이면 30% 아니면 10%를 적용

▷ 할인액이 포인트[E3:E9]를 초과하는 경우 포인트 금액까지만 할인함

조건1) 사용할 함수 : MIN, IF, OR 함수만 사용하시오.  // 기출문제 조건입니다.

조건2) 함수 제한없이 작성해 보시오.   // 기출과 상관없이 자유롭게 함수를 만들어서 만들어 보기 입니다.

※ 초과, 이상 : 의미는,

ex) 10 이상은 10이 포함,

     10 초과는 10이 미포함

조건1, 정답)

=MIN(F3*G3*IF(OR(C3="제휴",C3="현금"),30%,10%),E3)

조건2, 정답들중 하나)

=IF((F3*G3*IF(OR(C3="제휴",C3="현금"),30%,10%))>E3,E3,F3*G3*IF(OR(C3="제휴",C3="현금"),30%,10%))

설명)

조건1 정답풀이 설명

우선 조건 부분부터 수식을 세워 작성하시면 됩니다.

1)  F3*G3*IF(OR(C3="제휴",C3="현금"),30%,10%)

조건에 맞게 수식을 작성합니다.

2) MIN 함수를 이용하여 포인트 점수와 비교하여, 포인트 점수보다 큰값이 나오면, 포인트 점수가 반환되고,

포인트 점수보다 작은 결과값이 나오면, 작은 결과값이 가장 작기 때문에 반환되도록 하면 됩니다.

=MIN(F3*G3*IF(OR(C3="제휴",C3="현금"),30%,10%),E3)

▷ OR(C3="제휴",C3="현금") : [C3] 셀의 값이 ‘현금’ 이므로 "참(TRUE)"을 반환합니다. 이에대한 결과값을 적용하면,

=MIN(F3*G3*IF(TRUE,30%,10%),E3)

▷ IF(TRUE,30%,10%) : 조건이 참이면 30%를, 거짓이면 10%를 반환하는데, 조건이 참이므로 30%를 반환합니다.

이것을 대입하면,

=MIN(F3*G3*30%, E3) : F3*G3*30%의 결과값은 56,160 이고, E3셀의 값은 30,000 이므로 두 값중 작은 값인 30,000이 [H3] 셀에 입력됩니다.

@ AND : 모두 참(TRUE)일 때 참(TRUE) 반환하고, 거짓이 하나라도 있으면 FALSE(거짓)을 표시한다.

AND 함수는 여러 개의 논리식 결과가 모두 참(TRUE)일 때만 참(TRUE)을 반환하는 함수입니다.

조건값중에 하나라도 거짓이 있으면, 거짓(FALSE)를 반환합니다.

논리곱(AND) 이다.

형식) AND(조건1, 조건2, 조건3, 조건4 ….) : 주어진 인수가 모두 참이면 참을 반환합니다.

조건은 쉼표로 구분하며, 최대 255개까지 지정할 수 있습니다.

이 조건은 참과 거짓의 결과만 가진다.

예)

=AND(20>10, 4>2)    결과 : TRUE

20>10 은 TRUE(참) 이고, 4>2 이것도 TRUE(참)이다.

즉, true는 1, false는 0값이다.

1 X 1 = 1 되는 논리곱 형식이 된다. 자세한 설명은 맨 아래에 따로 설명해두었으니, 참고 바랍니다.

그래서, 모두 참이므로, 결과도 참이 된다.

실습1)

● 입력하고, 함수 적용 후, 결과값을 확인하세요.

정답) D3 : =AND(B3:C3)

실습2)

직접 입력해서 X(각각개별) 와 X(범위로처리) 부분을 연습해 보시기 바랍니다.

E3, F3 부분을 연습해 보세요.

정답 : 

E3 : =AND(A3,B3,C3,D3)

F3 : =AND(A3:D3)

▷ 범위를 잡아서 처리해도 되고, 각각 인수를 하나씩 처리해도 된다

※ AND 함수의 인수는 범위의 크기에 관계없이 쉼표로 구분하여 255개까지 지정할 수 있습니다.

★ AND함수는 논리곱에 대한 상세한 설명

논리곱의 소자는 보통 두개 이상의 입력단자와 하나의 출력단자를 가지며, 입력이 모두 1일때만 출력이 1로 되는 논리소자이다.

이방식이 엑셀 AND함수와 같은 동작으로 이루어진다.

★ 형식) AND(조건1, 조건2, 조건3, 조건4 ….)

여기서의 조건이 A, B 즉, 입력이되며, 이 논리의 곱이 AND함수의 결과값, 즉, 출력 C 값이 되는것이다.

IF 함수는 참, 거짓의 논리식을 판별하여 참, 거짓 일때 해당하는 값을 반환하기 위해 사용하는 함수 입니다.

이 함수는 조건이 참 또는 거짓으로 판별되며, 참이면 첫번째 인수1이 반환되고, 거짓이면 인수2가 반환되게 됩니다.

IF문에서는 두 개의 결과가 나타날 수 있습니다. 그 결과값은 True값과 False값 입니다.

형식) IF(조건, 인수1, 인수2)

: 조건을 비교하여 ‘참’ 이면 인수1, ‘거짓’이면 인수2를 실행합니다.

예)  IF(A1=”Yes”,1,2)는 IF(A1 = Yes이면 1을 반환하고, 그렇지 않으면 2를 반환한다)라는 의미입니다.

<일반적인 문제>

문제 : 셀의 0

value_if_true  또는 value_if_False에 대한 인수가 없습니다. 반환된 값을 보려면 두 인수에 인수 텍스트를 추가하거나 인수에 TRUE 또는 FALSE를 추가합니다.

문제 : #NAME?

일반적으로 수식의 철자가 잘못되었을때를 의미합니다.

실습)

파일 다운 받아서 실습해보세요.

● 입력하고, 함수 적용 후, 결과값을 확인하세요.

IF함수를 이용하여 12월 실적이 평균보다 크면 평가에 “우수”를, 그렇지 않으면 “미달”을 표시 하도록 합니다.

정답)

E4 : =IF(D4>$D$11,"우수","미달")

D는 열이고, 11은 행입니다.

열과 행을 고정하기 위해 열,행 을 표시하는 D11에 $표시를 넣어서 절대주소로 표시했습니다.

열 앞에 $를 넣으면 열이 고정되며, 행을 나타내는 숫자 앞에 $을 넣으면 행이 고정됩니다.

실습2)

실습파일은 다운로드 받아서 실습하세요.

매출점수와 영업점수가 모두 80점 이상이면 "★" 이 찍히도록 하고, 조건이 성립하지 않으면, 공백을 입력하세요.

정답)

F3 : =IF(AND(D3>=80,E3>=80),"★","")

☞ AND(D3>=80, E3>=80) 의 조건이 참(TRUE)이면, “★”를, 거짓(FALSE)이면 “”를 표시합니다.

▷ IF(조건, 인수1, 인수2) 함수에서 인수2에 아무 것도 입력하지 않으면 조건이 거짓일 때 ‘FALSE“가 표시됩니다.

※ “★”는 한글 자음 ‘ㅁ’ 입력하고 [한자]를 누른 후 바로 아래에 표시되는 문자 목록에서 선택하여 입력합니다.

※ AND 함수

@ AND : 모두 참(TRUE)일 때 참(TRUE) 반환하기

AND 함수는 여러 개의 논리식 결과가 모두 참(TRUE)일 때만 참(TRUE)을 반환하는 함수입니다.

형식) AND(인수1, 인수2, …) : 주어진 인수가 모두 참이면 참을 반환합니다.

하나라도 거짓이면 거짓이 됩니다.

YEARFRAC : 일년 중 기간이 차지하는 비율 구하기

YEARFRAC 함수는 시작 날짜와 끝 날짜 사이의 일수가 일년 중 차지하는 비율을 반환하는 함수입니다.

날짜는 DATE 함수를 사용하여 입력하거나 다른 수식 또는 함수의 결과값으로 입력해야 합니다.

예) 2019년 1월 1일과 2019년 10월 1일을 입력하여 두 날짜 사이의 일수가 일년 중 차지하는 비율을 계산하려면 YEARFRAC(DATE(2019,1,1), DATE(2019,10,1)과 같이 DATE 함수를 사용해야 합니다.

날짜를 텍스트로 입력해도 되지만 오류가 발생할 수 있습니다.

형식) YEARFRAC(시작날짜, 끝날짜, 옵션) : ‘시작날짜’ 와 ‘끝날짜’ 사이의 일수가 일년 중 차지하는 비율을 옵션에 맞게 계산합니다.

시작날짜 : 필수 요소 입니다. 시작날짜입니다.

끝날짜 : 필수 요소 입니다. 종료 날짜 입니다.

basis(옵션) : 선택 요소 입니다. 날짜 계산 기준입니다.

▷ YEARFRAC 함수의 옵션

• 0 또는 생략 : 30/360(미국식)

• 1 : 실제 일 수 / 실제 1년 일 수

• 2 : 실제 일 수 / 360

• 3 : 실제 일 수 / 365

• 4 : 30/360 (유럽식)

※ 옵션이 0~4가 아닌 경우 #NUM! 오류값을 반환합니다.

보통 옵션은 3 : 실제 / 365 이것을 주로 사용하게 됩니다.

중요)

▶ 날짜는 DATE 함수를 사용하거나 다른 수식 또는 함수의 결과로 입력해야 합니다.

    예를 들어 2018년 5월 23일에 대해서는 DATE(2018,5,23)을 사용합니다.

    날짜를 텍스트로 입력하면 문제가 발생할 수 있습니다.

▶ YEARFRAC 함수는 US(NASD) 30/360 기준을 사용할 때 잘못된 결과를 반환할 수 있으며 start_date는 2월의 마지막 날입니다.

주의)

▶날짜는 계산에 사용할 수 있도록 순차적인 일련 번호로 저장됩니다. 기본적으로 1900년 1월 1일이 일련 번호 1이고, 2018년 1월 1일은 1900년 1월 1일에서 43,101일째 날이므로 일련 번호 43,101이 됩니다.

▶모든 인수는 정수로 변환되며 소수점 이하는 무시됩니다.

▶start_date나 end_date가 유효한 날짜가 아니면 YEARFRAC에서 #VALUE! 오류 값이 반환됩니다.

▶basis < 0 또는 basis > 4이면 YEARFRAC에서 #NUM! 오류 값이 반환됩니다.

실습1)

파일 다운로드 받아서 실습해보세요.

정답

C4 : =YEARFRAC(A4,B4)

D4 : =C4*$E$2

옵션을 추가해서 사용해보세요. 결과값이 어떻게 다르게 나오는지도 확인해 보시기 바랍니다.

WEEKNUM : 날짜를 기준으로 <년의 주차> 계산하기

WEEKNUM 함수는 지정된 날짜가 일년 중 몇 번째 주에 속하는지를 계산하는 함수입니다.

WEEKNUM 함수는 1월 1일이 포함된 주를 해당 연도의 첫 번째 주로 간주합니다.

형식) WEEKNUM(날짜, 옵션) : ‘날짜’가 일년 중 몇 번째 주에 속하는지를 계산합니다.

날짜 : 날짜가 입력되어야 합니다. 서식이 텍스트일 경우 문제가 발생 할 수 있습니다.

옵션 : 사용자가 선택하는 요소입니다. 한 주의 시작을 결정하는 숫자입니다.

기본값은 1입니다.

▷ WEEKNUM : 특정 날짜가 입력된 해당 년도의 주 번호를 반환합니다.

▷ 1월 1일을 포함하는 주는 연도의 첫 번째 주이고, 주 번호는 1로 반환합니다.

▷ 날짜의 ‘주’차를 숫자로 반환하는 함수입니다.

▷ 옵션

• 1 또는 생략 : 일요일부터 주가 시작함

• 2 : 월요일부터 주가 시작함

옵션의 이해를 돕기 위해 실습2를 통해 실습한 그림 파일과 요일을 넣어서 처리한 그림 파일을 확인해보면 쉽게 이해를 할 수 있습니다.

실습1)

파일을 다운받아서 함수를 적용해 보세요.

아래 예제를 파일을 다운 받아서 적용해보세요. 또는 직접 타이핑해서 실습을 해보도록 하세요.

정답 : =WEEKNUM(C3)

실습2)

▶ A열에 연월일을 넣고 B1에 함수를 넣어보세요.

B1 : =WEEKNUM(A1)

보통 달력이 일요일부터 시작되므로, 옵션을 1 또는 생략해서 처리합니다.

▶ 옵션을 12로 주가 시작되는 요일을 화요일로 처리해서 해보도록 합니다.

B1 : =WEEKNUM(A1,12)

시작되는 요일이 화요일로 처리하게되면, 화요일이 1일부터 주가 시작되게 계산하게 됩니다.

햇갈릴거 같아서 엑셀로 화요일부터 시작해서 월요일로 끝나는 달력이 형성되게 됩니다.

그리고 이 요일에 맞춰서 '주'의 계산이 됩니다.