엑셀함수-IF 와 COUNTBLANK 이용하여 값을 구하세요.

<TABLE style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse" height=240 cellSpacing=0 cellPadding=0 width=532 border=0> <TBODY> <TR height=30> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: black 0.75pt dotted; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=30>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: black 0.75pt dotted; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69 bgColor=silver> A</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: black 0.75pt dotted; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101 bgColor=silver> B</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: black 0.75pt dotted; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70 bgColor=silver> C</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: black 0.75pt dotted; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81 bgColor=silver> D</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: black 0.75pt dotted; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84 bgColor=silver> E</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90 bgColor=silver> F</TD></TR> <TR height=30> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=30> 1</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69 bgColor=yellow> <A name=RANGE!A1:F9></A>초기잔액</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101> 1,000,000</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84 bgColor=yellow>  수입총계</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90> 441,500</TD></TR> <TR height=30> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=30> 2</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69 bgColor=yellow> 현재잔액</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101> 1,207,500</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84 bgColor=yellow>  지출총계</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90> 234,000</TD></TR> <TR height=21> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=21> 3</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81>  </TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84>  </TD> <TD style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0px; BORDER-TOP: medium none; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; BORDER-LEFT: medium none; PADDING-TOP: 0px; BORDER-BOTTOM: black 0.5pt solid" width=90>  </TD></TR> <TR height=29> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=29> 4</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69 bgColor=yellow> 번호</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101 bgColor=yellow> 날짜</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70 bgColor=yellow> 적요</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81 bgColor=yellow> 수입</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84 bgColor=yellow> 지출</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90 bgColor=yellow> 잔액</TD></TR> <TR height=25> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=25> 5</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69> 1</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101>  1996-01-01</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70> 　</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81> 100,000</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84> 　</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90> 1,100,000</TD></TR> <TR height=25> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=25> 6</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69> 2</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101>  1996-01-04</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70> 　</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81> 340,000</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84> 　</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90> 1,440,000</TD></TR> <TR height=25> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=25> 7</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69> 3</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101>  1996-01-07</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70> 　</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81> 1,500</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84> 134,000</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90> 1,307,500</TD></TR> <TR height=25> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: black 0.75pt dotted; BORDER-BOTTOM: black 0.75pt dotted" width=37 bgColor=silver height=25> 8</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=69> 4</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=101>  1996-01-08</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=70> 　</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=81> 　</TD> <TD style="BORDER-RIGHT: black 0.75pt dotted; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.75pt dotted" width=84> 100,000</TD> <TD style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: medium none; BORDER-LEFT: medium none; BORDER-BOTTOM: black 0.5pt solid" width=90> 1,207,500</TD></TR></TBODY></TABLE>   번호 A5 셀에 수식 : =IF(COUNTBLANK(D5:E5)<2,1,"") A6 셀에 수식 : =IF(COUNTBLANK(D6:E6)<2,A5+1,"") A7 셀 이후에 수식은 A6 셀 수식을 복사해서 붙여넣기 하십시요.   수입총계 F1 셀에 수식 : =SUM(D5:D8)   지출총계 F2 셀에 수식 : =SUM(E5:E8)   현재잔액 B2 셀에 수식 : =B1+F1-F2   잔액 F5 셀에 수식 : =IF(COUNTBLANK(D5:E5)<2,SUM(B1+D5-E5),"") F6 셀에 수식 : =IF(COUNTBLANK(D6:E6)<2,SUM(F5+D6-E6),"") F7 셀 이후에 수식은 F6 셀 수식을 복사해서 붙여넣기 하십시요.   ============= COUNTBLANK    지정한 범위에 있는 공백 셀의 개수를 계산합니다.   구문 COUNTBLANK(range)   Range   공백 셀의 개수를 계산할 범위입니다.   주의 ""(빈 텍스트)를 반환하는 수식이 들어 있는 셀의 개수도 계산됩니다. 그러나 0값을 갖는 셀의 개수는 계산되지 않습니다.