Excel 2007 - 公式更改为 #REF

发布于 2024-11-19 04:07:05 字数 657 浏览 3 评论 0原文

所以我得到了这本包含大量数据的工作簿。我有一张表，它基本上根据某些条件复制数据。

每行中的每个单元格看起来像这样（最后一个指定的单元格是公式所在的单元格）：

=IF(Numbers1!E2<>0;Numbers1!A2;"")
=IF(Numbers1!E3<>0;Numbers1!A3;"")
=IF(Numbers1!E4<>0;Numbers1!A4;"")
=IF(Numbers1!E2<>0;Numbers1!B2;"")
=IF(Numbers1!E3<>0;Numbers1!B3;"")
=IF(Numbers1!E4<>0;Numbers1!B4;"")

因此单元格 A2 中的公式是第一个，A3 中的公式是第二行等。

我想从Numbers1 工作表中的同一列和行，如果 E 列同一行中的值不为 0。这似乎工作得很好。

但是，当我更新 Numbers1 工作表中的数据时，公式突然无效，公式现在如下所示：

=IF(Numbers1!#REF!<>0;Numbers1!#REF!;"")

每个单元格中的每个公式看起来与上面的公式相同。我不能这样做，为什么 Excel 不能在不“帮助”我的情况下保持公式不变呢？

原文

So I've got this Workbook which contains a lot of data. And I've got this one sheet which basically copies the data based on certain conditions.

Each cell in each row looks like this (the last specified cell is the one where the formula is in):

=IF(Numbers1!E2<>0;Numbers1!A2;"")
=IF(Numbers1!E3<>0;Numbers1!A3;"")
=IF(Numbers1!E4<>0;Numbers1!A4;"")
=IF(Numbers1!E2<>0;Numbers1!B2;"")
=IF(Numbers1!E3<>0;Numbers1!B3;"")
=IF(Numbers1!E4<>0;Numbers1!B4;"")

So the formula in cell A2 is the first one, formula in A3 is the second line etc.

I want to copy the value from the same column and row from the sheet Numbers1, IF the value in the same row of column E is not 0. This seems to be working just fine.

But, when I update the data in Numbers1 sheet, the formulas are all of a sudden invalid and the formula now looks like this:

=IF(Numbers1!#REF!<>0;Numbers1!#REF!;"")

Each formula in each cells look identical to the formula above. And I can't have that, why can't Excel just keep the formula as it is without "helping" me?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

∞琼窗梦回ˉ 2024-11-26 04:07:05

由于您可能最好使用宏来重写公式，因此以下是基础知识：

Sub RewriteFormulas()

    Dim row, col As Integer
    row = 1               'row you want your target formulas to be on

    For row = 1 To 60
        For col = 1 To 13

            ActiveSheet.Cells(row, col).Formula = "=IF(Numbers1!" & Cells(row,col).Address & "<>0,Numbers1!" & Cells(row+2,col).Adddress & ","""")"

        Next row
    Next col

End Sub

您可以尝试使用不同的工作表（或不同的工作簿），而不仅仅是 ActiveSheet，这样您就可以拥有 1 个工作簿存储宏并更改提供更新数据集的任何工作簿中的数据。

希望有帮助...

Since you may be better off using a macro to rewrite your formulas, here are the basics:

Sub RewriteFormulas()

    Dim row, col As Integer
    row = 1               'row you want your target formulas to be on

    For row = 1 To 60
        For col = 1 To 13

            ActiveSheet.Cells(row, col).Formula = "=IF(Numbers1!" & Cells(row,col).Address & "<>0,Numbers1!" & Cells(row+2,col).Adddress & ","""")"

        Next row
    Next col

End Sub

You can play around with using different sheets (or different workbooks) instead of just ActiveSheet so you can have 1 workbook that stores the macro and alters data in whatever workbooks provide your updated datasets.

Hope that helps...

回复收藏 0 原文

~没有更多了~