これが一つのExcelの答え・VBAを使わずにマクロの記録だけで作る

次のような一覧表があります。

Excelではこのような表に対して、追加、変更、削除の3つの操作をしていくことで元データとして管理していくことになりますよね。
それは操作的には、セルを直接操作して入力していくことになります。
そうではなく安全に操作するための操作画面をVBAではなくマクロの記録で作ってみます。

作業データ用意しています。

こちら

Excelの怖いところ

とても大事なのですが、セルに直接操作するというのは、果たしてデータお一覧を管理するうえで安全なのでしょうか。
もしも、1行間違ったセルに入力したら、操作中にマウス操作を誤ってセルの移動をしてしまったら。
既存データを壊してしまうことになりかねないのです。
これは怖いことで、間違った操作をしたことに後から気付いてもその前の正しいデータがなんだったのか、その時点では思い出せないことも多いのです。
そうなるともしかしたら売上金額が1行当たり平均1000円だったとして、失われたデータが10000円だったとしたら、大きなデータの違いが浮き彫りになりますし、何より履歴としておかしくなります。
これがExcelでデータを管理する怖いところなのです。

操作画面

そこで、データの入力間違いを防ぐために次のような入力画面を用意することになります。

操作画面を考える

このような操作画面を組み立てるうえで、考えておかなければならないことがあります。
一覧表の操作は、データ追加(新規入力)、変更、削除の3つからなります。
そのうちの変更と削除に関しては、どのデータを操作するのか指定するためのデータ検索の作業が必要になります。

  • 新規入力
    空欄の入力ボックスに入力された内容を一覧表の一番下に追加する機能です。
  • データ検索
    変更や削除をする際に、どのデータを対象とするか選択する必要があります。
    今回は入力されたID番号を参照し、どのデータを対象とするか選び、その内容を入力ボックスにセットします。
  • データ書き込み
    これは実際には変更の動作です。
    入力ボックスに入力された内容を、元データの指定した行のデータに上書きします。
  • データ削除
    元データの指定した行の範囲を削除し上に詰めます。

データ書き込みしやすくする

追加と変更の場合は、入力ボックスの内容を一覧表に反映しますが、一覧表は1行、入力ボックスは1列なので、縦横が違います。
これでは、書き込む時に大変なので、次のようなIF関数を使った計算式の領域を作成します。

縦領域を横領域にリンクしながら変換し、空白の場合は空白の処理もしてしまいます。
計算式作成前に一覧表の中のどこかのデータを入力ボックスに入力しておくとよいでしょう。
あとはこの行を新規追加行として、一覧表の一番下の行に値で貼り付けるだけです。

一覧表の追加や変更する領域

一覧表の一番下の領域は、どこでしょうか。
追加・削除するたびにどんどん変わっていきますよね。
そう考えると。
「A列の個数を数えて、その個数の一つ下」と定義ができます。
そこで、新規追加行数として、次のようなセルを用意するといいのではないでしょうか。

さらに、その範囲として、新規追加はID番号を新規発行し、A列へ、B列から横に5つのセルを貼り付けます。
IDの書き込みセルとデータの書き込み範囲を次のように求めておきます。

では今度は変更する行です。
変更する行は、元の表からIDを検索して、その該当行ですので、MATCH関数でIDを一覧表から検索して、その行番号を出せばいいのです。
MATCH関数は該当するデータの位置を求める関数なのですが、列全体を範囲にすると行数を求める関数として使えるのですね。

そしてその行数に対する範囲を求めます。
変更する範囲はA列からF列まで書き込んでよいでしょう。
削除する範囲もA列からF列までですのでA列からにしておけば削除の時にも使えます。

新規追加

新規追加は、まず、ID追加セルに今まで登場していない番号を書き込みます。
今まで登場していない番号で一番簡単なのは、今までの最大値に1を足したものです。
これは数値データなら使える方法で、文字データの場合はなかなか難しいので工夫が必要になります。
同時編集をしないのであれば、NOW関数をTEXT関数で年月日時分秒を14桁の数値に置き換えたものを使えば、1秒空けて登録しているのであれば重複はしません。
次のように次のID欄を作成しましょう。

ここで、このセルL8に求めた次のID番号をコピーします。

では、コピー先であるID追加セルに移動してみましょう。
どんどん追加行は変わっていくので、セルをクリックして指定することはできません。
セルL2に書いてあるセルに移動する方法。
セルL2に入力されているのはただの文字ですので、これをセルだとExcelに分からせるためには、INDIRECT関数を使います。
それを名前ボックスに入力します。

=INDIRECT(L2)

それでEnterすれば、L12に記載されているセルに移動できます。
名前ボックスに「A1」ろ入力すればセルA1がアクティブセルになりますが、その応用で、セルを示すINDIRECT関数やOFFSET関数を入力するとそのセルに移動できるのですね。

このセルに値として貼り付けます。

では、同様に書き込みデータの日付データから右側を、追加データ範囲に値で貼り付けます。

最後に、入力ボックスをクリアし、次の入力がしやすいように入力ボックスの一番目のセルにカーソルを合わせて完了です。

ここまでが追加のアクションです。
これをマクロの記録で記録します。
記録の前に、データを入力しておきます。
そして、表示タブのマクロの中のマクロの記録で記録を始めます。
マクロ名は「追加」にするとよいでしょう。
次の手順を実行します。

  1. セルL8をコピー
  2. 名前ボックスに=INDIRECT(L2)と入力
  3. 値貼り付け
  4. セル範囲I10からM10をコピー
  5. 名前ボックスに=INDIRECT(L3)と入力
  6. 値貼り付け
  7. セル範囲I1からI6までDeleteキーでクリア
  8. セルI1にカーソルを合わせる

ここでマクロ記録終了です。
このようにマクロ記録前は手順を整理しておきましょう。
適当なデータを入力してデータが追加されるか動作チェックをしておきましょう。

データ検索

データ検索のアクションは、すでにID欄にデータを入力した時点で元のデータのどのデータ行か求めることはできるようになっています。
ここで必要なのは、入力ボックスにその値を書き込むことです。
その値の行は、変更する行として入っています。
それぞれの元データの範囲からデータを取り出すのでINDEX関数を以下のように使った式を作成し横に5つコピーします。

さらに、入力ボックスは縦なので、その参照した値を縦方向に変換する式を作成します。

検索の作業は、この縦方向にした元データを入力ボックスに値貼り付けする作業です。

では「検索」という名前で次の手順のマクロを登録しましょう。

  1. N2からN6の範囲をコピー
  2. I2からI6の範囲に値貼り付け
  3. セルI2を選択

変更

変更の作業は、検索した後に表示された入力ボックス内のデータを書き換え、すると自動で書き込みデータ範囲に反映するので、そのデータを元データの変更する範囲に値で貼り付ける作業です。
つまり、書き込みデータ範囲を元データの変更する範囲に値で貼り付けることをマクロに記録すればよいです。また、新規追加時のように、入力ボックスは次の作業のためにクリアしておきましょう。
「変更」マクロを記録します。

  1. H6からM6の範囲をコピー
  2. 名前ボックスに「=INDIRECT(L6)」と入力し、Enter
  3. 値貼り付け
  4. セル範囲I1からI6までDeleteキーでクリア
  5. セルI1にカーソルを合わせる

削除

削除の作業は、検索した後に元データの変更する範囲を削除して上に詰める作業です。
また、検索した時点で入力ボックスに値が入りますので、これもクリアします。

  1. 名前ボックスに「=INDIRECT(L6)」と入力し、Enter
  2. 上に詰め削除。(Ctrl+=のショートカットキーだとやりやすいです)
  3. セル範囲I1からI6までDeleteキーでクリア
  4. セルI1にカーソルを合わせる

ボタンにマクロを登録

あとはボタンをうまく配置して、それぞれのボタンを右クリックしてマクロを登録するだけです。
動作で使う追加する行や、追加するデータの範囲は文字を白くして見えなくします。
これで完成です。
マクロブックとして保存しましょう。

まとめ

一覧表に入力、変更、削除をする仕組みをVBAなしで実現できるとは思っていませんでしたが、実際に手順を整理した後、Excelでならどういう手順で実現するかを考えていったところ、今回の答えにたどり着きました。
この手順を考えるということがいかに重要か思い知りました。
また、ID番号がなければ今回は検索ができないので、変更や削除ができませんでした。
インデックス番号をデータに振っておくことも大事ですね。
VBAを使わないことについては否定的なご意見もあると思いますが、おそらく開発スピードはこのマクロの記録でする方法の方が早いと思います。
しかし、絶対的にVBAでしかできないのが、例えば、今回であれば検索してIDが見つからなかったら動作させないという分岐とか、検索して見つかったものが複数あってそれを一気に変更や削除をする繰り返し作業で、これらはマクロの記録ではできません。
このあたりはうまく使い分けていければいいかと思います。
VBAを使わないことで、それはシステムではないかもしれません。でも私がみなさんに作っていただきたいのは大きなシステムではなく、ほんの少しの作業自体をなくす、0にする「ツール」を作成していただきたいので、このプログラムなしでできる「マクロの記録」機能はだいじにしていきたいと思っています。
しかし、今回紹介した機能は実はフォームという機能でExcelにすでにあります。

Alt+Qを押してフォームと入力し、Enterすれば出てきます。

ただこの機能だと、入力規則やリスト入力ができません。
そのような時に、ちょっと使い難いなと思ったら、マクロの記録で簡単にツールを開発するのもよいのかもしれませんね。

サトウヨシヒロ

Excelを中心としたMicrosoft Officeの魔法使い。
仙台市在住のフリーランスのパソコンインストラクターです。
1969年1月生まれ。
Excelを黎明期から30年近く使っています。Wordも使えます。
出版社様と一緒にExcelの情報発信もしています。
最近は羊に夢中です。
2019年はRPAと業務効率化を勉強しています。

Twitterでも業務で使える便利なOffice技をめっちゃつぶやくツイッタラーです。@yosatonet

コメントを残す

メールアドレスが公開されることはありません。