Excel 複数の条件ごとに合計したい

質問

度々失礼します。
難しくてどうしてもできません。

↓やりたい事は4つあります↓

　　　　　A　　　　B　　　　C　
1　　　　4121　　　5　　　105%
2　　　　8502　　　23　　 98%
3　　　　7005　　　3 　 200%
4　　　　3425　　　11 80%
5　　　　8536　　　28 150%
6　　　　7765　　　30 112%
7　　　　4663　　　2 88%
8　　　　3987　　　19 79%

※A列の数字は必ず4桁
※C列は％で表示されている

その１
A列の4桁の数字の千の位(一番左の数字)をキーにしてB列の数字を引き抜いたものを合計して出したい。
【例】A列の数字の頭が「4」のB列の数字を足したい。
この場合、A1(4121)とA7(4663)が「4」始まりなので、その右側B列のB1とB7を足した数を関数で出したい。

その２
その１の応用で
【例】A列の数字の頭が「4」のものと「7」もののB列の数字を足したい。
この場合「4」始まりはA1(4121)とA7(4663)、「7」始まりはA3(7005)とA6(7765)なので、合計がB1+B7+B3＋B6となるように関数で出したい。

その３
その１の応用で
A列の4桁の数字の千の位(一番左の数字)をキーにしてC列の数字を引き抜いたものを平均して出したい。
【例】A列の数字の頭が「4」のもののB列の数字の平均を％で出したい。
この場合、A1(4121)とA7(4663)が「4」始まりなので、
C列のC1とC7の平均の数字を出したい(％で)

その４
その２とその３の応用で
A列の数字の頭が「4」のものと「7」もののB列の数字を平均した数字を％で出したい。
この場合「4」始まりはA1(4121)とA7(4663)、「7」始まりはA3(7005)とA6(7765)なので、C1・C7・C3・C6の平均の数字を出したい(％で)

何回か段階を踏んで・・・ではなく、このような式を入れれば一気にに答えが反映される関数式が可能であれば教えてください。

何卒よろしくお願いします！

回答No.4

piro19820122

データ形式がおかしかったりするのかもしれません。また配列数式は確定時は必ず Shift+Ctrl+Enter でないといけません。一度確定後に編集する場合もです。

それと、一度大きなサイズの現データではなく、小さなサイズのサンプルデータで試されてはどうでしょうか？

回答No.3

imogasi

既に答えは出ていますが
２条件付きの加算ですから、SUMPRODUCT関数または配列数式を思い出します。（条件付きの加算の定石）
２００７ならSUMIFS関数。
SUMPRODUCT関数では
例データ
A列　　B列　　　C列
5411 2 100
4713 1 100
5714 2 80
1234 3 70
4832 4 200
3453 2 130
7345 1 110
7100 3 90
7378 1 80
4211 1 120
４千台は
=SUMPRODUCT((LEFT(A1:A10)="4")*B1:B10)
結果
６
４千台＋７千台は
=SUMPRODUCT(((LEFT(A1:A10)="4")+(LEFT(A1:A10)="7"))*(B1:B10))
真中辺りに＋が有るのはOR条件であるから。４千「または」７千台のものを足す。
結果
１１
ーーーー
平均はサンプル数が毎度変わらないのかどうかで、出来る出来ないになると思うが、同じなら字面の数字の平均でよいのかな。
=SUMPRODUCT((LEFT(A1:A10)="4")*(C1:C10))/SUMPRODUCT((LEFT(A1:A10)="4")*1)
配列数式ではAVERAGE関数で出来るだろう。
=AVERAGE(IF(LEFT(A1:A10)="4",C1:C10,""))
と入れて、SHIFT+CTRL+ENTER。
結果
１４０
４＋７の例は略。

補足

返信ありがとうございます。

というわけで最初に回答いただいた方達同様やってみたのですが、エラーが出てしまいます。

原因もわかりません(涙)

回答No.2

maron--5

１）
=SUMPRODUCT((LEFT(A1:A8)="4")*B1:B8)

２）
=SUMPRODUCT((--LEFT(A1:A8)={4,7})*B1:B8)

３）
★率の平均はこの説明・データだけでは計算不能ではないかと思います

補足

(1)、(2)やってみたのですが、できませんでした（；_；）
私の説明が全部悪かったと思うのですが「♯VALUE!」と出てします！

回答No.1

piro19820122

配列数式を使えば解決できると思います。

その１は、
=SUM(IF((A1:A8>=4000)*(A1:A8<5000),B1:B8))
という式で、確定時に Ctrl + Shift + Enter です。

その２も条件が増えているだけですから、条件部を増やすだけです。
=SUM(IF((A1:A8>=4000)*(A1:A8<5000)+(A1:A8>=7000)*(A1:A8<8000),B1:B7))

その３・その４は、SUMがAVERAGEになるだけですね。

どういう理屈かは「配列数式」を調べてみてください。

補足

返信ありがとうございます。
いただいた式を参考にさせていただき、自分でやってみたところ・・・「♯VALUE!」と表示されてしまいました。

実際に私が組んでみた式↓(Ctrl+Shift+Enter後)
｛SUM(IF((B6:B1177>=4000)*(B6:B1177<5000),AO6:AO1177)｝
・B6：B1177にキーとなる4桁の数字が入っています。
・AO6：AO1177に合計したい数字が入っています。

何がだめなのかわかりません・・・(TｏT)