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

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

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

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

「挿入オプション」は、挿入する操作の途中で、「上の行」「下の行」「書式のクリア」の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

 

 

会員名簿や商品のデータベースなどで、同じデータが重複していたことはありませんか?

重複したデータを目で見て探し出すのは大変な作業です。

そんなときに便利なのは、エクセルの「重複の削除」という機能です。

基準項目をチェックするだけで、重なったデータをすばやく削除してくれます。

重複したデータを削除する方法

下の名簿の中の重複したデータを削除しましょう

img20151029_01

 

1.表の項目をひとつ選択し、[データ]タグ→「重複の削除」ボタンクリックします。

 

img20151029_02

↓ ↓ ↓

img20151029_02-2

 

2.「重複の削除」ウインドウが開くので、表示されている項目の「No.」のチェックをはずして「OK」ボタンをクリックします。

(「氏名」「年齢」「性別」「生年月日」が一致すると重複してるとみなされます)

img20151029_03

 

3.重複するデータの数残ったデータの数が表示されるので、確認をしたら「OK」をクリックします。

img20151029_04

 

3つのデータが削除されました。

img20151029_09

 

<削除されたデータを知りたい場合>

データが削除されていると、項目「No.」の上のセルとの数字の差が「2」以上になるので、「下のセル-上のセル」という数式で数字の差をもとめてみます。

 

1.セルF3に「=A3-A2」と半角入力し、「Enter」キーを押します。

img20151029_05

 

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

img20151029_06

 

2.F3セルの右下のフィルハンドルをダブルクリックして、下のセルに数式をコピーします。

img20151029_07

 

数字の差が「2」と「3」が、データが削除された箇所です。
消えているNo.をみてみると、No.16、No.24、No.25が削除されたデータとなります。

img20151029_08

 

 

前回の続きで、「2日」以降のセルに日付を表示させて予定表を完成させます。

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

方法は簡単で、「2日」のセルに簡単な数式を入れ、あとは残りのセルにコピー&ペーストするだけです。

年月を入力するだけで自動的に日付が表示される方法2

1.「11月1日」の下のセル(B6)を選択し、「=B5+1」と直接入力して[Enter]キーを押します。(「B5」の箇所は直接B5セルをクリックしてもOK)

img20151025_01

 

「11月1日」に1日をが足されて「11月2日」になりました。

img20151023_12

 

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

3.「11月2日」のセル(B6)を選択し、セル右下のフィルハンドル(+)を下までドラッグします。

img20151023_13

 

下まで日付が表示されました。

img20151025_04

 

4.12月の予定表を作るときには、月の欄(セルF2)に12」と半角入力します。
また、翌年の1月のときは、年の欄(セルD2)に2016」、月の欄(セルF2)に1」と半角入力します。

img20151025_05

 

その場合、月末の欄が空白になるので、すぐ上のセルのフィルハンドルをドラッグして、数式をコピーしましょう。

img20151025_06

 

 

以前に、条件と一致するセルの個数をもとめる方法(COUNTIF関数)をご紹介しました。

COUNTIF関数は指定できる条件が1つですが、今回ご紹介するCOUNTIFS関数は複数の条件を指定できます。

”「COUNTIF」に「S」がついた複数形”と考えると覚えやすいでしょう。

複数の条件と一致するセルの個数をもとめる方法(COUNTIFS関数)

下表のグループ1、グループ2それぞれの条件を満たす人数(セルの数)を関数を使ってもとめてみましょう。

グループ1の条件は「30歳以上」と「男」、グループ2の条件は「30歳以上」と「女」です。

 

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

 

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

img20151012_03

 

まず、検索条件範囲1で「年齢」を指定して、そこから検索条件の「30(歳)以上」を指定します。

3.「検索条件範囲1」欄をクリックし、B2からB26をドラッグします。

img20151012_04

img20151012_05

 

4.「検索条件範囲1」欄に「B2:B26」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$B$2:$B$26」にします。
(数式をコピーしたときに、範囲が変わらないために絶対参照にします)

img20151012_05-2

 

5.「検索条件1」欄に「>=30」(”30(歳)以上”の意味)と直接入力し、「OK」ボタンをクリックします。

img20151012_06

——————————–
【参考】
●と等しい…=●
●以上…>=●
●以下…<=●
●より大きい(上)…>●
●未満、より小さい(下)…<●
●と等しくない…<>●
——————————–

 

次に、検索条件範囲2で「性別」を指定して、そこから検索条件の「男」を指定します。

6.「検索条件範囲2」欄をクリックし、C2からC26をドラッグします。

img20151012_07

 

7.「検索条件範囲2」欄に「C2:C26」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$C$2:$C$26」にします。
(数式をコピーしたときに、範囲が変わらないために絶対参照にします)

img20151012_07.-2

 

8.「検索条件2」欄クリックしてセルE3(男)を選択し、「OK」ボタンをクリックします。

img20151012_09

※「男」と入力されたセルがなくて指定できない場合は、検索条件欄に直接「男」と入力します

img20151012_14

 

グループ1の「30歳以上」の「男」を満たす人数(セルの数)が表示されました。

 

では、数式をコピーをしてグループ2を満たす人数ももとめてみましょう。

9.条件1の人数セル(F2)を選択し、[Ctrl]キー+「C」を押します。(コピー)

 

10.条件2の人数セル(F7)を選択し、[Enter]キーを押します。(貼り付け)

関数式の「男」と指定したセル(E3)が「女」のセル(E8)に変わっているのを確かめましょう。

グループ2の「30歳以上」の「女」を満たす人数は「5」と表示されました。

 

結果は、グループ1が10(人)、グループ2が5(人)となります。

 

 

商品別の購入者数、売上額別の社員数など、ある条件を満たすセルの個数(データの数)をもとめたい場合は、COUNTIF関数を使ってみましょう。

範囲と条件を指定するだけで簡単にもとめられます。

◇COUNTIF関数・・・指定した範囲内の検索条件を満たすセルの個数をもとめます。

条件と一致するセルの個数をもとめる方法(COUNTIF関数)

下表の「人数別世帯数」をCOUNTIF関数を使ってもとめてみましょう。

img20150928_01

 

1.世帯数をもとめるセル(E6)を選択し、関数の挿入ボタン「fx」をクリックします。

img20150928_01-1

 

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

img20150928_02

 

3.「範囲」欄をクリックし、B2からB13をドラッグします。

img20150928_03

↓ ↓ ↓

img20150928_04

 

4.「範囲」欄に「B2:B13」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20150928_05

 

5.次に「検索条件」欄をクリック→D6を選択します。「OK」ボタンをクリックします。

img20150928_06

 

ほかにも、「2」と直接入力する方法もあります。
(検索条件が文字列の場合は「”」(ダブルクォーテーション)で囲みます。)

img20150928_06-1

 

人数が2の世帯数の数がもとめられました。

【数式の意味】
「B2からB13」の範囲の中で、「D6」(または「2」)と同じデータの数をかぞえます。

img20150928_07

 

数式をコピーして、人数が「3」「4」の世帯数をもとめてみましょう。

6.コピーするセルの右下にマウスポインターをおいて、フィルハンドル(+)を「4」の世帯数のセルまでドラッグします。

img20150928_08

コピーされました。

img20150928_09

 

「5以上」の世帯数をもとめる場合は、検索条件に注意しましょう。

7.「検索条件」欄に「>=5」と直接入力し、「OK」ボタンをクリックします。

img20150928_10

【参考】
「>=○」は「○以上」「<=○」は「○以下」をあらわします。

 

すべての人数別世帯数がもとめられました。

img20150928_11

 

 

範囲内の合計は、SUM関数を使ってもとめるのが一般的です。

では累計をもとめたいときはどのようにすればよいでしょうか?

実はこの場合もSUM関数を使います。最初の数値を絶対参照にするのがポイントです。
(絶対参照についての詳細はこちらをご覧ください。)

累計をもとめる方法(SUM関数・絶対参照)

下表の累計をSUM関数を使ってもとめてみましょう。

img20150921_01

 

1.累計をもとめたい一番上のセル(D2)を選択します。

img20150921_02

 

2.D2セルをクリックし「=SUM(」と入力します。
次に、C2セルをクリックし、[F4]キーを1回押して絶対参照$C$2」にします。

img20150921_03

 

3.「=SUM($C$2」の次をクリックし、続けて「:」と入力C2セルをクリック→「)」と入力します。
最後に[Enter]キーを押します。

img20150921_04

 

計算式が入力され、結果が表示されました。

img20150921_05

 

次は下のセルに計算式をコピーしましょう。

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

img20150921_06

 

下のセルにもコピーされて累計がもとめられました。

img20150921_07

 

次に、データを追加した場合のコピーの方法をご説明します。

1.6行目にデータを追加入力します。

img20150921_08

 

2.数式の入った一番下のセルを選択し、セルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150921_09

 

追加したデータの累計がもとめられました。

img20150921_10

 

 

売上データを支店別に複数のシートに分けて作った場合、どのようにすれば手早く集計できるでしょうか?

連続して並んでいる複数のシートの同じセル位置のセルを参照することで簡単に集計することができます。

複数シートの同じ位置のセルデータを集計する方法

シート「本店」「札幌店」「横浜店」のそれぞれの売上額を、シート「全本支店」の売上額に集計してみましょう。

img20150914_01

 

1.集計するセルを選択します。

img20150914_02

 

2.[ホーム]タブ→[編集]→「ΣオートSUM」を選ぶと、セル内に「=SUM()」と自動的に表示されます。

img20150914_03

↓ ↓ ↓

img20150914_04

 

3.シート名「本店」をクリックし、集計を表示するセルと同じ位置のセル(ここではセルB2)をクリックします。

img20150914_05

 

4.「Shift」キーを押しながら、シート「横浜店」(集計したい範囲の最後のシート名)をクリックし、[Enter]キーを押します。

数式バーに「=SUM(’本店:横浜店’!B2)」と自動的に表示されます。

img20150914_06

 

シート「全本支店」のセルに各本支店の売上額が集計されました。

img20150914_07

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

 

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

img20150914_08

 

全部のセルに数式がコピーされて集計されました。

img20150914_09

 

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

できるかぎり簡単な関数を使った方法にしていますので、トライしてみてください。

・1回目「曜日を自動で表示する方法」

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

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

 

曜日を自動で表示する方法(TEXT関数)

下表の日付に対して隣のセルに曜日を自動で表示させてみましょう。

img20150831_01

 

1.曜日を表示させたいセル(C2)を選択します。

img20150831_02

 

2.そのセルに「=text(B2,”aaa”)」(半角)と入力し、[Enter]キーを押します。

※TEXT関数
 TEXT(値,表示形式)…指定した表示形式の文字列に変換する
表示形式:aaa…日~土、aaaa…日曜日~土曜日

img20150831_03

 

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

img20150831_04

 

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

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

img20150831_05

 

数式を文字列にするためにコピー&ペーストをしましょう。

4.セルが選択された状態のままで右クリックをし、コピー(C)を選びます。([Ctrl]+[C]でも可能)

img20150831_06

 

5.同じ状態のままふたたび右クリックをし、[貼り付けのオプション:]→「値」アイコンをクリックします。

img20150831_07

 

曜日が文字として貼り付けられました。

img20150831_08

 

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

 

 

作成した表を別のシートに貼り付けたいときはありませんか?

普通にコピー&貼り付けをして、列幅が違ってレイアウトが崩れてしまうと困りますよね。

そんなときにはこれからご紹介する「表を画像として貼り付ける方法」を使ってみましょう。

貼り付けたあとの元の表は、画像データなので編集はできませんが(拡大縮小は可能)、元の表のデータを変更したときは貼り付けた画像データにも反映されるので便利です。

★表を別のシートに配置する方法★

下の価格表を別シートの個人別売上表の下に配置させてみましょう。

img20150820_02

 

1.貼り付けたい元の表(価格表)を選択し、[ホーム]タブ→「コピー」アイコンをクリックします。

img20150820_03

 

2.貼り付け先のワークシートを選びり付けたい範囲の左上のセルを選択します。
 次に、[ホーム]タブ→貼り付けアイコン下の「▼」をクリックします。

img20150820_04

 

3.[貼り付け]の中から「リンクされた図(I)」を選択します。

img20150820_05

 

価格表がほかのシートに貼り付けられました。

img20150820_06

 

貼り付けられた表は拡大縮小と移動はできますが、データの編集は直接できません。
しかし、元の表を変更した場合は、貼り付けられた表(画像)にも反映されます。
(下図参照)

 

『拡大縮小』

「□」印をドラッグすると・・・

img20150820_10

↓ ↓ ↓ ↓

拡大(縮小)できます。

img20150820_07

 

『元の表のデータ変更→反映』

元の表のデータを\980→¥1,000に変更すると・・・

img20150820_08

↓ ↓ ↓ ↓

変更した箇所が反映されます。

img20150820_09

 

 

表などをコピーして貼り付けたときに、列の幅が変わってしまって困ることはありませんか?

ふたたび、幅を揃えなおすのも面倒ですよね。

そこで、列幅を保ったままコピーする方法をご紹介しましょう。

★列幅を変えずにコピーする方法★

下表を、新しいシートに列幅を保ったままコピー&貼り付けをしてみましょう。

img20150817_01

 

1.コピーしたい範囲を選択し、[ホーム]タブ→「コピー」アイコンをクリックします。

img20150817_02

 

2.貼り付けたい範囲の左上のセルを選択します。(今回は新しいシートに貼り付けます)

img20150817_03

 

3.[ホーム]タブ→「貼り付け」アイコンをクリックし、貼り付けられたデータはそのまま選択した状態にしておきます。

img20150817_04

列幅がコピーされていないので、元の列幅を保たせるようにします。

4.[ホーム]タブ→[貼り付け]アイコン下の「▼」マークをクリックして、「元の列幅を保持(W)」アイコンを選択します。

img20150817_05

 

列幅が適用され、元と同じ列幅になりました。

img20150817_06