戻る  □VBAのサロン  □ 使用方法  □ 新着記事  □ 新規に質問する!  □ トピック一覧  □ 検索  □ 過去ログ
[ 最新記事及び返信フォームをトピックトップへ ]
このトピック参照回数 :
今期契約残高をだしたい

    [132623] 今期契約残高をだしたい-

    ■親トピック/記事引用/メール受信=OFF■

    □投稿者/ mi -(2021/09/28(09:57))
    □U R L/

      Excel2019


      L2に日付(2021/9/21)

        C  D    N   O
      8 9月 10月 〜 8月 今期残金
      9 10 10    10  100 ←契約金額

      L2の日付が9/20 10/20 11/20なら金額が出来ましたが
      =SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2),5,6,7,8,9,10,11,12,1,2,3,4)))

      日付をその都度で入力で残金を出す事になりました
      O9にはどんな関すなら残金がでますか?
      L2の日付が
      821〜9/20   までの間は残金 110
      9/21〜10/20  までの間は残金 100
      10/21〜11/20  までの間は残金  90

      7/21〜8/20   までの間は残金  0

      一応=IF(AND(L2>=R1,L2<=S1),SUM(D9:N9),IF(AND(L2>=R2,L2<=S2),SUM(E9:N9)・・・・・・・・・
      RとSには期間の日付が入って参照としています。

      で増やせば出来ますがもっとスマートな関数はないでしょうか
      よろしくお願い致します。




    [132624] Re[1]: 今期契約残高をだしたい-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2021/09/28(22:34))
    □U R L/
      ちょっと、質問の意図が分かりにくいです。
      
      こう言うことなんでしょうか?
      
      >=SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2)     ,5,6,7,8,9,10,11,12,1,2,3,4)))
                                                        ↓
        =SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2-20)+1,5,6,7,8,9,10,11,12,1,2,3,4)))
      




    [132625] Re[2]: 今期契約残高をだしたい-

    記事引用/メール受信=OFF■

    □投稿者/ mi -(2021/09/29(12:16))
    □U R L/

      半平太さま
      早速の回答をいただきありがとうございます。

      >=SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2-20)+1,5,6,7,8,9,10,11,12,1,2,3,4)))

      上記でできますがR3.12.21〜R4.1.20の間のみVALUエラーがでます。
      これの回避はありますでしょうか?

      それと残金の計算が少し変更になりまして
      L2に日付を8/21〜9/20の間は  残金120
           9/21〜10/20の間は 残金110
           ↓
      7/20〜8/20の間は  残金10

      =SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2-20)+1,4,5,6,7,8,9,10,11,0,1,2,3)))
      上記だと 8/21ではREFエラーがでます
      これの回避も教えていだだきたいです。




    [132626] Re[3]: 今期契約残高をだしたい-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2021/09/29(13:22))
    □U R L/
      >7/20〜8/20の間は  残金10
        ↓
       7/21〜8/20の間は  残金10 ですね。
      
      残金が0になることが無くなったのならば、
      
      =SUM(INDEX(C10:N10,MOD(MONTH($L$2-20)-8,12)+1):N10)
      
      




    [132627] Re[4]: 今期契約残高をだしたい-

    記事引用/メール受信=OFF■

    □投稿者/ mi -(2021/09/29(18:10))
    □U R L/

      半平太様
      早速にご回答ありがとうございます。

      >=SUM(INDEX(C10:N10,MOD(MONTH($L$2-20)-8,12)+1):N10)
      上記の数式で出来ました。
      ありがとうございます。
      INDEX関数(配列)は使用した事がなく勉強になりました。


      もし半平太様の時間がありましたら

      >=SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2-20)+1,5,6,7,8,9,10,11,12,1,2,3,4)))
      >上記でできますがR3.12.21〜R4.1.20の間のみVALUエラーがでます。
      これの回避はありますでしょうか?

      先にいただいた数式のエラーの原因を教えていただけると
      今後の参考になりますのでありがたいのですが
      よろしくお願い致します。




    [132628] Re[5]: 今期契約残高をだしたい-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2021/09/29(19:47))
    □U R L/
      >>=SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2-20)+1,5,6,7,8,9,10,11,12,1,2,3,4)))
      
      >上記でできますがR3.12.21〜R4.1.20の間のみVALUエラーがでます。
      >これの回避はありますでしょうか?
      
      MONTH($L$2-20)+1 の部分で
      L2が9/20なら9,次の期間が10、・・・とする(Choose関数で、それを1,2・・と読み替える)
      
      ところが、L2が12/21になると、20日を引いても12(月)中なので、それに+1したら「13」になってしまう。
      Chooseの引数が、5,6,・・1,2,3,4の「12」個目までしかないので、該当なしのエラーになった訳です。
      
      なので13個用意すればいいことになります。
      
      =SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2-20)+1,5,6,7,8,9,10,11,12,1,2,3,4,5)))
      
      又は、+1しないで、12個のまま順番を右に一つずらす(つまり、配列の方を全て+1する)
      
      =SUM(C10:N10)-SUM(OFFSET($C10,,,,CHOOSE(MONTH($L$2-20),6,7,8,9,10,11,12,1,2,3,4,5)))
      




    [132629] Re[6]: 今期契約残高をだしたい-

    解決!!ありがとうございました! / 記事引用/メール受信=OFF■

    □投稿者/ mi -(2021/09/30(12:34))
    □U R L/

      半平太様
      お忙しい中の回答ありがとうございます。

      >ところが、L2が12/21になると、20日を引いても12(月)中なので、それに+1したら「13」になってしまう。
      >Chooseの引数が、5,6,・・1,2,3,4の「12」個目までしかないので、該当なしのエラーになった訳です。

      普通にカレンダー的には12月の次は1月だからと 12+1を「1」にしていました。(汗)
      ありがとうございます。



このトピック内容の全ページ数 / [0]

このトピックに書きこむ
Name/
E-Mail/

└>このツリーのレス記事をメールで受信しますか? YES/ NO/
Title/
URL/
Comment/ 通常モード->  図表モード-> (←の場合適当に改行を入れて下さい)
タグが使用できます。例 ⇒ <font color="blue">文字</font>
解決!!ありがとうございました! 解決       保留中です・・・ 保留        迷宮入りorほかあたって見ます・・ 迷宮入       すいませ〜ん。誰か〜! Help! ←※回答者専用
解決したらチェック       保留(コードテスト中など・・)         解決不可orレスが全く付かなくてほかのサイトに行くときなど・・
削除キー/
(半角8文字以内) 


- Child Forum -
Edit:ゆう-G