【Officeスクリプト】オートフィルターした結果を転記する
こんにちは。aliceです。
本当は60～80℃で緑茶を入れたいのにあまりの寒さに100℃の熱湯で入れてしまいます🍵
濃くてしぶーいお茶も好きです🍵
今日はOfficeスクリプトでオートフィルターをした結果を転記してみます。
このような感じです。
それではやっていきましょう。
オートフィルター
まずはオートフィルターを使ってみます。
こんな表があります。
この中から「さつまいも」をフィルターしてみます。
オートフィルターといえば自動記録ですね✨
さっそく自動記録してみましょう。
「操作を記録」をクリックします。
（今回はExcelオンラインを使っています）
記録中になりました。
記録しながらオートフィルターを使っています。
操作が終わったので停止します。
次のようなスクリプトが記録されました。
見にくいのでこちらをどうぞ。
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Toggle auto filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getRange("A1:C1"));
// Apply values filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 0, { filterOn: ExcelScript.FilterOn.values, values: ["さつまいも"] });
}ちょっと修正しました🐻
function main(workbook: ExcelScript.Workbook) {
const worksheet = workbook.getWorksheet("Sheet1");
const range = worksheet.getUsedRange();
const autoFilter = worksheet.getAutoFilter();
autoFilter.apply(range, 0, {
values: ["さつまいも"],
filterOn: ExcelScript.FilterOn.values
});
}セルの取得まではいつもと同じです。
そのあとフィルターオブジェクトを取得します。
const autoFilter = worksheet.getAutoFilter();
そして、applyメソッドを使ってオートフィルターを実行します。
第1引数が範囲、第2引数がフィルターを適用する列のインデックス番号（この場合はA列なので0）、第3引数がフィルターの内容です。
autoFilter.apply(range, 0, {
values: ["さつまいも"],
filterOn: ExcelScript.FilterOn.values
});
filterOnの値はいろいろあるので、自動記録で確認するのが一番ラクです✨
オートフィルターした結果を転記する
オートフィルターを使えたので、次は転記をしていきましょう。
Sheet1に元データがあります。
Sheet2にオートフィルターした結果を転記します。
転記したらオートフィルターを解除します。
それでは実行します。
無事に実行できました。
コードはこちらです。
function main(workbook: ExcelScript.Workbook) {
const worksheet1 = workbook.getWorksheet("Sheet1");
const range = worksheet1.getUsedRange();
const autoFilter = worksheet1.getAutoFilter();
autoFilter.apply(range, 0, {
values: ["さつまいも"],
filterOn: ExcelScript.FilterOn.values
});
const values = range.getVisibleView().getValues();
const worksheet2 = workbook.getWorksheet("Sheet2");
worksheet2.getRangeByIndexes(0,0,values.length,values[0].length).setValues(values);
autoFilter.clearCriteria();
}
オートフィルターまでは先ほどと同じです。
getVisibleViewメソッドを使って表示されているセルの値を取得します。
const values = range.getVisibleView().getValues();
値の取得が終わったのでclearCriteriaメソッドでオートフィルターを解除します。
autoFilter.clearCriteria();オートフィルターを使ったときはgetVisibleViewメソッドを使うと良い感じに転記できそうです。
filterメソッドを使って転記する
せっかくなのでオートフィルターを使わずにfilterメソッドを使ってみます。
function main(workbook: ExcelScript.Workbook) {
const sheet1 = workbook.getWorksheet("Sheet1");
const [headers, ...records] = sheet1.getUsedRange().getValues();
const values = records.filter(record => record[0] === "さつまいも");
values.unshift(headers);
const Sheet2 = workbook.getWorksheet("Sheet2");
Sheet2.getRangeByIndexes(0, 0, values.length, values[0].length).setValues(values);
}
いろいろなやり方がありますね。
おまけ（オートフィルターいろいろ）
せっかくなのでいろいろ遊んでみました。
複数の文字でフィルター
function main(workbook: ExcelScript.Workbook) {
const worksheet = workbook.getWorksheet("Sheet1");
const range = worksheet.getUsedRange();
const autoFilter = worksheet.getAutoFilter();
autoFilter.apply(range, 1, {
values: ["あんのういも","きたあかり"],
filterOn: ExcelScript.FilterOn.values
});
}
valuesが配列なので複数の文字が入ります。
数値（○○以上）
function main(workbook: ExcelScript.Workbook) {
const worksheet = workbook.getWorksheet("Sheet1");
const range = worksheet.getUsedRange();
const autoFilter = worksheet.getAutoFilter();
autoFilter.apply(range, 2, {
criterion1: ">=400",
filterOn: ExcelScript.FilterOn.custom
});
}今回は400円以上でフィルターをかけてみました。
filterOnの値が文字列のときと違います。
複数条件
function main(workbook: ExcelScript.Workbook) {
const worksheet = workbook.getWorksheet("Sheet1");
const range = worksheet.getUsedRange();
const autoFilter = worksheet.getAutoFilter();
autoFilter.apply(range, 0, {
values: ["さつまいも"],
filterOn: ExcelScript.FilterOn.values
});
autoFilter.apply(range, 2, {
criterion1: ">=400",
filterOn: ExcelScript.FilterOn.custom
});
}組み合わせパターンです。
オートフィルター、奥が深いですね。
参考
🐻オートフィルター（ExcelScript.AutoFilter interface）
🐻フィルター条件（ExcelScript.FilterCriteria interface）
🐻FilterOn（ExcelScript.FilterOn enum）
🐻（getVisibleViewメソッド）
Officeスクリプト記事まとめ📝
『Officeスクリプト入門講座』を書きました📝