在此处输入图像描述我想在第二列中获取最小值属于特定日期范围内的特定范围.这一切都在excel中.
enter image description hereI want to get the minimum value in the second column for a certain range that falls within a specific date range. This is all in excel.
对于实例: 列1:1/1/2019 1/10/2109 1/15/2019 2/15/2019 列2:50 55 60 70
For Instance: Column1: 1/1/2019 1/10/2109 1/15/2019 2/15/2019 Column2: 50 55 60 70
行1将是1/1/2019和50. 第1行,第1列为1/1/2019.
Row 1 would be 1/1/2019 and 50. Row 1, Column 1 would be 1/1/2019.
如果Column1中的某个日期在1/05/2019-2/30/2019之间,则在该列中指定该特定日期范围内的最小数字(1/5/2019-2/30/2019).
If some date in Column1 is between 1/05/2019 - 2/30/2019, then give the minimum number in Column2 for that specific date range (1/5/2019 - 2/30/2019).
我不知道宏是否有帮助,或者我是否可以在excel中键入公式.感谢您的帮助.
I don't know if a macro would help or if I could just type a formula in excel. Thanks for your help.
推荐答案例如,您可以使用这样的数组公式(答案链接在注释中此处(记入@ScottCraner):
For example you could use an array formula like so (with the answer linked in comments here in mind (credits to @ScottCraner):
单元格E4中的公式如下:
=MIN(IF((A1:A4>=E1)*(A1:A4<=E2),B1:B4))通过使用 Ctrl Shift Enter
放入聚合后,它仍然是一个数组公式,但您不必像这样确认它:
Being put into aggregat, it's still an array formula but you don't have to confirm it as such:
=AGGREGAAT(15,6,B1:B4/((A1:A4>=E1)*(A1:A4<=E2)),1)更多推荐
如何找到特定日期范围内的最小值和最大值
发布评论