컴퓨터를 만나는 기쁨

1. 문제아래와 같이 이월액과 월별 매출액, 수금액, 미수금이 있을 때 아래와 같이 월별(연령별) 채권잔액을 구하려고 합니다. 2. 해법가. 논리7월의 채권잔액은 7월까지의 매출액(이월액 포함)에서 미수금의 합계를 차감한 다음 6월까지의 채권잔액을 차감하면 되는데, 6월까지의 채권잔액은 6월까지의 매출액(이월액 포함)에서 미수금의 합계를 차감한 금액으로 7월의 채권잔액을 구할 때나 6월까지의 채권잔액을 구할 때나 미수금의 합계는 같습니다. 나. 수식 만들기 (1) 7월 채권잔액 구하기 ㉮ 7월까지 매출액 계 구하기 '거래처수불현황'시트에서 7월까지의 매출액(이월액 포함) 합계를 구하는데, '매출액'이라는 글자는 4행에 있고, 금액은 그 아래 있습니다. 따라서, 7월까지의 매출액 합계는 SumIf 함수를 ..

1. 문제아래와 같이 이월액과 월별 매출액, 수금액, 미수금이 있을 때 월별 채권 잔액을 7행과 8행에서 구해보겠습니다. 2. 해법가. 논리문제를 풀려면 어떤 식으로 값을 구해야 하는지 논리가 먼저 정립이 돼야 합니다. (1) 1월의 채권잔액 구하기 1월의 채권잔액은 1월까지 발생한 채권액(이월액 포함)을 모두 회수했다면(초과한 경우 포함) 0이고, 아니면 채권액 - 수금액의 합계가 됩니다. (2) 2월 이후의 채권잔액 구하기 1월은 간단하게 위와 같이 구할 수 있는데, 2월은 2월까지의 채권잔액에서 1월의 채권잔액을 차감해야 합니다. 왜냐하면 2월까지의 채권액에서 수금액 계를 빼면 2월까지의 채권잔액이 나오고, 이것은 1월의 채권잔액을 포함한 금액이기 때문입니다. 이런 식으로 3월 이후는 3월까지의 채..

1. 엑셀과 구글 스프레드시트의 비교 엑셀은 Microsoft 365에서만 ByRow 함수가 지원되는데,구글 스프레드시트는 인터넷에서만 실행되지만 ByRow와 ByCol 함수를 모두 지원합니다. 아래와 같이 =by까지 입력하면 그 아래 BYCOL과  BYROW가 표시되고, 간단한 함수에 대한 설명, 여기서는 "행 별로 범위를 그룹화합니다."라고 표시됩니다. 또한 탭키를 눌러 BYROW를 선택하면인수가 표시되는데, 배열_또는_범위가 활성화되고, 람다가 두 번째 인수로 표시됩니다.  이에 비해 Excel에서는 인수로 array가 활성화되고, 두 번째 인수가 function이라고 표시됩니다. function이라고 표시되지만 Lambda인 것은 동일합니다. 2. 구글 스프레드시트에서 ByRow 함수 적용 위 파..

5. 해법 4ByRow 함수를 이용해 행 별로 곱해서 0보다 큰 경우의 수를 세서 값을 구할 수 있습니다.ByRow 함수는 Microsoft 함수에서만 사용 가능한 함수로서구문은 =BYROW(array, lambda(row))입니다.배열을 입력한 다음 lambda의 row 인수로 전달한 다음 수식을 통해 값을 구하는 것입니다. 가. 한 가지 조건을 만족하는 경우=BYROW(B2:B33,LAMBDA(row,IF(row>0,1,0)))라고  입력하면 행 별로 셀 값이 반환됩니다.  수식의 의미는B2셀에서 B33셀까지 범위를 입력한 다음Lambda함수의 첫 번째 인수로 대입하고,수식(calculation)으로, IF(row>0,1,0)를 입력한 것입니다.따라서 행의 값이 배열로 반환됩니다.  이제 필요한 것이..

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

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

1. 문제아래와 같이 월별 계획과 실적이 입력되어 있는데, B3셀에 월을 입력하면 해당 월까지의 합계가 B6셀과 C6셀에 표시하려고 합니다. 현재 수식은 3월이기 때문에 D6,F6,H6셀의 합계로 되어 있는데,이것이 월에 따라서 개수가 달라지고, 계획은 계획끼리 더해져야 하는 것입니다.   2. 해법 1 가. Mod, Column 함수Mod함수를 이용해 나머지가 홀수 또는 짝수인 것의 셀 주소를 더하면 됩니다.B7셀에 =MOD(COLUMN(D6:AA6),2)이라고 입력하고 엔터키, 이전 버전의 경우는 Ctrl + Shift + Enter 키를 누르면 0,1이 반복되면서 B열부터 시작했기 때문에 11월 실적 열인 Y열에서 끝납니다.  이제 계획이 0이므로 계획은 0인 것만 더하면 됩니다. 나. Offset..

1. 문제 왼쪽에 담당자별 지역별 현황이 있는데, 이것을 오른쪽 표와 같이 담당자별로 지역별로 집계를 하려고 하는데, 특이한 점은 지역을 묶어서 집계한다는 것입니다. 이런 경우에 일반적으로 사용하는 것이 피벗 테이블이지만, 위와 같이 수원, 용인 경우의 합계를 구하려면 두 개의 값을 더할 수밖에 없습니다. 이와 같은 경우에 수원 또는 용인인 경우라는 조건을 넣어서 건수를 집계하고자 하는 것입니다. 2. 해법 1 : TextSplit 함수 이용 가. Sum + CountIfs 이용 TextSplit 함수는 Microsoft 365에서만 사용 가능한 함수이기 때문에 이보다 낮은 버전이라면 사용할 수 없는 제한은 있지만 이와 같은 경우에 매우 쉽게 처리할 수 있습니다. 다시 말해 수원, 용인을 TextSpli..

1. 문제 아래와 같이 이름과 값이 자료가 있을 때, 이름에 해당하는 값의 합계를 구하려고 합니다. 2. 해법 1 위와 같은 표에서 이름이 홍길동이거나 장발산에 해당하는 값의 합계를 구하려면 =SUMPRODUCT(($A$3:$A$7=D3)+($A$3:$A$7=D4),$B$3:$B$7) 라고 입력해서 합계를 구합니다. 위 수식을 보면 ($A$3:$A$7=D3)+($A$3:$A$7=D4)라고 ($A$3:$A$7=D3)과 ($A$3:$A$7=D4)가 +로 연결되어 있는데, 이것은 두 가지 조건 중 하나만 일치해도 된다는 Or 조건입니다. 다시 말해 위 수식은 이름이 홍길동이거나, 이무인 경우가 됩니다. 그리고, 값의 범위 $B$3:$B$7에서 조건에 맞는 값을 찾아 합계를 구하는 것입니다. 이때 쉼표(,)를 ..

문제는 여기를 참고 바랍니다. 3. 해법 2 : 이름뿐만 아니라 나머지 내용까지 매크로로 처리하는 방법 가. 논리이번에는 셀 값을 Vlookup함수가 아니라 다른 시트의 셀 주소로 연결하는 방법과 같은 연번에서 이름이 반복될 경우 고지서에 한꺼번에 인쇄하는 매크로를 만들어 보겠습니다. (1) 셀 값을 다른 시트의 셀 주소로 연결하기 예를 들어 고지서 시트의 회사명(성명)인 D6셀에는 명단 시트의 E열의 값이 입력되어야 합니다. 이 때는 연결키가 없어도 됩니다.그리고, 토지 소재지인 시군, 읍면, 리동은 명단 시트의 G, H, i 열과 연결되면 됩니다. (2) 같은 연번에서 이름이 반복될 경우 인쇄하기 "같은 연번"이란 병합된 셀로서 처리 줄과 윗 줄의 A1셀이 동일한 경우이고, "이름이 반복된다"는 것은..