ä¸ç¥ä½ çæªå¾ä¸æ为ä½ä¼åºç°â0âï¼æçå
¬å¼ä¸åé¢è¿æ¥äºä¸ä¸ªââï¼å°±æ¯ä¸ºå»æè¿ä¸ªâ0âçã
å
¬å¼è§£è¯»ï¼
å
ç
OFFSET($B$1,,,ROW($1:$100))
å°±æ¯ä»B1å¼å§ï¼åä¸å移ï¼å¾å°çé«åº¦ï¼Bååå
æ ¼ä¸ªæ°ï¼ä»1 å°100çä¸ä¸ªå
åæ°ç»ã第1个åªæä¸ä¸ªæ°æ®ï¼B1ï¼ç¬¬2个2个æ°æ®ï¼B1:B2ï¼ä¾æ¬¡ç±»æ¨ï¼å°B1:B100ã
åæ¥åæå¤å±çSUMIFï¼å°±æ¯offsetå¾å°æ°ç»æ¡ä»¶æ±åï¼ä»ç¶å¾å°æ°æ®ï¼ç¬¬1个ï¼å¯¹B1æ±åï¼æ¾ç¶ç»æ为0ï¼ç¬¬2个å¾å°5ï¼ç¬¬3个å¾å°8ï¼ç¬¬4个å¾å°12ï¼ç¬¬5个å以åçé½æ¯12ï¼å³å¾å°æ°ç»ï¼
{0;5;8;12;12;12;â¦â¦12}
ååæLOOKUP
æ¥æ¾ç第1åæ°æ¯ROW(B1)-1ï¼å
¬å¼ä¸ææ¶ï¼ä¾æ¬¡å¾å°0ï¼1ï¼2ï¼3ï¼4ï¼5ï¼6ï¼çç
LOOKUPå¨SUMIFå¾å°æ°ç»ä¸å»æ¥æ¾è¿äºæ°å¼æ¶ï¼æ¾å°âå°äºæçäºâæ¥æ¾å¼çæ大å¼ï¼æ以å5 个ï¼0ï¼1ï¼2ï¼3ï¼4ï¼ï¼æ¾å°å°äºæçäºï¼0ï¼1ï¼2ï¼3ï¼4ï¼çæ大æ°å°±æ¯0ï¼ä½ç½®å¨ç¬¬1ä½ï¼äºæ¯è¿å第3åæ°ä¸ç¬¬1个ä½ç½®çæ°æ®ï¼ä¹å°±æ¯A2ä¸çâä»å¥³æ£âï¼å½å
¬å¼ä¸æå°D6æ¶ï¼LOOKUPæ¥æ¾ç第1åæ°ä¸º5äºï¼sumifå¾å° æ°ç»ä¸ï¼å°äºæçäº5çæ大å¼å°±æ¯5ï¼ï¼æå¨ç¬¬2ä½ï¼æ以è¿å第3åæ°ä¸ç第2个æ°æ®ï¼ä¹å°±æ¯A3ä¸çâå°æ¹âã
å
¬å¼æ¥å°D13æ¶ï¼ç¬¬1åæ°åæ11[ROW(B12)-1]ï¼SUMIFå¾å°çæ°ç»ä¸âå°äºæçäºâ11çæ大å¼ï¼ä»æ¯8ï¼å¨æ°ç»ä¸ç第3ä½ç½®ï¼æ以è¿å对åºA4ä¸çâæå·è´¹âã好äºçå
¨é¨éå¤å®åçD14ï¼è¿æ¶ç¬¬1åæ°åæ12[ROW(B13)-1]ï¼å¨SUMIFå¾å°çæ°ç»ä¸âå°äºæçäºâ11çæ大å¼å°±æ¯12ï¼å¯¹åºçä½ç½®å¨ç¬¬4ä½ç½®ï¼æ以è¿åA5çæ°æ®ï¼A5ä¸æ²¡ææ°æ®ï¼å
¬å¼å¼ç¨æ¶ï¼è¿åâ0âï¼åé¢è¿æ¥äºä¸ä¸ªç©ºï¼ââï¼ï¼æ以æ¾ç¤ºä¸ºç©ºãèä½ çæªå¾å´æ¾ç¤ºä¸º0ï¼ä¸ç¥ä½æ
ã
追é®![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/a50f4bfbfbedab643a47d3bde736afc379311e91?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
è¿ä¸ªOFFSETéé¢çç»æ为ä»ä¹è¦çæ°éå¢ï¼
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/d6ca7bcb0a46f21f3701c02ee6246b600c33ae92?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
è¿æè¿ä¸ªSUMIF为ä»ä¹ä¸åæ±åæ¡ä»¶
追çæ¨å¤©çåçåºè¯¥è¯¦ç»äºå§ï¼offsetå¾å°çç»æä¸æ¯ä¸ä¸ªæ°ï¼èæ¯æ°ç»ï¼æ°ç»æ¯éè¿åé¢çé«åº¦ä¸å产ççï¼row(1:100)å¾å°çæ¯æ°ç»ââ1å°100çï¼æ以offsetçç»æå°±æ¯100个æ°æ®ç»æçæ°ç»ï¼ç¬¬1个æ¯ä»B1å¼å§çä¸è¡æ°æ®ï¼å°±æ¯âæ°éâï¼ç¬¬2个æ°æ®æ¯B1:B2两æ°æ®ï¼ä¾æ¬¡ç±»æ¨ãä½ çæªå¾ä¸æ¾ç¤ºç»æ为âæ°éâï¼å°±æ¯æ¾ç¤ºçæ°ç»ä¸ç¬¬1个æ°æ®ââæ°éãä¹æ以è¦ä»¥ç¬¬1è¡å¼å§ï¼ç¬¬1è¡ä¸æ¯æ°åï¼ï¼ç¨sumifæ¡ä»¶æ±åæ¶ï¼å°±è½å
å¾å°ç»æ为0çæ°æ®ï¼ä»¥è¾¾å°å
åå°ç¬¬1个ç®æ æ°æ®A2çç®çã
sumifä¸ç第3åæ°å¿½ç¥æ¶ï¼å°±æ¯å¯¹ç¬¬1åæ°åæ±ååºåãå
¬å¼ä¸ææ±åæ¡ä»¶åï¼â>0"ï¼å°±æ¯æ±åæ¡ä»¶ã