선택한 셀별 차트

당신과 내가 2021년에 여러 국가의 자동차 판매 가치로 다음 표의 데이터를 시각화해야 한다고 가정합니다(여기에서 가져온 실제 데이터).

데이터 시리즈(국가)의 수가 많기 때문에 모든 데이터를 한 번에 하나의 그래프에 집어 넣으려고 하면 끔찍한 "스파게티 차트"가 생성되거나 각 시리즈에 대해 별도의 차트를 작성하는 것이 매우 번거롭습니다.

이 문제에 대한 우아한 해결책은 현재 행, 즉 활성 셀이 있는 행의 데이터에만 차트를 그리는 것입니다.

이를 구현하는 것은 매우 쉽습니다. 3줄에 두 개의 공식과 하나의 작은 매크로만 있으면 됩니다.

1단계. 현재 라인 번호

가장 먼저 필요한 것은 현재 활성 셀이 있는 시트의 행 번호를 계산하는 명명된 범위입니다. 탭에서 열기 수식 – 이름 관리자 (수식 — 이름 관리자), 버튼을 클릭하십시오 만들기 (창조하다) 거기에 다음 구조를 입력하십시오.

2단계. 제목 링크

차트의 제목과 범례에 선택한 국가를 표시하려면 첫 번째 열에서 (국가) 이름이 있는 셀에 대한 참조를 가져와야 합니다. 이를 위해 다른 로컬(예: Area = 현재 시트, 책 아님!) 다음 공식을 사용하여 명명된 범위:

여기에서 INDEX 함수는 우리가 이전에 결정한 행 번호가 있는 셀을 주어진 범위(서명 국가가 있는 열 A)에서 선택합니다.

3단계. 데이터 연결

이제 유사한 방식으로 현재 활성 셀이 있는 현재 행의 모든 ​​판매 데이터가 있는 범위에 대한 링크를 가져오겠습니다. 다음 수식을 사용하여 다른 명명된 범위를 만듭니다.

여기서 XNUMX인 세 번째 인수는 INDEX가 단일 값이 아니라 결과적으로 전체 행을 반환하도록 합니다.

4단계. 차트의 링크 대체

이제 테이블 머리글과 데이터(범위)가 있는 첫 번째 행을 선택하고 다음을 사용하여 차트를 작성합니다. 삽입 – 차트 (삽입 — 차트). 차트에서 데이터가 있는 행을 선택하면 함수가 수식 입력줄에 표시됩니다. 열 (시리즈) Excel에서 원본 데이터와 레이블을 참조하는 차트를 만들 때 자동으로 사용하는 특수 기능입니다.

이 함수의 첫 번째(서명) 인수와 세 번째(데이터) 인수를 2단계와 3단계의 범위 이름으로 조심스럽게 교체해 보겠습니다.

차트는 현재 행의 판매 데이터를 표시하기 시작합니다.

5단계. 매크로 재계산

마지막 터치가 남아 있습니다. Microsoft Excel은 시트의 데이터가 변경되거나 키를 누를 때만 수식을 다시 계산합니다. F9, 그리고 선택이 변경될 때, 즉 활성 셀이 시트를 가로질러 이동할 때 재계산이 일어나기를 원합니다. 이렇게 하려면 통합 문서에 간단한 매크로를 추가해야 합니다.

데이터 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택합니다. 출처 (소스 코드). 열리는 창에서 선택 변경 이벤트에 대한 매크로 처리기 코드를 입력합니다.

쉽게 상상할 수 있듯이 활성 셀의 위치가 변경될 때마다 시트 재계산을 트리거하기만 하면 됩니다.

6단계. 현재 라인 강조 표시

명확성을 위해 조건부 서식 규칙을 추가하여 현재 차트에 표시된 국가를 강조 표시할 수도 있습니다. 이렇게 하려면 테이블을 선택하고 홈 — 조건부 서식 — 규칙 만들기 — 공식을 사용하여 서식을 지정할 셀 결정 (홈 — 조건부 서식 — 새 규칙 — 수식을 사용하여 서식을 지정할 셀 결정):

여기서 수식은 테이블의 각 셀에 대해 행 번호가 TekRow 변수에 저장된 번호와 일치하는지 확인하고 일치하는 경우 선택한 색상으로 채우기가 트리거됩니다.

그게 다야 - 간단하고 아름답죠?

노트

• 큰 테이블에서는 이 모든 아름다움이 느려질 수 있습니다. 조건부 서식은 리소스 집약적인 작업이며 각 선택 항목에 대한 재계산도 무거울 수 있습니다.
• 셀이 실수로 테이블 위나 아래에 선택되었을 때 차트에서 데이터가 사라지는 것을 방지하려면 다음 형식의 중첩 IF 함수를 사용하여 TekRow 이름에 추가 검사를 추가할 수 있습니다.

  =IF(CELL("행")<4,IF(CELL("행")>4,CELL("행")))
  

• 차트에서 지정된 열 강조 표시
• Excel에서 대화형 차트를 만드는 방법
• 좌표 선택