【エクセル関数】採番いろいろ

エクセルは表計算ツールなので、データの順番を振る場面が多々あると思います。

使う人や場面によっていろいろな作法があるのではないでしょうか。

1. 上の行＋１

使用頻度：★☆☆

使い勝手：★★☆

利点：一番上の行以外、行を入れ替えても採番が崩れない。見やすい

難点：行の入れ替え時に採番が狂う

=(上のセル) + 1

2. ROW関数

使用頻度：★★★

使い勝手：★★☆

利点：行を入れ替えても採番が崩れない

難点：-

=ROW() - (任意の値)

ROW関数で行番号が求められるため、そこから任意の値を引き算することで採番する。

１．と比べこちらのほうが使い勝手がいいため、普通はこちらを使う。

3. 固定値

使用頻度：★★★

使い勝手：★★☆

利点：行を入れ替えると採番が崩れる

難点：行を入れ替えると採番が崩れる

数値直指定

オートフィル機能を使いあえて数値を直接指定することもある。

特に複数人が編集する管理簿などで、勝手に並び順を変えられたりしたら困るような局面ではあえて固定で数値を入力する。

【VBA】シート名を列挙する

仕事でちょっと使ったVBA集

'開いている別エクセル(確認.xlsx)のシート名を
'B列3行目から列挙する
Public Sub getSheet()
    Dim mySheet As Worksheet
    Dim trgBook As Workbook 
    Set mySheet = ActiveSheet
    Set trgBook = Workbooks("確認.xlsx")

    Dim i As Long
    For i = 1 To trgBook.Sheets.Count
        mySheet.Cells(i + 2, 2).Value = trgBook.Worksheets(i).Name
    Next i
End Sub

【ｼｮｰﾄｶｯﾄ】エクセルクイックアクセスツールの使い方

とっても便利だけどあんまりクイックアクセス使いに出会わない。

そもそもクイックアクセスって何っていう人が多いのじゃないでしょうか？

そんなあなたも、その便利さに気づいてしまえば仕事場のPCでもご自宅のPCでも、新しく使うPCではまずクイックアクセスツールバーの設定からといったヘビーユーザとなること間違いなしです。

そもそもクイックアクセスツールってなにかというと、ユーザ定義のショートカットみたいなものです。

ショートカットというとCTRL+Z(ひとつ前の処理に戻る)とかCTRL+1(書式設定)だとかエクセルには有用で簡単なショートカットキーがあらかじめ用意されています。

が、、、ここで威力を発揮するのが罫線を引くだとか、セルを結合するだとか、普通だとマウス操作が必須になる所をユーザ定義でショートカット化させてしまえちゃうことです。

クイックアクセスツールはエクセルの左枠(黄色くくりのところ)で定義できて、左からWinキー+1~の数字で定義されています。

そこで試しにWinキー＋2で格子罫線を引くを試してみると、

こんな感じに瞬殺になる。

これができるだけでちゃちゃっと表作ってお客さんや上司にイメージ共有するときにマウスいらずでとってもスピーディ。

あんまり褒められることじゃないけど話しながらWebページ設計の画面モックとか、テスト時のデータイメージなんかを即興で作ってすり合わせる作業もしてしまいます。

クイックアクセスツールの設定方法はとっても簡単でエクセル左上で▼からその他のコマンドを選択して、

クイックアクセスツールバーをカスタマイズするだけ。

私のおすすめはクイックアクセスは上の通りで、格子線を一通りとセル結合/解除それにセル削除が入るくらいで、上から順にWin+1から対応するので並びも同じ。

カスタマイズは人それぞれで、あまり多すぎても使いづらいです。
皆さんも最適なカスタマイズを模索してみては？

【VBA】SGN関数の盲点

みなさんSGN関数って使ったことありますか？

通常、SGN関数は引数に渡した値の正負を判定するために使われます。

◼︎Sgn(num)

numの判定	戻り値
num > 0	1
num = 0	0
num < 0	-1

でもこんなことしないですよね普通。

みなさん
if sgn(num) = 1 then …
なんて書くよりは
if num > 0 then …
なんて風に素直に書きますよね。

そもそもこんなドマイナーな関数を使うより、誰でも知っているような書き方をするはずで、現場によっては使った場合可読性が低いと怒られるでしょう。

なので、主にこの関数の活躍の場は上記のOfficeデベロッパーセンターの説明外の用法にあるようです。

なんと、以下のように配列の初期化が行われているかどうかを判別するために使われちゃったりします。

◼︎Sgn(array)

arrayの判定	戻り値
arrayが初期化されていない状態	0
arrayが初期化済みの状態	-16064

◼︎SGN関数の用法

Private Sub testSgn()
    '未初期化配列の宣言
    Dim sgnArray() As Variant
    If Sgn(sgnArray) = 0 Then
        MsgBox "配列:sgnArrayは初期化されていません"
    Else
        MsgBox "配列:sgnArrayは初期化済みです"
    End If
 
    '初期化済み配列の宣言
    Dim sgnArray2(2) As Variant
    If Sgn(sgnArray2) = 0 Then
        MsgBox "配列:sgnArray2は初期化されていません"
    Else
        MsgBox "配列:sgnArray2は初期化済みです"
    End If
 
End Sub

結果はもちろん
・配列:sgnArrayは初期化されていません
・配列:sgnArray2は初期化済みです
となります。

ただし、この使い方どのサイトでも「よく分からないけどこうなる」と書いてあります。

さてつかみどころのないSgn関数ですが、
筆者の現場ではツールリリース後に早速「環境によってこの使い方をすると落ちる」という厄介な事象に見舞われ、無事別の方法で対処しました。勘弁してください。

こういうことってvbaに限らずよくあるのですが、
やっぱりわからないものを分からないまま使うって怖いですね。

ちなみに配列の初期化済み確認方法はここに詳しいです。

【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項目を部分一致で絞りたい場合は、他の方法を模索しなければなりません。