[컴퓨터활용능력1급]수업 정리 3

<수학삼각함수>

SUM(인수) : 합계

sum(A1:B4 A2:C6) : 두영역의 교집합 부분을 더하기
sum(A1:B4,C2:D6) : 두영역을 더하기


ROUND(숫자, 표시되는 자릿수)  : 반올림
ROUNDUP(숫자, 표시되는 자릿수)  : 올림
ROUNDDOWN(숫자, 표시되는 자릿수)  : 내림, 버림, 절사(삭)

자릿수  : 일의 자리가 0, 소숫점 이하는 양수 자릿수, 
    십의 자리 이상은 음수 자릿수

표시되는 자릿수=결과


Abs(인수) : 절대값
 

Quotient(숫자, 나눌 수) : 나누기해서 몫(정수) 추출

Mod(숫자, 나눌 수) : 나누기해서 나머지 추출
 - 정수몫을 제외한 나머지


TRUNC(숫자, [표시되는 자릿수])  : 내림, 절사(삭), 
자릿수가 0인 경우 생략 가능

INT(숫자)  : 정수

근사치 : 작거나 같은 값들 중에서 가장 큰 값
예) 성적처리, 백화점 상품권 지급 행사(오름차순)


SQRT(숫자) : 제곱근, 루트

FACT(숫자)  : 계승, 5!=1*2*3*4*5

POWER(숫자, 거듭제곱승) : POWER(4, 2)=4^2=16


PRODUCT(인수...) : 곱하기, *

SUMPRODUCT(범위1, 범위2, .....) : 각각 곱한 다음에 더하기
범위1, 2 갯수가 동일, 방향 동일

RAND() : 0~1 난수
RANDBETWEEN(최소치, 최대치)  : 최소치~최대치 난수

PI() : 원주율, 3.14159265358979...

MDETERM(배열) : 행렬식
MINVERSE(배열) : 역행렬
MMULT(배열1, 배열2) : 행렬 곱


<단수 형태> : 조건이 하나

sumif(조건을 조사할 범위, 조건, [계산할 범위/더할 범위]) 
: 조건에 맞는 것만 더하기 
여학생들의 성적을 더하기
=sumif(성별 범위, "여", 성적 범위)

      - 조건은 값의 셀주소/값의 경우 직접 입력시
        무조건 ""(큰따옴표 안에)  ">=80"
      - 계산할 범위/더할 범위가 생략되는 경우는 
        조건을 조사할 범위랑 동일한 경우는 생략 가능

countif(조건을 조사할 범위, 조건)
: 조건에 맞는 것만 갯수, 인원수
여학생 인원수
=countif(성별 범위, "여")

averageif(조건을 조사할 범위, 조건, [계산할 범위/평균 낼 범위])
: 조건에 맞는 것만 평균 구하기
여학생들의 성적을 평균
=averageif(성별 범위, "여", 성적 범위)

        - 계산할 범위/평균낼 범위가 생략되는 경우는 
           조건을 조사할 범위랑 동일한 경우는 생략 가능
        =sumif/countif


**추가사항
1. 67page 표2번 활용- 판매량이 평균이상인 판매총액의 합계
&, average, sumif

=SUMIF(J3:J11,">="&AVERAGE(J3:J11),K3:K11)

2. 판매총액이 평균이상인 판매총액의 합계
&, average, sumif

=SUMIF(K3:K11,">="&AVERAGE(K3:K11))


<복수 형태>
: 조건 1 ~ 127개까지 가능, <단,  조건들간의 관계가 and만 가능>
  
sumifs(계산할 범위/더할 범위, 조건1을 조사할 범위, 조건1, 조건2을 조사할 범위, 조건2, ....)
: 모든 조건에 만족하는 부분만 더하기

:3학년 여학생의 성적 더하기 
=sumifs(성적 범위, 학년 범위, "3", 성별 범위, "여")


countifs(조건1을 조사할 범위, 조건1, 조건2을 조사할 범위, 조건2, ....)
: 모든 조건에 만족하는 부분만 개수/인원수
:3학년 여학생 인원수 
=countifs(학년 범위, "3", 성별 범위, "여")


averageifs(계산할 범위/평균 낼 범위, 조건1을 조사할 범위, 조건1, 조건2을 조사할 범위, 조건2, ....)
: 모든 조건에 만족하는 부분만 평균 구하기
:3학년 여학생의 성적의 평균
=averageifs(성적 범위, 학년 범위, "3", 성별 범위, "여")




<통계함수>

average(인수) : 숫자만 평균

끝에 a를 포함하는 함수 : counta, dcounta, averagea, maxa, mina
     텍스트를 포함해서 계산, 텍스트나 false는 0, true는 1 취급


max(범위) : 최대값
min(범위) : 최소값

large(범위, 순위) : 범위에서 큰 값 중에 순위에 해당하는 값
small(범위, 순위) : 범위에서 작은 값 중에 순위에 해당하는 값


rank(내점수/값, 전체점수범위/비교대상, [정렬방법]) : 순위 

오름차순 : 1 - 육상, 수영 기록 경기(작은 값이 1등)
내림차순 : 0이나 생략 - 일반 성적 등수(큰 값이 1등)

      전체점수범위는 비교대상이고, 대부분 절대참조

rank.eq : 동일 점수는 같은 등수, 
90점에 해당하는 3등이 2명이면 두명 다 3등 처리, 
              4등은 없는 것으로 처리

rank.avg : 동일 점수는 등수의 평균을 내서 등수를 부여
3등이 2명이면 3, 4등의 평균인 3.5등으로 처리
3등이 3명이면 3, 4, 5등의 평균 4등으로 처리


var(범위) : 분산
var.s(범위): 표본집단의 분산
var.p(범위): 모집단의 분산

stdev(범위) : 표준편차
stdev.s(범위) : 표본집단의 표준편차
stdev.p(범위) : 모집단의 표준편차

median(인수) : 범위나 수들 중에 중간값(나와있는 수들 중에)
mode(인수) : 범위나 수들 중에 빈도 수가 가장 많은 값


<출제유형>
**추가 : 지급액이 평균이상인 건수?
countif, average, &

=COUNTIF(H4:H20, ">=" & AVERAGE(H4:H20))
:조건에 해당하는 AVERAGE(H4:H20)은 ""안에 들어가면 텍스트 취급되므로 밖에 연결 연산자로 연결


COUNT(인수) : 숫자로 된 셀의 개수
COUNTA(인수) : 빈 셀이 아닌 곳의 개수(숫자, 텍스트 포함)
COUNTBLANK(인수) : 공백의 개수

공백 체크 사항
=COUNTIF(범위, "") : 스페이스(공백문자)를 문자로 인식하므로 countblank와 동일
=COUNTIF(범위, "<>납부")

70점대 인원수
=COUNTIFS(범위, ">=70", 범위, "<80")
=COUNTIF(범위, ">=70")-COUNTIF(범위, ">=80")


FREQUENCY(데이터 구간, 분포 구간) : 도수분포
- 배열함수로 값(답)이 나올 셀들(범위)을 블록 설정하고, 
수식을 작성한 후 ctrl+shift+enter로 마무리(복사 불가능)

{=FREQUENCY(데이터 구간, 분포 구간)}

{=FREQUENCY(H3:H11, L3:L7) & "명"}


PERCENTILE(범위, 백분율) : 백분위수

countif(조건을 조사할 범위, 조건)
: 조건에 맞는 것만 갯수, 인원수
여학생 인원수
=countif(성별 범위, "여")

averageif(조건을 조사할 범위, 조건, [계산할 범위/평균 낼 범위])
: 조건에 맞는 것만 평균 구하기
여학생들의 성적을 평균
=averageif(성별 범위, "여", 성적 범위)

        - 계산할 범위/평균낼 범위가 생략되는 경우는 
           조건을 조사할 범위랑 동일한 경우는 생략 가능
        =sumif/countif


countifs(조건1을 조사할 범위, 조건1, 조건2을 조사할 범위, 조건2, ....)
: 모든 조건에 만족하는 부분만 개수/인원수
:3학년 여학생 인원수 
=countifs(학년 범위, "3", 성별 범위, "여")



averageifs(계산할 범위/평균 낼 범위, 조건1을 조사할 범위, 조건1, 조건2을 조사할 범위, 조건2, ....)
: 모든 조건에 만족하는 부분만 평균 구하기
:3학년 여학생의 성적의 평균
=averageifs(성적 범위, 학년 범위, "3", 성별 범위, "여")




<논리 함수>

if(명제, 명제가 참일 때 값, 명제가 거짓일 때 값)
: 명제(조건식)을 판단해서 참이면 참일때 값, 거짓이면 거짓일 때 값을 돌려줌(둘 중에 하나만)

명제 : TRUE나 FALSE로 판단될 수 있는 수나 식,
         비교연산자(>,<,>=...), AND/OR 함수, 정보함수(iserror, isblank...)

=if(평균>=60, "합격", "불합격")




<날짜 시간 함수>

year(인수), month(인수), day(인수) 
  인수 - 날짜 셀, 날짜 함수, 날짜 텍스트(큰 따옴표)
  날짜를 큰 따옴표 안에 입력해야 빼기/나누기가 되지 않고, 
  날짜 문자열(날짜 텍스트)로 인식


hour(인수), minute(인수), second(인수) 
  인수 - 시간 셀, 시간 함수, 시간 텍스트(큰 따옴표)


ctrl + ;  오늘 날짜(업데이트 불가능)
ctrl + :(shift + ;)  현재 시간(업데이트 불가능)

=today() 오늘 날짜(업데이트 가능)
=now() 오늘 날짜와 현재 시간(업데이트 가능)


date : 날짜 형태로 표현 date(년, 월, 일)

time : 시간 형태로 표현 time(시, 분, 초)



출발시간 정거장수 "정거장당
소요분" 도착예정시간
8:40 12 3 9:16 
9:15 25 2 10:05

=TIME(HOUR(출발시간), MINUTE(출발시간)+정거장수*정거장당소요분, 0)



10분당 500원
입차시간 출차시간 주차시간 주차요금
8:50 10:15 1:25 4000 
9:12 9:53 0:41 2000 
hour, minute, int

주차요금=HOUR(주차시간)*6*500+INT(MINUTE(주차시간)/10)*500


weekday(날짜, [return-type]) : 요일 번호  
[return-type] 1 이나 생략 (일요일 1번 ~ 토요일 7번), 
2 (월요일 1번 ~ 일요일 7번), 
3 (월요일 0번 ~ 일요일 6번)
11~17
=>셀서식에서 요일 코드 aaa, aaaa, ddd, dddd를 적용할 경우에는 1번 타입만 가능


days(종료일, 시작일) : 시작일과 종료일의 경과일수

경과일수  =종료일-시작일 (+1)


datevalue(날짜 텍스트) : 날짜의 일련번호
   날짜를 큰 따옴표 안에 입력하면 날짜 문자열(날짜 텍스트)로 인식


edate(날짜, 개월 수)  : 특정 날짜의 몇 개월 전/후의 날짜 

eomonth(날짜, 개월 수) : 특정 날짜의 몇 개월 전/후의 날짜의 말일


networkdays(시작일, 종료일, [휴일]) : 시작일과 종료일의 경과일수(토일제외)
실제 업무일수


datedif(시작일, 종료일, "단위") : 시작일과 종료일의 경과일수

d 총일수
m 총개월수
y 년수
ym 년도를 제외한 나머지 개월수
md 개월을 제외한 나머지 일수


3년 8개월 근무
=DATEDIF(입사일, TODAY(), "y") & "년 " & DATEDIF(입사일, TODAY(), "ym") & "개월 근무"



weeknum(날짜, 요일을 결정할 숫자) : 특정 날짜의 일년 중 몇번째 주


workday(날짜, 평일/경과일수, [휴일]) : 특정 날짜에 경과일 더해서 예정일 추출(주5일)


edate(날짜, 경과달수)
 
사용기간
=TEXT(EDATE(검침일, -1)+1, "yy/mm/dd") & "~" & TEXT(검침일, "yy/mm/dd")




<텍스트 함수>

Len(글자)  : 글자수, 문자길이


LEFT(글자, [추출할 글자수])  : 앞(왼쪽)에서 몇 글자 추출, 
글자 수 생략 시에는 무조건 1글자 추출

RIGHT(글자, [추출할 글자수]) : 뒤(오른쪽)에서 몇 글자 추출
글자 수 생략 시에는 무조건 1글자 추출

MID(글자, 시작위치번호, 추출할 글자수) 
: 사이에 있는 몇 글자 추출


lower(글자) : 소문자 처리
upper(글자) : 대문자 처리
proper(글자) : 단어의 첫글자만 대문자 처리


trim(글자) : 단어 사이의 한칸의 공백만 남기고, 나머지 공백 삭제.


REPLACE(글자, 치환할 부분의 시작위치번호, 바꿀 글자수, 새글자)
: 치환 - 자릿수만 고려(철자무관)

SUBSTITUTE(글자, 기존 바꿀 글자, 새 글자)
: 치환 - 철자를 고려(자릿수 무관)


value(인수)  : 숫자처리, 숫자로 바꿀수 없으면 #value!(오류)


text(값, "형식") : 일정한 형식의 글자로 만들어 주는 함수

 = text(today(), "mm-dd") => 04-01

 = text(12500, "\#,##0") => \12,500


concatenate(인수1, 인수2.....): ( )안에 인수들을 연결하는 함수
 (& 연산자)


찾기 : 글자 시작위치 번호
=FIND(찾고자 하는 글자, 찾을 위치) - 대소문자 구분
=SEARCH(찾고자 하는 글자, 찾을 위치)

=FINDB(찾고자 하는 글자, 찾을 위치) 
=SEARCHB(찾고자 하는 글자, 찾을 위치)
 영숫자, 특수기호-1바이트, 한글-2바이트


rept(글자, 반복횟수)  : 반복횟수만큼 글자를 반복


생년월일
=DATE(MID(C4,1,2),MID(C4,3,2),MID(C4,5,2))



생년월일(2000년생-성별코드 확인 후)
=DATE(20 & LEFT(주민번호,2), MID(주민번호,3,2), MID(주민번호,5,2))
=DATE(LEFT(주민번호,2)+2000, MID(주민번호,3,2), MID(주민번호,5,2))


=if(OR(성별코드="1",성별코드="2"), DATE(MID(C4,1,2),MID(C4,3,2),MID(C4,5,2)), DATE(20 & LEFT(주민번호,2), MID(주민번호,3,2), MID(주민번호,5,2)))
, 

성별
=IF(MID(주민번호, 8, 1)="1", "남자", "여자")
=IF(VALUE(MID(주민번호, 8, 1))=1, "남자", "여자")
=IF(OR(MID(주민번호, 8, 1)="2", MID(주민번호, 8, 1)="4"), "여", "남")

=IF(ISEVEN(MID(주민번호, 8, 1)), "여", "남")
=IF(ISODD(MID(주민번호, 8, 1)), "남", "여")
=IF(MOD(MID(주민번호, 8, 1), 2), "남", "여")
=CHOOSE(MID(주민번호, 8, 1),"남", "여", "남", "여")



나이
=YEAR(TODAY())-LEFT(주민번호, 2)-1900 & "세"


나이에서 출생월이 지났으면 +1을 해주고, 아직 지나지 않았으면 +0을 해서 정확한 만 나이를 추출

=YEAR(TODAY())-LEFT(주민번호, 2)-1900 + IF(MID(주민번호, 3, 2)>=MONTH(TODAY()), 1, 0) & "세"




보호주민번호
=LEFT(주민번호, 8) & "******"
=LEFT(주민번호 , 8) & REPT("*", 6)
=CONCATENATE(LEFT(주민번호, 8), "******")
=CONCATENATE(LEFT(주민번호, 8), REPT("*", 6))
=REPLACE(주민번호, 9, 6, "******") 치환 : 자릿수만 고려(철자무관)
=SUBSTITUTE(주민번호, RIGHT(주민번호, 6), "******")
=SUBSTITUTE(주민번호, MID(주민번호, 9, 6), "******")


공백제거/글자 치환
=SUBSTITUTE(이름, " ", "") 치환 : 철자를 고려(자릿수 무관)



<논리 함수>

if(명제, 명제가 참일 때 값, 명제가 거짓일 때 값)

명제 : TRUE나 FALSE로 판단될 수 있는 수나 식,
         비교연산자(>,<,>=...), AND/OR 함수, 정보함수(iserror, isblank...)



and  논리곱(*),  ~이고, ~이면, 모두 다~

모두 만족하면 true, 하나라도 만족하지 못하면 false
=and(1월>=100000, 2월>=100000, 3월>=100000)


or   논리합(+),  또는 ~~~, ~이거나, ~중에 하나라도

하나이상 만족하면 true, 모두 만족하지 못할 때만 false
=or(1월>=100000, 2월>=100000, 3월>=100000)



IF(성적>=70, "합격", "불합격")
=IF(H15>=70, "합격", "불합격")


IF(평균>=80, "승진", "")
=IF(AVERAGE(H15:I15)>=80, "승진","")


IF(평균>=80, "상", IF(평균>=60, "중", "하")) : 중첩IF - 7개까지 가능
=IF(AVERAGE(H15:I15)>=80, "상", IF(AVERAGE(H15:I15)>=60, "중", "하"))



IF(AND(1과목>=80, 2과목>=80), "우수상", "")
=IF(AND(H15>=80, I15>=80), "우수상", "")
{=IF((H15:I15>=80), "우수상", "")}  -> 배열수식으로 마무리



컴활 필기 합격 함수
=if(and(average(성적)>=60, 1과목>=40, 2과목>=40, 3과목>=40), "합격", "불합격")

=IF(AND(AVERAGE(H15:J15)>=60, H15>=40,I15>=40,J15>=40), "합격", "불합격")

=IF(AND(AVERAGE(H15:J15)>=60, countif(H15:J15, ">=40")=3), "합격", "불합격")




=true() : 1, -1
=false() : 0

=not(1) : false


IFERROR(정상 수식, 정상수식이 오류 시에 나타내는 수식/값) 
: 오류가 발생하면 처리하는 함수, 2007 새로 생긴 함수
=IF+ISERROR



<찾기/참조 함수>
vlookup(찾을 때 사용할 값, 정보를 가지고 있는 범위, 원하는 값이 있는 열 번호, 옵션)
: 바코드(찾을 때 사용할 값, 첫열) 가지고 물건정보표(세로,참조표)에서 물건 가격(열번호)을 찾아오는 함수

hlookup(찾을 때 사용할 값, 참조표, 참조표에서 필요로 하는 행번호, 옵션)
: 바코드(찾을 때 사용할 값, 첫행) 가지고 물건정보표(가로)에서 물건 가격(행번호)을 찾아오는 함수

참조표는 대부분 절대참조
옵션 : true 나 1, 생략시 - 근사치
        false 나 0 - 정확하게 일치

lookup(찾을 값, 범위, 추출범위)
lookup(찾을 값, 범위)
: 검색 값을 검색 범위에서 추출

TRANSPOSE(배열) : 행과 열 바꾸기, 
CTRL+SHIFT+ENTER 마무리(배열함수)


index(값을 추출할 범위, 행번호, [열번호])
: 범위내에서 행과 열이 만나는 자리의 값 추출
  열번호는 값범위가 열의 갯수가 하나일때는 생략 가능


OFFSET(기준, 행수, 열수) : 기준으로부터 이동한 위치 셀의 값을 표시
행수  : +는 아래로, -는 위로
열수 : +는 오른쪽으로, -는 왼쪽(앞)으로


CHOOSE(가지고 있는 수, 1일때 값, 2일때 값,...254일때 값)
: 가지고 있는 수(인덱스 번호, 1~254)에 해당하는 값을 선택하는 함수


match(값, 값들이 있는 범위, 옵션) : 값의 위치 번호=위치 추출
 옵션 : 1  => 근사치, 작거나 같은 값들 중에서 가장 큰 값=오름차순
        0  => 텍스트나 정렬되지 않은 경우=정확하게 일치하는 값만 조회
        -1 => 근사치, 크거나 같은 값들 중에서 가장 작은 값=내림차순


address(행번호, 열번호, 참조유형)  : 셀 주소

areas(참조영역1, 참조영역2...)  : 영역수


column(인수)   : 셀주소의 열번호
column()  : 현재 이 함수가 실행된 셀의 열번호
columns(셀범위)  : 범위 열의 개수


row(인수)  : 셀주소의 행번호
row()  : 현재 이 함수가 실행된 셀의 행번호
rows(셀범위)  : 범위 행의 개수

hyperlink(파일이름, 셀주소)  : 지정 파일의 셀주소에 있는 값을 추출

indirect(참조할 텍스트) : 텍스트 문자열로 지정한 셀 주소를 찾아 입력된 값을 추출


<오류>
#N/A  : 참조 영역에 지정 값이 없거나 참조영역이 잘못 지정 되었을 경우



<재무함수>

FV  : 미래가치, 적금 만기금액

PMT  : 정기적인 납입액(월불입액/월상환액/분기지급액)

PV  : 현재가치, 미래가치를 현재에 적용시킨 가치

NPV  : 투자액+수익 현재가치


RATE 이율(월/분기)
NPER 기간(월/분기), 불입횟수
PMT 월납입액/월불입액/월상환액/분기지급액(금액 마이너스기호 삽입)
TYPE 월초=1/월말=0 이나 생략



<정보 함수>

isblank, iserror, iserr, iseven, islogical, isnontext, isnumber, isodd, istext 
: 결과가 true, false로 나타남(명제)


cell("정보 유형", 셀이나 값) : 정보 유형은 스크린 팁으로 나타나므로 선택해서 사용

 정보유형 - address, col, color, contents, row, type


iserr : 참조함수의 오류(#N/A)나 기타사항은 false, 
         다른 함수의 오류(#value, #div/0, #null, #name, #ref)는 true

Type(값) : 값의 형식, 
숫자면 1, 텍스트면 2, 논리값이면 4, 오류면 16, 
     배열(표, 범위)이면 64로 표시