☰

Excell，比较两列选项，并显示第三列的结果

tag-icon • tag-icon microsoft-excel
Excell，比较两列选项，并显示第三列的结果

我的问题是：我有一张有 3 列和大约 15000 行数据的表格。我想找到一个公式或一组公式来比较前两列，如果找到偏差为 3% 的匹配项，则显示第 3 列的值。所以基本上，如果，假设它找到不大于或小于另一对（假设为“A3000 和 B3000”）的 3% 的“A52 和 B52”，则显示两个值“C52”和 C3000。由于这 15000 行中的值有很多数字，所以我被迫使用那些 (+-)3%。我希望这能解决我的问题。

答案1

我理解您要做的事情是查看三个输入数据列（时间、温度、日照），并与第四个数据列（产量）进行比较。您想控制温度和日照，并确定产量是否随时间变化。

这是一项相当复杂的统计工作，特别是因为光伏系统输出的方差随时间的变化非常缓慢——20 年内产量损失约为 20%，而由于其他变量导致的方差要大得多，而且往往会掩盖您正在寻找的效果。

我想您意识到这是一项艰巨的任务，因为您曾询问如何通过查看不同日期相同的温度和日照对来简化分析。然而，这将导致数百个不同的数据对，每个数据对的重复次数都相当少，因此寻找与时间的相关性的个体能力很低。

我建议分析整个数据集，而不是将数据分解成几百个较小的分析，但尝试隔离时间变量以查看它是否对生产产生影响。

正如我所提到的，要正确做到这一点需要复杂的统计数据，超出了我日常应用的经验。但这里有一个你可以尝试的解决方案，它仍然可以告诉你你需要什么，而不需要统计严谨：

假设以下示例数据：

Time       Temp      Rad    Production
hours        *C     W/m2    W
      1      18      20    3194
      2      20      30    3984
      3      20      40    3976
      4      16      20    3174
      5      14       0       0
      6      10       0       0
      7       8       0       0
      8      10       0       0
      9      14      10    1964
     10      16      20    3136
     11      17      30    3888
     12      18      40    3856
     13      15      30    3824
     14      13      20    3034
     15       5       0       0
     16       8       0       0
     17      12       8    1478
     18      25      15    2263
     19      30      25    2942
     20      30      35    3240
     21      25      20    2712
     22      20      10    1768
     23      22       0       0
     24      18       0       0
     25      22       0       0
     26      25      10    1619
     27      26      20    2539
     28      18      24    2943
     29      12      26    3047
     30      10      18    2427

我们可以建立一个模型，根据其他 3 个变量来预测产量。一旦我们找到最佳拟合，我们就可以检查时间是否真的是一个重要变量，以及它应用了多少减少率。

在这个例子中，我假设以下等式将给出产量：

 Production = A*(B*Temp^b)*(C*Rad^c)*(1+D*Time^d)

该模型假设由于温度和日照引起的变化具有幂关系，并且时间可以通过 D 作为一个小的负数对结果产生负斜率。

将一些单元格标记为 A、B、b、C、c、D、d。然后在生产数据旁边创建一个新列，以根据此模型计算新的生产数据。输入方程式，引用记录的数据和命名的单元格（视情况而定）。使用 $ 符号固定对命名单元格的引用，然后向下拖动/填充。

目前，由于参数为零，模型将产生错误。因此将方程式包装在 iferror(__,0) 语句中。

在右侧再添加一列，命名为 Error，公式为 (Production-Model)^2，然后向下填充。这是衡量我们模型偏离程度的指标。在某处对该列的值求和 - 它会得出一个大数字。理想情况下，这个大数字稍后会变小，表明我们的方程有效并预测了现实。

使用规划求解来改变所有变量，最小化误差总和的单元格的值。

此时，如果您绘制随时间变化的产量图表，以及随时间变化的模型产量图表，则两者应该能够很好地匹配。 在此处输入图片描述

在求解器找到的参数值中，查看与时间相关的参数值（D 和 d）。如果您绘制模型的时间部分（y=1+D*Time^d）与时间的关系图，您将看到求解器认为时间对您的生产产生的百分比影响。

答案2

假设我对您的问题规范正确 - 即您希望结果（即温度在辐射的±3％以内时的生产值）显示在 D 列中。

在 D 列中使用此公式并将其复制到最后一行。

=IF(AND(B2>A2*97/100, B2<A2*103/100), C2, "")

相关内容