覚えて便利なエクセルテクニック その2 こんな操作も「関数」でこなそう!

2012/11/30掲載

エクセルといえばなんといっても強力な「関数」、これを使わない手はありません。ただ一口に関数といっても、数学的な関数ばかりではありません。エクセルにおける関数は、セルに入力された数字や文字列をもっと幅広く扱うことができます。また、数学が得意不得意ということとも特に関係なく使用することができます。そんな便利なエクセル関数を覚えてエクセル操作をぐっと上達させましょう。
※なお、今回ご紹介するのは、エクセル2010での操作です。他のバージョンのエクセルでは、操作が少し異なる場合があります。あらかじめご了承ください。

  1. 文字列の分割や結合を思いのままに操る

  2. 日付に関する計算も関数なら簡単 >>

  3. お金の計算はエクセル関数の真骨頂 >>

1.文字列の分割や結合を思いのままに操る

では早速「文字操作」をこなす関数から使ってみましょう。
セルに記載された文字列から一部を切り出したり、反対に別の2つのセルに入力された文字列を1つのセルに結合したり、そういった操作をこなすことができる関数です。

氏名から姓だけを取り出す「LEFT関数」「FIND関数」

1つのセルに姓名が入力されている氏名データから、姓だけを別のセルに取り出してみましょう。

あらかじめ姓名が1つのセルに入力されたデータを人から渡されたとき、また、データを作成したときは1つのセルに姓名を入力しておいてよいと考えていたのが、後になってやはり姓名を別のセルに入力した方がよかったことが判明することもありますよね。そんな場合でも、エクセルの関数を使って後から姓と名を別のセルに分解することができます。

日本名は姓が必ず最初(左)に来ます。文字列を左から○文字切り出す関数が「LEFT」関数です。まず基本的な使い方からやってみましょう。

画像:イメージ図

●姓が2文字の場合
日本人の名前では姓は2文字が多いです。LEFT関数を使用して「左から2文字を切り出す」という指定をすれば、姓の2文字を別のセルに切り出すことができます。姓を表示したいセルに下の関数の構文を入力します。

=LEFT(文字列,文字数)

この「文字列」とは文字列(ここでは姓名データ)が入力されているセルを指します。

B1セルに

=LEFT(A1,2)

と入力しEnterキーを押してみましょう。すると「沖田」だけが表示されました。後は関数を姓名分だけコピーすればできあがりです。

画像:イメージ図 画像:イメージ図

もちろん関数ウィザードを使用して構文を作ることもできます。

[数式]リボンから[関数の挿入]をクリックします。LEFT関数は「文字列操作」の中にあります。

画像:イメージ図

上で紹介したように「文字列」には「A1」を「文字数」には「2」を入力して[OK]をクリックすればできあがりです。

画像:イメージ図 画像:イメージ図

●姓が2文字ではない場合
さて、前述の方法では2文字ではない姓の場合は対応できません。ここでデータを確認すると、姓と名の間にスペースがあります。そこでスペースを探し、その左の文字列を切り出す、という関数ができれば姓だけを取り出すことができます。

まずFIND関数を使って、スペースのある位置を探してみましょう。FIND関数は以下のように使います。

=FIND(検索文字列,対象,開始位置)

「検索文字列」とは探し出すもの、つまりここではスペースです。「対象」はそのスペースを探す対象、つまり姓名の記入されたセルです。そして「開始位置」はセルの中の1番左つまり「1文字目」です。

以下のような式を作りましょう。もちろん関数ウィザードを使っても構いません。

=FIND(" ",A1,1)

これで各姓名でスペースが左から何番目にあるか、が表示されます。なお、スペースは「全角」か「半角」かを入力データどおりに正しく指定する必要があるので注意してください。

画像:イメージ図 画像:イメージ図

では最後に、冒頭のLEFT関数と併せてみましょう。セルのスペースの位置を数えてその左を切り出す、そんな関数ができれば姓の文字数によらず切り出すことができますよね。

以前LEFT関数で文字数を「2」と指定しましたが、この部分に上のFIND関数で作った構文をそっくりあてはめます。するとこんな構文になります。B1セルに記載します。

=LEFT(A1,FIND(" ",A1,1))

同じように、全姓名分関数をコピーしてみましょう。これで姓が3文字の人も1文字の人も上手く切り出すことができました。

住所から都道府県名だけを取り出す「LEFT関数」「FIND関数」「MID関数」「IF関数」

続いて住所で同じような操作をしてみましょう。1つのセルに入力された住所データから、都道府県名部分のみを別のセルに切り出します。使用するのは姓名と同じLEFT関数とFIND関数です。

画像:イメージ図

まず考え方です。県名は2文字と3文字があります。姓名のときはスペースを探しましたが、代わりに「県」という文字を探し、その左側を切り出します。するとB1はこのような構文になります。

=LEFT(A1,FIND("県",A1,1))

次に考えないといけないのは、「都」「道」「府」をどうするかです。当然上の構文のままではエラーになってしまいます。ここでデータをよく見ると、東京都、北海道、大阪府、京都府、はいずれも3文字です。県のときは神奈川県などいくつか4文字があります。そこでこんな構文が作れればよいわけです。

もし4文字目が「県」なら左から4文字を切り出す
もし4文字目が「県」でないなら左から3文字を切り出す

これで解決です。それには2つの関数を組み合わせます。

MID関数:文字列の指定した位置から指定した文字数を取り出す
IF関数:ある条件に対して、そうであるとき、そうでないとき、の結果を表示する
画像:イメージ図

まずMID関数を使って4文字目を取り出してみましょう。

=MID(A1,4,1)

とB1のセル記載します。A1セルの左から4番目の文字を1文字取り出す、という意味です。すべての住所にこの関数をコピーしたのが右のイメージです。B列にA列の左から4番目の文字が記載されていますね。

画像:イメージ図
IF関数とMID関数、LEFT関数を組み合わせて都道府県名を切り出すことができました。

続いてこの関数を条件として、IF文を作ります。条件、そうであるとき、そうでないとき、をカンマで区切って順番に記述していきます。そうであるとき、そうでないとき、の部分をいずれも上で使った「LEFT関数」の構文で記述します。

=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))

閉じるカッコの数などを間違えないよう注意して作ってください。

別のセルに入力された姓と名を結合する「CONCATENATE関数」

今度は反対に、2つのセルに入っている姓と名を1つのセルに結合してみましょう。ここまでの説明とは正反対ですが、これもまたよく出くわすシーンではないでしょうか?

画像:イメージ図 画像:イメージ図

使用するのは「CONCATENATE」という関数です。関数ウィザードでは、やはり「文字列操作」にあります。A1とB1を結合してC1に入れるので、ウィザードの「文字列」にはセルを順番に入れましょう。

画像:イメージ図 画像:イメージ図

姓と名の間にスペースを入れて結合したいときも簡単です。CONCATENATEはいくつもの文字列を結合できるので、A1とB1の間にスペースを入れて結合すればよいのです。

■表示価格は、特に記載がある場合を除きすべて税抜です。 ■請求書などの発行有料化 および 無料でのご利用手続きなどについて、詳しくはこちら