이틀째 엑셀 함수 공부

[송하(이성하)]

** 오늘의 엑셀 함수 공부 ** 

 

TODAY	=TODAY()	=TODAY()	시스템 현재의 날짜를 표시
SUMIF	=SUMIF(조건을 비교할 범위, 조건, 합계를 구할 범위)	=SUMIF(A2:A6,"김%“,C2:C6)	김으로 시작하는 조건에 해당하는 범위(A2:A6) 중에서 합계를 구할 범위(C2:C6)을 합한다.
MID	=MID(문자열, 부터, 까지)	=MID("KOREA",3,2) => RE	KOREA 문자열에서 왼쪽 3번째부터 2개의 문자 출력

 **  IF 함수      조건에 해당하여 참과 거짓을 판별하는 조건문 함수

    =IF(조건, 참 문장, 거짓 문장) => 조건이 참이면 참 문장 실행, 거짓이면 

                                                거짓 문장 실행

    =IF(평균>=70, “합격”, “불합격”) => 평균이 70점 이상이면 합격, 
                                                    그렇지 않으면 불합격을 출력한다.

* IF 함수

기능: 조건을 논리적으로 검사하여 참일때와 거짓일때에 따라 각기 다른 값을 반환하는 함수입니다.

용법: =IF(조건식, 참일때 값, 거짓일때 값)

-왼쪽항을 기준으로 봤을때 관계연산자들입니다.

• A > B  : A가 B 보다 크면(초과) (B 값이 포함되지 않음)
• A < B  : A가 B 보다 작으면(미만) (B 값이 포함되지 않음)
• A >= B : A가 B 보다 크거나 같으면(이상) (B값 포함)
• A <= B : A가 B 보다 작거나 같으면(이하) (B값 포함)
• A = B  : A가 B 와 같음
• A <> B : A와 B 가 서로 다름

용례1: =IF(C4>=60, "합격", "탈락")
용례1의풀이: 조건(C4 셀의 값이 60보다 크거나 같으면) 이 참(60이상)이면 참일때 값("합격")을 거짓(60미만)이면 거짓일때 값("탈락")을 결과로 반환합니다.)

용례2: =IF(C4>40, "통과", "")
용례2의풀이: 조건(C4 셀의 값이 40보다 크거나 같으면) 이 참이면(40초과) "통과"를 거짓이면(40이하) 거짓일때 값으로 공백을 반환합니다.)

용례3: =IF(C4>40, "통과")
용례3의풀이: 용례2의 풀이가 기본이고 이번예에서는 조건이 거짓일때 반환할 인수가 설정되어 있지않습니다. 이런경우 만일 조건이 거짓이라면 FALSE를 반환합니다. FALSE  는 논리값중 거짓을 의미하는 값입니다. 서식에 따라 0이 반환될수도 있습니다. 곧 FALSE 값을 수치값으로 변환 하면 0 이 된다는 것이지요. 당연히 TRUE 논리값(참)은 수치값으로 1이 되구요.

용례3: =IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))
용례3의풀이: 조건은 A2셀의 값이 89보다 크면(초과) 참인값 'A' 를 반환 하고 그렇지 않으면 붉은색의 IF문 전체를 거짓값으로 반환합니다. 바로 함수의 중첩이지요. 함수의 중첩시 가장자주 사용되는 함수가 바로 이 IF 함수 입니다. 이번 예의 경우 89보다 크면 'A' 그렇지 않으면 다시 조건비교하고 62보다크면 'C', 거짓이면 다시 조건비교를 거짓일때 반환합니다.
이런식으로 함수가 중첩되지요. 중첩에서 중요한 것은 함수의 인수로 사용되는 값이 단일값이 아니라 수식값일수 있다는 것입니다.

기출예제1: 점수가 90 이상이면 '우수', 90 미만이면 80 이상이면 '보통', 80미만이면 '조심' 이라고 등급[D3:D7] 영역에 표시하시오.(3급10회C형)

기출풀이1: D3 셀에 셀포인트를 놓고 다음의 수식을 입력합니다.

=IF(C3>=90,"우수",IF(C3>=80,"보통","분발"))

함수의 중첩이 보이지요.

C3셀의 값의 90 이상인지 비교하여 참이면 '우수' 값을 반환하지요. 거짓일때 값을 보면 다시 함수 중첩입니다. 수식은 용례3의 경우와 동일합니다.

여러분이 힘들어 하는 것은 수식적인 풀이가 아니라 의미적해석일 것입니다.

90이상일때는 우수를 반환하고 함수를 벗어납니다.
그렇지않은 경우,거짓인 경우로 넘어가는 값은 89점이하의 점수 겠지요. 여기서 다시 80 이상은 '보통'을 반환하고 함수를 벗어납니다.

남은 값은 모두 거짓일때의 값 '분발' 이 되겠지요.

기출예제2: 판매일[B3:B10]을 이용하여 요일번호[D3:D10]을 유형 '1'로 구하고 이때 요일번호가 '1' 이면 '일요일', 그 외는 공란으로 표시하시오.

기출풀이2:

1. D3셀에 셀포인터를 놓습니다. 다음의 수식을 입력합니다.
2. =IF(WEEKDAY(B3,1)=1,"일요일","")
3. 점끌기탭(채우기핸들)으로 D10셀까지 끌어 수식을 복사 합니다.

 요일을 수로 반환 받기 위해 WEEKDAY함수를 사용합니다. 1 번 유형을 사용하라고 친절히 설명까지 해줬네요. 1번 유형은 1을 일요일로보는 유형이지요.
또 그 값이 1인지 그렇지 않은지를 검사하기 위해 IF로 조건비교합니다.  IF함수에서 조건비교 인수로 WEEKDAY함수의 결과를 사용합니다. 

***☞  아래는 주요 함수에 대하여 ,  참고로 안내하였습니다.

(1) 날짜와 시간 함수

함수	형식	사용 예	기능
DATE	=DATE(연도, 월, 일)	=DATE(2002,09,15)		날짜 값으로 바꿔준다.
YEAR	=YEAR(날짜)	=YEAR(2002-09-15)		2002년만 출력된다.
MONTH	=MONTH(날짜)	=MONTH(2002-09-15)		9월만 출력된다.
DAY	=DAY(날짜)	=DAY(2002-09-15)		15일만 출력된다.
NOW	=NOW()	=NOW()		오늘 날짜와 현재 시간을 자동으로 표시
TODAY	=TODAY()	=TODAY()		시스템 현재의 날짜를 표시
WEEKDAY	=WEEKDAY(날짜)	=WEEKDAY(2002-09-15)		요일을 숫자 1~7로 나타낸다.
DAY360	=DAY360(시작날짜, 끝 날짜)	=DAY360(2002-05-31,          2002-09-15)		시작 날짜와 끝 날짜의 경과한 날수를 구함
TIME	=TIME(시간)	=TIME(12,59,29)		12:59 PM으로 시간을 구함
HOUR	=HOUR(시간)	=HOUR(13:30)		시간에서 시만 구함
MINUTE	=MINUTE(시간)	=MINUTE(13:30)		시간에서 분만 구함
SECOND	=SECOND(시간)	=SECOND(13:30:12)		시간에서 초만 구함

(2) 수학/삼각함수

함수		형식	사용 예	기능
SUM	=SUM(숫자나 셀 주소)		=SUM(A1:A2)	A1셀과 A2셀의 합을 구함
ABS	=ABS(숫자나 셀 주소)		=ABS(-5)	-5의 절대값은 5가 출력
INT	=INT(숫자나 셀 주소)		=INT(5.43)	정수 값만을 출력하므로 5가 출력
ROUND	=ROUND(반올림할 인수,      반올림할 자릿수)		=ROUND(12.5, -1)	자릿수가 -1이므로 왼쪽으로 한 칸 이동하면 2를 반올림시킨다. 10이 출력
ROUNDDOWN	=ROUNDDOWN(반올림할 인수,반올림할 자릿수)		=ROUNDDOWN(12.5, -1)	자릿수가 -1이므로 무조건 버림으로 2를 버리면 10이 출력
ROUNDUP	=ROUNDUP(반올림 할 인수, 반올림할 자릿수)		=ROUNDUP(12.5,-1)	자릿수가 -1이므로 무조건 올림으로 2를 올리면 20이 출력
SUMIF	=SUMIF(조건을 비교할 범위, 조건, 합계를 구할 범위)		=SUMIF(A2:A6,"김%“,C2:C6)	김으로 시작하는 조건에 해당하는 범위(A2:A6) 중에서 합계를 구할 범위(C2:C6)을 합한다.
MOD	=MOD(숫자1, 숫자2)		=MOD(12,5)	12를 5로 나누면 나머지 2가 출력
POWER	=POWER(숫자1, 숫자2)		=POWER(3,2)	3의 2를 거듭제곱하면 9가 출력
SQRT	=SQRT(숫자)		=SQRT(16)	16은 4의 제곱근이므로 4가 출력
TRUNC	=TRUNC(숫자)		=TRUNC(12.7)	소수이하를 버리고 정수로 변환

(3) 통계함수

함수	형식	사용 예	기능
AVERAGE	=AVERAGE(숫자나 셀 주소)	=AVERAGE(A1:A5)	A1:A5셀 안에 있는 셀 값의 평균을 구함
COUNT	=COUNT(숫자나 셀 주소)	=COUNT(A1:A5)	A1:A5셀 안에 있는 셀 값에 숫자의 개수를 구함
COUNTA	=COUNTA(숫자나 셀 주소)	=COUNTA(A1:A5)	A1:A5셀 안에 있는 셀 값에 숫자와 문자의 개수를 구함
COUNTIF	=COUNTIF(셀 범위, 조건)	=COUNTIF(A1:A5,"축구")	A1:A5셀 안에 있는 셀 값이 축구인 것에 개수를 구함
MAX	=MAX(숫자나 셀 범위)	=MAX(A1:A5)	A1:A5셀 안에 있는 셀의 최대값을 구함
MIN	=MIN(숫자나 셀 범위)	=MIN(A1:A5)	A1:A5셀 안에 있는 셀의 최소값을 구함
RANK	=RANK(기준셀, 범위, SORT)	=RANK(A1,$A$1:$A$5,0)	A1셀을 기준으로  A1:A5셀 범위의 순위를 내림차순으로 구함

(4) 찾기/참조영역 함수

함수	형식	사용 예	기능
CHOOSE	=CHOOSE (인수번호,인수 범위)	=CHOOSE(2,"A","B","C")	2번째의 인수를 선택하면 B가 출력
HLOOKUP	=HLOOKUP (찾을값,찾을 셀범위, 행번호)	=HLOOKUP(A1,$B$6:$E$9,2)	A1을 B6:E9범위에서 찾아 2행을 출력함
VLOOKUP	=VLOOKUP (찾을값,찾을 셀범위, 열번호)	=HLOOKUP(A1,$B$6:$E$9,2)	A1을 B6:E9범위에서 찾아 2열을 출력함
INDEX	=INDEX (셀범위, 행번호, 열번호)	=INDEX(A1:E5,3,2)	A1:E5셀에서 3행 2열의 셀값을 출력

(5) 데이터베이스 함수

함수	형식	사용 예	기능
DSUM	=DSUM(범위, 열번호, 조건범위)	=DSUM(A1:E6,4,A8:A9)	A1:E6셀에서 A8:A9셀 조건 에 해당하는 4행의 합을 구함
DAVERAGE	=DAVERAGE(범위, 열번호, 조건범위)	=DAVERAGE(A1:E6,4,A8:A9)	A1:E6셀에서 A8:A9셀 조건에 해당하는 4행의 평균을 구함
DCOUNT	=DCOUNT(범위, 열번호, 조건범위)	=DCOUNT(A1:E6,4,A8:A9)	A1:E6셀에서 A8:A9셀 조건 에 해당하는 4행의 개수 를 구함(숫자)
DCOUNTA	=DCOUNTA(범위, 열번호, 조건범위)	=DCOUNTA(A1:E6,4,A8:A9)	A1:E6셀에서 A8:A9셀 조건 에 해당하는 4행의 개수를 구함(문자)
DMAX	=DMAX(범위, 열번호, 조건범위)	=DMAX(A1:E6,4,A8:A9)	A1:E6셀에서 A8:A9셀 조건 에 해당하는 4행의 최대값 을 구함
DMIN	=DMIN(범위, 열번호, 조건범위)	=DMIN(A1:E6,4,A8:A9)	A1:E6셀에서 A8:A9셀 조건 에 해당하는 4행의 최소값 을 구함
DSTDEV	=DSTDEV(범위, 열번호, 조건범위)	=DSTDEV(A1:E6,4,A8:A9)	표준 편차를 구함

(6) 문자열 함수

함수	형식	사용 예	기능
LEFT	=LEFT(문자열,자릿수)	=LEFT("KOREA",2) => KO	KOREA 문자열에서 왼쪽으로 2개의 문자 출력
RIGHT	=RIGHT(문자열, 자릿수)	=RIGHT("KOREA",2) => EA	KOREA 문자열에서 오른쪽으로 2개의 문자 출력
MID	=MID(문자열, 부터, 까지)	=MID("KOREA",3,2) => RE	KOREA 문자열에서 왼쪽 3번째부터 2개의 문자 출력
UPPER	=UPPER(문자열)	=UPPER("Korea") => KOREA	모두 대문자로 출력
LOWER	=LOWER(문자열)	=LOWER("KOREA") => korea	모두 소문자로 출력
PROPER	=PROPER(문자열)	=PROPER("KOREA") => Korea	첫 글자만 대문자로 출력
TRIM	=TRIM(문자열)	=TRIM(" SSUN T") => SSUN T	양쪽 공백을 제거
LEN	=LEN(문자열)	=LEN("SSUN T") => 6	문자열의 길이 출력

(7)논리 함수

   입력된 논리식들이 참이면 TRUE, 거짓이면 FALSE를 출력하는 함수이다.

 1) AND 함수   = AND (논리식1, 논리식2....)  입력되는 논리식들이 모두 참이어야

TRUE 출력, 하나라도 거짓이 있으면 FALSE 출력 
 

 2) OR 함수  = OR (논리식1, 논리식2.....)  입력되는 논리식들 중 하나라도 참이


있으면 TRUE 출력  

 3) IF 함수      조건에 해당하여 참과 거짓을 판별하는 조건문 함수

    =IF(조건, 참 문장, 거짓 문장) => 조건이 참이면 참 문장 실행, 거짓이면


                                                거짓 문장 실행

    =IF(평균>=70, “합격”, “불합격”) => 평균이 70점 이상이면 합격,


                                                    그렇지 않으면 불합격을 출력한다.

(8) 재무 함수

 1) 재무함수의 공통 인수

    rate => 이자 지급 기간당 이율을 의미

    nper => 납입 총 횟수를 의미

    pmt => 각 기간의 납입액으로 전체 기간 동안 일정하며 기타 비용이나 세금은 포함되지 않고 원금과 이자만 포함된다.

    pv => 현재 가치를 의미

    type => 지급 기일이 기말인지 기초인지를 구분

    fv => 미래 가치 또는 최종 상환 후의 현금 잔고를 의미

 2) FV 함수  = FV(rate, nper, pmt, pv, type)

    일정 금액을 정기적으로 불입하고 일정한 이율이 적용되며 매월 복리로 이자가 계산되는

것으로 가정한 투자의 미래 가치를 계산해 주는 함수  

 3) PV 함수   = PV(rate, nper, pmt, fv, type)

    매월이나 매년 일정한 금액을 일정 기간 동안 지불해 주는 연금이나 보험의 지급총액에

대한 현재 가치를 구해주는 함수

 4) NPV 함수  =NPV(rate, value1, value2... value29)

    특정한 금액을 투자하고 매월 일정한 수입이 보장될 때 해당 투자의 현재 가치를

구해주는 함수

(9) 정보 함수

 1) ISBLANK 함수 =ISBLANK(셀 주소)

    인수로 지정한 셀이 비어 있으면 TRUE를 구해주고, 비어 있지 않으면 FALSE를

구해준다. 

 2) ISERROR 함수 = ISERROR(셀 주소)

    인수로 입력한 셀에 오류가 발생하였으면 TRUE를 구해주고, 오류가 발생하지 않으면

FALSE를 구해준다.

 이성하 횡설수설 :   내일은 , 시간을 내어서 , 7월의 눈부신 제일대학교 교정을 사진에 담아서 소개해 볼 까 합니다 .  여러분 , 방학 즐겁게 보람되게 보내세요 . . .  내일 뵙겠습니다.



김세환 노래모음

 
01. 옛친구
02. 길가에 앉아서
03. 목장길 따라
04. 토요일 밤에
05. 좋은걸 어떻해 
 
06. 부모
07. 메아리
08. 이거야 정말
09. 무뚝뚝한 사나이
10. 화가 났을까
 
11. 사랑하는 마음
12. 그럴수가 있을까
13. 두 손을 마주 잡고
14. 모래위를 맨발로
15. 오! 내사랑 
 
 

[송하(이성하)]

내일은 제가 중국에서 가져 온 고급 술을 , 작은 잔에 한 잔 씩 , 맛보기로 드실 수 있도록 , 가져 갈 까 합니다. 내일 8시 50분까정 연락 주십시요. 모든 것은 꽈대가 책임? 집니다. 011-644-2083 입니다. 수업도 제가 자칭 부회장이니 , 지도를 할 수 있는 부분도 있습니다. 믿으십시요 , 그러면 구원을 받을 것이니 . . . 사도행전 16장31절