INDIRECT 함수는 텍스트로 지정한 셀 주소를 반환하는 함수다.

예를 들어 '=INDIRECT("A3")'은 '=A3'을 의미한다.

그럼 '그냥 A3을 참조하면 될 것을 왜 이렇게 복잡한 방법으로 참조하지?' 하는 의문이 생길 것이다.

이 함수는 혼자 쓰이지 않으며 다른 찾기/참조 함수와 같이 쓰여, 동적(변하는)인 셀 참조를 가능하게 해준다.

예를 들어, INDEX, MATCH, CHOOSE, VLOOKUP, HLOOKUP, LOOKUP 과 같은 값을 찾을 경우 주로 사용하는 함수와 같이 쓰여서 동적으로 변하는 조건에 따른 정확한 값을 찾게 해준다는 것이다.

 

 

예제 파일과 완성 파일

 

성적표.xlsx 

성적표완성.xlsx

 

 

 

 

INDIRECT 함수 기초

 

 

 

=INDIRECT("셀 주소", [옵션])

셀 주소는 텍스트 문자열 형태로 A1 스타일 또는 R1C1 스타일의 셀주소를 의미한다.

옵션은 생략하거나 TRUE 값이면 A1 스타일을 사용한다는 의미이고,

FALSE 값이면 R1C1 스타일을 사용한다는 의미이다.

 

A1 스타일? R1C1 스타일?

A1 스타일의 셀 주소는 널리 사용되는 B5, M2와 같은 셀 주소 스타일이다.

RIC1 스타일은 ROW(행) 번호, COLUMN(열) 번호로 셀 주소를 참조하는 스타일을 의미한다.

예를 들어, 'R3C4'는 3행의 4열이므로 'D3' 셀을 의미한다.

 

 

INDIRECT 함수는 아래 두 가지 방법으로 사용할 수 있다.

 

 

 

 

INDIRECT에서 셀 주소가 진짜 셀 주소를 의미할 경우 문자열로 표시하기 위해 큰 따옴표(")를 사용하고,

셀 주소가 입력된 셀을 참조할 경우 큰 따옴표 없이 사용한다.

위의 예를 보면 'B9'셀에 입력된 식인 '=INDIRECT("B3")'은 '=B3'과 같으므로 '이순신'이 결과로 표시된다.

'B10'셀에 입력된 식인 '=INDIRECT(E3)'은 'E3' 셀에 입력된 값인 'B3'을 셀 주소로 사용한다는 의미이므로 '=B3'이 되어 역시 '이순신'이 결과로 표시되는 것이다.

 

 

 

INDIRECT 함수 활용

 

 

 

아래와 같이 '중간고사', '기말고사' 시트에 각각 학생들의 점수와 평균, 석차가 입력되어 있을 때 '학생별성적' 시트에서 '시험구분'과 '이름'을 지정하면 '국어', '영어', '수학', '평균', '석차'를 자동으로 찾아주는 예제를 만들어보겠다.

이 예제에서는 INDIRECT 함수와 함께 데이터 유효성 검사, VLOOKUP 함수가 사용되므로 모르겠다면 아래의 글을 함 보자~!!

 

엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y 

엑셀 함수 4 (찾기/참조 함수) by Y

 

  

 

 

 

시험구분은 데이터 유효성 검사 기능을 이용하여 선택하도록 만들어보겠다.

C2 셀을 선택한 후 [데이터]-[데이터 유효성 검사] 명령을 실행하여 '제한 대상'은 '목록', '원본'에는 '중간고사, 기말고사'를 입력한다. '중간고사, 기말고사'는 시트 이름이므로 정확하게 입력해야 한다.

 

 

 

 

아래와 같이 데이터를 선택할 수 있게 되면 하를 선택하고, 이름에는 학생 이름 중에 아무 이름이나 입력한다.

물론 이름도 '시험구분'처럼 데이터유효성검사를 이용하여 선택해도 되지만, 여기에서는 직접 입력하도록 했다.

 

          

 

 

 

C4~C8 셀에 아래와 같이 식을 입력하면, 이순신의 중간고사 데이터가 모두 찾아진다.

 

 

 

 

사실 C4 셀에 입력된 식에서 빨간색으로 표시된 'C2' 부분 때문에 INDIRECT 함수를 사용한 것이다.

C2 셀의 값이 현재는 '중간고사'지만, '기말고사'가 될 수도 있다.

이 변하는 값을 INDIRECT 함수를 이용하면 모든 참조가 가능해지는 것이다.

이와 같이 참조에서 변하는 부분이 있다면 INDIRECT 함수를 사용하면 해결된다.

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

데이터의 행열을 바꾸는 방법에는 [선택하여 붙여넣기]-[행/열 바꿈] 명령을 사용하는 방법과

Transpose 함수를 이용하는 방법의 두 가지 방법이 있다.

 

 

각 방법의 장점

행/열 바꿈 - 서식까지 복사되므로 따로 서식을 지정할 필요가 없다. 함수보다는 좀 더 쉽다.

Transpose 함수 - 원본값이 변경되면 자동으로 변경되므로 데이터 업데이트에 신경쓰지 않아도 된다.

 

두 가지 방법의 장점을 잘 살펴보고 각각 용도에 맞게 사용하면 좋을 듯하다.

(참고로 Transpose 함수는 컴활 1급 실기에 나오는 함수다.)

 

 

 

 

[선택하여 붙여넣기]-[행/열 바꿈] 사용하여 행열 바꾸기

 

 

복사할 범위를 지정한 후 Ctrl+C 키를 누른다.

 

 

 

붙여넣을 셀의 바로가기 메뉴에서 [선택하여 붙여넣기]를 클릭한다.

선택하여 붙여넣기의 단축키 Ctrl+Alt+V 를 눌러도 된다.

 

 

 

'행/열 바꿈' 옵션을 체크한 후 [확인]을 클릭한다.

 

 

 

아래와 같이 행열이 바뀌고 서식까지 모두 붙여넣기 된다.

 

 

 

 

 

Transpose 함수를 사용하여 행열 바꾸기

 

 

Transpose 함수 배열 함수이므로 사용법이 다른 함수와는 다르다.

 

Transpose 함수의 사용 순서

1. 범위 지정

2. '=transpose(원본범위)' 식 입력 후 Shift+Ctrl+Enter

 

 

 

값이 붙여질 범위를 미리 지정한다.

 

 

 

'=Transpose(B2:E7)' 식을 입력한 후 Shift+Ctrl+Enter 키를 누른다.

 

 

 

원본 범위의 값만 행열이 바뀌어 표시된다.

 

 

 

 

 

[선택하여 붙여넣기]를 이용하여 서식을 행열 바꾸어 붙여넣기

 

 

 원본을 복사한 후

붙여넣을 셀의 바로가기 메뉴에서 [선택하여 붙여넣기]를 클릭한다.

아니면 Ctrl+Alt+V키를 눌러도 된다.

 

 

 

붙여넣을 옵션으로 '서식'을 선택한 후 '행/열 바꿈'을 선택한다.

이렇게 설정하면 서식이 행열이 바뀌어 붙여넣기된다.

 

 

 

아래와 같이 서식이 행열이 바뀌어 붙여넣어진다. 

 

 

 

 

 

이제 원본을 변경해보면 Transpose 함수를 이용하여 붙여넣은 값만 자동으로 변경되는 것을 볼 수 있다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

DATEDIF 함수란?

경과년수, 경과개월수, 경과일수 등을 구할 때 자주 사용하는 함수다.

 

 

 

=DATEDIF(시작일, 종료일, "옵션")

시작일부터 종료일까지 경과일을 옵션에 따라 구한다.

 

옵션 설명

※ 옵션이 'MD', 'YM', 'YD' 일 경우 윈도우 버전에 따라

오류가 나거나 제대로 답이 구해지지 않는 경우가 있다.

 

 

 

 

DATEDIF 함수 활용 예 1

 

연인이 만난지 몇일이 지났는지 구해보자.

=DATEDIF(처음 만난 날짜,TODAY(),"D")

예) =DATEDIF("2012-05-11",TODAY(),"D")

 

*** TODAY()는 시스템의 현재 날짜를 의미한다 ***

 

 

 

 

DATEDIF 함수 활용 예 2

 

직원의 근무년수, 근무개월수, 근무일수와 근속기간을 모두 구하는 식을 구해보자. 

일단 작성일에 '=TODAY()' 식을 입력하여 현재 날짜가 표시되도록 한다.

 

 

 

 

근무년수에 '=DATEDIF(C4,$C$2,"Y")' 식을 입력한다.

시작일은 입사일이고, 종료일은 작성일로 지정한 후

경과년수만 구하기 위해 옵션에 Y를 입력한다.

 

 

 

 

근무개월수에 '=DATEDIF(C4,$C$2,"M")' 식을 입력한다.

경과월을 구하기 위해 옵션에 M 입력한다.

 

 

 

 

근무일수에 '=DATEDIF(C4,$C$2,"D")' 식을 입력한다.

경과일을 구하기 위해 옵션에 D를 입력한다.

 

 

 

 

'근속기간1'에는 경과년/경과월/경과일이 0이 되어도 그대로 표시해보겠다.

 

=DATEDIF(C4,$C$2,"Y")&"년 "&DATEDIF(C4,$C$2,"YM")&"개월 "&DATEDIF(C4,$C$2,"MD")&"일"

 

YM은 1년 미만의 개월수를 표시해주고,

MD는 1개월 미만의 일수를 표시해주는 옵션이다.

*** & 기호는 함수식이나 문자열을 연결하는 연산자다.***

 

 

 

 

'근속기간2'에는 경과년/경과월/경과일이 '0'일 경우 생략해보겠다.

 

=IF(DATEDIF(C4,$C$2,"Y")=0,"",DATEDIF(C4,$C$2,"Y")&"년 ")

&IF(DATEDIF(C4,$C$2,"YM")=0,"",DATEDIF(C4,$C$2,"YM")&"개월 ")

&IF(DATEDIF(C4,$C$2,"MD")=0,"",DATEDIF(C4,$C$2,"MD")&"일")

 

'IF(DATEDIF(C4,$C$2,"Y")=0,"",DATEDIF(C4,$C$2,"Y")&"년 ")' 식의 뜻은???

=> 경과년수가 0이라면 ""(공백)이고, 아니면 경과년수에 '년 '을 붙여 표시해준다.

 

IF 함수에 대해 알고 싶다면 함 보자~!!  엑셀 함수 7 (논리 함수, IF 함수) by Y

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

SUBTOTAL 함수란?

목록이나 데이터베이스의 부분합을 구하는 함수이고, [데이터] 메뉴의 [부분합] 명령을 사용하여 부분합 목록을 작성하는 방법과 같이 11가지 기능을 가진 다 기능 함수다. SUBTOTAL로 많이 사용하는 함수는 SUM, AVERAGE, COUNT 등의 함수이다.

 

 

SUBTOTAL 함수의 특징

1. SUBTOTAL 함수는 필터 결과에 포함되지 않은 행을 모두 무시하기 때문에 자동 필터와 함께 많이 사용한다.

2. [데이터]-[부분합]으로 부분합을 계산할 경우 사용되는 11가지의 계산이 SUBTOTAL 함수를 이용한 계산이므로 함수를 수정하여 부분합의 계산을 수정할 수 있다.

3. 범위에 다른 부분합이 있으면 이중으로 계산되는 것을 피하기 위해 중첩된 부분합을 무시한다. 즉, 영역 안에 다른 부분합은 계산하지 않는다.

 

 

=SUBTOTAL(함수 번호, 계산할 범위)

범위를 함수 번호에 해당하는 함수로 계산한다.

예) =SUBTOTAL(9, B5:B10)  =>  B5:B10 범위에 해당하는 숫자들을 합한다.

 

 

함수 번호에 해당하는 함수와 설명

 

 

101~111은 행을 숨길 경우 숨긴 행의 값을 계산하지 않을 때 사용하면 된다.

반면에, SUBTOTAL 함수는 숨긴 열에는 영향을 받지 않는다.

 

 

 

SUBTOTAL 활용 1

 

 

 

1. 자동필터를 적용한 후 화면에 표시되는 데이터만 자동으로 계산되는 예제를 만들어보자.

무게와 단가에 대한 합을 H15셀과 I15셀에 SUBTOTAL 함수로 미리 구해보았다.

여기까지는 SUM을 사용했을 때와 답이 다르지 않다.

 

무게의 합(H15) => SUBTOTAL(9, H3:H14)

단가의 합(I15) => SUBTOTAL(9, I3:I14)

  

 

 

 

 

2. 자동 필터를 적용하기 위해

합계 행을 제외한 데이터를 범위 지정한 후

[데이터]-[필터] 명령을 클릭한다.

 

 

 

 

3. 제목행에 조건에 따라 필터링할 수 있는 단추가 표시된다.

 

 

 

 

4. '지점명'이 '대륙전자'인 데이터만 필터링하기 위해

'지점명'의 단추를 클릭한 후 '제일전자'를 체크 해제한다.

 

 

 

 

5. 자동필터가 실행되어 '대륙전자' 데이터만 표시되고,

무게와 단가의 합계가 화면에 표시된 데이터의 합계만 구해진다.

SUM을 사용했다면 여전히 972, 7246000 이 구해졌을 것이다.

이렇게 SUBTOTAL 함수는 자동필터와 함께 자주 사용된다.

 

 

 

 

SUBTOTAL 활용 2

 

 

 

1. 자동필터링되도 일련번호가 항상 '1, 2, 3...'으로 유지되는 예제를 만들어보자.

B3 셀에 '=SUBTOTAL(3, $C$3:C3)' 식을 입력한 후 아래로 수식을 복사한다.

 

'=SUBTOTAL(3, $C$3:C3)' 식에서 '3'은 함수 'COUNTA'를 의미한다.

범위에 이렇게 사용한 이유는

'$C$3:C3'에서 앞의 'C3'을 절대참조로 고정하여

'C3:C4', 'C3:C5', 'C3:C6', 'C3:C7'과 같이 참조가 변하도록 하기 위해서다.

 

참조에 대해 궁금하다면 함 보자!!  엑셀 셀 참조 (상대참조, 절대참조, 혼합참조) by Y

 

 

 

 

2. 식이 복사되어 아래와 같이 일련번호가 만들어진다.

 

 

 

 

3. 위의 활용 예제에서와 같이 자동필터를 실행하면

눈에 보이는 범위만 자동으로 재계산하여 정상적으로 일련번호가 표시된다.

 

 

이와 같이 SUTOTAL 함수를 자동필터와 함께 사용하면 편리하게 계산할 수 있다.

 

 

 

 

☞ 일련번호 만드는 다른 방법

 

2013/04/29 - 엑셀 함수 15 - ROW 함수 (엑셀 일련번호 만들기) by Y

2013/05/14 - 엑셀 자동채우기 (일련번호, 수식복사, 날짜채우기 등 실습하기) by Y 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

예제 파일 : frequency.xlsx

 

 

FREQUENCY 함수는 숫자 범위에서 분포도를 세로 배열 형태로 구해주는 함수다.

FREQUENCY 함수를 사용하면 학생들의 점수대가 어떤지, 승진 시험 결과의 점수대가 몇명인지 등을 구할 수 있다.

 

 

=FREQUENCY(데이터 배열, 구간 배열)

데이터 범위에서 구간 배열의 간격에 해당하는 개수가 몇개인지 구할 수 있다.

예) =FREQUENCY(A1:A40, C5:C10)  =>  [A1:A40] 자료가 [C5:C10] 간격에 해당하는 분포수를 구한다.

 

 

FREQUENCY 함수 사용 유의점

1. 구간 배열에 '..49, 59..' 처럼 입력하면 '50~59' 사이가 몇 명인지 구할 수 있다.

2. 함수 작성 순서

① 분포수를 구할 범위를 미리 선택한다.

② 함수식을 작성한다.

Ctrl+Shift+Enter 키를 누른다.

 

 

 

FREQUENCY 활용

 

 

 

 아래 카페 회원 현황의 나이 데이터를 이용하여 각 나이대 인원수를 구해보겠다.

데이터 파일을 첨부했으니 다운받아 따라해보자.

 

 

 

 

1. 파일을 불러오면 아래 그림의 윗부분처럼 데이터가 작성되어 있다.

[G9:G12] 범위에 구간을 아래부분처럼 작성한다.

 

 

 

2. 각 점수가 의미하는 구간이다.

여기 데이터에는 10대가 없어 아래처럼 작성하였다.

 

 

 

 

3. 인원수를 구하기 위해 범위를 지정한다.

 

 

 

 

4. =FREQUENCY(C5:C17, G9:G12) 식을 작성한다.

C5:C17 범위는 나이 데이터이고, G9:G12 범위는 구간 배열 범위이다.

 

 

 

 

5. [Ctrl]+[Shift]+[Enter] 키를 누른다.

 

 

 

 

6. 인원수가 구해지면 맞는지 확인해보자.

40대를 살펴보면 맞게 구해진 것을 볼 수 있다.

 

 

 

 

7. 이제 지저분한 구간 범위를 숨기기 위해

[G] 열 머리글을 클릭하여 G 열을 선택한 후 마우스 오른쪽 메뉴에서

[숨기기] 메뉴를 클릭한다.

 

 

 

 

8. G 열이 숨겨져서 나이대와 인원수 필드만 표시된다.

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

ROW 함수는 참조셀의 행 번호를 구하는 함수다.

반대로 열 번호를 구하는 함수로 COL 함수가 있지만, ROW 함수의 활용도가 훨씬 높으니 ROW 함수만 다뤄보기로 한다.

 

 

=ROW(셀 주소)

간단히 셀 주소의 행번호를 구하므로 예를 들어 셀 주소가 C5라면 '5'가 답이 된다.

 

 

 

활용

 

 

ROW 함수를 이용하여 여러 행을 삽입/삭제해도 제대로 표시되는 일련번호를 만들어보겠다.

자동 채우기를 이용한 일련번호와 ROW 함수를 사용한 일련번호를 비교해보자.

 

 

1. B3 셀에 1을 입력한 후 [Ctrl] 키를 누르고

채우기 핸들을 클릭하고 아래로 쭈~욱 드래그하면

오른쪽 그림처럼 일련번호가 입력된다.

 

 

 

 

2. C3 셀에 '=ROW(C3)-2' 수식을 입력한 후

채우기 핸들을 클릭하여 아래로 쭈욱~드래그하면 수식이 복사되어

오른쪽 화면과 같이 일련번호가 입력된다.

 

수식 설명

'=ROW(C3)'만 입력하면 처음 값이 '3'이 되므로

2를 빼서 처음 값이 '1'이 되도록 구했다.

 

 

 

 

3. 중간 행이 삭제될 경우 어떻게 되는지 비교해보자.

'8~11' 행을 선택한 후 삭제한다.

 

 

 

 

4. 자동 채우기를 이용한 경우는 중간에 6~9가 빠지지만

ROW 함수를 사용한 오른쪽의 경우 항상 행 번호를 참조하기 때문에

변함없이 열련번호가 표시되는 걸 볼 수 있다.

 

 

 

추가 활용

 

홀수행이나 짝수행만 강조하는 조건부 서식일 경우 ROW 함수를 사용하면 된다.

조건부 서식을 모를 경우 참조!! -> 엑셀 조건부 서식 3 (수식을 이용한 조건부 서식) by Y

 

짝수행일 경우의 수식   =MOD(ROW(데이터의첫번째셀주소), 2)=0

홀수행일 경우의 수식   =MOD(ROW(데이터의첫번째셀주소), 2)=1

 

수식을 위와 같이 작성하면 홀수행/짝수행만 지정하여 서식을 줄 수 있게 된다.

MOD 함수는 앞의 수를 뒤의 수로 나눈 후 나머지를 구하는 함수다.

 

 

 

 

☞ 일련번호 만드는 다른 방법

 

2013/05/03 - 엑셀 함수 17 - SUBTOTAL 함수 (자동필터 결과 합하기, 일련번호 만들기 예제) by Y

2013/05/14 - 엑셀 자동채우기 (일련번호, 수식복사, 날짜채우기 등 실습하기) by Y 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

찾기/참조 함수 글을 포스팅할 때 CHOOSE 함수를 빼먹었다는 걸 얼마전에 알았다;;

 

그래서 CHOOSE 함수만 따로 정리해본다.

CHOOSE 함수는 숫자가 1~n일 경우 해당하는 값을 표시할 수 있는 함수다.

중첩 IF 함수를 사용해서도 같은 결과를 얻을 수 있다.

 

 

 

=CHOOSE(인덱스 번호, 값1, 값2, 값3, 값4......)

(인덱스 번호란? 1부터 시작하는 작은 정수를 의미한다. 음수나 0은 포함되지 않는다.)

인덱스 번호와 '값' 다음에 나오는 숫자가 일치하는 값이 표시된다. 즉, 인덱스 번호가 2라면 '값2'가 표시되고, 3이라면 '값3'이 표시된다.

 

 

 

CHOOSE 함수를 사용할 경우 유의할 점

 

1. IF 함수와 혼동하여 인덱스 번호가 들어갈 자리에 조건을 쓰면 안된다. (이런 경우를 너무 많이 봤음)

예) 응시번호(B4셀)가 '1'이면 'A'를 표시하고 '2'이면 'B'를 표시하시오.

틀린 경우

=CHOOSE(B4=1,"A",B4=2,"B")

=CHOOSE(B4,1,"A",2,"B")

맞는 경우

=CHOOSE(B4, "A", "B")

2. 인덱스 번호가 6까지 나올 가능성이 있다면 값도 6개를 입력해줘야한다. 예를 들어, 인덱스 번호가 4인데, 값3까지만 입력하면 '#VALUE!' 에러 표시가 나타난다.

틀린 경우

=CHOOSE(3, "A", "B")

맞는 경우

=CHOOSE(3, "A", "B", "C")

 

 

 

 

활용

 

 

문제) 교수의 평가점수가 1등인 사람에게는 보너스를 600,000원, 2등일 경우 400,000원, 3등일 경우 200,000원을 지급하고 나머지는 지급하지 않는다.

 

=CHOOSE(RANK(D6,$D$6:$D$11),600000,400000,200000,0,0,0)

RANK 함수로 순위를 구했다. 순위는 사람이 6명이므로 6까지 나올 가능성이 있다.

그럼 값도 '600000,400000,200000,0,0,0'의 6개를 입력해줘야 모든 결과가 에러 없이 제대로 표시될 수 있다.

 

 

 

 

 

 

추가)

 

CHOOSE 함수로 구할 값이라면 IF 함수로도 구할 수 있다.

위의 문제의 경우는 중첩 IF문을 좀 많이 사용해야 하긴 하지만 구해지긴 구해진다 ㅋ

 

=IF(RANK(D6,$D$6:$D$11)=1,600000,IF(RANK(D6,$D$6:$D$11)=2,400000,IF(RANK(D6,$D$6:$D$11)=3,200000,0)))

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 순위를 구할 수 있는 함수 RANK에 대하여 알아보자.

 

RANK(값, 참조 범위, [옵션])

참조 범위에서 값의 순위를 옵션에 따라 구한다.

옵션

0이나 생략 : 내림차순으로 순위를 구한다. 즉, 큰 수가 1위가 된다.

0이 아닌 임의의 값 : 오름차순으로 순위를 구한다. 즉, 작은 수가 1위가 된다.

 

 

RANK 함수 사용시 유의점

1. 수식을 복사할 경우가 많으므로 참조 범위가 고정되도록 반드시 [F4] 키를 눌러 절대 참조로 지정해야 한다.

2. 큰 수가 1위인 경우가 대부분이므로 옵션을 생략하는 경우가 대부분이다.

 

 

 

활용 예

1. 최근분기 순위 - 2분기 중에서 큰 수를 1위로 하여 순위를 구하시오.

2. 우량도 순위 - 반품 중에서 작은 수를 1위로 하여 순위를 구하시오.

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 가장 기본적인 함수에 대해 알아보자.

 

가장 먼저 엑셀에서 계산이 필요한 경우 SUM 같은 기본 함수를 사용하면서 엑셀 함수에 대해 알게 된다.

기본 함수에는 SUM, AVERAGE, MAX, MIN, MEDIAN, MODE, LARGE, SMALL 등의 함수가 있다.

 

엑셀 함수에 대해 초보여서 함수 입력부터 모른다면 다음 글을 참조하자.

함수 사용시 유의점과 팁 by Y

 

 

 기본 함수의 사용법과 설명

 

=> 범위는 숫자 범위를 말하고, 범위에 글자가 포함된 경우는 글자를 무시하고 계산된다.

=> [수식]-[함수 라이브러리]-[자동 합계] 명령을 이용하여 자동 합계 기능을 사용하면 SUM, AVERAGE, COUNT, MAX, MIN 함수를 쉽게 사용할 수 있다. 하지만, 범위에 따라 답이 틀리는 경우가 있기 때문에 나는 권장하지 않는다.

 

 

 

활용 예

 

 

 

 

 

 

함수 왕초보를 위한 합계(SUM) 구하기 실습

 

여기서부터는 함수를 전혀 사용할 줄 모르는 왕초보를 위한 합계 구하기 과정이다.

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 엑셀 2007부터 추가된 SUMIFS, AVERAGEIFS 함수에 대해 알아보자.

 

SUMIF 함수가 하나의 조건에 일치하는 합계를 구하는 함수라면 SUMIF 함수를 향상시킨 함수가 SUMIFS 함수다. SUMIFS 함수는 여러 조건을 만족하는 합계를 구하는 함수다.

마찬가지로 AVERAGEIF 함수를 향상시킨 함수가 AVERAGEIFS 함수다.

 

SUMIF(조건 범위, 조건, 합계 범위)

조건 범위에서 조건과 일치하는 합계 범위의 숫자를 합하는 함수이다.

 

SUMIFS(합계 범위, 조건 범위1, 조건1, 조건 범위 2, 조건2.....)

각 조건 범위에서 각 조건과 일치하는 합계 범위의 숫자를 합하는 함수이다.

 

AVERAGEIF(조건 범위, 조건, 평균 범위)

조건 범위에서 조건과 일치하는 평균 범위의 숫자를 평균내는 함수이다.

 

AVERAGEIFS(평균 범위, 조건 범위1, 조건1, 조건 범위 2, 조건2.....)

각 조건 범위에서 각 조건과 일치하는 평균 범위의 숫자를 평균내는 함수이다.

 

 

활용 예 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 개수를 구할 수 있는 함수를 알아보자.

 

개수 구하는 함수에는 COUNT, COUNTA, COUNTBLANK, COUNTIF 함수가 있다.

 

COUNT(범위)

범위에서 숫자 셀의 개수를 구하는 함수이다.

 

COUNTA(범위)

범위에서 빈셀을 제외한 모든 셀의 개수를 구하는 함수이다.

 

COUNTBLANK(범위)

범위에서 빈셀의 개수를 구하는 함수이다.

 

COUNTIF(조건범위, 조건)

조건 범위에서 조건에 일치하는 셀의 개수를 구하는 함수이다.

=COUNTIF(A1:A10, "승진") => A1:A10 범위에서 '승진' 문자가 입력된 셀의 개수를 구한다.

=COUNTIF(A1:A10, ">=80") => A1:A10 범위에서 80이상인 셀의 개수를 구한다.

=COUNTIF(A1:A10, "*주식회사") => A1:A10 범위에서 '주식회사'로 끝나는 문자가 입력된 셀의 개수를 구한다.

 

 

활용 예

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

배열 수식이란 표 형태의 데이터에서 함수로 풀 수 없는 복잡한 계산을 수행하거나 다른 데이터를 따로 입력하지 않고도 하나의 식으로 값을 구하고자 할 때 사용하는 수식이다.

 

 

배열 수식 작성시 유의점

1. 수식 입력을 종료하고 답을 보고자할 때 [Enter] 키가 아닌 [Ctrl]+[Shift]+[Enter] 키를 사용한다. [Enter] 키를 누르면 답이 제대로 나오지 않고, #VALUE? 오류가 표시된다.

2. 배열 수식을 사용하여 답을 구하고 수식을 살펴보면 자동으로 수식 앞 뒤에 중괄호({수식})가 붙어 표시된다.

3. 조건이 한개거나 여러개이거나 상관없이 괄호로 묶어주어 조건이라는 걸 쉽게 알아볼 수 있도록 작성하자.

4. 조건을 여러 개를 나열할 때 AND 조건은 *, OR 조건은 +를 사용하여 나열한다.

 

 

 

 

SUM, MAX, MIN, VAR, STDEV, MEDIAN, MODE 함수 등 같은 방식으로 배열 수식을 작성하므로, 여기에서는 SUM 함수의 예만 살펴본다. 단, COUNT 함수와 AVERAGE 함수는 2번 경우처럼 IF 함수와 같이 사용해야 한다.

 

 

1. SUM 함수만 사용하여 조건에 맞는 합 구하기

 

SUM((조건)*범위)-- 조건이 하나일 경우

SUM((조건1)*(조건2)*범위) -- 조건이 여러 개일 경우

범위에서 조건을 만족하는 값을 더하게 된다.

=SUM((C3:C8="법학과")*D3:D8) 입력 후 [Ctrl]+[Shift]+[Enter] 키    =>    44000

(C3:C8 범위에서 '법학과'인 금액의 합계를 구한다.)

 

 

2. SUM 함수와 IF 함수를 모두 사용하여 조건에 맞는 합 구하기

 

SUM(IF(조건, 범위)) -- 조건이 하나일 경우

SUM(IF((조건1)*(조건2), 범위)) -- 조건이 여러 개일 경우

범위에서 조건을 만족하는 값을 더하게 된다.

=SUM(IF(C3:C8="경영학과",D3:D8)) 입력 후 [Ctrl]+[Shift]+[Enter] 키    =>    27000

(C3:C8 범위에서 '경영학과'인 금액의 합계를 구한다.)

 

 

 

활용 예

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 데이터베이스 함수에 대해 알아보자.

 

데이터베이스 함수는 조건을 만족하는 자료에 대하여 더하기, 평균, 표준편차 등의 작업을 하는 함수를 의미한다. 데이터베이스 함수에는 DSUM, DAVERAGE, DMAX, DMIN, DCOUNT, DCOUNTA, DSTDEV, DVAR, DGET, DPRODUCT 함수 등이 있다.

 

 

 

 

데이터베이스 함수의 인수는 모두 같으므로 DSUM만 설명한다.

 

DSUM(데이터베이스 범위, 필드, 조건 범위)

데이터베이스 범위에서 조건과 일치하는 필드의 합계를 구하는 함수

필드에는 열번호, 필드 제목 중에 하나를 입력할 수 있다.

 

가전제품의 판매량을 합하시오.

1. 필드에 열번호를 입력할 경우    =>     =DSUM(B2:F10, 4, B2:B3)

2. 필드에 필드 제목을 입력할 경우      =>    =DSUM(B2:F10, E2, B2:B3)

 

 

 

활용예제

1. 제품분류가 사무용품인 제품의 매출액 합계를 구하시오.

2. 제품분류가 생활용품이고 판매량이 70 이상인 제품의 매출액 평균을 구하시오.

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 논리 함수에 대해 알아보자.

 

논리 함수에는 조건을 판단하는 IF 함수와 조건이 여러 개일 때 사용하는 AND, OR 함수가 있다.

 

 

 

 

IF(조건문, 값1, 값2)

조건문이 참이면 값1을 표시하고, 거짓이면 값2를 표시하는 함수

조건문에는 =(같다), >=(크거나 같다, 이상), <=(작거나 같다, 이하), >(크다, 초과), <(작다, 미만), <>(다르다) 등의 비교 연산자가 포함되어야 한다.

=IF(C3>=70,"우수","")     =>     우수

(중간 점수가 70 이상이면 '우수'를 표시하고, 아니면 공백을 표시한다.)

=IF(E3>=90,"A",IF(E3>=80,"B",IF(E3>=70,"C",IF(E3>=60,"D",IF(E3>=50,"E","F")))))     =>      C

(평균 점수가 90점 이상이면 A, 80점 이상이면 B, 70점 이상이면 C, 60점 이상이면 D, 50점 이상이면 E, 그 외에는 F를 표시한다.)

 

 

 

AND(조건1, 조건2, 조건3.....)

모든 조건이 참이면 TRUE, 하나라도 거짓이면 FALSE를 표시하는 함수

=AND(C3>=80, D3>=80)     =>     FALSE

 

 

 

OR(조건1, 조건2, 조건3.....)

모든 조건이 거짓이면 FALSE, 하나라도 참이면 TRUE를 표시하는 함수

=OR(C3>=80, D3>=80)     =>     TRUE

 

 

 

활용문제

평가1. 평균 점수가 70점 이상이면 합격, 아니면 불합격을 표시하시오.

평가2. 중간, 기말 점수가 모두 80점 이상이면 합격, 아니면 불합격을 표시하시오.

평가3. 중간, 기말 점수 중에서 하나라도 80점 이상이면 합격, 아니면 불합격을 표시하시오.

  

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 정보 함수에 대해 알아보자.

정보 함수는 셀에 어떤 값이 들어있는지 체크하기 위한 함수로 앞에 IS가 붙는다.

여기에서는 ISBLANK, ISERROR 함수를 알아보자.

 

 

 

 

 

ISBLANK(검사값)

검사값이 빈 셀인지 확인하여 빈 셀이면 TRUE, 빈셀이 아니면 FALSE를 반환하는 함수

=ISBLANK(C5)     =>     FALSE   (C5 셀이 빈셀이 아니므로 FALSE) 

=ISBLANK(C6)    =   TRUE    (C5 셀이 빈셀이므로 TRUE)

 

 

 

ISERROR(검사값)

검사값에 에러가 있는지 확인하여 에러면 TRUE, 에러가 아니면 FALSE를 반환하는 함수

=ISERROR(4/0)     =>     TRUE   (숫자를 0으로 나누면 '#DIV/0!' 에러가 난다. 에러이므로 TRUE)

 

 

 

 

 

 

 

 

 

활용 예제

1. 이벤트의 참석 여부에 따라 참석했으면 '증정', 아니면 '증정안함'이 표시되도록 하시오.

2. 시험 응시 여부에 따라 응시하지 않았으면 0을 표시하고, 응시했으면 중간 고사 점수에 10점 가산점을 더하시오.

 

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

사용자 정의 함수는 엑셀에서 제공하는 함수가 아닌 사용자가 입맛에 맞게 함수를 만드는 것을 말한다.

 

사용자 정의 함수는 Visual Basic 언어로 만들어지기 때문에 if문이나 swicth문, Select문을 알고 있으면 더 쉽게 만들 수 있다. 여기에서는 더하기 기능만 하는 기본적인 함수를 만들어 본다.

 

 

1. 중간과 기말 점수를 합하는 사용자 정의 함수를 만들어본다. [개발 도구] 탭을 클릭한다.

 

 

 

 

 

2. [코드] 그룹의 [Visual Basic] 명령을 클릭한다.

 

 

 

 

 

 

3. [Microsoft Visual Basic] 창이 나타나면 [삽입]-[모듈] 메뉴를 클릭한다.

 

 

 

 

 

 

4. 함수 내용을 입력한다. 'Public Function'은 함수를 정의하는 부분이므로 반드시 입력하고, fn_sum은 함수 이름이므로 영어와 한글을 조합하여 마음대로 작성한다. 괄호 안은 함수의 인수다. 다 작성한 후에 닫기 버튼을 클릭하면 자동으로 저장 후 엑셀로 돌아간다.

 

 

 

 

 

 

5. [함수 삽입] 버튼을 클릭한다.

 

 

 

 

 

 

6. [함수 마법사] 창이 나타나면 [범주 선택] 목록 버튼을 클릭한다.

 

 

 

 

 

 

7. [사용자 정의]를 클릭한다.

 

 

 

 

 

 

8. 함수를 선택한 후 [확인] 버튼을 클릭한다.

 

 

 

 

 

 

9. 인수를 입력하는 창이 나타나면 각각의 셀을 클릭하여 입력한 후 [확인] 버튼을 클릭한다.

 

 

 

 

 

 

10. 수식 결과 값이 나타나면 수식을 복사하여 완성한다.

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 찾기/참조 함수에 대해 알아보자.

 

찾기/참조 함수에는 값을 찾는 VLOOKUP, HLOOKUP, LOOKUP, INDEX 함수,

값을 선택하는 CHOOSE 함수, 값의 상대 위치를 구하는 MATCH 함수,

참조 영역을 구하는 OFFSET 함수 등이 있다.

 

 

VLOOKUP, HLOOKUP, LOOKUP 함수 먼저 알아보자.

 

 

LOOKUP 함수는 두 가지 방식이 있다.

 

1. LOOKUP(검색값, 검사범위, 대응범위)

검사 범위에서 검색값을 찾아 대응범위에서 같은 위치에 있는 값을 표시하는 함수

=LOOKUP(B4,B3:B6,C3:C6)    =>     70

2. LOOKUP(검색값, 배열)

배열에서 첫번째 열/행에서 검색값을 찾아 마지막 열/행의 같은 위치에 있는 값을 표시하는 함수

=LOOKUP(B4,B3:D6)    =>      C

 

 

VLOOKUP(검색값, 범위, 열번호, [검색옵션])

범위의 첫번째 열에서 검색값을 찾아, 범위에서 열번호에 해당하는 열에서 같은 행에 있는 값을 표시하는 함수

검색 옵션

TRUE나 생략할 경우 검색값과 정확히 일치하는 값이 없는 경우 근사값을 찾아 표시하고,

FALSE나 0일 경우 검색값과 정확하게 일치하는 값을 표시한다.

=VLOOKUP(B6,B3:D6,3,0)    =>    B

 

 

HLOOKUP(검색값, 범위, 행번호, [검색옵션])

범위의 첫번째 행에서 검색값을 찾아, 범위에서 행번호에 해당하는 행에서 같은 열에 있는 값을 표시하는 함수

=HLOOKUP(I3,G3:J5,2,0)     =>     95

 

 

 

 

 

INDEX, OFFSET, MATCH 함수에 대해 알아보자.

 

 

 

INDEX(범위, 행번호, [열번호])

표나 범위에서 해당 행번호, 해당 열번호만큼 떨어진 곳에 위치한 값을 구하는 함수

=INDEX(C5:G9,2,4)     =>     8900

(범위에서 2번째 행, 4번째 열에 위치한 값을 구한다)

 

 

OFFSET(기준, 행수, 열수, [높이], [폭])

기준으로부터 행 또는 열 수만큼 떨어진 곳에 있는 특정 높이와 너비의 참조 영역을 표시하는 함수

=OFFSET(D5,2,3)     =>     3800

(D5 셀로부터 2행과 3열 떨어진 곳에 위치한 값을 구한다)

 

 

MATCH(검사값, 검사범위, [검사옵션])

검사값을 검사범위에서 검색하여 그 위치를 구하는 함수

검사옵션은 1일 경우 검사값보다 작거나 같은 값 중에서 최대값을 찾고, 0일 경우 검사값과 같은 첫째 값을 찾고, -1일 경우 검사값보다 크거나 같은 값 중에서 최소값을 찾음

=MATCH(D4,C4:G4,0)      =>      2

(D4 값이 범위에서 몇번째에 위치하는 지 구함)

 

 

 

아래는 INDEX 함수와 MATCH 함수의 활용 예이다.

 

 

 

 

 

☞ VLOOKUP 함수 활용

 

2013/06/18 - 엑셀 주소라벨 만들기 (VLOOKUP 함수 실습) by Y

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 재무 함수에 대해 알아보자.

 

재무 함수에는 미래가치를 구하는 FV함수, 현재 가치를 구하는 PV 함수, 정기 납입 금액을 구하는 PMT 함수가 있다.

 

 

 

재무 함수에서 주의할 점

1. 지출하는 금액일 경우에는 돈이 나한테서 빠져나가므로 음수로 입력하고, 대출받았거나해서 나한테 들어온 돈일 경우에는 양수로 입력해야 한다.

2. 이율과 기간 모두 월 단위로 입력해야 하므로 연이율 경우에는 12로 나누고, 연단위 기간일 경우 12를 곱해야 한다.

 

 

 

 

 

FV(이율, 납입횟수, 정기납입액, [현재가치], [납입시점])

정기적으로 납입횟수만큼 은행에 정기납입액을 정해진 이율로 적립할 경우 미래가치를 구하는 함수

(적금을 부어 결과적으로 내가 탈 금액을 구하는 함수)

현재 가치는 미래 지급액에 상응하는 현재가치를 의미하고 생략하면 0에 해당한다. 생략 가능한 인수이다.

납입시점은 0이나 생략하면 월말에 납입하는 경우이고, 1이면 월초에 납입하는 경우이다. 생략 가능한 인수이다.

 

=FV(5%/12, 4*12, -200000, 0, 1)    =>   \10,647,156

(매월초 200,000원씩 적립한 금액을 연이율 5%로 계산하여 4년 뒤의 금액)

 

 

 

 

 

PMT(이율, 불입총횟수, 현재가치, [미래가치], [납입시점])

일정금액을 대출받았거나 투자했을 때 정기적으로 매월 또는 매년 납입하거나 수령할 금액을 구하는 함수

(대출을 받았을 때 정기적으로 납입할 금액을 구하는 함수)

 

=PMT(6%/12, 3*12, 0, 20000000, 0)    =>    -\508,439

(연이율 6%일 때 3년 동안 대출금 20,000,000원을 갚기 위해 매월말 입금해야 하는 금액)

 

 

 

 

 

PV(이율, 납입횟수, 정기납입액, [미래가치], [납입시점])

매월 일정한 금액을 일정 기간 동안 지불해주는 연금이나 보험의 지급 총액에 대한 현재 가치를 구하는 함수

(내가 일시불로 일정금액을 지불하여 연금을 가입한 후 매달 얼마씩 받는다 했을 때 그 연금에 가입한 것이 유리할지 연금의 현재가치를 알아보는 함수)

 

=PV(8%/12, 20*12, -300000)   =>   \35,866,288

(이율이 8%일 때 매월말에 300,000원씩 20년 동안 지급해 주는 연금의 현재 가치)

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 문자열 함수에 대해 알아보도록 하자.

 

문자열 처리를 위한 함수는 문자열 일부를 추출하는 LEFT, RIGHT, MID 함수,

영문 문자열 처리를 위한 LOWER, UPPER, PROPER 함수,

문자열의 형식을 바꾸는 VALUE, TEXT 함수,

그 외에 TRIM, LEN 함수 등이 있다.

 

LEFT(문자열, 문자수)

문자열의 왼쪽에서 문자수만큼 추출하는 함수

=LEFT("아름다운 우리나라", 2)    =>   아름

 

RIGHT(문자열, 문자수)

문자열의 오른쪽에서 문자수만큼 추출하는 함수

=RIGHT("아름다운 우리나라", 2)    =>    나라

 

MID(문자열, 시작 위치, 문자수)

문자열의 왼쪽에서 시작 위치만큼 떨어진 문자에서부터 문자수만큼 추출하는 함수

=MID("아름다운 우리나라", 3, 5)    =>    다운 우리

 

LOWER(문자열)

영문 텍스트를 소문자로 변환시키는 함수

=LOWER("ONE APPLE")    =>    one apple

 

UPPER(문자열)

영문 텍스트를 대문자로 변환시키는 함수

=UPPER("one apple")    =>    ONE APPLE

 

PROPER(문자열)

영문 텍스트에서 단어의 첫문자만 대문자로 변환하고 나머지는 소문자로 변환하는 함수

=PROPER("ONE APPLE")     =>     One Apple

 

VALUE(텍스트)

문자형 숫자를 숫자형 숫자로 변환하는 함수

=VALUE("2")     =>     2

 

TEXT(숫자 값, 표시 형식)

숫자 값에 표시 형식을 지정하여 텍스트로 변환하는 함수

표시 형식은 [셀 서식]-[표시 형식]-[사용자 지정]에서 사용하는 표시 형식과 같은 형식을 사용한다.

=TEXT(41261, "mm월 dd일 (aaaa)")     =>     12월 18일 (화요일)

 

TRIM(문자열)

문자열에서 여분의 공백을 제거하는 함수

=TRIM("  아름다운   세상 ")     =>     아름다운 세상

 

LEN(문자열)

문자열의 길이를 구하는 함수

=LEN("아름다운 우리나라!!")     =>     11

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

 

 

 

엑셀 함수 중에서 날짜 함수에 대해 알아보도록 하자.

 

날짜 함수는 함수의 인수가 없는 TODAY, NOW 함수,

날짜를 인수로 가지는 YEAR, MONTH, DAY, WEEKDAY 함수,

날짜의 경과 기간을 구하는 함수인 DAYS360 함수,

날짜 데이터를 만드는 DATE 함수가 있다.

 

TODAY()

시스템(컴퓨터)에 지정된 오늘 날짜를 결과로 보여주는 함수

=TODAY()   =>    2012-12-17

 

NOW()

시스템(컴퓨터)에 지정된 오늘 날짜와 시간을 보여주는 함수

=NOW()      => 2012-12-17 11:40

 

YEAR(날짜)

날짜의 년도를 추출하여 보여주는 함수

=YEAR("2012-12-17")     =>  2012

 

MONTH(날짜)

날짜의 년도를 추출하여 보여주는 함수

=MONTH("2012-12-17")   => 12

 

DAY(날짜)

날짜의 년도를 추출하여 보여주는 함수

=DAY("2012-12-17")   => 17

 

WEEKDAY(날짜, [옵션])

날짜의 요일을 숫자로 구해주는 함수

옵션은 생략가능하며, 생략하거나 1을 쓰면 일요일을 1로 시작

2를 쓰면 월요일을 1로 시작, 3을 쓰면 월요일은 0으로 시작

=WEEKDAY("2012-12-17",2)   =>   1(월요일을 의미함)

 

DAYS360(시작일, 종료일, 옵션)

시작일과 종료일 사이의 일수를 구해주는 함수

옵션은 종료일이 31일일 경우 TRUE이면 30일로 변경하여 계산하고, 생략하거나 FALSE는 31일로 계산한다.

=DAYS360("2012-1-1","2012-1-31",TRUE)   =>  29

 

DATE(연, 월, 일)

지정한 연, 월, 일로 날짜 데이터를 만드는 함수

=DATE(2012, 1, 2)   =>   2012-1-2

 

 

 

 

 

 

 

 

 

 

 

Posted by Y&S