エクセルにある「ピボットテーブル(Pivot Tables)」のチュートリアル

「差し込み印刷」はWordの上級機能です。Excelの上級機能といえば、やはり「ピボットテーブル」です。

Excelにあるドロップダウンメニュー機能（データ検証）及びVLOOKUP関数のチュートリアルは、本文の「ピボットテーブル」に関係があり、ピボットテーブルの基礎とも言えます。理由は、ピボットテーブルを使用して、データを分析する時に、誤入力が間違い結果を生み出すため、ドロップダウンメニュー機能を使いながらデータを入力すると、誤入力も避けられます。

次には例を挙げて、ピボットテーブルをよく解説し、及びどんな状況に使うほうがいいことを解明します。

複雑な文房具を注文する流れ

ほとんどの中小企業は文房具を注文する必要があります。文房具を注文することは中学生さえ余裕ですが、うまくやれるかどうか腕次第です。簡単に見えますが、意外と複雑です。次には注文の流れを解説します。

まず、各部門には需要な文房具を統計する人が要ります。その人は同僚の文房具のニーズを聞き取って、「文房具のリスト」というテーブルを作成します。そのテーブルはほとんど以下のようになります。

部門の種類は大体、製造部、財務部、開発部、事業部、品質管理部などがあります。

次にはテーブルから詳しくない項目をフィルタします。例えば、「ボールペン　1本」を書いていますが、ブランド、色、などの情報が知らないです。そのため、各部門から集めたデータを整理してから2つのテーブルを作成し、1つは注文の店に渡し、1つは会計士に渡します。

次の図のように、文房具屋の店主に渡すテーブルは品名、単価及び数量の情報が必要です。

文房具屋の立場から見ると、何を注文したいことが分かれば結構です。どの部門の誰か何を注文したことを知る必要がありません。

次のような、会社の会計士へのテーブルは、各部門が注文した文房具と金額の明細が必要です。

そのため、Excelでテーブルにデータを入力し、Excelの機能を使用してデータを集めます。それぞれのデータを集めることは面倒し、時間もかかります。変更の時にはもっと大変です。

テーブルを作成した後、まず注文請書を文房具屋に送ります。商品が着荷した次第に、各部門に配って、領収書などの証票を会計士に渡します。これまではすべての流れです。

流れの最適化

文房具を注文する流れの中で、各部門から注文リストを受けた後、店主及び会計士に渡すテーブルの作成することは一番面倒です。間違いがあれば、同僚に迷惑をかけ、商品の返品処理どちらも大変です。

また、会計士へのテーブルには注文数量と金額が一致しない場合は、ミスを見つけることが大変です。著者の先輩はよく気が付く女子こそ、この仕事をうまくやれますが、著者ならミスがよく出るため、困ります。

離職した先輩はそのやり方を教えてくれた後、自分でテーブルを作成する時に、流れを最適化しました。コンピューターの専門家として、時間の無駄遣いことはあり得ません。

もともと一日ほどかかるテーブルは今、20分ほど作成できます。そして、演算はコンピューターに任せるため、ミスに心配しなくても大丈夫です。これは全部、Excelにある「ピボットテーブル(Pivot Tables)」のおかげです。

基本的なデータテーブルを作成する

次には各部門から集めた情報をExcelに入力する方法を解説します。ピボットテーブルに必要なデータテーブルに変換するためです。

ピボットテーブルに対応するデータテーブルを作成することは大切な事前準備です。次の図のように、必要なデータフィールドは分析するデータの詳細程度から決めて、データをフラット化にします。

人ごとに注文した文房具は単行に分ける必要があります。列部門に行２、行４、行７の管理部が三つの行になるため、「管理部」を3回入力する必要があります。省略することはだめです。「種類」フィールドも同じです。

同じテキストをたくさん入力することが面倒で、誤入力の心配をしないでください。

この問題は「データ検証」と「VLOOKUP数式」を使用して解決できます。「部門、名前」と「種類、品名、単位、単価」は次のチュートリアルに従って、ドロップダウンメニューで対応のデータを入力します。

Excel VLOOKUP(ブイルックアップ）関数ガイド：指定された範囲を列で検索し、自動的にデータを入力します

Vlookupに代わって、Xlookup関数の使用方法（初級+中級+上級）

Excelのプルダウンリストを作成し、「データの入力規則」を実現します

Excelの「データの入力規則」と「名前」機能で、複数のドロップダウンリストの作成方法

次には、「文房具の注文請書」をデータテーブルに入力するステップになります。絶対に誤入力しないでください。1つのところに間違いデータを入力すると、演算結果も間違うため、細心の注意を払って入力してください。

ピボットテーブルを作成する

必要なデータテーブルを入力した後、「ピボットテーブル」を生成します。やり方はすごく簡単です。次には文房具屋に渡す注文請書例を挙げます。

店主向けの「文房具の注文請書」

Step 1.まず、[すべてのデータの行]を選択します。データを入力していない行も選択できます。また、[挿入]タブから[ピボットテーブル]をクリックします。次の図によって示します。

Step 2.「ピボットテーブル」のダイアログボックスが出た後、分析するデータが選択でき、ピボットテーブルを配置する場所も指定できます。普通は[新規ワークシート]でいいです。[OK]をクリックします。

Step 3.次にはピボットテーブルに表示する項目を設定します。次の図は設定していない状態であるピボットテーブルです。設定方法は右側にある[ピボットテーブルのフィールドリスト]を下の[行ラベル]、[列ラベル]、[値]に移動します。文房具屋や会計士に渡すテーブルはどちらもこのステップから設定します。

Step 4. 次には、[種類]と[品名]を下の[列ラベル]と[値]に移動すると、左側のワークシートがすぐに変更されます。 左側のワークシートが予想通りまで、右側のウィンドウで設定を行います。

上記の図表から見ると、このテーブルにあるデータは問題があるため、1つずつ修正する必要があります。

[文房具種類]の小計を削除する

Step 5.右下の[列ラベル]の[種類]タブ右側の三角をクリックし、[フィールドの設定]を選択します。次の図によって示します。

Step 6. 次には、[小計とフィルター]タブの[小計]を[なし]に設定し、[OK]をクリックします。

その後、これらの文房具種類の小計が消えます。明細の数量ははっきり見えるようになりました。

アイテムのラベルを表形式で表示する

このような「アウトライン」の表形式にしたくない場合には、[レイアウトと印刷]タブで、表形式が[アウトライン]を[アイテムのラベル]に変更します。

Step 7.まず、上記の手順と同様に[フィールドの設定]のウィンドウを開き、[レイアウトと印刷]に切り替えて、[アイテムのラベルを表形式で表示する]をオンにし、[OK]をクリックします。次の図によって示します。

例の図表を対照し、変化が分かれます。

空白のフィールドを非表示

最初、ピボットテーブルの分析範囲を選択する時に、デフォルトで[空白]も含めています。そのため、[列ラベル]にある[種類]に[空白]があります。文房具屋の店主は[空白]の意味が分からないはずです。[空白]を非表示に設定しましょう。

Step 8. [行ラベル]の右側にある三角ボタンをクリックし、[空白]をオフにします。次の図によって示します。

こうやった後、[空白]は消えます。

[数量]の表形式を変更する

[数量]のフィールドをよく見ると、少しは変です。[替え芯]の注文数量は4個はずですが、ここでは1を表示します。これはExcelのせいではないですが、合併演算のデフォルト値は[集計]であるため、1つのデータになりました。[カウント]を[合計]に変更した後、正しい数値になります。

Step 9.右下隅の[値]で、[集計-数量]右側にある三角をクリックし、[値フィールドの設定]を開き、[値フィールドの集計]タブの[合計]をクリックし、[OK]をクリックします。次の図によって示します。

[合計]に変更すると、数量は正解の4になりました。

Step 10. 注文請書のデータ完全性を守るため、[値]タブに[小計]を合計すると、文房具の値段合算が分かります。次には文房具屋の店主に渡す完全の注文請書です。

会計士への「各部門の購入金額の明細表」

上記の[店主への注文請書]を練習すれば、会計士へのテーブルはきっと簡単です。

まず、同じステップでStep 1からStep 3までをやります。次には、[種類]を[行ラベル]に、[部門]を[列ラベル]にドラッグし、[小計]を[値]にドラッグします。集計を[合計]に変更します。次の図に従って、気楽に作成できます。

上記の図から見ると、経費を一番かかる部門は一目瞭然です。[名前]を[列ラベル]にドラッグすると、誰か一番多く注文した、注文の金額もはっきりわかります。このような強い分析能力はピボットテーブルのメリットです。

テーブル全体を[アイテムのラベルを表形式で表示する]に設定する

この記事はチュートリアル用のため、実際にピボットテーブルを作成する場合には不便なところがあります。そのため、著者はMicrosoftの資料を探しました。デフォルト値を変更できないですが、ワンボタンでピボットテーブル全体の表形式を変更できます。手順は次に解説します。

まず、ピボットテーブルにあるワークシートに切り替え、ピボットテーブルに任意の位置をクリックし、ピボットテーブル ツールに移動します。次の図によって示します。

次には[デザイン]タブをクリックし、[レポートのレイアウト]のドロップダウンメニューをクリックして、[表形式で表示]を選択します。次の図によって示します。

[アウトライン形式で表示]に切り替えるのも同じ手順です。