컴퓨터를 만나는 기쁨

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

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 배열 수식을 이용해서 여러 가지 조건을 *(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..

(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여야 한다는 조건과 결과 표시 영역이 있고, 데이터 시트에는 업체별 판매물품 등 데이터가 들어있으므로 여기서 업체명..

1. 배열 수식Microsoft 365 전에는 Ctrl + Shift + Enter 키를 눌러 배열 수식이라는 것을 만들었습니다.그러나 Microsoft 365부터는 Ctrl + Shift + Enter 키를 누르지 않더라도 알아서 배열로 처리됩니다. Microsoft 365라 이전 버전을 확인하기 곤란하니 만약 설명한 값이 안나오면 Ctrl + Shift + Enter 키를 눌러쥬가 바랍니다. 아래와 같이 A열에서 D2셀의 값을 찾아 같은 행의 원어를 찾는다고 할 때=INDEX($B$2:$B$10,MATCH(D2,A2:A10,0))라고 수식을 입력합니다.아래 수식에서는 Ctrl + Shift + Enter키를 눌러 배열 수식이라는 것을 알리고, 그렇게 함녀 수식 양쪽에 중괄호 표시가 생깁니다. Micr..

1. 문제 아래와 같이 직원별, 일자별 근무 현황이 있는데, 근무 유형별, 일자별로 누가 근무했는지와 시간외근무를 오른쪽 '시간외근무 유형'에 따라 괄호 안에 시간수를 입력해서 표시하려고 합니다. 원 데이터는 다른 시트에 있는건데 이해와 설명의 편의를 위해 한 개 시트에 모았습니다. 2. 해결 방안 365 버전용 함수를 이용하는 방법과 이전 버전용 함수를 이용하는 두 가지 방법에 대해 알아보겠습니다. 가. 365 이전 버전 (1) d1, d2, n1, n2 유형에 따라 일자별 근무자 및 괄호안에 숫자 표시 일자와 유형 2가지 조건을 만족해야 하므로 두가지 조건을 * 연산자로 연결하면 =($B$3:$H$8=K$2)*($B$2:$H$2=$J3)이 되는데, #분산!라고 표시되고, 6*7의 크기로 테두리가 쳐집..

1. 문제 D열의 값과 O열의 월에 해당하는 값, 다시 말해 2024/01은 E열, 2024/02는 F열의 값을 곱한 다음 그 합을 구해서 P열에 입력하는 것입니다. P5열에 예시로 든 수식은 =(D5*E5)+(D6*E6)+(D7*E7)+(D8*E8)+(D9*E9)로 전형적인 SumProduct 함수의 사용 예입니다. 2. SumProduct 함수 SumProduct 함수의 구문은 =SUMPRODUCT(array1, [array2], [array3],...)으로 곱할 값들의 범위 또는 배열을 1개 이상 입력하는 것입니다. 위와 같은 경우 array1은 D5:D9가 되고, array2는 E5:E9가 됩니다. P4셀에 =SUMPRODUCT(D5:D9,E5:E9)라고 입력하면 =(D5*E5)+(D6*E6)+(..

1. 문제 A셀의 문자 길이가 4인 경우 B셀의 값을 합계하는 것입니다. 2. 해결 방안 다양한 해결방안이 있을 수 있습니다. ① A열의 코드를 살펴보니 A3세의 값이 5116인 경우 A4셀부터 5116으로 코드가 시작되므로 4개를 잘랐을 때 코드 값이 같은 것을 더할 수도 있고, ② 코드 길이가 7자리에서 4자리로 바뀔 때, 다시 말해 코드의 길이가 4인 것 전까지 더할 수도 있습니다. 그리고, 함수도 Sum과 If 배열 수식을 이용할 수도 있고, SumIf를 이용할 수도 있고, sumProduct, 그리고, Offset과 Match함수를 이용할 수도 있습니다. (1) Sum과 If 배열 수식을 이용하는 경우 =SUM(IF(LEFT($A$4:$A$24,4)=A3,$B$4:$B$24,0)) A열의 왼쪽 ..