SUMIFS関数のＯＲ条件をＩＦ関数で切り替える

質問

A B
1 S-B
2 S 10
3 A 20
4 B 30
5 C 40
6 D 50
7 E 60
8 集計 0

上記のようなデータテーブルがあり、セルＡ１はプルダウンで
A・S-A・S-B・S-C・S-D・S-Eの６種類変更できます。
集計を出力するセルＢ８の演算は以下の通りです。
=SUM(SUMIFS(B2:B7,A2:A7,IF(A1="S","S",IF(A1="S-A","{S,A}",IF(A1="S-B","{S,A,B}",IF(A1="S-C","{S,A,B,C}",IF(A1="S-D","{S,A,B,C,D}","{S,A,B,C,D,E}")))))))
この例だと、セルＢ８の答えには６０が欲しいのですが、条件範囲の文字に対し、文字{S,A,B}の比較になる為０となっています。
実際のデータテーブルは他のデータも有り、条件も複数有ります。
質問は、配列でのＯＲ条件として認識される切り替え方法です。
この例では、ＩＦにより範囲を指定したＳＵＭ演算を切り替えれば良いのですが、質問部分をフォーカスする為の例としています。
よろしくお願いします。

回答No.6

kkkkkm · ベストアンサー

No.5のユーザー定義関数は
地域名称の条件も有るとのことでしたので指定はS,A,Bのようにカンマ区切りでとなりましたが

もしABCDEFのようなアルファベットが並んでいるだけのデータでしたら

以下のようにしておくとS-B(S,A,Bと判断)やS-C(S,A,B,Cと判断)のような指定でも可能です。
Function MySum(SStr As String, FRange As Range, SRange As Range) As Long
Dim c As Range, TmpStr() As String
Dim Tmp As Long, i As Long, j As Long

ReDim TmpStr(Asc(Right(SStr, 1)) - Asc("A") + 1)
j = 0
TmpStr(j) = Left(SStr, 1)
For i = Asc("A") To Asc(Right(SStr, 1))
j = j + 1
TmpStr(j) = Chr(i)
Next
For i = LBound(TmpStr) To UBound(TmpStr)
For Each c In FRange
If c.Value = TmpStr(i) Then
Tmp = Tmp + Cells(c.Row, SRange.Column)
End If
Next
Next
MySum = Tmp
End Function

回答No.5

kkkkkm

> ただ、演算式がかなり長くなり、スマートでは
> 有りません。良い方法があればご教授願います。

S-BやA-Bなどがあるランダムだと勘違いしていましたが
指定したSの後のA,Bは範囲として連続していなくてバラバラだという事で

S-Bの場合の
=SUM(SUMIFS(B2:B7,A2:A7,{"S","A","B"}))
は最終的に
=SUM(B2,B3,B4)
ではなく
=SUM(B2,B4,B6)
になることもあるということでしたら

配列にセル参照はできないという記述をどこかで見たように思いますので
=SUM(IF(A1="S",B2,IF(A1="S-A",SUMIFS(B2:B7,A2:A7,{"S","A"}),IF(A1="S-B",SUMIFS(B2:B7,A2:A7,{"S","A","B"})))))以下略
というように並べていくしか思いつきません。

A1にS-BではなくてS,A,Bのように指定ができるのでしたら

ユーザー定義関数を作成して
Function MySum(SStr As String, FRange As Range, SRange As Range) As Long
Dim c As Range, TmpStr() As String
Dim Tmp As Long, i As Long

TmpStr = Split(SStr, ",")
For i = LBound(TmpStr) To UBound(TmpStr)
For Each c In FRange
If c.Value = TmpStr(i) Then
Tmp = Tmp + Cells(c.Row, SRange.Column)
End If
Next
Next
MySum = Tmp
End Function

式を
=MySum(A1,A2:A7,B2:B7)
にすればいけると思います。

お礼

kkkkkmさん
配列のセル参照は無理だったり、また、ｏｒｓ条件として｛"S","A"｝と｛"S","A","C","D"｝等の切り替えも制約があるのでしょうね。ご回答頂いた、ユーザ定義FUNCTIONプロシージャは、私が使用したことがないので、現在運用を始めた演算式が簡素化できるよう、勉強して試してみます。
最後までありがとうございました。

回答No.4

Chiquilin

失礼。真ん中の式は「"B"」が一つ抜けてました。
=SUM(SUMIFS(B2:B7,A2:A7,CHOOSE(MATCH(A1,{"A","S-A","S-B","S-C","S-D","S-E"},0),"A",{"S","A"},{"S","A","B"},{"S","A","B","C"},{"S","A","B","C","D"},"*?")))

お礼

ご回答ありがとうございました。配列の切り替えは制限があるみたいですね。演算式としてはかなり長くなりましたが、結局、ＩＦとsumifsで各ランクの集計をプラスする方法で実施しました。
又の機会も、ご協力お願いします。

補足

Chiqilinさん、ご回答ありがとうございます。
IFでの分岐については、試みてダメだったのでこの質問をさせて頂きました。なので、ご教授頂いたCHOOSE+MATCHを試してみました。MATCHでインデックスを求める部分は正常ですが、配列を選択するのに何か制約があるのでは？と感じています。（セルに配列を入力して、セル参照もダメでした）
実際の関数は
=SUM(SUMIFS(INDIRECT($B$2&$B$4&"!AG6:AG4000"),INDIRECT($B$2&$B$4&"!D6:D4000"),$E7,INDIRECT($B$2&$B$4&"!A6:A4000",*******))))))
であり、同一フォームのデータが、別のシートに複数あります。シート名は年＋月となっておりＢ２セルとＢ４セルでシートを選択。選択されたシートのＤ６～Ｄ４０００にお客様コードでＡＧ６～ＡＧ４０００が売上予定額、Ａ６～Ａ４０００が見込めるランクとなっています。お客様コードはＥ７セルで選択し、各ランクは、先に例で上げたように、ＳランクからＥランクまでを切り替えて集計。と言うものを考えています。その切り替え部分が上記関数内の
*******部分になり、ここがうまく機能しません。
制約上無理なのか、こういう場合はこの様にしないとダメとか、が解らずモヤモヤしています。よろしくお願いします。

回答No.3

Chiquilin

「=SUM(SUMIFS(……))」というやり方からしてスピルに対応していない
バージョンであるとの想定で回答します。

まず配列定数の書き方が間違っています。

「"{S,A,B,C}"」ではただの文字列です。
→「{"S","A","C"}」

書いている数式をそのまま修正するなら

=SUM(SUMIFS(B2:B7,A2:A7,IF(A1="S","S",IF(A1="S-A",{"S","A"},IF(A1="S-B",{"S","A","B"},IF(A1="S-C",{"S","A","B","C"},IF(A1="S-D",{"S","A","B","C","D"},{"S","A","B","C","D","E"})))))))

長ったらしいのでまとめると

=SUM(SUMIFS(B2:B7,A2:A7,CHOOSE(MATCH(A1,{"A","S-A","S-B","S-C","S-D","S-E"},0),"A",{"S","A"},{"S","A","B"},{"S","A","C"},{"S","A","B","C","D"},"*?")))

ただほんとうにこんなアプローチの仕方でいいのか疑問に思います。
PowerQueryでやったら何か問題があるんでしょうか。

あとこの条件だけなら
=SUM(MMULT((A2:A7={"A","S","B","C","D","E"})*B2:B7,N({1;2;3;4;5;6}<=FIND(RIGHT(A1),"ASBCDE"))))

こんな式でも一応結果は出ます。

回答No.2

kkkkkm

> 実際のデータはS-Eの並びはランダムであり、又、地域名称の条件も有ります。

回答した式でランダムとか関係ありますか？
「-」の前後の文字列を検索してその行間のSUMを出しているだけです。

> なので、sumifsの検索条件{A,B},{A,B,C}などの配列ＯＲ条件をＡ１セルの表示で切り替える方法をご教授頂きたいのが希望です。

たとえばS-Bの時にSUMIFSでどのような計算式になるのが希望なのでしょう？

回答した式の場合S-Bの時に
=SUM(B2:B4)
となります。

補足

kkkkkmさん
A1セルでSを選択した場合
=SUM(SUMIFS(B2:B7,A2:A7,"S"))
S-Aを選択した場合、
=SUM(SUMIFS(B2:B7,A2:A7,{"S","A"}))
S-Bを選択した場合、
=SUM(SUMIFS(B2:B7,A2:A7,{"S","A","B"}))
のように、検査範囲A2～A7の検査値を
切り替えたいと言うのが希望なのですが、
配列数式（ここではＯＲ条件に使用）は
制限があって無理なのでしょうか？
取りあえず、A1のＩＦ条件で"S"、"A"～"E"の
集計結果をプラスする方法で、運用しました。
ただ、演算式がかなり長くなり、スマートでは
有りません。良い方法があればご教授願います。

回答No.1

kkkkkm

A1で示された範囲をSUMすればいいのでしたら
S-CはS(A2)からC(A5)までSUMなど

=IF(A1="S",B2,SUM(INDIRECT("B" & MATCH(LEFT(A1,FIND("-",A1)-1),A1:A7,0) & ":B" & MATCH(RIGHT(A1,LEN(A1)-FIND("-",A1)),A1:A7,0))))

お礼

お忙しい中、回答頂きありがとうございました。

補足

kkkkkmさん、早速のご回答ありがとうございます。
例では、列の記号が行番号のところからに、ずれていたにもかかわらずご理解頂き感謝いたします。
さて、ご回答頂いた内容ですが、実際のデータはS-Eの並びはランダムであり、又、地域名称の条件も有ります。
なので、sumifsの検索条件{A,B},{A,B,C}などの配列ＯＲ条件をＡ１セルの表示で切り替える方法をご教授頂きたいのが希望です。