[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『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
=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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.