[技術]Excelで複数条件にマッチする行の合計値の求め方

Excelを使用して金額などの集計を行う際
複数の条件にマッチする行の合計値を算出するやり方。

Excelマクロを使わず、計算式のみで算出する方法です。

ここ数日、Excelで資料を作る機会があったので
備忘を含めてメモを書いておく。


※※※※※※※※※※※※※※※※※※※※
以降の表に関して閲覧環境により全てが
表示されない可能性があります。
その場合、お手間をかけますが、横画面表示
又はPC環境での閲覧をお願いします。
※※※※※※※※※※※※※※※※※※※※

集計対象となる表

A B C D E
品目 ジャンル 2015/01 2015/02 2015/03
3 パソコン 家電 200,000 20,000 80,000
4 テレビ 家電 220,000 270,000 20,000
5 HDD PC用品 50,000 80,000 10,000
6 洗濯機 家電 300,000 18,000 250,000
7 iPad PC用品 100,000 50,000 220,000

○ジャンルが「家電」である品目の2月~3月分の合計を
 求める方法としては、以下の3つが考えられます。
  1. IF関数、SUM関数を使用して行う
  2. SUMIFS関数を使用して行う
  3. SUMPRODUCT関数を使用して行う

この3つのうち、一番スマートに計算できるのは 3番 となります。
やり方を説明します。

SUMPRODUCT 関数
 書式 : SUMPRODUCT(配列1,配列2,配列3,・・・)
 説明 : 範囲または配列の対応する要素の
       計算し、その計算結果の合計を返します。
       ※ Excelの関数説明より

この関数のいいところは引数(配列1,配列2 など)に対して
計算式を書くことができることと関数の説明にもある通り
配列同士の論理積で計算できることにあります。
※ 論理積とは 中学/高校の数学で学んだ論理演算で
   A AND B : A と B が両方成り立つ
   A OR B : A と Bの少なくとも1つが成り立つ
  などです


よく使われる SUM関数、COUNT関数などの場合
合計範囲や検索範囲のように、あくまで
Excelの範囲を指定することしかできません。

SUM関数
 書式 : SUM(数値1,数値2,・・・)
       SUM(セルの範囲)
       ※ セルの範囲 → C3:C6 のように指定
         (C3セルからC6セルまでの意味)
 説明 : セル範囲に含まれる数値を全て合計する。
       ※ Excelの関数説明より

COUNT関数
 書式 : COUNT(数値1,数値2,・・・)
       COUNT(セルの範囲)
       ※ セルの範囲 → C3:C6 のように指定
         (C3セルからC6セルまでの意味)
 説明 : 範囲内の数値が含まれるセル(範囲)の個数を返す。
       ※ Excelの関数説明より


話を戻して、実際の記載方法ですが
まず、各条件を計算式化します。
 1.ジャンルが「家電」である → IF(B3:B7=”家電”)
 2.期限が「2015/02」である → IF(C2:E2>=201502)
 3.合計値検索範囲 → C3:E7
※ 3の条件に関しては、1と2の条件を満たすもの全てが対象と
  なるため、論理積ではなく論理和となります。

SUMPRODUCT 式に当てはめると、このようになります。
SUMPRODUCT(([1の条件] AND [2の条件]) OR [3の条件])
→SUMPRODUCT((B3:B7=”家電”)*(C2:E2>=201502),(C3:E7))

この計算式が展開されると

A B C D E F
品目 ジャンル 2015/01 2015/02 2015/03 展開式
3 パソコン 家電 200,000 20,000 80,000 ※1 100,000
4 テレビ 家電 220,000 270,000 20,000 ※2 290,000
5 HDD PC用品 50,000 80,000 10,000 ※3 0
6 洗濯機 家電 300,000 18,000 250,000 ※4 268,000
7 iPad PC用品 100,000 50,000 220,000 ※5 0
658,000

SUMPRODUCT(([1の条件] AND [2の条件]) OR [3の条件])
となるので
※1:
 ((TRUEFALSE)200,000)+(TRUETRUE)20,000)+
 (TRUETRUE)80,000))
 =(10200000)+(1120000)+(1180000) = 100000
※2:
 ((TRUEFALSE)220,000)+(TRUETRUE)270,000)+
 (TRUETRUE)20,000))
 =(10220000)+(11270000)+(1120000) = 290000
※3:
 ((FALSEFALSE)50,000)+(FALSETRUE)80,000)+
 (FALSETRUE)10,000))
 =(0050000)+(0180000)+(0110000) = 0
※4:
 ((TRUEFALSE)200,000)+(TRUETRUE)20,000)+
 (TRUETRUE)80,000))
 =(10300000)+(1118000)+(11250000) = 268000
※5:
 ((FALSEFALSE)200,000)+(FALSETRUE)20,000)+
 (FALSETRUE)80,000))
 =(00100000)+(0150000)+(01220000) = 0

100000(※1)+290000(※2)+0(※3)+268000(※4)+0(※5)
=658000

となります。
関数の引数を増やすことで、条件を容易に増やすことが
できるため、複数条件で集計を行いときなどに便利です。


以下、2つの方法に関しても簡単に説明します。
 1. IF関数、SUM関数を使用して行う
 2. SUMIFS関数を使用して行う


1.IF関数、SUM関数を使用して行う
 IF関数を使用して行う場合、条件判定しか行えないため
 作業列を設ける必要があります。
 E列の後ろに、C/D/E それぞれに対応するF/G/H列を設ける。

 F3セルに、以下条件文を入力し、F3~H7にコピーする。
  IF($B3=”家電”,IF(C3>=201502,”1″,”0″),”0″)

 さらにI列を設けて、I4~I7セルにコピーを行う
 行単位での合計値を算出する。
  SUM(IF($F3=”1″,$F3,0)+SUM(IF($G3=”1″,$G3,0)+
  SUM(IF($H3=”1″,$H3,0)

 I列の合計値を求める。
  SUM(I3:I7)

C D E F G H I
2015/01 2015/02 2015/03 C列用条件 D列用条件 E列用条件 行計
3 200,000 20,000 80,000 0 1 1 100,000
4 220,000 270,000 20,000 0 1 1 290,000
5 50,000 80,000 10,000 0 0 0 0
6 300,000 18,000 250,000 0 1 1 268,000
7 100,000 50,000 220,000 0 0 0 0
658,000

2. SUMIFS関数を使用して行う

SUMIFS関数
 書式 : SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2,・・・)
 説明 : 特定の複数条件に一致する数値の合計を求めます。
       ※ Excelの関数説明より

似た関数ではSUMIFというのもあります

SUMIF関数
 書式 : SUMIF(範囲,検索条件,合計範囲)
 説明 : 指定された検索条件に一致するセルの値を合計します。
       ※ Excelの関数説明より

 SUMIFS関数を使用する場合、関数説明にもあるように
 複数条件を指定することができます。
 ただし、合計範囲自体は複数列(行)指定を行うことができません。
 また、合計範囲と条件範囲指定は合わせる(行と行、列と列)必要も
 あります。
 そのため、合計したい列を個別に指定する必要がでてきます。

 ですので、合計値を求める際は
  1.201501 の家電の合計:下表(※1)
  2.201502 の家電の合計:下表(※2)
  3.201503 の家電の合計:下表(※3)

 の合算を求めることとなります。
 ただし、2月~3月分という指定があるため
 1 の合計値は計算せずとも 0 となります。

A B C D E
品目 ジャンル 2015/01 2015/02 2015/03
3 パソコン 家電 200,000 20,000 80,000
4 テレビ 家電 220,000 270,000 20,000
5 HDD PC用品 50,000 80,000 10,000
6 洗濯機 家電 300,000 18,000 250,000
7 iPad PC用品 100,000 50,000 220,000
※1 ※2 ※3
0 308,000 350,000 658,000

※2.201502 の家電の合計
 SUMIFS(D3:D7,B3:B7,”家電”) 又は SUMIF(B3:B7,”家電”,D3:D7)
  20,000 + 270,000 + 18,000 = 308,000
※3.201503 の家電の合計
 SUMIFS(E3:E7,B3:B7,”家電”) 又は SUMIF(B3:B7,”家電”,E3:E7)
  80,000 + 20,000 + 250,000 = 350,000

 ※ 今回のケースでは、SUMIFSで指定可能な列の条件は
  「ジャンル」のみであるため、SUMIF関数を使用する
  でも問題なしです

まとめ

以下、3つの方法を紹介しました。
  1. IF関数、SUM関数を使用して行う
  2. SUMIFS関数を使用して行う
  3. SUMPRODUCT関数を使用して行う

下の方法にいくにつれ、計算式自体は複雑になりますが
汎用性は大きくなっていきます。

簡単に用途をまとめると

方法 表の大きさ 条件数 集計対象
1 小さい 1つ以下 単一行/列
2 大きい 1つ以上 単一行/列
3 大きい 1つ以上 複数行/列

計算式にて合計値を算出する際の用途にあわせて
使い分ければ大量な表の合計値の計算も簡単にかつ
正確に行うことができることとなります。

参考になれば幸いです。

[技術]CEATEC JAPAN 2014 に行ってきました

10月11日、7日~11日で開催されていた
CEATEC JAPAN 2014に行ってきました。

IT業界に身を置いてあることもあり、テクノロジーの
最新動向は気になるので、2010年からこの時期に
なると毎年のように参加しています。

2011年に参加したときに書いた記事
CEATEC JAPAN 2011 に行ってきました

3年前の2011年は
 ・スマートグリッド
 ・3Dテレビ / 3D関連製品
 ・4Kテレビ
 ・BtoBのクラウド技術
 ・センサ関連

といったところがSONY、Panasonic、東芝、富士通
NECなど大手企業をはじめメインとなるブースが
ほとんどでした。
※ 3Dテレビ、もう3年前になるんですね~。

2014年の今年ですが、前評判通りSONYは出展して
いませんでしたがPanasonic、東芝、富士通
NECなどといった企業は出展しており、さまざまな
技術が展示されていました。

出展企業という面ではちょっと面白いと思ったのは
多くの大学研究室の研究技術が紹介されていたり
アジア圏の企業の出展が多かったのに時代の
流れを個人的に感じました。


出展されていた内容ですが
今年の時代を表すかのような内容でした。

2011年の頃と同様にセンサ関連、クラウド関連や
テレビでは4K、8Kなどといったものがまずありました。

Panasonic 4K
SHARP 4K

ただ、それ以上に多かったのは

ウェアラブルデバイス
水素エネルギーの利用

に関連する技術でした。

JINS メガネ ウェアラブル
京セラ
おさいふケータイジャケット

1つ目の写真はJINSメガネの ウェアラブルデバイス。
2つ目の写真は京セラ社のウェアラブルデバイス。
どちらもつけた人間の様々な状態(温度や心拍数など)の
収集を行う。

3つ目はiPhoneでおサイフケータイとして利用できるカバー。

赤ちゃんウェアラブル

こちらは赤ちゃんにつけるウェアラブルデバイス。
足につけているデバイスで赤ちゃんのいろいろな状態を
測定するもの。
また、右側にぶら下がっている丸い装置で赤ちゃんの
ライフログ(ご飯をあげた時間、体温など)を定期的に
測定できるらしいです。
将来的にこの2つは連動するように検討しているらいいです。

ウェアラブルデバイスは大人だけでなく、子供や
赤ちゃん向けのものも研究されているみたいで
すごいなと感じました。


もう1つは 水素エネルギー の利用に関して
TOYOTA 水素カー

数年前くらいにマツダが水素ロータリーというものを
発表して以来、水素を使ってということを耳にすることが
ありませんでした。
しかし、、今回、TOYOTA(上の写真)やHONDA、MAZDAと
いった自動車メーカーが揃って水素を使用した車を
展示していました。
これには個人的にはちょっと驚きでした。
展示で車として形があったということは
そのうち発売されるのでしょうか。

車以外にもスマートグリッド技術に水素エネルギーを
取り入れていたり、HONDAのブースでは車に限らず
「スマート水素ステーション(SHS)」のモックアップが
展示されていました。
これは、水素エネルギーを日常生活に取り入れることを
目的としたものでした。

HONDA SHS

ここ最近では電気自動車や蓄電器などを代表として
電気エネルギーを日常生活に取り込むといったことが
現実になりました。

もしかしたら、遠くない未来では電気に加えて
「水素」を使用する時代が来るのかもしれません。


上で紹介した以外にもたくさんの興味深い展示がありました。
新しい技術に触れるというのは、いちエンジニアとして
楽しいし、刺激を受けます。

自分でも新たな技術を生み出せるくらいに
日々、精進したいとCEATEC を見終わった後に
感じました。

[技術]iPhone6 と Android One

iPhone6

9月19日に iPhone6 がSoftbank/DocoMo/au 各社から
発売されテレビをつけても連日ニュースとなっています。

きっとiPhone6を買われた方、まだ買い替えるかどうか
ちょっと迷っている方などたくさんいるのでしょう。

日本各地で発売日に行列ができているニュースを見て
すごいなぁとしみじみ感じていました。

どちらかというとAndroidユーザなので個人的には
iPhone6発売の少し前の16日にひっそりと発売された
Androidの最新OSであるAndroid LへのUpdateが
確約されている「Andorid One」の方への関心の方が
強かったりします。

Android One
Android L

Googleサービスをよく使用している身からすると
親和性が高く、開発元もGoogleということでサポートに
強いというのはやはり使う側からすると魅力的。

ただ、まだ日本国内での発売は未定な状況ですがどんどん価格が
高くなる最近のスマホに対して、1万円弱という破格の値段は
すごいなと感じます。(国内発売時にこの価格かはわかりませんが)
#国内スマホ昔のガラケーのようにもっと安くなってほしいものです
#割賦制導入により難しいのは重々承知ですが。。。

上記リンク内にも書かれていますが、iPhoneとAndroidスマホ。
まったく、逆の方向性での戦略で売り出していっているというのは
今後も面白くなりそうだなぁと思います。

さて、そんなiPhone ですが、前述のとおり私自身は
Androidユーザなのでこのお祭り(?)的なことにはあまり関係なく
過ごしながらもそれでも、仕事側なのか、IT業界の人間だからなのか
やはり気になってしまい、auショップに行き、iPhone6/iPhone6 Plusを
実際に見て、触ってきました。

第一印象としては、まずどちらもデカいというところでした。
私自身iPhoneを使用しているわけではないので、大きなことは
言えないのですが、なんかiPhoneらしさというのがちょっと
薄れてきているのかなぁなんてのを感じました。

特にiPhone6 Plusの方は5.5インチということもあり
スマホというよりはファブレットといってもおかしくないサイズでした。
通常使用するにはちょっとサイズが大きすぎじゃないのかと思います。
Androidも最近大きい画面のものが主流になっていて見やすくて
いいとは思うのですが、個人的には「う~ん」って思います。

iPhone6 Plus

細かい機能に関しては、店頭で少し触ったくらいで
実用しているわけではないのでなんともなのですが
やはり Androidスマホよりも動きのサクサク感はよかったです。

また、使用範囲はまだ限定されるようですが今回から
Androidではすでに搭載されているNFCに対応したりと
iPhone と Androidスマホ、OSなどの大きな部分では
違うのでしょうけど、細かな機能面(スペック)では
似てきているなぁというのを感じました。

どちらも国内3キャリアから発売されていますし
iPhone と Android これからどうなっていくのか
非常に楽しみです。

そして、私自身、iPhoneを持つ日が来たりするのだろうか。。
(今のところ予定なしですが、触ってみていいなと感じたのは事実かな^^)