공무원 실무 엑셀, Ctrl + E부터 파일 취합까지

전화번호 같은 개인 정보 가리기

전화번호나 이메일 주소, 사는 곳 같은 민감한 데이터는 그대로 올리면 안 되기 때문에, 전화번호 가운데 네 자리를 지우는 식으로 가려서 올려야 할 때가 많습니다. 이런 작업은 함수로 처리하는 경우가 많지만, 원하는 결과를 한 번만 입력해 두면 단축키 하나로 나머지를 채울 수 있습니다.

1. 가릴 데이터 옆의 빈 열 첫 번째 셀에 원하는 결과를 한 번만 직접 입력합니다. 이번 실습에서는 전화번호 가운데 네 자리를 별표로 바꿔 010-****-5479 형태로 입력했습니다. 엑셀은 이렇게 입력한 예시 하나를 보고 나머지 셀에 적용할 규칙을 파악합니다.
   

Ctrl + E 로 나머지 셀을 한 번에 채우기

첫 셀에 결과를 입력했다면 Ctrl + E 로 나머지 셀을 채웁니다. 쉽다는 뜻의 영어 단어 Easy 의 첫 글자 E 로 기억해 두면 편리합니다.

1. 첫 셀에 결과를 입력한 후 Ctrl + E 를 누르면, 입력한 형태대로 나머지 셀이 한 번에 채워집니다.
   

이름 상자로 견적서 입력 범위 초기화하기

견적서처럼 같은 양식을 새로 발급해야 할 때는 입력된 값을 하나씩 지우고 다시 입력하게 됩니다. 지울 범위에 이름을 붙여 두면 다음부터는 그 범위를 한 번에 선택해 지울 수 있습니다.

1. 견적서에서 초기화할 범위를 선택합니다. 그냥 클릭하면 범위가 하나씩만 선택되므로, Ctrl 키를 누른 채 클릭해 떨어져 있는 여러 범위를 동시에 선택합니다.
   
2. 범위가 선택된 상태에서 왼쪽 위의 이름 상자에 범위 이름을 입력합니다. 이번 강의에서는 이름을 [초기화범위]로 지정했습니다.
   
3. 이후, 이름 상자에서 등록한 이름을 선택하면 지정했던 범위가 한 번에 선택됩니다. 그 상태에서 값을 지우면 입력된 내용이 한 번에 지워집니다.
   
   
   오빠두Tip : 이름으로 등록한 범위는 다른 시트에서 작업하다가 이름 상자에서 선택해도 같은 범위가 선택됩니다.

예산 금액을 천원·백만원·만원 단위로 바꾸는 방법

예산 작업에서는 금액이 몇억, 몇십억 단위로 커지다 보니 모든 숫자를 그대로 표시하면 읽기 어려워, 원·천원·백만원 단위로 절삭해 보여줄 때가 많습니다. 금액 단위를 바꾸는 방법은 입력된 실제 값 자체를 나누는 방법과, 실제 값은 그대로 두고 겉으로 보이는 서식만 바꾸는 방법 두 가지가 있습니다.

1. 수량에 "12EA" 처럼 단위가 붙어 있으면 숫자가 아니라 문자로 입력된 것이라 단가와 곱해도 계산되지 않습니다. 이럴 때는 옆의 빈 열 첫 셀에 숫자 12만 입력하고 Ctrl + E 를 눌러 숫자만 채운 뒤, 채운 값을 잘라내기 해 수량 자리로 옮깁니다.
   
2. 이후, 아래와 같이 수량과 단가를 곱하는 수식을 입력해 금액을 구합니다.

   =수량*단가

   

3. 금액에 0이 많아 얼마인지 읽기 어렵다면 천 단위 구분 기호를 넣습니다. 금액 범위를 선택해 우클릭한 뒤 셀 서식을 클릭하고, 아래쪽의 사용자 지정에서 형식에 #,##0 을 입력합니다.
   
   
   오빠두Tip : 사용자 지정 서식 목록은 복잡해 보이지만 반복해서 나오는 #,##0 만 알아 두면 됩니다. 0 은 숫자만 표시하고, 그 앞에 #,## 를 붙이면 천 단위 구분 기호가 들어갑니다.
4. 먼저 실제 값을 나누는 방법입니다. 빈 셀에 1000 을 입력해 복사한 뒤, 나눌 금액 범위를 선택합니다.
   
5. 선택한 범위를 우클릭해 복사한 값을 선택해서 붙여넣는 옵션을 열고, 연산 항목에서 나누기를 선택하면 금액이 1000 으로 나눠집니다.
   
   
   오빠두Tip : 실제 값을 나누면 그 금액으로 다시 계산할 때마다 1000 을 곱해야 해서 매번 신경을 써야 합니다. 금액 단위만 바꿔 보여 주고 싶을 때에는 값은 그대로 두고 서식만 바꾸는 방법을 추천드립니다.
6. 이제 값은 그대로 두고 서식만 바꾸는 방법입니다. 금액 범위를 선택하고 우클릭해 셀 서식을 연 뒤, 사용자 지정 형식의 맨 뒤에 쉼표(,)를 추가합니다. 쉼표를 하나 넣으면 천원, 두 개 넣으면 백만, 세 개 넣으면 십억 단위로 절삭되어 표시됩니다.
   
7. 필요에 따라 형식을 0.0 으로 바꾸고 쉼표 뒤에 단위를 붙여 표시할 수 있습니다. 쉼표 하나 뒤에 K 를 붙이면 천 단위, 쉼표 두 개 뒤에 백만을 붙이면 백만 단위로 표시됩니다.
   0.0,"K" / 0.0,,"백만"

   

8. 국내에서는 만원이나 억 단위를 많이 쓰지만, 기본 서식의 쉼표로는 천원과 백만원 단위만 만들 수 있습니다. 이러한 경우 아래와 같이 만원 단위 셀 서식을 사용합니다.
   0"."0,"만원"

   
   

   오빠두Tip : 마침표를 큰따옴표 없이 입력하면 실제 소수점으로 동작합니다. 만원 단위 서식을 만들 때에는 마침표를 반드시 큰따옴표로 묶어 문자로 입력합니다.

워드 와일드카드로 이름을 한 번에 바꾸기

출장자 명단에서 정해인을 정○○ 형태로 모두 바꿔야 할 때, 이름을 하나씩 바꾸면 자료가 많을수록 시간이 오래 걸립니다. 이럴 때는 이름을 워드로 옮겨 찾기 및 바꾸기의 와일드카드 검색으로 한 번에 바꿀 수 있습니다.

1. 엑셀에서 바꿀 이름이 입력된 범위를 복사해 워드에 붙여넣습니다.
   
2. 워드에서 Ctrl + H 를 눌러 찾기 및 바꾸기의 바꾸기를 실행한 뒤, 자세히를 클릭해 옵션을 열고 와일드카드 검색에 체크합니다.
   
   
   오빠두Tip : 와일드카드 검색은 정해진 글자가 아니라 한글로 시작하는 세 글자처럼 규칙을 정해 패턴으로 찾는 방법입니다. 이름처럼 글자가 제각각이라 규칙이 일정하지 않을 때 사용합니다.
3. 찾을 내용과 바꿀 내용에 아래 패턴을 각각 입력합니다. 한글은 가부터 힣까지이므로 [가-힣] 은 한글 한 글자를 뜻하고, 첫 글자를 괄호로 묶은 뒤 [가-힣]{2} 로 두 글자를 더 찾으면 한글 세 글자 이름을 찾습니다. 바꿀 내용의 \1 은 괄호로 묶은 첫 번째 항목을 그대로 출력하므로, 첫 글자는 남기고 뒤의 두 글자만 ○○ 으로 바뀝니다.
   찾을 내용: ([가-힣])[가-힣]{2}
   바꿀 내용: \1○○

   

4. 이제 모두 바꾸기를 클릭하면 한글 세 글자 이름이 한 번에 바뀝니다. 바뀐 이름은 다시 복사해 엑셀로 옮깁니다.
   
5. 엑셀 안에서 바로 처리하고 싶다면 정규표현식으로 값을 바꿔 주는 REGEXREPLACE 함수를 사용할 수 있습니다. 이름 범위와 앞에서 사용한 패턴을 넣어 아래와 같이 함수를 입력하면 이름이 바뀌고, 원본 이름을 수정하면 결과도 함께 업데이트됩니다.

   =REGEXREPLACE(이름범위,"([가-힣])[가-힣]{2}","$1○○")

   
   

   오빠두Tip : REGEXREPLACE 함수는 엑셀 Microsoft 365 버전에서 제공됩니다. 이 버전을 사용하지 않을 때에는 앞에서 소개한 워드의 와일드카드 검색으로 같은 작업을 할 수 있습니다.

폴더 속 엑셀 파일을 클릭만으로 취합하기

여러 명이 보낸 엑셀 파일을 취합할 때 파일마다 데이터를 잘라내기 해 아래에 붙여넣는 작업을 반복하는 경우가 많습니다. 머리글 같은 데이터의 구조가 같다면, 키보드 없이 마우스 클릭 열 번으로 폴더 안의 파일을 하나로 합치고 새 파일이 들어왔을 때 업데이트까지 할 수 있습니다.

1. 취합할 엑셀 파일을 하나의 폴더에 준비합니다. 이번 실습에서는 영업팀별 매출 파일이 담긴 매출 자료 폴더를 사용했습니다.
   
2. 엑셀에서 데이터 탭 -> 데이터 가져오기 -> 파일에서 -> 폴더에서를 차례로 클릭합니다.
   
3. 취합할 파일이 들어 있는 폴더를 선택하고 열기를 클릭하면 폴더 안에 있는 엑셀 파일 목록이 표시됩니다. 목록에 취합할 파일이 모두 있는지 확인합니다.
   
4. 이제 결합을 클릭하고 결합 및 로드를 선택합니다.
   
   
   오빠두Tip : 폴더에서 데이터를 가져오는 기능은 엑셀 2016 이후, 파일 결합 기능은 엑셀 2019 이후 버전에서 제공됩니다.
5. 파일 결합 창이 열리면 폴더 안 파일의 샘플을 볼 수 있습니다. 불러올 시트를 선택해 데이터에 이상이 없는지 확인한 뒤 확인을 클릭하면, 폴더 안 파일의 데이터가 하나의 표로 합쳐집니다.
   
6. 이후, 새로운 매출 파일이 생기면 취합에 사용한 폴더에 그 파일을 넣습니다.
   
7. 엑셀로 돌아와 불러온 표를 우클릭하고 새로고침을 클릭하면 새로 넣은 파일의 데이터가 표에 추가됩니다.
   
   
   오빠두Tip : 새로고침을 할 때에는 빈 셀이 아니라 불러온 표를 우클릭합니다.