質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Google

Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

Q&A

解決済

1回答

3471閲覧

Googleスプレッドシートでデータの入力規則に合致しない行を抽出したい。

kata45

総合スコア7

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Google

Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

0グッド

0クリップ

投稿2018/07/20 12:11

編集2018/07/23 01:21

前提

Googleスプレッドシートのデータの入力規則を使用して入力内容に制限をかけているセルがあります。
以下の表の値列に別シートの表を指定して入力制限を行っているイメージです。

ID名前
1社員Aaaa
2社員Bbbb
3社員Cccc

発生している問題

入力制限の基準となっている表の内容を変えなくてはいけなくなりました。
(値列にaaa, bbb, cccのみ入力可能としていたところをccc, ddd, eee, fffのみと変更)
実際のスプレッドシートでは10列以上変更箇所があるためできるだけ簡単に抽出したいです。

試したこと

  • フィルタ機能を使用して抽出できないか試しましたが、上手く抽出できませんでした。
  • 関数はISERRORやIFERRORを試してみましたが、数式のエラーでないためか期待した結果になりませんでした。

機能や設定の見落とし等あるかもしれませんが、ご教授頂けると幸いです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

macaron_xxx

2018/07/24 00:27

「抽出したい」とありますが、ゴールが何かわかりません。「抽出」は何をさしますか?例えば、合致していない行のフィルタをかけたいのか、行番号さえわかればいいのか、など
kata45

2018/07/24 01:16 編集

フィルタを使用して合致しない行のみ表示されている状態にしたい。という意味で抽出という書き方をしました。上記の表の場合、値列がaaa, bbbのみ表示されている状態です。
guest

回答1

0

ベストアンサー

下記のようなスクリプトを書いて、データ規則とデータ規則範囲を設定すれば、
関数実行でフィルタが適用されるはずです。

スクリプトがよくわからない場合は、フィルタのカスタム数式で乗せる数式を最下部に記述しておきます。
なんとなくわかると思うので、適宜変更して使ってください。
(スクリプトは自動でカスタム数式を作っているだけです。)

JavaScript

1function myFunc() { 2 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1"); 3 var range = sheet.getDataRange(); 4 5 var oldFilter = sheet.getFilter(); 6 if (oldFilter !== null) { 7 var result = Browser.msgBox("すでにフィルタが適用されていますが、削除して続行しますか", Browser.Buttons.YES_NO); 8 if (result === "no") { 9 return; 10 } else { 11 oldFilter.remove(); 12 } 13 } 14 15 var filter = range.createFilter(); 16 17 // フィルタ内容の定義 18 var fArray = []; 19 20 // データ規則とデータ規則範囲を追加していく 21 fArray.push({"target":"C2","data":"'シート2'!$A$2:$A$4"}); 22 fArray.push({"target":"D2","data":"'シート2'!$B$2:$B$5"}); 23 24 var formula = ""; 25 for each (var ar in fArray) { 26 if (formula === "") { 27 formula += "=NOT(AND("; 28 } else { 29 formula += "," 30 } 31 formula += ar.target + "=IFERROR(VLOOKUP(" + ar.target + "," + ar.data + ",1,FALSE),FALSE)" 32 } 33 formula += "))"; 34 35 var filterCriteria = SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied(formula); 36 filter.setColumnFilterCriteria(1,filterCriteria); 37}

カスタム数式

=NOT(AND(C2=IFERROR(VLOOKUP(C2,'シート2'!$A$2:$A$4,1,FALSE),FALSE),D2=IFERROR(VLOOKUP(D2,'シート2'!$B$2:$B$5,1,FALSE),FALSE)))

投稿2018/07/24 02:24

macaron_xxx

総合スコア3191

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kata45

2018/07/24 04:39

回答ありがとうございます。 VLOOKUPとIFERRORを組み合わせると抽出することができるんですね。 スクリプトも使わせて頂きます。助かりました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問