Excel Tech-3 / ピボットテーブル
ピボットテーブルで合計
ピボットテーブルで合計
ピボットテーブルで合計
【問題】
A1:C10に以下が入力されています。
日付 金額 銀行
9月1日 1,000 A銀行
9月1日 5,000 A銀行
9月1日 20,000 A銀行
9月1日 9,000 B銀行
9月9日 2,000 B銀行
9月9日 5,000 B銀行
9月9日 6,000 C銀行
9月9日 10,000 C銀行
9月9日 20,000 C銀行
これを日付毎、銀行毎に以下のように合計を算出するにはどうしたら良いでしょうか？
日付 A銀行 B銀行 C銀行 総計
9月1日 26,000 9,000 35,000
9月9日 7,000 36,000 43,000
総計 26,000 16,000 36,000 78,000
【回答】
ピボットテーブルを使います。
1.リスト内のセルのどれかを選択
2.メニュー[データ]-[ピボットテーブルとピボットグラフレポート]
3.[Excelのリスト／データベース]と[ピボットテーブル]にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[範囲]ボックスに $A$1:$C$10 が入力されていることを確認して[次へ]ボタンをクリック
5.[レイアウト]ボタンをクリック
6.[行]に 日付 をドラッグ、[列]に 銀行 をドラッグ、[データ]に 金額 をドラッグ
7.[データ]にドラッグした 合計：金額 をダブルクリック
8.[表示形式]ボタンをクリック
9.[分類]で 数値 を選択し、[桁区切り（,）を使用する]にチェック
10.[OK]ボタンをクリック
11.[OK]ボタンをクリック
12.[OK]ボタンをクリック
13.[既存のワークシート]をクリック。例えばセルF1をクリック。
14.[完了]ボタンをクリック
【解説】
上記はXL2000の場合の手順です。
XL97の場合は以下の手順になります。
1.リスト内のセルのどれかを選択
2.メニュー[データ]-[ピボットテーブルレポート]
3.[Excelのリスト／データベース]にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[範囲]ボックスに $A$1:$C$10 が入力されていることを確認して[次へ]ボタンをクリック
5.[行]に 日付 をドラッグ、[列]に 銀行 をドラッグ、[データ]に 金額 をドラッグし[次へ]ボタンをクリック
6.[データ]にドラッグした 合計：金額 をダブルクリック
7.[表示形式]ボタンをクリック
8.[分類]で 数値 を選択し、[桁区切り（,）を使用する]にチェック
9.[OK]ボタンをクリック
10.[OK]ボタンをクリック
11.[次へ]ボタンをクリック
[既存のワークシート]をクリック。例えばセルF1をクリック。
12.[完了]ボタンをクリック
データを更新するには以下の手順です。
1.ピボットテーブル内のセルのどれかをクリック
2.メニュー[データ]-[データの更新]
→[データの更新によって、ピボットテーブルレポートが変更されました]というメッセージが表示される。
3.[OK]ボタンをクリック
ピボットテーブルで個数カウント
【問題】
A1:C10に以下が入力されています。
日付 金額 銀行
9月1日 1,000 A銀行
9月1日 5,000 A銀行
9月1日 20,000 A銀行
9月1日 9,000 B銀行
9月9日 2,000 B銀行
9月9日 5,000 B銀行
9月9日 6,000 C銀行
9月9日 10,000 C銀行
9月9日 20,000 C銀行
9月1日かつA銀行、9月9日かつB銀行といったように、複数の条件を満たすものの数をカウントするにはどうしたら良いでしょうか？
【回答】
1.リスト内のセルのどれかを選択
2.メニュー[データ]-[ピボットテーブルとピボットグラフレポート]
3.[Excelのリスト／データベース]と[ピボットテーブル]にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[範囲]ボックスに $A$1:$C$10 が入力されていることを確認して[次へ]ボタンをクリック
5.[レイアウト]ボタンをクリック
6.[行]に 日付 をドラッグ、[列]に 銀行 をドラッグ、[データ]に 金額 をドラッグ
7.[データ]にドラッグした 合計：金額 をダブルクリック
8.[集計の方法]で 数値の個数 をダブルクリック
9.[OK]ボタンをクリック
11.[既存のワークシート]をクリック。例えばセルF3をクリック。
12.[完了]ボタンをクリック
【解説】
上記はXL2000の場合の手順です。
XL97の場合は以下の手順になります。
1.リスト内のセルのどれかを選択
2.メニュー[データ]-[ピボットテーブルレポート]
3.[Excelのリスト／データベース]にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[範囲]ボックスに $A$1:$C$10 が入力されていることを確認して[次へ]ボタンをクリック
5.[行]に 日付 をドラッグ、[列]に 銀行 をドラッグ、[データ]に 金額 をドラッグし[次へ]ボタンをクリック
6.[データ]にドラッグした 合計：金額 をダブルクリック
7.[集計の方法]で 数値の個数 をダブルクリック
8.[次へ]ボタンをクリック
9.[既存のワークシート]をクリック。例えばセルF3をクリック。
10.[完了]ボタンをクリック
関数を使って、件数や合計を計算するには以下になります。
セルE2に2001/9/1、セルF2にA銀行と入力するとします。
件数は
=SUMPRODUCT(($A$2:$A$10=E2)*($C$2:$C$10=F2))
金額の合計は
=SUMPRODUCT(($A$2:$A$10=E2)*($C$2:$C$10=F2),$B$2:$B$10)
となります。
ピボットテーブルの参照範囲を可変に
【問題】
A1:C9に、以下のような感じでデータを入力し、ピボットテーブルでデータを分析しています。
No. data data2
1 10 100
2 11 101
3 12 102
4 13 103
5 14 104
6 15 105
7 16 106
8 17 107
9 18 108
データ数は一定ではなく変動します。例えば、A1:C12にデータが入力されたりします。
現在はデータが追加される都度、ピボットテーブルの参照範囲を設定しなおしていますが、これを自動的にデータ数に対応してピボットテーブルを作成するにはどうしたら良いでしょうか？
【回答】
まずは、名前定義です。
1.Ctrl+F3(メニュー[挿入]-[名前]-[定義])
2.[名前]に例えば list と入力
3.[参照範囲]に =$A$1:INDEX($C:$C,COUNTA($A:$A)) と入力
4.[OK]ボタンをクリック
この名前定義を行った後、いつもの手順で、ピボットテーブルを作成します。ただし、[ピボットテーブル/ピボットグラフ
ウィザード - 2/3]のところでCtrl+F3(メニュー[挿入]-[名前]-[定義])で list
を選択します。
これで、データ数の変動に対応したピボットテーブルが作成されます。（ピボットテーブルなので、データの更新を反映させるには、[データの更新]ボタンをクリックする必要はあります。）
【解説】
以下のページもご参考になるのでは？
【エクセル技道場】
−関数−最下行の数値を返す
http://www2.odn.ne.jp/excel/waza/function.html#SEC62
−印刷−可変の印刷範囲の設定
http://www2.odn.ne.jp/excel/waza/print.html#SEC11
ピボットテーブルで最大値
【問題】
A1:B9に以下のようにデータが入力されているとします。
item data
A 100
A 120
A 150
B 80
B 120
B 100
C 50
C 80
これを、A,B,C毎に最大値を求めたいのですが、どうすれば良いでしょうか？
A 150
B 120
C 80
【回答】
1.A1:B9内のセルどれかを選択
2.メニュー[データ]-[ピボットテーブルとピボットグラフ レポート]
3.[Excelのリスト/データベース]及び[ピボットテーブル]が選択されていることを確認して[次へ]ボタンをクリック
4.[範囲]に正しい範囲が選択されていることを確認して[次へ]ボタンをクリック
5.[レイアウト]ボタンをクリック
6.[item]ボタンを[行フィールド]にドラッグ＆ドロップ
7.[data]ボタンを[データエリア]にドラッグ＆ドロップ
8.[データエリア]の[data]ボタン（「合計 : data」と表示されています）をダブルクリック
9.[集計の方法]で[最大値]をダブルクリック
10.[OK]ボタンをクリック
11.[新規ワークシート]にチェックされていることを確認して[完了]ボタンをクリック
【解説】
上記手順9.の[集計の方法]では、以下の方法を指定することができます。
合計
データの個数
最大値
最小値
積
数値の個数
標本標準偏差
標準偏差
標本分散
分散
