Excel条件付きの和を求めたい

質問

こんにちは。質問No7850523でお世話になりました。
http://okwave.jp/qa/q7850523.html
このときのベストアンサーには以下の式を選びました
=IF(D1<>D2,SUMIF(D:D,D1,AN:AN),"")

この質問にもう一つ条件が付きましたので
解決方法をお教えください。

Excel2007を使用しています。

D列にシリアル番号が昇順で入っています。

L列に数字（回数）が入っています

AN列に数字（個数）が入っています。
以下わかりやすく回数毎に改行してあります
実際は空の行はありません

D-----L--AN---BI
0001--1--5----5

0001--1--2----2

0002--1--2----12
0002--2--3
0002--3--1
0002--4--6

0002--1--1----9
0002--2--5
0002--3--3

0003--1--0----8
0003--2--3
0003--3--5

0003--1--2----2

0003--1--6----6

同じシリアル番号で回数が1回の時はその個数
複数の回数の時はその合計個数をBI列に求めたいと思います

このときの式の立て方をお教えください。
よろしくお願いいたします。

回答No.4

MarcoRossiItaly · ベストアンサー

No.1 です。どうやら見ていると、条件付きというよりも、L 列が 1 になったら合計をキャンセルして、値を戻すという意味ですかね。で、合計の対象は、AN 列なんだと。そうであれば、次式。

BI2　=0+(l2=1)
BJ2　=n(bj1)+bi2
BK2　=bi2*sumif(bj:bj,bj2,an:an)

こういうのは始めから枝番号を振っておかないと、今後もいろいろ苦労しますよ。添付図を参照。値複写で数式を除去しておき、今後もレコードが増えたら、手入力などで番号を追加していくとよいでしょう。

他にも、できるだけ作業列を使うようにすると、簡単な数式のみで欲しい結果が得られます。

ゼロという値のみをセルの書式で非表示にすることもできます。具体的には、対象のセル範囲を選択した状態で、右クリックなどから「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」に、「G/標準;-G/標準;」とか「[=0]"";G/標準」などを入力するだけ。

お礼

ご回答ありがとうございます。

作業列を作ると誤りが少なくなり、
横スクロールも無くなり作業がはかどりますね。

アドバイスありがとうございます。

補足

作業列で整理する考え方を教えていただきありがとうございます。
計算式を教えてくださった皆さんにも感謝します。

回答No.10

MarcoRossiItaly

No.4・5 です。

No.4 では枝番号という言い方をしましたが、枝番号というか、要するに「一意な番号」を振る、ということです。BJ 列に番号を振った後であれば、仮に D 列の番号を削除したとしても、フィルタにおける複数列での絞込みなどによって、各レコード（行）が特定できる状態になっているのがお分かりかと思います。

＞BJ2　=n(bj1)+bi2
　のn(bj1)のnの意味を教えてください。

N 関数です。多分 number という意味だと思います。T 関数というのもあります。多分 text という意味だと思います。よかったらネットで検索してみてください。

N 関数は、数値はそのままの値に、文字列は「0」という数値にそれぞれ変換してくれる関数です。

No.4 の添付図では、BJ2 セルが BJ1 を参照し、それに BI2 の値を足しています。もしも「=bj1+bi2」という数式で計算しようとすると、BJ1 の文字列に数値を足すことになるため、「#VALUE!」というエラーとなります。これを回避するため、n をくっつけといただけです。

ですから代わりに、BJ2 に「1」という数値を、BJ3 以下に「=bj2+bi3」という数式を入力することにしても、全く問題ありません。

N 関数は、たまに便利なときがあります。例えば、表中のあるセルで、同じ表の別セルの数値を使って計算したいという場合、使おうとしているセルに文字列も入力したいというケースがあります。SUM 関数などでは文字列は無視してくれますが、一般の四則演算などでは、文字列が混ざるとエラーになってしまいます。そういうときに n をくっつけとけば、エラーになりません。

次の添付図は具体例です。「未定」の箇所をいつか数値で上書きすれば、直ちに計算結果に反映されるという仕組みです。なお「=N(B7*D7)」という数式にするとエラー回避できていないので、ご注意。

お礼

解説ありがとうございます。
ExcelにN関数というのが有るんですね。
初めて知りました。

回答No.9

某HN クロメート(Chromate)（@CoalTar）

No.3です。ちょこっと違ったので修正
L列は1000以下ではなく、1000未満でした。
=IF(L2<>1,"",
IF(ISNA(MATCH(1,L3:L1001,0)),SUM(AN2:AN1000),
SUM(AN2:INDEX(AN2:AN1000,MATCH(1,L3:L1001,0)))))
に修正。適宜範囲は変更してください

作業列 MATCHとして
BJ2セル =MATCH(1,L3:L1001,0)
下へオートフィル

BIセル
=IF(L2<>1,"",IF(ISNA(BJ2),SUM(AN2:AN1000),
SUM(AN2:INDEX(AN2:AN1000,BJ2))))
下へオートフィル

L列が 1以外なら空白""
一番下の1だったら 数式の入っている行を含み、下へ999行分のの合計
一番下ではないなら、次の1の一つ上までの合計

回答No.8

kagakusuki

回答:No.6、7です。

　尚、L列の値が、D列において同じシリアル番号が上から数えて何回現れたのかを示す値だとしますと、BI1セルに入力される関数を次の様なものとしますと、L列の数値が無くとも、同じ働きをさせる事が出来ます。

=IF(AND($D1<>"",COUNTIF($D$1:$D1,$D1)=1),SUMIF($D:$D,$D1,$AN:$AN),"")

或いは

=IF(AND(INDEX($D:$D,ROW())<>"",COUNTIF($D$1:INDEX($D:$D,ROW()),INDEX($D:$D,ROW()))=1),SUMIF($D:$D,INDEX($D:$D,ROW()),$AN:$AN),"")

回答No.7

kagakusuki

済みません、間違えました。
　正しくは

=IF($L1=1,SUMIF($D:$D,$D1,$AN:$AN),"")

或いは

=IF(INDEX($L:$L,ROW())=1,SUMIF($D:$D,INDEX($D:$D,ROW()),$AN:$AN),"")

です。

回答No.6

kagakusuki

BI1セルに次の関数を入力されてから、BI1セルをコピーして、BI2以下に貼り付けられると良いと思います。

=IF($AN1=1,SUMIF($D:$D,$D1,$L:$L),"")

回答No.5

MarcoRossiItaly

No.4 です。何度もすみませんが、言い忘れたことを補足します。BI 列は、次式でも構いません。BI 列の結果が TRUE または FALSE で表示されるようになるだけで、他の列は正しく計算されます。

BI2　=l2=1

補足

すみません。
BJ2　=n(bj1)+bi2
のn(bj1)のnの意味を教えてください。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

下へ1000行分が検査対象 つまり、B列 1000以下として
D２セルに
=IF(B2<>1,"",
IF(ISNA(MATCH(1,B3:B1001,0)),SUM(C2:C1001),
SUM(C2:INDEX(C2:C1001,MATCH(1,B3:B1001,0)))))
下へオートフィル

お礼

早速の回答ありがとうございます。

実際に手計算してみたところあっています。

これで力わざから解放されました。

回答No.2

chonami

ピボットテーブルでいいのでは？

回答No.1

MarcoRossiItaly

質問文の BI 列の値が答えなのですか？その計算の方法をお知らせください。

補足

はいそうです。
L列が1だけの時はANの数字のまま
L列が1からnまであるときはANの数字の和
です。