한빛출판네트워크

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

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

GOOGLETRANSLATE로 번역하기

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

GOOGLETRANSLATE의 기본 사용법

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

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

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

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

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

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

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

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

SPARKLINE으로 소형 차트 만들기

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

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

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

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

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"}))

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

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

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

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

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

02 부장님이 좋아하실 것 같은 총천연색 이미지가 셀 안에 입력되었습니다. 셀에 입력된 이미지 수식으로 가져올 수 있습니다.

03 [목차] 시트에서 D열과 E열에 걸쳐 있는 문자와 번호에 따라 이미지를 가져오기 위해 [B3]셀에 =ARRAYFORMULA(IF($D$3:$E<>"",VLOOKUP($D$3:$E,IMAGE!$B$6:$D,3,0),""))를 입력합니다.

04 번호에 해당하는 그림이 삽입되었습니다. 기존 번호가 있는 D, E열을 선택하고 마우스 오른쪽 버튼을 클릭한 후 [D~E열 그룹화]를 선택하여 숨겨놓으면 부장님이 원하는 목차가 완성됩니다.