엑셀 표 기능의 모든 것, 기초-응용 완벽정리

엑셀 표 기능의 모든 것, 기초-응용 완벽정리

엑셀 표 기능의 모든 것 목차 바로가기

영상 강의

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

표 기능이란 무엇인가요?

엑셀 표 기능이란 '데이터를 편리하고 체계적으로 관리할 수 있도록 도움'을 주는 기능입니다. 표 기능은 엑셀 2007 버전부터 추가된 기능으로, 엑셀의 고급단계로 넘어가기 위한 파워쿼리, 파워피벗을 활용하거나 외부 프로그램과의 연계(SQL, 파워BI, 태블로 등..)을 위해서 반드시 알아야 할 필수 기능입니다.

표 vs 범위의 차이점

사용자 입장에서 볼 때 표와 범위는 시트 위에 출력되는 데이터로 둘 사이에 큰 차이를 느끼지 못합니다. 하지만 데이터를 관리하는 엑셀입장에서 보았을때에는 표와 범위사이에 아주 큰 차이가 있는데요. 바로, "범위는 셀 간에 데이터를 형성하고 표는 필드 안에서 데이터를 형성한다"는 점에서 차이가 있습니다.

범위 : 셀 간에 데이터를 형성합니다. (각각의 데이터가 독립적)

표 : 필드 안에서 데이터를 형성한다. (각 데이터는 필드안에 종속됨)

이를 쉽게 비교하기 위해 '쌀'을 각각의 데이터로 예를 들자면, 범위는 '바가지 안에 담긴 쌀 뭉탱이' , 표는 '포장된 쌀 포대' 로 이해할 수 있습니다.

표 기능은 언제 사용하나요?

엑셀로 데이터를 관리하는 경우는 크게 2가지로 나눌 수 있습니다. 첫번째는 서버(중앙 프로그램)에서 데이터를 관리하면서 필요할 때마다 다운받아서 편집하는 경우이고, 두번째는 엑셀 안에서 모든 데이터를 입력하고 편집하는 경우인데요.

표 기능은 두번째 경우인 '엑셀 안에서 모든 데이터를 입력하고 편집하는 경우'에 사용하기 적합합니다. 즉, 표 기능은 '엑셀로 누적되는 데이터(=많은 양의 데이터)를 관리할 때 사용하면 좋은 기능' 이 되겠습니다.

범위를 표로 바꾸는 방법

범위를 표로 바꾸는 방법은 아주 간단합니다. 표로 변환할 범위를 직접 드래그하여 선택하거나, 표로 변환할 범위 중에 셀을 하나 선택한 뒤, [삽입] - [표]를 클릭하면 표 만들기 대화상자가 실행됩니다. 또는 단축키 Ctrl + T 로 표 만들기 대화상자를 실행할 수 있습니다.

이후 머릿글 포함여부를 체크한 뒤, [확인] 버튼을 누르면 범위가 표로 변환됩니다.

표 기능의 장점 7가지

장점1. 자동필터 기능

표 기능의 첫번째 장점은 범위가 표로 변환되면서 머릿글에 필터 기능이 자동으로 추가된다는 점입니다. 표에는 새로운 데이터가 추가되면, 표의 범위도 동시에 확장되는데요. 따라서 필터링 되는 범위도 자동으로 확장되므로 매우 유용하게 사용할 수 있습니다.

장점2. 요약행 기능

표 기능의 두번째 장점은 요약행을 사용할 수 있다는 점입니다. 요약행 기능은 기본으로 추가되지 않기 때문에, 단축키 Ctrl + Shift + T 로 추가하거나 또는 표를 우클릭 - [표] - [요약 행]을 선택하여 추가할 수 있습니다.
[오빠두Tip!] 표 디자인 - 표 스타일 옵션에서 '요약행'을 추가할 수도 있습니다.

요약행을 사용하면 새롭게 추가되는 데이터를 포함하여 데이터의 합계, 개수, 평균등을 실시간으로 구할 수 있어 실무에서 매우 유용하게 사용할 수 있습니다.

다만, 요약행을 사용할 경우에는, 표의 아래방향으로 새로운 데이터를 바로 추가할 수 없습니다. 따라서 [요약]을 선택한 뒤 단축키 Ctrl + Shift + + 로 새로운 행을 삽입하여 데이터를 추가하거나, 요약행을 우클릭 - [삽입] - [위쪽에 표 행 삽입]을 클릭하여 새로운 행을 삽입한 뒤 데이터를 추가해야 합니다.

장점3. 표 스타일 기능

표 기능의 세번째 장점은 표 스타일 기능입니다. 표 기능을 사용하면 손쉽게 표를 꾸미고 줄무늬 행을 적용할 수 있습니다. 표를 선택한 뒤, [표 디자인] - [표 스타일 옵션] 에서 다양한 표 스타일을 선택하거나, 줄무늬 행 표시 여부를 선택할 수 있습니다.

장점4. 수식 자동입력 기능

표 기능의 네번째 장점은 수식 자동입력 기능인데요. 필드에 값을 입력하면 나머지 비어있는 곳으로 수식이 자동으로 채워집니다. 다만, 이 기능은 일부 상황에서 제대로 동작하지 않을 수 있습니다.

장점5. 자동 확장 기능

표 기능의 다섯번째 장점은 자동확장 기능입니다. 표는 표의 아래쪽이나 오른쪽으로 새로운 데이터가 추가되면 표의 범위가 자동으로 확장됩니다.

만약 표가 자동으로 확장되지 않을 경우, [파일] - [옵션] - [언어교정] - [자동 고침 옵션] - [입력할 때 자동 서식] - [표에 새 행 및 열 포함]을 활성화합니다.

만약 자동으로 확장된 표 범위를 취소하려면 Ctrl + Z 를 눌러 이전 단계로 돌아가면 자동 확장이 취소할 수 있습니다. 또는 표의 오른쪽 하단에 있는 꺽쇠모양 기호를 마우스로 드래그하여 표의 범위를 직접 조절할 수도 있습니다. (단, 표의 범위를 임의로 조절할 경우, 구조적 참조로 입력된 수식이 깨지면서 #REF! 오류가 반환될 수 있으므로 주의합니다.)

장점6. 슬라이서 기능

표 기능의 여섯번째 장점은 슬라이서를 활용할 수 있다는 점입니다. 슬라이서는 '실시간으로 데이터를 필터링 할 수 있는 버튼' 인데요. 표를 선택한 뒤, [삽입] - [슬라이서]를 클릭하여 원하는 항목의 슬라이서를 추가할 수 있습니다.

슬라이서는 다양한 시각화 보고서에 더욱 효과적으로 활용할 수 있는데요. 슬라이서를 활용한 실전 예제는 아래 관련 강의에서 자세히 설명해 드렸습니다.

장점7. 구조적 참조

표 기능의 일곱번째 장점은 구조적 참조를 활용하여 수식을 보다 편리하게 입력할 수 있다는 점입니다. 표를 선택한 뒤, [표 디자인]으로 이동하면 왼쪽에 [표 이름]을 확인할 수 있습니다.

이후 수식을 입력할 때 "=표이름["을 입력하면 표의 필드를 키보드 입력으로 손쉽게 참조할 수 있습니다.

구조적 참조를 활용하면 표에 새로운 데이터가 추가될 때 구조적참조로 입력된 범위도 동시에 확장되어 새로운 데이터를 유동적으로 반영한다는 점인데요. OFFSET 함수나 INDEX 함수를 활용한 동적범위보다 매우 빠르게 동작하므로 많은 양의 데이터를 다룰 시 매우 편리하게 이용할 수 있습니다.OFFSET 함수를 활용한 동적범위에 대한 설명은 아래 강의에서 자세히 설명해드렸습니다.

표 기능 사용시 주의사항

표 기능을 사용할 때 주의해야 할 점은 크게 2가지 입니다.

주의사항1. 셀병합을 사용할 수 없습니다.

표 기능은 정규화 된 데이터(=온전한 데이터)를 다룬다는 가정하에 사용하는 기능입니다. 이러한 정규화 된 데이터에서는 병합된 셀을 허용하지 않으므로, 표 기능 또한 셀 병합을 지원하지 않으므로, 표로 변환할 기존 범위에 병합된 셀이 있을 경우 사전에 셀 병합을 해제하여 온전한 데이터 형태로 수정한 뒤 표로 변환해야 합니다.

데이터 정규화란 '데이터가 세로 방향으로 누적되도록 관리하는 것'을 이야기하는데요. 데이터 정규화에 대한 자세한 내용은 아래 강의에서 자세히 설명해드렸습니다.

주의사항2. 구조적 참조 사용의 어려움

구조적 참조는 '정규화 된 데이터를 다룰 경우' 대부분의 상황에서 매우 편리하게 사용할 수 있는 효율적인 참조방식 입니다.

하지만 실무에서는 정규화되지 않은 데이터를 다루는 상황도 종종 발생하므로, 구조적참조를 사용하기 어려울 때가 있는데요. 가장 흔한 경우는 '구조적참조를 절대참조로 사용해야 하는 경우' 입니다. 이 문제에 대한 해결방법은 구조적 참조시 주의사항에서 단계별로 알아보겠습니다.

구조적 참조란?

구조적 참조란 기존의 셀 참조방식(예: A1:C1)과는 다르게 표의 필드명으로 참조하는 방식(예: 표1[날짜]) 입니다. 구조적 참조는 기존의 셀 참조보다 아래 두가지 편리한 점이 있습니다.

구조적 참조는 아래 4가지만 기억하면 되는데요. 실무에서 구조적참조를 직접 입력하는 경우는 드물기 때문에, 구조적참조가 어떻게 구성되는지 정도만 기억하셔도 충분합니다.

구조적 참조시 주의사항 (구조적 절대참조)

구조적참조를 사용할 경우 3가지 주의사항이 있습니다.

표 기능에서 절대참조 사용시 문제점 및 해결방법

표에서 수식으로 절대참조와 상대참조를 동시에 사용하는 경우(예: 확장범위), 표가 자동 확장되면서 옳지 않은 수식이 입력되는 상황이 발생합니다. 그럴경우, INDIRECT 함수를 사용하여 문제를 해결할 수 있습니다.