[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『予選通過者に印をつけたい』（minami）
陸上競技大会の各種目出場者のリストがあります
A B C D E F G H 1 2 女子1年100Ｍ 4組 1 着 + 4 3 4 予選 1組 5 ﾚｰﾝ 名前 所属 学年 順位 記録 6 1 7 2 A 1 7 15.66 8 3 B 1 4 14.67 9 4 C 1 5 14.75 10 5 D 1 1 13.65 Q 11 6 E 1 2 14.28 12 7 F 1 6 15.40 13 8 G 1 3 14.48 14 15 16 予選 2組 17 ﾚｰﾝ 名前 所属 学年 順位 記録 18 1 19 2 O 1 2 14.21 q 20 3 P 1 6 16.02 21 4 Q 1 4 14.28 22 5 R 1 1 13.99 Q 23 6 S 1 3 14.23 24 7 T 1 5 14.88 25 8 26 27 28 予選 3組 29 ﾚｰﾝ 名前 所属 学年 順位 記録 30 1 31 2 H 1 5 14.51 32 3 I 1 2 14.12 q 33 4 J 1 1 13.44 Q 34 5 K 1 3 14.21 q 35 6 L 1 4 14.38 36 7 M 1 6 15.02 37 8 N 1 7 15.11 38 39 40 予選 4組 41 ﾚｰﾝ 名前 所属 学年 順位 記録 42 1 43 2 U 1 1 13.88 Q 44 3 V 1 4 14.66 45 4 W 1 6 14.82 46 5 X 1 2 14.01 q 47 6 Y 1 3 14.55 48 7 Z 1 5 14.78 49 8
予選は2組ある種目、3組ある種目、4組ある種目があり 2組の時 各組2位と各組3位以下でタイム上位4名が予選突破 3組の時 各組1位と各組2位以下でタイム上位5名が予選突破 4組の時 各組1位と各組2位以下でタイム上位4名が予選突破 となり、Ｅ2に組数・Ｆ2に順位通過の位・Ｈ2にタイム通過の人数を あらかじめ入力します 各組の人数は最大8人ですが少ない場合が多いです
上記表で列Ｇにタイムを入力していったとき 列Ｇのタイムの隣のセル"列Ｈ"に 順位通過の時「Ｑ」が タイム通過の時「ｑ」が自動で表示するようにしたいのですが 列Ｈに入力する関数をご教授願います よろしくお願いいたします
ちょっとよく分からないので ご提示のデータでは どの値をどの様に見て Qとqが決まっているのか 具体的に数字を挙げながら 説明して見られるのが良いのではないかと思います。
サンプルデータでは記録の結果で順位が決まっている様なので そう考えると「タイム上位・・・」なんて考えなくても 良さそうにも思うのですが。。。。？
(HANA)
こんな感じ H7=IF(ISNUMBER(F7),IF(E7<=$D$2,"Q",IF(F7<=SMALL(IF($E$7:$E$49>$D$2,$F$7:$F$49),$F$2),"q","")),"") 配列数式です H49までフィルコピー
同着、同タイムを考慮していません
By しげちゃん
訂正します（１列ずれてましたね）
=IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$7:$F$49>$F$2,$G$7:$G$49),$H$2),"q","")),"")
By しげちゃん
こんにちは。 作業列（I,J）を使って考えてみました。 ・I列で各行に組情報を計算 ・J列で順位通過以外者の記録を再掲 しています。
計算の都合上、E2やC4など、組の情報は数値にしています。 （必要なら表示形式 0"組" などで対応ください） ついでに、F2、H2も数式にしてみました。 （E2の値によって設定されます）
あとやはり、同記録者がいたときにどうするかですね。
R/C A B C D E F G H I J 1 2 女子1年 100Ｍ 4 1 着 + 4 3 組↑ 組 タイム 4 予選 1 ←組 5 ﾚｰﾝ 名前 所属 学年 順位 記録 0 6 1 0 7 2 A 1 7 15.66 0 15.66 8 3 B 1 4 14.67 0 14.67 9 4 C 1 5 14.75 0 14.75 10 5 D 1 1 13.65 Q 0 11 6 E 1 2 14.28 0 14.28 12 7 F 1 6 15.4 0 15.4 13 8 G 1 3 14.48 0 14.48 14 0 15 0 16 予選 2 ←組 1 17 ﾚｰﾝ 名前 所属 学年 順位 記録 1 18 1 1 19 2 O 1 2 14.12 q 1 14.12 20 3 P 1 6 16.02 1 16.02 21 4 Q 1 4 14.28 1 14.28 22 5 R 1 1 13.99 Q 1 23 6 S 1 3 14.23 1 14.23 24 7 T 1 5 14.88 1 14.88 25 8 1 26 1 27 1 28 予選 3 ←組 2 29 ﾚｰﾝ 名前 所属 学年 順位 記録 2 30 1 2 31 2 H 1 5 14.51 2 14.51 32 3 I 1 2 14.12 q 2 14.12 33 4 J 1 1 13.44 Q 2 34 5 K 1 3 14.21 q 2 14.21 35 6 L 1 4 14.38 2 14.38 36 7 M 1 6 15.02 2 15.02 37 8 N 1 7 15.11 2 15.11 38 2 39 2 40 予選 4 ←組 3 41 ﾚｰﾝ 名前 所属 学年 順位 記録 3 42 1 3 43 2 U 1 1 13.88 Q 3 44 3 V 1 4 14.66 3 14.66 45 4 W 1 6 14.82 3 14.82 46 5 X 1 2 14.01 q 3 14.01 47 6 Y 1 3 14.55 3 14.55 48 7 Z 1 5 14.78 3 14.78 49 8 3
H5 =IF(AND(ISNUMBER(G5),I5<$E$2),IF(F5<=$F$2,"Q",IF(RANK(J5,$J$6:$J$49,1)<=$H$2,"q","")),"") →H5:H49フィルコピー I5 =I4+IF(B5="予選",1,0) →I5:I49フィルコピー J5 =IF(AND(ISNUMBER(G5),I5<$E$2,F5>$F$2),G5,"") →J5:J49フィルコピー F2 =CHOOSE($E2,8,2,1,1) H2 =CHOOSE($E2,0,4,5,4)
(コタ) 16:23 H5とJ5の数式修正
しげちゃん様、(コタ)様ご教授ありがとうございます。
しげちゃん様の数式にて作成してみましたが 順位通過（Ｑ）がタイム通過（ｑ）に合算されるのではないでしょうか？ 3組1着+5のとき（ｑ）が二つしかつかないようです Ｈ2の数値を予選通過総数にするといいのでしょうか？
申し訳ございませんがよろしくお願いいたします
（minami）
>3組1着+5のとき（ｑ）が二つしかつかないようです
例示された表で H2の値を5に変更して40行目以下を削除してどの様になりますか?
多くなることはあっても少なくはならないと思いますが・・・・
「（ｑ）が二つしかつかない」 その表のデータ（順位、タイム）を提示願えますか?
By しげちゃん
しげちゃん様、40行目以降を削除しましたが変化はありませんでした
下記のような表です
H11,20,24にも（ｑ）が入ってほしいのですが すみません、何か問題がありますでしょうか？
H7に =IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$7:$F$49>$F$2,$G$7:$G$49),$H$2),"q","")),"") 以下フィルコピー
B C D E F G H 1 2 女子1年100Ｍ 3組 1 着 + 5 3 4 予選 1組 5 ﾚｰﾝ 名前 所属 学年 順位 記録 6 1 7 2 A 1 7 15.66 8 3 B 1 4 14.67 9 4 C 1 5 14.75 10 5 D 1 1 13.65 Q 11 6 E 1 2 14.28 12 7 F 1 6 15.40 13 8 G 1 3 14.48 14 15 16 予選 2組 17 ﾚｰﾝ 名前 所属 学年 順位 記録 18 1 19 2 H 1 7 15.82 20 3 I 1 3 14.41 21 4 J 1 2 14.20 q 22 5 K 1 1 13.63 Q 23 6 L 1 4 14.45 24 7 M 1 5 14.58 25 8 N 1 6 14.92 26 27 28 予選 3組 29 ﾚｰﾝ 名前 所属 学年 順位 記録 30 1 31 2 O 1 5 15.23 32 3 P 1 3 14.49 33 4 Q 1 2 14.09 q 34 5 R 1 1 13.70 Q 35 6 36 7 S 1 4 14.76 37 8 T 1 6 15.49
（minami）
配列数式にしていますか?
普通の式は、式を入力して ENTER で確定しますが 配列数式は Ctrlキーと Shiftキーを押しながら Enter
By しげちゃん
横からこそっと。 H5とJ5の数式を、OFFSET関数を使わないように修正しました。 (コタ)
配列数式を多用すると重くなりますので、作業用セル(H3)を使用して
B C D E F G H 1 2 女子1年100Ｍ 3組 1 着 + 5 3 4 予選 1組 5 ﾚｰﾝ 名前 所属 学年 順位 記録 6 1 7 2 A 1 7 15.66 8 3 B 1 4 14.67 9 4 C 1 5 14.75 10 5 D 1 1 13.65 Q 11 6 E 1 2 14.28 12 7 F 1 6 15.40 13 8 G 1 3 14.48 14 15 16 予選 2組 17 ﾚｰﾝ 名前 所属 学年 順位 記録 18 1 19 2 H 1 7 15.82 20 3 I 1 3 14.41 21 4 J 1 2 14.20 q 22 5 K 1 1 13.63 Q 23 6 L 1 4 14.45 24 7 M 1 5 14.58 25 8 N 1 6 14.92 26 27 28 予選 3組 29 ﾚｰﾝ 名前 所属 学年 順位 記録 30 1 31 2 O 1 5 15.23 32 3 P 1 3 14.49 33 4 Q 1 2 14.09 q 34 5 R 1 1 13.70 Q 35 6 36 7 S 1 4 14.76 37 8 T 1 6 15.49
H3=SMALL(IF($F$6:$F$49>$F$2,$G$6:$G$49),$H$2) 配列数式 Ctrl + Shift + Enter
H6=IF(G6="","",IF(F6<=$F$2,"Q",IF(G6<=$H$3,"q",""))) 必要範囲にコピー
SMALL,RANK関数の多様は重くなる原因なので、多用は避けたほうがいいかな 今回の様に100行程度であれば問題ないと思いますが・・・
By しげちゃん
しげちゃん様 遅くなってすみません
>配列数式にしていますか?
配列にすると"Ｑ"のみ表示"ｑ"が消えます
>作業用セル(H3)を使用して
みても"Ｑ"のみ表示となります
さっきからご教授いただいた数式の意味を考えているのですが 今の所わかりません もうすこし考えて見ます
（minami）
>>配列数式にしていますか?
>配列にすると"Ｑ"のみ表示"ｑ"が消えます
何度やっても こちらでは問題ありませんが・・・
H7をクリックして、数式バーを見て下さい
{=IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$7:$F$49>$F$2,$G$7:$G$49),$H$2),"q","")),"")}
と言うように式が{}で囲まれていますか?
列番号のA列が抜けていますが、値は提示された表のようになっていますか?
>>作業用セル(H3)を使用して >みても"Ｑ"のみ表示となります
H3に式を入力して 配列数式にした結果 H3の値はどの様になりましたか こちらでは 14.45 になりますが・・・・
By しげちゃん
>H7をクリックして、数式バーを見て下さい
{=IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$7:$F$49>$F$2,$G$7:$G$49),$H$2),"q","")),"")}
同様です
>列番号のA列が抜けていますが
A列は使用していません
>H3に式を入力して 配列数式にした結果
"0"になります
何か問題があるのでしょうか？
H3に任意に 14.45 と入力すると思いどうりになります
ちなみに SMALL(IF($F$6:$F$49>$F$2,$G$6:$G$49),$H$2) この数式の意味は F6からF49(順位)がF2(順位通過)より大きい(後位)の場合のG6からG49(記録)の範囲でH2(5)番目の小さい値 と言う意味でしょうか？
（minami）
>F6からF49(順位)がF2(順位通過)より大きい(後位)の場合のG6からG49(記録)の範囲で >H2(5)番目の小さい値と言う意味でしょうか？ そのような意味で結構です
因みに、式を間違えて入力していませんか? SMALL(IF($F$6:$F$49>$F$2,$G$6:$G$49),$H$2) SMALL(IF($F$6:$F$49>$F$2,$G$6:$G$49,),$H$2) ↑ ここにカンマを入れていませんか?
下記のような表を作成して A B C 1 1 1 2 2 2 3 1 3 4 2 4 5 1 5 6 2 6 7 1 7 8 2 8 9 1 9 10 2 10
C1=SMALL(IF(A1:A10=2,B1:B10),2) C2=SMALL(IF(A1:A10=2,B1:B10),3) 配列数式にするとどのようになりますか?
C1=4 C2=6 になりますか
普通に ENTER にすると C1=#NUM! C2=3
By しげちゃん
因みに
>>H3に式を入力して 配列数式にした結果 >"0"になります この記載方法では H3に文字として0がは行っていることになりますが・・・ 数値と文字の違いが理解できますか
今日はコレでおしまい、また明日
By しげちゃん
しげちゃん様
>因みに、式を間違えて入力していませんか? 間違えてはいません
>C1=4 C2=6 になりますか
>普通に ENTER にすると C1=#NUM! C2=3
上記のようになります
>H3に文字として0がはいっていることになりますが
表示形式を数値にして小数点以下の表示を2とすると 0.00と表示されます
ちなみに最初にご教授いただいた =IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$7:$F$49>$F$2,$G$7:$G$49),$H$2),"q","")),"") ですが
G7(当該記録)が数値の時、F7(順位)がF2(順位通過)以下の場合は"Q"を表示 偽の時G7(当該記録)が「F7からF49(順位)がF2(順位通過)より大きい(後位)の場合のG7からG49(記録)の 範囲でH2(5)番目の小さい値」以下の時"q"を表示 偽の時空白を表示
という意味になると思いますが 計算結果をみると「F7からF49(順位)がF2(順位通過)より大きい場合」の記録が省かれないようです
なぜ省かれないのかも疑問なのですが
=IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$7:$F$49>$F$2,$G$7:$G$49),$H$2),"q","")),"") この数式を配列数式にすることも私にとって疑問です
長々とお付き合いしてもらっている上、このような質問も 恐縮なのですがよろしくお願いいたします
ご迷惑かけついでですが
上記数式を =IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$6:$F$49>$F$2,$G$6:$G$49),($H$2+$E$2*$F$2)),"q","")),"")
（タイム通過数に（組数*順位通過）を加える）
として配列で無しにフィルコピーすると 見た目上はタイム通過者を拾ってくれるのですが 弊害がありますでしょうか？
（minami）
>>C1=4 C2=6 になりますか >>普通に ENTER にすると C1=#NUM! C2=3 >上記のようになります 配列数式及び 普通にENTER の両方について確認したということですか
C1=SMALL(IF(A1:A10>1,B1:B10),2) C2=SMALL(IF(A1:A10>1,B1:B10),3) でも同じになることを確認して下さい
>という意味になると思いますが >計算結果をみると「F7からF49(順位)がF2(順位通過)より大きい場合」の記録が省かれないようです >なぜ省かれないのかも疑問なのですが こちらでは問題なくそのように表示されます (失礼な言い方ですが、逆にきちんと配列数式にしているのだろうか? 式を間違いなく入れているのだろうかと思っています。) どのように思われても結構ですが、その根拠を提示ください 私の提示した表で、配列数式によりC1=4 C2=6 となることを確認すれば省かれることが確認できると思います
配列数式については、下記を参照して下さい http://pc.nikkeibp.co.jp/pc21/special/hr/
>上記数式を >=IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$6:$F$49>$F$2,$G$6:$G$49),($H$2+$E$2*$F$2)),"q","")),"") >（タイム通過数に（組数*順位通過）を加える） と同じ結果を求めるのであれば =IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL($G$6:$G$49,$H$2+$E$2*$F$2),"q","")),"") でいいですね >弊害がありますでしょうか？ ある組の1位が全体の8位より悪い場合 全体の8位+ある組の1位になります 本来は全体の7位+ ある組の1位 にしたいのでは? 滅多にないことなので、それでよければいいのですが・・
大変失礼な言い方になりますが、あなたが 提示した式及び 私の提示した式を ここからコピーして 数式バーに貼り付けて配列数式にして行ってみて下さい
=IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$7:$F$49>$F$2,$G$7:$G$49),$H$2),"q","")),"") =IF(ISNUMBER(G7),IF(F7<=$F$2,"Q",IF(G7<=SMALL(IF($F$6:$F$49>$F$2,$G$6:$G$49),($H$2+$E$2*$F$2)),"q","")),"")
By しげちゃん
F5 F17 F29 の 順位 G5 G17 G29 の 記録 という文字を削除して (F,G列の文字を削除 41行目にもあれば)
H6=IF(ISNUMBER(G6),IF(F6<=$F$2,"Q",IF(G6<=SMALL(INDEX(($F$6:$F$49<=$F$2)*100+$G$6:$G$49,),$H$2),"q","")),"")
By しげちゃん
しげちゃん様
昨日から延々とご指導いただき誠に申し訳ございません
大変申し訳ございません
F列に各組の順位を記録が入力された時に求めるように関数を入力していました
F6=IF(G6="","",(RANK(G6,$G$7:$G$13,1))) ・ ・ ・ F49=IF(G49="","",(RANK(G49,$G$42:$G$49,1)))
お騒がせしてすみませんでした
（minami)
解決したのかな
>F列に各組の順位を記録が入力された時に求めるように関数を入力していました ということであれば(それを避ければいいので)
H6=IF(ISNUMBER(G6),IF(F6<=$F$2,"Q",IF(G6<=SMALL(IF(($F$6:$F$49>$F$2)*ISNUMBER($F$6:$F$49),$G$6:$G$49),$H$2),"q","")),"")
配列数式です
By しげちゃん
しげちゃん様
解決しました、ありがとうございました
最後の数式で完全に思い通りにできるようになりました
心よりお礼申し上げます
（minami)
何度も申し訳ございませんが 今一度お教え願います
記録を編集しておりましたところ 1組から4組まで一列に作成していたものを 二列にしなければならなくなりました
B C D E F G H I J K L M N O P Q 2 3組 1 着+ 5 3 4 予選 1組 予選 2組 5 ﾚｰﾝ 名前 所属 学年 順位 記録 ﾚｰﾝ 名前 所属 学年 順位 記録 6 1 1 7 2 7 15.66 2 7 15.82 8 3 4 14.67 3 3 14.41 q 9 4 5 14.75 4 2 14.20 q 10 5 1 13.65 Q 5 1 13.63 Q 11 6 2 14.28 q 6 4 14.45 q 12 7 6 15.40 7 5 14.58 13 8 3 14.48 8 6 14.92 14 15 16 予選 3組 予選 4組 17 ﾚｰﾝ 名前 所属 学年 順位 記録 ﾚｰﾝ 名前 所属 学年 順位 記録 18 1 1 19 2 5 15.23 2 20 3 3 14.49 3 21 4 2 14.09 q 4 22 5 1 13.70 Q 5 23 6 6 24 7 4 14.76 7 25 8 6 15.49 8
列I,Jは未使用
｛H6=IF(ISNUMBER(G6),IF(F6<=$F$2,"Q",IF(G6<=SMALL(IF(($F$6:$F$49>$F$2)*ISNUMBER($F$6:$F$49),$G$6:$G$49),$H$2),"q","")),"")｝
これを
｛H6=IF(ISNUMBER(G6),IF(F6<=$F$2,"Q",IF(G6<=SMALL(IF((($F$6:$F$25,$O$6:$O$25)>$F$2)*ISNUMBER(($F$6:$F$25,$O$6:$O$25)),($G$6:$G$25,$P$6:$P$25)),$H$2),"q","")),"")｝
というように
F6:F49 を F6:F25,O6:O25 に
G6:G49 を G6:G25,P6:P25 に
変えてみたのですが、違うんですね
記録が入っているところの列H,Qに"Q"以外は"VALUE!"が表示されます
この場合の手法を出来ればご教授願います
（minami)
作業用列を利用したほうがいいのでは
Sheet2のA列を作業用列とします Sheet2の A1=IF(AND(Sheet1!F6>Sheet1!$F$2,ISNUMBER(Sheet1!G6)),Sheet1!G6,"") A25までコピー
A26=IF(AND(Sheet1!O6>Sheet1!$F$2,ISNUMBER(Sheet1!P6)),Sheet1!P6,"") A50までコピー
Sheet1の H3=SMALL(Sheet2!A:A,H2)
H6=IF(F6="","",IF(F6<=$F$2,"Q",IF(G6<=$H$3,"q",""))) H25までコピー
Q6=IF(O6="","",IF(O6<=$F$2,"Q",IF(P6<=$H$3,"q",""))) Q25までコピー
By しげちゃん
因みに >記録を編集しておりましたところ >1組から4組まで一列に作成していたものを >二列にしなければならなくなりました エクセルで計算させるためには、表を分割しないことをお勧めします
しげちゃん様
重ね重ねありがとうございます
ご迷惑おかけしました
うまく出来そうです
（minami)
Sheet2を使用しなくても
H3=SMALL(IF(($F$6:$O$25>$F$2)*(MOD(COLUMN($G$6:$P$25),9)=7)*ISNUMBER($F$6:$O$25),$G$6:$P$25),$H$2)
配列数式です
でいいかな
By しげちゃん
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.