brunch

매거진 삶의 기술이 있다면

라이킷 13 댓글

You can make anything
by writing

C.S.Lewis

계정을 잊어버리셨나요?

by 일상예술가 정해인 Apr 24. 2023

손가락 덜 쓰는 엑셀팁

나만의 엑셀 노하우

엑셀을 쓴 지도 거의 20년이 되어간다. 아직은 엑셀이 낯선 동생들을 위해 몇 글자 적어보다.

(강의용으로 작성한 내용이라 상세 자료 없이 요약 내용만 올립니다.)

* 피벗 테이블

엑셀에서 가장 많이 쓰는 기능 중 하나다. 월별로 나온 자료를 분기별로 더하고 있었다. 그럴 때는 그룹을 사용하면 훨씬 쉽게 계산 가능하다. 3개월 단위로 더하려면 분기, 연단위로 계산하려면 연으로 그룹화하면 된다.

자료가 바뀌었다면 [새로고침]을 누르자. 피벗은 자동으로 값이 바뀌지 않는다. 기본 데이터 값에서 자료가 변경이 되었다면 피벗에서 마우스 오른쪽 버튼 클릭 후 새로고침을 눌러주면 변경된 값을 반영해서 보여준다.

피벗 내용을 편집하려면 다른 시트에서 값만 붙여 넣자. 피벗에서 보여주는 값은 GETPIVOT~ 이렇게 복잡한 자료로 나온다. 우리가 편집해서 쓰기에는 매우 불편하다. 그럴 때 다른 시트를 열어서 피벗 자료를 값만 복사하여 [선택하여 붙여 넣기] - [값]으로 붙여 넣는다. 그러면 정확하게 값만 붙는다. 다만 서식이 보기 싫다면 피벗에 해당하는 범위를 선택 후 서식 복사를 누르고 붙여놓은 값 위에서 마우스를 클릭해 주면 원래 피벗에서 보여줬던 모양을 그대로 보여준다.

* 카메라

빠른 실행 메뉴라고 위에 보면 저장 버튼이 제일 위에 보인다. 해당 라인에 기능을 추가하면 별도로 기능을 선택하지 않고도 클릭만으로 바로 실행이 가능하다. 내가 주로 세팅하는 기능은 개채 선택과 카메라다. 개체 선택은 엑셀 위에 그려진 개체들을 한 번에 선택할 때 유용한 기능이다. 카메라는 엑셀에 보인 표를 카메라처럼 찍어서 그림으로 변환해 주는 기능이다.

물론 엑셀의 숫자를 그대로 한글로 이동해서 쓰고 싶다면 엑셀 표를 선택 후 한글에서 붙여 넣기를 하면 표가 깨지기는 하지만 숫자들이 붙는다. 이것과 다르게 엑셀에 보이는 범위를 선택 후 카메라 기능을 누르면 마치 화면 캡처를 한 것처럼 그 부분이 그림으로 변환된다. 변환된 그림을 다른 파일이나 문서에 붙여 넣으면 된다. 물론 Window키 + Shift키 + S키를 눌러도 화면캡처 범위를 지정해서 저장하는 것도 가능하다.

* 셀서식

보고서를 쓰다 보면 백만 원 단위나 천만 원 단위로 자료를 쓰게 된다. 물론 함수로 숫자를 나누고 값만 붙여 넣는 것도 가능하지만 숫자는 건드리지 않고 보이는 금액만 바꿀 때 셀서식을 사용한다.

1000000 이렇게 백만 원을 입력하면 뒤에서 0부터 한참을 세야 한다. 그럴 때 셀서식을 #,### 이렇게 넣어주면 ,를 중간중간 찍어준다. 여기에 #,###, 이렇게 콤마를 하나 더 찍어주면 셀에 들어 있는 값은 1백만이지만 보여주는 값은 1,000 이렇게 천 원 단위로 표시된다. 내가 제일 많이 쓰는 백만 원 단위로 하려면 ,를 두 개 찍어주면 된다. #,###,, 콤마가 두 개 붙게 되면 실제 값은 1백만이지만 보여주는 값은 1이 된다.

보여주는 값은 문자가 포함되어 있지만 계산을 하고 싶다면 "문자"를 뒤에 붙여주면 된다. 100일 * 1만 원이란 계산을 하기 위해 엑셀에 "100일", "10,000" 이렇게 입력 후 곱하기를 하면 계산이 되지 않는다. 문자 * 숫자는 값을 계산할 수 없기 때문이다. 그럴 때 0"일" 이렇게 바꿔주면 보여주는 것은 100일이라도 그 안에 들어있는 숫자 100으로 계산하기 때문에 연산이 가능하다.

* 함수

엑셀의 백미라고 하면 단연코 함수를 꼽을 수 있다. 사실 엑셀에서 함수를 쓰지 않으면 굳이 엑셀을 쓰는 의미가 없다고 봐도 된다.

주로 쓰는 if 함수를 설명해 본다.

한 자료에 입금과 출금이 뒤섞여 있다. 필터 기능만으로는 한 열에 있는 자료만 가능할 뿐 2개의 열을 동시에 비교하기는 어렵다. 그런 경우 아래와 같이 함수를 자료 오른쪽 끝에 넣어 비교를 한다.

=INT(IF(K4="", J4/1000000, K4/1000000))

if 함수는 조건을 주고 조건에 맞으면 앞에 값, 안 맞으면 뒤에 값을 넣으란 뜻이다.

예를 들어 If(K4>2, "A", "B") 만약에 K4가 2보다 크다면 A를 쓰고 크지 않다면 B를 쓰라는 이야기다. 내가 작성한 함수로 다시 돌아가자.

IF(K4="", J4/1000000, K4/1000000) K4가 빈칸이면 J4를 백만으로 나누고 빈칸이 아니면 K4를 백만으로 나누란 뜻이다.

그런데 20만을 1백만으로 나누면 0.2가 된다. 소수점 이하는 지저분하니 int로 정수만 남겨준다.