필터링에 사용할 수있는 항목 수는 제한되어 있습니다. Excel은 항목 수가 999 개 (행 수가 아님)를 초과하는 열을 필터링 할 수 없습니다.

999 개 이상의 항목이있을 때 필터링하려면 고급 필터를 사용하십시오.

고급 필터를 만들기 위해 Microsoft Excel에서 “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에서 999 개 이상의 항목을 추가 할 수있는 방법입니다 .