Excel VBA セル操作
セルの指定方法いろいろ
セルを選択・参照する方法http://www.k1simplify.com/vba/tipsleaf/leaf21.html http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_cell.html http://wada811.blogspot.com/2012/03/excel-vba.html http://www.nurs.or.jp/~ppoy/access/excel/xlA002.html http://kabu-macro.com/kouza/cell/cell_gyouretu_sanshou.html Excelの列名⇔列番号
兼業主夫 ときどき 指揮者 のち ギーク？ さんが、ユーザ定義関数を公開してくださっています。http://d.hatena.ne.jp/hiko_s/20110819/p1 http://d.hatena.ne.jp/hiko_s/20110819/p2 Excelの最終行を取得
下のコードを登録して、セルにデータを入力して試してみて下さい。★私の推奨★ ただし、最終行までデータが登録されているシートでは誤認するため、使えません。
追記1：
④と同じ考え方で、
右端の列数 を求めるときは、下記のようにします。
Cells(1, ActiveSheet.
Columns .Count).End(
xlToLeft ).
Column
列番号は、列 A のときは 1、列 B のときは 2 のように、数値で返されます。
ただし、最右列(IV)までデータがある場合は使えません。
追記2：
なにぬねのーつ さんのサイトで、下記のコードが模範解答として紹介されていました。
UsedRange で、書式付きセルを除外して値の入っている範囲内の最終行、最終列を求める方法です。
With ActiveSheet.
UsedRange
MaxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
MaxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
End With
http://www.niji.or.jp/home/toru/notes/8.html
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_cell.html
Sub Excelの最終行取得()
Dim myRange As Range
Range ("B3").CurrentRegion .Interior.ColorIndex = 6
MsgBox _
"① .Cells .SpecialCells (xlCellTypeLastCell).Row =" _
& Cells.SpecialCells(xlCellTypeLastCell).Row _
& Chr(13) & Chr(10) _
& "② .Cells.SpecialCells(xlLastCell).Row= " _
& Cells.SpecialCells(xlLastCell ).Row _
& Chr(13) & Chr(10) _
& "③ .Range(" & Chr(34) & "A65536" & Chr(34) & ").End(xlUp).Row=" _
& Range("A65536 ").End(xlUp) .Row _
& Chr(13) & Chr(10) _
& "④ .Cells(ActiveSheet.Rows .Count , 1).End(xlUp).Row=" _
& Cells(ActiveSheet.Rows.Count , 1).End(xlUp ).Row _
& Chr(13) & Chr(10) _
& "⑤.Range ("B3").CurrentRegion .Row=" _
& Range ("B3").CurrentRegion.Row _
& Chr(13) & Chr(10) _
& "⑥ .Range ("B3").CurrentRegion.Rows.Count=" _
& Range ("B3").CurrentRegion.Rows.Count
SpecialCells メソッド は、指定された条件を満たしているすべてのセル (Range オブジェクト) を返します。
expression .SpecialCells(
Type, Value )
expression は、必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。
Type は、必ず指定します。XlCellType クラスの定数を使用します。取得するセルの種類を指定します。
使用できる定数は、次に示す XlCellType クラスのいずれかです。
定数 意味
xlCellTypeAllFormatConditions 表示形式が設定されているセル xlCellTypeAllValidation 条件の設定が含まれているセル
xlCellTypeBlanks 空の文字列
xlCellTypeComments コメントが含まれているセル
xlCellTypeConstants 定数が含まれているセル
xlCellTypeFormulas 数式が含まれているセル
xlCellTypeLastCell 使われたセル範囲内の最後のセル xlCellTypeSameFormatConditions 同じ表示形式が設定されているセル
xlCellTypeSameValidation 同じ条件の設定が含まれているセル
xlCellTypeVisible すべての可視セル
Value 引数 Type に xlCellTypeConstants または xlCellTypeFormulas を設定し、引数 Value を使うと、特定の種類の定数や数式を含むセルだけを取得することができます。複数の値を加算して指定すると、複数の種類の定数や数式を指定できます。この引数を省略すると、すべての定数および数式が対象になります。XlSpecialCellsValues クラスの定数を使用します。
使用できる定数は、次に示す XlSpecialCellsValue クラスのいずれかです。
Row プロパティ は、対象セル範囲の最初の領域の先頭行の番号を返します。値の取得のみ可能です。長整数型 (Long) の値を使用します。Rows プロパティは、Worksheet オブジェクトでは、指定したワークシートのすべての行を表す Range オブジェクトを返します。値の取得のみ可能です。
このプロパティでは、オブジェクト修飾子を指定せずに、ActiveSheet.Rows と指定しても同じ動作を表します。
Rows プロパティは、Range オブジェクトでは、指定した範囲の行を表す Range オブジェクトを返します。Columns プロパティ
複数のセル範囲を含む Range オブジェクトに対して Rows プロパティを使用すると、選択範囲の中で最初に選択した領域の行だけが返されます。たとえば、Range オブジェクトに A1:B2 および C3:D4 の 2 つのセル範囲が含まれているとき、Selection.Rows.Count を実行すると、4 ではなく 2 が返されます。
複数のセル範囲を選択している可能性があるときは、このプロパティを使用する前に
Areas .Count を実行し、範囲が複数選択であるかどうかを確認します。複数選択の場合は、次の例のようにセル範囲の領域ごとにループします。
次の例は、
シート 1 の選択範囲の行数を表示 します。
複数の領域が選択 されている場合は、領域ごとにループします。
Option Explicit
Sub 選択範囲の行数()
Dim 範囲カウント As Integer
Dim 行 As Integer
Dim 範囲 As Range
Worksheets ("Sheet1").Activate
範囲カウント = Selection.Areas .Count
If 範囲カウント <= 1 Then
MsgBox "選択されているのは " & _
Selection.Rows .Count & " 列です。"
Else
行 = 1
For Each 範囲 In Selection.Areas
MsgBox "領域 " & 行 & " で選択されているのは " & _
範囲.Rows.Count & " 行です。"
行 = 行 + 1
Next 範囲
End If
End Sub
次の使用例は、シート 1 の行 3 を削除します。
Worksheets("Sheet1").Rows(3).Delete
次の使用例は、目的の行のセル 1 の値が前の行のセル 1 の値と同じ場合に、ワークシート 1 のアクティブ セル領域内の行を削除します。
For Each rw In Worksheets(1).Cells(1, 1).CurrentRegion.Rows
this = rw.Cells(1, 1).Value
If this = last Then rw.Delete
last = this
Next
次の使用例は、シート 1 の選択範囲の行数を表示します。複数の領域が選択されている場合は、領域ごとにループします。
Worksheets("Sheet1").Activate
areaCount = Selection.Areas.Count
If areaCount <= 1 Then
MsgBox " 選択されているのは " & _
Selection.Rows.Count & " 行です。"
Else
i = 1
For Each a In Selection.Areas
MsgBox "領域 " & i & " で選択されているのは " & _
a.Rows.Count & " 行です。"
i = i + 1
Next a
End If
Areas プロパティ は、複数の選択範囲のすべてのセル範囲を表す Areas コレクションを返します。値の取得のみ可能です。Columns プロパティを WorkSheet オブジェクトに指定 した場合は、指定されたワークシートのすべての列を表す Rangeオブジェクトを返します。値の取得のみ可能です。
expression .Columns expression 必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。
Columns プロパティを Range オブジェクトに指定 した場合は、指定されたセル範囲の列を表す Range オブジェクトを返します。値の取得のみ可能です。Rows プロパティ
expression .
Columns
expression 必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。
複数のセル範囲を含む Range オブジェクトに対して Columns プロパティを使用すると、選択範囲の中で最初に選択した領域の列が返されます。
たとえば、Range オブジェクトで A1:B2 および C3:D4 の 2 つのセル範囲が含まれているとき、Selection.Columns.Count を実行すると 4 ではなく 2 が返されます。
複数のセル範囲を選択している可能性があるときは、このプロパティを使用する前に
Areas .Count を実行し、範囲が複数選択であるかどうかを確認します。複数選択の場合は、次の例のようにセル範囲の領域ごとにループします。
次の例は、
シート 1 で選択されているセル範囲の列数を表示 します。複数の領域が選択されている場合は、領域ごとにループします。
Option Explicit
Sub 選択範囲の列数()
Dim 範囲カウント As Integer
Dim 列 As Integer
Dim 範囲 As Range
Worksheets ("Sheet1").Activate
範囲カウント = Selection.Areas .Count
If 範囲カウント <= 1 Then
MsgBox "選択されているのは " & _
Selection.Columns .Count & " 列です。"
Else
For 列 = 1 To 範囲カウント
MsgBox "領域" & 列 & " で選択されているのは" & _
Selection.Areas(列).Columns.Count & " 列です。"
Next 列
End If
End Sub
Column プロパティは、指定されたセル範囲の最初の領域で、最初の列の番号を数値で返します。値の取得のみ可能です。長整数型 (Long) の値を使用します。
対象セル範囲の最後の列番号を求めるには、次のように式を指定します。
myRange.
Columns (myRange.Columns.Count).
Column
次の例は、
シート1 で 1列ごとに幅を 4 に設定 します。
Option Explicit
Sub 偶数列の列幅を4に()
Dim 列 As Range
For Each 列 In Worksheets ("Sheet1").Columns
If 列.Column Mod 2 = 0 Then
列.ColumnWidth = 4
End If
Next 列
End Sub
CurrentRegion プロパティ は、アプリケーション情報オブジェクトを返すプロパティで、アクティブ セル領域 (Range オブジェクト) を返します。アクティブ セル領域とは、空白行と空白列で囲まれたセル範囲 です。値の取得のみ可能です。Cells や、UsedRange も参照下さい。複数行をまとめて削除
Rows プロパティを使う
Excelでは、空白でも、セルが残っていると、ファイル・サイズが大きくなります。
不要な部分を、行削除して、上につめると、容量が小さくなります。
If 最終行 > 11 Then
Rows ("12:" & CStr(最終行 )).Delete Shift:=xlUp
End If
途中の行を削除
これは、複数のキーで連続する行データの、最初と終わりだけを残して、途中の行を削除するマクロの例です。
Option Explicit
Dim 処理行 As Integer
Dim モデル As String
Dim タイプ As String
Dim 前モデル As String
Dim 前タイプ As String
Dim 後モデル As String
Dim 後タイプ As String
Sub 中間行削除()
ThisWorkbook.Worksheets("Delivery Machine List in USA an").Activate
処理行 = 0
' 処理を高速化するため、画面描画停止､自動計算停止
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Do
処理行 = 処理行 + 1
モデル = Range("A1").Cells(処理行, 1).Value
タイプ = Range("B1").Cells(処理行, 1).Value
後モデル = Range("A1").Cells(処理行 + 1, 1).Value
If 後モデル = "" Then Exit Do
後タイプ = Range("B1").Cells(処理行 + 1, 1).Value
If モデル = 前モデル And モデル = 後モデル And タイプ = 前タイプ And タイプ = 後タイプ Then
Rows(処理行).Delete Shift:=xlUp
処理行 = 処理行 - 1
End If
前モデル = モデル
前タイプ = タイプ
Loop
' 画面描画再開､自動計算停止解除
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
EntireRow プロパティを使う
A11セルを選択して、CtrlキーとShiftキーを押さえながら、↓キーを押す と最終行まで一気に選択できます。この状態で行削除する方法と同じです。
Range(Range("A11"), Range("A11").End(xlDown) ).Select 'セルA11から、A列の最後まで、行削除
Selection.EntireRow .Delete
End プロパティ は、対象となるセルが含まれる領域の終端のセルを示す Range オブジェクトを返します。End + 方向キー (↑、↓、←、→のいずれか) に相当します。値の取得のみ可能です。
expression .End(Direction ) expression 必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。Direction 必ず指定します。移動する方向を指定します。XlDirection クラスの定数を使用します。
EntireRow プロパティ は、指定されたセル範囲を含む 1 行または複数の行全体 (Range オブジェクト) を返すプロパティです。値の取得のみ可能です。EntireColumn プロパティ は、指定されたセル範囲を含む 1 列または複数の列全体 (Range オブジェクト) を返すプロパティです。値の取得のみ可能です。行列処理
VBA を使って便利なのは、行列を For～Next で一気に操作できる点です。始点のセルを Range で指定 して、 Cells を使って行列を数値 指定 しています。
Sub 順次処理()
Dim i, j As Integer
For i = 1 To 3
For j = 1 To 4
Range ("B2").Cells (i, j ).Value = i & j
Next j
Next i
End Sub
For...Next ステートメント は、指定した回数だけ、一連のステートメントを繰り返すフロー制御ステートメントです。
For...Next ステートメントの構文は、次の指定項目から構成されます。
For counter =
start To end [
Step step ]
[
statements ]
[
Exit For]
[
statements ]
Next [
counter ]
指定項目の
Step は、ループを繰り返すごとに引数
counter に加算される値を指定します。引数 step を省略すると、ループを繰り返すごとに引数
counter には 1 が加算されます。
Cells プロパティ では、Cells の後に続けて行および列のインデックスを指定して、Range オブジェクトを取得 することができます。同上の補完
例えば、右下のExcel画面のように、重複した項目を省略した表を書くことが有ります。above-mentioned.xls
Option Explicit
Sub 同上()
Dim i As Integer
Dim j As Integer
Dim 始点 As String
Dim 処理行数 As Integer
Dim 値 As Variant
Dim 書式 As Variant
ThisWorkbook.Worksheets ("Sheet1").Activate
始点 = "A2" 'この事例では、A2のセルを始点として、下にコピーします。
処理行数 = Cells (ActiveSheet.Rows .Count , 3).End(xlUp).Row - Range(始点).Row + 1
'処理行数は、C列の最終行数から始点行数を引いて、1 加えたもの。
For j = 1 To 2 'この事例では、列方向に2列、処理をしています。
For i = 1 To 処理行数
If i = 1 Then
値 = Range(始点).Cells (i, j ).Value
書式 = Range(始点).Cells(i, j).Borders .LineStyle
Else
If Range(始点).Cells(i, j).Value = "" Then
Range(始点).Cells(i, j).Value = 値
Range(始点).Cells(i, j).Borders .LineStyle = 書式
Else
値 = Range(始点).Cells(i, j).Value
書式 = Range(始点).Cells(i, j).Borders.LineStyle
End If
End If
Next i
Next j
End Sub
Borders プロパティ を、Range オブジェクト、Style オブジェクトに指定した場合、スタイルまたはセル範囲 (条件付き書式の一部として定義されている範囲を含む) の 4 本の罫線を表す Borders コレクションを返します。
LineStyle プロパティ Parent プロパティ Weight プロパティ
変数を使って、セルを指定する方法
変数を使って、セルを指定する方法を整理すると、以下のようになります。一つのセルを指定する場合 Range ("起点").Cells (移動行, 移動列)1が起点自身で、0は一つ前 、-1は2つ前になります。
Option Explicit
Dim i As Integer
Dim j As Integer
Sub 変数を使ったセル指定()
For i = -2 To 2
For j = -2 To 2
With Range ("K11 ").Cells (i, j)
.NumberFormat = "@"
'セルの書式を「文字列」にする。
.Value = CStr(i) & " " & CStr(j)
If i Mod 2 = 0 And j Mod 2 = 0 Then
'行・列のインデックスが、ともに偶数の場合に、黄色で塗りつぶす。
.Interior.ColorIndex = 6
End If
End With
Next j
Next i
End Sub
セル範囲を指定する場合 Range ("起点").Cells (移動行, 移動列).Resize (行数 , 列数)起点を移動する必要が無い場合は、「Cells(移動行, 移動列).」部分は不要です。
複数のセル範囲を指定する場合 Union (Range ("範囲1の起点").Cells (移動行, 移動列).Resize (行数, 列数), Range ("範囲2の起点").Cells (移動行, 移動列).Resize (行数, 列数))起点を移動する必要が無い場合は、「Cells(移動行, 移動列).」部分は不要です。
別解： Cells 以外に、Offset を使うこともできます。0が起点自身 で、-1は一つ前、1は一つ後ろになります。
Option Explicit
Dim i As Integer
Dim j As Integer
Sub 変数を使ったセル指定②()
For i = -3 To 1
For j = -3 To 1
With Range ("K11 ").Offset (i, j)
.NumberFormat = "@"
'セルの書式を「文字列」にする。
.Value = CStr(i) & " " & CStr(j)
If (i + 1) Mod 2 = 0 And (j + 1) Mod 2 = 0 Then
'行・列のインデックスが、ともに奇数の場合に、黄色で塗りつぶす。
.Interior.ColorIndex = 6
End If
End With
Next j
Next i
End Sub
行列入れ替えコピー
セルの行列を入れ替えてコピーする、3つの方法を紹介します。
Sub 行列入れ替えコピー① ()
ThisWorkbook.Worksheets ("Sheet1").Activate
ActiveCell.CurrentRegion .Copy
Worksheets("Sheet2").Range("A1").PasteSpecial _
Transpose:=True
Application .CutCopyMode = False
ThisWorkbook.Worksheets("Sheet2").Activate
Range("A1").Select
End Sub
Sub 行列入れ替えコピー② ()
Dim i As Integer
Dim j As Integer
ThisWorkbook.Worksheets("Sheet1").Activate
For i = 1 To Cells (ActiveSheet.Rows .Count , 1).End(xlUp).Row
For j = 1 To Cells(1, ActiveSheet.Columns .Count).End(xlToLeft).Column
ThisWorkbook.Worksheets("Sheet2").Range("A1").Cells(j, i ).Value _
= ThisWorkbook.Worksheets("Sheet1").Range("A1").Cells(i, j ).Value
ThisWorkbook.Worksheets("Sheet2").Range("A1").Cells(j, i ).NumberFormat _
= ThisWorkbook.Worksheets("Sheet1").Range("A1").Cells(i, j ).NumberFormat
Next j
Next i
End Sub
Sub 行列入れ替えコピー③ ()
Dim 配列()
Dim R As Integer
Dim C As Integer
ThisWorkbook.Worksheets("Sheet1").Activate
R = Cells(ActiveSheet.Rows .Count , 1).End(xlUp).Row
C = Cells(1, ActiveSheet.Columns .Count).End(xlToLeft).Column
配列 = ThisWorkbook.Worksheets("Sheet1").Range("A1").Resize (R, C).Value
配列 = WorksheetFunction .Transpose (配列)
= 配列
End Sub
Option Explicit
Option Base 1
Sub 行列入れ替えコピー④ ()
Dim 配列()
Dim 添字数 As Long
Dim 次元数 As Long
Dim 数値 As Long
ThisWorkbook.Worksheets("Sheet1").Activate
配列 = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion .Value
添字数 = UBound (配列)
'mougモーグ > 即効テクニック > Excel VBA > 関数関連のテクニック > 配列の次元数を取得する
'http://www.moug.net/tech/exvba/0100039.htm
On Error Resume Next
次元数 = 1
Do While Err.Number = 0
次元数 = 次元数 + 1
数値 = UBound (配列, 次元数)
Loop
配列 = WorksheetFunction .Transpose (配列)
ThisWorkbook.Worksheets("Sheet2").Range("A1").Resize(次元数, 添字数).Value = 配列
End Sub
PasteSpecial メソッド (形式を選択して貼り付け)は、クリップボードのデータを、指定されたセル範囲に貼り付けます。
expression .
PasteSpecial (
Paste, Operation, SkipBlanks, Transpose )
expression は、対象となる Range オブジェクトを返すオブジェクト式を指定します。
Paste は、XlPasteType クラスの定数を使用して、範囲の中で貼り付ける部分を指定します。省略可能です。
使用できる定数は、次に示す XlPasteType クラスの定数のいずれかです。
xlPasteAll 既定値全て xlPasteAllExceptBorders 罫線以外全て xlPasteColumnWidths 列幅 xlPasteComments コメント xlPasteFormats 書式 xlPasteFormulas 数式 xlPasteFormulasAndNumberFormats 数式と数値の書式 xlPasteValidation 入力規則 xlPasteValues 値だけ xlPasteValuesAndNumberFormats 値と数値の書式
Operation は、「演算」の部分です。XlPasteSpecialOperation クラスの定数を使用して、貼り付けの操作を指定します。省略可能です。
SkipBlanks は、クリップボードに含まれる空白のセルを貼り付けの対象にするかしないかのオプションです。空白セルを貼り付けない場合には、True を指定します。既定値は False です。
Transpose は、行列入れ替え、のオプションです。貼り付けるときにデータの行と列を入れ替えるには、True を指定します。既定値は False です。
次の使用例は、シート 1 のセル範囲 D1:D5 の各セルのデータに、シート 1 のセル範囲 C1:C5 の対応するセルのデータを加算します。
With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
End With
Worksheet オブジェクトの PasteSpecial メソッド
expression .PasteSpecial Format Link DisplayAsIcon IconFileName IconIndex IconLabel NoHTMLFormatting expression 必ず指定します。対象となる Worksheet オブジェクトを返すオブジェクト式を指定します。Format Variant ) の値を使用します。クリップボードのデータの形式を文字列で指定します。Link Variant ) の値を使用します。元のデータと貼り付けたデータの間にリンクを設定するには、True を指定します。元のデータがリンクに適さないデータである場合や、元のデータを作成したアプリケーションがリンクをサポートしない場合には、この引数は無視されます。既定値は False です。DisplayAsIcon Variant ) の値を使用します。True を設定すると、貼り付けたものをアイコンで表示します。既定値は False です。IconFileName Variant ) の値を使用します。使われるアイコンの含まれているファイルを指定するには、引数 DisplayAsIcon に True を指定します。IconIndex Variant ) の値を使用します。アイコン ファイルのどのアイコンを使うかを示すインデックス番号を指定します。IconLabel Variant ) の値を使用します。アイコンのラベルを文字列で指定します。NoHTMLFormatting Variant ) の値を使用します。True を設定すると、HTML から書式設定、ハイパーリンク、およびイメージを削除します。False を設定すると、HTML をそのまま貼り付けます。既定値は False です。
メモ NoHTMLFormatting Format NoHTMLFormatting
このメソッドを使う前に、貼り付け先のセル範囲を必ず選択してください。
このメソッドは、クリップボードの内容に合うように現在の選択範囲を変更します。
CutCopyMode プロパティ は、切り取りモード、またはコピー モードの状態を示す値を設定します。False で、切り取りモードまたはコピー モードを解除し、点滅している枠線を取り除きます。
戻り値 内容
False 切り取りモードとコピー モードが共に選択されていない状態。 xlCopy コピー モード。 xlCut 切り取りモード。
設定値 内容
False 切り取りモードまたはコピー モードを解除し、点滅している枠線を取り除きます。 True 切り取りモードまたはコピー モードを解除し、点滅している枠線を取り除きます。
Resize プロパティ は、直前の Range で指定したセルに対して、サイズが変更されたセル範囲 (Range オブジェクト) を返します。行列入れ替えコピー3 では、これを使って、セルの範囲の値を一気に配列変数に格納し、また配列変数から一気にセルに書き出し ています。配列のインデックス番号は1から始まる )になります。配列のインデックス番号は0から始まる )になる点に、注意して下さい。セルに代入する配列は、二次元配列でなければならない 点に、注意して下さい。Option Base ステートメント は、モジュール レベルで有効で、 Sub の前に書きます。
Option Base 1 ' 配列の添字の既定値を 1 に設定します。
私は、配列のインデックス0を、項目名ヘッダを登録するのに使うことがよくあります。こんなとき、上の、「読み込み1オリジン、書き出し0オリジン」は、実用上便利です。Dim 配列() の後ろは、 As Variant とするか、空白 (デフォルトで Variant)にします。 WorksheetFunction プロパティ は、WorksheetFunction オブジェクトを返すプロパティです。値の取得のみ可能です。WorksheetFunction オブジェクト は、Visual Basic から呼び出すことができる Excel ワークシート関数のコンテナとして使用されます。VBA で Excel の関数を使うときに指定します。Transpose(配列) は、［転置行列 ］を作る関数で、ワークシート上にある配列の縦と横を逆転させることができます。TRANSPOSE 関数は、配列の列数および行数とそれぞれ同数の行数および列数のセル範囲に、配列数式として入力する必要があります。ReDim ステートメント は、動的配列変数に対するメモリ領域の再割り当てを行います。配列の要素数を、状況によって変更するときに、使います。セルの範囲を、配列に直接取り込むときには、Dim ステートメントでは、配列要素数を指定しません。そして、取り込みの直前に、ReDim ステートメントで、再割り当てします。 Preserve を指定すると、既存の配列に格納されている値を失うことなく、配列の最後の次元の要素数を変更することができます。変更できるのは、動的配列の最後の次元のサイズに限られます。 また、次元数は変更できません。次元が 2 つ以上ある動的配列の場合、サイズを変更できるのは、最後の次元のみ です。Transpose します。ファイルリスト一覧 の事例を、参照下さい。Redim Preserve MyArray(15) ' 配列の要素数を 15 に変更します。
ReDim Preserve で、「メモリが不足しています。 」と実行時エラーになる場合が有ります。メモリを節約するために、「ReDim」を使ったつもりが、逆にメモリを消費してしまうようです。Erase を使います。UBound 関数は、 配列の指定された次元で使用できる、添字の最大値を、長整数型 (Long) の値で返します。UBound (arrayname[, dimension])
指定項目 内容
arrayname 必ず指定します。配列変数の名前です。
dimension 省略可能です。バリアント型 (内部処理形式 Long の Variant) の値を指定します。
連立1次方程式の解
上で、行列が出たついで ( ・・・ ) {} で囲まれて、「配列数式」と認識されます。
3x-2y-9z=0
equations.xls
Option Explicit
Sub 連立1次5元方程式の解法()
'Cramer の公式
Dim 係数()
Dim 係数の逆行列()
Dim 右辺()
Dim 解()
Dim 係数の行列式 As Double
Dim r As Integer
Dim c As Integer
'係数のチェック
ThisWorkbook.Worksheets ("sheet1").Activate
r = Range("A2").End(xlDown).Row - 1 '方程式の行数
c = Range("A2").End(xlToRight).Column '方程式の未知数の数
If r <> c Then
MsgBox "係数の範囲の取得ができませんでした。" _
& Chr(13) & Chr(10) _
& "空白セルに「空白」文字が無いか確認下さい。"
End
Else
ReDim 係数(r, r) '係数部分を登録する変数
係数 = Range("A2").Resize (r, r).Value
係数の行列式 = WorksheetFunction .MDeterm (係数)
If 係数の行列式 = 0 Then
MsgBox "解は存在しない(不能)か、無数に存在(不定)。"
End
Else
Range("A8").Value = 係数の行列式
End If
End If
'係数の逆行列を求める
ReDim 係数の逆行列(r, r)
係数の逆行列 = WorksheetFunction .MInverse (係数)
Range("I2").Resize(r, r).Value = 係数の逆行列
'方程式の右辺に逆行列を掛ける
ReDim 右辺(r, 1) '右辺の定数部分を登録する変数
ReDim 解(r, 1) '解を登録する変数
右辺 = Range("G2").Resize(r, 1).Value
解 = WorksheetFunction .MMult (係数の逆行列, 右辺)
Range("P2").Resize(r, 1).Value = 解
Range("P2").Select
End Sub
MDeterm(配列) は、配列の行列式を返します。配列には、行数と列数が等しい正方行列を指定します。matrix determinant の省略です。MInverse(配列) は、行列の逆行列を返します。配列には、行数と列数が等しい正方行列を指定します。matrix inverse の略です。
列 A 列 B
行 1 d/(a*d-b*c) b/(b*c-a*d)
行 2 c/(b*c-a*d) a/(a*d-b*c)
2 行 2 列の行列がどのように計算されるかを示します。セル範囲 A1:B2 にはそれぞれ a、b、c、d という数値が含まれているとします。右の表は、行列 A1:B2 の逆行列を示しています。MMult(配列 1,配列 2) は、2 つの配列の行列積を返します。matrix multiplication の略です。 子供の血液型の出現率
上で「配列」が出たついでに、配列を使って、血液型出現確率を計算してみましょう。BloodType.xls http://homepage2.nifty.com/tabbycats/blood/top.htm
Option Explicit
Dim 父血液型()
Dim 母血液型()
Dim 子血液型()
Dim 出現血液型()
Dim fi As Integer
Dim fj As Integer
Dim mi As Integer
Dim mj As Integer
Dim ci As Integer
Dim グラフ描画エリア As Range
Sub 血液型組合せ()
ReDim 父血液型(8, 2)
ReDim 母血液型(8, 2)
ReDim 子血液型(6, 2)
ReDim 出現血液型(2)
ThisWorkbook.Worksheets("Sheet1").Activate
'セルのA11:B42の範囲に、親の血液型のパターンを登録しておき、配列に読み込みます。
父血液型 = Range("A11").Resize(8, 2).Value
母血液型 = Range("A11").Resize(8, 2).Value
'セルのA51:A56に、子の血液型のパターンを登録しておき、配列に読み込みます。
子血液型 = Range("A21").Resize(6, 2).Value
For fi = 1 To 8
For fj = 1 To 2
For mi = 1 To 8
For mj = 1 To 2
If 父血液型(fi, fj) <= 母血液型(mi, mj) Then
出現血液型 = Array ("", 父血液型(fi, fj), 母血液型(mi, mj))
Else
出現血液型 = Array ("", 母血液型(mi, mj), 父血液型(fi, fj))
End If
For ci = 1 To 6
If Left(子血液型(ci, 1), 1) = 出現血液型(1) _
And Right(子血液型(ci, 1), 1) = 出現血液型(2) Then
子血液型(ci, 2) = Val(子血液型(ci, 2)) + 1
End If
Next ci
Next mj
Next mi
Next fj
Next fi
'出現頻度の結果表示
Range("A1").Resize(6, 2).Value = 子血液型
'出現率のグラフ表示
Set グラフ描画エリア = Range("A1").Resize(6, 2)
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData _
Source:=グラフ描画エリア _
, PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = _
"=""両親の血液型がA,B,AB,Oそれぞれ25%で等しい場合の、" & Chr(13) & "子供の血液型の出現率"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.ApplyDataLabels _
Type:=xlDataLabelsShowLabelAndPercent _
, LegendKey:=False _
, HasLeaderLines:=True
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
ActiveWindow.Visible = False
Range("A1").Select
End Sub
Array 関数 は、配列が格納されたバリアント型 (Variant) の値を返します。arglist ) で、引数 arglist は、必ず指定します。引数 arglist には、値のリストをカンマ (,) で区切って指定します。指定した値は、バリアント型 (Variant) に格納されている配列の要素に代入されます。引数 arglist を指定しない場合は、長さ 0 の配列が作成されます。Array 関数を使用して作成した配列のインデックスの最小値は、常に 0 です。ほかの種類の配列とは異なり、Option Base ステートメントに最小値を指定しても影響を受けません。 」とヘルプに書かれていますが、誤りです。Option Base ステートメントで、1 に変更できます。Charts オブジェクトの Add メソッド は、新しいグラフ シートを作成します。グラフ シート (Chart オブジェクト) を返します。
expression .Add(Before, After, Count ) expression 必ず指定します。対象となる Charts コレクションを返すオブジェクト式を指定します。Before 指定したオブジェクトのシートの直前に、新しいグラフ シートを追加します。After 指定したオブジェクトのシートの直後に、新しいグラフ シートを追加します。Count 追加するシートの数を指定します。既定値は 1 です。
ChartObjects コレクションオブジェクト に対してAddメソッド を使います。シートの位置を指定して、複数の領域を含む、複数のグラフを表示 の項を参照下さい。セル範囲を、配列に一括登録、およびその逆
配列から、セル範囲に、データを一括登録したり、逆に、セル範囲を、一気に配列に読み込むと、スピードも速く、便利です。Resize プロパティを使って、セル範囲を指定して、配列を使う方法 は、上に書きました。事例コードは、この行です。 部品表のローレベル・コード設定(その1) 」のマクロでは、ソート処理で、この配列操作を使っています。
Option Explicit
Option Base 1
Sub 行列入れ替えコピー④ ()
Dim 配列()
Dim 添字数 As Long
Dim 次元数 As Long
Dim 数値 As Long
ThisWorkbook.Worksheets("Sheet1").Activate
配列 = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion .Value
添字数 = UBound (配列)
'mougモーグ > 即効テクニック > Excel VBA > 関数関連のテクニック > 配列の次元数を取得する
'http://www.moug.net/tech/exvba/0100039.htm
On Error Resume Next
次元数 = 1
Do While Err.Number = 0
次元数 = 次元数 + 1
数値 = UBound (配列, 次元数)
Loop
配列 = WorksheetFunction .Transpose (配列)
ThisWorkbook.Worksheets("Sheet2").Range("A1").Resize(次元数, 添字数).Value = 配列
End Sub
VBA を使って便利なのは、行列を For～Next で一気に操作できる点です。
