ExcelにはPOISSONというワークシート関数があります。
この使い方です。
例えば次のようの問題があったとします。
「ある交差点では1分間に車が平均で5台通過します。このとき1分間に3台通過する確率を求めなさい。」
時間帯によって平均台数が変動するようなケースではダメですが、一定であればPOISSON関数を使うことができます。
POISSON関数は次のように使います。
POISSON(確率を求めたい回数,平均回数,FALSE)
上の問題の場合は「=POISSON(3,5,FALSE)」とすることで求められます。
結果は「0.1404」となります。これが「3回通過する確率」です。
蛇足。
同じように「0回通過する確率」は「0.0067」となります。
これを「16回通過する確率」まで計算して合計すると次の通り「1.0000」になります。
| 回数 |
確率 |
| 0 |
0.0067 |
| 1 |
0.0337 |
| 2 |
0.0842 |
| 3 |
0.1404 |
| 4 |
0.1755 |
| 5 |
0.1755 |
| 6 |
0.1462 |
| 7 |
0.1044 |
| 8 |
0.0653 |
| 9 |
0.0363 |
| 10 |
0.0181 |
| 11 |
0.0082 |
| 12 |
0.0034 |
| 13 |
0.0013 |
| 14 |
0.0005 |
| 15 |
0.0002 |
| 16 |
0.0000 |
| 合計 |
1.0000 |
Excelには「VLOOKUP」というワークシート関数があります。
VLOOKUP(検索値,範囲,列番号,検索の型)
このように引数に検索値を指定します。検索値に指定できる値は一つだけです。
そこで、検索値としてカンマ区切りなどで複数の値を指定して検索するユーザー定義関数「XLOOKUP」を作ってみました。
検索結果が複数ある場合、カンマ区切りの文字列が返されます。
VBA
Function XLOOKUP(mystr As String, myrange As Range, mycol As Long, Optional delimiter = ",")
Dim x As Long
Dim t, parts() As String
Dim one As Variant
parts = Split(mystr, delimiter)
t = ""
For x = 1 To myrange.Rows.Count
For Each one In parts
If myrange.Cells(x, 1).Value = one Then
If t <> "" Then
t = t & delimiter
End If
t = t & myrange.Cells(x, mycol).Value
Exit For
End If
Next
Next x
If t = "" Then
t = "#N/A"
End If
XLOOKUP = t
End Function
書式
XLOOKUP(検索値,範囲,列番号,区切り文字)
検索値 範囲の左端の列で検索する値を指定します。
範囲 2列以上のデータ列です。範囲を指定します。範囲の左端の列の値が、検索値で検索される値です。
列番号 範囲内で目的のデータが入力されている列を、左端からの列数で指定します。列番号に1を指定すると、範囲の左端の列の値が返され、列番号に2を指定すると、範囲の左から2列目の値が返されます。
区切り文字 検索値として「a,b」や「x/y/z」など区切り文字を含んだ文字列を指定することができます。省略可能です。省略した場合「,」が指定されます。
※VLOOKUPと違い、検索の型は指定しません。FALSEに似た動作をします。
使用例
次のような範囲について、数式をセットします。
|
A |
B |
| 1 |
code |
name |
| 2 |
a |
Apple |
| 3 |
b |
Banana |
| 4 |
c |
Chocolate |
| 5 |
d |
Donut |
| 数式 |
結果 |
| =XLOOKUP("c",$B$2:$C$6,2) |
Chocolate |
| =XLOOKUP("a,b",$B$2:$C$6,2) |
Apple,Banana |
| =XLOOKUP("a/c/d",$B$2:$C$6,2,"/") |
Apple/Chocolate/Donut |
春分の日は今年は3月20日でしたが年によって変わります。
これを計算で求める方法があります。
春分の日 - Wikipedia
1900年から2099年までの求め方が載っています。
これをJavaScriptで書くと次のようになります。
function shunbun(y){
if(y<1900||y>2099)return;
switch(y%4){
case 0:
if(y<=1956)return 21;
if(y<=2088)return 20;
return 19;
case 1:
if(y<=1989)return 21;
return 20;
case 2:
if(y<=2022)return 21;
return 20;
case 3:
if(y<=1923)return 22;
if(y<=2055)return 21;
return 20;
}
}
ちょっと長いので、もっと短い方法を考えます。
国立天文台が作っている「歴要項」というものがあります。
暦要項 PDF版 一覧
これを見ると春分の日の中央標準時が分かります。
2000年の場合、3月20日16時35分です。
この中央標準時が翌年以降、3月の何日になるかを考えます。
地球の公転周期は365.242190402日です。
つまり毎年、中央標準時が0.242190402日(5時間48分45秒に相当)ずつ遅くなります。
2000年は20日16時35分でしたが、2001年は20日22時23分、2002年は翌日の21日4時12分となります。
どんどん日が後ろにずれていくのですが、閏年には1日戻りますので、20日から22日までの間に収まるというわけです。
これをJavaScriptで表わすと次の通りです。2000年から2099年までに対応します。
「20.6910」というのは上述の「20日16時35分」に相当します。
function shunbun_easy(y){
if(y<2000||y>2099)return;
var x=y-2000;
return Math.floor(20.6910+0.242190402*x-Math.floor(x/4))
}
秋分の日については次の通りです。
function shubun(y){
if(y<1900||y>2099)return;
switch(y%4){
case 0:
if(y<=2008)return 23;
return 22;
case 1:
if(y<=1917)return 24;
if(y<=2041)return 23;
return 22;
case 2:
if(y<=1946)return 24;
if(y<=2074)return 23;
return 22;
case 3:
if(y<=1979)return 24;
return 23;
}
}
同じく、簡易な式は次の通りです。
「23.09」のところは正しくは「23.1028」(23日2時28分に相当)なのですが、2012年などで、微妙なところで翌日になってしまう「誤差」があります。そこで便宜的に少し早めて「23.09」としています。
function shubun_easy(y){
if(y<2000||y>2099)return;
var x=y-2000;
return Math.floor(23.09+0.242190402*x-Math.floor(x/4))
}
JavaScriptで関数名を文字列で与えるとその関数が実行されるような方法を考えます。
次のような例で考えます。
- 「orange」ならば「ミカン」と表示する関数を実行。
- 「apple」ならば「リンゴ」と表示する関数を実行。
ifで振り分ける
オーソドックスにifで処理を振り分ける方法です。
一つずつif節を書くので、関数が増えると長くなってしまいます。
var word="apple";
function orange(){
alert("ミカン");
}
function apple(){
alert("リンゴ");
}
if(word=="orange"){
orange();
}else if(word=="apple"){
apple();
}
evalを使う
evalを使うと文字列があたかもコードを書いたかのように振る舞います。
これならば「eval(関数名+"()")」とするだけで実行されます。
振り分ける必要がないので関数が増えてもコードが長くなりません。
また、既存の関数を書き換える必要がありません。
var word="apple";
function orange(){
alert("ミカン");
}
function apple(){
alert("リンゴ");
}
eval(word+"()");
オブジェクトとして使う
JavaScriptの指南本にはよく「evalを使うな」と書かれています。見通しが悪くなってデバッグしにくくなるからでしょう。
関数を配列(オブジェクト)のようにして作り、文字列を与えて実行する方法です。
これも関数が増えてもコードが長くなりません。
ただし、既存の関数が「function 関数名()」という形式で書かれている場合は、書き直す必要があります。
var word="apple";
var func_obj=[];
func_obj.orange=function(){
alert("ミカン");
}
func_obj.apple=function(){
alert("リンゴ");
}
func_obj[word]();
ExcelのワークシートからGoogleの検索結果にハイパーリンクを張ることを考えていたところ、URLの一部として日本語をそのまま渡すのではなく、UTF-8でエンコードする必要があることに気付きました。
そこで、本来の目的とは違うのですが、VBAを使い、文字列をUTF-8でエンコードするユーザー定義関数を作りました。
例えば「あア亜」を「%E3%81%82%E3%82%A2%E4%BA%9C」に変換します。
JavaScriptだとescapeやencodeURIに相当します。
設定
VisualBasicエディタの設定をする必要があります。
ツール-参照設定-参照設定ダイアログで「Microsoft ActiveX Data Objects 2.8 Library」にチェックを入れます。
2.5以上であれば動くようです。
コード
Function encodeUTF8(mytext As String) As String
Dim mystream As New ADODB.Stream
Dim mybinary, mynumber
With mystream
.Open
.Type = adTypeText
.Charset = "UTF-8"
.WriteText mytext
.Position = 0
.Type = adTypeBinary
.Position = 3
mybinary = .Read
.Close
End With
For Each mynumber In mybinary
encodeUTF8 = encodeUTF8 & "%" & Hex(mynumber)
Next
End Function
JavaScriptで配列からHTMLのULを使ったリストを作るユーザー定義関数を作ってみました。
配列を与えるとレベルに応じて入れ子構造になったULリストを文字列で出力します。
function array2ul(a) {
var t = "";
var exlevel = 0;
var level;
var i, j;
for (i = 0; i <= a.length; i++) {
level = i < a.length ? a[i][0] : 0;
for (j = 0; j < Math.abs(exlevel - level); j++) {
t += exlevel < level ? "<ul>" : "</ul>";
}
t += i < a.length ? "<li>" + a[i][1] + "</li>" : "";
exlevel = level;
}
return t;
}
次のように使います。配列は[レベル,テキスト]という形で与えます。レベルは1から始めます。
var a = [
[1, "a"],
[2, "b"],
[3, "c"],
[3, "c"],
[2, "b"],
[3, "c"],
[3, "c"],
[1, "a"],
[2, "b"],
[3, "c"],
[3, "c"],
[2, "b"],
[3, "c"],
[3, "c"]
];
var ul = array2ul(a);
出力結果です。
1桁の数「5」に「0」を追加して2桁「05」で表示するような手法を「ゼロ埋め」や「パディング」などと言います。
Excelには「TEXT」、VBAには「Format」、PHPには「sprintf」という関数がありますが、JavaScriptには見当たりません。
これを作ってみます。 (さらに…)
PHPにはmb_convert_kanaという便利な関数があります。
全角と半角を相互に変換するだけでなくカタカナとひらがなを変換してくれます。 (さらに…)
Excelのワークシート関数の「DATEDIF」で引数に「MD」を使うと月未満の日数が計算できます。
バグがあることで有名ですが、具体的な不具合を挙げておきます。
月末から1日まで
明らかにおかしいのは終期を3月1日にした場合。値がマイナスになります。
DATEDIFのロジックはおそらく終期の前月の応当日から数えるようになっているようです。「2月31日から3月1日まで」というありえない日付で計算するのでこのような不具合が生じるのだと思います。 (さらに…)
ExcelのDATEDIF関数は今一つ挙動不審なので使わないようにしていたのですが、具体的なバグについて今頃、知りました。
使っていないから自分で気付くはずがないのですが。
このバグは有名なのだそうですが、修正されていません。
マイクロソフトはこれをExcelの正式なワークシート関数として取り扱っていないからでしょう。
確かにExcel2007のヘルプを見てもDATEDIFは見付かりません。 (さらに…)