컴퓨터를 만나는 기쁨

이번에는 최신 함수인 Filter와 Sort 함수를 이용해 읍면동을 입력한 후 입력한 읍면동을 포함하는 법정동을 추출한 후 유효성 검사를 이용해 법정동을 선택한 후 그것에 맞는 법정동코드를 추출하고, 지번에서 특지 구분과 본번 부번을 추출한 후 결합해서 최종적인 PNU를 만드는 것을 해보겠습니다. Index와 Aggregate함수를 이용하는 것은 아래 글을 참고 바랍니다.https://lsw3210.tistory.com/567https://lsw3210.tistory.com/568 Microsoft 도움말 사이트를 살펴보면 Filter와 Sort 함수 모두 Excel 2021 이상에서 사용 가능한 것으로 되어 있습니다.https://support.microsoft.com/ko-kr/office/filte..

https://lsw3210.tistory.com/567에서 읍면동명을 포함하는 법정동명을 구했는데,이번 글에서는 유효성 검사를 이용해 목록을 만드는 것부터 해보겠습니다. 나. 데이터 유효성 검사D1셀을 클릭하고 데이터 탭에서 데이터 도구 그룹의 데이터 유효성 검사 윗부분을 누릅니다. 그리고, 제한 대상으로 목록을 선택하고, C2:C30을 마우스로 끌어서 원본을  선택하고 확인 버튼을 누릅니다. 그러면 D2셀 오른쪽에 콤보 상자 버튼이 생깁니다. 콤보 상자 버튼을 누르면 C열의 법정동명이 표시되므로 원하는 동을 클릭해서 선택하면 됩니다. 맨 아래 회색 부분은 공백 때문에 그런 것입니다. 다. 법정동코드 구하기이제 선택된 법정동명에 대한 법정동코드를 구해보겠습니다. C2셀에 사용했던 수식과 비슷한데, in..

1. 문제 ① 읍면동명과 지번을 A2셀과 B2셀에 입력하면 ② 읍면동명을 포함한 법정동명을 검색한 후③ 유효성 검사 목록에 넣고④ 법정동명을 선택하면 해당 법정동 코드가 구해지고,⑤ 지번을 이용해 특지구분과 본번과 부번을 각각 4자리로 만들어⑥ 이들을 모두 결합해서 19자리 PNU 코드를 완성하려고 합니다. 법정동코드 시트는 법정동코드, 법정동명, 폐지여부 3개 열로 구성되어 있습니다. 2. 해법  가. A2셀에 입력된 읍면동명을 포함하는 법정동명 구하기포함이니까 Find 함수와 IsNumber가 필요하고,포함하는 법정동명을 여러 개 순서대로 구해줘야 하니 Small 또는 Aggregate 함수가 필요하고,순번에 맞는 법정동명을 구해야 하니 Index 함수,그리고, 에러 발생 시 공백으로 만들기 위해 I..

3. 특지 구분 연결하기특지 구분 등을 구하는 사용자 정의 함수는 아래 글을 참고 바랍니다.https://lsw3210.tistory.com/564 이번에는 사용자 정의 함수가 아니라 매크로로 특지 구분을 구하겠습니다.그러나 구문은 사용자 정의 함수나 같은데,사용자 정의 함수는 특지구분이란 변수로 값을 반환해야 하는데 반해서매크로는 직접 처리하고 값을 반환할 필요가 없고, 사용자 정의 함수의 경우는 인수(아래 코드에서 '범위')를 직접 대입할 수 있는데 반해서,매크로의 경우는 인수를 대입할 수도 있지만 본 예제의 경우는 반복 구문이기 때문에 인수를 직접 입력할 수 없다는 점입니다.Function 특지구분(범위 As Range) '범위(주소)에서 특지구분을 추출한다. Dim 마지막공백 A..

1. 법정동 코드 가져오기 PNU 코드는 법정동 10자리 + 특지 구분 1자리 + 본번 4자리 + 부번 4자리 = 19자리로 되어 있습니다. 법정동 코드는 https://www.code.go.kr/ 사이트에서코드 검색을 누르고, 코드명에 '법정동'이라고 입력하고 검색을 하면텍스트 파일로 받을 수 있으면 이것을 엑셀 파일로 바꾸면 됩니다.자세한 것은 https://lsw3210.tistory.com/517 을 참고 바랍니다. 텍스트 파일을 연 후 Ctrl + A키를 눌러 전체를 선택한 후 복사해서 시트2에 붙여 넣고 시트명을 '법정동코드'로 바꾸면 아래와 같습니다.  2. 법정동에 대한 법정동 코드 검색하기 매크로로 만들기 위해 개발도구 - Visual Basic을 누른 후코드 창에 Sub pnu라고 입력..

법정동을 구하는 것은 아래 글을 참고 바랍니다.https://lsw3210.tistory.com/563 5. 특지 구분 구하기특지는 법정동 다음에 '산'이 있는지 여부로 판단하면 되므로법정동 함수를 참고해서 만들면 됩니다. 코드는 아래와 같습니다.Function 특지구분(범위 As Range) '범위(주소)에서 특지구분을 추출한다. Dim 마지막공백 As Integer Dim 지번 As String 마지막공백 = InStrRev(범위, " ") If Mid(범위, 마지막공백 - 1, 1) = "산" Then 마지막공백 = InStrRev(범위, " ", 마지막공백 - 1) End If 지번 = Mid(범위, 마지막공백 + 1)..

1. 수식 이용 하기아래 글에서 수식을 이용해 주소에서 법정동을 분리해내고,https://lsw3210.tistory.com/519 주소를 PNU로 만들기(1-3) : 주소를 법정동명과 지번으로 분리하기4. 주소를 법정동과 지번으로 분리하기 가. 주소의 형태  아래와 같이 시도, 시군구, 읍면동, 리로 구성된 주소가 있을 때법정동과 지번을 구분해야 하는데한 칸 공백의 개수가 3개인 경우도 있lsw3210.tistory.com 지번에서 특지구분과 본번, 부번을 분리했는데https://lsw3210.tistory.com/521 주소를 PNU로 만들기(1-5) : 필지구분과 본번,부번 변환하기산인 경우 하이픈이 있는 경우가 없어서,A8셀의 주소를 경기도 가평군 북면 화악리 산 339에서 경기도 가평군 북면 적..

1. 엑셀과 구글 스프레드시트의 비교 엑셀은 Microsoft 365에서만 ByRow 함수가 지원되는데,구글 스프레드시트는 인터넷에서만 실행되지만 ByRow와 ByCol 함수를 모두 지원합니다. 아래와 같이 =by까지 입력하면 그 아래 BYCOL과  BYROW가 표시되고, 간단한 함수에 대한 설명, 여기서는 "행 별로 범위를 그룹화합니다."라고 표시됩니다. 또한 탭키를 눌러 BYROW를 선택하면인수가 표시되는데, 배열_또는_범위가 활성화되고, 람다가 두 번째 인수로 표시됩니다.  이에 비해 Excel에서는 인수로 array가 활성화되고, 두 번째 인수가 function이라고 표시됩니다. function이라고 표시되지만 Lambda인 것은 동일합니다. 2. 구글 스프레드시트에서 ByRow 함수 적용 위 파..

5. 해법 4ByRow 함수를 이용해 행 별로 곱해서 0보다 큰 경우의 수를 세서 값을 구할 수 있습니다.ByRow 함수는 Microsoft 함수에서만 사용 가능한 함수로서구문은 =BYROW(array, lambda(row))입니다.배열을 입력한 다음 lambda의 row 인수로 전달한 다음 수식을 통해 값을 구하는 것입니다. 가. 한 가지 조건을 만족하는 경우=BYROW(B2:B33,LAMBDA(row,IF(row>0,1,0)))라고  입력하면 행 별로 셀 값이 반환됩니다.  수식의 의미는B2셀에서 B33셀까지 범위를 입력한 다음Lambda함수의 첫 번째 인수로 대입하고,수식(calculation)으로, IF(row>0,1,0)를 입력한 것입니다.따라서 행의 값이 배열로 반환됩니다.  이제 필요한 것이..

3. 해법 2 배열 수식을 이용해서 여러 가지 조건을 *(and)로 연결해서 값을 구할 수 있습니다. 가. 한 가지 조건을 만족하는 경우(1) 수식 1수식은=SUM(IF(B2:B23>0,1,0))라고 할 수 있습니다.배열 수식이기 때문에 엔터 키가 아니라 Ctrl + Shift + Enter키를 눌러 입력해야 하는데, Microsoft 365 버전이기 때문에 누르지 않아도 되고, 좌우에 중괄호 표시도 없습니다. (2) 수식 2B열 전체를 지정해서=SUM(IF(B:B>0,1,0))-1라고 할 수도 있는데, 맨 뒤에 -1을 한 것은 cnt_1이 0보다 큰 것으로 값이 나오기 때문입니다. 나. 두 가지 조건을 만족하는 경우 두 가지 조건을 만족하는 경우는 *를 이용해야 하므로=SUM(IF(($B$2:$B$33..