엑셀 함수 OFFSET 이중으로 유효성 검사 만들기

엑셀을 사용할 때 유효성 검사 기능으로 목록을 지정해서 원하는 값만 입력할 수 있도록 할 수 있는데요. OFFSET 함수를 이용해서 고급지게 이중으로 유효성 검사를 해서 카테고리가 두 개 있을 때 적용하는 방법에 대해 알려드리겠습니다.

 

예제 파일도 아래 첨부했으니 참고하시면 됩니다. OFFSET 함수는 셀 범위를 유동적으로 선택할 수 있는 아주 중요한 엑셀 함수이니 꼭 익혀두어야하는 함수입니다.

 

 

유동적인 셀 범위 만들기

제품 분류를 선택하면 해당하는 제품 이름이 나오도록 [수식] - [이름 정의]를 통해서 이름을 만들어 줍니다. 우선 아래 표처럼 제품 분류를 [데이터] - [데이터 유효성 검사] - [목록]으로 만들어 줍니다.

 

아래 첨부한 예제 파일을 열고 수식을 함께 확인해 보세요.

이중유효성검사.xlsx

0.01MB

[E5] 셀에서 [데이터 유효성 검사]를 만들어 주면 됩니다.

 

이렇게 입력하면 제품 분류를 목록으로 입력할 수 있습니다.

제품 분류에서 선택한 제품 이름이 나오도록 유동적인 셀 범위를 만들어야 합니다. 여기서 사용한 함수는 제품 분류의 시작 위치를 알아내는 MATCH 함수입니다.

=MATCH(E5,B5:B28,0)

선택한 제품 분류의 개수를 알아내는 COUNTIF 함수를 사용해서 목록에 길이를 알아내는 겁니다.

=COUNTIF(B5:B28,E5)

 

[수식] - [이름 정의]를 통해서 '선택한 제품 분류의 시작 위치'는 "위치", '선택한 제품 분류의 개수'는 "위치"로 정의해 줍니다.

제품분류에 따른 제품이름은 OFFSET 함수로 정의해 줍니다. 아래 참조 대상을 보면 시작 위치에서 이동하지 않고, 아래로 '개수'만큼 떨어진 셀 범위를 선택하게 해주는 겁니다.

=OFFSET(Sheet1!$C$5,위치-1,0,개수,1)

잘 이해가 안 된다면 OFFSET 함수에 대해 설명한 링크를 아래 추가해 놨으니깐 참고해 보세요.

 

제품 이름 [F5] 셀에 데이터 유효성 검사를 만들어줍니다. '원본'은 위에서 만든 '제품이름' 이름 정의를 입력만 해주면 됩니다.

 

마무리

이중 유효성 검사를 통해 제품 분류에 따라 제품 이름이 나오도록 만들어 봤습니다. 다른 예로 인사부, 총무부 등등을 고르면 해당 부서의 이름만 나오도록 할 수 있듯이 다양한 활용이 가능한 방법이니 공식처럼 사용하시면 됩니다. OFFSET 함수에 대한 자세한 설명은 아래 링크에서 확인해 보세요.

엑셀 함수 OFFSET 움직이는 셀 범위 만들기