[구글 앱스 스크립트] 2주차 두 번째 : 스프레드시트 필수 함수?

나는 내가 조금 더 거짓말을 잘 하는 성격이었으면 좋겠다 …

오늘 공부를 시작하기 전 각설하고 충격 고백을 하고자 합니다

 

투두몰을 학습하다 보면 말했듯이 기한 내에 정해진 투두(like 미션)을 해야 한다고 했다

그리고 만약에 그 기한을 넘겨버려도 재도전을 할 수 있는 일정 기회가 주어진다. (강의마다 상이한 듯)

그러나 그 재도전 기회마저도 모두 소진하면 강의를 처음부터 재시작 해야한다.

왜 이렇게 잘 아냐구요 … ?

 

네, 맞습니다.

하하

새로운 마음으로 다시 시작하려고 합니다 …

공부 내용은 그래도 이어서 적겠습니다!

미안합니다! 투두몰

 

vlookup, hlookup으로 원하는 값을 찾아내다

말그대로 엑셀을 한번이라도 써봤다면 봤을만한 함수이다. 바로 찾아주는 함수. 두 가지 룩업 루컵 룩업 함수들.

lookup 앞에 붙은 vertical, horizontal 에서 알 수 있듯이 수직선에서 데이터를 찾냐 수평선에서 데이터를 찾냐의 차이이다.

 

요약하자면 '범위' 내에 있는 '검색 키' 값의 '색인' (index) 번째 값을 찾아주는 것이다. vlookup의 예시를 들어보자.

어떤 범위 내에서 이 값을 찾아줘, 하고 부탁한다. 범위 내에서 그 값을 찾고 나서 만약 색인 값이 2라면, 찾은 값의 행 위치에서 2번째 값을 반환해주는 것이다. 설명을 하고자 하면은 무지하게 어려운 내용이지만, 막상 쓰다보면 너무 익숙해져서 물아일체가 된다. 투두몰의 예시를 자세히 보여줄 수는 없으니 궁금하면 결제 … 를 부탁드립니다.

 

hlookup 경우에도 원리는 같다. 다만 정말 두 함수의 차이는 수평선이냐, 수직선이냐의 차이다. VLOOKUP이 같은 행에 놓인 다른 열의 데이터를 불러온다면, HLOOKUP은 같은 열에 놓인 다른 행의 데이터를 불러오는 것이다. 받아오는 값도 검색 키, 범위, 색인으로 같다.

 

마지막 요소값인 [정렬됨]을 false로 하면 완전히 일치하는 값을, true로 하면 가장 비슷한 값의 위치를 알아내준다. 그리고 동일한 범위 내에서 여러 번 찾아야 하는 경우가 있으니 범위 부분은 절대 참조로 적는 것을 매우 추천한다.

 

잠시나마 이미 마스터가 된 기분을 느낄 수 있다

 

셀 범위가 매우 넓으면은?

우리가 색인 값을 설정한다는 건 찾아주는 값이 몇 번째에 있는 지를 알고 있기 때문에 가능한 것이다. 그런데 만약 찾고자 하는 값이 세어보니까 1, 2, 3 … 21번째구나. 이렇게 클 때 일일히 셀 수는 없지 않을까? 그래서 match 함수가 필요하다.

MATCH 함수는 특정 셀의 범위 안에서 검색한 값의 상대적 위치를 반환하는 함수이다. 그래서 우리가 vlookup이나 hlookup 함수를 사용할 때 색인 값에 match 함수를 대신 넣음으로서 좀 더 편하게 함수를 사용할 수 있다. 어쩌면 이번 강의에서 제대로 배우는 함수가 여러 개 쓰인 예시가 아닐까?

위치만 반환하면 돼서, '범위' 내에 찾고자 하는 '검색_키' 값만 입력하면 된다. 여기서 [검색_유형]은 역시 true나 false로 입력하면 되는데, 위에서 본 lookup 함수처럼 완전 일치하는 것을 찾을 지 가장 비슷한 것을 찾을 지에 따라 달라진다.

 

기본적으로 주어지는 실습 시트에 따라, 어렵지 않게 진행할 수 있었다.

 

xlookup이 최종 진화판임을 보여주는 짤

vlookup의 상위호환으로 index / match 함수를 같이 쓰기도 하고, 그 상위에 xlookup이 있다고 한다. 실제로 index / match는 잘 쓰인다고 하는데, 기초적인 수준에서는 vlookup만 알아도 지장이 없으니 궁금하면 따로 검색해보도록 하자.

 

Left, right, find, split로 일정 값만 추출해보기

문자열 데이터를 다루기에 적합한 네 개의 함수다.

left, right는 말그대로 해당 문자열은 왼쪽 / 오른쪽에서 몇 번째 값까지 추출할 건지 정하는 함수이다.

left, right 함수의 각각 설명

 

단순한 문자열 반환 함수같지만 일련번호나 상품명같이 고정된 양식에서 일정 부분만 추출할 때 꽤나 유용하다.

 

이는 find 함수와 함께 응용될 수 있다.

Find 함수는 원본 데이터 안에서 특정 글자가 위치한 곳을 알려주는 함수이다.

left, right 함수로 불필요한 텍스트를 덜어낸 후의 가공된 텍스트에서 특정 문자열을 찾을 수도 있고,

find 함수로 먼저 텍스트 내의 기준점을 조사한 뒤에 left, right 함수로 특정 문자열을 덜어낼 수도 있다.

두 조합을 어떻게 사용하느냐에 따라 적재적소에 사용될 수 있다.

 

split 함수로는 데이터를 특정 기준 텍스트에 맞춰서 나눈 후, 이 나눈 각각의 값을 배열로 보여준다. 만약 문자열 내에 반복되는 문자가 있다면, 게다가 그 문자에 의해 구분이 되는 정보가 있다면 유용한 함수이다. 외운다기 보단 split 함수가 필요한 순간이 있을 때 그 순간에 떠올리는 아이디어가 중요한 함수이다.

 

 

서식 설정하기

같은 값이지만 표기를 다르게 할 수도 있다. 본질은 같지만 보는 방법을 편하게 바꾸는 방법과 같다.

특정 셀을 클릭한 상태로 서식 탭을 클릭하고, 숫자 카테고리 안의 맞춤 숫자 형식을 클릭하면 바꿀 수 있다.

찾아오는 길

아무래도 회계를 다루게 되니 이 강의에서는 맞춤 숫자 형식만을 배웠다.

 

누르면 이렇게 화면이 나타나는데, 쉽게 말해 #은 각 자리수를 나타낸다.

그저 # 하나만 입력하면 소수점이 절삭된 정수만을, #.# 을 입력하면 소수점 일의 자리 밑으로는 절삭된 소수를 반환한다.

그 밑에 #이 늘어나면 같은 원리로 소수점 아래 숫자가 늘어난다.

또한 화폐 단위로 바꾸려면 ￦를 입력하고 시작하면 숫자 앞에 ￦이 붙어서 반환된다. 그 뒤에 숫자를 입력하는 원리는 위와 같다.

잘못 쓰면 이 꼴 나는 거다. 서식을 잘 적용하는 것도 중요하니 주의.

서식 잘 못 쓰면 갑자기 다른 나라 언어를 구사하는 일개 나비족 한 마리가 될 수도 있다.

 

정렬과 필터

엑셀의 꽃이라고 할 수 있는 필터 파트가 왔다. 정렬은 말그대로 오름차순, 내림차순으로 특정 열을 기준으로 정렬하는 것이다.

시트 정렬과 범위 정렬 두 가지가 있는데, 시트 정렬은 현재 보고 있는 시트 전체에 정렬을 적용하는 것이고, 범위 정렬은 선택한 범위 내에서 정렬하는 것이다. 보통은 후자가 많이 쓰이기 때문에, 필요한 범위만 키보드나 마우스로 드래그해서 정렬하는 것이 기본이다.

필터 만들기로 필터를 '걸면' (흔히 엑셀을 쓸 때는 이렇게 표현했다) 각각 요소로부터 특정 값이 들어간 것만 추출하거나, 특정 값 이상인 것만 추출할 수도 있다. 문자열로 조작이 되고, 수치 값으로도 조작이 가능하다는 의미이다.

판매 수량으로 필터를 걸었더니 옆의 아이콘이 바뀌었다.

판매 수량이 30 초과인 것만 걸었더니 잘 나타난다.

---

기본적인 수식을 배웠지만 정말 간단하면서도 이것만 알면 데이터 전처리의 모든 것을 배웠다고 할 만큼 유용했던 함수였다. 군대에서도 적어도 이제까지 배운 vlookup, countifs, left, right 만으로 수많은 장병들의 정보들을 관리했다고 해도 과언이 아니었다. 그만큼 유용했던 함수였고 적절한 실습 시트로 빠르게 배울 수 있던 강의였다.

 

다만 한번 강의 재시도 기간을 놓치고 나니 왜 이 기간이 정해져 있는 지 알 수 있었다. 정말 공부는 연속적으로 이어나가야 잊지 않는 기억의 과정이었다 … 내용이 쉬워서 망정이지 조금만 어려웠으면 해내지 못 할 뻔 했다. 스프레드시트가 엑셀이랑 동일한 부분도 많아서 쉽게 할 수 있는 것도 그 이유였다.

다들 스프레드시트 하는 척 엑셀 하잖아요 그쵸?

스프레드시트와 GAS를 병행하면서 하는 것은 그리 어렵지 않았지만 그저 이 공부를 일상 속에서 이어나가야 한다는 것이 조금은 부담이 됐다. 그래도 꾸준히 해나가야겠지. 관심 분야에 놓여있는 툴이라 포기하지 않고 나아갈 수 있는 이유기도 하다. 파이팅.

나를 똑똑하게 만들어준 투두몰에게 오늘도 박수~

---

위 글은 투두몰 서포터즈 활동의 일환으로 작성된 글입니다.

https://edu.todomall.kr/?utm_source=supporters&utm_medium=contents

투두몰 ㅣ 일잘러의 투두리스트를 훔치다