동적 목록에서 마지막 값을 검색하기 위해 Microsoft Excel 2010/2013의 OFFSET 및 COUNTA 함수와 함께 데이터 유효성 검사 옵션을 사용합니다.

COUNTA : 값을 포함하는 셀 수를 반환합니다.

“COUNTA”함수 구문 : = COUNTA (값 1, 값 2, 값 3 …….)

_ 예 : _ _ 범위 A1 : A5, 셀 A2, A3 및 A5에는 값이 포함되고 셀 A1 및 A4는 비어 있습니다. 셀 A6을 선택하고 공식을 작성하십시오 .-_ _ = COUNTA (A1 : A5) _ _ 함수는 3_을 반환합니다.

img1

OFFSET : 다른 범위 또는 셀에서 여러 행과 열을 오프셋하는 범위에 대한 참조를 반환합니다.

OFFSET 함수 구문 : = OFFSET (참조, 행, 열, 높이, 너비) Reference : – _ 오프셋하려는 셀 또는 범위입니다 ._

_ 이동할 행 및 열 : _ – 시작점을 이동할 행 수와 둘 다 양수, 음수 또는 0 일 수 있습니다 .

_Height and Width _ * :-반환하려는 범위의 크기입니다. 이것은 선택적 필드입니다 . Excel의 오프셋 기능을 이해하는 예를 들어 보겠습니다.

A1 : D10 범위의 데이터가 있습니다. A 열에는 제품 코드가, B 열에는 수량이, C 열에는 제품 당 비용이, D 열에는 총 비용이 포함됩니다. E2 셀의 C5 셀 값을 반환해야합니다.

img2

아래 언급 된 단계를 따르십시오.

  • 셀 E2를 선택하고 수식을 작성하십시오.

OFFSET (A1,4,2,1,1) * 키보드에서 Enter 키를 누릅니다.

  • 이 함수는 C5 . 셀의 값을 반환합니다

img3

img4

이 예에서는 C5 셀에서 E2로 값을 가져와야합니다. 참조 셀은 A1 범위의 첫 번째 셀이며 C5는 A1에서 아래 4 행과 오른쪽에 2 열입니다. 따라서 수식은 = OFFSET (A1,4,2,1,1) 또는 = OFFSET (A1,4,2)입니다 (1,1은 선택 사항이므로).

이제 동적 목록에서 마지막 값을 검색하는 예를 들어 보겠습니다.

범위에 국가 이름이 있습니다. 이제이 목록에 더 많은 국가를 추가하면 드롭 다운 목록에서 자동으로 사용할 수 있습니다.

img5

동적 목록을 준비하려면 열의 마지막 값을 검색하고 새 번호가 추가되면 자동으로 업데이트되는 수식을 만들어야합니다.

아래 주어진 단계를 따르십시오 .- * 셀 B2를 선택하십시오.

  • 데이터 탭으로 이동하여 데이터 도구 그룹에서 데이터 유효성 검사를 선택합니다.

img6

  • “데이터 유효성 검사”대화 상자가 나타납니다. “설정”탭의 허용 드롭 다운 목록에서 “사용자 지정”을 선택합니다.

img7

  • 수식 상자가 활성화됩니다.

  • 이 상자에 공식을 작성하십시오.

* _ = OFFSET (A : A, 1,0, COUNTA (A : A) -1,1) ._

  • 확인을 클릭합니다.

__

img8

  • 이 단계에서 마지막으로 업데이트 된 셀은 A11입니다.

img9

  • 데이터 유효성 검사가 제대로 작동하는지 확인하려면 A12 셀에 도시 이름을 추가하십시오.

img10

A12에 항목을 추가하자마자 드롭 다운 목록에 추가됩니다.

Microsoft Excel 2010 및 2013에서 동적 목록을 만들고 새 항목을 자동으로 채울 수있는 방법입니다 .

__