複数の条件に当てはまる物の合計を算出！

質問

こんばんわぁ^^　明日が締め切りで・・・もう私の頭では゜゜(´□｀。)°゜。
頭がもう限界の為、質問内容がいつも以上に分かりづらいと思いますが、
明日までにExcelのデータを完成させなくちゃいけなくて・・・　宜しくお願いします。m(__)m

　　　Ａ　　　　　　Ｂ　　　　　 　Ｃ　　　　　　Ｄ
　 孔種別 　　　　Ｌｕ　　　　 　ａ値　　　　　ｂ値
１　１次孔　　　　 2　 　　　　　1.40　　　　 3.5
２　２次孔　　　 　 2　　 　　　　1.20 　
３　１次孔　　　 　 5 　　　 　　 1.60 　　　　 4.2
４　３次孔　　　 　 2 　　　 　　 1.50 　 　　　 5.0
５
６　≪2Luの各計≫
７　１次孔　　　　　（１）　　　　　（２）　　　　　（３）
８　２次孔　　　　　（４）　　　　　（５）　　　　　（６）
９　３次孔　　　　　（７）　　　　　（８）　　　　　（９）

【条件】
　・数値が定期的に変化する為、数値を修正すると自動に各結果も修正されるように、関数を設定すること。
　・ｂ値欄は空白セルがあるが、空白セルもカウントして平均値を算出する。
　（例えば、10行ある内、9行は入力。1行は空白。　通常なら、合計÷9＝平均値となってしまうが、合計÷10＝平均値となるようにしたい）　

　　※ｂ値の欄には「0」という数値もある為、空白セルに「0」を入力する事はＮＧ！
　　　また、画面上では見えるが印刷はされない！という設定も複数の人がこのファイルを利用する為、分かりづらくなるのでＮＧ！

【質問内容】
　ａ）　（１）に、１次孔の２Ｌｕのみの、数の合計を算出したい
　ｂ）　（２）に、１次孔の２Ｌｕのみの、ａ値の平均値を算出したい
　ｃ）　（３）に、１次孔の２Ｌｕのみの、ｂ値の平均値を算出したい

　※（４）～（９）はグラフのイメージが伝われば・・・と思い書いただけなので、気にしないで下さい。

　分かりづらいと思いますが、どうぞ宜しくお願いします。m(__)m

回答No.2

pkh4989 · ベストアンサー

回答が遅くなりましてすみません。
セル(B7) には以下のようにしてください。
１）セル(B7) → =SUMPRODUCT((A2:A5="１次孔")*(B2:B5=2),B2:B5) / 2

>(A2:A5="１次孔")と(B2:B5=2),C2:C5）の間にある「 * 」の意味がよく分からなくて。。。
AND 条件の時→ * OR条件の時→ + を使う場合があります。

お礼

pkh4989サン

こんばんわぁ^^
お忙しい中ご回答頂きありがとうございました。
「*」について？？？だったのですが、理解できることが出来ました。
ありがとうございました。m(__)m

回答No.1

pkh4989

こんにちは。
B列のＬｕが「2」のものを対象にすればいいですよね。
間違ったら、変えてくださいね。
<１次孔>
(1) セル(B7) → =SUMPRODUCT((A2:A5="１次孔")*(B2:B5=2),B2:B5)
(2) セル(C7) → =SUMPRODUCT((A2:A5="１次孔")*(B2:B5=2),C2:C5) / COUNTIF(A2:A5,"１次孔")
(3) セル(D7) → =SUMPRODUCT((A2:A5="１次孔")*(B2:B5=2),D2:D5) / COUNTIF(A2:A5,"１次孔")

２次孔、３次孔は上記の条件を変えれば良いと思います。

>また、画面上では見えるが印刷はされない！という設定も複数の人がこのファイルを利用する為、分かりづらくなるのでＮＧ！
→意味が分かりませんが・・・

補足

pkh4989サン

pkh4989サンに助けて頂いたのは今回で２回目です。　本当にありがとうございました。m(__)m
会社に来て朝一でgooを確認。　でも、返信が0件、　もう今回はもうダメだ・・・と諦めながPMgooを確認したら・・・　
まさか今回もpkh4989サンに助けて頂けるなんて・・・　本当に感謝×２です。（＞▽＜）　ありがとうございました。

解決したんだからいいじゃん！と思うかもしれませんが、いくつか質問したいことがありますので、良かったら教えて下さい。　宜しくお願いします。m(__)m

（１）セル(B7) → =SUMPRODUCT((A2:A5="１次孔")*(B2:B5=2),B2:B5)
　　　 この関数をセル（B7）に入力したら、合計が「２」と出ました。　本来ならセル（B7）には「１」と出るはずなのですが・・・
　　　 今回はこの関数を使わず、「DCOUNTA」という関数を使って合計を求めることが出来ましたが、この方法はチョット手間がかかってしまうのです。
　　　 そこで、「SUMPRODUCT」を使った方法も覚えておきたいので、どこを修正すればいいのか良かったら改めて教えて下さい。

（２）=SUMPRODUCT((A2:A5="１次孔")*(B2:B5=2),C2:C5) / COUNTIF(A2:A5,"１次孔")
　　　 この関数について・・・
　　　 後半部分の「 / COUNTIF(A2:A5,"１次孔") 」は理解出来るのですが、前半部分の「 =SUMPRODUCT((A2:A5="１次孔")*(B2:B5=2),C2:C5) 」はよく意味が分かりません。
　　　「 * 」の記号は掛け算の意味を表しますが、掛け算以外の意味はあるのでしょうか？
　　　((A2:A5="１次孔")*(B2:B5=2),C2:C5)
　　　　↑
　　　(A2:A5="１次孔")　と　(B2:B5=2),C2:C5）の間にある「 * 」の意味がよく分からなくて。。。
　　　もし掛け算を意味するなら、何故ここで掛け算するのカナッ？と思って。

（３）>また、画面上では見えるが印刷はされない！という設定も複数の人がこのファイルを利用する為、分かりづらくなるのでＮＧ！
　　　 この件についてpkh4989サンは「意味がわかりませんが・・・」と書かれていましたが、確かにこれじゃ説明が足りず、分かりづらいですね。　失礼致しました。
　　　 私が言いたかったことは・・・　入力した内容がある設定をすると「 数式バー 」以外では見れないということです。
　　　 説明が上手く出来ないので・・・　「 セルの書式設定 」→「 表示形式でユーザー定義 」→「 種類（T） 」の部分に「 “” 」を直接入力→ＯＫボタン
　　　 この設定にすると「 数式バー 」以外では入力した内容が確認できないんです。(^-^)v

長々と失礼致しましたm(__)m