はじめに
進捗管理をエクセルでやるとき、以下のような形にしてしまいがちです。苦しいですね。
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| 年度 | 主担当 | 副担当 | 予定 | 実績 | 進捗率 | ||
| 2020年 | |||||||
| 第一四半期 | |||||||
| 喧嘩 | 猿 | 犬 | 2020/4/10 | 2020/4/8 | 100% | ||
| バーゲン・セール | 猫 | 小判 | 2020/5/15 | 2020/5/15 | 100% | ||
| かけっこ | 亀 | 兎 | 2020/6/20 | 90% | |||
| 第二四半期 | |||||||
| おしゃれ | 豚 | 真珠 | 2020/7/10 | 90% | |||
| 葬式 | 馬 | 念仏 | 2020/8/15 | ||||
| 散歩 | 犬 | 棒 | 2020/9/20 |
このような表は、小規模なうちは比較的分かりやすい形ですが、規模が大きくなってきたり、複数の表を横断的に見る必要がある時に、見落としなど管理不備が発生しやすくなります。機械的に集約できたり抽出できると管理コストは下がるのですが、このままの形式では機械的に扱うことが困難です。
また、エクセル特有の問題として、割と頻繁にフォーマットが変更されたりします。そのため、プロジェクト内外に似たようなフォーマットの「亜種」が散乱していることがあったりします。つまり、ここで言う「機械的」な処理は、かなり柔軟な変更耐性が求められます。
この記事では、このような辛い表を機械的に扱うための手法を提案します。
理想の形
以下のようにすると多少扱いやすくなります。A 列と B 列を下までオートフィル（？）した形です。情報が一行にまとまっています。
「2020年度」しか無い行は要らなくなりましたが、一旦弄らないことにします。このようなデータは PowerShell でフィルタするのは簡単だからです。
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| 年度 | 主担当 | 副担当 | 予定 | 実績 | 進捗率 | ||
| 2020年 | |||||||
| 2020年 | 第一四半期 | ||||||
| 2020年 | 第一四半期 | 喧嘩 | 猿 | 犬 | 2020/4/10 | 2020/4/8 | 100% |
| 2020年 | 第一四半期 | バーゲン・セール | 猫 | 小判 | 2020/5/15 | 2020/5/15 | 100% |
| 2020年 | 第一四半期 | かけっこ | 亀 | 兎 | 2020/6/20 | 90% | |
| 2020年 | 第二四半期 | ||||||
| 2020年 | 第二四半期 | おしゃれ | 豚 | 真珠 | 2020/7/10 | 90% | |
| 2020年 | 第二四半期 | 葬式 | 馬 | 念仏 | 2020/8/15 | ||
| 2020年 | 第二四半期 | 散歩 | 犬 | 棒 | 2020/9/20 |
エクセルファイルのデータをPowerShellに取り込む
では、早速エクセルのデータを取り込んでいきます。以下のようにします。
Import-XlFile -path hoge.xlsx | set xlsx -passthru
Name Value
---- -----
Sheet1 {@{A=年度; D=主担当; E=副担当; F=予定; G=実績; H=進捗率}, @{A=2020年}, @{B=第一四半期}, @{C=喧嘩; D=猿; E=犬; F=43931; G=43929; H=1}...}
import-xlfile については以下の記事で書いてます。
エクセルファイルのデータをPowerShellに取り込む - Qiita
$xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | ft
A B C D E F G H I J
- - - - - - - - - -
年度 主担当 副担当 予定 実績 進捗率
2020年
第一四半期
喧嘩 猿 犬 2020/04/10 0:00:00 2020/04/08 0:00:00 1899/12/31 0:00:00
バーゲン・セール 猫 小判 2020/05/15 0:00:00 2020/05/15 0:00:00 1899/12/31 0:00:00
かけっこ 亀 兎 2020/06/20 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
第二四半期 1900/01/01 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
おしゃれ 豚 真珠 2020/07/10 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
葬式 馬 念仏 2020/08/15 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
散歩 犬 棒 2020/09/20 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
new-xlcolumnrange については以下の記事で書いてます。
エクセルのアドレス形式A:Eを入力するとき列名A,B,C,D,Eを出力する - Qiita
1行目を見出しに昇格する
列名がアルファベットになっているとアレなので以下のようにします。
$xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | Convert-XlHeader | ft
年度 C1 C2 主担当 副担当 予定 実績 進捗率 C3 C4
---- -- -- ------ ------ ---- ---- ------ -- --
2020年
第一四半期
喧嘩 猿 犬 2020/04/10 0:00:00 2020/04/08 0:00:00 1899/12/31 0:00:00
バーゲン・セール 猫 小判 2020/05/15 0:00:00 2020/05/15 0:00:00 1899/12/31 0:00:00
かけっこ 亀 兎 2020/06/20 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
第二四半期 1900/01/01 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
おしゃれ 豚 真珠 2020/07/10 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
葬式 馬 念仏 2020/08/15 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
散歩 犬 棒 2020/09/20 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
Convert-XlHeader は以下のような処理です。
Convert-XlHeader.ps1
Function Convert-XlHeader {
Param(
[Parameter(Mandatory=$True, ValueFrompipeline=$True)]
[psobject[]]$SheetData
)
begin {
$ErrorActionPreference = "Stop"
$index = 1
}
process {
$SheetData | Foreach-Object {
if (-not $Header) {
$Header = $_ | Select-Object *
return
}
$o = new-object psobject
$row = $_
$row.psobject.properties | Foreach-Object {
$n = $_.Name
if (-not $Header.$n) {
$Header | Add-Member -Force -MemberType NoteProperty -Name $n -Value "C$index"
$index++
}
$o | Add-Member -MemberType NoteProperty -Name $header.$n -Value $row.$n
}
$o # output
}
}
}
列を埋める
というわけで、列を埋めます。
$xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | Convert-XlHeader | Expand-XlHierarchicalData -Column "年度",C1 | ft
年度 C1 C2 主担当 副担当 予定 実績 進捗率 C3 C4
---- -- -- ------ ------ ---- ---- ------ -- --
2020年
2020年 第一四半期
2020年 第一四半期 喧嘩 猿 犬 2020/04/10 0:00:00 2020/04/08 0:00:00 1899/12/31 0:00:00
2020年 第一四半期 バーゲン・セール 猫 小判 2020/05/15 0:00:00 2020/05/15 0:00:00 1899/12/31 0:00:00
2020年 第一四半期 かけっこ 亀 兎 2020/06/20 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
2020年 第二四半期 1900/01/01 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
2020年 第二四半期 おしゃれ 豚 真珠 2020/07/10 0:00:00 1900/01/01 0:00:00 1900/01/01 21:36:00
2020年 第二四半期 葬式 馬 念仏 2020/08/15 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
2020年 第二四半期 散歩 犬 棒 2020/09/20 0:00:00 1900/01/01 0:00:00 1900/01/01 0:00:00
Expand-XlHierarchicalData については以下の通り。名前が直訳すぎて恥ずかしいけど面倒なのでそのまま。
Expand-XlHierarchicalData.ps1
Function Expand-XlHierarchicalData {
Param(
[Parameter(Mandatory=$True, ValueFromPipeline=$True)]
[psobject[]]$SheetData,
[Parameter(Mandatory=$False)]
[string[]]$Column
)
begin {
$ErrorActionPreference = "Stop"
$o = $null
$add = {
$name = $_.Name
if ($o.psobject.properties.name -contains $name) {
$o.$name = $_.value
} else {
$o | Add-Member -MemberType NoteProperty -Name $name -Value $_.value
}
}
}
process {
foreach($data in $SheetData) {
$Data | Foreach-Object {
# initialize
if ($null -eq $o) {
$o = $_ | Select-Object *
}
if ($null -eq $col) {
if ($null -eq $column) {
$col = $_.psobject.properties.name
} else {
$col = $column
}
}
# copy properties(overwrite)
$_.psobject.properties | Where-Object { $_.Name -notin $Col } | ForEach-Object $add
# copy properties(inherit)
$inherit = $true
$_.psobject.properties | Where-Object { $_.Name -in $Col } | Where-Object {
if (-not $inherit) { return $true }
if ($null -eq $_.value) { return $false }
$inherit = $false
return $true
} | ForEach-Object $add
# output / psobject clone
$o | Select-Object *
}
}
}
}
後処理
やりたいことはもう終わりましたが、列を集約する方法も覚書程度にまとめておきます。
$list = $xlsx.Sheet1 | select (new-xlcolumnrange A:Z) | Convert-XlHeader | Expand-XlHierarchicalData -Column 年度,C1
$list | select-object @{N="作業項目"; E={ $_.年度,$_.C1,$_.C2 -join "\" -replace "\\+","\" }},主担当,副担当,予定,実績,進捗率 | ft
作業項目 主担当 副担当 予定 実績 進捗率
-------- ------ ------ ---- ---- ------
2020年\
2020年\第一四半期\
2020年\第一四半期\喧嘩 猿 犬 2020/04/10 0:00:00 2020/04/08 0:00:00 1
2020年\第一四半期\バーゲン・セール 猫 小判 2020/05/15 0:00:00 2020/05/15 0:00:00 1
2020年\第一四半期\かけっこ 亀 兎 2020/06/20 0:00:00 1900/01/01 0:00:00 0.9
2020年\第二四半期\ 1900/01/01 0:00:00 1900/01/01 0:00:00
2020年\第二四半期\おしゃれ 豚 真珠 2020/07/10 0:00:00 1900/01/01 0:00:00 0.9
2020年\第二四半期\葬式 馬 念仏 2020/08/15 0:00:00 1900/01/01 0:00:00
2020年\第二四半期\散歩 犬 棒 2020/09/20 0:00:00 1900/01/01 0:00:00
終わりに
色々なデータに適用して実用性を確認したいと思います。
大事なのは、この後どのように集約するかですが果たして。。。