エクセルで不特定の項目ごとに合計を自動計算したいのですが。

質問

こんな表があったとします。
品名　 数量　 単価　 金額
りんご 　10　 250　 2500
ばなな 　8　 120　 960
すいか 　15　 700　 10500
ばなな 3 120 360
りんご 21 250 5250

↓

りんご 7750
ばなな 1320
すいか 10500

こんな感じに自動で入力された商品ごとの合計金額を計算したいのです。
商品は不特定ですのでどんなものが入力されるか分かりません。

宜しくお願いいたします。

回答No.8

rin01 · ベストアンサー

こんにちは～♪

皆さんお勧めのピボットテーブルが、
１番良いと思うんですが～。。

>データーの更新をせずに表を作った時点で自動的に
>更新できる方法がないかと思っています

更新が、面倒ですか？
でしたら、数式か、VBAになるのではないでしょうか？

★数式の案ですけれど。。。
同シートの場合です。
mike_gさんの表をお借りします。。。。すみません。

F2: =IF(COUNT(INDEX(1/(MATCH($A$2:$A$100,$A$2:$A$100,)=ROW($1:$99)),))<ROW(A1),"",INDEX(A:A,SMALL(INDEX(SUBSTITUTE(ISNUMBER(1/(MATCH($A$2:$A$100,$A$2:$A$100,)=ROW($1:$99)))*1,0,10^7)*ROW($1:$99)+1,),ROW(A1))))

下にコピーしておきます。
mike_gさんの表のF列の様に、品名が抽出されます。

注)あまりデータ行が多いと、重くなります。。。

★合計は、
これも、mike_gさんのをお借りして～。。。
>セル G2 に次式を入力して、此れを下方にズズーッと入力
>　　=IF(F2="","",SUMIF(A$2:A$100,F2,D$2:D$100))

。。。。Rinでした～♪♪

お礼

解決しました！
大変助かりました。胸のつかえが取れたようです。
本当にありがとうございました。

回答No.12

since_hoge

ピボットテーブルに拘っているわけではありませんが、追記します。

>こんな感じに自動で入力された商品ごとの合計金額を計算したいのです。
自動でデータが入ってくるとのことなので今回のケースでは質問者さんはデータの入力者ではないですね。他者が入力するか、ＤＢ等からデータを引っ張ってくるか

>データーの更新をせずに表を作った時点で自動的に更新できる方法
これがいつなのか？が問題ですね。
１．他者が入力して、その時点で合計も同時に（データ更新なしに）確認をしたいのであれば関数で求めるしかありませんが文面からして違うでしょう。
２．入力者と別の方、もしくはＤＢ等から抽出したデータの合計を求めるのであれば合計確認者が当該ＢＯＯＫを開いた時点ということになります。
３．当該ＢＯＯＫを開いている最中にＤＢ等からマクロなどを使い（手動は無いでしょう？）データを引いてくる場合はその直後ですね。
※その他の状況もあるかもですが・・・
※排他書き込み許可は考慮してません。推奨されないと思うので。

２であれば、ピボットテーブルのオプションで「開いた時に更新する」にチェックすれば解決します。

３であればマクロの最後に当該シートを選択後
ActiveSheet.PivotTables("任意の名前").RefreshTable
を実行すれば自動で更新されます。

お礼

ありがとうございます。
実は、私は建設会社で原価管理をして入る者です。
今回質問させていただいたのは、工事の原価計算書から業者別の発注工事額を自動で求めたかったのです。
今までは手計算をしていましたが、なんとか関数を使って自動でできないかなーと常々思っており、今回解決できて非常によかったです。

原価管理の担当者はたくさんおり私も含めてみんな苦労して計算していますので、今回の関数を埋め込んだ原価計算書を配布したいと思います。
皆様ほんとうにありがとうございました。

回答No.11

Wendy02

こんばんは。

どの程度の自動化をお望みか分かりませんが、このマクロを、コントロールツールのボタンに取り付けたらどうかと思います。フィルタオプションを駆使しても、慣れれば、そんなに時間的な差があるわけではありませんが。

'シートモジュール
'コントロールツールのボタン・イベント
Private Sub CommandButton1_Click()
　Call ListUp
End Sub

'標準モジュール
'データは必ず、項目行（タイトル名)が必要です。

Sub ListUp()
Dim r As Range
'----------------------------
'**初期設定**

　　'データの書き出し場所
　　Const TO_PASTE As String = "F1"
　　'データの左上端の位置
　　Set r = Range("A1").CurrentRegion
'----------------------------
　Application.ScreenUpdating = False

　Range(TO_PASTE, Range(TO_PASTE).End(xlDown)).ClearContents

　With r.Columns(1)
　　　 If .Cells.Count < 3 Then Exit Sub　'データが少なすぎる
　　　.AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　　　　CopyToRange:=Range(TO_PASTE), _
　　　　　　　　　　　Unique:=True
　End With
　With Range(TO_PASTE, Range(TO_PASTE).End(xlDown))
　 'フィルタオプションが失敗している場合
　 If .Cells(.Count).Row = 65536 Then Exit Sub
　 Set r = r.Offset(1).Resize(r.Rows.Count - 1)

　 .Cells(1).Offset(, 1).Value = "金額"

　 .Offset(1, 1).Resize(.Rows.Count - 1).FormulaLocal = _
　　　"=SUMIF(" & r.Columns(1).Address(1, 1, 0) & ",RC[-1]," & _
　　　　r.Columns(4).Address(1, 1, 0) & ")"
　End With
　　　Set r = Nothing
　　Application.ScreenUpdating = True
End Sub

お礼

マクロになるかなーと思っていましたが。関数でやりたかったのです。
皆さん本当にありがとうございました。

回答No.10

noname#204879

[ANo.9回答]への補足コメント、

》 それも嫌うなら貴方を満足させる方法はない、と思います。

と述べましたが、[ANo.8回答 Rin]さんが見事に解決されていましたね。Rinさんに脱帽 m(__)m

でもォ～、失礼ながら、oldbook915さんはそれを解読(理解)できますかぁ？

お礼

理解できません

回答No.9

noname#204879

[ANo.7この回答へのお礼]に対するコメント、

》 データーの更新をせずに表を作った時点で自動的に更新できる方法がないかと思
》 っています。

あらかじめ、全ての品名をＦ列に列挙しておいたら如何かと。この場合、Ａ列に登場しない品名の合計額は 0 と表示されますが。
それも嫌うなら貴方を満足させる方法はない、と思います。

回答No.7

noname#204879

ピボットテーブルによる集計がお奨めだけど、新たに入力されたデータを結果に反映させるために[データの更新]を実行する必要があります。
下記の SUMIF関数による集計は、新たに入力されたデータが既存の品名の場合は結果が自動更新されるが、新たな品名が入力された場合は、ステップ２～７を再実行する必要があります。

　　 A　　　B　　 C　　　D　　E　　F　　　 G
1　品名　　数量　単価　金額　　　品名　　合計額
2　りんご　　10　 250　 2500　　 りんご　　7750
3　ばなな　　 8　 120　　960　　 ばなな　　1320
4　すいか　　15　 700　10500　　 すいか　 10500
5　ばなな　　 3　 120　　360
6　りんご　　21　 250　 5250
7

１．範囲 A1:A100 を選択
２．[データ]→[フィルタ]→[フィルタオプションの設定]を実行
３．[抽出先]として“指定した範囲”に目玉入れ（あるいは、そうなっていることを確認）
４．[リスト範囲]が $A$1:$A$100 を入力（あるいは、そうなっていることを確認）
５．[抽出範囲]が $F$1 を入力（あるいは、そうなっていることを確認）
６．“重複するレコードは無視する”にチェック入れ（あるいは、そうなっていることを確認）
７．[OK]をクリック
８．セル G2 に次式を入力して、此れを下方にズズーッと入力
　　=IF(F2="","",SUMIF(A$2:A$100,F2,D$2:D$100))

お礼

ありがとうございます。
データーの更新をせずに表を作った時点で自動的に更新できる方法がないかと思っています。

回答No.6

CHRONOS_0

品名でソートしてから
「データ」「集計」で合計を求めるだけです

合計欄だけでよければグループの詳細を折りたためばいいのです

回答No.5

since_hoge

>混在していても、混在している通り表示し合計を計算したい
とは、「りんご」と「リンゴ」は別物と判断して良いと云うことでしょうか？

関数ではありませんがピボットテーブルを使って見ては？
ウィザードに従って、行に「品名」、データに「金額」の合計を選択。
で、出来ます。いかがでしょう？

お礼

ピボットテーブルは使った事がないので一度使って見ます。
ありがとうございました。

回答No.4

mu2011

以下の方法は如何でしょうか。
仮に表リストがシート１、シート２に合計項目セルをA列とし、金額合計セルB1に=IF($A1<>"",SUMIF(sheet1!A:A,$A1,sheet1!D:D),"")をした方向にコピーで如何でしょうか。

お礼

ありがとうございます。

回答No.3

ipsum11

「= SUMIF(A2:A6,"りんご",D2:D6)」で、りんごの合計
「= SUMIF(A2:A6,"ばなな",D2:D6)」で、ばななの合計
「= SUMIF(A2:A6,"すいか",D2:D6)」で、すいかの合計

お礼

ありがとうございます。

回答No.2

Turbo415

たとえば、品名A列　数量B列　単価C列　金額D列で９行目まで数値が入っているとして、E1に品名を入れると、F1に合計が出るようにするなら、=SUMIF(A1:D9,E1,D1:D9)です。つまり、E1と同じ品名の行の金額（D列）だけを合計するとういうことになります。
表が大きくなればD9をD100とかD1000とかにするだけです。
このときE1にはA列にある物と同じ名前を入れなければいけません。ですから、A列には「りんご」となっているのにE1に「リンゴ」としてしまうと、合計できませんし、A列に「りんご」と「リンゴ」が混在している場合は別の物と判断してしまいますので注意してください。
こんな感じで良いでしょうか？
参考までに。

補足

早速ありがとうございます。
ご指摘の通り「りんご」と「リンゴ」が混在している場合なのです。

本当に調べたかったのは「不特定」ということです。
混在していても、混在している通り表示し合計を計算したいのです。

SUMIFだと検索条件を特定しないといけませんが、不特定の検索条件に対応した計算式が分からないのです。

スミマセンがいかがでしょうか？