トップ > スキル : アプリケーション > Excel(エクセル) > 基礎編(関数をマスターする)

Excel

関数をマスターする

文字列の編集

エクセルは、数値の計算だけではなく、文字の編集や加工も行える関数が用意されています。

◆ 同じ文字数の「部名」を別セルに分ける(LEFT、SUBSTITUTE関数)

=LEFT(文字列,文字数)

文字列の左端から、文字数で指定した数の文字を取り出します。

=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)

文字列内で間作文字列を探し、置換文字列に置き換えます。該当する文字が複数含まれている場合に、何番目の文字を置き換えるのかを置換対象に数値で指定することも出来ます。置換対象は省略可能です。

セルの文字を適当な位置で分割し、別のセルに分けたいケースがあります。文字列の先頭から文字を取り出すのであれば、LEFT(レフト)関数を使います。この関数は、文字列の左端から、指定した文字数分だけを取り出します。

セルB3に「=LEFT(A3,3)」と入力し、文字列の左端から3文字分取り出します。その数式を下のセルにコピーします。

部名が取り出されたら、次に部名以下の課名を取り出します。これは、部課名から部名だけを取り除けばよいので、SUBSTITUTE(サブスティテュート)関数を使います。引数「文字列」に部課名のセルA3を、「検索文字列」に部名の「B3」を指定し、「置換文字列」に空欄を意味する「""」を指定します。セルC3に「=SUBSTITUTE(A3,B3,"")」と入力し、その数式を下のセルにコピーします。


◆ 異なる文字数の「部名」を別セルに分ける(LEFT、FIND、SUBSTITUTE関数)

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

文字列の左端から、文字数で指定した数の文字を取り出します。

部名の文字数がそれぞれ異なる場合、重要なのは部課名の中から「部」の位置を探すことです。「部」が左端から何文字目にあるか分かれば、左端からその文字数分が部名になります。このような時に使うのがFIND(ファインド)関数です。この関数は、引数に「検索文字列、対象」を指定することで、検索文字列が先頭から何文字目にあるかの数値を調べられます。

セルB3に「=LEFT(A3,FIND("部",A3))」と入力して、下のセルにコピーします。
課名を別のセルに表示するのであれば、SUBSTITUTE関数を使います。セルC3に「=SUBSTITUTE(A3,B3,"")」と入力して、下のセルにコピーします。


◆ 都道府県と市区町村以降を別セルに分ける(IF、MID、LEFT、SUBSTITUTE関数)

=MID(文字列,開始位置,文字数)

文字列内の指定した位置から、文字数で指定した数の文字を取り出します。何文字目以降を取り出すかを、開始位置として数値で指定します。

住所録などで、1つのセルに入力されている住所のデータを「都道府県」と「市区町村以降」に分けたいときがあります。都道府県を取り出す方法を考えると、都道府県は必ず3文字か4文字で構成されています。具体的になると、「神奈川県」「和歌山県」「鹿児島県」の3県のみが4文字で、その他の都道府県はすべて3文字になります。
そこで、IF関数を使って4文字目が「県」であるかどうかを判定します。もし4文字目が「県」なら、住所の左端から4文字分を取り出し、そうでなかったら左端から3文字分を取り出せば、都道府県のみの部分を分割できます。
住所の4文字目を取り出して「県」かどうかを調べる時に使うのが、MID(ミッド)関数です。住所の4文字目から1文字を取り出して判定します。

セルB3に「=IF(MID(A3,4,1)="県",LEFT(A3,4),LEFT(A3,3))」と入力します。これは、セルA3の文字列の4文字目の1つが「県」であれば、セルA3の文字列の左端から4つを取り出し、そうでなければ左端から3つめを取り出すことになります。
後は、下のセルにコピーします。市区町村以降は、SUBSTITUTE関数を使えば、簡単に取り出せます。

全角⇔半角など文字種を変更する

◆ 半角文字を全角文字に変換する(JIS、ASC関数)

=JIS(文字列)

文字列内の半角文字を全角に変換する

=ASC(文字列)

文字列内の全角文字を半角に変換する

セル内の半角文字を全角に置き換えたり、またはその逆に直したい時に利用するのが、JIS(ジス)関数とASC(アスキー)関数です。例題ではまず、半角を全角に置き換えたいので、セルC3に「=JIS(B3)」と入力します。後は、下のセルにコピーします。

逆に全角文字を半角にする例題のセルC3に「=ASC(B3)」と入力し、下のセルに関数式をコピーします。なおこの例題では、変換前のB列を削除すると、関数式を入力したC列がエラーになります。B列を削除するのであれば、変換後はC列を関数式から「値」に変更します。


◆ 英文の小文字・大文字を自在に変換する(PROPER、UPPER、LOWER関数)

=PROPER(文字列)

文字列内の英字の先頭文字だけを大文字に、2文字目以降を小文字に変換します。

=UPPER(文字列)

文字列内の小文字を大文字に変換します。

=LOWER(文字列)

文字列内の大文字を小文字に変換します。

大文字の英字で表示されている文字列の先頭文字だけを大文字にし、2文字目以降を小文字に変換する場合は、PROPER(プロパー)関数を使います。例題のセルC3に「=PROPER(B3)」と入力します。引数の中は、変換したい文字があるセル番地を指定します。他に小文字をすべて大文字に変換したい場合はUPPER(アッパー)関数を使い、逆に大文字をすべて小文字に変換する場合はLOWER(ロウワー)関数を使います。なお、これらの関数は英字が対象なので、日本語に関しては変換されません。


◆ 漢字のフリガナを自動表示する(PHONETIC関数)

=PHONETIC(範囲)

範囲に指定したセルに入力されている漢字のひらがな情報を取り出します。

氏名などの漢字からフリガナを取り出して表示する場合は、PHONETIC(フォネティック)関数を使います。例題の「顧客名簿」の氏名からフリガナを取り出すのであれば、セルB3に「=PHONETIC(A3)」と入力します。あとは、下のセルにコピーすれば氏名のフリガナがすべて表示されます。エクセルは、漢字入力の際にキーボードで入力した“変換前のカナ”を自動記録しているので、このようなフリガナを取り出すことができます。ただし、本来と違う読み方で入力した場合は、そのまま表示されるので注意します。

また、ひらがなをカタカナに変換したい場合もPHONETIC関数を使います。
逆にカタカナをひらがなに変換したい場合は、一工夫必要です。まず基になるカタカナのセルを選択しメニュー[書式]-[ふりがな]-[設定]を選択します。

[ふりがなの設定]ダイアログボックスの[ふりがな]タブを選択し、[種類]を「ひらがな」に設定すると、PHONETIC関数で取り出された「ふりがな」がひらがなに変換されます。


◆ 常に「連番」が表示できるようにする(ROW、COLUMN関数)

=ROW(範囲)

指定したセルの行番号を調べます。引数を省略すると、式を入力したセルの行番号を求めます。

=COLUMN(範囲)

指定したセルの列番号を調べます。引数を省略すると、式を入力したセルの列番号を求めます。

連番が付いた一覧表で、データを削除した時に自動的に番号が振りなおされて、欠番がないように常に連番となるような仕掛けを実現したい時は、ROW(ロウ)関数を使います。
シートの行番号は、行データを削除しても番号が繰り上がって常に連番で表示されます。つまり、この行番号をセルに取り出せれば、常に連番に出来ます。

まず、連番が付いた先頭の行の行番号を調べます。例題では、開始セルがA3なので、行番号は「3」になります。一覧表ではここがデータの開始行なので、セルA3から順に1、2、3…と数えたいので、セルA3から「2」を引けば、セルA3が開始「1」になります。今回は、「1001」から始めたいので、「1000」を加えます。
セルA3に「=ROW()-2+1000」と入力して、下のセルにコピーします。
なお、列番号を取り出したい場合は、COLUMN(コラム)関数を使います。列番号はシート上では「A、B、…」と表示されますが、この関数で取り出すとA列が1、B列が2、…となります。


◆ 「&」で結合した「日付」や「数値」の書式を整える(TEXT、&、TODAY関数)

=TEXT(値,表示形式)

値に特定の表示形式を指定した上で、文字列に変換して表示します。表示形式を指定する記号を「"」で挟み、引数「表示形式」に指定します。

=値1&値2

値1、値2に指定した文字列やセルの値を結合し、1つの文字列を作ります。

引数に「参照文字列」を指定すると、文字列として入力したセル番地や範囲名を計算式で参照できる形に変換できます。そこでまず、「第4四半期計集約」シートのB2からD2までの項目名を10月、11月、12月のようにシート名と同じ名前にします。

文字列同士を結合したり、数値同士を結合して1つの文字列にする時には、「&」(アンド)演算子が使われます。しかし、日付と「現在」を&演算子で結合するとおかしな表記になります。例えば、「=TODAY()&"現在"」と入力すると「39566現在」というように表示されます。結局は&演算子を使って結合すると、結果的に思った表示が出来ない場合があります。セルの表示形式を設定するTEXT(テキスト)関数を使うと&でつないだ時でも適切な形で表示できます。

セルD3に「=TEXT(TODAY(),"yyyy/m/d"&"現在")」と入力します。また、金額の場合も同様に行います。セルE12に「=TEXT(E10,"#,##0"&"円也")」と入力します。これは、金額の数値に3桁区切りのカンマを付けて、最後に「円也」という文字列を追記させています。単に、&を使ってつなぐと書式が消えると思ってください。


◆ 電話番号のカットとハイフンを自在に変換する(&、SUBSTITUTE関数)

例題のように、「-」(ハイフン)で区切られた電話番号をカッコ付きの表記に変更したい場合、SUBSTITUTE(サブスティテュート)関数を使います。

この関数は、ある文字列内の指定した文字を、別の文字に置き換えるものです。しかもその際、左から見て何番目に出現する文字を置き換えるのかを指定することが出来ます。例題のセルA3にある電話番号の文字列にある最初の「-」を「)」に置換します。また、先頭には「(」をつなげるために&を使います。セルB3に「="("&SUBSTITUTE(A3,"-",")",1)」と入力して、下のセルにコピーします。

また、逆にカッコ付きの電話番号をハイフンを使った形に変換する場合は、一工夫します。まず、「(」を「)」に置換してから、さらに「)」を「-」に置換するようにします。セルB3に「=SUBSTITUTE(SUBSTITUTE(A3,"(",""),")","-")」と入力します。あとは、下のセルにコピーします。

文字の削除や配置の変換を一気におこなう

◆ 2つのセルのデータをつなげて2行で表示する(&、CHAR関数)

=CHAR(数値)

引数「数値」に指定した文字コード番号に該当する文字を求めます。

例題の「顧客名簿」で、A列とB列の文字列をC列のセルにまとめたいときは、&を使います。しかし、このままだと1行として表示されます。もし2行にしたいときにはCHAR(キャラクター)関数を使います。セル内の改行は、改行文字と呼ばれる特殊な文字が利用でき、改行文字の番号が「10」となります。そこで、セルC3に「=A3&CHAR(10)&B3」と入力すると、A3とB3の値をつなぎ、その間のCHAR(10)が改行を行うという意味になります。よって、住所と電話場号が2行になって表示されます。
なお、この式をセルC3に入力しただけでは改行表示されません。セルを選択し、セルの書式設定画面の「配置」タブで「折り返して全体を表示する」にチェックをつけておく必要があります。


◆ セル内の改行や不要な空白を削除する(CLEAN、TRIM関数)

=CLEAN(文字列)

値に特定の表示形式を指定した上で、文字列に変換して表示します。表示形式を指定する記号を「"」で挟み、引数「表示形式」に指定します。

=TRIM(文字列)

文字列内の先頭や末尾、文字間にある余分な空白を削除します。文字間の空白は1つだけ残ります。

セル内で改行されている文字列を1行で表示する場合は、CLEAN(クリーン)関数を使います。この関数はセル内の印刷できない文字を削除するものです。

セルB3に「=CLEAN(A3)」と入力して、下のセルにコピーします。

また、他のソフトやWebページからの文字をエクセルにコピーした時に、不要な空白が挿入されることがあります。必要ない空白を削除するにはTRIM(トリム)関数を使います。単語間に空白が含まれる場合、その空白を1つだけ残し、残りの空白を削除します。セルB3に「=TRIM(A3)」と入力して、下の行にコピーします。


◆ 縦1列の表を縦3列の表に配置を変更する(INDEX関数)

=INDEX(範囲,行番号,列番号)

範囲の中から、指定した位置にあるデータを取り出します。範囲内の上から何行目か、左から何列目かをそれぞれ、引数「行番号」と「列番号」に指定します。

縦1列に並んだデータを、見やすくするために3列にして表示したい場合、単純な手作業で行うことは面倒です。このような時には、セル範囲の中から指定した行位置や列位置にあるデータを取り出すINDEX(インデックス)関数を使います。

まず、連番を3列並べた表を用意しておく必要があります。セルC2に「1」D2に「2」E2に「3」と入力し、同様にセルC3、D3、E3に4、5、6と入力します。後は、その6つのセルをすべて選択し、オートフィルで下2行までドラッグすると12までの連番が振られます。
次に、セルC8からデータを並べたいので、セルC8に「=INDEX($A$2:$A$13,C2)」と入力します。あとは、この数式を上の連番と同じ形でドラッグすれば、3列となる表が表示されます。