【Powershell】Excel関数をつかう

SUMやSUBTOTAL関数を使う

powershellではExcelで使える関数がほぼそのまま使用できます。
意外と使い方の説明がないと思ったので書いておきます。

・SUM関数
さっそくSUMの使い方です。
関数の中で関数を使う場合ちょっと工夫がいります。

#excelを宣言しているとして
#セル1,6にSUM関数を含むIF関数を設定
for($index = 1; $index -le 100; $index++) {
  $excelSheet.Cells.Item(1,6) = "=IF(C$index="""","""",SUM(H${index}:O${index}))"   
}

ポイントはSUM関数の中の変数の宣言です。
$変数でそのまま宣言しても見てくれないので、中かっこ{}で囲みます。
中かっこで囲むことで、変数であることを宣言しています。
H$(index)とすることで、indexがインクリメントされていきH列を対象にできます。

・SUBTOTAL
つづいてはフィルタをかけたときの件数をしるために
SUBTOTALを使う場合です。

#hensuでフィルタをかける
$excelSheet.Range("A4:X4").AutoFilter(2, $hensu)
#フィルタをかけた件数を取得
$filtaAllCount = $excel.WorksheetFunction.Subtotal(3,$excelFile1.ActiveSheet.Range("A:A"))
#フィルタのヘッダの件数だけ減らす
$filtaCount = $filtaAllCount - 2

AutoFilterの使い方はVBAと同じですね。
ここでは$hensuでフィルタをかけています。
フィルタをかける範囲はRangeで指定してこの場合A4からX4までを指定しています。
Subtotalを使う場合、WorkSheetFunctionをかませて使います。
ここではアクティブなシートのA列すべてを指定しています。

そしてsubtotalではフィルタをかけた列もカウントされてしまうので
その分マイナスしています。

VBAとは若干使い方がちがうので注意してください。