EXCEL:オートフィルタの抽出結果を反映させる

質問

EXCELで顧客管理をしています。
オートフィルタで８月に問合せを頂いた顧客だけを抽出し、
受注数／問合せ数→受注率　で
８月分だけの受注率を出したいと思っています。

別シートに上記の受注率の計算式だけ入れておいて
オートフィルタでの抽出結果が自動的に反映されるようにしたいのですが、
どういった形でデータを参照させれば良いのかがわかりません。
そもそもそういったことは可能なのでしょうか？

お詳しい方のご教授をお待ちしております。
どうぞよろしくお願いいたします。

回答No.2

noname#52504 · ベストアンサー

可能かどうか、ということであれば「おそらく可能」とお答えしますが、
具体的にどうすればよいか、となると情報が不足しています。

○ある月の「問合せ数」＝抽出後の行数　でよいのか？
　それとも、抽出後のデータのいずれかの列で何らかの判定をしてカウントする必要があるのか？
○ある月の「受注数」は既に判っているのか？
　それとも、抽出後のデータのいずれかの列で何らかの判定をしてカウントする必要があるのか？

一般論として、
オートフィルタで抽出した結果を数式で返す値に反映させるには、
SUBTOTAL関数を使うのがセオリーです。

例えば、
抽出後の行数を取得するには、
「（顧客名列など）必ずデータが入力されている列」を対象に
COUNTA関数に対応するSUBTOTAL(3)を使います。

ただし、
SUBTOTALにはCOUNTIFに対応する機能がありませんから、
抽出後のデータについてさらに(受注の有無など)判定を行うには、
あらかじめ元データに作業列を設けて、
抽出の結果及びその他の判定の結果に応じて値が変化するようにしておく必要があります。

以下、例として、
・元シートをSheet1
・元シートの第３行～第999行までが(見出し行を除く)データ範囲である
・元データのB列には必ずデータが入力されている
・ある月の「問合せ数」＝抽出後の行数である
・受注のあった案件についてはC列に"受注"と記載されている
・作業列を元シートのZ列に設ける
とします。

抽出前の状態で、元シートのZ3セル：
　=IF(SUBTOTAL(3,B3)=0,"",IF(C3="受注","受注",""))
としてフィルしておけば、オートフィルタで抽出した際に、
抽出後残った行で、かつ、受注のあった行についてのみ"受注"と入りますから

別シートの任意のセルで、
　=COUNTIF(Sheet1!Z3:Z999,"受注")/SUBTOTAL(3,Sheet1!B3:B999)
とすれば、抽出後の「受注数／問合せ数」の値が返ります。

(Excel2003で動作確認済)

お礼

ご丁寧にありがとうございます！
大変参考になりました！

回答No.3

imogasi

>受注数／問合せ数→受注率　で/・・
受注数、問合せ数はどうやって出すの。データはどういう風になっているの。
模擬データも載せないで質問して、判らないじゃない。
自分の会社の仕事ことは、読者に判らない。
ーー
＞別シートに
ＳＵＢＴＯＴＡＬ関数には注目して、やってみたの。
例データ
分類 係数
a 1
b 2
a 3
c 4
b 5
a 6
a 1
c 2
b 3
a 1
c 2
Ｓｈｅｅｔ２に
=SUBTOTAL(9,B2:B12)
や
=SUBTOTAL(2,B2:B12)
を入れると
オートフィルタでＡ列を選択すると、それぞれ１２，５になって
選択の結果が反映されるようだが。
SUBTOTAL関数には、第１引数に１－１１まで種類があり、
ＷＥＢ照会でもして調べてください。
それらの利用をやってみて、出来ない結果の質問かな。

回答No.1

mu2011

次の様な事ならば、オートフィルタは不要ですが如何でしょうか。
仮に履歴表（仮にシート１）に日付（Ａ列：内部シリアル値）、受注有無（Ｂ列：文字列の有無）の情報があったとします。
(1)８月の問合せ件数は、=SUMPRODUCT((MONTH(sheet1!A2:A100)=8)*1)
(2)８月の受注件数は、、=SUMPRODUCT((MONTH(sheet1!A2:A100)=8)*(sheet1!B2:B100="有"))
よって、受注率は、=SUMPRODUCT((MONTH(sheet1!A2:A100)=8)*(sheet1!B2:B100="有"))/SUMPRODUCT((MONTH(sheet1!A2:A100)=8)*1)

お礼

なるほど！ありがとうございます！

補足

ありがとうございます。
オートフィルタを使わないやり方も考えたのですが、
自分が使うのではなく、エクセルに詳しくない人が使うので
操作の中で数式をいじることはひかえたいと考えています。

オートフィルタではやっぱり無理なんでしょうか？