こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

EXCEL合計について

Excelの関数についての質問です。

     A列   B列
1行目 1-2-3  1200
2行目 2-3-1  800
3行目 4-5-1  400
4行目 3-2-1  2000
5行目 1-3-2  500
6行目 答え→ ( 4500 )

上記の表があり、B列6行目に条件に応じた計算結果を出したいです。

条件としては、1-2-3、2-3-1、3-1-2など、同じ数字で構成されているものであれば、順番が違っていても同じものとみなして合計します。
※1-2-3は文字列です。
※上記の表では、1-2-3でできる組合せの合計でB6に4500という答えを出してます。

思いつくままに関数を書いてみました。

=SUM(SUMIF(A1:A5,{"1-2-3","1-3-2","2-1-3","2-3-1","3-1-2","3-2-1"},B1:B5))

これでもできなくはないのですが、組合せをたくさん書くのがとても面倒で、
仮に、1-2-3-4 や、1-2-3-4-5 など組合せが多くなった場合はとても上記のやり方では対応できません。また、組合せは1桁だけでなく2桁(1-3-10)、3桁(5-80-100)も存在します。

前回こちらで以下の書き方をおしえていただいたのですが、

=SUMPRODUCT((ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A6,"-",REPT(" ",100)),1,10)),A$1:A$5))*ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A6,"-",REPT(" ",100)),100,10)),A$1:A$5))*ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A6,"-",REPT(" ",100)),200,10)),A$1:A$5))),B$1:B$5)

こちらですと

1-12-3と1-2-3が同類とみなされて一緒に合計されてしまいます。

2桁以上でも対応できるやり方か、他にもっと効率の良い求め方があれば教えて下さい。

投稿日時 - 2011-06-04 00:00:24

QNo.6784613

すぐに回答ほしいです

質問者が選んだベストアンサー

 基本は、前回の御質問(QNo.6779713「Excelのカウントについて(複数条件)」 http://okwave.jp/qa/q6779713.html)に対する各回答にある、SUMPRODUCTを使用した数式中に、

*$B$1:$B$5

を付け加えるだけです。
 例えば、私の回答にある関数を基に、合計値を求める関数を組みますと、次の様になります。

=SUMPRODUCT(ISNUMBER(FIND("-1-","-"&$A$1:$A$5&"-"))*ISNUMBER(FIND("-2-","-"&$A$1:$A$5&"-"))*ISNUMBER(FIND("-3-","-"&$A$1:$A$5&"-"))*(LEN($A$1:$A$5)-LEN(SUBSTITUTE($A$1:$A$5,"-",))=2)*$B$1:$B$5)

投稿日時 - 2011-06-04 08:02:13

お礼

前回にあわせていただいてありがとうございます。
LEN関数が肝ですね。勉強になりました。

投稿日時 - 2011-06-04 09:26:58

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

前回の式にLEN関数を付ければ解決します。
B6セルには次の式を入力します。

=SUMPRODUCT((ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A6,"-",REPT(" ",100)),1,10)),A$1:A$5))*ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A6,"-",REPT(" ",100)),100,10)),A$1:A$5))*ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A6,"-",REPT(" ",100)),200,10)),A$1:A$5))*(LEN(A$1:A$5)=LEN(A$6))),B$1:B$5)

投稿日時 - 2011-06-04 07:34:15

お礼

ありがとうございます。
LEN関数を使うんですね。
できました。

投稿日時 - 2011-06-04 09:23:38

添付図参照

1.A列のデータを、[データ]→[区切り位置]の機能で、C~E列に振り分け
2.セル F2 に式 =SMALL($C2:$E2,COLUMN(A2)) を入力し、此れを右2列にドラッグ&ペースト
3.範囲 F2:H2 を下方にズズーッとドラッグ&ペースト
4.セル I2 に式 =F2&"-"&G2&"-"&H2 を入力し、此れを下方にズズーッとドラッグ&ペースト
5.範囲 A1:I8 を選択してピボットテーブルを作成

投稿日時 - 2011-06-04 07:01:11

お礼

ありがとうございます。
画像までつけていただいて申し訳ないですが、
新しい列の追加や、ピボットは考えておりませんでした。

投稿日時 - 2011-06-04 09:22:18

ANo.1

あれこれ難解な計算式を考えるより、VBAでユーザー定義関数を作ったほうが早くないですか。
それともVBAはNG?

投稿日時 - 2011-06-04 04:43:59

お礼

ありがとうございます。
すいませんVBAはNGです。

投稿日時 - 2011-06-04 09:21:24

あなたにオススメの質問