Excel VBA セル操作

Excel VBA のトップに戻る
Excel VBA 目次

Excelの最終行を取得
複数行をまとめて削除
行列処理
 ・同上の補完
 ・変数を使って、セルを指定する方法
行列入れ替えコピー
 ・連立1次方程式の解
 ・子供の血液型の出現率
セル範囲を、配列に一括登録、およびその逆
 ・部品表のローレベル・コード設定
Excel関数の VLOOKUP をマクロで
選択範囲を取得し、A1形式で表示
セルのデータチェック
セルの文字検索と置換
セルデータの集計と文字検索
並び替え+重複行削除
使っている全てのセルを指定する

キーのトップと属性の重複を摘出
索引


セルの指定方法いろいろ

 セルを選択・参照する方法
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の列名⇔列番号

兼業主夫 ときどき 指揮者 のち ギーク? さんが、ユーザ定義関数を公開してくださっています。

列Noから列名を取得して返す関数
http://d.hatena.ne.jp/hiko_s/20110819/p1

列名から列Noを取得して返す関数
http://d.hatena.ne.jp/hiko_s/20110819/p2

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

Excelの最終行を取得

 下のコードを登録して、セルにデータを入力して試してみて下さい。
 @は、Excelの操作で行削除していると、行削除前の行を表示するので、誤認します。
 Aは、@の省略表現です。
 Bは、A列の最終行を取得しようとしています。
 お尻から遡るので、途中で行削除していても使えます。65536行は、Excelの現在のバージョンの最大行数です。ただし、最終行までデータが登録されているシートでは誤認するため、使えません。
 Cは、Bの変形で、シートの最大行数をプロパティの組合せで求めているので、Excelのバージョンによらず、使えます。★私の推奨★ただし、最終行までデータが登録されているシートでは誤認するため、使えません。
 Eは、データの入力されている範囲を自動判定して計算するので、行頭や途中に空白の行が無い場合は、使えます。

追記1:
 Cと同じ考え方で、右端の列数を求めるときは、下記のようにします。
Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
列番号は、列 A のときは 1、列 B のときは 2 のように、数値で返されます。
ただし、最右列(IV)までデータがある場合は使えません。

追記2:
 なにぬねのーつ さんのサイトで、下記のコードが模範解答として紹介されていました。

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


 参考:Excel(エクセル) VBA入門:セル範囲の指定方法
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) _
    & "A.Cells.SpecialCells(xlLastCell).Row= " _
        & Cells.SpecialCells(xlLastCell).Row _
    & Chr(13) & Chr(10) _
    & "B.Range(" & Chr(34) & "A65536" & Chr(34) & ").End(xlUp).Row=" _
        & Range("A65536").End(xlUp).Row _
    & Chr(13) & Chr(10) _
    & "C.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row=" _
        & Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row _
    & Chr(13) & Chr(10) _
    & "D.Range("B3").CurrentRegion.Row=" _
        & Range("B3").CurrentRegion.Row _
    & Chr(13) & Chr(10) _
    & "E.Range("B3").CurrentRegion.Rows.Count=" _
        & Range("B3").CurrentRegion.Rows.Count

End Sub

 解説:
 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 クラスのいずれかです。
  • xlErrors
  • xlLogical
  • xlNumbers
  • xlTextValues


 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 コレクションを返します。値の取得のみ可能です。
 選択範囲が単一セルの場合、Areas プロパティは対象となる Range オブジェクト自身を持つ 1 つのコレクションが返されます。選択範囲が複数のセル範囲の場合、Areas プロパティは各選択範囲を 1 つのオブジェクトとして持つ、1 つのコレクションが返されます。


 Columns プロパティを WorkSheet オブジェクトに指定した場合は、指定されたワークシートのすべての列を表す Rangeオブジェクトを返します。値の取得のみ可能です。
 expression.Columns
 expression 必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。
 このプロパティでは、オブジェクト修飾子を指定せずに、ActiveSheet.Columns と指定しても同じ動作を表します。

 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) の値を使用します。
 列番号は、列 A のときは 1、列 B のときは 2 のように、数値で返されます。

 対象セル範囲の最後の列番号を求めるには、次のように式を指定します。
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では、空白でも、セルが残っていると、ファイル・サイズが大きくなります。 不要な部分を、行削除して、上につめると、容量が小さくなります。
 下の例は、12行目以降を、一気に行削除しています。


   If 最終行 > 11 Then
      Rows("12:" & CStr(最終行)).Delete Shift:=xlUp
   End If

この種類の目次に戻る↑ 索引へ↓ トップページに戻る

途中の行を削除

 これは、複数のキーで連続する行データの、最初と終わりだけを残して、途中の行を削除するマクロの例です。

 モデル、タイプをキーに、シリアルrP位の属性情報を持った、行データが並んでいます。
 このデータは、前もって、モデル、タイプ、シリアルbナ、行ソートされています。
 このデータから、モデル・タイプ単位に、シリアルbフトップのデータと、ラストのデータだけを残して、途中のシリアルbフ行データを削除するものです。


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 クラスの定数を使用します。

 XlDirection クラスの定数は、次のいずれかです。
 xlDown、xlToRight、xlToLeft、xlUp

 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 オブジェクトを取得することができます。
 行および列のインデックスを指定しないときは、ワークシートのすべてのセル (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(移動行, 移動列)

 注意:Cells で移動する行・列のインデックスは、1が起点自身で、0は一つ前、-1は2つ前になります。
Cells のセル指定インデックスは1オリジン

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

Aセル範囲を指定する場合
 Range("起点").Cells(移動行, 移動列).Resize(行数 , 列数)

起点を移動する必要が無い場合は、「Cells(移動行, 移動列).」部分は不要です。

B複数のセル範囲を指定する場合
 Union(Range("範囲1の起点").Cells(移動行, 移動列).Resize(行数, 列数), Range("範囲2の起点").Cells(移動行, 移動列).Resize(行数, 列数))

起点を移動する必要が無い場合は、「Cells(移動行, 移動列).」部分は不要です。


 別解:
 セルを相対指定する方法には、上の Cells 以外に、Offset を使うこともできます。
 Offset の場合は、移動する行・列のインデックスは、0が起点自身で、-1は一つ前、1は一つ後ろになります。
 私は、正の場合に1から始まる Cells を使っていますが、0オリジンをお好みなら、Offset を使って下さい。
Offset のセル指定インデックスは0オリジン

Option Explicit

Dim i As Integer
Dim j As Integer

Sub 変数を使ったセル指定A()
    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つの方法を紹介します。
 @は、PasteSpecial メソッドを使う方法で、高速に、セルの全ての情報をコピーできます。
 Aは、For 〜 Nextを使って一つずつの情報をコピーするので、若干時間がかかりますが、コピー対象を限定(下の例では、値と書式)したり、データを途中加工する場合には、有効です。
 Bは、セルの値だけを、高速にコピーできます。


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 行列入れ替えコピーA()
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 行列入れ替えコピーB()
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(配列)
    ThisWorkbook.Worksheets("Sheet2").Range("A1").Resize(C, R).Value = 配列
End Sub



Option Explicit
Option Base 1

Sub 行列入れ替えコピーC()
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) の値を使用します。使われるアイコンの含まれているファイルを指定するには、引数 DisplayAsIconTrue を指定します。
 IconIndex   省略可能です。バリアント型 (Variant) の値を使用します。アイコン ファイルのどのアイコンを使うかを示すインデックス番号を指定します。
 IconLabel   省略可能です。バリアント型 (Variant) の値を使用します。アイコンのラベルを文字列で指定します。
 NoHTMLFormatting   省略可能です。バリアント型 (Variant) の値を使用します。True を設定すると、HTML から書式設定、ハイパーリンク、およびイメージを削除します。False を設定すると、HTML をそのまま貼り付けます。既定値は False です。

 解説

メモ   NoHTMLFormatting は、Format = "HTML" のときにのみ意味を持ちます。それ以外の場合、NoHTMLFormatting は無視されます。

このメソッドを使う前に、貼り付け先のセル範囲を必ず選択してください。

このメソッドは、クリップボードの内容に合うように現在の選択範囲を変更します。



 CutCopyMode プロパティは、切り取りモード、またはコピー モードの状態を示す値を設定します。False で、切り取りモードまたはコピー モードを解除し、点滅している枠線を取り除きます。
戻り値内容
False切り取りモードとコピー モードが共に選択されていない状態。
xlCopyコピー モード。
xlCut切り取りモード。
設定値内容
False切り取りモードまたはコピー モードを解除し、点滅している枠線を取り除きます。
True切り取りモードまたはコピー モードを解除し、点滅している枠線を取り除きます。

 Resize プロパティは、直前の Range で指定したセルに対して、サイズが変更されたセル範囲 (Range オブジェクト) を返します。
 行列入れ替えコピー3では、これを使って、
セルの範囲の値を一気に配列変数に格納し、また配列変数から一気にセルに書き出しています。

 Rangeの値を、配列に一気に読み込むときは、1オリジン(配列のインデックス番号は1から始まる)になります。
 一方、配列を、Rangeに一気に書き出すときは、0オリジン(配列のインデックス番号は0から始まる)になる点に、注意して下さい。
 出力を、添え字1から始めたい場合は、Option Base ステートメントを使って、配列の添字の最小値を 1 に変更しておきます。
 また、セルに代入する配列は、二次元配列でなければならない点に、注意して下さい。

 Option Base ステートメントは、モジュール レベルで有効で、 Sub の前に書きます。
 (このステートメントを、モジュール内のどのプロシージャよりも前に記述します。)

Option Base 1 ' 配列の添字の既定値を 1 に設定します。
Dim 配列()
 私は、配列のインデックス0を、項目名ヘッダを登録するのに使うことがよくあります。こんなとき、上の、「読み込み1オリジン、書き出し0オリジン」は、実用上便利です。

 また、Dim 配列() の後ろは、 As Variant とするか、空白(デフォルトで Variant)にします。
 そうしないと、「コンパイル エラー:型が一致しません: 配列またはユーザー定義型を指定してください。」となって止まります。


 WorksheetFunction プロパティは、WorksheetFunction オブジェクトを返すプロパティです。値の取得のみ可能です。
 WorksheetFunction オブジェクトは、Visual Basic から呼び出すことができる Excel ワークシート関数のコンテナとして使用されます。VBA で Excel の関数を使うときに指定します。
 Visual Basic では、WorksheetFunction オブジェクトを介して Excel ワークシート関数を使用します。


 Transpose(配列)は、[転置行列]を作る関数で、ワークシート上にある配列の縦と横を逆転させることができます。TRANSPOSE 関数は、配列の列数および行数とそれぞれ同数の行数および列数のセル範囲に、配列数式として入力する必要があります。
 「配列」には、行列変換を行うワークシートの配列またはセル範囲を指定します。配列の行列変換を行うと、元の配列の第 1 行が新しい配列の第 1 列になり、元の配列の第 2 行が新しい配列の第 2 列 (以下同様) になります。

 ReDim ステートメントは、動的配列変数に対するメモリ領域の再割り当てを行います。配列の要素数を、状況によって変更するときに、使います。

 セルの範囲を、配列に直接取り込むときには、Dim ステートメントでは、配列要素数を指定しません。
そして、取り込みの直前に、ReDim ステートメントで、再割り当てします。

 そうしないと、エラーになるので、注意しましょう。
 また、一列だけを配列に取り込んでも、インデックスは2次元になります。(1,1)(2,1)・・・

 ReDim ステートメントで、Preserve を指定すると、既存の配列に格納されている値を失うことなく、配列の最後の次元の要素数を変更することができます。
 Preserve を指定した場合、変更できるのは、動的配列の最後の次元のサイズに限られます。また、次元数は変更できません。
 たとえば、次元が 1 つしかない動的配列の場合、その次元は最後のただ 1 つの次元なので、その次元のサイズを変更できます。
 次元が 2 つ以上ある動的配列の場合、サイズを変更できるのは、最後の次元のみです。
 行方向に、動的に配列サイズを増やしたい場合は、列方向に ReDim で増やした後、セルに貼り付けるときに、Transposeします。
 →ファイルリスト一覧の事例を、参照下さい。

 次のステートメントでは、以前の要素を消去せずに、配列のサイズを変更します。
Redim Preserve MyArray(15) ' 配列の要素数を 15 に変更します。

 注:ReDim Preserve で、「メモリが不足しています。」と実行時エラーになる場合が有ります。メモリを節約するために、「ReDim」を使ったつもりが、逆にメモリを消費してしまうようです。
 このような場合は、初めから Dim で大きめに配列を宣言しておいた方が、メモリ不足になりません。(^^ゞ

 配列データの消去には、Erase を使います。


 UBound 関数は、配列の指定された次元で使用できる、添字の最大値を、長整数型 (Long) の値で返します。
 UBound 関数は、LBound 関数と組み合わせて、配列のサイズを調べるために使います。
 LBound 関数は、配列の添字の最小値を調べるときに使います。

構文:UBound(arrayname[, dimension])
指定項目内容
arrayname必ず指定します。配列変数の名前です。
変数の標準的な名前付け規則に従って指定します。
dimension省略可能です。バリアント型 (内部処理形式 Long の Variant) の値を指定します。
添字の最大値を調べる対象となる配列の次元を示す整数を指定します。
最初の次元なら 1、2 番目の次元なら 2、というように指定します。
引数 dimension を省略すると、1 が指定されたものと見なされます。


連立1次方程式の解  上で、行列が出たついで(・・・)に、Excel の「行列式の関数」を使って、連立方程式の解を求めてみましょう。
 Excel でこれらの関数を使うときは、「配列数式」ということで、
@.関数を設定するセル範囲を選択して
A.関数ウィザードで関数を選択して目的の範囲を指定した後
B.[Ctrl]+[Shift]を押しながら、[OK]ボタンを押す
必要が有ります。こうすると、関数が {} で囲まれて、「配列数式」と認識されます。
 マクロでやった方が、簡単?ですね。
 右下の図は、マクロを使って、以下の3元連立1次方程式の解を求めているところです。
連立1次方程式の解
3x-2y-9z=0
5x+4y-4z=0
4x+y-5z=3

 このマクロの事例をダウンロードできます。→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 の省略です。
 行列式とは、配列内の値から導き出される数値のことです。たとえば、3 行 3 列の配列 A1:C3 に対する行列式は、次の数式で表すことができます。
 MDETERM(A1:C3) =
 A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)
 MDETERM 関数は、ほぼ 16 桁の精度で計算されるため、計算の過程でその結果にわずかな誤差が生じることがあります。たとえば、特異な行列式では、本来 0 になる計算結果が 1E-16 のように限りなく 0 に近い数値で表される場合があります。

 MInverse(配列)は、行列の逆行列を返します。配列には、行数と列数が等しい正方行列を指定します。matrix inverse の略です。
 行列とその逆行列の積は、単位行列 (右下がりの対角線上にある成分の値がすべて 1 で、そのほかの成分がすべて 0 であるような正方行列) となります。

列 A列 B
行 1d/(a*d-b*c)b/(b*c-a*d)
行 2c/(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 の略です。
 計算結果は、行数が配列 1 と同じで、列数が配列 2 と同じ配列になります。
 配列 1 の列数は、配列 2 の行数と等しくなければなりません。また、両方の配列には数値だけが含まれている必要があります。
 2 つの配列 b、c の行列積である配列 a は 2 つの配列 b、c の行列積 のように定義されます。ここでは、i = 行数、j = 列数を示します。
xyz=
3-212
-21-3-9
43-24

 マクロを登録したら、右のサンプル・データをコピーして、解をチェックしてみて下さい。

この種類の目次に戻る↑ 索引へ↓ トップページに戻る


子供の血液型の出現率

 上で「配列」が出たついでに、配列を使って、血液型出現確率を計算してみましょう。

 日本人の血液型の比率は、概数で、A:O:B:AB=4:3:2:1です。
 アメリカの血液型の比率は、O:A:B:AB=4.5:4:1:0.5 です。

 子供の血液型は、両親の血液型の組合せで決まります。もし、両親の血液型の比率が、それぞれ25%ずつで均等の場合について、子供の血液型の出現比率を、求めてみます。
 結果は、どの血液型が増加して、どの血液型が減るでしょうか? 想像してみて下さい。

 このマクロをダウンロードできます。→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) の値を返します。
 構文は、Array(arglist) で、引数 arglist は、必ず指定します。引数 arglist には、値のリストをカンマ (,) で区切って指定します。指定した値は、バリアント型 (Variant) に格納されている配列の要素に代入されます。引数 arglist を指定しない場合は、長さ 0 の配列が作成されます。
 「Array 関数を使用して作成した配列のインデックスの最小値は、常に 0 です。ほかの種類の配列とは異なり、Option Base ステートメントに最小値を指定しても影響を受けません。」とヘルプに書かれていますが、誤りです。
 Array 関数を使用して作成した配列のインデックスの最小値は、デフォルトは 0 ですが、Option Base ステートメントで、1 に変更できます。


 Charts オブジェクトの Add メソッドは、新しいグラフ シートを作成します。グラフ シート (Chart オブジェクト) を返します。

 expression.Add(Before, After, Count)
 expression 必ず指定します。対象となる Charts コレクションを返すオブジェクト式を指定します。

 Before 指定したオブジェクトのシートの直前に、新しいグラフ シートを追加します。
 After 指定したオブジェクトのシートの直後に、新しいグラフ シートを追加します。
 Count 追加するシートの数を指定します。既定値は 1 です。

 引数 Before と引数 After の両方を省略すると、新しいグラフ シートは作業中のワークシートの直前に挿入されます。

 グラフ作成の部分は、Excelの「マクロの記録」機能を使って、VBAコードを生成しました。
 ワークシートにグラフを埋め込むと、グラフが配置される場所は、表示されている画面の中央付近になります。
 グラフを、セルの場所を指定して、作りたい場合は、ChartObjects コレクションオブジェクトに対してAddメソッドを使います。
 詳しくは、シートの位置を指定して、複数の領域を含む、複数のグラフを表示 の項を参照下さい。


この種類の目次に戻る↑ 索引へ↓ トップページに戻る


セル範囲を、配列に一括登録、およびその逆

 配列から、セル範囲に、データを一括登録したり、逆に、セル範囲を、一気に配列に読み込むと、スピードも速く、便利です。

 Resize プロパティを使って、セル範囲を指定して、配列を使う方法は、上に書きました。

 「部品表のローレベル・コード設定(その1)」のマクロでは、ソート処理で、この配列操作を使っています。


この種類の目次に戻る↑ この種類の続きを見る↓ 索引へ↓ トップページに戻る



ホームページのトップに戻る