* 출처 : https://www.hanbit.co.kr/channel/category/category_view.html?cms_code=CMS3203321164
구글 스프레드시트로 엑셀 밟고 칼퇴하자
『일잘러의 비밀, 구글 스프레드시트 제대로 파헤치기』 중
그 누구도 알려주지 않았지만, 알면 너무나도 편리한!
'스프레드시트의 신'만 쓴다는 실무에서 바로 쓰는 치트키 함수 3가지를 소개합니다.
GOOGLETRANSLATE로 번역하기
그림_핵심 함수 | 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열 그룹화]를 선택하여 숨겨놓으면 부장님이 원하는 목차가 완성됩니다.
댓글 없음:
댓글 쓰기