用数组公式进行判别一组数交替上下变化,和判别连续上升或连续下降,为什么实现不了,恳请各大侠支招。见如下:
序号 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 数值1 | 23 | 28 | 44 | 28 | 76 | 35 | 46 | 49 | 57 | 78 | 83 | 95 | 33 | 28 | 50 | 43 | 36 | 30 | 27 | 24 | 18 | 13 | 29 | 57 | 19 | 36 | 54 | 46 | 49 | 51 | 数值2 | 23 | 28 | 44 | 28 | 76 | 49 | 89 | 43 | 55 | 46 | 60 | 41 | 53 | 28 | 52 | 43 | 66 | 30 | 44 | 38 | 57 | 35 | 49 | 37 | 49 | 36 | 54 | 46 | 49 | 51 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | A | 6 | 第五行 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | B | 10 | 第六行 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
第五行:想要达到的效果是,体现至少6个及以上连续上升或连续下降的数据,如果是就用A表示
C5~AF5使用的数组公式:{IF(OR(C1="",C1<$B$5),"",IF(OR(SUM(OFFSET(C2,,1-$B$5,,$B$5-1)>=OFFSET(C2,,2-$B$5,,$B$5-1))>$B$5-1,SUM(OFFSET(C2,,1-$B$5,,$B$5-1)<=OFFSET(C2,,2-$B$5,,$B$5-1))>$B$5-1),$A$5,"C"))}
第六行:想要达到的效果是,体现至少10个及以上交替大小变化的数据 ,如果是,就用B表示。
C6~AF6使用的数组公式:{IF(OR(C1="",C1<$B$6),"",IF(SUM((OFFSET(C3,,1-$B$6,,$B$6-1)<=OFFSET(C3,,2-$B$6,,$B$6-1))*MOD(OFFSET($C$1,,,,$B$6-1),2))=ROUND(($B$6-1)/2,),$A$6,"C"))}
|