Excelで複数条件での合計値を求めるには

質問

過去の質問をみていろいろやってみましたが、どうしてもできませんでした。。

　　　Ａ　　　　Ｂ　　　　　Ｃ
1　工事件名　　内　　　容　 　
2　物件Ａ　　　工程名　掘る
3　　　　　　　　期間　　1/1-1/4
4(A2-4結合） 　工数　　0.2　　　
5　物件Ａ　　　工程名　土台作り
6（A5-7結合）　期間　　1/5-1/25
7　　　　　　　　工数　　0.7
8　物件Ｂ　　　工程名　掘る
9　　　　　　　　期間　　1/2-1/10
10　　　　　 　　工数　　0.3
　　・
　　・

15　　　　　　物件A　（工数合計）

「物件ごとの工数合計」をとりたいのです。
関数を下記の通り書いていますが、エラーになります。
=SUMPRODUCT(($A$2:$A$10="物件A")*($B$2:$B$10="人月")*($C$2:$C$10))

Ｃ列に文字が混ざっているのがもんだいなのでしょうか・・

どうか知恵を貸して下さい。

よろしくお願いします。

回答No.6

telescope · ベストアンサー

セルを結合した場合、結合した複数のセルの左上端の値が保持されます。
結合するセルすべてに何らかのデータ値を入れて「書式」－「セル」の[配置]で[セルを結合する]を選択すると上記のメッセージがでます。
つまり、工事件名が入力された行のC列が文字列であるために、SUMIF関数もSUMPRODUCT関数もカウントしません。
この表のままで使うなら、工数のある行に工事件名を入力する必要があります。
また、式　=SUMPRODUCT(($A$2:$A$10="物件A")*($B$2:$B$10="人月")*($C$2:$C$10))に「任月」と入っていますが、「工数」の間違いでしょうか？
A列に工事件名「物件A」が入力されていれば、A列が「物件A」のC列の値を合計するので
($B$2:$B$10="工数")の部分は不要です。

なお、データは1件一行で作成するのが原則です。そのほうがずっと管理しやすくなります。
空いた列、たとえばE列に移し変えてみましょう。
E1セルに「工事件名」、F1に「工程名」、G1に「自」、H1に「至」、I1に「工数」とタイトルを入力します。
期間は、開始日と終了日を分けておいたほうが、工事の期間の検索も出来ますし、日数などの計算もできます。
工事件名　　工程名　　自　　至　　工数

E2に　=INDIRECT("A"&ROW()*3-4)
F2に　=INDIRECT("C"&ROW()*3-4)
G2に　=INDIRECT("C"&ROW()*3-3)
I2に　=INDIRECT("C"&ROW()*3-2)
と式を入力して、下方向にフィルします。

G2に
=LEFT(INDIRECT("C"&ROW()*3-3),FIND("-",INDIRECT("C"&ROW()*3-3),1)-1) H2に
=MID(INDIRECT("C"&ROW()*3-3),FIND("-",INDIRECT("C"&ROW()*3-3),1)+1,4)
とすれば、開始日と終了日を分けられます。

新しい表全体をコピーして、別のsheetに「編集」－「形式を選択して貼り付け」で「値」のみを貼り付けます。
値のみ貼り付けた後なら、
G2に　=INDIRECT("C"&ROW()*3-3)　であっても
G列を選択して、「データ」－「区切り位置」で「カンマ～」を選び「次へ」ボタンを押し、「その他」の右の欄に「－」を入れて「完了」で開始日と終了日を分けられます。

1件一行で作成してあれば、「データ」－「フォーム」を選ぶと、1件ずつカード型データベースのように表示することも出来ます。
検索や今回のような計算も簡単になります。
また、「データ」－「ピボットテーブル」で
「行のフィールド～」に「工事件名」を、「データアイテム～」に「工数」をドラッグすれば、計算式を使わなくても簡単に集計できます。

回答No.5

papayuka

結合セルは範囲の左上のセルに値を保持します。
つまり、結合してあると A列が「物件Ａ」で B列が「工数」という行は無いのです。

データ構造が一定ならば、配列（引数）のデータ数だけ合わせてやれば取り合えず結果は得られるようです。
つまり、A列の2～13行までと、B・C列の4～15行を使うって事です。

A2:A13の範囲で「物件Ａ」は 0 から始まる Indexだと 0 と 3 となり、
B4:B15の範囲で「工数」は 0 から始まる Indexたと 0 と 3 と 6 と 9 となる訳です。
A列が「物件Ａ」で B列が「工数」の Index が 0 と 3 で、C4:C15 の Index 0 と 3 の数値を加算させれば良いと言うことになります。

=SUMPRODUCT((A2:A13="物件Ａ")*(B4:B15="工数"),C4:C15)
=SUMIF(A2:A13,"物件Ａ",C4:C15)

上記のいずれも Excel2000 で試したところ正しいと思われる結果が返されましたよ。

お礼

ご回答ありがとうございました！

C列の参照場所が問題だったんですね。。

目からウロコでした。

ありがとうございました。

回答No.4

imogasi

（１）＞SUMPRODUCT(($A$2:$A$10="物件A")*($B$2:$B$10="人月")*($C$2:$C$10))
の人月は工数のミスタイプか。築いたら訂正を入れること。大切な箇所だから。
（２）エクセルでは、このような表構成は後で使いにくいと思います。
エクセルの知識経験で一番大切な点です。将来は作る前に表構成に熟慮を。
上と同じ値の行も空白にするのも使いにくい。
同じ値は白色文字にする（条件付書式）のを見たことがあるが、ビジネスでは常識化しているよう。
（３）セル結合も関数利用時には弊害が多い。
関数ではセル結合をしているとか、してないとか捉えることができないと思うので、物件Xを捕まえるのが難しい。
ーーーー
例データ
工事件名 内 容
物件Ａ 工程名 掘る
期間 1/1-1/4
工数 0.2
物件Ａ 工程名 土台作り
期間 1/5-1/25
工数 0.7
物件Ｂ 工程名 掘る
期間 1/2-1/10
工数 0.3
質問の通りに、工事件名は、セル結合している。（A2,A3.A4）（A5,A6,A7)・・
データのあるセル以外に（下記理由から、第１２行までは避けて）
=SUM(IF((A2:A10="物件Ａ")*(B4:B12="工数"),C4:C12,""))
と入れて、SHIFT+CTRL+ENTERを同時に押す（配列数式）
結果０．９
配列数式は質問者には難しいかも。
A2:A10とB4:B12のようにずらす例は初めてだが、うまく行くようです。
SUMPRODUCTでもうまくいくように思うが。
意物件名あたり３行は一定していないと（バラバラだと）この回答はダメ。

お礼

ご回答ありがとうございました。

配列数式はやったことがないので、
これから勉強します。

当初表を作成したときは、集計を
考慮していなかったので、集計をしずらい
形態になっています。。

表の作り方も含めて勉強します！！
ありがとうございました。

回答No.3

kamejiro

＃１です。

試す際にもＡ列は３つのセルを結合して正しい数値が出ました。

私の試した式をよ～く見て下さい。Ａ列の領域は「$A$2:$A$13」、Ｃ列の領域は「$C$4:$C$15」です。
つまり、Ａ列のセルに対するＣ列の対応は、A2ならC4、A5ならC7です。
行数が２つずれていることがポイントです。

お礼

あ！失礼いたしました。

Ｃ列の領域は「$C$4:$C$15」なんですね。

うまく結果がでました！！！

勉強になりました。

ありがとうございました。

回答No.2

grumpy_the_dwarf

試してみましたが、A列が結合された状態では、
=SUMPRODUCT((A2:A15="物件Ａ")*(B2:B15="工数"),C2:C15)
=SUMIF(A2:A15,"物件Ａ",C2:C15)
のいずれでも結果は0になります。

A列を結合せず、条件付き書式で隠すなどの工夫をすることで解決す
るかもしれません。

お礼

ご回答ありがとうございました。

０となるのは、結合しているからなのでしょうか？

ＳＵＭＩＦ意外でも複数検索の合計の出し方を
ご存じでしたらぜひ教えて下さい。

セルの結合をちょっといじってみます！！

回答No.1

kamejiro

試してみました。

=SUMIF($A$2:$A$13,"物件A",$C$4:$C$15)

って関数でうまく「物件Aごとの工数合計」が出るかと思います。

お礼

ご回答ありがとうございます。
教えていただいた式をあてはめましたが、
０　となってしまいます。。

なんでなのか、原因がわからず頭をひねっています。。

もうちょっといじってみます！