ピボットテーブル

概要

ピボットテーブルはデータ概要の把握や、分析、比較、データ間の関係確認を簡便に行えます。このツールは、ワークシートにある情報をソート、カウントしたり、合計、最小・最大・平均の計算もできます。

必要なOriginのバージョン: Origin 2015 SR0以降

学習する項目

  • ピボットテーブルでデータの概要を表示する
  • ピボットテーブルで出力した列や行の合計によってデータをソートする
  • 列や行の小さな値をまとめる等、カスタムする

データベースからデータをインポートする

  1. ピボットテーブルを作成する前に、データベースからデータをインポートする必要があります。 ここでは、サーバマシンmyServer でSQLを実行している環境でAdventureWorks2008R2という名前のデータベースの設定を行ったとします。ユーザ名は「accounting」、パスワードは「mydatabase」であるとします。
  2. データベースに接続するには、接続文字列を使用します。
    Provider=SQLOLEDB.1;Password=mydatabase;Persist Security Info=True;
    User ID=accounting;Initial Catalog=AdventureWorks2008R2;Data Source=myServer
  3. 空白のワークシートをアクティブにし、データベースアクセスツールバーのSQLエディタを開くボタンをクリックしてSQLエディタを起動します。
    ImportDataDatabase 1.png
  4. SQLエディタのファイルメニューから接続文字列を編集を選択してダイアログを開きます。テキストボックスに上記の接続文字列を入力します。入力してからテストボタンをクリックして、接続を確認します。問題なければ、OKをクリックしてデータベースにアクセスします。
    ImportDataDatabase 2.png
  5. 右側のテキストボックスに以下のSQLステートメントを入力します。
  6. SELECT 
    DatePart(yyyy, SOH.OrderDate) AS YEAR,
    CR.Name As CustomerCountry,
    	Pr.Name As ProductName,
    	Pr.Color As ProductColor,
    	PC.Name As ProductCategory,
    	PS.Name As ProductSubcategory,
    	SOH.OrderDate As OrderDate,
    	SOD.OrderQty As OrderAmount,
    	SOD.LineTotal As TotalCost
    FROM Person.CountryRegion AS CR
    INNER JOIN Person.StateProvince AS SP
    ON SP.CountryRegionCode = CR.CountryRegionCode
    INNER JOIN Person.Address AS A
    ON A.StateProvinceID = SP.StateProvinceID
    INNER JOIN Person.BusinessEntityAddress AS BEA
    ON BEA.AddressID = A.AddressID
    INNER JOIN Person.Person AS P
    ON P.BusinessEntityID = BEA.BusinessEntityID
    INNER JOIN Sales.PersonCreditCard AS PCC
    ON PCC.BusinessEntityID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderheader AS SOH
    ON SOH.CreditCardID = PCC.CreditCardID
    INNER JOIN Sales.SalesOrderDetail AS SOD
    ON SOD.SalesOrderID = SOH.SalesOrderID
    INNER JOIN Production.Product AS Pr
    ON Pr.ProductID = SOD.ProductID
    INNER JOIN Production.ProductSubcategory AS PS
    ON PS.ProductSubcategoryID = Pr.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS PC
    ON PC.ProductCategoryID = PS.ProductCategoryID
    --WHERE SOH.OrderDate BETWEEN '1/1/2005' AND '12/31/2008'
  7. メインメニューのファイル:現在のワークシートに保存を選択し、これらの設定をワークシートに保存します。次にクエリー:インポートを選択して、データをワークシートにインポートします。SQLエディタを閉じます。次の画像で、インポートされたデータを確認出来ます。
  8. Pivot imp1.png
  9. 閉じるをクリックしてダイアログを閉じます。

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

インポートしたデータセットは、異なる6つの国々での、3つの製品カテゴリー(Bikes,Accessories, Clothing)の年毎の全体費用をまとめたものです。異なる製品カテゴリーの年間全体コストの合計を確認するために、ピボットテーブルを作成します。次のピボットテーブルを作成するステップに従ってください。

  1. Sheet1をアクティブにし、メニューからデータ整形:ピボットテーブルを選択し、ダイアログを開きます。ダイアログ内で次の設定を行います。
    • ピボットテーブルの行ソースでは三角形ボタンButton Group List Add.pngをクリックし、列Aを追加します。
    • ピボットテーブルの列ソースでは三角形ボタンButton Group List Add.pngをクリックし、列Eを追加します。
    • サマリー出力タイプ合計を選択します。ピボットテーブルのデータソースに列Iを選択します。
    • オプションブランチを開き、行合計の列追加列合計の行追加にチェックを付けます。そして、合計列のソートドロップダウンリストから行ラベルの昇順を選びます。

    Pivot Table Tutorial n02.png

  2. OKボタンをクリックして、ピボットテーブルを作成します。テーブルは次のようになります。
    Pivot Table Tutorial n03.png


小さな値をまとめる

このセクションでは、しきい値%を超えた総計に閉める集計値(Count/Sum/Mean/Min/Max)の割合をこれらのカテゴリに表示し、デフォルトの分類に小さな値の分類を統合する方法を示します。

  1. 上記の例題を踏まえて、Pivot1ワークシートの錠前アイコンをクリックし、パラメータの変更を選んで再びダイアログを開きます。

    Pivot Table Tutorial nn.png

  2. ダイアログ内で次の設定を行います。
    • より小さな値をまとめるブランチを開き、方向の統合ドロップダウンリストから、を選びます。
    • 総計の%モードのドロップダウンリストから選択し、
    • %の欄に30を入力します。列ラベルのテキストボックスに、Other Smaller Itemsと入力します。
    • オプションブランチを開き、行合計の列追加列合計の行追加にチェックを外します。
    Pivot Table Tutorial n05.png

  3. OKボタンをクリックします。ピボットテーブルは合計によってまとめられたデータを表示します。Bikesのカテゴリーのみ、総合計の%がしきい値の30%を超えています。他の小さいカテゴリーは減少して、Other Smaller Itemsのカテゴリーに入れられます。

    Pivot Table Tutorial n06.png

追加カテゴリソース

このセクションでは、Column Source Extra Valueでソースデータシートから無くなっている、いくつかのカテゴリーを表示する方法を説明します。これは、全ての必要なカテゴリーが、後で作図に使用される可能性のある結果ピボットテーブル内にあるか確認する時に便利です。

2007より前の、異なる製品カテゴリーの全体コストの合計を確認したいとします。次のピボットテーブルを作成するステップに従ってください。

  1. 2007年より前の年を除外するため、データフィルタを使います。Sheet 1 に移動し、列Aを選択します。メインメニューバーにあるButton Add Or Remove Data Filter.pngボタンをクリックします。列Aにあるフィルタアイコンを再度クリックし、より小さいを選択します。単純な数値フィルター ダイアログを次の通りに編集し、OKをクリックしてダイアログを閉じます。
    Pivot Table Tutorial filt.png
  2. Pivot1ワークシートのカギマークをクリックし、再計算を選択します。次のピボットテーブルが示すように、他の2つの製品カテゴリは2005・2006の費用データには記録されていませんので、Bikesのみが存在しています。
    Pivot Table Tutorial filt1.png
  3. Pivot1 のワークシートに戻り、作図:棒、円、面積:縦棒 を選択し、縦棒グラフ(Graph1)を作図します。
  4. 次に、ピボットテーブルに2つの無くなったカテゴリを加えます。Pivot1ワークシートのカギマークをクリックし、パラメータを変更を選択します。次のようにダイアログを編集し、OKをクリックしてダイアログを閉じます。 ピボットテーブルは次のようになります。
    Pivot Table Tutorial filt3.png
    ピボットテーブルは次のようになります。
    Pivot Table Tutorial filt4.png
  5. Pivot1のワークシートに再度戻ります。 列Aにあるフィルタアイコンを再度クリックし、ポップアップメニューからフィルタを削除を選択してフィルタを削除します。全ての列を選択して縦棒グラフ(Graph2)を作図します。グラフでは無くなったカテゴリが表れています。
  6. Pivot1のワークシートに再度戻り、灰色の部分を右クリックして、グラフの追加を選択し、Graph2をPivot1ワークシートに追加します。
    Pivot Table Tutorial filt5.png

同様に年のフィルタもデータベースから取得されます。この場合、次のスクリプトを書き換えることで、好みの時間帯に編集することが出来ます。

 --WHERE SOH.OrderDate BETWEEN '1/1/2005' AND '12/31/2008'

分析テンプレートの作成

このセクションでは、ピボットテーブル用の分析テンプレートの作成、データベースからの再インポートと新規のデータのためにピボットテーブルを作成する分析テンプレートの再利用について説明します。

  1. Book1をアクティブにし、ファイル:ワークシートをテンプレートとして保存を選択し、Book1をSumTotalCost.ogwと名前を付けて保存します。
  2. 新規のOPJを開き、ファイル:開くからSumTotalCost.ogw を開きます。
  3. データソースを、データベースでAdventureWorks2008 に変更します。
    • Sheet1 をアクティブにし、SQLエディタを開くボタンButton Open SQL Editer.pngをクリックして、SQLエディタを開きます。
    • メインメニューからファイル:接続文字列を編集を選択し、開いたダイアログに次の文字列を入力します。テストをクリックして、OKをクリックしデータベースに接続します。
    Provider=SQLOLEDB.1;Password=mydatabase;Persist Security Info=True;
    User ID=accounting;Initial Catalog=AdventureWorks2008;Data Source=myServer
    • SQL編集のダイアログに戻り、右側のパネルで、以前のスクリプトを次のように書き直します。
    --WHERE SOH.OrderDate BETWEEN '1/1/2001' AND '12/31/2004'
    • メインメニューの ファイル:現在のワークシートに保存 を選択し、これらの設定をワークシートに保存します。次に クエリー:インポート を選択して、データをワークシートにインポートします。SQLエディタを閉じます。次の画像で、インポートされたデータを確認出来ます。

    Pivot-table-reimport1.png

  4. ピボットテーブルを更新するために、Pivot1 のシートに移動し、黄色のカギマークをクリックして再計算を選択します。新規のデータに従って、ピボットテーブルが更新されます。
  5. 埋め込みグラフを更新するには、
    • 埋め込みのグラフをダブルクリックし、フローティングチャートをポップアップさせます。
    • グラフを選択し、再計算ボタンButton Rescale.pngをクリックして更新します。フローティングチャートも同様に更新します。
    • フローティングチャート右上の矢印をクリックし、ワークシートにフローティングチャートを戻します。ワークシートは次のようになります。
    Pivot-table-reimport2.png