[구글 스프레드시트]#39 값을 선택하면 자동으로 원하는 값을 추출해주는 유효성 검사 + VLOOKUP ( 엑셀 공통 )

2021/01/12 - [Google Docs/스프레드시트] - [구글 스프레드시트]#37 함수 - VLOOKUP ( 엑셀 공통 )

[구글 스프레드시트]#37 함수 - VLOOKUP ( 엑셀 공통 )

 

목록 만들기 - 구글 스프레드시트 , 엑셀

지역명을 선택하면 해당 인구수를 자동 추출할 수 있도록 "구글 스프레드시트는 데이터 확인 기능 " , "엑셀은 유효성 검사 기능" 을 이용하여 목록을 만들어 보도록 하겠습니다.

 

아래의 그림과 같이 M5셀에 A5:A286셀의 값을 나타낼 예정입니다. 범위가 제법 양이 많으니 미리 이름정의를 해 두거나 , 첫번째 셀 클릭 CTRL + SHIFT +  ↓ 기능을 이용하여 범위를 지정하도록 합니다.

 

 

 

 

 

구글 스프레드시트 - 데이터 확인

 

1. M5셀에 셀 포인터를 둔 뒤 - [ 데이터 ] - [ 데이터 확인 ]

 

 

*  "범위 또는 수식을 입력하세요"에서 바로 범위를 지정하려하면 단축키를 사용할 수 없습니다.

*  첫셀을 클릭하면 자동으로 입력되는 시트명때문에 그러한데, 만약 "범위 또는 수식을 입력하세요"를 이용했다면 시트 이름을 지운 후
첫 셀클릭 - CTRL + SHIFT + ↓

* 아래 그림의 "데이터범위" 아이콘을 이용하면 번거러움 없이  첫 셀클릭 - CTRL + SHIFT + ↓ 사용이 바로 가능합니다.

 

2. 기준을 [ 범위에서의 목록 ] 으로 선택 한 후 - [ 범위지정 ] - [ 셀의 드롭다운 목록표시 ] 체크 - [ 저장 ]

 

 

3.  M5셀에 목록단추를 클릭하면 , 범위 지정 영역의 데이터가 모두 호출 된 것을 확인 할 수 있습니다.

 

 

 

 

엑셀 - 유효성 검사

작업방식은 유사하나 아이콘과 메뉴가 달라 설명 첨부합니다.

 

1. M5셀에 셀포인터를 둔 뒤 [ 데이터 ] - [ 데이터 유효성 검사 ]

 

 

 

2. 제한 대상 : 목록 / 원본칸을 클릭 한 후 범위영역의 [ 첫번째 셀 클릭 ] - CTRL + SHIFT + ↓ - 확인

 

 

3. M5셀에 목록버튼을 클릭하여 결과를 확인 합니다.

 

 

 

VLOOKUP + 유효성 검사

문제1 >  M5셀의 값에 따라 인구수가 자동으로 변경될 수 있도록 VLOOKUP함수를 이용하여 값을 추출 하세요

 

M5셀의 값에 해당되는 내국인 총계 , 내국인 남자 , 내국인 여자를 찾는 문제입니다.

 

Lookup_value ( 찾을 값 ) 은 M5셀이 됩니다.

 

 

Table_array ( 데이터베이스범위 ) 는 "M5"의 값을 가지고 있는 시군구의 값이 첫번째 열이 되게 범위를 지정합니다.

* VLOOKUP : 배열의 첫 열에서 값을 검색하여 지정한 열의 같은 행에서 데이터를 돌려줍니다.

 

내국인의 총계는 범위지정한 영역에서 5번째 열에 위치하고 있습니다.

내국인의 남자는 범위지정한 영역에서 6번째 열에 위치하고 있습니다.

내국인의 여자는 범위지정한 영역에서 7번째 열에 위치하고 있습니다.

 

수식 작성

1. 값이 출력될 위치인 N5셀에 셀포인터를 둔 뒤 =VLOOKUP( 를 입력합니다.

1-1 : 엑셀의 경우 =VLOOKUP( 입력 한 뒤 함수삽입(Fx)을 이용하여 작업하면 인수를 구분하는 쉼표를 사용하지 않아도 됩니다. 

각 인수는 구글스프레드시트와 크게 다르지 않으니 엑셀로 사용하시는 분들은 참고하세요.

함수삽입(함수마법사)

 

 

 

2. 
Lookup_value 찾을값 : $M$5
Table_array 데이터베이스 : $A$5:$J$286
col_index_num 열 번호: 5
range_lookup 찾을 방식 : 정확하게 일치하는 값을 찾을 경우 0 ( 또는 FALSE , 엑셀의 경우는 F만 입력해도 됩니다. )

* Lookup_value ,  Table_array에 F4를 이용하여 절대참조를 한 이유는?

이번에는 대상이 총계 뿐 아니라 남자, 여자도 구해야하는데 매번 수식 작성하기 번거로우니 오른쪽방향으로 채우기 핸들을 할 예정입니다. 해당방향으로 끌어 당겼을 때 기준값이 되는 M5와 참조범위로 사용할 A5:J286의 영역위치가 변동되면 안되므로 , 절대참조를 이용하여 고정하였습니다.

 

3. N5셀의 값을 채우기 핸들 합니다.

 

당연히, 같은 수식을 사용했으니 같은 결과값이 나오게 됩니다.

 

 

4. O5셀 더블클릭하여 5를 6으로 바꾼 뒤 ENTER . 같은 방법으로 P5셀을 클릭하여 5를 7로 변경합니다.

 

 

 

 

 

 

 

결 과