You Look Too Cool

ゆるくつくる stabuckyのブログ

POISSON関数の使い方

ExcelにはPOISSONというワークシート関数があります。
この使い方です。
例えば次のようの問題があったとします。 (さらに…)

複数の値で検索するユーザー定義関数「XLOOKUP」

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]();

文字列をUTF-8でエンコードするユーザー定義関数

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);

出力結果です。

JavaScriptでゼロ埋めする関数

1桁の数「5」に「0」を追加して2桁「05」で表示するような手法を「ゼロ埋め」や「パディング」などと言います。
Excelには「TEXT」、VBAには「Format」、PHPには「sprintf」という関数がありますが、JavaScriptには見当たりません。
これを作ってみます。 (さらに…)

mb_convert_kanaのオプション

PHPにはmb_convert_kanaという便利な関数があります。
全角と半角を相互に変換するだけでなくカタカナとひらがなを変換してくれます。 (さらに…)

DATEDIFの日数計算の不具合

Excelのワークシート関数の「DATEDIF」で引数に「MD」を使うと月未満の日数が計算できます。
バグがあることで有名ですが、具体的な不具合を挙げておきます。

月末から1日まで

明らかにおかしいのは終期を3月1日にした場合。値がマイナスになります。
DATEDIFのロジックはおそらく終期の前月の応当日から数えるようになっているようです。「2月31日から3月1日まで」というありえない日付で計算するのでこのような不具合が生じるのだと思います。 (さらに…)

DATEDIFのバグ

ExcelのDATEDIF関数は今一つ挙動不審なので使わないようにしていたのですが、具体的なバグについて今頃、知りました。
使っていないから自分で気付くはずがないのですが。
このバグは有名なのだそうですが、修正されていません。
マイクロソフトはこれをExcelの正式なワークシート関数として取り扱っていないからでしょう。
確かにExcel2007のヘルプを見てもDATEDIFは見付かりません。 (さらに…)

新しい記事

タグ

Amazon API App DOT Google HTML iPhone JavaScript jQuery PHP tips Twitter VBA まとめ アルゴリズム カレンダー グラフ ダレナノ ブックマークレット プラグイン 一覧 中央区 九州豚骨 入門 八王子市 千代田区 味噌 地図 変換 家系図 数学 日付 日本語 東京駅 正規表現 町田市 町田駅 相模原市中央区 相模原市緑区 確率 神田駅 解決法 都道府県 配列 関数

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報