Excel公式-查找由0值包围的列中的动态范围

我正在电子表格中查看1分钟分辨率的股票数据。我已经在这个问题上纠结了好几天了，测试了不同的方法。
我所做的是，我有一个公式，根据股票数据来决定进场和卖出，然后我有两个现有的公式，可以找到股票在进场和卖出分钟之间的最高/最低价格。到目前为止，一切顺利，现在我需要找到在哪一分钟（= row）这个最高和最低的价格发生了。我有一些版本，我测试的工作时，我只有1个条目和出售，但是当我有几个条目并出售时，它就会变得混乱。

增加了样本数据的屏幕截图。该数据应从底部和顶部读取。如果交易活跃，第一列= 1。第二列记录活跃交易中出现的最高值，并在下一次交易时重置。第三列记录活跃交易中出现的最低值，并在下一次交易时重置。
我需要帮助在列4 - 5公式找到当最高值发生在活跃的贸易和最低值分别。
可能有这样的情况，我有超过2个条目，以及。当前公式在第4列：= IF（AND（B4〉B5，B3〈= B4），%% time of row from other cell here %%，MISSING（））正如你所看到的，对于第二个条目，我有两种情况下这个公式给出"true"，这是错误的。只有一个显示为09：54应该在那里。
第5列的公式很糟糕，它是用MIN-if组合写的，但我知道在很多情况下这不起作用，所以我在这里省略了它。
编辑：
| 时间|1 =交易中|迄今为止的最高贸易额|迄今为止贸易额最低|高分钟|低分钟|
| - ------| - ------| - ------| - ------| - ------| - ------|
| 2020年5月12日10时01分|无|无|无|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日上午10时|1个|四、十三|三、五|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时59分|1个|四、十三|三千九百零三|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时58分|1个|四、十三|三千九百零三|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时57分|1个|四、十三|三千九百零三|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时56分|1个|四、十三|三千九百零三|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时55分|1个|四、十三|三千九百零三|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时54分|1个|四、十三|三、九十四|09时54分|#萨克纳斯!|
| 2020年5月12日09时53分|1个|四点零九分|三、九十四|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时52分|1个|四点零九分|三、九十四|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时51分|1个|四点零九分|三、九十四|上午9时51分|#萨克纳斯!|
| 2020年5月12日09时50分|无|无|无|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时49分|无|无|无|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09：48| 1个|四点零三分|三、八十八|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时47分|1个|四点零三分|三、八十八|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时46分|1个|四点零三分|三、八十八|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时45分|1个|四点零三分|三、八十八|上午9时45分|#萨克纳斯!|
| 2020年5月12日09时44分|1个|四点零一分|三、八十八|#萨克纳斯!| #萨克纳斯!|
| 2020年5月12日09时43分|1个|三、九十八|三、八十八|#萨克纳斯!|上午9时43分|
| 2020年5月12日09时42分|无|无|无|#萨克纳斯!| #萨克纳斯!|

麦克斯比有点矫枉过正，但不如这样：

=LET(range,A2:C21,
     time,INDEX(range,,1),
     trade,INDEX(range,,2),
     value,INDEX(range,,3),
     DROP(REDUCE(0,trade,
          LAMBDA(x,y,
          VSTACK(x,
                 IF(y=0,
                    "",
                    LET(sq,SEQUENCE(COUNTA(trade)),
                        start,XLOOKUP(1,(trade=0)*(sq+1<=ROW(y)),sq+2,"",0,-1),
                        end,XMATCH(1,(INDEX(trade,sq)=0)*(sq+1>=ROW(y))),
                        sqsingle,SEQUENCE(1+end-start,,start-1),
                        singlevalue,INDEX(value,sqsingle),
                        singlemax,XLOOKUP(MAX(singlevalue),singlevalue,sqsingle,,0,-1),
                        INDEX(time,singlemax)))))),
     1))

它将单个交易的范围分离出来，寻找最大值在该范围内的位置（从下到上），并返回（日期&）时间。

在第2行中使用不带REDUCE（）的替代解决方案，需要向下拖动：

=LET(range,$A$2:$C$21,
     time,INDEX(range,,1),
     trade,INDEX(range,,2),
     value,INDEX(range,,3),
     start,XLOOKUP(0,B$2:B2,SEQUENCE(COUNTA(B$2:B2))+1,,0,-1),
     end,XLOOKUP(1,(trade=0)*(ROW(trade)>=ROW()),SEQUENCE(COUNTA(trade))-1),
     sqsingle,SEQUENCE(1+end-start,,start),
     singlevalue,INDEX(value,sqsingle),
     singlemax,XLOOKUP(MAX(singlevalue),singlevalue,sqsingle,,0,-1),
     IF(B2=0,"",INDEX(time,singlemax)))

对于MIN值：

=LET(range,$A$2:$D$21,
     time,INDEX(range,,1),
     trade,INDEX(range,,2),
     value,INDEX(range,,4),
     start,XLOOKUP(0,B$2:B2,SEQUENCE(COUNTA(B$2:B2))+1,,0,-1),
     end,XLOOKUP(1,(trade=0)*(ROW(trade)>=ROW()),SEQUENCE(COUNTA(trade))-1),
     sqsingle,SEQUENCE(1+end-start,,start),
     singlevalue,INDEX(value,sqsingle),
     singlemax,XLOOKUP(MIN(singlevalue),singlevalue,sqsingle,,0,-1),
     IF(B2=0,"",INDEX(time,singlemax)))

Excel公式-查找由0值包围的列中的动态范围

1条答案

相关问题

热门标签

最新问答