エクセル初級者・関数が苦手な方でもカンタン!ピボットテーブルの使い方

財務経理部の皆様は、各種データの集計を行う場面に直面することも多いかと思います。
そこで、今回は、エクセル初級者・関数が苦手な方でもカンタンに使えるピボットテーブルをご紹介いたします。
ピボットテーブルは、エクセルの機能のひとつです。関数を勉強している暇はない、でも、集計したい分析したい確認したいという場合に便利な機能です。

むずかしい関数を使わずに、いろんな集計が可能ですし、マウスドラッグで、視覚的に配置していけるのも初級者向けだと思います。

ただ、データがきちんと揃っている必要があるなどのルールがあります。たとえばピボットテーブルでどんなことができるのかとあわせて、最低限押さえたいルールをご紹介します。

支払管理表をピボットテーブルで加工する

よくピボットテーブルの説明に使われているのは、各店売上の集計表を操作したものですが、もちろん他にもいろいろな表の集計が可能です。

たとえば画像のような支払管理表。支払期限が前後していたり、振込先もバラバラです。

支払日や支払額を確認するために、期限で並べ替えして、振込先でフィルターをかけて…といろいろな方法があります。

ここでは、ひとめで、何日までに、どの口座に、いくらぐらいの残高が必要なのか、という情報がわかるように、ピボットテーブルで加工してみましょう。

先に、目指す形をお見せすると、右側の画像のようになります。

何日までに、どの銀行に、いくら(概算)必要か、ひとめで判る表になりましたね。

ピボットテーブルを作るために欠かせないこと

早速作ってみましょう、といきたいところですが、最初の表には足りない部分があるので、そこに補足していきます。

補足後の表が以下です。

変更したのは以下の点。
〇 セルの結合の解除
〇 空欄となっているセルに入力

ピボットテーブルは、一行を1データとして集計していきます。

エクセルは、人間のように「空白=同上」と判断できないので、結合を外し空白を埋めることで、一行のみを見るだけで何のデータか判るようにします。そのため、例えば上記の表のように「仕入」の下の空欄は、同じように「仕入」が続く場合には、空欄のままとせず、「仕入」を入力しておきます。

(なお、この表の作成日は3/16頃、支払日・支払金額は決定してからの入力という設定ですので、まだ空白が残ります)

セル結合の一括解除や、同じ意味の上のセルから空白セルへの一括入力については、また次回以降にご紹介できればと思います。

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

ピボットテーブル作成の操作方法については、様々なサイトで紹介されているので、詳しくはそちらをご覧ください。おおまかに説明しますと、メニューの挿入から、ピボットテーブルをクリックすると設定画面が現れます。

ここで指定する必要があるのは、2点。

1点目は元データ。挿入前に範囲指定していれば、すでに表示されているはずです。この画面で範囲を指定してももちろん大丈夫。

2点目が出力先。元データが小さいなら、そのまま同じシートに並べて、比較しながら、いろいろ試されるとよいかと思います。大きい表にたくさんのデータが入った元データの場合は、新規ワークシートにしましょう。

ひとまずその2点を指定したらOKを押します。

ピボットテーブルのフィールド設定

ピボットテーブルのフィールド設定画面が右に表示されます。

上部に元データの表の、範囲指定した最上行にあった見出しが並んで表示されます。見出しが空欄だと表示されず、集計もできません。

下部の十字は出力する表の、行と列に見立てます。

上部の項目を下部の十字のなかにドラッグして、それぞれ割り振っていきます。

 

先ほどの、銀行ごと日ごとの必要残高を表示させるには、以下右のような配置にドラッグします。

すると、出力先の表も以下左のように変わっていきます。

 

 

元データとなった表から様々な集計が可能

銀行別・期限日別の集計の他にも、この元データから集計できるものをいくつかご紹介します。

たとえば、実際の支払額が決定して支払いも済んだら、今度は概算額と支払額を入れ替えて、列を発生月、行を相手先にすると、月の実際にかかった費用がわかります。

 

 

 

 

もちろん店ごとにかかった費用も集計できます。

1年分のデータがあれば、月ごとの比較も可能です。

元データの表に何かあらたに入力したら、メニューのピボットテーブル→分析で「更新」すると出力先の表に反映されます。

ピボットテーブルで重複のチェックも

重複の発見は、並べ替えや他のエクセルの機能でも可能ですが、ピボットテーブルでも同じようなことができます。

たとえば社名・団体名の場合、㈱などが有るか無いか、あるいは半角全角の違いなどでも集計がばらけてしまいます。ピボットテーブルもこれらを区別して集計し、並べ替えてくれるので、ある程度の重複を発見することが可能です。


いかがでしたか。ピボットテーブルは初級者向きと、はじめにお話した意味が少しご理解いただけたでしょうか。

ピボットテーブルはグラフ作成や、より難しい集計・分析もできるようですが、カンタンに利用しようと思えば、この程度でも充分便利な機能かと思います。

今後もできるだけカンタンで、覚えやすい方法をお伝えし、それが皆さんの作業の効率化につながれば幸いです。

(書き手はExcel 2013 を使用しています)

関連記事

  1. 消費税の軽減税率制度導入により変更となる事務処理②
  2. コロナウィルスの影響で減額した役員給与を元の水準に戻した場合の定…
  3. 文字列を数値へ変換して、計算や加工可能なExcelデータへ修正す…
  4. インボイス制度 短期前払費用の取り扱い
  5. 消費税の軽減税率対象商品について③
  6. 収益認識に関する会計基準と遡及適用
  7. 所得拡大促進税制 適用判定時の在宅勤務手当等の取り扱いについて
  8. Excel関数で特殊な日付形式を変換する:ChatGPTと共に計…

最近の記事

PAGE TOP