Excelについて質問です。

質問

Excelについて質問です。

現在、会費管理の為のエクセルシートを作っているのですが
関数などあまり理解できていないので（マクロやVBAももちろん）、
質問させて頂きます。

２年毎に更新がある会員制度となっていて、
納入は一年に１回となっております。

打ち込むデータとしては、会費納入があった日付のみにしてあります。

エクセルを開いた時点でのその会員の「継続・期限切れ」を確認したくて
添付画像にあるように、TODAY関数を使用しようしてみたのですが
このやり方だと、一人ひとり２年目か４年目かを指定しないといけません。

２年目の振込があった時点で、期限が４年目の日付までになります。

説明下手で申し訳ないのですが、関数など式を入れる程度の内容で
解決できる方法がありましたら、お教えください。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.4

nayuta_lot · ベストアンサー

こんにちは

Ｆ４のセルに下記の数式を入力してＦ５にコピーしてみてください。

=IF(A4="","",IF(COUNT($B4:$E4)<MATCH(DATEVALUE(YEAR(TODAY())&"/3/31"),$B$3:$E$3,0)-1,"期限切れ","継続中"))

前提条件

１．前年度まで会費を納入していれば、前年度の納入期限を過ぎていても継続とみなす。
２．前年度に会費が納入されていなければ、期限切れとする。
３．納入日は常に連続している。

という勝手な解釈で作成してみました。

＜解説＞

=IF(A4="","",継続中、期限切れの判断)
A4に人の名前が入っていなければ空白表示、でなければ判断します。

継続中、期限切れの判断
IF(COUNT($B4:$E4)<MATCH(DATEVALUE(YEAR(TODAY())&"/3/31"),$B$3:$E$3,0)-1,"期限切れ","継続中")

今年が何年目（何列目）かを取得して、納入回数がその年数-1（前年まで年数）よりも小さければ、前年度に納入
していないことがわかります。

従って、まず納入回数をCOUNT($B4:$E4)で取得します。
次に、何年目であるかを取得します。

MATCH(DATEVALUE(YEAR(TODAY())&"/3/31"),$B$3:$E$3,0)-1
Ｂ列の納入期限を検索範囲、今年の年+"3/31"を検索値として
その列の位置が何列目なのかを取得します。

DATEVALUEは、この文字列形の日付をシリアル値に変換しています。
こうしないと、文字とシリアル値の比較になって検索できないからです。

前年度のまで入力されていれば継続中としますので、前々年度が空白か否かで継続中か期限切れを判断します。
従って、前年度までの年数と比較するために最後に-1を入れています。

これで、納入回数　＜　前年度までの年数ならば、"期限切れ"
そうでないならば、"継続中"と判断することができます。

ただし、この方法は納入期限の月日があくまでも3/31の場合なので
変更する場合は注意してください。
できれば、納入期限の上の行辺りに隠しセルを用意してそこを年度だけを用意して、キーにするのをお勧めします。

あまり説明がよくないかもしれませんが＾＾；
意図しない結果だったらご容赦ください。　以上、ご参考まで

お礼

ありがとうございます！！

参考になりました！！

無事解決できました。

回答No.5

KURUMITO

解答Ｎｏ2です。ごめんなさい。式を間違いていました。
Ｆ４セルに次の式を入力し下方にドラッグコピーしてください。
=IF(A4="","",IF(INDEX($2:$2,IF(C4<>"",COLUMN(E4),COUNT(B4:E4)+1))<TODAY(),"期限切れ","継続中"))

お礼

ありがとうございます！！

参考になりました！！

無事解決できました。

回答No.3

tom04

こんにちは！
解釈が違っていたらごめんなさい。

振込日はまったく関係なくて、
B列のみの入力であれば、C3セル日付が「今日」以前であれば、「期限切れ」と表示、
C列以降に入力があればE3セル日付が「今日」以前であれば「期限切れ」
そうでなければ「継続中」となれば良いわけですかね？

もしそうであればF4セルに
=IF(A4="","",IF(COUNTA(B4:E4)<2,IF($C$3<TODAY(),"期限切れ","継続中"),IF($E$3<TODAY(),"期限切れ","継続中")))

という数式を入れオートフィルで下へコピーしてみてください。

以上、参考になれば幸いですが、
的外れならごめんなさいね。m(__)m

お礼

ありがとうございます！！

参考になりました！！

無事解決できました。

回答No.2

KURUMITO

Ｆ２セルに次の式を入力し下方にドラッグコピーします。
=IF(A3="","",IF(INDEX($2:$2,IF(C3<>"",COLUMN(E3),COUNT(B3:E3)+1))<TODAY(),"期限切れ","継続中"))

お礼

有難うございます。

やってみましたが、うまくいきませんでした。。。

回答No.1

Cupper

数値が入力されているセルの数を数える COUNT関数
と
　参照するセルを指定する INDEX関数 や INDIRECT関数、OFFSET関数
などを組み合わせて使ってはいかがでしょう

　=INDEX(B4:E4,1,COUNT(B4:E4))
　=INDIRECT(CHAR(65+COUNT(B4:E4))&ROW())
　=OFFSET(A4,0,COUNT(B4:E4))

これらで日付を入力する範囲の一番右に入力されている数字を参照することができます
あとは TODAY関数と比較するセル番号にこの関数を入れるだけです
（分かりやすいのは、INDEX関数と、OFFSET関数かな）
それぞれ Excelのヘルプを参照してどんな使い方をしているのかを確認して
理解した上で使うことをお奨めします
（そうすれば、いくらでも応用が利きます）

お礼

ありがとうございます。
ヘルプを読み勉強になりました！

しかし、うまく組みこむことができませんでした・・・涙

一番右に入力されている数字を参照するというより
一番右に入力されている列番号（B～E)が把握できればいいのですが・・・

勉強不足ですみません。