タグ別アーカイブ: Search関数

文字列の抽出~上級編17回

 エクセル関数で特定の文字列を抽出する場合には、RIGHT関数やLEFT関数、そしてMID関数が良く使われます。今回の解説では、Power Queryでこれらの関数と同じ様に特定の文字列を抽出する方法を解説します。

 また、特定の文字列の位置が不特定の場合、エクセル関数ではFIND関数やSEARCH関数が前述の関数と組み合わせて使用されます

Power Queryでは、特定の文字列の位置が不特定の場合でも一括で抽出できる裏技がありますので、そちらについても解説を行います

例えば、

 下のGIF画像のように、「-」が不規則に出現する文字列の中から「左から2つ目3つ目ハイフン」の文字列を抽出することもできます

では、Power Queryにて特定文字列を抽出する方法について詳細な解説をはじめます

 以降の解説は、全てPower Queryエディタ(以降、エディタ)を開いた状態から解説を行います

エディタ内で使用するタブは「変換タブ」、使用メニューは「抽出」になります

目次

1.左から指定した長さの文字数を抽出

2.右から指定した長さの文字数を抽出

3.位置を指定して文字列の途中から指定した長さを抽出

4.特定の文字列の前後の文字を抽出

5.「特定の文字列の間」の文字を抽出

<まとめ>

1.左から指定した長さの文字数を抽出

エクセル関数では、LEFT関数で行う内容です

こちらは、下の画像の「最初の文字」から抽出を行います

上の画像の「最初の文字」をクリックすると下の画像の画面が開くので、こちらで抽出する長さを指定します

OKボタンを押せば、下のGIFのように左から3文字が抽出されます

2.右から指定した長さの文字数を抽出

こちらはエクセル関数で言えば、RIGHT関数になります

下の画像にある「最後の文字」から指定します

1.の「左から指定した長さの文字数を抽出」と同じ様に、「最後の文字」をクリックした後に開くダイアログボックスにて抽出する長さを指定します

後の処理は、1.と同じです

3.位置を指定して文字列の途中から指定した長さを抽出

こちらは、エクセル関数でいえばMID関数の内容になります

こちらは、下の画面の箇所から指定を行います

「範囲」をクリックした後、次の画像の画面で「①開始インデックス」「②文字数」を指定します

①開始インデックス・・・抽出を開始する位置(0から指定可)

②文字数・・・抽出文字数の長さ

上の①②を指定して右下のOKボタンを押せば、①開始インデックスの位置から②の文字数が抽出されます

上のGIF画像では①は「3」、②は「2」を指定しています

MID関数とは違い、指定した「開始インデックス」の「数字」に+1した位置から文字が抽出されます

下の図のように、MID関数では「4」の位置が、Power Queryの開始インデックス「3」に相当します

この関数との違いは、Power Queryでは「0」から指定ができるようになっていることに起因していますので、注意が必要です

4.特定の文字列の前後の文字を抽出

①特定の文字列のの文字を抽出

4.の①では、下の文字から、「ABC」のの文字を抽出します

抽出は下の画像の「区切り記号の前のテキスト」を指定します

「区切り記号の前のテキスト」をクリックすると、下の画像のようにダイアログボックスが開くので、こちらで「特定の文字列」の「ABC」を指定します

特定の文字列を指定し、ダイアログボックス右下のOKボタンを押せば、「ABC」の前の文字が抽出されます

② 特定の文字列のの文字を抽出

①と基本的に方法は一緒ですが、今回は2つ特殊な方法を付け加えます

ⅰ)繰返し「特定の文字列」が出現する場合には、位置(〇番目)を指定

ⅱ)「特定の文字列」を読み込む方向を指定

では、下の文字列から「ハイフン/-」を特定文字列としてⅱ)入力末尾から、ⅰ)「ハイフン/-」が2回目に出現した「後」の文字列を抽出します

「特定文字列の後」の文字列を抽出するには、下の画像の「区切り記号の後のテキスト」をクリックします

次に開いたダイアログボックスではまず、「詳細設定オプション」をクリックします

次に、詳細設定オプションにて2つの箇所を指定をします

詳細設定オプションでは、前述のⅱ)の通り、入力末尾からを指定します

そして、「スキップする区切り記号の数」については、2つ目を指定しますので、1つ目の「ハイフン/-」はスキップします

5.「特定の文字列の間」の文字を抽出

5.では下の文字列から、記事の冒頭のGIF画像と同じ様に、文頭から2つ目3つ目の「ハイフン/-」の間の文字列を抽出します

今回、使用するメニューは下の画像の黄色の箇所になります

「区切り記号の間のテキスト」をクリックした後に開いたダイアログボックスにて、まずは「開始区切り記号」と「終了区切り記号」を指定します

こちらは両方とも「ハイフン/-」を指定します

次に、下の画像のように詳細設定オプションを指定します

詳細設定オプション

まず、上の画像 (詳細設定オプション) の1番目の黄色の箇所「開始区切り記号(特定文字列)のスキャン」と3番目の「終了区切り記号のスキャン」の2つを指定します

こちらの2つの箇所では、特定文字列の読み込む方向を「文字列の先頭から」にするのか、それとも「末尾」からにするのかを指定します

こちらはそれぞれ、1番目は「入力の先頭から」、3番目は「開始区切り記号から入力の末尾方向へ」を指定して、読み込む方向を2つとも一致させます

そして、上の画像(詳細設定オプション)の2番目「スキップする開始区切り記号の数」は「1」を指定することで、2つ目の「ハイフン/-」を特定文字列の対象にします

もし、1つスキップせずに「0」を指定した場合には、前述のように1つ目の「ハイフン/-」が特定文字列の対象になります

次に、 上の画像の最後「スキップする終了区切り記号の数」 については、デフォルトの「0」のままにします

もし、「スキップする終了区切り記号の数」を 「1」で指定した場合には文頭から3つ目ではなく、4つ目の「ハイフン/-」が特定文字列(開始ではなく終了側)として指定されます

では、詳細設定オプションの指定が終了したら、右下のOKボタンを押します

すると、2つ目と3つ目の「ハイフン/-」の間の文字列が抽出されています

<まとめ>

 今回は、次の内容について解説を行いました

1.文字列の左から指定した長さ分の文字列を抽出

2.文字列の右から指定した長さ分の文字列を抽出

3.位置を指定して文字列の途中から指定した長さを抽出

4.文字列の中から「特定の文字列」の前後を抽出

5.文字列の途中から2つの特定文字列間の文字列を抽出

 上の内容の内、特に下から2つの内容については、「スキップ」や「読み込み方向」などを指定することで、高度な「抽出」が行えるようになっています

 うまく「スキップ」「読み込み方向」を組み合わせて、Power Queryをより有効に活用していきましょう!

今回は以上です

参考までに今回使用したエクセルファイルを添付します

尚、クエリの読込先は全て「接続専用」になっています

アイコン

上級17回 20.66 KB 12 downloads

...

最後まで長文にお付き合い頂き誠にありがとうございました

次回は文字列の置換、追加について解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

エクセルなのに・検索付きリスト_2/2

今回は、検索付きリストの具体的な作成方法を紹介します

作成するには、3つの手順を踏みます

ちなに、VBAは一切使いません!

➀検索文字を作成

リストに入力された文字から検索文字を作成する

➁候補データの作成する

検索文字をもとにして、リストデータから候補データを作成する

③候補データの表示

②で作成した候補データをリスト表示します

上の①~③をエクセルシート上で表すと、次の画像の通りとなります

画像に alt 属性が指定されていません。ファイル名: 3-1.jpg

上の画像の中で、最もポイントとなるのはです!

ここで、エクセルの新機能である、1セルに出力範囲が収まらない関数を使用します!

では、ここから詳細な解説を開始します

➀検索文字を作成

下の画像の通り、C1セルに「=E2」を入力し、リスト内の文字(E2セル)を、検索文字としてC1セルに抽出します

➁候補データを作成する

ここから次の3つの関数を組み合わせて、検索文字から候補データを作成しますⅰ)SEARCH関数

ⅱ)ISNUMBER関数

ⅲ)FILTER関数

ⅰ)SEARCH関数

構文/SEARCH(検索文字列,対象,[開始位置])

まずは下のGIF画像にて、この関数を入力した時の様子を確認してください

1つのセルに入力したら、対象(リストデータ)に該当したデータを複数出力してくれます

ちなみに、上のGIFで出力される「1」の意味合いは想像がつきますでしょうか?

もし、検索文字が「悟」の場合には、下の画像のように「4」が出力されます



つまり、SEARCH関数は検索文字が含まれる位置を出力してくれるのです!

ですので、検索文字が含まれる候補データを、リストデータの中から特定できるようになりました

ただし、あくまで候補データを特定しただけで、候補データとしてはまだ使用できませんので、次のⅱ)でもう少し修正を加えます

ⅱ)ISNUMBER関数

構文/ ISNUMBER(判定対象)

ISNUMBER関数は判定対象が「数字」かどうかを判定する関数です

判定対象が数字であればTRUE数字でなければFALSEを返します

ですから、前述ⅰ)のSEARCH関数で出力した内容(例/1や4)をISNUMBER関数の判定対象にした場合にはどうなるでしょうか?

ISNUMBER関数の判定がTRUEのものは「候補データとして使えるもの」、FALSEは「候補データとして使えないもの」として区分することができます

ⅲ)FILTER関数

構文/FILTER(a:対象,b:条件式,c:省略可/合致するもの無の場合)

➁の仕上げとしてFILTER関数を使います

FILTER関数は、aの対象リストの中からbの条件式に合致するものを抽出します

今回は、FILTER関数で下の画像の「A列/リストデータ」の中から、「C1セル/検索文字」が含まれているものを抽出します

画像に alt 属性が指定されていません。ファイル名: 6.jpg

但し、検索文字に該当するかどうかについては、直接は判定することができないので、b条件式を2段階に分けます

ちなみに、条件式はTRUEかFALSEかどうか、でも設定可能です!

1段階目➡SEARCH関数にて、リストデータの中で検索文字を含むものは数値化

2段階目➡1段階目で数値化したものをISNUMBER関数でTRUEとFALSEに置換

では、上記の「1段階目」と「2段階目」を実際にFILTER関数で表現してみしょう!

FILTER( _

a:A列/リストデータ _

b:ISNUMBER(SEARCH(C1セル/検索文字,A列/リストデータ)) _

)

上のb条件式を前述の1段階目と2段階目で分けると次の通りとなります

1段階目 ➡ SEARCH(C1セル/検索文字,A列/リストデータ)

1段階目で検索文字が含まれている位置を1や4などに数値化します

2段階目 ➡ ISNUBER(1段階目)

1段階目で数値化されたものをTRUE、それ以外をFALSEで返します

では、上記のFILTER関数の実際の”動き”をGIF画像で確認してみましょう

C1セルに設定した、E2セルとの連動式(=E2)を一旦外し、「鈴木」と入力してみます

検索文字の「鈴木」を含む候補データが、A列のリストデータから抽出されています

検索文字の「鈴木」を入力する前は、全てのデータが出力されています

これは、氏名の間に空白が含まれているためです

③候補データをリストに表示する

②で候補データを抽出する仕組みができました

いよいよ、候補データから検索付きリストを作成します

➀で検索文字を入力しますが、通常のリストの作成の仕方では、エラーになってしまいます

何故なら、検索文字自体はリストに含まれていないからです

例えば、下の画像でいえば、鈴木健司~鈴木修の中に「鈴木」と一致するものがないです

ではどうするか?

リスト機能にはエラーメッセージを調整する機能があるので、そちらの機能を使います

上の画像の黄色の箇所「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外します

そうすれば、リストに完全一致しないものをリストに入力してもエラーメッセージは出力されません

そして、最後にリストの範囲の仕方を工夫しましょう

リストの「元の値」として、候補データが出力される最初のセルを指定しましょう!

 そして、を加えれば、出力される候補データ数に合わせてリストが自動作成されます!

下のGIF画像は#の使用例です!

<まとめ>

 今回は、エクセルの新機能(SEARCH関数、FILTER関数)とリスト機能を組み合わせてリストに検索機能を加えることができました

 SEARCH関数により、検索文字を含む文字列から検索文字が含まれる「位置/数値」を抽出し、ISNUMBER関数でTRUEFALSEに置き換えるのがポイントです

 検索文字が含まれているかどうかをTRUEかFALSEに置き換えることで、FILTER関数の条件式として活用することができます

 FILTER関数をはじめとする、エクセルの新関数を有効活用すれば「VBA無」でもかなりの事ができますので、今回を機会に、ぜひ研究してみてください

FILTER関数の使用例については、他にも記事を書いています!

 もし、今までリスト機能を使ったことがない方がいたら、エクセル画面上のデータタブからデータの入力規則を見つけてみてください

データの入力規則から前述のリストが作成できます

解説は以上です

長文に最後までお付き合い頂き誠にありがとうございました

参考までに今回使用したエクセルファイルを添付します

アイコン

検索付きリスト 11.00 KB 2 downloads

...
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

エクセルなのに・検索付きリスト_1/2

エクセルのリスト機能はとても便利です

私も重宝しています

ただ、リストデータが一定数を超えるととても探しづらくなります・・・

とにかく、カーソルを下へ下へと旅が続きます

ところが、

エクセルの新機能を使えば、検索を行いながらリスト選択ができるのです

上のGIFでは、まずリストの中に”鈴木”と入力します

その後に、▽を押すとリストデータ内で”鈴木”を持つデータが表示されます

このリストを作成するのにVBAは使用しません

エクセル新機能と言える2つの関数を使用します

普通、関数と言えば値が1つ返ってくるのが常識でした

今回の検索付きリストで使用するSEARCH関数とFILTER関数はこの常識を覆すものです

例えば、下の画像で言えば、C3セルにSEARCH関数を入力すると「鈴木」を含む位が複数出力されます

今回のリスト作成を通じて、ぜひ、これらの新機能も有効活用できるようになりましょう!

では次回、詳細を解説していきます

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村