PC関連

Excelで簡単集計・クレジットカード等の利用金額を部署別に集計したい

クレジットカードの利用金額を部署別や工事番号別等に集計するには、ピボットテーブルで簡単にできます。

利用明細の件数が多い場合は、ここで説明する方法が早いです。

 

実際にあった集計方法の例

実際にあった集計方法に近い例です。

  • 集計結果の利用目的は経理の仕訳で、「工事番号別のETC利用金額を計上する」でした。
  • 利用明細の件数は、毎月約1,000件。

今までのやり方の説明を受け、かなり驚いた方法でした。

旧作業方法

(1)郵送で送られてくるクレジットカードの利用明細の右端に、手書きで工事番号を書きます。同じ工事番号が続いている場合は、手書きで合計金額を書きます。

エクセルでETCの利用明細を部署別に集計

(2)クレジット利用明細に、工事番号ごとの合計金額を入力します。入力は明細を見ながら、「+」で入力していきます。

エクセルでETCの利用明細を部署別に集計

上記の例のように、件数が少なければ簡単かもしれません。件数は最初に記載した通り、約1,000件。気が遠くなる計算式です。途中で間違えた場合は、どこで間違えたのかチェックするのも大変です。

はじめに説明を受け、実際やる前に挫折しました。私は基本的に今までのやり方を一度はやってみます。大変そうにみえて、実はそのやり方に意味があったりするから。ただ、この件に関しては最初から違う方法をやらせていただきました。

 

利用明細データを利用し、ピボットテーブルで簡単に集計する方法

最初からあるフォーマット(クレジット利用明細)に、集計結果を反映させる方法です。

(1)クレジットカード利用明細のCSVデータを入手します。

クレジットカード利用明細は、知っている限り、PDFデータやCSVデータ、またはその両方をクレジット会社の会員サイトからダウンロードできます。

 

(2)利用明細のデータに工事番号欄を追加します。同じ工事番号が続いている場合は、空欄にせず工事番号を入力します。

エクセルでETCの利用明細を部署別に集計

(3)利用明細データ内をアクティブに(選択)します。ここでの例では、セルA1等データの範囲内をクリックします。

(4)リボン > テーブル >ピボットテーブル を選択します。

Excel(エクセル)ピボットテーブルで部署別の集計を簡単にする

(5)選択されている範囲を確認します。範囲は点線で囲まれます。

エクセル・ピボットテーブルで集計

(6)「ピボットテーブルレポートを配置する場所を選択」で、「既存のワークシート」を選択します。

※新規ワークシートでも可能です。シートを増やしたくない等の場合は、この方法を使います。

エクセル・ピボットテーブルで集計

(7)ピボットテーブルを配置したい場所を選択します。

エクセル・ピボットテーブルで集計

(8)ピボットテーブルのフィールドより、集計する項目をボックスにドラックして設定します。

  • 行:工事番号
  • 値:利用金額

エクセル・ピボットテーブルで集計

結果

エクセル・ピボットテーブルで集計

(9)ピボットテーブルの集計結果を最初からあるフォーマット(クレジット利用明細)に値のみ貼り付けます。

ピボットテーブルの集計結果をコピーします。

エクセル・ピボットテーブルで集計

値のみ選択します。

エクセル・ピボットテーブルで集計

結果

エクセル・ピボットテーブルで集計

 

-PC関連
-

© 2020 バイクOBA3