@kunio_fujigasaki (Kunio Fujigasaki)posted at 2022-08-22

Spreadsheet上で特定IDをフィルターの条件にした抽出方法

Q&A

Closed

GoogleSpreadSheet

解決したいこと

Googleスプレッドシートで、クエリやフィルターを使おうとしたのですが解決しないので投稿させていただきます。

複数の特定のIDをフィルターの条件にして、全リストの中から抽出する方法になります。

sheet1にある複数の特定のIDを下記とします。
注文ID
1111
1113
1116

またsheet2に、商品データ元として下記のテーブルがあるとします。
注文ID 商品ID 商品名
1111 2345 りんご
1111 2346 りんご
1111 2347 りんご
1111 2348 りんご
1112 3334 みかん
1112 3335 みかん
1112 3336 みかん
1112 3337 みかん
1112 3338 みかん
1112 3339 みかん
1113 4111 レモン
1113 4112 レモン
1113 4113 レモン
1113 4114 レモン
1114 6111 すいか
1114 6112 すいか
1114 6113 すいか
1114 6114 すいか
1114 6115 すいか
1115 7111 なつめ
1115 7112 なつめ
1115 7113 なつめ
1115 7114 なつめ
1115 7115 なつめ
1115 7116 なつめ
1116 8111 バナナ
1116 8112 バナナ
1116 8113 バナナ
1116 8114 バナナ
1116 8115 バナナ

質問としては、sheet3にsheet1のリストに該当する注文IDのみをsheet2から抽出した結果の下記リストを自動出力するような関数を作りたいです（商品名は下記どおり重複でよい）

注文ID 商品ID 商品名
1111 2345 りんご
1111 2346 りんご
1111 2347 りんご
1111 2348 りんご
1113 4111 レモン
1113 4112 レモン
1113 4113 レモン
1113 4114 レモン
1116 8111 バナナ
1116 8112 バナナ
1116 8113 バナナ
1116 8114 バナナ
1116 8115 バナナ

vlookup, filter, query関数などで簡単にできる気がするのですが、恥ずかしながら方法が思いつきません。

クエリ―をアナログに書くなら
SELECT * where A列 = '1111' or A列 ='1113' or A列 ='1116'
のようになりますが、これを変数や特定セルを呼び出してきれいに自動でまとめる方法はあるのでしょうか。

お教えくだされば嬉しいです。

1Answer

@Mt_SQ posted at 2022-08-23

おそらく、期待されている回答ではないかもしれませんが、ご容赦ください。

GoogleスプレッドシートのQUERY関数で使用できるクエリ言語（Google Visualization API のクエリ言語）では、複数のWHEREに対応するためのIN句がサポートされていないようです。

このあたりは、代替案も含めて、次のブログが詳しいです。
(https://www.officeisyours.com/entry/2019/12/08/013750)

質問者の期待する解決方法ではないと思いますが、求められている処理を実現するために、簡単なユーザー定義関数を作ってみました。

メニューの拡張機能 - Apps Script から、エディタに次のコードを貼り付けます。

function qiita_QA() {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let s1 = ss.getSheetByName("シート1");
    let s2 = ss.getSheetByName("シート2");

    let values1 = s1.getRange("A1").getDataRegion().getValues().slice(1).flat();
    // Logger.log(values1);

    let values2 = s2.getRange("A1").getDataRegion().getValues().slice(1);
    // Logger.log(values2);

    let values3 = values2.filter(x => values1.includes(x[0]));
    // Logger.log(values3);
    return values3;
}

「シート3」のセルA2に=qiita_QA()でユーザー定義関数が実行されます。

処理としては、各シートのデータ範囲から、値を配列で取得して、フィルターをかけた結果を返すだけです。
データの範囲も自動的に取得できるので、データ範囲のセル指定は不要です。

下図は、スクリプトエディタでログ出力させて、値が配列として操作されている様子を確認したものです。

質問者の参考になれば幸いです。

(8月24日　追記)
１点、注意を忘れていました。
この関数はセルを参照する引数を持っていないので、シート1のIDを変更しても自動再計算されません。
シート3の関数を消して、再度同じ関数を入力すると新しい結果を返します。
求められる最低限の動作をする関数として、思いつきで書いたものです。
自動再計算させるためには、もう少し手を加える必要があります。

ついでに画像も添付します。

シート1（抽出したい注文ID）

シート2（元データ）

シート3に関数を入力

結果待ち

結果が表示されます

(8月24日　追記 2)
質問者からの返信を受けて、関数の改善修正を行いました。

function qiita_QA(range_ID) {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let s2 = ss.getSheetByName("シート2");

    let values1 = range_ID.flat();
    let values2 = s2.getRange("A1").getDataRegion().getValues().slice(1);

    let values3 = [];
    values1.forEach(id => values3.push(values2.filter(data => data[0] == id)))

    return values3.flat();
}

関数の引数として、シート1の注文IDのセル範囲を与えることにしました。

また、注文IDの順に抽出を行うように、手を加えました。

以下、画像です。

シート1（抽出したい注文ID　【ID順不同　空行あり】）

シート3に関数を入力した結果
とりあえず、関数の引数を　'シート1'!A2:A20　にしてあります。

画像は添付しませんが、シート1の値を変更すると、シート3の結果が自動的に変更されます。

0Like

Comments

@kunio_fujigasaki
Questioner
回答ありがとうございます！
GASも初心者なので、関数の使い方などとても参考になりました。

お察しのとおり、サンプルの作りこみが甘くて本来求めているものとずれた質問になってしまい反省しています。

実務では結局filter関数を使って商品ID上位３つを表示するようなアナログな手法にしています。関数は恐ろしく長いですが自動出力されるので便利に感じます。

いただいた回答案の場合、毎回関数を入力する必要がある点は承知しました。シート１の商品IDが昇順などでなく順不同であった場合、その順番どおりに出力することはできるでしょうか。
@Mt_SQ
当方の的外れな回答にご返信いただき、感謝申し上げます。

質問の目的が Google Spreadsheet のクエリ関数に関するものであれば申し訳ないなと思いつつ回答させていただきました。

さて、追加でご質問の「シート1の商品ID(注文ID?)の順番どおり･･･」に対応し、シート1の変更がシート3の抽出結果に即時反映されるようにコードを修正してみました。
修正コードは、後ほど回答に追記させていただきます。

あくまでも、即席で要望にお応えしたものです。稚拙さについてはご容赦ください。

GASはプログラムですので、シート関数をひねり回すよりもシンプルに記述できることが多いと思います。
正しく管理(コメントやドキュメントなどの整理)をすれば、保守性も優れているので、実務においても強力なツールになります。
（いま、「ちゃんと管理しろと」、自分に言い聞かせています。）

もちろん、多少の学習コストを要しますが･･･
@kunio_fujigasaki
Questioner
お返事大変遅くなり申し訳ありません！
改定コードでうまく動作しました！　ありがとうございました。

Are you sure you want to delete the question?

Spreadsheet上で特定IDをフィルターの条件にした抽出方法

解決したいこと

1Answer

Comments

Your answer might help someone💌