컴퓨터를 만나는 기쁨

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

3. 해법 2 - SubTotal 함수 이용 1편은 여기를 참고 바랍니다. 가. SubTotal 함수SubTotal 함수의 구문은SUBTOTAL(function_num,ref1,[ref2],...)로서 function_num에 따라 합계, 평균, 숫자의 개수 등 다양한 값을 구할 수 있습니다. Function_num(숨겨진 행 포함) Function_num(숨겨진 행 무시) 함수 1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP  아래와 같은 숫자의 합을 구할 때 Sum을 사용할 수도 있고, Subtotal(9,을 사용할 수도 있습니다.값이 같은지 비교하기 위해 그룹 1과 그..

1. 문제  위와 같이 C열에 매장별 입고량이 있고, D열에는 판매량이 있으며, 판매량 목표는 입고량의 80%입니다.이에 따른 달성 여부가 G열에 있는데,목표가 언제 달성되었는지, 다시 말해 H열부터의 일자별 판매량 누계가 목표량을 넘어가는 일수 또는 날짜를 구하려고 하는 것입니다. 2. 해법 1 - Scan 함수 이용 가. 누계 판매량 구하기 MIcrosoft 365 버전이라면 Scan 함수를 이용할 수 있습니다.Scan 함수의 사용법에 대해서는 아래 글을 참고 바랍니다. https://lsw3210.tistory.com/484 선입선출법에 따른 재고월 구하기1. 문제아래와 같이 월별 매입수량과 매출수량이 있을 때 선입선출법에 따라 남는 매입월(재고월)이 어떻게 되는지 구해보려고 합니다.예를 들어 A품..

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

(3) 떨어진 셀부터 일정 영역 지정하기엑셀에서는 Offset 함수에 height와 width를 지정할 수 있는 인수가 있지만,VBA에는 없으므로 Resize 속성(Property)을 이용해야 합니다. (가) 구문expression.Resize (RowSize, ColumnSize)입니다.여기서 expression에는 범위(셀)가 올 수 있으며RowSize와 ColumnSize로 높이와 너비를 지정하는 것입니다. Offset 함수와 구별해야 할 것은 RowOffset이나 ColumnOffset의 경우는 떨어진 거리이므로 0부터 시작하는데,RowSize와 ColumnSize는 크기이므로 1부터 시작한다는 것입니다. (나) 일정 영역 지정하기 아래와 같이 코드를 작성하고 실행하면Sub resize1() ..

1. 구문 비교ExcelVBAOFFSET(reference, rows, cols, [height], [width])expression.Offset (RowOffset, ColumnOffset)기준(참조) 셀이 첫번째 인수이고,행과 열만큼 떨어진 셀을 지정하기 위해 rows와 cols를 지정하는데 기준 셀의 위치가 0임그리고, 떨어진 셀부터 높이와 너비만큼 영역을 지정하기 위해 height와 width 지정 가능기준셀을 먼저 expresseion 자리에 지정하고,속성(property)으로 Offset을 지정하는데,행과 열로 떨어진 값만 지정 가능하고높이와 너비는 지정할 수 없음 2. 사용 예  가. Excel의 Offset 함수(1) 떨어진 셀 지정C9셀을 기준으로 아래로 3칸, 오른쪽으로 한 칸 이동한 ..

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. 문제아래와 같이 월별 매입수량과 매출수량이 있을 때 선입선출법에 따라 남는 매입월(재고월)이 어떻게 되는지 구해보려고 합니다.예를 들어 A품목의 경우 매입이 워낙 많고, 매출은 겨우 158개로 기초 재고도 소진하고 있지 못하며,F품목의 경우는 5월까지의 매출 수량이 1,031개로 기초재고 1000개를 소진하고, 2월 매입 물량 34개 중 31개를 소진하고 3개가 남게 되므로 재고 월은 2월이 됩니다.  2. 해법매입물량과 매출물량을 비교해서 매입물량이 최종적으로 매출물량을 커버하고 남는 달이 재고월이 됩니다.따라서, 매입물량도 더해가고, 매출물량도 더해가야 하며, 수량이 초과되는지 여부를 판단해야 하므로 기존까지의 Match함수와 sum함수로는 처리하기가 너무 복잡해지는데,Microsoft 365에..