実務で使える！Excelワザ10選！

こんにちは。経営コンサルタントのJunです。

調査・分析、作業管理などを行っていくにあたり、Excelはよく使うツールです。Excelの使い方次第で業務効率が大幅にアップできるといっても過言ではありません。そこで今回は、私がよく使う、実務で使えるExcelワザを皆さんにシェアしたいと思います。

Excel作業の前の企画作成については、こちらの記事をご参照ください。

1. 表作成のショートカットコンボ

Excelで表を作成する際の罫線や色設定のショートカットコンボです。

これができるようになるとマウスを使うことなく、短時間で表を作成することができます。

手順はこちらです。

①表を全選択する。（Ctrl + Shift + →、↓）
②「セルの書式設定」を開く。（Ctrl + 1）
③外枠の罫線を引く。（Alt + O）
④罫線のスタイルを変更する。（Alt + S、スペース）
⑤内枠の罫線を引く。（横線Alt + H、縦線Alt + V）
⑥表のヘッダの色を設定する。（Alt + C、スペース）

１つずつ解説します。

①表を全選択する。（Ctrl + Shift + →、↓）

まず、表の左上端にカーソルを置きます。

「Ctrl」と「Shift」ボタンを押したまま、「→」ボタンを押します。

「Ctrl」と「Shift」ボタンを押したまま、「↓」ボタンを押します。

これで表全体を選択することが出来ました。

②「セルの書式設定」を開く。（Ctrl + 1）

表全体を全選択した状態で、「Ctrl」ボタンを押しながら「1」ボタンを押します。下記の様に「セルの書式設定」が開きます。

③外枠の罫線を引く。（Alt + O）

「←」ボタンを押して、「罫線」タブに移動します。

「Alt」ボタンを押しながら、「O」ボタンを押します。これで表の外側に実線が引けます。

④罫線のスタイルを変更する。（Alt + S、スペース）

表の内側の線を引くために、罫線のスタイルを変更します。

「Alt」ボタンを押しながら、「S」ボタンを押します。

「スタイル」と書いてあるボックス内を矢印ボタンで移動します。引きたい罫線のスタイルを決めたら、「スペース」ボタンを押します。

⑤内枠の罫線を引く。（横線Alt + H、縦線Alt + V）

横線を引きます。「Alt」ボタンを押しながら、「H」ボタンを押します。

縦線を引きます。「Alt」ボタンを押しながら、「V」ボタンを押します。

「Enter」ボタンを押すと、罫線が引けます。

⑥表のヘッダの色を設定する。（Alt + C、スペース）

表の左上端にカーソルが合っている状態で、「Ctrl」と「Shift」ボタンを押しながら、「→」ボタンを押して、表のヘッダを選択します。

表のヘッダの色を付けるため、「Ctrl」ボタンを押しながら「1」ボタンを押します。

「→」ボタンを押して、「塗りつぶし」タブへ移動します。

「Alt」ボタンを押しながら「C」ボタンを押します。背景色を選択できるので、矢印ボタンで、設定したい色を選びます。

「スペース」ボタンを押します。色が選択できます。

「Tab」ボタンを5回押すと、「OK」ボタンが選択できるので、「Enter」ボタンを押し、色を塗ります。完成！

2. 条件付き書式の活用

例えば下記の様なタスク表を作成した時、「インタビュー準備」や「インタビュー実施」が縦に並んでいて、2種類のタスクがあることが、パッと見ただけではわかりません。また、「完了」しているタスクも、「対応中」「未着手」のタスクも白抜きで表示しており、「完了」しているタスクが一目でわかりません。

そこで、見やすい表にするために、下記のような表示になるよう、条件付き書式を設定します。

「タスク分類」は下のように条件付き書式を設定しています。

①数式を入力する。（=$C3=$C2）
②「フォント」タブで、「色」を「白」に設定する。
⑦「罫線」タブで、罫線の上辺の設定を無くす。

数式の入力例です。

フォントを「白」に設定します。

「罫線」の上辺をクリックして、上辺の設定を無くします。

「タスク分類」が下記のように、真上のセルと同一内容である場合に、フォントは白くなり、罫線の上辺が無くなります。タスクは「インタビュー準備」と「インタビュー実施」の2種類のみであることが一目でわかるようになりました。

「状況」は下記の様に設定しています。

①数式を入力する。（=$F3="完了"）
②「塗りつぶし」タブで、「色」を「グレー」に設定する。

数式の入力例です。

塗りつぶしの色を「グレー」に設定します。

「完了」しているタスクはグレーで塗りつぶされ、完了していることが一目でわかります。

3. iserror関数＋vlookup関数の汎用コンボ

vlookup関数は、「検索値」に紐づいたセルの情報を取得して表示することができます。

例えば、システム調査の場合、伝票番号に紐づく、各伝票項目をvlookup関数で整理することがあります。

システムエラーが発生し、エラーとなった伝票番号がエラーログから分かります。（下図B列）

伝票番号だけでは、どのような伝票なのか分からないため、伝票番号ごとに、入力ユーザや転記日付、伝票ヘッダテキスト、取引先、金額などを整理することはよくあります。

「伝票ヘッダテーブル」や「伝票明細テーブル」などのデータベースをExcelへダウンロードし、vlookup関数で値を取得し、情報を整理します。

上の例ですと、下記の様な関数入力をして値を取得しています。

入力ユーザ：　=VLOOKUP(B4,I:J,2,FALSE)
転記日付：　　=VLOOKUP(B4,I:K,3,FALSE)
伝票ヘッダ：　=VLOOKUP(B4,I:L,4,FALSE)
取引先：　　　=VLOOKUP(B4,N:O,2,FALSE)
金額：　　　　=VLOOKUP(B4,N:P,3,FALSE)

counta関数などで代用はできますが、iserror関数とvlookup関数を組み合わせ、値が取得できているか判定できるようにしておくと、汎用的に使えます。

例えば、債権自動消込機能にて消込処理されたはずの伝票が、実際には消込がなされておらず、未消込状態で残ってしまった伝票を調査することがあります。

下図B列に記載されている伝票番号が、債権自動消込機能で消込処理されたはずの伝票です。しかし、右の「消込テーブル」には一部の伝票しか格納されておらず、実際には3伝票しか消し込まれていません。

そこで、未消込状態で残ってしまった伝票を調べるために、iserror関数とvlookup関数を使います。

「実際に消込ステータスとなっているか」の欄は、下記関数を入力しています。この関数を使用することで、「消込済」か「未消込」か判断できるように情報を整理しています。

=IF(ISERROR(VLOOKUP(B4,E:E,1,FALSE)),"未消込","消込済")

他の関数でも代用は可能ですが、vlookup関数を使い慣れてくると、iserror関数と組み合わせて使用したほうが、直感的にわかりやすかったりします。（好みの問題かもしれません。）

4. powershellを使ったファイル一覧作成コンボ

プロジェクトのフェーズ終わりには、納品物をExcelに一覧化して納めます。また、プロジェクトの最中でも、ドキュメント管理のためにドキュメントを一覧化することがあります。

手作業でファイル名やディレクトリを取得してドキュメント一覧を作ることはできますが、時間がかかってしまう上、正確性がそこなわれるので、私はpowershellを使っています。（コマンドプロンプトでも、ファイル一覧作成マクロを使ってもできます。）

「Win」ボタンを押しながら、「R」ボタンを押し、「powershell」と入力してEnterを押すと、powershellのコマンド入力画面が表示されます。

下記のコマンドを入力しEnterを押すとファイルの一覧をテキストファイルで出力できます。

get-childitem -recurse [一覧取得元のフォルダパス] | out-file [一覧出力先のフォルダパス\ファイル名.txt]

出力したテキストファイルはExcelに貼り付けた上で不要な行を削除するなど加工をすれば、ドキュメントの一覧を作ることができます。

手作業で加工するのは面倒なので、私は関数を駆使して一覧を作れるようにしています。powershellの仕様により、フォルダパスが2行に分かれてしまうケースがあるため、下記処理を行って1行に直したり、工夫しています。

a. サクラエディタで変換する。
変換前： \r\n
変換後： !!!!!

b. Notepadで変換する。
変換前： "!!!!! " ※""は指定不要
変換後： ブランク(何も指定しない)

c. サクラエディタで変換する。
変換前： !!!!!
変換後： \r\n

5. ガントチャート作成

WBSやタスクの線表を作る際に、ガントチャートを作成します。例えば下記の様なガントチャートを作成します。

ガントチャートを作成することで、タスクごとのスケジュールが視覚化できます。

一例ですが、下記の様な関数を条件付き書式に入力して、セル色を使い分けてガントチャートを作成しています。

●未着手のタスクについて、予定されている開始日・終了日までセル色を塗る

=AND($[作業開始予定日]<=[ガントチャート上の日付],$[作業終了予定日]>=[ガントチャート上の日付])

●実施中のタスクについて、作業の実績開始日から、作業の予定終了日までセル色を塗る
●実施済のタスクについて、作業の実績開始日から、作業の実績終了日までセル色を塗る

=IF(AND($[作業開始実績日]<>"",$[作業終了実績日]=""),AND($[作業開始実績日]<=[ガントチャート上の日付],[現在日付]>=[ガントチャート上の日付]),AND($[作業開始実績日]<=[ガントチャート上の日付],[作業終了実績日]>=[ガントチャート上の日付]))

ガントチャートの曜日について、休日の場合に赤字になるように設定しています。

=WEEKDAY([ガントチャートの日付],1)=1
=WEEKDAY([ガントチャートの日付],1)=7

土日以外の祝日は別シートに祝日の一覧を作っておき、ガントチャートの日付が、一覧の日付である場合に赤字になるように条件付き書式を設定しています。

6. コピー＆書式選択貼り付けのショートカットコンボ

セルの内容を「Ctrl」ボタンを押しながら、「C」ボタンを押してコピーした後、「Ctrl」ボタンを押しながら、「V」ボタンを押すことで、セルの内容を貼り付けできます。

しかし、セルの書式が崩れてしまい、再度、書式を整えるのは面倒です。例えば、B5のセルをコピーして、B12へ貼り付けると、罫線も貼り付けされてしまいます。罫線を後から削除するのは、一手間かかり、面倒です。

そこで、下記の様に書式選択貼り付けを行います。

①書式選択貼り付け「Ctrl」＋「Alt」＋「V」
②「Alt」ボタンを押しながら、「V」ボタンを押し、Enter

「Ctrl」＋「Alt」＋「V」を押すと、下記の様に、「形式を選択して貼り付け」のボックスが表示されます。

「Alt」ボタンを押しながら、「V」ボタンを押すと、「値(V)」のチェックボックスがONになります。※「Alt」ボタンを押しながら、「T」を押せば、書式のみ貼り付けが出来ますし、「C」を押せば、セルに入力したコメント欄を貼り付けできます。

Enterを押せば、値のみを貼り付けることができます。（下記例だと、B12へ値のみを貼り付けしました。）

地味ですが、値のみ貼り付けをショートカットで行えば、Excel操作が早くなります。

7. countifs関数

調査データを加工する際に、特定の条件を満たすもののみ、数字をカウントしたいケースがあります。

例えば、下記はフィリピンのDoHが公表している、新型コロナウィルスの感染者データです。

フィリピンは人別にデータを集計しており、そのデータが公表されているのですが、新型コロナウィルスの感染者数を集計するためには、このデータを加工する必要があります。

そこで、countifs関数を使います。countifs関数を使ってデータ集計することで、下記の様に、地域別・日付別に感染者数や回復者数、死者数を集計できます。

下記は関数の入力例です。

=COUNTIFS(生データの[地域]項目,集計シートの[地域]項目,生データの[日付]項目,集計シートの[日付]項目)

8. 見栄えの良いグラフ作成

通常、Excelでグラフを作成すると、下記のようなグラフがデフォルトで作成されます。見づらく、見栄えが良くありません。

そこで、下記の様な加工をします。

①目盛線を消す
②目盛りを調整する
③凡例を消す

まず、目盛線を消します。目盛線をクリックします。

「目盛線の書式設定」を「線なし」にします。

目盛線が消えて、すっきりします。

次に、目盛を調整します。2日おきに横軸の日付がとられており、見づらいです。

横軸の日付をクリックします。

「軸の書式設定」で、「単位」の「主(J)」を「10」に変更します。

横軸の日付が10日おきに表示されスッキリしました。

縦軸の目盛は「-1000」が表示されているので、表示する値範囲を変更しておきます。縦軸をクリックして、「軸の書式設定」から、「境界値」の「最小値」を「0」にします。

最後に、グラフ下部に表示されている凡例が見づらいため、凡例を消します。凡例をクリックして「Delete」ボタンを押して、消します。

あとは好みに合わせて、グラフを微修正し下記のようなグラフにすると、スッキリとして見栄えの良いグラフになります。

9. VBAを利用した条件付き書式の再設定

WBSを作り、条件付き書式を設定しておくと、行を後から追加した際に、条件付き書式が崩れてしまうことがあります。その場合、再度条件付き書式を設定しなおせばよいのですが、毎回、手で設定するのは面倒です。

そこで、VBAを利用して、条件付き書式を自動で再設定するようコーディングしておき、条件付き書式を再設定したくなったら、下記ボタンを押すことで自動化するようにしています。

下記はサンプルソースです。

●セルの入力内容が上のセルの入力内容と同内容である場合に、セルのフォントを白、セルの上罫線を非表示とする条件付き書式の再設定

'B列の条件付き書式設定

Sheets("WBS").Select MaxValueAfterRow = Range("B10").End(xlDown).Row
Cells.FormatConditions.Delete '条件付き書式のクリア
Application.CutCopyMode = False
Range(Cells(10, 2), Cells(MaxValueAfterRow, 2)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B10=$B9"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlNone
Selection.FormatConditions(1).StopIfTrue = False
Application.CutCopyMode = False

「B10」や「B9」と記載している箇所を適宜修正すれば、上記をコピペして使いまわすことができます。

他にも、ガントチャートの再設定や、「未着手」「対応中」などのステータスに応じたセル色設定なども、VBAで再設定できるようにしています。

10. [名前の定義]を使ったリスト作成

Excelでは、下記の「状況」のようにプルダウンメニューを作ることができます。

プルダウンメニューは、「データの入力規則」から作成することができますが、プルダウンメニューの内容を確認するには、都度、「データの入力規則」を見ないといけません。一覧性が無いため、管理がしづらいです。また、プルダウンメニューの内容が増えるごとに、「データの入力規則」をメンテナンスする必要があります。

そこで、「名前の定義」を使ってプルダウンメニューを作成します。

下記は、「List」の列に記載してある内容を「名前の定義」を使って、プルダウンメニューを作っています。プルダウンメニューの内容は一覧で分かりますし、「List」の内容を「完了」の下に付け足すだけで、プルダウンメニューのラインナップを増やすことができ、メンテナンスが楽です。

設定方法は下記です。

①「名前の定義」より、下記例のような関数を入力する。※セルの範囲は適宜変えてください。
=OFFSET(プルダウンメニュー!$J$3,0,0,COUNTA(プルダウンメニュー!$J:$J)-1,1)
②「データの入力規則」へ設定した「名前の定義」を入力する。

「名前の定義」の設定例です。「参照範囲」に上記、関数例を入力しています。「名前」は「状況」としています。

「データの入力規則」の設定例です。「元の値」に「名前の定義」で設定した名前を入力し、「=状況」としています。

最後に

いかがでしたでしょうか。今回は実務で使えるExcelワザということで、よく使うテクニックをシェアさせていただきました。

人によっては全く使う必要のないテクニックもあったかもしれませんが、少しでも、作業効率のヒントになれましたら幸いです。

よかったらTwitterもフォローしてくれる嬉しいです！

Jun | 経営コンサル (@jn_econ) | Twitter