[Excel] 동적범위 데이터 유효성 검사(드롭다운)

안녕하세요!
서현입니다!

오늘은 데이터 유효성검사 동적범위에 대해서 알아볼건데요,

오늘 포스팅은 다른 블로그에서도 많이 나와있는INDIRECT 함수를 사용하지 않고, 동적범위를 설정해 데이터 유효성검사(드롭다운, 콤보박스)를 하는 방법을 알아보도록 하겠습니다.

 

우리가 업무를 하다보면, 따로 파일을 수정하지 않고, 원본 파일을 이용하여 데이터를 추출하는 경우가 많아요. 왜냐면 데이터를 수정하는 것도 시간이 걸리는 일이기도 하고. 시간이 걸린다는 것은 퇴근이 그만큼 늦어진다는 거니까요... ㅋㅋㅋ

 

그래서 오늘은 따로 대분류, 중분류, 소분류 등을 만들지 않고 원본파일에서 바로 원하는 데이터가 대분류, 데이터 유효성 검사의 데이터가 소분류로 지정될 수 있게 데이터 유효성 검사를 만들어 보겠습니다.

 

오늘은 많은 함수가 사용될거고, 그 함수들을 제 나름의 방식대로 설명해드릴게요.

일단 데이터 시트에 이렇게 제품명에 대한 색상이 있습니다. 그리고 이 제품명은 참 많은 색상을 가지고 있네요.

우리가 기존에 알던 INDIRECT함수를 사용하여 데이터 유효성 검사를 하려면 색연필에 대한 색상의 이름정의와 사인펜에 대한 이름정의, 그리고 크레파스에 대한 이름을 정의 해줘야 했습니다. 근데 데이터는 막 늘어간텐데 계속 이렇게 제품명에 대한 이름을 정의 하고, 수정을 할 수 없잖아요?

 

그래서 저는 오늘 OFFSET함수와 MATCH함수 그리고 COUNTIF함수를 이용하여 제품명의 동적범위 데이터유효성 검사를 만들어 제품명의 색상만 추출되게 해보겠습니다.

 

일단, 제품을 불러올 시트에 제품명, 규격을 따로 작성했고, 제품명에 따라서 규격을 불러올 수 있게 할거예요.

제품명에는 데이터 시트에 있던 색연필, 사인펜, 크레파스일거고, 규격은 제품명에 맞는 규격을 불러와야 하는데요!

제품명은 따로 데이터 유효성 검사를 하셔서 선택할 수 있게 하거나 아니면 직접 쓰셔도 돼요!

제품명을 유효성검사하는 방법은 다른 곳에도 많으니까 여기서는 따로 설명하지 않겠습니다.

일단 제품명을 작성하고 규격에 데이터 → 데이터유효성검사 → 목록을 선택한 다음 수식을 작성해줄게요.

=OFFSET(데이터!$B$2,MATCH($A2,데이터!$A:$A,0)-2,0,COUNTIF(데이터!$A:$A,$A2),1)

=OFFSET(규격기준, MATCH(제품명, 데이터시트의 제품명열 선택,정확히일치)-제품명이 시작되는 행위치, (데이터가 시작되는 위치에서부터 범위를 참조하겠다 라는 뜻입니다.)

그래서 크레파스의 경우에는 크레파스가 시작되는 A8셀부터 범위를 참조하겠다. - A2셀부터 데이터를 끌고와야 하니까 -2 하고 참조할게. 라는 뜻 입니다.) A2셀부터 6번째 아래로 이동해서 범위를 시작하겠다는 뜻.

0 (행이동 없음),

COUNTIF(제품명의 개수) 사인펜의 경우 4개의 규격이 있으므로 제품명의 개수 만큼 아래셀 4개를 참조하겠다는 뜻

1 너비 - 너비는 1로 하여 한 열만 참조하겠다는 뜻 입니다.

 

결과값

 

이렇게 데이터 유효성을 해주면 따로 데이터를 정리할 필요 없이 원본에서 해당 제품명에 맞는 규격명을 갖고올 수 있습니다.

많은 함수가 이용되기 때문에 어렵긴 하지만, OFFSET 함수를 이해하고 계시다면 쉽게 하실 수 있는 함수입니다.

 

감사합니다!