ピボットテーブル

内容

  1. 1 サマリー
  2. 2 学習する項目
  3. 3 データベースからデータをインポートする
  4. 4 ピボットテーブルを作成する
  5. 5 小さな値をまとめる
  6. 6 追加カテゴリソース
  7. 7 分析テンプレートの作成

サマリー

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

必要な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ステートメントを入力します。
    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'
            
    
  6. メインメニューのファイル:現在のワークシートに保存を選択し、これらの設定をワークシートに保存します。次にクエリーを選択します。インポートを選択して、データをワークシートにインポートします。SQLエディタを閉じます。インポートデータを確認でき、以下のような画像が表示されます。
    Pivot imp1.png
  7. 閉じるをクリックしてダイアログを閉じます。

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

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

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

    Pivot Table Tutorial n02.png

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


小さな値をまとめる

このセクションでは、列の合計の上位3列だけを表示する方法と、その他の列を1つの列にまとめる方法を示します。(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. フィルタアイコンを再度クリックして、コンテキストメニューでフィルタを有効するを選択します。
  3. Pivot1ワークシートのカギマークをクリックし、再計算を選択します。次のピボットテーブルが示すように、他の2つの製品カテゴリは2005年と2006年の費用データには記録されていませんので、Bikesのみが存在しています。
    Pivot Table Tutorial filt1.png
  4. Pivot1 のワークシートに戻り、作図:棒グラフ/円グラフ:縦棒グラフを選択し、棒グラフ(Graph1)を作図します。
  5. 次に、ピボットテーブルに2つの無くなったカテゴリを加えます。Pivot1ワークシートのカギマークをクリックし、パラメータを変更を選択します。次のようにダイアログを編集し、OKをクリックしてダイアログを閉じます。ピボットテーブルは次のようになります。
    Pivot Table Tutorial filt3.png
    ピボットテーブルは次のようになります。
    Pivot Table Tutorial filt4.png
  6. Pivot1 のワークシートに再度戻り、棒グラフ(Graph2)を作図します。グラフでは無くなったカテゴリが表れています。
  7. 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