商品別の購入者数、売上額別の社員数など、ある条件を満たすセルの個数(データの数)をもとめたい場合は、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

 

 

データの増減に対して、自動的に合計を再計算してくれたら便利だと思いませんか?

実は、関数の指定方法でそれができるのです。

指定方法は簡単ですので活用してみてください。

データを増減したときに自動的に再計算する方法

下表の「世帯主(A列)」と「人数(B列)」のデータが増減した場合に、「総世帯数」欄(D2)と「総人数」欄(E2)が自動的に再計算されるように関数を指定してみましょう。

「総世帯数」を求めるにはCOUNTA関数、「総人数」を求めるにはSUM関数を使います。

img20150924_01

 

1.「総世帯数」欄(D2)を選択し、数式バー横の関数の挿入ボタン「fx」をクリックします。

img20150924_02

 

2.[関数の分類(C)]から「総計」を選び、[関数名(N)]から「COUNTA」を選び「OK」ボタンをクリックします。

img20150924_03

 

3.列の「A」をクリックすると、値1に「A:A」(”指定した列番号全範囲”を意味します)と表示されます。

img20150924_04

 

4.セルA1の項目名「世帯主」を除くために、「A:A」のうしろに「-1」と入力し、「OK」ボタンをクリックします。

img20150924_05

 

関数が入力されて、総世帯数が表示されました。

img20150924_06

 

同様に、総人数欄にも関数を指定しましょう。

6.「総人数」欄(E2)を選択し、数式バー横の関数の挿入ボタン「fx」をクリックします。

img20150924_08

 

7.[関数の分類(C)]から「数学/三角」を選び、[関数名(N)]から「SUM」を選び「OK」ボタンをクリックします。

img20150924_09

 

8.列の「B」をクリックすると、数値1に「B:B」(”指定した列番号全範囲”を意味します)と表示されます。
「OK」ボタンをクリックします。

img20150924_10

 

関数が入力されて、総人数が表示されました。

img20150924_11

 

では、データを増やしてみましょう。
12行目に「佐藤 浩一」「5」とデータを入力します。
すると、自動的に「総世帯数」と「総人数」の値が再計算されます。

img20150924_12

 

 

範囲内の合計は、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

 

 

データの個数を求めるのに、ひとつずつ数えると手間がかかって大変ですよね。
そんなときには、関数を使うと便利です。

◇COUNT関数・・・指定した範囲内の数値のセルの個数をかぞえます

◇COUNTA関数・・・指定した範囲内の空白以外のセルの個数をかぞえます

データの個数をもとめる方法(COUNT関数・COUNTA関数)

下表の「参加世帯数」と「所属世帯数」の数を関数を使って求めてみましょう。

img20150917_01

まずは「参加世帯数」を求めましょう

1.参加世帯数を表示させるセル(B12)を選択、関数の挿入ボタンをクリックします。

img20150917_02

 

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

img20150917_03

 

3.関数の引数ウインドウが開くので、個数を求める範囲(B2からB11)をドラッグして選択します。

img20150917_04

img20150917_05

 

4.値1欄に「B2:B11」と表示されているのを確認して「OK」ボタンをクリックします。

img20150917_06

 

個数(6世帯)が求められました。

img20150917_07

img20150917_07_1

 

次は「所属世帯数」を求めましょう。

1.所属世帯数を表示させるセル(B13)を選択、関数の挿入ボタンをクリックします。

img20150917_08

 

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

img20150917_09

 

3.関数の引数ウインドウが開くので、個数を求める範囲(A2からA11)をドラッグして選択します。

img20150917_10

 

4.値1欄に「A2:A11」と表示されているのを確認して「OK」ボタンをクリックします。

img20150917_11

 

個数(10世帯)が求められました。

img20150917_12

img20150917_12_1

 

 

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

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

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

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

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

 

表を作ったときに、1行ごと交互に色がついていると見やすいですよね。

ただし、1行ごとに色をつけていくと手間がかかります。

そこでオートフィルオプションを使って簡単に色をつける方法をご紹介しましょう。

 

1行ごと交互に色をつける方法

下表に1行ずつ交互に色をつけてみましょう。

img20150910_01

 

1.色をつけたい行(A2~C2)を範囲選択します。

img20150910_02

 

2.[ホーム]タブ→[フォント]→「塗りつぶしの色」アイコンをクリックし、「ゴールド」を選択します。

img20150910_03

 

3.2行目に色がついたら、色のついていない下の行と2行(A2~C3)を範囲選択します。

img20150910_04

 

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

img20150910_05

 

文字もコピーされるので、「オートフィルオプション」で書式だけをコピーします。

5.オートフィルオプションのアイコンをクリックします。

img20150910_06

 

6.「書式のみコピー(フィル)(F)」に◎チェックをつけます。

img20150910_07

 

1行ごと交互に色がつきました。

img20150910_08

 

●行を追加した場合

行を追加したときでも同じように色をつけることができます。

img20150910_09

 

1.色がついている最後の行と下の行の2行(A10~C11)を選択します。

img20150910_10

 

2.あとは、上記の4~6と同じ方法で書式のみをコピーします。

img20150910_11

↓ ↓ ↓ ↓

img20150910_12

↓ ↓ ↓ ↓

追加した行にも交互に色がつきました。

img20150910_13

 

「自動表示させた土日祝に色をつけたい」の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

 

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