Excelで年齢を求める方法

Excelで年齢や経過年数を求める場面があります。
DATEDIFという関数を使えば求められますが、なぜかExcelはこれを標準の関数とはしていないようです。
以下、A1セルに生年月日、B1セルに基準日が入力されているとします。
このとき、C1セルに数式を入力します。 (さらに…)

Excelの列番号を英字に変換するユーザー定義関数

「(1,1)」のセルならば「A1」と表示したいのですが、これを行うには、1ならばA、2ならばB、27ならばAAというように、列を英字に変換する必要があります。
ヘルプを調べましたが見付からなかったのでユーザー定義関数を作ってみました。

Function num2xlcol(num)
    'エクセルの列番号を英字に変換
    Dim pos(3) As Long
    pos(1) = num Mod 26
    If pos(1) = 0 Then pos(1) = 26
    pos(2) = ((num - pos(1)) / 26) Mod 26
    If pos(2) = 0 Then pos(2) = 26
    pos(3) = (num - pos(2) * 26 - pos(1)) / 26 / 26
    If num <= 26 Then 'A-Z
        num2xlcol = chr(64 + pos(1))
    ElseIf num <= 702 Then 'AA-ZZ
        num2xlcol = chr(64 + pos(2)) & chr(64 + pos(1))
    ElseIf num <= 18278 Then 'AAA-ZZZ
        num2xlcol = chr(64 + pos(3)) & chr(64 + pos(2)) & chr(64 + pos(1))
    Else
        num2xlcol = "Error"
    End If
End Function

Excel2007だと「XFD」(16384列)までありますが、「ZZZ」(18278列)まで対応しています。

これを作った後、Googleで検索すると、既にありました。しかもマイクロソフトのサイトに。
Excel で列番号を英文字に変換する方法
手法は全く同じでした。ただし「ZZ」までしか対応していません。「AAA」以上を扱う場合は上で紹介した関数が有用です。

PHPのpreg_match_allをJavaScriptで再現

PHPにはpreg_match_allという関数があります。正規表現を使い、マッチする文字列を全て取得できます。括弧を使うとその部分も取得できます。テキストファイルやHTMLファイルからパターンに沿った文字列を簡単に取得できるのでとても便利です。

一方、JavaScriptにはmatchというメソッドがあります。gフラグを指定するとマッチする文字列を全て取得することができます。ただし括弧を使ってその部分を取得するということはできません。
そこで、preg_match_allを再現する方法を考えました。

PHPのpreg_match_allは「preg_match_all($pattern, $subject, $matches, $flags)」のように使います。
$patternは"/正規表現/フラグ"のように指定します。
$subjectは対象となる文字列です。
$matchesにマッチした文字列が配列として格納されます。
$flagsはマッチさせる順番です。詳細は後述します。
下はこれをJavaScriptで再現したものです。 (さらに…)

JavaScriptでExcelのRANK関数を再現

配列中の順位を調べる方法を考えました。
ExcelにRANK関数がありますので、これを再現してみます。

サンプルは下の通りです。
updownに従ってarrs内の数値を並べ替えたとき、valueが何番目に位置するかを返します。
updownは、省略または0は降順(最多が1位)、0以外は昇順(最少が1位)となります。 (さらに…)

VBAのユーザー定義関数で引数を省略

VBAのユーザー定義関数で引数を省略する方法です。
下に示す関数はaとbの二つの引数を与え、合計を返します。
引数に「Optional」を付けると省略することができます。また初期値を与えることができます。

Function kansu(a, Optional b = 0)
    kansu = a + b
End Function

例えば「=kansu(2,3)」とすると2+3で「5」を返します。
「=kansu(2)」のように2番目の引数を省略すると初期値の0を与えたことになりますので、2+0で「2」を返します。
初期値を与えなかったときは「IsMissing」で値がセットされたかどうか判定できます。セットされていない場合、TRUEになります。

日付をカウントするユーザー定義関数「COUNTDATE」

選択範囲の日付を条件に応じてカウントするユーザー定義関数を作ってみました。

VBA

Function COUNTDATE(hani As Range, nen As Long, tsuki As Long, hi As Long) As Long
    Dim v As Variant
    Dim ct As Long
    ct = 0
    For Each c In hani
        v = c.Value
        If IsDate(v) Then
            If (nen = 0 Or Year(v) = nen) And (tsuki = 0 Or Month(v) = tsuki) And (hi = 0 Or Day(v) = hi) Then
                ct = ct + 1
            End If
        End If
    Next c
    COUNTDATE = ct
End Function

書式

COUNTDATE(範囲,年,月,日)

「= COUNTDATE("A1:C50", 2013, 2, 5)」のようにして使います。
例えば、年に「2013」、月に「2」、日に「5」を指定すると「2013-2-5」に合致するセルの数を返します。

年、月、日は「0」を指定することもできます。
例えば、年に「0」、月に「2」、日に「5」を指定すると、年に関わらず、2月5日であるセルの数を返します。「2011-2-5」「2012-2-5」「2013-2-5」などを数えます。
例えば、年に「0」、月に「2」、日に「0」を指定すると、年や日に関わらず、2月であるセルの数を返します。「2011-2-3」「2012-2-1」「2013-2-5」などを数えます。
全て「0」を指定すると、日付がセットされているセルの数を返します。

JavaScriptでPHPのmktime関数とdate関数を再現

JavaScriptの日付の扱いはとても面倒です。
一方、PHPではdateという関数があって、年月日などの整形がとても簡単です。
完全再現というわけにはいきませんが、JavaScriptでdate関数を使う方法を考えました。

php_mktime

まずPHPのmktimeという関数を作ります。
これは年月日時分秒を与えると1970年1月1日午前0時からの通算秒を返します。
PHPのmktimeは、時分秒月日年の順に与えるという奇妙な使用になっていますが、これに従います。
「2001年2月3日4時5分6秒」は「timestamp=php_mktime(4,5,6,2,3,2001)」として使います。

function php_mktime() {
    //PHPのmktimeを再現。
    //1970年1月1日午前0時からの通算秒数を返す。
    //引数は時,分,秒,月,日,年。省略のときは現在日時。
    var now, vals, mydate, i;
    now = new Date();
    vals = [];
    vals[0] = now.getHours();
    vals[1] = now.getMinutes();
    vals[2] = now.getSeconds();
    vals[3] = now.getMonth() + 1;
    vals[4] = now.getDate();
    vals[5] = now.getFullYear();
    for (i = 0; i < 6; i++) {
        if (typeof arguments[i] != 'undefined') {
            vals[i] = arguments[i];
        }
    }
    mydate = new Date(vals[5], vals[3] - 1, vals[4], vals[0], vals[1], vals[2]);
    return mydate.getTime() / 1000;
}

php_date

次にdateという関数を作ります。
これはフォーマットとタイムスタンプを与えると日付や時間を整形して返します。
フォーマットにYを指定すると、年を4桁で返します。
本当はもっとたくさんあるのですが、ここではY,y,m,n,F,M,d,j,w,l,D,G,H,g,h,i,s,a,Aに対応しています。
例えば「date("Ymd",timestamp)」のようにして使うと「20010203」が返ります。

function php_date() {
    //PHPのdateを再現。
    //ただしY,y,m,n,F,M,d,j,w,l,D,G,H,g,h,i,s,a,Aに対応。
    //引数は形式,タイムスタンプ。タイムスタンプは省略できる。省略のときは現在日時。
    var format, timestamp, months, weekdays, mydate, temp;
    format = arguments[0];
    timestamp = arguments[1];
    if (typeof timestamp == "undefined") {
        timestamp = php_mktime();
    }
    months = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
    weekdays = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];
    mydate = new Date(timestamp * 1000);
    //二重変換防止のためformatの文字を@で挟む。
    format = format.replace(/([YymnFMdjwlDGHghisaA])/g, "@$1@")
    //年
    format = format.replace(/@Y@/g, mydate.getFullYear());
    format = format.replace(/@y@/g, mydate.getFullYear() % 100);
    //月
    format = format.replace(/@m@/g, ("0" + (mydate.getMonth() + 1)).slice(-2));
    format = format.replace(/@n@/g, mydate.getMonth() + 1);
    format = format.replace(/@F@/g, months[mydate.getMonth()]);
    format = format.replace(/@M@/g, months[mydate.getMonth()].substr(0, 3));
    //日
    format = format.replace(/@d@/g, ("0" + mydate.getDate()).slice(-2));
    format = format.replace(/@j@/g, mydate.getDate());
    //曜日
    format = format.replace(/@w@/g, mydate.getDay()); //0:日曜
    format = format.replace(/@l@/g, weekdays[mydate.getDay()]);
    format = format.replace(/@D@/g, weekdays[mydate.getDay()].substr(0, 3));
    //時(24時)
    format = format.replace(/@G@/g, mydate.getHours());
    format = format.replace(/@H@/g, ("0" + mydate.getHours()).slice(-2));
    //時(12時)
    temp = mydate.getHours() % 12 == 0 ? 12 : mydate.getHours() % 12;
    format = format.replace(/@g@/g, temp);
    format = format.replace(/@h@/g, ("0" + temp).slice(-2));
    //分
    format = format.replace(/@i@/g, ("0" + mydate.getMinutes()).slice(-2));
    format = format.replace(/@s@/g, ("0" + mydate.getSeconds()).slice(-2));
    //AM,PM
    temp = mydate.getHours() < 12 ? "am" : "pm";
    format = format.replace(/@a@/g, temp);
    format = format.replace(/@A@/g, temp.toUpperCase());
    return format;
}

POISSON関数の使い方

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

複数の値で検索するユーザー定義関数「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

Excelで組合せを列挙する関数

Excelを使って組合せをすべて列挙する関数「KUMIAWASE」を作りました。

書式

KUMIAWASE(全体,抜き取り数)
全体 抜き取る対象をカンマ区切りの文字列で指定します。「a,b,c,」のように末尾に「,」を付けます。
抜き取り数 抜き取る組み合わせ1組に含まれる項目の数を指定します。

解説

  • 組合せを「a,b;a,c;b,c;」のような文字列で返します。「a,b」「a,c」「b,c」の組合せが列挙されます。
  • 全体の末尾が「,」でない場合、あるいは総数<抜き取り数である場合、エラー値#VALUE!が返されます。

コード

Function KUMIAWASE(zentai, nukitorisu)
    '使用例
    'zentai="a,b,c,"
    'nukitorisu=2
    'KUMIAWASE="a,b;a,c;b,c;"
    Const sep = ";"
    Dim arrayall, arraypart
    Dim numall As Long, numpart As Long, i As Long, j As Long
    Dim temp As String, textpart As String
    If Right(zentai, 1) <> "," Then
        KUMIAWASE = "#VALUE!"
        Exit Function
    End If
    arrayall = Split(zentai, ",")
    numall = UBound(arrayall)
    If nukitorisu > numall Then
        KUMIAWASE = "#VALUE!"
    ElseIf nukitorisu = 1 Then
        For i = 0 To numall - 1
            KUMIAWASE = KUMIAWASE & arrayall(i) & sep
        Next i
    ElseIf nukitorisu > 1 Then
        For i = 0 To numall - nukitorisu
            temp = ""
            For j = i + 1 To numall - 1
                temp = temp & arrayall(j) & ","
            Next j
            textpart = KUMIAWASE(temp, nukitorisu - 1)
            arraypart = Split(textpart, ";")
            numpart = UBound(arraypart)
            For j = 0 To numpart - 1
                KUMIAWASE = KUMIAWASE & arrayall(i) _
                    & "," & arraypart(j) & sep
            Next j
        Next i
    End If
End Function

使用例

全体 抜き取り数 KUMIAWASE
a,b,c,d,e, 1 a;b;c;d;e;
a,b,c,d,e, 2 a,b;a,c;a,d;a,e;b,c;b,d;b,e;c,d;c,e;d,e;
a,b,c,d,e, 3 a,b,c;a,b,d;a,b,e;a,c,d;a,c,e;a,d,e;b,c,d;b,c,e;b,d,e;c,d,e;
a,b,c,d,e, 4 a,b,c,d;a,b,c,e;a,b,d,e;a,c,d,e;b,c,d,e;
a,b,c,d,e, 5 a,b,c,d,e;
古い記事

タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報