1−3 セルを調べる
さまざまな関数を利用して計算するとき、対象となるセルに入力されている内容を調べて、その結果に合わせた処理を行う場面があります。セルが空白なのか、数値が入力されているのか、文字が入力されているのか、などを調べて計算処理を選択することもあります。
■数値セルの個数を求める
指定するセル範囲内に数値が入力されているセルの個数を求めるとき、COUNT関数を使います。数値が入力されているセル数を求めて、何に使うのか、と問われそうですが、データの入力数を調べる時に使ったり、数値が入力されたかどうかの判断に利用したり、さまざまな場面で使います。
1.COUNT関数の使い方
関数の書き方は以下のとうりです。括弧()の中に数値セルの個数を調べるセル範囲を書きます。セル範囲でも、1つのセル番地を指定することができます。1つのセル番地を指定する目的は、そのセルが数値かどうかを判断するときに使います。
=COUNT(引数)
↑
ここにセル番地またはセル範囲を書く
2.使用例
一例として、あるデータの平均値を求めるときの利用方法を紹介してます。平均値は次の式で求められます。この式の分母にあたるデータ数をCOUNT関数で求めるようにしています。図Aの集計表があるとき、各項目()
r操作
@ 空白欄のある集計表
@ 各項目行の平均値を求める
平均値=ラインA〜Dの合計数/ライン数
EX4C837Z 作例 1-3-A
ラインA〜DのうちラインCは点検整備中のため、ラインABDの3つになります。実生産数を例にすると、実際の計算式は次のようになります。
=SUM(D6:D9)/3
↑
この3をCOUNT関数で求める
セル範囲D6:D9をCOUNT関数の引数に入れると、数値が入力されているセルD6,D7,D9の3が求められます。稼働ライン数として求められる値を確認してみます。
B セルD12にCOUNT関数を入力する
=COUNT(D6:D9) を入力する
@ 結果(3)が表示される
作例 1-3-A シート2,3
EX4C838Z EX4C839Z
数値が入力されているセルの個数(D6,D7,D9の3つ)が求められることが分かります。これを平均値を求める式に当てはめると以下のようになります。
=SUM(D6:D9)/COUNT(D6:D9)
この式を平均値を表示するセルに入力すれば、空白セルを省いたときの平均値を求めることができます。
@ ここに数式を入力する
=SUM(D6:D9)/COUNT(D6:D9) を入力する
@ 平均値が表示される。(小数点以下の桁数を0にしています)
EX4C840Z EX4C841Z
同じように、セルE13に不良数の平均値を求める式,セルF13に生産数の平均値を求める式、を入力します。
Aここに数式を入力する
=SUM(E6:E9)/COUNT(E6:E9) を入力する
不良数の平均値が表示される。(小数点以下の桁数を0にしています)
Bここに数式を入力する
=SUM(F6:F9)/COUNT(F6:F9) を入力する
生産数の平均値が表示される。(小数点以下の桁数を0にしています)
EX4D241Z
作例 1-3-A シート2,3
注意)<<
この例は考え方だけで、実際に平均値を求めるとき、AVERAGE関数を利用すれば、指定したセル範囲の数値が入力されているセルだけを計算の対象とするので、以上のようにする必要はありません。
【操作のまとめ】
●COUNT関数は、指定セル範囲の数値が入力されているセルの個数を求める
●文字が入力されているセルはカウントされない。
●数値0が表示されているセルはカウントされる。
■空白以外のセルの個数を求める
指定したセル範囲にデータが入力されているセルの個数を求めるとき、たとえば、ある集計表などで、入力済みのセル数を求めるようなときに利用します。全体のセル数が分かっているとき、逆にデータを入力していないセル(空白セル)の個数を求めることもできます。
1.COUNTA関数
関数の書き方は以下のとうりです。引数にセル範囲を書きます。コンマ(,)で区切って複数のセル範囲や1つのセル番地を指定することもできます。
=COUNTA(引数)
↑
ここにセル範囲を書く
2.使用例[購入確認書]
実際の使用例として、購入確認書を取り上げると、以下のような使い方になります。氏名の列で全体の人数をCOUNTA関数で求め、購入確認のできている(○または×を入力しているセル)数を求めて、確認済みと未確認の人数を求めています。
●全体の人数を求める
全体の人数はCOUNTA関数を使って、氏名が入力されているセル数で全体の人数を求めます
@ 記号を使った集計表
@ セルC14に氏名の数を求める式を入力する
=COUNTA(B6:B12) を入力する
@ 人数が表示される
EX4D242Z 作例 1-3-B
●確認済み人数
購入する、しないを確認した人の人数はCOUNTA関数を使って、○または×を入力しているセル数で求めます。○×を使いましたが、入力の有無を調べるので他の文字でも同じ結果が得られます。
@セルC15に確認済みの人数を求める数式を入力する
=COUNTA(C6:C12) を入力する
入力済みのセル数(6)が表示される
AセルD15に確認済みの人数を求める数式を入力する
=COUNTA(D6:D12) を入力する
入力済みのセル数(5)が表示される
EX4D243Z
●未確認の人数
未確認(空白セル)の人数は、全体の人数から確認済みの人数を引けばいいので、関数を使わずに、すでに求められた値(セルC14、C15、D15)を利用した数式にしてコンパクトにします。当然ですが、関数を使って次のように書くこともできます。
セルC16に入力する式の場合
=COUNTA(B6:B12)−COUNTA(C6:C12)
r操作
@セルC16に[リコーダ]の未確認の計算式「=C14−C15」を入力する
未確認の人数が表示される
AセルD16に[ハーモニカ]の未確認の計算式「=C14−D15」を入力する
未確認の人数が表示される
EX4D244Z
【操作のまとめ】
●すでに求められている値は、その値を利用した計算式にする
●入力済みのセル数を求めるので、入力内容は○や×以外でも同じ結果が求められる
●空白セルの数を求めるときは、全体の数から入力済みのセル数を引いて求める
注意
セルにスペースが入力されていると、画面上では空白のように見えますが、スペースも文字の1つなので入力済みと判断されCOUNTA関数で求める数に反映されます。セルを空白にするには、空白にするセルにセルポインタを合わせて[BS]キーを押します。スペースで入力内容を消すようなことは避けるよう心がけましょう。なお、IF関数などで””を使った空白表示も入力済みと判断されます。画面上で見える、見えない、にかかわらず何か入力されていると入力済みと判断されます。
■○×の個数を求める(同じ入力内容を数える)
データベース関数の入力セル数を求めるDCOUNT関数を使うと、同じ内容が入力されているセルの個数を求めることができます。
1.関数の使い方
他のデータベース関数(DSUM関数など)と同じように、検索値(抽出条件)を指定した書き方になります。ここで紹介する購入確認書の[リコーダー]を例にすると次のようになります。
=DCOUNT(データ範囲,個数を求める列,抽出条件)
@ A B
引数
@入力内容を調べるデータ範囲(セル範囲)
C5:C12を指定する
Aデータ範囲を1列(C列)だけにしているので1を指定する
B抽出条件(検索する内容)を入力したセル範囲を指定する
C18:C19を指定する
EX4D246Z
ポイント
データ範囲と抽出条件に含める項目名(リコーダー)は、同じ内容(半角と全角を取り違えないよう)にする。一致しないと正しく作動しません。
2.抽出条件
まず最初に、検索する内容(抽出条件)を入力します。データ範囲に入力している項目名の写し間違いを避けるために、参照式を使って項目名を写すようにします。下に続くセルに入力する検索内容は入力します。検索内容も参照式にするとデータ範囲のデータを書き換えたとき、検索内容も変わってしまいます。
@セルC18に項目名のセル参照式を入力する
=C5 を入力する
項目名[リコーダー]が表示される
AセルC19に検索内容を入力する
購入する人を数えるので記号「○」を入力する
EX4D247Z
3.関数を入力する
関数に書き込む引数(データ範囲と抽出条件)がそろえば、DCOUNT関数を入力します。リコーダーを例にすると、関数は次のようになります。
=DCOUNT(C5:C12,1,C18:C19)
↑ ↑ ↑
データ範囲 求める列 検索内容(抽出条件)
データ範囲を1列(C列)だけにしているのは、リコーダの列の[○]が入力されているセル数を数えるだけなので、他のデータは必要ないからです。データ範囲が1列だけなので、求める列も1になります。データ範囲の指定を1列だけにした理由の1つに以下で操作する複写も関係します。
@ここに人数(○)を求める関数を入力する
=DCOUNT(C5:C12,1,C18:C19)を入力する
Aリコーダー列の○の数が表示される
EX4D248Z EX4D249Z
4.複写を利用する
リコーダーの隣のハーモニカの購入者数を求める関数は、リコーダーの購入者数に入力した数式(関数)をそのまま複写で入力することができます。複写できる理由は次のとうりです。
セルC21に入力した数式
=DCOUNT(C5:C12,1,C18:C19)
↑ ↑ ↑ ↑
セルD21に入力する数式
=DCOUNT(D5:D12,1,D18:D19)
↑ ↑ ↑ ↑
※↑:数式の違う箇所
セルC21に入力した数式をセルD21に複写すると、相対番地の関係で関数に入力しているデータ範囲と抽出条件のセル番地の列番がCからDに変化します。求める列数の1はそのまま変化しません。この結果、データ範囲と求める列数はセルD21に入力する数式に一致します。抽出条件をリコーダーの隣に入力すれば関数を書き換えることなく、使えることになります。データ範囲を複数列にしていると、求める列数の1が2や3になるので書き換えが必要になります。
@抽出条件を入力する
セルD18に=D5を入力する
セルD19に○を入力する
AセルC21をセルD21に複写する
セルC21をドラッグでセルD21にコピーする
B購入者数が表示される
EX4D245Z
【操作のまとめ】
●DCOUNT関数を使うと、同じ入力内容のセル数を求めることができる
●複写できるように数式を工夫する