엑셀에서 유용한 표를 만드는 10가지 비밀

엑셀을 이용해 분석하는 대부분의 데이터는 목록 형태를 띠고 있다. 경우에 따라서는 데이터를 정렬하고 분류하며 통합하고 차트를 작성하기도 한다. 엑셀의 표는 목록의 형태를 띤 데이터를 처리할 수 있는 뛰어난 툴을 제공한다.

 

예를 들어 데이터의 열을 자동으로 합하여 확인할 수 있는 셀의 합계만을 표시하도록 하고 싶을 때, 엑셀의 표 기능을 활용하면 된다. 그리고 간단한 몇 단계를 거쳐 엑셀의 데이터를 구성하고 싶을 때에도 엑셀의 표 기능을 사용하면 쉽게 해결할 수 있다. 그리고 일반적인 스프레드시트 셀에 번호를 넣는 대신에 양식을 사용할 때도 표 기능을 사용할 수 있다.

 

엑셀의 표 기능을 이용해 데이터 목록을 관리하는 10가지 방법에 관해 알아보도록 하자.

 

1. 다양한 방식으로 표를 작성하기

 

삽입 또는 홈 탭에서 스타일을 선택하여 표를 작성할 수 있다.

 

엑셀의 표 기능을 사용하는 방법에 관해 배우는 첫 번째 단계는 해당 프로그램을 이용해 표를 작성하는 것이다. 이를 위해서는 열 제목과 (필요한 경우) 열 제목이 필요하다. 제목 열과 열을 포함하여 데이터를 선택하고 삽입 > 표 를 클릭한다. 선택한 영역이 정확한지 눈으로 확인하고 머리글 포함 체크상자를 클릭한 후 확인 을 클릭한다. 그러면 양식을 갖춘 표가 생성된다. 특정 표 양식을 선택하고 싶은 경우에는 동일한 데이터 영역을 선택하고 (삽입 대신에) 홈 을 선택한 후 스타일에서 표 서식을 선택한다.

 

2. 필터 화살표 제거하기

 

필터의 옵션을 클릭하여 필터 화살표의 표시를 선택/해제 할 수 있다.

 

엑셀의 표 기능을 일부 사용하고 싶지만 데이터를 분류하거나 정렬하지 않을 경우, 필터의 화살표를 감출 수 있다. 이를 위해서는 표 내부의 한 곳을 클릭하고 데이터 > 정렬 및 필터 > 필터 를 선택한다. 이제 한 번의 클릭으로 화살표를 표시하거나 감출 수 있다. 단축키 Shift-Ctrl-L도 동일한 효과를 낼 수 있다.

3. 서식은 취하되 표는 버리자

데이터를 엑셀의 표로 작성하는 것은 엑셀에서 깔끔한 형태의 셀을 구성하는 가장 손쉬운 방법이다. 유일한 단점은 표에 원하는 기능만을 적용하여 구성할 수 없을 것 같아 보인다는 것이다. 기술적으로는 이런 한계점이 있지만 본인이 원하지 않는 기능을 굳이 유지할 필요는 없다. 작업 시트에서 표의 스타일을 가져오기 위해서는 우선 데이터를 표로 작성하고 원하는 표 스타일을 반드시 선택해야 한다.

 

그리고 표의 안쪽을 클릭하고 표 도구 > 디자인 > 범위로 변환 을 클릭한다. '표를 정상 범위로 변환하시겠습니까?'라는 메시지가 표시될 때 예를 클릭하면 표가 양식은 유지된 채 정상 범위로 복귀된다.

 

4. 열 제목 고정하기

 

들여쓰기 옵션을 사용하여 우 정렬된 열의 제목을 필터 화살표의 왼쪽으로 옮겨보자.

 

엑셀 표의 열 제목에서 제목이 오른쪽으로 당겨져 있으면 보기에 좋지 않다. 화살표가 제목을 일부 가리게 될 뿐만 아니라 이를 해결할 수 있는 명확한 방법도 존재하지 않는다. 이를 우회적으로 해결하는 방법은 콘텐츠를 셀의 우측에서 들여쓰기 하는 것이다. 이를 위해서는 부분적으로 감추어져 있는 제목을 포함한 셀을 선택하고 홈 > 들여쓰기 를 클릭한다. 셀의 콘텐츠가 셀의 좌측으로 급격하게 이동하면 홈 > 우정렬 을 클릭하여 다시 우측으로 이동시킨다. 필요한 경우 들여쓰기 를 여러 번 클릭하여 제목의 텍스트가 필터의 화살표에 가려지지 않도록 한다.

 

5. 표에 새로운 행 추가하기

표에서 행은 일반적인 작업시트의 행과는 조금 다르다. 표에 새로운 행을 추가해야 할 경우 그리고 합계 행이 보이지 않는 경우에는 표의 우측 하단 셀을 클릭하고 Tab 키를 누른다. 이를 통해 워드에서 표를 작성할 때와 마찬가지로 표에 새로운 열을 추가할 수 있다.

 

표 내부에 새로운 행을 추가하려면 표 내부의 셀을 선택할 경우에만 나타나는 특수한 삽입 옵션을 사용한다.

 

표의 끝에 행을 추가하기 위해서는 표의 우측 하단에 위치한 작은 인디케이터(Indicator)를 드래그하여 행과 인접열을 추가한다. 표 내부에 행을 추가하려면 행을 삽입할 곳의 위 또는 아래를 클릭하고 홈 > 삽입 > 위에 표 행 삽입 또는 홈 > 삽입 > 아래에 표 행 삽입 등 새로운 행을 추가할 곳에 따라 필요한 옵션을 클릭한다. 표의 양식은 자동으로 조정되기 때문에 새로운 행은 정확하게 구성된다.

6. 정확한 합계를 계산하자

 

표의 열에서 합계를 구하기 위해 선택하면 보이는 셀의 값만을 더하는 소계(SUBTOTAL) 함수가 추가된다.

 

일부 행이 숨겨져 있는 데이터 열에서 합계를 구하기 위해 합계 함수를 사용해본 사람은 예상하지 못한 상황에 당황한 적이 있을 것이다. 합계 함수는 범위 내에 있는 모든 셀의 합계를 계산한다. 즉, 보이는 행의 수를 합한 값을 결과를 표시하지 않는다는 뜻이며, 이런 차이는 상당한 문제가 될 수 있다. 이런 문제를 해결하기 위해서는 SUBTOTAL을 사용해야 한다. 엑셀에서 표에 대해 합계 행 기능을 사용하면 해당 기능이 자동으로 활성화된다.

 

표에 합계 행을 추가하려면 표의 내부를 마우스로 우 클릭한 후 표 > 요약행을 선택하거나 표의 내부를 클릭하고 표 도구 > 디자인 > 요약행을 클릭한다. 그러면 표의 아랫부분에 요약행이 나타난다. 마지막 열에 수치값이 포함되어 있을 경우, 엑셀은 자동으로 SUBTOTAL을 사용하여 합계를 구한다.

 

다른 행에 합계를 추가하려면 요약행에서 적절한 셀을 클릭한 후 드롭다운(Drop-down) 메뉴에서 합계를 클릭한다. 이를 통해 셀에 소계 수식을 추가하여 표에 필터를 적용했을 때 시각적으로 확인할 수 있는 값의 합계를 구할 수 있다. 해당 드롭다운 메뉴에서는 최소값, 최대값, 숫자 개수, 평균 등의 기타 계산 옵션을 선택할 수 있다.

 

7. 표 데이터에서 차트 생성하기

 

엑셀의 표에서 생성한 차트는 표에서 데이터를 추가하거나 삭제할 때 자동으로 확장/축소 된다.

 

목록을 표로 구성했을 때의 장점은 표 데이터로부터 생성한 차트가 표에서 데이터를 추가하거나 삭제할 때 능동적으로 변화한다는 점이다. 따라서 어떤 범위의 값으로 구성된 열 차트는 표에 새롭게 추가된 값을 포함하기 위해 확장된다. 표의 아래쪽에 새로운 데이터를 추가하거나 우측에 새로운 열을 추가할 때도 같은 현상이 발생한다. 표에 기반하여 차트를 생성하는 것은 엑셀에서 차트를 생성하는 것과 별반 다르지 않으며, 오직 차트의 행태만이 달라지게 된다. 이런 유형의 표는 시간이 지나면서 확장되거나 축소되는 데이터로 작업할 때 매우 유용하다.

 

8. 레코드 기록을 이용해 데이터 입력하기

광범위한 표에서 방대한 데이터를 입력하는 것은 꽤나 복잡한 작업이 될 수 있어, 서식에 데이터를 입력하는 것이 유리할 경우가 많다. 이전 버전의 엑셀에는 유용한 기록 툴이 포함되어 있었으며, 지금도 해당 툴을 사용할 수 있지만 리본 메뉴에서는 찾아볼 수 없다. 이것을 손쉽게 찾으려면 엑셀의 빠른실행 도구모음(Quick Access) 툴바에 추가하면 된다: 우선 파일 > 옵션 > 빠른실행 도구모음을 클릭한다. 다음에서 명령선택 목록에서 모든 명령 을 선택하고 아래로 이동하여 레코드 관리를 클릭한다. 여기에서 추가 를 클릭하여 빠른실행 도구모음 툴바에 해당 툴을 추가한 후 확인을 클릭한다.

엑셀의 숨겨진 레코드 기록 기능은 자동으로 표에 데이터를 입력할 수 있는 양식을 만들어준다. 

해당 기능을 사용하기 위해서는 표의 내부를 클릭한 후 레코드 기록 버튼을 클릭하여 입력창이 나타나도록 한다. 입력창의 제목은 시트명이며, 현재의 서식 데이터를 미리 보고 새로운 데이터를 추가할 수 있는 상자가 포함되어 있다. 새로운 데이터를 추가하기 위해서는 새로 만들기 를 클릭하고 관련된 텍스트 상자에 데이터를 입력한다. 서식 데이터를 보기 위해서는 이전 찾기 또는 다음 찾기를 클릭하여 한 번에 한 줄씩 이동할 수 있다. 서식을 끝내려면 닫기를 클릭한다.

 

9. 표의 데이터를 정렬하고 분류하기

엑셀의 표에서 데이터를 정렬하고 분류하는 기능은 핵심 기능이라 할 수 있다. 해당 기능을 사용하기 위해서는 표 열의 우측에 위치한 아래쪽 화살표를 클릭하고 정렬 또는 필터 옵션을 선택한다. 정렬의 경우, "오름차순"과 "내림차순"을 선택할 수 있다. 필터 옵션은 숫자, 텍스트, 날짜 등 열의 종류에 따라 달라진다.

 

엑셀에 내장된 자동필터 기능을 이용해 표의 데이터를 분류할 수 있는 사전에 설정된 필터를 선택할 수 있다.

 

그리고 나서 사전에 설정된 다양한 옵션 중에서 선택하거나 고급 필터를 선택하여 자신만의 필터를 구성하면 된다. 또는 ‘그리고’ 혹은 ‘또는’ 필터 등 복잡한 필터를 구성할 수도 있다. 예를 들어, 200,000달러 이하 또는 400,000달러 이상인 열에서 값의 위치를 설정하려면 OR 필터를 포함시켜야 한다. 이를 생성하기 위해서는 사용자 지정 필터를 선택한 후 다이알로그 영역에서 검색의 양쪽 부분을 구성하되 반드시 ‘또는’ 옵션을 선택하도록 한다. 이와 마찬가지로 2개의 열에 적용할 수 있는 ‘그리고’ 필터를 생성하여 "매출이 300,000달러 이상인 캐나다의 모든 입력값" 등의 정보를 표시하도록 할 수 있다. 이런 경우, 위치 열에서는 '캐나다'만이 표시되도록 선택할 수 있다. 매출 열에서 숫자 필터 > 사용자 지정 필터 > 이상 을 클릭하고 300000 을 입력한 후 확인을 클릭한다.

 

필터가 포함된 모든 열은 아래쪽을 향한 삼각형 대신에 필터 아이콘을 표시하기 때문에 필터의 위치를 한 눈에 파악할 수 있다. 필터를 비우기 위해서는 필터 아이콘을 클릭하고 필터 해제를 클릭하거나 홈 > 정렬 및 필터 > 지우기 를 클릭하여 표의 모든 열에서 모든 필터를 지운다.

 

10. 복수의 열에서 복잡한 ‘또는’ 검색을 생성하자

 

복수의 열에 적용된 '또는' 검색은 상대적으로 복잡하며 자동필터 옵션을 사용해서는 생성할 수 없다.

 

"캐나다의 모든 입력값 또는 매출이 200,000달러 이상인 곳" 유형의 ‘또는’ 검색은 메뉴 옵션을 이용하여 구성할 수 있는 검색 유형이다. 따라서, 반드시 다른 방법으로 이런 유형의 검색 설명을 작성해야 한다. 이를 위해서는 우선, 표의 제목행을 복사한 후 표의 아래에 몇 줄을 붙여 넣는다. 해당 제목들 아래의 Location열에서 ="=캐나다"를 입력하고 두 번째 행의 매출열에서 >200000를 입력한다. 표의 내부를 클릭한 후 데이터 > 고급 > 현재 위치에 필터를 클릭한다. 목록의 범위가 테이블의 범위와 일치하는지 확인하고 두 번째 제목 세트와 그 아래 2개의 데이터행을 포함하는 영역을 기준 범위로 설정한다. 그리고 확인 을 클릭하면 목록에 필터가 적용된다. 

출처: itworld.