Excelのクロス集計表をデータ形式にする［列のピボット解除］（PowerQuery・取得と変換）・マクロ

Excelでクロス集計表を入力してしまうと、その後の加工がやりにくくなります。
クロス集計表を、加工しやすいデータ形式にする方法をまとめてみました。

Excelのクロス集計表のデメリット

Excelで集計表を入力してつくってしまうと、それを加工することができません。
別の形式で集計したいときや会計ソフトに取り込みたいときなどに困ります。

こういった経費の集計表もこのままだと加工しにくいものです。

紙の時代の名残で、こういった表をExcelでつくらないようにしましょう。
経費のデータを科目ごとに集計して合計で会計ソフトに入れるという方法もありますが、それだと会計ソフト上で、細かいデータを見ることができず、分析もすることができません。

こういったデータ形式であれば、Excel上での集計もかんたんですし、会計ソフトに取り組むこともできます。

また、こういった売上の集計表をつくっていると 。

やっぱりこうしたいなと思ったときにできないわけです。
年別、四半期別や担当者別に集計したいときも、困ります。

理想は最初からこのようなデータ形式で入力するか、集めることです。

データ→ピボットテーブルの活用。Excelは表計算ソフトだけど、表を作ってはいけない。 | EX-IT

Excelではデータ形式のほうが加工しやすいのですが、受け取ったデータが表形式の場合もあるでしょう。
そういった場合に、どう対処するかをまとめてみました。

 

 

Excelのクロス集計表をデータ形式にするピボット列の解除

1つの方法は、 Excel の「データの取得と変換」（Power Query）という機能を使います。
この機能はバージョンによって、名称やメニューが変わりますので気をつけていただければ。
Excelの「取得と変換」は、リボンの［データ］タブにあります。
（ないバージョン、Excel2010、2013では使えません。こちらからダウンロードする必要があります。Download Microsoft Power Query for Excel from Official Microsoft Download Center）

今回の記事は Office 365のExcelで試しました。

まず、該当範囲を選択して（事例の場合は個別の経費データ部分のみ）、表をテーブル（Ctrl＋T →Enter）にします。

Excelのリボンの［データ］から［データの取得と変換］の［テーブルまたは範囲から］をクリックしましょう。

すると、このような Power Query というものが別画面で開きます。

今回は、こういった表を。

こういったデータに変更したいので、

変換したい部分は、消耗品費から旅費交通費の別の部分です。
該当の列を選択して［変換］タブの［列のピボット解除］をクリックしましょう。

一瞬でこのようなデータに変わります。

日付に時刻も入ってしまう場合は、［変換］タブの「データ型」で「日付」を選択しておきましょう。

［ホーム］タブの［閉じて読み込む］を押すと

Power Queryは終了し、Excelに戻り、別シートにデータができあがります。
こうなれば、集計も会計ソフトへの取り込みも楽です。

 

売上のデータも、同じように表からデータに変えることができます。

［ピボット列の解除］は、手軽に、クロス集計表をデータにできるので、使ってみましょう。
ただ、複数のシートや複数のファイルになるとちょっと複雑にはなり、マクロを使ったほうがよい場合もあります。

 

Excelのクロス集計表をデータ形式にするマクロ

Excelのクロス集計表をデータ形式にするマクロを書くなら、プログラムはこのようなものです。

Sub trans()


    Dim c
    
    For Each c In Worksheets("keihi").Range("c2", "h10")
        
    'もしセルが0じゃなかったら
        If c.Value <> 0 Then
        
            
            Worksheets("data").Range("b" & c.row).Value = Worksheets("keihi").Cells(1, c.Column).Value  '借方科目
            Worksheets("data").Range("d" & c.row).Value = c.Value                                       '金額
        
            Worksheets("data").Range("a" & c.row).Value = Worksheets("keihi").Range("a" & c.row).Value  '日付
            Worksheets("data").Range("e" & c.row).Value = Worksheets("keihi").Range("b" & c.row).Value  '内容
            Worksheets("data").Range("c" & c.row).Value = "未払費用"                                    '貸方科目
                    
        
        End If
    
    Next
    
End Sub

 

変換するデータ範囲をシート「keihi」のセルC2からH10と指定し、それぞれに数字が入っているかどうかを判定して、数字が入っていれば、シート「data」に転記します。

たとえば、セルC2から見ていき、C2は2,160があるので転記し、D2は空欄なので転記せず・・・とチェックしていき、

セルD3は313が入っているので、転記するというしくみです。

今回の事例では、セルの範囲をきっちりと指定しましたが、データのある範囲を自動的に選択することも可能です。

経費の科目は、2,160円が入っているときに、1行目で、2,160円が入っているセルの列 、C列の科目を転記します。

日付は A列から、内容はB列から、貸方科目は、たとえば未払費用と固定で入れれば、会計データとして完成するわけです。

マクロのほうが、複数シート、複数ファイルの場合など、応用が利きます。

ただ、大前提としては、データ形式で準備する、または準備していただくというのが原則です。

 

---

■編集後記

昨日は、新Excel本の最終チェック。
今日締切です。
最後の最後まで修正が入れて編集者さんには申し訳なかったのですが、納得できる出来になりました。
あとは編集者さんに委ねます。

■「1日1新」

iPad　TVアプリ

 

■娘(2歳）日記

『おばけだじょ』という本が気に入って、「〇〇だじょ」を連発しています。

「〇ちゃん」だじょとか。