컴퓨터를 만나는 기쁨

엑셀의 TextSplit함수에 대응하는 구글 스프레드시트의 함수는 Split함수로서 함수명이 다릅니다. 1. 구문SPLIT(텍스트, 구분자, [split_by_each], [remove_empty_text])로서 ① 텍스트 : 구분할 문자열로서 셀 주소로 입력할 수 있습니다.② 구분자 : 구분 문자입니다. 특이한 점은 기본 값이 'the'인 경우 t와 h, e를 기준으로 텍스트를 분리한다는 것입니다.구분 문자로 "("를 지정하면 "("를 기준으로 텍스트를 구분하는데,=split(A2,"(") =split(A2,"()")라고 하면 "("와 ")"를 기준으로 텍스트를 구분하므로 'HS반'이 두 번째 배열로 반환됩니다. ③ split_by_each : 구분 문자열을 각각 구분 문자로 사용할지 여부로 기본값은 ..

1. 문제아래와 같은 문자열에서 괄호 사이의 문자열을 추출하려고 합니다.   2. TextSplit, Index, TextBefore 함수의 구문 가. TextSplit 함수=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])① text : 문자열 주소② col_delimiter : 가로로(열) 구분할 문자열③ row_delimiter : 세로로(행) 구분할 문자열④ ignore_empty : 구분 문자열이 연속될 경우 빈 배열이 반환되는데, 이를 무시할지 여부로, 무시하면(True) 빈 배열을 없애고, 무시하지 않으면(False) 빈 배열이 반환됩니다. 기본 값을 False입니다. =TEXTSPLIT..

이번에는 최신 함수인 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..

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

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라고 입력..

크게 배열 수식을 이용하는 방법과 동적 배열 함수인 Filter 함수를 이용하는 방법으로 나눠 살펴보겠습니다. 나. 배열 수식을 이용하는 방법배열 수식을 이용하는 것은 조건에 맞는 값을 찾은 후 index 함수를 이용해 순번에 따른 값을 차례로 표시하면 되는데, 차례대로 표시할 때 Small 함수를 이용할 수도 있고, Aggregate 함수를 이용할 수도 있습니다. 배열이 레거시 배열과 동적 배열로 구분되므로 먼저 동적 배열을 지원하는 Microsoft 365에서 해보고, 레거시 배열인 2019에서 해보겠습니다. (1) Microsoft 365의 경우 필터 시트는 아래와 같이 업체명이 업체4여야 한다는 조건과 결과 표시 영역이 있고, 데이터 시트에는 업체별 판매물품 등 데이터가 들어있으므로 여기서 업체명..

1. 문제 E열에 있는 문장에서 D2셀에서 D4셀에 있는 단어를 찾아 D6셀에서 D9셀에 표시하는 것입니다. D6셀에는 수식이 들어있지 않고, 눈으로 판단한 값인 수박이 써져 있습니다. D7셀부터 그렇습니다. 2. 해법답을 찾는 방법이 여러 가지입니다.하나씩 알아보겠습니다. 가. 첫 번째 방법(1). Find 함수와 Match 함수 비교(가) Find 함수구문은 FIND(find_text, within_text, [start_num])로서찾을 문자열을 먼저 입력하고, 찾을 대상 문자열을 두 번째로 입력하고, 세 번째 인수는 시작 위치에 해당하는 숫자입니다. Find 함수를 이용해서 D6셀에 수식을 입력하는데find_text가 사과, 배, 수박이므로 이것을 범위로 입력하고, 찾을 대상 문자열을 E6셀로 지..

1. 필지구분 구하기가. Match 함수 이용하기필지구분(산 또는 일반)을 구하는 수식이 =IF(C2="산*",2,1)라고 되어 있는데, Match함수를 이용하면=IF(ISNUMBER(MATCH("산*",C2,0)),2,1)라고도 할 수 있고,=IF(ISERROR(MATCH("산*",C2,0)),1,2)라고도 할 수 있습니다. 그러나, =IF(ISERROR(MATCH("산",C2,0)),1,2)라고 *를 없애면 안 됩니다. 나. CountIf 함수 이용하기=IF(COUNTIF(C2,"산*"),2,1)로 Match 함수와 달리 IsError 함수나 IsNumber 함수를 사용하지 않고,CountIfs 함수의 인수의 위치가 Match와 다르고,If 함수의 참일 때 값과 거짓일 때 값의 위치가 바뀐 점이 다릅..

5. 법정동에 해당하는 코드 구하기가. 필요한 파일주소에서 법정동 코드와 지번을 분리한 것은 아래 파일을 다운로드하면 되며,  법정동 코드 자료는 아래 엑셀 파일에 있습니다.  나. 법정동에 해당하는 코드 찾기(1) Index + Match 함수Vlookup 함수를 찾으려면 찾으려고 하는 법정동명이 왼쪽에 있어야 하는데 오른쪽에 있으므로 Index + Match 함수를 사용해야 합니다. Match함수로 법정동명에 해당하는 위치를 찾고, Index 함수와 연결해서 그 줄에 해당하는 법정동코드를 가져오는 것입니다. 수식은 =index(법정동코드 범위, match(법정동명, 법정동명 범위,0))입니다. 법정동코드 전체자료.xlsx의 내용을 PNU만들기(주소 분리).xlsx 파일에 시트로 추가할 수도 있지만 그..

1. 문제 아래와 같이 회차별 점수가 있을 때오른쪽부터 3개의 평균만을 구하려고 합니다. 2. 관련된 함수해법 1은 Index와 Large 함수가 필요하고,해법 2는 Take, Filter 함수가 필요합니다. 각각의 함수의 구문에 대해 알아보겠습니다. 가. Index 함수참조형과 배열형이 있는데,배열형을 사용하면 되며, 배열형의 구문은INDEX(array, row_num, [column_num])로서, 배열 array에서 행 수와 열 수에 해당하는 값을 가져오는 것입니다. 나. Large 함수구문은 LARGE(array,k)로서array는 배열, k는 순번입니다. 다. Take 함수구문은=TAKE(array, rows,[columns])로서 첫 번째 인수는 array 배열이고, rows는 가져올 행 수,..