[[20050526115154]]　『複数条件で数値を返す方法』（カメ吉）　ページの最後に飛ぶ

『複数条件で数値を返す方法』（カメ吉）

 初めましてお知恵を借りたくてお邪魔しました　エクセル初心者です。
学校の成績表を作ってまして行き詰まりました。どなたかの暖かい救いの手があれば助かります。
以下のような表がありましてふたつの条件を満たす評価を出す計算式等がありましたらお願いします。

表１

氏名　国語　算数　理科　社会　英語　音楽　合計　評価

山田　　20 　２０　２０　　 8　　２０　　２０　　１０８　　　４

 表２

評価　条件１（教科点）　　条件２（合計）

１　　各教科１５点以上　　９５以上

２　　各教科１２点以上　　８８以上９５以下

３　　各教科１０点以上　　８１以上８７以下

４　　各教科５点以上　　　７４以上８０以下

５　　各教科１点以上　　　６７以上７３以下

外　０点が一つでもある場合　　　６６以下
　

条件１が優先します。
この説明で理解できますでしょうか？
ヨロシクお願いします。

Excel2002　　WindowsXP

教科は全部で6教科でよろしいですか？（KD)

はい　６教科でお願いします。　（カメ吉）

ところで条件2の以上と以下なんですが数値を

はっきりして欲しいのですが、９５以上で１　９４以下８８以上で４

ってことですかね？（KD)

すみません！！　数値を間違えて入れてました。
KD　さんの言われるとおり

９５以上が１

９４以下８８以上が２です

わかりにくくて申し訳ないです。　（カメ吉）

かなり式が長くなりつなげられなかったので、作業列を作っても良いでしょうか？

（KD)

=IF(OR(MIN(A1:A6)=0,SUM(A1:A6)<66),"外",MAX(LOOKUP(MIN(A1:A6),{1,5,10,12,15},{5,4,3,2,1}),LOOKUP(SUM(A1:A6),{66,74,81,88,95},{5,4,3,2,1})))

因みに

 外　０点が一つでもある場合　　　６６以下　 
の「外」を「6」と表記しても良いのであれば

=MAX(LOOKUP(MIN(A1:A6),{0,1,5,10,12,15},{6,5,4,3,2,1}),LOOKUP(SUM(A1:A6),{0,66,74,81,88,95},{6,5,4,3,2,1}))

衝突

B2～G2に点数を入れるとして、H2に合計、I2に評価で

J2とK2を作業列として

I2=IF(J2="外","外",IF(J2<K2,J2,K2))

H2=IF(AND(B2>=15,C2>=15,D2>=15,E2>=15,F2>=15,G2>=15),1,IF(AND(B2>=12,C2>=12,D2>=12,E2>=12,F2>=12,G2>=12),2,IF(AND(B2>=10,C2>=10,D2>=10,E2>=10,F2>=10,G2>=10),3,IF(AND(B2>=5,C2>=5,D2>=5,E2>=5,F2>=5,G2>=5),4,IF(AND(B2>=1,C2>=1,D2>=1,E2>=1,F2>=1,G2>=1),5,IF(COUNTIF(B2:G2,0),"外",""))))))

K2=IF(H2>=95,1,IF(H2>=88,2,IF(H2>=81,3,IF(H2>=74,4,IF(H2>=67,5,IF(H2<=66,"外",""))))))

でどうですか？間違ってるかも（KD)

 う～ん、わたしゃ頭悪いから良うわかりまへんねんけど・・・
 これって条件１が優先するっちゅうことでっしゃろ？
 それやったら条件２は関係ないんとちゃいまんのん？
 例えば 13,15,13,12,12,13　やと、条件１の評価は2になりますけど
 合計の評価は4になりますわなぁ。
 条件１が優先やと評価は2やから、条件2は関係おまへんやろ？？？

 条件１と条件２でどちらか低い方と言うんなら話は分かりますけどなぁ・・・
 　　良う理解でけへん（弥太郎）

　私もそう思ったんですけど、例えば１教科でも３があったら

　条件１では評価が５になりますよね。しかし他の教科で20や30があったとして

　合計が74以上の場合も考えられるのではと…

　であれば条件2も必要なのかなと…（KD)

(KD)さま　弥太郎様お手数をおかけします。

得点は２０点満点なのです。

条件付けが本人も数学的にわかっていないのですが

仮に全教科を１５点取っていたとすると

条件１が優先なので評価は１になるはずですが

合計が９０点しかないので条件２の評価は２が適用となり　私が欲しい数値も評価が２なのです。

すみません　言葉足らずで。

計算式をありがとうございます。

これから試してみます。

（カメ吉）

 何度読んでも私には、理解不能です。

 >仮に全教科を１５点取っていたとすると 
 >条件１が優先なので評価は１になるはずですが 
 >合計が９０点しかないので条件２の評価は２が適用となり　私が欲しい数値も>評価が２なのです。

 ということなら、優先順位は条件2のほうが高いということになりませんか？
 例えば、下記のような場合。評価はどうなってほしいのでしょうか。
 （※アドバイスできるかどうかは分かりませんが……。）

 氏名 国語 算数 理科 社会 英語 音楽 合計 評価
 山田   10   10   10   10   10   10   60
 田中   10   20   20   20   20   20  110
 (kkk)

確かに条件2が優先みたいですね。

条件2が優先になるなら条件1は必要ないような気がしますがね。

なんだかよくわからないのでギブアップします。

（KD)

「優先」の意味が相談者と回答者で捉え方が違うような気がします。

相談者の「優先」＝評価の順番が優先

回答者の「優先」＝評価基準が優先

（傍観者）

 下から失礼します。

 回答者の方が言っておられるのは、見方の順番はどうであっても、最終的に条件2で決まるのであれば、
 条件1は必要ないので、計算式に考慮する必要ないのでは・・・？（逆の場合も同じです。）
 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑これは弥太郎さんが言っておられます。

 ってことだと思いますよ。

 （川野鮎太郎）

 無記名さんが書かれた式でよいのなら・・・
 =CHAR(MAX(CODE(LOOKUP(MIN(B2:G2),{0,"外";1,5;5,4;10,3;12,2;15,1})),CODE(LOOKUP(SUM(B2:G2),{0,"外";67,5;74,4;81,3;88,2;95,1}))))
 とすることで、「外」を表示することができます。　　(Hatch)

 なんとなくかめ吉さんの魂胆が見えてきましたわ。（笑
 kkkはんの例題で申せば山田は「外」で田中は「３」になるんとちゃいまっか？
 かめ吉さんのおっしゃるニュアンスからすれば条件１と条件２のどちらか低い方
 （成績の）を評価点にしたいと受け取れまんねんけど、どうでっしゃろ？
 これ、関数で処理しょうと思うたらえらい長い式になってまいまんなぁ。（ホンマは
 わかれへん！！！）
 で、まぁ、他人様に疎まれる（笑　ユーザー関数を作ってみましたんやけど、いっぺん
 試してみまっか？
 1)Alt+F11でVBEを開き
 2)「挿入」→「標準モジュール」を選択して
 3)真新しい画面に下のコードをコピペ
 4)エクセルにもどる
 これで準備完了

 あとは評価を出したいセルに（山田と入力してあるセルがA1やとしたばやい）
          =hyouka(a1)
 と打ち込んでみてくだはい。
 どうでっか、こんなんで。
 これはあくまで関数ですんで、扱いは他の関数と同じです、ハイ。
 　　　　（弥太郎）
 '------------------------------------------
 Function hyouka(data As Range)
    Dim tbl As Range
    Dim get_pnt As Integer, totl_pnt As Integer
    Set tbl = Range(Range(data.Address).Offset(, 1).Address & ":" _
                & Range(data.Address).Offset(, 6).Address)

    pnt = Application.WorksheetFunction.Min(tbl)
    Select Case pnt
        Case Is >= 15
            get_pnt = 1
        Case Is >= 12
            get_pnt = 2
        Case Is >= 10
            get_pnt = 3
        Case Is >= 5
            get_pnt = 4
        Case Is >= 1
            get_pnt = 5
        Case Else
            get_pnt = 6
    End Select
    Select Case WorksheetFunction.Sum(tbl)
        Case Is >= 95
            totl_pnt = 1
        Case Is >= 88
            totl_pnt = 2
        Case Is >= 81
            totl_pnt = 3
        Case Is >= 74
            totl_pnt = 4
        Case Is >= 67
            totl_pnt = 5
        Case Else
            totl_pnt = 6
    End Select

    If get_pnt > totl_pnt Then

        hyouka = get_pnt
    Else
        hyouka = totl_pnt
    End If
    If hyouka = 6 Then
        hyouka = "外"
    End If

 End Function

 >山田は「外」で田中は「３」になるんとちゃいまっか？
 そうだったら、無記名さんの回答でOKだったはずですけど・・・
 どうなんでしょ?　　(Hatch)

 よく理解ているかどうかわかりませんが、試しに書いてみます。　　　　　　　　　(LOOKUP)
 まず、最低点で、最高評価を決め、次に合計点から評価を算出するものです。

 K2:M6に変換表を配置します。
 確認用にJ行に科目の最低点を表示させています。J2に=IF(A2="","",MIN(B2:G2))。
 I2に=IF(A2="","",INDEX(K$1:K$6,MATCH(H2,M$1:INDEX(M$1:M$6,MATCH(J2,L$1:L$6)))))。
 として、これらを下にフィルドラッグ。というものですが、どうでしょう？

  　A     B     C     D    E    F     G     H     I     J    K   L  M
1　氏名　国語　算数　理科　社会　英語　音楽　合計　評価　　min　外　 0　 0
2　小野　 20　　20　　20　　20　　20　　20　 120　　 1　　20　 5　 1　67
3　関根　 15　　15　　15　　15　　15　　20　　95　　 1　　15　 4　 5　74
4　木村　 15　　15　　15　　15　　15　　15　　90　　 2　　15　 3　10　81
5　塚田　  0　　20　　20　　20　　20　　20　 100　　外　　 0　 2　12　88
6　大山　  5　　20　　20　　20　　20　　20　 105　　 4　　 5　 1　15　95
7　升田　 11　　20　　20　　20　　20　　20　 111　　 3　　11

 Hatchはん、わたしゃそのままコピペしとりましたワ。（笑
 うん、仰有るとおりです、いけま。
 話は変わりますけど、61UPはんはLOOKUPはんの別名でっか？
 やとしたら、１つだけ私に近付きましたなぁ。（笑
 でも、こればっかしは負ける訳には参りまへんのんで来年の年始めにはきっちし
 差ぁをひろげときまっさぁ。（笑
 それにしてもこのメンバー、LOOKUPはんならではの取り上げ方でんなぁ。
 草場の陰から「おい、その評価はないで」とぼやいとる御方も居てまっしゃろナ。（笑
 あぁ、懐かしや・・・（弥太郎）

 ふむふむ、て事は、これでもOK！　ですか？ 
細かい処理はしてませんけど・・・
=SUBSTITUTE(MAX(LOOKUP(MIN(B2:G2),{0,1,2,10,12,15},{6,5,4,3,2,1}),LOOKUP(H2,{0,66,67,74,81,88,95},{6,6,5,4,3,2,1})),6,"外")
(sin)
※読み返してみたら、既出の方法でした。失礼しました。。。　m(__)m

コメント返信：

[ 一覧(最新更新順) ]

[[20050526115154]] 『複数条件で数値を返す方法』（カメ吉） ページの最後に飛ぶ

[[20050526115154]]　『複数条件で数値を返す方法』（カメ吉）　ページの最後に飛ぶ