業務に役立つExcel COUNTIFSにSUBTOTAL関数を追加

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存
  • 0

業務に役立つExcelで、COUNTIF関数SUBTOTAL関数を追加し、さらにCOUNTIFS関数SUBTOTAL関数を追加するまで昇華させます。今回はちょっとマニアックですが、知っていると大変便利な関数複数混合式を紹介します今回もDLファイル付です。

はじめに

SUBTOTAL関数は非表示(非可視)セルは計算されずに、表示(可視)セルのみ計算してくれる大変便利な関数です。おそらく日常SUBTOTAL関数を使用される方は、ファイルにオートフィルタをつけて、特定列の項目をフィルタでくくって、その項目だけの合計を見るのに使用されているのではないでしょうか?

大変便利な SUBTOTAL関数ですが、この関数は数字の合計や平均あくまで数字の計算にしか使用出来ない関数です。
セルに入力された個数を計算するのがCOUNTIF関数で、こちらも頻繁に使用される関数です。
ですが残念な事にこのCOUNTIF関数には、フィルタでくくった表示(可視)セルのみといったSUBTOTAL関数のような機能ななく、非表示(非可視)セルも計算されてしまいます。
それで今回ご紹介する複合関数が、そのCOUNTIF関数SUBTOTAL関数の機能を追加した複合関数です。さらに今回は複数条件のCOUNTIFS関数SUBTOTAL関数を追加するまで昇華させます。

複数条件のCOUNTIFS関数にSUBTOTAL関数の機能を追加

サンプルファイル(画像は全て拡大します)

SUBTOTAL機能①

以前の、重複データ最新のみを抽出する関数。で使用したファイルを今回用に改造して使用しております。

A列が手入力欄で、販売日を入力する列になっています。

B列とC列は一応リスト選択にしました。I列とJ列にリストデータが入れてあります。

実は、このリスト。知っているようで知らない方がけっこう多い(うちの会社では誰一人知りませんでした。(笑))B列リストで選択した項目に該当するC列リストのみ抽出表示されてくれる大変便利なExcel技ですが、それはまた別の記事でご紹介したいと思います。

C列は販売金額を手入力する列です。

そして、今回ご紹介する重複データの最新のみを抽出する関数がD列に入っています。

そして、E列は前回ご紹介した最新を抽出したデータのみの金額を関数で抽出してあります。

そちらは前回の記事をご参照下さい。

業務に役立つExcel重複データ最新のみを抽出する関数。DL付。
業務に役立つExcelで、重複するデータの中から最新のみを抽出してくれる(重複しない場合はそのデータを抽出)大変便利な関数複数混合式を紹介し...

そして、F列はD列と、F列をCONCATENATE関数で一つのセルにして、わかりやすくしてみました。

そして肝心の今回「COUNTIFS関数にSUBTOTAL関数の機能を追加」された複合関数式が入っているのがこちら。

SUBTOTAL機能②

では、関数式をご紹介します。

COUNTIF関数にSUBTOTAL関数の機能を追加、関数の紹介

COUNTIF関数にSUBTOTAL関数の機能を追加、関数の紹介

まず、単数条件の「COUNTIF関数にSUBTOTAL関数の機能を追加」から見ていきましょう。

この式で、前半SUBTOTALの機能をSUMPRODUCT関数を複合させる事でCOUNTIFへ追加付加しております。COUNTIFの選択範囲はC列 $C$3:$C$38 3行目から38行目までで、エアコンBが、COUNTIFの対象項目です。

COUNTIFS関数にSUBTOTAL関数の機能を追加、関数の紹介

そして今回の目玉、複数条件の「COUNTIFS関数にSUBTOTAL関数の機能を追加」がこちら。

ここがポイントロゴ

icon-check-square-o SUMPRODUCT関数の本来の計算式

を付加させる事により、単数条件のCOUNTIF関数から複数条件のCOUNTIFS関数へSUBTOTAL関数の機能を追加する事へ昇華させているわけです。

この計算式だと追加した、”白物家電”へはSUBTOTAL関数の表示(可視)セルのみ計算という機能は不可されておりませんが、最終計算される、”エアコンB”に表示(可視)セルのみ計算機能が付加されている為、結果複数条件でも表示(可視)セルのみ計算の条件をクリアしたわけです。

ここがポイントロゴ

icon-check-square-o さらに、もし複数条件の列が増えた場合でも

というように複数条件を増やす事も容易です。素晴らしい応用力だと思います。

SUBTOTAL機能③

フィルタでくくってみたところです。

見事に、複数条件COUNTIFS関数を満たした、SUBTOTAL関数の機能が追加させて出力されました。

ダウンロードファイル

“ExcelファイルCOUNTIFSにSUBTOTAL関数を追加” をダウンロード SUBTOTALCOUNTIFS.xlsx – 830 回のダウンロード – 17 KB

  • ご自身で使用されているファイルへご使用されるならば、この記事を参考に計算式をコピペして使用して下さい。
  • もちろんダウンロードして頂いたファイルのデータベースシートもそのままご利用になれますので、ご活用下さい。

さいごに

日常、ファイルへ例えば、売り上がった商品をどんどん追加していく場合等、フィルタをかけて、項目ごとでの集計を頻繁にする場面があると思います。

そういった業務をされている方へは、大変利便性が高い関数式に仕上がっていると思います。

もちろん、こういったケースのデータベースには何にもで応用出来ますので、活用する幅は多岐にわたります。日頃の業務の大幅な効率化を図れる事は間違いありません。

もちろん、関数を知っていても、活用出来るか、否かは皆さま次第です。

是非、日頃の業務にお役立て頂ければ幸いです。

以上、業務に役立つExcel COUNTIFSにSUBTOTAL関数を追加。DL付。でした。

ご視聴ありがとうございました。

合わせて読みたい

業務に役立つExcelデータを列と行で抽出。INDEXとMATCH関数。DL付。
業務に役立つExcelで、作成したデータベースから行と列でピンポイントにデータを抽出してくれる大変便利な関数( INDEX関数とMATCH関...
業務に役立つExcel指定範囲をVBAで一括削除。DL付。
業務に役立つExcelで事前に選択した範囲の値を、マクロボタン一つで自動で削除してしまう計算式をご紹介します。DLファイル付です。
業務に役立つExcel選択範囲に一瞬で罫線。DL付。
業務に役立つExcelで任意に選択した範囲へ罫線をマクロボタン一つで自動で挿入してしまう計算式をご紹介します。DLファイル付です。
業務に役立つExcel任意行ごとに空白行自動挿入。DL付。
業務に役立つExcelで任意に指定した行数ごとにマクロボタン一つで自動で空白行を挿入していく計算式をご紹介します。DLファイル付です。
業務に役立つExcelデータベースを自動で仕訳し抽出。DL付。
業務に役立つExcelで作成したデータベースを、自動で仕訳し別シートへ抽出させる計算式をご紹介します。DLファイル付です。
業務に役立つExcelファイル会計年度計算の関数式。DL付。
業務に役立つExcelのファイリングで会計年度計算の関数式です。 DLファイル付きです。
業務に役立つExcelで検索エンジンVBAを5分で搭載。DL付。
業務に役立つExcelのデータベースファイリングに、VBAで作成した検索エンジンを5分で簡単に搭載出来ます。サンプルDL付です。

スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存

フォローする


スポンサーリンク

トップへ戻る