파드레의 요모조모

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

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

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

 수입의 경우 수식은 "=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함수와 if 함수로 N열에서 납입금액을 계산한 것으로 조건서식에서 수식을 입력하여 색상을 표시한 것입니다. 납입개월수와 잔여금액은 조건서식으로 표시된 결과가 맞는지 확인하는데 도움이 되도록 계산을 하였습니다.

조건서식 만들기

위 오른쪽 그림은 홈에서 조건부 서식을 클릭했을 때 메뉴이다. 위와 같이 수식을 삽입하여 만드려면 새규칙을 입력해야 한다. 오른쪽 그림의 규칙 설명편집에서 "=($O3-COLUMN(B$3)+2>0)"을 넣는다.

COLUMN(B$3)은 열의 위치를 숫자로 표시하는 함수이다. B3의 값은 3이다. 

참고로 COLUMNS(범위)는 배열의 셀수를 세는 함수이다.

조건서식이 시작하는 B3셀에서 서식 설정한 후 B3셀 서식을 복사해서 B3셀부터 시작해서 M13셀까지 셀을 붙여넣기한다. 

B3셀에 적용되는 수식의 의미는 "O3의 납입개월수에서 현재 컬럼수의 값을 빼고 2를 더한 값이 "0"보다 크면 빨강색으로 표시한다."이다.  빨강색 서식을 지정하려면 서식을 눌러 아래와 같은 창이 나오면 채우기에서 원하는 색을 선택한 후 확인을 누른다.

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

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

셀에 표시를 하기 위해서 조건서식과 수식이 필요하고 역시 회원들의 납입금액을 계산하기 위해 집합연산 즉 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

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

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

장부 시트의 작성을 살펴보겠습니다.

1. 틀고정

   엑셀시트에서 데이타를 입력하거나 조회할때 맨위의 항목이름이나 오른쪽의 내용이 넘어가서 보이지않게되는

   것을 방지하여 화면이 잘 보이도록 만드는 것입니다.

   틀 고정은 보기에서 틀고정을 설정합니다.  예제처럼 항목과 열의 고정을 원하는 경우에 그림처럼 설정하고 행만하는 경우와 열만하는 경우는 그림에 틀 고정 밑에 있는 항목을 선택합니다.

아래 그림과 같이 인쇄를 하는데 항목이 반복적으로 나타나도록 인쇄하기 위해서 인쇄설정을 바꾸는 방법을 알아보겠다.

페이지 레이아웃 => 누르고 인쇄제목 => 시트를 누르고 인쇄 영역을 설정하고 반복할 행을 설정한다.

위의 4번을 누르면 아래 그림과 같이 나오는데 마우스로 장부시트에서 반복할 행을 오른쪽 숫자에서 누르면 행이 설정된다.

이전 : 교회 회계장부, 가계부 만들기 (엑셀)

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