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

【M言語に慣れる】_15回目_TEXT関数/文字列関数

【M言語は難しくない】今回は、TEXT関数の中から、2つの事例を紹介したいと思います。通常のエクセル関数と同じ様に、M言語でも文字列を操作する関数は多数あります。興味のある方はぜひ、こちらのMicrosoft社のページも参照してみてください

目次

文字列の有無を判定する関数

文字列を置き換える関数

<まとめ>

文字列の有無を判定する関数

 Text.Contains関数は、ある文字列の中に指定した文字列があるかとうかを判定し、「TRUE」か「FALSE」で返す関数です

以下の一文は、Microsoft社のページからの抜粋になります

Text.Contains(“Hello World”, “Hello”)

上の ように記述した場合には、「TRUE」を返します

次もMicrosoft社のページからの抜粋になりますが、この場合は「FALSE」を返します

Text.Contains(“Hello World”, “hello”)

ちなみに、

 M言語では、大文字と小文字の違いは考慮されますので、この点は注意が必要です

では、実際にPower Queryエディタ(以降、エディタ)で実際にこの関数を操作してみます

使用するデータは、「抽出」という1列だけがあるこちらのデータになります

 上の画像のデータから、Text.Contains関数で文字列「0」を含む行を判定し、「TRUE」もしくは「FALSE」を返してみます

まず、「列の追加」タブからカスタム列・作成画面を開きます

こちらの画面でカスタム列の式に「Text.Contains」と入力します

上のGIF画像のように「Text.Con・・・」と入力する途中で、該当の関数が出てきますので、Tabキーで確定します

次にText.Contains関数の丸括弧の中を、以下の画像のように確定します

すると、エディタ内に「0」が含まれる行を判定した列が追加されます

文字列を置き換える関数

 Text.Replace関数は、ある文字列の中から、指定した文字列を発見して置き換える関数です

以下の一文は、Microsoft社のページからの抜粋になります


Text.Replace(“the quick brown fox jumps over the lazy dog”, “the”, “a”)

この場合、返される文字列は次のようになります

“a quick brown fox jumps over a lazy dog”

上記の場合、”the”が”a”に置き換えられています

 では、Text.Contains関数と同じデータを使用して、「0」を「a」に置き換えて見ましょう!

 こちらもText.Contains関数と同じ様に、カスタム列作成画面からM関数を使用します

上の図では、カスタム列作成画面に次のような式を入力しています

=Text.Replace([抽出],”0″,”a”)

では、上記の式を入力してカスタム列作成画面のOKボタンを押します

すると、エディタ内に文字列が置き換えられた列が追加されます

<まとめ>

今回は、Text関数の中から2つだけ事例を紹介しました

 1つ目は、文字列が含まれるかどうかを判定する「Text.Contains関数」でした

2つ目は、文字列を置き換える「Text.Replace関数」でした

 もし、通常のメニューにない操作をM関数で行う場合には、前述のMicrosoft社のページをぜひ参照してみてください

 ちなみに、カスタム列作成画面でM関数を入力する際に、自動で候補の関数が表示されます

 該当の関数を選択した場合、Text関数の場合であれば「TextText」と重なってエラーになるケースがあります

こちらのエラーについては、注意が必要です

今回の解説は以上となります

長文を最後まで読んでいただき誠にありがとうございました

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


にほんブログ村

関数をマスターする9~文字列操作関数・一式~

今回は文字列操作関連の関数を一式で解説します

この文字列操作関連の関数は一つ一つ覚えるより、組み合わせ方を覚えていくととても実践的な活用の仕方が可能になります

中でもMID関数FIND関数LEN関数の組み合わせは鉄板です

とにかく、”習うより慣れろ”を意識して取り組んでいきましょう!

1.氏名から空白を介して名前を抽出

サンプルファイルに演習シートがあります。このシートで「A列の氏名」から「名前/姓」を関数の組み合わせにて自動抽出します

➀LEFT関数、RIGHT関数

本題に入る前にLEFT関数とRIGHT関数でウォーミングアップをしましょう

対象の文字列の左、もしくは右側から指定した文字数を抽出します

=LEFT(対象文字列,文字数)、=RIGHT(対象文字列,抽出文字数)

上のGIF画像では、B2セルに「=LEFT(A2,1)」が入力されており、氏名の「田中 敦」の左から1文字目の「田」を抽出しています

C2セルには「=RIGHT(A2,1)」が入力されており、氏名の「田中 敦」の右から1文字目の「敦」を抽出しています

②MID関数

ここからが本題です

対象の文字列の途中から文字を抽出します

=MID(対象文字列,抽出開始位置,文字数)

ちなみに木村と浩の間には空白が入っています

上のGIF画像では、D2セルに「=MID(A2,2,2)」が入力されており、氏名の「田中 敦」の左から2文字目を起点として長さ2文字「中 」を抽出しています

D4セルは「=MID(A4,2,2)」が入力されており、氏名の「奈良原 功」の左から2文字目を起点として長さ2文字「良原 」を抽出しています

③FIND関数

FIND関数は、文字列の中から指定した文字の位置を見つけます。

=FIND(検索文字列,検索対象文字)

上のGIF画像のE2セルには「=FIND(“ ”,A2)」が入力されており、A2セルの「田中 敦」から空白の位置を見つけています

④LEN関数

このLEN関数は文字の長さを返してくれます

=LEN(対象文字列)

⑤MID&FIND&LEN関数

②~④を組み合わせて空白を介して氏名から名前/姓を抽出します

STEP1:FIND関数で空白(全角)の位置を見つける

STEP2:(LEN関数-FIND関数)で名前/姓の長さを抽出する

STEP3:STEP1、STEP2の値を使ってMID関数を作成する

2.桁区切りや小数点表示を指定する

桁区切りや小数点の表示桁数は、TEXT関数を使って自由自在に指定できます

=TEXT(対象文字列,書式)

桁区切り

桁区切りは、TEXT関数の2番目の引数を次のように”#,000″と指定します

=TEXT(A8,”#,000″)

小数点の表示桁数

小数点の表示桁数は、第2位まで表示させる場合はTEXT関数の2番目の引数を次のように”=TEXT(A9,”#.00″)”と指定します

=TEXT(A9,”#.00″)

以上です。いかがでしたか?関数の便利さを実感できたのでないでしょうか?

ではまた次回お会いしましょう!

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

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

エクセルで弾丸チャートを作成しよう 8回目

今回は前回に案内した通り、前年比と予算比を見やすく表示しましょう

キャプチャ37

本題に入る前に、今回使用するTEX関数とMAX関数を紹介します

まず、TEXT関数です。下記の形で使用します

=TEXT(データ、表示形式)

データのところで、セルなどを指定して、好きな表示に変換します

次にMAX関数です

シンプルに指定した範囲の中で最大のものを表示する、と覚えましょう

=MAX(引数、引数、引数・・・)

では本題です

まず、準備作業として、サンプルの演習シートで3つ列を挿入して”前年比”、”予算比”を下の画像のように、H列、I列にて算出しておきましょう

キャプチャ40

次にH2セルの値をTEXT関数で文字列に変換しましょう

キャプチャ41

何故”#,##0%”の形になるのか、という疑問を持つ方もいると思いますが、ここは割り切って覚えましょう!

H2セルが0%の形になりましたか?

次に&を使って”前年比”、”  | “、”予算比”、そしてI2セルも文字列に変換して組み合わせましょう

キャプチャ42

上記のようになりましたでしょうか?、上記のようになっていればG2セルの数式は

G3、G4にもコピーしておきましょう

そして、いよいよ文字列の組み合わせをデータラベルとして表示したいところですが、前回指摘した通り、グラフに重なることがないように工夫する必要があります

そこでF列にてMAX関数を使います

キャプチャ43

つまり、実績、前年、予算の3つのうち、一番高いものと同じグラフ(系列)を追加して、データラベルを追加するのです。

ここから2つ、作業があります

追加したグラフ(系列)は5回目で解説したのと同じ手法で見えなくしましょう(5回目では誤差範囲だけ表示してグラフ自体は非表示にしました。この回では詳細な説明は割愛します)

そして、表示を消したデータラベルの表示を行うのですが、2つの点を注意しましょう!

まず、データラベルの表示の仕方は”セルの値”を選択して、変換して組み合わせた文字列を範囲として選択しましょう!

キャプチャ44

 

そして、表示位置は”外側上”を指定しましょう

キャプチャ45

どうでしょう?こんな感じになりましたか?

キャプチャ37

これで完成です

最後に

隠れた注意点についてですが、変換して組み合わせた文字列(前年比#%・・・)は変なところで改行になっていたり、上のタイトルや凡例などとぶつかったりしないように下のような微調整はいれましょう

・フォントやデータラベルのオブジェクトの幅を調整する

・グラフの位置自体を調整する(通常、下に調整するのが無難)

ではまた次のテーマでお会いしましょう!