[[20170629111244]]　『subtotalとcountif関数の合成』（みーちゃん）　ページの最後に飛ぶ

『subtotalとcountif関数の合成』（みーちゃん）

おはようございます。
お忙しいところ恐れ入りますが、教えてください。
現在

    C　　　　　D
4  ハンカチ　　=COUNTIF(D12,D10006,$C4)
5  タオル　　　=COUNTIF(D12,D10006,$C5)
とCの品数をDに表記するようにしております。
これをフィルターをかけた時にも同様に正確な数字が出るような関数はありますでしょうか？
subtotal関数とcountif関数を合体できればいいかとも思ったのですが$C4がネックになってなかなかいい方法がみつかりません。

どうかよろしくお願いします。

＜使用 Excel：unknown、使用 OS：Windows10 ＞

 D4セルに
 =SUMPRODUCT(SUBTOTAL(3,INDIRECT("D"&ROW(D$12:D$10006)))*(D$12:D$10006=C4))
 と入力して下へフィルコピーでどうか。
（ねむねむ） 2017/06/29(木) 11:39

ねむねむさん
お返事ありがとうございます。
早速関数を入力してみましたが、表示された数字が269865とセル範囲より大きい数字が出てきました。
（みーちゃん） 2017/06/29(木) 11:56

 実際に入力した式をコピーして貼り付けてみてくれないか？

 なにか行数の27倍の値のようだが。
（ねむねむ） 2017/06/29(木) 13:07

ねむねむさん
実際に入力したのは
=SUMPRODUCT(SUBTOTAL(3,INDIRECT("D"&ROW(D$12:D$10006)))*(D$12:D$10006=C4))
になります。
関数を一つずつ分解して調べましたら、
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Yes))
これとか
=SUMPRODUCT((SUBTOTAL(3,INDIRECT("B"&ROW(8:14))))*MATCH(B8:B14&"_"&D8:D14,B8:B14&"_"&D8:D14,0)=ROW(1:7)))
これも中の数字と文字を変えて試してみたのですが同じように範囲以上の数字が出るか、０となります。

あっもしかして27倍ってことは/27入れれば解決？　ちょっとそれも試してみます。
（みーちゃん） 2017/06/29(木) 13:15

 念のため、EXCELのバージョンはなんだろうか？

 あと、
 >なにか行数の27倍の値のようだが。
 これは
 9995*27=269865
 と12行目から10006行目までの27倍になっているということなので27で割っても意味がないかと。
（ねむねむ） 2017/06/29(木) 13:27

エクセルは2003です。
念のため別のPCを起動してexcel2013とついでにオープンオフィスでも検証してみます。

27で割っても意味がなかったです＾＾；

それと何かのVBAが被って数字が出たのかと思ってマクロのないBookを立ち上げても同じ数字でした。
（みーちゃん） 2017/06/29(木) 13:55

D$12:D$10006からD$12:1011に範囲を狭めても27倍の10000という数字が出ました
（みーちゃん） 2017/06/29(木) 14:05

 =SUMPRODUCT(SUBTOTAL(3,INDIRECT("D"&ROW(D$12:D$10006))))
 =SUMPRODUCT((D$12:D$10006=C4)*1)
 と入力した場合、それぞれ結果はどうなるだろうか？
（ねむねむ） 2017/06/29(木) 14:12

=SUMPRODUCT(SUBTOTAL(3,INDIRECT("D"&ROW(D$12:D$10006))))とした場合9995とでます

=SUMPRODUCT((D$12:D$10006=C4)*1)とした場合27と出ます

手動で計算した場合27が正解です

もしかして。。。
（みーちゃん） 2017/06/29(木) 14:27

 もう一つ。

 =SUMPRODUCT((D$12:D$10006<>"")*(D$12:D$10006=C4))
 だとどうなるだろうか？

（ねむねむ） 2017/06/29(木) 14:46

 =SUMPRODUCT(SUBTOTAL(3,INDIRECT("D12:D10006"))*(D$12:D$10006=C4))
 や
 =SUMPRODUCT(SUBTOTAL(3,D$12:D$10006)*(D$12:D$10006=C4))
 なら269865になってもわかるのだが。
（ねむねむ） 2017/06/29(木) 16:41

ねむねむさん　こんばんわ
こちらのパソコン環境やデータ内容も分からない状況で
色々と親身になって考えて、そして助けてくれて本当にありがとうございます。

色々と検証してみたので長文になりますが、結果を言います。
まず最初にねむねむさんの教えてくれた
=SUMPRODUCT(SUBTOTAL(3,INDIRECT("D"&ROW(D$12:D$10006)))*(D$12:D$10006=C4))
ですが　エクセル2010で見事に出来ました。
出来なかった理由も検証してみたのですが、バージョンによっては出来ない、設定によっては出来ないという
結論に至りました。*が特定の状況下や関数で乗算処理になるのではないかと推測しました。
ですので正解数27に対して27の倍数返しで表示されるのかと思います。

こちらの計算式
=SUMPRODUCT((D$12:D$10006<>"")*(D$12:D$10006=C4))
の表示も２７になります

=SUMPRODUCT(SUBTOTAL(3,INDIRECT("D12:D10006"))*(D$12:D$10006=C4))
=SUMPRODUCT(SUBTOTAL(3,D$12:D$10006)*(D$12:D$10006=C4))
上記２つは５２６２３と表示されます
こちらは２７で割ると１９４９
丁度空白データを除いたセル個数1949になります

とりあえず、エクセル2010でやります。
ありがとうございました。
（みーちゃん） 2017/06/30(金) 01:33

 ＞バージョンによっては出来ない、設定によっては出来ないという結論に至りました。
 ＞*が特定の状況下や関数で乗算処理になるのではないかと推測しました。

 その結論には賛同できません。少なくとも2003ならできるはず。

 後段の推測に関しては明らかに間違い。
 その「*」は乗算ですよ、状況やバージョンの違いなど関係ありません。

 先入観を捨てて、もう一度2003でも試してみたらどうですか。

 参考まで。
（笑） 2017/06/30(金) 12:57

コメント返信：

[ 一覧(最新更新順) ]

[[20170629111244]] 『subtotalとcountif関数の合成』（みーちゃん） ページの最後に飛ぶ

[[20170629111244]]　『subtotalとcountif関数の合成』（みーちゃん）　ページの最後に飛ぶ