Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
支払回数に応じて金額をセルに入力する
投稿日時: 19/09/05 17:39:30
投稿者: vaioyuki

いつもお世話になっております。
また相談にのってください。
 
 

会社名|契約金  |契約期間始|契約期間終|
A社  |1,800,000 |2015/01/01|2020/12/31|
B社  |1,200,000 |2016/11/01|2019/10/31|
C社  |2,100,000 |2019/04/01|2023/03/31|
 
上記のようなデータがあり契約期間終の次の列に契約している中の契約開始始が一番古い年月から契約期間終の一番先の年月まで表示させて、
それぞれに月で分割された金額を入れていきたいです。
 

会社名|契約金  |契約期間始|契約期間終|2015/01|2015/02|・・・|2016/11|2016/12|
A社  |1,000,000 |2015/01/01|2020/12/31|30,000 |30,000 |   |30,000 |30,000 |
B社  |1,200,000 |2016/11/01|2019/10/31|    |    |   |20,000 |20,000 |
 
 
データが100件以上ありますのでなんとかVBAで出来ないものかと思ってますが、案が全く浮かびません。(TーT)
検索するにもどういった言葉で検索していいのか、(ローンで検索したら出てきますが思ったようなものはありません)
日付は最初に入力しておいた方がいいのかさえも迷っています。
まず何回払いになっているのかを算出して、
そこからループで日付に入れていくのかな?とざっくり思ってますがそれをどうしていいかもわかりません。
 
よろしくお願いします。。。

回答
投稿日時: 19/09/05 19:07:33
投稿者: mattuwan44

1)契約開始日の最小値をMin関数で取得しメモっておく
2)契約終了日をMax関数で取得しメモっておく
3)表の1行目の最終列の1つ右にめもった日付を入力
4)今入力したセルを選択し、フィルハンドルを右クリックで掴み右へ欄を余分にドラック
5)右クリックメニューで連続データを選択し、
日付→月と選び停止値にめもった最大値を入力してOK
 
これで表のタイトル行が作成できます。
 
これを自動で行うようVBAで作業手順を書けば、
タイトル行の生成マクロが完成です。
どんなコードになるかは、その操作を「マクロの記録」という機能で、
操作をVBAに翻訳してくれるので、それで探ることが可能です。
その他、
メモっておく→変数へ代入
などVBA語の文法を覚えてください。
 
やりたいことがあるのはいいことですが、
基礎を飛ばしては、マクロを完成することが出来ません。
まずは作業のながれを日本語で言語化し、
それをVBAではどう書くのかを逐一調べるなり、
質問をするなりしてみてください。
パソコンは繰返し作業は得意なので、
1つ出来るようになればあとは繰り返すのみです。
 
>1つづつ出来るようになればいいと思いますので、
漠然と、「どうしたらいいの?」
ではなく、各各も少しテーマを絞って、質問してみてはいかがでしょうか?

回答
投稿日時: 19/09/05 19:47:26
投稿者: simple

提示された資料だけでは、金額は決まらないと思います。
適用利率とかがなければ決まりませんし、色々な方式があるものと思います。
まずは、そういった事情をご自分で理解することから始めないと、
こうした場で質問しても回答は得られません。

回答
投稿日時: 19/09/05 22:53:08
投稿者: WinArrow
投稿者のウェブサイトに移動

単純に
>月で分割された金額
というと、契約金額÷月数
で計算すればよいの?
 
A社は、72ヶ月あるから、単純計算すると、25,000/月になります。
しかし、例題では、30,000が入っています。
 
この差って何ですか?
 
>月で分割された金額
という説明はあいまいです。
 
表のレイアウトについて
 
私ならば、元の表に追加するのではなく、
会社名 ○○月 ??金額
というような表(縦長)の表を作成します。
なぜならば、横に広げると、右へ右へ・・エンドレスになります。
見づらい、印刷しずらい、
多分契約終了で不用となった列は削除するだろうと思うが、操作ミスが発生しやすい。
など、非効率な表になります。

回答
投稿日時: 19/09/05 23:37:47
投稿者: 半平太

私なら、古い契約は、終わった期間は除いて、現在生き残っている期間だけにするか、
古くても、会社の期首からにするような気がします。
 
まぁ、この表の目的が分かってないので、一概に言えないところですけど。

投稿日時: 19/09/06 10:04:34
投稿者: vaioyuki

皆様、
いつもいつもわかりにくい私の説明にお付き合い頂き、ありがとうございます。
 
金額は単純に月で割ればいいだけです。
何に使いたいかは10月からの消費税増税に伴い、10月以降の分に2%上乗せする前の金額を求めたいです。
ずっと使用するのではなく、資料のため今回だけ出来上がればいいというものになります。
横にずらっと並べるのはそういう指示なので私には何とも言えません。(^^;)
元々ある表に追記する形になるので横に並べる。。。という形になります。
あと、25000円が30000円になっているのは単純に私のミスです。すいません。。。
 
頭に描いていることを日本語にしてみます。
 
@mattuwan44さんがいうように、関数を使って必要な日付を入力していきます。
A支払回数を求めておきます。
B契約期間始を@の日付列のセルに入力し、Aの支払回数分ループさせます。
CBを繰り返します。
 
イメージとしてはこういう感じになるのでしょうか。。。?
 
会社だと見られるページも制限されていたりしてなかなか見たいページにたどり着きません。
(ブログの形になっているページはセキュリティ上開くことが出来ません)
私の検索が下手なのかもしれませんが、
行でループするものは見つけられるのですが、列でループするものがなかなか見つからずにいます。
 
もちろん、自分でも頑張って作成します!!
ヒントを頂けると助かります!!(T−T)
 
よろしくお願いします。

回答
投稿日時: 19/09/06 10:30:53
投稿者: 半平太

>今回だけ出来上がればいい
 
それを先に言って欲しかった。数式を使っても十分できます。
 
こんなので行けると思います。(100年分は無いものとします)

Sub destribution()
    Dim rngSrc As Range, aCell As Range
    Dim st As Date, ed As Date
    Dim stCol As Long, numCol As Long
    
    Set rngSrc = Range("A2", Cells(Rows.Count, "A").End(xlUp))
    
    With Application
        st = .EoMonth(.Min(rngSrc.Columns("C")), -1) + 1
        ed = .EoMonth(.Max(rngSrc.Columns("D")), 0)
    End With
    
    numCol = DateDiff("m", st, ed) + 1 '全枠数を求める
    
    If numCol > 1200 Then
        MsgBox "100年以上のスパンがあります。日付データをチェックしてください。"
        Exit Sub
    End If
    
    With Range("E1") '日付タイトルを埋める
        .Resize(rngSrc.Rows.Count + 1, 1200).ClearContents
        .Value = st
        .AutoFill Destination:=.Resize(1, numCol), Type:=xlFillMonths
        .Resize(1, numCol).NumberFormat = "yyyy/mm"
    End With
    
    For Each aCell In rngSrc.Columns("A").Cells
        If Not IsEmpty(aCell) Then   '書込み開始列と書込み列数を求める
            stCol = DateDiff("m", st, aCell.Offset(, 2).Value)
            numCol = DateDiff("m", aCell.Offset(, 2).Value, aCell.Offset(, 3).Value) + 1
            
            aCell.Offset(0, stCol + 4).Resize(1, numCol).Value = aCell.Offset(, 1) / numCol
        End If
    Next
End Sub

投稿日時: 19/09/06 11:10:46
投稿者: vaioyuki

半平太様
 
早速ありがとうございます!!
理解しながら確認しながら検証してみます!!
 
また結果報告させたいただきます。

回答
投稿日時: 19/09/06 12:31:28
投稿者: mattuwan44

>行でループするものは見つけられるのですが、列でループするものがなかなか見つからずにいます。
 
https://teratail.com/questions/210384
 
↑他の掲示板に書いたので、そっちを参考にしてください。

投稿日時: 19/09/06 15:10:11
投稿者: vaioyuki

mattuwan44さま
 
わざわざありがとうございます!!
ブックマークしておきます!!

投稿日時: 19/09/19 15:44:59
投稿者: vaioyuki

別件で忙しく実証できていません。(T-T)
 
とりあえず閉じます。
 
ありがとうございました!!