Excel（エクセル）でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添

Excel（エクセル）でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法




【内容】
添付画像１のようなデータを作成しました。
　※関数＝COUNTIFS　C　7行目に　〇と△の合計をカウントしています。

【質問内容】
添付画像２のように担当者別でフィルタをかけた所、非表示の部分も計算されていまい
表示と合計が合わない状況です。

自力で検索いたしましたが、=SUMPRODUCT((SUBTOTAL(103,INDIRECT？を使用する関数まで
辿りついたのですが、知識不足もありうまく使い方がわかりません。

さらに条件が2つ（〇と△を合計してカウントしたい）あるので、より複雑で解決の糸口も見えないので
お詳しい方にお力をおかりしたいです。

No.4 ベストアンサー 回答者： 銀鱗 回答日時： 2023/01/30 18:15

無理でしたね。

オートフィルタを使った後、数を求めたい列のフィルタに対して、
「カスタム フィルター」を設定して ”〇” ”△” の値を持つ行に絞り込み。
そのセルの数を数える。

または、初めからSUMPRODUCT関数で条件を絞り込んで求めることになります。

SUMPRODUCT関数では、まず１つ目にオートフィルタを掛けたい値と同じであることを条件として設定します。
2つ目に値が ”〇” または ”△” であることを条件にします。
この２つをSUMPRODUCT関数内で掛け算するだけで結果を得られます。

　＝SUMPRODUCT((範囲＝フィルタ条件) * (範囲＝"〇") + SUMPRODUCT((範囲＝フィルタ条件) * (範囲＝"△")

こんな感じ。
（SUMPRODUCT関数内で OR条件は作らないほうが分かりやすいので
　別々にして足し算することを勧めています）

この回答へのお礼

ありがとうございました！！
頑張ってみます！！！

お礼日時：2023/01/30 18:21

No.3 回答者： fujillin 回答日時： 2023/01/30 18:10

こんにちは

＞非表示の部分も計算されていまい表示と合計が合わない状況です。
SUBTOTAL関数等は非表示の行を計算から外すことはできますが、あくまでも、直接セル範囲を合計したりカウントしたりする場合にしか利用できません。
どうしても「非表示の行を計算から外す」という考え方で計算したいのなら、計算内容を別列に表示できるようにしておいて、そちらを集計するようにすれば可能です。

例えば、「〇の数」を計算したければ、作業列に
　=(対象列のセル="〇")*1
を入力しておき、この列に対してSUBTOTALを行えばカウントできます。
"〇"の部分を別セルの値で指定できるようにしておけば、計算対象を〇、△、×等に変えることも可能でしょう。
とは言え、ご提示の表では対象が10列ほどあるようなので、作業列も10列必要になってしまいます。


「関数でフィルターをかける」というように発想を変えても良ければ、作業列を用いずに直接集計が可能です。
同様に「Aさんの〇」を数えるのであれば、
　=SUMPRODUCT((氏名列のセル範囲="Aさん")*(対象列のセル範囲="〇"))
で、計算できます。
上式の、"Aさん"の部分を入れ替えたり、別のセルの値で指定するようにしておけば、フィルター操作と同様の効果を得ることができるでしょう。
（操作方法が変わってしまいますけれど・・）

※ 関数でフィルターをかけていますので、手操作のフィルターとは無関係に計算します。
　（＝シート上の表示/非表示とは無関係に計算するという意味です）
※ 365環境などのFILTER関数が使用できる環境であれば、そちらを用いることで、もう少しわかりやすい式になると思います。

No.2 回答者： 銀鱗 回答日時： 2023/01/30 17:51

ああ。

使っていらっしゃいましたか。失礼しました。

SUBTOTAL関数にはCOUNTIFに相当するものはありませんので、
ちょいと工夫が必要ですね。

確認してみますので少しお待ちください

No.1 回答者： 銀鱗 回答日時： 2023/01/30 17:46

「SUBTOTAL関数」を使いましょう。

それで解決する問題ですね。

※ SUBTOTAL関数の使い方を確認してください。