EXCELで項目ごとに計算したい

質問

検索したのですが、どうもうまくひっかからないので
質問させて頂きます。お知恵拝借、お願致します。

EXCELで作成している下記のようなデータがあります。
(毎月１回で、1回につき数百～３万程度出てきます。
機種名、クラス名は、その時々でいくつ出てくるか変わります。)

機種名　台数　クラス
XXX　　　1　　　A
YYY　　　 2　　　B
XXX　　　3　　　C
ZZZ　　　1　　　A
YYY　　　2　　　B
　：　　　　：　　　：

と言ったデータを

クラス　
A　　　　　XXX　　1
　　　　　　ZZZ　　1

B　　　　　YYY　　4

C　　　　　XXX　　3

と言ったように計算して提出するマクロを作りたいと
考えています。ピボットも考えましたが
どうもうまく考えが浮かびません…

こんな考え方があるよとか、参考になるURLなど
教えて頂けますでしょうか？

宜しくお願い致します。

回答No.1

marcy1 · ベストアンサー

エクセルのヴァージョンにもよると思いますが、
SUMIFS
SUMPRODUCT
とかが使えるのではないかと。

お礼

とある業務で必要だったので、この質問を立てたのですが
その業務が無くなってしまい、質問を締め切るのを失念しておりました…
親身に教えて頂いたのに、お礼が遅れ申し訳ありません…

>SUMIFS
>SUMPRODUCT
>とかが使えるのではないかと。

そうですね！こういう関数がありました！
一番の回答ありがとうございます。
甲乙つけ難かったので、一番乗りの回答者様を
BAとしたいと思います。

回答No.7

MarcoRossiItaly

＞ピボットも考えましたが
　どうもうまく考えが浮かびません…

多分、考えるより、あれこれいじくり回していれば、勝手にできます。とにかくいろいろと試してください。ピボットテーブルは一見、難しそうですが、案外、簡単にできてしまいます。操作の方法は基本的に、ドラッグ・アンド・ドロップだけです。慣れればパッとできます。便利なので、今後、使う機会が多くなると思いますよ。

「集計」とか「小計」という欄がたくさん表示されてウザければ、その箇所を右クリックで消せます。もちろん再表示も右クリックからできます。また、「合計」値などを表示させる表中の領域を右クリックすれば、同一データの「個数」とか、数値データの「最大値」、「最小値」などを表示させることもできます。なお数値だけでなく、文字列データであっても、その個数をピボットテーブルで集計することなどもできます。

お礼

とある業務で必要だったので、この質問を立てたのですが
その業務が無くなってしまい、質問を締め切るのを失念しておりました…
親身に教えて頂いたのに、お礼が遅れ申し訳ありません…

回答ありがとうございます！

>多分、考えるより、あれこれいじくり回していれば、勝手にできます。

そうなんですよね…でもこの時は、行き詰ってしまい、相談する人もおらず
ネットにすがった次第なのです…

回答No.6

tom04

こんにちは！
他の方も仰っているようにピボットテーブルが一番簡単だと思いますが・・・

マクロでの一例です。
Sheet1のデータをSheet2に表示するようにしてみました。

Alt＋F11キー　→　画面左側の「This Workbook」をダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long, k As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1") '←「Sheet1」は実際のSheet名に！
Set ws2 = Worksheets("Sheet2") '←「Sheet2」も同様！
Application.ScreenUpdating = False
ws2.Cells.ClearContents
With ws2.Cells(1, 2)
.Value = ws1.Cells(1, 3)
.Offset(, 1) = ws1.Cells(1, 1)
.Offset(, 2) = "合計"
End With
ws1.Columns(1).Insert
For i = 2 To ws1.Cells(Rows.Count, 2).End(xlUp).Row
ws1.Cells(i, 1) = ws1.Cells(i, 4) & "_" & ws1.Cells(i, 2)
With ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
.Value = ws1.Cells(i, 4)
.Offset(, 1) = ws1.Cells(i, 2)
.Offset(, -1) = ws1.Cells(i, 4) & "_" & ws1.Cells(i, 2)
End With
k = ws2.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Range(ws2.Cells(2, 1), ws2.Cells(k, 1)), ws2.Cells(k, 1)) > 1 Then
ws2.Rows(k).Delete
End If
Next i
k = ws2.Cells(Rows.Count, 1).End(xlUp).Row
Range(ws2.Cells(2, 1), ws2.Cells(k, 4)).Sort key1:=ws2.Cells(1, 1), order1:=xlAscending
For k = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Cells(k, 4) = WorksheetFunction.SumIf(ws1.Columns(1), ws2.Cells(k, 1), ws1.Columns(3))
If WorksheetFunction.CountIf(Range(ws2.Cells(2, 2), ws2.Cells(k, 2)), ws2.Cells(k, 2)) > 1 Then
ws2.Cells(k, 2).ClearContents
End If
Next k
ws1.Columns(1).Delete
ws2.Columns(1).Delete
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数ではないのでSheet1のデータ変更があってもSheet2に反映されません。
データ変更があるたびにマクロを実行する必要があります。

お役に立ちますかね？m（_ _）m

お礼

とある業務で必要だったので、この質問を立てたのですが
その業務が無くなってしまい、質問を締め切るのを失念しておりました…
親身に教えて頂いたのに、お礼が遅れ申し訳ありません…

こんなに大量のソースまで書いていただきありがとうございます！

>お役に立ちますかね？m（_ _）m

もちろんです。(^_-)-☆
この業務はなくなってしまったのですが
EXCELを使う業務はあるので、ぜひ参考にさせていただきます！

回答No.5

SakuraiMisato

下記URLのサイトでも調査を実施されましたでしょうか？
http://support.microsoft.com/kb/214142/ja

お礼

とある業務で必要だったので、この質問を立てたのですが
その業務が無くなってしまい、質問を締め切るのを失念しておりました…
親身に教えて頂いたのに、お礼が遅れ申し訳ありません…

回答ありがとうございます！
このURLは初めてです、次回の時、ぜひ参考にさせていただきます！

回答No.4

KURUMITO

次のようにすることで関数で対応できます。
例えばシート1のA2セルから下方に機種名、B2セルから下方に台数、C2セルから下方にクラス名が入力されているとしたら作業列を3列用意します。
D2セルには次の式を入力します。

=C2&A2

E2セルには次の式を入力します。

=IF(C2="","",IF(COUNTIF(C$1:C2,C2)=1,MAX(E$1:E1)+10000,ROUNDDOWN(INDEX(E$1:E1,MATCH(C2,C$1:C2,0)),-4))+IF(COUNTIF(D$1:D2,D2)=1,COUNTIF(C$1:C2,C2),INDEX(E$1:E1,MATCH(D2,D$1:D2,0))-ROUNDDOWN(INDEX(E$1:E1,MATCH(C2,C$1:C2,0)),-4)))

F2セルには次の式を入力します。

=IF(E2="","",IF(COUNTIF(E$1:E1,E2)>0,"",RANK(E2,E:E,1)))

D2セルからF2セルを選択して、右クリックで「コピー」し、名前ボックスのD2：F2と表示されているところをD2:F40000のように変更します。それらのセルが選択されますので右クリックして「貼り付け」を行います。

お望みの表をシート2に表示させるとしてA1セルにはクラス、B1セルには機種名、C1セルには台数と項目名をそれぞれ入力したのちにA2セルには次の式を入力したのちにC2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$C:$C,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),IF(COLUMN(A1)=2,INDEX(Sheet1!$A:$A,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),IF(COLUMN(A1)=3,SUMIF(Sheet1!$E:$E,INDEX(Sheet1!$E:$E,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),Sheet1!$B:$B),""))))

これでシート1の内容が変更されても新たなデータが追加されても即座に対応してシート2の表が完成します。なお、シート1の作業列が目障りでしたら列を選択して右クリックし、「非表示」を選択すればよいでしょう。

お礼

とある業務で必要だったので、この質問を立てたのですが
その業務が無くなってしまい、質問を締め切るのを失念しておりました…
親身に教えて頂いたのに、お礼が遅れ申し訳ありません…

こんなに丁寧にソースを書いていただきありがとうございます！
次回の時、ぜひ使わせていただきますね！

回答No.3

malixiang

こんにちは。

データの内容は、常に変わりますよね?
私でしたら、ピボットテーブルで集計します。

データが例えば、毎日変わる(追加)されるのであれば
データに名前をつけてピボットのデータ更新機能を使用します。

データ更新の操作のみ記録マクロを作成して、ボタンなど作ると
良いかもしれませんね。

参考URL：

http://www11.plala.or.jp/koma_Excel/contents3/mame3030/mame303001.html

お礼

とある業務で必要だったので、この質問を立てたのですが
その業務が無くなってしまい、質問を締め切るのを失念しておりました…
親身に教えて頂いたのに、お礼が遅れ申し訳ありません…

ご回答や有益なURLありがとうございます！
データがすんごい量でピボットのデータ更新が
うまくいかず、マクロの方がよいのかなと考えた次第です…

記録マクロはよいかもしれません。参考にさせていただきます。

回答No.2

-9L9-

単純に、クラスでソートして機種名で集計すればいいだけだと思いますが？

お礼

とある業務で必要だったので、この質問を立てたのですが
その業務が無くなってしまい、質問を締め切るのを失念しておりました…
親身に教えて頂いたのに、お礼が遅れ申し訳ありません…

回答ありがとうございます！

>単純に、クラスでソートして機種名で集計すればいいだけだと思いますが？

はい、そうなんです！そのマクロが思いつかず、
お恥ずかしながら質問たてた次第です…