Excel2019からスピル機能が追加されてUNIQUE関数やSORT関数が実現されましたが、自作関数でもスピル機能を利用できるって知っていましたか？
今回は表形式のデータをリスト形式に変換する自作関数を作成したので実例としてご紹介です
スピル、使ってますか？
そもそもみなさんはスピル機能使っていますか？
Excelおじな私はExcelの進化スピードについて行けず実装からかなり遅れて使い始めたのですが、まぁこれが便利！
業務をブラックボックス化させると世を騒がせるExcel玄人をさらに躍進させることになりそうですが、実際便利なんだしExcelの機能くらい覚えようよ・・と思う今日この頃です
スピルについては↓↓で紹介してますので参考まで
自作関数でスピルを使う方法
自作関数でスピルを使う方法は単純、返値を配列（2次元配列）にするだけ
昔は値を返値にするしかなかったのですが、スピルができたおかげで配列を返値にすれば範囲で反映することができるようになりました
具体的な例として表を一覧に変換する自作関数を紹介します
変換前の表と変換後の一覧
まずは今回自作関数を作ろうかと思ったきっかけの表と作りたかった一覧の具体例を
変換前の表
いわゆる地域区分と言われる各地方にどの都道府県が分類している表
| 北海道地方 | 北海道 | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| 東北地方 | 青森県 | 岩手県 | 宮城県 | 秋田県 | 山形県 | 福島県 | |||
| 関東地方 | 茨城県 | 栃木県 | 群馬県 | 埼玉県 | 千葉県 | 東京都 | 神奈川県 | ||
| 中部地方 | 新潟県 | 富山県 | 石川県 | 福井県 | 山梨県 | 長野県 | 岐阜県 | 静岡県 | 愛知県 |
| 近畿地方 | 三重県 | 滋賀県 | 京都府 | 大阪府 | 兵庫県 | 奈良県 | 和歌山県 | ||
| 中国地方 | 鳥取県 | 島根県 | 岡山県 | 広島県 | 山口県 | ||||
| 四国地方 | 徳島県 | 香川県 | 愛媛県 | 高知県 | |||||
| 九州地方 | 福岡県 | 佐賀県 | 長崎県 | 熊本県 | 大分県 | 宮崎県 | 鹿児島県 | 沖縄県 |
特徴としては左側にヘッダー部分があってそれに紐づくデータ部が横に伸びている
見るだけの表としてはわかりやすいんですが、例えばLookup系の関数には使えないです
変換後の一覧
そしてこれが変換したかった一覧
| 北海道地方 | 北海道 |
| 東北地方 | 青森県 |
| 東北地方 | 岩手県 |
| 東北地方 | 宮城県 |
| ・・・ | |
| 九州地方 | 鹿児島県 |
| 九州地方 | 沖縄県 |
縦一列にすることでLookup系でも使えるマスタテーブルになります
そんなに使いどころがあるかと微妙ですが、必要となった時の”そう、コレコレ！”感は半端ではないです
クロステーブルのパターン
ヘッダーが横だけでなく縦にもあるクロス表のパターンも紹介
左上から右下にデータを読み込んで一覧にします
変換した結果の一覧がこんな感じ
横のヘッダー、縦のヘッダー、データの順番で一覧化します
自作関数の詳細
ようやくですが自作関数の実例を
プログラムは長いので先に関数と引数の説明です
関数の使用例
まずは引数部分などをFunction部分から
活用の幅を広げるため最低限データ領域さえ指定していればOKに
他はヘッダー部があれば縦・横をそれぞれ指定できるようにしているのと空白をスキップするかどうかの判定、多分使うことはないと思いつつもヘッダーの並び順を”行→列”だけでなく”列→行”へ切り替え可能な引数を準備しました
Function UNPIVOTCROSSTABLE(DATA_AREA As Range, _
Optional HEADER_ROW_AREA As Range, _
Optional HEADER_COLUMN_AREA As Range, _
Optional BLANK_SLIP As Boolean = True, _
Optional HEADER_ORDER As Long = 0)
'----------------------------------------------------------------------------------------------------
' クロス表をリストに変換する自作関数
' DATA_AREA :縦一列にするデータ領域
' HEADER_ROW_AREA :テーブルの行ヘッダー（左部）領域
' HEADER_COLUMN_AREA:テーブルの列ヘッダー（上部）領域
' BLANK_SLIP :データ領域が空白だった時にスキップするかの判定
' HEADER_ORDER :
' 1 :ヘッダー部を左からHEADER_COLUMN_AREA→HEADER_ROW_AREAにする
' 1以外 :ヘッダー部を左からHEADER_ROW_AREA→HEADER_COLUMN_AREAにする
'----------------------------------------------------------------------------------------------------
End Function上部の画像を参考に表を一覧に使用とするとこんな感じに
=UNPIVOTCROSSTABLE(D4:H8,B4:C8,D2:H3,TRUE,0)
あえて全ての引数を入力していますが”BLANK_SLIP“と”HEADER_ORDER“は多分指定することが少ないだろうと初期値設定しているので空白のままでもOK
プログラムの中身
プログラム自体はこんな感じ
各所にコメントを入れているので説明は割愛しますが、特に難しいことはしていないので見てもらえればわかるはず
標準モジュールにそのままコピペしてもらえればすぐに使えます
Function UNPIVOTCROSSTABLE(DATA_AREA As Range, _
Optional HEADER_ROW_AREA As Range, _
Optional HEADER_COLUMN_AREA As Range, _
Optional BLANK_SLIP As Boolean = True, _
Optional HEADER_ORDER As Long = 0)
'----------------------------------------------------------------------------------------------------
' クロス表をリストに変換する自作関数
' DATA_AREA :縦一列にするデータ領域
' HEADER_ROW_AREA :テーブルの行ヘッダー（左部）領域
' HEADER_COLUMN_AREA:テーブルの列ヘッダー（上部）領域
' BLANK_SLIP :データ領域が空白だった時にスキップするかの判定
' HEADER_ORDER :一覧への反映を行ヘッダー、列ヘッダーのどちらからにするか判定する
' 1 :ヘッダー部を左からHEADER_COLUMN_AREA→HEADER_ROW_AREAにする
' 1以外:ヘッダー部を左からHEADER_ROW_AREA→HEADER_COLUMN_AREAにする※こっちがデフォ
'----------------------------------------------------------------------------------------------------
Dim header_column_row As Long, header_row_column As Long
Dim header_row_columns As Long, header_column_rows As Long
Dim data_row As Long, data_column As Long
Dim result_tmp() As Variant, result() As Variant
Dim data_count As Long
Dim r As Long, c As Long
'--- 行ヘッダーが設定されていたら列数をカウントする
If HEADER_ROW_AREA Is Nothing Then
header_row_columns = 0
Else
header_row_columns = HEADER_ROW_AREA.Columns.Count
End If
'--- 列ヘッダーが設定されていたら行数を感とする
If HEADER_COLUMN_AREA Is Nothing Then
header_column_rows = 0
Else
header_column_rows = HEADER_COLUMN_AREA.Rows.Count
End If
'--- 仮の二次元配列を作る※列数はヘッダー部＋1、行数は一旦データ部のセル数にしておく
ReDim result_tmp(1 To DATA_AREA.Count, 1 To header_row_columns + header_column_rows + 1)
'--- データ数を一旦0にしておく
data_count = 0
'--- 行→列の順にデータをまとめる
For data_row = 1 To DATA_AREA.Rows.Count
For data_column = 1 To DATA_AREA.Columns.Count
'--- 空白スキップの設定を見ながらデータを取得する
If Len(Trim(DATA_AREA(data_row, data_column))) > 0 Or BLANK_SLIP = False Then
'--- データ数をカウントアップする
data_count = data_count + 1
'--- 行列のどちらから反映させるか判定しつつ一覧にする
If HEADER_ORDER = 1 Then
'--- 行ヘッダーを一覧にする
For header_column_row = 1 To header_column_rows
result_tmp(data_count, header_column_row) = HEADER_COLUMN_AREA(header_column_row, data_column)
Next
'--- 列ヘッダーを一覧にする
For header_row_column = 1 To header_row_columns
result_tmp(data_count, header_column_rows + header_row_column) = HEADER_ROW_AREA(data_row, header_row_column)
Next
Else
'--- 列ヘッダーを一覧にする
For header_row_column = 1 To header_row_columns
result_tmp(data_count, header_row_column) = HEADER_ROW_AREA(data_row, header_row_column)
Next
'--- 行ヘッダーを一覧にする
For header_column_row = 1 To header_column_rows
result_tmp(data_count, header_row_columns + header_column_row) = HEADER_COLUMN_AREA(header_column_row, data_column)
Next
End If
'--- データを一覧にする
result_tmp(data_count, header_row_columns + header_column_rows + 1) = DATA_AREA(data_row, data_column)
End If
Next
Next
'--- 空白をスキップした分リサイズさせる
ReDim result(1 To data_count, 1 To header_row_columns + header_column_rows + 1)
For r = 1 To data_count
For c = 1 To header_row_columns + header_column_rows + 1
result(r, c) = result_tmp(r, c)
Next c
Next r
'--- リサイズした2次元配列を返す
UNPIVOTCROSSTABLE = result
End Functionあとがき
純粋にExcelで使うよりデータベースに投入するためのデータ作りに役立つ自作関数でした
歯痒いところに手が届かない時は自作関数で解決できることも多いので今後も何かあればご紹介しますが、何かご希望あればコメントもらえると優先的に作成してみます
サンプルデータとプログラム入りファイル
このページで紹介したサンプルデータと自作関数”UNPIVOTCROSSTABLE”が入ったExcelファイルです
直接データみたい時にどうぞ
コメント