Excelで特定の列のセルの種類ごとに1行挿入できるようにマクロを使う。苦手だったマクロは検索でコピペを駆使しよう♪


編集部内のノートパソコンの利用状況を仮定してみたExcel形式の実態調査表画面

編集部内のノートパソコンの利用状況を仮定してみた様子

Excelで多数のデータをそれぞれマウス操作で処理する手間を省き一気に終わらせられたら

Officeには興味が極めて乏しい1さんを追いながらマクロにおっかなびっくりと手を出した記録です。

編集長が300人のちょっとしたノートパソコンの利用状況の集計データ作成を知り合いから依頼されたようです。

これは編集長からちょろい初心者でも安心という作業があるんだ、頼んだよと言われてしまい後にひけません。

 

そして、10人目までて手入力していきます。やっつけ仕事だと思いながらここまでできました。

「あ、Windowsバージョンが変わるごとに1行空白入れておいてくれる?後で見やすいから」と編集から1手間増やされたようです。

1さんは、「Excel使えば一瞬だよね?」と気安く言われてしまいます。馬鹿なこと言うなと1さんは内心ムカッとしました。

おなじみのExcelの右クリックからどの方向に行を挿入するかの選択画面の様子

おなじみのExcelの右クリックからどの方向に行を挿入するかの選択画面の様子です。

さらっと言われてしまった1さんはEの列の4行目から1行スキマができるようにします。右クリックをして、1行挿入、下方向を選択です。

行の選択ではなく、セルを選択したので当然ズレができます。

行の選択ではなく、セルを選択したので当然ズレができます。

1さんはちょっとイラっとしました。

これなら1行挿入されてWindowsの種類ごとに1行ずつスキマが作られます。が位置が1行違うのでした

1行挿入されてWindowsの種類ごとに1行ずつスキマが作られます。が位置が1行違うのでした

1さん「Officeの仕事は苦手なんすよ」とつぶやきます。今度はセル1つどころか1行ずれてしまいます。

行ごと指定し、挿入を押して1行挿入できます。

行ごと指定し、挿入を押して1行挿入できます。

1さんは週刊アスキーの連載マンガExcel同情もとい道場をみてはいましたが、実際は連載漫画のように楽しくありません。

この時1さんはサンプルデータを渡されていてまさか300人分、別に新たに入力し、年齢層ごとに改行、メーカーごとに改行、年齢層ごとに改行と複数のデータを作らされるとは予想できなかったのです。

1さんは最初はWindows7と8と10と3回スキマをマウスを使って右クリックするだけだとなんとか頑張ります。

右クリックでこなせると安心していたのですが、だんだん任されたデータ集計は自分の取材中の〆日へ食い込むのは困ると

エクセルの真髄なるサイトの練習問題14(行の挿入・削除の練習)http://excel-ubara.com/excelvba9/EXCELVBA614.html

で必死に短時間でさばく魔法のようなプラグインを探してみたのです。

 

プラグインといってもGoogleChromeのプラグインのように画面のスクリーンショットを撮れるようになるという言い方をします。

Excelのプラグインという呼び方にはズレがありますね。

やはり、Excelでマクロにより自動化できるようにするVBAを作るという言い方が適当でしょう。

率直に言えばマウス操作ではなく、Excelで理解される命令文を作るということですね。ググってみる1さん。

Sub 練習問題14()
  Dim i As Long
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    Select Case Cells(i, 1)
      Case “I”
        Rows(i).Insert
      Case “D”
        Rows(i).Delete
    End Select
  Next
End Sub

サイトの趣旨からであれば、自分で考えて学ぶのが王道ですが、今回はなんとかやりこの場だけを切り抜けるしかありません。

Subという単語はファイル名とかメールで使うSubjectの略称で件名とひとまず理解します。

Cellsとあります。セルの英語表現と理解し、複数形となっているのでシート上のBookのセル全体で使えそうですね。

Rowsは行のことを示し、1行目からスタートし、最後の行までのようですね。

Columns(列)はその対義語になります。

selectはデータベースの勉強では必ず勉強します。条件に合うセルを拾う命令だと想像します。

ITパスポートの勉強をしていた1さんはなんとか乏しい知識で理解しようと必死です。

サイトの問題文で区分が、D の行を削除、区分が、Iの行の前に新規行を挿入とあります。

今回はWindowsのバージョンごとなどで新規行を入れたいので

 

Case “I”
        Rows(i).Insert

をそのまま使えそうです。(数の少ない行から多い行へを降順と呼びます。)

通常ならWindows7の行が終わったら、1行入れる、8の行が終わったら1行入れるの順(つまり降順)になります。

問題文を応用してIの行の後に新規行を挿入する操作が自動化できれば、今回の集計は買ったも同然でしょう。

同じデータではなくデータの集まりに変化がおきたタイミングで改行させたいのが本来にしたいことです。

サイトのVBAのコピペでは指定する列に含まれている文字や数字のある時に全部改行を入れることになります。

これでは、改行だらけの画面です。

Cells(Rows.Count, 1).End(xlUp).Row

これで、最終行まで指定をしているそうです。連続データに1行ずつ挿入という解説までたどり着けました。

連続するデータの区切りごとに1行を挿入したいんです。

 Sub test()
    Dim R As Long
    R = 3
    While (Cells(R, 3) <> "")
        If Cells(R, 3) <> Cells(R - 1, 3) Then
            Rows(R).Insert
            R = R + 1
        End If
        R = R + 1
    Wend
 End Sub

上記のようなサイトを運良くみつけました。Rという変数は任意で、3行目からデータが始まる前提となります。
でもこれをテキストファイルを作り保存したけれど、拡張子はどうやって保存したらよいのでしょうか?

そのままビジュアルベーシックエディターを使う際にインポートすることでテキスト形式でも読み込みを確認します。(初歩な1さんらしい理解です)

運良くWindowsのバージョンごとに改行が入れられる瞬間

運良くWindowsのバージョンごとに改行が入れられる瞬間

ちなみにマクロを実行した結果、アンドゥが効きません。

1さん、冷や汗が出ます。

やはりマスター登録してあるデータは触らずにコピーして別のブックに転写してからマクロは試す必要があります。

Sub SHUKEI()
Dim E As Long
E = 2
While (Cells(E, 5) <> “”)
If Cells(E, 5) <> Cells(E – 1, 5) Then
Rows(E).Insert
E = E + 1
End If
E = E + 1
Wend
End Sub

このように変化させたところうまくいきました。

これで、列の指定しだいで属性ごとに区切りに挿入することができました。

これで、列の指定しだいで属性ごとに区切りに挿入することができました。

これなら応用が効きそうになったVBA

データのD列でソートをして若い順から並べ替えて、年齢層ごとに改行、画面サイズごとに1行空けて改行、メーカーごとにまとめて、メーカーごとに改行と応用できます。

Excel2007ではマクロボタンよりインポートやエクスポートでマクロを読み出し書き出しできるようです。

Excel2007ではマクロボタンよりインポートやエクスポートでマクロを読み出し書き出しできるようです。

拡張子が.basとなっています。Excelのバージョンなどでバラツキが予想されます。

マクロ機能のスタート前の様子です。

マクロ機能のスタート前の様子です。

マクロが苦手でも手入力にばかり束縛されたくないあなたへ

今回は、使った検索キーワード

  • 「Excel2007」 「マクロ命令で行を装入する」(本当に1さんは挿入でなく誤字のまま検索したそうです。後日談)
  • セルの値が変わったら 1行入れる
  • IT用語 マクロの語源

これらの自分に都合の良いExcelの使い方の模範解答を先にもらい、後で自分の環境に合わせた解決策を探ってみました。

たしかに、マウス操作は分かりやすい操作です。しかし繰り返すこと時間が追いつけないことも多い量では非力です。

ぜひ、1さんのヘタレなマクロ操作を参考にしてマクロにも手を広げてみたりすることでExcel操作の能率アップのたたき台のようになれば編集部としても幸いです。

参考サイトなど

エクセルの学校の中の

他マクロに不慣れな編集部の1さんを助けて下さったサイトさまのお知恵に深くお礼を申し上げます。