컴퓨터를 만나는 기쁨

3. 구글 스프레드시트의 수식 분석 가. 읍면동을 포함하는 법정동 불러오기 (1) 수식 엑셀에서의 수식은=IFERROR(INDEX('법정동코드'!$B$1:$B$49860,AGGREGATE(15,3,1/(ISNUMBER(FIND($A$2,'법정동코드'!$B$2:$B$49860))*('법정동코드'!$C$2:$C$49860="존재"))*ROW('법정동코드'!$B$2:$B$49860),ROW()-1)),"")인데, 구글 스프레드시트에서 가져오니앞에 ARRAY_CONSTRAIN과 ARRAYFORMULA가 붙어 있고,=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('법정동코드'!$B$1:$B$49860,AGGREGATE(15,3,1/(ISNUMBER(FIND($A$2,'법정동코드'!$..

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

(2) 2019 버전의 경우2019 버전은 수식 입력할 때 Enter키로 하면 안 되고, Ctrl +  Shift + Enter키로 입력해야 한다는 것이 다릅니다. B6셀에 =b2라고 입력한 다음 절대 참조로 만들기 위해 F4키를 누르고, 데이터 시트를 선택한 다음 a2셀에서 a30셀까지 마우스로 끕니다. 그리고, Ctrl +  Shift + Enter키를 누르면 A6셀에만 수식이 입력되고, 수식 좌우에 중괄호 표시가 생기는데,  a2셀에서 a30셀까지 선택한 다음  F2키를 누르고, Ctrl +  Shift + Enter키를 누르면    선택한 영역 전체에 수식이 들어가고 결괏값이 표시되는데, 맨 아래만 조건이 맞으므로 True라고 표시됩니다. 조건에 해당하는 경우 행수를 반환받고, 아니면 개수보다 큰..

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

3. Find함수를 이용해 첫 번째 숫자의 위치 찾기(성공) Find함수의 첫 번째 인수는 찾을 값인데, 배열로 지정할 수 있으므로 숫자를 배열에 넣어서 =find({0,1,2,3,4,5,6,7,8,9},a2)라고 하면 Microsoft 365의 경우는 아래와 같이 동적 배열로 반환됩니다. 다른 엑셀 버전은 7만 반환될 듯합니다. 위 반환값에서 7은 0의 위치, 9는 1의 위치이며, 세 번째부터는 에러가 발생하는데 해당하는 숫자가 없기 때문입니다. 에러가 나면 떠오르는 것이 Aggregate 함수입니다. 배열형인 경우 Aggregate 함수의 구문은 AGGREGATE(function_num, options, array, [k])이며, 두 번째 인수인 options에서 오류값을 무시하는 옵션을 지정할 수 ..

라. 조건에 맞는 데이터를 작은 값부터 표시하기 (1) 가장 작은 값이 0으로 표시되는 문제 j2셀의 채우기 핸들을 오른쪽으로 끈 후 K2셀의 Aggregate 함수의 첫 번째 인수를 작은 값부터 표시하도록 15-Small로 바꾸고 엔터키를 누르면, 가장 작은 값이 0이 아닌데 0이 나옵니다. 따라서, 조건에 해당하는 부분을 마우스로 끌어서 선택 후 F9키를 눌러서 계산값을 확인하 조건에 맞지 않을 경우 0이 돼서 그렇습니다. (2) 행정구역명(대)는 맞고 행정구역명(중)이 불일치할 때 문제 Esc키를 눌러 수식을 원래로 돌려놓고, 수식 맨 앞에 1/를 추가하고 엔터키를 누르면 될 줄 알았는데, False일 경우 #DIV/0!로 표시되는데, 첫 번째 #DIV/0! 다음이 0입니다. 왜 그런가 하고 살펴보..

이번에는 Aggregate 함수를 이용해 조건에 맞는 값을 찾아보겠습니다. 1. Aggregate함수의 구문Aggregate 함수의 구문은 참조형인 경우는 AGGREGATE(function_num, options, ref1, [ref2], …) 배열형인 경우는 AGGREGATE(function_num, options, array, [k]) 입니다. function은 합계는 9, 최댓값은 14이며, 오류값을 무시하는 options는 2, 3, 6, 7이 있습니다. Aggregate 함수의 보다 자세한 설명은 아래 글을 참고하기 바랍니다. https://lsw3210.tistory.com/entry/Aggregate%EC%98%B5%EC%85%98-%EC%A7%91%EA%B3%84-%ED%95%A8%EC%8..

Aggregate 함수 사용시 두가지 이상 조건을 만족하는 조건을 설정하는 방법을 알아보겠습니다. 조건이 1개인 경우는 1/(조건)*(원하는 값 범위)로 했는데, 2개이상인 경우는 1/(((조건1)*(조건2)...)*(원하는 값 범위) 식으로 여러 개의 조건을 괄호로 감싸야 합니다. 아래 왼쪽 데이터에서 오른쪽 판매일과 지점명이 일치하는 데이터를 찾아 크기순으로 나열해 보겠습니다. 1. 판매일 조건보다 작고 지점명이 일치하는 판매금액을 큰 값부터 표시하기 ① H2셀에 커서를 놓고, ② =ag까지 입력하고 탭 키를 눌러 =AGGREGATE(까지 입력합니다. ③ 그리고, 함수명 중에 14번 Large를 아래 화살표키 또는 PgDn키를 눌러 이동한 후 탭키를 누릅니다. ④ ,를 누르면 옵션이 표시되는데 오류값..

Aggregate(옵션 적용 집계) 함수(1)에서는 참조형(Sum, Subtotal, Aggregate 등)에 대해서만 살펴봤는데, 이번에는 배열형에 대해 알아보겠습니다. 배열형이 적용되는 함수는 LARGE, SMALL, PERCENTILE.INC(, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC인데, Large, Small함수에 대해서만 다루겠습니다. Vlookup함수는 범위에서 일치하는 첫 번째 값만 처리가능한데, Large 또는 Small함수는 같은 값이 여러 개일 경우라도 순차적으로 값을 반환해주는 장점이 있습니다. 1. Vlookup함수의 한계 위와 같은 경우 같은 판매일이 한 개인 경우는 그에 해당하는 지점명과 판매금액을 구할 수 있으나, 같은 판매일이 두 개이상..

Aggregate 함수에 Large, Small, Percentile(백분위 수), Quatile(사분위 수) 함수가 있기 때문에 이들 함수에 대해 먼저 알아보겠습니다. 1. 의미 Large : 데이터 집합에서 k번째로 큰 값을 반환 Small : 데이터 집합에서 k번째로 작은 값을 반환 2. 구문 LARGE(array,k) SMALL(array,k) Array k번째로 큰 값 또는 작은 값을 확인할 데이터 배열 또는 범위. k 데이터의 배열이나 셀 범위에서 가장 큰 값 또는 작은 값을 기준으로 한 상대 순위. 3. 사용 예 max, min 함수는 최댓값, 최솟값을 구해주는데, rank.eq함수는 해당 값의 순위를 구해주는데, large, small 함수는 순서에 해당하는 큰 값, 작은 값을 구해주는 함..