さて、「一昔前エクセルシリーズ」も現在は、「エクセル2016」以降で追加された関数を紹介しております。
前2回は条件分岐の関数だったので、今回は久々に
文字列を操作する新しい関数を見ていきたいと思います。
「エクセル2016(Office365)」または「パッケージ版のエクセル2019」が登場する前までは、文字列を連結する関数と言えば
「CONCATENATE」関数
でした。
「CONCATENATE」関数の使い方は簡単で、連結したい文字列を引数に指定すればいいだけです。
こんな使い方があるかどうかは分かりませんが、
入力規則と「CONCATENATE」関数と条件付き書式と前回の「IFS」関数を使って、
次のようなシートを作ってみました。
このシートの流れは以下のようになります。
有名な野球選手の氏名が入力されたA列・B列の組み合わせに間違いがあるので、
D列に入力されている名字に対応する名前をE列に入力してもらいます。
ただ、E列には文字を入力してもらうのではなく、5択のリストを作成してそこから一つ正解を選択してもらうようにします。
G列には、D列とE列を「CONCATENATE」関数でつなげた文字を表示します。
つまり名字と名前を繋げるわけですが、間には「半角スペース」を入れます。
H列には、G列の文字が合っているかどうかを判断するために「IFS」関数を使います。
正解か不正解かを文字で表示するのですが、正解なら”青字”で不正解なら”赤字”に装飾します。
これは、条件付き書式を使います。
それでは、順番に見ていきましょう。
E列にリストを作成する
まずは、E列に名前の一覧をリストにします。
これは、エクセルを長年使ってきた方ならおなじみの「入力規則」を使います。
E列を5行選択して、「データ」タブから「入力規則」をクリックします。
一覧から「データの入力規則」をクリックします。
「データの入力規則」ウィンドウが開きます。
「設定」タブの[入力値の種類]から「リスト」を選択します。
[元の値]を「,(半角カンマ)」で分けながら上のように入力します。
ドロップダウンボタンが表示されます。
クリックすると、作成した一覧がリスト候補として表示されていますね。
E列の5行すべてがリストから5つの候補を選択する形式に変わりました。
G列に名字と名前を繋げる
G列には、D列の名字とE列で選択された名前を「半角スペース」で繋げます。
ここで、メインの「CONCATENATE」関数を使います。
「CONCATENATE」関数は、引数に指定した文字を単に繋げるだけです。
上のように、セル「D1」に入力された文字とセル「E1」に入力された文字の間に「半角スペース」を入れる場合は、「” “」のように、本来文字列を表現する際にダブルクォーテーションの間に入れる文字の代わりに、キーボードの「スペースキー」で半角スペースを入れます。
フィルハンドルをドラッグして、セル「G5」まで数式をコピーします。
H列にIFS関数で条件分岐を行う
H列には、G列に表示された氏名が正しいかどうかを「IFS」関数で判断します。
論理式は3つだけです。
・E列に何も選択されていない場合
・氏名が合っている場合
・氏名が間違っている場合
最初に「セルG1が”王 “」だった場合を記述します。
この「”王 “」というのは、「CONCATENATE」関数で
【=CONCATENATE(D1,” “,E1)】
としていますので、もしセル「E1」に何も選択されていない場合は、「CONCATENATE」関数によって「D1」と「” “」が接続された文字が返されます。
つまり、「”王 “」という状態は、リストから名前が選択されていないという意味になりますので、
セル「H1」には、空文字を返すようにします。
したがって、「IFS」関数の「値が真の場合1」には「””」を設定します。
次に、セル「E1」に「貞治」が選択され、「セルG1が”王 貞治”」となった場合は、氏名が合っていますのでセル「H1」には「正解」と表示するようにします。
「ISF」関数の「値が真の場合2」には「正解」と入力します。
最後は、セル「E1」が「貞治」以外の場合ですが、もちろん氏名が間違いですので、セル「H1」には「不正解」と表示されます。
上の画面に表示されていませんが、「IFS」関数の「値が真の場合3」には「不正解」と入力します。
ここもフィルハンドルをコピー・・でいいのですが、
比較する名前がセル「H1」からセル「H5」でそれぞれ違いますので、コピーした後に比較する氏名をそれぞれ変更していきます。
例えばセル「H2」であれば、以下のような関数を作成します。
セル「H3」以降は、比較する氏名を「鈴木一朗」「松井秀喜」「大谷翔平」となるように関数式を変更してください。
H列に条件付き書式で文字の色分けをする
最後に、セル「H1」からセル「H5」までの”正解”、”不正解”の文字を色分けしてみたいと思います。
正解なら「青文字」、不正解なら「赤文字」という具合です。
そのために、各セルに「条件付き書式」を使います。
セル「H1」からセル「H5」までを範囲選択し、「ホーム」タブの「条件付き書式」をクリックします。
表示された一覧から「新しいルール」をクリックします。
[ルールの種類]から「指定の値を含むセルだけを書式設定」を選択します。
[ルールの内容]を上の画面のように
「セルの値 次の値に等しい 正解」
となるように編集します。
その下の「書式」ボタンをクリックして、文字の色を青に設定します。
セル「E1」で正しい回答を選択すると、セル「H1」には「正解」と表示されますが、
その文字色は「青色」となっています。
同様に不正解の場合の「赤色」も設定します。
セル「H1」からセル「H5」までを範囲選択し、「ホーム」タブの「条件付き書式」をクリックします。
表示された一覧から「新しいルール」をクリックします。
上の画面のように、セルの値が「不正解」であれば書式の文字色を「赤色」に設定します。
セル「E1」に間違った答えが入力された場合は、セル「H1」に「不正解」と表示され、その文字色は「赤色」となっていますね。
これまでに作成してきた条件付き書式のルールは、一覧で確認できます。
セル「H1」からセル「H5」を範囲選択して、「ホーム」タブの「条件付き書式」から「ルールの管理」をクリックします。
現在作成されているルールが一覧で表示されます。
各ルールを選択して、「ルールの編集」ボタンをクリックすれば、”文字色を変える”、”ルールを適用するセル範囲を変更する”などの処理が簡単にできます。
総括
メインの「CONCATENATE」関数の操作はほとんどなく、エクセルのシート操作が大半となってしまいました（笑）
リストの氏名は野球を全く知らない人でも、”この名前だったら大丈夫だろう”という人を挙げてみました。
バットマンとしては松井秀喜より落合博満の方を推したかったんですけどね。
氏名の正解・不正解がそもそも分からない・・
という方は、「野球 名字(長嶋など)」等で検索してみてください。
文字を繋げるメインの話とは全然繋がりのないお話なので、この辺でやめておきます。
「CONCATENATE」関数は上で見てきたように、セルに入力されている文字列を繋げるような使い方が一般的です。
逆を言えばそれ以外に用途がない、という意味でもありますね。
また、文字を繋げるだけなら関数なんかわざわざ使わなくても・・
と思った方も正解です。
文字連結の「&(アンパサンド)」を使っても結果は以下のように同じになるからです。
関数を使うより見た目がすっきりするのは、「&」の方でしょう。
さて、そんな「CONCATENATE」関数ですが、今後消えていく運命の関数であり、後継となる関数がエクセル2019(Office365版は2016)以降で登場しています。
その名も「CONCAT」関数と言って、「CONCATENATE」関数より少し名前が短くなりました。
「CONCATENATE」関数にはなかった、”ある操作”で文字を連結できるようになっています。
これまで、「CONCATENATE」関数を使ってきた方は、引数指定が同じである「CONCAT」関数に順次置き換えていった方がいいでしょう。
次回は、その「CONCAT」関数と「TEXTJOIN」関数という2つの新しい文字連結を見ていきたいと思います。
※今回のシートを編集していく前段階のファイルは、以下からダウンロードできます。
ご利用ください。
