Excel에서 동적 명명된 범위를 만드는 방법은 무엇입니까?

일반적으로, 명명된 범위는 Excel 사용자에게 매우 유용합니다. 열의 일련의 값을 정의하고 해당 열에 이름을 지정한 다음 셀 참조 대신 이름으로 해당 범위를 참조할 수 있습니다. 하지만 대부분의 경우, 미래에 참조하는 범위의 데이터 값을 확장하기 위해 새로운 데이터를 추가해야 합니다. 이 경우, 수식 > 이름 관리자로 돌아가서 새 값을 포함하도록 범위를 다시 정의해야 합니다. 이를 방지하기 위해 동적 명명된 범위를 생성할 수 있으며, 이는 목록에 새 행이나 열을 추가할 때마다 셀 참조를 조정할 필요가 없다는 것을 의미합니다.

테이블을 만들어 Excel에서 동적 명명된 범위 만들기

함수를 사용하여 Excel에서 동적 명명된 범위 만들기

VBA 코드를 사용하여 Excel에서 동적 명명된 범위 만들기

테이블을 만들어 Excel에서 동적 명명된 범위 만들기

Excel 2007 또는 이후 버전을 사용 중이라면 동적 명명된 범위를 만드는 가장 쉬운 방법은 명명된 Excel 테이블을 만드는 것입니다.

다음과 같은 데이터 범위가 있고 이를 동적 명명된 범위로 만들어야 한다고 가정해 보겠습니다.

1. 먼저 이 범위에 대해 범위 이름을 정의하겠습니다. A1:A6 범위를 선택하고 이름 상자에 Date를 입력한 다음 Enter 키를 누릅니다. 같은 방법으로 B1:B6 범위에 Saleprice라는 이름을 정의합니다. 동시에 빈 셀에 =sum(Saleprice) 공식을 작성합니다. 스크린샷을 참조하세요:

2. 범위를 선택하고 삽입 > 테이블을 클릭합니다. 스크린샷을 참조하세요:

3. 테이블 생성 대화 상자에서 내 테이블에 제목이 있음(범위에 제목이 없는 경우 선택 해제)을 체크하고 확인 버튼을 클릭하면 범위 데이터가 테이블로 변환됩니다. 스크린샷을 참조하세요:

4. 그리고 데이터 뒤에 새 값을 입력하면 명명된 범위가 자동으로 조정되고 생성된 수식도 변경됩니다. 다음 스크린샷을 참조하세요:

참고:

1. 새로 입력하는 데이터는 위의 데이터와 인접해야 하며, 즉 새 데이터와 기존 데이터 사이에 빈 행이나 열이 없어야 합니다.

2. 테이블에서는 기존 값 사이에 데이터를 삽입할 수 있습니다.

함수를 사용하여 Excel에서 동적 명명된 범위 만들기

Excel 2003 또는 이전 버전에서는 첫 번째 방법을 사용할 수 없습니다. 따라서 여기에 다른 방법이 있습니다. 다음 OFFSET() 함수는 이를 처리할 수 있지만 다소 복잡합니다. 예를 들어, 정의한 범위 이름을 포함하는 데이터 범위가 있다고 가정해 보겠습니다. 예를 들어, A1:A6 범위 이름은 Date이고, B1:B6 범위 이름은 Saleprice이며, 동시에 Saleprice에 대한 수식을 작성합니다. 스크린샷을 참조하세요:

다음 단계에 따라 범위 이름을 동적 범위 이름으로 변경할 수 있습니다:

1. 수식 > 이름 관리자를 클릭합니다. 스크린샷을 참조하세요:

2. 이름 관리자 대화 상자에서 사용하려는 항목을 선택하고 편집 버튼을 클릭합니다.

3. 나타난 이름 편집 대화 상자에서 Refers to 텍스트 상자에 다음 수식 =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)을 입력합니다. 스크린샷을 참조하세요:

4. 그런 다음 확인을 클릭하고 2단계와 3단계를 반복하여 Saleprice 범위 이름에 대해 Refers to 텍스트 상자에 다음 수식 =OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B), 1)을 복사합니다.

5. 이제 동적 명명된 범위가 생성되었습니다. 데이터 뒤에 새 값을 입력하면 명명된 범위가 자동으로 조정되고 생성된 수식도 변경됩니다. 스크린샷을 참조하세요:

참고: 범위 중간에 빈 셀이 있는 경우 수식 결과가 잘못될 수 있습니다. 그 이유는 비어 있지 않은 셀이 계산되지 않아 범위가 짧아지고 마지막 셀이 범위에서 제외되기 때문입니다.

팁: 이 수식에 대한 설명:

• =OFFSET(참조,행,열,[높이],[너비])
• 
• =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)

• 참조는 시작 셀 위치에 해당하며, 이 예에서는 Sheet1!$A$1입니다.
• 행은 시작 셀에 대해 아래로 이동할 행 수(또는 음수 값을 사용할 경우 위로 이동)를 나타냅니다. 이 예에서는 0이므로 목록은 첫 번째 행부터 시작됩니다.
• 열은 시작 셀에 대해 오른쪽으로 이동할 열 수(또는 음수 값을 사용할 경우 왼쪽으로 이동)를 나타냅니다. 위의 예제 수식에서는 0이므로 오른쪽으로 0열 확장됩니다.
• [높이]는 조정된 위치에서 시작하는 범위의 높이(또는 행 수)에 해당합니다. $A:$A는 열 A에 입력된 모든 항목을 계산합니다.
• [너비]는 조정된 위치에서 시작하는 범위의 너비(또는 열 수)에 해당합니다. 위의 수식에서는 목록이 1열 너비가 됩니다.

필요에 따라 이러한 인수를 변경할 수 있습니다.

VBA 코드를 사용하여 Excel에서 동적 명명된 범위 만들기

여러 열이 있는 경우 나머지 모든 열에 대해 개별 수식을 반복해서 입력할 수 있지만, 이는 길고 반복적인 과정입니다. 더 쉽게 하기 위해 동적 명명된 범위를 자동으로 생성하는 코드를 사용할 수 있습니다.

1. 워크시트를 활성화합니다.

2. ALT + F11 키를 누르고 있으면 Microsoft Visual Basic for Applications 창이 열립니다.

3. 삽입 > 모듈을 클릭하고 모듈 창에 다음 코드를 붙여넣습니다.

VBA 코드: 동적 명명된 범위 만들기

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. 그런 다음 F5 키를 눌러 코드를 실행하면 첫 번째 행 값으로 명명된 몇 가지 동적 명명된 범위가 생성되며 전체 데이터를 포함하는 MyData라는 동적 범위도 생성됩니다.

5. 행 또는 열 뒤에 새 값을 입력하면 범위도 확장됩니다. 스크린샷을 참조하세요:

참고:

1. 이 코드를 사용하면 범위 이름이 이름 상자에 표시되지 않습니다. 범위 이름을 편리하게 보기 및 사용하기 위해 Kutools for Excel을 설치했으며, 탐색 창을 통해 생성된 동적 범위 이름이 나열됩니다.

2. 이 코드를 사용하면 데이터의 전체 범위가 세로 또는 가로로 확장될 수 있지만, 새 값을 입력할 때 데이터 사이에 빈 행이나 열이 없어야 합니다.

3. 이 코드를 사용할 때 데이터 범위는 셀 A1에서 시작해야 합니다.

관련 기사:

Excel에서 새 데이터를 입력한 후 차트를 자동으로 업데이트하려면 어떻게 해야 합니까?