구글 스프레드 시트의 함수는 엑셀과 정말 유사합니다.
그 중 데이터를 다루는 몇 가지 함수와 그 사용법을 정리해보았습니다.
데이터를 다루는 몇 가지 함수
가. IMPORTRANGE
다른 파일에서 데이터를 가져오는 함수입니다.
=IMPORTRANGE("가져올 파일의 인터넷 주소(url)", "시트명!가져올 범위")
=IMPORTRANGE("구글 파일 ID)", "시트명!가져올 범위")
예시)
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ORIZ-MkkmdgPYlivgSP9fgKPCFZefvM1MBmx02IKatE", "함수 정리!A2:F20")
=IMPORTRANGE("1ORIZ-MkkmdgPYlivgSP9fgKPCFZefvM1MBmx02IKatE", "함수 정리!A2:F20")
① 이렇게 데이터를 가져온 후 합계 범위를 달리한다거나 새로운 열 또는 행을 추가할 수 있습니다.
단, 가져온 데이터가 있는 범위에 무언가를 추가하시면 에러가 뜹니다. 해당 범위 밖에서 작업하세요.
② 일반적으로 경로(url)를 가져올 때는 쌍따옴표(" ")를 씁니다.
한편 탭은 그 이름이 한글이거나 사이띄우기가 있을 경우 홑따옴표(' ')로 묶어줍니다.
예를 들어 DATA 탭에 있는 셀의 데이터를 가져올 때는 다음과 같이 씁니다. 'DATA'!A2:A10
다만, IMPORTRANGE 함수는 조금 다르게 표현하고 있습니다. "함수정리!A2:F20"
이런 사소한 것으로 에러가 나는 경우가 많으니 주의하세요~
나. QUERY
다른 시트로부터 데이터를 가져옵니다. 이때 다양한 옵션을 통해 원하는 형식으로 가공할 수 있습니다.
=QUERY('시트명'!A:Z, "SELECT *", 1) // 1은 제목행
=QUERY('시트명'!A:Z, "SELECT * WHERE J IS NOT NULL", 1) // J는 현재 시트 기준
=QUERY('시트명'!A:Z, "SELECT * WHERE Col1 is not null", 1) // 1번 열 기준
=QUERY('시트명'!A:Z, "SELECT * ORDER BY J ASC", 1) // J열을 기준으로 오름차순 정렬, 내림차순은 DESC
=QUERY('시트명'!A:Z, "SELECT * WHERE J IS NOT NULL ORDER BY J ASC", 1)
다. FILTER
다른 시트로부터 데이터를 조건식에 따라 가져올 수 있습니다.
=FILTER('시트명'!A:Z, 조건)
=FILTER('시트명'!A:Z, 조건1, 조건2)
라. INDEX & MATCH
다른 시트로부터 조건에 부합하는 데이터를 가져올 수 있습니다.
=INDEX(출력할 목록, MATCH(검색값, 일치검색범위, 0)) // 0은 정확히 일치
기타 다양한 수식
ARRAYFORMULA
Arrayformula 함수는 구글 스프레드 시트에만 있는 함수입니다.
이 함수를 활용하면 첫 행에 한줄만 입력해도 그 아래는 자동으로 수식이 적용됩니다.
자동연번 생성 수식
=ARRAYFORMULA(IF(B4:B="", "", ROW(B4:B) - 3))
ROW(B4:B)는 B열의 각 셀에 대한 행번호를 가져옵니다.
즉 B4면 4가 됩니다. 여기서  ROW(B4:B) - 3은 곧 4-3, 즉 1이 됩니다.
한편 IF 부분은 B4:B가 빈 셀("")이면 빈칸을, 아니면 위에서 계산한 행번호를 숫자로 보여줍니다.
즉, 위 수식은 연번을 시작할 셀이 B4셀인 경우에 해당하는 예제입니다.
연번 시작하는 범위가 달라진다면, 해당 연번이 시작할 범위, 그리고 해당 셀의 행번호 숫자보다 1 작은 값으로 수정해 사용하시면 됩니다.