☰

如何在電子表格中正確對齊平均值和標準差輸出？

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 值的最後一個單元格之間建立範圍。

       

當然，您可以隱藏 Column 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 列目前行中的值具有相同的值。將其轉換為實際參考，然後將其傳遞給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 列與上一行之間的 A 列不同時才顯示某些內容。

Averageif 的工作方式與您想像的完全一樣。

D 列是一個數組公式，因此首先它進入並在範圍內的每個單元格上執行if 語句，它會為每個單元格返回一個類似於(1,14,13,3,FALSE,FALSE.. .) 的數組，然後計算標準差，這基本上應該忽略 FALSE 值。

此方法假設資料按 ID 排序。如果未排序，平均值和標準偏差計算將是正確的，但每次 ID 更改時它們都會顯示，而不僅僅是第一次。

相關內容