[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ランキングの表示』（nobusan）
宜しくお願い致します。 以前問い合わせがあった [[20080617110955]]「別シートにランキング」 を参考にして同じように作成してみたのですが、
Sheet1が下記のようにタイムの脇に順位が既に表示してます。
A B C D E F G H I
1 1回目 2回目 3回目 4回目 2 2008/6/1 07:11.01 12 07:10.35 11 11:17.85 54 09:57.06 47 3 2008/6/2 07:24.18 19 07:09.44 8 09:47.94 45 10:00.21 48 4 2008/6/3 07:15.91 14 14:17.53 61 08:20.24 37 5 2008/6/4 08:26.88 38 14:20.90 63 08:51.27 39 6 2008/6/5 13:08.77 55 08:14.47 36 14:10.34 60 7 2008/6/6 07:36.32 32 14:20.40 62 09:19.00 42 8 2008/6/7 07:04.76 5 13:12.32 56 07:28.88 24 9 2008/6/8 07:33.90 28 07:57.95 35 10:37.12 51 10 2008/6/9 07:27.26 23 07:52.47 34 10:45.47 53 13:49.95 57 11 2008/6/10 07:25.09 21 13:54.66 58 07:33.26 27 14:01.06 59 12 2008/6/11 07:16.34 15 09:37.06 43 13 2008/6/12 07:09.56 9 07:36.82 33 10:15.93 50 07:24.49 20 14 2008/6/13 07:14.44 13 07:35.87 31 10:38.05 52 06:47.81 3 15 2008/6/14 07:23.44 18 07:35.14 29 10:09.19 49 16 2008/6/15 07:09.94 10 07:22.50 17 09:48.83 46 06:34.42 2 17 2008/6/16 07:07.11 6 07:26.98 22 09:01.96 40 18 2008/6/17 07:08.62 7 07:21.24 16 19 2008/6/18 07:29.29 25 07:35.66 30 09:44.88 44 20 2008/6/19 07:01.87 4 07:31.43 26 09:13.84 41 06:24.73 1
B,D,F,H列のタイムは「mm:ss.00」の形式、 C,E,G,I列の順位は「C2=IF(ISERROR(RANK(B2,($B$2:$B$20,$D$2:$D$20,$F$2:$F$20,$H$2:$H$20),1)),"",(RANK(B2,($B$2:$B$20,$D$2:$D$20,$F$2:$F$20,$H$2:$H$20),1)))」 という数式です。
ちなみに約１年分のデータ(行)となっています。
これを(sin)様のご教授を参照にSheet2にUXILEさん同様
A B C D
1 順位 時間 月日 回数
2 1
3 2
4 3
5 4
6 5
7 6
8 7
としたかったのですが、良くわからなかったため一旦Sheet3にSheet1のデータを 「元データはSheet1のA1:Exxにあり、1行目が見出しとして考えてます。」となるように 作成するようにしてSheet1→Sheet3→Sheet2と参照するようになっています。
この為Sheet1に数値を入力した時、再計算が非常に長いです。
（sin)様は「早い時間を先に求めて、その値をベースに他の要素を出してます」 との手法だったのですが、Sheet1にせっかく順位が計算されるので、なんとかSheet3を 使わないで、順位を元にタイム、日付、回数がSheet2に表示できないかと思うのですが 何とかなりますでしょうか？
ちなみに回数を求める時 Sheet2のD2セルに（改行してます） =IF(COUNT(B2),INDEX(Sheet1!$B$1:$E$1, MATCH(TEXT(B2,"mm:ss.00"), INDEX(TEXT(OFFSET(Sheet1!$A$1,MATCH(C2,Sheet1!$A$2:$A$32,0),1,1,4),"mm:ss.00"),0),0)),"")
このようにしてみたのですが、仮に1回目と２回目が同タイムの時 両方「１回目と表示されるようなのですが」 上手く出来ますでしょうか？
宜しくお願い致します。
こんにちは〜♪
Sheet2 ↓
┌─┬────┬─────┬─────┬──────┐ │ │ A │ B │ C │ D │ ├─┼────┼─────┼─────┼──────┤ │ 1│順位 │時間 │月日 │回数 │ ├─┼────┼─────┼─────┼──────┤ │ 2│ 1│ 06:24.7│ 6月19日│4回目 │ ├─┼────┼─────┼─────┼──────┤ │ 3│ 2│ 06:34.4│ 6月15日│4回目 │ ├─┼────┼─────┼─────┼──────┤ │ 4│ 3│ 06:47.8│ 6月13日│4回目 │ └─┴────┴─────┴─────┴──────┘
A2セルへ =SMALL((Sheet1!$C$2:$C$20,Sheet1!$E$2:$E$20,Sheet1!$G$2:$G$20,Sheet1!$I$2:$I$20),ROW(A1))
B2セルへ =INDEX(LARGE(((Sheet1!$C$2:$I$20)=A2)*(Sheet1!$B$2:$H$20),COUNTIF($A$2:A2,A2)),)
C2セルへ =INDEX(Sheet1!$A:$A,INDEX(SMALL(((Sheet1!$C$2:$I$20)<>A2)*10^4+ROW($2:$20),COUNTIF($A$2:A2,A2)),))
D2セルへ =INDEX(Sheet1!$1:$1,INDEX(SMALL(((Sheet1!$C$2:$I$20)<>A2)*10^4+COLUMN(C:I),COUNTIF($A$2:A2,A2)),))
それぞれ下へコピー。。。
空白処理はしていませんので。。
ご参考にどうぞ。。。
。。。Ms.Rin〜♪♪
私も考えてみました。しかも元データのシート400行・計算シート1600行に数式コピーで。 重たいです。古PCを差し引いても重たいです。クラッシュするのでは？dokidokiものでした。 んで、Ms.Rin〜さんのパクリも含めて、 ※現実に即した言葉に変更。はい、パクリました。
A2セル：改行してます。 =IF(COUNT(Sheet1!$C$2:$C$400,Sheet1!$E$2:$E$400,Sheet1!$G$2:$G$400,Sheet1!$I$2:$I$400)<ROW(A1),"", SMALL((Sheet1!$C$2:$C$400,Sheet1!$E$2:$E$400,Sheet1!$G$2:$G$400,Sheet1!$I$2:$I$400),ROW(A1)))
B2セル： =IF(A2="","",SMALL((Sheet1!$B$2:$B$400,Sheet1!$D$2:$D400,Sheet1!$F$2:$F$400,Sheet1!$H$2:$H$400),A2))
C2セル：改行してます。 ※配列数式にしてます。CtrlとShiftとEnterとを一緒に打鍵してください。 =IF($A2="","",INDEX(Sheet1!$A$1:$A$400, INT(SMALL(IF(Sheet1!$C$2:$I$400=$A2,ROW(Sheet1!$C$2:$I$400)*10+COLUMN(Sheet1!$C$2:$I$400), COUNT(Sheet1!$A$2:$A$400)*100),COUNTIF($A$2:$A2,$A2)))/10))
D2セル：改行してます。 ※配列数式にしてます。 =IF($A2="","",INDEX(Sheet1!$B$1:$H$1, MOD(SMALL(IF(Sheet1!$C$2:$I$400=$A2,ROW(Sheet1!$C$2:$I$400)*10+COLUMN(Sheet1!$C$2:$I$400), COUNT(Sheet1!$A$2:$A$400)*100),COUNTIF($A$2:$A2,$A2)),10)-2))
UXILEさんの時には、思いもしなかったので、パスしてますが >仮に1回目と２回目が同タイムの時 の計算も出来ているはずです。 しかし、そのために配列数式しか思いつかなかったので、上記式になっています。 これが重たい理由 かも？ ※データいじるのが面倒なので、細部の検証まではしてませんが。。。 また、これは余計な事ですが、 全データを二回も参照させる事（ランク計算部分とそのランク部分を計算させる今回の部分）も、 重たくなる一因かと思います。 (sin) リンク付けの為、ご質問の文章を少しいじりました。
。。。Ms.Rin〜♪♪様 (sin)様
ご教授ありがとうございます。
返事が遅くなって申し訳ございません。
奮闘中ですがまだはっきりするところまではたどり着きません。
出来ましたら報告させていただきたいと思いますので
よろしくお願いいたします。
申し訳ございません。
（nobusan)
恐れ入りますが再びご教授願います。
いまさら言うと怒られるかもしれませんが、Sheet1の列と行が
C D E F G H I J K L
1回目 2回目 3回目 4回目
7 2008/6/1 07:11.01 12 07:10.35 11 11:17.85 54 09:57.06 47
8 2008/6/2 07:24.18 19 07:09.44 8 09:47.94 45 10:00.21 48
9 2008/6/3 07:15.91 14 14:17.53 61 08:20.24 37
10 2008/6/4 08:26.88 38 14:20.90 63 08:51.27 39
11 2008/6/5 13:08.77 55 08:14.47 36 14:10.34 60
12 2008/6/6 07:36.32 32 14:20.40 62 09:19.00 42
・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・
385 2008/6/19 07:01.87 4 07:31.43 26 09:13.84 41 06:24.73 1
(C385の日付は無視願います)
実際には日付が列CでC7〜C385迄、列E,G,I,Kがタイムで行7〜385、列F,H,J,Lがタイムを 基にしたランキングが行7〜385に入ります。（空欄も多数あり）
(sin)様ご指導分をセル番地を変えて入力しましたが、列A(順位)・列B(時間)は上手く 行きましたが、列Cが5日ずれて表示される為、C2セルに
=IF($A2="","",INDEX(Sheet1!$C$6:$C$385, INT(SMALL(IF(Sheet!$F$7:$L$385=$A2,ROW(Sheet!$F$7:$L$385)*10 +COLUMN(Sheet1!$F$7:$L$385),COUNT(Sheet1!$C$7:$C$385)*100),COUNTIF ($A$2:$A2,$A2)))/10))-5 ←「-5」と対処してみました。
列Dですが、D2セルに
=IF($A2="","",INDEX(Sheet!$E$6:$K$6, MOD(SMALL(IF(Sheet1!$F$7:$L$385=$A2,ROW(Sheet1!$F$7:$L$385)*10 +COLUMN(Sheet1!$F$7:$L$385),COUNT(Sheet1!$C$7:$C$385)*100), COUNTIF($A$2:$A2,$A2)),10)-2))
と入力しましたが、「０」となります。以下にコピーすると
1回目 → 「０」 2回目 → 「０」 3回目 → 「1回目」 4回目 → 「#VALUE!」
となります。
ずっと考えているのですが何処を修正すればよいか一向にわかりかねます。
何卒ご指導願えますよう御願い致します。
(nobusan)
昨日は、終日遊んでいたので、遅くなりました。。。 指導できるほど偉くもないので、考え方の説明を拙い文章にしてみます。
月日(C列)に関しては、
=IF($A2="","",INDEX(Sheet1!$A$1:$A$400,
~~~~~~~~~~~~~~~~~~~~~~~~INDEXの配列の何番目かを↓で求めます。何番目：A1が一番目、A400が400番目
INT(SMALL(IF(Sheet1!$C$2:$I$400=$A2,ROW(Sheet1!$C$2:$I$400)*10+COLUMN(Sheet1!$C$2:$I$400),
_____ =========条件============ ~~~~~~~~~~~~~~真の場合~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COUNT(Sheet1!$A$2:$A$400)*100),COUNTIF($A$2:$A2,$A2))/10)) ※『)』がおかしかったので修正！
~~~~~~~~~~~~偽の場合~~~~~~~~~~ _____________________
SMALL関数の範囲(IF関数の結果)の順位↑ ----INT関数でINDEXの配列の何番目かを指定するために調整
IF関数でC2:I400の範囲の各セルの値が、A2の値(自然数)と同じならば、そのセルの行番号*10の値と列番号の値を加算。 違う場合は、A2:A4の範囲内の数値の数*100の値にする。 という条件で、仮想シートC2:I400のセルに値を入れていると思って下さい。 （ 配列数式にすることによりこの処理が出来ます。 ）
真の場合の式の意図は、掛ける10することで、10の位以上に行番号情報を、1の位で列情報を保持する数値を作ります。 今回L列(列番号12)までとなりますので、行番号*10では、10の位が行・列情報が混在する事になります。 従って、*100とすれば、100の位以上に行番号、10の位までに列情報を保持する数値に出来ます。 偽の場合は、SMALL関数の対象外となる数値を作りたいだけですから、データ範囲における最大値38512（最大行番号385*100+最大列番号12) より大きい数字を設定します。前回は、数値データ数の100倍という式にしてますが、38512より大きければ問題ないです。 例えば、40000と数値入力でもOKです。
SMALL関数で求められた値を10で割り(今回だと100)で割り、INT関数で整数部分の行番号をINDEXの何番目かに当てはめますが、 INDEXの配列の最初のものが、1になるようにしなければなりません。7行目からであるなら行番号7を2とするために-5すれば、調整できます。 INDEXの配列を見出しを入れずに7行目からとするならば、-6で調整できます。
回数(D列)に関しても、上記と考え方は同じです。 ただ、列情報を得るためにINT関数でなく、余りを求めるMOD関数に置き換えています。 前回なら10で割った余り、今回なら100で割った余りを元に列番号を入手し、 INDEXの配列に対し、同様の調整を行えば出来ます。注意する点は、SMALL関数の対象列は、順位の列ですお間違えないように。。。
という内容でわかりますでしょうか？ご自身でもチャレンジしてみてください。
一応式自体は↓こんな感じでしょうか？ =IF($A2="","",INDEX(Sheet1!$C$6:$C$405, INT(SMALL(IF(Sheet1!$F$7:$L$405=$A2,ROW(Sheet1!$F$7:$L$405)*100+COLUMN(Sheet1!$F$7:$L$405),COUNT(Sheet1!$C$7:$C$405)*1000), COUNTIF($A$2:$A2,$A2))/100)-5)) と =IF($A2="","",INDEX(Sheet1!$E$6:$K$6, MOD(SMALL(IF(Sheet1!$F$7:$L$405=$A2,ROW(Sheet1!$F$7:$L$405)*100+COLUMN(Sheet1!$F$7:$L$405),COUNT(Sheet1!$C$7:$C$405)*1000), COUNTIF($A$2:$A2,$A2)),100)-5)) (sin)
(sin)様 まことにありがとうございます。 ご丁寧に説明いただき恐縮しております。 表自体は思い通り作成でき感激しております。
私の力では今のところ完全に理解するに至りませんが、引き続き勉強したいと思います。 また何かございましたらよろしくお願いいたします。 (nobusan)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.