堅牢な統計データ分析のための 5 つの実績のある Excel テクニック

方法 1 – 中心的な傾向と変動性を計算する

7 年生の上記のデータセットの得点は数学に基づいて与えられています。 、歴史 、科学 、 と文学

1.1 AVERAGE 関数の使用

平均点 が表示されます。 イーサンの場合 セルC20内 .

ここではAVERAGE 関数を使用しました。 、データセットの算術平均を返します。

• セル C20 に次の数式を入力します。

ここで、セル範囲は C9:F9 です。 はイーサンのさまざまな主題のマークを指します。 。

1.2 AVERAGEIF 関数と AVERAGEIFS 関数の使用

上記のデータセットは、AVERAGEIF のさまざまな使用法を示しています。 と 平均値 機能。

6 年生の生徒が取得した得点の平均を求めたいとします。これを行うには、

• セルB22に次の数式を入力します。

=AVERAGEIF(B5:B16,B19,D5:D16)

ここでは、セルB19です。 は平均点を求める基準を示します。 。

平均点を見つけたい別の状況を想定してみましょう。 2 つの基準に基づいて生徒の数を決定します:彼ら  グレード とセクション .

• セルC24に次の数式を入力します。 :

=AVERAGEIFS($D$5:$D$16,$B$5:$B$16,B19,$C$5:$C$16,C19)

ここで、セル範囲 $D$5:$D$16 マーク のセルを示します。 列、セル範囲 $B$5:$B$16 グレード のセルを指します。 列とセル範囲 $C$5:$C$16 セクション のセルを示します。 コラム。細胞 B19 と C19 2 つの基準を表します。 。

1.3 HARMEAN 関数と GEOMEAN 関数の利用

データとして 6 つの数値があるとします。数値は 1、2、3、4、5、6 です。調和平均値は次のようになります。

Harmonic Mean = 11+1/2+1/3+1/4+1/5+1/66 = 2.4489

ジオメアン 関数は幾何平均を計算します。 選択したデータセットの。幾何平均は、n 乗根 を求めることで計算されます。 n を掛けた後 データセットの値。ここでは、 n データセット内の値の総数です。たとえば、5 あるとします。 数値をデータセットとして使用します。これらは 1、2、3、4、5 です。 つまり幾何平均となります。

Geometric Mean = 51*2*3*4*5 = 2.6051.

上の画像は、 HARMEAN の両方を使用する実際の例を示しています。 とジオミアン 機能。

• B21 の調和平均を計算する式 セルは:

ここでは、セル範囲 C5:C17 です。 平均点 のセルを表します。 コラム。

明らかなことが 1 つあります。この場合、調和平均の値は、平均マークの平均値よりも低いということです。算術平均は82.58です。 ですが、 調和平均は 82.37 です。 。つまり、平均点の大きな値の価値が制限されるということです。 .

• 幾何平均を求める場合、セルC22で次の数式を使用しました。

調和平均と同様に、幾何平均 (82.47) ) は算術平均 (82.58) とは異なります。 。投資家は幾何平均を使用します。これは、行の値が複数の期間にわたって与えられるたびに、より正確な平均値が得られるためです。

1.4 STANDARDIZE 関数の適用

手順 :

• C平均と標準偏差を計算する データセットの。

• セルD5に次の数式を入力します。

=STANDARDIZE(C5,$C$20,$C$21)

ここで、セル C5 は平均点を表します。 マシューの 、セル $C$20 は平均値を示します。 、セル $C$21 STDEV.P (標準偏差) を指します。 値。

MODE.SNGL を使用できます。 、中央値 、VAR.S 、 VAR.P 、STDEV.S 、 およびSTDEV.P Excel 内のデータをさらに統計的に分析する関数。

方法 2 – 相対的な順位を計算する

両方とも5 番目としましょう。 そして6 位です。 -ランクの値は同じです。その場合はRANK.EQ 関数はランク 5 を返します 両方の値について、次のランク値はランク 7 になります。 。ここに合計マークがあります。 7 位 学年 データセットとして。

ここで 10 番目 であることがわかります。 そして11 番目 価値観は結びついていた。つまり、RANK.EQ 関数はランク 10 を返しました 両方の値について。

• セルD5に次の数式を適用しました。 .

=RANK.EQ(C5,$C$5:$C$17,0)

ここで、セル C5 合計マークの最初のセルを指します。 列とセル範囲 $C$5:$C$17 は合計マークのセルを表します。 コラム。

こちらは第10 回です。 と 11 番目の値は同数だったので、RANK.AVG 関数は平均 10.5 を返しました 両方の値について。

RANK.AVG この関数は、データセットの相対的なランクも返します。ただし、 同点の場合は平均順位が返されます。 同点の値の場合。たとえば、4 番目としましょう。 と 5 位の値は同点です。つまり、RANK.AVG 関数はランク 4.5 を返します。 両方の値について。次の値のランクは6になります。 。次に、以下の手順に従って RANK.AVG を利用してみましょう。 データを統計的に分析するための Excel の関数。

• セル E5: では次の数式を使用しました。

=RANK.AVG(C5,$C$5:$C$17,0)

さらに、PERCENTRANK.INC を使用することもできます。 、パーセントランク.EXC 、パーセンタイル株式会社 、パーセンタイル.EXC 、QUARTILE.INC および QUARTILE.EXC 関数 Excel 内のデータの相対的な位置を計算します。

方法 3 – 相関関係と回帰の決定

3.1 SLOPE、INTERCEPT、および STYEX 関数の使用

STYEX 機能 Y 値 の標準誤差が得られます。 指定された X 値について 。これを使用してY 値を予測できます。 X 値から .

手順 :

• セルC21に以下の数式を入力します。

ここで、セル範囲は C5:C17 です。 Y 値のセルを示します。 列とセル範囲 B5:B17 X 値のセルを指します。 コラム。

• ENTER を押します。 .

標準エラーが発生します。 Y 値の 指定された X 値について セルC21内。

3.2 CORREL 関数の適用

CORREL 関数 2 つのデータセットがどの程度密接に関連しているかを調べるのに役立ちます。

• C19 では次の式を使用しました。 セル:

ここでは、C5:C17 の範囲です。 は合計マークのセルを示します。 列、範囲は D5:D17 平均点のセルを指します。 コラム。

4.配列関数を統計分析に適用する

ここでは、 各ビン範囲に対する周波数が示されています。 次の図に示すように。 FREQUENCY 関数を使用しました。 は、Excel のデータを統計的に分析するために最もよく使用される配列関数の 1 つです。

• セルC17に次の数式を入力します。 :

=FREQUENCY(C5:C14,B17:B23)

ここで、範囲は B17:B23 です。 最初の 7 を表します。 ビン範囲のセル コラム。

MODE.MULT 関数を使用できます。 、LINEST 関数 、トレンド機能 、 そして成長機能です。 Excel でデータを統計的に分析します。

注: 古いバージョンの Excel を使用している場合は、CTRL + Shift + Enter を押す必要がある場合があります。 配列数式を使用します。 Excel 365 を使用しているため、ENTER を押すだけです。 我々のためにしてくれるでしょう。

方法 5 – データ分析ツールを利用して移動平均を計算する

上の画像は移動平均を表しています。 データセットの。 データ分析ツールパック このオプションは Excel のリボンにありません デフォルトでは。この機能は手動で有効にする必要があります。 この記事に従ってデータ分析ツールをアクティブ化し、そのさまざまな使用方法についても学ぶことができます .

• データ に移動します。 リボンのタブ>> データ分析 を選択します。 分析 のオプション グループ。

データ分析 上の画像に示すように、ダイアログ ボックスがワークシートに表示されます。

• 入力範囲に移動します。 フィールドで平均点のセルを選択します。 列>> 出力範囲 をクリックします。 フィールドに移動し、セルG5を選択します。>> [OK] をクリックします。 .

Excel の一般的なデータ分析ツール

• 分散分析:単一因子 → 2 つ以上の観測値の分散を分析します。
• 分散分析:レプリケーションを使用した 2 要素 → 変数のレベルの組み合わせごとに、2 つの独立変数とさまざまな観測値を含む分散分析が作成されます。
• 分散分析:レプリケーションを使用しない 2 要素 → 変数のレベルの組み合わせごとに、2 つの独立変数と 1 つの観測値を含む分散分析が作成されます。
• 相関関係 → 人のサンプルに 3 つ以上の測定値がある場合、考えられる測定値のペアごとに相関係数の行列が計算されます。
• 共分散 → 人のサンプルに 3 つ以上の測定値がある場合、考えられる測定値のペアごとに共分散係数の行列が計算されます。
• 記述統計 → 定義されたセル範囲内の値の中心的な傾向、変動性、その他のプロパティを要約したレポートを生成します。
• 指数平滑化 → 前の値のシーケンスと以前の予測を使用して、シーケンスの次の値を予測します。
• 分散に対する 2 つのサンプルの F 検定 → F 検定を実行することで 2 つの分散を比較します。
• ヒストグラム → 選択したセル範囲内の値の頻度分布を表形式で作成します。
• 乱数の生成 → 7 つの潜在的な分布の 1 つに基づいて、特定の量の乱数を生成します。
• ランクとパーセンタイル → 一連の値の各値を序数および百分位のランクとともに表示するテーブルを作成します。
• 回帰 → これにより、1 つの従属変数と 1 つ以上の独立変数を含む一連のデータに適用された線形回帰統計のレポートが作成されます。
• サンプリング → 指定された範囲内のセルから値のサンプルが生成されます。

データ分析ツールパックには次の分析ツールが含まれています。 .

覚えておくべきこと

• Excel でデータ分析を実行する前に、データの種類（連続型かカテゴリ型など）を明確にしておく必要があります。
• 次に、t 検定、ANOVA、回帰、相関関係などの統計分析ツールの豊富なリストから選択する必要があります。
• 分析を実施したら、結果を有意義に解釈することが重要です。これは、数値が何を意味するのか、そしてそれらが研究課題にどのように関連しているのかを理解することを意味します。
• 最後に、エラーをチェックして結果を検証し、分析が堅牢であることを確認することが重要です。これには、外れ値のチェック、仮説のテスト、感度分析の実施が含まれます。

練習セクション

Excel ワークブック内 では練習セクションをご用意しました。

実践ワークブックの各ワークシートには、サンプルの実践セクションが用意されています。

練習用ワークブックをダウンロード

次のワークブックをダウンロードして練習してください。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!