[関数] 集計用の関数についてご教示ください

質問

こちらの識者の方々にはいつもお世話になっています。
関数の質問です。

環境は下記になります。
OS=windows7 pro 64bit
Office=Excel2010(14.0.7128.5000)

・やりたいこと
図のようなリスト(A3:G7)があり、りんごやバナナなどの項目ごとに誰がいくつか集計したい（A11:C14）
B12:C14の範囲に関数を入力して数字を導き出したいのですが、
その際の関数の範囲はA4:G7を指定したいのです。
（りんごの列の関数の範囲はA4:G5、ばななの列の関数の範囲はA6:G7という風に分けたくない）
Aに1列追加して
A4=りんご担当
A5=りんご個数
というような集計用の列を作成することは可能です。

このような場合どのような関数を使えばよいでしょうか？
もし可能であれば集計用の列を追加した場合としない場合、両方の関数を教えていただければ幸甚です。
（集計用の列をどのような値にするかも併せて教えていただけますでしょうか）

質問に不備不足等ございましたらご指摘ください。
ご面倒お掛けしますがよろしくお願いします。

回答No.6

keithin · ベストアンサー

サンプルの通りに配置すると
B12:
=SUMIF(OFFSET($1:$1,MATCH(B$11,$A:$A,0)-1,0),$A12,INDEX($A:$A,MATCH(B$11,$A:$A,0)+1))
右に下にコピー

ぐらいで。
サンプルのりんごばななの表範囲に，計算範囲を限定するのは実用上得策じゃありません。

＞集計用の

Ａ列に追加しても全然役に立ちません。

敢えて集計用に追加をするなら，たとえば３行目に
Ｃ３：　りんご山田
Ｄ３：　りんご田中
Ｅ３：　りんご山田
Ｆ３：　りんご久保田
Ｇ３：　りんご山田

６行目に一行挿入してばななの表は７：８行に下げ
Ｃ６：　ばなな田中
Ｄ６：　ばなな田中
Ｅ６：　ばなな久保田
Ｆ６：　ばなな田中
Ｇ６：　ばなな山田

となるよう計算式を追加すれば，集計表は単純なSUMIF関数だけで一発計算できますが，たぶんこんなレイアウトでは使いたくないでしょうから回答は割愛します。

お礼

回答ありがとうございます！
教えていただいた関数で問題なくできました。
関数の意味を一つ一つ紐解きながら理解していこうと思います。
たしかに仰るような作業列でないとできなさそうですね。
参考になりました。
ありがとうございます。

回答No.5

bunjii

>B12:C14の範囲に関数を入力して数字を導き出したいのですが、その際の関数の範囲はA4:G7を指定したいのです。
関数の引数でセル範囲を指定するときすべての場面で$A$4:$G$7を使うと言うことですか？
B12セルへ次の数式を入力し、右へC2までオートフィルコピーした直後に下へC15までコピーしました。
=IF($A12="","",SUMPRODUCT((INDEX($A$4:$G$7,MATCH(B$11,INDEX($A$4:$G$7,0,1),0),0)=$A12)*1,INDEX($A$4:$G$7,MATCH(B$11,INDEX($A$4:$G$7,0,1),0)+1,0)))
結果は貼付画像のようになります。
A15が空欄のためB15:C15は""（空欄）になります。
担当の該当が無いときは0が表示されます。（エラーになりません）
0を表示したくないときは条件付き書式で0のときはフォントの色をセルの塗りつぶしの色と同じにすれば良いでしょう。（塗りつぶしなしのときはフォントを白にする）
数式が長くても良ければ次のように変更すれば0のときは””になります。
=IF(OR($A12="",SUMPRODUCT((INDEX($A$4:$G$7,MATCH(B$11,INDEX($A$4:$G$7,0,1),0),0)=$A12)*1,INDEX($A$4:$G$7,MATCH(B$11,INDEX($A$4:$G$7,0,1),0)+1,0))=0),"",SUMPRODUCT((INDEX($A$4:$G$7,MATCH(B$11,INDEX($A$4:$G$7,0,1),0),0)=$A12)*1,INDEX($A$4:$G$7,MATCH(B$11,INDEX($A$4:$G$7,0,1),0)+1,0)))

回答No.4

Safe_Mode

質問に対する直接的な回答ではないのですが…
そもそも集計用データの作り方を工夫すれば、難しい関数など使わなくてもピボットテーブルでいくらでも対応できるのに…と思ってしまいます。
添付画像はピボットテーブルを使うための一例です。

お礼

回答ありがとうございます！
ピボットもたしかに解の一つですね。

回答No.3

msMike

「注文の多い料理店」は宮沢賢治の短編ですが、貴方は“注文の多い質問者”かも知れない(^_^)
セル B12 に次式を入力して、此れを右および下方にオートフィルでＯＫかと。
=SUMPRODUCT((OFFSET($A$4,MATCH(B$11,OFFSET(INDIRECT(CELL("address",data)),,,ROWS(data),),0)-1,2,,COLUMNS(data)-2)=$A12)*OFFSET($A$4,MATCH(B$11,OFFSET(INDIRECT(CELL("address",data)),,,ROWS(data),),0),2,,COLUMNS(data)-2))
【お断り】data は参照範囲(今回は A4:G7)に付けた範囲名だけど、その代わりにアドレス(今回は $A$4:$G$7)を入力しても構いません。

お礼

回答ありがとうございます！
教えていただいた関数で問題なくできました。
関数の意味を一つ一つ紐解きながら理解していこうと思います。

回答No.2

kagakusuki

失礼しました。

>その際の関数の範囲はA4:G7を指定したいのです。
>（りんごの列の関数の範囲はA4:G5、ばななの列の関数の範囲はA6:G7という風に分けたくない）

という条件がある事を見落としておりました。
　その場合、まずB12セルに次の様な関数を入力して下さい。

=IF(OR(B$11="",$A12=""),"",IFERROR(SUMIF(INDEX($C$4:$G$7,MATCH(B$11,$A$4:$A$7,0),),$A12,INDEX($C$4:$G$7,MATCH(B$11,$A$4:$A$7,0)+1,)),0))

或いは

=IF(OR(B$11="",$A12=""),"",IFERROR(SUMIF(OFFSET($C$3:$G$3,MATCH(B$11,$A$4:$A$7,0),),$A12,OFFSET($C$3:$G$3,MATCH(B$11,$A$4:$A$7,0)+1,)),0))

　その上で、B12セルをコピーして、B12:C14のセル範囲に貼り付けて下さい。

お礼

回答ありがとうございます！
教えていただいた関数で問題なくできました。
関数の意味を一つ一つ紐解きながら理解していこうと思います。

回答No.1

kagakusuki

まず、B12セルに次の関数を入力して下さい。

=SUMIF($C$4:$G$4,$A12,$C$5:$G$5)

　次に、C12セルに次の関数を入力して下さい。

=SUMIF($C$6:$G$6,$A12,$C$7:$G$7)

　そして、B12～C13のセル範囲をコピーして、B13～C14のセル範囲に貼り付けて下さい。