Excel

【Excel】エクセルにて１つおきの平均を求める方法【一つ飛ばし：一定間隔・等間隔】

 
当サイトでは記事内に広告を含みます
技術ブログ特化メルマガはこちら

この記事では「エクセルにて1つごと（1つおき）に平均を求める方法」について解説していきます。

方法としては下記のようなものが一般的に利用できます。

・統合機能

・AVERAGEIF関数

・VBAマクロ

それでは、具体例を用いてエクセルでの一定間隔（一つ飛ばし）の平均値を求めるための方法をチェックします。

 

エクセルにて１つごと（一つ飛ばし）の平均を求める方法【関数使用せず】

非常に稀なケースではありますが、関数・マクロの使用に制限があるときは、「統合」の機能を利用するよりないです。

下記サンプルでは、各店舗(A~D店)の牛肉・豚肉の売上を書き出しています。

ここから、牛肉・豚肉の全店舗の平均売上を計算してみましょう。

セルB10及びB11を選択し、リボンより「データ」-「データツール」-「統合」をクリックします。

「統合の設定」ダイアログで「統合元範囲」に$B$2:$C$9を設定し、「追加」ボタンを押します。

また、「集計の方法」を平均に設定します。

デフォルトの設定は合計なので、自身で設定を変えないといけない旨、忘れないようにしましょう。

「統合元」に上記範囲が反映されたら、統合の基準を「左端列」に設定し、OKを押します。

問題なければ、牛肉・豚肉のそれぞれの平均売上が計算され、セルC10、C11に出力されます。

 

エクセルにて１つごと（一定間隔）の平均を求める方法【関数使用】

前例の方法は値が変化したら、その度に手動で操作を実行する必要があり、その分の煩わしさは否めません。

通常の1つ飛ばしの平均を求めるケースでは「関数を用いるのが望ましい」といえます。

そこで、前例と同じサンプルデータでAVERAGEIF関数による平均値算出を解説しています。

 

前例と同じサンプルデータにおいて、セルC10に「=AVERAGEIF($B$2:$C$9, B10, $C$2:$C$9)」と入力します。

この関数の意味を解説していきます。

AVERAGEIF関数は、指定した範囲のなかから条件を満たすセルの値の平均を求める関数であり、

=SUMIF(範囲,検索条件,平均対象範囲)

と入力します。

各引数には

・範囲($B$2:$B$9)：検索対象のセル範囲

・検索条件(B10)：「範囲」の中から該当するセルかどうかを判定する条件値

・平均対象範囲（$C$2:$C$9）：平均を求めたい数値が入力されているセル範囲

をそれぞれ指定します。

ENTERを押すと全店舗の牛肉の平均売上が計算されます。

同じくセルC11に「=AVERAGEIF($B$2:$C$9, B11, $C$2:$C$9)」と入力しENTERで完成です。

 

エクセルにて等間隔（1つ飛ばし）の平均を求める方法【マクロを使用する方法】

マクロを利用したて1つおき（等間隔）の平均を出す方法を紹介します。

 

まずVBE(Visual Basic Editor)を起動する必要があり、キーボードで「Alt+F11」を同時押しします。

下記画面が開けばOKです。

左側のツリーの「VBAProject…」を右クリックし、「挿入」→「標準モジュール」を実行します。

「標準モジュール」-「Module1」をクリックし、エディタ画面を開きます。

そこへ下記コードを記述します。


Sub Work()
    '全体の行数
    Dim i As Integer
    '牛肉用カウンタ
    Dim k1 As Integer
    '豚肉用カウンタ
    Dim k2 As Integer
    
    '一旦リセット
    Range("C10").ClearContents
    Range("C11").ClearContents
    
    For i = 0 To 7
        If Range("B2").Offset(i, 0) = "牛肉" Then
            '合計値と行数を記憶
            Range("C10").Value = Range("C10").Value + Range("C2").Offset(i, 0)
            k1 = k1 + 1
        End If
        If Range("B2").Offset(i, 0) = "豚肉" Then
            '合計値と行数を記憶
            Range("C11").Value = Range("C11").Value + Range("C2").Offset(i, 0)
            k2 = k2 + 1
        End If
    Next
    
    '合計値　/ 行数で平均値が求まる
    Range("C10").Value = Range("C10").Value / k1
    Range("C11").Value = Range("C11").Value / k2
End Sub

各コードでしていることは下記をご覧ください。

Work:

マクロ名。エクセル側でこの名前が認識される。

Dim:

内部変数の宣言。

本例ではループ用の「i」、牛肉及び豚肉の行数をカウントする「k1」「k2」が使用されている。

Range(***).ClearContens :

セル「***」の値を空にする。

For i=0 To N(処理) Next:

i=0からスタートし、(処理)に記載したことが繰り返し実行される。

1周毎に「i」がインクリメントされ、i=Nとなれば終了。

If *** = @@@ then (処理) end if

*** = @@@であれば(処理)が実行される。

Range(***).Offset(x,y):

セル「***」からみてx行y列のセルを指す。なおx及びyについてマイナス値でも動作するが、その際は「Range(“A1”).Offset(-1,-1)」のような事態にならないよう注意。

なお、この記事で使用しているサンプルデータの処理対象は8行ですが、この場合、上記Nは7が正しいといえます。

0,1,…,7で8周と考えればよいでしょう。

また、「’」はコメントとみなされ、実行には影響を与えません。

しかし、マクロは1回書いて終わりとは限らないので、その後のメンテ及び後任者への引継ぎに備えて確実に書いておくことをお勧めします。

 

コードが書けたら、VBEを閉じ、シートを表示します。

「Alt+F8」の同時押しで下記ダイアログが開きます。

「マクロ名」の中に、先ほど作成した「Work」がありますので、選択し「実行」をクリックします。

牛肉・豚肉それぞれの全店舗における平均値が出力されれば目的達成です。

 

まとめ　エクセルにて１つ飛ばしの平均を求める方法【一定間隔・等間隔】

 この記事では「エクセルにて1つ飛ばし（１つごと）の平均を求める方法」について解説しました。

今回用意したサンプルデータでは店舗数や対象の商品が少ないので「=(C2+C4+C6+C8)/4」といったような数式で計算したくなる気持ちもわかりますが、これが「牛肉・豚肉・鶏肉…」となった場合、あるいは店舗数が何十となった場合にはそのような考えは通用しません。

エクセルにはよりスマートに処理を行う機能がたくさん備わっています。

それらを利用しないのは非常にもったいないことだと認識すべきでしょう。

RELATED POST