[[20150327100420]] 『SUBTOTALとCOUNTIFの組み合わせ』(こまったさん) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『SUBTOTALとCOUNTIFの組み合わせ』(こまったさん)

エクセル2003について質問です。
オートフィルタで絞った数量をカウントするためにSUBTOTALを使用しているのですが、
さらにオートフィルタで絞った数量かつ1以上の数量をカウントしたいのですが、うまくいきません。
何か良い方法はありませんでしょうか?
宜しくお願い致します。

< 使用 Excel:Excel2003、使用 OS:unknown >


 A列が数量で、最大100行目までだとする。

 =SUMPRODUCT(SUBTOTAL(2,INDIRECT("A"&ROW(2:100)))*(A2:A100>=1))
 で動だろうか?
 なお、データが100行以上ある場合は式中の「100」を大きくしてくれ。
(ねむねむ) 2015/03/27(金) 10:35

ご回答ありがとうございました!
仕事で困っていたので助かりました!
こんな関数の組み合わせは使ったことがなかったです。
(こまったさん) 2015/03/27(金) 11:34

こんにちは。
更に質問なのですが、回答いただいた式で上手くいったのですが、式の意味合いがいま一つ理解できませんでした。。
とくにINDIRECTから後ろがそれぞれどんな意味になるのかが分かりません。
今後も使うことがありそうなので教えていただけると助かります。
よろしくお願いします。
(こまったさん) 2015/03/28(土) 12:06

 =SUMPRODUCT(SUBTOTAL(2,INDIRECT("A"&ROW(2:100)))*(A2:A100>=1))
 を展開すると
 SUBTOTAL(2,INDIRECT("A"&ROW(2)))*(A2>=1)+
 SUBTOTAL(2,INDIRECT("A"&ROW(3)))*(A3>=1)+
 SUBTOTAL(2,INDIRECT("A"&ROW(4)))*(A4>=1)+
 …
 SUBTOTAL(2,INDIRECT("A"&ROW(100)))*(A100>=1))
 となり"A"&ROW(…)部分が

 SUBTOTAL(2,INDIRECT("A2"))*(A2>=1)+
 SUBTOTAL(2,INDIRECT("A3"))*(A3>=1)+
 SUBTOTAL(2,INDIRECT("A4"))*(A4>=1)+
 …
 SUBTOTAL(2,INDIRECT("A100"))*(A100>=1))
 となる。

 ここでINDIRECT関数はセル番地の文字列を実際のセル参照に変換するため

 SUBTOTAL(2,A2)*(A2>=1)+
 SUBTOTAL(2,A3)*(A3>=1)+
 SUBTOTAL(2,A4)*(A4>=1)+
 …
 SUBTOTAL(2,A100)*(A100>=1)

 これでSUBTOTAL関数部分はA列がオートフィルタで隠れていなくてさらにデータがあるところだけ「1」となり、隠れているところ、データがないところは「0」となる。

 A2>=1
 の部分は「A2>=1」が成り立つ場合は「TRUE」(真)に、成り立たない場合は「FALSE」(偽)になりこの「TRUE」「FALSE」を
 計算に使うと「TRUE」が「1」、「FALSE」が「0」になるので結果としてオートフィルタで隠れていなくて値が1以上の時のみ
 「1」となりそれを合計するため個数を求められる。

 一応下記も参考にしてくれ。
http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml

 あと、前回はうっかりしていたが
 =SUMPRODUCT((SUBTOTAL(9,INDIRECT("A"&ROW(2:100)))>=1)*1)
 でも求められる。

 この場合はオートフィルタで隠れている行は0、現れている行はその行の値となり、それが「1」以上かを見ている。

(ねむねむ) 2015/03/28(土) 15:29


 追記で。

 もともとSUBTOTAL関数はセル範囲を扱う関数のため単純に「SUBTOTAL(2,A2:A100)」などとするとA2セルからA100セルまでを
 まとめて処理してしまうためROW関数を使うことでA2セルからA100セルまでのセルを一つづつ処理する様にしている。

 で、ROW関数の結果をセル参照にするためにINDIRECT関数を使っている。
(ねむねむ) 2015/03/28(土) 15:35

ねむねむさん。いつもありがとうございます!
奥が深いっ!ですね。
まだまだエクセル初心者なのでもっと使いこなせるように頑張ります!
ありがとうございました。
(こまったさん) 2015/03/28(土) 18:42

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.