【VBA】SPLIT関数の使い方

データを扱っていてふと区切り文字で列挙されている情報を分割して、わかりやすくまたは加工しやすく表示したいという状況があります。

①図は海上自衛隊カレーレシピのサイトから取得した「しらせシーフードカレー」のレシピになります。このレシピにそって材料を買い揃えることを考えると、①図の材料表記は「、」区切りで横並びになっており買物には不便です。
そこで材料を縦並びにリスト化して買物一覧(②図)を作ろうと思います。

①レシピシート

②買物一覧シート　↓最終イメージはこんな感じ

そんな時に使われるのがSPLIT関数です。

■Split(対象文字列, 区切り文字)
対象文字列を区切り文字で区切って分割し、配列を返します。

①図の材料をリスト化するには場合は下のようにして区切り文字で分割してあげます。

    Dim zairyoArray As Variant '材料配列
    zairyoArray = Split(Range("C3").Value, "、")
 

さてSplit関数で取得できた情報は分割した材料の配列となります。この場合はFor each構文で取り出すと良いでしょう。こんな感じです。

Private Sub testSplit()
    Dim zairyoArray As Variant '材料配列
    Dim zairyo As Variant '材料名
    zairyoArray = Split(Range("C3").Value, "、")
 
    For Each zairyo In zairyoArray
        Debug.Print (zairyo) 'とりあえずイミディエイト・ウインドウで表示
    Next zairyo
End Sub

最終的に①レシピシートの材料から②買物一覧シートを作ろうとするとこんな感じになります。

Private Sub main()
    Dim plotStRow As Integer '記載開始行番号
    Dim plotRow As Long '記載行番号
    Dim curryCol As Integer 'カレー名記載列番号
    Dim zairyoCol As Integer '材料名記載列番号
    Dim curryNm As String 'カレー名
    Dim zairyoArray As Variant '材料配列
    Dim zairyo As Variant '材料名
    '/*変数セット*/
    plotStRow = 3
    plotRow = plotStRow
    curryCol = 2
    zairyoCol = 3
 
    ' カレー名取得 Cells(3, 2)はRange("B3")と同じ
    curryNm = Worksheets("レシピ").Cells(3, 2).Value
    ' 材料ごとに分割した配列を取得 Cells(3, 3)はRange("C3")と同じ
    zairyoArray = Split(Worksheets("レシピ").Cells(3, 3).Value, "、")
                                                                                       
 
    For Each zairyo In zairyoArray
        '/*レシピプロット*/
        Worksheets("買物一覧").Cells(plotRow, curryCol).Value = curryNm
        Worksheets("買物一覧").Cells(plotRow, zairyoCol).Value = zairyo
     
        plotRow = plotRow + 1 '次の記載行へ(4行目、５行目、...)
    Next zairyo  
End Sub

これができればエクセルでCSVからデータを取得することもできます。
むしろそこがSplit関数の使い所のメインであったりしますが。

【関数】フィルターでの部分一致(複数項目)

前回のポストの3−2−2では部分一致の場合、VBAを駆使しても３項目までしかフィルターがかけられないという問題について触れました。
今回のポストでは、別の方法で３項目以上のフィルターがけを解説します。

◎関数での対処
フィルター対象に対して、部分一致での複数項目がけができないため、フィルター対象が複数項目の条件を満たすかを判定する列を別に追加し、追加した列の判定結果を完全一致でフィルターがけすることで擬似的に３項目以上のフィルターがけを実施します。



上の図では、下の条件のいずれかを満たす商号または名称を絞るため、H列に判定フラグを設けています。
・「　株式会社」を含む
・「和」を含む
・「エンジニア」を含む

判定フラグの列で「TRUE」を絞り込めば、上記条件を満たす商号または名称が絞られることとなります。

○関数解説
判定フラグ列にはこんな風に関数を記載しています。

=(3*LEN($G3) -(LEN(SUBSTITUTE($G3,"　株式会社","")) +LEN(SUBSTITUTE($G3,"和",""))+LEN(SUBSTITUTE($G3,"エンジニア","")))>0

論理式で指定の文字列を含むかを判定しているのですが、わかりづらいので補足すると、
「商号または名称」の文字数と、「商号または名称」から条件の文字列を空で置換した文字列の文字数を比較することで、指定の文字列を含んでいるかを判定しています。

これは文字列がある文字列を含んでいるかを確認する一般的な関数の使い方で、以下のように使います。

■文字列が条件の文字列を含むかを確認する方法
LEN(文字列) 
　- LEN(SUBSTITUTE(文字列,条件の文字列,"")) > 0 

今回は３つ条件があるため、3*LEN($G3)と減算対象を３倍にしているわけです。

【VBA】フィルターでの完全一致・部分一致

大量の情報を絞り込むのに、エクセルのフィルター機能は大変役に立ちます。
手作業で絞り込みをすることもできますが、いつも使う絞り込みルールがある場合、VBAで実装した方が作業が格段に効率化されるでしょう。

今回は下記のテーブルにフィルターを掛ける例を幾つか提示します。

※国土交通省建設コンサルタント登録事業者一覧より抜粋・改変
http://www.mlit.go.jp/totikensangyo/const/totikensangyo_const_tk2_000059.html

1.フィルターをかける

'フィルターをかける
Private Sub filterData()
    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter
End Sub

Sheets("filterTest").Range("B2").CurrentRegionにてB2セルの属するテーブル領域であるB2:G10の範囲を取得し、この範囲に対してフィルターをかけています。

ちなみに、このコードを再実行するとフィルターが解除されます。

2. 完全一致で絞り込む
2−1. 1項目での絞り込み

上図で「商号又は名称」を「株式会社　東横エルメス」で絞り込む際、下記のように書きます。

'フィルター1項目完全一致

Private Sub filterData0()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社　東横エルメス"

End Sub

この時field:=6の6はフィルター範囲左端から数えて絞り込み対象列である「商号又は名称」が6列目に当たることを示します。

2−2. 2項目以上での絞り込み

同様に2項目で絞り込む際、下記のように書きます。

'フィルター2項目完全一致

Private Sub filterData1()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社　東横エルメス", _

            Operator:=xlOr, _

            Criteria2:="沖昌エンジニアリング　株式会社"

End Sub

2項目以上はCriteria1に配列を指定することで絞り込むことができます。

'フィルター2項目以上完全一致

Private Sub filterData2()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("株式会社　一測設計", "星和電機　株式会社", "株式会社　長大"), _

            Operator:=xlFilterValues

End Sub

3. 部分一致で絞り込む
3-1. 1項目での絞り込み

基本的に完全一致と書き方が同じで、Criteriaに指定する文字列を変えることで絞り込むことができます。

'フィルター1項目部分一致

Private Sub filterData3()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社*"

End Sub

この場合、Criteria1にワイルドカード(*)を用いて「株式会社*」と指定しているので、「株式会社」が前方一致するように絞られます。

3−2. 2項目以上での絞り込み

これも完全一致と書き方は同じとなります。

'フィルター2項目部分一致

Private Sub filterData4()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="*　株式会社", _

            Operator:=xlOr, _

            Criteria2:="*和*"

End Sub

また、配列を指定して下記のように書くこともできます。

'フィルター2項目部分一致

Private Sub filterData5()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("*　株式会社", "*和*"), _

            Operator:=xlFilterValues

End Sub

ただし、残念ながら以下のように3項目以上の指定はできないようです。

'フィルター3項目以上部分一致NGパターン

Private Sub filterData6()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("*　株式会社*", "*和*", "*エンジニア*"), _

            Operator:=xlFilterValues

End Sub

完全一致で複数指定できたのだから普通できるだろうと思うのですが、実際実装してみ
ると何も表示されない絞り込みとなり、VBAエラーともなりません。
エクセルのこの感じなんとも言えません。

そのため、3項目を部分一致で絞りたい場合は、他の方法を模索しなければなりません。

【エクセル関数】文字列の加工① MID関数とFIND関数

外部のデータを利用する上で、一部のデータを加工したい場合があると思います。
たとえば官公庁のデータだったり、お客様のデータや外部システムからの出力データだったりと実際そういう場面は多々あるでしょう。

そういう時活躍する関数がMID関数です。

◼︎MID(文字列,開始位置,文字数)
引数「文字列」の文字列から、引数「開始位置」に指定した文字数目から数えて引数「文字数」分を取得します。

たとえば、文字列「WindowsVista」の1文字目から7文字目まで、つまり「Windows」を取得しようと考えた場合、

=MID("WindowsVista",1,7)

と記述します。

またこの文字列から「Vista」を取得しようと考えた場合、8文字目から5文字分を取得することとなるため、
=MID("WindowsVista",8,5)
と記述します。
この場合、指定する「文字数」はオーバーしても良いため、このように書くこともできます。
=MID("WindowsVista",8,100)

◼︎MID関数の用法
下図に日本郵便の配布するCSVから取得した郵便番号と住所の一部を記載しました。ただし、CSV上では郵便番号がハイフン「-」抜きで書かれているため、MID関数を用いてハイフン「-」ありに文字列を加工することにします。

こんな風に書きます。

=MID($C3,1,3)&"-"&MID($C3,4,4)

「MID($C3,1,3)」で1文字目から3文字の「604」を取得して、「MID($C3,4,4)」で4文字目から4文字の「0991」を取得、最後にハイフン「-」でつなげて郵便番号の形にしています。

このようにMID関数を使えば、抜き出す文字列の始まりと始まりから取得する文字数がわかる場合に限り、文字列を抽出し、加工することができます。
今回は郵便番号が必ず4文字目にハイフン「-」がくる8文字の文字列となるという規則性に着目してMID関数を利用しましたが、文字数が決まっていない場合にもMID関数の利用場面があります。

たとえば、メールアドレスから「@」以降を省いたユーザーIDを取得する場合、こんな風に取得できます。

=MID($C3,1,FIND("@",$C3)-1)

ユーザーIDを取得するにはMID関数を利用するには「開始位置」が1文字目であるかわかっているので、「@」の手前までの「文字数」を取得する必要が有ります。
そのためこの関数式ではFIND関数で「＠」の文字が何文字目に来るかを取得し、その手前までの文字数を計算してMID関数の第3引数にしています。

◼︎FIND(検索文字列,対象,[開始位置])
「対象」に指定した文字列から「検索文字列」を検索し最初の文字列から何文字目であるかを取得する。オプショナルな引数である「開始位置」を指定すると指定した文字数目から「検索文字列」を探すようになります。

◼︎FIND関数の用法
上述の通り。


◼︎より応用的な使い方として
FIND関数のオプショナルな引数である「開始位置」を活用すると、以下のように「検索文字列」が複数ある場合でも対応できます。

例えばC4セルにある「osama-no-mimi@lobanomimi.com」から「-」に囲まれた「no」をMID関数を用いて取得する場合を考えます。
この時FIND関数で初めて「-」がくる位置と、次に「-」がくる位置を求められれば「開始位置」と開始位置からの「文字数」を取得でき、そうすればMID関数を使うことができます。

計算してみると、 この場合最初の「-」は

=FIND("-",C4)
で6文字目に来ることがわかり、

次の「-」は「開始位置」を以下のように設定することで、

=FIND("-",C4,FIND("-",C4)+1)

9文字目に来ることがわかります。

よってMID関数で指定すべき「開始位置」は、
=FIND("-",C4) + 1
(=7文字目)
「文字数」は、

=FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1
(=2文字)

となります。

以上から「no」を取得するには

=MID(C4,FIND("-",C4)+1,FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1)

と記述すれば良いこととなります。


さすがにここまで来るとちょっと読みにくいですね。

【エクセル関数】HLOOKUP関数とVLOOKUP関数

みなさんHLOOKUP関数って使いますか？

多分使わないし、そもそも何？って方もおられるのではないでしょうか。
でもこれが「VLOOKUP関数って使いますか？」ということでしたら結構な人がつかってるよと言ってくれるのではないでしょうか？

今回はそんなLOOKUP関数兄弟の話です。

まずVLOOKUP関数から

◼︎VLOOKUP(検索値, 範囲, 列番号, [検索の型])
範囲指定の右端の列から検索値を探し出して、見つけ出したセルの行から指定された列番号分右に行ったセルの値を検索します。［検索の型］はオプショナルでTRUE(検索値近似一致；1で代用可)かFALSE(検索値完全一致；0で代用可)を指定します。通常は近似値を検索してくるというのは意外と厄介なのでFALSEを指定することが多いでしょう。

◼︎VLOOKUP関数の用法
下図に支社コードにエリアと電話番号、代表者を持った電力事業者や通信事業者にありがちなの支社データのようなものを用意しました。

これをB12セルの支社コードを元にB13セルに代表者を取得しようとすると、図のように
=VLOOKUP($B12,$B$3:$E$7,4,FALSE)
という風になります。
ちなみに絶対参照を使っているのは、B12セルをそのままコピーしてB13セルに貼り付けても範囲指定がずれないようにするためです。

じゃあ本題のHLOOKUP関数ってなんだよと言いますと、簡単に申しますとHLOOKUPの縦列と横列が逆になったものと考えてください。

こんな感じです。

◼︎HLOOKUP(検索値, 範囲, 列番号, [検索の型])
範囲指定の上端の列から検索値を探し出して、見つけ出したセルの列から指定された行番号分下に行ったセルの値を検索します。［検索の型］はVLOOKUPと同様の使い方です。

◼︎HLOOKUP関数の用法
下図にVLOOKUPの時に使用したデータの縦列と横列を入れ替えたデータを用意しました。

同様にこれをB9セルの支社コードを元に代表者名を取得しようとすると、図のように
=HLOOKUP($B9,$C$2:$F$5,4,0)
という風になります。


さて、そんなHLOOKUP関数ですが、特殊な事情がない限り使われません。
そもそも項目を縦向きに並べて横にレコードを積み重ねていくことが普通ないからです。

ただし、使う場面になると結構役立つのでそんな関数があったなと覚えておいて損はないですよ。