è¡¥å
åç
=sumproduct((mod(row(a1:a100),10)=1)*row(a1:a100))
row()æ¯åè¡å· row(a1:a100)å³å1,2,3.....100
mod()æ¯ä¸ä¸ªæ°é¤ä»¥å¦å¤ä¸ä¸ªæ°ååä½æ°,æ¯å¦mod(91,10)å°±æ¯91é¤ä»¥10以åçä½æ°æ¯1
mod(row(a1:a100),10)=1çææå°±æ¯æ1~100è¿100个æ°åå«ä¸10ç¸é¤åçä½æ°æ¯ä¸æ¯=1,çäº1ä¼è¿åtrueå³1,å¦åè¿åfalseå³0,è¿æ ·ä¼è¿å1个ç±1å0ç»æçæ°å
åç¨
sumproduct({1,0,0,0,0,0,0,0,0,0,1,0,0......}*{1,2,3,4,.........100})两两ç¸ä¹åæ±å,å°±å¾å°äº1+11+21+31...91çå
é´éåºè¯¥æ¯20ç~ å
¬å¼æ¹ä¸º =sumproduct((mod(row(a1:a100),20)=1)*row(a1:a100))
温馨提示:内容为网友见解,仅供参考