엑셀 장부만들기 : IF, 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

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

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