Excelの数値範囲を指定したクロス集計

質問

Excel2010です。
やりたいことは、
フィールドA（文字列）、フィールドB（文字列）、フィールドC（文字列）、フィールドD（数値）、フィールドE（数値）、というデータがあり、フィールドDの値を「1未満」「1以上10未満」「10以上30未満」「30以上」と範囲を分けて、フィールドA、B、Cのそれぞれの組み合わせにおけるフィールドDの合計値を求める
ということです。

フィールドA＝"あ"且つフィールドB＝"イ"且つフィールドC＝"山"且つフィールドDが１以上10未満のレコードのフィールドEの合計値、というような全ての組み合わせです。
目標の形式としては、ピボットテーブルは、列にフィールドA、Bを、行にフィールドC、Dを設定します。この時、フィールドDのすべての値が羅列することになりますが、上記のように数値範囲を指定して集計したいです。

範囲指定してその合計値を出すにはSUMIFでできるようですが、ピボットテーブルと組み合わせて使えればいいのかなと思ってますが。。。やり方が分かりません

もし、一発では無理というのであれば、先に別処理をすることで可能であればその方法でもいいですので、教えてください。

回答No.1

keithin · ベストアンサー

=SUMIFS(E:E,A:A,"あ",B:B,"イ",C:C,"山",D:D,">="&1,D:D,"<"&10)
うち「＞＝＆１」「＜＆１０」の部分は、前に回答したのと同様実際にはセルに記入しておいて作成してください。

お礼

遅くなりましたが、ありがとうございます！

回答No.6

Nouble

どうもです
http://sdrv.ms/17BmYjUの
「数値範囲指定クロス集計」タブに書いておきました

全展開するとアップロードできなくなるので
式を下方にフィルすると
全展開できるようにしておきました

ですので、
G2:K2を必要なっだけ下にフィルしてください
但しWEB APP上ではしないで下さいね。

A:Eは、乱数でデータを作らさせています。
これも下方にフィルすると、
G:Kに集計がぽつぽつと出始めます。

ダウンロードして試してみて下さいね。

O:Rは、
閾値を示す場所です。
各行最終データの直下に
2行目の値を入れておいて下さいね。

これにより、
G:Kがフィルで再展開できるようになっています。

如何でしょうか？
お役に立てていたならば幸いです。

お礼

遅くなりましたが、ありがとうございます！

回答No.5

MarcoRossiItaly

No.3・4 です。たびたび申し訳ありません…。E4 セルの不等号から「=」を抜かしてしまいましたので、修正お願いします。

E4　=(C$1<=a4)*1

お礼

遅くなりましたが、ありがとうございます！

回答No.4

MarcoRossiItaly

No.3 です。すみません、No.3 の中で「B1、C1、E1」と書いたのは、「B3、C3、E3」のことなので、訂正します。

お礼

遅くなりましたが、ありがとうございます！

回答No.3

MarcoRossiItaly

ピボットテーブルをお考えなら、集計の対象の表に列を追加し、数式によってフラグを立てておくのがラクかと。

A1:C1 のセル範囲に「1」、「10」、「30」が入力されているなら、次式。D 列は、C 列を入力後、それを右方向にドラッグしてオートフィル。

B1　="x＜"&a1
C1　=a1&"≦x＜"&b1
E1　=c1&"≦x"

B4　=(a4<A$1)*1
C4　=(A$1<=$A4)*($A4<B$1)
E4　=(C$1<a4)*1

お礼

遅くなりましたが、ありがとうございます！

回答No.2

KURUMITO

Ａ列からＥ列までの2行目から下方にデータが入力されているとします。
Ａ列からＣ列までの文字列の組み合わせはたくさんあると思われますのでそれらの組み合わせをもれなく表示させた上で、4つの条件のもとでの集計を表示させるためには初めに作業列をＦおよびＧ列に設けます。
Ｆ2セルには次の式を入力して下方にドラッグコピーします。

=A2&B2&C2

Ｇ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(F$1:F1,F2)=0,MAX(G$1:G1)+1,"")

Ｉ2セルには次の式を入力して下方にドラッグコピーして文字列の組み合わせを表示します。

=IF(ROW(A1)>MAX(G:G),"",INDEX(F:F,MATCH(ROW(A1),G:G,0)))

そこでそれらの文字列の組み合わせでのＤ列における数値の条件でのＥ列の値の集計ですがＪ1セルには1未満、Ｋ1セルには1以上10未満、Ｌ1セルには10以上30未満、Ｍ1セルには30以上とそれぞれ文字列を入力します。
Ｊ２セルには次の式を入力してＭ２セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($I2="","",IF(COLUMN(A1)=1,SUMIFS($E:$E,$F:$F,$I2,$D:$D,"<1"),IF(COLUMN(A1)=2,SUMIFS($E:$E,$F:$F,$I2,$D:$D,">=1",$D:$D,"<10"),IF(COLUMN(A1)=3,SUMIFS($E:$E,$F:$F,$I2,$D:$D,">=10",$D:$D,"<30"),SUMIFS($E:$E,$F:$F,$I2,$D:$D,">=30")))))

お礼

遅くなりましたが、ありがとうございます！