exl函数求简化,是判断L列多个单元格分别在b列一个单元格中是否存在,存在则输出第N列对应单元格数值,不存在输入0,并求所有输出数值的和写在a列,并且b列每一个单元格都需要进行这样的求和。例如
b7=红包我拿 L7=红包 N7=8
b8=红包她拿 L8=他 N8=9
L9=我 N9=5
L10=拿 N10=3
L11=她 N11=2
那么A7=8+0+5+3+0=16
A8=8+0+0+3+2=13
我写出的公式太复杂
a8=IF(COUNTIF(B$8,"*"&L1&"*"),N1,"0")+IF(COUNTIF(B$8,"*"&L2&"*"),N2,"0")+IF(COUNTIF(B$8,"*"&L3&"*"),N3,"0")+IF(COUNTIF(B$8,"*"&L4&"*"),N4,"0")+IF(COUNTIF(B$8,"*"&L5&"*"),N5,"0")+IF(COUNTIF(B$8,"*"&L6&"*"),N6,"0")+IF(COUNTIF(B$8,"*"&L7&"*"),N7,"0")
a7==IF(COUNTIF(B$7,"*"&L1&"*"),N1,"0")+IF(COUNTIF(B$7,"*"&L2&"*"),N2,"0")+IF(COUNTIF(B$7,"*"&L3&"*"),N3,"0")+IF(COUNTIF(B$7,"*"&L4&"*"),N4,"0")+IF(COUNTIF(B$7,"*"&L5&"*"),N5,"0")+IF(COUNTIF(B$7,"*"&L6&"*"),N6,"0")+IF(COUNTIF(B$7,"*"&L7&"*"),N7,"0")
求简单的公式
ä¸åç¡®ãé¦å æä¸ä» è¦æ±å¤æï¼è¿è¦æ±å¤ææ£ç¡®ä¼è¾åºNåæå®æ°å¼ï¼å¹¶ä¸è¿æè¦æ±Låæ¯ä¸ä¸ªåå æ ¼é½å¤æä¸æ¬¡å¹¶ä¸è¾åºçæ°å¼é½å èµ·æ¥