PowerShellを使ってクロス集計をしてみる

PowerShellを使ってExcelのピボットテーブル様な表を作ってみます。具体的には下図のように個別原価計算の明細表(原価明細)が与えられ、それをプロジェクトコード毎に集計します。

原価明細には原価の内訳となる費目、プロジェクトコード、金額、摘要の4項目があるとします。集計結果はプロジェクトコード毎に各原価の内訳とそれらの合計額を表示します。

ピボットテーブルのフィールドリストは次の通りです。

PowerShellでの集計

事前準備

データがExcelだとPowerShellでは扱いにくいのでCSVファイルにしてください。実務の場合、システム部門からCSV形式でファイルをもらうことが多いと思いますので、その場合はそのまま利用しましょう。

原価明細のCSVデータは次の通りです。CSVファイルの文字コードは、UTF-8で保存しないと文字化けを起こすので注意してください。

pivot.csv
type, PJcode, amount, description
外注費, A001, “663,000”, X社外注費
外注費, A001, “918,000”, Y社外注費
外注費, A002, “808,000”, Y社外注費
外注費, A002, “544,000”, Z社外注費
加工費, A001, “449,600”, Aさん
加工費, A002, “445,000”, Aさん
加工費, A002, “877,800”, Bさん
加工費, A003, “943,000”, Cさん

項目名はPowerShell内で扱いやすいようにアルファベットにしました。

ソースコード

PowerShellの対話モードでクロス集計に挑戦しましたが、無理そうだったので無難にスクリプトを書いて対応することにしました。コードは次の通りです。

$data = Import-Csv ".\pivot.csv"
# (配列)ユニークなプロジェクトコードを保存
$uPJcode = $data | group PJcode | select Name
# (配列)ユニークなタイプ(外注費や加工費など)を保存
$utype = $data | group type | select Name
$result = @()

# 最終表示項目(PJコード、原価内訳、合計)を持つオブジェクトを作成する関数
function getObject {
    $obj = New-Object -TypeName PSObject
    $obj | Add-Member -MemberType NoteProperty -Name pjcode `
                                                                                   -Value $result[0].pjcode
    # オブジェクトのメンバーにタイプ(外注費や加工費)を加える
    foreach($t in $utype) {
        $obj | Add-Member -MemberType NoteProperty -Name $t.Name -Value 0
    }
    $obj | Add-Member -MemberType NoteProperty -Name total -Value 0
    return $obj
}

# PJcode別、type別に金額を集計するための入れ物を作る
foreach($pj in $uPJcode) {
    foreach($ty in $utype) {
        $obj = New-Object psobject | Select-Object pjcode, type, amount
        $obj.pjcode = $pj.Name
        $obj.type = $ty.Name
        $obj.amount = 0
        $result += $obj
    }
}

# PJcode別、type別に金額を集計する
foreach($d in $data) {
    foreach($r in $result) {
        if( ($d.PJcode -eq $r.pjcode) -and ($d.type -eq $r.type) ) {
            $r.amount += $d.amount
            break
        }
    }
}

# pjcode順に$resultを並び変える
$result = $result | Sort-Object {$_.pjcode}

# プロジェクトコード別の原価内訳金額を集計
$obj = getObject
$costTable = @()
foreach($r in $result) {
    if($r.pjcode -ne $obj.pjcode) {
        $costTable += $obj
        $obj = getObject
        $obj.pjcode = $r.pjcode
    }
    foreach($t in $utype) {
        if($r.type -eq $t.Name) {
            $obj.($t.Name) = $r.amount
        }
    }
}
$costTable += $obj

# プロジェクトコード別の原価合計を算出
foreach($cT in $costTable) {
    $sum = 0
    foreach($t in $utype) {
        $sum += $cT.($t.Name)
    }
    $cT.total = $sum
}

# 最終結果を表示
$costTable | Out-GridView

実行すると次の画面が出てきます。

最初に見たExcelでのピボットテーブルと同じ結果を得られました。

コードの説明

前半では、プロジェクトコード別に外注費と加工費の金額を集計し、 $result にその結果を保存しています。最終値には次の通りです。

後半では、プロジェクトコード、外注費、加工費、合計の項目を持つ独自オブジェクトを作成(getObject関数)して、$result のデータを独自オブジェクトに集約させています。独自オブジェクトは、配列$costTable に加えられていきます。

今回作成したスクリプトはプロジェクトコードの数や原価の種類に依存しません。従って、材料費や労務費といった費目が出てきた場合でも、最終結果の項目が増えた形で結果を表示してくれます。

まとめ

Excelの機能を使えば数秒で完了してしまう処理を、わざわざプログラムを組んでまで行う意味はあまりないかもしれません。データ量が極端に多くても、年に数回しかしない処理ならば時間はかかってもExcelで強引に集計した方が良いでしょう。月次決算で極端に時間のない中、ボタン一つで結果まですべて計算してほしい場合には今回のようなプログラムの意義が出てきます。

処理の最後にOut-GridViewで結果を出力しましたが、PowerShell内データのままでさらに処理を進めることが可能です。ここからプロジェクトコード毎に完了しているか確認を行い、完了したプロジェクトは売上原価への計上、未完了のものは仕掛に残す処理につなげていけば、さらに手作業を減らすことができるでしょう。そこら辺の処理については今後書いていきたいと思います。