☰

スプレッドシートで平均と標準偏差の出力を適切に揃えるにはどうすればよいでしょうか?

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

等

入力セルが 1、2、3 個など、出力セルが 1 個ある場合、Excel/Calc で平均値と標準偏差を計算して、値が適切に揃うように (理想的には結合されたセルがあるように) したいと思います。

スクリーンショットの例:

ここに画像の説明を入力してください

私が得たいもの。AVG と SD は、（ランダムな）与えられたデータの適切な値を意味します。したがって、AVG と SD は適切に整列しています。1

つまり、異なる n を考慮して平均と SD を自動的に計算し、適切に整列/フォーマットする方法が必要です。

簡単な方法があるはずですが、今のところわかりません。-_-

何かご提案があればいただければ幸いです。

答え1

列が並べ替えられていると仮定すればA、これは難しくありません。つまり、列内の連続した範囲を扱っていることになります(これは、質問の視覚的な側面から示唆されていることです)。次の数式を使用して、列をヘルパー列としてB 設定します。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 に変更し、2 番目と 3 番目の列を小数点以下 2 桁のみを表示するように書式設定しました。それ以外は、ピボットテーブル ビルダーに表示されるものと同じです。)

以下のものを配置することで、要求したものを取得できます。

=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 は特定の条件が満たされる数値の平均を計算します。この場合、最初の列を調べ、最初の列の現在の行の値と同じ値を持つ数値を選択し、2 番目の列の対応する数値の平均を計算します。

残念ながら、「STDEVIF」はありません（少なくともMacのExcel 2011では、使用しているスプレッドシートプログラムにはあるかもしれません。その場合は、C2の数式でAVERAGEの代わりに使用してください）。そのため、トリッキーにする必要があります:-)。アプローチは、標準偏差を求めるセルの範囲を見つけ、それらのセルへの参照を作成し、その参照をSTDEV.Pに渡すことです。範囲は、初め列1の現在の行の値と同じ値を持つ列1の行を検索し、最後列 1 の行の値が、列 1 の現在の行の値と同じ値である。この 2 つの値は、使用したい列 1 のサブ範囲の上限と下限を表すので、文字列で R1C1 スタイルの参照を作成し、INDIRECT を使用して実際の参照に変換してから、それを STDEV.P に渡します。簡単です! :-) まあ、ちょっと見苦しいですが、機能します。

答え3

Excel にはこのような機能が組み込まれていません。小計やピボット テーブルを使用する必要がありますが、これでは目的を達成できません。

ここに画像の説明を入力してください

数式を使用して表を作成するには、次の 2 つの関数を使用します。

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 だけでなく、ID が変更されるたびに表示されます。

関連情報