時短テクニックを仕事に活用できれば、Excelで手作業でやっていた複雑な作業や、わざわざ別セルに切り出して行っていたデータ集計作業も大幅に簡略化することができます。ここでは基本的な関数や数式でなく、Excelで「中級スキル」と表現されることが多いVLOOKUPなどの関数からピボットテーブルを使いこなして仕事をスピードアップさせてぜひ「働き方改革」を実現させてみましょう。
VLOOKUP関数:条件に合う値に対応するデータを取得する
実践的かつ便利な関数でぜひ覚えたいのがVLOOKUP関数です。これは、ある列の範囲の中から指定した値を検索し、見つかったら、それと同一行にある別の列の値を取得する関数です。まずは次の例の画面を見てください。
G3のセルに
=VLOOKUP(G2,A2:C13,3,FALSE)
と入力し、G4のセルには
=VLOOKUP(G2,A2:D13,4,FALSE)
と入力しています。
ここで、G2のセルに商品番号1~12のどれかを入力すると、その値をA列から検索し、その商品番号が対応する別の列の情報(ここではC列:商品の価格)をG3のセルに表示します。同様にG4のセルには、この商品番号に対応する商品の在庫数(D列)が表示されます。
VLOOKUP関数の書式は以下のような形を取ります。
=VLOOKUP(検索値,検索範囲,取得する列番号,検索方法)
上記の例では、「検索値」にはG2のセルを指定していますので、G2のセルに入力した値が検索値になります。「検索範囲」は検索する値がある列 (A列) から取得したい値がある列 の最初から最後までの行になります。ここで注意することは検索する値がある列を最左端に置くことです。「取得する列番号」には、取得対象の列を検索値の列を含めて何番目の列なのかを指定します。「検索方法」には「FALSE」または「TRUE」を指定します。「FALSE」を指定した場合は、検索値と完全一致するセルを見つけます。「TRUE」を指定した場合は完全一致するセルがあればそれを、完全一致するセルがなければそれより1つ前のセルを返します。
先の例は、実はこのままではあまり見映えがよくありません。というのは、G2に値が入力されていない場合や、対応する商品番号外の値を入力した場合に、単価と在庫数のセルにエラー(ここでは「#N/A」)が表示されてしまいます。この対処方法を次項で紹介します。
IFNA関数・IFERROR関数でエラー表示を変更する
関数がエラーとなり、「#N/A」などと表示されるその代わりに「見つかりません」と表示させてみたいと思います。このときに役立つのがIFNA関数です。G3に
=IFNA(VLOOKUP(G2,A2:C13,3,FALSE),”見つかりません”)
G4に
=IFNA(VLOOKUP(G2,A2:D13,4,FALSE),”見つかりません”)
と入力すれば、G2に値が入力されていない場合や、対応する商品番号外の値を入力した場合に「見つかりません」と表示されます。
もちろんG2に有効な値が入力されれば、その単価と在庫数を表示します。
IFNA関数の書式は以下のように記述します。
=IFNA(論理式,#N/Aエラーの場合の値)
IFNA関数の代わりにIFERROR関数を使うこともできます。書式も同じで以下の形を取ります。
=IFERROR(論理式,エラーの場合の値)
IFNA関数もIFERROR関数も、橙色の部分にセル参照もしくは論理式を記述し、もしその結果がエラーであった場合、緑色に記載した値(もちろん論理式やセル参照を入れることも可能)を表示するというものです。
IFNA関数とIFERROR関数の違いは、IFNA関数が#N/Aエラー(計算対象のデータがないときに発生するエラー)にのみ対応しているのに対し、IFERROR関数はすべてのエラーに対応していることです。
IF関数で、未入力の場合に何も表示しないようにする
さらに上記の例をもう少し工夫してG2に何も入力されていないときはG3とG4に何も表示しないようにすることにします。もちろん正しい番号が入力されれば対応する単価と在庫数が表示されますし、範囲外の番号が入力されれば「見つかりません」と表示されるのは同じです。
G3セルには
=IF(G2=””,””,IFNA(VLOOKUP(G2,A2:C13,3,FALSE),”見つかりません”))
と入力し、G4セルには
=IF(G2=””,””,IFNA(VLOOKUP(G2,A2:D13,4,FALSE),”見つかりません”))
と入力します。
ここで使っているのはIF関数です。基本的な関数ですが、念のため書式を以下に示します。
=IF(論理式,真の場合,偽の場合)
橙色の式が正しければ緑色の結果が表示され、正しくなければ水色の結果を表示するというものです。この例では、G2セルが空白の場合はG3、G4も空白にし、G2が空白でなければIFNA以下を実行するというものです。
=IF(G2=””,””,IFNA(VLOOKUP(G2,A2:C13,3,FALSE),”見つかりません”))
=IF(G2=””,””,IFNA(VLOOKUP(G2,A2:D13,4,FALSE),”見つかりません”))
VLOOKUP関数の代わりにINDEX関数を使う
VLOOKUP関数を使う代わりにINDEX関数を使うという方法もあります。INDEX関数自体には検索機能はありませんが、MATCH関数などの検索用関数を併用することでVLOOKUP関数と同様のことができます。VLOOKUPだけでできるのに、なぜINDEXを使う必要があるのかはまた後述しますので、ひとまずINDEXの関数を見てみましょう。
INDEX関数は、
INDEX(配列(範囲),行番号,列番号)
上記のように書き、指定した範囲内から◯行、◯列目にある情報を取り出す関数です。列番号は省略すると「1」とみなされます。
つまり、例えば先程の表で
=(INDEX(A2:D13,4,3)
と指定すると、D5セル(範囲内で4行目、3列目のセル)の値「100」を表示します。
先程はVLOOKUPを用いて商品番号から単価や在庫を調べましたが、これは実はINDEXを使っても同じことができます。それが以下の式です。
G3セル:
=IF(G2=””,””,IFERROR(INDEX(A2:C13,G2,3),”見つかりません”))
G4セル:
=IF(G2=””,””,IFERROR(INDEX(A2:D13,G2,4),”見つかりません”))
行番号のところにG2セルを指定しています。G2セルでは商品番号が入力されており、これがたまたま行番号と同じ値なので、VLOOKUP関数と同じ結果になるというわけです。
INDEX関数とMATCH関連を使う
先のINDEX関数の例は商品番号がきれいに1から順番に並んでいるからできることで、商品番号がとびとびになっていたり、順番になっていない場合はこの方法は使えません。その場合はINDEX関数にMATCH関数などを併用する必要があります。
MATCH関数の書式は以下のような書式を持ちます。
=MATCH(検索値,範囲,照合の種類)
これは検索値の値が指定した範囲の中にあるかどうかを検索し、水色の方法で照合し、それに該当した場合、範囲内の何行目にあるかを表示します。照合の種類には「-1」、「0」「1」の3種類を取りますが、まず一番わかりやすい「0」(完全一致)を例にとってまず説明します。例えば先程の表で任意のセルに以下を入力します。
=(MATCH(5000,C2:C13,0)
こうすると、そのセルに「1」の数字が表示されます。検索値5000がC2:C13の範囲内で1行目に見つかるからです。
照合の種類は3種類あり、1、0、-1のいずれかの数値を入力します
照合の種類 | 詳細 |
---|---|
1 | 検索値を超えない範囲で一番大きい数値を探します |
0 | 完全一致。検索値を同じ数値を探します |
-1 | 検索値を超えた値で一番小さい数値を探します |
ちなみに、INDEX関数とMATCH関数を使うことで、前項で紹介したVLOOKUP関数による品番号から対応する単価と在庫数を表示する例と同じことができます。それが以下です。
B3セル:
=IF(G2=””,””,IFERROR(INDEX(A2:C13,MATCH(G2,A2:A13,0),3),”見つかりません”))
G4セル:
=IF(G2=””,””,IFERROR(INDEX(A2:D13, MATCH(G2,A2:A13,0),4),”見つかりません”))
ここまで読んだ方はあることに気になったかもしれませんが、VLOOKUPを使ってできることを、なぜわざわざ複雑なINDEXやMATCHを使って紹介したのかということです。それはVLOOKUPの弱点に関係があります。
VLOOKUP関数で検索する場合、条件として検索値が検索範囲の最左端列になければなりませんが、INDEX関数とMATCH関数の組み合わせでは、検索値がどの列にあってもよいという特長があります。また、実際に求める値の範囲が1列だけであれば、VLOOKUP関数のような列番号の指定は必要なくなります。
例えば、商品番号に対応する単価を調べるために、G3セルに入力していた関数は
=IF(G2=””,””,IFERROR(INDEX(C2:C13,MATCH(G2,A2:A13,0)),”見つかりません”))
と記入しても当初のVLOOKUPを使って結果と同じになります。さらにVLOOKUPとは違い、商品番号の記載列を最左端のA列(上記ではA2:A13)にする必要がなく、任意の列にできる柔軟さがあります。
水平方向に検索していくHLOOKUP関数
ここまででVLOOKUP関数とその代替となる関数を紹介しましたが、VLOOKUP関数の関連関数として、HLOOKUP関数があります。VLOOKUP関数が複数列に並んでいる表内を検索するのに対し、HLOOKUP関数は複数行に並んでいる表内を検索する点が異なります。
HLOOKUP関数書式は以下のように記述します。
=HLOOKUP(検索値,検索範囲,取得する行番号,検索方法)
VLOOKUP関数との違いは、方向が縦(Vertical)か横(Horizontal)かの違いにすぎないので、詳しく説明する必要はないと思いますが、一例として下記を見てください。
B8のセル:
=HLOOKUP(B7,B1:M4,3,FALSE)
B9のセル:
=HLOOKUP(B7,B1:M4,4,FALSE)
ここで、B8のセルに商品番号1~12のどれかを入力すると、その値を1行目から検索し、その商品番号が対応する別の行の情報(ここでは3行目:商品の単価)をB8のセルに表示します。同様にB9のセルには、この商品番号に対応する商品の在庫数(4行目)が表示されます。
SUMIFで販売集計データから各種合計を算出
上記では、Excelのデータ取り出し作業を簡略化する代表的な関数としてVLOOKUPと関連する関数を紹介しました。さらにより複雑な集計表を操作する方法としてピボットテーブルに触れてみたいと思います。
次の画面のように、月ごとにシートに分かれた販売データがあるとします。このデータを使って色々な分析を行う場合を、まずは関数を使いながら考えてみます。
月ごとの販売額合計を計算するときはSUM関数を使えば簡単にできますが、例えば、担当者ごとの販売額の合計を知りたい場合はどうでしょうか。担当者ごとにソートしてからSUM関数を使うという方法もありますが、ソートしなくてもそのまま部分合計を計算する関数としてSUMIF関数が使えます。具体的にはJ3のセルに
=SUMIF(G3:G123,”山田”,F3:F123)
と入力します。同様にJ4とJ5のセルに
=SUMIF(G3:G123,”中村”,F3:F123)
=SUMIF(G3:G123,”斉藤”,F3:F123)
と入力します。これで担当者ごとの販売合計額が求められます。
SUMIF関数の書式は次のとおりです。
=SUMIF(範囲,検索条件,合計範囲)
「範囲」には検索する範囲を指定し、「検索条件」に数値、または文字列を指定します。文字列を指定する場合は“”で囲みます。「合計範囲」には合計を計算したい値のある範囲を指定します。なお、「検索条件」にはワイルドカード (「*」:任意の文字列/「?」:任意の1文字/「~」:ワイルドカードの反対) が利用できます。
次の例として、曜日ごとの販売額合計と平均額を計算する場合を考えてみます。
J9~J15の列には
=SUMIF(B3:B123,”日曜日”,F3:F123)
=SUMIF(B3:B123,”月曜日”,F3:F123)
=SUMIF(B3:B123,”火曜日”,F3:F123)
=SUMIF(B3:B123,”水曜日”,F3:F123)
=SUMIF(B3:B123,”木曜日”,F3:F123)
=SUMIF(B3:B123,”金曜日”,F3:F123)
=SUMIF(B3:B123,”土曜日”,F3:F123)
と入力します。
これで曜日ごとに販売合計額が計算できます。平均額は各曜日が月に4日ありますので、K9以下の列には =J9/4 のように合計額を4で割れば曜日の平均販売額になります。
さらに土日の平均販売額、平日の平均販売額計算してみます。
K17には
=AVERAGE(J9,J15)/4
と入力し、K18には
=AVERAGE(J10:J14)/4
と入力すれば、土日の平均販売額、平日の平均販売額が求められます。
同じ集計でピボットテーブルを使うと
ここまでで、SUMIF関数やAVERAGE関数などを使って部分集計を行う方法を説明しました。同様の方法で商品ごとの販売合計額なども計算できます。ただ、こうした集計を行うたびに集計用の表を作ったり関数を入力したりするのは煩雑で時間がかかります。また、商品ごとでかつ担当者ごとの販売合計など、複数の条件での集計は、SUMIFS関数を使うなどさらに手間がかかります。
実はもっと簡単に同じことができる機能がExcelにはあります。それがピボットテーブルです。説明の前にまずは実際に使ってみましょう。
以下では、Excel 2016の場合で説明します。他のバージョンの場合は画面やメニューが異なっている場合がありますが、基本的な使い方は同じです。
(1)販売集計表のどこかにカーソルを置いた状態で、[挿入] メニューから [おすすめピボットテーブル] をクリックします。
(2)集計表全体が選択された状態で「おすすめピボットテーブル」のウィンドウが開きます。「担当者 (集計元: 合計 / 合計額) がデフォルトで選択されているでしょうから、そのまま [OK] ボタンをクリックします。
(3)新しいシートが作られ、画面のように担当者別の販売合計結果が表示されます。このようにSUMIF関数を使わなくても、簡単なマウス操作だけで希望する結果が得られます。簡単でしょう。この結果からは、山田さんが一番販売成績が良いことがわかります。
ピボットテーブルの活用
ピボットテーブルの良いところはこれだけではありません。例えば担当者別の代わりに商品別の販売合計を見たい場合は、右側にある「ピボットテーブルのフィールド」で「担当者」のチェックを外して「商品」にチェックを付けると、左の集計結果が、直ちに商品別の販売合計になります。この結果からはセーターCが一番の売れ筋であることがわかります。
日付ごとの集計や曜日ごとの集計も同じようにできます。「日付」と「担当者」の両方にチェックを付けると日付ごとでさらに担当者ごとの集計結果が表示されます。このように複数条件での集計も簡単です。
「ピボットテーブルのフィールド」で「日付」と「担当者」にチェックを付け、下のボックスの「担当者」を「列」のフィールドにドラッグすると、縦軸に「日付」横軸に「担当者」の表が表示され、縦横のクロス集計が行われます。
この結果からは、山田さんは比較的毎日まんべんなく販売しているのに対し、斉藤さんは日によってムラがあることがわかります。曜日によってどんな商品が売れているかは、縦軸に「曜日」、横軸に「商品」を指定することで分析できます。
セーターCだけに分析対象を絞りたいときは、「フィルター」機能を使います。「列ラベル」の右にある下矢印をクリックして「セーターC」以外のチェックを外せば「セーターC」だけが表示されます。
個数や平均、最大値、最小値などの計算も簡単
集計結果は合計だけでなく、個数や平均、最大値、最小値なども計算できます。合計のセルを右クリックして「値の集計方法」で指定します。
また、合計のセルを右クリックして「計算の種類」では比率などを計算することができます。
なお、ピボットテーブルの書式は通常と同じに、見やすいように変更ができます。また「挿入」メニュー、あるいは「分析」メニューにある「ピボットグラフ」をクリックすれば、簡単にデータがグラフ化されます。
本格的なシステムを使うまでもないので、Excelで簡単な集計表を作っているというケースは多いでしょう。そんなときに関数などを使って分析を手作業で行っていては大変時間がかかってしまいます。
そんなときの効率化の技がピボットテーブルです。ピボットテーブルは視点を自由に変更して、色々な角度から誰でも簡単にデータの分析ができます。データ数が多い表を業務で扱う際にはぜひピボットテーブルを活用して時短テクニックを磨いてみてください。
[ネクスマグ] 編集部
パソコンでできるこんなことやあんなこと、便利な使い方など、様々なパソコン活用方法が「わかる!」「みつかる!」記事を書いています。