【エクセル裏ワザ】仕事を効率化させるExcelの表の作り方

仕事でエクセルの表を作ること、よくありますよね！？
その表が、分かりずらい、見にくい表だったら、どうでしょう？

結論、仕事の効率が下がります。  

分かりずらい、見にくい表は、データがちゃんと整理されていない場合が多いです。
その結果、毎回手直ししなければならなかったり、表の計算ミスが発生するといったことも起こり、仕事の効率を下げてしまいます。

また、他人からは、「この表よくわからないよ」、「もっと見やすい表を作れないのか」と思われ、表を作った人の評価も下がってしまいます。

そんなことにならないようにするためにも、今回は、仕事を効率化させるエクセルでの表の作り方について解説していきます。

エクセルでの表の作り方【５つのポイント】

分かりやすい、見やすいエクセルの表を作るためには、次の５つのポイントを実践する必要があります。

このサンプル表では、

・表の合計＝薄い黄色
・表の項目＝薄い緑色
・データ＝白色

といったように、セルの色に意味を持たせています。

セルの色を選択するとき、

・濃い色
・派手な色

は使わないでください。

その理由は、
・濃い色を使うと、合計値や項目の文字が見えなくなります。
・派手な色をつかうと、目が疲れます。

目立たせようと、派手なピンクや赤色を使う人もいます。
その結果、文字と色が被ってしまいものすごく見ずらい表になってしまうので注意してください。

見やすい表を作るときは、色が重要になります。
使う色は、できるだけ薄い色を使って表を作ることをおすすめします。

表にはオートフィルタを設定する

オートフィルタ機能とは、

データの中から、条件に合うデータだけを抽出して表示する

機能です。

必要なデータだけを表示したい場合、このオートフィルタ機能を使います。
表を使うときは、このオートフィルタの機能が活躍します。

オートフィルタ機能の設定方法

まずは、オートフィルタ機能を使うための設定方法を覚えましょう。

オートフィルタ機能を設定するためには、その機能を使いたい表を選択します。
（表のどこを選択しても大丈夫です）

次に、エクセルのホーム画面にある「並べ替えとフィルター」を選択し、そのメニューから、「フィルター」メニューを押します。

そうすると、各項目に四角ボタンの「▼」が表示されます。

これで、オートフィルタの設定は完了です。

オートフィルタ機能の使い方

オートフィルタ設定後に表示された、各項目の四角ボタンの「▼」を押してみましょう。
このボタンを押すと以下のようなメニューが出てきます。
（仕入先Ｎｏの項目を押した場合の表示です）

このメニューから、表示したい項目だけに✔を入れてみます。
（ここでは、仕入Ｎｏ200001だけに✔を入れてみます）

そうすると、表示したい項目だけが反映された表が表示されます。
なお、最初の表に戻したい場合は、上記メニューにある「すべて選択」に✔すれば、すべてのデータが表示されます。

このように、オートフィルタを設定することで、表の項目を思い通りに表示できるので、効率よく表を使うことができます。

表の上に、合計の欄を作る

表にあるデータの合計欄は、普通であればデータを一番下に入れると思います。
しかし、表を効率よく使うには、それをすべきではありません。

その理由は、

・一番下に「合計」を入れると、いちいち下にスクロールして合計を確認しなければならない
・「合計」は、一番見やすいところに入れるとすぐチェックできて便利

だからです。

では実際にサンプルを見てみましょう。

このように、毎回チェックしたい合計は、表の上に作るのがおすすめです。
ちょっとしたことですが、合計が表の上にあると、すぐに合計を確認することができるのがよいところです。

また詳細は後述しますが、行を追加したとき、合計が正しく計算されないという問題を避けるために、合計を表の上に別枠で設定しています。

行を追加しても、正しく合計が計算できるように工夫する

エクセルの表では、行を追加してデータを増やしていくことが普通に行われます。

そのとき、「SUM」関数や「SUBTOTAL」関数を使って表の合計を集計していると、行を追加して増やしたデータが合計範囲に含まれていないという問題が起きてしまいます。

その結果、行を追加してデータを増やす度に、「SUM関数」や「SUBTOTAL関数」の合計する範囲を変更しなければなりません。

例えば、SUM関数で合計している範囲が、「L11からL19」までだったとします。（以下のサンプル参照）

そして、追加データを入力するため、行を追加しました（水色がデータ増えたところです）

その結果、データは増えましたが、SUM関数で合計している範囲が、「L11からL19」のままです。
「SUM」関数の合計範囲の修正が必要です。

これだと、データが増えるたびに計算式を修正しなければならないので、かなり面倒です・・・

ここで、ちょっと工夫をしてみましょう！

要はデータが増えても、計算式を修正しなくていいようにすればいいのです。

具体的には、次の2つを実行します。

①合計対象範囲を、列すべてとする
（サンプルでは、SUMの合計範囲を「L:L」としています）

②合計するセルの位置を変える
（サンプルでは、合計するところを黄色のセルに移動しています）

このように、列すべてを合計範囲にしてしまえば、データが増減しても合計範囲は変わらないので、計算式を修正する必要がありません。

ただし、列すべてを合計範囲にする場合、合計するセルを同じ列に設定することができません。
（L列すべてを合計範囲とする場合、L列のどこかに合計セルを設定することはできない）

このため、合計するセルの位置を変えてあげる必要があります。
（ここではＧ列の上の方に合計欄を設定しています）

今回は、「SUM」関数を例に説明しましたが、後述する「SUBTOTAL」関数も同じように対応することができます。

データを合計する時は、「SUBTOTAL」関数を使う

エクセルでデータを合計するときは、一般的に「SUM」関数を使うことが多いかと思います。
しかし、表のデータを集計する場合は、「SUBTOTAL」関数を使うことをおすすめします。

その理由は、

オートフィルタでデータを非表示した場合、「SUBTOTAL」関数は非表示データを除いて合計してくれる

からです。

そもそも「SUBTOTAL」関数とは？

「SUBTOTAL」関数とは、合計や個数などのさまざまな集計値を求めることができる関数です。
「SUM」関数と同様に、データの合計を求めることができる関数と覚えておいて問題ありません。

「SUM」関数との違いは？

「SUBTOTAL」も「SUM」関数も、合計を求めるという点では、使い方は同じです。
ただし、合計の処理方法が異なります。

「SUM関数」は、非表示セルも含めた合計が計算されます。
オートフィルタ等を使った場合、非表示のセルも集計されて、合計が表示されます。

一方、「SUBTOTAL」関数を使って計算した合計は、表示されているデータだけが合計されます。

「SUM」関数と「SUBTOTAL」関数の違いをまとめると、

ということになります。

上記で説明したオートフィルタ機能を使って表を作成したとき、確認したい合計は、表示されているデータの合計だと思います。

このため、オートフィルタ機能と組み合わせて表の合計を表示する場合、「SUBTOTAL」の関数が断然便利ということになります。

「SUBTOTAL」関数の使い方

「SUBTOTAL」関数の書式は、

=SUBTOTAL(集計方法,参照1,[参照2],…)

というように表示されますが、詳細を覚える必要はありません。

具体的にサンプル表を参考にしてみましょう。

サンプル表の数量合計のセルには、「SUBTOTAL(9,K:K)」という関数が入っています。

この「SUBTOTAL」関数は、

”SUBTOTAL(9,K:K)”　＝「９」は合計するという意味
”SUBTOTAL(9,K:K)”　＝「K:K」は合計する範囲

というように簡単に覚えましょう。

このサンプルでは、Kの列の数量をすべて合計するという意味になっています。
ただし、上記でも説明したとおり、「SUBTOTAL」関数を使っているので、非表示となっているデータは合計されず、表示されたデータのみの合計値となっています。

「SUBTOTAL」関数は、覚えてしまえば簡単に使うことができます。
オートフィルタ機能とも相性がよく、エクセルで表を作るときは非常に役に立つ関数ですので、使えるようになりましょう。

まとめ

今回は、仕事を効率化させるエクセルの表を作る場合の、５つのポイントを解説しました。

この５つのポイントに従って表を作成すれば、

・わかりやすい
・見やすい

エクセルの表が作成できます。
さらに、行を追加した時、合計が違っているといったミスもなくなります。

エクセルで作業する際は、ぜひこの５つのポイントに従って表を作成してください。
効率のよいエクセルの表を作成することができ、他の人からも「見やすい表だね」と評価されるはずです！

執筆者情報／経理部ＩＳ
20年以上にわたり、上場企業とその子会社で経理業務を経験。
転職６回・複数の上場企業での経験を活かし、経理を中心とした仕事に役立つ情報をブログで発信中。

ブログ名：経理課長の仕事術　https://www.keiri-manager.com/