Excelは表計算ソフトなので、本来、全く計算をしないで使うというのは、僕は間違っていると思います。でも、実際には計算をせずに使う使い方で十分な業務効率化を果たしている事例もあります。
Excelは計算書作成、データ分析といった機能がある中で、このような計算式を使わない使い方を僕は「一覧表管理」と呼んでいます。
一覧表管理を便利に操作するうえで欠かせないExcelの機能はたくさんあります。
その中でもよく使うのが、一覧表を並べ替える「並べ替え」、一覧表から必要なデータだけを抜き出す「フィルター」です。
さらに計算式を使わないとはいえ、一覧表を便利にする文字列操作の関数もあります。さらに、他の一覧表と連携して新たな情報を作り足すことができるVLOOKUP関数、集計関数であるCOUNTIF関数も覚えておく必要があります。
このような機能を理解することで、顧客一覧表や社員一覧表をより活用できるようになります。
その事例を紹介します。
一覧表の種類
一口に一覧表と言ってもいろいろな形があります。いくつか例を見てみましょう。
顧客リスト
次の一覧表は、顧客の連絡先リストです。注文された品物をこのリストの住所に送ったり、連絡を取ったりしたいときに見る一覧表です。
進捗一覧表
次の一覧表は、案件の進捗を管理するチェック表です。その案件で進捗があるごとにその日付を記入していきます。
この形だと、まだ行われていない作業では未記入になるので、表内に空白が存在します。
集計表
次の一覧表は、四半期ごとの目標と実績の集計された一覧表です。
本来、このような値を集計したものは一覧表とは呼びませんので、今回、このような表は一覧表として扱いません。このような表は一覧表とは別に「集計表」と呼びます。
一覧表のルール
集計したものを一覧表として扱わない理由があります。それらは一覧表のルールとして守らないと、非常に扱いにくい一覧表になります。手書きの一覧表なら目で見てわかるのですが、Excelの一覧表は完全にルールに従っていないといけません。これはExcelで扱うことで、効率化されるのですが、そのためにも必要なことなのです。
1行目には項目名が入る
一覧表は、それぞれの列が何のデータを表しているかわかるようにしなければなりません。1行目にそれぞれの列の項目名が入っていなければいけません。集計の一覧表ではセルB3とセルC3に本当であればそれぞれ「仕事の種類」「項目」といった文字が入っていなければなりません。
1行に1件が入る
一覧表は、1件が1行に入っていなければいけません。集計されたデータは、すでに1件1件が仕事の種類「Webサイト」などでグループ化されて集計されてしまっているものなので、1件1件のデータとは呼びません。また、セル結合されていると複数行に1件が結合されて入ってしまい1行1件のデータにはならなくなってしまうのです。
一覧表内に空欄があってはならない
一覧表は、空欄があってはなりません。しかしこれは表の種類にもよります。
顧客の一覧で顧客名が空欄になっているとそれはどの顧客かわからないデータになるので、このような必須のデータは入力されていなければなりません。電話番号はなくても住所さえわかれば商品が送れるから不要だという理由で空欄でもよいというルールにする場合もあります。
進捗管理であれば未作業のところは空白となっているので、空白が未作業だとわかるという目的があります。
このように必須の項目以外で空欄だったり、目的があって空欄があってもよいとしたりすることがあります。
一覧表の上下左右にほかの一覧表が隣接してはいけない
一覧表から列や行の隙間がなく、他の一覧表が隣接すると、どこからどこまでが1つの一覧表かわからなくなります。
見た目だけではなく、Excelとしても処理ができなくなります。
このことから一覧表は1シートに１つあるのが理想です。
一覧表の一部だけを並べ替えてはいけない
Excelで単価の高い順に並べ替えようとして、単価の値を範囲選択し並べ替えるととてもまずいことが起きます。
次のような範囲選択で並べ替えてみます。
下のような結果になります。確かに単価の高い順で並びます。
しかしよく考えてみると、Webサイトが単価200,000円だったものが、800,000円に変わっています。同じようにほかの仕事の単価も変わってしまっています。
単価の高い順に並べ替えるというのは、単価だけを並べ替えるのではなく、仕事ごとの単価と納期の保ったまま、単価の高い順に並べて、一番上に営業システムが来るように並べ替えるという意味だったはずなのです。
この場合は、範囲選択する必要はありません。表の1つのセルだけを選択して並べ替えればいいのです。
テーブル
Excelには一覧表を管理するためのとても便利な「テーブル」機能があります。
Excelのセルは、はじめはただの１つ１つの箱という意味合いしか持ちませんが、一覧表のセル範囲をテーブルに設定することで、ここからここまでが一覧表と定義できるので、様々な恩恵を得ることができます。
絶対に一覧表はテーブルにして使った方が良いです。
テーブルに設定するには、一覧表のどこかをクリックし、Ctrlキーを押したままTのキーを押すと、テーブルの作成ダイアログボックスが表示されますので、「先頭行をテーブルの見出しとして使用する」のチェックを入れてOKボタンをクリックします。
一覧表に色がついて、項目名に下向き三角が表示されます。これで一覧表のセル範囲をテーブルにした状態です。
テーブルのメリット・操作編
テーブルに設定した場合の操作や機能でできるようになるメリットを紹介します。
一部だけの並べ替えができなくなる
まず、テーブルになると、一部だけを並べ替えできないようになります。
表の一部だけを範囲選択して並べ替えても、表全体を対象として並べ替えます。
これだけでも誤操作しないための仕組みとしては優秀です。
並べ替えとフィルター操作が簡単になる
下向き三角をクリックすると、次のような画面が表示されます。並べ替えや一部のデータだけを表示するフィルターの操作ができます。
並べ替えとフィルターの2つの操作は一覧表操作の代表的な操作でしたよね。
それがテーブルに設定することで簡単に出来るようになるのです。
右クリックで操作が簡単にできる
また、テーブルを右クリックすることで、データを挿入したり、削除したりできます。挿入と削除は、1つのセルだけ削除はできなくなります。あくまで行ごとか、列ごとかでしか削除できません。
このことにより、1行1件のデータの関連性は保たれます。半面、列を削除してしまうとその項目が全部なくなるので、列の削除は気を付ける必要があります。
他にもテーブルとしてできることが、右クリックで簡単にできるようになっています。
テーブルを操作する専用のテーブルデザインタブが表示される
テーブルを選択していると、リボンに「テーブルデザイン」タブが表示され、テーブルの操作のすべてがこのリボンで操作できます。
特に、テーブルには名前を設定できるので「単価表」といった名前を付けておくとあとで管理がしやすいです。
項目名を列見出しに表示する
テーブルを下にスクロールすると、列の見出しがアルファベットではなく、項目名になります。
これは縦に大きな表において、管理がものすごくやりやすくなります。
この状態で、列見出しをクリックすると、その項目のデータ範囲だけが選択されます。
自動でテーブルの範囲が広がる
一覧表の下や右にデータを入力すると、そこまでテーブルが自動で広がります。
仕事の項目の最後のWebサイトの下のセルに「受付システム」を入力したので、自動的にテーブルが1行増えました。
追加された行には、上の行に設定してある書式や各種設定も引き継がれます。
テーブルのメリット・計算式編
テーブルにすることで、計算式も作りやすくなります。
計算式がわかりやすくなる
通常、Excelの計算式では、セル参照を使って「=C2+D2」のような計算式を使います。また、計算式はコピーすることによって、位置関係でずれて計算するようになっています。位置をコピーしても絶対にそのセルを見るというときは「$」を付けて絶対参照にします。
次の表でセルE2に売り上げの割合を求めるには、セルB2の値をB2からB5のセル範囲の合計で割るので「=B2/SUM($B$2:$B$5)」となります。B2からB5のセル範囲は下にコピーしても変わらないので「$」がついて絶対参照になります。
テーブルでは、この方法も使えますが、次のような、項目名を使った計算式でもできます。
「[ ]」で囲めば、「その項目のデータのセル全体（項目名のセルは除く）」という範囲になります。また項目名の前に「@」が入ると「計算式が入る行の」という意味になります。
割合を日本語で表すと、計算式と同じ行の売り上げ割る売上全体の合計という式になるので、まさに「=[@売上]/SUM([売上])」という、日本語に近い形の計算式として使うことができます。
この項目名で表す参照のことを「構造化参照」と呼びます。
また、テーブルの外やほかのテーブルに計算式を作成すると、「テーブル名[項目名]」という形になります。
以下のテーブルには「売上表」という名前がついています。
テーブルのデータ全体を示すには「テーブル名」だけにします。
計算式のコピーが不要
通常、Excelに列に対して計算式を設定するには、１つのセルに計算式を入力して、その後にその計算式を下方向にフィルやコピーする必要があります。もしコピーし忘れてしまうと、その下の部分には計算式が入りません。しかし、テーブルでは、その列の中の1つのセルに計算式を入力するだけで、自動的にすべてのセルに計算が入ります。
次のように、1つのセルだけ計算式を入力します。
確定した瞬間に、その項目に計算式がすべて入ります。
すでに計算式がその列に入っていて、その計算式を修正する場合でも同様で、１つの計算式を修正するだけですべての計算式の修正が完了します。修正し忘れになる心配がありません。
データを追加すると計算式がコピーされる
テーブルの次の行にデータを入力すると、その範囲までテーブルが増えますが、データを追加したら、その追加した行にも計算式が反映します。
これだけでもテーブルを設定する意味があります。
一覧表で使う関数
一覧表は、計算しない管理をしますが、しかし、関数はいわゆる四則計算や合計のような数値の計算だけではなく、値を探し出すという使い方をする関数もあり、そのような関数は、一覧表同士のデータを連結することができるので、絶対に覚えておきたい関数です。
VLOOKUP関数
値を探し出す、検索関数の代表がVLOOKUP関数です。
左側の青い表は販売の一覧表です。右側のオレンジの表が商品の一覧表です。2つの表は仕事の項目で結び付けることができます。
販売一覧表には売上と作業日数は記録されていません。商品一覧表を見ればわかるからです。このようなとき、情報を販売一覧表に情報を集約するために結び付けるのがVLOOKUP関数です。
VLOOKUP関数は、そのものを探す場合は「=VLOOKUP(検索値,範囲,列番号,FALSE)」という計算式になります。今回は、「営業システム」は「営業システム」そのもので一致させる完全一致になります。完全一致のほかに近似値一致という一番近い値で探す方法があります。
一覧表管理の場合のほとんどは完全一致のパターンになると思います。
セルB2に売上を求めるには、同じ行の仕事項目を、商品一覧表から探し出し、一致した行の2列目の値を連結します。
つまり、次のようにセルB2には「=VLOOKUP([@仕事],商品,2,FALSE)」という計算式が入力されます。
同じ行の仕事列のデータを、商品テーブルの中で探し、その一致した2列目のデータという意味です。
作業日数は同じ計算式で列番号が3列目になります。作業日数が求まると、開始日を足すことによって、「=[@開始日]+[@作業日数]」の式により、完了日を求めることもできます。
COUNTIF関数
一覧表は数字の計算はしないとしても、件数のカウントくらいはするかもしれません。その場合は「=COUNTA([仕事])」でカウントできます。また、仕事ごとの販売数を求めるにもCOUNTIF関数が使えます。この場合は、商品ごとの一覧表になっている商品一覧表に求めることになるでしょう。
COUNTIF関数は「=COUNTIF(範囲,検索値)」で入力していきます。セルJ2に仕事ごとの販売件数を求めるには、販売一覧表の仕事欄で、それぞれの仕事をカウントすればいいのですから、「=COUNTIF(売上表[仕事],[@仕事])」という計算式になります。
この計算式を入力すると、商品一覧表のテーブル範囲が横に広がり、新しい項目として「列１」ができて、この項目の計算式がすべて入ります。項目名を「販売数」などに変更しましょう。
この状態で、販売一覧表の一番下に「営業システム」と入力したとたん、商品一覧表の販売数が1つ増えます。
このように自動計算され、あとは販売されるたびに販売一覧表に入力していけば商品一覧表にどんどん集計されていくのです。
もし新しい商品の注文があたっときには、販売一覧表に記入前に商品一覧表に新しい商品を追加してから販売一覧表にその商品の販売記録を追加するように使っていけば、とても使いやすい販売管理表になります。
まとめ
今回はExcelの表計算としては本来の使い方ではないけど、実務では有効に使える計算式を使わない、一覧表の使い方について、どんなルールがあるのか、どんなメリットがあるのかを紹介しました。
一覧表はテーブルに設定することを強くお勧めします。同じ項目名が複数あるとどっちが本当の項目なのかわからなくなるし、セルが結合されていれば、どのセルの値を指しているのかわからなくなります。テーブルに設定することで、そういった表を作成できなくなります。もしはじめにルール違反の表であれば、項目名が変わってしまいますし、セル結合されているセルがあればテーブルに設定することすらできません。そのように一覧表として正しいのかというチェックにもなります。
テーブルに設定していると、小計の機能が使えなくなります。また、テーブルとして指定した範囲の中にスピルの計算式を入れることもできなくなります。このようにできなくなることもありますが、設定するメリットの方がはるかにおおきいです。
テーブルにデータを追加すると追加したデータもテーブル範囲になってくれますが、その設定はテーブルとしての色の付き方、計算式をコピーしてくれるというだけではなく、表示形式のような設定も上のセルの設定と同じになります。ここですごいのは、条件付き書式や入力規則も上のセルと同じになります。
以上のことから、一覧表はテーブルにすること、テーブルには計算式を設定しておくこと、ユーザー定義の表示形式や、条件付き書式、入力規則も設定しておくこと、といったことをすることで、より便利な仕組みがいとも簡単に作れます。
テーブルの中にスピルの計算式は作れませんが、テーブルを元にしたスピルの計算式は普通のセルに作成できます。その場合でもテーブル名や項目名を使って計算式を作成できますので、より簡単に数式を作成できます。
ぜひ、Excelの一覧表としての使い方、並びにテーブル機能を積極的に使っていき、手間のかからない自動化された効率の良い仕事をしていきましょう。
