|
发表于 2012-9-29 17:02
|
显示全部楼层
本楼为最佳答案
如果直接用公式,很长,选中H5单元格,定义三个名称:1、用来计算出生天数 =TODAY()-DATE(MID(Sheet1!$G6,7,4),MID(Sheet1!$G6,11,2),MID(Sheet1!$G6,13,2)) 2、用来定义比出生天数大的值 =IF(Sheet1!H$5="42天",153,IF(RIGHT(Sheet1!G$5,1)="月",LEFT(Sheet1!G$5,LEN(Sheet1!G$5)-2)*30,IF(RIGHT(Sheet1!G$5,1)="岁",LEFT(Sheet1!G$5,LEN(Sheet1!G$5)-1)*30*12,42)))3、定义一个比天数小的值 =IF(RIGHT(Sheet1!H$5,1)="月",LEFT(Sheet1!H$5,LEN(Sheet1!H$5)-2)*30,IF(RIGHT(Sheet1!H$5,1)="岁",LEFT(Sheet1!H$5,LEN(Sheet1!H$5)-1)*30*12,42))
在显示单元格中输入=IF(ISERROR(IF(AND(天数<大条件,天数>=小条件),"√","")),"",IF(AND(天数<大条件,天数>=小条件),"√","")) 计算金额数组公式{=SUM(IF($H6:$S6="√",1,0)*{22,11,46,11,11,26,11,26,11,31,31,31})}
上传不了附件 |
|