SUM関数の謎[Excel]

SUM関数のナゾ

akashi-keirin.hatenablog.com

に、id:mmYYmmdd さん(勝手に脳内で「ヤマダさん」と呼んでいるw)からコメントをいただいた。曰く、

=SUM(1/COUNTIF(範囲,範囲)) だと求める値にならないけど、中身の「1/COUNTIF(範囲,範囲)」をF9で確定してやるとSUMのままでも求める値になってくれるのが不思議

とのこと。

えっ? そうなの?

と思いつつ、「F9で確定してやるとSUMのままでも求める値になってくれる」というのがよく分からなかったので調べてみた。

[F9]キーの挙動

Excelで[F9]といえば、「再計算」ぐらいしか印象がなく、かつて使ったこともほとんどなかったんだが、コチラによると、

Excelの「F9」キーは通常「再計算実行」の機能ですが、数式バーで数式を選択して押すと、数式の計算結果を表示できます。この機能を使えば、数式の計算結果を確認したり、計算した値に置き換えることができます。

とのこと。へえ、知らんかった。

SUMPRODUCTをSUMに置き換えてみる

id:mmYYmmdd さんのコメントの通りのことをやってみる。

SUMPRODUCTをSUMにしてみる

まずは、単純に数式の「SUMPRODUCT」をTRF「SUM」に置き換えてみた。

f:id:akashi_keirin:20180321084847j:plain

なるほど。「1」が返っている。全然ダメじゃん。

数式バー内で[F9]してみる

今度は、数式バー内で[F9]を押して「数式の計算結果を表示」してみる。

f:id:akashi_keirin:20180321084855j:plain

このように数式バー内にカーソルを置いて、[F9]をポチッ!

f:id:akashi_keirin:20180321084905j:plain

ぬな!?

確かに「11」になっとる!

これはもう、サッパリ何が何だか分からない。

数式の検証

ここで、今までほとんど使ったことがない「数式の検証」機能を投入。

f:id:akashi_keirin:20180321084914j:plain

f:id:akashi_keirin:20180321084923j:plain

f:id:akashi_keirin:20180321084933j:plain

f:id:akashi_keirin:20180321084942j:plain

f:id:akashi_keirin:20180321085002j:plain

f:id:akashi_keirin:20180321085031j:plain

と、このような過程で計算しているらしい。

わけわからんのが2枚目。

f:id:akashi_keirin:20180321084923j:plain

こいつ。なんで

=SUM(1/COUNTIF(D2:D15,D2:D15))

の次が

=SUM(1/COUNTIF($D$2:$D$15,"林冲"))

なのだろうか。

はっ! まさか、COUNTIFの第2引数が勝手に「D列の数式を入れた行のセル」に読み替えられているのか?

そこで、数式を1行目に移してみた。

f:id:akashi_keirin:20180321085040j:plain

ぬな!?

エ、エラー???

再度「数式の検証」を実行してみる。

f:id:akashi_keirin:20180321085050j:plain

COUNTIFの第2引数が「#VALUE!」エラーになっとる。

マジでわけわからん。

ちなみに、この場合でも

f:id:akashi_keirin:20180321085102j:plain

この状態で[F9]を押すと、

f:id:akashi_keirin:20180321085111j:plain

やはり期待通りの値に置き換わる。

おわりに

本当にこれはいったいどういうわけなのか???

@akashi_keirin on Twitter