Excel関数の質問です。(VBAではありません)


SUMIFで検索範囲から検索値を検出して合計値を出力することの延長だと考えて下さい。
AコードとBコードがあり、この両方が一致する行の数値の合計を求めるにはどうしたらよいのでしょうか?

Excel関数のみで解決できる方法を探しています。
文字だけだと何をしたいかわかりづらいので画像も載せておきます。

http://mewlet.com/excel.gif

回答の条件
  • 1人3回まで
  • 登録:2006/10/10 10:59:22
  • 終了:2006/10/13 14:02:27

ベストアンサー

id:ohmix1 No.7

ohmix1回答回数235ベストアンサー獲得回数142006/10/10 16:52:28

ポイント20pt

INDIRECT関数を使ってセル範囲を文字列で指定すると行追加/削除しても大丈夫と思います。


=SUMPRODUCT((INDIRECT("Sheet1!A1:A65535")=A2)*(INDIRECT("Sheet1!B1:B65535")=B2)*INDIRECT("Sheet1!C1:C65535"))


ただ、よく分かりませんがうちのEXCEL2000では65536まで指定するとエラーになりました。65535なら正常でした。


http://www.officetanaka.net/excel/function/function/indirect.htm

id:nyankochan

みなさん、ほんとーーーーにごめんなさい><

Ohmixlさんの回答にピンときたので頭を整理してよくよく考えたら、

$B$4:$B$65535 と絶対参照にしてしまうと行削除を行った際に参照範囲がズレてしまうのは当たり前のことでした^^;

なにをボケていたのかやっと気づかされました。

みなさんの回答を参考に最終的にVBAに組み込むセル内の関数のみで集計できる式を完成させることが出来ました。

ありがとうございますm(_ _)m

2006/10/13 13:50:13

その他の回答(8件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982006/10/10 11:01:57

ポイント20pt

SUMIFを用いたらいいと思います。

別のセルに

=A1 & B1

といった感じで AコードとBコードを連結させてひとつにしたセルを作り、それで 集計したらいいでしょう。



http://q.hatena.ne.jp/1160445561

id:nyankochan

ごめんなさい、説明不足でした。

検索する範囲については登録型で可変となります。

ゆえに、その方法をとることができませんでした。

2006/10/10 12:46:17
id:beatgoeson No.2

beatgoeson回答回数128ベストアンサー獲得回数142006/10/10 11:11:52

ポイント20pt

複数条件の場合には、SUMPRODUCT関数を使うテクニックがあります。

画像の表1の左上がセルB3にあり、表2の平利上のセルがB12にあるとした場合、

D4には、=SUMPRODUCT(($B$13:$B$19=B4)*(($C$13:$C$19)=C4)*$D$13:$D$19) として、あとはドラッグ&ドロップで、D9列までコピーしてください。

以下のURLもご参考までに。

http://www.excel-jiten.net/apply_funcs_001/sum_by_and_condition....

id:nyankochan

これも範囲が可変でなければ有効でしたのでとても残念です。

範囲に名前を付けてしまえば良いのですが、その名前を付けるというのにもデータ登録・削除でズレが出てしまい出来ず、B:Bのように列丸ごと指定で求めなくてはなりません。

ですが、列丸ごと指定の場合は、可能性のあったDSUMでもフィールド名が取得できずにエラー値で返されてしまいました。

2006/10/10 13:27:32
id:ootatmt No.3

ootatmt回答回数1307ベストアンサー獲得回数652006/10/10 11:21:09

ポイント20pt

表2の範囲が、A10 から C16 の範囲だとして、

表1, 2 ともに D列に =A10&B10 のように A, B列の値を足したものを用意します。


この状態で、表1 の C列に以下の式を入れると計算できます。

{=SUM(C$10:C$16*(D1=D$10:D$16))}

外側の大括弧は、=SUM(C$10:C$16*(D1=D$10:D$16)) を入力して Shift+Ctrl+Enter を押すことで入力できます。

http://q.hatena.ne.jp/1160445561

id:nyankochan

こちらの方もごめんなさい。

上の回答で書いたとおり、検索する範囲については登録型で可変となります。

ゆえに、その方法をとることができませんでした。

どうしてもAコードを調べてヒットしたらBコードを調べ、それでヒットしたものの合計を出すという2段式のようなことをしなければなりません。

2006/10/10 12:38:49
id:rikuzai No.4

りくっち回答回数1366ベストアンサー獲得回数1412006/10/10 13:17:58

ポイント20pt

ちょっと重くなりますが、配列数式が使えます。


表1がSheet1、A1:C5の範囲にあり、

A列=Aコード

B列=Bコード

C列=数値

とし、


表2がSheet2にあり、

A列=Aコード

B列=Bコード

C列=集計列

とした場合、


Sheet2のC1に

=SUM(IF((Sheet1!A$1:A$5=A1)*(Sheet1!B$1:B$5=B1),Sheet1!C$1:C$5))

と記入してからCtrl+Shift+Enterを押して確定します。

これをSheet2のC列にフィルコピーすれば算出されます。


http://pc.nikkeibp.co.jp/pc21/special/hr/hr4.shtml

id:nyankochan

やはり、自動ではいかないのでしょうか。

ごめんなさい、説明不足過ぎてなかなか欲しい答えにたどり着いていないので、

ちょっと整理して現在やろうとしてる事に本格的に近づけて説明させていただきます。

シート1に登録された人の名前とシート2に登録された商品があり、シート3で毎月の販売詳細を登録していき、シート4に名前と商品の両方の条件にあうデータをシート3から探し出して合計金額を表示します。

いろいろやってみた結果、行削除による行範囲のズレ対策として列丸ごと指定をする方法になり、それをExcel関数のみでSUMIFに似た事をしようとすると範囲指定がうまくいかない現状にあります。

http://mewlet.com/excel2.gif

2006/10/10 13:48:14
id:rikuzai No.5

りくっち回答回数1366ベストアンサー獲得回数1412006/10/10 14:05:36

#4の回答者です。

集計の元表が可変ということですね。

配列数式は基本的に列参照ができません。

行削除をする度確かに数式の指定範囲は短くなっていってしまうので、

例えば、

=SUM(IF((Sheet1!A$1:A$65535=A1)*(Sheet1!B$1:B$65535=B1),Sheet1!C$1:C$65535))

といったように最大行数から1だけ少ない行数で指定すれば、

よほど大量なデータを扱うのでない限りある程度の期間は自動化できるかと思います。

id:nyankochan

1少ない数字で試してみたのですが、やはりデータを一行削除すると$A$65535 ⇒ $A$65534 と減算されてしまいました。

全データの途中のデータを削除することもあるため、行ごと削除以外の方法をとると、削除時に行整理するという膨大な負担が増えてしまうため、現状の仕様から変更が難しくあります。

2006/10/10 14:21:51
id:rikuzai No.6

りくっち回答回数1366ベストアンサー獲得回数1412006/10/10 14:49:44

ええと…何度もすいません、コメント欄が開放されていればそちらに書くのですが…。


#5の回答は、指定範囲が削除行分だけ減っていくので、

最大の指定範囲にしておけば、データ範囲より指定範囲の方が大きい状況がしばらくあるので、

「ある程度の期間は自動化できる」という意味で書いたのですが、うまく伝わらなかったようで申し訳ないです。

Excelはあくまで「計算ソフト」であって、データベース機能は付随にすぎません。

なので、レコード単位での集計管理には基本的には向いていないソフトなのです。

随時1万件を登録し、5000件を削除してまた登録するといった内容であれば、

正直なところExcelではなく、Accessでの管理をお薦めしたいところです。

(Accessならば難なく出来てしまう内容です)


それほどのデータ量でなく、1日100件前後の増減を日々繰り返す程度で、2000件程度の項目に対して集計するような作業であれば、

1月位はデータ範囲より指定範囲の方が小さくなることはあまりないでしょうから、

登録シートだけを白紙にしたテンプレートを作成して、

規定の期間が終わったらテンプレートでファイルを新規作成し、

前期間分の登録シートのデータだけを貼り付けて移行していけばいいように思います。


それも行程上無理と言うことなら、VBAで処理するしかないと思います。

id:nyankochan

実の所、Accessを使用して作った方が良いのですが全てExcelでという先方の依頼でExcelの限界に挑みつつなんとかする的な状況下で作成をしています。

それで、確かに65536指定から行を一度挿入する事で擬似固定?できるようなのですが、後々追加されていくデータでの座標設定がVBAで新規に書き込まれるため、ズレるものとズレないものがやはりでてきてしまうようです。

これらを使う方法として、可能性があるならばシート4に合計を出す項目を追加する度にシート3の最終行に挿入をするという形をとることになるのでしょうか。

いろいろ別のところで負荷がかかってエラーが出始めているので調整しながら試して見ます。

2006/10/10 15:51:01
id:ohmix1 No.7

ohmix1回答回数235ベストアンサー獲得回数142006/10/10 16:52:28ここでベストアンサー

ポイント20pt

INDIRECT関数を使ってセル範囲を文字列で指定すると行追加/削除しても大丈夫と思います。


=SUMPRODUCT((INDIRECT("Sheet1!A1:A65535")=A2)*(INDIRECT("Sheet1!B1:B65535")=B2)*INDIRECT("Sheet1!C1:C65535"))


ただ、よく分かりませんがうちのEXCEL2000では65536まで指定するとエラーになりました。65535なら正常でした。


http://www.officetanaka.net/excel/function/function/indirect.htm

id:nyankochan

みなさん、ほんとーーーーにごめんなさい><

Ohmixlさんの回答にピンときたので頭を整理してよくよく考えたら、

$B$4:$B$65535 と絶対参照にしてしまうと行削除を行った際に参照範囲がズレてしまうのは当たり前のことでした^^;

なにをボケていたのかやっと気づかされました。

みなさんの回答を参考に最終的にVBAに組み込むセル内の関数のみで集計できる式を完成させることが出来ました。

ありがとうございますm(_ _)m

2006/10/13 13:50:13
id:kaiton No.8

kaiton回答回数260ベストアンサー獲得回数342006/10/10 17:30:39

ポイント20pt

=A1&B1とする考え方の応用で...それを別シートで行削除にも対応しては、だめですか?


「販売詳細」シートのA列に名前、B列に商品、C列には販売数量が入っているとします。

「作業」というシートを追加し、行削除・行挿入でも影響を受けないように

表は2行目から始まるとして、「作業」シート

A2 =OFFSET(販売詳細!$A$2,ROW()-2,0)&"_"&OFFSET(販売詳細!$B$2,ROW()-2,0) 式を下にコピー

実際には、=A2&"_"&B2 と同じ意味

#場合によってはこのシートを非表示 2行目以外なら、ROW()-2の式を調整


集計用シートも、A列に名前、B列に商品とし、C列に

=A2&"_"&B2 これを下にコピー(別シートでも可能)

=SUMIF(作業!A:A,C2,販売詳細!C:C)


Excel2000で確認しました。

id:ardarim No.9

ardarim回答回数896ベストアンサー獲得回数1442006/10/10 23:47:57

ポイント20pt

Sheet1に元データ(表2)があるとします。


Sheet2に、Sheet1のコピーを作ります。このとき、Sheet1で行削除等が行われてもいいように、間接参照でSheet2の参照が変わらないようにしておきます。

具体的には、Sheet2に次のような数列を入力します。

列Aの全セル =OFFSET(Sheet1!$A$1,ROW()-1,0)
列Bの全セル =OFFSET(Sheet1!$B$1,ROW()-1,0)
列Cの全セル =OFFSET(Sheet1!$C$1,ROW()-1,0)

あとはSheet2に対して配列演算なりを行うことができます。

Sheet1に対して削除、挿入等を行っても、Sheet2の参照には影響しませんので、見かけ上参照を固定することができます。

  • id:robbie21
    関数は使わないので質問主旨と外れるかもしれないのでコメントにて。
    ピボットテーブルを使ってはどうでしょうか
    サンプルを以下においておきます。

    http://space.geocities.jp/micnak2001/samples/index.html
  • id:nyankochan
    最終的な完成品です。
    Cells(LastData, 8).Value = "=IF(SUMPRODUCT((INDIRECT($B$2&""!C4:C65536"")=B" & LastData & ")*(INDIRECT($B$2&""!D4:D65536"")=C" & LastData & ")*(INDIRECT($B$2&""!G4:G65536"")))>0,SUMPRODUCT((INDIRECT($B$2&""!C4:C65536"")=B" & LastData & ")*(INDIRECT($B$2&""!D4:D65536"")=C" & LastData & ")*(INDIRECT($B$2&""!G4:G65536""))),"""")"

    新しく追加する行の8列目に合計値が0より多かったら合計値を返し0以下だったら「""」を返すというものです。
    後続の方へ残しますのでご参考までに。
    もし、合計が0より多い少ないの判定式で可変式でも使える複合参照でもっと簡単なのあるよ~という方いましたら
    是非ご教授お願いします~。

    LastDataはチェックを走らせて一番若い空白セルの値が入っているものです。
  • id:kaiton
    INDIRECT($B$2&""!C4:C65536"")=B" & LastData

    INDIRECT($B$2&""!C4:C" & LastData & "")=B" & LastData
    としないのは、入力後も下にデータが増えると計算値が変わるということですね?

    そうなら、
    INDIRECT($B$2&""!C4:C"" & COUNTA(C:C)-1 )=B" & LastData
    とすればどうでしょうか?
    COUNTA(C:C)でC列のデータ個数を求め、-1で見出し行の行数を引いています。他の部分も同様に変更します。
    なので、常に65536行目まで計算せずに、C列の行数によって計算範囲が可変になり、少し早いような気がするのですが..
    COUNTA関数は文字をのみをカウントするので、データによってはCOOUNT関数にする必要があります。
    SUMPRODUCT関数の状態で、行追加・削除等をして問題ないことは確認しています。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません