添加新行时公式会发生变化。如何阻止这种情况发生？
我有以下公式
=COUNTIF('New Workplan'!$J$3:J133,"My Value")
问题是，当新行添加到工作表“新工作计划”第 3 行到第 133 行之间时，公式会发生变化。
例如，如果添加 10 行，Excel 将公式更改为“
=COUNTIF('New Workplan'!$J$13:J133,"My Value")
当发生这种情况时，前 10 行（从 3 到 13）不再被计算在内。
我需要的是始终保持 =COUNTIF('New Workplan'!$J$3:J133,"My Value") 的公式。
任何帮助都会很棒。
提前致谢。
更新：此工作簿被几个人使用，我不知道他们做了什么修改。
谢谢。
I have the following formula
=COUNTIF('New Workplan'!$J$3:J133,"My Value")
The problem is that when new rows are added to the worksheet 'New Workplan' between the row 3 to 133, then the formula changes.
For example if 10 rows are added excel changes the formula to
=COUNTIF('New Workplan'!$J$13:J133,"My Value")
When this happen, then the first 10 rows (from 3 to 13) are not counted any more.
What I need is the formula to stay =COUNTIF('New Workplan'!$J$3:J133,"My Value") at all the times.
Any help would be great.
Thanks in advance.
UPDATE: This workbook is used by several people, and I dont know what they do to get it modified.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用 INDIRECT 删除公式范围更新，即
=COUNTIF(INDIRECT("'New Workplan'!$J$3:J133"),"My Value")
Pearson 在这里为 INDIRECT 提供了有用的参考，http://www.cpearson.com/excel/indirect.htm
Use INDIRECT to remove the formula range update, ie
=COUNTIF(INDIRECT("'New Workplan'!$J$3:J133"),"My Value")
Pearson has a useful reference for INDIRECT here, http://www.cpearson.com/excel/indirect.htm