Excelでのデータ集計／関数の組み合わせ等

質問

Excelでのデータ集計／関数の組み合わせ等

いつも、お世話になっております
データ集計用に配列数式や関数を駆使しているのですが
思った通りの集計が出来ません。
以下のような集計方法を教えて頂けますでしょうか。

日付-ＩＤ-数
2010/7/1-AAA-5000
　　　　-BBB-3000
　　　　-CCC-0
2010/7/2-AAA-6000
　　　　-BBB-3000
　　　　-CCC-4000
……

2010/8/1-AAA-4000
……

1月単位でＩＤ毎に数が１以上あるレコードの数と
その合計を取得したいのです。
ex1:2010/7/1-2010/7/31 AAA 31件　100000
ex2:2010/7/1-2010/7/31 CCC 30件　 80000(7/1が0なので件数は１件減る）

{=SUM(IF(MONTH(!$A$1:!$A$500)=1,1,0))}のような式では
日付の無いセルも参照している為、(ID:BBBの前には日付がない)
空白セルが1月と見なされてよけいな数字が入ってしまいます。
(1900/1/0と見なされている)
YEAR関数も併用してみようと思いましたが上手く行きませんでした。

以上、よろしくお願い致します。

回答No.3

KURUMITO · ベストアンサー

ＩＤの数も毎日決まった数である必要もない形にも対応できる方法として次のようにしてはどうでしょう。
シート1はお示しの表でＡ1セルには日付、Ｂ1セルにはＩＤ，Ｃ1セルには件数とそれぞれ項目があり、データは2行目から下方に有るとします。
作業列でＤ2セルには次の式を入力して下方にオートフィルドラッグします。ＩＤの種類が幾つあるかをみるためです。

=IF(COUNTIF(B$2:B2,B2)=1,MAX(D$1:D1)+1,"")

Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(A2<>"",C2>0),DATE(YEAR(A2),MONTH(A2),1)&"/"&B2,IF(AND(A2="",B2<>"",C2>0),LEFT(E1,FIND("/",E1))&B2,""))

そこでお求めの表ですがシート2に表示させることにします。

Ａ列には2010/7/1-2010/7/31 のような表示をさせるとしてＡ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(DATE(YEAR(MIN(Sheet1!A:A)),MONTH(MIN(Sheet1!A:A))+ROW(A1)-1,1)>MAX(Sheet1!A:A),"",TEXT(DATE(YEAR(MIN(Sheet1!A:A)),MONTH(MIN(Sheet1!A:A))+ROW(A1)-1,1),"yyyy/m/d")&"-"&TEXT(DATE(YEAR(MIN(Sheet1!A:A)),MONTH(MIN(Sheet1!A:A))+1+ROW(A1)-1,0),"yyyy/m/d"))

これでシート1に並べられている期間について毎月の期間が表示されます。
次にＩＤをＢ1セルから右横の列に表示させることにして、一つのＩＤあたりに2列を使用することにします。左の列はＩＤの件数で右の列には個数を表示させることにします。
Ｂ1セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(ROUNDUP(COLUMN(A1)/2,0)>MAX(Sheet1!$D:$D),"",INDEX(Sheet1!$B:$B,MATCH(ROUNDUP(COLUMN(A1)/2,0),Sheet1!$D:$D,0)))

Ｂ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR($A2="",B$1=""),"",IF(MOD(COLUMN(A1),2)=1,COUNTIF(Sheet1!$E:$E,LEFT($A2,FIND("-",$A2)-1)*1&"/"&B$1),SUMIF(Sheet1!$E:$E,LEFT($A2,FIND("-",$A2)-1)*1&"/"&B$1,Sheet1!$C:$C)))

お礼

ご回答ありがとうございます。
まさに求めていた通りのデータを得る事が出来ました。

ありがとうございます、助かりました！

回答No.2

MackyNo1

>日付-ＩＤ-数
2010/7/1-AAA-5000
　　　　-BBB-3000
　　　　-CCC-0
2010/7/2-AAA-6000
　　　　-BBB-3000
　　　　-CCC-4000
……

例示されたリストのレイアウトが分かりにくいところがあるのですが、A列に日付、B列にID,C列に数字が入力されているということでしょうか？

その場合は、A列の日付の空白欄にも上と同じデータを入力すれば簡単に集計できます。

空白セルに一括して上と同じ日付を入力するには以下のような操作を行います。

A列のデータ範囲を選択し、Ctrl+Gでジャンプダイアログを出し、「セル選択」で「空白セル」にチェックを入れ、数式バーに一番上の空白セルの1つ上のセル番地を「=A2」のように入力し、Ctrlキーを押しながらEnterしてください。

このようにしておけばSUMPRODUCT関数だけでご希望のデータが自由に集計することができます。
たとえば7月のBBBのデータで数字が０より大きい件数は以下の数式になります。

＝SUMPRODUCT((MONTH(A2:A100）=7)*(B2:B100="BBB")*(C2:C100>0))

お礼

ご回答ありがとうございます。
件数に関して、希望通りのデータは得る事が出来ました。

回答No.1

SaKaKashi

形式の異なるデータを集計することは無理でしょうね。