エクセル（Excel）技道場 HOME ｜ このページのトップ
| エクセル技道場 HOME |
| エクセルの技 目次 |
| シート |
| 関数 |
| 名前定義 |
| ツールバー |
| セル選択 |
| ファイル |
| 条件付き書式 |
| 書式設定 |
| 編集 |
| 印刷 |
| 入力規則 |
| マクロ |
| グラフ |
| ピボットテーブル |
| エクセル・ショートカット |
| エクセル関数語源リスト |
| エクセル関数引数リスト |
ピボットテーブルで合計
【問題】
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.の[集計の方法]では、以下の方法を指定することができます。
合計
データの個数
最大値
最小値
積
数値の個数
標本標準偏差
標準偏差
標本分散
分散
【問題】
A1:B9に以下のようにデータが入力されているとします。
item data
A 80,000
A 20,000
A 30,000
B 50,000
B 60,000
B 40,000
C 10,000
C 90,000
[集計方法]は合計でピボットテーブルを作成しました。
合計 / data
item 合計
A 130,000
B 150,000
C 100,000
総計 380,000
これを以下のように1万単位で表示させるには、どうしたらよいでしょうか？
合計 : 万単位
item 計
A 13
B 15
C 10
総計 38
【回答】
1.ピボットテーブル レポートのセルどれかをクリック
2.[ピボットテーブル] ツールバーで、[ピボットテーブル]-[数式]-[集計フィールド]
3.[名前]ボックスに例えば、 万単位 と入力
4.[フィールド]ボックスで data（万単位にしたいフィールド名）をダブルクリック
→これで[数式]ボックスに =data と入力されます。
5. =data のあとに /10000 と入力。
→これで[数式]ボックス内の式は =data/10000 になります。
6.[追加]ボタンをクリック
7.[OK]ボタンをクリック
8. 合計：data となっているセルを選択し右クリック-[表示しない]
【解説】
サンプルファイルを用意しました。
千単位、百万単位で表示するのは、書式設定だけで可能です。以下は参考ページです。
【エクセル技道場】−書式設定−千単位、百万単位で表示
http://www2.odn.ne.jp/excel/waza/format.html#SEC4
【問題】
A1:A10に↓のように売上が入力されています。
売上
1500
2000
2500
3200
3300
3600
3800
4200
4800
このデータについて、以下の(1)から(4)がそれぞれ何件あるかをピボットテーブルで集計するにはどうすれば良いでしょうか？
(1) 2000未満、
(2) 2000以上3000未満
(3) 3000以上4000未満
(4) 4000以上5000未満
【回答】
1.リスト内（A1:A10）のセルのどれかを選択
2.メニュー[データ]-[ピボットテーブルとピボットグラフレポート]
3.[Excelのリスト／データベース]と[ピボットテーブル]にチェックが入っていることを確認して、[次へ]ボタンをクリック
4.[範囲]ボックスに $A$1:$A$10 が入力されていることを確認して[次へ]ボタンをクリック
5.[レイアウト]ボタンをクリック
6.[行]に 売上 をドラッグ、[データ]にも 売上 をドラッグ
7.[データ]にドラッグした 合計：売上 をダブルクリック
8.[集計の方法]で データの個数 をダブルクリック
9.[OK]ボタンをクリック
11.[既存のワークシート]をクリック。例えばセルD1をクリック。
12.[完了]ボタンをクリック
→これで、いったんピボットテーブルが完成
13.セルD2上で右クリック-[グループ化]
14.[先頭の値]を 2000 に
15.[末尾の値]を 5000 に
16.[単位]が 1000 になっていることを確認して[OK]ボタンをクリック
これで↓のように表示されます。
データの個数 / 売上
売上 合計
<2000 1
2000-2999 2
3000-3999 4
4000-5000 2
総計 9