구글스프레드 시트에서 알아두면 가장 도움이 되는 함수들이다.
엑셀이나 구글스프레드시트를 잘 다루는지를 알아 볼 때, index와 match 함수를 활용할 줄 안다면 상당히 잘 사용하고 있다고 인정해 줄 수 있다. 거기에 offset 함수까지 다룰 줄 안다면 학교에서 하는 대부분의 업무는 가능하다고 보면 된다.
vlookup 함수가 할 수 없는 것들을 할 수 있다. index는 한번에 전체열이나 행을 나타나게 할 수 있고, 중간에 전입생이나 전출생으로 인해 원본데이터의 행을 추가하거나 삭제해도 에러없이 작동하게 할 수 있다.
활용방법은 아래 링크에 있는 시트를 사본 복사해서 테스트 해 보기 바란다.
https://docs.google.com/spreadsheets/d/1dY8fvdI-Cr_IIbWvnsAdsDskQHJkmUHZuVpb0Bta3Gw/edit?usp=sharing
OFFSET MATCH INDEX함수 활용
설명 MATCH (위치를 숫자로 반환) - 원하는 내용이 어디있는지 찾을 때 사용 범위에서 지정된 값과 일치하는 항목의 상대적 위치를 반환합니다. MATCH(검색할_키, 범위, 검색_유형) 범위는 반드시 1차
docs.google.com
1. MATCH (위치를 숫자로 반환) - 원하는 내용이 어디있는지 찾을 때 사용
범위에서 지정된 값과 일치하는 항목의 상대적 위치를 반환합니다.
정확하게는 기준셀로 부터 몇번째 위치하는지 숫자를 반환함.
MATCH(검색할_키, 범위, 검색_유형)
범위는 반드시 1차원 배열이어야 함. 검색유형은 0으로 해야 정확하게 일치하는 위치를 찾을 수 있음
즉 범위가 A1:A100 이나 A1:Z1 은 가능하지만 A1:B100 처럼 1줄이 아니라 2줄 이상이 되면 에러남
=MATCH("김민서",A1:A8,0)
A1:A8 범위(즉 이름이 있는 범위)에서 '김민서' 이름이 정확히 일치하는 셀을, A1셀로 부터 몇번째 위치에 있는지를 숫자로 반환(A1셀 부터 숫자를 셈)
2. INDEX - 범위에서 지정한 행과 열의 값을 가져 올때 사용
참조범위(지정된 범위에서) 수학좌표 처럼 좌측상단 첫번째 셀에서 행과, 열위치에 있는 단일 값 출력.
INDEX(참조, [행], [열])
행 또는 열을 0으로 설정하면 INDEX는 전체 열 또는 행의 값 배열을 각각 반환해서 편리함
MATCH 함수와 함께 사용하면 행이나 열에 해당하는 숫자를 알아내서 원하는 내용을 가져올 수 있음.
=INDEX(A1:F8,3,5)
A1:F8 범위에서 A1셀을 기준으로 A1셀을 포함해서 3행(아래로 이동) 5열(오른쪽으로 이동) 한 값.
행 또는 열을 0으로 설정하면 INDEX는 전체 열 또는 행의 값 배열을 각각 반환해서 편리함
위 수식을 아래처럼 열 값을 0으로 바꾸면, 전체 행값을 가져옴
=INDEX(A1:F8,3,0)
3. OFFSET (범위를 반환) - 큰 범위에서 내가 원하는 범위를 지정해서 가져올 때 사용
시작 셀 참조에서 지정된 수의 행과 열로 변환된 범위 참조를 반환합니다.
즉 기준셀로부터 원하는 행과 열만큼을 범위로 설정합니다.
OFFSET(셀_참조, 오프셋_행, 오프셋_열, [높이], [너비])
INDEX 함수와 비슷함. 다만 범위가 아니라, 행과 열 숫자로 위치를 지정해 주는 것만 다름
OFFSET 을 이용하는 대부분은 INDEX 함수로 대치할 수 있음.
단 OFFSET 함수는 범위가 고정되어 있는 INDEX 함수와는 다르게 유기적으로 시작 위치를 변화시킬 수 있음.
마찬가지로 MATCH 함수와 함께 사용하면 행이나 열에 해당하는 숫자를 알아내서 원하는 내용을 가져올 수 있음.
4. TRANSPOSE - 행과 열 위치를 한번에 바꿀 때 사용
셀의 배열 또는 범위의 행 및 열을 바꿉니다.(가끔 행과 열의 위치를 바꿔줄 필요가 있음)
각 교과목 성적표 가로로 나열되어 있는 경우, 세로로 바꿀 수 있음
세로로 바꾸면 소트하고 싶을 때 사용하기 편리함
배열의 행과 열을 바꿔 줍니다.
TRANSPOSE(배열_또는_범위)
5. SORT - 행 전체 범위에 있는 내용들을 정렬할 때 사용
하나 이상의 열의 값을 기준으로 지정된 배열 또는 범위의 행을 정렬합니다.
성적순, 이름순 등등 원하는 순으로 정렬해 줄 수 있음
성적이 행으로 배열되어 있는 경우, 소트를 하고 싶으면 위에 TRANSPOSE 함수를 이용해서 열로 배열을 바꿔주면 됨
SORT(범위, 열_정렬, 오름차순, [열_정렬2, 오름차순2, ...])
가끔 번호순, 성적순으로 정렬하면 1,2,3...10,11 순으로 정렬되지 않고, 1,10,11,2,3... 이런식으로 2보다 10이 먼저 오는 경우가 있는데, 이건 원본 데이타가 텍스트로 되어 있어서 생기는 현상임, 원본 데이터를 숫자로 바꿔주면 해결 됨.(서식-숫자)
6. UNIQUE - 중복된 내용을 없애고, 한번씩만 나오게 할 때 사용
중복된 것은 버리고 입력된 원본 범위에서 고유 행을 반환합니다. 원본 범위에 처음 표시되는 순서대로 행이 반환됩니다.
참가자를 받았는데 범위안에 같은 학생이 여러번 신청한 경우 함수를 이용해서 중복되는 이름을 제거하고 한번만 나오게 함. 실제 참가자만 확인할 수 있음.
UNIQUE(범위)
<복합적으로 사용한 예>
=UNIQUE(SORT(TRANSPOSE(offset(A1,match(A2,C2:C915,0),1,1,100)),1,true))
괄호안 부터 해석해 나가면 됨.
match : 성적표에서 A2에 입력된 이름과 일치하는 학생을 C2:C195에서 몇번째 있는지 위치를 숫자로 확인
offset : A1에서 부터 학생의 위치를 이용해서 넓이가 100칸 까지 모든 성적을 가져옴. 범위를 설정
TRANSPOSE : 행(가로)으로 배열된 성적표를 소트하기 위해 열(세로)로 배열(행과열을 바꿈)
SORT : 열(세로)로 배열된 성적을 오름차순으로 정렬
UNIQUE : 정렬한 값중에 중복되는 값들을 제거
<추가> SUMPRODUCT 함수
비용과 횟수를 곱해서 모두 더한값을 계산
비용이 들어가는 행사들을 학생별로 자신이 원하는 행사만 신청한 한 경우, 학생별로 비용을 계산할 때 사용
홍길동 총 참가비 1000X1 + 3000X1 + 500X2 = 5000 을 sumproduct 함수로 한번에 계산
