코딩 오답노트

엑셀에서 SUM 함수는 가장 기본적으로 배우는 함수 입니다.

 =SUM(범위)   를 입력하면 범위의 합계가 계산이 됩니다.

그런데, 내가 원하는 조건에 맞는 구간만 합계를 구하고 싶을땐, 어떤 함수를 사용하면 될까요?

조건에 맞는 합계를 구하고 싶을때 사용하는 함수가 바로~

SUMIF, SUMIFS 입니다. (예제파일 sheet1 참조)

160726_SUMIF, SUMIFS함수.xlsx

SUMIF 함수는 이런 구조로 되어있습니다.

SUMIF(조건범위, 조건, 조건에 맞는 셀의 합계)

그렇다면 E4셀에서

"=SUMIF( $B$3:$B$26 , "<3", $C$3:$C$26 )" 이라는 수식의 의미는...

"$B$3:$B$26의 범위 중에서 3미만에 해당되는 셀을 찾아서 $C$3:$C$26 범위의 값을 찾아서 더해줘라" 는 뜻입니다.

조건범위에 문자를 넣어도 됩니다. 아래 그림을 참조하세요.

조건이 "김" 일 경우 C열 합계를 구하는 수식입니다. (예제파일 sheet2 참조)

SUMIFS 함수는 이런 구조로 되어있습니다.

SUMIFS( 조건에 맞는 셀의 합계, 조건범위, 조건, )

그렇다면 E7셀에서

"=SUMIFS( $C$3:$C$26 , ① $B$3:$B$26 ,"<=6", ② $B$3:$B$26 ,">=4")" 이라는 수식의 의미는...

"$B$3:$B$26의 범위 중에서 ①: 6이하에 해당되는 셀과 ②: 4이상에 해당되는 셀을 찾아서 $C$3:$C$26 범위의 값을 찾아서 더해줘라" 는 뜻입니다.

※ SUMIF 는 한가지의 조건을 가지고 그 조건에 맞는 셀의 합계를 구했다면..

SUMIFS는 여러가지 조건에 맞는 셀의 합계를 구할 수 있습니다.

예는 한두가지를 들었지만, 자신이 직접 여러가지로 해보는것이 가장 중요합니다.

오늘은 TEXT 함수를 알아보겠습니다.

=TEXT ( 값 , 적용할 형식 )

아래 예시를 참고하세요

45475 라는 숫자에 각각의 형식을 적용한 것입니다.

=TEXT($A$2,"yyyy-mm-dd")

=TEXT($A$2,"yy-mm-dd")

=TEXT($A$2,"yy년 mm월 dd일")

=TEXT($A$2,"yymmdd")

=TEXT($A$2,"yyyy")

=TEXT($A$2,"m")

=TEXT($A$2,"mm")

=TEXT($A$2,"d")

=TEXT($A$2,"dd")

=TEXT($A$2,"ddd")

=TEXT($A$2,"dddd")

=TEXT($A$2,"#,#") : 값이 0일때 아무 표시가 되지 않습니다.

=TEXT($A$2,"#,#.0")

=TEXT($A$2,"#,#0") : 값이 0일때 "0" 이라고 표시가 됩니다.

=TEXT($A$2,"#,#0.00")

13~16행에서 각 형식의 미묘한 차이가 있습니다.

A2셀의 숫자를 0으로 바꿔보시면 어떤 차이가 있는지 알 수 있을겁니다.

직접 해보세요

무엇보다 연습이 중요합니다~

이번엔 조금더 활용해서 사용해보겠습니다.

어떤가요?

TEXT함수 하나면 얼마든지 변형해서 활용할 수 있겠죠??

궁금한게 있으시면 댓글 남겨 주시고,

도움이 되었다면 역시 댓글 남겨주세요~

160726_TEXT함수.xlsx

이번엔 조건부 서식을 사용하면서 MOD, COLUMN 함수를 함께 활용하는 방법을 알려드립니다.

우선 함수부터 설명하고 시작하겠습니다.

MOD 라는 함수를 우선 설명합니다. (number, divisor)

예를 들어 MOD(5,2) 라고 함수를 사용했다면 5÷2 해서 나머지가 몇인지 알려달라는 뜻입니다.

계산해보면 몫은 2, 나머지는 1입니다.

그래서 값은 1이 나오죠.
ex) =MOD(9,7) 은, 9÷7 을 계산해서 나머지 2를 반환하게 되죠.

COLUMN 이라는 함수는 ROW 함수와 반대의 개념이라고 할 수 있는데요, 주어진 셀 참조의 열 번호를 알려드리는 함수 입니다.

예를 들어 A열에 "=COLUMN()"이라고 입력하면 1 이라는 값을,

B열에 "=COLUMN()"이라고 입력하면 2 이라는 값을 반환하게 됩니다.

이번엔 직접 한번 해보세요.

"=COLUMN(A1)" 이라고 어느 셀에서든지 입력해 보세요.

"=COLUMN(F6)", "=COLUMN(D100)" 등등

무엇보다 직접 해보는 것이 가장 중요합니다.

그럼 지금부터 다음과 같이 셀에 선이 그어져 있을 경우, 짝수열에서 우측 선만 안보이게 하려고 하는 조건부 서식을 알아보겠습니다.

조건부 서식 사용하려면... 엑셀 2013 버젼에서는 다음과 같이 찾으실 수 있구요,

개인적으로는 ALT, O, D 를 순서대로 누르는걸 선호합니다.

(마우스 사용하는걸 별로 좋아하지 않아서..)

뭐 어쨌든 "새 규칙"을 클릭하고,

"수식을 사용하여 서식을 지정할 셀 결정" 을 선택합니다.

그리고 다음과 같이 수식을 입력합니다.

=MOD(COLUMN(),2)=0)

위에서 함수는 설명했지만, COLUMN() 이라는 함수는 열번호를 가져오게 되죠?

그래서 지금 셀의 번호가 1인지 2인지 3인지... 등을 감지하게 됩니다.

지금부터는 열번호가 13이라는 가정을 해봅니다.

그다음은 MOD 함수가 작동하게 되죠

=MOD(COLUMN(),2)

=MOD(13,2)     : 위에서 열번호를 13으로 가정했다고 했죠?

=1                 : 13 을 2 로 나누고 나면 나머지는 1 이죠?

홀수와 짝수를 구분짓는건.. 2로 나누었을때 나머지가 0인지 1인지의 차이로 구분 할 수 있죠..

그래서 =MOD(COLUMN(),2)=0) 라는 함수는..

열이 짝수인지 아닌지를 판단하는 함수라는걸 알 수 있겠죠?

이제 서식을 지정합니다.

오늘는 짝수열에 우측선을 없애고 싶다고 했는데요~

서식을 클릭하고 아래 그림과 같이 셀 서식을 지정해줍니다.

그리고 나서 어느 범위 안에서 조건부 서식을 작동시킬지, 범위를 선택해야 합니다.

이제 어떻게 바뀌었는지 결과를 볼까요???

이렇게 짝수열의 우측선만 보이지 않게 되었습니다~

굳이 선을 일일이 지울 필요 없이, 셀을 삽입하거나 삭제했을때 자동으로 선이 보이지 않게 해주기 때문에 일을 많이 줄일 수 있게 되는 "조건부 서식"

참 좋지요??