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

점점 더 중요해질 엑셀 필수 기능인 표 기능의 모든 것을 기초부터 응용까지 단계별로 알아봅니다.

# 엑셀시작하기 # 엑셀기능&팁

작성자 :
오빠두엑셀
최종 수정일 : 2021. 09. 09. 11:05
URL 복사
메모 남기기 : (67)

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

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

큰 화면으로 보기

예제파일 다운로드

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

  • [기초입문] 엑셀 표 기능의 모든 것
    예제파일
  • [기초입문] 엑셀 표 기능의 모든 것
    PPT자료

표 기능이란 무엇인가요?

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

엑셀 표 기능이란 무엇인가
엑셀 표 기능은 고급 단계로 넘어가기 위해 반드시 알아야 할 필수 기능입니다

표 vs 범위의 차이점

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

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

범위 셀 간에 데이터 독립적
범위 안에 데이터는 각 셀간에 독립적으로 입력됩니다.

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

표 필드안에 데이터 종속적
표 안의 데이터는 특정 필드안에 종속됩니다.

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

표와 범위 비교
범위는 바가지 안에 담긴 쌀 뭉탱이, 표는 포대 안에 정돈 된 쌀과 같습니다.

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

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

엑셀로 데이터를 관리하는 경우
엑셀로 데이터를 관리하는 경우는 크게 두가지로 나눌 수 있습니다.

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

범위를 표로 바꾸는 방법

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

엑셀 표 만들기 기초

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

표 기능의 장점 7가지

장점1. 자동필터 기능

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

엑셀 표 기능 자동필터추가
범위를 표로 변환하면 필터가 자동으로 적용됩니다.
장점2. 요약행 기능

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

엑셀 표기능 요약행 추가

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

엑셀 요약행 사용법 예제
요약행을 사용하면 각 필드의 데이터를 합계를 손쉽게 구할 수 있습니다.

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

요약행 사용시 주의사항
단, 요약행을 사용할 경우 요약행 위로 새로운 데이터를 추가해야 합니다.
장점3. 표 스타일 기능

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

엑셀 표 디자인 표 스타일
표 디자인에서 줄무늬행을 적용하거나 표 스타일을 선택할 수 있습니다.
장점4. 수식 자동입력 기능

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

  • 수식으로 다른 셀 주소를 참조하면서 절대참조와 상대참조를 동시에 사용한 경우
  • 수식으로 표의 다른 일부분을 셀참조로 입력하면서 절대참조로 수식에 입력한 경우
  • 수식으로 확장범위를 사용한 경우
  • [자동고침] 옵션에서 '표에 수식을 채워 계산된 열 만든기' 옵션이 비활성화 된 경우
    [오빠두Tip!] [파일] - [옵션] - [언어교정] - [자동 고침 옵션] - [입력할 때 자동 서식] 에서 '표에 수식을 채워 계산된 열 만들기' 옵션을 활성화합니다.
장점5. 자동 확장 기능

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

엑셀 표 자동확장 기능
표의 아래/오른쪽으로 새로운 데이터가 추가되면 표가 자동으로 확장됩니다.

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

엑셀 표에 새 행 및 열 포함 활성화
표가 자동으로 확장되지 않을 경우 설정을 변경합니다.

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

엑셀 표 넓이 변환하기
표의 오른쪽 아래 꺾쇠를 마우스로 드래그하여 표 넓이를 조절할 수 있습니다.
장점6. 슬라이서 기능

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

엑셀 표기능 슬라이서 활용
슬라이서를 활용하여 표를 실시간으로 필터링 할 수 있습니다.

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

장점7. 구조적 참조

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

표 이름 확인
[표 디자인]의 왼쪽에서 선택된 표의 이름을 확인할 수 있습니다.

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

구조적 참조 수식 입력
표 이름을 입력한 뒤 대괄호([)를 입력하면 구조적참조로 표 범위를 참조할 수 있습니다.

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

표 기능 사용시 주의사항

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

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

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

셀 병합 풀림 문제
표로 변환하기 전 범위에 병합된 셀이 있을 경우, 레이아웃이 깨지는 문제가 발생합니다.

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

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

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

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

구조적 참조란?

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

  1. 수식의 범위가 어디를 참조하는지 직관적으로 알 수 있어 복잡한 수식을 관리할 때 용이합니다.
  2. 새롭게 추가되는 데이터가 자동으로 범위에 반영되어 누적되는 데이터를 보다 편리하게 관리할 수 있습니다.

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

구조적참조 4가지만 기억
구조적 참조는 아래 4가지만 기억하면 됩니다.
구조적참조 설명
표이름[#모두] 표 모든 범위를 참조합니다. (머릿글, 값)
표이름[#머릿글] 표의 모든 머릿글 범위를 참조합니다.
표이름[필드명] 표의 특정 필드값 범위를 참조합니다.
표이름[@필드명] 수식이 입력된 셀과 동일한 행의 필드값을 참조합니다.

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

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

  1. 온전한 표 범위를 참조하는 경우에만 사용할 수 있다.

    표 온전한 범위 선택
    구조적 참조는 표의 온전한 범위를 참조할 때만 적용됩니다.
  2. 표의 연속된 범위만 참조할 수 있다.

    표 연속된 범위 선택
    구조적 참조는 표의 연속된 범위만 참조할 수 있습니다.
  3. 구조적참조를 범위형태로 입력하면 절대참조가 된다.

    표 범위 절대 참조
    구조적 참조를 절대참조 하려면 범위로 입력해야 합니다.

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

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

  1. 예제파일의 [구조적참조] 시트로 이동합니다. 이후 H6셀에 아래 수식을 입력하면 수식이 아래로 자동채우기 되며 금액의 누계가 계산됩니다.
    =SUM($G$5:G6)

    엑셀 표 기능 누계 계산
    금액의 누계를 계산합니다.
  2. 표의 아래쪽에 새로운 데이터를 추가하면 누계도 자동으로 계산되지만, 확장범위가 옳지않게 계산됩니다.

    엑셀 표기능 절대참조 오류
    수식이 자동으로 채워지면서 옳지않은 범위가 참조됩니다.
  3. 다시 예제파일의 H6셀에 아래 수식을 입력한 뒤, 아래로 자동채우기 하면 확장범위가 올바르게 적용됩니다.
    =SUM(INDIRECT("$G$5:G"&ROW()))

    엑셀 indirect 함수 해결
    INDIRECT 함수로 범위를 참조하여 문제를 해결할 수 있습니다.
  4. Indirect 함수에 대한 자세한 설명은 아래 함수 관련 포스트를 참고해주세요.
5 46 투표
게시글평점
67 댓글
Inline Feedbacks
모든 댓글 보기
67
0
여러분의 생각을 댓글로 남겨주세요.x