[EXCEL] 주소 데이터 유효성 검사

엑셀로 광역시도, 시군구, 읍명동, 상세주소를 따로따로 받고 싶은데

광역시도를 선택했을 경우 거기에 따른 시군구를 데이터 유효성 검사 목록에서 보여주고 싶었다.

 

그런데 이런거 한 사람이 한명도 없더라...?

 

그래서 혹시라도 이런걸 찾을 사람을 위하여 첨부한다.

sample

엑셀로만 관리하는 경우

주소 simple.xlsx

0.13MB

 

 

코드값으로 관리하는 경우

주소.xlsx

0.21MB

 

 

간단하게(?)엑셀로만 관리하는 방법을 작성해본다.

 

1. 광역시도 시트 생성

2. 광역시도 데이터 유효성 검사

2.1. 광역시도 데이터 유효성 검사 할곳 선택

2.2. 데이터 > 데이터 유효성 검사 > 데이터 유효성 검사

2.3. 설정 > 목록 선택 & 드롭다운 표시 체크

2.4. 원본에 수식 추가

일부 범위 지정

=광역시도!$A$2:$A$18

전체 범위 지정 (타이틀 포함 또는 행지정 안함)

=광역시도!$A:$A

계속 추가해도 다 나오게 할 수 있다는 편리함이 있다.

광역시도(타이틀)가 안나오게 하려면 지워서 안나오게 할 수 있다. (나중에 뭔지는 내가 알아서 알아야한다.)

3. 시군구 시트 생성

어떤 광역시도에 포함되어있는지 알기위해서 광역시도도 함께 놔둔다.

4. 시군구 데이터 유효성 검사

4.1. 시군구 데이터 유효성 검사 할곳 선택

4.2. 데이터 > 데이터 유효성 검사 > 데이터 유효성 검사

4.3. 설정 > 목록 선택 & 드롭다운 표시 체크

4.4. 원본에 수식 추가

광역시/도와 동일하게 할 경우 광역시/도를 뭘 선택하였든 그냥 다 나온다

=시군구!$C:$C

이러나 광역시/도를 클릭한거 기준으로 나오게 하려면 아래와 같이 해야한다.

=OFFSET(시군구!$C$2, MATCH(C1, 시군구!$B:$B, 0) - 1, 0, COUNTIF(시군구!$B:$B, C1), 1)

이걸 참조하여 읍명동도 똑같이 할 수 있다.

 

수식 해석

OFFSET

주어진 참조 영역으로부터 지정한 행과 열만큼 떨어진 위치의 참조 영역을 돌려줍니다.

1. USED
   • =OFFSET(Reference, Rows, Cols, Height, Width)
     =OFFSET(시군구!$C$2, MATCH(C1, 시군구!$B:$B, 0) - 1, 0, COUNTIF(시군구!$B:$B, C1), 1)
     =OFFSET(시작 지점, 일치하는 갯수, 0(정확도), COUNTIF(시군구!$B:$B, C1), 1)
     
   • Reference : 기본 참조영역
     - 시군구!$C$2
     - 첫번째 찾기 시작하는 곳으로 나는 타이틀(광역시도)이 있기 때문에 C2 부터 찾는다
       타이틀이 없거나 타이틀이 포함된다고 생각하면 C1으로 해도 상관없다.
   • Rows : 기존 참조 영역의 첫 행과 출력할 영역의 첫 행 사이의 간격
     - MATCH(C1, 시군구!$B:$B, 0) - 1 = 111
     - 정확히 일치하는것들의 갯수를 찾아서 첫행을 제외한(-1) 간격을 적어줌
   • Cols : 기본 참조 영역의 첫 열과 출력할 영역의 첫 열 사이의 간격
     - COUNTIF(시군구!$B:$B, C1)
     - 시군구B열에 각행마다 C1(광역시/도) , C2 (충청남도) 등에 매칭 되는 항목들의 갯수
   • Height : 출력하려는 참조 영역의 높이(행 수) 입니다.
     - 1
   • Width : 출력하려는 참조 영영의 너비(열 수)입니다. 생략하면 기본 참조 영역과 동일하게 설정됩니다.

MATCH

배열에서 지정된 순서상의 지정된 값에 일치하는 항목의 상대 위치 값을 찾습니다.

1. USED
   • =MATCH(Lookup_value, Lookup_array, Match_type)
   • Lookup_value : 배열, 숫자, 텍스트, 참조 등에서 찾으려고하는 값입니다.
     - C1 (데이터 시트의 광역시/도 항목)
     - 내가 C2부터 시작하지않고 전체 다 선택했기 때문에 C1부터 시작한다.
     - 찾고자 하는 항목
   • Lookup_array : 참조 값, 값 배열, 배열 참조가 들어 있는 연속된 셀 범위
     - 시군구!$$B:$B
     - 내가 항목들을 찾으려고 하는 범위
   • Match_type : 되돌릴 값을 표시하는 숫자로 1, 0 또는 -1 입니다.
     - 0 (정확히 일치)
     - 1 : 보다 작음, 0: 정확히 일치, -1 : 보다 큼)

COUNTIF

지정한 범위 내에서 조건에 맞는 셀의 개수를 구합니다.

1. USED
   • =COUNTIF(Range, Criteria)
     - COUNTIF(시군구!$B:$B, C1)
   • Range : 조건에 맞는 셀의 수를 구하려는 셀 범위입니다.
     - 시군구!$B:$B
   • Criteria : 숫자, 식, 텍스트 형태의 조건입니다.
     - C1 (광역시도)
     - Range에서 찾고자 하는 값 (조건)

5. 읍면동 시트 생성

어디의 시군구에 속해있는지 시군구도 적어줘야하는데 이때 시군구가 

6. 읍면동 데이터 유효성 검사

6.1. 읍명동 데이터 유효성 검사 할곳 선택

6.2. 데이터 > 데이터 유효성 검사 > 데이터 유효성 검사

6.3. 설정 > 목록 선택 & 드롭다운 표시 체크

6.4. 원본에 수식 추가

=OFFSET(읍면동!$D$2, MATCH(CONCAT(C2,"_",D2), 읍면동!$A$2:$A$5054, 0) - 1, 0, COUNTIF(읍면동!$A2:$A$5054, CONCAT(C2,"_",D2)), 1)

★ 완성 ★