Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
COUNTIFS関数を使用した冗長な計算式のリファクタリング
投稿日時: 18/06/19 18:16:00
投稿者: run_no_where

COUNTIFS(検索条件範囲1, 検査条件1......検索条件範囲N, 検査条件N) 関数の検索条件範囲は仕様上、1列しか指定できないようで、例えば、
COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, 検索条件範囲3, D:F)
といった集計ができません。
このため、3列を横断的に集計する場合、
COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, 検索条件範囲3, D:D)
+COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, 検索条件範囲3, E:E)
+COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, 検索条件範囲3, F:F)
といった冗長な計算式の記述を余儀なくされます。
これでは、計算式を変更する際に影響範囲が大きくなり、保守性が著しく低下しますし、プログラミングでいうところの DRY(Don't Repeat Your Self:繰り返しを避けること) 原則とも相容れません。
複数列を横断的に集計する場合、どうすれば計算式の冗長化を解消できるでしょうか。

投稿日時: 18/06/19 18:27:54
投稿者: run_no_where

訂正です。
 
> COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, 検索条件範囲3, D:D)
> +COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, 検索条件範囲3, E:E)
> +COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, 検索条件範囲3, F:F)
 
上記は誤りで、正しくは下記でした。<(_ _)>
 
COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, D:D, 検査条件3)
+COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, E:E, 検査条件3)
+COUNTIFS(検索条件範囲1, 検査条件1, 検索条件範囲2, 検査条件2, F:F, 検査条件3)

回答
投稿日時: 18/06/19 18:50:28
投稿者: んなっと

あ,b,50以下
 
  A   B   C   D   E   F
1   条件1 条件2 条件3 条件3 条件3
2 4   あ   a   65   50   14
3    あ   a   52   28   18
4    あ   b   30   40   38
5    あ   b   90   45   80
6    い   a   64   16   33
7    い   a   67   59   35
8    い   b   37   22   34
9    い   b   80   49   47
 
A2
=SUMPRODUCT((B2:B20="あ")*(C2:C20="b")*(D2:F20<=50))
 
SUMPRODUCTの場合、B:Bのような列全体を指定すると処理が重くなるのでB2:B20のようにしています。

回答
投稿日時: 18/06/19 21:39:56
投稿者: sy

質問者さんの提示の式は本当に合ってますか?
 
なんかニュアンス的には、んなっとさんが提示されてるサンプル図で言うと、4と5行目だけが該当して、
2と言う結果を返したいんじゃないかと思うんですけど?

回答
投稿日時: 18/06/20 08:27:37
投稿者: んなっと

COUNTIFSにこだわる場合... 範囲のサイズを揃えればいいので
条件1、条件2の右に2列ずつ挿入して
 
    B C D   E F G   H   I   J
1 条件1    条件2    条件3 条件3 条件3
2   あ      a      65   50   14
3   あ      a      52   28   18
4   あ      b      30   40   38
5   あ      b      90   45   80
6   い      a      64   16   33
7   い      a      67   59   35
8   い      b      37   22   34
9   い      b      80   49   47
 
C2
=B2
右方向・→下方向・↓
F2:G2にコピー貼り付け
 
 
  A   B  C  D   E F G   H   I   J
1   条件1     条件2    条件3 条件3 条件3
2 4   あ あ あ   a a a   65   50   14
3    あ あ あ   a a a   52   28   18
4    あ あ あ   b b b   30   40   38
5    あ あ あ   b b b   90   45   80
6    い い い   a a a   64   16   33
7    い い い   a a a   67   59   35
8    い い い   b b b   37   22   34
9    い い い   b b b   80   49   47
 
A2
=COUNTIFS(B2:D9,"あ",E2:G9,"b",H2:J9,"<=50")
下方向・↓
 
C:D,F:G列非表示

回答
投稿日時: 18/06/20 08:44:12
投稿者: んなっと

あと、こんなのも。
 
  A   B   C   D   E   F G H I
1   条件1 条件2 条件3 条件3 条件3     
2 4   あ   a   65   50   14 1 2 1
3    あ   a   52   28   18     
4    あ   b   30   40   38     
5    あ   b   90   45   80     
6    い   a   64   16   33     
7    い   a   67   59   35     
8    い   b   37   22   34     
9    い   b   80   49   47     
 
G2
=COUNTIFS($B2:$B9,"あ",$C2:$C9,"b",D2:D9,"<=50")
右方向・→
A2
=SUM(G2:I2)

投稿日時: 18/06/27 09:12:00
投稿者: run_no_where

コメントを頂いた皆様、ありがとうございます。
 
結局、計算式のチューニングアップに十分な時間と労力を掛けることができず、妥協せざるを得ませんでした。
不本意な結果となり、申し訳ありません。
修正により、計算式の若干のスリム化は可能と思いますが、性能面では劇的な改善までは見込めそうもなく、工数に見合う費用対効果を実現するのは難しそうです。