구글 스프레드시트로 자동 번역하는 방법(실무 치트키 함수 3가지 공개!)

한빛미디어

2021-05-24

by 강남석

페이스북 퍼가기 트위터 퍼가기

55,405

구글 스프레드시트로 엑셀 밟고 칼퇴하자

『일잘러의 비밀, 구글 스프레드시트 제대로 파헤치기』 중

구글 스프레드시트에 번역 함수, 셀 하나에 쏙 들어가는 소형 차트 그리기 함수, 숫자를 이미지로 바꿔주는 함수가 있다는 사실, 알고 계셨나요?

그 누구도 알려주지 않았지만, 알면 너무나도 편리한!
'스프레드시트의 신'만 쓴다는 실무에서 바로 쓰는 치트키 함수 3가지를 소개합니다.

GOOGLETRANSLATE로 번역하기

00 일잘러의 비밀 스프레드시트 제대로 파헤치기_GOOGLETRANSLATE_함수 예제_텍스트 번역.PNG 그림_핵심 함수 | GOOGLETRANSLATE

구글 번역기의 성능은 갈수록 좋아지고 있습니다. 구글 스프레드시트에도 구글 번역기가 함수로 들어와 있습니다. 대량의 외국어 데이터를 함수를 이용해서 간단하게 이해 가능한 수준의 한국어로 바꾸거나 한국어를 간단한 외국어로 바꿀 수 있습니다.

GOOGLETRANSLATE의 기본 사용법

01 [시트1] 시트의 [A5]셀에 수식 =GOOGLETRANSLATE(A1)를 입력합니다. ‘출발어’ 인수는 대부분 넣지 않아도 잘 인식하며, ‘도착어’ 인수는 구글 계정의 기본 언어로 지정됩니다.

01 일잘러의 비밀 스프레드시트 제대로 파헤치기_GOOGLETRANSLATE로 번역하기.PNG

02 [A5:A7] 범위를 선택한 후 Ctrl + D 를 눌러서 [A5]의 수식을 나머지 셀에 붙여넣습니다.

03 번역의 품질이 썩 좋지는 않습니다. 구글 번역 사이트와 비교해보면 번역 품질의 차이가 확연히나는 것을 알 수 있습니다. 하지만 스프레드시트의 함수는 단순 반복 작업에서 강점을 발휘합니다.

03 일잘러의 비밀 구글 스프레드시트 제대로 파헤치기_GOOGLETRANSLATE로 번역하기.PNG

GOOGLETRANSLATE로 여러 언어 한꺼번에 번역하기

GOOGLETRANSLATE는 많은 언어로 한꺼번에 번역할 필요가 있거나 생소한 언어들을 한글로번역할 때 유용하게 사용할 수 있습니다.

01 [시트2]의 [D4]셀에 수식 =GOOGLETRANSLATE($D$3,$C$3,$C4)를 입력합니다. [C4]셀에 입력된 언어로 [D3]셀의 내용을 번역하는 수식입니다.

05 일잘러의 비밀 구글 스프레드시트 제대로 파헤치기_GOOGLETRANSLATE로 여러 언어 한꺼번에 번역하기.PNG

02 [D4:D15] 범위를 선택한 후 Ctrl + D 를 눌러 [D4]셀의 수식을 나머지 셀에 붙여넣습니다.

06 일잘러의 비밀 구글 스프레드시트 제대로 파헤치기_GOOGLETRANSLATE로 여러 언어 한꺼번에 번역하기.PNG

03 어떤 언어로 번역된 것인지 확인하기 위해 [E3]셀에 수식 =DETECTLANGUAGE($D3)를 입력하고 자동 채우기 핸들을 더블클릭하여 나머지 셀에도 수식을 붙여넣습니다. 번역된 글을 이해할 수는없지만 구글은 번역된 언어를 제대로 인식하고 있습니다.

07 일잘러의 비밀 구글 스프레드시트 제대로 파헤치기_GOOGLETRANSLATE로 여러 언어 한꺼번에 번역하기.PNG

SPARKLINE으로 소형 차트 만들기

00 일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형차트.PNG

GOOGLEFINANCE에서 잠깐 살펴본 것처럼 SPARKLINE은 한 셀에 소형 차트를 그려주는 함수입니다. 일련의 데이터마다 소형 차트를 그리면 추세나 성향을 파악하기 편합니다. 엑셀에는 메뉴로 스파크라인을 추가할 수 있지만, 구글 스프레드시트에서는 함수로 지원합니다. 지원하는 형태는 선 그래프, 누적 막대 그래프, 열 차트, 승패 그래프이며 옵션을 지정하지 않으면 기본값인 선 그래프가 그려집니다.

00 SPRAKLINE의 종류.PNG

여기서는 SPARKLINE 함수를 통해 현장별 자금 회수 스케줄을 열 차트로 표시하고, 목표공정과 실공정을 비교하는 누적 막대 그래프를 만들어보겠습니다.

01 [실습] 시트의 [O4]셀에 수식 =SPARKLINE($C4:$N4,{"charttype", "column";"highcolor","red";"axis",true})를 입력합니다. 열 차트로("charttype", "column") 최대값을 빨간색으로 표시("highcolor","red")하고 전체 기간의 길이를 함께 보기 편하도록 X축을 표시("axis",true)했습니다.

01 일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형 차트.PNG

01 여기서 잠깐_일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형 차트.PNG

02 나머지 범위에도 동일한 수식을 입력하기 위해 [O4]셀을 선택하고 자동 채우기 핸들을 더블클릭합니다.

02 일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형 차트.PNG

03 공정률을 비교하는 누적 막대 그래프를 만들어보겠습니다. 목표 초과 달성 시 초과달성분(실적 - 목표)은 파란색, 목표는 오렌지색으로, 목표 미달성 시 미달분(목표 - 실적)은 빨간색, 실적은 오렌지색으로 표시하겠습니다. 100% 대비 공정률을 비교할 수 있도록 최대값을 100%로 잡습니다. 조건에 따라 표시할 데이터와 색상 옵션이 달라지므로 SPARKLINE을 2개 쓰고 IF 절로 조건에 따라 각각을 선택합니다. [R4]셀에 다음 수식을 입력합니다. SPARKLINE의 데이터 인수를 중괄호 배열로 넣어주었습니다.

=IF($Q4>$P4,
SPARKLINE({$P4,$Q4-$P4},{"charttype","bar";"max",1;"color1","orange";"color2","blue"}),
SPARKLINE({$Q4,$P4-$Q4},{"charttype","bar";"max",1;"color1","orange";"color2","red"}))

03 일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형 차트.PNG

04나머지 범위에도 동일한 수식을 입력하기 위해 [R4]셀을 선택하고 자동 채우기 핸들을 더블클릭합니다.

04 일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형 차트.PNG

04 여기서 잠깐_1_일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형 차트.PNG

04 여기서 잠깐_2_일잘러의 비밀 스프레드시트 제대로 파헤치기_SPARKLINE_함수 예제_소형 차트.PNG

IMAGE로 숫자를 그림으로 바꾸기

00 일잘러의 비밀 스프레드시트 제대로 파헤치기_IMAGE_함수 예제_숫자를 그림으로 바꾸기.PNG

IMAGE 함수는 셀 안에 이미지를 함수로 넣을 수 있게 합니다. 메뉴 기능이 아닌 함수이므로 규칙에 맞춰 많은 이미지를 한꺼번에 넣을 수 있습니다. 이렇게 불러온 이미지는 셀 안에서 일종의 값으로 취급되므로 VLOOKUP과 같은 함수로 불러올 수 있습니다.

부장님께 사내 교육용 구글 스프레드시트 교재의 초안을 보고드렸더니 내용에 대한 피드백은 안 주시고 목차의 번호가 너무 밋밋하다며 알록달록한 그림 문자로 바꾸라고 하십니다. IMAGE 함수를 사용하여 수정해봅시다

01 다아이콘을 제공하는 사이트(https://www.flaticon.com/packs/alphabet-andnumbers-20)에서 IMPORTXML 함수로 번호에 사용할 문자와 이에 대응하는 무료 이미지의URL을 [IMAGE] 시트에 크롤링해두었습니다. 이미지 파일의 URL로 이미지를 불러오기 위해 [D6]셀에 =ARRAYFORMULA(IMAGE($C$6:$C$61))를 입력합니다.

02 일잘러의 비밀 스프레드시트 제대로 파헤치기_IMAGE_함수 예제_숫자를 그림으로 바꾸기.PNG