カテゴリー別アーカイブ: 基本

エクセル文字列操作関数の使い方

エクセルの文字列関数であるFIND関数やMID関数、LEFT関数

そして、論理関数のISERROR関数を使い、住所を都道府県単位とそれ以降の表示に分ける方法をご紹介します。

その前に、それぞれの関数を簡単に説明しておきましょうね。

FIND関数は文字列から任意の文字の位置を教えてくれるエクセルの文字列操作関数。

引数には、探す文字・検索対象文字列・検索開始文字(省略すると頭から検索します)の順番で記述します。

例えば、「秋田県鹿角市花輪字馬場○○番地」という文字列がA2に入力されていて、この中から「県」という文字の位置を調べたい時は、任意のセルに=FIND(“県”,A2)とすると3の数字を返します。

次にMID関数ですが、こちらは文字列の指定した場所から、指定した数だけ任意の文字を抜き出す文字列操作関数。

例えば、先ほどの文字列の先頭から「秋田県」の3文字を取り出したいなら、任意のセルに=MID(A2,1,3)、

7番目から出てくる「花輪」の2文字を取り出したいなら、=MID(A2,7,2)といった具合にします。

そして、LEFT関数。

こちらは文字列の最初から(つまり左からなのでレフトな訳)指定した文字数だけ取り出す文字列操作関数。

例えば、先ほどの文字列から「秋田県鹿角市」までを取り出したかったら、任意のセルに=LEFT(A2,6)と記述します。

で、最後に論理関数のISERRORですが、これは検査対象がエラー値かどうか見分ける関数で、エラー値ならばTRUE、そうでなければFALSEを返します。

私は単独で使う機会はほとんどなく、もっぱらFIND関数や同じ論理関数のIFとの抱き合わせで使っています。

この関数の使用例については、本題の説明で具体的に取り上げますから参考にしてください。

前置き長くなりましたが、ここから本題に入ります。

文字列操作関数を使う場合に大切だと思われるのは、文字の並び具合の特徴に着目すること。

しばらく眺めて、どうしたら抽出したい文字列を得られるか考え、使用する関数を選びます。

今回は都道府県とそれ以下の住所を分離して表示すること。

なので、まず、どうしたら都道府県単位までを抽出できるかを考えます。

日本の住所は都道府県から始まりますが、都は東京都、道は北海道のイチイチ、そして府は大阪府と京都府の2つだけです。

そこで私はまずこう考えました。

(ここからは添付の表を見ながらお読みください。クリックすると拡大します。)

B列の住所の文字列から「県」を探して、

エラー値(TRUE)だった場合は、東京都・大阪府・京都府・北海道に共通する都道府の区切りの位置3

FALSEの場合は「県」の位置

を住所の隣のC列に表示。

例えば、国会議事堂の場合、C3に次の式を入力。

=IF(ISERROR(FIND(“県”,B3)),3,FIND(“県”,B3))

そして、MID関数を使って住所(B列)の先頭からC列で抽出した位置までを抜き出す。

国会議事堂の場合、D3に次の式を入力。

=MID(B3,1,C3)

そして都道府県以下を表示するE列には

国会議事堂の場合、E3に

=MID(B3,C3+1,99)

そして、C3,D3,E3を選択してフィルハンドルを下にドラックすると、ワオ!通天閣までうまく表示されました。

と思いきや・・・

「まてよ、これじゃあ、仮に東京都○○区××県△番地だったら県が出てくる9文字までを拾ってしまうなあ・・・」

で実際にやってみたら案の定へんてこなことに・・・→D8参照

ということで、思いついたのが「県名は長くても4文字」ということ。

つまり、B列の文字列の右から4文字までをFINDの検査対象にすれば、上記のような不都合が起こらないことに気づきました。

つまり、上記計算式

=IF(ISERROR(FIND(“県”,B3)),3,FIND(“県”,B3))

のFIND(“県”,B3)を

FIND(“県”,LEFT(B3,4))

に置き換えると上記のような不都合はほとんど解消されます。

都道府のすぐ後ろが県だったら話別ですが・・・ま、その時はその時で手作業で直すのがいいと思います。

作業効率を図るための関数、めったに起こらないイベントに時間を取られすぎるのはナンセンス・・・ですね。

関数CONCATENATEの使い方

関数CONCATENATEの使い方を紹介します。

図は縮小しているので見づらいと思いますので、クリックして分離した後で拡大(+マークでます)してご覧ください。

このエクセル関数は簡単に言うと「一つ一つの部品をくっつける」関数です。

使う目的はいろいろ考えられると思いますけど、私は例えばこんな風にCONCATENATE関数を利用しています。

サイトを作る場合、サイドバーにメニューを表示することがあります。

例えば、料理のホームページ(abc.com)を作っていて、サイドバーのメニューから「きんぴらの作り方」(abc.com/kinpira.html)のページにリンクを張るときは、

下の表の中にA2にあるリンクタグをメニューバーに組みます。

この時、手打ちでタグを作ってももちろんいいわけですが、サイトを構成するページはきんぴらの作り方だけではちょっと物足りない・・・

魚のさばき方、温泉卵の作り方・・・などなどいろいろあるはず。

これを一つずつ手打ちで作るのってとっても面倒・・・と私は思うのです。

で、エクセルのCONCATENATE関数を利用すると、そのほかのメニューページのリンクタグも一瞬のうちにできちゃうのです。

どうするかというと・・・

リンクタグの中で変化するのは個別ページのURLと名称。

なので、この部分を空セルにして、変化しないタグをA2から切り取って、以下のようにA4、C4、E4に配置し、

F4には

=CONCATENATE(A4,B4,C4,D4,E4)

と関数を組み込みます。

=conと入力すれば、おそらくCONCATENATE関数が候補の最初に現れるはず・・・

後は、コントロールキーを押しながら、A4 B4 C4 D4 E4を一個ずつクリックして最後にエンターキー押せばいいです(最後の)はつけなくてもOK)。

B4~B6に、

kinpira、sakanasabaku、onsentamago

D4~D6に、

きんぴらの作り方、魚のさばき方、温泉卵の作り方

とそれぞれデータを入力します。

で、最後にA4、C4、E4、F4のセルを下にフィルするとこの通り。

このようにCONCATENATE関数を利用するとスピーディにリンクタグが作れます。

実際は、A4からF4までを選択して下にフィルしてから、データを入力した方がもっと手っ取り早いのですが・・・

ま、いろいろ試してみてください。