資料、振込、会議など、時間厳守のある仕事をしているときに期限を忘れてしまうほど困ったことはないですよね。

そんなとき、エクセルに、期限の数日前から「もうすぐ期限ですよー」と知らせてくれる機能はあれば便利だと思いませんか?

実は、エクセルの条件付き書式を使えば、シートに期限日を入力しておくだけで、期日直前だということを思い出させてくれるのです!
一度、設定しておけば、期日を入力しておくだけでOK!

方法は、「期日から今日の日付を引いた日数が何日前であるか」という数式を入力し、書式を設定するだけです。

書式を強調して期限を知らせる方法(条件付き書式)

資料の提出日をまとめた表があります。

img20160610_00

提出日まで2日を切ったセルを、自動的に強調するようにしましょう。

手順は、関数を使って「今日の日付」を表示し、次に条件付き書式を使って提出日まで2日を切っているセルの背景色が黄色になるように設定していきます。

 

1.今日の日付を表示させたいセル(D1)を選択し、数式バーに=toと半角入力すると、画面に「TODAY」と関数候補が表示されるので、その「TODAY部分をダブルクリックします。

すると数式バーに「=TODAY(」と表示されるので、その後に「)と半角入力し、[Enter]キーを押します

↓ ↓ ↓ ↓

img20160610_01-3

↓ ↓ ↓ ↓

img20160610_01-4

 

今日の日付が表示されました。

img20160610_01-5

関数を使っているので、日付が変わると自動的に日にちも変わります。
だから、一度入力しておけば、毎日、入力し直す必要はありません。

 

次に条件付き書式の設定をしていきます。

2.提出日の日にちのセルを全て選択し、リボンの[スタイル]グループから「条件付き書式」→「新しいルール(N)」をクリックしていきます。

↓ ↓ ↓ ↓

img20160610_02-1

 

3.[新しい書式ルール]のウィンドウが開いたら、「数式を利用して、書式設定するセルを決定」をクリックします。

img20160610_02-2

 

4.数式を入力するウィンドウが開いたら、「次の数式を満たす場合に値を書式設定(O)」の欄をクリックし、「=B3-$D$1<=2」という数式を入力していきます。
この数式は、「提出日から今日の日付を引いた日数が2日以下」という意味です

では、提出日のセルの一つであるセルB3をクリックします。
すると「=$B$3」と絶対値で表示されるので、「=B3(相対値)になるようにファンクションキー「F4」を3回押します

↓ ↓ ↓ ↓

img20160610_02-4

img20160610_02-5

 

5.続けて「(マイナス)と半角入力し、今日の日付のセルD1をクリックします。
こちらも絶対値で表示されますが、今日の日付は固定させておきたいので絶対値のままにしておきます。

img20160610_02-6

 

6.続けて「<=2と半角入力し、下にある「書式(E)」のボタンをクリックします。

img20160610_02-7

 

7.[塗りつぶし]タブをクリックして、背景色(C)の中から黄色のボックスをクリック、「OK」ボタンをクリックします。

書式のプレビューの背景色が黄色になっていたら、「OK」ボタンをクリックします。

img20160610_03

↓ ↓ ↓ ↓

img20160610_03-2

 

すると、資料Bの提出日「2016/6/12」のセルの背景色が黄色になりました。
今日の日付は「2016/6/10」なので、資料Bの提出日は2日後になっていますよね。

img20160610_04

 

今日の日付が変わるごとに、自動的に、提出日から2日切ったセルに背景色がつきます。

img20160610_04-2

 

 

連番を自動でつけ直しする方法(ROW関数)

直接入力した連番があります。

img20160513_01

 

もし1行削除したとしたら…

img20160513_02

 

削除した行の番号が抜けて、連番でなくなってしまいます。

img20160513_03

 

そこで今回は、行を削除しても自動的に連番をつけ直しする便利な関数をご紹介します。

それはROW(ロウ)関数です。

ROW関数が入力されているセルの行番号を表示させて、そこから、連番が入力されていない行数(今回では1行目なので「1」)を引いて連番を表示させます。

ではさっそく、操作方法を説明していきましょう。

 

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

 

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

 

3.関数の挿入ウインドウが開いたら、何も入力しないままで「OK」ボタンをクリックします。

※何も入力せずに引数を省略すると、そのセルの行番号が表示されます。
今回の場合は2行目なので「2」と表示されます。

 

4.数式バーに表示されている関数式の後ろをクリックして「-1」と半角入力をし、[Enter]キーを押します。

↓ ↓ ↓ ↓

「1」と表示されました。

 

5.下のセルにも関数をコピーするために、フィルハンドル(+)を下へドラッグします。

 

連番が入力できました。

 

では、ためしに1行削除してみましょう。

img20160513_12

 

削除しても番号が抜けないで連番になります。

img20160513_13

 

 

他の人が作ったデータや、他のファイルから取り込んだデータの中で、セル内の文章が強制的に改行されているときはありませんか?

「実はセル内は改行なしの1行で表示させたいんだけど…」となった場合に、1行ずつBackspaceキーで改行を解除させていくのも手間がかかって面倒ですよね。

そんなときは、CLEAN(クリーン)関数を使えば簡単に1行にすることができます!

しかも、1行にしたいセルを選べばいいだけなので超簡単ですよ!

◆CLEAN(文字列)…指定したセル内の文字列の改行を解除する

セル内の改行を解除して1行にする方法(CLEAN関数)

CLEAN関数を使って、改行を解除した1行で表示させてみましょう。

 

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

 

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

 

3.関数の挿入ウインドウが開いたら、改行された文字列が入っているセル(A2)をクリックし、「OK」ボタンをクリックします。

img20160407_04

↓ ↓ ↓ ↓

↓ ↓ ↓ ↓

 

改行が解除されて1行で表示されました。

 

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

 

下のセルの文字列も1行で表示されました。

 

 

住所を都道府県とそれ以降の住所に分ける方法<1>では、まず都道府県だけを表示させました。

では今回は、都道府県をのぞいた残りの住所を表示する方法をご紹介しましょう。

使う関数はRIGHT関数LEN関数だけです。

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

住所(D列)に都道府県をのぞいた残りの住所を表示させましょう。

 

【考え方】

「元の住所」から「都道府県」の文字をのぞいて「住所(D列)」に表示する、と考えます。

具体的にいうと、RIGHT関数を使って、元の住所(B列)の文字を右側から取り出すのですが、そのときに「元の住所(B列)の文字数から都道府県(C列)を引いた文字数」取り出します。(下図参照)

 

【関数式】

=RIGHT(B2,LEN(B2)-LEN(C2))

B2元の住所(B列)の右側から
LEN(B2)-LEN(C2)「元の住所(B列)の文字数」から「都道府県(C列)の文字数」を差し引いた文字


 

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

 

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

 

3.RIGHT関数の引数ウインドウが開きます。まず、文字列の欄には、元の住所のセル(B2)をクリックして入力します。

 

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

 

5.LEN関数の関数の引数ウインドウが開きますので、文字列の欄には、元の住所のセル(B2)をクリックします。

↓ ↓ ↓ ↓

 

6.次に、下の「OK」ボタンはクリックしないで数式バーに表示されている「RIGHT」の文字のところをクリックします。

 

7.再び、RIGHT関数の引数ウインドウが開くので、文字数欄に「LEN(B2)」と表示されていれば、そのあとに続けて「」と半角入力します。

↓ ↓ ↓ ↓

 

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

 

9.LEN関数の関数の引数ウインドウが開きますので、文字列の欄には、都道府県のセル(C2)をクリックし、「OK」ボタンをクリックします。

↓ ↓ ↓ ↓

 

都道府県をのぞいた住所が表示されました。

 

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

 

下のセルにも、都道府県をのぞいた住所が正しく表示されました。

img20160330_00

 

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

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

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

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

住所を都道府県とそれ以降の住所に分ける方法<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

 

 

文字変換シリーズの最後は、英単語の頭文字だけを大文字に変換する方法です。

英語で人名、月、曜日を書く場合、頭文字だけを大文字にしますよね。
これを入力するには、Shiftキーを押したり離したりで打ち間違いが多く面倒だったりしませんか?

そんなときに便利な関数が「PROPER(プロパー)関数」です。
UPPER関数は、英単語の頭文字だけを大文字に変換します。
関数の入力方法は簡単で、関数を選んで大文字にしたい文字の入っているセルを選ぶだけです。

◇PROPER(プロパー)関数…英単語の頭文字だけを大文字に変換する /PROPER(文字列)

英単語の頭文字だけを大文字に変換する方法(PROPER関数)

文字、小文字のみで入力されている英単語の頭文字をすべて大文字に変換しましょう。

 

1.頭文字を大文字表示させるセル(B2)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151001_02_1

 

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

 

3.「文字列」欄に、セルA2を選択します。

↓ ↓ ↓

 

4.「文字列」欄に「A2」と表示されたら、「OK」ボタンをクリックします。

 

英単語の頭文字が大文字に変換されました。

 

では、数式を下のセルにコピーしましょう。

5.コピーするセル(B2)の右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

 

ほかのセルの文字もすべて大文字に変換されました。

 

 

文字変換シリーズの第3段です。

英字の大文字をキーボードから入力するはどのようにしていますか?
Shiftキーを押しながら打つのも指が大変だし、Caps Lockをかけたままでは小文字を打つときにややこしいし、と、英字の大文字を打ち込むのは意外と手間だったりもします。

そこで、もしすべての英字を大文字の表記にしたいときは「UPPER(アッパー)関数」を使いましょう。
UPPER関数は、すべての英字を大文字に変換します。
なので、UPPER関数を使えば、小文字が混ざっていたり、すべてが小文字だったりしても、一発で大文字に変換できるのです。
操作は簡単で、関数を選んで大文字にしたい文字の入っているセルを選ぶだけです。

一方、英字をすべて小文字に変換する関数は「LOWER(ロワー)関数」といいます。
操作方法はUPPER関数と同じなので、セットで覚えておくといいですね。

◇UPPER(アッパー)関数…英字をすべて大文字に変換する /UPPER(文字列)
◇LOWER(ロワー)関数…英字をすべて小文字に変換する /LOWER(文字列)

英字をすべて大文字に変換する方法(UPPER関数)

文字と小文字が混じっている英字をすべて大文字表記に変換しましょう。

 

1.大文字を表示させるセル(B2)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151001_02_1

 

2.関数の挿入ウインドウが開くので、[関数の分類(C)]から「文字列操作」を選び、[関数名(N)]から「UPPER」を選び「OK」ボタンをクリックします。(小文字表記にしたいときは「LOWER」を選びます)

 

3.「文字列」欄に、セルA2を選択します。

↓ ↓ ↓

 

4.「文字列」欄に「A2」と表示されたら、「OK」ボタンをクリックします。

 

すべて大文字に変換されました。

 

では、数式を下のセルにコピーしましょう。

5.コピーするセル(B2)の右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

 

ほかのセルの文字もすべて大文字に変換されました。