プログラミング

【Powershell】powershellでExcelの関数をつかう

※はてブより引っ越しました。

前回powershellでExcelを操作する方法を書きました。

Excelを操作しているときに「関数」の使い方が分からなかったので調べてみました!

Excelの関数を使う

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

基本的なcomオブジェクトの使い方はこちら⇒【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を使う場合、ExcelオブジェクトにWorkSheetFunctionをかませなくてはいけません。
RangeはアクティブなシートのA列すべてを指定しています。

「subtotal」ではフィルタをかけた列もカウントされてしまうのでその分マイナスします。
VBAとは若干使い方がちがうので注意ですね。

まとめ

基本的にExcelで使える関数は大体使用できます。

ただし使い方にひと工夫必要なのでTry & Errorでネバギブ♪

RECOMMEND

1

新卒で入社したけど『もう仕事を辞めたい』『我慢できない』という方に向けて。新卒入社でカフェのフロアスタッフとしてして働いたけれど、約1年で退職した私からアドバイスです。心がだめになるまで続ける必要なんてありません。

2

女性はライフステージによって休職や転職しなければいけないときがありますよね。
そこでこの記事では女性の転職に有利な資格をまとめてみました。
一生職に困らない、転職に有利な資格とはなにかご覧ください。

3

「今日は会社に行く気がしない・・。」「たまには会社をさぼりたい・・。」なんてことありませんか?本記事では、実際に私の周りで使われていた欠勤理由をご紹介します。今から使える欠勤理由です。本当に仕事に行きたくないときなど活用できると思うので、ぜひ読んでみてください。

-プログラミング

Copyright© Job search info , 2020 All Rights Reserved.