다양한 OFFSET 동적 테이블을 학습하는 Excel 차트

겨울의 시작인데 춥네요! 집에 난방이 안되나요? 그냥 움직이고, 발을 구르고, 손을 비비면 따뜻함을 느낄 것입니다. 겨울이 시작되고 또 한해가 끝나가는군요! 아직 급여 인상은 없나요? 시계가 과거와 다르게 움직이고, 동시에 업데이트되고, 동적으로 표시되도록 하면 문제가 해결될 뿐만 아니라 상사도 "급여"를 공개하고 싶어할 것입니다...

첫 번째 조치: 감동하세요. 움직이는 차트가 상사에게 감동을 줄 것입니다

상사님이 보고하던 평범한 테이블(아래)에 익숙해지셨는데, 이제 갑자기 동적으로 쿼리할 수 있는 차트(아래)를 보여주시면 성공할 수 있다고 생각하시나요? 상사의 까다로운 마음? 보상으로 상사를 놀라게 할 수 있나요?

현재 보고서                                                                                                                                                                    내일 보고서

이 변화를 달성하는 방법은 무엇입니까? 매우 간단합니다. 동적 차트를 만드세요.

동적 차트는 상사가 다른 영역을 선택하고 차트에 다른 데이터가 표시된다는 의미입니다. 이를 달성하기 위한 세 단계가 있습니다. 한 단계는 상사가 선택할 수 있는 드롭다운 메뉴를 만드는 것이고, 한 단계는 선택에 따라 동적으로 변경되는 데이터 영역을 만드는 것이고, 한 단계는 이를 기반으로 차트를 삽입하는 것입니다. 동적 데이터 영역.

동적 데이터 영역은 일반적으로 VLOOKUP 함수로 구현되지만 오늘은 OFFSET 함수를 사용하여 동적 데이터 영역을 완성해 보겠습니다.

1단계: 드롭다운 선택하기

1. J1 셀을 선택하고 "데이터" 탭에서 "데이터 확인"을 클릭하세요.

2. "데이터 유효성 검사" 창 하단의 "설정" 옵션에서 "허용"을 "시리즈"로 선택하고, 5개 판매 지역이 위치한 셀을 소스 "=$"로 선택합니다. A$2:$A$6". 확인을 클릭합니다.

2단계: 동적으로 업데이트되는 보조 데이터 영역 만들기

3. 다음 단계는 셀 J1의 값을 기반으로 차트를 동적으로 업데이트하는 것입니다. J1은 "베이징 지역"을 선택합니다. 셀 B7에 "=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)"을 입력합니다. 그런 다음 수식이 G7 셀의 오른쪽에 채워집니다. 이러한 방식으로 셀 B7:G7은 1월부터 6월까지 베이징 지역의 판매량을 반환합니다.

분석:

참조 시스템으로 "B1"을 사용하려면 오프셋 행 수를 사용하여 $A$2:$A$6에서 $J$1의 위치를 ​​가져옵니다. 오프셋 열 번호가 0입니다. 오프셋이 없습니다. 그림 J1에 표시된 대로 값은 "Beijing Area"이고 $A$2:$A$6의 위치는 1입니다. OFFSET에서 반환된 값은 "B1"을 기준 시스템으로 한 행 아래의 참조 오프셋입니다. 이러한 방식으로 선택한 영역 $J$1이 계속 변경됨에 따라 셀 B7:G7은 해당 영역의 판매 데이터를 가져옵니다.

4. 그런 다음 평균 라인 데이터를 설정하고 B8 셀에 "=AVERAGE($B$7:$G$7)"를 입력하여 $B$7:$G$7의 평균을 구합니다. 그런 다음 수식이 G8 셀의 오른쪽에 채워집니다. 선택한 영역 $J$1이 변경되면 $B$7:$G$7이 변경되고 이에 따라 평균값도 변경됩니다.

3단계: 차트 만들기

5. 설정된 보조 행을 기반으로 차트를 만듭니다. 머리글 B1:G1과 보조 행 B7:G8 영역을 선택하고 "삽입" 탭 아래의 "차트" 그룹에서 "2차원 기둥형 차트"를 클릭합니다.

6. 셀 K1에 "=J1&"Sales Data""를 입력합니다.

차트 제목 상자를 클릭하고 편집바에 "=Sheet2!$K$1"을 입력하면 차트 제목과 데이터 확인 영역이 동시에 업데이트됩니다.

7. "차트 도구" 아래의 "디자인" 탭에서 "차트 유형 변경"을 클릭하세요.

8. "차트 유형 변경" 창에서 "모든 차트" 옵션 아래의 "조합"을 클릭하고, 평균값이 있는 계열을 "선형 차트"로 변경합니다.

9. 마지막으로 차트 범례를 삭제하고 보조 데이터 B7:G8 및 K1 셀의 글꼴을 흰색으로 보이지 않게 변경하면 완료됩니다.

두 번째 작업: 피벗 테이블과 데이터 소스가 동기적으로 업데이트될 수 있도록 연결

피벗 테이블이 데이터 소스와 동기적으로 업데이트되기를 원한다면 앞서 슈퍼 테이블을 소개한 적이 있습니다. 또한 OFFSET을 사용할 수도 있습니다. 그림과 같이 왼쪽의 데이터 소스를 기준으로 오른쪽의 피벗 테이블이 삽입됩니다.

이제 다음과 같이 데이터 소스가 업데이트된 후 피벗 테이블을 동시에 업데이트해야 합니다.

1단계: 이름 정의

1. "수식"에서 "정의됨"을 클릭합니다. " 탭의 이름 옵션 그룹에서 "이름 정의"를 탭합니다.

2. "새 이름" 창에서 "이름" 열에 "Data"를 입력하고 "참조 위치"에 다음 수식을 입력합니다.

=OFFSET(Sheet1!$A$1,,,COUNTA( Sheet1!$A: $A),COUNTA(Sheet1!$1:$1))

Analytic:

"Sheet1"은 데이터가 있는 워크시트입니다. 이 함수는 "Sheet1!$A$1"을 참조 시스템으로 사용하고 오프셋을 적용하지 않으며(오프셋 행과 열이 비어 있음) 전체 테이블 데이터를 동적으로 반환합니다. COUNTA(Sheet1!$A:$A)는 테이블 데이터의 행 개수를 가져오는 데 사용되고, COUNTA(Sheet1!$1:$1)는 테이블 데이터의 열 개수를 가져오는 데 사용됩니다. 얻은 결과는 동적이며 테이블의 행과 열 수가 증가하거나 감소함에 따라 변경됩니다.

2단계: 데이터 소스 변경

3. 피벗 테이블의 아무 셀이나 클릭하면 "피벗 테이블 도구"가 나타납니다. 그런 다음 "피벗 테이블 도구" 아래의 "분석" 탭에서 "데이터 원본 변경"을 클릭합니다.

4. "피벗 테이블 데이터 원본 변경" 창에서 "테이블/범위"를 방금 정의한 이름인 "데이터"로 변경하고 확인을 클릭합니다.

3단계: 동기화 새로 고침

5. 그런 다음 데이터의 마지막 행에 데이터를 추가합니다.

6. 피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 "새로 고침" 명령을 선택하면 피벗 테이블이 업데이트됩니다.

오프셋 기능을 사용하여 두 가지 "액션"을 구현했습니다. 첫 번째 액션은 상사가 "급여"를 지불할 수 있도록 동적 차트를 사용하는 것입니다. 데이터 소스를 사용하면 더 많은 문제를 줄일 수 있습니다. 좋아요, 서둘러서 양식을 움직여 보세요! 겨울이 다가오고 있습니다. 손발이 차가워지는 것은 피할 수 없지만 월급 인상으로 마음이 따뜻해질 것입니다!

관련 학습 권장 사항: excel 튜토리얼

위 내용은 다양한 OFFSET 동적 테이블을 학습하는 Excel 차트의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!