[[20250705094146]]　『工事番号を順番に並べて該当する原価を整列表示す』（老眼めがね）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) |

| 全文検索 | 過去ログ ]

　

『工事番号を順番に並べて該当する原価を整列表示する方法』（老眼めがね）

御世話になります。
工事原価を材料・経費・外注費べつに、工事番号順に把握していますが、原価は必ずしも全ての原価項目で発生するわけではなく欠落番号も特定の工事では生じます。会計帳簿は原価発生番号のみの集計表となっているため、例として材料＃189は経費集計表には記載されず外注費には発生が有ったため#189として表示されています。従って各原価の集計表では記載されている工事番号数が異なるため材料では90件　経費では40件　外注費では60件というような形になっています。工事原価として全ての工事番号を番号順に抽出して、各工事原価を該当する工事＃セルに記入したいのですが、全ての工事番号を羅列集計する方法及び該当する工事＃に該当する原価（未対応の工事＃では空欄とする）を表示させる方法を教えて下さい。使用している工事＃は100番から300番を使用しています。集計表の項目としては、番号順・材料・経費・外注費・総合計という形を考えております。関数ORマクロでも結構ですので教えて下さい。

＜ 使用 Excel：Excel2013、使用 OS：Windows11 ＞ 

 サンプルを行番号・列番号がわかる表形式で示してもらえますか？
 実行前はこういうもので、結果としてこういうものを得たい、という説明をして下さい。


 # 適宜改行を入れたり、箇条書きで説明してもらうと、より可読性が高まったのでしょうけどねえ。
（xyz） 2025/07/05(土) 10:39:37

 表を提示する際、行の最初を半角スペースで始めるとレイアウトが崩れにくいですよ。
（xyz） 2025/07/05(土) 10:54:51
会計帳簿の補助簿は科目別＆設定した番号を記録表示の為一覧での同一番号合計は不可となっています。。
当該数値をエクセルへ転写して、番号順及び同一番号の合計を表示させたい。
当初転送時の結果は同一番号が同一位置に表示はされていません。
　　　　　　　　列Ｂ（材料）　列Ｃ（経費）　列Ｄ（外注費）　
　　　　　　　　100　\90　　　101　\80　　　100　￥96
　　　　　　　　102　\85　　　102　\70　　　103　￥97
　　　　　　　　104　\89　　　106　\69　　　105　￥95
　　　　　　　　105　\85　　　107　\70　　　106　￥79
上記工事番号別原価を、工事番号順に並べて当該順に原価合計を表示する。新規に最左列に番号＃　最右列に原価合計列を追加記入する。
列Ａ（番号順）　列Ｂ（材料）　列Ｃ（経費）　列Ｄ（外注費）列Ｅ（原価総計）
100　　　　　　100　　90　　　100 0 　　　100　　96　　　100　　186
101　　　　　　101　　 0 101 80 101 0 101 80
102 102 85 102 75 102 0 102 160
103 103 0 103 0 103 97 103 97
104 104 89 104 0 104 0 104 89
105 105 85 105 0 105 95 105 180
106 106 0 106 69 106 79 106 148
107 107 0 107 70 107 0 107 70
番号順での総合計集計で売値に対する全ての原価費目値を比較して工事番号別の工事損益を把握するという目的になります。　例示表示は以上です。
送信部分を表示しましたら、101番は工事番号と対応工事原価とがズレて表示され102番以降は　工事番号と対応原価とが左側に詰まって表示されています。訂正方法に不案内のため御寛恕下さい。
（老眼めがね） 2025/07/05(土) 14:00:24
＞＞当初転送時の結果は同一番号が同一位置に表示はされていません。
は　下記と考えてよいのでせうか　相違点[アドレス若しくはEXCELではなくテキストファイル？等々]
があればご説明賜れば
お話が進みやすくなるかもしれません。(*^^*)　お手伝いだけでも←邪魔してるかもですね^^;
m(__)m
とある　お名前の　エクセルのシート 

    |[A]         |[B]         |[C]           
 [1]|列Ｂ（材料）|列Ｃ（経費）|列Ｄ（外注費）
 [2]|100　\90    |101　\80    |100　￥96     
 [3]|102　\85    |102　\70    |103　￥97     
 [4]|104　\89    |106　\69    |105　￥95     
 [5]|105　\85    |107　\70    |106　￥79     
（隠居Z） 2025/07/05(土) 14:32:31

                 列B(材料)     列C(経費)     列D(外注費)  
                 100  \90      101  \80      100  \96
                 102  \85      102  \70      103  \97
                 104  \89      106  \69      105  \95
                 105  \85      107  \70      106  \79
 念のための確認ですが、
 ・ひとつのセルに、工事番号と金額が二つとも入っているんですか？
   それは説明の都合上であって、本当は別々のセルです、ということでは？
 ・上記は4つの行ということなんですか？
   関数をやりとりするにも、お互いに同一の前提のほうが提案も検証も
   話が通じやすいと思うのですが、いかがですか？


                 列B    C        D    E        F    G  
 1               100    \90      101  \80      100  \96
 2               102    \85      102  \70      103  \97
 3               104    \89      106  \69      105  \95
 4               105    \85      107  \70      106  \79
 といったことでいいのですか？ 
 そうでなければ、実際のありようをサンプルに反映してください。 


 # 行を半角スペースで始める、ということは伝わりにくいのですかねえ。
（xyz） 2025/07/05(土) 14:52:33
結果予想図　。。。どうやら　↓　の様な感じみたいな気がいたします。　^^;
あわせてこちらも相違がありましたら教えて下さい。 

    |[A]           |[B]         |[C]         |[D]           |[E]             
 [1]|列Ａ（番号順）|列Ｂ（材料）|列Ｃ（経費）|列Ｄ（外注費）|列Ｅ（原価総計）
 [2]|           100|          90|           0|            96|             186
 [3]|           101|           0|          80|             0|              80
 [4]|           102|          85|          75|             0|             160
 [5]|           103|           0|           0|            97|              97
 [6]|           104|          89|           0|             0|              89
 [7]|           105|          85|           0|            95|             180
 [8]|           106|           0|          69|            79|             148
 [9]|           107|           0|          70|             0|              70
（隠居Z） 2025/07/05(土) 15:56:51
御世話になります。
隠居Ｚ様が作成して下さった一覧表形式を作成する目的で、各原価項目では必ずしも全ての原価番号対応の原価が発生している訳ではないという形を、会計帳簿からＢ列からＤ列までをエクセルへ転送させてＡ列に全ての工事番号を記入してＢ列からＤ列までの発生原価をＡ列の番号に合致する場所に移動表示させ、Ｅ列に同一番号構成する各原価項目の合計値を表示算出するという形でございます。
会計帳簿の転送デ−タ−は￥0に対応する工事番号は抽出していないという形です。
よろしく御願い致します。
（老眼めがね） 2025/07/05(土) 16:41:56 
 確認事項に回答いただけないので、ここまでです。
（xyz） 2025/07/05(土) 16:48:01
お望みの結果は概ね理解出来たように思いますが　xyz　さんもお尋ねの
元情報の型式
＞＞会計帳簿の転送デ−タ−なるものが何ものなのかを明確にご説明戴けなければ
どなたも、どうしようもないと存じます。
（xyz） さん　2025/07/05(土) 14:52:33
の　確認事項にご回答くださいませ。無理にとは申し上げません^^;
m(__)m

（隠居Z） 2025/07/05(土) 17:00:47

多々御迷惑を掛けて申し訳ございません。
会計帳簿から　エクセルヘ転送　を選びますと、各費目に存在する工事番号・工事原価がエクセルシ−トに転送されます。之は(XYZ)さんの仰るとおりに工事番号と工事原価とは別列（工事番号があれば工事原価をという形）に個別に転送されますので一緒表示ではなく別々の列セルに工事番号と工事原価とが同一位置行のセルに転記されているという提示頂きました形となっております。
宜しく御願い致します。
（老眼めがね） 2025/07/05(土) 18:42:38
（xyz） さん2025/07/05(土) 14:52:33　ご案内のいちばん下の表が　Sheet1
出力は Sheet2 ←　初期化されます
が前程のコードです^^;　きっともっとスマートな方法があるかも
しれませんね。。。←間違ってるかも(￣▽￣；)
他の回答者様のお出ましをお待ちくださいませ 

 Option Explicit
Private Sub VBACodeByFearfulSpeculationAndConjecture()
    Dim y As Long
    Dim x As Long
    Dim i As Long
    Dim Material&, Expenses&, OutsourcingC&
    Dim Dc As Object
    Dim v(), tmp(), dKey(), r
    Set Dc = CreateObject("Scripting.Dictionary")
    With Worksheets("Sheet1")
        Set r = .Cells(2, 2).CurrentRegion
        Set r = r.Offset(1).Resize(r.Rows.Count - 1)
        v = r.Value
        .Activate
        r.Select
    End With
    For x = 1 To UBound(v, 2) Step 2
        For y = 1 To UBound(v, 1)
            Select Case x
                Case 1
                    Material = v(y, x + 1)
                Case 3
                    Expenses = v(y, x + 1)
                Case 5
                    OutsourcingC = v(y, x + 1)
            End Select
            If Not Dc.Exists(v(y, x)) Then
                Dc(v(y, x)) = Array(Material, Expenses, OutsourcingC)
            Else
                tmp = Dc(v(y, x))
                tmp(0) = tmp(0) + Material
                tmp(1) = tmp(1) + Expenses
                tmp(2) = tmp(2) + OutsourcingC
                Dc(v(y, x)) = tmp
            End If
            Material = 0
            Expenses = 0
            OutsourcingC = 0
        Next
    Next
    dKey = Dc.keys
    With Worksheets("Sheet2")
        .UsedRange.Clear
        .Cells(1, 2).Resize(, 5) = Array("工事番号", "材料", "経費", "外注費", "原価総計")
        y = 2
        For i = 0 To UBound(dKey)
            .Cells(y, 2) = dKey(i)
            .Cells(y, 3).Resize(, 3) = Dc(dKey(i))
            .Cells(y, 3).Offset(, 3).Value = Application.Sum(Dc(dKey(i)))
            y = y + 1
        Next
        Set r = .Cells(1, 2).CurrentRegion
        r.Sort key1:=r.Columns(1), order1:=xlAscending, Header:=xlYes
    End With
    Erase v, dKey, tmp
    Dc.RemoveAll
End Sub

しっかり、間違っていましたですね。　変数の初期化　3行追加修正
いたしました。 

 Material = 0
 Expenses = 0
 OutsourcingC = 0
でぇす。　<< _ _ >> はずかし〜
　m(__)m

（隠居Z） 2025/07/05(土) 19:08:15

(隠居Ｚ）様　（XYZ)様　多々ありがとうございました。
マクロは落ち着いて拝見させて頂きます。チョット難しそうなので　寛恕乞
# 行を半角スペースで始める、という箇所試行錯誤させてください。数字が揃う形のようなのでワ−という印象です。
何故皆さん簡単に回答を導き出せるのか、嬉しいのを通り越して、ビックリしています。
本当に御多用下の時間を拘束略奪いたしまして申し訳ございませんでした。
決算月には、まだチョット月数がありますので解読させてくださいね。感謝＆感謝　拝

（老眼めがね） 2025/07/05(土) 22:16:45

おはようございます。(*^^*)
プロシジャー名は除く、13行目、14行目の
.Activate
r.Select
は、デバッグ時の確認用なのですが範囲を選択状態ですので
消していただいた方が無難かもしれません。
当方の消し忘れです。済みません。m(__)m
ご丁寧なご挨拶恐縮で御座います。
楽しく勉強させて戴きました。m(__)m

（隠居Z） 2025/07/06(日) 09:06:41

 既に解決済みのようですが、別法として、手作業での方法も挙げておきましょう。
 ・工事番号の列をコピーして縦一列に並べ、昇順にソートしてから、「重複の削除」を行って一意化します。
 ・次に、SUMIF関数を使って、材料、経費、外注費ごとに、
   工事番号に対応する費用の合計を作成します。
 ・最後に、3つの合計をSUM関数で計算します。
（xyz） 2025/07/06(日) 14:08:04
コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.