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

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

ただいまの
回答率

90.33%

  • Google Apps Script

    959questions

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

  • Google

    663questions

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

  • Google スプレッドシート

    14questions

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 288

kata45

score 1

 前提

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

ID 名前
1 社員A aaa
2 社員B bbb
3 社員C ccc

 発生している問題

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

 試したこと

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

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • macaron_xxx

    2018/07/24 09:27

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

    キャンセル

  • kata45

    2018/07/24 10:06 編集

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

    キャンセル

回答 1

checkベストアンサー

+1

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

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

function myFunc() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  var range = sheet.getDataRange();

  var oldFilter = sheet.getFilter();
  if (oldFilter !== null) {
    var result = Browser.msgBox("すでにフィルタが適用されていますが、削除して続行しますか", Browser.Buttons.YES_NO);
    if (result === "no") {
      return;
    } else {
      oldFilter.remove();
    }
  }

  var filter = range.createFilter();

  // フィルタ内容の定義
  var fArray = [];

  // データ規則とデータ規則範囲を追加していく
  fArray.push({"target":"C2","data":"'シート2'!$A$2:$A$4"});
  fArray.push({"target":"D2","data":"'シート2'!$B$2:$B$5"});

  var formula = "";
  for each (var ar in fArray) {
    if (formula === "") {
      formula += "=NOT(AND(";
    } else {
      formula += ","
    }
    formula += ar.target + "=IFERROR(VLOOKUP(" + ar.target + "," + ar.data + ",1,FALSE),FALSE)"
  }
  formula += "))";

  var filterCriteria = SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied(formula);
  filter.setColumnFilterCriteria(1,filterCriteria);
}

カスタム数式

=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 13:39

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

    キャンセル

同じタグがついた質問を見る

  • Google Apps Script

    959questions

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

  • Google

    663questions

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

  • Google スプレッドシート

    14questions