ExcelのTips

提供：Asterisk Works Wiki

移動：案内, 検索

このページではExcelのちょっとした便利技をまとめます。

テンプレートを指定して起動する

よく使うテンプレートがあるなら、最初から指定してExcelを起動すると便利。
テンプレートファイルを指定するには起動パラメータ"/t"を利用する。
指定するファイルはExcelのテンプレートファイルである必要はありません。通常のExcelファイルでもOKです。
スタートメニューの「ファイル名を指定して実行」を開いて次の例のように入力して起動する。
何度も使うようならこの内容でショートカットを作るとよい。

excel.exe /t "テンプレートファイルのパス"

関数を作る

独自に関数を作るには次のようにする。

VBAのエディタを開く
「挿入」-「標準モジュール」を選択し、現在編集しているbookに標準モジュールを追加する。
関数を定義し実装する。

関数の書式

関数を定義するには次の書式で書く。要はFunction 関数名で始まり、End Functionで終わればよい。

Option Explicitは変数定義を強制する設定でつけておくのがよい習慣。

Option Explicit
Function 関数名(関数の引数)
    'ここに関数を実装する
    '最終的な戻り値は関数名を同じ名前の変数に格納する。
End Function

関数の例

'セル内のコロンの後の数字の合計を求める関数。かけ算と足し算にも対応
Function sum_num(ByVal val As String) 'As Integer
    Dim regex As Object
    Dim Matches As Object, Match As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(:|：)([0-9*+]+)"
    regex.Global = True
    Set Matches = regex.Execute(val)
    For Each Match In Matches
        sum_num = sum_num + Evaluate(Match.SubMatches(1))
    Next Match
    Set regex = Nothing
    Set Matches = Nothing
End Function

正規表現を使う

Excelネイティブには正規表現を扱う関数はないが、ユーザ定義関数を作ってその中でVBScript.RegExpを使う方法がある。

使用する手順

CreateObject("VBScript.RegExp")でRegExpのオブジェクトを生成する。
RegExp::Patternメソッドでパターンを設定
パターンにマッチするすべてを取得するならRegExp::Global = Trueを設定する
RegExp.Execute()メソッドで評価を実行し結果となるMatchesオブジェクトを取得
あとはMatchesをごにょごにょする。

パターンにマッチした一部を取得する

()を使うことによりパターンを区分けし、マッチした部分の一部のみを取り出すこともできる。その場合、()でくくった部分がMatchesの要素であるMatchのさらに要素であるSubMatchesに格納されているのでインデックス(0始まり)を指定して取得する。

正規表現を使った関数の例

'セル内のコロンの後の数字の合計を求める関数。かけ算と足し算にも対応
Function sum_num(ByVal val As String) 'As Integer
    Dim regex As Object
    Dim Matches As Object, Match As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(:|：)([0-9*+]+)"   '<-- コロンの後の数字と*,+の1文字以上にマッチさせるパターン
    regex.Global = True                  '<-- 評価する文字列でヒットする部分すべてを取り出す
    Set Matches = regex.Execute(val)     '<-- 評価を実行
    For Each Match In Matches            '<-- 評価結果をループで取り出す
        sum_num = sum_num + Evaluate(Match.SubMatches(1))  '<-- SubMatchesを使ってパターンの2番目の()の中を取り出す。
    Next Match
    Set regex = Nothing
    Set Matches = Nothing
End Function

参考資料：Clinick's Clinic on Scripting: 正規表現による Visual Basic Scripting Edition (VBScript) の機能強化

関数の使用

通常の関数と同様に利用できる。ただし、Bookに追加した標準モジュールに実装するとそのBookでしか使えない。

日時の計算

セル内の数式で日時を差し引きすることはできないが、セルに書かれた日時の値同士を差し引きすることは可能。

その際、24時間以上の時間や60分以上の分はそのままでは表示できない。

表示するには、セルの書式設定でユーザ定義の書式として[h]:mmや[mm]という書式を作って適用すると、24時間以上の時間や、60分以上の分を表示できる。