☰

为你的公式制作一个表格

tag-icon • tag-icon microsoft-excel worksheet-function
为你的公式制作一个表格

我有一个单变量函数 y=f(x)，它太复杂了，我自己无法积分。所以我打算把可能由数千个 x 给出的所有结果 (y) 相加。

Excel 中是否有任何函数可以在单个单元格中进行数值积分？

例如，我会采取

  • A1 作为 x
  • B1 作为y=f(x)
  • C1 作为计算器函数，计算（从 x=1 到 x=n）并总结所有的 y（从 f(1) 到 f(n)）。

我认为 AUTOSUM 和 SUMPRODUCT 不适合这个目的。

编辑（详细说明该问题）：

这是原始函数：
原有的功能。。

这里 Fn1n2 是 2 个变量 n1 和 n2 的函数。我必须保持 n1 不变，然后求解 n2 的 1000 个不同值并将它们相加。

然后我会逐一增加 n1（1000 倍），求解 n2 并反复求和。

这意味着我必须解决 1000x1000 次问题，手动写入可能要花一个小时。

以下是这种情况的一个例子：
例子

答案1

[编辑：我添加了 n1 和 n2 的图像，从 1 到 10，并对其进行了简化。感谢@Máté Juhász 的反馈。]

有几种方法可以解决此问题。似乎每次我回答时，我都会首先说“使用表格”。这个建议在这里也很好，因为您希望每个公式对于整个列都是统一的，并且它的好处是存储每个列的公式一次而不是每个单元一次。

所以，

为你的公式制作一个表格

  1. 将 A1 更改为“n1”
  2. 将 B1 更改为“n2=1”
  3. 将 C1 更改为“n2=2”
  4. 突出显示 B1..C1
  5. 将选定范围右下角的点拖到 ALM1 以到达“n2=1000”在此处输入图片描述
  6. 在“插入”功能区中，插入一个表格。在“创建表格”对话框中，将数据范围更改为=$A$1:$ALM$2，并确保选中“我的表格有标题”。
  7. 在 A2 中，添加公式ROW()-1在此处输入图片描述
  8. 将此工作表复制到工作簿中的新工作表中以供日后使用。
  9. 好的，现在开始计算。将公式添加到 b2 中，但不要使用 n1，(ROW()-1)而要使用 ，不要使用 n2 (COLUMN()-1)。我只是制作了一个乘法表，所以我的公式 (n1*n2) 是=(ROW()-1)*(COLUMN()-1)。
  10. 单击仅当光标位于表格内时才可用的“表格设计”功能区，然后在功能区中选择“总行”和“第一列”。 （顺便说一句，“第一列”仅用于美观目的。）
  11. 将 B3 更改为=SUM([n2=1])+IF(COLUMN()=2,0,A3)
  12. 编辑 B3，突出显示“A3”，然后单击单元格 A3。这应该会将 B3 的公式更改为类似=SUM([n2=1])+IF(COLUMN()=2,0,Table1[[#Totals],[n1]])
  13. 突出显示 B2..B3，然后将公式拖出或复制到 ALM 列，直到行的其余部分。现在，您应该有一个表格，其中包含一个总计行，该行给出了 n1=1 和 n2=1 的总和，最大为 1000在此处输入图片描述
  14. 要将 n1 更改为 1000，在“表格设计”功能区左端有一个“调整表格大小”按钮。单击它并将表格大小更改为=$A$1:$ALM$1002在此处输入图片描述

总和

如果这就是您所需要的，您现在可以停止。但是，如果您需要 n1 和 n2 中任何给定值的总和，直到 1000，那么我们可以采用以下两种方法之一：

总结选项 1：简单但缓慢

在这种情况下，我们有两个选项卡。上面的选项卡将包含一个表格，其中包含“未求和”公式值。然后我们将创建这个选项卡，当我们尝试对 n1=(1 到 4) 和 n2(1 到 4) 的值求和时，我们通过执行以下操作来获取所有值：

Table1[[#Headers],[n1]]给我们第一个表格的左上角单元格。

OFFSET(Table1[[#Headers],[n1]],1,1)给出一个向下的单元格和一个向右的单元格，即 n1=1 和 n2=1 的第一个数据单元格。

Offset 具有可选的第 4 和第 5 个参数，用于指定返回单元格范围的高度和宽度。使用这些可选参数，=OFFSET(Table1[[#Headers],[n1]],1,1,n1,n2)给出了我们想要总结的所有值！

因此，为了利用这一点，

  1. 转到我们在步骤 9 中创建的新工作表。
  2. 在第二张工作表的 B2 中，将公式更改为=SUM(OFFSET(Table1[[#Headers],[n1]],1,1,row()-1,column()-1)) 这样，当 n1 为 5 且 n2 为 3 时，您将获得从表 1 的 B2 单元格开始的矩形的总和，高 5 行，宽 3 列。
  3. 将单元格 B2 复制到剪贴板。
  4. 突出显示 C2..ALM2
  5. 粘贴
  6. 去喝杯咖啡吧。如果你真的想达到 n1=1000 和 n2=1000 的水平，那么之前的 Sum 方法将花费很长时间！使用该 SUM 方法的单元格表将有 1000*1000 个单元格，每个单元格都会查看 Sheet1!B2，其中 999^2 个单元格还将包括 Sheet1!C3，等等，因此 n1=1000 和 n2=1000 的 SUM() 公式将查看一百万个细胞尽管除了其中一个之外，其他所有数 据之前都已经求和过了！在此处输入图片描述（请注意，我将第二张表的表格样式更改为“表格样式中等 4”）

求和选项 2：先按行求和，再按列求和

相反，我们可以让 Excel 计算运行总和，从而提高效率。以下屏幕截图显示了此优化方法。请注意，我重命名了表格（和标签，但这并不重要）这就是为什么以下公式使用“公式”和“N1Sums”而不是“Table1”和“Table2”。

我们可以使用两个表来代替那个总和表。

第二个表格的 B 列公式是=Formula[@[n2=1]]+IF(COLUMN()=2,0,[@n1])，然后您必须将其拖到其他列，这样 C 列的公式就是=Formula[@[n2=2]]+IF(COLUMN()=2,0,[@[n2=1]])。这样每行都会有累计总和。实际上，这个公式的意思是“将第一个表格中对应的单元格加上我左边的单元格。”

第三个表格类似，只是它的公式是“在第二表格加单元格在我上面，比我强，在我之上”。B 列的公式为=N1Sums[@[n2=1]]+IF(ROW()=2,0,OFFSET([@[n2=1]],-1,0))，然后您必须将其拖到其他列，以便 C 列的公式为=N1Sums[@[n2=2]]+IF(ROW()=2,0,OFFSET([@[n2=2]],-1,0))

下面的截图展示了这种优化方法。

在此处输入图片描述 在此处输入图片描述 在此处输入图片描述

答案2

这是一个仅使用一个单元格、两个不同变量的解决方案，如您的示例所示：

在此处输入图片描述

  • A 列和 B 列包含 n1 和 n2 的值
    • 您需要给出两个范围，例如1:10
    • 您只能使用工作表行范围内的自然数（因此不能使用负数和零）
    • 即使您只需要一个数字，也可以将其作为范围输入（例如在 A2 中）
    • Excel 可能会尝试将您的输入转换为数字/日期，因此可以：
      • 在输入值之前将 A、B 列的数字格式更改为文本
      • '或在每个单元格开头输入( '1:100)
    • 您只能为其中一个变量指定一个范围（否则您会收到类似第 5 行的错误）
  • C 列中的示例函数为：
    • =SUM(COS(ROW(INDIRECT(A2))+ROW(INDIRECT(B2)))+SIN(ROW(INDIRECT(A2))+ROW(INDIRECT(B2))))
    • 也就是说：Sum(Cos(n1+n2)+Sin(n1+n2))
    • 这是一个数组公式，因此输入后需要按 CTRL+SHIFT+ENTER。

相关内容