재무담당자가 즐겨 쓰는 엑셀 꿀팁은?
[부록] 오직 재무담당자를 위한 팁 (2)
(스위치 매거진 마지막 글입니다. 제 글을 읽어주셔서 감사합니다. ^_^)
재무 담당자가 가장 많이 쓰는 업무툴 부동의 1위는 역시 엑셀이 아닐까 싶습니다. 엑셀로 일하는 시간이 많다 보니, 엑셀 기능 몇 가지만 잘 구사해도 업무 시간이 확 줄어듭니다. 아마 잘 아시는 분들도 많겠지만, 모르는 분들도 분명히 있을 테니, '퇴근시간을 당겨주는 엑셀 꿀팁' 열 가지만 추려서 공유드릴게요. 단축키 위주로 설명을 드릴 텐데요, 키를 동시에 눌러야 하는 건 'Ctrl + F', 키를 차례대로 누르는 건 'Alt → W → N' 이런 식으로 표시할 테니 참고해 주세요~
1. 새창 열기 Alt → W → N
여러 개의 Sheet가 있는 파일의 경우, 그 Sheet를 왔다 갔다 하면서 봐야 해서 무척 불편할 때가 있습니다. 이때 이 기능을 사용해 보세요. 마치 윈도우 창이 하나 더 열리듯이 똑같은 파일 화면이 하나 더 열립니다. 그럼 여러 개의 Sheet를 동시에 볼 수 있어요. 물론 같은 파일에서 작업하는 것이기 때문에 한쪽 창에 수식 입력을 하면 다른 창에서도 똑같이 입력이 됩니다. 특히 여러 개 Sheet에 있는 셀에서 수식을 걸 때 매우 편리한 기능입니다.
2. 셀 내용 바꾸기 Ctrl + F
여러 데이터 값을 넣다 보면, 입력값을 수정하고 싶을 때가 있습니다. 물론 한두 개가 수정되면 그냥 다시 쓰면 그만이지만, 여러 개를 한 번에 고쳐야 한다면? 이걸 하나하나 고치고 있으면 엄청 시간이 걸리겠죠? 보통 Ctrl + F는 값을 찾는 용도로 많이 쓰지만, 데이터를 바꿀 때 강력한 힘을 발휘하는 기능입니다. 아래 표에서 O사라고 쓰인걸 O업체라는 형식으로 바꿔주고 싶다면, 수정을 원하는 셀 범위를 지정한 후 Ctrl + F를 눌러주고 찾을 내용과 바꿀 내용을 각각 입력해 줍니다. 그리고 '바꾸기'를 누르면 셀 하나씩 차례대로 변경하고, '모두 바꾸기'를 누르면 일괄 변경됩니다. 서식(M)이라는 버튼도 보이실 텐데 여기를 눌러주시면 파란 글자, 볼드체, 노란색 채우기 같은 서식도 마음대로 일괄 변경됩니다.
이 기능이 유용한 건 함수를 일부 수정할 때도 사용 가능하다는 점입니다. 만약 =SUM(F3:F9)이라는 수식을 넣었는데 =SUM(G3:G9)라고 고치고 싶다? 그런데 이렇게 고쳐야 할 셀이 한두 개가 아니다? 그럼 이때 바꾸기 기능을 이용해서 F를 G로 바꿔주면 됩니다. 다만 SUM이라는 함수값이 있는데 S, U, M 글자를 바꿔주게 되면 수식 자체가 어그러질 수 있으니 이 점만 유의해 주세요!
2021.03.05 형식으로 되어 있는 날짜값을 2021-03-05 형식으로 일괄 변경할 때도 무척 편리합니다.
3. 틀 고정하기 Alt → W → F → F
방대한 표를 보다 보면, 첫 행이나 첫 열은 고정해두고 나머지 값들을 보고 싶을 때가 있죠. 이때 틀 고정 기능을 사용하면 편합니다. 틀 고정 기준으로 두고 싶은 셀을 찍은 뒤, Alt → W → F → F를 차례대로 눌러보세요. 그러면 클고정이 되면서 그 기준 셀 위쪽과 왼쪽은 계속 보이게 됩니다. Alt → W → F → F를 한번 더 누르면 틀 고정은 취소되고요. 셀을 기준으로 하지 않고, 열 전체나 행 전체를 기준으로 하는 것도 가능합니다. 그냥 무조건 첫 번째 행을 고정하고 싶다면 Alt → W → F → R, 무조건 첫 번째 열을 고정하고 싶다면 Alt → W → F → C를 누릅니다.
4. 필터 만들기 Ctrl + Shift + L
방대한 표를 보다 보면, 내가 보고 싶은 데이터만 걸러서 보고 싶을 때가 있습니다. 이때 필터 기능을 사용합니다. 필터를 걸고 싶은 범위를 지정하고 Ctrl + Shift + L을 살포시 눌러줍니다. 그럼 필터가 생겨요. 위 예시에서 거래처 A사, B사, C사의 데이터만 보고 싶다면 거래처의 필터 모양을 누른 후 해당 값만 눌러줍니다. 그러면 A사, B사, C사의 데이터만 보이게 됩니다. *를 잘 쓰면 더 편리하게 이용할 수 있는데요, '*사'라고 입력하면 앞에 무슨 글자가 있든 상관없이 뒤에 '사'가 있으면 모두 필터 걸 수 있습니다. 반대로 *를 뒤에 붙이는 것도 가능하죠.
또 이 필터 기능을 이용하면 텍스트 오름차순, 내림차순으로, 또는 색이나 텍스트 기준으로 정렬하는 것도 가능합니다. 혹은 특정 색이나 텍스트만 보이게 할 수도 있어요.
5. 보이는 값만 더하기 (subtotal 함수)
필터 기능을 사용하다 보면, 필터로 걸러진 값만 더해보고 싶을 때가 많죠. 그런데 그냥 흔히 쓰는 SUM 함수를 사용하면 필터에 가려진 모든 값까지 다 계산이 됩니다. 이때 subtotal 함수를 이용하면 편리합니다.
거래처가 A사부터 G사까지 있지만 A사와 G사의 데이터만 출력했습니다. 그리고 공급가액을 더해보고 싶어요 그냥 =sum(C3:C9)라고 치면 필터에 가려 있는 숫자까지 다 더해진 값이 나와요. 그렇다고 C3+C9로만 해도 되겠지만 더할 값이 많아지면 힘들어지겠죠? 그럴 때는 =subtotal(9,C3:C9)라고 입력해 봅시다. 그럼 화면에 보이는 값만 더해서 계산해 줍니다!
6. 값복사, 수식복사, 서식복사 'Ctrl + C' → 'Ctrl + Art + V'
보통 데이터를 복사해서 붙을 때 Ctrl + C에 이은 Ctrl + V는 정말 많이 쓰죠. 이건 데이터 내용 전부를 그대로 옮긴다는 뜻이 내포되어 있는데, 사실 그 데이터 내용에는 데이터 값뿐만 아니라 수식과 서식까지 모두 포함되어 있습니다. 엑셀에 이런 표가 있다고 해볼게요.
여기서 부가세가 입력된 D3 셀에는 5240이란 숫자를 넣은 게 아니라 공급가액의 10%, 즉 =C3*10% 라는 수식이 들어가 있습니다. 만약 이 D3 셀에 Ctrl + C, Ctrl + V를 하면 그대로 복사가 될 거예요. 그런데 이 D3 셀의 값만 복사하고 싶거나, 수식만 복사하고 싶거나, 서식만 복사하고 싶을 때 이 기능을 이용합니다.
먼저 복사하고 싶은 범위를 지정하고 Ctrl + C를 누릅니다. 그리고 그 값을 옮기고 싶은 셀을 지정해 준 다음, Ctrl + V가 아닌, Ctrl + Art + V를 눌러주세요. 그럼 새로운 창이 하나 뜨는데, 여기서 수식복사를 하고 싶으면 f, 값복사를 하고 싶으면 v, 서식복사를 하고 싶으면 t를 눌러주고 확인을 누릅니다.
이 창을 보시면 아래쪽에 '행/열 바꿈'을 선택할 수 있는데, 행과 열의 위치를 한 번에 바꿔줄 수 있어서 무척 편리한 기능이에요.
7. 그룹 설정하기 Ctrl + Art + →
특정 행 범위 또는 특정 열 범위를 숨기고 싶을 때가 있죠. 그럴 때 그냥 숨겨 놓으면 어디서 숨겨져 있는지 한 번에 찾기 어려울 때가 있어요. 이때 버튼 한 번으로 숨기고서 그 숨겨진 곳을 표시해주는 기능이 있습니다. 그룹 설정하기 기능이에요.
먼저 숨기고 싶은 행이나 열의 범위를 지정해 줍니다. 그리고 Ctrl + Art + →를 동시에 눌러주세요. 그럼 위에 줄 같은 표시가 생기면서 그룹이 설정됩니다. 그 줄 오른쪽 제일 끝 네모를 눌러주면 한 번에 쫙 숨겨집니다. 이 그룹은 몇 개든 동시에 만들 수 있어요. 만약 이 그룹 설정을 풀고 싶다면 Ctrl + Art + ←를 동시에 눌러주면 됩니다.
8. 텍스트 나누기 Alt → A → E
2015.01.12에서 '.'처럼 일정한 기호가 들어가 있는 경우, 그 기호를 기준으로 셀을 나누고 싶을 때가 있습니다. 2015.01.12가 한 셀에 있던 것을 2015 / 1 / 12 이렇게 3개의 셀로요. 구분할 수 있는 기호 같은 것만 공통으로 들어가 있다면 간단하게 쪼갤 수 있습니다. 먼저 셀을 쪼개고 싶은 범위를 지정한 뒤 Alt → A → E를 차례대로 눌러줍니다. '구분 기호로 분리됨'을 선택하고 다음으로 넘어가요.
만약 구분 기호가 '.'이라면 기타에 '.'를 넣어줍니다. 구분 기호가 탭, 세미콜론(;), 쉼표(,) 또는 공백이라면 거기 맞는 구분 기호를 선택하면 됩니다. 그럼 셀이 어떻게 쪼개질지 미리 보여줍니다. 그리고 다음으로 넘어가면...
이렇게 셀이 나누어집니다!
9. Sheet 통째로 복사하기
엑셀 작업을 하다 보면 Sheet 하나를 통째로 복사해서 다른 파일로 옮겨야 할 때가 있습니다. 그럴 때 그냥 Ctrl + A를 누르거나, 1행과 A열 사이에 있는 끄트머리를 누르고 전체 선택을 해서 복사해 옮기는 경우가 많습니다. 그러면 기존 파일의 서식이 어그러지거나 행 높이나 열 너비가 흐트러져서 다시 조정해줘야 하는 불편함이 생길 수 있어요. 그래서 Sheet를 통째로 옮겨서 복사하는 방법을 쓰면 편합니다.
그 Sheet 가장 아래를 보면 Sheet명이 보이는데요, 이곳을 마우스 오른쪽 버튼으로 클릭해줍니다. 그러면 '이동/복사'를 선택할 수 있어요.
현재 열려있는 엑셀 파일 중 하나로 Sheet를 복사할 수 있습니다. '복사본 만들기'를 체크해줘야 복사가 되고, 만약 여기를 체크하지 않으면 현재의 파일에서는 삭제되면서 Sheet가 옮겨가게 됩니다. 여기서는 '거래처 현황'이란 파일로 복사해 볼게요. 그럼 거래처 현황 파일로 넘어온 걸 확인할 수 있습니다.
10. 참조열에서 특정값 가져오기 (vlookup 함수)
워낙 유명한 함수인만큼 재무 담당자라면 반드시 알아야 할 함수여서 마지막 팁은 vlookup 함수로 선정해 봤습니다.
거래처의 공급가액, 부가세 등의 데이터가 나열된 표에서도 A사와 G사의 매출채권만 따로 표시하고 싶다고 해볼게요. 그럼 해당 값이 산출되는 셀에다가 이렇게 수식을 넣습니다.
=vlookup(기준셀, 참조 데이터 범위, 참조 데이터의 첫 열로부터 원하는 값의 위치순서,)
예시를 통해 설명드리면 '기준 셀'은 값을 가져올 기준값을 의미합니다. 표에서 A사 오른쪽에 나열된 데이터에서 값을 가져오겠다는 의미예요. '참조 데이터 범위'는 참조할 표의 전체 범위를 지정해줍니다. 범위를 지정하고 F4를 눌러주면 데이터 범위가 딱 고정되니 필요하면 사용해주세요. 그리고 참조 데이터의 첫 열로부터 원하는 값의 위치 순서를 지정해주는데요, 매출채권 값은 거래처를 첫 열이라 보면 네 번째 열에 위치하니까 '4'라고 기입해 줍니다. 이렇게 함숫값을 넣어주면 원하는 값이 나옵니다. 행이 수백수천 개나 되고 원하는 값을 찾기가 쉽지 않을 때 vlookup 함수를 잘 이용하면 몇 초 만에 뚝딱 찾아낼 수 있어서, 재무 담당자들이 가장 애정 하는 함수 중의 하나입니다!
