36 マクロをカスタマイズするVBA入門
【エクセル時短】記録したマクロを自分仕様に！ 「VBA」によるカスタマイズに挑戦
マクロの記録はしてみたものの、あまり時短になっていないような......。そんなときは、マクロのカスタマイズに挑戦してみましょう。例として、PDF形式で保存するときに自動でワークシート名がファイル名に付加されるようにします。
前回では、ExcelファイルをPDF形式で保存するマクロを作成しました。しかし、「いちいちマクロを表示して実行するのでは、通常の方法でPDFにするのと時間的にあまり変わらないのでは？」と感じた方もいるでしょう。
それはその通りで、［マクロの記録］で作成したマクロをそのまま使うのでは、あまり時短にはなりません。ただ、少し手を加えることで、実用的なマクロに仕上げることが可能です。例えば、「ワークシート名をファイル名に自動で付加し、ワンクリックでPDFに変換する」といったことができれば、十分な効率化が期待できるでしょう。
【エクセル時短】第36回では、記録したマクロのカスタマイズに挑戦してみましょう。さらに、マクロをすばやく実行できるように、クイックアクセスツールバーに登録します。
VBAの編集画面を表示する
Excelのマクロは、「VBA」（Visual Basic for Applications）というプログラミング言語で記述されています。よって、マクロをカスタマイズするには、VBAを編集することになります。
一気に難しい印象がしてきたかもしれませんが、ここで解説するとおりに進めれば大丈夫。［Alt］＋［F11］キーを押し、VBAの編集画面である「VBE」（Visual Basic Editor）を表示してみましょう。
①マクロを有効にしてExcelブックを表示する
前回で作成したマクロ有効ブック（.xlsm）を開きます。最初はセキュリティの警告が表示されるので、［コンテンツの有効化］をクリックし、マクロを有効化します。
②VBAの編集画面を表示する
マクロ有効ブックが開いたら、そのまま［Alt］＋［F11］キーを押してVBAの編集画面を表示します。続いて、画面左側のリストにある「Module1」をダブルクリックします。表示されたウィンドウは最大化しておいてください。
マクロをカスタマイズする
このVBAの編集画面には、ざっくり言うと「アクティブなシートを「営業実績.pdf」というファイル名でデスクトップに保存する』という意味の構文が書いてあります。この状態では、どのシートを開いていても同じファイル名で保存されてしまうので、具合がよくありません。
そこで、ワークシート名を付加して保存できるように、マクロをカスタマイズしてみましょう。
③VBAを編集する
「営業実績」と「.pdf」の間に「" & ActiveSheet.Name & "」と半角で入力します。
この行を先頭から見ると、「"C:¥Users¥（ユーザー名）¥Desktop¥営業実績" & ActiveSheet.Name & ".pdf"」となります。「ActiveSheet.Name」は「アクティブシートの名前」という意味で 、「&」で「営業実績」と「ActiveSheet.Name」と「.pdf」を連結しています。なお、「（ユーザー名）」の部分は使っているPCのアカウントによって変わります。
④カスタマイズしたマクロを実行する
①［上書き保存］ボタンをクリックし、カスタマイズしたマクロを保存しておきましょう。マクロを実行するには②［Sub/ユーザーフォームの実行］ボタンをクリックします。
⑤ワークシート名を付加したPDFファイル作成された
ワークシート名を付加したPDFファイルが保存されました。アクティブシートを切り替えれば、そのワークシート名が自動で付加されます。 画面右上の［閉じる］ボタンをクリックして、VBAの編集画面を閉じておきます。
クイックアクセスツールバーにマクロを登録する
マクロをカスタマイズできました。最後にクイックアクセスツールバーにマクロを登録して、すばやく実行できるようにしましょう。［Excelのオプション］から登録します。
⑥［Excelのオプション］-［クイックアクセスツールバー］を表示する
クイックアクセスツールバーの［クイックアクセスツールバーのユーザー設定］ボタンをクリックして、［その他のコマンド］を選択します。
⑦クイックアクセスツールバーにマクロを登録する
［Excelのオプション］-［クイックアクセスツールバー］が表示されました。①［コマンドの選択］から［マクロ］を選択します。②リストからマクロを選択し、③［追加］ボタンをクリックすると、クイックアクセスツールバーのリストにマクロが追加されます。最後に④［OK］ボタンをクリックします。
⑧クイックアクセスツールバーにマクロが登録された
クイックアクセスツールバーにマクロが登録されました。今後はこのボタンをクリックするだけでマクロを実行できます。
マクロのカスタマイズ、いかがでしたでしょうか？ VBAと聞いただけで「無理！」と思ってしまうかもしれませんが、この程度の編集だけでも、意外と便利になるものです。日々の業務にぜひ応用してみてください。
37 【エクセル時短】行や列を隠すときに「非表示」はNG !? できる人は「グループ化」を使っている
今は必要ないけど、削除できない行や列をつい「非表示」にしていませんか？ 実はその操作が、ムダな手間を生んでしまっているかもしれません。今回は、できる人が使っている「グループ化」機能を紹介します。
縦や横に長くなったExcelの表のうち、あまり必要のない行や列を隠しておきたい。そんなとき、とりあえず「非表示」にしていませんか？
非表示はExcelの基本として広く知られていますし、削除できない行や列を残しておくために有効です。ただ、実はあまり、おすすめできる機能ではないのです。
では、どうすれば？ 【エクセル時短】第37回では、非表示とあわせて知っておきたい「グループ化」の機能と、それぞれの違いについて解説します。
■非表示には2つのデメリットがある
最初に、どうして非表示がおすすめできないのかを説明しましょう。デメリットは大きく分けて2つあります。
1つは「表のどの部分が非表示になっているのかがわかりにくい」こと。行や列の番号を注意深く見ないと、隠れた部分があることに気づけません。
この例では4〜8行目を非表示にしています。矢印で示した部分に注目し、行番号が飛んでいることに気づかない限り、非表示の行があることがわかりません。また、セルN20の合計値が本当に正しいのか、判断に迷います（この例では「=SUBTOTAL(9,N4:N19)」と入力してあるため、非表示の行の値を含めて合計されています）。
もう1つは「再表示する操作に手間がかかる」なこと。先ほどの例で非表示になっていた行を見たいとき、以下の操作が必要になります。非表示の行や列が何か所もあると面倒です。
①非表示の行を含む行（ここでは3〜9行目）をドラッグして選択し、②選択範囲を右クリックし、③［再表示］をクリックする。再表示のために3回のマウス操作が必要です。
■グループ化なら隠れている部分が明確に
こうした非表示のデメリットをカバーできるのが「グループ化」です。さっそく使い方を見ていきましょう。
①行をグループ化する
①非表示にしたい行や列を選択し、［データ］タブにある②［アウトライン］→③［グループ化］をクリックします。
②グループ化した行を非表示にする
行がグループ化され、ワークシートの左側にボタンが表示されます。［−］をクリックすると、行を非表示にできます。
③グループ化した行を非表示にできた
④列をグループ化する
同様の操作で、列もグループ化できます。列の場合はワークシートの上部にボタンが表示され、［-］をクリックすれば非表示にできます。
［-］と［+］のアイコンを見て、「こういうExcelファイル、受け取ったことがある！」と思った人もいるかもしれませんね。行や列を非表示にするから「非表示」の機能、と覚えてしまうのも無理はないのですが、多くのExcel中級者は、非表示にするなら「グループ化」を活用しているものです。
グループ化の注意点としては、連続する行や列をグループ化すると1つのグループに追加されていく点が挙げられます、複数のグループを作成したい場合は、グループの区切りとなる小計行（列）、もしくは見出し行（列）を用意しておきましょう。空白の行や列でもかまいません。
特定の行や列を一時的に非表示にしたいときに重宝する「グループ化」。「非表示」ばかり使っていたという人は、ぜひ試してみてください。
38 【エクセル時短】必ず使いたくなる！ 実務にすぐ効く「テーブル」の3つのメリット
Excelの「テーブル」は、表をデータのまとまりとして管理できる機能ですが、難しそうだから使っていないという人も多いかもしれません。今回はテーブル機能のメリットについて解説します。
Excelの「テーブル」機能をご存じですか？ ワークシート内に作成した表を、関連するデータのまとまりとして扱いやすくするための機能です。
ただ、別に表をテーブルに変換しなくても、計算などが普通に行えます。「複雑な処理をするわけでもないのに、わざわざ難しそうな機能を使う必要はないのでは？」と感じる人もいるかもしれません。
しかし、実は簡単に利用でき、業務に役立つメリットもしっかりあるのです。「使わず嫌い」はやめて、ちょっと試してみませんか？ 【エクセル時短】第38回では、テーブルの「3つのメリット」について解説します。
1. 表の管理が楽になる
テーブルのもっともわかりやすいメリットは、表の管理が簡単になることでしょう。
既存の表をテーブルに変換すると、標準でフィルターボタンが追加され、1行おきに色分けされます。また、行や列の追加に応じてテーブルの範囲が拡張されるため、書式やフィルター範囲をいちいち再設定する必要がなくなります。
さっそくテーブルを使ってみましょう。ここでは、作成済みの表をテーブルに変換してみます。
①表をテーブルに変換する
①表内の任意のセルを選択しておき、［挿入］タブの②［テーブル］ボタンをクリックします。
②テーブルに変換するデータ範囲を指定する
［テーブルの作成］ダイアログボックスが表示されました。通常、テーブルに変換するデータ範囲は自動的に判定されるので、特に変更する必要はありません。もし、表に見出しがない場合は、［先頭行をテーブルの見出しとして使用する］のチェックマークを外しておけば、自動的に見出し行が追加されます。
③表がテーブルに変換された
前の画面で［OK］をクリックすると、表がテーブルに変換されます。書式が設定され、フィルターボタンも追加されていますね。デザインやフィルターボタンの有無は［テーブルツール］-［デザイン］タブで変更可能です。
④テーブルに行を追加する
テーブルのいちばん下に行を追加してみましょう。［注文日］列に「2017/11/30」と入力すると、自動的にテーブルの範囲が拡張されます。書式やフィルターの範囲を再設定する必要はありません。
⑤テーブルに列を追加する
今度は、テーブルの右端に列を追加してみましょう。セルG1に「備考」と入力すると、行と同じように自動的にテーブルの範囲が拡張されました。
既存の表に空白の行や列が含まれていると、［テーブルの作成］ダイアログボックスで1つのテーブルとして判定されません。データ範囲を指定し直すこともできますが、空白はあらかじめ削除しておくことをおすすめします。
2. すばやく数式を入力できる
2つ目のメリットは、すばやく数式を入力できることです。先ほど、テーブルのいちばん下に追加した行に注目してください。
［売上］列に「0」と表示されていますが、これは「=D32*E32」が自動的に入力された結果です。［売上］列の上の行には［出荷個数］×［単価］を意味する数式が入力されているため、同様の数式が自動的に入力されるという仕組みです。
今度は、数式を一気に入力してみましょう。
⑥テーブルに列を挿入する
列を挿入したい位置の右側の列番号を右クリックし、［挿入］をクリックします。挿入された列には［列1］という見出しが自動的に設定されます。
⑦追加した列に数式を入力する
「売上」列の税込金額を算出するため、セルG2に「=F2*1.08」という数式を入力します。
⑧全部の行に数式が入力された
数式の入力後に［Enter］キーを押すと、G列のすべての行に同様の数式が入力され、結果が表示されました。
3. データの入力規則も自動設定される
3つ目のメリットは、テーブルではデータの入力規則も自動的に引き継がれる点です。ここまでの例の表では、［製品コード］列にリストから選択できる入力規則が設定されていますが、これは新しい行にも自動設定されます。
身に覚えのある人も多いと思いますが、通常の表で入力規則の設定されたセルのコピー＆ペーストを繰り返すと、条件が崩れて大幅な修正が必要になることがあります。入力規則が自動設定されるのは、非常にありがたいメリットでもあります。正しく自動設定されているか、確認してみましょう。
⑨［データの入力規則］ダイアログボックスを表示する
①追加した行にあるセルB32を選択しておき、［データ］タブの②［データの入力規則］ボタンをクリックします。
⑩データの入力規則を確認する
［データの入力規則］ダイアログボックスの［元の値］に、リストとして参照しているセル範囲が表示されています。このように、テーブルでは追加した行にも入力規則が正しく引き継がれます。
テーブルの3つのメリット、いかがでしたか？ 簡単に変換できるわりに、実務で役立つメリットが多いことに気付くと思います。次回は関数式などで役立つ、もう一歩踏み込んだテーブルの活用テクニックを紹介しましょう。
39 【エクセル時短】データに合わせて伸縮自在！ ドロップダウンリストをメンテナンス不要にするワザ
決まったデータを入力するときに便利なドロップダウンリストですが、データが増減するときにはどうすればいいのでしょうか？ ポイントは「テーブル」と「名前」の活用です。
前回はドロップダウンリストの基本を紹介しましたが、「リスト内の項目が徐々に増えていく」というケースもあると思います。新製品の名前や、新設された部署名などが想定できますが、そのようなケースにもっとも適したドロップダウンリストの作成方法、わかりますか？
あきらめて手入力するなんてもったいない！ 【エクセル時短】第41回では、リストの項目の追加・削除に自動的に対応できるドロップダウンリストの作成方法を紹介します。
伸縮自在のリストが必要なら「テーブル」＋「名前」
項目を追加・削除する可能性のあるリストを参照してドロップダウンリストにするには、「テーブル」と「名前」を上手く使います。テーブルについては、第38回と第39回で解説しました。
名前とは、特定のセル範囲を示す文字列のこと。定義した名前は、ドロップダウンリストで指定するセル範囲の代わりに使えます。伸縮自在なテーブルと名前を組み合わせて、メンテナンス不要のドロップダウンリストを作成しようという算段です。
ここでは、以下の画面にある［発注表］シートで入力する製品コードを、ドロップダウンリストで選択できるようにします。最初に［製品コード一覧］シートにある「テーブル2」の［製品コード］列に名前を定義しましょう。
①［新しい名前］ダイアログボックスを表示する
［製品コード］列を選択し、［数式］タブにある［名前の定義］ボタンをクリックします。
②テーブルの列に名前を定義する
［新しい名前］ダイアログボックスが表示されました。ここでは「製品コード」という名前にします。［参照範囲］には「=テーブル2[製品コード]」と自動的に入力されます。これは第39回でも紹介した「構造化参照」という仕組みです。
定義した名前でドロップダウンリストを作る
続けて、定義した名前を使ってドロップダウンリストを作成しましょう。［発注表］シートに切り替えて、セルにデータの入力規則を設定します。［データの入力規則］ダイアログボックスを表示して［リスト］を選択するまでは、前回解説した通りです。
③［名前の貼り付け］ダイアログボックスを表示する
ドロップダウンリストを作成する［発注表］シートに切り替えておきます。ドロップダウンリストを作成したいセル範囲を選択し、［データの入力規則］ダイアログボックスを表示しましょう。そして［設定］タブの［入力値の種類］から［リスト］を選択し、［元の値］の入力欄をクリックして［F3］キーを押します。
④名前を貼り付ける
［名前の貼り付け］ダイアログボックスが表示されました。先ほど定義した名前「製品コード」をクリックして［OK］ボタンをクリックします。
⑤データの入力規則を設定する
［名前の貼り付け］ダイアログボックスが閉じ、［データの入力規則］ダイアログボックスが表示されました。［元の値］に「=製品コード」と入力されていることがわかります。［OK］ボタンをクリックします。
⑥リスト項目にデータを追加する
ドロップダウンリストが正しく動作するか、参照するリストに項目を追加して確かめてみましょう。新しい製品コードを入力すると、［製品コード一覧］のテーブルは自動的に伸びます。
⑦ドロップダウンリストを確認する
［発注表］シートに切り替えてドロップダウンリストを表示します。追加した製品コード（S1-003CN2）が、リストに自動的に追加されていることがわかります。
HINT 定義した「名前」を削除するには
名前として定義したセル範囲を削除したい場合は、［名前の管理］ダイアログボックスを使います。［数式］タブの［名前の管理］ボタンをクリックしましょう。名前の削除のほか、セル範囲の編集なども行えます。
削除したい名前を選択し、［削除］ボタンをクリックします。
40 【エクセル時短】縦横で交わるデータを取り出す！「クロス抽出」を実現する関数の組み合わせとは？
行と列のそれぞれのデータを条件として、クロスする箇所のデータを取り出す方法を知っていますか？ 今回はExcelで「クロス集計」を実現する関数の組み合わせについて解説します。
Excelで作成した表には、縦方向（行）と横方向（列）にデータが並んでいますよね。その表から「特定の値に対応するデータを取り出す」と聞いて、真っ先に思い浮かぶのは「VLOOKUP（ブイ・ルックアップ）関数」だと思います。
しかし、特定の行と列がクロスする箇所、つまり「2つの値に対応するデータを取り出す」場合、VLOOKUP関数だけでは、ちょっとやりにくいのです。表の形式によっては、VLOOKUP関数が使えないこともあります。
【エクセル時短】第42回では、このような「クロス抽出」を関数の組み合わせで実現するテクニックを紹介します！
クロス抽出でVLOOKUP関数をどう使う？
ここでは例として、以下のような表を用意しました。縦方向に「日付」、横方向に「地区」があり、それぞれを値として指定すると「担当者」が取り出される、という具合です。
セルH2に「1月29日」、セルH3に「名古屋」という2つの値を指定すると、セルH5に担当者として「加藤」が抽出されるようにします。
ここで、VLOOKUP関数の構文をあらためて見てみます。
VLOOKUP（検索値, 範囲, 列番号, 検索の型）
引数［検索値］には「H2」、引数［範囲］には「A2:E13」と指定するとして......引数［列番号］はどうしましょうか？
「名古屋」は3列目なので「3」と指定すれば担当者を求められますが、いちいち何列目かを数えて関数式を修正するのはスマートではありません。この問題は、もう1つの関数「MATCH（マッチ）関数」が解決してくれます！
MATCH関数でデータの位置を数値化
MATCH関数は、「指定した条件に一致するデータがセル範囲の何番目にあるか」を求める関数です。構文は以下のとおり。
MATCH（検索値, 検査範囲, 照合の種類）
引数［検査範囲］の先頭のセルの位置を1として数え、引数［検索値］が何番目にあるかを数えます。完全一致のデータを検索する場合、引数［照合の種類］には「0」を指定します。
先ほどの表で「地区」が何番目にあるかを求めるには、以下のような関数式になります。これをVLOOKUP関数と組み合わせる手順を見てみましょう。
=MATCH（H3, A2:E2, 0）
①MATCH関数で「地区」を検索する
まずはMATCH関数の働きをわかりやすくするため、条件となるセルの右側にMATCH関数だけを入力して結果を確認します。セルI3に「=MATCH(H3,A2:E2,0)」と入力します。ここではMATCH関数の結果をVLOOKUP関数の引数に利用したいので、引数［検査範囲］には「A2:E2」と指定しています。
②MATCH関数の結果を確認する
MATCH関数の結果として、セルI3に「3」と表示されました。「名古屋」の列番号が数値として取り出されたことにより、VLOOKUP関数の引数として使えます。
③VLOOKUP関数とMATCH関数を組み合わせる
セルH5に、VLOOKUP関数とMATCH関数を組み合わせた以下のような関数式を入力します。引数［列番号］をMATCH関数で指定するわけですね。
=VLOOKUP（H2, A2:E13, MATCH(H3,A2:E2,0), FALSE）
④VLOOKUP関数とMATCH関数でクロス抽出ができた
意図どおり、クロス抽出が行えました！
条件を変更しても、正しくデータが取り出されることがわかります。
INDEX関数＋MATCH関数も定番の組み合わせ
ここまではVLOOKUP関数を使いましたが、クロス抽出を実現する関数の組み合わせとしては、INDEX（インデックス）関数とMATCH関数もあります。INDEX関数の構文は以下のとおり。
INDEX（参照, 行番号, 列番号, 領域番号）
INDEX関数は「配列形式」と「セル参照形式」の2つの使い方ができる関数で、上記はセル参照形式での構文です。引数［範囲］のうち、指定した引数［行番号］と引数［列番号］が交わる箇所のデータを取り出します。まさにクロス抽出のための関数ですね。参照するセル範囲が1つの場合、引数［領域番号］は省略しても結果は変わりません。
まずはINDEX関数の働きを見てみましょう。
表の「第5回」に相当する5行目、「広島」に相当する3列目のデータを取り出す、という使い方です。「=INDEX(B3:E13,5,3)」と指定すると、担当者として「大村」が得られます。
ただ、引数［行番号］に「5」、引数［列番号］に「3」などと、いちいち数値に変換して指定するのでは実践的ではありませんよね。そこで、MATCH関数の出番です。INDEX関数とMATCH関数を以下のように組み合わせれば、1つの関数式でクロス抽出が可能になります。
=INDEX（B3:E13, MATCH(I2,F3:F13,0), MATCH(I3,B2:E2,0)）
セルH5に、上記の関数式を入力します。INDEX関数の引数［行番号］と引数［列番号］をMATCH関数で指定するわけですね。
1つの関数式で、意図どおりのクロス抽出ができました！
ちなみに、VLOOKUP関数＋MATCH関数の例とは異なり、表の左端にある「日付」ではなく、右端に追加した「開催回数」を条件にしていることにお気づきでしょうか？
VLOOKUP関数では引数［検索値］が表の左端にある必要がありますが、INDEX関数なら、そうした制約はありません。INDEX関数＋MATCH関数のほうが、より汎用的に使える組み合わせと覚えておいてください。
関数リファレンス
VLOOKUP関数で範囲を縦方向に検索する
商品番号の一覧表を検索して商品の価格を取り出すなど、範囲を縦方向に検索して一致する値を求めるVLOOKUP（ブイ・ルックアップ）関数の使い方を解説します。
関数リファレンス
MATCH関数で検査値の相対位置を求める
指定した検査値が、指定した検査範囲のなかの何番目のセルであるかを求める、MATCH関数の使い方を解説します。
関数リファレンス
INDEX関数で指定した位置の値を求める
配列のなかの、指定した行番号と列番号の位置にある値を求める、INDEX関数の使い方を解説します。