データを集計したいのですがどのように数式を作れば？

質問

初めて質問箱に投稿させていただきます。よろしくご指導下さい。

※　シート(1)にデータ、シート(2)に結果を求める。
※　シート(1)のA列に大分類（8項目）、B列に中分類（35項目）、C列に小分類（15項目）、D列に金額　　　を入力します。　　これを2行から99行に色々な組み合わせのデ-タを入力したシートを基にして　　　
※　シート(2)のB列2行～D列2行に、シート(1)のA列～C列のデータの中で組み合わせの１番多い組　　　み合わせを表示し、シート(2)のE列2行にその組み合わせの金額の合計額を表示する。
※　シート(2)のB列3行～E列3行以降の行に、シート(1)の組み合わせの2番目以降の順に表示する　　　という具合に、すべての組み合わせとその計金額を表示する。
※　このような求め方をするためには、どのような数式をつくればよいのでしょうか。ご指導ください。

　シート(1)の例
　　　　　　A列　　　　　　B列　　　　　　C列　　　　　D列
１行　　大分類　　　　中分類　　　　小分類　　　　金　額
2行　　　　３　　　　　　　B　　　　　　　あ　　　　　　１，０００
3行　　　　２　　　　　　　C　　　　　　　あ　　　　　　　　５００
4行　　　　３　　　　　　　B　　　　　　　い　　　　　　　　５００
5行　　　　１　　　　　　　A　　　　　　　う　　　　　 　１，５００
6行　　　　２　　　　　　　C　　　　　　　あ　　　　　　１，０００
7行　　　　３　　　　　　　A　　　　　　　う　　　　　 　１，５００
8行　　　　３　　　　　　　B　　　　　　　あ　　　　　　　　８００
9行　　　　２　　　　　　　C　　　　　　　あ　　　　　　　　６００
10行　　　１　　　　　　　A　　　　　　　う　　　　　　 　　７００
11行　　　３　　　　　　　A　　　　　　　あ　　　　　　 　　２００
12行　　　３　　　　　　　B　　　　　　　あ　　　　　 　１，０００
13行　　　５　　　　　　　A　　　　　　　い　　　　　 　　　１００
14行　　　２　　　　　　　C　　　　　　　あ　　　　　 　　　２００
15行　　　３　　　　　　　A　　　　　　　う　　　　　　　　　７００
～
99行 計　　　　　　　　　　　　　　　　　　　　　　１０，３００

　　シート(2)の例　　（上記シート(1)の例の集計結果をシート(2)の例のように表示したい）

　　　 　　A列　　　B列　　　　C列　　　　　D列　　　　　　E列
1行　　　回 数　　大分類　　中分類　　　小分類　　　金　　額　　
2行　　　　４　　　　２　　　　　　C　　　　　　あ　　　　　２，３００
3行　　　　３　　　　３　　　　　　B　　　　　　あ　　　　　２，８００　　　
4行　　　　２　　　　１　　　　　　A　　　　　　う　　 　　　２，２００
5行　　　　２　　　　３　　　　　　A　　　　　　う　　 　　　２，２００
6行　　　　１　　　　３　　　　　　B　　　　　　い　　　　　　　５００
7行　　　　１　　　　３　　　　　　A　　　　　　あ　　　　　　　２００
8行　　　　１　　　　５　　　　　　A　　　　　　い　　　　　　　１００
9行
10行
～
30行　　　計　　　　　　　　　　　　　　　　　　　　　　　１０，３００

回答No.6

nayuta_lot · ベストアンサー

こんにちは

まず、シート(1)にキーを作成します。
　　　　￣￣￣￣￣

手順１

　Ｅ２に　=A2&B2&C2　と入力します。

手順２

　Ｆ２に

　　=IF(OR(E2="",COUNTIF(E$2:E2,E2)<>1),"",COUNTIF(E:E,E2)+ROW()/1000)

　と入力します。

手順３

　Ｅ２からＦ２を選択してコピーして、Ｅ３からＥ９８を選択して、ペーストします。

ここまでが、キーを作成するための手順です。
次に、シート(2)に集計するための式を入力します。
　　　　￣￣￣￣￣

手順４

　Ａ列の前に１列挿入して、Ａ２からＡ２９に１から２８の連番を入力します。

　そちらの例では、１列目（Ａ列）から回数となっていますが、その前に１列挿入して、
　Ａ列に行番号を入れます。　こうしておいた方が、式がすっきりします。

　また、１行目にタイトル行となっていますが、タイトルの上に行挿入をした場合に
　対応できるように、１番目の数値、２番目の数値と式ではっきりと認識できるように
　しておきます。

手順５

　Ｂ２に、

　=IF(A2>COUNT(Sheet1!$F$2:$F$98),"",LARGE(Sheet1!$F:$F,A2))

　と入力します。

手順６

　Ｃ２に、

　=IF(B2="","",INDEX(Sheet1!$A$2:$C$98,MATCH($B2,Sheet1!$F$2:$F$98,0),1))

　と入力します。

手順７

　Ｄ２に、

　=IF(C2="","",INDEX(Sheet1!$A$2:$C$98,MATCH($B2,Sheet1!$F$2:$F$98,0),2))

　と入力します。

手順８

　Ｅ２に、

　=IF(D2="","",INDEX(Sheet1!$A$2:$C$98,MATCH($B2,Sheet1!$F$2:$F$98,0),3))

　と入力します。

手順９

　Ｆ２に、

　=IF(B2="","",SUMIF(Sheet1!$E:$E,C2&D2&E2,Sheet1!$D:$D))

　と入力します。

手順１０

　Ｂ２からＦ２を選択してコピーして、Ｂ３からＢ２９を選択してペーストします。

これで、基本的な形ができます。

ただし、そちらの例と異なる点が２点ほどあります。

・回数に小数点が付いている。
・同順位の場合、そちらの表示順序とことなる。

回数は、集計の直接的なキーではありませんが、並びかえを使用せずに、回数の大きい順
に表示をさせるには、集計というより、データを読み込む順番のキーとなります。

しかし、同順位が存在すると、正確な読み込みができませんので、行番号を1000で割って
強制的に順位付けを行います。

このため、回数には、小数点が付いています。　この小数点はINT関数で整数化できますが
この数値を使って、大分類、中分類、小分類を読み込んでいます。

この際、

　LARGE(Sheet1!$F:$F,A2)

という、コードを検索値として使用すれば、回数を整数化しておくことも可能ですが、回数は
表示形式で整数のみ見せる、または加工時に整数化すれば、事足りるので、わざわざ、式を複雑
にする必要はありません。

ですから、”，”ボタンで、表示形式を整数に変更すればよいと思います。

また、並び順ですが、これは金額の昇順、降順などで並ぶのではなく、データが存在した行番号
の昇順か、降順かの違いなのでデータの位置によっては、必ずしも例の通りにはなりません。

金額の昇順、降順にしたいのであれば、関数に頼らずに並べ替え機能を使うのが本来の使い方
だと思います。

それから、別の方法として、ピボットテーブルを使用することで簡単に表が作成できます。

シート(1)のキー作成まで行い、Ｅ１に”分類”、Ｆ１に”回数”などとタイトルをつけて
おきます。

１）　Ａ１からＦ９８を範囲選択して、メニューの『挿入』から『ピボットテーブル』を選択します。
２）　新規ワークシートが選択されていると思いますのでそのままＯＫを押します。
３）　ピボットテーブルのフィールドリストにある回数を、値の欄にドラックして持っていきます。

　　　　もし、ピボットテーブルのフィールドリストが消えてしまったら、シートの行ラベルや
　　　　値と書かれたセルを選択すると表示されます。
　　　　それでも表示されない場合には、そこで右クリックして一番下の

　　　　　　フィールドリストを表示する

　　　　を選択します。

４）　回数の集計方法が、『データの個数 / 回数』になっていれば、そのセルの上で右クリック
　　　　　して、データの集計方法から合計を選択します。

５）　金額を回数と同様に値の欄に、回数の下へドラックします。
　　　　『データの個数 / 回数』になっていれば合計に変更します。

６）　分類を行ラベルへドラックします。
７）　行ラベルの▼をクリックして空白の場所の左隣のチェックをはずします。
８）　シートの『合計 / 回数』と書かれたセルの、１行下を、右クリックして
　　　　並べ替え　→　その他の並べ替えオプション　を選択します。
９）　並べ替えオプション　降順　並べ替えの方向　行単位　を選択してＯＫを押します。
１０）Ｂ列とＣ列の全体を選択してセルの書式を、"#,###"で整数化します。

多少、レイアウトは異なりますが、似たようなことはできます。
関数以外にもやり方はあるので、覚えておくとよいと思います。

長くなりましたが、ご参考まで

お礼

お礼が遅くなりすみません。セクセル初心者に分かり易くご説明いただきましてありがとうございました。お陰で求めていた書式を作ることができました。

回答No.5

KURUMITO

シート２の例えばH２セルに次の式を入力してみてください。

=MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)

答えは３となるでしょうか？

I２セルには次の式を入力してみてください。

=INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0))

答えは　2Cあ　と表示されますか？

ご使用のエクセルのバージョンはエクセル２０１０でしょうか？

お礼

何回もお手数をかけました。エクセルのバージョンが２００３となっていましたので、ご指導通りの作業ができなかったと思います。すみませんでした。ありがとうございました。

回答No.4

KURUMITO

回答No2,3です。
MATCH関数ではしばしば小数点を持つ数値が対象になっている場合にはエラーが起こることがあるといわれます。
こちらでの試験では問題がなくともそちらではできないということですので、小数点を扱うことの無いように多少式を変えることにします。
まずシート１の作業列ではE２セルには次の式を入力して下方にオートフィルドラッグします。回答２と同じです。

=A2&B2&C2

F２セルには次の式を入力して下方にオートフィルドラッグします。回答２と違っています。

=IF(E2="","",IF(COUNTIF(E$2:E2,E2)=1,ROUND(COUNTIF(E:E,E2)*100000+10000/ROW(A1),0),""))

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

=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))/100000),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))

今度は成功すると思いますね。

お礼

何度もすみません。ご指導のようにシート１，シート２共に式を変更しましたが、やはり同じ箇所のROWにてエラーがでました。

回答No.3

KURUMITO

回答No2です。
こちらの示した通りに試験されているのでしょうか？
シート２のA２セルには前にも示しましたが次の式を入力して右にオートフィルドラッグしたのちに下方にもオートフィルドラッグすることで問題なく表示されるはずですが。

=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))

この式をシート２のA２セルにコピーして貼り付けても何らの問題はないはずです。
シート１の作業列ではデータが表示されているのでしょうね。

具体的にどこがどのようになったかを示していただけませんか？

お礼

すみません。初心者なものですから。
IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),"")))) の式をシート２A2セルに貼り付け、最初のIFの前に＝を入力すると、後半のMATCH(LARGE(Sheet1!$F:$F,ROW(A1)),のところの式のROWにエラー箇所表示がでます。

宜しくお願いします。

回答No.2

KURUMITO

作業列を作って対応します。
シート１ではE２セルに次の式を入力してE９８セルまでオートフィルドラッグします。

=A2&B2&C2

F２セルには次の式を入力してF９８セルまでオートフィルドラッグします。

=IF(COUNTIF(E$2:E4,E2)=1,ROUNDDOWN(COUNTIF(E:E,E2)+0.5/ROW(A1),5),"")

そこでお求めの表ですがA１セルからE１セルまでにお示しの項目名が有るとして、A２セルには次の式を入力してE２セルまで横にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))

お礼

ありがとうございました。早速入力し実行しようとしましたが、シート２の次の式で実行出来ませんでした。いかが対処したらよろしいでしょうか？　なお、式は頂いたものをコピーにて貼り付けましたが？
IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INT(LARGE(Sheet1!$F:$F,ROW(A1))),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$A:$C,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)-1),IF(COLUMN(A1)=5,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(LARGE(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$D:$D),""))))
宜しくお願いします。

回答No.1

mu2011

一例です。
１）シート（１）のF2に=A2&B2&C2を入力、下方向にコピー
２）シート（２）B1を選択→データ→フィルタ→フィルタオプションの設定、
　　「指定した範囲」を選択、「リスト範囲欄」にシート（１）!A:C、抽出範囲欄にB1、「重複するレコードは無視する」を選択→ＯＫ
３）A1に「回数」入力、A2に=COUNTIF(シート（１）!F:F,B2&C2&D2)を入力、下方向にコピー
４）シート（２）を回数列で降順に並び替え
５）E1に「金額」を入力、E2に=SUMIF(シート（１）!F:F,B2&C2&D2,シート（１）!D:D)を入力下方向にコピー

お礼

早速ご回答下さいましてありがとうございました。エクセルは初心者でご回答頂いた式、手順を良く理解できませんでした。