【Excel】生年月日から受けられる検診を返したい

質問

こんばんは。
エクセルは簡単な関数程度しか使えませんが、教えていただけると助かります。

今、生年月日から各健康健診が受けられるかをエクセルで返せるようにしたいのですが、検診ごとに対象が違い困っています
検診は複数あり、関数で組むかデータベースで返すか悩んでいます

例
(1)一般検診・・・25歳以上70歳未満が対象
(2)××検診・・・20歳以上50歳以下、かつ年齢偶数、かつ女性のみ
・・・・

また、年齢偶数というのは今日現在ではなく、年度ごとによって分けられています
　1980/4/2～1981/4/1　＝29歳　奇数
　1981/4/2～1982/4/1　＝30歳　偶数

例(1)に関してはIFで絞り込んだのですが、例(2)は理論式がめちゃくちゃになってしまいました・・・
データベースを使おうかと思い、生年月日からVLOOKUPで探そうとしたんですが、そのデータベースが相当な量になってしまいます

　　　　　一般検診　××検診　○○検診　◇◇検診
1980/4/2　　○　　　　×　　　　　×　　　×
1980/4/3　　○　　　　×　　　　　×　　　×
～
1981/4/2　　○　　　　○　　　　　　○　　　　○

これを延々とやるのはさすがに厳しいのではないかと思ってます・・・
（3万くらいいきました・・・。間違えたらおしまいという感じです）

イメージとしては・・・

名前　生年月日　性別　一般検診　××検診　○○検診　◇◇検診
○○　1980/8/8　女　　○　　　　×　　　　　　×　　　　×
◇◇　1989/9/9　男　　×　　　　×　　　　　　○　　　　○

生年月日を入力してうけられる検診には○、受けられない検診には×
を返せるようにしたいのです

もしよろしければお力添えいただければと思います
よろしくお願いいたします

回答No.1

rukuku · ベストアンサー

こんばんは

>検診は複数あり、関数で組むかデータベースで返すか悩んでいます
エクセルのIF関数でも、データベースでも、基本的な考え方は同じです。

＞年齢偶数というのは今日現在ではなく、年度ごとによって分けられています
４月1日の時点での年齢を求めればOKです。
DATEDIFという関数が使えます。
http://www.relief.jp/itnote/archives/000423.php
http://maglog.jp/excel/Article223046.html

なお、今年の４月２日は、以下の数式で得られます。
=DATE(YEAR(TODAY()),4,1)

＞かつ年齢偶数
mod(年齢,2)=0
なら年齢は偶数です。

実際に運用するには、性別の右の列（以下の説明ではD列）に「年齢」という列をもうけておくと便利です。
D2の数式は以下のようになります。
=DATEDIF(B2,DATE(YEAR(TODAY()),4,1),"Y")

これらを組み合わせると、「××検診」の二行目は
=IF(AND(20<=D2,D2<70,C2="女",MOD(D2,2)=0),"○","×")
となります。

お礼

rukukuさま

ご回答、ありがとうございます！
なるほど、4/1時点で考えれば良いのですね
ありがとうございます
また、年齢なのですが、その検診日が人によって違うので
データを常に更新したいと考えています
つまり、年齢はその検診日の年齢表記にしたいのです
その場合はどうすればよいでしょうか？
恐れ入りますが、ご回答いただけますと助かります

回答No.6

rukuku

＞また、年齢なのですが、その検診日が人によって違うので
＞データを常に更新したいと考えています
＞つまり、年齢はその検診日の年齢表記にしたいのです
＞その場合はどうすればよいでしょうか？

回答で書いた「年齢」は「年度」で計算するのためのものです。
受診日の年齢を知りたいのならば、D列に「受診日」を追加し、
E2に
=DATEDIF(B2-1,D2,"Y")
で得ることができます。
(^。^)v

お礼

すいません、何度もありがとうございます！
無事完成させることができました！
本当にありがとうございました！！

回答No.5

tom04

No.3・4です
ほんとぉ～！にっ！ごめんなさい

E3セル　=IF(B3<=DATE(YEAR(B3),MONTH(4),DAY(1)),DATEDIF(B3,"2010/4/1","y"),DATEDIF(B3,"2010/4/1","y"))
でした。

これでもかっ！というくらい顔をだしてしまいました。
どうもすみません。m(__)m

お礼

いえいえ、何度もありがとうございます！
おかげで、きちんとできました！！
そして、勉強になりました
ありがとうございました！

回答No.4

tom04

No.3です。
たびたびごめんなさい。
先ほどの回答でE3セルの数式が間違っていると思います。
E3　=IF(B3<=DATE(YEAR(B3),MONTH(4),DAY(1)),DATEDIF(B3,"2010/4/1","y")+1,DATEDIF(B3,"2010/4/1","y"))
が正解だとおもいます。
（要らない「1」をプラスしていました）

どうも何度も失礼しました。m(__)m

回答No.3

tom04

こんばんは！
質問内容の解釈が間違っていたらごめんなさい・・・

一番引っかかったのは
＞また、年齢偶数というのは今日現在ではなく、年度ごとによって分けられています
　1980/4/2～1981/4/1　＝29歳　奇数
　1981/4/2～1982/4/1　＝30歳　偶数
というところです。
つまり年度末の4/1での年齢という解釈でいいのだと判断して
↓の画像のような表を作ってみました。
（※表では　1981/4/2～1982/4/1　＝30歳　の部分が28歳になってしまいますけど、どちらが正しいのかわかりません）

作業列として、D列に実年齢・E列に年齢偶数を挿入しています。
実年齢が必要ないのであれば、削除してください。

数式としては
D3　=DATEDIF(B3,TODAY(),"y")
E3　=IF(B3<=DATE(YEAR(B3),MONTH(4),DAY(1)),DATEDIF(B3,"2010/4/1","y")+1,DATEDIF(B3,"2010/4/1","y"))
F3　=IF(AND(D3>=25,D3<70),"○","×")
G3　=IF(OR(C3="男",D3<20,D3>50,MOD(E3,2)=1),"×","○")
として、D3～G3を範囲指定した後に、オートフィルで下へコピー
又はオートフィルマークの(＋)でダブルクリック

これで希望に近い形ができないでしょうか？

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m

お礼

tom04さん
こんばんは！
丁寧な解説ありがとうございます！
すごく分かりやすくて助かりました！
本当にありがとうございます！

回答No.2

rukuku

訂正です

4/1日生まれの人をどう扱うかと考えているうちに記述を間違えました。

＞なお、今年の４月２日は、以下の数式で得られます。
＞=DATE(YEAR(TODAY()),4,1)
今年の4月2日を得るには
=DATE(YEAR(TODAY()),4,2)
です。

年齢を計算する式については、先ほどの回答（↓）の通りです。
=DATEDIF(B2,DATE(YEAR(TODAY()),4,1),"Y")

年齢は、誕生日の「前日」加算されます。
http://koyomi.vis.ne.jp/doc/mlwa/200804020.htm
http://www5d.biglobe.ne.jp/Jusl/TomoLaw/NenreiKazoekata.html
しかし、DATEDIF関数は指定された「その日」との差を計算します。

法律による年齢のルールととDATEIDIF関数の微妙な違いによって、調整が必要になります。

お礼

わざわざ訂正ありがとうございます！
年齢は誕生日の前日加算なんですね・・
それも初めて知りました・・・
本当にありがとうございます！