次の方法で共有

Excel関数

Anonymous
2020-09-25T08:11:44+00:00

Excelを使って家計簿を付ける時、クレジットの引落日の設定は難しいのでしょうか？

引落日にどんな関数入れたら、利用日と締め日から引落日が出ますか？

例の画像を使って教えて頂けると助かります

Microsoft 365 と Office | Excel | 家庭向け | Windows

ロックされた質問。 この質問は、Microsoft サポート コミュニティから移行されました。 役に立つかどうかに投票することはできますが、コメントの追加、質問への返信やフォローはできません。

0 件のコメント

質問作成者が受け入れた回答

ひまじん 17,185 評価のポイント
2020-09-25T14:39:19+00:00

こんにちは。

カードの種類が提示されている 2種類に限っていて、それ以上増えることはなく、また「引落日」は曜日に左右されることがないということでしたら、それほど難しい数式を組まなくても可能かと思います。

ただし、その場合も、銀行やカード会社の都合で本来の期日から一時的に変更になる場合があったり、カード会社へカードの使用が通知される日が店側の事務処理の都合で後日になることもあるかもしれず（推測ですが）、そうなると、数式で正確な「引き落とし日」を算出することは困難で、算出が出来るのはあくまで目安の日付になろうかと思います。

なので、本来は、カード会社から通知される「引き落とし日」を数式を使わずに手入力するのが一番確実でしょう。

ですが、目安の日付でも良いということでしたら、せっかくなのでカードの種類も増やしていくことが出来るように、様々な算出条件を想定した場合の数式を組んでみました。

※カードごとの「利用日」・「締め日」・「引落日」の関係と、「引き落とし日」の算出条件

ただし、「引き落とし日」の曜日は考慮していません。

（説明の都合で、提示されておられる表の「月日」となっている見出しを「利用日」に変更させていただきました。）

【「利用日」が「締め日」と同一もしくはそれより前の日付の場合（「利用日」<=「締め日」）】

  • 「引落日」に『翌月』の指定がない場合（＝『当月』指定の場合）

「引き落とし日」は、「利用日」の『当月』の指定された日付。（「末日」指定の場合は『当月』の末日）

  • 「引落日」に『翌月』の指定がある場合

「引き落とし日」は、「利用日」の『翌月』の指定された日付。（「末日」指定の場合は『翌月』の末日）

【「利用日」が「締め日」より後の日付の場合（「利用日」>「締め日」）】

  • 「引落日」に『翌月』の指定がない場合（＝『当月』指定の場合）

「引き落とし日」は、「利用日」の『翌月』の指定された日付。（「末日」指定の場合は『翌月』の末日）

  • 「引落日」に『翌月』の指定がある場合

「引き落とし日」は、「利用日」の『翌々月』の指定された日付。（「末日」指定の場合は『翌々月』の末日）

この条件で良い場合の「引き落とし日」を求めるための数式（ I8 セルに入れる数式）は以下のようになります。あくまで一例です。

=IF(ISNUMBER(A8),IFERROR(EOMONTH(A8,IFERROR(SUBSTITUTE(HLOOKUP(H8,$1:$3,2,0),"日","")*1<DAY(A8),0)+(LEFT(HLOOKUP(H8,$1:$3,3,0),2)="翌月")-1)+SUBSTITUTE(SUBSTITUTE(HLOOKUP(H8,$1:$3,3,0),"日",""),"翌月","")*1,EOMONTH(A8,IFERROR(SUBSTITUTE(HLOOKUP(H8,$1:$3,2,0),"日","")*1<DAY(A8),0)+(LEFT(HLOOKUP(H8,$1:$3,3,0),2)="翌月"))),"")

途中で自動的に折り返されているかと思いますが、全部で 1行の数式になります。

※数式を組み立てた際の前提条件

  • 「締め日」や「引落日」は、共に全て『文字列』として入力されているものとします。

例えば、セルの書式設定などで『数値』の見た目を加工して表示させたりはしていない、と推測しています。

  • 「利用日」は日付のシリアル値として入力されているものとします。

例えば、「 1/3 」 と入力すると、自動的に日付が入力されたものと解釈して「1月3日」というように表示されるかと思いますが、この値は Excel 内部では日付のシリアル値（『数値』）として扱われます。

  • 「引落日」に『翌月』の指定が無い場合や他の文字列が指定されていた場合は、全て『当月』指定と見なします。（「締め日」は全て『当月』指定と見なします。）

この数式を、提示されておられる表の I8 セルに入力（またはコピー・貼り付け）し、必要なだけ下方向にコピーしておきます。

コピーする行数は、将来的にどのくらいの数の「利用日」を入力するかによりますが、多めに見積もって 100行分とか 200行分コピーしておけば良いかと。

尚、コピー方法はオートフィルコピーがお勧めです。

・https://dekiru.net/article/12457/

図１は、「締め日」と「引落日」の様々な組み合わせを試すため、カードの種類を増やした上で「利用日」に「締め日」前後の日付を入れ、「引き落とし日」を上記の数式で算出してみた結果です。

尚、増やしたカード名は適当に考えた架空のものです。図１のように右方向にいくらでも増やしていくことが出来ます。

・図１

「引き落とし日」がお考えの日付になっているかどうか確認の上、よろしければお使いになってみてください。

＜数式の動作概要＞

EOMONTH(A8,IFERROR(SUBSTITUTE(HLOOKUP(H8,$1:$3,2,0),"日","")*1<DAY(A8),0)+(LEFT(HLOOKUP(H8,$1:$3,3,0),2)="翌月")-1)

の部分で、「利用日」に対してのカード別の「前月」の末日、あるいは「当月」の末日、あるいは「翌月」の末日を求め、

SUBSTITUTE(SUBSTITUTE(HLOOKUP(H8,$1:$3,3,0),"日",""),"翌月","")*1

の部分で、カード別の「引落日」に指定された日にちを求め、求めておいた末日の日付にプラスすることで実際の「引き落とし日」を求めています。

尚、「引き落とし日」を求める計算がエラーになった場合（日にちを指定する数字が無い＝「末日」が指定されていると見なす場合）、

IFERROR(・・・,EOMONTH(A8,IFERROR(SUBSTITUTE(HLOOKUP(H8,$1:$3,2,0),"日","")*1<DAY(A8),0)+(LEFT(HLOOKUP(H8,$1:$3,3,0),2)="翌月")))

の部分で、「当月」の末日、あるいは「翌月」の末日、あるいは「翌々月」の末日の「引き落とし日」を求めています。

また、

=IF(ISNUMBER(A8),・・・,"")

の部分で、「利用日」が空白だったり文字列だったりした場合に、エラー表示ではなく空白を表示するようにしています。

これにより、多めに数式をコピーしておいても良いようにもなっています。

以上です。これより細かく書くと長くなりすぎるので省略します。

数式の不明点については、出来るだけお答えしたいとは思いますが・・・。

Windows10 と Excel2016 の組み合わせで動作確認しています。

ご参考になれば幸いです。

この回答は役に立ちましたか?

2 人がこの回答が役に立ったと思いました。
0 件のコメント

質問作成者が受け入れた回答

ひまじん 17,185 評価のポイント
2020-09-26T01:00:45+00:00

お役に立てたようで良かったです。

＞「現金」の場合は空白になるようにできますでしょうか？

これについてですが、「支払方法」欄に「現金」と書き込んだ場合ということでしょうか？。

そうであれば、ご紹介した数式の最初の

=IF(ISNUMBER(A8),・・・）

の部分を

=IF(ISNUMBER(A8)*(H8<>"現金"),・・・)

のように修正してください。

修正した数式全体は下記のようになります。（念のため記載）

=IF(ISNUMBER(A8)*(H8<>"現金"),IFERROR(EOMONTH(A8,IFERROR(SUBSTITUTE(HLOOKUP(H8,$1:$3,2,0),"日","")*1<DAY(A8),0)+(LEFT(HLOOKUP(H8,$1:$3,3,0),2)="翌月")-1)+SUBSTITUTE(SUBSTITUTE(HLOOKUP(H8,$1:$3,3,0),"日",""),"翌月","")*1,EOMONTH(A8,IFERROR(SUBSTITUTE(HLOOKUP(H8,$1:$3,2,0),"日","")*1<DAY(A8),0)+(LEFT(HLOOKUP(H8,$1:$3,3,0),2)="翌月"))),"")

※修正した数式をコピーすることも忘れずに行ってください。

あと、「イオンカード」について書かれていますが、この名称を「支払方法」欄に書かない限り何も起こりませんのでご安心を。

もしも「締め日」と「引落日」に何も入れないまま、「利用日」に日付を入れた状態でこの名称を「支払方法」欄に書き込むと、架空の「引き落とし日」が表示されてしまいますのでご注意ください。

＞ここの質問に「表示15」と出てたのですが・・・

これについては、ご自身が立ち上げたこの『Excel関数』の質問スレッドを何人の方が表示してご覧になったかの数字です。

この質問スレッドに返信をされている方が何人なのかといった数字ではありません。

尚、返信の有無は、自動送信される「更新通知メール」でも確認できますが、必ずこのスレッドを表示させて確認されることをお勧めします。

余談ですが、このスレッドを表示させた場合にブラウザの右上のほうに表示される「表示」数と、スレッドの一覧で表示される「表示」数は必ずしも一致しませんのでご注意ください。

ご参考になれば幸いです。

＜追記＞

ご紹介した数式がかなり長くなってしまっていたので、少し短くしてみました。

動作的には変わりませんので、お好きなほうをお使いください。

=IF(ISNUMBER(A8)*(H8<>"現金"),EOMONTH(A8,IFERROR(SUBSTITUTE(HLOOKUP(H8,$1:$3,2,0),"日","")*1<DAY(A8),0)+(LEFT(HLOOKUP(H8,$1:$3,3,0),2)="翌月")-(RIGHT(HLOOKUP(H8,$1:$3,3,0),2)<>"末日"))+IFERROR(SUBSTITUTE(SUBSTITUTE(HLOOKUP(H8,$1:$3,3,0),"日",""),"翌月","")*1,0),"")

この回答は役に立ちましたか?

1 人がこの回答が役に立ったと思いました。
0 件のコメント

2 件の追加の回答

並べ替え方法: 最も役に立つ
最も役に立つ 新しい順 古い順
  1. Anonymous
    2020-09-26T05:38:13+00:00

    ひまじんさん！いいえひまじん様( ☆∀☆)

    今、私は感動でパソコンに向かって「凄いな！凄いな！」と大きめの独り言が止まりません！

    こんな主婦一人の希望の関数が存在しててそれを考えて作ってくれた方がいるということが本当に本当に嬉しくてたまりません。

    現金についてはひまじんさんのおっしゃるように「現金」と入力した場合です

    プルダウン？ドロップダウン？で「現金」「出光カード」「楽天カード」「イオンカード」のどれかを選択するようにしたんです。

    こちらの状況を理解したアドバイスの数々本当にありがとうございますm(_ _)m

    また、表示の意味も教えて頂きありがとうございます(^^;

    この回答は役に立ちましたか?

    0 件のコメント
  2. Anonymous
    2020-09-25T16:00:19+00:00

    ひまじんさんありがとうございますm(_ _)m

    素晴らしいです！天才です！神です！感動しました！(^O^)

    ひまじんさんに教えて頂いた関数を入れたら出来ました♪

    目安の日付で充分満足です。祝日等でずれても後日にずれるだけだからまあいいか～くらいの気持ちでしたので。

    実は支払方法の中に現金も入れていて、でも質問する際はなるべくシンプルな方がいいかと思い現金を抜いて質問してしまいました(>_<)

    無理ならいいのですが、もし可能なら「現金」の場合は空白になるようにできますでしょうか？

    ちなみに、イオンカードはめったに使わないし締め日と引落日を把握してないのでわかってからでいいか～と思いまだ書いてません。

    質問ばかりですいませんm(_ _)m

    ここの質問に「表示15」と出てたのですがひまじんさんの他に14人の方が回答してくれているということでしょうか？だとしたら他の方の回答はどうやって見れるのでしょうか？

    お時間のある時によろしくお願い致します。

    この回答は役に立ちましたか?

    0 件のコメント