エクセル関数でグループ単位に集計を行う

質問

エクセル初心者です。

入力伝票データを元に、集計シートに自動的に品名コード順の合計を作成したいのですが、うまくできません。
データシートのデータをソートせずに関数だけで出来る方法をお教えください。
※エクセルはExcel2003を使用しています。

《データシート》例
[見出し]
A1:年月日,B1:伝票番号,C1:品名コード,D1:数量,E1:単価,F1:金額
[データ]
A2:2008/10/1,B2:Z-001,C2:S0001,D2:3,E2:1,000,F2:3,000
A3:2008/10/1,B3:Z-002,C3:S0002,D2:1,E3:2,000,F3:2,000
A4:2008/10/1,B4:Z-003,C4:S0003,D4:2,E4:3,000,F4:6,000
A5:2008/10/2,B5:Z-004,C5:S0002,D5:1,E5:2,000,F5:2,000
A6:2008/10/2,B6:Z-005,C6:S0001,D6:3,E6:1,000,F6:3,000
A7:2008/10/3,B7:Z-006,C7:S0003,D7:2,E7:3,000,F7:6,000

《集計シート》例
[見出し]
A1:品名コード,B1:伝票数,C1:数量計,D1:合計金額
[集計値]
A2:S0001,B2:2,C2:6,D2: 6,000
A3:S0002,B3:2,C3:2,D3: 4,000
A4:S0003,B4:2,C4:4,D4:12,000
※品名コードは固定せず、データシートに入力されたものだけを集計したい

回答No.5

noname#70958

ピボットテーブルを使うのが最短だと思いますが、「関数だけで」というご要望なので……。
------------------------------------------------------------
●甲案：数式一発で

　集計シートについて

　　A2セル：　=IF(ROW()-1>=SUMPRODUCT(1/COUNTIF(データシート!C$2:C$999,データシート!C$2:C$999&"")),"",INDEX(データシート!C:C,1/LARGE(INDEX((COUNTIF(OFFSET(データシート!C$2,,,ROW(データシート!C$2:C$999)-1),データシート!C$2:C$999)=1)/ROW(データシート!C$2:C$999),),ROW()-1)))
　　B2セル：　=IF(A2="","",COUNTIF(データシート!C:C,A2))
　　C2セル：　=IF(A2="","",SUMIF(データシート!C:C,A2,データシート!D:D))
　　D2セル：　=IF(A2="","",SUMIF(データシート!C:C,A2,データシート!F:F))

　としてA2:D2を下方にフィルすれば、ご要望の結果が得られます。
　ただし、かなり重いので、データシートが1000行を超えるような場合には実用にならないかもしれません。
------------------------------------------------------------
●乙案：エラー処理(停止処理)を省く

　集計シートについて

　　A2セル：　=INDEX(データシート!C:C,1/LARGE(INDEX((COUNTIF(OFFSET(データシート!C$2,,,ROW(データシート!C$2:C$999)-1),データシート!C$2:C$999)=1)/ROW(データシート!C$2:C$999),),ROW()-1))

　とすればだいぶ軽くなります。(B,C,D列はA案と同様)
　条件付書式で#DIV/0!を白フォントにすれば、実質Ａ案と同じ結果が得られます。
------------------------------------------------------------
●丙案：作業列を使う

　データシートについて
　　G2セル：　=IF(C2="","",G1+(MATCH(C2,C:C,0)=ROW(C2)))
　として下方にフィル。G列を非表示に。

　集計シートについて
　　A2セル：　=IF(ROW()-1>MAX(データシート!G:G),"",INDEX(データシート!C:C,MATCH(ROW()-1,データシート!G:G,0)))

　とするのがより標準的かつ実用的かと思われます。(B,C,D列はA案と同様)
------------------------------------------------------------
いずれもExcel2003で動作確認。以上ご参考まで。

回答No.4

losedog

品名コード順にする必要がなければ１０分でできたのですが・・

とりあえず，説明は省略します。以下のとおりに入力してください。

１．「データシート」のＡ列の前に２列挿入する（年月日がＣ列になる）
２．Ａ１セルに「出現回数」，Ｂ１セルに「初回連番」と入力（見出しなので，何でもよいのですが・・）
３．Ａ２セルに「=COUNTIF(E$2:E2,E2)」と入力し，下方向へ適宜コピー
４．Ｂ２セルに「1」を入力，Ｂ３セルに「=IF(A3=1,MAX(B$2:B2)+1,0)」と入力し，下方向へ適宜コピー
５．「品名コード」というワークシートを新規作成
６．Ａ１に「初回連番」，Ｂ１に「昇順」，Ｃ１に「品名コード」，Ｎ１に「数値化」と入力
７．Ａ２に「=SUM(A1)+1」と入力
８．Ｂ２に「=IF(N2="","",RANK(N2,N:N,-1))」と入力
９．Ｃ２に「=IF(ISERROR(VLOOKUP(A2,データシート!B:E,4,0)),"",VLOOKUP(A2,データシート!B:E,4,0))」と入力
１０．Ｄ２に「=IF($C2="","",CODE(MIDB($C2,1,1)))」と入力
１１．Ｅ２に「=IF($C2="","",CODE(MIDB($C2,2,1)))」と入力
１２．Ｆ２に「=IF($C2="","",CODE(MIDB($C2,3,1)))」と入力
１３．Ｇ２に「=IF($C2="","",CODE(MIDB($C2,4,1)))」と入力
１４．Ｈ２に「=IF($C2="","",CODE(MIDB($C2,5,1)))」と入力
１５．Ｉ２に「=IF(D2="","",RANK(D2,D:D,-1))」と入力
１６．Ｊ２に「=IF(E2="","",RANK(E2,E:E,-1))」と入力
１７．Ｋ２に「=IF(F2="","",RANK(F2,F:F,-1))」と入力
１８．Ｌ２に「=IF(G2="","",RANK(G2,G:G,-1))」と入力
１９．Ｍ２に「=IF(H2="","",RANK(H2,H:H,-1))」と入力
２０．Ｎ２に「=IF(C2="","",I2*100000000+J2*1000000+K2*10000+L2*100+M2)」と入力
２１．Ａ２～Ｎ２セルを下方向へ適宜コピー
２２．「集計シート」へ移動
２３．Ａ２に「=IF(ISERROR(VLOOKUP(ROW()-1,品名コード!B:C,2,0)),"",VLOOKUP(ROW()-1,品名コード!B:C,2,0))」と入力
２４．Ｂ２に「=IF(A2="","",COUNTIF(データシート!E:E,A2))」と入力
２５．Ｃ２に「=IF(A2="","",SUMIF(データシート!E:E,A2,データシート!F:F))」と入力
２６．Ｄ２に「=IF(A2="","",SUMIF(データシート!E:E,A2,データシート!H:H))」と入力
２７．Ａ２～Ｄ２セルを下方向へ適宜コピー

以上で，ご要望のとおりになるかと思います。

回答No.3

imogasi

だから手作業またはフィルタオプションの設定で、「重複のない品名の行」を作り、そこへ集計の関数を入れる。
２００３までなら
＝SUMPRODUCTs関数で、２条件以上に該当する合計や、件数が出せる。
毎日同じような質問が出ているので、WEBでSUMPRODUCT関数を調べること。
http://www.asahi-net.or.jp/~ef2o-inue/shiki/sub03_030_04.html
など。
上記は「SUMPRODUCT関数」でGoogle照会して、実例の載っているもの。
あるいは「sumproduct関数 OKWAVE」で照会するのもよいかも。

回答No.2

mu2011

一例です。
（１）集計シートのＡ列に品名コード一覧を作成
　　データ→フィルタ→フィルタオプションの設定から、「指定した範囲」を選択、リスト範囲欄にデータシートのＣ列、抽出範囲欄に集計シートのA1、「重複するレコードは無視する」にチェック→ＯＫ
（２）集計シートのB2に=COUNTIF(データシート!C:C,A2)、C2に=SUMIF(データシート!C:C,A2,データシート!E:E）、D2に=SUMIF(データシート!C:C,A2,データシート!F:F）
　　B2:D2を選択し、下方向にコピー

回答No.1

noname#96418

ピボットテーブルが簡単でしょう。複数列の集計が可能です。