メニュー/挿入/テーブルにするListObjectが使えるようになる。Accessのデータセットと同じように使えないかしら?
こっちが詳しい素晴らしい m(_ _)m (また帰って来てくださいね)。
これ結構深いのでまた追記します。
メニュー/挿入/テーブルにするListObjectが使えるようになる。Accessのデータセットと同じように使えないかしら?
こっちが詳しい素晴らしい m(_ _)m (また帰って来てくださいね)。
これ結構深いのでまた追記します。
ドキュメントにフォルダaaを作り、aaの中で以下のことをします。
エクスプローラーを起動して、VBAの関数の実行結果を確認します。
DirコマンドといえばMS-DOS。1990年ごとバブル景気の最後のころです。そのころにVBAでもDOSコマンド相当の関数が提供されました。
その1では、必要な行のデータを転記する例でした。次は、2つの表から1つの表を作る方法を説明します。
その1で作った配列は、そのまま引数arGouとして使う。セルの範囲を指定をする引数rAddressは、受講台帳のデータ範囲を指定する。変数名のarやrは、arはArray配列、rはRangeの意味で付けてます。
'合格者の配列と受講台帳のセル範囲で照合する
Function myAddr(arGou, rAddress As Range)
Dim ar() 'Rangeの範囲のデータを入れる動的配列
ar = rAddress 'こちらは全部の値をチェックする
Dim i As Integer, j As Integer, k As Integer
j = 1 'For文の中で使い始めで0だと飛ぶため。
ReDim arr(1 To UBound(ar, 1), 1 To UBound(ar, 2)) 'データ以上になることは無い
Dim iii As Integer 'arrの行番号用、列はjを使う
For i = 1 To UBound(ar, 1) '2次元配列の1番目、行。1から始めるのはrがRangeだから
For k = 1 To UBound(ar, 1) 'iの各行に全arGouの名前と照合するために繰り返す
If arGou(i, 2) = ar(k, 4) Then 'arGouの2列目とarの4列を比較
iii = iii + 1 'スタートが1なので先に1にしておく
For j = 1 To UBound(ar, 2) '2次元配列の2番目、列
arr(iii, j) = ar(k, j) 'iではなくk
Next
End If
Next
Next
myAddr = arr
End Function
'=myAddr(myGoukaku(テーブル3),テーブル12)
その1のmyGoukakuで合格者だけの2次元配列、myAddrで降格者だけの受講台帳の2次元配列を作ることができた。では、この2つの2次元配列を使って合体させるのがmyTennkiです。
'2つの2次元配列から転移目的の関数の完成です
Function myTennki(arTest, arAddress)
Dim i As Integer, j As Integer, k As Integer
If UBound(arTest, 1) > UBound(arAddress, 1) Then
i = UBound(arTest, 1)
Else
i = UBound(arAddress, 1)
End If
If UBound(arTest, 2) > UBound(arAddress, 2) Then
j = UBound(arTest, 2)
Else
j = UBound(arAddress, 2)
End If
ReDim arr(1 To i, 1 To j) '2つの引数の最大でメモリを確保
j = 1 'For文の中で使い始めで0だと飛ぶため。
Dim iii As Integer 'arrの行番号用、列はjを使う
For i = 1 To UBound(arTest, 1) 'rTestの方の行処理
For k = 1 To UBound(arAddress, 1) 'rAddreの方の行の処理
If arTest(i, 2) = arAddress(k, 4) Then '名前が一致するか
iii = iii + 1 'スタートが1なので先に1にしておく
arr(iii, 1) = arAddress(i, 2) 'クラス番号
arr(iii, 2) = arAddress(i, 3) '出席番号
arr(iii, 3) = arAddress(i, 4) '名前
arr(iii, 4) = arTest(k, 3) '設備
arr(iii, 5) = arTest(k, 4) '取扱
arr(iii, 6) = arTest(k, 5) '法規
End If
If VBA.IsEmpty(arTest(i, 1)) Then Exit For
If VBA.IsEmpty(arAddress(k, 1)) Then Exit For
Next
Next
myTennki = arr
End Function
'myTennki(myGoukaku(rTest), myAddr(myGoukaku(rTest), rAddress))
実際に使う時は、以下のmyKansei関数を使います。引数は、2つの表の範囲を指定するだけです。今まで、作った関数を内部で使ってます。これをラッパー関数という。ラッピンですね。包装です。使いやすくするのが目的です。
内部で使っているmyTennkiやmyAddrはPrivate Functionにしてセルの入力時に使えないようにします。myKanseiだけ使えればいいので問題ありません。
'使いやすくする。ラッパー関数。2つのテーブルを引数にする
Function myKansei(rTest As Range, rAddress As Range)
myKansei = myTennki(myGoukaku(rTest), myAddr(myGoukaku(rTest), rAddress))
End Function
2つの表のデータ作成用マクロは その1にあります。
エクセルマクロとは、ExcelオブジェクトとVBA仕様です。こうした関数は、ほぼVBAの知識だけでできます。
以下を調査します。
エクセルが何をしているのか?。特に、入力と表示結果の違いはマクロ記録も見るとよくわかる?
しかし、
マクロ記録は、VBAのコードを書くときは以下の内容を確認するのがほとんでしょう。
転記となるとマクロ?。危なっかしいというかSubプロシージャを書くのにパワーがいる。何が難しいのかというとセルの位置の指定ですよね。一方、ワークシート関数は、セルに埋め込むので目的のセルにデータを入れる。確実に指定のセルに入れる?表示することができる。
エクセルのワークシート関数を使うならINDEX関数?なんかな。
ここではユーザー定義関数/カスタム関数/自作関数であるmyIndex関数を作ります。INDEX関数とは関係ありません。
例題、テスト結果の表と受講台帳がある。この2つの表から合格者の表を作る。表の項目は、クラス番号、出席番号、名前、各科目の点数とする。
これがすぐできる人は天才です。私は、「困難は分割せよ」、「数が多い時は少なくして、データの桁が大きい時は小さくして考える」ということを心がけます。「困難は分割せよ」の方は有名かな?。2つ目は経験から。プログラムではこの考えで複数の関数を作る。
ここでは、合格者の配列を返す関数myGoukakuを作る。これは、myTemplateを参考にしてます。
'とにかく合の人だけを返す
Function myGoukaku(rTest As Range)
Dim ar() 'Rangeの範囲のデータを入れる動的配列
ar = rTest 'セルの値が入る
Dim i As Integer, j As Integer
j = 1 'For文の中で使い始めで0だと飛ぶため。
ReDim arr(1 To UBound(ar, 1), 1 To UBound(ar, 2)) 'データ以上になることは無い
Dim ii As Integer 'arrの行番号用、列はjを使う
For i = 1 To UBound(ar, 1) '2次元配列の1番目、行。1から始めるのはrがRangeだから
If "合" = ar(i, 6) Then '6列目に合か否かがある
ii = ii + 1 'スタートが1なので先に1にしておく
For j = 1 To UBound(ar, 2) '2次元配列の2番目、列
arr(ii, j) = ar(i, j)
Next
End If
Next
myGoukaku = arr
End Function
その2では、合格者名と一致する受講台帳の行の配列を返す関数です。
Sub テスト表()プロシージャを実行する前に、以下の関数を先に標準モジュールに貼り付けます。
Function hanntei(setu, tori, hou)
If setu + tori + hou <180 Then
hanntei = "不"
Else
hanntei = "合" '以下の判定前にとりあえず合
If setu < 40 Then hanntei = "不" 'Thenの内容が簡素なら一行の方が見やすい
If tori <40 Then hanntei = "不"
If hou < 40 Then hanntei = "不"
End If
End Function
テスト表作成のマクロ。上の関数を使うので先に上の関数を貼り付けて下さい。以下のマクロも標準モジュールに貼り付け実行します。
Sub テスト表()
If VBA.MsgBox("選択しているワークシートに上書きします!!!", vbOKCancel) = VBA.vbCancel Then Exit Sub
If ActiveSheet.ListObjects.Count = 1 Then ActiveSheet.ListObjects(1).Delete
Range("A1") = "名前"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("B1") = "設備"
ActiveCell.Characters(1, 2).PhoneticCharacters = "セツビ"
Range("C1") = "取扱"
ActiveCell.Characters(1, 2).PhoneticCharacters = "トリアツカイ"
Range("D1") = "法規"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ホウレイ"
Range("E1") = "判定"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ハンテイ"
Range("A2") = "名前1"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A8"), Type:=xlFillDefault
Range("A2:A10").Select
Range("B2") = "56"
Range("C2") = "87"
Range("D2") = "87"
Range("B3") = "76"
Range("C3") = "76"
Range("D3") = "89"
Range("B4") = "54"
Range("C4") = "67"
Range("D4") = "34"
Range("B4") = "90"
Range("B5") = "89"
Range("C5") = "78"
Range("D5") = "67"
Range("B6") = "56"
Range("C6") = "45"
Range("D6") = "67"
Range("B7") = "48"
Range("C7") = "68"
Range("D7") = "43"
Range("B8") = "23"
Range("C8") = "67"
Range("D8") = "87"
Range("C8") = "98"
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "No"
Range("A2") = "1"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A8"), Type:=xlFillSeries
Range("A2:A8").Select
Range("D5").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$8"), , xlYes).Name = _
"テーブル3"
Range("F2") = "=hanntei([@設備],[@取扱],[@法規])"
End Sub
受講生台帳の作成
Sub 受講生台帳()
If VBA.MsgBox("選択しているワークシートに上書きします!!!", vbOKCancel) = VBA.vbCancel Then Exit Sub
If ActiveSheet.ListObjects.Count = 1 Then ActiveSheet.ListObjects(1).Delete
Range("A1") = "No"
Range("A2") = "1"
Range("A2").AutoFill Destination:=Range("A2:A8"), Type:=xlFillSeries
Range("B1") = "クラス"
Range("B2") = "1"
Range("B3") = "2"
Range("B4") = "1"
Range("B5") = "1"
Range("B6") = "2"
Range("B7") = "1"
Range("B8") = "2"
Range("C1") = "出席番号"
ActiveCell.Characters(1, 4).PhoneticCharacters = "シュ"
Range("C2") = "1"
Range("C3") = "1"
Range("C4") = "3"
Range("C5") = "2"
Range("C6") = "2"
Range("C7") = "4"
Range("C8") = "3"
Range("d1") = "名前"
Range("d2") = "名前1"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("d2").Select
Selection.AutoFill Destination:=Range("d2:d8"), Type:=xlFillDefault
Range("E1") = "住所"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ジュウショ"
Range("E2") = "住所1"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ジュウショ"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E8")
Range("E2:E8").Select
Range("D3").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$8"), , xlYes).Name = _
"テーブル12"
Range("テーブル12[#All]").Select
End Sub