AppSheet⑯文字数と文字の種類でフィルタリング制限する

　AppSheetで文字数制限したい人は多いでしょう。今回は、文字数と文字の種類でフィルタリング制限の記事を書きます。これで最適なデータのみ抽出することができます。

AppSheetに特定の文字数や文字の種類でフィルターする関数はない
テーブルの制限はスライスのRow filter conditionで制限する
lenとsubstituteを使って式を構築できる

特定の文字列と正規表現で比較してフィルタリング

　AppSheetで文字列と正規表現を比較して、Yesなら表示、Noなら非表示をやる方法を調べたり、実際に正規表現で入れてみたりしたのですが、うまく行かず。

　やりたかったことは、特定の列の文字が８桁の数字のみになっているかを判断したい。そして、その条件を満たす行だけを抽出したリストを出したいという事になります。

　エクセルなどであれば、それに対応した関数などがありそうなものですが下記のページを見ればわかる通りで、AppSHeetの関数は非常に少ないので、ありません。仕方ないので、何か力業的な方法で実現してみたいと思います。

スライスのRow filter conditionでテーブル抽出

　まず、テーブルから特定の条件のみを抽出したリストを作るにはSliceの作成が必要になります。これは基本的なことなので、問題ないでしょう。

　その作成したスライスで、特定の条件でフィルタリングするのは「Row filter condition」の部分に記載することで実現できます。

　上記で右側の値の部分をクリックすると、ポップアップウィンドウが表示されます。下記の画像では既に式が記載されていますが、最初は空欄になっています。ここに条件を入れると、その条件を満たした行のみが表示される抽出テーブルになります。

　式を記載したらその下にチェックが入って、問題がなければSaveしてそのスライスを表示すればOKです。

長さのチェックと文字の置き換えで制限する

　どんな式を入れるかですが、まず２つの条件を入れることになるので、ANDを書きます。条件１と条件２の部分はこの後に説明します。

AND(
 条件１,条件２
)

　条件１の部分に入れたいのは、文字の長さが８文字であるかです。私の場合にはログインIDの文字の長さが８文字ちょうどの場合のみだったので下記の式の通りです。例えば、８文字以下などの場合には「len([ログインID])<=8」と<を追加します。10文字なら8の数字を書き換えましょう。

len([ログインID])=8

　条件２の方は、数字だけが使われているかの確認です。考え方としては、特定の文字列から０～９の数字を順番に取り除いていく。そして、全ての数字を取り除いた後の文字の長さが０であれば、全て数字だという計算式になります。

　まず、大きなくくりとしてlen(対象の文字列)=0があります。これによって、条件を満たす文字を除いた後の文字列の長さが０になっていることを確認します。

　そのあとはSUBSTITUTE( 対象の文字列 ,”0“,””)というSUBSTITUTEという関数を使って、対象の文字列から指定の文字を取り除いていきます。上記の式では0という数字を取り除いています。その結果をさらに対象の文字として、SUBSTITUTE(SUBSTITUTE( [ログインID] ,”0″,””),”1“,””)といった感じで1という数字を取り除いていきます。　これを0～9まで繰り返すことで、全ての数字が取り除かれます。

 len(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(  [ログインID]  ,"0","")
  ,"1","")
  ,"2","")
  ,"3","")
  ,"4","")
  ,"5","")
  ,"6","")
  ,"7","")
  ,"8","")
  ,"9","")
 )=0

　全てをまとめると下記の通りになります。これで文字の長さがちょうど８文字で数字のみの行が抽出されます。

AND(
 len([ログインID])=8,
 len(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(
  SUBSTITUTE(  [ログインID]  ,"0","")
  ,"1","")
  ,"2","")
  ,"3","")
  ,"4","")
  ,"5","")
  ,"6","")
  ,"7","")
  ,"8","")
  ,"9","")
 )=0
)