エクセルで特定のセルより右側だけのセルの合計を算出したいとき

あまりない状況かもしれません。そしてこんなのがほしいって思ったときには、なんて検索したら良いのかもわからないかもしれません。でも、解説しようと思います。

実際は、ステップアップまでの生徒さんの点数を合計したいって用途がありまして、ステップアップした日に合計をリセットしたかったんです。

で、”R”という文字があわられた以降の合計、あるいは”R”がなければ全部のデータを合計するっていうシートを関数で作りました。

下の図は果物の月の合計を算出していますが、途中で”R”が発生したらリセットし、それ以降の数だけを合計しています。具体的には赤枠の中の数を合計して、「合計」の列に表示するシートになってます。

この記事では、こんなシートの作り方を説明したいと思います。

“R”があったらそれより右のセルを合計、”R”がなければ全てのセルを合計するシートの作成方法

仕組み

各行ごとに、Rが出てきた位置を算出します。図ではAHに記録するようにしてます。

で、合計は、Rの位置からAFまでのセル範囲で算出するようにしてます。

使う関数

• MATCH(検査値,検査範囲,照合の種類)
  検査範囲の中で検査値が出てきたセルの番号を返答する関数です。
• IFERROR(実行関数, エラーのときの戻り値)
  実行関数の結果が#N/A、#REF!、#DIV/0! などエラーになったとき、エラーのときの戻り値を返答する関数です。エラーにならない場合は実行関数の戻り値をそのまま返答します。
• OFFSET(基準セル範囲,OFFSET行数,OFFSET列数,[高さ],[幅])
  基準セル範囲の位置をずらします。また、オプションで基準セル範囲の行数(＝高さ)、列数(＝幅)を変えることができます。
• IF(条件文,TRUEのときの値, FALSEのときの値)
  条件文を評価して、その真偽に応じて、2つのうちのどちらかの値を返答します。

使う変数の解説

MATCH

MATCH関数は検査値が検査範囲の何番目にあるかを返答します。

照合の種類は、「1:以下, 0:完全一致, -1:以上」で、デフォルトは1です。
以下：検査値を超えない最大の数、
以上：検査値より大きい最小の数
となってますが、「以下、以上」を使うときは、検査範囲のセルが並べ変えておく必要があります。

今回は完全一致を使います。

以下のように、「R」という文字を探した場合、検査範囲の中で４番目にあるので、MATCH関数は4を返答します。

見つからない場合は、#N/Aエラーを発生します。

IFERROR

MATCH関数が”R”がないときエラーを返してしまうと、他の演算で結果を使えなくなってしまうため、エラーのときは、オフセットしないということで「0」を返答するために使用します。

=IFERROR(MATCH(xx,xx,xx), 0)
の様に使用すると、MATCH関数が#N/Aのとき 0 を返答します。

OFFSET

基準セル範囲の位置をずらします。また、オプションで基準セル範囲の行数(＝高さ)、列数(＝幅)を変えることができます。

・位置をずらすだけの場合

高さと幅を省略した場合は、位置をずらすだけ行えます

・高さと幅も変更する場合

今回、合計する範囲を狭くするため、幅も変更します。

IF

合計する範囲がなくなったときエラーとなってしまうのを避けるためにIF文を使います。

というようなIF文を実行すると、セルAH2の値が31だったら 0 を返答し、それ以外の数だったら5を返答します。

計算シートの作成

計算元データ、リセット位置のデータ入力

まず、BからAFまでの列は、実際に計算したい数データと、リセットしたい位置に”R”の文字を記入していきます。

Reset位置(=R)の算出

図ではAH列にReset位置と言う名称の列を追加してます。

この中の計算式は次のようになってます(4行目の場合)。

これはB4:AF4までの間に「R」があれば、それが現れたインデックス番号がB4を1として算出されます。「R」が見つからない場合はMATCH関数の戻り値は#N/Aとなり、IFERROR関数が0を返答することになります。

以下の様に各行に対してReset位置が正しく算出されています。

合計の算出

Reset位置の分だけ合計算出する左側セルを右にオフセットさせ、その分、セル範囲を短くして31日まで合計するようにしています。

数式は以下のとおりです(4行目の場合)

AH列がReset位置になってます。

AH列が31のとき、最終日にリセットしているため、合計を算出するセル範囲がなくなってしまいますので、IF文で 0 を返答しています。

それ以外のときは、SUM(セル範囲)で合計を算出していますが、
セル範囲に「OFFSET(B4,0,AH4,1,31-AH4)」と指定しています。

これは1日の位置(=B4)を基準に列方向にAH4(=Rの位置)分だけ開始位置をずらし、その分幅を31-AH4として合計する日数を減らしています。

結果の確認

改めて結果を貼り付けます。

Reset位置に応じて、赤枠の範囲が合計されており、「合計」枠のように正しく合計されています。

5行目以降は、上に挙げた4行目の「Reset位置」と「合計」の数式をコピーすれば、行番号だけ自動でずらしてコピーしてくれます。

まとめ

特定の文字より右側のセルだけ合計するエクセルシートを作成する手順を説明しました。

関数を組み合わせて、複雑な計算をするエクセルシートになってしまいましたが、一つづつの関数の使い方を理解すれば、組み合わせた数式も理解できると思います。

ぜひ、応用してエクセルの業務を効率化してみてくださいね。