SUMIFで検索範囲から検索値を検出して合計値を出力することの延長だと考えて下さい。
AコードとBコードがあり、この両方が一致する行の数値の合計を求めるにはどうしたらよいのでしょうか？
Excel関数のみで解決できる方法を探しています。
文字だけだと何をしたいかわかりづらいので画像も載せておきます。
http://mewlet.com/excel.gif
No.7
235
14
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
No.1
135391198
20pt
SUMIFを用いたらいいと思います。
別のセルに
=A1 & B1
といった感じで AコードとBコードを連結させてひとつにしたセルを作り、それで 集計したらいいでしょう。
ごめんなさい、説明不足でした。
検索する範囲については登録型で可変となります。
ゆえに、その方法をとることができませんでした。
No.2
12814
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....
これも範囲が可変でなければ有効でしたのでとても残念です。
範囲に名前を付けてしまえば良いのですが、その名前を付けるというのにもデータ登録・削除でズレが出てしまい出来ず、B:Bのように列丸ごと指定で求めなくてはなりません。
ですが、列丸ごと指定の場合は、可能性のあったDSUMでもフィールド名が取得できずにエラー値で返されてしまいました。
No.3
130765
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 を押すことで入力できます。
こちらの方もごめんなさい。
上の回答で書いたとおり、検索する範囲については登録型で可変となります。
ゆえに、その方法をとることができませんでした。
どうしてもＡコードを調べてヒットしたらＢコードを調べ、それでヒットしたものの合計を出すという２段式のようなことをしなければなりません。
No.4
1366141
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列にフィルコピーすれば算出されます。
やはり、自動ではいかないのでしょうか。
ごめんなさい、説明不足過ぎてなかなか欲しい答えにたどり着いていないので、
ちょっと整理して現在やろうとしてる事に本格的に近づけて説明させていただきます。
シート１に登録された人の名前とシート２に登録された商品があり、シート３で毎月の販売詳細を登録していき、シート４に名前と商品の両方の条件にあうデータをシート３から探し出して合計金額を表示します。
いろいろやってみた結果、行削除による行範囲のズレ対策として列丸ごと指定をする方法になり、それをExcel関数のみでSUMIFに似た事をしようとすると範囲指定がうまくいかない現状にあります。
No.5
1366141
#4の回答者です。
集計の元表が可変ということですね。
配列数式は基本的に列参照ができません。
行削除をする度確かに数式の指定範囲は短くなっていってしまうので、
例えば、
=SUM(IF((Sheet1!A$1:A$65535=A1)*(Sheet1!B$1:B$65535=B1),Sheet1!C$1:C$65535))
といったように最大行数から1だけ少ない行数で指定すれば、
よほど大量なデータを扱うのでない限りある程度の期間は自動化できるかと思います。
１少ない数字で試してみたのですが、やはりデータを一行削除すると$A$65535 ⇒ $A$65534 と減算されてしまいました。
全データの途中のデータを削除することもあるため、行ごと削除以外の方法をとると、削除時に行整理するという膨大な負担が増えてしまうため、現状の仕様から変更が難しくあります。
No.6
1366141
ええと…何度もすいません、コメント欄が開放されていればそちらに書くのですが…。
#5の回答は、指定範囲が削除行分だけ減っていくので、
最大の指定範囲にしておけば、データ範囲より指定範囲の方が大きい状況がしばらくあるので、
「ある程度の期間は自動化できる」という意味で書いたのですが、うまく伝わらなかったようで申し訳ないです。
Excelはあくまで「計算ソフト」であって、データベース機能は付随にすぎません。
なので、レコード単位での集計管理には基本的には向いていないソフトなのです。
随時1万件を登録し、5000件を削除してまた登録するといった内容であれば、
正直なところExcelではなく、Accessでの管理をお薦めしたいところです。
(Accessならば難なく出来てしまう内容です)
それほどのデータ量でなく、1日100件前後の増減を日々繰り返す程度で、2000件程度の項目に対して集計するような作業であれば、
1月位はデータ範囲より指定範囲の方が小さくなることはあまりないでしょうから、
登録シートだけを白紙にしたテンプレートを作成して、
規定の期間が終わったらテンプレートでファイルを新規作成し、
前期間分の登録シートのデータだけを貼り付けて移行していけばいいように思います。
それも行程上無理と言うことなら、VBAで処理するしかないと思います。
実の所、Accessを使用して作った方が良いのですが全てExcelでという先方の依頼でExcelの限界に挑みつつなんとかする的な状況下で作成をしています。
それで、確かに65536指定から行を一度挿入する事で擬似固定？できるようなのですが、後々追加されていくデータでの座標設定がVBAで新規に書き込まれるため、ズレるものとズレないものがやはりでてきてしまうようです。
これらを使う方法として、可能性があるならばシート４に合計を出す項目を追加する度にシート３の最終行に挿入をするという形をとることになるのでしょうか。
いろいろ別のところで負荷がかかってエラーが出始めているので調整しながら試して見ます。
No.7
23514
ここでベストアンサー
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
みなさん、ほんとーーーーにごめんなさい＞＜
Ohmixlさんの回答にピンときたので頭を整理してよくよく考えたら、
$B$4:$B$65535 と絶対参照にしてしまうと行削除を行った際に参照範囲がズレてしまうのは当たり前のことでした＾＾；
なにをボケていたのかやっと気づかされました。
みなさんの回答を参考に最終的にVBAに組み込むセル内の関数のみで集計できる式を完成させることが出来ました。
ありがとうございますm(_ _)m
No.8
26034
20pt
=A1&B1とする考え方の応用で...それを別シートで行削除にも対応しては、だめですか？
「販売詳細」シートのＡ列に名前、Ｂ列に商品、Ｃ列には販売数量が入っているとします。
「作業」というシートを追加し、行削除・行挿入でも影響を受けないように
表は２行目から始まるとして、「作業」シート
A2 =OFFSET(販売詳細!$A$2,ROW()-2,0)&"_"&OFFSET(販売詳細!$B$2,ROW()-2,0) 式を下にコピー
実際には、=A2&"_"&B2 と同じ意味
＃場合によってはこのシートを非表示 ２行目以外なら、ROW()-2の式を調整
集計用シートも、Ａ列に名前、Ｂ列に商品とし、Ｃ列に
=A2&"_"&B2 これを下にコピー（別シートでも可能）
=SUMIF(作業!A:A,C2,販売詳細!C:C)
Excel2000で確認しました。
No.9
897145
20pt
Sheet1に元データ（表２）があるとします。
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の参照には影響しませんので、見かけ上参照を固定することができます。
robbie21
2006/10/12 11:10:18
42
39
nyankochan
kaiton
1
1
みなさん、ほんとーーーーにごめんなさい＞＜
Ohmixlさんの回答にピンときたので頭を整理してよくよく考えたら、
$B$4:$B$65535 と絶対参照にしてしまうと行削除を行った際に参照範囲がズレてしまうのは当たり前のことでした＾＾；
なにをボケていたのかやっと気づかされました。
みなさんの回答を参考に最終的にVBAに組み込むセル内の関数のみで集計できる式を完成させることが出来ました。
ありがとうございますm(_ _)m