Excel VBA セル操作
複数シートを串刺し集計(3D集計)
一つのブックに、例えば、各支店の月別売上金額のそれぞれのシートがあったときに、全支店の月別売上金額を集計した、合計シートを作りたいことが有ります。
こんな時に使うものが、串刺し集計(3D集計) です。
VBA のコードでは、以下の形式になります。
"=SUM('" & "パス(例 D:\temp\マクロ事例\)" & "[ブック名(例:テストVBA1.xls)]" & "左端シート名(例:北海道支店)" & ":" & "右端シート名(例:鹿児島支店)" & "'!" & "対象セル" & ")"
これを、Range に代入します。
次の例は、シート 1 のセル A1 に数式を設定する例です。
Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"
セルの範囲を連続コピー
セルの範囲を、連続したセルの範囲にコピーしたいことが有ります。
こんなときには、AutoFill メソッドを使います。
解説:
AutoFill メソッドは、指定された対象セル範囲内のセルに対してオートフィルを実行します。バリアント型 (Variant) の値を使用します。
expression.
AutoFill(Destination, Type)
expression 必ず指定します。対象となるオブジェクトへの参照を返すオブジェクト式を指定します。
Destination 必ず指定します。
オートフィルの書き込み先になる Range オブジェクトを指定します。基準となるデータの入ったセル範囲も含むようにします。
Type 省略可能です。XlAutoFillType クラスの定数を使用します。リストの種類を指定します。
使用できる定数は、次に示す XlAutoFillType クラスのいずれかです。
xlFillDays
xlFillFormats
xlFillSeries
xlFillWeekdays
xlGrowthTrend
xlFillCopy
xlFillDefault (既定値)
xlFillMonths
xlFillValues
xlFillYears
xlLinearTrend
この引数に xlFillDefault を指定する、または省略すると、元になるセル範囲に応じて、最も適切な種類のリストが選択されます。
次の例は、シート 1 のセル範囲 A1:A20 に対して、セル範囲 A1:A2 の値を基にしてオートフィルを実行します。この使用例を実行する前に、セル A1 には「1」、セル A2 には「2」を入力しておいてください。
キーのトップと属性の重複を摘出
行方向に、重複を認めるキー(A列)と、その属性データ(B列)が並んでいるとき、指摘の列(C列)に、@.キーの最初の行(トップ)と、同一キーでの属性の重複(重複)を検出して表示します。
マクロのファイルは、別のバージョンですが、指定ブック中の全てのシートで、指定列の「セルデータの重複」をチェック の CheckRepetitionInSheetVBA**.xls を、ダウンロードできます。
キー |
属性 |
指摘 |
AAA |
ア |
トップ |
AAA |
イ |
|
AAA |
ア |
重複 |
AAA |
ウ |
|
BBB |
イ |
トップ |
BBB |
エ |
|
BBB |
オ |
|
BBB |
エ |
重複 |
CCC |
ア |
トップ |
CCC |
イ |
|
CCC |
ウ |
|
解説:
指定列のセルの値を、指定した方法で型変換して、指定したシートの指定した列に貼付ける
ダウンロードして取得した Excel のデータなどには、列に登録されたデータの型が、不適切なことがあります。
例えば、列に登録されたデータの形式が、「文字列」扱いになっているため、数値としてカンマ挿入できなかったり、日付けとして、書式を設定できなかったりすることがあります。
あるいは、文字列の前後に、不要な空白が入っている場合もあります。
これらを、列指定で、一括変換できるように、汎用ツールとして作りました。
ファイル名として使えない文字を削除
参考にさせて頂いたサイト
Excel VBA でファイル名に使えない文字を取り除く・チェックする
https://tanaka-misaki.blogspot.com/2012/02/excel-vba.html
"\", "/", ":", "*", "?", """", "<", ">", "|"
ファイル名に使えない文字が含まれていないかチェックする
https://tsware.jp/tips/tips_557.htm
このルーチンは、ファイル名を変更マクロ に組み込んでいます。
階段昇降
小学校の算数で興味深い問題がありました。
Excel のセルで階段を表現してみました。(階段の方向が上下逆なのはご愛嬌です。)
階段を「3歩上がって、2歩下がる」という繰り返しで上り下りします。
この時、初めて16段目に着くのは何歩目ですか。
解説:
Range.Offset プロパティ
指定された範囲からオフセットした範囲を表すRangeオブジェクトを返します。
構文
expression.Offset (RowOffset, ColumnOffset)
expression は Range オブジェクトを表す変数です。
パラメーター
名前 |
必須 / オプション |
データ型 |
説明 |
RowOffset |
Optional |
バリアント型 |
範囲をオフセットする行の数 (正、負、または 0 (ゼロ) )。 正の値は下方向、負の値は上方向のオフセットを表します。 既定値は 0 です。 |
ColumnOffset |
Optional |
バリアント型 |
範囲をオフセットする列の数 (正、負、または 0 (ゼロ) )。 正の値は右方向、負の値は左方向のオフセットを表します。 既定値は 0 です。 |
例
次の使用例は、シート 1 のアクティブ セルから 3 列右、3 行下のセルをアクティブにします。
Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
シート 1 にタイトル行を持つ表が含まれていると仮定します。 ヘッダー行は選択せず、テーブルのみを選択します。 この例を実行する前に、表の任意のセルがアクティブになっている必要があります。
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
RowOffset または ColumnOffset が 0 の場合、省略できます。
セル D1 を選択する
Range("A1").Offset(, 3).Select
セル A5 を選択する
Range("A1").Offset(4).Select