[패스트캠퍼스] 컴퓨터활용능력 1급 정리 - 6

 

6. 스프레드시트 일반 - 날짜, 시간 함수, 수학, 삼각 함수,통계 함수, 텍스트 함수

 

날짜, 시간함수 (헷갈리는 것만 정리함)

실기시험볼때와 유사.
필기는 실기보다 함수가 적게 나옴.
그러니까 참조만하기.
필기를 먼저 보는 사람들이 많기 때문에 실기에 나오는 함수 스타일을 확인하기.

NOW() 현재 날짜, 시간 둘 다 표시됨.
DATE(연,월,일)
EDATE(시작 날짜, 전이나 후의 개월 수) : 시작 날짜에 개월 수를 더해서 며칠인지 계산
EOMONTH(시작 날짜, 전이나 후의 개월수) : 개월 수를 더한 달의 마지막날짜 계산
 DAYS 360 (시작날짜, 종료날짜, 계산방법을 지정하는 것으로 false 또는 생략할 경우 미국식, TRUE는 유럽식) : 1년을 360일로 가정한 후 일수구함
YEARFRAC(시작날짜, 종료날짜, 날짜계산기준) : 1년중 시작날짜와 끝 날짜 사이의 날짜 수가 1년 중 차지하는 비율을 계산함.

추가 정보)

날짜데이터는 순차적인 일련번호(1900년 1월 1일이 1임.)로 저장되기 때문에 날짜 데이터를 이용한 수식을 작성할 수있다.

시간 데이터는 날짜의 일부로 인식되며 소수로 저장되며, 낮 12시는 0.5로 계산된다.

TODAY함수는 셀이 활성화 되거나 워크시트가 계산될 때 또는 함수가 포함된 매크로가 실행될 때마다 시스템으로부터 현재 날짜를 업데이트한다.
따라서 다른 날짜로 내가 컴퓨터시스템에 설정했을 때, 똑같이 그 날짜가 업데이트되는 것이다.

WEEKDAY함수는 날짜에 해당하는 요일을 구하는 함수로 RETURN_TYPE인수를 생략 또는 1을 입력하면 '일월화수목금토'중 해당하는 한자리 요일이 "숫자 값"으로 반환.

---

 

수학/삼각함수

ROUND(인수, 자릿수) 반올림
ROUNDUP(인수, 자릿수) 올림
ROUNDDOWN(인수, 자릿수) 내림
ABS(숫자) 숫자의 크기만 구함. 즉, 절대값구하기
MOD(인수, 제수) 숫자 나누고 나머지 계산
INT(인수) 실수의 소수점 이하를 제거하여 정수로 변환
SQRT(숫자) 숫자의 양의 제곱근 계산
FACT(숫자) 계승값(팩토리알!) 계산
POWER(인수1, 인수2) 두개의 인수로 거듭제곱만들기.
POWER(3,4) = 4의 3승
EXP(수치) e를 숫자만큼 거듭제곱 계산 
RAND() 0보다 크거나같고 1보다 작은 랜덤수를 구함

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

 

---

통계함수

AVERAGE(인수1, 인수2,...) 주어진 인수들의 평균 구하기
AVERAGEA(인수1, 인수2,...) 빈 셀을 제외한 모든 인수를 포함하여 평균 구하기
AVERAGEIF(범위, 조건, 평균을 구할 범위) 지정한 조건에 맞는 데이터만 찾아서 평균을 구함
AVERAGEIFS(평균범위, 조건범위1, 조건1, 조건범위2, 조건2,...)
: 지정한 조건에 맞는 데이터만 찾아서 평균을 구함
MAX(인수1, 인수2,...) 주어진 인수 중에서 최대값을 구함
MIN(인수1, 인수2,...) 주어진 인수 중에서 최소값을 구함
MAXA(value1, value2,...) 주어진 인수 중 최댓값 구함. 그러나 다른점은 빈셀, 논리값, 숫자로 표시되니 텍스트 등도 인수로 사용될 수있음

RANK(값, 참조영역, 순위 결정 방법) 선택한 셀이 지정한 범위 내에서 몇 번째인지를 구함
COUNT(인수1, 인수2,...) 주어진 인수의 숫자개수를 구함 (날짜도 포함임)
COUNTA(인수1, 인수2,...) 지정한 범위 내에서 비어있지 않은 셀 갯수
COUNTBLANK(범위) 지정한 범위 내에서 빈셀 수  
COUNTIF(조건범위, 조건) 지정한 범위 내에서 조건에 맞는 셀의 갯수
COUNTIFS(조건범위1, 조건1, 조건범위2, 조건2,..) 참조한 셀 영역에서 여러 조건에 맞는 셀 갯수

LARGE(배열, K) 자료에서 숫자 중 K번째로 큰 숫자 구함
SMALL(배열, K) 자료에서 숫자 중 K번째로 작은 숫자 구함
MEDIAN(인수1, 인수2,....) 인수로 주어진 숫자들을 크기 순으로 나열했을 때 딱 중간에 '위치'하는 숫자 구함
MODE(인수1, 인수2,...) 주어진 숫자들 중 가장 많이 발생한 값 구함

 

---

 

 

텍스트함수

LEFT(텍스트, 문자수)
문자열에서 왼쪽을 기준으로 지정한 개수만큼 추출
RIGHT(텍스트, 문자수) 
문자열에서 오른쪽을 기준으로 지정한 개수만큼 추출
MID(텍스트, 시작위치, 문자수)
문자열에서 지정한 시작위치를 기준으로 지정한 개수만큼 추출
TRIM(텍스트)
단어 사이에 있는 한 칸의 공백을 제외하고 문자 열에 있는 모든 공백 제거
LOWER(텍스트)
문자열의 대문자를 모두 소문자로 변환
UPPER(텍스트)
문자열의 소문자를 모두 대문자로 변환
PROPER(텍스트)
영어 단어의 첫번째 문자와 영문자가 아닌 문자 다음에 오는 영문자를 대문자로 변환, 나머지 문자들은 소문자로 변환
FIND(찾을 텍스트, 찾을 텍스트를 포함한 텍스트)
문자열에서 특정 문자를 찾아 해당 위치를 문자 단위로 반환
FINDB(찾을 텍스트, 찾을 텍스트를 포함한 텍스트)
문자열에서 특정문자를 바이트단위로 반환함
예를들어 '패스트캠퍼스' 이 문자열에 '패스트'캠'까지 찾으려고한다면 한글은 2바이트니까 '캠'자리=7,8Byte 이니까 7로 반환

 

예시문)
A1셀의 TR-A-80을 B1에 TR-A80으로 바꾸어 표시하자.

1. REPLACE(A1,5,1,"") 5번째글자를공백으로바꿔라
2. CONCATENATE(LEFT(A1,4),MID(A1,6,2))
왼쪽에서 4번째, 중간에서 6번째자리에서 2개문자를 남겨놓고 합쳐라.
3. SUBSTITUTE(A1,"-","",5)
A1에 있는 -를 찾아서 공백으로 바꾸는데 5번째로 나타나있는 -이다.
이경우에는 -가 두개뿐이니까 결과값은 다 바꿔서 나타남. 따라서 틀린 것.
4.LEFT(A1,4)&RIGHT(A1,2) 
왼쪽에서 4번째, 오른쪽에서 두번째를 이어라.

SEARCH와 FIND함수 차이
FIND함수는 찾으려면 영어의 대/소 문자를 구분해서 적어야하고 SEARCH함수는 구분하지 않아도 가능
즉 FIND는 좀 더 신경써야함.

---

찾기참조함수

VLOOKUP(검색값, 범위, 열번호, 검색 유형)
지정한 셀 영역의 첫번째 셀에서 비교값을 찾은 다음,
비교값과 같은 행에서 사용자가 지정한 열의 위치에 있는 데이터
HLOOKUP(검색값, 범위, 행번호, 검색 유형)
지정한 셀 영역의 첫번째 셀에서 비교값을 찾은 다음,
비교값과 같은 열에서 사용자가 지정한 행의 위치에 있는 데이터

COLUMN(참조)
셀주소에대한 열번호
COLUMNS(배열)
범위 안에 들어있는 열의 갯수
ROW(참조)
셀주소에대한 행번호
ROWS(배열)
범위 안에 들어있는 행의 갯수
CHOOSE(색인번호범위, 번호, 값1, 값2,...)
색인번호를 이용하여 특정번째에 있는 값을 반환
INDEX(범위, 행번호, 열번호, 참조영역번호)
셀범위나 데이터 배열에서 행번호와 열번호가 교차되는 곳에 있는 값 반환
MATCH(검사값, 검사범위, [검사유형])
지정된 범위에서 기준값과 같은 데이터를 찾아 범위 내에서 상대적인 위치를 반환