行の挿入をすると「上の行」、列を挿入すると「左の列」、の書式が勝手にコピーされて困ったことはありませんか?

これは初期設定で、自動的にコピーされるようになっているからなんです。

とはいえ、挿入したあとに、わざわざ罫線を消したり、文字の大きさを変えたりと、書式設定をしなおさないといけないのは面倒ですよね。

そんなときは、「挿入オプション」を使って「上下(左右)どちらの行(列)の書式をコピーするのか」または、「書式はコピーしないのか」を選択することができます。

「挿入オプション」は、挿入する操作の途中で、「上の行」「下の行」「書式のクリア」の3つの選択肢から選ぶだけなので簡単ですよ。(列の場合は「左の列」「右の列」「書式のクリア」の3つです)

それでは詳しくご説明していきましょう。

行(列)の挿入時に、コピーする書式の行(列)を選択する方法

2行目に、下の行の書式のコピーをした行を挿入して、「商品コード 102-0001」の行を増やしましょう。

img20160427_01

 

1.挿入したい行(今回ならば2行目)の行番号をクリックして行全部を選んだ状態にし、選んだ行のところで右クリックして、表示されたメニューの中から「挿入(I)」を選びます。

img20160427_02

↓ ↓ ↓ ↓

img20160427_03

 

2.挿入された行の左下(行番号の横)に「挿入オプション」マークが表示されるので、マウスカーソルを「挿入オプション」に近づけて、▼マークをクリックします。

img20160427_04

↓ ↓ ↓ ↓

img20160427_04_2

 

3.選択メニューが表示されるので、真ん中の「下と同じ書式を適用(B)」の○印をクリックします。(書式をコピーしたくない場合は、「書式のクリア(C)」を選択します。)

img20160427_04_3

 

すると、下の行(3行目)の書式をコピーした行が挿入されました。

img20160427_05

 

挿入された行は下の行の書式がコピーされているので、あとは入力していくだけで完成です。

img20160427_06

 

 

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

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

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

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

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

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

 

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

 

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

img20150406_04

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

 

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

 

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

 

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

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

img20160225_08

 

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

img20160225_09

 

本日はリクエストにお答えいたします。

毎月、月間予定表の日付を作るとなると、その都度、入力するのは面倒だと思いませんか?
そこで、年と月を入力するだけで一ヶ月の日付が自動的に表示される表を作成しました。

長くなるので2回に分けて説明しますね。

●1回目
DATE関数を使い、別のセルに入力されている「年」「月」「日」をあわせて表示させて、表示を「○月○日(○)」にいう形にします。

●2回目
「2日」以降のセルに「1日に1を足す」という数式を入れて完成させます。

実際にやってみるととても簡単なので、カレンダー以外でも活用してみてくださいね。

◇DATE関数…別のセルのデータを組み合わせて日付として表示する
DATE(“年”を示す数字かセルを指定,”月”を示す数字かセルを指定,”日”を示す数字かセルを指定)

例1:=DATE(2015,10,23)と入力すると →「2015/10/23」と表示される
例2:=DATE(A1,B2,C3)と入力すると、それぞれのセルに入力されている数字が日付形式で表示される

年月を入力するだけで自動的に日付が表示される方法1(DATE関数)

下の月間予定表を、年と月を入力するだけで自動的に一ヶ月の日付が表示されるように設定しましょう。

img20151023_01

 

1.年(セルD2)月(セルF2)にそれぞれ「2015」「11」と半角入力します。

img20151023_02

 

2.1日を表示するセル(B5)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151023_03

img20151001_02_1

 

3.関数の挿入ウインドウが開くので、[関数の分類(C)]から「日付/時刻」を選び、[関数名(N)]から「DATE」を選び「OK」ボタンをクリックします。

img20151023_04

 

4.「」として表示させるセル(D2)を選択します。

img20151023_05

img20151023_05-2

↓ ↓ ↓

img20151023_05-3

 

5.「月」の欄をクリックし、「」として表示させるセル(F2)を選択します。

img20151023_06

↓ ↓ ↓

img20151023_06-2

 

6.「日」の欄をクリックし、「1」と直接半角入力します。
「OK」ボタンをクリックします。

img20151023_07

 

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

img20151023_08

 

では、表示形式を「○月○日(○)」にしてみましょう。

7.リボンの「数値」の右下アイコンをクリックします。

img20151023_09

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

img20151023_17

 

8.セルの書式設定ウインドウが開くので、[分類(C)]から「ユーザー定義」を選び、[種類(T)]から「m”月”d”日”」を選びます。

img20151023_09-1

 

9.[種類(T)]のすぐ下の欄に表示されている「m”月”d”日”」のあとに「(aaa)」と直接半角入力し、「OK」ボタンをクリックします
(※aaaは日~土を表示するための形式)

img20151023_09-2

 

表示が「11月1日(日)」となりました。

img20151023_10

 

次回は、「2日」以降のセルに簡単な数式を入れて予定表を完成させます。

 

「自動表示させた土日祝に色をつけたい」の3回目です。

「祝日に色をつける方法」として、条件付き書式とCOUNTIF関数を使います。
※COUNTIF関数
 COUNTIF(範囲、検索条件)…指定した範囲から検索条件を満たすデータの数を求める

設定方法を細かくご紹介しているのでやや長めになっていますが、これで完成となりますので試してみてくださいね。

 

祝日に色をつける方法(条件付き書式・COUNTIF関数)

以下の順序で設定していきましょう。
・祝日一覧表の作成
・条件付き書式のルール設定
・ルールの優先順位変更

 

1.別のシートに祝日の一覧表を作成し、シート名を「祝日」とします。

img20150907_01

 

2.土日に色がついている状態で、色をつけたい曜日の列(C列)を範囲選択します。

img20150907_02

 

3.[ホーム]タブ→[スタイル]の「条件付き書式」→「新しいルール(N)」を選択します。

img20150903_02

img20150907_04

 

4.[ルールの種類を選択してください(S)]から「►数式を使用して、書式設定するセルを決定」を選択します。

img20150907_05

 

5.次の数式を順次入力していきます。

=COUNTIF(祝日!$A$2:$A$16,’5月’!$B2)=1

これは「祝日シート-祝日一覧表の範囲(祝日!$A$2:$A$16)の中から、検索条件の日付(’5月’!$B2)の数が1回(=1)であれば色をつける」という意味をあらわしています。

つまり、5月シートの日付(C列)のうち、祝日一覧表の中の日付と一致する日付の曜日は色が変わり、一致しなければ変わらないということです。

 

では以下の順で数式を完成させていきましょう。

[次の数式を満たす場合に値を書式設定(O)]の欄に「=COUNTIF(」と半角で入力します。

img20150907_06

 

6.「祝日」シートの祝日一覧表から、祝日の日付部分(A2からA16)をドラッグして範囲選択します。

img20150907_07

すると、下図の赤線部分のように自動的に範囲が入力されます。

img20150907_08

 

7.続けて「,」と半角入力し、カレンダー日付の「2015/5/1」(セルB2)をクリックします。

img20150907_09

img20150907_10

 

8.セルB2を選択したら「$B$2」となっているので、[F4]キーを2回押して$B2」(列は絶対値、行は相対値)にしてから、続けて「)=1」と半角入力します。

img20150907_11

=COUNTIF(祝日!$A$2:$A$16,’5月’!$B2)=1」と入力できていれば、「書式(F)」を選択します。

 

9.[フォント]タブ→[色(C)]の「▼」→「オレンジ」を選択し、欄がオレンジ色になっていれば「OK」ボタンをクリックします。(プレビューに表示されます)

img20150907_12

 

10.ルールの一覧に追加されているのを確認したら「適用」をクリックします。

img20150907_14

祝日(5/3、4、5)の曜日がオレンジ色になりました。

img20150907_15

 

日曜日が祝日であるとき、日曜日の赤色を優先したい場合は、ルールの表示順位を変更しましょう。

ルールの順位は上からが優先となるので、「祝日のルール」を「日曜のルール」の下に移動させます。

11.「祝日のルール」を選んで、右上部の「▼」ボタンをクリックします。

img20150907_16

 

「祝日のルール」が「日曜のルール」の下に移動しました。

img20150907_17

「OK」ボタンをクリックします。

 

5/3(日・祝)の曜日がオレンジ色から赤色に変わりました。

img20150907_18

 

 

「自動表示させた土日祝に色をつけたい」というリクエストがありましたので、3回にわけて設定方法をご紹介したいと思います。

今回はその2回目。
条件付き書式の機能を使って、土日に色をつける方法をご紹介します。

 

・1回目「曜日を自動で表示する方法(クリックするとページを見られます)

・2回目「土日に色をつける方法」

・3回目「祝日に色をつける方法」

 

「土日」に色をつける方法(条件付き書式)

「土」を青色文字、「日」を赤色文字に設定してみましょう。

1.前回に貼り付けした曜日のセルを範囲選択します。

img20150903_01

 

2.[ホーム]タブ→[スタイル]の「条件付き書式」→「ルールの管理(R)」を選択します。

img20150903_02

img20150903_03

 

3.[条件付き書式ルールの管理]ウィンドウが開くので、「新規ルール(N)」を選択します。

img20150903_04

 

4.[ルールの種類を選択してください(S)]→「►指定の値を含むセルだけを書式設定」を選択し、
つぎに、[ルールの内容を編集してください(E)]→[次の値の間]横の「▼」→「次の値に等しい」を選択します。

img20150903_05

 

5.[次の値に等しい]の隣の欄に「土」と入力し、下の「書式(E)」を選択します。

img20150903_06

 

6.[セルの書式設定]ウィンドウが開くので、[フォント]タブ→[色(C)]の「▼」→「青」を選択し、欄が青色になっていれば「OK」ボタンをクリックします。(プレビューに表示されます)

img20150903_07

↓  ↓  ↓

img20150903_08

 

※セルの背景に色をつることもできます。
[塗りつぶし]タブ→[背景色(C)]の中の色をクリック→「OK」ボタンをクリック

img20150903_16

 

7.[プレビュー:]欄で青色になっているのを確認して「OK」ボタンをクリックします。

img20150903_09

 

これで「土」が青色になる設定ができました。

img20150903_10

 

次は「日」を赤色に設定してみましょう。

8.設定方法は同様で、3~7の操作を繰り返します
違いは書式で色の設定をするときに「赤」を選びましょう。

img20150903_12

 

9.「日」の設定ができたら、「適用」ボタンをクリックします。(ウィンドウは開いた状態にしておきます)

img20150903_14

 

「土」が青色文字、「日」が赤色文字になりました。

img20150903_15

 

次回は「祝日に色をつける方法」をご紹介します。

日付に対して曜日を表示させたいとき、自動で表示できると便利だと思いませんか?

表示形式で設定することで、日付と同じセル内に曜日を表示させることができます。

★日付と同じセル内に曜日を自動表示する方法★

下表の日付の横に曜日を自動で表示するように設定してみましょう。

img20150827_01

 

1.日付が入力してあるセルを選択(ここではB3セル)し、右クリック→「セルの書式設定(F)」を選択します。

img20150827_02

もしくは、[ホーム]タブ→[数値]グループの右下の「斜め矢印」をクリックします。

img20150824_06

2.セルの書式設定ウインドウが開くので、[表示形式]タブの[分類(C):]→「ユーザー定義」を選択し、[種類(I):]欄に表示されている文字の最後に「(aaa)」(※すべて半角)と追加入力します。
サンプル欄に「(水)」と表示されるのを確認したら、「OK」ボタンをクリックします。

img20150827_05

 

日付の横に曜日が自動表示されました。

img20150827_06

下のセルにも設定しましょう。

 

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

img20150827_07

下のセルにも曜日が表示されました。

img20150827_08

 

 

会員番号などで「0」から始まる数字を入力したいことがあると思います。
ところが通常、たとえば下図のように「0001」と入力したら「1」と表示されてしまいます。

img20150824_02

img20150824_03

実は、0を入力しなくても「1」と入力すれば先頭に「0」が自動的について「0001」と表示できる方法があるのです。
その設定方法をご紹介しましょう。

★数字の先頭「0」を自動表示する方法★

下表の会員番号に0から始まる4桁の数字(0001~)を表示してみましょう。

img20150824_01

 

1.数字を入力する範囲を選択します。

img20150824_04

 

2.右クリック→「セルの書式設定(F)」を選択します。

img20150824_05

もしくは、[ホーム]タブ→[数値]グループの右下の「斜め矢印」をクリックします。

img20150824_06

 

3.[表示形式]タブ→分類(C):「ユーザー定義」を選択します。

img20150824_07

 

4.種類(T):の欄「0000」と入力し「OK」ボタンをクリックします。
(3桁にしたい場合は「000」、2桁の場合は「00」と入力します)

img20150824_08

 

5.「0001」と表示させたいセルに「1」と入力し確定します。

img20150824_09

 

「0001」と表示されます。

img20150824_10

下のセルにも表示させましょう。

 

6.A2セルの右下にマウスポインターをおいて、フィルハンドル(+)を下のセルまでドラッグします。

img20150824_11

 

7.オートフィルオプションの「▼」をクリックし、「連続データ(S)」にチェックをつけます。

img20150824_12

 

連番が表示されました。

img20150824_13

 

 

シートをロックする方法で、シート全体に対して編集ができないようにしました。

閲覧専用のシートならば問題はありませんが、その都度入力することがあるときは、入力するセルを編集可能にしておく必要があります。

方法は、「編集したいセルのロックを解除」してから「シート全体を保護」という順で設定していきます。

★一部のセルのみ編集可能にする方法★

下表の個数の列(D列)だけを編集できるようにして、その他のセルは編集できないようにシート全体をロックしてみましょう。

img20150730_01

 

1.編集可能にしたい範囲を選択します。

img20150730_02

 

2.[ホーム]タブ→[書式]→「セルのロック(L)」を選択します。(セルのロックをはずす)※ページ下参照

img20150730_03

 

3.[校閲]タブ→「シートの保護」を選択し、[シートの保護]ウィンドウが開くので、シートをロックされたセルの内容を保護する」にチェックが入っているかを確認し、「OK」ボタンをクリックします。(シートをロックする)

img20150727_02

↓ ↓ ↓

img20150727_04

 

これで、指定した範囲のみ編集できるようになりました。

↓編集ができる

img20150730_05

↓編集しようとするとエラーメッセージが出る

img20150730_06

 


※「セルのロック」についての解説

初期設定ではすべてのセルに「ロック」設定されています。

img20150730_07

ロックにチェックされている=セルに施錠されていない鍵がある、と考えてみましょう。

書式設定のロックにチェックがあるセルは、シートをロックすることによって、セルの鍵が施錠されてしまうので、編集ができなくなります。

一方、ロックのチェックをはずすということは、鍵の施錠を機能させない(鍵が存在しない)ということになります。

よって、書式設定のロックのチェックをはずしていれば、シートをロックしても、そもそもセルには鍵がないので、施錠されることなく編集ができる、ということなのです。

 

 

リスト中の未記入のセルを見つけたいとき、データが多いと見つけ損ねることもありますよね。

そんなときは、未記入セルに書式を適用することで簡単に探すことができます。

★空白セルに書式を適用する方法(条件付き書式)★

 

下表の空白セルに赤色をつけます。

 

1.探したいセルを含む範囲を選択します。

img20150420_01

 

.[ホーム]タブ→[スタイル]→「条件付き書式」を選択し、「新しいルール(N)」を選択します。

img20150420_02

 

3.[ルールの種類を選択してください(S)]から「指定の値を含むセルだけを書式設定」を選択し、[ルールの内容を編集してください(E)]→[次のセルのみを書式設定(O)]から[セルの値]右の[▼]をクリックして「空白」を選択します。

img20150420_03

 

4.[プレビュー:]右端の「書式」ボタンをクリックします。

img20150420_04

 

5.[塗りつぶし]タブ→[背景色(C):]から「」を選び「OK」ボタンをクリックします。

img20150420_05

 

6.プレビュー右欄の背景が赤色になっているのを確認して「OK」ボタンをクリックします。

img20150420_06

 

空白セルが赤色になりました。

img20150420_07

 

前回ご紹介した「条件付き書式」機能は、指定した期間内のセルに書式を適用することもできます。

表に入力されている日付のうち、「今日」「昨日」「来週」「先週」などを指定し、書式を設定することで、その日付のセルの文字色や背景色を変えられます。

特定の期間のデータを探すときに便利です。

 

★指定した期間内のセルに書式を適用する方法★

 

下表から、「先週」の日付のセルに文字色と背景色をつけます。

img20150416_01

 

1.日付欄を範囲選択します。

img20150416_02

 

.[ホーム]タブ→[スタイル]→「条件付き書式」を選択し、「セルの強調表示ルール(H)」→「日付(A)」を選択します。

img20150416_03

 

3.[次の期間内の日付を含むセルを書式設定:]下の[▼]をクリックし、「先週」を選択し、[書式:]は「濃い赤の文字、明るい赤の背景」のままにしておきます。

img20150416_04

 

4.選択項目を確認して「OK」ボタンをクリックします。

img20150416_05

 

先週の日付に文字色と背景色がつきました。

img20150416_06