kabuさん、元データから複数の指定キーワードを除いたリストを作成したいです！
指定キーワードは列範囲でパパッと指定できるようなものが良いです！
んー
確か「FILTER」関数や「QUERY」関数で指定キーワードの除外は出来ますが、複数個・列範囲指定などでは上手く動きませんね。
じゃあ今回は
① 元データから”リスト“で複数の除外項目を指定できるフィルタシステム
② 作成した数式を「名前付き関数」に登録する
③ 作成した関数を動かしてみる
④ 数式の説明
ってことで話をしましょう。
元データからリストで複数の除外項目を指定できるフィルタシステム
なんか、タイトルが難しい汗
このタイトルでやりたいことを伝えられているか分かりませんが、これから僕が紹介しようとしているのは次のようなフィルタ機能です。
A:C列にはマスターデータがあり、E:F列はそれを参照しているデータですね。
H列に「除外キーワード」を入れていくことで、E:F列にフィルタがかかる、って感じです。
例えば除外リストに「会社」と入れると、「会社」と名のつくものが除外されます。
続いて「有」と入れると、同様に「有」がつくものが除外されます。
「不動産」と入れても同様ですね。
うんうん！
そうです！私がイメージしていたのもこういうリストです！
このスクリーンショットでいう、E3セルに入力した数式は次のとおり。
=QUERY(B3:C,"Where not Col1 is null"&CONCATENATE(ARRAYFORMULA(" and not "&
"Col1"&" contains '"&QUERY(H3:H,"Where not Col1 is null")&"'")))詳しくはこちらで説明しますが、
簡単に言うとH列の項目を「ARRAYFORMULA」関数と「CONCATENATE」関数でつなげて、「QUERY」関数の「クエリ」に”除外項目”として渡しています。
作成した数式を「名前付き関数」に登録する
数式をシンプルにするのと、他のスプレッドシートでも流用できるよう、
「名前付き関数」として登録しましょう。
「名前付き関数」の詳しい説明については以下の記事でも紹介しています。
興味があれば見てみてください。
名前付き関数は、スプレッドシートの
上部メニュー ＞ 「データ」 ＞ 「名前付き関数」 と進むと画面の右にメニューが表示されます。
「新しい関数を追加」ボタンをクリックします。
新しい関数の登録メニューが表示されるので、次のように入力していきます。
| 関数名 | QUERYEX |
| 関数の説明 | 除外リストを指定できるQUERY関数です。 |
| 引数 | データ、クエリ、除外列、除外リスト |
| 数式の定義 | =QUERY(データ,クエリ&CONCATENATE(ARRAYFORMULA(” and not “&除外列&” contains ‘”&QUERY(除外リスト,”Where not Col1 is null”)&”‘” ))) |
「次へ」を押します。
続いて「補足情報」のメニューでは・・・
| データ | クエリを実行するセルの範囲です。 |
| データの例 | B3:C12 |
| クエリ | 実行するクエリを Google Visualization API のクエリ言語で指定します。 |
| クエリの例 | “Where Col1 like (‘%会社%’)” |
| 除外列 | 「データ」の中で除外リストと照合する列を指定します。 |
| 除外列の例 | “Col1” |
| 除外リスト | 除外する項目が入力されている列範囲を指定します。 |
| 除外リストの例 | ‘sheet1!’A1:A |
「作成」を押して関数を登録します。
作成した関数を動かしてみる
適当なセル（今回はE3セル）に「=QUERYEX」と入力します。
すると作成した「名前付き関数」が候補に出てきますね。
「データ」にはマスターデータの範囲「B3:C」を指定し、クエリには「”Where not Col1 is null”」と入力します。
続いて「除外列」ですね。
「除外列」には「”Col1″」と入力してください。
これは何を指定しているかと言うと、「データ」で指定した範囲の中の”何列目”と「除外リスト」を照合しますか？ってのを指しています。
今回データには「B3:C」と2列の範囲を指定しているので、そのうちの“Col1″=”1列目”を指定しています。
最後に除外リストの列範囲を指定します。
今回は「H3:H」を指定しています。
エンターキーを押すと・・・
・・・あれ〜？？
エラーが表示されてしまいましたよ？！
私どこか間違えたかな？？
今回作った関数は「除外リスト」ありきなので、
「除外リスト」に何もないとエラーが出てしまいます。
なんでもいいんですが、
「会社」、「有」、「不動産」と入力してみます。
あっ！！
ちゃんと「除外リスト」の項目が除外されて表示されました！！
「除外リスト」がブランクの場合の処理もやろうと思えば出来ますが、
そもそもそれだったらただの「QUERY」関数使えば良いですしね。
今回はそこまでの数式は組んでいません。
数式の説明
今回作成した数式について説明していきます。
「QUERY」関数の構造は次のようになっています。
=QUERY( データ , クエリ )
※「[見出し]」については今回使用していないので説明を省いています。=QUERY(B3:C,"Where not Col1 is null"&CONCATENATE(ARRAYFORMULA(" and not "&
"Col1"&" contains '"&QUERY(H3:H,"Where not Col1 is null")&"'")))で、これが今回の数式ですね。
「データ」は参照範囲とでも思ってもらって結構です。
「クエリ」については、他の関数とは一線を画す書き方になっているので、なかなか理解をするのは難しいですよね。
他の関数だったら、数値だったりセルだったりを指定すれば良いのに・・・
この「QUERY」関数、ただものじゃないですね〜汗
「QUERY」関数の書き方については、一旦他のブログ等に任せるとして・・・
今回はこの数式で何をやっているのかについて説明していきますね。
=QUERY(B3:C,
"Where not Col1 is null" &
CONCATENATE(
ARRAYFORMULA(" and not " &
"Col1" &
" contains '" &
QUERY(H3:H,
"Where not Col1 is null"
)&"'"
)
)
)階層を分けて見ていきます。
「QUERY」関数の「クエリ」では、「Where not Col1 is null」このように英文っぽい書き方をします。
この文章の意味は、「Col1」=「データの1列目」で「not is null」=「空白だった行は除外」しています。
問題は次の階層からだと思いますが・・・一番深い階層にある「QUERY」関数を先に見てみましょう。
=QUERY(B3:C,
"Where not Col1 is null" &
CONCATENATE(
ARRAYFORMULA(" and not " &
"Col1" &
" contains '" &
QUERY(H3:H,
"Where not Col1 is null"
)&"'"
)
)
)ここでも先ほどと同様、「Col1」=「データの1列目」で「not is null」=「空白だった行は除外」していますが、「データ」は「H3:H」、つまり「除外リスト」を指しています。
「H3:H」の「除外リスト」から”空白を詰めて”値を取得しているわけです。
=QUERY(B3:C,
"Where not Col1 is null" &
CONCATENATE(
ARRAYFORMULA(" and not " &
"Col1" &
" contains '" &
"会社"
"有"
"不動産" &"'"
)
)
)今回の見本のデータだと、「会社」、「有」、「不動産」が入ってきます。
=QUERY(B3:C,
"Where not Col1 is null" &
CONCATENATE(
ARRAYFORMULA(" and not " &
"Col1" &
" contains '" &
"会社"
"有"
"不動産"&
"'"
)
)
)それを「ARRAYFORMULA」関数で、他のテキストと合わせて、がっちゃんこしています。
” and not “や”Col1″、” contains ‘”はただのテキストに見えますが、分けている理由があるのでしょうか？？
これを分けている理由としては、真ん中の「”Col1″」を引数にしたいからですね。
「引数のプレースホルダ」で指定した「除外列」として、可変できる値にしておきたいわけです。
1列目じゃなく、2行目や3行目の項目を「除外リスト」と照合したい場合もありますからね。
=QUERY(B3:C,
"Where not Col1 is null" &
CONCATENATE(
ARRAYFORMULA(" and not " &
"Col1" &
" contains '" &
"会社"
"有"
"不動産"&
"'"
)
)
)話を戻すと・・・「QUERY」関数で取得した”空白無し”の「除外リスト」を、「ARRAYFORMULA」関数で他のテキストとつなげています。
=QUERY(B3:C,
"Where not Col1 is null" &
CONCATENATE(
" and not Col1 contains '会社'"
" and not Col1 contains '有'"
" and not Col1 contains '不動産'"
)
)「QUERY」関数で取得した値は、それぞれのセルで分かれているので、「ARRAYFORMULA」関数の処理はこんな感じに仕上がります。
この出力結果を「CONCATENATE」関数で、更にがっちゃんしているわけです。
=QUERY(B3:C,
"Where not Col1 is null" &
" and not Col1 contains '会社' and not Col1 contains '有' and not Col1 contains '不動産'"
)「contains」というのは、「〜を含む」って意味なので、
「Col1」の中で、「会社」や「有」、「不動産」を含む行は「除外してね」ってことになります。
・・・っほー
「QUERY」関数って難しいー汗
数式に使用した関数の説明
使用した関数のAi解説も載せておきます。
参考にどうぞ。
QUERY
QUERY関数は、Google スプレッドシートで提供されている強力な関数の一つで、指定された範囲からデータをクエリ（問い合わせ）し、条件に一致するデータを抽出または操作することができます。この関数は、Google Visualization API Query Languageを使用して、データをフィルタリング、ソート、集計するための複雑なクエリを記述することが可能です。
QUERY(データ範囲, クエリ文字列, [ヘッダー数])
- データ範囲: クエリを実行するデータの範囲を指定します。
- クエリ文字列: 実行するクエリを文字列として指定します。この言語はSQLに似ており、
SELECT,WHERE,ORDER BY,LIMITなどの命令を使用してデータを操作します。 - ヘッダー数 (オプション): データ範囲の最初の何行をヘッダーとして扱うかを指定します。この引数を省略すると、Google スプレッドシートが自動でヘッダーを判断します。
QUERY関数は、大量のデータから特定の情報を抽出したい場合や、データを特定の条件でフィルタリングして表示したい場合、またはデータをソートや集計したい場合などに非常に便利です。たとえば、特定の条件を満たす行のみを抽出したり、特定の列のデータを基に集計を行ったりする場合に使用できます。
CONCATENATE
CONCATENATE関数は、複数のテキスト文字列を一つに結合します。例えば、名前と姓を別々のセルに分けて入力している場合、この関数を使ってフルネームを作成することができます。
CONCATENATE(テキスト1, [テキスト2, ...])
ARRAYFORMULA
ARRAYFORMULA関数は、範囲や配列に対して一括で式を適用します。通常の関数がセルごとに適用されるのに対し、ARRAYFORMULAを使うと、一度に複数のセルに同じ操作を適用できます。
ARRAYFORMULA(式)
まとめ
今回は
① 元データから”リスト“で複数の除外項目を指定できるフィルタシステム
② 作成した数式を「名前付き関数」に登録する
③ 作成した関数を動かしてみる
④ 数式の説明
について話をしました。
「QUERY」関数むずかしかった・・・
でも「CONCATENATE」や「ARRAYFORMULA」関数で、「QUERY」関数用の”除外リスト”を作った、ってことは分かりました。
コメント