月別アーカイブ: 2006年9月
目標値達成のための条件を探る（ゴールシーク）
2006年9月30日 集計・シミュレーション関連
例えば、
[Sheet1]
| A | B | |
| 1 | 科目 | 得点 |
| 2 | 国語 | 60 |
| 3 | 算数 | 80 |
| 4 | 理科 | 70 |
| 5 | 社会 | |
| 6 | 平均 | 70 |
【 ただし、B6セル⇒ =AVERAGE(B2:B5) 】
という表があるときに、全教科の平均点を75点以上にするには、社会科で何点以上とる必要があるかを知りたい場合は、以下の手順で行います。
(1)[ツール(T)]→[ゴールシーク(G)]
(2)「ゴールシーク」ウィンドウの「数式入力セル(E)」の空欄をクリックし、B6セルをクリック。(「$B$6」と入ればOKです。)
(3)引き続き、「ゴールシーク」ウィンドウの「目標値(V)」に「75」と直接入力。次の「変化させるセル(C)」の空欄をクリックし、B5セルをクリック(「$B$5」と入ればOKです。)して「OK」。
「解答が見つかりました」というメッセージとともに、B5セルに「90」というデータが入っていれば「OK」ボタンをクリックして完了です。単純なシミュレーションであれば、この機能で充分対応可能です。
<<ガイドシートURL>>
http://www.tcat.ne.jp/~ryu/PLE/Shukei/45.pdf
ピボットテーブル上のデータを利用する
2006年9月28日 集計・シミュレーション関連
例えば、
[Sheet1]
| A | B | C | D | |
| 1 | 番号 | 氏名 | ﾚﾍﾞﾙ | 得点 |
| 2 | 100 | Ｄさん | A | 45 |
| 3 | 120 | Ａさん | B | 50 |
| 4 | 130 | Ｅさん | C | 80 |
| 5 | 140 | Ｃさん | B | 95 |
| 6 | 150 | Ｂさん | C | 60 |
という表があるときに、前回(第41号)で、[Sheet2]にレベルごとの平均点と人数を表示させました。
このピボットテーブルのデータの一部(レベルBの平均点)を[Sheet3]に転記して別の計算に利用したい場合は、以下の手順で行います。
(1)[Sheet2]のA3セルからC8セルを選択し、[挿入(I)]→[名前(N)]→[定義(D)]
(2)「名前の定義」ウィンドウで、「参照範囲(R)」が「=Sheet2!$A$3:$C$8」になっていることを確認し、「名前(W)」の空欄に「pivot」と入力して「OK」ボタンを押す。
(3)[Sheet3]のA1セルを選択し、「=getpivotdata(pivot,"B 平均 : 得点")」と、getpivotdata関数を入力する。
getpivotdata関数の( )内には、まず対象とするピボットテーブルの名前を入れます。上の例では、手順(1)～(2)でピボットテーブルに「pivot」という名前をつけましたので、これを入力します。ピボットテーブルの始点となっているセル(上の例では「Sheet2!$A$3」)を入力してもできますが、わかりやすくするために名前をつけてみました。
次に「，」で区切って、半角のダブルクォーテーションの中に、縦軸の項目名，横軸の項目名を入れます。項目名の間には「，」ではなく、半角スペースを入れます。上の例ではレベルBの平均点を取得したいので、"B 平均 : 得点"と入力します。
getpivotdata関数を用いると、ピボットテーブルのデータが更新された場合でも、自動的に更新後のデータが取得されるので便利です。
<<ガイドシートURL>>
http://www.tcat.ne.jp/~ryu/PLE/Shukei/44.pdf
一つの表上のあらゆるデータを自由に集計･分析する（ピボットテーブル）（2）
2006年9月26日 集計・シミュレーション関連
[Sheet1]
| A | B | C | D | |
| 1 | 番号 | 氏名 | ﾚﾍﾞﾙ | 得点 |
| 2 | 100 | Ｄさん | A | 45 |
| 3 | 120 | Ａさん | B | 50 |
| 4 | 130 | Ｅさん | C | 80 |
| 5 | 140 | Ｃさん | B | 95 |
| 6 | 150 | Ｂさん | C | 60 |
という表があるときに、[Sheet2]にレベルごとの合計点を表示させたい場合は、以下の手順で行います。
（「ピボットテーブル」という集計機能を使います。）
(1)[Sheet1]のA1セルからD6セルを選択し、[データ(D)]→[ピボットテーブルとピボットグラフレポート(P)]。
(2)そのまま「次へ」ボタンをクリック。
(3)セル範囲が「$A$1:$D$6」になっていることを確認して「次へ」ボタンをクリック。
(4)「新規ワークシート(N)」にチェックが入っていることを確認し、「完了(F)」ボタンをクリック。
(5)「ピボットテーブル」ウィンドウの「レベル」をA4～A5セルあたりにドラッグアンドドロップ。
(6)「ピボットテーブル」ウィンドウの「得点」をD6～D7セルあたりにドラッグアンドドロップ。(ここまで前回と同じ)
(7)もう一度、「ピボットテーブル」ウィンドウの「得点」を今度はB5～B6セルあたりにドラッグアンドドロップ。
(8)B4セルの「合計 : 得点」の上にカーソルを合わせ、右クリックし、出てきたメニューの「フィールドの設定(E)」を選択。
(9)「集計の方法(S)」で「データの個数」を指定し、「OK」。
(10)B5セルの「合計 : 得点2」の上にカーソルを合わせ、右クリックし、出てきたメニューの「フィールドの設定(E)」を選択。
(11)「集計の方法(S)」で「平均」を指定し、「OK」。
(12)B3セルの「データ ▼」の上にカーソルを合わせ、カーソルが四方向矢印の形に変わったら、そのままC3セルの「計」の上にまでドラッグアンドドロップ。
新しいシートの表のデータが、
| A | 1 | 45 |
| B | 2 | 72.5 |
| C | 2 | 70 |
| 総計 | 5 | 66 |
となっていればOKです。
ピボットテーブルはこの他にもいろいろな設定が可能です。最初は少し難しいですが、慣れれば表のすべてのデータを縦横無尽に操ることができ、表計算ソフトの便利さを体感できます。オプションの設定などをいろいろいじって試してみてください。
<<ガイドシートURL>>
手順(1)～(6)：
http://www.tcat.ne.jp/~ryu/PLE/Shukei/42.pdf
手順(7)～(12)：
http://www.tcat.ne.jp/~ryu/PLE/Shukei/43.pdf
