[[20210818155142]]　『条件に合うかどうかを確認する関数』（nobbyta）　ページの最後に飛ぶ

『条件に合うかどうかを確認する関数』（nobbyta）

学校での成績を出すとき、観点の評価(A,B,C）の組み合わせによって評定５段階が正しいかどうかを判定する関数があればご教示ください。

観点の評価の組み合わせと評定は以下の通りです。
AAA 5
AAA 4
AAB 4
BBB 3
ABC 3
ABB 3
BBC 3
BCC 2
CCC 1

これ以外の組み合わせは認めない。
例えば、"AAB"で「3」の評定がでている場合は何かエラー（×等）の標記を返す関数です。一つだけの組み合わせ(例えばAAAのみ）だったら、If and 関数で何とかなるのですが、多くの組み合わせがあるので、何かよい方法があれば
ありがたいと存じます。よろしくお願いいたします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 >AAA 5
 >AAA 4
 これはAAAだった場合、5でも4もいいということだろうか？
（ねむねむ） 2021/08/18(水) 16:08

 ＞これ以外の組み合わせは認めない

 「ABA 4」もダメってことですか？(順序も問題にするのか、組合せの問題なのかちょっと不明瞭なんですけど）

（半平太） 2021/08/18(水) 16:11

AAA 5 AAA 4

AAA 5
ABB 4

と、書きたかったのでは？
（tim） 2021/08/18(水) 16:14

 下の方に
 >ABB 3
 があるのだが。
（ねむねむ） 2021/08/18(水) 16:16

 組み合わせ的には10通りあるはず
 AAA
 AAB
 AAC
 ABB
 ABC
 ACC
 BBB
 BBC
 BCC
 CCC

 AAC とか ACC は組み合わせ的にありえないということ？
（とおりすがり） 2021/08/18(水) 16:18

ABBありましたね。失敬

「AAA 4」がなければ数式で行けそうですね
（tim） 2021/08/18(水) 16:24

早速のご回答ありがとうございます。
ねむねむ　様
これはAAAだった場合、5でも4もいいということだろうか？　→　その通りです。

半平太　様
「ABA 4」もダメってことですか？(順序も問題にするのか、組合せの問題なのかちょっと不明瞭なんですけど）　→　組合せです。ですので、ABA 4はOKです。

tim様
ABB　は３でよいです。

通りすがり様
確かに10通りあるのですが、簡易的に少なくしました。

（nobbyta） 2021/08/18(水) 16:29

A1に評価があるとして。
下記の式で文字列内の各文字(A,B,C)に重みをつけて、計算する

=LEN(A2)-LEN(SUBSTITUTE(A2,"A",""))*3
+ LEN(A2)-LEN(SUBSTITUTE(A2,"B",""))*2
+ LEN(A2)-LEN(SUBSTITUTE(A2,"C","")) *1

そして、その値から評価(5,4,3,2,1)を求める。※B1に上の式があるとする

=IF(B1>=9,"5",IF(B1>=8,"4",IF(J2>=5,"3",IF(B1>=4,"2","1"))))

この値と、比較して違えばエラーと判断できるのでは。

AAAは必ず5になりますし、
面倒な方法かも。
ご参考までに。
（tim） 2021/08/18(水) 16:39

 式は
 =(LEN(A2)-LEN(SUBSTITUTE(A2,"A","")))*3
 +(LEN(A2)-LEN(SUBSTITUTE(A2,"B","")))*2
 +(LEN(A2)-LEN(SUBSTITUTE(A2,"C","")))
 では？
（ねむねむ） 2021/08/18(水) 16:44

殴り書きですまない。
文字列内の特定文字数を求めた式に対して重みをつけてくれ。

細かいミスが目立つ。反省。

他アプローチがあれば、私も知りたいのでぜひ。

（tim） 2021/08/18(水) 16:49

 C2セル =IF(B2="","",IF(ISNUMBER(MATCH(B2*1000+SUMPRODUCT((LEN($A2)-LEN(SUBSTITUTE($A2,{"A","B","C"},""))),{100,10,1}),{5300;4300;4210;3030;3111;3120;3021;2012;1003},0)),"","×"))
 下にコピー

 <結果図>
 行  __A__  __B__  __C__
  1  評価   評定   適否 
  2  AAA      5       
  3  AAA      4       
  4  AAB      4       
  5  BBB      3       
  6  ABC      3       
  7  ABB      3       
  8  BBC      3       
  9  BCC      2       
 10  CCC      1       
 11  ABA      4       
 12  ACC      5  　×

（半平太） 2021/08/18(水) 16:53

半平太　様

　返信が遅くなり、申し訳ございませんでした。
　式をコピペしたところ、できました。
　ありがとうございました。

　関数の意味は少しずつ、時間をかけて解析したいと思いますが、
　わがままを言って申し訳ございませんが、もし、お時間あれば、解説もしていただけるとありがたい
です。２つ目のif以降がチンプンカンプンです。
（nobbyta） 2021/08/19(木) 16:24

 ＞組合せです。ですので、ABA 4はOKです。

 との事なので、A,B,Cがいくつあるのか調べなければならない。
 常套手段に、Aを空白に代えて文字の長さを調べ、元の長さとの差がAの個数だ、と言うのがあります。

 通常、こんな感じの式になります。
 　　　　↓
 =LEN(A2)-LEN(SUBSTITUTE(A2,"A",""))

 但し今回、文字数は3と決まっているので、
 これで良かったのですが、惰性でLEN($A2)を使ってしまった。
 　↓
  =3-LEN(SUBSTITUTE(A2,"A",""))

 実際は"A"だけじゃないので、第二引数を配列にします。→{"A","B","C"}
 すると、A,B,Cの数が配列で戻ってきます。

 A,B,Cの分別認識の為、それぞれに重みづけをする。
 つまり、配列→{100,10,1}　を掛けてから、合算する(Sumproductを使用）
 それで、例えば、BBA なら120が得られる。

 許容組合せなら9通の数値→300,300,210,30,111,120,21,12,3　となる。

 それに許容評定の数字を1000倍して足せば以下の9通りになります、
 →5300,4300,4210,3030,3111,3120,3021,2012,1003

 個々の「分別認識の数値」を算出後、
 それが「許容評定数値」にマッチするかMatch関数で調べれば、
 正常な数値(1～9)が返ってくるなら「空白」、返ってこなければ「×」を出せばよい。

（半平太） 2021/08/19(木) 18:46

コメント返信：

[ 一覧(最新更新順) ]

[[20210818155142]] 『条件に合うかどうかを確認する関数』（nobbyta） ページの最後に飛ぶ

[[20210818155142]]　『条件に合うかどうかを確認する関数』（nobbyta）　ページの最後に飛ぶ