エクセルで住所を入力したときに、都道府県とそれ以降の住所に分けたいときはどうしていますか?

一行ずつ都道府県だけを入力して、もとの住所からは都道府県だけを削除して・・・なんて作業は手間と時間がかかってしかたありませんよね。

そんなときは、いくつかの関数を組み合わせて使えば、手間をかけずに都道府県を分けることができるのです。

手順が多くてむずかしそうにみえますが、考え方はいたって単純です。
操作内容も同じことを繰り返すので思ったよりも簡単です。
この方法は住所以外でも応用できるので、知っておいて損はありませんよ!

住所を都道府県とそれ以降の住所に分ける方法<1>

関数を使ってB列の住所から都道府県と残りの住所を分けてみましょう。
今回はまず、都道府県を表示するところまでを説明します。
(都道府県をのぞいた残りの住所を表示する方法はこちら

 

<完成図>

【考え方】

都道府県は3文字、もしくは4文字なので、LEFT関数で都道府県だけを表示することはできません。

しかし、4文字なのは「神奈川県、和歌山県、鹿児島県」だけなので、住所の頭文字から4番目の1文字目が「県」であるかどうかを判断して、左から4文字表示するか、3文字表示するかを決めます。そうすれば正しく表示することができるのです。(下図参照)

img20160330_00_1

img20160330_00_2

【関数式】

B列の住所の4番目の1文字目が”県”であるならば、
 B列の住所の左から4文字分をC列に表示する
そうでなければ左から3文字分を表示する

というIF関数の式を作ります。

=IF(MID(B2,4,1)=”県”,LEFT(B2,4),LEFT(B2,3))

MID(B2,4,1)=”県”→もし、セルB2の頭文字から4番目の1文字目が”県”である
LEFT(B2,4)→セルB2の左から4文字分を表示する
LEFT(B2,3)→セルB2の左から3文字分を表示する


1.都道府県を表示させたいセル(C2)を選択し、数式バー横の[fx]関数の挿入ボタンをクリックします。

 

2.関数の挿入ウインドウが開くので、[関数の分類(C):]→「論理」、[関数名(N):]→「IF」を選び、「OK」ボタンをクリックします。

 

3.IF関数の引数ウインドウが開きます。論理式欄には、MID関数を使った「都道府県が入った住所の頭文字から4番目の1文字目が”県”である」という式を入力していきます。

img20160330_02_2

 

名前ボックスの(A列の上部にある)▼マークをクリックして「MID」を選びます。もしも「MID」がないときは、下の「その他の関数」をクリックして関数の挿入ウインドウから「MID」を選びます。

すると、MID関数の関数の引数ウインドウが開きます。

↓ ↓ ↓ ↓

 

4.文字列の欄には、都道府県が入っている住所のセル(B2)をクリックします。

img20160330_05

↓ ↓ ↓ ↓

img20160330_05_2

 

5.開始位置欄には4番目の「4」、文字数欄には1文字分の「1」をそれぞれ半角入力します。次に、下の「OK」ボタンはクリックしないで数式バーに表示されている「IF」の文字のところをクリックします。

img20160330_06

↓ ↓ ↓ ↓

img20160330_06_2

 

6.再び、IF関数の引数ウインドウが開くので、論理式欄に「MID(B2,4,1)」と表示されていれば、そのあとに続けて「=”県”」と県以外は半角入力します。

img20160330_07

↓ ↓ ↓ ↓

img20160330_07_2

これで、IF関数の「B2(元住所)の頭文字から4番目の1文字が”県”であれば」という部分(式)ができました。

 

7.真の場合欄には、LFET関数を使った「左から4文字分を表示する」という式を入力します。

真の場合欄をクリックし、3.と同様に、名前ボックスのA列の上部にある)▼マークをクリックして「LEFT」を選びます。もしも「LEFT」がないときは、下の「その他の関数」をクリックして関数の挿入ウインドウから「LEFT」を選びます。

img20160330_08

 

8.LEFT関数の引数ウインドウが開いたら、文字列欄には都道府県が入っている住所のセル(B2)をクリックし、文字数欄には4文字分の「4」を半角入力します。次に、下の「OK」ボタンはクリックしないで数式バーに表示されている「IF」の文字のところをクリックします。

img20160330_09

 

9.再び、IF関数の引数ウインドウが開き、真の場合欄に「LFET(B2,4)」と表示されていれば、次に偽の場合欄をクリックし、7.と同様に名前ボックス▼マークから「LEFT」を選びます。

img20160330_10

↓ ↓ ↓ ↓

img20160330_11

 

10.LEFT関数の引数ウインドウが開いたら、文字列欄には都道府県が入っている住所のセル(B2)をクリックし、文字数欄には3文字分の「3」を半角入力します。これで入力が終わりなので、「OK」ボタンをクリックします。

img20160330_12

 

都道府県だけが表示されました。

img20160330_13

 

11.下のセルにも関数をコピーするために、フィルハンドル(+)をダブルクリックします。

 

下のセルにも都道府県が正しく表示されました。

 

次回は、都道府県が分けられた残りの住所の表示方法をご紹介します。

 

ひとつのセルに何文字入力しているのかを知りたいときに、どのようにしてかぞえますか?

「ひとつ、ふたつ・・・」と一文字ずつかぞえてもいいのですが、文字数が多くなると大変ですよね。

そんなときに便利な関数が、LEN関数です。

LEN関数は、半角文字、全角文字ともに1文字とかぞえます。

◆LEN(文字列)…指定した文字列の文字数をかぞえる

文字数をかぞえる方法(LEN関数)

LEN関数を使って住所の文字数をかぞえてみましょう。

 

1.文字数を表示させたいセル(C2)を選択し、数式バー横の[fx]関数の挿入ボタンをクリックします。

 

2.関数の挿入ウインドウが開くので、[関数の分類(C):]→「文字列操作」、[関数名(N):]→「LEN」を選び、「OK」ボタンをクリックします。

 

3.関数の挿入ウインドウが開くので、文字列の欄に文字数をかぞえる元のセル(B2)をクリックし、「OK」ボタンをクリックします。

↓ ↓ ↓ ↓

 

文字数が表示されました。

 

4.下のセルにも関数をコピーするために、フィルハンドル(+)をダブルクリックします。

 

下のセルにも文字数が表示されました。

 

 

前回、文字の一部分だけを取り出す方法としてLFET関数とRIGHT関数をご紹介しました。

では、同じく文字を取り出したい場合でも、「頭文字から数えて○番目の文字から何文字分かを取り出したい」というときはどのようにすればよいでしょうか。

その場合にはMID(ミッド)関数を使いましょう。

MID関数は、文字の指定した位置から任意の文字数を取り出します。

◆MID(文字列,開始位置,文字数)…指定した文字列の位置から指定した文字数を取り出す

文字の一部分だけを指定した位置から取り出す方法(MID関数)

MID関数を使ってカレンダーのサイズ部分の2文字だけ(A4・卓上・壁掛)を取り出してみましょう。

 

1.サイズを表示させたいセル(C2)を選択し、数式バー横の[fx]関数の挿入ボタンをクリックします。

 

2.関数の挿入ウインドウが開くので、[関数の分類(C):]→「文字列操作」、[関数名(N):]→「MID」を選び、「OK」ボタンをクリックします。

 

3.関数の挿入ウインドウが開くので、文字列の欄に文字を取り出す元のセル(B2)をクリックします。

↓ ↓ ↓ ↓

img20160317_05

↓ ↓ ↓ ↓

img20160317_05_2

 

4.取り出したい文字が、頭文字から数えて6文字目の位置にあるので、開始位置の欄に「6」と半角入力します。

img20160317_06

 

5.取り出したい文字数は2文字(「A」と「4」)なので、文字数の欄に「2」と半角入力し、「OK」ボタンをクリックします。

img20160317_07

 

サイズ「A4」が表示されました。

 

6.下のセルにも関数をコピーするために、フィルハンドル(+)をダブルクリックします。

 

7.このままだと罫線ごとコピーしてしまうので、オートフィルオプションの「▼」をクリックし、「書式なしコピー(フィル)(O)」にチェックを入れます。

img20160317_10

 

下のセルに、罫線はコピーされずサイズだけが表示されました。

 

 

入力してある文字の一部分だけを取り出したいことはありませんか?

たとえば、フルネームで入力してある人名のふりがなを、頭文字から2文字だけにしたいときに、再び2文字だけのかなを入力するのも面倒ですよね。

そのような場合に便利な関数があります。

それはLFET関数とRIGHT関数です。

LEFT関数は文字の左側から指定した文字数を取り出し、RIGHT関数は文字の右側から指定した文字数を取り出します。

◆LEFT(文字列,文字数)…指定した文字列の左側から指定した文字数を取り出す
◆RIGHT(文字列,文字数)…指定した文字列の右側から指定した文字数を取り出す

文字の一部分だけを取り出す方法(LEFT関数・RIGHT関数)

PHONETIC関数でふりがなを表示させ、結果を値に変換したあとのふりがな(←クリックするとPHONETIC関数のページが開きます)を、LEFT関数を使って左から2文字だけのかなを取り出してみましょう。

 

1.ふりがなを表示させたいセル(D2)を選択し、数式バー横の[fx]関数の挿入ボタンをクリックします。

 

2.関数の挿入ウインドウが開くので、[関数の分類(C):]→「文字列操作」、[関数名(N):]→「LEFT」を選び、「OK」ボタンをクリックします。

 

3.関数の挿入ウインドウが開くので、文字列の欄に文字を取り出す元のセル(C2)をクリックそ、文字数の欄に2文字の「2」を入力します。
「OK」ボタンをクリックします。

↓「やまだ たろう」の「やま」を2文字を取り出したいので、文字数には「2」と入力します。

img20160310_07_2

 

かなが2文字で表示されました。

 

4.下のセルにもふりがなを表示させるために、フィルハンドル(+)を下へドラッグします。

 

下のセルも2文字だけ取り出せました。

img20160310_10

 

右側から文字を取り出す場合は、関数の挿入ウィンドウで、[関数の分類(C):]→「文字列操作」、[関数名(N):]→「RIGHT」を選びます。

img20160310_11

 

 

桁の大きい金額の入った表を作ったときに、千円以下の数字は四捨五入したほうが見やすくスッキリする場合があります。

とはいえ、金額をまた入力し直すのは面倒ですよね。

そんなときに、再入力をしなくても桁を省略して表示できる方法があるんです。

今回は、簡単に桁を省略して表示する方法をご紹介しましょう。

金額の下3桁を省略して表示する方法

下の売上表の金額下3桁を省略して表示させてみましょう。

 

1.金額の部分を範囲選択します。

 

2.[ホーム]タブ→[数値]横の斜め矢印のアイコンをクリックします。

img20150406_04

もしくは、選択範囲内で右クリック→「セルの書式設定(F)」を選択します。

 

3.[表示形式]タブ→「ユーザー定義」→「#,##0」(上の方にあります)を選択します。

 

4.種類(T)に表示されている「#,##0」のあと,」(カンマ)を入力し(,」は半角)、「OK」ボタンをクリックします。

 

すると、金額の下3桁が省略されて表示されました。

金額の下3桁を省略したときは単位がわかるように、「単位:千円」などと表示しておきましょう。

img20160225_08

 

注意)下3桁を省略した状態で表示されてるだけで、セル内にはもともとの金額が入力されています。

img20160225_09

 

文字入力のときに「(c)」と「(r)」を入力すると、「Ⓒ」と「Ⓡ」に自動で変わってしまうのをご存知でしょうか?
Ⓒは著作権の所有、Ⓡは商標登録を示しているのですが、そういった意味で使うのではなく、ただそのまま「(c)」「(r)」と入力したいときには困りますよね。
そこで今回は、「(c)」と「(r)」の自動変換を解消する方法をご紹介します。

(c)と(r)を表示させる方法

(c)と(r)をそのまま表示させるには、オプションのオートコレクトで設定します。

1.「(c)」と入力すると自動的に「Ⓒ」に、「(r)」は「Ⓡ」に変わってしまいます。

img20160219_01

↓ ↓ ↓ ↓

img20160219_02

 

img20160219_03

↓ ↓ ↓ ↓

img20160219_04

 

2.[ファイル]タブ→「オプション」をクリックします。

img20160219_05

 

3.[Excelオプション]ウィンドウが開くので、「文章校正」→「オートコレクトのオプション(A)」をクリックします。

img20160219_06

 

4.上から5つめの「入力中に自動修正する(T)」の欄をクリックしてチェックをはずし、「OK」ボタンをクリックします。

img20160219_07

 

5.[Excelオプション]ウィンドウの「OK」ボタンをクリックします。

img20160219_08

 

これで、「(c)」「(r)」と入力しても自動変換されなくなりました。

img20160219_09

注文書や見積書など、同じスタイルの書類を作るときは、どのようにして作成していますか?

一度作った書類をコピーして、必要なところだけ書き換えていることも多いのではないでしょうか。

ただそれでは、書き換え忘れて、別のお得意先の名称や商品名が入ったままになるようなミスをしてしまうかもしれませんよね。

そういうミスを防ぐためにも、コピーではなく、テンプレートを作っておきましょう。

一から作る手間がはぶけて便利ですよ。

テンプレート用ファイルを作る方法

見積書のテンプレート用ファイルを作ってみましょう。

1.テンプレートにするファイルを作成します。

img20160204_01

 

2.ファイルが作れたら、[ファイル]タブをクリックし、「名前を付けて保存」をクリックします。

img20160204_02

↓ ↓ ↓ ↓

img20160204_03

 

3.ファイルの保存先を指定するために「参照」アイコンをクリックします。

img20160204_04

 

4.[ライブラリ]の中の[ドキュメント]「Officeのカスタムテンプレート」フォルダをクリックします。

注意:テンプレート用ファイルは他のフォルダに保存をしても、「Ofiiceのカスタムテンプレート」専用フォルダに保存されてしまいます。

img20160204_05

 

5.「ファイル名(N)」欄右端の「▼」マークをクリックして、表示された「ファイルの種類(T)」の中から「Excelテンプレート(*.xltx)」を選びます

img20160204_06

 

6.ファイル名の拡張子が.xltxとなれば、右下の「保存」ボタンをクリックします。

img20160204_07

↓ ↓ ↓ ↓

img20160204_07-2

 

これでテンプレート用ファイルが保存できました。

 

では今度は、保存したテンプレート用ファイルを開いてみましょう。

7.[ライブラリ]の中の[ドキュメント]「Officeのカスタムテンプレート」フォルダをクリックします。

img20160204_08

 

8.ファイル内の「見積書.xltx」をクリックします。
すると、テンプレート用ファイルが開きます。

img20160204_09

 

 

貼り付けた画像を、ある図形の型にしたいということはありませんか?

角丸やひし形から、星型やハート型など、バラエティに富んだ形に切り抜く機能があります。

しかも簡単にできるので、いろんなシーンで活用してみてください。

図形にあわせて画像を切り抜く(トリミングする)方法

花の写真を角丸の型にしてみましょう。

1.まずは花の写真を貼り付けるために、[挿入]タブ→[画像]をクリックします。

img20160128_01

 

2.貼り付けたい画像ファイルを選び、「挿入」ボタンをクリックします。

img20160128_02

 

3.花の写真が貼り付けられたら、図ツールの[書式]タブになっているのを確認します。

img20160128_03

 

4.[トリミング]ボタンの「▼」マークをクリック、「図形に合わせてトリミング(S)」→四角形「角丸四角形」のアイコンをクリックします。

img20160128_04

 

すると、写真が角丸の型になりました。

img20160128_05

 

もしハート型にくり抜きたいときは、4.の操作で最後に、基本図形「ハート」のアイコンをクリックします。

img20160128_06

 

すると、写真がハート型に切り抜かれました。

img20160128_07

 

 

テキストボックスは、文字を直接入力するだけのものだと思っていませんか?

実は、それ以外にも、セルの内容をそのまま表示することもできるのです。

見出しに使えば、セルの内容を変えるだけで自動的に見出し文字を変えることができて便利ですよ。

テキストボックス内にセルの内容を表示する方法

テキストボックス内に最大売上の値を表示させて、見出しを完成させましょう。

img20160121_01

 

1.テキストボックスをクリックして、選択された状態にします。

img20160121_02

 

2.数式バーに、まず「=」と半角入力し、セルB11をクリックして[ENTER]キーを押します。

img20160121_03

 

すると、セルB11と同じ数値「5200」が、テキストボックス内に表示されます。

img20160121_04

 

ここから文字を大きくして、テキストボックスの枠線を消しましょう。

3.文字の大きさの▼マークをクリックして「16」を選びます。

img20160121_05

 

4.「描画ツール」の「書式」タグをクリックし、「図形の枠線」→「線なし」を選びます。

img20160121_06

 

文字が大きくなり枠線が消えました。

img20160121_07

 

 

エクセルで画像を貼り付けたあとに、その画像を別のものと変えたいときがありますよね。

そんなとき、元の画像を削除して新しい画像を貼り付け直ししなくても、すばやく変更できる便利な機能があるんです!

この機能で画像を変更すれば、新しい画像は、元の画像と同じ位置に貼り付けられるので、貼り付け位置を気にしなくても大丈夫なんですよ。

画像を別の画像に変更する方法

次のコアラの画像をペンギンの画像に変更してみましょう。

img20160114_01

 

1.コアラの画像をクリックし、図ツール「書式」タブをクリックします。

そして、「図の変更」をクリックします。

img20160114_02

 

2.「画像の挿入」の画面が表示されるので、「ファイルから」→「参照►」の文字部分をクリックします。

img20160114_03

 

3.差し替えたい画像ファイルを選び、「挿入」ボタンをクリックします。

img20160114_04

 

すると、コアラの画像からペンギンの画像に変わりました。

img20160114_05