[[20100618135135]]　『SUBTOTALで飛び飛びのセルの合計を出したいのです』（あややん）　ページの最後に飛ぶ

『SUBTOTALで飛び飛びのセルの合計を出したいのですが･･･』（あややん）

 　　Ａ　　Ｂ　　　Ｃ
 　　店舗　商品名　売上金額
 １　Ａ店　○商品　5000
 ２　Ａ店　×商品　2000
 ３　Ｂ店　○商品　1000
 ４　Ｂ店　×商品　7000
 ５　Ｃ店　○商品　8000
 ６　Ｃ店　×商品　6000
 ７　合計　○商品　？？①
 ８　合計　×商品　？？②

 ？？①には =SUMPRODUCT(B1:B6="○商品")*(C1:C6)
 ？？②には =SUMPRODUCT(B1:B6="×商品")*(C1:C6)
 を入れているのですが、オートフィルタでＡ店・Ｂ店のみを表示した時には
 ２店舗だけの商品の売上合計を出したのですが、、、
 SUBTOTALと上手く組み合わせられません。

 良い方法をご教示お願いします。

 まず作業列を使う方法です。

 例えばD列を作業列としてD2セルに（1行目は見出し行だとして）
 =(SUBTOTAL(9,C2))
 と入力して下へコピーします。

 そして
 =SUMPRODUCT((B1:B6="○商品")*(D1:D6))
 =SUMPRODUCT((B1:B6="×商品")*(D1:D6))
 とD列を計算の対象にします。

 次に作業列を使わない方法です。
 =SUMPRODUCT((B2:B7="○商品")*SUBTOTAL(9,INDIRECT("C"&ROW(2:7))))
 =SUMPRODUCT((B2:B7="×商品")*SUBTOTAL(9,INDIRECT("C"&ROW(2:7))))
 で。
 (独覚)

 出来ました！
 ありがとうございました！

 （あややん）

 横から失礼します。
初歩的なことで申し訳ないのですが、今回もいつものように質問者さんの例題をエクセルにコピーし
回答者さんの関数をセルに入力してもどうしても正しい答えが求まりません。
原因は文字の非同一性にあることは分かったのですが、なぜそうなるかを教えて下さい。

 私のやり方はこうです。

 まず、質問者さんの例題から必要な表の部分をコピーして、開いておいたエクセルに貼り付けます。
次に、データ―区切り位置でスペースを指定、矢印を入れて完了まで持って行き,表にします。
そこで求めたいセルに自分なりに考えた関数を入力して計算結果を見ます。
分からないときは回答者さんの提示した関数をコピーして研究します。

 今回自分で入力した関数も、回答者さんのコピーも文字列の部分が例題のものと一致せず答えが出ません。
コピーの仕方に問題があるのか、文字変換の仕方が違うのかだと思うのですが、分かりません。

 ちなみに、表の中の文字列を新たに書き直して試してみると正しい答えが出ます。
見た目には全く同じ"○商品、×商品"ですがどこが違うのでしょうか。
（おいちゃん）

 すみません、原因が分かりました。
文字列の後ろのスペースが原因でした。
文字列のときは左詰のため文字列の前のスペースには注意をしていたのですが後ろはウッカリしていました。

 これは"データ―区切り位置"のやり方が違っているのでしょうか。
それとも、この作業をするときは常に分割の後,スペースの処理をしなければならないのでしょうか。
質問の内容が変わってしまい申し訳ありません。
（おいちゃん）

 「スペースによって～固定長フィールドのデータ」　だとスペースは残ります。
 「カンマやタブなどの～区切られたデータ」　だとスペースはなくなります。
 (alt)

 altさん、ありがとうございます。出来ました。
「元のデータの形式」ではなく、「区切り文字」の所でスペースを選択ですね。

 独覚さん、本来の質問者さんへの回答で使われたINDIRECT関数の使い方について教えて下さい。
SUBTOTAL関数の第二引数として「C2:C7」と入力すると、当然のことながらこの間の表示セルの値を合計してしまいます。
INDIRECT関数を使うとどうして合計しないで、配列定数として機能するのでしょう。
INDIRECT関数に関してはこの学校で以前にも教えてもらったのですが、使い方が変わり又分からなくなってしまいました。
理由が分かれば有難いのですが、裏技的なもので特別な使い方というのであればただ覚えるしかないのですが…。
（おいちゃん）

 まず、ROW関数のヘルプを見てください。

 >範囲がセル範囲に対する参照で、ROW 関数が縦方向の配列として入力されている場合、範囲の行番号は縦方向の配列となります。

 つまり配列数式内で「ROW(2:7)」と入力すると「{2;3;4;5;6;7}」という数値の配列を返します。

 INDIRECT関数はあくまでも文字列をセル参照に変換しているだけですので配列定数化の肝はROW関数の方になります。

 試しに「INDIRECT("C2:C7")」としてみてください。
 こちらでは配列化され無いのがわかるかと。
 (独覚)

 独覚さん、ありがとうございます。

 ROW関数にそんな機能があるとは知りませんでした。

 ヘルプの意味も,教えてもらうまで何を言っているのかさえ理解できていませんでした。

 COLUMN関数も同じ機能を持っているんですね。

 配列数式を作るときの一つの小道具として覚えておきます。

 的確なご説明で素人にも分かりやすくバッチリでした。

 （おいちゃん）

コメント返信：

[ 一覧(最新更新順) ]

[[20100618135135]] 『SUBTOTALで飛び飛びのセルの合計を出したいのです』（あややん） ページの最後に飛ぶ

[[20100618135135]]　『SUBTOTALで飛び飛びのセルの合計を出したいのです』（あややん）　ページの最後に飛ぶ