1. 종류별 함수
Google 스프레드시트의 전체 함수 목록은 # 참조.※ 함수 전부를 쓰지 마시고, 사용빈도가 많은 함수를 서술해주세요.
이 중 많은 것들은 Microsoft Excel, LibreOffice Calc에서 그대로 호환된다.
모든 함수를 입력할 때는 =를 반드시 입력하고 시작해야 한다. 예를 들어 "=NOW()" 이런 식으로.
1.1. 수학 함수
| 함수 | 설명 |
| SUM(값1, 값2) | 일련의 숫자 및/또는 셀의 합계를 반환합니다. |
| SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) | 여러 기준에 따른 범위의 합계를 반환합니다. |
| COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) | 여러 기준에 따른 범위의 수를 반환합니다 |
| PRODUCT(인수1, 인수2) | 일련의 숫자를 곱한 결과를 반환합니다. |
| ROUND(값, 소수점_이하_자릿수) | 표준 규칙에 따라 숫자를 특정 소수점 이하 자릿수로 반올림합니다 |
| ROUNDDOWN(값, 소수점_이하_자릿수) | 숫자를 특정 소수점 이하 자릿수로 버립니다. |
| ROUNDUP(값, 소수점_이하_자릿수) | 숫자를 특정 소수점 이하 자릿수로 올립니다. |
| COUNTBLANK(범위) | 지정된 범위에서 빈 셀의 개수를 반환합니다. |
| ABS(값) | 숫자의 절대값을 반환합니다. |
| EXP(지수) | 자연상수 e(~2.718)의 거듭제곱을 반환합니다. |
| FACT(값) | 숫자의 계승을 반환합니다. |
| GCD(값1, 값2) | 한 개 이상 정수의 최대 공약수를 반환합니다. |
| INT(값) | 해당 숫자보다 작거나 같은 가장 가까운 정수로 숫자를 버립니다. |
| LCM(값1, 값2) | 한 개 이상 정수의 최소 공배수를 반환합니다. |
| LN(값) | e(오일러 수)를 밑으로 하는 자연 로그 값을 반환합니다. |
| LOG(값, 밑) | 밑에 대해 숫자의 자연 로그를 반환합니다. |
| LOG10(값) | 밑이 10인 자연 로그 값을 반환합니다. |
| MOD(피제수, 제수) | 나누기 연산 후 나머지로 모듈러 연산자의 결과를 반환합니다. |
| PI() | 소수점 이하 14자리까지의 Pi 값을 반환합니다. |
| POWER(밑, 지수) | 거듭제곱한 숫자를 반환합니다. |
| SUBTOTAL(함수_코드, 범위1, 범위2) | 지정된 집계 함수를 사용하여 열 방향 범위의 셀에 대한 소계를 반환합니다. |
1.2. 통계학 함수
| 함수 | 설명 |
| VAR(값1, 값2) | 표본의 분산의 추정치를 계산합니다. 즉, 표본 분산을 구합니다. Excel의 var.s를 쓰면 #NAME? (Unknown function: 'var.s'.) 오류가 발생합니다. |
| VARP(값1, 값2) | 전체 모집단의 분산( 모 분산)을 계산합니다. Excel의 var.p를 쓰면 자동으로 varp 함수로 변환합니다. |
| COVAR(데이터_y, 데이터_x) | 데이터 집합의 공분산을 계산합니다. |
| STDEV(값1, 값2) | 표본의 표준 편차의 추정치를 계산합니다. Excel의 stdev.s를 쓰면 #NAME? (Unknown function: 'stdev.s'.) 오류가 발생합니다. |
| STDEVP(값1, 값2) | 전체 모집단의 표준 편차( 모 표준 편차)의 추정치를 계산합니다. Excel의 stdev.p를 쓰면 #NAME? (Unknown function: 'stdev.p'.) 오류가 발생합니다. |
| AVEDEV(값1, 값2) | 데이터 집합의 평균에서 데이터의 절대 편차의 평균을 계산합니다. |
| DEVSQ(값1, 값2) | 표본을 기준으로 하는 편차의 제곱의 합을 계산합니다. |
| NORMDIST(x, 평균, 표준_편차, 누적_여부) | 지정된 값, 평균 및 표준 편차에 대해 정규 분포 함수(또는 정규 누적 분포 함수) 값을 반환합니다. |
| STANDARDIZE(값, 평균, 표준_편차) | 분포의 평균 및 표준 편차에 대해 확률 변수의 정규화된 값을 계산합니다. |
| ZTEST(데이터, 값, 표준_편차) | 표준 분포를 이용하여 두 부분으로 갈라진 Z-검정의 P-값을 반환합니다. |
| CONFIDENCE(유의수준, 표준_편차, 모집단_크기) | 정규 분포에 대한 신뢰 구간의 절반의 폭을 계산합니다. |
1.3. 날짜
| 함수 | 설명 |
| TIME(시,분,초) | 주어진 시, 분, 초를 시간으로 변환합니다. |
| TODAY() | 현재 날짜를 날짜값으로 반환합니다.[1] |
| WEEKDAY(날짜, 유형) | 주어진 날짜의 요일을 나타나는 숫자를 반환합니다. |
| YEAR(날짜) | 주어진 날짜에 지정된 연도를 반환합니다. |
| NETWORKDAYS.INTL(시작_날짜, 종료_날짜, [주말], [공휴일]) | 주어진 두 날짜 사이에 주말과 휴일이 제외된 일자를 반환합니다.(두 날짜 사이의 평일의 수) |
| WORKDAY(시작일, 영업일_수, [휴일]) | 지정된 영업일 수 이후의 종료일을 계산합니다. |
| YEARFRAC(시작일, 종료일, [날짜_계산_기준]) | 지정된 날짜 계산 기준을 사용하여 두 날짜 사이의 연도 차이를 반환합니다. |
| DATE(년, 월, 일) | 주어진 연, 월, 일을 날짜로 반환합니다. |
| DATEDIF(시작일, 종료일, 단위) | 두 날짜 사이의 일, 월 또는 연수를 계산합니다. |
| DAYS360(시작일, 종료일, 방법) | 일부 금융이자 계산에 사용되는 360일 연도를 기준으로 두 날짜의 차이를 반환합니다. |
| WEEKNUM(날짜, [유형]) | 입력된 날짜가 한해 중 몇 번째 주인지 나타내는 숫자를 반환합니다. |
| EDATE(시작일) | 지정된 날짜의 특정 개월 전후 날짜를 반환합니다. |
| EOMONTH(시작일, 개월수) | 지정된 날짜의 특정 개월 전후에 해당하는 월의 마지막 날의 날짜를 반환합니다. |
| HOUR(시간) | 특정 날짜의 시간 구성요소를 숫자 형식으로 반환합니다. |
| MINUTE(시간) | 특정 날짜의 분 구성요소를 숫자 형식으로 반환합니다. |
| MONTH(날짜) | 특정 날짜에 해당하는 연도의 월을 숫자 형식으로 반환합니다. |
| NETWORKDAYS(시작일, 종료일, 휴일) | 주어진 두 날짜 사이의 순 영업일수를 반환합니다. |
| TIMEVALUE(시간_문자열) | 하루 24시간 중 시간이 나타내는 부분을 반환합니다. |
| NOW() | 현재 날짜 및 시간을 날짜 값으로 반환합니다. |
| SECOND(시간) | 특정 날짜의 초 구성요소를 숫자 형식으로 반환합니다. |
1.4. 엔지니어링
1.5. 필터
| 함수 | 설명 |
| SORT(범위, 열_정렬, 오름차순, 열_정렬2, 오름차순2) | 하나 이상의 열의 값을 기준으로 지정된 배열 또는 범위의 행을 정렬합니다. 일반정렬 기능과의 차이점은 함수이기때문에 매개변수 값에 따라 유동적으로 변한다는 점입니다. |
| UNIQUE(범위) | 중복된 것은 버리고 입력된 원본 범위에서 고유 행을 반환합니다. 원본 범위에 처음 표시되는 순서대로 행이 반환됩니다. |
| FILTER(범위, 조건1, 조건2) | 지정된 조건을 충족하는 열 또는 행만 반환하여 원본 범위의 필터링 버전을 반환합니다. 일반 필터기능과의 차이점은 함수기 때문에 매개변수의 값에 따라 유동적으로 변한다는 점입니다. |
1.6. 금융
1.7. Sheets 전용 함수
이 함수들은 Google Sheets에서만 지원한다.| 함수 | 설명 |
| IMPORTRANGE(스프레드시트_키, 범위_문자열) | 다른 스프레드시트에서 셀 범위값을 실시간으로 가져온다. 여러 시트에 흩어져있는 데이터를 실시간으로 한곳에 모으고 싶을때 유용하다 |
| IMPORTHTML(URL, 검색어, 색인) |
웹 페이지의 표 또는 목록을 실시간으로 가져온다. 주식이나 환율 정보등을 실시간으로 가져올때 매우 유용하다 단, 로그인후 접근가능한 정보 등 권한이 필요한 웹사이트의 정보는 - |
| GOOGLEFINANCE(시세_표시, [속성], [시작일], [종료일|일수], [간격]) | 구글 파이낸스에서 유가증권 정보를 가져온다. |
1.8. 정보
| 함수 | 설명 |
| ISNONTEXT(값) | 값이 텍스트가 아닌지 여부를 확인합니다 |
| ISNA(값) | 값이 오류값 '#n/a'인지 여부를 확인합니다. |
| ISURL(value) | 값이 유효한 URL인지를 확인합니다. |
| ISFORMULA(cell) | 참조된 셀에 수식이 있는지 확인합니다. |
| ISERROR(값) | 값이 오류인지 여부를 확인합니다. |
| ISERR(값) | 값이 `#n/a` 이외의 오류 값인지 여부를 확인합니다. |
| ISEMAIL(value) | 값이 유효한 이메일 주소인지를 확인합니다. |
| ISBLANK(값) | 참조된 셀이 비어 있는지 여부를 확인합니다. |
| TYPE(value) | 함수에 입력된 데이터 유형과 관련된 숫자를 반환합니다. |
| ISTEXT(값) | 값이 텍스트인지 여부를 확인합니다. |
| ISREF(값) | 값이 유효한 셀 참조인지 여부를 확인합니다. |
| ISNUMBER(값) | 값이 숫자인지 여부를 확인합니다. |
| CELL(info_type, reference) | 지정한 셀에 대해 요청된 정보를 반환합니다. |
1.9. 논리
| 함수 | 설명 |
| AND(논리_표현식1, 논리_표현식2) | 입력된 인수가 모두 논리적으로 TRUE이면 TRUE를 반환하고 입력된 인수가 모두 논리적으로 FALSE이면 FALSE를 반환합니다 |
| OR(논리_표현식1, 논리_표현식2) | 입력된 인수가 하나라도 논리적으로 TRUE이면 TRUE를 반환하고 입력된 인수가 모두 논리적으로 FALSE이면 FALSE를 반환합니다. |
| NOT(논리_표현식) | 논리 값의 역을 반환합니다.`NOT(TRUE)'는 `FALSE`를 반환하고; `NOT(FALSE)`는 `TRUE'를 반환합니다. |
| IFERROR(값, 오류인_경우_값) | 첫 번째 인수가 오류 값이 아니면 첫 번째 인수를 반환하고, 오류 값이면 두 번째 인수가 있는 경우 두 번째 인수를 반환하고, 두 번째 인수가 없는 경우 비워 둡니다. |
| IF(논리_표현식, TRUE인_경우_값, FALSE인_경우_값) | 논리 표현식이 'TRUE'인 경우 특정 값을 반환하고 'FALSE'인 경우 다른 값을 반환합니다. |
1.10. 검색
| 함수 | 설명 |
| CHOOSE(색인, 선택1, 선택1) | 색인을 기반으로 선택 목록에서 요소를 반환합니다. |
| ROWS(범위) | 지정된 배열 또는 범위에 있는 행의 개수를 반환합니다. |
| ROW(셀_참조) | 지정된 셀의 행 번호를 반환합니다. 엑셀의 row와는 다르게 범위를 지정할 경우 가장 처음 셀의 값만 반환합니다. |
| OFFSET(셀_참조, 오프셋_행, 오프셋_열, 높이, 너비) | 시작 셀 참조에서 지정된 수의 행과 열로 변환된 범위 참조를 반환합니다. |
| MATCH(검색할_키, 범위, 검색_유형) | 범위에서 지정된 값과 일치하는 항목의 상대적 위치를 반환합니다. |
| INDIRECT(문자열로_지정된_셀_참조) | 문자열로 지정된 셀 참조를 반환합니다. |
| INDEX(참조, 행, 열) | 행과 열 오프셋으로 지정된 셀 내용을 반환합니다. |
| HYPERLINK(URL, 링크_라벨) | 셀 안에 하이퍼링크를 만듭니다. |
| HLOOKUP(검색할_키, 범위, 색인, 정렬됨) | 행 방향 검색입니다. 범위의 첫 번째 행에서 키를 검색한 다음 키가 있는 열에서 지정된 셀의 값을 반환합니다. |
| ADDRESS(행, 열, 참조_방식, A1_표기법_사용) | 셀 참조를 문자열로 반환합니다 |
| COLUMNS(범위) | 지정된 배열 또는 범위에 있는 열의 개수를 반환합니다 |
| COLUMN(셀_참조) | 지정된 셀의 열 번호를 반환합니다('A=1'). |
| VLOOKUP(검색할_키, 범위, 색인, 정렬됨) | 열 방향 검색입니다. 범위의 첫 번째 열에서 키를 검색한 다음 키가 있는 행에서 지정된 셀의 값을 반환합니다. |
1.11. 연산자
1.12. 텍스트
1.13. 데이터베이스
1.14. 파서
1.15. 배열
2. 배열 수식
배열수식은 처음 접해본다면 이해하기 쉽지 않지만, 제대로 이해만하고 활용한다면 남들은 흉내도 낼 수도 없는 시트를 만들어 낼 수 있을것이다.
읽기전에 우선 배열이라는게 뭔지 알아야한다. 정확한 정의는 참고 하면되고
프로그래밍 지식이없다면 위의 내용을 이해할 수 있을리가 없으므로
스프레드시트에서의 배열을 쉽게 설명하자면 시트의 A1을 셀이라고한다면 A1:B1 이라는 범위 = 배열이라고 생각하면 편하다.
그리고 A1:B1 또는 A1:A2 같이 한줄로된 범위를 1차원 배열이라고 보면 되고
A1:B2 처럼 2줄 이상으로된 범위를 2차원 배열이라고 생각하면된다.
1. = { 배열_수식 , 배열_수식 } 또는 = { 배열_수식 ; 배열_수식}
수식을 입력하면 배열이 시각화되어 셀에 표시된다.
세미클론 ( ; ) 을 붙이면 열 방향으로 배열이 만들어지며
콤마 ( , ) 를 붙이면 행 방향으로 배열이 만들어진다.
응용해 ={1,2,3;4,5,6} 으로 2차원 배열을 만들수도 있다.
아주 간단하게 활용할수있는 부분은 두개의 열 또는 행을 하나로 만드는것이다. [ ex) = {A1:A10 ; B1:B10} ]
당연히 함수와 연계해 사용도 가능하다.주의할점은 MS 엑셀의 배열수식을 써본사람은 MS엑셀의 배열수식과 비슷한건가라고 생각할수 있지만 사실 많이 다르다.
MS 엑셀의 배열수식은 구글 스프레드시트에서는 아래에있는 ArrayFormula 라는 함수에서 비슷한 동작을 한다.
2. =ArrayFormula(배열_수식)
IF함수처럼 배열이 아닌 함수를 배열함수로 만들어주는 강력한 기능이다.
Ctrl + Shift + Enter를 통해 배열수식을 만들 수 있으며 =ArrayFormula() 를 수동으로 입력할수도있다. 함수의 일부분에도 사용이가능하다
사용 예제는 링크를 참조하자
참고여담으로 ArrayFomula는 MS Excel의 배열수식과 비슷하지만 큰 차이점이 있다.
엑셀은 {=SUM(C2:C11*D2:D11)} 같은 형식으로 중괄호 '{ }'로 배열수식을 만들고
구글시트는 =ArrayFomula(SUM(C2:C11*D2:D11)) 같은 형식으로 ArrayFumula 함수로 배열수식을만든다.
구글스프레드시트는 배열수식이 함수로 되어있어 더 복잡하고 유연한 연산이 가능하고
무엇보다 배열의 시각화가 큰 장점이자 차이점이다
배열의 시각화는 예를들어 B1 셀에 =ArrayFormula(A1:A2)를 입력하면 B1,B2셀에 각각 A1,A2셀의 내용이 들어가지만
MS엑셀은 A1셀의 내용은 B1셀에 입력되지만, A2셀의 내용은 눈에 보이지않는 컴퓨터 메모리에만 입력이되고 셀에 나타나지않는다.
때문에 구글스프레드시트는 더 다양한 방법으로 표현이 가능하다.
구글 스프레드시트의 중괄호 '{ }' 배열수식과 ArrayFormula 배열수식의 기능은 많이 다르다
중괄호 '{ }'는 흩어진 셀이나 범위를 하나의 범위로 묶어주는 기능을 하고 IF함수 같은 배열이 아닌함수를 배열함수로 만들 수 없다
ArrayFomula는 수식의 동작자체를 배열을 사용할 수 있도록 바꿔 주고, 세미클론 (;)을 넣어서 열방향 배열을 만들 수 없다.
아래는 ArrayFomula함수로 1차원 배열과 2차원 배열을 만들어 활용한 실생활에 적용 가능한 시트다.
배열 수식 응용 이 시트는 신용카드 결제 및 대출을 했을때 어느시기에 얼마씩 상환이되는지 원리금을 복수 계산해주는 시트이다.
대출&할부 옵션을 다 추가하느라 계산이 좀 봅잡하고
난이도가 좀 높으므로 엑셀 함수에 대한 이해가 있는 상태에서 참고삼아 보길 권한다 (배열의 시각화때문에 MS 엑셀로 변환해도 호환안되는점 참고)