Excel VBA > 関数 > 自作関数でExcel作業の幅を「すんごく」効率的にするには
このエントリーをはてなブックマークに追加

自作関数でExcel作業の幅を「すんごく」効率的にするには

自作関数が業務をハッピーにするかは関数次第！？

Excelソフトは、一般機能だけでもかなり使えます。しかしマクロ(VBA)を使えば「無限大」と言っていい程、作業を効率化させることができます。
ここでは「かゆいところに手が届く」だろう自作関数についていくつか紹介します。
ここで挙げている自作関数は例であり、実際には、皆さんのやっている「まさにこの業務」に即した形に(カスタマイズ)すれば、その恩恵は大きくなるはずです。
こちらのページは、下記に作成している自作関数をそのまま試したい方向けの内容です。これらの関数で「どんなことができるか」は、こちらの記事からご覧ください。
余談ですが、プログラム的には、自作関数は独自関数とも呼んだりします。

自作関数をそのまま試すには(関数を使うための準備)

Excelは2つの画面を持っています。1つは、普段皆さんが使うシートが3枚並んだ見慣れた画面ですが、「マクロ(自作関数)を使うにはもう一つの画面」を使います。
新規のExcelファイルを起動したら「Altキー + F11」を押します。
自作関数でExcel作業の幅を「すんごく」広げるには_01

または下記画像の開発タブからも起動できます。
(※開発タブは表示されてない場合、Excelのオプションから設定してやる必要があります)
自作関数でExcel作業の幅を「すんごく」広げるには_02

すると下記の画面が表示されます。これがExcelのもう一つの画面で、簡単に言うと「プログラム作成用のエディタ画面」です。
自作関数でExcel作業の幅を「すんごく」広げるには_03

この画面にシートのようなもの(標準モジュール)を追加します。
自作関数でExcel作業の幅を「すんごく」広げるには_04

このシート(標準モジュール)にプログラムを書き込み(作成し)、それを実行すると「通常の画面」で文字が勝手に動いたり、シートが勝手に追加されたりします。
今回の自作関数の場合は、(既に作成済みなので)このモジュールにコピーします。

このエディタ画面の(上記で追加した)標準モジュールに自作関数をコピーします。
自作関数でExcel作業の幅を「すんごく」広げるには_05

下記は4種類の関数が書かれているプログラムコードです。この内容を選択し、標準モジュールへコピーペーストします。

'============================================
'指定したフォルダ(パス)にファイル名があるか検索する
'============================================
Function ファイル検索(検索ファイル場所 As Range, 検索ファイル名 As Range) As String

    Dim strPath As String
    Dim strFileName As String

    strPath = 検索ファイル場所.Value
    strFileName = 検索ファイル名.Value

    'ファイル場所の末尾に「\」がなければ付与
    If VBA.Right(strPath, 1) <> “\” Then
        strPath = strPath & “\”
    End If

    If VBA.Dir(strPath & strFileName) <> “” And strFileName <> “” And strPath <> “” Then
        ファイル検索 = “有り”
    Else
        ファイル検索 = “無し”
    End If

End Function

'============================================
'読点「。」ごとにBRタグを入れる
'============================================
Function 読点をBRに置換(対象セル As Range) As String

    読点をBRに置換 = VBA.Replace(対象セル.Value, “。”, “。<br />”)

End Function

'============================================
'選択範囲のセルで、同じ値をグループ化した後の件数を数える
'============================================
Function セル値のグループ化後をカウント(対象セル As Range) As Long

    Dim rng As Range
    Dim dicValue As Object

    Set dicValue = CreateObject(“Scripting.Dictionary”)

    For Each rng In 対象セル
        If Not dicValue.Exists(rng.Value) Then
            dicValue.Add rng.Value, rng.Value
        End If
    Next rng

    セル値のグループ化後をカウント = dicValue.Count

End Function

'============================================
'区切り文字を含むデータを分割する(配列数式)
'※シート上で使うときは、セルは複数選択が前提で
'※「Ctrl + Shift + Enter」で配列数式にする
'============================================
Function 区切り文字データの分割(対象セル As Range, 区切り文字 As String) As Variant

    区切り文字データの分割 = VBA.Split(対象セル.Value, 区切り文字)

End Function

以上の作業で「通常の画面」からこれらの独自関数を使うことができます。
自作関数でExcel作業の幅を「すんごく」広げるには_06

自作関数とFunctionプロシージャ

ここの例で挙げている自作関数は「Functionプロシージャ」と言う方法で作成されています。
Fucntionプロシージャを使う(作る)と、文字通りExcelで好きな機能を持った関数を追加できるということが大きなメリットです。
弊社の経験では、今回の「シートに埋め込む為」のFunctionプロシージャはあまり作成せず、ツールやシステムの中で使うことが多いです。ただし、今回のようにやり方によってはそれなりに使えるだろう方法もあるので参考にしてもらえればと思います。

注意点など

今回提示している独自の関数はお試し用です(完成度を高めるためには、もう少し色々と手を付け加える必要がある点、自己責任で利用される点に注意してください)。
また、この使い方(今回説明した追加方法)だと原則的には「独自関数のある(コピペした)Excelファイル上でしか動かない」仕様になります。「他のExcelファイルでもこれらの関数を使いたい」等といった状況も想定できるため、そのための知識、プログラムの追加工程も必要になります。
勉強用として設定を変更などして試してみると、プログラムの理解に役立つと思いますのでガンガンお試しください。
なお、「ちゃんとした自作関数にして実務で使いたい」、「自社仕様にしたい」等があれば、ぜひお問い合わせくださいませ。

カテゴリ：関数

Split関数の「Limit」の使い方
自作関数でExcel作業の幅を「すんごく」効率的にするには
Array関数に変数をセット(格納)する
ランダム関数を使う