関数 誕生日を入力別のセルに色がつく、変更▢がつく

質問

bunjiiさんからいただいた日の値を出す式
=INT((INT(MOD(C4,100)/10)+MOD(C4,10))/10)+MOD(INT(MOD(C4,100)/10)+MOD(C4,10),10)
これの適用範囲を変えて22日＝4日＝13日＝31日,"△"表示できると思います。
=IF(AND( 　で日の値が同じ,月の値が同じ,"▢"を表示したいのです。AND以外でもできるのかも、、、。

回答No.4

bunjii · ベストアンサー

>軽くできる方法があるみたいなので４種類の判定全部が１行でできるみたいですね。
シート全体で負荷が若干軽くなる程度です。
分かり易くするために表の構成を変更してください。
A1:G39は次のように配置してください。H列からS列までは提示の画像に準じてください。
開催日 場 福島 10R 場 東京 10R
11月19日 1位 2位 3位 1位 2位 3位
騎手名 石橋 岩田 吉田隼 松岡 Rムーア Cルメール
騎手生日 403 312 1220 718 918 520
作業用騎 3 3 2 9 9 2
馬＿生日 502 401 317 524 122 220
作業用馬 2 1 8 6 4 2
[空欄]
場 東京 11R
馬番 1番 2番 3番 4番 5番 6番
騎手名 Cルメール Mバルサ 田辺 松岡正 柴田大 柴田晋
騎手生日 520 803 212 718 618 730
作業用騎 2 3 3 9 9 3
連動
馬＿生日 309 330 317 302 329 402
作業用馬 9 3 8 2 2 2
連動
結果 3
[空欄]

馬番 1番 2番 3番 4番 5番 6番
騎手名 川島 幸 武豊 デムーロ Aアッゼー 和田
騎手生日 1124 112 315 111 326 623
作業用騎 6 3 6 2 8 5
連動
馬＿生日 525 508 402 317 407 416
作業用馬 7 8 2 8 7 7
連動
結果
5行目の数値は2桁の日をあなたが指定した方法で1から9までの値に変換したものです。
7行目、13行目、16行目、24行目、27行目は5行目の数式をコピー&ペーストしています。
B14セルへ次の数式を設定します。
=IF(B12<>"",IF(SUMPRODUCT(($B$4:$Q$6=B12)*1),"◎",IF(SUMPRODUCT((SUBSTITUTE(B12,RIGHT(B12,2),"")=SUBSTITUTE($B$4:$Q$6,RIGHT($B$4:$Q$6,2),""))*1,($B$5:$Q$7=B13)*1),"□",IF(SUMPRODUCT((RIGHT(B12,2)=RIGHT($B$4:$Q$6,2))*1),"○",IF(SUMPRODUCT(($B$5:$Q$7=B13)*1),"△","")))),"")
解読困難かと思いますがIF関数は外側から順次計算されますので、順次分解していけばどのような論理で"◎"、"□"、"○"、"△"に区分けしているか理解できるでしょう。

1番外側のIF関数は「※※生日」が未入力のとき""を代入しています。
2番目は「※※生日」の月日の比較でデータの処理は次のようにしています。
SUBPRODUCT(($B$4:$Q$6=B12)*1)が0より大きいときB4:Q6にB12と同じ値が1個以上あることになります。
IF関数の論理式の戻り値が0のときはFALSEでその他はTRUEと判定されます。
SUMPRODUCT関数の代わりにCOUNTIF関数でも同じ結果を得られます。
COUNTIF($B$4:$Q$6,B12)
3番目は「※※生日」の月の値と日の値を加工して1から9までの値を作業用のセルにあらかじめ算出した値同士の複合値で比較しています。
SUMPRODUCT((SUBSTITUTE(B12,RIGHT(B12,2),"")=SUBSTITUTE($B$4:$Q$6,RIGHT($B$4:$Q$6,2),""))*1,($B$5:$Q$7=B13)*1)が0より大きいとき"□"を代入して終了します。
SUBSTITUTE関数は元の文字列の中の指定の文字列を置換文字列に書き換えます。
SUBSTITUTE([元の文字列],[指定の文字列],[置換文字列])
SUBSTITUTE(B12,RIGHT(B12,2),"")→SUBSTITUTE("520",RIGHT("520",2),"")→SUBSTITUTE("520","20","")→5
SUMPRODUCT関数は配列数の要素の積を合計します。
第1引数の配列はB5:Q7の文字列から右2文字を消した値とB12の文字列から右2文字を消した値と同じのセルを1とし、違うセルを0に置き換えた数値の配列です。
第2引数はB5:Q7の1桁数値とB13の1桁数値を比較した結果を1または0の配列にしたものです。
第1引数と第2引数を要素毎に掛け算した値（積）を合計します。
A*Bは論理演算のAND(A,B）と同じです。
また、(B5=B13)*1→(3=2)*1→(FALSE)*1→0と言うことです。
4番目以降の論理は数式から解読してください。
2番目から5番目のIF関数の論理式を分離すればあなたがやろうとしていることに一致するか否かを判断できると思います。
尚、5番目の"△"にも該当しないデータは今回の質問にありませんので無意味かと思います。

>もーひとつ、"◎","〇"の式がすでにありますので、"□","△"の式も別にお願いできればと
上述のように解説してありますので、ご自身が数式を組み替えてください。

補足

いや～、すごいですね～。読んでるうちはどーしようかと思いましたが、画像までつけてくださって、ありがとうございます。いったん日の計算を出してあるわけですね、、、実際に実装までしてしてくださって、感謝に堪えません。
ありがとうございました～～。

回答No.3

bunjii

>式が長くなるとまずいんじゃないかと思いますので「◎」「〇」が入る行と「▢」「△」が入る行に分けたということです。
B4:Q5と21行目の比較で日付に相当する右2桁を加算して1桁の1から9までに中間加工することを作業用セル範囲で行うことでIF関数の多重入れ子のときに数式を短くでき、然も、バグフィックスや数式の組み換えで解読しやすくすることが行を増やす目的です。
あなたの考え方の『「◎」「〇」が入る行と「▢」「△」が入る行に分けた』のは本末転倒になるでしょう。

>＞月が同じで然もB4:Q5を加工した1桁データが一致する2つの条件で"▢"となるということですか？
>そういうことなんです。
日付部分の2桁を1桁に加工した値を比較する範囲は騎手生日と騎手生日および馬生日の双方を比較対象にして良いのですよね？
これは重要なことなので再確認させてください。
また、"□"に判定されるためには次のように判断することでしょうか？
騎手名　=Aシュタ
月の比較
騎手生日=104→1
比較対象=F5セル=122→1
日の比較
騎手生日=104→4
比較対象=F5セル=122→2+2→4
馬　生日=308→8
F5セルは1つのセルで2つの条件が一致するのでI22セルへ"□"を代入して終わる
月の比較
馬　生日=308→3
比較対象=D5セル=317→3
日の比較
馬　生日=308→0+8→8
比較対象=D5セル=317→1+7→8
D5セルは1つのセルで2つの条件が一致するのでI24セルへ"□"を代入して終わる
他の例
騎手名　=川島
月の比較
騎手生日=1124→11
比較対象=P4セル=1130→11
日の比較
騎手生日=1124→2+4→6
比較対象=P4セル=1130→3+0→3
P4セルは月の情報は一致するが日の情報が不一致なので次のチェックへ移る
月の比較
馬　生日=525→5
比較対象=G4セル=Q4セル=520→5
　〃　　=B5セル=502→5
日の比較
馬　生日=525→2+5→7
比較対象=G4セル=Q4セル=520→2+0→2
　〃　　=B5セル=502→0+2→2
G4,Q4,B5セルは月の情報が一致していて日の情報が不一致のため次のチェックへ移る
他の比較対象セルは月の情報が不一致なので次のチェックへ移る

補足

B4:Q5を対象として、B21,B23の行の値を比較します。

"□"の一致条件は上のとうりで間違いありません。

軽くできる方法があるみたいなので４種類の判定全部が１行でできるみたいですね。それでお願いします。
順番は◎,No＞□,No＞〇,No＞△,No,""の順で判定してください。

もーひとつ、"◎","〇"の式がすでにありますので、"□","△"の式も別にお願いできればと、、、。行を追加して使う場合もあるかと、、、。

宜しくお願い致しま～す。

回答No.2

bunjii

>上の画像に２５行目を追加しました、B23とB4:Q5との比較になります。
25行目に1行挿入したのですか？
そこにどのような値が入りますか？
B21は騎手生日の値ですが比較値ではなく、B23（馬生日）のみがB4:Q5から加工した1桁の値（1から9）と比較するのですか？

>”▢”は月が同じだけじゃだめなんです。
補足の度に追加の条件が増えますね。
月が同じで然もB4:Q5を加工した1桁データが一致する2つの条件で"□"となるということですか？
条件を良く整理して頂かないと先へ進められません。

補足

A21騎手生日
A22連動、「◎」「〇」が入る行
A23連動、「▢」「△」が入る行
A24馬　生日
A25連動、「◎」「〇」が入る行
A26連動、「▢」「△」が入る行
A27結果
画像の表の変更です。
ベストは画像のA22とA24の行に「◎」「〇」「▢」「△」が判定されて入るのがベストなんですけどね。式が長くなるとまずいんじゃないかと思いますので「◎」「〇」が入る行と「▢」「△」が入る行に分けたということです。

＞月が同じで然もB4:Q5を加工した1桁データが一致する2つの条件で"▢"となるということですか？
そういうことなんです。

で、”△”は加工した１桁データだけの一致条件です。
お分かりいただけたでしょうか？

宜しくお願い致します。でわ

回答No.1

bunjii

>これの適用範囲を変えて22日＝4日＝13日＝31日,"△"表示できると思います。
？？？？、意味不明。
『=IF(AND( 　で日の値が同じ,月の値が同じ,"▢"を表示したいのです。AND以外でもできるのかも』についても考え方が分かりません。
あなたの脳内を質問の文言から引き出さなければならないので条件提示を細部に亘って説明して頂けないと回答できません。

添付画像から読み取れるのは月日が同じのセルがあるときの"◎"と日が同じのセルがあるときの"○"について数式ができているようです。
B22=IF(B21<>"",IF(SUMPRODUCT(($B$4:$Q$5=B21)*1),"◎",IF(SUMPRODUCT((RIGHT(B21,2)=RIGHT($B$4:$Q$5,2))*1),"○","")),"")
これに月が同じの"□"を追加すると次のようになり、結果は"◎"と"□"にになってしまいます。
B22=IF(B21<>"",IF(SUMPRODUCT(($B$4:$Q$5=B21)*1),"◎",IF(SUMPRODUCT((SUBSTITUTE(B21,RIGHT(B21,2),"")=SUBSTITUTE($B$4:$Q$5,RIGHT($B$4:$Q$5,2),""))*1),"□",IF(SUMPRODUCT((RIGHT(B21,2)=RIGHT($B$4:$Q$5,2))*1),"○",""))),"")

あなたの目的はそれで良いのでしょうか？
◎、○、△、□の順ならすべてが表示されると思います。

補足

>◎、○、△、□の順ならすべてが表示されると思います。
◎,No＞▢,No＞〇,No＞△,No,""の順に並べば最高なんですけどね。

＞あなたの脳内を質問の文言から引き出さなければならないので条件提示を細部に亘って説明して頂けないと回答できません。
すいません、、、。
上の画像に２５行目を追加しました、B23とB4:Q5との比較になります。

”▢”は月が同じだけじゃだめなんです。
=INT((INT(MOD(B4:Q5,100)/10)+MOD(B4:Q5,10))/10)+MOD(INT(MOD(B4:Q5,100)/10)+MOD(B4:Q5,10),10)
C4をB4:Q5に変えました。
この値とB23の=INT((INT(MOD(B23,100)/10)+MOD(B23,10))/10)+MOD(INT(MOD(B23,100)/10)+MOD(B23,10),10)この値の一致が必要なんです。
騎手生日=926 → 2+6 → 8 → 0+8 → 8
馬　生日=208 → 0+8 → 8 → 0+8 → 8
馬　生日=829 → 2+9 → 11 → 1+1 → 2
二つの条件が一致して”▢”になるんです。条件が２つあるんです。
何卒宜しくお願い致します。