Excel Dynamic Named Range는 Excel 사용자가 잘 알지 못하는 Excel 기능 중 하나입니다. 이 문서는 Dynamic Named Ranges를 더 가깝게 만듭니다. 다음 줄에는 Excel Dynamic Named Range와 관련된 힌트와 요령이 표시됩니다.
Excel의 OFFSET 함수를 사용한 동적 명명 범위
다이나믹 레인지를 만들기 위해 Offset 기능 사용할 수 있습니다.
엑셀의 오프셋 기능
다음은 Excel의 OFFSET 함수 구문입니다.
OFFSET(참조, 행, 열, [높이], [너비])
![Excel 동적 명명 범위 [4가지 방법]](/article/uploadfiles/202210/2022103116295381.png)
함수에서 인수가 의미하는 것:
- 참조 – 오프셋의 기준이 되는 참조입니다.
- 행 – 참조 셀에서 위 또는 아래로 행의 수입니다.
- 열 – 참조 셀에서 오른쪽 또는 왼쪽에 있는 열의 수입니다.
- 높이, 너비 – 참조된 셀에서 선택 영역의 높이와 너비를 정의합니다.
OFFSET 기능이 있는 동적 명명 범위
이름 관리자 대화 상자에서 , 동적 명명 범위를 정의하는 데 사용할 수 있습니다.
방법은 다음과 같습니다.
수식으로 이동 탭의 정의된 이름 명령 그룹에서 이름 정의를 클릭합니다. 명령을 실행하면 새 이름이 표시됩니다. 다음과 같은 대화 상자:
![Excel 동적 명명 범위 [4가지 방법]](/article/uploadfiles/202210/2022103116295379.png)
같은 열에서 A1보다 낮은 한 셀을 시작하는 모든 셀 범위를 선택하기 위해 Dynamic Named Range가 생성되고 아래 이미지의 예에서 K1 및 K2 셀에 지정된 열과 행 수를 선택합니다.
![Excel 동적 명명 범위 [4가지 방법]](/article/uploadfiles/202210/2022103116295361.png)
"참조" 필드의 공식은 다음과 같습니다.
=OFFSET(시트1!$A$1;1;0;시트1!$K$1;시트1!$K$2)
OFFSET 함수의 모든 요소는 동적으로 만들 수 있습니다. 예를 들어 특정 날짜의 날짜를 선택하고 싶습니다.
다음은 일부 출력입니다.
=OFFSET(Sheet1!$A$1; 1; 0; 6; 2) =A2:B7 범위를 참조합니다.
![Excel 동적 명명 범위 [4가지 방법]](/article/uploadfiles/202210/2022103116295367.png)
K1, K2, K3 및 K4의 항목에 따라 달라지는 수식은 다음과 같아야 합니다.
=OFFSET(시트1!$A$1;시트1!$K$3;시트1!$K$4;시트1!$K$1;시트1!$K$2)
Excel의 다른 함수와 마찬가지로 OFFSET은 결과적으로 숫자를 제공하는 다른 함수와 결합될 수도 있습니다. 월요일을 참조로 하여 지난 주를 제외한 모든 화요일 날짜를 선택하고 싶다고 가정해 보겠습니다. 동일한 예를 기반으로 수식은 다음과 같아야 합니다.
=OFFSET(시트1!$A$1;1;1;COUNT(시트1!$B:$B);1)
자세히 알아보기:셀 값을 기반으로 하는 Excel 동적 명명 범위(5가지 쉬운 방법)
유사한 수치
- 셀 값 기반 Excel 동적 범위
- Excel VBA:셀 값 기반 동적 범위(3가지 방법)
- Excel에서 VBA로 마지막 행에 동적 범위를 사용하는 방법(3가지 방법)
INDEX 기능이 있는 동적 명명 범위
예를 들어 INDEX와 같은 다른 기능을 Dynamic Named Ranges에 사용할 수도 있습니다. "월요일"이 몇 개나 있더라도 모두 선택하고 싶습니다.
=시트1!$A$2:INDEX(시트1!$A:$A;COUNTA(시트1!$A:$A))
참조도 동적으로 만들려면 수식을 조합하여 사용할 수 있습니다. 한 셀에 요일 이름을 입력한 다음 테이블에서 해당 요일에 속하는 모든 날짜를 선택하려고 한다고 가정해 보겠습니다.
=OFFSET(INDIRECT(ADDRESS(2;MATCH(시트1!$K$6;시트1!$1:$1;0)));0;0;COUNT(시트1!$A:$A);1)
자세히 알아보기:효과적인 방법으로 여러 열을 오프셋하는 Excel 오프셋
VBA를 사용한 동적 명명 범위
전문가들은 일상 업무에서 동일한 작업을 반복해서 수행해야 하는 상황에 직면합니다. Dynamic Named Ranges를 생성할 때도 같은 일이 발생할 수 있습니다. 반복 작업을 피하기 위해 VBA를 사용할 수도 있습니다.
VBA는 수식과 정확히 같은 방식으로 작동합니다. 명명된 범위를 추가하는 데 특징적인 구문을 따르기만 하면 됩니다.
구문은 다음과 같습니다.
ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”
예를 들어 이전 예제 중 하나를 VBA에 쉽게 적용할 수 있습니다. 완전한 매크로는 다음과 같습니다.
하위 이름 지정()
ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”
종료 서브
자세히 알아보기:Excel에서 VBA를 사용하여 동적 명명 범위 만들기(단계별 지침)
빈 셀이 있는 동적 명명 범위
모든 셀이 채워지지 않았거나, 즉 일부 셀이 비어 있는 경우 종종 문제에 직면할 수 있습니다. 가장 중요한 것은 참조를 찾는 데 사용되는 공식의 정의를 아는 것입니다. 예를 들어 COUNTA는 모든 NON-BLANK!를 계산하는 함수입니다. 범위의 셀. 공백을 포함할지 여부에 따라 다릅니다.
아래 이미지는 빈 셀이 있는 상황을 보여줍니다.
![Excel 동적 명명 범위 [4가지 방법]](/article/uploadfiles/202210/2022103116295332.png)
모든 "월요일"을 선택하려는 경우 이전 예와 같은 수식을 사용하면 매주 월요일을 선택하지만 빈 셀(A5)이 있으므로 마지막 월요일을 선택합니다. 이에 대한 해결책은 다음 공식의 조합인 다른 공식을 사용하는 것입니다.
=OFFSET(시트1!$A$1;1;0;SUMPRODUCT(MAX((시트1!$A:$A<>””)*ROW(시트1!$A:$A)))-1;1 )
A2에서 A7까지의 모든 셀을 선택합니다.
앞의 줄과 예제는 작업을 더 빠르고 효율적으로 수행하는 데 도움이 됩니다. Dynamic Named Ranges는 탐색할 수 있는 많은 옵션이 있는 거대한 영역을 나타내며 기술과 창의성에 따라 유용한 도구가 될 수 있습니다. 많은 조합이 있는 것으로 나타났으며 해당 응용 프로그램을 찾는 것은 귀하에게 달려 있습니다.
자세히 보기:Excel에서 셀 값을 기반으로 동적 합계 범위 만들기(4가지 방법)
작업 파일 다운로드
아래 링크에서 작업 파일을 다운로드하십시오.
관련 기사
- Excel에서 Dynamic Range VBA를 사용하는 방법(11가지 방법)
- Excel 테이블 동적 범위를 사용한 데이터 유효성 검사 드롭다운 목록
- Excel에서 동적 차트 범위 만들기(2가지 방법)
- Excel에서 숫자 범위를 만드는 방법(3가지 쉬운 방법)