トップ > スキル : アプリケーション > Excel(エクセル) > 基礎編(関数:黄金の組み合わせ術)

Excel

関数:黄金の組み合わせ術

得意先別の集計を求める(Sheet:SUMIF)

次に、得意先別の集計を行います。この場合、検索条件にはあいまいな条件を指定することによって、得意先別にまとめて集計することができます。


▶ あいまいな条件の指定方法には以下のようなものがあります。

条件 入力例 意味 表示例
ある文字と等しい ABC 「ABC」と等しい ABC
ある文字で始まる ABC* 「ABC」で始まる ABC店、ABC商事
ある文字で終わる *会社 「会社」で終わる 三陽会社、動産株式会社
ある文字を含む *会社* 「会社」を含む 電算会社寮、会社松浪、松浪会社
ある文字以外 <>ABC 「ABC」以外 XYZ、BCA
~以上 >=500 500以上 500~
~以下 <=500 500以下 ~500
~より大きい >500 500超 501~(整数の場合)
~より小さい <500 500未満 ~499(整数の場合)
Point:

あいまい条件で使用する*(アスタリスク)や不等号の記号は半角(日本語OFF)で入力します。また?(クエスチョン)は、1文字を指定する記号です。例えば、「???会社」と記述すると「会社」の前に3文字ある会社名しか表示されません。(ABC会社、小田原会社などは表示されるが、AB会社、AB株式会社などは表示されない)
*(アスタリスク)は任意の文字列なので、文字列の長さは問いません。

取引先の条件を作業セル(ここでは、セル【D9】から【D11】)に記述して、SUMIF関数で完成させましょう。

セル【D9】からセル【D11】に右のように入力します。(※ *(アスタリスク)は半角入力です)

セル【F9】にSUMIF関数を使って取引先「ファニナ」系列会社の売上合計を求めましょう。次に計算式をセル【F11】までコピーしましょう。

=SUMIF($A$3:$A$15,D9,$C$3:$C$15)

「その他」の欄には、全体の合計から集計された結果を引いた数式を入力します。
セル【F12】に次の数式を入力します。

=SUM(C3:C15)-F9-F10-F11

Point:

引数の[検索条件]には直接文字列を記述してもかまいません。検索条件を変更したい場合は、セル参照で条件を指定し、セル内で変更すると効率的です。

「漢検テスト」を受験した人数と欠席した人数を求める(Sheet:COUNT系)

◆ データの個数を数える(COUNT系)

Excelが扱うデータは計算の対象となる「数値」、計算の対象とならない「文字列」の2種類あります。
これらのデータの個数を調べるにはCOUNT系の関数を使います。COUNT系の関数には次のようなものがあります。

=COUNT(値1,値2・・・)

引数で指定したセルやセル範囲の中で「数値データ」の個数を求めます。

=COUNTA(値1,値2・・・)

引数で指定したセルやセル範囲の中で、データが入力されているセル(空白でないセル)の個数を求めます。

=COUNTIF(値1,値2・・・)

引数の「範囲」の中で、「検索条件」に合うセルの個数を求めます。「検索条件」を文字列で指定する場合は、「”」で囲んで指定します。また、条件の記述されているセル番地を指定することもできます。

=COUNTBLANK(値1,値2・・・)

範囲内の「空白セル」の個数を求めます。

「漢検テスト」を受験した人と、欠席した人の人数を求めましょう。

受験者数を求めるには、「得点」の列から数値データの入っているセルを数えれば求められます。
セル【F2】に次の数式を入力しましょう。

=COUNT(C3:C13)

欠席者数を求めるには、データが入っているセルの数から、受験者数を引けば求められます。
セル【F3】に次の数式を入力しましょう。

=COUNTA(C3:C13)-F2

(※ セル【F2】と【F3】にはあらかじめ単位として「人」と表示されるように表示形式が設定されています)


次に、このテストに登録されている方の世代別の人数を求めましょう。条件に合うデータの個数を調べるにはCOUNTIF関数を使いますが、この関数は条件を1つしか指定できません。そこで、『作業セル』を作成し、20歳以上、30歳以上という計算式を入力し、「20歳代」の人を数えるには20歳以上から30歳以上を引いて求めましょう。

G列を『作業セル』として使用します。セル【G7】に以下のような数式を入力します。

=COUNTIF($B$3:$B$13,">="&E7)

(※ セル範囲【E7】:【E10】にはあらかじめ「○○歳代」と表示されるように表示形式が設定されています)

セル【G7】の数式を見ると、第1引数の「範囲」は年齢が入力されているセルを指定し、絶対参照を設定しています。「検索条件」の引数では、あいまい条件とセルを結合する「&」演算子を使用して条件を設定します。

Point: 【&演算子】

&(アンバサンド)は、文字同士を結合したり、演算子と文字列を結合できます。

セル【G7】の数式をセル【G11】までコピーします。セル【G11】までコピーするわけは、50歳代は60歳以上の人数を引く必要があるからです。よって、セルG11の数式を次のように書き換えます。

=COUNTIF($B$3:$B$13,">=60")

では、作業セルを参照してF列の受験登録者数を求めます。
セル【F7】に次の数式を入力します。

=G7-G8

そして、セル【F7】の数式をセル【F10】までコピーします。

『作業セル』を非表示にして完成です。検索条件にあいまい条件とセル番地を「&」演算子で結合することで、条件の幅を広げることができます。
またセル範囲内の空白のセルを数える場合は、COUNTBLANK関数を利用します。

テストの成績に順位をつける ( Sheet:RANK )

◆ 順位を調べる(RANK)

売上成績のランク付けなどの順位を調べるには、RANK関数を使用します。

=RANK(数値,範囲,順序)

※ 順序・・・降順の場合 0、 昇順の場合1を入力(0は省略可)

RANK関数の第1引数の「数値」は、順位を調べたい数値データを指定します。「範囲」には、調べる対象となるデータ全体を指定します。この「範囲」にもコピーを考慮して、絶対参照を設定することに注意します。


「漢検」テストの順位を求めましょう。
セル【C3】にRANK関数を使って「浅田」さんの順位を求めます。表のデータを見ると、「鈴木」さんは欠席(文字列)が入力されています。このまま数式を入力すると「鈴木」さんの順位欄はエラー表示されます。
その理由は、RANK関数の引数は「数値」のみを対象としますので、「文字列」は指定できないからです。この場合、ISNUMBER関数とIF関数を組み合わせることで、文字列が記述されていても順位が正しく表示されます。


◆ 引数に指定されたデータが数値であるかどうかを調べる(ISNUMBER)

=ISNUMBER(テストの対象)

数値であれば「TRUE」(真)、数値でなければ「False」(偽)の値を返します。引数の「テストの対象」とは、数値データかどうかを調べたいセルのことです。数値であれば、TRUEを返します。

IF関数を使って対象となるデータが数値であればRANK関数で順位を求め、そうでなければ(文字列の場合)空白セルにします。
セル【C3】に以下のように数式を入力します。

=IF(ISNUMBER(B3)RANK(B3,$B$3:$B$13,0),"")

セル【C3】の数式を【C13】までコピーします。

▶ エラー処理では、条件分岐を行うIF関数と「IS」で始まる関数を組み合わせるのが基本となります。
「IS」で始まる関数には以下のようなものがあります。

ISで始まる関数 引数 意味
ISBLANK (テストの対象) セルの内容が空白の場合にTRUEを返す
ISERR (テストの対象) セルの内容が#N/A以外のエラー値の場合TRUEを返す
ISEVEN (数値) セルの内容が偶数のときTRUEを返し、奇数ではFALSEを返す
ISNA (テストの対象) セルの内容が#N/Aの場合TRUEを返す
ISODD (数値) セルの内容が奇数のときTRUEを返し、偶数ではFALSEを返す
ISTEXT (テストの対象) セルの内容が文字列のときTRUEを返す

同得点の順位を振り直す( Sheet:RANK(2) )

RANK関数は順位を求めるものですが、同じ得点が存在すれば同順位になります。成績を付ける場合、同じ点数でも得点の内容によって優劣の差をつけたい場合があります。下図の成績で、漢字の書き取りのほうを重視するなど、別の基準で順位に差を付けるようにすることで順位を振り直すことができます。

得点が「161点」の人が2名いて、順位がどちらも6位になっています。「書き取り」の得点の高いほうを上位にするには、「重み付け」という方法を利用して、同順位を含まない順位を付けます。
セル【E3】にはRANK関数を使って次のような数式が入力され、その数式が【E13】までコピーされています。

=RANK(D3,$D$3:$D$13,0)

F列に書き取りのみの点数で順位を求めましょう。
次の数式をセル【F3】に入力し、セル【F13】までコピーします。

=RANK(C3,$C$3:$C$13,0)

G列にはF列の順位に「0.01」を掛けて重み付けをします。
セル【G3】に次の数式を入力し、セル【G13】までコピーしましょう。

=F3*0.01

G列で求めた「重み」とE列の「順位」を加算した結果をH列に作ります。こうすることによって、同順位でも小数点以下の値によって必ず差がつくことになります。これが「重み付け」の考え方です。
【H3】に次の数式を入力し、セル【H13】までコピーします。

=E3+G3

H列の数値を基に、順位を求めることで、「書き取り」テストのほうを重視した順位を求めることができます。
I列に最終的に求める順位を作成します。セル【I3】に次のような数式を入力し、セル【I13】までコピーします。

=RANK(H3,$H$3:$H$13,1)

Point:

H列のRANK関数は、順位に重み付けをしたものに対して行うので、数値の小さいほうが上位となるよう昇順にしましょう。