SUMIF関数、COUNTIF関数

条件を基にデータを解析するための関数

というと、難しそうですが、実際にやって見ると意外と簡単で、非常に便利な関数です。

SUMIF 関数

書式:「 SUMIF(範囲,検索条件,合計範囲) 」

SUMIF 関数は「指定した条件」の合計を計算します。

注釈  :範囲内の、検索条件に一致する数値の合計を求めます。
範囲注釈:範囲には評価の対象となるセルの範囲を指定します。
(言葉を変えると、「指定した条件に合うデータだけを合計する」ということです。)

MS-ヘルプより

検索条件は、計算の対象となるセルを定義する条件を、数値、式、または文字列で指定します。
式および文字列を指定する場合は、">32"、"Windows" のように、半角の二重引用符 (") で囲む必要があります。
合計範囲 実際に計算の対象となるセル範囲を指定します。
合計範囲に含まれるセルの中で、範囲内の検索条件を満たすセルに対応するものだけが計算の対象となります。

      A         B 
 1 エアコンの価格  取り付け手数料 
 2  100,000	     7,000 
 3  200,000      14,000
 4  300,000      21,000 
 5  400,000      28,000
 
 数式      :「 =SUMIF(A2:A5,">160000",B2:B5) 」 
 説明 (計算結果) : エアコン価格が \160000 を超える場合の取り付け手数料の合計を求めます (63,000) 
 ----------------------------------------------------------------------------------------

数式:「 =SUMIF(A2:A5,">160000",B2:B5) 」の解説
=SUMIF(    ) :SUMIF関数です。
A2:A5      :引数1の 「指定した条件の範囲」です。[,]で区切ります。
">160000"    :引数2の 160000より大きい条件です。[""]で囲みます。[,]で区切ります。
B2:B5      :引数3の 合計する範囲です。
([A2:A5]と[B2:B5]は列が異なるだけで、選択する行数は同じです。


左図は、一般的な家計簿です。実際出費科目は10〜20倍はあろうかと思いますが例題ですのでご勘弁願います。
また、1月、2月のみですが1年でも、同じことです。科目ごとに列を増やして整然と記帳しようとすると、横に広がり過ぎてかえって 散漫になろうかと思います。科目数を限定することなど、好みにもよりますが。
そんなとき、SUMIS 関数の出番です。



手順説明
  1. 例えば、E 列に集計したい科目名を書きます。
  2. その右セル、この場合は[F4]を選択します。
  3. 数式バーの[fx]ボタンをクリックします。(WinMeでは、標準ツールバーの[fx])
    「関数の挿入ダイアログ」から、SUMIF 関数を選択します。
  4. 範囲:「D3からD23」まで、ドラッグして選択します。
  5. 検索条件:電気料と記入して、[Tab]キーを押すと電気料は自動的に「""」で囲まれます。
  6. 合計範囲:「C3からC23」まで、ドラッグして選択します。
  7. [OK]ボタンで終了します。
  8. 次の「水道料」に移りますが、「電気料の数式をコピー」しておきます、
  9. 水道料の右[F5]をクリックして、数式バーに「コピーしたものを」貼り付け、電気料を水道料に書き改めます。
    2つ目の方法:[編集]メニュー →[形式を選択して貼り付け]→[テキスト]でも構いません。
    3つ目の方法:電気料の右下隅の小さい□を右クリック、ポインターが[+]で下にドラッグすると、 暫くしてポップアップメニューがでます。 [書式なしコピー(フィル)(O)] (書式なしコピーとは=テキストのことです。)をクリックした上で、”改めて「水道料」のセルを選択” し、数式バーの「電気料」を「水道料」に書き改めます。
  10. 上の作業の繰り返しで、20〜30科目は簡単に集計できます。
  11. 例えば1年分の3000行分の集計にしても、[Ctrl+Shift+End]キー、ノートパソコンでは、[[Ctrl+Fn+Shift+End]を押すと、 瞬時に「記入欄末尾」に到達します。
  12. または、名前ボックスに[A3000]などと記入する方法もありますので、わざわざ、スクロールバーでページをめくらなくても、一発で目的地へ到達 します。
  13. 範囲指定を手書きでするのも、一つの方法です。

COUNTIF 関数

書式:「 COUNTIF(範囲,検索条件) 」

COUNTIF 関数は、「指定した条件」のセルの個数を数えます。

注釈  :指定した<範囲>に含まれる空白以外のセルのうち、<検索条件>に一致するセルの個数を返します。
範囲注釈:範囲にはセルの個数を求めたいセルの範囲を指定します。

MS-ヘルプより

検索条件 計算の対象となるセルを定義する条件を、数値、式、または文字列で指定します。
式および文字列を指定する場合は、">32"、"Windows" のように、半角の二重引用符 (") で囲む必要があります。

第1図

簡単な例題です。6人の男女が居ます。男性の数、女性の数を求めなさい。
女性の数を求めるとき、前項の手順9−3の方法を図示しました。(Excel2003の場合です。)単にオートフィルすると、書式もコピー され、ややっこしくなります。

第2図

2重申し込みなどの重複を発見する

第3図
左図数式バーの「Criteria(クライテリア)」にご注目ください。[B4]から[B19]まで「範囲選択」したのですが、 [$B$4:$B$19]と表示せずに、EXCELが勝手に記述したのです。 翻訳では「評価の基準」ですが、EXCEL上では、「検索条件」になっているようです。(データベース関係関数、例えば[DCOUNT]などでは、 引数3で出現しています。) 第1図と第2図の場合には出なかった現象です。どうしてなのか、分かりません。

仔細に検討すると、[人見賢治]の手前までは、[$B$4:$B$18]と範囲選択ができ、結果も正しくでました。 また、「人見賢治」の後に何名かを追加して行ったところ、行列座標で「範囲選択」されるのです。 「人見賢治」のところを[Del]で消したり、「書式のクリア」をしても駄目でした。 ここで、分かったことは、[Criteria]で範囲指定すると絶対参照の[$]を付けなくても上手くいくことがあるようです。

「B列」が空欄のところは、[0]が表示されますので、[ツール]メニュー →[オプション]→[表示]タブ→[ゼロ値]のチエックを外しました。
一応重複が2とか3とかで表示されました。「条件付書式設定」で、[次の値がより大きい][1]で色付けすれば、 大きな表でも一目瞭然になります。

若しくは、「表示された数値」欄4行目のの右隣をアクティブにしておいて、 [IF]関数を使って、
「 =IF(COUNTIF($B$4:$B$19,B4)>1,"重複",B4) 」(意味:カウントが1以上なら「重複」と表示しなさい。 そうでなければ、カウント[1]者の氏名を表示しなさい。です。)

そうしてから、元の氏名欄の末尾から、重複者の名前を削除して行くと、新しい氏名欄は重複はなくなり。全てが[1]表示になります。


全国住みよい町ランキングで、北相馬郡が6位にランク付けされました。

算出方法

1.基本構成              ( )内数値は全国平均値である
  A県総合満足度 =+A県 ゴミ処理    (0.31)
           + =@病院      (0.17)
           +   窓口サービス  (0.15)
           +   図書館     (0.10)
           +   公園緑化    (0.09)
           +   下水道     (0.07)
           +   国道      (0.05)
           +   地方道     (0.05)
           +   河川砂防海岸  (0.02)
                    合計 (1.00)
(対象外行政サービス:消防署,水道,公民館,防災,公立小,中,保育園幼稚園,成人教育,公営バス
          高齢者福祉,障害者福祉,公営住宅,公営駐車場,市議会議員,公営スポーツ施設 等15)
その他、但し書きや例外処理あり。

私は、利根町は「気候が良く」「自然災害」や「犯罪」の少なさが全国でも、トップクラス(かな?)なので、 6位になったのだと思っていたところ、事実は上記のとおり、人為的産物の内容でありました。

そこで、私の独断と偏見で仮説の「アンケート」表を作成し、[COUNTIF] 関数で、いろいろと「アンケートの集計」を行いました。 集計実例のための表です。

(100年に1度かという卵大の降雹が町の半分を襲いましたが、保険賠償でたっぷり形がついたようなので省略しました。)
(私の家も15〜20万円の損害を蒙りました。70有余年、この町で生活して、天災は昔の堤防決壊2度とこの雹害の3度だけです。)

第4図

上の表で、「同地区複数名」の設定セル番地[N14]は誤りで[N15]が正しいです。

トップへ戻る 

inserted by FC2 system