스팀잇에서도 구글시트 덕을 톡톡히 보고 있는 @youngbinlee 빈누입니다.
구글시트 강좌1 IMPORTHTML로 한방에 하는 웹페이지 크롤링
구글시트 강좌2 CONCATENATE 함수로 원하는 웹페이지 주소 만들기
과거 강좌를 보셔야 이후 내용을 진행하실 수 있습니다.
구글 시트란?
구글 시트는 구글에서 제공하는 스프레드시트 프로그램입니다. 마이크로 소프트 엑셀과 거의 동일하지만, 클라우드 상에 파일이 존재하여, 여러 기기에서 접근하거나, 여러 사람이 동시에 수정을 하는 등 엑셀보다 편리한 점이 많이 있습니다.
기존의 엑셀 함수는 대부분 사용이 가능하고, 구글 시트에서만 작동하는 유용한 함수들도 많이 있습니다. 구글 시트를 익혀서 여러분의 생산성을 올려보세요! :) 구글 드라이브 바로가기
오늘의 함수 VLOOKUP
이 함수는 엑셀에서도 쓰이는 함수로, 아마 엑셀을 조금 쓴다 하시는 분이라면 이미 알고 계실만한 함수입니다. 특정 범위의 첫번째 열에서 지정한 키워드를 검색한 뒤, 그 키워드가 있는 동일한 행에 위치한 다른 셀의 값을 가져오는 함수입니다.
이런 학생들의 시험 성적에 대한 테이블이 있을때, "이영빈"을 찾아서 그 행에 있는 "세번째 칸"의 값을 가져오라는 함수가 바로 VLOOKUP 함수입니다.
함수 사용법
VLOOKUP(검색할_키워드, 범위, 색인(몇번째 칸), [정렬됨])
- 검색할_키워드 - 범위의 첫번째 열에서 검색할 값을 넣습니다.
- 범위 - 검색을 수행할 범위입니다.
- 색인 - 첫번째 열을 포함하여 몇번째 열의 값을 반환할지를 설정합니다.
- 정렬됨
- FALSE(혹은 0)로 설정된 경우 완전 일치 값만 반환됩니다. 일치 값이 여러 개인 경우, 처음으로 발견된 값에 해당하는 셀의 콘텐츠가 반환되며, 그런 값이 없는 경우에는 #N/A가 반환됩니다.
- TRUE(혹은 1)이거나 생략된 경우, 근접한 값(검색할 키보다 작거나 같은 값)이 반환됩니다.
- 대부분의 경우는 정확한 데이터를 찾으려고 하는 경우이므로 FALSE나 0으로 입력하면 됨.
활용예
시트에 IMPORTHTML 함수를 활용해 현재 각종 암호화폐의 가격을 불러올 수 있는건 이제 다들 아시죠? 여기에서 vlookup 함수를 이용해 특정 암호화폐의 가격 정보를 불러와봅시다.
=vlookup("XRP Ripple",B:G,3,0)
- "XRP Ripple" - 이 키워드를 검색합니다.
- B:G - B열에서 G열까지를 범위로 지정했습니다. B2:G187과 같은 식으로 특정할 수도 있지만, IMPORTHTML함수를 사용하는 경우에는 데이터의 갯수가 그때그때 달라질 수 있으므로 전체 열을 범위로 잡아두는 것이 안전합니다.
- 3 - B열로부터 3번째 열. 즉, D열에 있는 값을 반환하라는 의미입니다.
- 0 - 검색어가 정확하게 같은 것만 찾습니다.
현재 리플의 가격이 1.13 달러이군요!
오늘의 강좌 포인트 두번째 : 드롭다운 메뉴 만들기
어떤 키워드를 검색하려고 하거나, 웹페이지 주소 만들기 등으로 활용하려고 할때 간혹 문제가 되는 것이 바로 '휴먼 에러'입니다.
예를 들면 coinmarketcap.com/bitcoin 에서 데이터를 가져와야하는데 coinmarketcap.com/bitcoim 이라고 오타를 입력한다던가 하는 식이죠. 스프레드시트가 간단하게 구성되어있을때는 이런 실수를 쉽게 찾아낼 수 있지만, 복잡해지면 복잡해질 수록 이런 사소한 실수가 큰 영향력을 끼칠 수 있습니다. 이런 종류의 문제점은 사전에 차단해두는 것이 좋겠죠?
바로 입력을 할때 사람이 직접 타이핑을 해서 입력하는 것이 아니라 드롭다운 메뉴에서 선택을 해서 입력을 하게끔 해두는 것이 좋은 솔루션이 됩니다.
드롭다운 메뉴의 예
구글 시트에서 드롭다운 메뉴 만드는 법
드롭다운 메뉴를 넣을 셀에서 마우스 우클릭을 한 뒤, 메뉴 가장 하단에 있는 '데이터 확인'을 클릭.
데이터 확인 메뉴에서 '범위에서의 목록'을 선택하고, 드롭다운에 포함될 데이터가 있는 범위를 선택해주면 됩니다.
시트 한편에 아래의 10개의 암호화폐 이름을 복사한뒤, 해당 범위를 선택해봅시다.
Bitcoin
Ethereum
Bitcoin Cash
Ripple
Litecoin
Cardano
IOTA
Dash
NEM
Monero
저장 버튼을 누르면 해당 셀에는 아래와 같은 드롭다운 메뉴를 나타내는 화살표가 생기게 됩니다.
화살표를 클릭하면 지정해둔 값들 중에 선택할 수 있게 출력됩니다.
VLOOKUP과 드롭다운 메뉴의 콜라보 예제
이제 2가지 기능을 함께 활용한 예제를 하나 보여드리겠습니다.
| Name | Symbol |
|---|---|
| Bitcoin | BTC |
| Ethereum | ETH |
| Bitcoin Cash | BCH |
| Ripple | XRP |
| Litecoin | LTC |
| Cardano | ADA |
| IOTA | MIOTA |
| Dash | DASH |
| NEM | XEM |
| Monero | XMR |
이렇게 생긴 암호화폐의 이름과 그 심볼로 이루어진 테이블이 있다고 합시다. 데이터를 가져오기 위해서는 심볼을 활용해야하지만, 심볼을 다 외우는건 쉬운일이 아니겠죠? 그래서 사람들이 읽으면 알 수 있는 '이름'을 선택하면 '심볼'이 저절로 채워져서, 그 이후의 작업이 진행될 수 있도록 해두는 것이 좋습니다.
제가 주식 관련 템플릿을 작업할때 비슷한 기능을 많이 활용했는데, 삼성전자는 알아도 삼성전자의 종목코드인 005930을 외우는 사람은 적기 때문에 종목명을 입력하면 종목코드가 알아서 입력되는 기능이 필요했습니다. 암호화폐 혹은 다른 종류의 데이터를 처리할때도 비슷하겠죠?
하는 순서는 어렵지 않습니다. 앞서서 소개한 드롭다운 메뉴를 통해 이름을 선택할 수 있게 만든 뒤, 그 선택된 값을 VLOOKUP 함수를 이용해서 찾아오면 됩니다.
F1 셀에 드롭다운 메뉴를 만들었습니다. 방법은 이제 다 아시죠? 셀에서 마우스 우클릭 >> 데이터 확인 >> 데이터로 사용할 목록(여기에서는 B2:B11)을 선택하시면 됩니다.
=vlookup(F1,B2:C11,2,0)
G1 셀에는 위와 같은 함수를 사용해서 F1의 값에 따라 심볼을 불러올 수 있게 만듭니다.
숙제
여기까지 다들 잘 따라오셨나요? 오늘의 숙제를 위해서는 구글 시트에서 한개의 부가기능을 설치해주셔야 합니다. 바로 cryptofinance라는 부가기능인데요. 간단하게 암호화폐의 가격 정보를 불러올 수 있는 부가기능입니다.
메뉴 >> 부가 기능 >> 부가기능 열기 >> cryptofinance 검색하신 뒤 설치하시면 됩니다.
설치한 뒤에는
=cryptofinance("BTCUSD")
=cryptofinance("SBDKRW")
등 암호화폐의 심볼과 국가별 통화의 심볼을 합쳐서 현재 가격을 불러올 수 있습니다.
이번 숙제는 암호화폐의 이름과 통화 등을 선택하면 그것에 맞게 현재 가격을 불러오는 시트를 만들어보는 것입니다. 제가 요거 만들때 썼던 것인데, 여러분도 이제 충분히 하실 수 있답니다.
결과물은 요렇게 나오면 됩니다. 이번 숙제는 지난번 숙제들보다는 조금 더 시간이 필요할 것 같아서, 숙제 댓글로 남겨주시는 분들께는 풀보팅 해드릴게요! >ㅁ<
