컴퓨터를 만나는 기쁨

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..

나. 세 번째 방법 (1) Concat과 Concatenate 함수첫 번째와 두 번째 방법에서 모두 IsNumber와 Find 함수를 사용했는데,  이번에는 Concat 함수를 사용해 보겠습니다. Concat 함수의 구문은CONCAT(text1, [text2],…)로서text 여러 개를 결합한 값을 반환해 주는 함수입니다.연산자 &와 같은 역할을 합니다. 비슷한 최신 함수인 TextJoin함수의 경우는 두 텍스트를 연결할 때 delimiter(구분자)를 사용할 수 있는데, Concat 함수는 없는 단점이 있습니다. CONCAT은 CONCATENATE 함수를 대체하는데, 엑셀이 Concat 함수를 지원하지 않는다면 Concatenate 함수를 사용할 수밖에 없을 것입니다. (2) If 함수를 이용해 IsN..

나. 두 번째 방법 (1) Lookup 함수VLookup 함수는 많이 다뤄봤는데, Lookup 함수는 낯섭니다. 그렇지만 일련의 범위에서 일치하는 값을 찾은 후 반환값을 찾는 것은 동일합니다. Lookup 함수는 벡터형과 배열형으로 나뉘는데 배열형의 경우는 VLookup 또는 HLookup함수를 사용하라고 권장하고 있습니다. 벡터형의 구문은 LOOKUP(lookup_value, lookup_vector, [result_vector])로서lookup_value는 찾을 값,lookup_vector는 찾을 대상으로 행이나 열을 한 개만 포함하는 범위,result_vector는 반환 값이 있는 행이나 열을 한 개만 포함하는 범위입니다. 아래 예제의 수식은 =LOOKUP(4.19,G2:G6,H2:H6)로서4.19..

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셀로 지..

주소를 PNU로 만들기(1-3) : 주소를 법정동명과 지번으로 분리하기와 TextBefore와 TextAfter 함수를 이용해 주소를 법정동과 지번으로 분리하기 모두 산과 지번 사이에 공백아 있는 경우만 다뤘는데, 이번에는 산과 지번이 공백 없이 붙어 있는 경우를 대상으로 살펴보겠습니다. 1. 주소 데이터 '산'이 없는 경우는 동일하고, '산'이 있을 경우 산238과 같이 산과 본번사이에 공백이 없는 것만 다릅니다. 2. 주소를 법정동명과 지번으로 분리하기가. Substitute 함수 이용'산'의 위치와 관계없이 공백의 개수를 기준으로 앞과 뒤를 나누면 됩니다. (1) 법정동명 구하기(가) Substitute 함수의 구문SUBSTITUTE(text, old_text, new_text, [instance_..

TextBefore와 TextAfter함수는 Microsoft 365 버전에서만 사용가능하기 때문에 사용에 제약이 있지만 쉽게 주소에서 법정동과 지번을 분리해 낼 수 있으므로 알아보겠습니다. 1. 구문 TextBefore함수의 구문은 =TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])로서 text는 범위로 지정하면 되며 delimiter는 구분자로 문자열을 나눌 구분 기호 또는 문자열이 됩니다. 예를 들어 쉼표(,) 또는 한 칸 공백(" ")이 될 수 있습니다. [instance_num]은 몇 번째인지를 지정하는 것으로 음수도 가능하면 음수인 경우는 뒤에서부터 가져옵니다. 2. 적용 가. '산'이 있는지..

산인 경우 하이픈이 있는 경우가 없어서,A8셀의 주소를 경기도 가평군 북면 화악리 산 339에서 경기도 가평군 북면 적목리 산 1-1로 수정했습니다.   6. 필지구분(산과 일반) 구하기일반이면 1이고, 산이면 2이므로 간단한데,지번 데이터를 기준으로 생각하면 '산'이 있으면 2, 없으면 1이 됩니다. 따라서, 수식은=if(left(c2,1)="산",2,1)이 됩니다. F2셀의 채우기 핸들을 더블 클릭하면 '산'인 경우 2로 잘 표시됩니다. 7. 본번과 부번을 4자리로 변경하기가. 본번을 네 자리로 만들기본번과 부번을 모두 4자리로 표시해야 합니다.따라서, 앞에 '0"을 3개 붙인 후 뒤에서 4개를 가져와야 합니다. 그리고, 문제는 '산'이 있느냐 없느냐에 따라 다르고, 하이픈이 있느냐 없느냐에 따라 달..

1. 문제 D열 주소에  Sheet2의 단어가 포함되어 있으면 그 행 전체에 색칠을 하려고 합니다. 2. 실패Sheet2의 단어가 주소에 포함되어 있을 경우이므로 CountIf를 생각할 수 있습니다. 먼저 A열에서 O열까지 선택하고,홈 > 조건부 서식 - 새 규칙을 누른 후 '수식을 사용하여 서식을 지정할 셀 결정'을 클릭한 후 수식 입력란에 =COUNTIF(Sheet2!$A$1:$A$36,$d1)>0 이라고 입력하고, 아래쪽의 서식 버튼을 누른 다음, 채우기 탭을 누르고, 원하는 색을 선택한 다음 확인 버튼을 누릅니다. 그러면 원하는 색이 미리 보기에 표시되는데, 확인 버튼을 누릅니다. 그리고, 주소에 '대상빌딩'이라고 입력하면 A열부터 O열까지 초록색이 칠해지는데 '대상빌딩주식회사'라고 입력하면 완전..

이번에는 Find 함수를 이용해 보겠습니다.  1. Find 함수의 구문FIND(find_text, within_text, [start_num])입니다. 다른 함수의 경우와는 다르게찾을 문자열이 먼저 나오고, 찾을 대상 주소가 두 번째로 나옵니다. 2. 첫 번째 원문자의 위치 찾기가. 마지막 글자가 원문자 1인 경우 찾기역시 B3을 대상으로 먼저 해보겠습니다.원문자 1은 Unicode가 9312입니다.그리고, 문자열을 하나씩 분리해야 하므로 Mid 함수를 사용합니다.그런데, 길이를 알아도 1부터 길이까지 연속으로 글자가 생성돼야 하는데,Sequence 함수를 사용할 수 없다면 아래와 같이 배열을 이용해서 {1,2,3,4,5,6,7,8,9,10}라고 최대 10글자라고 생각하고 배열을 만들었습니다.그러면, ..