GoogleスプレッドシートのAND関数の記述方法について

Question

実現したいことと、その問題

A,B,Cのセルのいずれかに"hoge"という文字列が存在する時、A>B>Cのセルの優先度で、最初に"hoge"がヒットしたセルの背景色を変えたい。
この時、各A,B列の条件付き書式設定ルールでは

=AND($A1="hoge")
=AND($A1<>"hoge",$B1="hoge")

で条件設定できるが、C列に

=AND($A1:$B1<>"hoge",$C1="hoge")

の記述では条件設定できない。しかし

=AND($A1<>"hoge",$B1<>"hoge",$C1="hoge")

の記述だと条件が機能する。

仮にA>B>C>D>E>Fと、判定するセルが増えた場合、

=AND($A1<>"hoge",$B1<>"hoge",$C1<>"hoge",$D1<>"hoge",$E1<>"hoge",$F1="hoge")

と、コードが冗長化してしまう。

質問内容

以上の問題を解決するために、もっとスッキリした書き方はできないでしょうか？
また、なぜ複数セルの参照ができないのでしょうか？

以上、お知恵を拝借願えないでしょうか。
よろしくお願いします。

Answer 1

=CONCATENATE($A1:A1)="hoge"

こう条件を設定すれば、全部のセル共通で条件設定できますよ。

他の文字列が入ったりする場合には、もう少し工夫が必要です。

追記

色々考慮漏れでしたね。
A列は

=A1="hoge"

B列以降は全て

=AND(B1="hoge",IFERROR(FIND("hoge",CONCATENATE($A1:B1)),0)=LEN(CONCATENATE($A1:A1))+1)

こうですね。
※hoge以外の文字列が他のセルに入っている場合にも、一番始めにでてくるhogeに反応するようになっています。

ただし、単一セルにxxhogexxのようにhogeを含んだ文字列やho,geのような連続したセルでhogeが構成される場合は考慮にいれていません。

Comments

[nelpesica]

ご回答ありがとうございます。
CONCATENATEは文字連結の関数ですよね？
この数式でどのように問題解決できるか、よく分かりません。
もう少し詳しく解説していただいてもよろしいでしょうか？

[nelpesica]

ご解説ありがとうございます。
文字列を数値化して比較し、その真偽で判定する方法だったのですね。
そういうアプローチはまったく考えにありませんでした。
その方法で当初の問題に対応することができるようになりました。
ありがとうございます。

Answer 2

シート内の数式では難しそうだったので、Google Apps Script でやってみました。
151598を自分のマイドライブ内にコピーして開き、[ツール]＞[マクロ]＞[myFunction]で実行できます。
初回のみ承認要求画面が出ますので、画面の指示に従ってください。

コードは以下の通りですが、[ツール]＞[スクリプトエディタ]からも参照／変更可能です。

function myFunction() {
  var keyword = "hoge";
  var bg = "blue";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = ss.getLastRow();
  var lastColumn = ss.getLastColumn();
  var vals = ss.getRange(1, 1, lastRow, lastColumn).getValues();
  Logger.log("lastRow = " + lastRow + ", lastColumn = " + lastColumn);
  Logger.log("vals = " + vals);

  for (var i=0; i<lastRow; i++) {
    for (var j=0; j<lastColumn; j++) {
      if (vals[i][j] == keyword) {
        ss.getRange(i+1, j+1).setBackground(bg);
        break;
      }
    }
  }
}

Comments

[nelpesica]

ご回答ありがとうございます。
数式を簡潔にすることは難しいということですね。

作成していただいたスクリプトを値の変更をトリガーにして実行することで、やりたいことはできるようになりました。
しかしスクリプトを実行した後、同行にhogeを追加・削除を行ったときの背景色の再判定がなく、これでは実用に至りませんでした。

GASの知識が無いため、もしよろしければ恐縮ですが再判定機能を追記していただけると幸いです。