ピボットテーブルとショートカットキーを効率的に学ぶ講座の④です。
ピボットテーブルの集計の元となるデータを変更する方法を紹介していきましょう。
よかったらフォローしてください。→ハヤトのTwitter
1.ピボットテーブルとショートカットキーを効率的に学ぶ講座
2.ピボットテーブルで活用したい5つの機能。グループ化、フィルター他
3.Excelの印刷設定。1ページに収める・タイトルを全ページに表示
4.フィルターや置換機能でピボットテーブルのデータを修正
5.ピボットテーブルの集計や表示を変更して損益計算書をカスタマイズする
6.Vlookup関数を使ってデータを追加してピボットテーブルをカスタマイズする
これまでの内容
講座①
・ピボットテーブルの作成方法
・ピボットテーブルのデータのルール
講座②
・千円単位の表示の設定
・ピボットテーブルのグループ化
・ピボットテーブルのフィールドの展開/折りたたみ
・ピボットテーブルの比率の計算
・ピボットテーブルの条件付き書式
・ウィンドウ枠の固定
・ショートカットキーでの操作
講座③
・全てを1ページに収めて印刷
・全ページにタイトルを表示して印刷
前の記事
→Excelの印刷設定。1ページに収める・タイトルを全ページに表示
※この記事からスタートの場合、Pivotサンプルデータ04をダウンロ-ドして、シート「Sheet1」から初めてください。
フィルターや置換機能でピボットテーブルのデータを修正
・フィルター
・F2で編集
・フィル機能
・一括置換
・オートフィル機能
・列の挿入
・ピボットテーブルのデータソースの変更
・ピボットテーブルの更新
5.色々な機能を使って目的に探してデータを修正する
ピボットテーブルの表記方法を以下のように変更してください。
①「勘定科目」の「04経費」を「+」ボタンをクリックして、内訳を展開してください。(展開している場合はそのままでOKです)
よく見ると「3-05福利更正費(原)」が間違っています。
「福利厚生費」が正しいですので、訂正が必要となります。
シート「伝票データ」のデータを確認しましょう。
②「フィルター(Ctrl + Shift + L)」でフィルター機能を実行します。
③勘定科目を選択した状態で、「Alt + ↓」でフィルターメニューが表示されます。
④検索ボックスに「福利更正」と入力して「OK」をクリックしてください。
勘定科目が「3-05福利更正費(原)」のみ表示されます。
⑤セルC747を選択した状態で、F2を押して編集状態にします。
「←キー」と「Backspace」で『更正』を『厚生』に訂正します。
方法1:貼り付けで一括訂正する
⑥セルD748を選択した状態で「上のセルをコピー(Ctrl + D)」して、セルC748を「コピー(Ctrl + C)」し、「セル一括選択(Shift + Ctrl + ↓)」で「貼り付け(Ctrl + V)」を実行します。
もう一つの方法で試しますので、「Ctrl + Z」で戻してください。
方法2:フィル機能で一括で訂正する(マウスを利用)
⑦セルC747を選択し、セル右下にマウスポイントが「+」になるようにあわせ、ダブルクリックします。
フィルターで表示されているものが一括で選択している内容に変更されます。
⑧方法3:一括置換で訂正する
ピボットテーブルを見ると「3-05福利更正費(原)」以外にも「3-07水道高熱費(原)」と「光熱」であるところが「高熱」と誤っているのが見れます。
一括で変換することができる「置換」機能を使います。
置換されたのがわかりやすいように、「水道高熱費」だけを表示するようにしましょう。
⑨「フィルター(Ctrl + Shift +L)」で勘定科目から「3-07水道高熱費(原)」を選択します。
「3-07水道高熱費(原)」のみ抽出されました。
⑩「置換(Ctrl + H)」で「検索と置換」ダイアログボックスが開きます。
検索する文字列に「高熱」
置換後の文字列に「光熱」
と入力し、「すべて置換(Alt + A)」を実行してください。
全ての「高熱」が「光熱」に置換されました。
元のデータを訂正してもピボットテーブルは前のままです。
⑪いずれかの勘定科目を選択した状態で、「右クリックキー or Shift + F10」→「更新(R)」を実行します。
更新をすると訂正した勘定科目は下の方に表示されますので、表示位置を直してやりましょう。
⑫移動したい勘定科目を選択し、セルの境界線にマウスを合わせるとポインターが十字の矢印になりますので、ドラッグ&ドロップで動かします。
「旅費交通費」の詳細な内訳を表示する
詳細情報を確認するために「補助科目名」を下層に追加してみましょう。
⑬「旅費交通費」をダブルクリックすると「詳細データの表示」ダイアログボックスが開きますので、「補助科目名」を選択して「OK」で実行してください。(マウスを使ってください)
「補助科目名」が追加され、勘定科目名の左に「+-」ボタンが表示されました。
ちなみに行ラベルボックスに「補助科目名」が追加されています。
⑭「補助科目名」を削除して「摘要」を追加する
「補助科目名」のどれか(説明ではガソリン代)を選択した状態で、「右クリックキー or (Shift + F10)」→「"補助科目名"の削除(V)」を実行します。
「補助科目名」を追加したのと同じように「摘要」を追加します。
「福利厚生費」を展開させてください。
「健康診断料」の中に「健康診断料会社負担分」が混じっていますので、訂正しましょう。
訂正するにはシート「伝票データ」のデータを直す必要があります。
方法としては、「検索」「フィルタ」「置換」で行う方法などありますが、今回はフィルハンドル機能でデータにID番号を追加して確認する方法でやります。
⑮フィルハンドル機能を使ってID情報を追加する
シート「伝票データ」を選択してください。
A列のいずれかのセルを選択した状態で「セルの挿入(Ctrl + Shift + +(プラス)」を実行し、「列全体(C)」を実行します。
A列が挿入されました。
⑯セルA1に「ID」セルA2に「1」セルA3に「2」と記入してください。
セルA2とA3を選択した状態で、セルA3の右下にマウスポインタを合わせ、ポインタが「+」の状態でダブルクリックします。(マウスを使ってください)
自動的に連続したID番号が追加されました。
ただこの状態ではピボットテーブルのデータとして認識されません。
データソースを変更する必要がります。
「月度」の上に「ID」が表示されるようにする必要があります。
⑰「分析(Alt + JT)」→「データソースの変更(I)」→「データソースの変更(D)」を実行する
※Excel2010では「分析」ではなく、「オプション」
シート「伝票データ」に切り替わります。
データ/範囲で選択されているデータ範囲は[伝票データ!$B$1:$N$1033]となっており、A列は範囲指定されていません。
⑱データ範囲にA列を加えるためにBをAに変更します。(マウスを使ってください)
変更後範囲[伝票データ!$A$1:$N$1033]
IDが追加されました。
⑲「摘要」の健康診断料会社負担分の下層に「ID」を追加する。
健康診断料会社負担分」の下にID情報「750」が追加されました。
⑳目的のデータをシート「伝票データ」から探す
「フィルター(Ctrl + Shift + L)」を使ってIDから「750」を選択します。
「健康診断料会社負担分」となっているの「F2」を使って訂正しましょう。
「左シートに移動する(Ctrl + Pageup)」でシート「Sheet1」に移動し、ピボットテーブル内を選択している状態で「右クリックキー or Shift + F10」→「更新(R)」を実行します。
「健康診断料会社負担分」がなくなり「健康診断料」になりました。