Excel에 크기를 조정할 수 있는 데이터가 포함된 테이블이 있습니까? 즉, 작업 과정에서 행(열) 수가 증가하거나 감소할 수 있습니까? 테이블 크기가 "부동"이면이 순간을 지속적으로 모니터링하고 수정해야합니다.
- 표를 참조하는 보고서 공식의 링크
- 테이블에 따라 작성된 피벗 테이블의 초기 범위
- 우리 테이블에 따라 작성된 차트의 초기 범위
- 테이블을 데이터 소스로 사용하는 드롭다운 범위
이 모든 것이 당신을 지루하게 만들지 않을 것입니다 😉
동적 "고무" 범위를 만드는 것이 훨씬 더 편리하고 정확할 것입니다. 이 범위는 데이터의 실제 행과 열 수에 맞게 크기가 자동으로 조정됩니다. 이를 구현하기 위해서는 여러 가지 방법이 있습니다.
방법 1. 스마트 테이블
셀 범위를 강조 표시하고 탭에서 선택 홈 – 표 형식(홈 – 표 형식):
부작용으로 표에 추가되는 스트라이프 디자인이 필요없으시다면 나타나는 탭에서 끄시면 됩니다 생성자(디자인). 이러한 방식으로 생성된 각 테이블은 탭의 같은 위치에서 보다 편리한 이름으로 대체할 수 있는 이름을 받습니다. 생성자(디자인) 현장에서 테이블 이름 (테이블 이름).
이제 "스마트 테이블"에 대한 동적 링크를 사용할 수 있습니다.
- 표 1 – 헤더 행(A2:D5)을 제외한 전체 테이블에 대한 링크
- 표1[#전체] – 전체 테이블 링크(A1:D5)
- 표1[피터] – 첫 번째 셀 헤더가 없는 범위 열에 대한 참조(C2:C5)
- Table1[#헤더] – 열 이름이 있는 "헤더"에 대한 링크(A1:D1)
이러한 참조는 다음과 같은 수식에서 잘 작동합니다.
= SUM (표1[모스크바]) – "모스크바" 열의 합계 계산
or
=VPR(F5;표 1;3;0) – F5 셀에서 해당 월에 대한 테이블을 검색하고 이에 대한 상트페테르부르크 합계를 발행합니다(VLOOKUP이란?)
이러한 링크는 탭에서 선택하여 피벗 테이블을 만들 때 성공적으로 사용할 수 있습니다. 삽입 – 피벗 테이블(삽입 – 피벗 테이블) 스마트 테이블의 이름을 데이터 소스로 입력:
이러한 테이블의 일부(예: 처음 두 열)를 선택하고 모든 유형의 다이어그램을 생성하면 새 라인을 추가할 때 다이어그램에 자동으로 추가됩니다.
드롭다운 목록을 생성할 때 스마트 테이블 요소에 대한 직접 링크를 사용할 수 없지만 전술적 트릭을 사용하여 이 제한을 쉽게 해결할 수 있습니다. 간접적 인 (간접), 텍스트를 링크로 바꿉니다.
저것들. 텍스트 문자열 형식의 스마트 테이블에 대한 링크(따옴표!)는 본격적인 링크로 바뀌고 드롭다운 목록은 일반적으로 이를 인식합니다.
방법 2: 동적 명명된 범위
어떤 이유로 데이터를 스마트 테이블로 변환하는 것이 바람직하지 않은 경우 약간 더 복잡하지만 훨씬 더 미묘하고 다재다능한 방법을 사용할 수 있습니다. 즉, Excel에서 테이블을 참조하는 동적 명명 범위를 만듭니다. 그러면 스마트 테이블의 경우와 같이 생성된 범위의 이름을 수식, 보고서, 차트 등에 자유롭게 사용할 수 있습니다. 간단한 예부터 시작하겠습니다.
태스크: 도시 목록을 참조하는 동적 명명 범위를 만들고 새 도시를 추가하거나 삭제할 때 크기가 자동으로 늘어나고 축소됩니다.
모든 버전에서 사용할 수 있는 두 가지 기본 제공 Excel 기능이 필요합니다. 포이포즈 (성냥) 범위의 마지막 셀을 결정하고 INDEX (인덱스) 동적 링크를 생성합니다.
MATCH를 사용하여 마지막 셀 찾기
MATCH(lookup_value, 범위, match_type) – 범위(행 또는 열)에서 주어진 값을 검색하고 발견된 셀의 서수를 반환하는 함수입니다. 예를 들어 수식 MATCH("March";A1:A5;0)는 "March"라는 단어가 A4:A1 열의 네 번째 셀에 있기 때문에 결과적으로 숫자 5를 반환합니다. 마지막 함수 인수 Match_Type = 0은 정확히 일치하는 항목을 찾고 있음을 의미합니다. 이 인수가 지정되지 않은 경우 함수는 가장 가까운 가장 작은 값에 대한 검색 모드로 전환됩니다. 이것이 바로 배열에서 마지막으로 점유된 셀을 찾는 데 성공적으로 사용할 수 있는 것입니다.
트릭의 본질은 간단합니다. MATCH는 위에서 아래로 범위의 셀을 검색하며 이론상 주어진 값에 가장 가까운 가장 작은 값을 찾으면 중지되어야 합니다. 테이블에서 사용 가능한 값보다 분명히 큰 값을 원하는 값으로 지정하면 MATCH는 테이블의 맨 끝에 도달하고 아무 것도 찾지 않고 마지막으로 채워진 셀의 시퀀스 번호를 제공합니다. 그리고 우리는 그것을 필요로합니다!
배열에 숫자만 있는 경우 원하는 값으로 숫자를 지정할 수 있습니다. 이 값은 테이블에 있는 것보다 분명히 더 큽니다.
보증을 위해 Excel에서 원칙적으로 사용할 수 있는 최대 숫자인 9E + 307(9 곱하기 10의 307, 즉 9의 307 XNUMX)을 사용할 수 있습니다.
열에 텍스트 값이 있는 경우 가능한 가장 큰 숫자에 해당하는 구성으로 REPEAT("i", 255) - 255개의 문자 "i"로 구성된 텍스트 문자열 - 마지막 문자 알파벳. Excel은 실제로 검색할 때 문자 코드를 비교하므로 테이블의 모든 텍스트는 기술적으로 "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" 줄보다 "작습니다".
INDEX를 사용하여 링크 생성
이제 테이블에서 비어 있지 않은 마지막 요소의 위치를 알았으므로 전체 범위에 대한 링크를 형성해야 합니다. 이를 위해 다음 기능을 사용합니다.
INDEX(범위; row_num; column_num)
범위의 셀 내용을 행과 열 번호로 제공합니다. 예를 들어 이전 방법의 도시와 월이 있는 테이블의 =INDEX(A1:D5;3;4) 함수는 1240 – 내용을 제공합니다. 세 번째 행과 네 번째 열, 즉 D3 셀. 열이 하나만 있는 경우 해당 번호를 생략할 수 있습니다. 예를 들어 공식 INDEX(A4:A3;2)는 마지막 스크린샷에서 "Samara"를 제공합니다.
그리고 완전히 명확하지 않은 뉘앙스가 있습니다. INDEX가 평소와 같이 = 기호 뒤의 셀에 입력되지 않고 콜론 뒤 범위에 대한 참조의 마지막 부분으로 사용되면 더 이상 표시되지 않습니다. 셀의 내용이지만 주소! 따라서 $A$2:INDEX($A$2:$A$100;3)와 같은 수식은 출력에서 A2:A4 범위에 대한 참조를 제공합니다.
그리고 이것이 MATCH 함수가 들어오는 곳이며, INDEX 내부에 삽입하여 목록의 끝을 동적으로 결정합니다.
=$A$2:INDEX($A$2:$A$100; MATCH(대표("나";255);A2:A100))
명명된 범위 만들기
모든 것을 하나의 전체로 포장하는 것이 남아 있습니다. 탭 열기 공식 (방식) 그리고 이름 관리자 (이름 관리자). 열리는 창에서 버튼을 클릭하십시오 만들기 (뉴), 필드에 범위 이름과 공식을 입력하십시오. 범위 (참고):
클릭하는 것이 남아 있습니다. OK 준비 범위는 모든 수식, 드롭다운 목록 또는 차트에서 사용할 수 있습니다.
- VLOOKUP 함수를 사용하여 테이블 및 조회 값 연결
- 자동 채우기 드롭다운 목록을 만드는 방법
- 많은 양의 데이터를 분석하기 위해 피벗 테이블을 만드는 방법