Excel。検索値の文字数が異なってもと合致するデータを手早く検索抽出したい。【extract】

Excel。検索値の文字数が異なってもと合致するデータを手早く検索抽出したい。

＜XLOOKUP＋LEFT関数＋ワイルドカード＞

東日本を「EJP」、東京を「TK」というように、検索値が文字列の一部で、しかも文字数が一致していないデータを元に、検索抽出したい場合は、どのようにしたらいいのでしょうか。

例えば、次の表。

C2:C5の地域番号は、ハイフンよりも左側で、地域を区分しています。

ハイフンから左側の文字列と合致する地域名を、A7:B10の表から、検索抽出したいわけです。

VLOOKUP関数でもいいのですが、XLOOKUP関数で数式をつくっていきます。

C2に次のように数式を設定します。

=XLOOKUP(LEFT(B2:B5,2)&"*",A8:A10,B8:B10,"",2,1)

これで、検索値の文字数が異なっても手早く検索抽出することができます。

XLOOKUP関数をつかうことで、オートフィルで数式をコピーしなくても、スピル機能によって、数式がコピー（ゴースト）されますし、絶対参照も不要です。

では、数式を確認してきましょう。

XLOOKUP関数の最初の引数は、「検索値」。

地域番号と合致させるには、ハイフンから左側の文字列が対象になるわけですが、ハイフンから左側の文字数が異なっているため、単純に左から何文字というわけにはいきません。

FIND関数をつかって、ハイフンまでの文字数をカウントして、その数をつかって抽出という方法もありますが、XLOOKUP関数には、一致モードのワイルドカードをつかうことができます。

幸い、「EJ」「WJ」「TK」と左から2文字だけ抽出しても、区分けがつくことがわかります。

そこで、検索値は、左側から指定した文字列を抽出するLEFT関数をつかい設定します。

LEFT(B2:B5,2)&"*"

「&”*”」とワイルドカードを文字結合します。

2つ目の引数は、「検索範囲」。

検索値を元にどこの範囲から検索するのかを設定しますので、

A8:A10

3つ目の引数は、「戻り範囲」。

表示したいデータは、どの列にあるのかを指定しますので、B8:B10。

4つ目の引数は、「見つからない場合」。

今回は、「””（ダブルコーテーション×2）」と空白にするように設定します。

5つ目の引数は、「一致モード」。

完全一致ではなく、ワイルドカードで検索させますので、「2」と設定します。

最後の引数は、「検索モード」。

データも多くないので、通常通り、先頭から後方に向けて検索させますので「１」。

XLOOKUP関数とLEFT関数。そしてワイルドカードを組み合した方法をご紹介しました。

Excel。読み込んだ郵便番号を、手早く０付き・ハイフンを表示したい【post code】

Excel。読み込んだ郵便番号を、手早く０付き・ハイフンを表示したい

＜TEXT関数＞

データを読み込んでみたら、郵便番号のデータにハイフンがありませんでした。

また、1文字目が０のため、0が表示されず、6桁の数値になっていました。

どのようにしたら、手早く、6桁ならば、「0（ゼロ）」を追加する。

そして、ハイフンを含めて表示することができるのでしょうか。

左から何文字目が…とか考えちゃうところですが、最初に「0（ゼロ）」があろうがなかろうが、セルの書式設定ダイアログボックスを表示して、表示形式の「その他」にある「郵便番号」を設定しちゃえばいいわけです。

または、TEXT関数をつかってもいいですね。

C2には、次の数式を設定しました。

=TEXT(A2,"000-0000")

これで、最初の「0（ゼロ）」が表示されていない場合でも、ハイフンが含まれていない場合でも、対応することができます。

Excel。2023/4/22-4/29にTEXTJOIN関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2023/4/22-4/29にTEXTJOIN関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

4月23日

Excel。

T.DIST関数

読み方は、ティー・ディストで、t分布の左側（下側）累積確率か確率密度を算出します。

4月24日

Excel。

T.DIST.RT関数

読み方は、ティー・ディスト・ライトテールで、t分布の右側（上側）確率を算出します。

4月25日

Excel。

T.DIST.2T関数

読み方は、ティー・ディスト・ツーテールで、t分布の両側確率を算出します。

4月26日

Excel。

TEXT関数

読み方は、テキストで、数値を書式設定した文字列に変換する

4月27日

Excel。

TEXTJOIN関数

読み方は、テキストジョインで、複数の範囲や文字列からテキストを結合する

4月28日

Excel。

TIME関数

読み方は、タイムで、指定した時刻を算出します。

4月29日

Excel。

TIMEVALUE関数

読み方は、タイムヴァリューで、時刻を表す文字列をシリアル値に変換する

Access。テキスト型で設定したフィールドに入力された数値を並べ替えるには。【SORT】

Access。テキスト型で設定したフィールドに入力された数値を並べ替えるには。

＜Access：CLng＞

Excelでは、あまり気にしなくてもいい「型」。

本来データベースでは「型」というのは重要なわけです。

例えば、次のテーブルの出席番号フィールドを昇順で並べ替えてみることにします。

出席番号フィールドは、左揃えになっているように、「短いテキスト型」で設定されています。

作成タブのクエリデザインをつかってクエリをつくっていきます。

出席番号フィールドに昇順の並べ替えを設定して、実行してみましょう。

希望しているように、昇順になってくれませんでした。

これは、「テキスト型（短いテキスト型）」が原因なわけですね。

出席番号フィールドが「数値型」ならば、問題なく並べ替えができました。

テーブルの出席番号フィールドの型を変えると、すでにリレーションシップを設定していれば、「型」を変更することはできません。

そこで、CLng関数をつかうことで、疑似的に「数値型」として扱うことができます。

出席番号フィールドを演算フィールドとして設定していきます。

並べ替え: CLng([出席番号])

並べ替えの設定の「昇順」

実行してみましょう。

出席番号は、希望通りに昇順で並べ替えることができました。

このCLng関数は、「数値型」に変えて処理してくれる関数です。

「短いテキスト型」だったものをCLng関数で、疑似的に「数値型」することができます。

これで、数値型と同じように並べ替えることができました。

Excel。順位を算出するRANK.EQ関数。離れた範囲も含めた順位を求めたい。【rank】

Excel。順位を算出するRANK.EQ関数。離れた範囲も含めた順位を求めたい。

＜RANK.EQ関数＞

順位を算出することができる、「RANK.EQ関数」

通常、順位を算出したい場合は、順位の基準となる数値の列を範囲選択するわけですが、離れた飛び地の場合、どのようにしたらいいのでしょうか。

C2の数式は、

=RANK.EQ(B2,(B5:B9,D5:D9),0)

このように数式を設定すると、算出することができます。

どこが問題なのかというと、2番目の引数である「参照」です。

今回使用するRANK.EQ関数の引数は、

1番目は、数値で、これは、ここで指定した値は、何位なのかを知りたい数値を選択します。

よって、「B2」

2番目が、参照。この参照が全体の範囲です。

3番目が、昇順か降順での順をきめる「順序」です。

点数がいい順での順位を算出したいので、「0」と設定します。

C2には、B2の得点がA4:D9の表内の得点全体の中で、何位なのかを算出したいわけです。

注目は、得点がB5:B9とD5:D9と離れていることです。

離れているところを範囲選択する場合、Ctrlキーをつかって範囲選択をします。

すると、数式には、

B5:B9,D5:D9と「,（カンマ）」で区切られて入力されます。

そうなると、Ctrlキーをつかった離れた範囲選択先が、3番目の引数になってしまうので、エラーが発生します。

SUM関数でも離れたところが範囲だと

=SUM(A1,C1)

と数式を作成しますよね。

そのため、離れた範囲を設定する場合には、2番目の引数は、「()カッコ」で囲ってあげる必要が発生します。

ちょっとしたことですが、離れたところが「,（カンマ）」で区切られることから「()カッコ」が必要なことを知らないと、エラーが発生しますので、ちょっと覚えておくといいかもしれませんね。

Excelのショートカットキー。Ctrl+@の時刻表示などを紹介【shortcut】

Excelのショートカットキー。Ctrl+@の時刻表示などを紹介

＜Ctrlキー+記号 その2＞

Excelのショートカットキー

作業効率もUPする、知っていると便利なショートカットキー。

今回は、Ctrlキーと記号の組み合わせを紹介。

Ctrl＋.

範囲選択時、範囲選択内の四隅を順番に移動する

Ctrl＋>

左のセルの内容を右にコピーする

Ctrl＋>

テキストボックス内：フォントサイズを拡大

Ctrl＋<

上のセルを下にコピーする

Ctrl＋<

テキストボックス内：フォントサイズを縮小

Ctrl＋@

表示形式で時刻に変更します。

Ctrl＋{

数式で直接参照しているセルをすべて選択します。

参照元

Ctrl＋}

数式で直接参照している数式が入力されたすべてのセルを選択します。

参照先

Excel。セル内に指定した文字が含まれていたら、行全体を手早く塗りつぶしたい【contains】

Excel。セル内に指定した文字が含まれていたら、行全体を手早く塗りつぶしたい

＜条件付き書式＋FIND関数＞

指定した文字が含まれるセルだけを塗りつぶしするならば、条件付き書式のセルの強調表示ルールにある「文字列」をつかって、設定すれば手早く設定することができます。

では、指定した文字が含まれていた場合、セルではなくて、行全体を塗りつぶすにはどのようにしたらいいのでしょうか。

今回は、B列の住所に「横浜市」が含まれていたら、行全体を塗りつぶすようにします。

条件に一致したら、行全体を塗りつぶしたいわけですから、「条件付き書式」をつかっていきます。

あとは、どのような条件を設定するかを考えていきます。

A2:B6を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックします。

「次の数式を満たす場合に値を書式設定」のボックスに数式を設定します。

=FIND("横浜市",$B2)

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックすれば完成です。

設定した数式は、FIND関数をつかった数式です。

FIND関数は、指定した文字列が含まれているかどうかを判断する関数です。

「=FIND("横浜市",$B2)」

と設定することで、横浜市が含まれていたら＝TRUE。

含まれていなければ＝FALSEを返します。

TRUEならば条件が満たされているということになりますので、塗りつぶす対象になるというわけです。

なお、「$B2」と列を固定した複合参照にすることで、行全体を対象にすることができます。

複合参照をつかうことで、行全体を塗りつぶすことができるというわけですね。