エクセルのA欄に紐付けしたB欄の合計をA欄の属性ごとに集計したいのですが

質問

下記のような表でＢ欄、Ｃ欄の小計をＡ欄の条件ごとに集計したいのですが
　Ａ　　　　Ｂ　　　Ｃ
１２　　　１４　　２０　　　　A欄１２のB欄合計　C欄合計
１２　　　１５　　２１　　　　A欄１３のB欄合計　C欄合計
１２　　　１６　　２２
１３　　　１７　　２３
１３　　　１８　　２４
１３　　　１９　　２５

A欄１２・１３の間に行を挿入してΣすればよいと思いますが、データが１０００行以上でA欄の条件も１００以上なので、何とか手間を少なく出来ないかと考えております。Ａ欄の属性数値１２とか１３を入力することで簡単に集計できないかと、ネット検索してＤＳＵＭとかＶＬＯＯＫとか挑戦してみましたがダメでした。
よろしくお願いいたします。

回答No.11

nishi6 · ベストアンサー

『集計機能』そのもののように思われます。

質問の表で行なうと、

１．まず、Ａ欄でソートしておきます。

２．表の内部を選択しておいて、データ→集計　を実行。
　　出てきたダイアログボックスで、
　　　→グループの基準を『Ａ』
　　　→集計の方法を『合計』
　　　→集計するフィールドの『Ｂ』、『Ｃ』をチェック
　　　→『現在の集計表と置き換える』にチェック
　　　→『グループごとに改ページを挿入する』はチェックしない
　　　→『集計行をデータの下に挿入する』にチェック

３．すぐ集計が終わります。
　　左にアウトラインが表示されるので、小さな『2』をクリックすれば、質問の答えでしょう。

この集計結果のみをコピーしたい場合は、

４．小さな『2』をクリックして折りたたんだ状態で、表の中を選択し、

５．Ｃｔｒｌ+Ｓｈｉｆｔ+*(コントロールキーとシフトキーを押した状態でアスタリスクキーを押します)でデータをすべて選択

６．編集→ジャンプ→セル選択　でダイアログボックスの『可視セル』を選択しＯＫ

７．Ｃｔｒｌ+Ｃ　でコピーします

８．貼り付ける位置を選択し、Ｃｔｒｌ+Ｖ　で貼り付け

算式など一切使わず、集計できるはずです。

また、集計処理を元に戻すには、表内をクリックした状態で、データ→集計→すべて削除　とします。元の表に戻ります。

お礼

おかげさまで出来ました。こんなに簡単にできるとは！感激です。エクセルって知れば知るほど便利になってくるんですね。有難うございました。

回答No.13

noname#204879

[ANo.10この回答へのお礼]に対する回答、

》 やり方がおかしいのか、…

そのとおりだと思います。
項目名（FLD1、FLD2、FLD3等）を間違いなく入力しましたか？

》 ＲＯＷ（２:２）はどのような意味なのですか？

単に数値 2 を返すためのものです。ROW(A2) や ROW(B2) と同じ結果を得ます。

お礼

有難うございます。項目名の入力が間違っていました。再度やってみます。

回答No.12

hyorono

Ａ　　　　Ｂ　　　Ｃ　　Ｄ　　 　　　 Ｅ　　　　　　 Ｆ
１２　　　１４　　２０　　１　=SMALL(A:A,D1)　=SUMIF(A:A,E1,B:B)
１２　　　１５　　２１　　２　=SMALL(A:A,D2) =SUMIF(A:A,E2,B:B)
１２　　　１６　　２２　　３ =SMALL(A:A,D3) =SUMIF(A:A,E3,B:B
１３　　　１７　　２３　　４　　　・　　　　　　　　　・
１３　　　１８　　２４　　５　　　・　　　　　　　　　・
１３　　　１９　　２５　　６　　　・　　　　　　　　　・
　　　　　　　　　　　　　　　　下へコピー　　　　下へコピー

では、いかがでしょう？
Ｅ列は、Ａ列のリストがＤ列のランク順に表示されます。
候補がなくなり次第、エラーが出るので、その行以上（３００くらい？）の式（ＤＥＦ列）は必要ありません
あ、Ｇ列には =SUMIF(A:A,E1,C:C) を入力し、同じく下へコピーです。
多分、簡単な方法ではないかと思うのですが・・・
（準備列は多いですけど＾＾）

お礼

有難うございました。大変参考になりました。

回答No.10

noname#204879

》 …ＤＳＵＭとかＶＬＯＯＫとか挑戦してみましたがダメでした。

そうですか。それでは、=DSUM()関数を用いる方法を示しておきます。

　　A　　 B　　 C　　D　　 E　　　　F　　 G
1　FLD1　FLD2　FLD3　　　　　　　　FLD1　FLD1
2　　12　　14　　20　　　　　　　　　12　　13
3　　12　　15　　21
4　　12　　16　　22　　 B欄合計→　　45　　54
5　　13　　17　　23　　 C欄合計→　　63　　72
6　　13　　18　　24
7　　13　　19　　25
8

F4: =DSUM($A$1:$C$1500,ROW(2:2),F$1:F$2)
此れを右および下に複写

お礼

有難うございます。やり方がおかしいのか、数値が０と表示されます。
すみません勉強不足で、ＲＯＷ（２:２）はどのような意味なのですか？

回答No.9

imogasi

例データ　Sheet1
A列　　B列
コード 計数
12 1
12 2
12 3
13 4
13 5
13 1
13 2
14 3
（１）A列の重複なきコードの作成
A列A1を指定
データーフィルターフィルタオプションの設定
重複するレコードは無視する
で出てきたものをコピーしSheet2に貼り付け
（２）集計計数
Sheet2のA列　フィルタの結果貼り付けたコード
B２の式
=SUM(IF(Sheet1!$A$2:$A$10=A2,Sheet1!$B$2:$B$10))
と入れてSHIFT+CTRL+ENTERの３つのキーを同時押し（配列数式）
B3,B4にB2の式を複写。
結果
コード 計数集計
12 6
13 12
14 3
C列に当たる計数合計は類推でできると思うので略。

お礼

有難うございました。ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ同時押ししましたら、「値の更新Ｓｈｅｅｔ１」というウィンドウが表示されました。計算結果が０と表示されてしまいましたので、私のやり方がまずいようです。出来るでやってみます。

回答No.8

hirokazu5

No.5に追加。

間に合わせ的な方法でよければ、subtotal関数で代用できます。
上の表なら、
B8のセルに「=subtotal(9,b2:b7)」
C8のセルに「=subtotal(9,c2:c7)」と書きます。
（subtotalのあとの「9」は「合計を出しますよ」との意味）

次に、範囲を全部選択→データ→フィルタ→オートフィルタ。
Aの列のオートフィルタのプルダウンで「12」とか「13」とかを選べば、
B8セルやC8セルに答えが出ます。

Aの全部の条件について合計を出すには不便ですが、
特定のどれかの条件についてだけ合計を出すには簡便です。
ピボットテーブルとの同時使用もできますよ。

回答No.7

ham_kamo

No.6です。すみません、間違えました。

> No.4の回答者の方法でやる場合は、E1,F1にそれぞれ
> =SUMPRODUCT((A1:A1000=D1)*(B1:B1000))
> =SUMPRODUCT((A1:A1000=D1)*(C1:C1000))

ではなくて、

=SUMIF(A:A,D1,B:B)
=SUMIF(A:A,D1,C:C)

でした。失礼いたしました。

回答No.6

ham_kamo

他の回答者の書かれているようにピボットテーブルを使えば一発なのですが、ファイルサイズが大きくなって開くのに時間がかかるようになったりするのが嫌であれば、No.3やNo.4の方のような関数を使った方が軽くなります。

しかし、データが1000行以上でA列の条件も100以上だということなので、条件を１つ１つ抽出して式にあてはめていくのも大変だと思います。そこで、A列の条件を抽出する方法です。

まずA列全体を選択し、コピーし、別シートに貼り付けます。
貼り付けた列にタイトル行がなければタイトル行を先頭に挿入します。
貼り付けた列全体を選択し、「データ」＞「フィルタ」＞「フィルタオプションの設定」を選択します。
出てきた画面で、「重複するレコードは無視する」にチェックを入れ、OKボタンを押します。
重複のないデータが抽出されるので、抽出されたデータをコピーし、元のシートの、たとえばＤ列に貼り付けます。

No.3の回答者の方法でやる場合は、E1,F1にそれぞれ
=SUMPRODUCT((A1:A1000=D1)*(B1:B1000))
=SUMPRODUCT((A1:A1000=D1)*(C1:C1000))

No.4の回答者の方法でやる場合は、E1,F1にそれぞれ
=SUMPRODUCT((A1:A1000=D1)*(B1:B1000))
=SUMPRODUCT((A1:A1000=D1)*(C1:C1000))

と入力し、抽出したデータ分だけ下にコピーすればいいです。

お礼

有難うございました。Ａ列の重複ないデータですっきりと出来ました。

回答No.5

hirokazu5

Excelのバージョンによって微妙に違うのですが、大筋としては、

範囲（左上のAから右下の25まで）をドラッグして選択。
データ→ピボットテーブル
次へ→次へ
レイアウト
「列」の白四角に「A」をドラッグ＆ドロップ
「行」の白四角に「B」をドラッグ＆ドロップ
行の左・列の下の広い白四角に「C」をドラッグ＆ドロップ。
完了
「ｃ：データの個数」とか書いてある灰色のところを右クリックして、
「合計」のところまで動かす。

みたいな感じで、お望みの表が出来ます。
1000行でも10000行でもなんでもござれ。
ただし、Excelのファイルサイズが、これをやると
あっという間に何十メガバイトになります。
パソコンのメモリよりも大きなファイルになっちゃうと扱いきれませんので、それだけは注意。

お礼

有難うございます。ビボットテーブルは今後の挑戦課題です。

回答No.4

hyorono

こんにちは。

A列の12のB列合計 =SUMIF(A:A,12,B:B)
A列の12のC列の合計 =SUMIF(A:A,12,C:C)

のように、表示したいセルに↑の関数を入れてみてください。
ちなみに、13であれば =SUMIF(A:A,13,B:B)というふうに
「,」と「,」の間の数字を変えればよいです。

お礼

有難うございます。簡単なので驚きました。
「,」「,」の間に入力候補が３００あるので３００回関数を入力しますが、簡単に入力候補の数列だけで出来る方法などはないでしょうか？そのくらいの手間はかけなければダメでしょうね。

回答No.3

noname#58440

申し訳ない、脱字があります。
=SUMPRODUCT((A1:A1000=13)*(B1:B1000))
A列が13の時のB列の合計

これが正解です。