Excelメニュー Excelのピボットテーブル

フィルターや置換機能でピボットテーブルのデータを修正

更新日:

ピボットテーブルとショートカットキーを効率的に学ぶ講座の④です。
ピボットテーブルの集計の元となるデータを変更する方法を紹介していきましょう。

よかったらフォローしてください。→ハヤトの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)」を実行します。

Excel81-03

更新をすると訂正した勘定科目は下の方に表示されますので、表示位置を直してやりましょう。

Excel81-01

⑫移動したい勘定科目を選択し、セルの境界線にマウスを合わせるとポインターが十字の矢印になりますので、ドラッグ&ドロップで動かします。

Excel81-02

 

「旅費交通費」の詳細な内訳を表示する
詳細情報を確認するために「補助科目名」を下層に追加してみましょう。

⑬「旅費交通費」をダブルクリックすると「詳細データの表示」ダイアログボックスが開きますので、「補助科目名」を選択して「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)」を実行します。

「健康診断料会社負担分」がなくなり「健康診断料」になりました。

次へ→ピボットテーブルの集計や表示を変更して損益計算書をカスタマイズする

03投稿記事下_関連コンテンツ

-Excelメニュー, Excelのピボットテーブル
-, , ,

Copyright© 経理と総務の効率化 , 2024 All Rights Reserved.