이번 시간에는 RANK 함수에 대해서 알아보겠습니다.

RANK 함수는 흔히 순위를 구할때 사용됩니다.

RANK함수를 활용하여 순위를 구해보겠습니다.

 

 

아래 자료로 RANK 함수를 사용해보겠습니다.

 

예상적립금이 높은 사람이 1등으로 하는

순위를 구하는 RANK 함수

 

=RANK(I3,$I$3:$I$20,0)

=RANK(값,범위,정렬기준)

정렬기준 : 내림차순 = 0 혹은 빈칸

정렬기준 : 오름차순 = 1

 

쉽게 풀어보면

=순위구하기(I3값이, I3~I20까지의 수 중에서, 높으면 1등으로(내림차순)) 구해라

 

여기서 내림차순은 높은숫자가 위로 올라 가는 것이기 때문에

범위내에서 값이 제일 높은 것이 순위가 1로 표시 됩니다.

 

여기서 I3:I20 범위에

$표시가 앞뒤로 들어가 있습니다.

 

$표시는 절대값이라고 표현하기도 하며,

변하지 않는 값입니다.

 

랭크함수를 쓰고 아래로 드래그 하거나 자동채우기를 할때에

($I$3:$I$20) 범위값은 고정이 되는 겁니다.

 

엑셀에서 절대값을 설정할 시에는 F4키를 누르시면 자동으로 절대값이 지정됩니다.

I3:I20 범위를 선택하고 F4키를 누르시면 $표시가 붙을겁니다.

 

이상 엑셀 RANK함수를 활용한 순위구하기 포스팅을 마칩니다.

 

이번 시간에는 엑셀 필터에 대해 알아보겠습니다.

아래의 자료를 필터작업을 해보겠습니다.

 

 

범위설정 하시고

[데이터] - [필터]

필터 단축키 : Ctrl + Shift + L

 

 

필터를 하시면 위 사진처럼 필터 구분에 아래 화살표가 생깁니다.

 

 

아래 화살표를 누르시고

오름차순 및 내림차순 정렬을 할 수 있고

세부 필터를 적용해서 보고싶은 것만 필터걸어서 보실수 있습니다.

(이것은 자료가 삭제되는 것이 아닌 숨겨진 상태입니다.)

 

 

필터가 걸리면 위 사진처럼 필터모양이 표시가 됩니다.

(88,187만 선택함)

왼쪽에 화살표 부분을 보시면

1번다음에 바로 3번으로 표시됩니다.

2번 행이 숨겨져 있는 것입니다.

 

필터를 취소하시려면

[데이터] - [필터]를 눌러서 필터를 취소시키거나

 

 

필터를 사용했던 열을 찾아서 필터버튼을 다시 누르면

필터 해제(C) 메뉴가 활성화 되어 있습니다.

필터 해제를 해주시면 원상태로 돌아옵니다.

 

필터는 숫자 오름차순 내림차순 뿐만 아니라 색상필터도 걸 수 있고,

필요한 데이터의 행만 추출할 수 있습니다.

매우 유용한 메뉴이기 때문에 자주 활용하시면서 익히시기 바랍니다.

 

이상 포스팅을 마칩니다.

이번 시간에는 엑셀 정렬에 대해 알아보겠습니다.

 

엑셀에서 자주 사용되는 메뉴입니다.

 

 

[데이터] - [정렬]

 

정렬을 할 때에는 정렬하고자 하는 셀을 클릭하시고

[정렬]을 누르셔도 되고

직접 범위를 드래그 하셔서 잡아주셔도 됩니다.

 

 

[체크] 내 데이터에 머리글 표시(H)

(위에 머리글은 남겨두고 아래 데이터만 정렬하는 옵션)

 

 

체크 해제 시 아래와 같이 열 전체를 정렬대상으로 잡습니다.

 

 

머리글 표시로 하고 정렬을 해보겠습니다.

 

 

첫번째 기준 : [나이] 오름차순

두번째 기준 : [구분] 내림차순

[나이]가 같으면 두번째 기준인 [구분]이 내림차순(높은 것이 위쪽)으로 표시됩니다.

 

 

오름차순 

작은 단위가 위로 표시됩니다.

1, A, 가

2, B, 나

3, C, 다

.

.

.

 

내림차순

큰 단위가 위로 표시됩니다.

3, C, 다

2, B, 나

1, A, 가

.

.

.

 

이상 포스팅을 마칩니다.

이번 시간에는 엑셀 틀고정에 대해 알아보겠습니다.

 

틀고정이란?

나머지 시트를 스크롤 하는동안 시트 일부가 표시되도록 하는 기능입니다.

 

 

위 사진과 같이 데이터가 많을 경우

[연번] [일자] [성명] [생년월일] . . .메뉴를 고정시키는 겁니다.

(마우스 스크롤을 내려도 메뉴는 그대로 보이게 하는 기능)

 

상단의 [보기] - [틀고정]을 누르시면 됩니다.

기본적으로 틀고정, 첫 행 고정, 첫 열 고정이 있습니다.

첫 행 고정은 맨 위 첫줄을 고정시키는 것이고

첫 열 고정은 맨 왼쪽 첫줄을 고정시키는 것입니다.

 

자주 사용하는 틀고정 메뉴를 설명하겠습니다.

고정하고자 하는 행, 열 또는 행과열 모두를 설정할 수 있습니다.

 

 

행고정 열고정을 해보기

첫 행 고정과 첫 열 고정이 아니여도

원하는 행과 원하는 열 고정을 할 수 있습니다.

 

 

8번 행을 클릭하고 틀고정을 하면

스크롤을 내려도 연번 5번까지는 고정이 됩니다.

 

이해가 되시나요?

틀고정한 부분은 위 사진과 같이 줄이 생깁니다.

 

열도 마찬가지 입니다.

 

지금까지 엑셀 틀고정 메뉴 활용법에 대해 알아봤습니다.

 

이상 포스팅을 마칩니다.

이번 시간에는 반올림 함수에 대해 알아보겠습니다.

 

ROUND 함수(반올림)

ROUNDUP 함수(올림)

ROUNDDOWN 함수(내림 혹은 버림)

ROUND 함수는 반올림 함수입니다.

 

 

간단한 예로 나누기를 할 때에 소수점이 표시되는 경우가 있습니다.

1,000 나누기 3 = 333.3333을 예제로 만들어봤습니다.

 

=ROUND(C2,-2) : 333.3333 5미만이라 버림 -> 300

=ROUND(C2,-1) : 333.3333 5미만이라 버림 -> 330

=ROUND(C2,0) : 333.3333 5미만이라 버림 -> 333

=ROUND(C2,1) : 333.3333 5미만이라 버림 -> 333.3

.

.

 

0~4 값은 버림 -> 0으로 표시

5~9 값은 올림 ->0으로 표시하고 앞단위 올림

 

2,000 나누기 3 = 666.6667

 

=ROUND(C3,-2) : 666.6667 5이상이라 올림 -> 700

=ROUND(C3,-1) : 666.6667 5이상이라 올림 -> 670

=ROUND(C3,0) : 666.6667 5이상이라 올림 -> 667

=ROUND(C3,1) : 666.6667 5이상이라 올림 -> 666.7

.

.

 

이렇듯 ROUND함수는 [반올림 하려는 수], [자릿 수]로 구성됩니다.

 

 

 

ROUNDUP : 무조건 올리는 함수입니다.

 

ROUNDDOWN : 무조건 버리는 함수입니다.

 

 

[엑셀 꿀팁] 함수를 사용하지 않고 소수점 반올림 하는 방법 1

위 사진과 같이 [홈] 메뉴에서 [자릿수 줄임] 클릭

(자릿수가 줄어들면서 자동 반올림이 됩니다.)

 

 

[엑셀 꿀팁] 함수를 사용하지 않고 소수점 반올림 하는 방법 2

위 사진과 같이 [홈] 메뉴에서 [ , ] 쉼표 스타일 클릭

[1000 단위 구분 기호]입니다.

(소수점 첫째자리에서 반올림 됩니다.)

 

이상 포스팅을 마칩니다.

 

이번 시간에는 ERROR.TYPE 함수와 IFERROR 함수에 대해 알아보겠습니다.

 

날짜 형식이 잘못된 것을 찾을때 활용 할 수 있는 방법을 소개하고자 합니다.

 

2019-02-28 = 정상

2019-02-29 = 오류

2019-02-30 = 오류

.

.

.

 

 

위 사진과 같이 일자가 맞지 않으면 확인하라는 메시지를 표시하게 됩니다.

 

첫번째 방법은

=IF(ERROR.TYPE(DAY(A12))=3,"일자 확인","")

 

IF 함수와

ERROR.TYPE 함수

DAY 함수

총 3가지 함수를 사용했습니다.

 

늘 그렇듯 함수가 여러게 쓰이면 제일 안쪽 함수부터 풀어보셔야 합니다.

DAY(A2) : 주어진 달에 일자를 반환하는 함수입니다. 

(A12셀이 2019-02-29이니 오류(#VALUE!)를 반환합니다.)

... 19년 2월 29일은 없으니까요. 오류..

 

ERROR.TYPE(#VALUE!) : 오류에 해당하는 번호를 반환합니다.

DAY함수에서 #VALUE!가 반환되었습니다.

 

 

위 사진 처럼 ERROR.TYPE(#VALUE!) = 3이라는 것을 알 수 있습니다.

 

IF(ERROR.TYPE(#VALUE!)=3,"일자 확인","")

이 식은

IF(3=3,"일자 확인","")

이것과 같습니다.

 

그리하여, 2019-02-29는 "일자 확인" 이라는 문구가 반환되는 겁니다.

 

 

IF는 함수활용에 대해서는 아래 포스팅을 참고하시기 바랍니다.

 

 

두번째 방법으로는

IFERROR 함수입니다.

 

 

IFERROR 함수는 계산식에 오류가 있으면 오류 값을 다른 값으로 반환하는 함수입니다.

(오류가 아니라면 그 값을 그대로 반환합니다.)

즉, #N/A라고 뜨는 수식을

IFFERROR(#N/A,"") 라고 하시면

#N/A -> 빈칸 으로 표시됩니다.

 

앞서 ERROR.TYPE 함수에 적용을 한다면

=IFERROR(IF(ERROR.TYPE(DAY(A12))=3,"일자 확인",""),"")

위사진에서 #N/A라고 표시된 부분이 빈칸으로 됩니다.

다른문구 "정상" 이라고 쓰셔도 되고 활용은 취향에 따라 하시면 됩니다.

 

=IFERROR(DAY(A12),"일자 확인")은

 

마찬가지로 안에 함수부터 풀어보면

DAY(A12) : #VALUE! 값이 나옵니다.

=IFERROR함수는 오류 값을 다른 값으로 반환하는 것이기 때문에

=IFERROR(#VALUE!,"일자 확인")

이 수식에서 오류가 있으면 "일자 확인"이라는 단어를 반환해라!

라는 겁니다.

오류가 있기 때문에 일자 확인 이라는 단어가 반환됩니다.

단, 오류가 없다면 DAY(A12) 값이 그대로 반환됩니다.

 

이상 날짜형식 참과 거짓을

ERROR.TYPE 함수와 IFERROR 함수를 활용하여

구분을 해봤습니다.

 

 

 

 

이번 시간에는 [빠른실행 도구모음 사용자 지정]에 대해 알아보겠습니다.

엑셀을 키시면 맨위에 있는 메뉴입니다.

기본으로 저장버튼, 취소, 다시실행 버튼이 있습니다.

 

 

아래 화살표를 누르시면 [기타 명령(M)]이 있습니다.

 

 

여러가지 기능들을 삽입 할 수 있습니다.

테스트로

[인쇄 미리보기]와 [빠른인쇄]를 넣어보겠습니다.

 

 

추가(A) >> 누르시고

확인버튼 클릭

 

 

위 화면과 같이 메뉴가 추가된 것을 볼 수 있습니다.

이게 편한게 단축키 처럼 사용할 수 있다는 점입니다.

 

Alt + 1, 2, 3, 4, 5, ...

Alt + 1 = 저장

Alt + 2 = 취소(되돌리기)

Alt + 3 = 다시실행

Alt + 4 = 인쇄 미리보기

Alt + 5 = 빠른 인쇄

이해 하셨나요?

 

Alt + 숫자 조합으로 단축키를 만들 수 있는 겁니다.

순서 변경도 가능하니 필요에 맞게 사용하시면 됩니다.

 

 

자주 쓰는 메뉴가 있다면 추가해서 단축키로

사용하면 유용하겠죠?

 

단축키로 안써도 맨 위에 바로 표시되니 클릭하셔서 쓰시면 매우 편리합니다.

 

이상 포스팅을 마칩니다.

이번 시간에는 엑셀 기초 LEFT, MID, RIGHT함수 활용법에 대해서 알아보겠습니다.

개인정보보호법 강화로 주민등록번호를 생년월일로만 표기하고 보관하는 경우가 많습니다.

 

간단하게 함수 설명을 하면

 LEFT 함수 : 셀의 텍스트 왼쪽에서 몇개를 가져와라

=left(B3,3)이라고 한다면 결과값은 900

MID 함수 : 셀의 텍스트 중 왼쪽에서 몇번째부터 몇개를 가져와라

=mid(B3,3,4)이라고 한다면 결과값은 0123

RIGHT 함수 : 셀의 텍스트 중 오른쪽에서 몇개를 가져와라

 =right(B3,3)이라고 한다면 결과값은 001

LEFT 함수 활용하기 위한 예제 화면입니다.

주민등록번호를 생년월일로 변환하고 싶으시면

간단한 LEFT 함수를 사용하시면 됩니다.

C3셀에

=left(B3,8)

라고 치시면

900123-1

표시될 것입니다.

 

 

위 사진과 같이 저 위치에서 마우스를 드래그 앤 드롭 하시면 김십등까지 바로 적용이 됩니다.

(십자가 위치에서 마우스 더블클릭하셔도 적용 됩니다.)

주민등록번호 생년월일 변환 함수 어렵지 않죠?

 

다음은 MID함수와 IF함수를 사용해 성별 구분해보겠습니다.

주민등록번호에는 많은 정보를 포함하고 있습니다.

앞자리는 태어난 연,월,일 이고요

 

뒷자리의 첫숫자는 성별을 나타냅니다.

주민등록번호 뒷자리가 1로 시작하면 [남성]

주민등록번호 뒷자리가 2로 시작하면 [여성]

주민등록번호 뒷자리가 3로 시작하면 [남성]

주민등록번호 뒷자리가 4로 시작하면 [여성]

주민등록번호 뒷자리가 5로 시작하면 [외국인 남성]

주민등록번호 뒷자리가 6로 시작하면 [외국인 여성]

 

각설하고

MID함수와 IF함수를 사용해 보겠습니다.

 

함수를 보실때는 앞에 함수보다 괄호안에 있는 함수부터 살펴봐야 합니다.

(MID(B3,8,1) 알아보겠습니다.

MID 중간텍스트를 가져와라.

B3셀의 왼쪽에서 8번째부터 1개를 가져와라 입니다.

그래서 MID값은 "1"이 도출되는 겁니다.

여기에 그냥 1로 쓰지않고 따옴표("")를 쓰는 이유는

B3셀의 데이터는 숫자가 아니기때문에 엑셀에서는 텍스트로 인식을 하고 있습니다.

즉, 계산식의 결과물이 숫자라면 따옴표("")는 필요가 없습니다.

하지만, 계산식의 결과물이 텍스트라면 따옴표("")를 써주셔야 수식이 제대로 도출됩니다.

 

=IF(MID(B3,8,1)="1","남성","여성")

IF함수 밑줄친 부분은 조건문.

파란색을 참 값, 빨간색의 거짓 값,

조건이 참(맞다면) "남성"이라고 도출하고

조건이 거짓(아니라면) "여성"이라고 도출하라

입니다.

 

RIGHT함수는 LEFT함수와 매우 유사하니 생략하도록 하겠습니다.

LEFT함수와 반대로 뒤에서 몇개를 가져오는 함수입니다.

 

위에 IF함수가 잘 이해가 안가신다면

아래 바로가기로 가셔서 참고하시기 바랍니다.

 

이상 포스팅을 마치겠습니다.

 

이번 시간에는 엑셀 기초 IF함수 활용법에 대해서 알아보겠습니다.

 

IF함수는 흔히 많이 사용되는 함수입니다.

활용도가 무궁무진하죠.

 

이번시간에는 기초인 만큼 IF함수 사용법에 대해 알아보겠습니다.

 

예시 입니다.

3명의 사람이 각기 다른 세뱃돈과 자기 돈으로 과일과 채소를 샀습니다.

 

계산은 간단한 부분이기 때문에

아래와 같이 =B3-C3-D3로 입력하였습니다.

 

 

간단한 수식 활용 및 SUM함수 활용은

아래 포스팅을 참고해주세요.

 

 

이제 IF함수 사용법에 대해 최대한 쉽게 설명하겠습니다.

 

 

모든 함수에는 =를 앞에 쓰고 사용하셔야 합니다.

= 난 함수를 쓰거나 수식을 걸거야~ 라는 표시입니다.

IF함수는 조건이 맞으면 이것을 표시하고 조건이 안맞으면 저것을 표시해! 입니다.

즉, =IF(조건문,이것,저것)이 되는 것 입니다.

이것 과 저것은 사용자가 정해주면 되는 겁니다.

위에서는 "우수", "나쁨"을 사용했습니다.

 

E3>=0 이란말은 E3셀(김부자의 계산 금액이) 0 보다 크거나 같으면(이상) 이란 뜻입니다.

E3=0 (E3셀이 0이라면, 동등)

E3>0 (E3셀이 0보다 크다면, 초과)

E3<0 (E3셀이 0보다 작다면, 미만)

E3<= (E3셀이 0보다 작거나 같으면, 이하)

 

이렇게 활용할 수 있습니다.

 

=IF(E3>=0,"우수","나쁨") 이 수식을 보자면

조건문인 (E3>=0)이 값이 -30,000 이므로,  0보다 크지 않아! (아니다)거짓이라고 나옵니다.

그렇기 때문에 "나쁨"이라는 단어를 표시하는 겁니다.

 

조건결과 값이 0이 이상이였다면 "우수"란 단어를 표시하겠죠?

 

IF문은 다른 함수와 같이 활용할 수 있는 활용도가 매우 높은 함수입니다.

자꾸 접해보시고 시도해시기 바랍니다.

 

이번 시간에는 엑셀에서 가장 간단한 더하기에 대해 포스팅 하겠습니다.

2가지 방법으로 설명하겠습니다.

 

1. 셀 더하기

말 그대로 연속된 셀 혹은 위치가 다른 셀의 값을 더할 때 사용 됩니다.

합계 부분에 =B3+C3+D3 표시된 것 보이시나요?

"="를 치시고 더하고자 하는 셀을 마우스로 클릭하시면 됩니다.

셀을 클릭하시고, 더하기(+) 쓰시고 다시 다른 더할 셀을 클릭하시면 됩니다.

 

엑셀에서 모든 수식과 함수를 사용하시려면 앞에 "="를 입력하고 하셔야 합니다.

 

더하기 뿐만 아니라 곱하기(*), 나누기(/), 빼기(-) 다 가능합니다.

 

함수를 몰라도 기본 산수만 알면 쉽게 계산이 가능합니다.

 

2. SUM 함수

함수를 사용할때도 마찬가지로 =를 쓰고 함수를 사용합니다(=SUM)

합계에서 보시다시피 =SUM(B3:D3) 사용했습니다.

여기서 ":"란 무엇이냐?

B3셀부터 D3셀을 포함한 것입니다.

B3셀과 D3셀에 사이에 있는 C3셀도 포함한다는 말입니다.

 

즉, 위사진에서 보듯이 문학 한국사 지리를 모두 더하라는 식입니다.

 

 

 

SUM함수를 사용할 때 아래와 같이

=SUM( 치시고 마우스로 드래그 하시면 범위를 설정하실 수 있습니다.

 

이상 포스팅을 마칩니다.

+ Recent posts