No.12
- 回答日時:
ピボットテーブルに拘っているわけではありませんが、追記します。
>こんな感じに自動で入力された商品ごとの合計金額を計算したいのです。
自動でデータが入ってくるとのことなので今回のケースでは質問者さんはデータの入力者ではないですね。他者が入力するか、DB等からデータを引っ張ってくるか
>データーの更新をせずに表を作った時点で自動的に更新できる方法
これがいつなのか?が問題ですね。
1.他者が入力して、その時点で合計も同時に(データ更新なしに)確認をしたいのであれば関数で求めるしかありませんが文面からして違うでしょう。
2.入力者と別の方、もしくはDB等から抽出したデータの合計を求めるのであれば合計確認者が当該BOOKを開いた時点ということになります。
3.当該BOOKを開いている最中にDB等からマクロなどを使い(手動は無いでしょう?)データを引いてくる場合はその直後ですね。
※その他の状況もあるかもですが・・・
※排他書き込み許可は考慮してません。推奨されないと思うので。
2であれば、ピボットテーブルのオプションで「開いた時に更新する」にチェックすれば解決します。
3であればマクロの最後に当該シートを選択後
ActiveSheet.PivotTables("任意の名前").RefreshTable
を実行すれば自動で更新されます。
ありがとうございます。
実は、私は建設会社で原価管理をして入る者です。
今回質問させていただいたのは、工事の原価計算書から業者別の発注工事額を自動で求めたかったのです。
今までは手計算をしていましたが、なんとか関数を使って自動でできないかなーと常々思っており、今回解決できて非常によかったです。
原価管理の担当者はたくさんおり私も含めてみんな苦労して計算していますので、今回の関数を埋め込んだ原価計算書を配布したいと思います。
皆様ほんとうにありがとうございました。
No.11
- 回答日時:
こんばんは。
どの程度の自動化をお望みか分かりませんが、このマクロを、コントロールツールのボタンに取り付けたらどうかと思います。フィルタオプションを駆使しても、慣れれば、そんなに時間的な差があるわけではありませんが。
'シートモジュール
'コントロールツールのボタン・イベント
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.9
- 回答日時:
[ANo.7この回答へのお礼]に対するコメント、
》 データーの更新をせずに表を作った時点で自動的に更新できる方法がないかと思
》 っています。
あらかじめ、全ての品名をF列に列挙しておいたら如何かと。この場合、A列に登場しない品名の合計額は 0 と表示されますが。
それも嫌うなら貴方を満足させる方法はない、と思います。
No.8ベストアンサー
- 回答日時:
こんにちは~♪
皆さんお勧めのピボットテーブルが、
1番良いと思うんですが~。。
>データーの更新をせずに表を作った時点で自動的に
>更新できる方法がないかと思っています
更新が、面倒ですか?
でしたら、数式か、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.7
- 回答日時:
ピボットテーブルによる集計がお奨めだけど、新たに入力されたデータを結果に反映させるために[データの更新]を実行する必要があります。
下記の SUMIF関数による集計は、新たに入力されたデータが既存の品名の場合は結果が自動更新されるが、新たな品名が入力された場合は、ステップ2~7を再実行する必要があります。
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
1.範囲 A1:A100 を選択
2.[データ]→[フィルタ]→[フィルタオプションの設定]を実行
3.[抽出先]として“指定した範囲”に目玉入れ(あるいは、そうなっていることを確認)
4.[リスト範囲]が $A$1:$A$100 を入力(あるいは、そうなっていることを確認)
5.[抽出範囲]が $F$1 を入力(あるいは、そうなっていることを確認)
6.“重複するレコードは無視する”にチェック入れ(あるいは、そうなっていることを確認)
7.[OK]をクリック
8.セル G2 に次式を入力して、此れを下方にズズーッと入力
=IF(F2="","",SUMIF(A$2:A$100,F2,D$2:D$100))
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) グループの最後の行に書式、計算式なども同じ行を追加するマクロを教えてもらえませんか。 7 2022/05/18 10:13
- Excel(エクセル) 【エクセル関数】複数条件に該当する場合、別の列の数値を合算する。 9 2022/07/09 08:46
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
- 消費税 消費税の納税額の計算 1 2023/02/19 18:12
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
- その他(データベース) accessでの請求管理について 2 2022/06/13 21:51
- Excel(エクセル) 列を自動で追加したい 3 2022/07/11 12:58
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- docomo(ドコモ) Amazonでd払いで商品を購入しました。 3 2022/10/10 03:29
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル 文字を増やしたい。
-
エクセルの計算
-
セルの内容表示が邪魔になる
-
Excel
-
Microsoft365に変えたのですが...
-
エクセル:一覧表に存在する文...
-
エクセルで日付を数字+アルフ...
-
エクセルでの作業計算方法について
-
エクセルで年休を管理する方法...
-
はがきについて。
-
【マクロ】その時、その時で変...
-
excelの不要な行の削除ができな...
-
Microsoft1Officeの互換ソフト...
-
エクセル関数を教えてください
-
Excel ピボットテーブルで日付...
-
【マクロ】読取専用のファイル...
-
【関数】適切な文字数の数字を...
-
時間によってファイル名が変わ...
-
ある列、或いは、ある行のセル...
-
UNIQUE関数が使えないバージョ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報