FC2ブログ

ピボットテーブル 4

ピボットテーブルについて

Microsoft Excel には
出来ることが まだ、あるのですが
Spreadsheets では、
出来ないようなので
ピボットテーブルは、終わりにします


 
スポンサーサイト



ピボットテーブル 3

ピボットテーブル2.et (「月」の列を作成したバージョン)

201508101001.png

これを使いピボットテーブルを作成
 1.「行範囲」に、「月」
 2.「列範囲」に、「製品名」
 3.「データ範囲」に、「金額」


   201508101002.png

作成したピボットテーブル

201508101003.png

レイアウトの変更

行や列を変更する

「製品名」を 行範囲へ移動します

   201508101006.png

ピボットテーブルが以下のように変化する

   201508101007.png

「月」 と 「製品名」 の 順番を変えると

   201508101008.png   201508101009.png

目的によって変化させることができる
また
「月」 を 列範囲へ移動すると


   201508101010.png


データを項目ごとに表示する

「店舗」 を 「ページ範囲」 へ ドラッグする

   201508101011.png

   201508101012.png

「店舗」が選択されている状態で
サイドバー作業ウィンドウの一番下にある
「並べ替えと表示」 を クリック
「西口店」をクリックする


   201508101017.png

すると
B1セル (すべて) が、「西口店」 に変わり 
テーブル内の数値も変化する


   201508101018.png


それぞれ、「東口店」「南口店」も確認する
「すべて」をクリックすると、元に戻る


集計データの詳細を表示する

クッキーの総計から 売上額の詳細を表示する

クッキーの総計のセルE5 をクリック
[ピボットテーブルツール]タブ
 「詳細データの表示」 を クリックする


   201508101019.png

新規にシートが作成される 「 Sheet2
集計データの詳細が表示される


   201508101020.png


データを更新する

ピボットテーブルの基のデータを変更し
ピボットテーブルのデータを更新してみる

 1.Sheet1の4月のクッキーの売上額を確認する

     B5セル=1575000

   201508101012.png

 2.シート「売上一覧」の
   04/01(E6セル)の「クッキーの数量」を 「10000」に 変更する
   
わかりやすいように、桁数を増やして実験
   金額にすると、315,000 → 31,500,000 差額は、31,185,000

201508101021.png


 3.Sheet1を確認する
   まだ 元のままなので
   [ピボットテーブルツール]タブ 「データの更新」 を クリックする


   201508101022.png

 4.クリックした瞬間に データが更新され数値が変化する
   
1575000 → 32760000 差額=31185000

   201508101023.png


 

ピボットテーブル 2

グループ化について

Excel では、出来たピボットテーブルで
「日付」を 「月別」に グループ化し
 製品別売上を 月単位で集計できますが

Spreadsheetsでは、できないようでした
製品名の並び順を変更したい場合

現在 クッキー・チョコレート・ハーブティ・有機コーヒー・有機ジャム・有機紅茶
変更 有機コーヒー・有機紅茶・有機ジャム・ハーブティ・クッキー・チョコレート

1.変更したいセルをクリック 例:B4「クッキー」
2.[ピボットテーブルツール]タブ 「順序▼」クリック
  表示されるメニュー 「下へ移動」・「末尾に移動」を選択

   クリックし 好みの位置へ移動する

2015080900016.png


ピボットテーブルに表示するデータを変更するには

「製品名」で
 「有機コーヒー」「有機紅茶」「有機ジャム」だけを表示したい場合

 B3セル「製品名」または、セル範囲B4:G4のセルをクリック
 [ピボットテーブルツール]タブ
 「フィールド設定」をクリック


2015080900012.png

 [ピボットテーブルフィールド]画面が表示される
 [表示]タブをクリック


  2015080900013.png

【 すべて表示 】のチェックを外し
 【 有機コーヒー 】
 【 有機紅茶 】
 【 有機ジャム 】
 3つにチェックを入れる
 [OK]クリック


   2015080900014.png

   2015080900015.png


月別に表示できないので
日付の表示を
「4/1」「4/10」「4/15」「4/18」「4/28」だけに チェックを入れる
[OK]クリック

2015080900017.png

4月の合計が表示できる

2015080900018.png


どうしても月別に表示したい場合
KingsoftSpreadsheets では、
 元のデータ「シート売上一覧」を 編集するしかない


セルH3に「月」と入力
H4セルに、=MONTH(B4) と入力する
下へオートフィルする
ピボットテーブルを作成する


2015080900019.png

2015080900020.png

[ピボットテーブルの作成]画面では
 「使用するデータの範囲を指定する」のセル範囲が
 「'売上一覧'$A$3:$H96」と H列まで範囲になる
 [OK]クリック


   2015080900021.png

フィールドリスト」欄を確認すると
一番下に「▼月」が表示されている


   2015080900022.png

「行範囲」に、「▼月」をドラッグし
「列範囲」に、「▼製品名」をドラッグ
「データ範囲」に、「▼金額」をドラッグする


  2015080900023.png

出来たものが、月別の集計

2015080900024.png


Kingsoft Office Spreadsheetsに 機能がない以上
 ソフトを変更するか ・・・→ Microsoft Office を購入し、Excel を使う
 使い手が工夫する



 

ピボットテーブル 1

多角的な視点で、データを分析する

データを統計的にまとめたり
多角的な視点で分析するには 「ピボットテーブル」を使います
「ピボットテーブル」は 対話型のシートテーブルです


データの配置
集計方法の変更
表示するデータの選択などが簡単に設定でき
さまざまな視点でデータを分析できます

集計元データが更新された場合
ピボットテーブルを更新することができます
また
ピボットグラフを作成すると
ピボットテーブルのデータを視覚的に表現したり分析できます


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

1.ピボットテーブル.et   を 開く
2.リスト内のセルをクリック
  例:セルC6
3.[データ]タブ 「ピボットテーブル」アイコンをクリックする


201508090004.png

4.[ピボットテーブルの作成]画面が表示される
  「分析するデータを指定してください」欄では
  【 使用するデータの範囲を指定する 】にチェックが入り
  下の欄にリストの範囲「'売上一覧'!$A$3:$G$96」と表示されていることを確認
  「ピボットテーブルの作成先を指定してください」欄で
  【 新規ワークシート 】にチェックが入っていることを確認
  [OK]をクリックする

   201508090005.png

5.ピボットテーブルが新しいシート「Sheet1」に作成される

201508090006.png

  右側のパネルで、「フィールドリスト」欄の
  「▼日付」をクリック
  下にある「ピボットテーブル範囲」欄の
  「行範囲」に位置へドラッグする


   201508100031.png

「▼製品名」を「列範囲」へ ドラッグする

   201508100030.png

「▼金額」を「データ範囲」へ ドラッグする

   201508100032.png

ピボットテーブルが完成します

2015080900011.png



 

データベース 14 練習問題

練習問題

ファイル:H27C2S3.et  (CS技能評価表計算2級の課題3の一部分)


設問1

シート「一覧表」からシート「分析」へ抽出をする

<抽出1>
 シート「分析」のセルB5以降に
 「合計」の上位5位のものを抽出する

 並び順は、
  1.「合 計」が 「降順」
  2.「客席数」が 「昇順」


<抽出2>

 シート「分析」のセルB20以降に
 ①と②いずれかを満たすものを抽出
 ① 2012年6月1日以降2012年11月30日まで、
    かつ「味」が 5pt 
    かつ「持ち帰り」に「不可」が含まれる
 ② 「客席数」が20席以上で、
    かつ「合計」が15pt以上で、
    かつ「個室」が有りで、
    かつ「予約」が不可以外のもの


 並び順は、
  1.「客席数」が 「降順」
  2.「ジャンル」が「昇順」


設問2 集計
    難しいかもしれませんが、楽しんでください
    シート「一覧表」を末尾にコピーして作業するとやりやすいかも


1.並び順

  「個室」が 「昇順」
  「ジャンル」が 「昇順」


2.集計

  「個室」基準で、「味」「料金」「内装」「接客」の平均
  「ジャンル」基準で、「味」「料金」「内装」「接客」の平均


3.以上の結果を
  シート「分析」のセル範囲 R21:U38の該当欄へ表示する



回答画像

201508100029.png

   201508100027.png

   201508100028.png


 

データベース 13 フィルタオプション2

複数の条件を設定する

1.すべての条件を満たすケース
  複数のフィールドの同じ行に各検索条件を入力する
2.いずれかの条件を満たすケース
  検索条件範囲の異なる行に各検索条件を入力する
3.1つのフィールドに複数の条件または数値の範囲があるケース
  同じフィールド名を必要なだけ作成し、検索条件範囲にいれる
4.複数の検索条件が組み合わせてある場合
  各検索条件の組み合わせが異なる行へ入力


ワールドカード

文字列の一部を検索条件にして、レコードを抽出する際に使用する
 「 * 」 複数(0を含む)個の任意の文字
 「 ? 」 1個の任意の文字


使用例

 201508100024.png

完全一致した文字列を抽出するには

たとえば、
前回の抽出で検索条件として「最寄駅」欄に、「四谷」と入力し
抽出すると 「四谷三丁目」も抽出される

  201508090001.png

「四谷」だけを抽出したい場合
検索条件には「="=四谷"」と入力する


   201508090002.png

  一度お試しください

検索条件範囲の設定

前回の設定では
フィールド名をすべてコピー&貼付けしましたが
検索条件範囲は、条件を設定するフィールド名だけで大丈夫です
条件を設定しないフィールド名を作成する必要はありません


参考

データを抽出する際 
 検索条件範囲に空白行を含めると
 すべてのレコードが抽出されます



 

原稿用紙設定 Writer 2013

ついに

原稿用紙設定が装備されました

2015年3月24日に
バージョン「9.1.0.4917」の公開の際 
原稿用紙の機能に対応しました」
ニュースになりましたが

Kingsoft Office 2013(9.1.0.4917)バージョンに搭載予定だった
原稿用紙機能については、
不具合が見つかったため、機能を非公開にしております


いつになるのかと思っていました

別のパソコンに、キングソフトオフィスをインストールしたとき
バージョンを確認したら
なんと 更新されていて「「9.1.0.4922」になっていました


バージョン 9.1.0.4922

201508100020.png

さっそく確認したら
ちゃんと「原稿用紙設定」がありました


201508100001.png

[レイアウト]タブにある201508100003.png 「原稿用紙設定」をクリック
すると
[原稿用紙設定]画面が表示される


    201508100002.png

【 原稿用紙設定を有効にする 】にチェックを入れる

    201508100004.png

「文字数×行数」の選択肢は、4種類でした
 10×20 (200文字)
 15×20 (300文字)
 20×20 (400文字)
 20×25 (500文字)


    201508100005.png

「スタイル」では、3種類が選択可能
 マス目付原稿用紙
 下線付原稿用紙
 外枠付原稿用紙


  201508100006.png

「罫線の色」は、好みの色を選択できるようです

    201508100007.png

あとは
  用紙サイズ
  印刷の向き 縦・横
  改行の設定

    禁則処理を行うか行わないか
    句読点のぶら下げを行うか行わないか


普通 原稿用紙設定というと、400字詰め原稿用紙なので
 印刷の向きは、「横」ですね 通常は


    201508100008.png

 プレビューで確認し、[OK]クリック

201508100009.png

出来たものを 確認すると 「ヘッダー」「フッター」が入っている
自分に 必要か、不必要か 
判断し いらない場合は、
「ヘッダー」「フッター」の部分を、ダブルクリックするか
[挿入]タブ 201508100010.png 「ヘッダーとフッター」アイコンをクリックすると
編集できる状態になる


201508100011.png

たとえば
「ヘッダー」の【 ページ1ページ 総1ページ 】を削除する
表示されている 201508100013.png をクリック
メニューが表示される
  現在のページのページ番号を削除
  以前のページ番号を削除
  現在のセクションのページ番号を削除
  すべてのページ番号を削除


必要ないので、「すべてのページ番号を削除」にチェックが入った状態で
[OK]をクリック


201508100012.png

201508100014.png


「フッター」の場合
フッター部分をクリックする


201508100015.png

[ページ番号の挿入]が フッターの方に表示され
カーソルが 「20 × 20 = 400」の前に表示される


201508100016.png

[Delete]キーで 「20 × 20 = 400」を削除する

201508100017.png

[ヘッダーとフッター]タブ 閉じるをクリックする

201508100018.png

これで、ヘッダーフッターには何も表示されない

201508100019.png


もちろん ページ番号が必要な方は、
必要な設定
 配置設定
 右寄せ・中央寄せ・左寄せ
 表示設定
 「1ページ」・「P1」・「1」など
設定を行い表示してください


    201508100021.png

ちなみに
下線付原稿用紙


201508100022.png

 ※ 実は、レポート用紙タイプかと思ったのですが...

一応
外枠付原稿用紙


201508100023.png

 ※ どんな場合に使うのかな?


 

データベース 13 フィルタオプション 2

「賃料」を追加して抽出する

前回の終了時のファイルに条件を追加する

3つの駅で、「賃料」が10万円以下を抽出する

10万円以下を数式にすると <=100000 
この数式を 「賃料」の列に入力する


201508080022.png

手順は、前回と同じ

 1.リスト内のセルをクリック
 2.「自動フィルタ」 「フィルタオプションの設定」クリック

    前回操作を行ったので
    「検索条件範囲」にセル範囲$A$4:$I$7と表示されている
 3.[OK]クリック

    201508080023.png

 4.抽出結果が表示される
   総データ150 のうち 8個のデータ


201508080024.png


解除する

「自動フィルタ」→「すべて表示」でデータがすべて表示されます

201508080025.png



 

データベース 12 フィルタオプション

フィルタオプションを使ってデータを抽出する


フィルタオプションを設定してもデータを抽出できます
初期の頃の「オートフィルタ」は機能が少なかったので
「フィルタオプション」は、非常に貴重な機能でした
現在では、「オートフィルタ」で出来ることが増えたので
「フィルタオプション」でなくてもできるケースがあります
が、
知っていると便利な機能ですので書きます


1つのフィールドに3つ以上の検索条件を設定したり
複数のフィールドの検索条件を組み合わせて設定する

 「フィルタオプションの設定」画面

    201508080008.png

この画面で設定する項目の内容は 

1.抽出先
  [選択範囲内]を選択すると
  リスト内に指定した検索条件に一致しないレコードが非表示になります
  [指定した範囲]を選択すると
  検索条件に一致したデータとフィールド名が
  シートの指定した範囲にコピーされます
2.リスト範囲
  抽出の対象となるリストのセル範囲を指定する
  リスト内の任意のセルをアクティブにしておくと
  リスト範囲は自動認識されます
3.検索条件範囲
  データを抽出するための条件が入力されたセル範囲を指定する
  検索条件範囲には、条件を設定するフィールド名が必要です
4.抽出範囲
  [抽出先]の[指定した範囲]を選択した場合に
  抽出結果を表示するセル範囲を指定する
  抽出範囲には、リストが入力されているシート内のリスト以外の範囲を指定します
  空白セルを1つだけ指定すると、そのセルを左上隅とする範囲に表示されます


検索条件範囲を作成する

元の表はそのままにしておきたいので
新しいシートを作成し、表をコピー&ペーストします

データベース2_1.et 

1.シート見出し「賃貸物件」を 右クリック
2.「シートの移動またはコピー」を クリック

  201508080009.png

3.[シートの移動またはコピー]画面
  下にある
  【 □ コピーを作成する 】に チェックを入れる
  「挿入先」欄の(末尾へ移動)を選択
  [OK]クリック


  201508080010.png

4.新しいシート名が「賃貸物件(2)」となる
  リストの上に、検索条件範囲を作るための行を挿入します
  行番号「4」で右クリックします


  201508080011.png

5.「挿入」の右側にある行数ボックスの数値を「1」から「」に変更し
  「挿入をクリックする


   201508080012.png

6.行が挿入される 「5」にしたので、5行挿入される

  201508080013.png

7.リストのフィールド行をコピーする
  セル範囲A9:I9


201508080014.png

8.セルA4に貼り付ける

201508080015.png

9.リストのフィールド名が貼り付けられる

201508080016.png

 フィルタオプションでは、
 検索条件範囲に、条件を設定するフィールド名が必要なので
 このように、フィールド名を コピー&貼り付けし
 下に条件を入力する行を多めに作成する

 
 ※ リストの範囲に隣接したセルは、空欄でなければならない


準備ができたので 3つの駅名を指定して抽出する

最寄駅が「渋谷」「原宿」「恵比寿」の3つの駅のいずれかのデータを抽出する

抽出する条件が「いずれか」の場合は、検索条件欄の違う行へ入力する
 ※ 「いずれか」=「または」=「OR」

「最寄駅」の列に、渋谷・原宿・恵比寿を入力する

注意
   入力する際には、同じ形式で入力すること
   この程度では、漢字の変換ミスなどないと思いますが違うと抽出できません
   不安な方は、コピー&ペーストで行うほうが良いでしょう

201508080017.png


リスト内のセルをクリック
 例:C11
[ホーム]タブ 「自動フィルタ」
「フィルタオプションの設定」クリック


201508080018.png

[フィルタオプションの設定]画面が表示される

   201508080019.png

 1.「抽出先」が「選択範囲内」であることを確認
 2.「リスト範囲」が、シート「賃貸物件(2)」の$A$9:$I$159になっていることを確認
 3.「検索条件範囲」欄をクリック
   セル範囲A4:I7をドラッグする
   [OK]クリック


   201508080020.png

 4.総データ150のうち28個が抽出される

201508080021.png


現在の「オートフィルタ」は、
 チェックボックスにチェックを入れるスタイルなので
 複数の条件も選択できます


     201508080026.png


 

データベース 11 オートフィルタ 4

オートフィルタの解除

1か所の抽出の場合は、
 「クリア」をクリックすれば 解除できます
複数個所の抽出を行った場合 
 抽出を解除するには
 2通りの方法があります


  「すべて表示する」
  オートフィルタを解除する


  すべてを表示する

 1.抽出条件の解除は、
   「自動フィルタ▼」クリック
   メニューの「すべてを表示する」をクリック

201508080001.png

 2.すべてのデータが表示されますが、オートフィルタ機能はそのままです

201508080002.png


 オートフィルタを解除する

 1.「自動フィルタ▼」クリック
   メニューの「自動フィルタ」をクリック


201508080003.png


 抽出が解除され、オートフィルタの▼ボタンもなくなり
 自動フィルタアイコンも変化します


201508080004.png

     201508080007.png