関数の中に関数を入れたいが…

質問

添付図をご覧ください。

[ふりがなの設定]を“全角カタカナ”にして、
セル A3 に式 =PHONETIC(A1) を入力して、此れを右方にズズーッとドラッグ＆ペーストしています。
セル C7 に全角カナで入力した後で、セル B7、D7 に夫々下記の式を入力しています。
B7: =SUMIF(A3:E3,C7,A2:E2)
D7: =SUMPRODUCT((A3:E3=C7)*(A2:E2))

そこで、上式中の「A3:E3」の場所に「PHONETIC(A1:E1)」の代入を試みたところ、
B7 では「入力した数式は正しくありません」と叱られ、
D7 では叱られはしなかったけど、戻り値が 28 でなく 0 になってしまいました。

なぜ B7 で叱られ、D7 では 0 になってしまうのか、教えてください。

ちなみに、Excel 2002 で試した結果に基づいてお尋ねしています。

回答No.3

MackyNo1 · ベストアンサー

皆さんの回答に勘違いがあるようなので参考までに。

まずPHONETIC関数の戻り値ですが、基本はふりがな情報が返りますが、数式で表示された文字列などではふりがな情報を取得できません（数字の1なども同じです）。

またPHONETIC関数の引数を範囲指定した場合は、そのふりがな文字列を連結した結果を返します。
したがって、ふりがな情報のないデータの文字列（Officeクリップボードを使うかワードにコピー貼り付けしそのデータをエクセルに貼り付け戻す）を連結するのに使用することができます。

しかし、関数で取得した文字列は上述のようにふりがな情報はありませんので、ご質問の期待した結果を得ることはできません。

次にSUMIFやCOUNTIF系の関数では、引数の範囲部分は配列を含めて指定できない関数です。
したがって、ご質問のような通常の範囲以外のデータを入力するとエラー表示が出るわけです。
一方、MATCH関数などは範囲の部分に配列を代入することができます（関数によって使える場合と使えない場合がありますので試行錯誤で確認するしかありません）。

さらにSUMPRODUCT関数でエラーが出るのは数式の範囲の一部にエラー値が入っている場合とA2:E2に数値以外が入っている場合だけですので、例示の数式では「0」が返ることになるわけです（PHONETIC(A1:E1)=C7の配列がすべてFALSEになるため）。

お礼

懇切丁寧なご説明、お蔭様で納得することができました。
誠にありがとうございました。

回答No.2

bunjii

>B7 では「入力した数式は正しくありません」と叱られ、
SUMIF関数の第1引数の範囲にPHONETIC関数を使えないのが原因でしょう。

>D7 では叱られはしなかったけど、戻り値が 28 でなく 0 になってしまいました。
SUMPRODUCT関数の引数としては有効ですが、(PHONETIC(A1:E1)=C7)→("アンズアンズアンズアンズアンズ")=アンズ)*1→0となりますので、0*A2:E2→{0,0,0,0,0}の合計は0になります。

回答No.1

chie65536（@chie65535）

＞なぜ B7 で叱られ、D7 では 0 になってしまうのか、教えてください。

「PHONETIC(A1:E1)」の結果は

"アンズアンズアンズアンズアンズ"と言う１５文字の文字列

です。

SUMIFの最初の引数には「文字列ではなく、セルの範囲を指定しなければならない」ので

=SUMIF(PHONETIC(A1:E1),C7,A2:E2)

は

=SUMIF("アンズアンズアンズアンズアンズ",C7,A2:E2)

と書いたのと同じになりますから、エラーになります。

一方

=SUMPRODUCT((PHONETIC(A1:E1)=C7)*(A2:E2))

は

=SUMPRODUCT(("アンズアンズアンズアンズアンズ"=C7)*(A2:E2))

と書いたのと同じになりますから、結果は0になります。

試しに、C7に「アンズアンズアンズアンズアンズ」と入力してみましょう。SUMPRODUCTの方の結果が28になる筈です。

要点は

PHONETIC(A1:E1)の結果は、配列ではなく、単なる１つの文字列になる

と言うことです。

補足

》 SUMIF関数の第1引数の範囲にPHONETIC関数を使えないのが原因でしょう
まぁ、それで納得することにします。(^_^)

しかしながら、SUMPRODUCT関数の場合を考えてみます。
私の添付図の１行目の左端(A1)から、全角アルファベット
Ａ、Ｋ、Ｕ、ｉ、ｓ
を入力します。
次に、セル A3 に式 =MOD(CODE(A1),10) を入力して、此れを右方にズズーッとドラッグ＆ペーストします。(3行目はすべて数値の 5 )

このとき、次式の戻り値は当然 28 になります。
=SUMPRODUCT((A3:E3=5)*(A2:E2))

そこで、上式中の「A3:E3」の場所に「MOD(CODE(A1:E1),10)」を代入した式
=SUMPRODUCT((MOD(CODE(A1:E1),10)=5)*(A2:E2))
の戻り値は、私の期待通り 28 を呈します。
此方のSUMPRODUCTは期待通りで、質問の場合のSUMPRODUCTは期待外れなのはなぜなのでしょうか？