実務で使えるExcel入門セミナー 【ピボットテーブル】ピボットテーブル&VLOOKUP関数で、思い通りの並び順に変更する

シェアする

  • このエントリーをはてなブックマークに追加

実務でExcelを使っていると、作業のほとんどが数値の集計であることに気づくはずです。数値の集計でおすすめする機能がピボットテーブル。こちらの記事(【ピボットテーブル】数値の集計には関数よりもピボットテーブルを優先して使おう)でも紹介していますが、操作が簡単でミスもほとんどなく効率がいいんですよね。ただ、メリットの多いピボットテーブルですが、欠点もあります。今回はそれを克服するための方法を説明します。

”さらっ”とまとめると
・ピボットテーブルの欠点は項目の並び順が思い通りにならないこと

・VLOOKUP関数と組み合わせることで、決まった並び順に変えることができる

・「いつも同じ形」にすることが、読み手にとっても作業者にとってもメリットが多い

スポンサーリンク
google adsense




ピボットテーブルの欠点

「ピボットテーブル」は、このようなリスト形式になっているデータを集計するのに力を発揮する機能です。

スクリーンショット 2017 06 05 9 49 46

集計したい範囲を指定して、「挿入」タブの「ピボットテーブル」をクリック。

スクリーンショット 2017 06 05 9 55 36

「ピボットテーブルの作成」画面で「OK」をクリックすればピボットテーブルが作成されるので、「ピボットテーブルのフィールド」で「集計したい項目の列の見出し」(ここでは「勘定科目(借方)」)と「数値の列の見出し(ここでは「金額(借方)」)」を指定すれば、

スクリーンショット 2017 06 05 10 04 38

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

スクリーンショット 2017 06 05 10 06 55

このように少ない手順で大量のデータを正確に計算できるので、実務でも優先して使いたい機能なんですが、欠点もあります。

それは、項目の並び順が思い通りにならないことです。

もう1度ピボットテーブルを見てもらいたいのですが、「行ラベル」より下の並び順は必ずしも、自分が「こう並べたい」と考えた順番ではないはずです。

スクリーンショット 2017 06 05 10 18 15

確かに、「数値の昇順・降順」「見出しのあいうえお順」「手動(ドラッグして並べ替える)」など並べ替えの手段も用意されてはいますが、それ以外の並び順にしたいばあいもよくあります。

たとえば、今回のように勘定科目を扱う場合なら、決算書と同じ並び順にしたい場合や、分析シートなどで重要性の高い順にならべたい場合などです。

スクリーンショット 2017 06 05 10 30 52

このような並び順は、「昇順・降順」と言ったピボットテーブルで用意されたルールとは別のルールによる並び順です。これをピボットテーブルの中で並び変えようとすると、手動でやるしかありません。

たくさんある項目を手動で並び替えるのは、さすがに効率が悪い。
せっかくピボットテーブルで時間が短縮ができたのに、最後の作業で時間をとっていては意味がありません。

ですが、このピボットテーブルの欠点は克服することが可能です。
VLOOKUP関数を使って転記する方法です。

VLOOKUP関数で定形のフォーマットに転記する

ピボットテーブの項目の並び替えは、ピボットテーブルの中で行うのではなく、正しい並び順で入力した表を先に作成しておいて、そこにVLOOKUP関数で転記するのが効率のよい方法です。

たとえば、先ほどの勘定科目を「決算書と同じ並び順で表示させたい」場合ですが、先に、このような表を作っておきます。

スクリーンショット 2017 06 05 10 39 36

確かに、最初は手動で入力しなければいけないので面倒ですが、1度入力すれば、あとは項目が追加された時に入力すればいいだけなので、ほとんど手間がかかりません。

正しい並び順で表ができれば、あとは、VLOOKUP関数で転記するだけです。

「検索値」を表の左端の列(A列)にある「見出し」にして、

スクリーンショット 2017 06 05 10 50 25

「範囲」を作成したピボットテーブルにします。

スクリーンショット 2017 06 05 10 47 54

「列番号」はピボットテーブルで集計された列なので、この場合は「2」で、「検索の型」を「0」にすると、

スクリーンショット 2017 06 05 10 57 50

これで、ピボットテーブルから数値を転記することができます。

スクリーンショット 2017 06 05 11 02 20

後は、他のセルにVLOOKUP関数をコピー&ペーストすれば表全体が完成します。

スクリーンショット 2017 06 05 11 05 47

ひょっとすると、「いちいちVLOOKUP関数を入力するのが面倒」と感じる方がいるかもしれませんが、VLOOKUP関数もデータが更新されるたびに入力する必要はありません。

なぜなら、ピボットテーブルの配置はいつも決まっているので、VLOOKUP関数の「範囲」新しくデータが更新されたシートのシート名に変更すれば、正しく転記されるからです。

スクリーンショット 2017 06 05 11 14 40

たとえば、「置換」を使って、シート名「Pivot2017」をシート名「Pivot2018」に置き換えるだけで、新しく作成したピボットテーブルのデータを転記することができます。

なので、VLOOKUP関数についても1度フォーマットを作っておくだけで、新しいデータへの更新は簡単にできるのです。

いつも同じ型で資料を作ることが、読み手に効果的に伝わる

「数値の集計が正しくできていれば、見た目はあまり気にしなくても良い」という考え方もあると思います。ピボットテーブルで数値を集計すれば、わざわざ順番を並び替えることはないということですね。

ですが、自分が報告を受ける立場だったとして、いつも並び順が変わるような資料を提出されてどう感じるでしょうか。

いつも提出される資料の場合、ちょっとした配置やフォントの変更、装飾の違いがあるだけで、違和感を感じるはずです。それが、最も重要な数値の部分で並び順が変更されているとしたら。違和感どころではなく、大きなストレスを感じることになるでしょう。

資料を作成する時は、見る人のことを第1に考えるべきです。

「決まった配置」「決まった並び順」「決まった強調の方法」で資料を作成することは、読み手に不要な配慮をさせることなく、安心して内容を読み取ってもらうことにつながります。そうすることで、本当に伝えたいことがストレートに伝わることになるんですね。

なので、資料を作成する時は、最初に「決まった型」を作り上げて、それに沿って資料を作るように心がけましょう。

今回の取り上げた方法も、そのための工夫の1つです。

まとめ

ピボットテーブルの並び順を、自分が意図した通りに変更するには、先に正しい並び順の表を作成しておいて、VLOOKUP関数で転記すれば、効率よく並び替えることができます。

実務で使えるExcel入門セミナー
実務で使えるExcel入門セミナー

経理、税務、財務の実務に特化した『実務で使えるExcel入門セミナー』を開催します。
「近日出荷」を運営する高橋良和が公認会計士・税理士として監査、経理、税務の現場で実際に使ってきたExcelスキルを直接お伝えする講座です。

就職・転職で「Excelなら使えます」とアピールしたい
Excelで同僚に差をつけられたくない
実務レベルで使うExcelのテクニックが知りたい

など教科書では学べない実務の現場で"使える"実践的スキルをお伝えします。

【実務で使えるExcel入門セミナー】
基礎編:10時10分〜 詳細はこちら
応用編:14時〜 詳細はこちら
日程:7/15(土),22(土),29(土), 8/5(土),12(土),19(土),26(土)
場所:東京都千代田区セミナールーム

スポンサーリンク
google adsense




google adsense




シェアする

  • このエントリーをはてなブックマークに追加

フォローする

error: Content is protected !!