Googleスプレッドシートで大量のデータの中から条件に合うデータを抽出したいときには、filter関数を使えば抽出できます。
しかし、複数条件でフィルターをかけたい場合に、filter関数式内で複数条件を指定することはできず、個々の条件を指定したfilter関数の式を+でつなげる必要があります。
少ない条件であれば+でつなげて対応できますが、条件が多い場合には式を書くのに時間も手間もかかりますし、メンテナンスもしにくくなります。
そこで、複数条件でフィルターをかけてデータを抽出するGASを作成しました。
ボタンひとつで実行できて、セル参照で条件を指定できるのでメンテナンスも簡単です。
使用例
全データには1000件のダミーデータが入っています。
このダミーデータから、都道府県が「和歌山県」「兵庫県」「愛知県」「大阪府」のデータだけを抽出します。
抽出条件のシートには、上記4つの都道府県が記入されています。
カスタムメニューのフィルターボタンを押すと、指定の都道府県のデータだけを抽出できました。
コピペ用コード
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
// 全データの取得
const allDataSheet = spreadSheet.getSheetByName('全データ');
const allDataValues = allDataSheet.getRange(2,1,allDataSheet.getLastRow()-1,allDataSheet.getLastColumn()).getValues();
// 抽出条件の取得
const keyValuesSheet = spreadSheet.getSheetByName('抽出条件');
const keyValues = keyValuesSheet.getRange('A2:A').getValues().filter(String).flat();
//出力先シートの取得
const filteredDataSheet = spreadSheet.getSheetByName('抽出データ');
//フィルターをかけたデータを格納する配列を準備
const filteredData = [];
//フィルターの処理
function filterData() {
for (i = 0; i < allDataValues.length; i++) {
if (keyValues.includes(allDataValues[i][2])) {
filteredData.push(allDataValues[i]);
}
}
filteredDataSheet.getRange(2, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
}使い方
シートの準備
使用例の動画の通り、3つのシートを準備します。
1つ目は「抽出データ」という名前のシートで、フィルターをかけたデータを書き出すシートです。
2つめは「全データ」という名前のシートで、フィルターをかけたい元データを貼り付けます。
3つ目は「抽出条件」という名前のシートで、A列にフィルターをかける条件を記入します。
各シートで1行目のデータは処理の対象にならないので、任意の名称で見出しを記入してください。
コードの編集
データの様式が違う場合、1箇所だけコードの修正が必要になります。
コピペ用コードの20行目、allDataValues[i][2]の数字部分です。
使用例の動画では、フィルターをかけたい都道府県の列が3行目にあるので、2を指定しています。
フィルターをかけたい列が1行目の場合は0を指定し、allDataValues[i][0]にします。
フィルターをかけたい列が5行目の場合は4を指定し、allDataValues[i][4]にします。
※プログラミングの世界では数字を0から数えるので、「列番号-1」になります。
ここまで準備ができたら、スクリプトを実行するだけです。
使用例の動画内ではカスタムメニューに実行ボタンを設置しています。
カスタムメニューに実行ボタンを設置すれば、GASがわからない人でも操作できるので便利です。
まとめ
今回はセル参照で複数条件の指定できるフィルターの作り方を紹介しました。
僕は仕事でSIMカードの請求データを毎月作成しているのですが、5万件ほどの通話履歴の中から100回線の番号の通話履歴だけを抽出する必要があり、スプレッドシートのfilter関数では対応できなかったので、この機能を作成することにしました。
ボタンひとつで抽出できて、抽出条件の変更も簡単なので、時間も手間も劇的に減りました。
同じような業務に悩んでいる方がいたら、ぜひこの機能を導入してみてください！
今回の記事はいつもより難易度が高いと思いますので、使い方がわからない方がいれば、Twitterかお問合せからお気軽にご連絡ください。
サイト下部のTwitterボタン・Contactボタンからお問合せいただけます。