☰

如何在电子表格中正确对齐平均值和标准差输出？

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet libreoffice-calc
如何在电子表格中正确对齐平均值和标准差输出？

我现在不确定如何最好地表述这个问题，所以我将使用随机数作为示例。我从分配给 ID 的值开始，这样每个 ID 可以是 n=1、n=2，... 等等

ID  Value   
1   1235        
1   326     
1   567     
2   768     
2   646     
3   4367        
3   346     
3   35      
4   436     
5   3467        
5   46      
6   3467        
6   3532        
6   457     
7   3463        
7   3463
7   9328
7   2498

ETC

我想在 Excel/Calc 中计算平均值和 SD，以便值正确对齐（理想情况下会有合并单元格），给定一个、两个、三个……等输入单元格，一个输出单元格。

示例截图：

在此处输入图片描述

我想要获得什么。AVG 和 SD 表示给定（随机）数据的正确值；因此 AVG 和 SD 正确对齐]1

也就是说，我想要一种自动计算平均值和 SD 的方法，同时考虑不同的 n，以便正确对齐/格式化。

肯定有一个简单的方法可以做到这一点，但现在我不知道。-_-

我将非常感激任何建议。

答案1

如果您假设 ColumnA已排序，那么这并不难，因此我们处理的是 Column 中的连续范围B （这是您问题的视觉方面所暗示的）。使用以下公式将 Column 设置E为辅助列：

  • E2→=IF(A2=A3, E3, ROW())

对于每一行，这将标识当前行所在范围的最后一行。然后你可以使用

  • B2→=IF(A1=A2, "", AVERAGE(B2:INDIRECT("B" & E2)))
  • C2→=IF(A1=A2, "", STDEV(B2:INDIRECT("B" & E2)))

（或者使用任何您想要的方法来计算标准偏差。）这将检查它是否是范围的第一行。如果是，它将使用该INDIRECT()函数在当前单元格和最后一个具有相同 ID 值的单元格之间构建一个范围。

       

当然，您可以隐藏列E，或使用一些看不见的列（例如Z）作为辅助列。请注意，此解决方案不使用数组公式。

答案2

这不完全是你想要的，但我会使用数据透视表：

包含平均值和标准差的数据透视表

（我将第一列标题更改为 ID，并将第二列和第三列格式化为仅显示两位小数，否则它只是数据透视表生成器中显示的内容。）

您可以通过以下方式获得您所要求的内容：

=IF($A2=$A1,"",AVERAGEIF($A2:$A19,$A2,$B2:$B19))

在 C2 中，以及：

=IF($A2=$A1,"",STDEV.P(INDIRECT("R"&MATCH($A2,$A:$A,0)&"C2:R"&MATCH($A2,$A:$A,1)&"C2",0)))

在 D2 中并填充两列。每个公式中的外部 IF 是将值仅放在包含特定 ID 的第一行中。C2 公式的其余部分应该很简单，AVERAGEIF 计算特定条件为真的数字的平均值。在本例中，它查找第一列，选择与第一列中当前行的值相同的数字，然后计算第二列中相应数字的平均值。

不幸的是，没有“STDEVIF”（至少在 Mac 上的 Excel 2011 中没有，也许在您使用的任何电子表格程序中都有。如果是这样，只需在 C2 公式中用它代替 AVERAGE），所以您必须要有技巧 :-)。方法是找到您想要标准差的单元格范围，构建对这些单元格的引用，然后将该引用传递给 STDEV.P。通过查找第一的查找第 1 列中与第 1 列当前行的值相同的行，然后查找最后的第 1 列中的行具有与第 1 列中当前行的值相同的值。这两个值描绘了要使用的第 1 列子范围的顶部和底部，因此在字符串中构造一个 R1C1 样式引用，使用 INDIRECT 将其转换为实际引用，然后将其传递给 STDEV.P。很简单！:-) 好吧，这有点丑陋，但它有效。

答案3

Excel 没有内置这样的功能。您需要使用小计或数据透视表，但它们并不能实现您想要的功能。

在此处输入图片描述

要使用公式构建表格，请使用以下两个函数。

在C2中

=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")

在 D2 中输入并按ctrl+shift+enter

=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")

然后把这些公式抄下来

开头的 IF(A2<>A1... 基本上表示仅当此行和上一行的 A 列不同时才显示内容。

Averageif 的工作原理正如您所想象的那样。

D 列是一个数组公式，因此它首先进入并对范围内的每个单元格执行 if 语句，然后它会为每个单元格返回一个类似 (1,14,13,3,FALSE,FALSE...) 的数组，然后计算标准差，这基本上应该忽略 FALSE 值。

此方法假设数据按 ID 排序。如果没有排序，则平均值和标准差计算是正确的，但它们会在每次 ID 更改时显示，而不仅仅是第一次更改。

相关内容