ここにPCソフトを法人向けに販売している会社が制作した、販売したソフトとその納入先のリスト(という設定)があります。
もしも「一番総売り上げの高いソフトはどれかわかるようにして」とか「総売上と別に売れた個数が最も高いソフトがどれかもわかるようにして」とか「もっとも売り上げの大きい納入先はどこか書いといて」などと指示されたらどうしますか？
こんな大量のデータから自分で計算したり分析したりして、手作業で書くなんて面倒すぎるよ！
きっとそう思うでしょう。
そこで今回、この記事で解説するのは、大量のデータの集計や分析を行うための表を自動で作成する「ピボットテーブル」という機能です。これは初心者向けとは言いがたい、使いこなすのは難しい機能ですが、理解すればとても便利で、Excelでデータの一部を抽出して集計といったことを簡単に行うことが出来ます。
ピボットテーブルを作成しよう
ピボットテーブルを作成するために、まず分析したい表のセルの一部を選択します。
次に、画面上の「挿入」タブをクリックしてから、現れたメニューの一番左にある「ピボットテーブル」をクリックします。このとき、クリックするのは上半分の絵が描かれた部分です。
下半分をクリックすると、このようなメニューが現れます。この場合は、上の「ピボットテーブル」をクリックしましょう。
ピボットテーブルの作成ウィンドウが、画面に現れます。ここではテーブルを作成する範囲と、テーブルを作成したときに新しいワークシートを作るか、現在作業しているワークシートに、テーブルを作成するかを設定します。ここでは、このまま「OK」をクリックして次に進めます。
表を作っておけば、セルの一部を選択しただけで自動的にその表全体を範囲と定めてくれます。セルとセルの間に空行があると、一つの表とは見なされなくなってしまうので注意しましょう。
「OK」をクリックして新しいワークシートが作られましたが、見ての通り表は作られておらず、「レポートを作成するには～」という謎のメッセージが書かれています。
落ち着いて画面の右側を注目します。「ピボットテーブルのフィールドリスト」というウィンドウがあります。「販売日付」「製品名」など、先ほどの表の見出し部分が並んでいます。
まず、試しに「製品名」の左にある小さい四角、チェックボックスをクリックしてみましょう。
すると、このように「行ラベル」という見出しの下に製品名が一瞬で並べられました。
次に、「総売上」のチェックボックスをクリック。
このように、それぞれの製品の総売上が自動的に並べられます。しかも、先ほどの表を基に、それぞれの製品の総売上の合計が、自動的に計算されています。これで、どの製品が一番売れたかの集計が楽になります。
「個数」にチェックボックスを入れてみると、販売個数も合計が自動的に計算されて表示されます。
ピボットテーブルのレイアウトを変えてみよう
では、それぞれの納入先ごとにどの製品が売れたかをチェックしてみましょう。「納入先」のチェックボックスをクリックします。
すると、どの製品がどの納入先に売られたかを表す表が作られました。縦長でちょっと見にくいかもしれません。
この表のレイアウトを変えてみましょう。
画面右下に注目。「ボックス間でフィールドをドラッグしてください」というメッセージの下に、４つのボックスがあります。それぞれ「レポートフィルター」「列ラベル」「行ラベル」「値」となっております。
「行ラベル」のボックスの中にある、「納入先」と書かれている部分があります。この部分のことをフィールドと呼びます。このフィールドをドラッグして、「列ラベル」のボックスに置いてみましょう。
今度は横長の表になりました。「行ラベル」「列ラベル」はざっくり説明すれば、見出しのフィールドを縦に表記するか、横に表記するかを決めるものです。「行」が横で、「列」が縦になります。
「納入先」と「製品名」のフィールドを入れ替えてみるとこうなりました。
ピボットテーブルから必要な情報だけを抜き出してみよう
縦に書いても、横に書いても見にくいなあ。どの納入先にどの製品が売れたかが重要だから、納入先を一つだけ抜き出して、どの製品が売れたかをリストにできないかな？
ずばり、できます。やり方もシンプルです。
「納入先」のフィールドを、「レポートフィルター」のボックスにドラッグしましょう。
お使いのExcelのバージョンによっては、「レポートフィルター」ではなく「フィルター」と表示されていますが、表記が違うだけで機能は変わりません。
するとこのように、最初は納入先が書かれていない状態に戻ってしまったと思うかもしれません。しかし、あわてずに、表の上に注目しましょう。
「納入先」「(すべて)」と書かれた２つのセルが追加されています。「(すべて)」のセルの右にある、三角形をクリックしましょう。
するとこのように、納入先の名前のメニューが現れます。試しに「アイミュージック」を選択してから、「OK」をクリックしてみましょう。
このように、作成した表に従って、アイミュージックに関連したデータだけがリストに表示されるようになります。これは表が消されたわけではなく、アイミュージックのデータだけをフィルタリングして表示するようにしているのです。
他の納入先を選べばこのように、その納入先に関するデータを抜き出すように表示が変わります。
セルの右の三角形のマークが変わっていますが、これは一部のデータだけを抜き出して、フィルタリングしている状態であることを示すマークです。ちなみに、描かれているのはろ過器です。
個別に集計したいデータのフィールドは、「レポートフィルター」あるいは「フィルター」に置くということを覚えておきましょう。
フィールドをドラッグして、製品名と納入先を入れ替えれば
どの製品を、どの会社に納入したかの個別のデータの集計もできます。
表の順番が気になったら
今までの表を見て、「売り上げの合計は一番右にあるべきだろう」と思って、表の位置を入れ替えたいと思った場合でも、簡単にできます。
画面右下の「値」のボックスの中にある、２つのフィールド「合計/総売上」(以下総売上と表記)が上で「合計/個数」(以下、個数と表記)が下になっています。カーソルを合わせると、形が変わりますがこのとき、「個数」のフィールドを「総売上」のフィールドの上になるようにドラッグしましょう。
フィールドの位置が入れ替わると
表の位置も入れ替わります。
ボックスの上から下は、表の右から左となることを覚えておきましょう。
スライサーでさらに素早くデータを集計
個別にデータの集計をしているんだけど、メニューを開いてフィールドを選んで「OK」をクリックする手間がわずらわしいなあ。もっと、すばやくできないの？
無茶な要望に聞こえますが、そんな人のための機能があります。スライサーという機能です。
画面上にある「オプション」タブをクリックして、現れたメニューの「スライサー」をクリックします。
Excelのバージョンによっては、オプションではなく「分析」と表記されています。
スライサーの挿入というウィンドウが現れます。ここでは、製品名をクリックしてチェックボックスを入れてから、OKをクリックします。
すると画面に、画像の右のような、製品名のフィールドが並んだ白い四角が現れます。これをスライサーと呼びます。このスライサーの中にある、フィールドのどれかをクリックします。
するとこのように、クリックした製品に関するデータだけが表になります。
表の一部を切り取って表示するからスライサー(Slicer)というわけです。
Shiftキーを押しながらフィールドをクリックすれば、複数表示することもできます。
スライサーを有効活用して、データの集計をより素早く行っていきましょう。
ちなみに、スライサーの右上のマーク、カーソルを合わせると「フィルターのクリア」と出る部分をクリックすれば
全ての表を表示する、最初の状態に戻すことができます。
スライサーを消したいときは、スライサーをクリックしてDeleteキーかBackspaceキーを押せば消えます。
このとき、フィールドのどれかを選択して一部の表だけが表示されている状態だと、スライサーを消しても、表は一部が表示されているままです。
表が消えてしまった！ とあわてる必要はありません。「行ラベル」と書かれたセルの右のマークをクリックしましょう。
上のようなメニューが現れます。この下の部分の、製品名の横にチェックボックスが置かれている箇所に注目しましょう。表示されている製品が選択されている状態になっています。
データの一部だけを切り取って、表にすることはこのメニューからも行うことができます。スライサーはこの操作を、マウスをクリックするだけですばやく行うための機能なのです。
「すべて選択」を選んで、すべてのチェックボックスを選択すれば
すべての表が表示された状態に戻ります。
使い方さえわかれば、ピボットテーブルはデータの分析や集計に非常に便利です。積極的に使ってみましょう。
