英文:
Change just the date in Excel formula
问题
在单元格P3中，我有以下的Excel公式：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=1/31/2023", I3:I8198, ">1/1/2023")
然后是单元格P4：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=12/31/2022", I3:I8198, ">12/1/2022")
单元格P5：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=11/30/2022", I3:I8198, ">11/1/2022")
单元格P6：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=10/31/2022", I3:I8198, ">10/1/2022")
单元格P7：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=9/30/2022", I3:I8198, ">9/1/2022")
单元格P8：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=8/31/2022", I3:I8198, ">8/1/2022")
单元格P9：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=7/31/2022", I3:I8198, ">7/1/2022")
单元格P10：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=6/30/2022", I3:I8198, ">6/1/2022")
单元格P11：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=5/31/2022", I3:I8198, ">5/1/2022")
单元格P12：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=4/30/2022", I3:I8198, ">4/1/2022")
单元格P13：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=3/31/2022", I3:I8198, ">3/1/2022")
单元格P14：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=2/28/2022", I3:I8198, ">2/1/2022")
这一系列一直持续到单元格P273，其中是：
=AVERAGEIFS(H3:H8198, I3:I8198, "<=7/31/2000", I3:I8198, ">7/1/2000")
是否有一种简单的方法来仅更改日期，而不是手动更改273个单元格的日期？
我尝试选择两到三个单元格并将其拖动到列的底部，希望Excel能理解我只想更改年份，但这并不起作用。公式的其他部分也会被更改，如开始和结束范围。
英文:
I have the following excel formula in cell P3:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=1/31/2023", I3:I8198, ">1/1/2023")
Followed by
cell P4:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=12/31/2022", I3:I8198, ">12/1/2022")
cell P5:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=11/30/2022", I3:I8198, ">11/1/2022")
cell P6:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=10/31/2022", I3:I8198, ">10/1/2022")
cell P7:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=9/30/2022", I3:I8198, ">9/1/2022")
cell P8:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=8/31/2022", I3:I8198, ">8/1/2022")
cell P9:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=7/31/2022", I3:I8198, ">7/1/2022")
cell P10:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=6/30/2022", I3:I8198, ">6/1/2022")
cell P11:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=5/31/2022", I3:I8198, ">5/1/2022")
cell P12:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=4/30/2022", I3:I8198, ">4/1/2022")
cell P13:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=3/31/2022", I3:I8198, ">3/1/2022")
cell P14:
=AVERAGEIFS(H3:H8198, I3:I8198, "<=2/38/2022", I3:I8198, ">2/1/2022")
This continues all the way down to cell P273 which is
=AVERAGEIFS(H3:H8198, I3:I8198, "<=7/31/2000", I3:I8198, ">7/1/2000")
Is there a simple way to change just the date VS manually changing 273 cells to different dates?
I tried selecting two or three cells and drag to the bottom of my column, hoping excel would understand I wanted to change just the year, but that did not work. Other parts of the formula get changed also such as the start and stop range.
答案1
得分: 1
使用$或绝对引用来锁定引用：$H$3:$H$8198，然后当它被向下拖动时，它不会改变。
要更改年份，我们可以简单地通过数学和行号进行拼接：
"1/31/" & 2024-ROW($ZZ1)
现在当$ZZ1被向下拖动时，由于行引用不是绝对的，它会更改，每一行将从2024中减去一个。
=AVERAGEIFS($H$3:$H$8198, $I$3:$I$8198, "<=1/31/" & 2024-ROW($ZZ1), $I$3:$I$8198, ">1/1/" & 2024-ROW($ZZ1))
英文:
To lock the references we use $ or absolute referencing: $H$3:$H$8198 Then as it dragged down it will not change.
To change the year we can simply contatenate using some math and a row number:
"1/31/" & 2024-ROW($ZZ1)
Now as is is dragged down the $ZZ1, since the row reference is not absolute it will change and each row will subtract one more from the 2024
=AVERAGEIFS($H$3:$H$8198, $I$3:$I$8198, "<=1/31/" & 2024-ROW($ZZ1), $I$3:$I$8198, ">1/1/" & 2024-ROW($ZZ1))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论