エクセルＳＵＭの計算を効率的に行う

質問

いろいろ自分で考えてみたのですが、やはり思いつかなくて、こちらに質問させていただきました。
添付のような表があるとします。
Ａ，Ｂ，Ｃ、、、社とあり、それぞれに果物の名前が入っており、数が記載されています。
一番右の列にある数量は、Ａ，Ｂ，Ｃ社それぞれのくだものの数の合計です。

この一番右の列のようなＳＵＭの計算を自動的に行う関数を考えています。
いつも固定のセルのＳＵＭでは簡単ですが、今回の場合、Ａ社いくつ、Ｂ社いくつ、という果物の数は決まっておりません。つまり、合計すべきセルの数は変化する、ということです。

なので、考えたのは、一番左の列にＡ、Ｂ、Ｃ社と記載するので、その記載が出てくる前の行までを合計させるようにするしかないか、、など、案はあったのですが、実際にエクセル関数が思いつきません。

もしいい案がありましたら何卒お願いいたします。

回答No.6

kagakusuki · ベストアンサー

回答番号:ANo.2を投稿した時点では動作確認が出来ていなかったのですが、SUMPRODUCT関数の代わりに、MATCH関数を使用した方が、数式が短くなります（＝データが少なくて済む）し、処理速度も速くなります。

=IF(AND(ISNUMBER($C1),$C2=""),SUM(INDIRECT("C"&MATCH("゛",$A$1:$A1,-1)&":C"&ROW())),"")

　例えば、この数式をD1セルに入力してから、D1セルをコピーして、D4セルに貼り付けると、

MATCH("゛",$A$1:$A1,-1)

の部分は、

MATCH("゛",$A$1:$A4,-1)

という具合に変換されますが、これは、MATCH関数の照合の型に「-1」を指定して、
「゛」（濁点のみ）という、A列に存在しない文字列が、
A1～A4の範囲の何番目に存在するのかを検索させると、
検索範囲内で、文字列データが存在するセルの中で、最も下にあるセルの位置（A1～A4の範囲では3）が返される
という現象を利用しています。
　この現象は、「色々試している内に発見したもの」で、何故そうなるのかは、私にも解りません。
　又、検索する文字列は、検索範囲内のセルに存在しない（入力される筈がない）文字列であれば何でも構いません。

　尚、上記の数式を、行と列のどちらを削除した場合にも対応する様に改良すると、次の様になります。

=IF(AND(ISNUMBER(INDEX($C:$C,ROW())),INDEX($C:$C,ROW()+1)=""),SUM(INDIRECT("R"&MATCH("゛",OFFSET(INDIRECT("R1C"&COLUMN($A:$A),FALSE),,,ROW()),-1)&"C"&COLUMN($C:$C)&":R"&ROW()&"C"&COLUMN($C:$C),FALSE)),"")

お礼

R1C1形式ですね。やっとわかりました。それでもまだ全部は意味がわかっていいないのですが、、（すみません）

でも、大まかなところはわかりました。

このたびは大変丁寧に教えていただきありがとうございました。
また、お礼が遅くなり申し訳ございませんでした。

補足

まことにありがとうございます。
こちらの中に出てくる　”R"とは何でしょうか？

回答No.8

kagakusuki

>このCOUNTIF(OFFSET($A$1,,,4),"><")についてですが、
>私の理解では、A1から右にも左にも動かず４つだけ下に動く。
>なので、A5のセルのことを指すと思っていました。

　それは違います。
　OFFSET関数は、

OFFSET(基準,行数,列数[,高さ][,幅])

という形式です。
　ですから、「A1から右にも左にも動かず４つだけ下に動く」数式は

OFFSET($A$1,,4)

という具合に、OFFSET関数内の2つ目の「,」の後ろに、「4」が来る数式になります。

OFFSET($A$1,,,4)

は、2つ目ではなく、3つ目の「,」の後ろの所（セル範囲の高さを指定する箇所）に、「4」が来ていて、行方向の移動量を指定する1つ目の「,」の後ろの所と、列方向の移動量を指定する2つ目の「,」の後ろの所には、何も入力されていませんから、
この数式が表しているのは、A1セルに対して、行方向にも列方向にも移動しないセル、即ちA1セルを基準（セル範囲の中で最も左上にあるセル）として、下方向に4マスの高さ（縦幅）があるセル範囲になります。
　尚、セル範囲の幅を指定する4つ目の「,」とその後ろの数値が省略されていますから、セル範囲の横幅は1マスのみになります。

お礼

そうでしたか。OFFSETは基準値からの’範囲’だったのですね。

基準値から動いたその位置（セルひとつ）と勘違いしておりました。
（もちろん使い方によってはそうなるのでしょうが）

ありがとうございます。このことをふまえて、教えていただいた関数を今一度
見返してみたいと思います。

丁寧にありがとうございます。

回答No.7

kagakusuki

>(1=2)とあるのですが、（このあとも1＝2）（2＝2）
と続きますが、この前者の数字（＝より前の数字）がどうやって出てきたのかがわかりません。

　4番目の

COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")

の箇所を例として説明させて頂きます。
　ROW関数は括弧内で指定しているセルの行番号を返す関数です。
　ですから、、ROW($A4)の括弧内にはA4セルが指定されていますから、ROW($A4)の表す値は4になります。
　因みに、

ROW()

という関数は、その数式が入力されている行の行番号を返します。

　従って、

COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")

=COUNTIF(OFFSET($A$1,,,4),"><")

という事になります。
　OFFSET関数は、

OFFSET(基準,行数,列数[,高さ][,幅])

という形式ですから、

OFFSET($A$1,,,4)

は、A1セルを先頭（1番左上のセル）とする、縦方向が4行に渡るセル範囲を示す関数です。（幅が指定されていないため、横幅は1列のみになります）
　従って、

OFFSET($A$1,,,ROW($A4))=$A$1:$A4

COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")

=COUNTIF(OFFSET($A$1,,,4),"><")

=COUNTIF($A$1:$A4,"><")

という事になります。
　$A$1:$A4の範囲で空欄ではないセルは、A1とA3の2つだけですから、

COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")

の値は2になります。

　それから、申し訳御座いませんが、ANo.5で記した数式は、Excel2007よりも前のバージョンのExcelでは使えない事が判りました。
　Excelにはネストという概念があるのですが、それは関数や括弧の中に、更に関数を入れ子にした数式を作成した場合において、関数や括弧が何重の入れ子になっているのかを表す数です。
　例えば、

=COUNTIF($A$1:$A4,"><")

は単純な関数ですから、ネストレベル1、

=COUNTIF(OFFSET($A$1,,,4),"><")

はCOUNTIF関数の範囲を指定するのに、OFFSET関数を使用した複合的な関数ですから、ネストレベル2、

COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")

はCOUNTIF関数の中に、OFFSET関数があり、更にその中にROW関数がありますから、ネストレベル3になります。
　Excel2007より前のバージョンでは、ネストレベル7までの関数しか扱っていないため、ネストレベルが8以上の関数は入力する事が出来ないのです。
　そのため、Excel2007より前のバージョンでは、ANo.5で記した数式を入力する事が出来ません。（Excel2007以降のバージョンでは、ネストレベル64まで可能）
　ですから、ANo.6の数式を御使用下さい。

お礼

大変、お世話になります。ありがとうございました。
私はどうもOFFSET関数を理解していないのでしょうか。。今回ももう一度OFFSETをいろいろ勉強してみたのですが、こちらがまだ理解できません。
=COUNTIF(OFFSET($A$1,,,4),"><")

=COUNTIF($A$1:$A4,"><")

このCOUNTIF(OFFSET($A$1,,,4),"><")についてですが、
私の理解では、A1から右にも左にも動かず４つだけ下に動く。
なので、A5のセルのことを指すと思っていました。

たしかにOFFSETで範囲を指定できるのも勉強したのですが、この場合、幅にあたる数字がないことから、A5のセルのみを指すと考えてしまいました。
そして、いろいろ調べたのですが、やっぱりわかりません。

これはどう考えたらよいか、お手すきのときで良いので教えていただけませんか。

回答No.5

kagakusuki

回答番号:ANo.2,3,4です。

>この部分’=IF(AND(ISNUMBER($C1),$C2=""),’
の、＄C2のところを、動かないようにしたいです。
どういう意味かというと、列を削除したとき、他の部分は列を削除しても関数がずれずに保っているのですが、この＄C2のところだけは＃REFとなってしまいます。

　仰る意味が良く解らないのですが、＃REFエラーが出たという事は、参照先のセルが存在しないという事です。
　＄C2の所に＃REFエラーが出たという事は、C2セルが削除されているという事になりますが、列ごと削除してC2セルが無くなるのは、C列を削除した場合のみです。
　しかし、C列を削除したのであれば、$C1の部分も＃REFエラーとなる筈ですが、＃REFエラーが出ているのは、C2の所だけだと仰っておられます。
　もしかすると、削除したのは列ではなく、行なのではないでしょうか？

　取り敢えず、行と列のどちらを削除した場合にも対応する様に改良した数式を記しておきます。

=IF(AND(ISNUMBER(INDEX($C:$C,ROW())),INDEX($C:$C,ROW()+1)=""),SUM(INDIRECT("R"&SUMPRODUCT(ROW(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE))*(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE)<>"")*(COUNTIF(OFFSET(INDEX($A:$A,1),,,ROW(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE))),"><")=COUNTIF(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE),"><")))&"C"&COLUMN($C:$C)&":R"&ROW()&"C"&COLUMN($C:$C),FALSE)),"")

　尚、上記の数式では、列の削除や挿入に対応するためにINDIRECT関数の中の参照形式をR1C1形式にしています。
　又、1行目が削除される場合に備えて、OFFSET関数によらない対処をしています。

お礼

そうです。大変申し訳ありません。行のことでした。下記間違えました。

行の削除はすることが多いので、OFFSET関数を入れてみたのですが、
なぜか上記のようなエラーになってしまい。。
（今までこういうことがあればOFFSETを使ってうまくいっていたのですが、
他の部分が難しくてOFFSETがうまく入れられていなかったせいか、どうやってもエラーになってしまいました）

教えていただいたもので、また、やってみたいと思います。

回答No.4

kagakusuki

回答番号:ANo.3の続きです。

　SUMPRODUCT関数は本来は配列の積の和を計算するための関数なのですが、これを応用しますと、

SUMPRODUCT((判定式1)*(判定式2)*セル範囲)

という様な形式とする事で、複数条件に該当するセルの値のみの合計値を求める事も出来ます。
　例えば、

=SUMPRODUCT((A2:A5=3)*(B3:B6>0)*C2:C5)

という数式は、パソコンの内部で

=(A2=3)*(B2>0)*C2
+(A3=3)*(B3>0)*C3
+(A4=3)*(B4>0)*C4
+(A5=3)*(B5>0)*C5

という計算が行われています。
　尚、

(A2=3)

という部分は、
A2セルの値が3である場合には、数値の1と同様に扱われ、
A2セルの値が3ではない場合には、数値の0と同様に扱われます。

SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))

という数式は、それを更に応用したものです。
　例えば、D1セルをコピーして、D4セルに貼り付けると、その部分は

SUMPRODUCT(ROW($A$1:$A4)*($A$1:$A4>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A4)),"><")=COUNTIF($A$1:$A4,"><")))

という様に変換されますが、これは

ROW($A1)*($A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A1)),"><")=COUNTIF($A$1:$A1,"><"))
+
ROW($A2)*($A2<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A2)),"><")=COUNTIF($A$1:$A2,"><"))
+
ROW($A3)*($A3<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A3)),"><")=COUNTIF($A$1:$A3,"><"))
+
ROW($A4)*($A4<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")=COUNTIF($A$1:$A4,"><"))

と同じ意味になりますから、

=1*("A社"<>"")*(1=2)
+
2*(""<>"")*(1=2)
+
3*("B社"<>"")*(2=2)
+
4*(""<>"")*(2=2)

=1*1*0+2*0*0+3*1*1+4*0*1

=3

という計算が行われます。
　文章で説明しますと、
A1～A4のセル範囲で、
A列のセルが空欄ではなく、
且つ
A1セルからそのセルまでの範囲にある、空欄ではないセルの個数が、A1～A4の範囲にある空欄ではないセルの個数と等しい
という条件を満たすセルが存在する行の行番号を合計する
という数式です。
　この条件を満たすのは、A1からそのセルまでの範囲にある、空欄ではないセルの個数が、2となる最初の行である3行目のみであるため、計算結果は3行目の行番号である3になります。

ROW()

の値は、その数式が入力されている行の行番号になりますから、

INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A4)*($A$1:$A4<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A4)),"><")=COUNTIF($A$1:$A4,"><")))&":C"&ROW())

=INDIRECT("C"&3&":C"&4)

=INDIRECT("C3:C4")

=$C3:$C4

という事になります。

補足

どうもありがとうございます。
申し訳ありません。ここでつまづいてしまいました。
=1*("A社"<>"")*(1=2)
+
2*(""<>"")*(1=2)
+.................

のところです。
(1=2)とあるのですが、（このあとも1＝2）（2＝2）
と続きますが、この前者の数字（＝より前の数字）がどうやって出てきたのかがわかりません。申し訳ありません。
もう少し考えてみますが、もしこちらの質問に気づかれましたら教えてください。

回答No.3

kagakusuki

回答番号:ANo.2です。

>ざっくりでいいので中身（意味）を教えていただけないでしょうか。

=IF(AND(ISNUMBER($C1),$C2=""),SUM(INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW())),"")

の中の

ISNUMBER($C1)

の部分は、C1セルに数値データが入っているか否かを判定する関数です。（数値の場合は成り立っていると見做す）
　ですから、

=IF(AND(ISNUMBER($C1),$C2=""),真の場合,偽の場合)

という形式の関数は、C1セルに数値データが入っている事と、C2セルが空欄である事の、
両方がともに成り立っている場合には、真の場合の部分に入力した値を表示し、
どちらか一方でも成り立っていない場合には、偽の場合の部分に入力した値を表示する
という関数になります。
　D1セルの数式では、偽の場合の部分が

""

となっていますから、数式が入力されているセルがあるのと同じ行の、C列のセルに数値が入力されていて、尚且つ、１つ下の行のC列のセルには、何も入力されていない場合には、真の場合の部分に入力した数式によってきまる値が表示され、
それ以外の場合には、何も表示しない
という数式になります。

　そして、真の場合の部分に入っている

SUM(INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW()))

という部分ですが、SUM(　)は括弧内に入力されているセル範囲内の数値を合計する関数です。
　INDIRECT(　)は、括弧内に入力されている文字列と同じ名前のセル、又はセル範囲を参照する関数です。
　例えば、

=INDIRECT("Sheet1!A"&B1+C1)

という数式があって、B1セルに2、C1セルに3が入力されていた場合には、括弧内の文字列は

Sheet1!A

という文字列の後に、2と3を足し合わせた数値である

5

を結合した

Sheet1!A5

という文字列になりますから、Sheet1!A5に入力されているデータを参照する数式になります。

=Sheet1!A5

と同様に思えるかも知れませんが、B1セルやC1セルの値によって、参照するセルを変更する事が出来ます。（OFFSETとは違って、参照するSheetも変える事が可能）
　ですから、

INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW())

という数式の、INDIRECT(　)で囲まれた部分は

"C"&

という部分と

SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))

と

&":C"&

と

ROW()

という部分から成り立っていますから、
C列における

SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))

の計算結果と同じ数の行番号から、

ROW()

の結果と同じ数の行番号まで
のセル範囲を表します。

　そろそろ、回答欄の入力可能な文字数の限界を超えるため、このサイトの規則には少々反しますが、残りは後で投稿させて頂きます。

お礼

こんなにたくさん教えてくださって（この後の投稿も含め）ありがとうございます！
このあとじっくり読んでみます。また、お礼と併せてご連絡させていただきます。

回答No.2

kagakusuki

列を追加しなくても良い方法です。

　今仮に、「A社」と入力されているセルがA1セル、「27」と入力されているセルがD13セルだとします。

　まず、D1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($C1),$C2=""),SUM(INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW())),"")

　次に、D1セルをコピーして、D2以下に貼り付けて下さい。

　以上です。

お礼

すみません、あとひとつ教えていただきたいことがあり、こちらに記載しました。
こちらの関数ですが、はじめのこの部分’=IF(AND(ISNUMBER($C1),$C2=""),’
の、＄C2のところを、動かないようにしたいです。
どういう意味かというと、列を削除したとき、他の部分は列を削除しても関数がずれずに保っているのですが、この＄C2のところだけは＃REFとなってしまいます。

なので、OFFSET関数で、いつもこのC2の部分を参照するようにしようと思ったのですが、他の部分が難しいですが、うまくOFFSET関数が入ってくれません。
ご教授いただけないでしょうか。

補足

ありがとうございます！すごいです。
ただ、自分でどういう仕組みか調べてみましたが、やっぱり途中でわからなくなりました。今後の参考に、ざっくりでいいので中身（意味）を教えていただけないでしょうか。

回答No.1

acha51

Ｂ列を追加Ａ列をコピーして必要分ドラッグしてコピー、
（Ｂ列は印刷時は表示⇒表示しないで隠す）
E1に=sumif(b$1:b$14,A1,D$1:D$14)
E3に=sumif(b$1:b$14,A3,D$1:D$14)
E6に=sumif(b$1:b$14,A6,D$1:D$14)

です