HAKINA SUN

매달 동일한 문서를 작성하고 계신가요?

동일하지만 몇개의 숫자, 몇개의 글자만 바꿔야 되는 상황인가요?

문서 형식은 유사하지만 바꿔야될 값들이 많다구요?

그렇다면 이 방법을 추천해드립니다!

■ "데이터 값 자동 변경하기" 예시로 미리 알아보기

"OO 업체의 계약기간은 OO 입니다."

→ "HAKINASUN 업체의 계약기간은 2019.07.10 ~ 2019.08.10 입니다."

위 예시 그림에서 업체, 계약기간만 적어주면, 노란색 박스안의 문구가 자동으로 수정됩니다. 형식은 동일하지만, 업체명이나 기간 등을 달리 적용시켜 문서를 작성해야하는 경우 이 방법을 사용하면 아주 편리합니다.

■ "데이터 값 자동 변경하기" 방법

데이터 값 자동 변경하는 방법을 알아보도록 하겠습니다. 먼저, 노란색 박스안에 문구는 수식으로 구성되어야 어떠한 값이 들어오면 그것을 받아들여 자동으로 수정되어집니다.

1) ="(문구를 기입하세요.)"

우선 수식 '='을 이용해서 텍스트를 넣기 위해서는 텍스트가 큰따옴표 사이에 있어야 합니다. 예를들어,

="안녕하세요."

="반갑습니다."

와 같이 말이죠. 그러면 셀은 직접 텍스트를 친것과 같이 표현이 됩니다.

안녕하세요.

반갑습니다.

2) "&(셀 선택)&"

수식으로 텍스트를 넣는 방법을 알았으니, 다른 셀의 값을 읽을 수 있는 방법도 알아야겠죠? 그것이 바로 "&(셀 선택)&" 입니다. ="(문구)" 의 큰따옴표 안에 "&(셀선택)&"을 넣으면 다른 셀의 값을 읽어낼 수 있는 것이죠.

A1셀 : HAKINA SUN BLOG 입니다.

="안녕하세요. "&A1&""

안녕하세요. HAKINA SUN BLOG 입니다.

="반갑습니다. "&A1&""

반갑습니다. HAKINA SUN BLOG 입니다.

텍스트 수식에 "&(셀)&"을 넣어주면 다른 셀에 있는 값을 불러들여 화면상 표시를 할 수 있게 되었습니다. 이제 제일 처음 예시를 따라할 수 있게 되었습니다.

HAKINASUN 업체의 계약기간은 2019.07.10 ~ 2019.08.10 입니다.

이를 수식으로 나타내보면 아래와 같겠죠?

=""&C2&" 업체의 계약기간은 "&C3&" 입니다."

위 그림에서 텍스트 수식에서 두개의 "&(셀)&"이 적용된 것을 볼 수 있습니다. 두개 뿐만아니라 여러개의 값을 읽어낼 수 있습니다. 한개이 텍스트를 구성하기 위해 여러개의 셀에서 값을 읽어드릴 수 있으므로, 수식 구성만 잘한다면 정말 편리하게 사용할 수 있는 방법이 될 수 있습니다.

 동일한 문서에 몇개의 정보만 수정해야되는 경우 이 방법을 사용해보세요. 실수로 수정하지 못할 일도 없으며, 간단하고 쉽게 수정하고자 하는 값들을 변경할 수 있습니다.

엑셀을 작업할 때는 데이터 정리를 다 한 뒤에 마지막으로 서식을 지정해주는 것이 기본이죠. 그러나, 데이터를 싹~ 정리한 후에 서식도 지정했는데, 데이터를 또 수정해야되는 경우가 생기기 마련입니다. 그럴때, 수식이 걸려있는 데이터를 다른 셀에도 적용하고 싶지만, 글자 크기, 진하기, 음영 등의 서식 또한 채우기가 적용되어 전부 서식을 다시 지정을 해주어야 했던 경험이 분명 있으실 겁니다. 하지만! 그럴필요 없이 서식없이 데이터만 채우기를 적용시킬 수 있습니다.

아래에서 서식없이 데이터만 채우기를 적용해보도록 하겠습니다. (요약은 제일 아래로 내려서 확인해주세요!!)

위 그림은 '동물 초등학교'의 코끼리, 고양이, 강아지, 물고기, 호랑이의 국영수 점수 입니다. 채점을 모두 마치고 나서 국영수 점수를 기입하고 평균을 냈고, 평소 공부를 잘 하지 않았던 코끼리와 물고기 점수를 예의주시하여 관리하고자 주황색으로 서식을 지정했습니다. 그런데, 표를 다 정리하고나서보니, 모든 동물의 점수가 잘못 기입되었음을 깨닫게 되고, 표를 급히 수정하게 됩니다. 그리고 평균 수식을 다시 넣고 채우기를 하게 되는데...

코끼리 수식을 채우기 하는데, 서식까지 다 채우기가 되었습니다. 그래서 선생은 코끼리와 물고기를 제외하고 나머지 동물들의 서식을 직접 지정해서 돌려주었습니다.

그러나! 그럴필요 없이 서식없이 채우기만 하면 서식을 다시 지정할 필요는 없었을 것입니다. 그렇다면 서식없이 채우기는 어떻게 하는 걸까요?

정답은 오른쪽 마우스를 클릭한채로 드래그를 하는 것입니다. 그러면 위 그림과 같이 '서식 없이 채우기'가 보일텐데요, 클릭을 해주면 서식은 변경되지 않고, 데이터만 채워지는 것을 확인할 수 있습니다.

'서식 없이 채우기'는 실무하다보면 정말 유용하게 사용할 일이 많으실 거에요. 엑셀은 알면 상당히 쉽고 편리한데, 모르면 삥~ 둘러서 가야되기 때문에 많은 기능들을 숙지하고 있는게 좋습니다. '서식 없이 채우기' 기능을 알았으니, 수정할일이 있을때 서식을 다시 지정해야 하는 공수를 덜 수 있게 되었습니다. 직접 한번 사용해보세요!

[요약]

① 셀 채우기 할때 왼쪽 마우스가 아닌 오른쪽 마우스로 채우기를 한다.

② '서식 없이 채우기'를 클릭한다.

엑셀에서 숫자를 표현하다보면 소수점이 지저분하게 보일때가 있습니다. 그럴때 유용하게 사용할 수 있는 함수가 'ROUND', 'ROUNDUP', 'ROUNDDOWN' 함수 입니다. 숫자를 '반올림', '올림', '내림'을 의미 합니다.

아래 그림에서 연한 노란색 음영으로 표시되어 있는 값인 '9876543210.12345'을 그 아래 표에 있는 소수점 네번째, 세번째, 두번째, 첫번째, 일의 자리, 십의 자리, 백의 자리, 천의 자리로 나타내고 싶을때 ROUND함수로 나타낼 수 있습니다.

굳이 ROUND 함수를 사용하지 않고, 엑셀 메뉴에서 버튼만 눌러도 반올림과 같은 기능을 사용할 수 있긴 합니다.

위의 그림에서 빨간색 네모박스로 표시한 버튼을 누르게 되면 소숫점을 줄이거나 늘릴수 있죠. 그러나, 일의 자리, 십의 자리 이상은 표현할 수가 없더군요. 소수점만 절삭을 해주는 기능이니 참고해주세요.

본론으로 돌아가서, 'ROUND', 'ROUNDUP', 'ROUNDDOWN' 함수에 대해서 알아보도록 하겠습니다.

1. ROUND, ROUNDUP, ROUNDDOWN 함수란 무엇인가!?

'ROUND', 'ROUNDUP', 'ROUNDDOWN' 함수는 말그대로 숫자를 '반올림', '올림', '내림' 시켜주는 함수 입니다. 예를들어 12345라는 값이 있을때, 이값을 백의 자리까지 반올림해서 나타내고 싶다하면 십의 자리에서 반올림하면 되겠죠? 그럼 12345라는 값은 12300라는 값이 되는 것입니다. 올림이면 12400, 내림이면 12300 값이 됩니다.

아래에서 반올림, 올림, 내림 함수를 사용하는 방법을 알아보겠습니다.

2. ROUND, ROUNDUP, ROUNDDOWN 함수 사용방법!!

ROUND 함수 : =ROUND(셀 선택, 몇번째 자리까지 나타낼것인지 숫자 기입)

ROUNDUP 함수 : =ROUNDUP(셀 선택, 몇번째 자리까지 나타낼것인지 숫자 기입)

ROUNDDOWN 함수 : =ROUNDDOWN(셀 선택, 몇번째 자리까지 나타낼것인지 숫자 기입)

※ 몇번째까지 나타낼것인지 숫자 기입

         ......

   - 천의 자리 : -3

   - 백의 자리 : -2

   - 십의 자리 : -1

   - 일의 자리 : 0

   - 소수점 첫번째자리 : 1

   - 소수점 두번째자리 : 2

   - 소수점 세번째자리 : 3

         ......

세개 함수 모두 의미만 다르지, 사용하는 방법은 똑같습니다. 그래서 ROUND 함수에 대해서만 한번 적용시켜보도록 하겠습니다.

소수점 네번째 자리까지 나타내기 (두번째 인수 : 4) : =ROUND(C2,4)

소수점 세번째 자리까지 나타내기 (두번째 인수 : 3) : =ROUND(C2,3)

소수점 두번째 자리까지 나타내기 (두번째 인수 : 2) : =ROUND(C2,2)

소수점 첫번째 자리까지 나타내기 (두번째 인수 : 1) : =ROUND(C2,1)

일의 자리까지 나타내기 (두번째 인수 : 0) : =ROUND(C2,0)

십의 자리까지 나타내기 (두번째 인수 : -1) : =ROUND(C2,-1)

백의 자리까지 나타내기 (두번째 인수 : -2) : =ROUND(C2,-2)

천의 자리까지 나타내기 (두번째 인수 : -3) : =ROUND(C2,-3)

ROUNDUP, ROUNDDOWN 함수도 위와 동일하게 적용할 수 있습니다.

반올림, 올림, 내림 함수에 대해서 알아보았습니다. 몇번째 자리까지 표현할지를 나타내는 두번째 인수에 달려있으므로, 해당 자리의 숫자를 기억해두면, 헤깔리지 않게 ROUND 함수를 사용할 수 있겠습니다.

엑셀에서 어떠한 표에서 특정 위치에 있는 값을 추출하여 나타내고자 할때 어떻게 해야할까요? VLOOKUP 함수나 HLOOKUP 함수를 이용해서도 값을 구할 수는 있지만, 이 함수들은 특정 단어를 표시를 해야 값을 불러올 수 있죠. 하지만, 특정위치를 지정해주어 값을 나타낼 수도 있습니다. 수학에서 어떠한 함수에 값을 넣어주면 그 함수에 걸맞는 값이 나오는것 처럼 말이죠. 엑셀에서도 이와 유사하게 값을 표현할 수 있습니다. INDEX 라는 함수를 통해서 말이죠. 물론, 수학에서 처럼 식으로 이루어져 있는 것은 아닙니다. 엑셀에서는 행과 열의 특정 위치의 값을 넣으면 해당하는 셀의 값이 추출되어 나타내어집니다.

1. INDEX 함수란 무엇인가!?

엑셀에서 INDEX함수는 어떤 영역의 특정 행과 열에 있는 값을 나타낼 수 있게 해주는 함수 입니다.

위 그림에는 1에서 25까지 숫자가 적힌 5X5표가 있습니다. 이중에서 초록색으로 칠해진 값을 따로 나타내고 싶을때, INDEX 함수로 이를 표현 할 수 있습니다. 아래에서 어떻게 하면 해당 값을 구할 수 있는지 INDEX 함수의 사용방법에 대해서 알아보도록 하겠습니다.

2. INDEX 함수 사용방법!!

=INDEX(array, row_num, [column_num])

=INDEX(표시할 데이터 표 드래그, 드래그된 데이터의 행, 드래그된 데이터의 열)

다음 간단한 예제를 통해서 INDEX 함수의 사용방법에 대해서 알아보도록 하겠습니다.

1) 표시할 데이터 표 드래그

노란색 음영으로 표시된 1에서 25까지의 기입된 5X5 데이터표를 드래그 합니다.

2) 드래그된 데이터에서 나타내고자 하는 행 기입

초록색 음영의 값을 표현하고자 하므로, 해당 값이 있는 행인 '4'를 기입합니다.

3) 드래그된 데이터에서 나타내고자 하는 열 기입

초록색 음영의 값은 네번째 열이므로, '4'를 기입합니다.

=INDEX(C3:G7,4,4) 함수를 완성하여 엔터를 누르면 다음과 같은 결과가 나옵니다.

이렇게 초록색 음영으로 칠해져있는 '19'라는 값을 INDEX 함수로 추출하여 나타내었습니다.

추가로, 위 그림의 숫자 '8'을 나타내고자 한다면 어떻게 해야할까요?

=INDEX(C3:G7,2,3)

추가로, 위 그림의 숫자 '15'를 나타내고자 한다면 어떻게 해야할까요?

=INDEX(C3:G7,3,5)

INDEX 함수를 자주 사용할 일이 없다보니, INDEX 함수의 인수를 어떻게 넣어주어야 할지 가물가물 할 때가 있습니다. 모든 함수를 외우는것이 엑셀을 다루는데 편하긴 하지만, 모든 함수를 다루기는 벅차지 않을 수 없습니다. 위와 같이 한번 따라해보면, 훗날 기억이 나지 않아도 위와 같은 방법을 대강 훑어만 봐도 금방 기억이 날 것입니다.

엑셀 AVERAGE 함수는 여러 데이터의 산술평균을 내어 줍니다. 산술평균이란 간단하게 말해서 모든 데이터의 값들을 더한 값을 데이터의 갯수로 나눈 값을 의미합니다. AVERAGE 함수는 대부분의 사람들이 알고 있지만, 실제 업무에서는 잘 사용하지는 않는 것 같습니다. 하지만, 통계 관련된 직종에서 일을 하시는 분들은 자주 접할 수도 있겠습니다. 학생이라면 국어, 영어, 수학 등 여러 과목들의 점수의 평균도 낼 수 있습니다. 간단하게 AVERAGE 함수에 대해서 알아보도록 하겠습니다.

1. AVERAGE 함수란!?

평균 = 데이터 값들의 합 / 데이터 갯수

다르게 표현하면,

평균 = SUM(셀1,셀2,셀3,...) / COUNT(셀1,셀2,셀3,...) 으로 표현할 수도 있습니다. 엑셀에서는 이것을 간단하게 AVERAGE 라는 함수로서 표현할수 있게 만들었습니다.

2. AVERAGE 함수 사용법!!

=AVERAGE(평균낼 모든 데이터)

평균내고자 하는 모든 데이터를 드래그하여 인수를 기입해주면 간단하게 AVERAGE 함수를 사용할 수 있습니다.

다음 간단한 예제를 통해서 AVERAGE 함수를 사용해보겠습니다.

위 표에서 기입된 1, 2, 3, 4, 5를 평균내어 보도록 하겠습니다.

=AVERAGE(B2:F2)

평균내고자 하는 데이터 셀 'B2', 'C2', 'D2', 'E2', 'F2'을 드래그하여 AVERAGE 함수의 인수로 기입해줍니다. 그 결과 '3'이라는 평균값이 나옵니다.

제일 위에서 구했던 "평균 = SUM(셀1,셀2,셀3,...) / COUNT(셀1,셀2,셀3,...)" 방법으로 구한 값과 동일한 것을 볼 수가 있습니다. 엑셀에서 AVERAGE 함수를 통해 어렵지 않게 평균값을 구할 수 있는 것이죠.

위의 예제는 너무 간단한 값이므로 더 복잡한 숫자들을 평균내 보도록 하겠습니다.

'7832', '90429', '8239', '23681', 123984' 5개의 값을 평균을 내어보았습니다. (실제 업무에서 사용한다하면, 이것보다 더 많은 데이터를 접하게 되겠죠?)

먼저, AVERAGE 함수를 이용해서 구한 값은 '50833'이고, "평균 = SUM(셀1,셀2,셀3,...) / COUNT(셀1,셀2,셀3,...)" 을 이용해서 구한 값도 '50833'임을 알 수 있습니다. 이렇듯 엑셀에서는 산술평균의 공식대로 값을 구할필요가 없어 편리합니다.

간단하게 AVERAGE 함수, 평균을 낼 수 있는 함수에 대해서 알아보았습니다. 알아두면 유용하게 사용할 수 있는 함수이오니, 참고하셔서 효율적인 엑셀 사용하시길 바랍니다!

엑셀 REPLACE 함수, SUBSTITUTE 함수는 유사한 기능을 가지고 있습니다. 셀 안에 들어있는 데이터의 일부 문자 혹은 숫자를 다른 값으로 대체할 수 있는 기능을 가지고 있습니다. 두 함수의 차이점이라고 한다면, 데이터에 무엇이 써있든지 상관없이 몇번째 자리에 있는 값을 다른 값으로 대체할건지, 아니면, 내가 찾아서 바꾸고자 하는 문자를 입력해서 대체할 것인지 입니다. 전자가 REPLACE 함수, 후자가 SUBSTITUTE 함수라고 할 수 있습니다.

우선, REPLACE 함수를 먼저 알아보도록 합시다.

1. REPLACE 함수

1) REPLACE 함수란 무엇인가!?

REPLACE 함수는 셀안에 들어있는 데이터중 특정 위치에 있는 문자나 숫자를 다른 문자나 숫자로 대체할 수 있도록 하는 함수 입니다.

2) REPLACE 함수 사용방법!!

=REPLACE(old text, start_num, num_chars, new_text)

=REPLACE(값을 대체하고자하는 셀, 대체하고자 하는 문자 혹은 숫자의 시작 자리수, 시작위치에서 몇번째까지 데이터를 바꿀것인지 자리수, 대체하고자 하는 문구)

글만 봐서는 도통 감이 오질 않네요.. 실제로 한번 적용해보도록 하겠습니다.

"오늘은 2019년 7월 2일 화요일 입니다." 문자중 '2019'를 '십구'로 대체해보도록 합시다.

=REPLACE(B4,5,4,"십구")

첫번째 인수는 대체할 셀 'B4', 두번째 인수는 '2019'를 다른 문자로 대체할 것이므로 '2'가 시작되는 자리수인 '5'를 기입, 세번째 인수는 '2019' 네자리 모두를 다른 문자로 대체할 것이므로 '4', 네번째 인수는 대체할 문자인 '"십구"'를 기입 합니다. 그러면 결과가 "오늘은 십구년 7월 2일 화요일 입니다."로 나오게 됩니다.

2. SUBSTITUTE 함수

1) SUBSTITUTE 함수란 무엇인가!?

SUBSTITUTE 함수는 앞서 설명한 REPLACE 함수와 기능은 비슷하지만, 접근하는 방법이 다릅니다. SUBSTITUTE 함수는 바꾸고자 하는 문자를 직접 찾아내어 다른 문자나 숫자로 대체할 수 있습니다.

2) SUBSTITUTE 함수 사용법!!

=SUBSTITUTE(text, old_text, new_text, [instance_num])

=SUBSTITUTE(바꾸고자하는 문자 혹은 숫자가 있는 셀, 바꾸고자하는 문자 혹은 숫자, 대체할 새로운 문자 혹은 숫자, [몇번째 인지 숫자]

아래와 같이 바로 적용해보도록 합시다.

"오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다." 에서 '2019'를 '2020'으로 대체해도록 합시다.

=SUBSTITUTE(B4,"2019","2020")

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입합니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2020년 7월 2일 화요일 입니다. 내일은 2020년 7월 3일 수요일 입니다."

→ 네번째 인수를 기입하지않으면 '2019' 모두를 '2020'으로 변경 합니다.

=SUBSTITUTE(B4,"2019","2020",1)

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입, 네번째 인수에는 '1'을 기입해보겠습니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2020년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

→ 두개의 '2019'중 첫번째 '2019'만 '2020'으로 변경한것을 확인할 수 있습니다.

=SUBSTITUTE(B4,"2019","2020",2)

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입, 네번째 인수에는 '2'을 기입해보겠습니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2020년 7월 3일 수요일 입니다."

→ 두개의 '2019'중 두번째 '2019'만 '2020'으로 변경한것을 확인할 수 있습니다.

=SUBSTITUTE(B4,"2019","2020",3)

첫번째 인수는 바꾸고자하는 셀 'B4', 두번째 인수는 바꾸고자 하는 문자 '2019', 세번째 인수는 새롭게 대체할 문자 '2020'을 기입, 네번째 인수에는 '3'을 기입해보겠습니다.

기존 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

결과 : "오늘은 2019년 7월 2일 화요일 입니다. 내일은 2019년 7월 3일 수요일 입니다."

→ 세번째 '2019'는 없으므로 기존과 동일한 결과를 나타냅니다.

REPLACE 함수와 SUBSTITUTE 함수에 대해서 알아보았습니다. 기능은 유사하지만, 사용하는 측면에서 접근방법이 다른 두 함수입니다. 상황에 맞게 적절한 함수를 사용한다면 큰도움이 될 것입니다.

LEFT, RIGHT, MID 함수는 셀안에 포함된 데이터의 일부를 추출하여 표현할 수 있는 함수 입니다. 예를 들어, 한 셀 안에 "오징어"라는 문구가 적혀 있으면, "오", "징", "어", "오징", "징어" 와 같이 "오징어"의 글자중 일부를 추출하여 표현할 수 이는 것이죠.

1. LEFT, RIGHT, MID 함수란!?

1) LEFT 함수 : 셀안에 기입된 데이터의 왼쪽에서부터 지정한 숫자의 갯수만큼 추출하여 표시.

2) RIGHT 함수 : 셀안에 기입된 데이터의 오른쪽에서부터 지정한 숫자의 갯수만큼 추출하여 표시.

3) MID : 셀안에 기입된 데이터의 지정한 지점에서부터 지정한 숫자의 갯수만큼 추출하여 표시.

2. LEFT, RIGHT, MID 함수의 사용 방법!!

1) LEFT 함수 : =LEFT(텍스트 포함된 셀, 좌측에서부터 몇번째까지 값을 추출할지에 대한 숫자)

2) RIGHT 함수 : =RIGHT(텍스트 포함된 셀, 우측에서부터 몇번째까지 값을 추출할지에 대한 숫자)

3) MID 함수 : =MID(텍스트 포함된 셀, 시작지점, 시작지점으로부터 몇번째까지 값을 추출할지에 대한 숫자)

LEFT, RIGHT, MID 함수에 포함되는 인수에 대해서 알아보았습니다. 다음은 구체적인 사용 방법에 대해서 알아보겠습니다.

코끼리, 거북이, 호랑이, 나무늘보, 강아지의 주민등록번호가 기입된 표가 있습니다. 모두가 알다시피 주민등록번호에는 많은 개인정보가 포함되어 있죠. 왼쪽에서부터 여섯째자리는 생년월일, 뒷부분 7자리는 성별 1자리, 지역코드 및 출생신고 5자리, 검증번호 1자리로 구성되어 있습니다. 여기에서는 주민등록번호로부터 간단하게 생년월일, 성별, 검증번호를 추출해 보도록 하겠습니다. (검증번호는 가장 우측의 번호 한자리로 자세한 설명은 넘어가도록 하겠습니다.)

코끼리 생년월일 : =LEFT(C3,6)

첫번째 인수는 코끼리 주민등록번호 'C3'셀, 왼쪽 6번째 자리가 생년월일 이므로 두번째 인수는 '6'을 기입합니다.

코끼리 성별 : =MID(C3,8,1)

첫번째 인수는 코끼리 주민등록번호 'C3'셀, 두번째 인수는 8번째 자리가 성별을 의미하는 수이므로 '8', 세번째 인수는 8번째 자리의 숫자 하나만 필요로 하므로 '1'을 기입합니다.

코끼리 검증번호 : RIGHT(C3,1)

첫번째 인수는 코끼리 주민등록번호 'C3'셀, 두번째 인수는 오른쪽에서 첫번째 자리가 검증번호 이므로 '1'을 기입합니다.

코끼리의 생년월일, 성별, 검증번호를 기입한후 아래로 드래그 하면 아래와 같은 결과가 나옵니다.

원하는 값들이 모두 표현되었습니다. 그러나, 성별이 '남자', '여자'가 아닌 '1'과 '2'로 표현되어 있어 이를 '남자', '여자'로 표현해보고자 합니다. 조건 IF 함수를 이용해 '1'이면 '남자', '2'이면 '여자'로 나타내봅시다.

코끼리의 성별 : =IF(MID(C3,8,1)="1","남자","여자")

'MID(C3,8,1)' 값이 '1'이면 '남자' 아니면 '여자' 입니다. 단 여기서 주의해야할 점은 MID(C3,8,1)을 통해서 추출한 값은 숫자가 아닌 텍스트 이므로 '1'을 큰따옴표 안에 넣고 수식을 완성해야 합니다.

나열되어 있는 데이터에서 값을 더해야하는 경우는 아주 빈번하게 발생합니다. 예를들어, 한달간 총지출한 금액의 합계를 구하거나 또는 구매해야할 자재들의 값의 합계를 구해 내가 가지고 있는 예산에 부합하는지를 검증해볼 수 있겠죠. 엑셀에서 합계를 구하는 방법은 수식에 '+' 또는 '-' 기호를 이용할 수 있습니다만, 이 기호를 이용하게 되면 셀을 하나하나 지정을 해줘야하는 큰 단점이 있습니다. 그러나, 'SUM'이라는 함수를 이용하게 되면, 값을 드래그하여 손쉽게 합산할 수 있습니다. 또한, 'SUMIF' 함수를 이용해 조건에 맞는 값을 합산할 수도 있죠.

1. SUM, SUMIF, SUMIFS 함수란!?

1) SUM 함수 : 숫자로 구성된 데이터의 값을 합산해주는 함수.

2) SUMIF 함수 : 조건에 맞는 값을 합산해주는 함수.

3) SUMIFS 함수 : 다중 조건에 맞는 값을 합산해주는 함수.

2. SUM, SUMIF, SUMIFS 함수 사용법!!

1) SUM 함수 : =SUM(합산범위)

2) SUMIF 함수 : =SUMIF(조건범위, 조건, 합산범위)

3) SUMIFS 함수 : =SUMIFS(합산범위, 조건범위1, 조건1, 조건범위2, 조건2, 조건범위3, 조건3, .....)

아래 그림의 'A창고 재고'의 표에서 '재고 총수량', '셔츠 총수량', '파란색 셔츠의 수량'에 대한 합산을 한번 해보도록 하겠습니다.

재고 총 수량 : =SUM(F5:F12)

합산할 데이터인 셀 F5 ~ 셀 F12까지 드래그하여 괄호안을 채워줍니다.

셔츠 수량 : =SUMIF(D5:D12,"셔츠",F5:F12)

첫번째 인수에는 조건에 대한 범위인 셀 D5 ~ 셀 D12까지 드래그하고, 두번째 인수에는 조건인 "셔츠"를 기입합니다. 그리고 합산할 데이터인 셀 F5 ~ 셀 F12까지 드래그하여 괄호안을 채워줍니다.

파란색 셔츠 수량 : =SUMIFS(F5:F12,D5:D12,"셔츠",E5:E12,"파랑")

SUMIFS 함수는 SUMIF 함수와 인수 구성이 다릅니다. SUMIFS 함수의 첫번째 인수에는 합산할 데이터 셀 F5 ~ 셀 F12까지 드래그하여 기입을 하고, 두번째 인수에는 종류에 해당하는 항목 셀 D5 ~ 셀 D12까지 드래그하여 기입하고, 세번째 인수에는 두번째 인수 조건의 범위에 해당하는 조건인 "셔츠"를 기입합니다. 네번째 인수에는 색상에 해당하는 항목 셀 E5 ~ 셀 E12까지 드래그하여 기입하고, 다섯번째 인수에는 네번째 인수 조건의 범위에 해당하는 조건인 "파랑"을 기입합니다.

위와 같이 기입을 하면 아래와 같은 결과가 나오게 됩니다. 

재고 총 수량 : 497

셔츠 수량 : 258

파란색 셔츠 수량 : 133

SUM, SUMIF, SUMIFS 함수에 대해서 알아보았습니다. 엑셀에서 합계를 구하고자 할때 SUM, SUMIF, SUMIFS 함수를 이용하여 데이터 합산을 효과적으로 처리해봅시다.

엑셀에서 나열되어 있는 데이터의 개수를 파악해야될 때가 있습니다. 그럴때 사용하는 엑셀 함수가 바로 'COUNT' 입니다. 'COUNT'라는 의미가 '~을 세다' 인데, 말그대로 데이터 수를 세어줍니다. 사실 수식을 기입하지 않고, COUNT 함수 보다 더 간단하게 데이터 수를 파악하는 방법도 있습니다. 셀에 기입된 데이터들을 모두 드래그하면 우측 하단에 개수, 합계 등의 정보를 한번에 파악할 수 있습니다.

위 그림의 우측 하단을 보시면 평균, 개수, 합계가 간단하게 표현이 되어 있는것을 볼 수 있습니다. 함수를 굳이 사용하지 않아야되는 경우에는 데이터들을 드래그하여 엑셀의 우측하단에서 쉽게 개수를 파악하는 것이 업무 효율이 더 좋습니다. 그러나 데이터개수를 COUNT 함수를 통해서 처리해야하는 경우에는 위와같은 임시방편적인 방법은 효율이 떨어질 수 있습니다. 때에 따라서 함수를 사용할지, 아니면 드래그를 해서 간단하게 정보를 파악할지를 선택해서 사용하시면 되겠습니다.

1. COUNT 함수의 종류?

COUNT 함수는 숫자 데이터만 셀 수 있습니다. 그러나 숫자 외의 문자값이 들어있는 셀도 세고 싶을때는 COUNT 함수를 쓸수 없습니다. 또, 빈칸인 셀을 세고 싶을때도 그냥 COUNT 함수를 사용해서는 개수를 세지 못합니다. 그래서 COUNT 함수는 다양한 형태가 존재 하는 것이죠. 아래에서 함수의 종류를 살펴 보겠습니다.

1) COUNT 함수 : 숫자가 들어있는 셀 세기

2) COUNTA 함수 : 숫자 뿐만 아니라 어떠한 데이터라도 들어있는 셀 세기

3) COUNTBLANK 함수 : 비어있는 셀 세기

4) COUNTIF : 조건에 맞는 셀 세기

2. COUNT 함수 사용법!!

이렇게 다양한 형태의 COUNT 함수가 존재 합니다. 4개의 COUNT 함수중 COUNTIF 함수를 제외하고 모두 1개의 인수를 가집니다. COUNTIF 함수의 인수는 조건이 추가가되어 2개 입니다.

1) COUNT 함수 : =COUNT(범위)

2) COUNTA 함수 : =COUNTA(범위)

3) COUNTBLANK 함수 : =COUNTBLANK(범위)

4) COUNTIF 함수 : = COUNTIF(범위, 조건)

아래의 연한 노란색부분에 기입된 값들을 COUNT 함수, COUNTA 함수, COUNTBLANK 함수, COUNTIF 함수를 적용시켜 보겠습니다.

1) "=COUNT(C2:C10)"

2) "=COUNTA(C2:C10)"

3) "=COUNTBLANK(C2:C10)"

4) "=COUNTIF(C2:C10, "바나나")"

1), 2), 3), 4)에 차례대로 위와 같이 기입해봅니다.

1) COUNT 함수는 숫자 '1', '2', '3' 3개의 데이터를 세어 '3'이라는 값을 나타냅니다.

2) COUNTA 함수는 숫자뿐만 아니라 문자도 세어주므로 '사과', '감자', '바나나', '1', '2', '3', '바나나' 이렇게 총 7개의 데이터를 세어 '7'이라는 값을 나타냅니다.

3) COUNTBLANK 함수는 빈칸 2개를 세어 '2' 값을 나타냅니다.

4) COUNTIF 함수는 조건이 바나나 이므로 바나나의 개수를 세어 '2'라는 값을 나타냅니다.

COUNT 함수에 대해서 알아보았는데, 실무에서도 자주 사용되는 함수이므로 위 네가지 'COUNT', 'COUNTA', 'COUNTBLANK', 'COUNTIF'를 숙지하고 때에 맞게 사용한다면 효율적으로 업무를 수행할 수 있을 것입니다.