SUBTOTALとCOUNTIFを混ぜた関数について

ExcelにてSUBTOTALとCOUNTIFの機能を併せ持った関数が無いのかと先日インターネットで調べていたところ、以下のような関数が出てきました。

SUMPRODUCT(SUBTOTAL(3,OFFSET(M8,ROW(INDIRECT("1:"&ROWS(M9:M100))),))*(M9:M100="処理済"))

この関数の解説をお願いしたく、投稿した次第です…。

各関数については既に調べ、基本的な使い方を把握しましたが、こうズラッと並ばれると何が何をどうしているのかが分からなくなります。

どうかExcel初心者の私にもこの関数の意味を理解させていただけないでしょうか？
例えばこれを無理やり日本語にするとどうなるのでしょう。

以上、どうか助力願います。

No.1 回答者： fujillin 回答日時： 2018/03/08 13:12

こんにちは

＞調べていたところ、以下のような関数が出てきました。
そこに、何を求めている関数式であるかの解説はなかったのでしょうか？

＞各関数については既に調べ、基本的な使い方を把握しました
基本的に、各関数の内容さえわかれば、あとは丹念に考えればわかるはずです。

＞ズラッと並ばれると何が何をどうしているのかが分からなくなります。
いきなり全部を把握しようとせずに、順に部分を解釈して、それを組み合わせてゆけば良いと思います。
例えば、ご提示の式を分解すると以下のようになりますので、内側に入っている式から順に解釈して意味をメモしておいて、その外側の関数を解釈する際には、内側の式ではなくメモした意味で考えるようにすれば、わかってくるのではないかと思います。

ただし、ご提示の式の場合、全体がSUMPRODUCT関数なので、部分でセル範囲となっているところも、それはSUMPRODUCTで計算するための範囲であるということを忘れないようにしておく必要があります。（実際には、セル範囲は2か所だけですが）

セル範囲が使われていなければ、実際のシートで内側から順にセルに関数を設定して、外側の関数はそのセルを参照するようにして、複数セルに分割して算出してみるような方法も考えられますが、今回はSUMPRODUCTなのでちょっとやりにくいですね。
（私は、長い関数式を組み立てる際には、上記を応用して、内側から式を組んでセルの値を確認しながら少しずつ関数を組み合わせていったりしています）


ご提示の式を分解してみた例です

SUMPRODUCT(
　　SUBTOTAL(
　　　　3, OFFSET(
　　　　　　M8, ROW(
　　　　　　　INDIRECT(
　　　　　　　　　"1:" & ROWS(M9:M100)
　　　　　　　)
　　　　　　),
　　　　)
　　) *
　　(M9:M100="処理済")
)