假如数据在sheet1的A列和B列,区域在A2:B5,公式可分两步:
第一步把数值大于0对应的内容提取出来,假如放在表二的C列,公式=INDEX(sheet1!A:A,SMALL(IF(sheet1!B$2:B100>0,ROW($2:$100),4^8),ROW(sheet1!A1)))&"",按ctrl+shift+回车;
第二步提取数值,公式=IF(C2="","",VLOOKUP(C2,A:B,2,0))。
追问![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/caef76094b36acafaaab44b877d98d1001e99c2f?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/960a304e251f95ca141a4ff2c2177f3e66095283?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
老师你好!这是一张考勤表,AI列是“冲抵项”,负值是请假,正值是加班,加班要统计出来报加班费,所以有此想法此
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/c2fdfc039245d68803726be0afc27d1ed21b2406?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
将上一表中的“冲抵项”里的正数引用到这张表的加班里