파드레의 요모조모

본 장부만들기에서 대차대조표가 매우 형편없다. 왜냐하면 내용이 별로 없어서이다.

너무나 단순한 것을 갖고 자료를 만들다보니 다소간 빈약하다

그럼에도 불구하고 여기서 사용하는 수식은 충분히 더 복잡하고 중요한 자료에도 다 쓸 수 있는 수식임으로 다시 설명하겠다.

 수입의 경우 수식은 "=SUMPRODUCT((장부!$A$2:$A$276=2019)*(장부!$F$2:$F$276)*(장부!$H$2:$H$276="수입"))"이고 여기서 회계년도 부분이 장부에서 귀속연도가 2019년 인 것을 논리 곱으로 금액과 곱하고 수입인 것과 논리 곱을 했다. 세부 항목부분도 마찬가지 방법으로 논리 곱을 하여 더한 것이다.

회비는 =SUMPRODUCT((장부!$A$2:$A$276=2019)*(장부!$F$2:$F$276)*(장부!$H$2:$H$276="수입")*(장부!$I$2:$I$276=C5))

sumproduct에 대한 자세한 설명은 앞에서 설명했으니 간단히 넘어가겠다.

여기서 대차대조표의 항목은 항목계의 구성에서 정한 것으로 내역을 만드는 것이다. 그래서 처음에 이런 자료의 구성이 중요하다고 했었다.

이와 같은 것은 피벗 테이블을 통해서도 비슷하게 구현할 수 있으나 피벗 테이블로는 원하는 모양을 구현하기 위해서는 수정작업을 추가로 해야하고 더 유연한 수식을 구사하기가 힘들다. 그러나, 위의 sumproduct수식을 이용하면 좀더 유연하고 복잡한 결과들을 원하는 모양으로 간단히 구현 할 수 있다.

피벗 테이블이 유연하게 이렇게 저렇게 모양과 스타일을 바꿀 수 있고 편리하지만 어떤 특정한 형태로 만들고자하면 좀 많이 불편할 수 있으나 직접 수식을 입력하여 처리하면  원하는 서식에 맞게 간단히 만들 수 있으며 결과를 재설정하지않아도 표가 갱신되는 장점이 있다. 

앞의 포스팅을 이어서 설명합니다.

회원들의 회비 납부내역을 시각적으로 표시하기 위해 시트를 만들었습니다.

셀에 표시를 하기 위해서 조건서식과 수식이 필요하고 역시 회원들의 납입금액을 계산하기 위해 집합연산 즉 sumproduct 함수의 연산 수식이 필요합니다.

1. SUMPRODUCT , IF 함수 사용하기

회원 회비납부 표시를 자동으로 하기 위해 회원들의 납입금을 자동으로 계산하기 위해 sumproduct함수를 사용합니다.

N3셀의 수식은 "=IF(SUMPRODUCT((장부!$A$2:$A$181=2019)*(장부!$F$2:$F$181)*(장부!$E$2:$E$181=$A3)*(장부!$I$2:$I$181="회비"))=0,"",SUMPRODUCT((장부!$A$2:$A$181=2019)*(장부!$F$2:$F$181)*(장부!$E$2:$E$181=$A3)*(장부!$I$2:$I$181="회비")))"입니다.

이 수식은 if함수와 sumproduct 함수를 섞은 것으로 sumproduct 결과가 "0"이면 빈칸으로 두고 아니면 sumproduct의 결과 값을 입력하는 것입니다.

if 함수는 "=if (조건, 참일때 값, 거짓일때 값)" 과 같이 사용합니다.

특별히 계산할 때 셀값에 N/A등의 오류 표시가 나지 않도록 할때 if( iserror(수식), "",수식)과 같이 입력하면 오류가 표시되지않도록 할 수 있습니다.

sumproduct 함수 "배열의 연산 합계를 구합니다."

여기서 논리 연산을 이해해야 합니다.

장부!$A$2:$A$181=2019는 장부 시트의 A2에서 A181까지의 셀에서 "셀의 값이 '2019'이다" 가 참이 되면 '1', 아니면 '0'이 된다. 0이 되는 것은 뒤에 값이 무엇이 되든 0임으로 '0'이고 '1'되면  '장부!$F$2:$F$181'를 곱하는데, F2에서 F181까지의 셀값을 곱하게 된다. '장부!$E$2:$E$181=$A3'는 E2에서 E181의 셀 중에 A3와 같은 값을 갖으면 1 아니면 0입니다. A열의 값은 회원이 이름입니다. 장부에서 회원의 이름을 찾는 것입니다.  장부!$I$2:$I$181="회비에서도 S열은 계에서 내역이 '회비'인 경우에만 1이는 것입니다.

풀어서 다시 생각해보면 (장부!A2=2019)*(장부!F2)*(장부!E2=A3)*(장부!I2="회비")+(장부!A3=2019)*(장부!F3)*(장부!E3=A3)*(장부!I3="회비")+ .....  와 같이 됩니다.

위 그림에서 적용해 해보면 

    (장부!A2=2019)*(장부!F2)*(장부!E2=A3)*(장부!I2="회비")

    =( 기준년이 2019로 1)*(장부의 금액 30,000)*(홍길동이 아닌 곽재우임으로 0)*(회비임으로 1)

    = 1 * 30,000 * 0 * 1

    = 0

   (장부!A3=2019)*(장부!F3)*(장부!E3=A3)*(장부!I3="회비")

    =( 기준년이 2019로 1)*(장부의 금액 15,000)*(홍길동이 맞음으로 1)*(회비임으로 1)

     = 1 * 15,000 * 1 * 1

     = 15,000

.

.

.

장부!$A$2:$A$181라는 범위에서 $표시는 수식을 복사할때 범위의 값이 변하지않도록 만들어줍니다.

셀 수식 창에서 그림과 같이 범위 부분을 마우스로 선택하고 키보드 상단의 F4버튼을 계속 누르면 

장부!$A$2:$A$181 => 장부!A$2:A$181 => 장부!$A2:$A181 => 장부!A2:A181=> 장부!$A$2:$A$181

와 같이 변한다.

장부!A$2:A$181 => 복사할때 열값 고정,

장부!$A2:$A181 => 복사할때 행값 고정,

장부!$A$2:$A$181 => 복사할때 행값열값 고정

위의 고정값을 잘 사용하면 수식을 복사하여 사용할때 매우 유용합니다.

      : 교회 회계장부, 가계부 만들기 2020.04.30

       : (엑셀)엑셀 장부만들기 : 틀고정, 인쇄설정 

이전 : 엑셀 장부만들기 : 스키마 만들기(항목구성하기),유효성검사