人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

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

http://mewlet.com/excel.gif

●質問者: nyankochan
●カテゴリ:コンピュータ
✍キーワード:Excel VBA コード 検索 画像
○ 状態 :終了
└ 回答数 : 9/9件

▽最新の回答へ

1 ● きゃづみぃ
●20ポイント

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

別のセルに

=A1 & B1

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



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

◎質問者からの返答

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

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

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


2 ● beatgoeson
●20ポイント

複数条件の場合には、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でもフィールド名が取得できずにエラー値で返されてしまいました。


3 ● ootatmt
●20ポイント

表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

◎質問者からの返答

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

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

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

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


4 ● りくっち
●20ポイント

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


表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

◎質問者からの返答

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

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

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

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

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

http://mewlet.com/excel2.gif


5 ● りくっち
●0ポイント

#4の回答者です。

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

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

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

例えば、

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

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

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

◎質問者からの返答

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

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


1-5件表示/9件
4.前の5件|次5件6.
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ