Excelの表計算の方法（各セルの自然対数の合計と分数の合計の求め方）

質問

A　B　C　D　E
１　０　２　１　３

まず、上記の表で、D1はA1からC1までの合計です。
ここでE１で
＝　-A1/F1*LN(A1/F1)-B1/F1*LN(B1/F1)-C1/F1*LN(C1/F1)
という特殊な演算（詳しくはエントロピーの計算です）をしたいと思います。
以下詳細な説明です。

A1/F1　→　分子が各セル、分母が合計
LN(A1/F1）　→　上記の自然対数を求めます
-A1/F1*LN(A1/F1）　→　上の二つを掛けて、負にします
-A1/F1*LN(A1/F1)-B1/F1*LN(B1/F1)-C1/F1*LN(C1/F1)　→　行全ての合計

計算式は上記であっているのですが、２つ問題があります。
・実際は列が多く、各セルの計算式を上記のように書くことはできません。まとめて関数を記述することはできないでしょうか？
・自然対数の計算（＝LN(値)）は、値に０が入ると、エラー（＃NUM!)
が出てしまいます。上記の計算式では、＃NUM!が起こります。上手く値の０は飛ばして行全体の合計を出すことはできないでしょうか？

以上、ご教授の方、よろしくお願い致します。

回答No.4

noname#52504

ご質問の趣旨を取り違えていましたらすみません。
素朴に配列数式でもいけるように思います。

　E1：　=SUM(IF(A1:C1=0,0,-(A1:C1/F1)*LN(A1:C1/F1)))

を配列数式として入力。
※通常の数式は、数式を入力した後Enterキーで確定しますが、
　これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

略解
　A1からC1の各セルの値Xについて、
　X=0ならば0を、でなければ-(X/F1)*LN(X/F1)　を返した配列の和を返す。

或いは、LN(1)=0であることを利用して、

　E1：　=SUMPRODUCT(-(A1:C1/F1)*LN((A1:C1=0)+(A1:C1<>0)*A1:C1/F1))

とする方法もあります。こちらはEnterキーのみで確定してOKです。

略解
　A1からC1の各セルの値Xについて、
　-(X/F1)*LN(【Xが0ならば1，Xが0でなければX/F1】))　を返した値の和を返す。

A1～C1の値が0でない場合に質問文の数式を同じ値が返ることは確認しました。
0を含む場合の解釈があっているかどうか…。

回答No.3

ka_na_de

ka_na_deです。

２番目の回答では、
データが複数行あると仮定して説明しました。

もし、１行だけでよいのなら、
１枚のシートで簡単にできます。

その場合は補足してください。
また、説明します。

回答No.2

ka_na_de

関数の方法を紹介します。

１）ファイルを新規作成して、シートを６枚用意してください。
　　Sheet1～Sheet6として説明します。
２）Sheet1に元のデータを貼り付けてください。
　　（見出し行なしで説明します。）
　　（合計の列は不要です。）
３）Sheet2に合計を計算します。
　　Sheet2のA1セルに
　　=SUM(Sheet1!1:1)
　　と入力し、以下コピー＆ペースト
４）Sheet3に割合？を計算します。（各値／合計）
　　Sheet3のA1セルに
　　=Sheet1!A1/Sheet2!$A1
　　と入力し、以下コピー＆ペースト
　　その後、列方向にもコピー＆ペースト
５）Sheet4に対数を計算します。
　　Sheet4のA1セルに
　　=IF(Sheet3!A1=0,0,LN(Sheet3!A1))　　
　　と入力し、以下コピー＆ペースト
　　その後、列方向にもコピー＆ペースト
６）Sheet5に（各値／合計）＊対数（各値／合計）＊(－1）を計算
　　Sheet5のA1セルに
　　=Sheet3!A1*Sheet4!A1*(-1)　
　　と入力し、以下コピー＆ペースト
　　その後、列方向にもコピー＆ペースト
６）Sheet6に　行すべての合計を計算します。
　　Sheet6のA1セルに
　　=SUM(Sheet5!1:1)
　　と入力し、以下コピー＆ペースト

以上です。
Sheet1の元データは、
EXCEL2003では、65536行、256列まで可能です。
EXCEL2007では、もっといけるようです。

尚、シート名は、式をすべて入力後に、
シートタブ上で右クリックして変更すれば、
式のシート名も自動で変更できます。
必要であれば、適当に分かりやすい名前にしてください。

また、途中の計算シートが邪魔であれば、
シートを選択し、
「書式」→「シート」→「表示しない」
とすることもできます。

お礼

Sheet別に計算する、というのは思いつきませんでした。
ありがとうございます！！
試してみます！
補足など細かい配慮までありがとうございました。

回答No.1

kigoshi

F1セルの内容が不明ですが、
「D1はA1からC1までの合計」を「F1はA1からC1までの合計」
と読み替えて回答いたします。

まず　#NUM!　への対処法ですが、一般的にはiserr関数とif関数の組合せで対処する
のが常套かと思います。
例）
=IF(ISERR(LN(A1)),0,LN(A1))
LN(A1)がエラー値のときには0を返します。

それと長い式への対応ですが、これは隠しセルをたくさん使うか、ユーザー定義関数を
用いるしかないと思います。
隠しセルを使用する場合は、実際の事例により対処が異なりますが、基本的にはたとえ
ば、GA、GB、GCセル（それぞれ183、184、185列目）を使って
GA=-A1/F1*LN(A1/F1)
GB=-B1/F1*LN(B1/F1)
GC=-C1/F1*LN(C1/F1)
などとし、GA、GB、GCを使って値を求める、といったやりかたです。

最後、ユーザー定義関数を用いる方法ですが、ご質問の事例をコード化しました。

Function fENT(rIdx As Long, cIdx As Integer) As Double
Application.Volatile
Dim AX, BX, CX, FX As Double
If cIdx < 5 Then
fENT = 0
Exit Function
End If
AX = Val(Cells(rIdx, cIdx - 4).Value)
BX = Val(Cells(rIdx, cIdx - 3).Value)
CX = Val(Cells(rIdx, cIdx - 2).Value)
FX = AX + BX + CX
fENT = 0
If AX <> 0 Then fENT = fENT - AX / FX * Log(AX / FX)
If BX <> 0 Then fENT = fENT - BX / FX * Log(BX / FX)
If CX <> 0 Then fENT = fENT - CX / FX * Log(CX / FX)
End Function

１）[Alt]+[F11]を押す。
２）左側のプロジェクトエクスプローラーのVBAProjectを右クリックし
　　[挿入]→[標準モジュール]
３）右側のエディタエリアに上のコードを貼り付け

以上でシート上からfENT関数を使えるようになります。
使い方はE1セルに=fent(ROW(),COLUMN())と入れて下さい。

あとは上記コードを書き換えることで対応することになるのですが、その方法については
別途ご質問頂いた方がよろしいかと思います。

お礼

ありがとうございます！
隠しセルについては初めて知りました。
試してみます！