엑셀 (Excel)이 복잡하고 어렵다고 생각하는 분들이 많이 계실텐데요, 그 중 많은 분들은 프로그램을 사용하다 실수를 하여 수정을 한 경험을 가지고 계신 분들입니다. 이 포스팅에서는 엑셀을 사용하면서 쉽게 할수 있는 오류들과 어떻게하면 그런 실수들을 피해가며 유용한 이 프로그램을 효율적으로 사용할 수 있는지를 알아 보겠습니다.
Note: 아래에 게재된 팁은 엑셀 데스크톱과 웹버전용 입니다.
셀 병합
아래의 이미지와 같이 많은 분들이 데이터가 있는 셀들 중 어떤 행은 모든 셀을 병합하는 것을 선호합니다.
문제점:
셀을 병합하고 나면 데이터에 어떠한 액션을 취하는 것이 어렵게 됩니다. 대다수의 엑셀 함수들은 일관성있는 행과 열의 설정을 필요로 하므로 데이터 셀들을 합치는 것은 이런 일관성을 파괴하는 일이죠.
우선, 데이터를 정렬하려고 하면 다음과 같은 오류가 뜨게 됩니다.
게다가 병합되지 않은 데이터 셀의 데이터를 병합된 셀에 붙여 넣기를 하면 다음과 같은 오류가 생깁니다.
해결방법:
1. 먼저 병합된 셀을 선택한 후, 메뉴에서 “병합하고 가운데 맞춤 (Merge & Center)” 버튼을 클릭하면 병합된 셀들이 다시 나누어 지게됩니다.
2. 다른 데이터 셀들과 맞춤을 동일하게 하기 위해 “맞춤 (Alignment)” 메뉴 그룹의 우측 하단의 작은 화살표 버튼을 클릭하여 “셀 서식 (Format Cells)” 옵션을 활성화 시킵니다.
3. 옵션 윈도우에서 다음과 같이 텍스트 맞춤 영역의 가로 섹션의 값을 “선택 영역의 가운데로 (Center Across Selection)”로 설정한 후, 확인 (OK)을 클릭합니다. (중요: 실제 데이터의 맞춤 양식에 따라 설정하는 값들이 달라집니다)
이젠 “병합하고 가운데 맞춤 (Merge & Center)” 옵션을 사용한 것 같이 텍스트가 보이게 됩니다, 하지만 셀 구조는 다른 데이터 셀들과 동일성을 유지하고 있습니다.
Note: “선택 영역의 가운데로 (Center Across Selection)”의 가로 맞춤 옵션은 선택 영역의 방향이 가로인 행에서만 가능합니다. 방향이 세로인 열은 불가능합니다.
수동으로 테이블 만들기
엑셀의 파워 유저가 되기 전까지는 수동으로 테이블을 만들고 포맷하는 것이 문제가 되는지는 몰랐습니다. ^^;
문제점:
데이터를 포맷하고 공식을 새로운 행에 붙여넣기를 수동으로 해왔었습니다, 하지만 새로운 행을 삽입하거나 데이터를 사용하여 차트를 만드는 작업에 문제가 있었습니다. 아래의 이미지의 예를 보면, 행의 바탕색을 번갈아가며 수동으로 넣었는데 중간에 새로운 행을 삽입하게 되면 번거롭게 바탕색을 다시 넣어야 합니다.
또한 아래와 같이, 열에 필터를 설정한 후, 정렬을 다르게 하면 바탕색이 원치않게 변하게 됩니다.
이런 문제는 수동으로 각 행의 배경색이 지정되어 있으므로 정렬이나 새로운 행을 삽입하면 기존 행의 위치가 변경이 되고 배경색은 변경이 안되므로 배경색은 변경된 위치에 그대로 남게 되죠.
해결방법:
해결방법은 아주 간단합니다.
1. 테이블에 포함시킬 모든 데이터 (헤더행과 데이터를 포함)를 선택합니다.
2. 상위의 홈 탭에서 “표 서식 (Format as Table)” 메뉴 버튼을 클릭한 후 원하는 디자인을 선택합니다.
Note: 웹 버전에서는 삽입 (Insert) > 표 (Table)를 클릭합니다.
3. 헤더 (머리글)가 있으면 표 만들기 (Create Table) 옵션 윈도우 상에서 “머리글 포함 (My Table Has Headers)” 체크박스를 클릭 후, 확인 (OK) 버튼을 클릭합니다.
테이블 디자인 (Table Design) 탭에 원하는 표 이름을 아래의 이미지와 같은 방법으로 지정하게 되면 공식을 사용하게 될시에 표 이름을 사용하거나 테이블 바로가기에 사용 할 수 있습니다.
같은 탭에서 표 스타일이나 옵션등을 변경할 수 있습니다.
이제는 테이블 내에 새로운 행이나 열을 삽입하면 포맷이 자동으로 업데이트가 될 것입니다. 그리고 아래의 이미지와 같이 테이블의 우측 하단에 위치한 핸들을 드래그하면 포함하는 행과 열을 수정할 수 있습니다. 게다가 새로운 행을 삽입시에 엑셀은 자동으로 다른 행에 존재하는 기존의 공식도 삽입하게 됩니다.
결과적으로는 테이블을 포맷하는 것은 데이터 뿐만아니라 서식까지 저장하므로 엑셀에서 제공하는 표 서식을 이용하면 아주 쉽게 원하는 포맷의 표를 저장하고 수정이 가능하죠.
빈 행이나 열 사용
많은 유저들이나 심지어는 온라인 튜터리얼까지도 첫번째 행과 열을 빈값으로 설정합니다. 이는 아마도 보기 좋아서 그럴 것 같습니다. 워드 같은 프로그램에서는 프로그램 상에서 프린트 할 시에 보여지는 마진이나 보더등이 보여지지만 엑셀에서는 프린트 레이아웃을 오픈해야 볼수 있는 이유도 포함이 됩니다.
문제점:
불필요할 뿐더러 엑셀은 프린트할 시에 자동으로 프린트 마진 보더를 넣으므로 출력을 하게되면 자동으로 좌우상하에 빈 공간이 보이게 되고, 게다가 다른 문제들도 존재합니다. 예를 들면 빈 행이나 열은 정렬과 필터링에 방해를 하고, 공식이나 자동 채우기 (AutoFill)을 할 시에 오류도 생길 수 있습니다.
해결방법:
한두개의 빈 행과 열이 있다면 최좌측 행이나 최상단 열의 버튼을 오른쪽 마우스 클릭을 하고 “삭제 (Delete)”를 선택하여 빈 행과 열을 삭제합니다.
하지만 빈 행이 아주 많다면, COUNTA 함수를 이용하면 됩니다. 아래 예제를 보면:
1. 데이터가 끝나는 열의 오른쪽 셀에 다음의 공식을 입력하고:
=COUNTA(괄호 안에 데이터가 존재하는 모든 행의 셀을 마우스로 선택한 후 Enter 키를 누릅니다.
2. 그 후에, 같은 열의 아래로 데이터가 끝나는 행까지 자동 채우기 (AutoFill)로 드래그를 합니다.
3. COUNTA 공식이 존재하는 열의 최상단 버튼을 클릭하여 선택합니다.
4. 홈 메뉴 탭의 편집 메뉴 그룹에서 “정렬 및 필터 (Sort And Filter)” 버튼을 클릭한 후 “숫자 오름차순 정렬 (Sort Smallest To Largest)”을 선택합니다.
5. 다음의 정렬 경고 다이얼로그 박스에서 “선택 영역 확장 (Expand The Selection)”을 선택하고 정렬 버튼을 클릭합니다.
6. 아래의 이미지와 같이 빈 행들은 모두 상단으로 옮겨지게 되므로 모든 빈 행들을 선택하여 삭제를 하면 됩니다.
7. 마지막으로 COUNTA 함수가 존재하는 열을 삭제합니다.
수동으로 순서 입력하기
엑셀을 사용하면 순서적으로 데이터를 표현하는 작업이 많습니다
문제점:
순서를 수동으로 입력하는 방법은 시간도 많이 걸리고 값을 건너 뛴다거나 잘못 입력하는 문제가 생길 수 있습니다.
해결방법:
엑셀의 자동 채우기 (AutoFill) 기능은 존재하는 데이터의 패턴을 인지하고 나머지 데이터를 채워주는 기능입니다. 처음 두개의 데이터 값을 입력한 후, 자동 채우기 (AutoFill)의 핸들을 드래그하여 나머지 값을 자동으로 입력하게 합니다.
이 기능은 숫자가 있는 문자에도 사용이 가능합니다 (예제, 유저 1, 유저 2 등등). 하지만 숫자없이 문자열로만 이루어진 데이터는 사용이 불가능 합니다.
잘못된 데이터 참조 타입 사용
공식에서 다른 셀을 참조하는 것은 엑셀에서 아주 유용한 기능이죠. 아래의 예제 이미지에는 C2의 값을 계산하기 위해 B2와 F1 셀을 참조 하였습니다.
문제점:
많은 유저들은 자동 채우기 (AutoFill) 기능을 사용할 시에 엑셀에 존재하는 여러가지 참조 타입을 이해하지 못하는 경우가 많습니다.
자동 채우기 (AutoFill) 기능을 사용하여 남은 셀들을 계산하고 나면 아래와 같이 10% 부과세 계산이 잘못되어 있는것을 발견하게 됩니다.
해결방법:
엑셀에는 세가지 참조 타입이 존재하고 공식이나 함수를 오류 없이 사용하기 위해 이 세가지를 반드시 이해하고 있어야 합니다.
상대적 참조 (relative references)는 셀들간에 상대적인 위치를 나타냅니다 위의 예제에서 처음 계산은 C2 (공식의 위치), B2 (C2에서 왼쪽으로 한칸), 그리고 F1 (C2에서 오른쪽으로 3칸, 위로 한칸)의 위치를 이용하여 공식이 지정되어 있습니다. C3에 같은 공식이 붙여넣기가 되면, 엑셀은 아래의 이미지와 같이 상대적인 위치를 이용하여 공식을 업데이트 하므로 오류가 존재하게 되죠.
오류가 없기 위해서는 F1의 절대 위치를 지정하는 참조 타입 (absolute references)을 사용하게되면 자동 채우기 (AutoFill)는 참조되는 셀의 위치를 고정하게 됩니다. 그러기 위해서 $ 키를 사용합니다.
그런 후에 다시 자동 채우기 (AutoFill)를 하게되면 엑셀은 공식에서 F1의 위치를 고정하게 됩니다.
만약에 행이나 열의 위치만 고정을 한다면 참조하는 셀의 위치에 고정하려는 행이나 열에만 $ 키를 입력하면 됩니다.
데이터 잠금이나 유효성 검사를 사용하지 않기
엑셀은 스프레드시트를 작성하여 다른 사람들과 공유하기 적합한 툴입니다.
문제점:
하지만 엑셀에 서툰 유저나 파일을 마음대로 수정하는 유저와 공유를 하다보면 데이터가 망가져 있는 경우가 많습니다.
해결방법:
엑셀에는 다른 유저들이 데이터를 마음대로 수정하거나 입력하지 못하게 하는 데이터 유효성 검사나 잠금 기능이 존재합니다.
데이터 유효성 검사 기능은 유효한 데이터만 입력할 수 있게 합니다. 검증할 셀들을 선택한 후, 데이터 탭 메뉴에서 데이터 도구 (Data Tools) 메뉴 그룹 중 “데이터 유효성 검사 (Data Validation)” 버튼을 클릭하여 활성화 시킵니다.
유효성 조건 (Validation Criteria) 섹션에서 “제한 대상 (Allow)”의 값에 따라 옵션을 설정합니다. 또한 오류 메시지 (Error Alert) 탭에서 입력이 불가능한 값을 입력 하였을 때 오류 메시지가 뜨게 할 수도 있습니다. 모든 옵션을 설정하였으면 확인 (OK) 버튼을 클릭합니다.
만약에 데이터를 수정하지 못하게 보호하려면, 워크시트를 보호할 수 있습니다. 기본적으로 워크시트를 보호하게되면 모든 셀이 잠금처리가 되므로 먼저 어떤 셀을 선택하여 수정이 가능한지 설정을 해야 합니다.
그러기 위해서는 수정이 가능한 셀에 오른쪽 마우스 버튼을 클릭하고 “셀 서식 (Format Cells)”을 클릭합니다
그 후에, 보호 (Protection) 탭에서 잠금 (Locked) 옵션의 체크 박스를 해제한 후 확인 (OK)을 클릭합니다.
시트를 공유하기 전에 검토 (Review) 탭 메뉴에서 시트 보호 (Protect Sheet) 버튼을 클릭하고 “잠긴 셀 선택 (Select Locked Cells)” 옵션을 해제, “잠금 해제된 셀 선택 (Select Unlocked Cells)”을 활성화 시킵니다. 그리고 암호설정을 하려면 이 곳에서 가능합니다. 옵션 설정이 완료되면 확인 (OK) 버튼을 클릭합니다.
옵션 설정 후에는 선택된 셀들만 클릭과 수정이 가능하게 됩니다.
이 기능을 해제하려면 “시트 보호 해제 (Unprotect Sheet)” 버튼을 상단의 메뉴에서 클릭합니다. 만약에 암호를 입력하였다면 암호를 입력해야 합니다.
Note: 웹 버전에서는 검토 (Review) 탭 메뉴에서 “보호 (Protection)” 버튼을 클릭하고, 우측 사이드바에서 잠금 해제할 셀들을 선택합니다.
참고:
