Excel에서 히트맵 차트 생성하기

Excel에서 히트맵 차트는 데이터 세트의 비교 뷰를 보여주는 시각적 표현인 표처럼 보입니다. 워크시트에 대규모 데이터 세트가 있는 경우, 한눈에 낮거나 높은 값을 식별하기가 매우 어렵습니다. 그러나 히트맵에서는 셀 값이 다른 색상 패턴으로 표시되어 더 큰 데이터나 작은 데이터를 아래 스크린샷과 같이 빠르고 쉽게 볼 수 있습니다.

• 조건부 서식을 사용하여 간단한 히트맵 차트 생성하기
• Excel에서 동적인 히트맵 차트 생성하기
• 예제 1: 스크롤 바를 사용하여 동적인 히트맵 생성하기
• 예제 2: 라디오 버튼을 사용하여 동적인 히트맵 생성하기
• 예제 3: 체크박스를 사용하여 동적인 히트맵 생성하기
• 히트맵 차트 샘플 파일 다운로드
• 비디오: Excel에서 히트맵 차트 생성하기

조건부 서식을 사용하여 간단한 히트맵 차트 생성하기

Excel에는 직접적인 히트맵 차트 기능이 없지만, 강력한 조건부 서식 기능을 통해 빠르게 히트맵을 생성할 수 있습니다. 다음 단계를 따라 진행해 주세요:

1. 조건부 서식을 적용하려는 데이터 범위를 선택하세요.

2. 그런 다음 홈 > 조건부 서식 > 색상 척도를 클릭하고 오른쪽 확장된 드롭다운에서 필요한 스타일을 선택합니다 (이 경우 녹색-노란색-빨간색 색상 척도를 선택하겠습니다). 스크린샷을 참조하세요:

3. 이제 값에 따라 셀을 강조 표시하는 히트맵이 생성되었습니다. 녹색은 가장 높은 값을 나타내고 빨간색은 가장 낮은 값을 나타내며 나머지 값들은 녹색과 빨간색 사이의 그라데이션 색상으로 표시됩니다. 스크린샷을 참조하세요:

4. 숫자를 숨기고 색상만 남기고 싶다면 데이터 범위를 선택하고 Ctrl + 1 키를 눌러 셀 서식 대화 상자를 엽니다.

5. 셀 서식 대화 상자에서 숫자 탭 아래 왼쪽 카테고리 목록 상자에서 사용자 정의 옵션을 클릭하고 형식 텍스트 상자에 ;;; 를 입력합니다. 스크린샷을 참조하세요:

6. 확인 버튼을 클릭하면 아래 스크린샷과 같이 모든 숫자가 숨겨집니다:

참고: 원하는 다른 색상으로 셀을 강조 표시하려면 데이터 범위를 선택하고 홈 > 조건부 서식 > 규칙 관리를 클릭하여 조건부 서식 규칙 관리자 대화 상자로 이동합니다.

그런 다음 기존 규칙을 두 번 클릭하여 서식 규칙 편집 대화 상자를 열고 필요에 따라 규칙을 재설정합니다. 스크린샷을 참조하세요:

Excel에서 동적인 히트맵 차트 생성하기

예제 1: 스크롤 바를 사용하여 동적인 히트맵 생성하기

워크시트에 여러 열의 데이터가 있지만 제한된 공간에 표시하고 싶다면 스크롤 바를 삽입하여 아래 데모와 같이 히트맵을 동적으로 만들 수 있습니다.

이 유형의 동적 히트맵 차트를 생성하려면 다음 단계를 따르세요:

1. 새 워크시트를 삽입하고 원래 시트에서 첫 번째 열(월)을 새 시트로 복사합니다.

2. 그런 다음 개발 도구 > 삽입 > 스크롤 막대를 클릭합니다. 스크린샷을 참조하세요:

3. 그런 다음 마우스를 드래그하여 복사된 데이터 아래에 스크롤 막대를 그리고 스크롤 막대를 마우스 오른쪽 버튼으로 클릭한 후 서식 컨트롤을 선택합니다. 스크린샷을 참조하세요:

4. 서식 객체 대화 상자에서 컨트롤 탭 아래 최소값, 최대값, 증분 변경, 페이지 변경 및 연결된 셀을 데이터 범위에 따라 설정합니다. 스크린샷을 참조하세요:

5. 확인을 클릭하여 대화 상자를 닫습니다.

6. 이제 이 새 시트의 B1 셀에 다음 수식을 입력하고 Enter 키를 눌러 첫 번째 결과를 얻습니다:

참고: 위 수식에서 data1!$B$1:$I$13은 행 헤더(월)를 제외한 원래 시트의 데이터 범위이고 $I$1 은 스크롤 막대와 연결된 셀이며 $B$1:B1은 수식을 출력하는 셀입니다.

7. 그런 다음 이 수식 셀을 나머지 셀로 드래그합니다. 워크시트에 3년만 표시하려면 B1부터 D13까지 수식을 드래그하세요. 스크린샷을 참조하세요:

8. 그런 다음 새로운 데이터 범위에 조건부 서식 기능의 색상 척도를 적용하여 히트맵을 생성합니다. 이제 스크롤 막대를 드래그하면 히트맵이 동적으로 이동됩니다. 스크린샷을 참조하세요:

예제 2: 라디오 버튼을 사용하여 동적인 히트맵 생성하기

라디오 버튼을 사용하여 동적인 히트맵을 생성할 수도 있습니다. 하나의 라디오 버튼을 선택하면 가장 큰 n개의 값을 강조 표시하고 다른 라디오 버튼을 선택하면 가장 작은 n개의 값을 아래 데모와 같이 강조 표시합니다.

이 유형의 동적 히트맵을 완성하려면 다음과 같이 하세요:

1. 개발 도구 > 삽입 > 옵션 버튼(양식 컨트롤)을 클릭한 다음 마우스를 드래그하여 두 개의 라디오 버튼을 그리고 필요에 따라 텍스트를 편집합니다. 스크린샷을 참조하세요:

2. 라디오 버튼을 삽입한 후 첫 번째 라디오 버튼을 마우스 오른쪽 버튼으로 클릭하고 서식 컨트롤을 선택합니다. 서식 컨트롤 대화 상자에서 컨트롤 탭 아래 라디오 버튼과 연결된 셀을 선택합니다. 스크린샷을 참조하세요:

3. 확인 버튼을 클릭하여 대화 상자를 닫고 위 단계(2단계)를 반복하여 두 번째 라디오 버튼을 같은 셀(M1 셀)에 연결합니다.

4. 그런 다음 데이터 범위에 조건부 서식을 적용해야 합니다. 데이터 범위를 선택하고 홈 > 조건부 서식 > 새 규칙을 클릭하여 새 서식 규칙 대화 상자로 이동합니다.

5. 새 서식 규칙 대화 상자에서 규칙 유형 선택 목록 상자에서 수식을 사용하여 서식을 지정할 셀 결정을 선택하고 다음 수식을 서식을 지정할 값이 참인 경우 텍스트 상자에 입력한 후 서식 버튼을 클릭하여 색상을 선택합니다. 스크린샷을 참조하세요: =IF($M$1=1,IF(B2>=LARGE($B$2:$I$13,15),TRUE,FALSE))

6. 확인 버튼을 클릭하면 첫 번째 라디오 버튼을 선택했을 때 가장 큰 15개의 값이 빨간색으로 강조 표시됩니다.

7. 가장 작은 15개의 값을 강조 표시하려면 데이터를 계속 선택한 상태로 새 서식 규칙 대화 상자로 이동하여 다음 수식을 서식을 지정할 값이 참인 경우 텍스트 상자에 입력하고 서식 버튼을 클릭하여 필요한 색상을 선택합니다. 스크린샷을 참조하세요: =IF($M$1=2,IF(B2<=SMALL($B$2:$I$13,15),TRUE,FALSE))

참고: 위 수식에서 $M$1은 라디오 버튼과 연결된 셀이고 $B$2:$I$13은 조건부 서식을 적용하려는 데이터 범위이며 B2는 데이터 범위의 첫 번째 셀이고 15는 강조 표시하려는 특정 숫자입니다.

8. 확인을 클릭하여 대화 상자를 닫습니다. 이제 첫 번째 라디오 버튼을 선택하면 가장 큰 15개의 값이 강조 표시되고 두 번째 라디오 버튼을 선택하면 가장 작은 15개의 값이 아래 데모와 같이 강조 표시됩니다:

예제 3: 체크박스를 사용하여 동적인 히트맵 생성하기

이 섹션에서는 필요에 따라 히트맵을 표시하거나 숨길 수 있도록 체크박스를 사용한 동적 히트맵 차트를 소개합니다. 체크박스를 선택하면 히트맵이 표시되고 체크박스를 해제하면 즉시 숨겨집니다. 아래 데모를 참조하세요:

1. 먼저 데이터 범위를 테이블 형식으로 변환해야 합니다. 이렇게 하면 새 데이터 행을 삽입할 때 자동으로 조건부 서식을 적용할 수 있습니다. 데이터 범위를 선택하고 Ctrl + T 키를 함께 눌러 테이블 생성 대화 상자를 엽니다. 스크린샷을 참조하세요:

2. 확인을 클릭하여 대화 상자를 닫고 개발 도구 > 삽입 > 체크박스(양식 컨트롤)를 클릭한 다음 마우스를 드래그하여 체크박스를 그리고 필요에 따라 텍스트를 편집합니다. 아래 스크린샷을 참조하세요:

3. 그런 다음 체크박스를 마우스 오른쪽 버튼으로 클릭하고 서식 컨트롤을 선택합니다. 서식 객체 대화 상자에서 컨트롤 탭 아래 체크박스와 연결된 셀을 선택합니다. 스크린샷을 참조하세요:

4. 확인을 클릭하여 대화 상자를 닫고 히트맵을 생성하려는 데이터 범위를 선택한 후 홈 > 조건부 서식 > 새 규칙을 클릭하여 새 서식 규칙 대화 상자로 이동합니다.

5. 새 서식 규칙 대화 상자에서 다음 작업을 수행하세요:

• 규칙 유형 선택 목록 상자에서 모든 셀을 해당 값에 따라 서식 지정 옵션을 선택합니다;
• 서식 스타일 드롭다운 목록에서 3-색상 척도를 선택합니다;
• 최소값, 중간값 및 최대값 드롭다운 목록 아래의 형식 상자에서 각각 수식을 선택합니다;
• 그런 다음 세 개의 값 텍스트 상자에 다음 수식을 입력합니다:
• 최소값: =IF($M$1=TRUE,MIN($B$2:$I$13),FALSE)
• 중간값: =IF($M$1=TRUE,AVERAGE($B$2:$I$13),FALSE)
• 최대값: =IF($M$1=TRUE,MAX($B$2:$I$13),FALSE)
• 그런 다음 색상 섹션에서 필요에 따라 강조 색상을 지정합니다.

참고: 위 수식에서 $M$1 은 체크박스와 연결된 셀이고 $B$2:$I$13은 조건부 서식을 적용하려는 데이터 범위입니다.

6. 설정을 마친 후 확인 버튼을 클릭하여 대화 상자를 닫습니다. 이제 체크박스를 선택하면 히트맵이 표시되고 그렇지 않으면 숨겨집니다. 아래 데모를 참조하세요:

히트맵 차트 샘플 파일 다운로드

비디오: Excel에서 히트맵 차트 생성하기